SQL Server logical consistency-based I/O error I have a job on my live environment DB and the job has failed again this morning reporting SQL Server Error 824 (Soft I/O error, where the OS could read the page but SQL has treated it as corrupt):

  SQL Server detected a logical consistency-based I/O error: incorrect checksum it occurred during a read of page (1:9545681) in database ID 5 at offset 0x0000123615a000 in file 'D:XXX\XX.mdf'

I have had a look and ran DBCC CHECKDB with all_errormsgs and saw the error didn't mention CHECKSUM failure, however it reports the repair level needed to repair this error is repair_allow_data_loss.

Is there anything else that can be done other than running the repair SQL Server has recommended since I don't want any data loss?

I have a good backup policy in place, however the backup that was last working when the job ran without any problems has now been deleted due to space issues. Is there anything else I can do now?

First; ensure you have a reliable, recent backup of the database in question.  Test the backup by restoring it to some other server, then run DBCC CHECKDB against it to determine if the backup contains the corrupted page(s).  If the backup shows no corruption, and DBCC CHECKDB returns a clean bill-of-health for the database, you might consider just restoring the backup if it's recent enough to meet your recovery point objective.

Second, backup the database (while ensuring you don't overwrite any existing backups you may need), and restore it to some other machine.  Run DBCC TRACEON (3604);  DBCC PAGE (5, 1, 9545681) to see what kind of data is stored in the page - look for m_objId and m_indexId in the output, then check that against sys.objects and sys.indexes.  Best case scenario, the data will be part of a non-clustered index that you can simply drop and recreate with ALTER INDEX ... REBUILD.  If the data on the page belongs to a clustered index, or a heap, you run the risk that DBCC CHECKDB (repair_allow_data_loss) will cause you to lose access to the entire table, or at least part of the data stored in it.

If you manage to get the corruption issue resolved, you should determine the root cause so you can mitigate the issue, and prevent future corruption.  If hardware is an issue, replace it.

Brent Ozar has a great article detailing how to approach this problem on his blog.


Read Only Access Except Stored Procedures in Specific Schema I'm building an application that is attached to a legacy application database. The application has it's own database that is on the same server as the legacy database.

My application is ASP.NET and I'm using Entity Framework.  I've set up a domain user that is a DB owner for my application database and has read-only access to the legacy database.  I don't want to accidentally make any updates to the legacy database through some bug in the application.

I would like to create stored procedures in my new database that can modify data in the legacy database.  I figured putting them in their own schema might be a logical start, at least for the sake of organization.  I understand schemas are good for organizing security access.  Is there a good approach I can take to make this work?

If you are looking to create embedded sql-inline Linq queries within Entity Framework, you can assign dbreader or dbwriter permissions appropriately to the application login.

Unfortunately currently in SQL Server, you cannot create specific access to Stored Procedures which only conduct read operations as a category. You will have to grant individual reader or write access to the specific stored procedures by
Grant execute on Stored procedure to user, etc.


Why does `SELECT count(*)` return 1? Some RDBMS's seem to allow a query without a FROM clause, eg:

postgres=# SELECT 'foo' bar;
┌─────┐
│ bar │
├─────┤
│ foo │
└─────┘

But for those that allow it, why would SELECT count(*) not return 0?

postgres=# SELECT count(*);
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘

Speaking for PostgreSQL, I found this question to be somewhat interesting.

Omitted FROM Clauses

This isn't at all permitted in the SQL spec. PostgreSQL calls this feature an "Omitted FROM clause", and says this about it

  PostgreSQL allows one to omit the FROM clause. [...] Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT. Note that if a FROM clause is not specified, the query cannot reference any database tables.

Here is what isn't explicit. PostgreSQL is simply introducing an implicit one-row dummy table for you. The example of the syntax is SELECT 2+2 so you can see it's only really made for trivial cases.

# SELECT count(*);
 count
-------
     1

This makes sense so long as you remember that it's operating on an implicit dummy table.

CREATE TABLE dummy_table ();
INSERT INTO dummy_table DEFAULT VALUES;

SELECT count(*) FROM dummy_table;


Creating a function for a simple select statement? I'm writing an application and I'll be running the following query:

SELECT Job_name,EMP,Lastname,FirstName from employe E
inner join JOBS F ON F.CODE_JOB=  E.CODE_JOB
inner join EMPS S ON S.Code_str = E.CODE_STR
where USER_ID = X

Does it make sense to actually make a function for it and send the USER_ID as a parameter or should I just run the query in the application? I'm just looking to know what are the best practices here.

You could write an inline table valued function, or use a view to encapsulate this code, but it would depend on what you hope to get out of it.

There's no performance gain by doing so. It can make the application query simpler, or obfuscate table names, but that's about it.

Obfuscating table names may provide an additional, though minimal layer of security. Any escalation of privileges could result in someone seeing the view definition and exposing the table names anyway.


MySQL: Expanding table columns and insert/update values I need to combine two tables into one.

Table s_ogr

id | ad | numara | yurt_id |

Current Table s_kontrol

id | ogr_id | yurt_id

What is needed s_kontrol_k

id | ogr_id | ogr_numara |ogr_ad | yurt_id  `(ogr_id represent the id from s_ogr)`

( ad = name , yurt_id = department_id, ogr_id is student_number(reason of problem, should be student_id)  )

My Sittuation; s_kontrol is full of data. I need it to be like s_kontrol_k , but new two fields should be taken from s_ogr table , by the match operation of s_ogr and s_kontrol (s_ogr.id=s_kontrol.ogr_id and s_ogr.yurt_id =s_kontrol.yurt_id). Previous mistake was not to add the ogr_id as student_id into s_kontrol

How to create my new s_kontrol_k with filled values of new columns. I am able to copy the same values from s_kontrol but could'n achieve to fill new colums

As far as I can understand what you want is

    CREATE TABLE IF NOT EXISTS `s_kontrol_k` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `ogr_id` int(11) NOT NULL,
      `ogr_ad` varchar(255) NOT NULL,
      `ogr_numara` varchar(255) NOT NULL,
      `yurt_id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    );

    INSERT INTO `s_kontrol_k` (
        SELECT sk.id,sk.ogr_id, so.ad AS ogr_ad,
        so.numara AS ogr_numara, so.yurt_id FROM s_kontrol AS sk
        INNER JOIN s_ogr AS so ON sk.ogr_id = so.id
    );

First create the desired table s_kontrol_k and copy data from both s_ogr and s_kontrol  to newly created table s_kontrol_k.

I hope this helps..


Java driver dont fire query-start probe in postgres 11 While using SystemTap to trace postgres session on PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit.

global livequeries

probe process("/usr/pgsql-11/bin/postgres").mark("query-start"){
  printf("[%s] Query start on PID %d : %s \n", ctime(gettimeofday_s()), pid(), user_string($arg1) )
  livequeries[tid(),$arg1] = gettimeofday_us()
}

psql fires this probe but java driver dont.

REF: docs

I presume the difference between you running your queries via psql vs. from a Java program is that you use prepared statements in Java. Processing "simple statements" and prepared statements take different code paths in the Postgres backend (postgres.c).

"query-start" probe is only called when a simple statement is executed (exec_simple_query), while a prepared statement processing has two separate distinct stages: parse (exec_parse_message) and execute (exec_execute_message).

Both code paths converge when they call PortalRun in pquery.c, where the "query-execute" probe is called.

In other words, the two code paths look like this (omitting the "done" probes):

psql -> simple statement
  query-start probe
  query-parse-start probe
  query-parse-done probe
  query-rewrite-start probe
  query-rewrite-done probe
  query-plan-start probe
  query-plan-done probe
  query-execute-start probe
  query-execute-done probe
  query-done probe

JDBC -> prepared statement
  prepare phase
    query-parse-start probe
    query-parse-done probe
  execute phase
    query-execute-start probe
    query-execute-done probe

If you're not particularly concerned about the query parsing and planning latency, you need to capture the "query-execute-*" probes. If you need the query text, capture also "query-parse-start".


How to get Oracle_Home directory? I'm not asking "where is my home directory", or "how do I find it". I am pretty sure I don't have an Oracle_Home directory and I want one. What is the minimum software package that I should install to get that directory? Or, what is the simplest way to set up the directory odbcad uses for tnsnames.ora?

Background:

I have a Windows 7 32bit computer with an assortment of random non-oracle software on it, which needs to establish an ODBC connection to an oracle database. We have other similar computers with established connections using instantclient_12_1. I was not involved in setting up those other computers.

I installed instanclient_12_1 on the subject computer, set up the ODBC connection in ODBCAD32, and then I got an error:

  Unable to connect SQLState=08004 [Oracle][ODBC][Ora]ORA-12154:
  TNS:could not resolve the connect identifier specified

OK. That's probably because I forgot to copy over the tnsnames.ora file to get this set up. So I copied my tnsnames.ora from one of the running computers, and then I realized that I don't know where the oracle home directory is on the target computer. I checked my environment variables, and I don't think I even have an oracle home directory. There must be some other software package I need to install to get this running. What am I missing?

Place your tnsnames.ora in a directory of your choice, then set the TNS_ADMIN environment variable.

Open CMD, enter sysdm.cpl
In the Advanced tab, select Environment Variables
Under "System Variables", click on 'New' and enter TNS_ADMIN as the name, and the path where your TNSNAMES.ORA resides as your value

Windows steps from here.

On Linux, it's as easy as setting the variable in your shell startup script, eg .bashrc:

export TNS_ADMIN=/path/to/instant/client


Detached DB showing up in back-up history I found a script to show me the databases that are not backed-up in the last 24 hours: Script to retrieve SQL Server database backup history and no backups

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date),
GETDATE()) AS  [Backup Age (Hours)]
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING
(MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24,     GETDATE()))
 UNION
 SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
 master.dbo.sysdatabases.NAME AS database_name,
 NULL AS [Last Data Backup Date],
 9999 AS [Backup Age (Hours)]
 FROM
 master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
 ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
 WHERE msdb.dbo.backupset.database_name IS NULL
 AND  master.dbo.sysdatabases.name <> 'tempdb'
 ORDER BY
 msdb.dbo.backupset.database_name

The result shows me the DB that no logner exists. These are the DB that i have deleted/detached a while back. Why are these showing up?

When you use Delete Database from modern versions of Management Studio, it asks whether you want to delete the backup history for this database in MSDB. The reason that a deleted database is showing in your query is simply because backup history exists for it - in MSDB.

I adjusted your query by joining backupset to sysdatabases, so it excludes databases that do not exist at the moment you run the query.

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date),
GETDATE()) AS  [Backup Age (Hours)]
FROM    msdb.dbo.backupset
inner join master.dbo.sysdatabases
on msdb.dbo.backupset.database_name=master.dbo.sysdatabases.name
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING
(MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24,     GETDATE()))
 UNION
 SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
 master.dbo.sysdatabases.NAME AS database_name,
 NULL AS [Last Data Backup Date],
 9999 AS [Backup Age (Hours)]
 FROM
 master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
 ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
 WHERE msdb.dbo.backupset.database_name IS NULL
 AND  master.dbo.sysdatabases.name <> 'tempdb'
 ORDER BY
 msdb.dbo.backupset.database_name


Conditonally create temporary table Invoking the following SQL leads to a warning 'column "s" has type "unknown"' which I can understand. What I'm trying to accomplish is to conditionally create a temporary in preparation of running a SELECT over multiple schemas. Can I approach this in another way?

CREATE OR REPLACE FUNCTION table_exists(v_table text)
  RETURNS boolean AS
$BODY$
  DECLARE
    v_count int;
    v_sql text;
BEGIN
  v_sql =
    'SELECT ' ||
    '  count(1) ' ||
    'FROM ' ||
    '  information_schema.tables ' ||
    'WHERE ' ||
    E'  table_name=\'' || v_table || E'\'';

  EXECUTE v_sql INTO v_count;
  RETURN v_count>0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
  COST 100;

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT distinct table_schema FROM information_schema.tables WHERE table_schema NOT IN('information_schema', 'pg_catalog', 'public') AND table_type='BASE TABLE'
  LOOP
  IF table_exists('combined_settings') THEN
    EXECUTE '
    INSERT INTO combined_settings
      SELECT '''|| r.table_schema ||''' AS s, prefix, id, value FROM '|| quote_ident(r.table_schema) ||'.settings WHERE id=''VERSION''
    ';

  ELSE
    EXECUTE '
    CREATE TEMP TABLE combined_settings AS
      SELECT '''|| r.table_schema ||''' AS s, prefix, id, value FROM '|| quote_ident(r.table_schema) ||'.settings WHERE id=''VERSION''
    ';
  END IF;

  END LOOP;
  EXECUTE '
  COPY (SELECT * FROM combined_settings) TO ''/tmp/out-' || current_database() || ''' CSV;
  DROP TABLE IF EXISTS combined_settings;
  ';
END$$;

You do not need a function to check if the temporary table exists. You do not even need the table. The query below gives all tables settings in all schemas (except of public and system schemas) in a database, with appropriate schema names:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE
    table_type = 'BASE TABLE'
    AND table_name = 'settings'
    AND table_schema NOT IN('information_schema', 'pg_catalog', 'public')

In the anonymous function below we shall build sql query instead of storing results in a temp table, according to the following pseudocode:

for all tables 'settings':
    build select query text and store it in 'vselect',
    append 'vselect' to 'vquery' joining consecutive selects with word 'UNION'

At the end of the loop we shall have in vquery something like this:

SELECT 'schema1', prefix, id, value FROM schema1.settings WHERE id = 'VERSION'
UNION
SELECT 'schema2', prefix, id, value FROM schema2.settings WHERE id = 'VERSION'
-- etc

and we are going to use this in COPY to a file.

The function:

DO $$
DECLARE
    vrec record;
    vquery text = '';
    vselect text;
BEGIN
    FOR vrec IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
            AND table_name = 'settings'
            AND table_schema NOT IN('information_schema', 'pg_catalog', 'public')
    LOOP
        vselect = format(
            'SELECT ''%s'', prefix, id, value FROM %s.%s WHERE id = ''VERSION''',
            vrec.table_schema, quote_ident(vrec.table_schema), vrec.table_name);
        IF vquery = '' THEN vquery = vselect;
        ELSE vquery = format('%s UNION %s', vquery, vselect);
        END IF;
    END LOOP;
    EXECUTE format('COPY (%s) TO ''/tmp/out-%s'' CSV', vquery, current_database());
END $$;


Login into Oracle Database Error I had installed Oracle 12 to my Linux box. I tried to login using sqlplus but it prompt out this error at below. How to solve it?

  Enter user-name: SYSDBA

  Enter password:  ERROR: ORA-01034: ORACLE not available

  ORA-27101: shared memory realm does not exist

  Linux-x86_64 Error: 2: No such file or directory

  Additional information: 3701

  Additional information: 542678011

  Process ID: 0 Session ID: 0 Serial number: 0

EDIT:
How to find out default ORACLE_SID?

There could be various reason for this error.
 You are using wrong ORACLE_SID and ORACLE_HOME.
 The instance that you are trying to connect isn't up and running.

When you have wrong ORACLE SID and HOME.

I could reproduce the error by changing my SID to something else. My SID is oracdb.

[oracle@myserver ~]$ ps -ef | grep pmon
oracle   29336     1  0 Apr27 ?        00:00:22 ora_pmon_oracdb

But I changed it to orcl.

[oracle@myserver Desktop]$ export ORACLE_SID=orcl

Then I tried to connect to the database using SQL*PLUS.

[oracle@myserver Desktop]$ sqlplus sys

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:23:50 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -923528497
Process ID: 0
Session ID: 0 Serial number: 0

Then I changed it to original SID.

[oracle@myserver ~]$ export ORACLE_SID=oracdb
[oracle@myserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:28:40 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

When your instance is down and connected as a non-sys user

Use the following linux command to check whether some of the important background processes are running or not such PMON or SMON.

[oracle@myserver ~]$ ps -ef| grep pmon
oracle   57587 57044  2 00:52 pts/3    00:00:00 grep pmon

My oracle instance is down.

Now, as you have tried, I attempt to connect to the database using non-sys user, sysdba is a privilege not a database user.

[oracle@myserver ~]$ sqlplus sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:53:04 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -688980181
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

I got the same error as you have got.

Now, connect as sysdba/sysoper and start the database instance.

[oracle@myserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 00:53:36 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup


Passing a table name to a stored procedure Once a new table is created, we have to execute the same SQL code segment, that grants some users rights to the table and does similar maintenance. This code is identical for all cases, differing only in the table name.

I wanted to write a stored procedure to abstract the entire set of statements into one stored procedure call, but I don't understand how to do it. For example, here is a simple one I created:

CREATE PROCEDURE dbo.spGrantTableRights
       @TableName varchar(255)
AS
BEGIN
    DECLARE @Name varchar(255)
    SELECT @Name = name
      FROM sysobjects
     WHERE name = @TableName

    GRANT SELECT            ON @Name TO mainUserName
    GRANT INSERT            ON @Name TO mainUserName
    GRANT DELETE            ON @Name TO mainUserName
    GRANT UPDATE            ON @Name TO mainUserName
    GRANT DELETE STATISTICS ON @Name TO mainUserName
    GRANT TRUNCATE TABLE    ON @Name TO mainUserName
    GRANT UPDATE STATISTICS ON @Name TO mainUserName
    GRANT TRANSFER TABLE    ON @Name TO mainUserName
END

But passing a string table name does not help -- I don't get a system object back. How do I convert this string into the system object? Or is there a different better way to do this altogether?

Note I understand dynamic SQL will work, but it makes code both ugly and unreadable. Is there a standard way to deal with this problem outside of the dynamic SQL framework?

As @Shaulinator stated in his answer; the only way to accomplish this is to use dynamic SQL.

Having said that, I would caution against applying the dont-repeat-yourself mantra too heavily to database code where it doesn't necessarily belong.  I realize your question is about Sybase ASE (now SAP ASE); however since SQL Server and ASE share a very common language I would point you to the dynamic SQL "bible" by Erland Sommarskog, The Curse and Blessing of Dynamic SQL

I would recommend templating your permissions code, such that you do a search-and-replace on the object name whenever you add a new object.  Retain the modified template code in your version control system; most likely in the same place as the object code itself.


Why create an "oracle" user for installing Oracle 10g or 11g? What is the specific reason for creating the oracle user to install Oracle 10g or 11g, even though root exists and can be used?

Is it possible to install as root by adding root to the oinstall group?

Every suitable technical reason is welcome.

PostgreSQL refuses to run under root, and the reasons there may be of interest in this question.

This approach allows you to essentially separate UNIX and db permissions as Phil mentions above, but there are a number of reasons why this is important especially when you have a programmable server like Oracle.

The first is that generally speaking you want to be able to contain the behavior of the system in the event of a bug in your stored procedures or otherwise.  Bugs in stored procedures that allow the db to overwrite arbitrary files are really bad in a database, but they become far worse when you have the ability to overwrite operating system binaries.  So this allows you to lock down and contain damage in a way you can't do if the db runs as root.

The second is that you can essentially give the DBA a smaller area of responsibility.


Update by iterating table in batches faster than whole table in PostgreSQL I have a table with approximately one million rows.

It is being used in production and I ran an UPDATE which covers ~95% of those rows.

After five hours I cancelled the request because it was taking so long.

The table has an auto-incrementing ID column, so I tried extending the WHERE condition of my query to include id BETWEEN 1 AND 500.

This update completed in approximately two seconds. I then manually iterated up through the id in batches of 500, e.g. BETWEEN 500 AND 1000, then BETWEEN 1000 AND 1500.

At this rate it will require 2000 batches of 500 to update the whole table.

Updating 2000 batches at 2 seconds each is just over an hour.

My questions are:

What reasons are there for this difference?
I don't care about transaction isolation, so is there a way to simulate this 'update in batches', without having to script the 2000 updates to run separately.

I'll try my best to answer your question in brief, but since I'm not really aware of your level of comfort with PostgreSQL, and I don't have a lot of time to go into an in-depth explanation anyways, I'll keep the answers simple, and you can ask for clarification if you'd like more info.

1) Why is it faster in batches?

Due to the structure of PostgreSQL's write ahead log, the amount of shared buffer space in RAM, and the attempt to perform the entire UPDATE in a single transaction, my guess is that you simply don't have enough computing resources to efficiently handle the update to nearly a million records in a single transaction.

PostgreSQL has a well-built concurrency control system, essentially meaning that it has to keep the old copies of your pre-UPDATE rows available during your UPDATE operation.  This is so that, in case another client tries to access these rows while you're updating, in case the update fails, or in case you cancel the update, you don't lose the old information.

If you perform a large enough UPDATE, PostgreSQL will load pages into memory and modify them, but will eventually run out of memory to work with, so it is forced to immediately copy these pages temporarily to disk if it wants to be able to load further pages and continue the transaction.

Rather than being able to amortize the disk writes over a period of time, you've just forced your database into a bottleneck.

2) Scripting the updates

You absolutely can script the updates, by creating a function in PL/pgSQL.  There's a lot to learn about PL/pgSQL, including a lot I probably don't know, but generally speaking, you could do something like this

CREATE OR REPLACE FUNCTION mini_batch_update()
  RETURNS void AS
$BODY$
DECLARE

id_val integer;

BEGIN

  FOR id_val IN 0..2000
  LOOP

  **UPDATE GOES HERE**
  WHERE id > (500 * id_val) AND id <= 500 * (id_val + 1);

  END LOOP;

END
$BODY$
LANGUAGE plpgsql;

I didn't take a lot of time to make this batch function in tip-top form; what I mean is that I simply hard-coded several of the numerical values for simplicity's sake.  In your case, you may want to get more detailed and include: 1) Something that checks for the maximum id value so that you set your bounds appropriately, and 2) even though I  hard-coded batches of 500, you could easily make this a function input parameter.

Sorry I don't have time to test this or make sure it really works well.  Good luck!


About count and or null I am practising my MySQL in Leetcode and I've encountered this question:

  The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03|
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

  The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

  Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

I have found a solution in the question's Discuss section, here it is:

select t.Request_at as Day, round(count(t.Status = 'completed' or null)/count(t.Status), 2) AS `Cancellation Rate`
from
Trips as t,
Users as u
where u.Users_Id = t.Client_Id
and u.Banned = 'No'
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;

That is fine. But I want to change it to this:

select t.Request_at as Day, round(count(t.Status = 'completed')/count(t.Status), 2) AS `Cancellation Rate`
from
Trips as t,
Users as u
where u.Users_Id = t.Client_Id
and u.Banned = 'No'
and t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;

and I get the wrong answer.

I don't understand what the or null is used for. Is it necessary? If so, why?

An example - http://sqlfiddle.com/#!9/9eecb7d/59602

The explanation - COUNT(<expr>) counts all non-NULL values. Comparison operators return boolean values 1(=true) or 0(=false) and both are non-NULL so both would be counted by COUNT(). When you add the or NULL part, the entire expression is still 1 for true but becomes NULL when the comparison returns false (because of the ternary logic definition used in SQL). So the COUNT() only count "trues".

Thats for standard SQL. In MySQL you can use a different trick. Because true and false are actually 1 and 0, you can just sum(t.Status = 'completed') to get the right number without using NULLs.


CREATE FUNCTION inside BEGIN TRY and END TRY I want to use CREATE FUNCTION in a block that will "swallow" errors, but this
BEGIN TRY

CREATE FUNCTION test (@ID int)
RETURNS int
AS
BEGIN
    RETURN(2 * @ID)
END

END TRY
BEGIN CATCH
END CATCH

results in

Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword
'FUNCTION'. Msg 137, Level 15, State 2, Line 7 Must declare the scalar
variable "@ID".

Why?

Many DDL statements must either start a batch or be the only statement in a batch.  So the general solution is to use dynamic SQL.  EG
I want to use CREATE FUNCTION in a block that will "swallow" errors, but this
BEGIN TRY

exec('
CREATE FUNCTION test (@ID int)
RETURNS int
AS
BEGIN
    RETURN(2 * @ID)
END
')

END TRY
BEGIN CATCH
END CATCH


MySql recommended hardware Our company servers are currently hosted on a VPS and we've decided to move into dedicated servers.

When choosing the best hardware for a DB server, what should we invest more resources:
better CPU (more cores)? or more RAM?

Where is the best ROI?

Any suggestions?

You should invest in both.

As for MySQL, if you are using InnoDB as your default storage engine, you must upgrade to MySQL 5.5 in order to use more cores. Here are some of my past posts on this subject

Multi cores and MySQL Performance
Possible to make MySQL use more than one core?
About single threaded versus multithreaded databases performance
16 Cores 12 GB RAM server MySql Configuration - my.cnf

As a point of reference, I have a client at my employer's web hosting company that has three DB Servers in Circular Replication. Each DB server has the following:

MySQL 5.5.9
192 GB RAM
162 GB InnoDB Buffer Pool
dual hexacore (that's right, 12 CPUs)
1.8TB Disk Volume
978 multitenant databases
892 GB of InnoDB Data
innodb_file_per_table enabled

They have had this setup for a whole year. I personally configured my.cnf for multicore engagement. All 12 CPUs are in use. The InnoDB Buffer Pool is full to capacity. There have been no complaints from the client on the DB Performance since this layout was setup for them.

Tuning InnoDB is very important because if you don't, MySQL 5.5 will perform worse than MySQL 5.1. In some cases, it can be worse than MySQL 4.1.

Query runs a long time in some newer MySQL versions
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

As for RAM, tuning connection usage, the number of connections and InnoDB caches is the most important thing

Cache MySQL database in memory
What are the main differences between InnoDB and MyISAM?
How do I properly perform a MySQL bake-off?


Select only the most recent record I've got a problem with creating a SQL query in order to get only the most recent record from multiple records. I've already checked different solutions like a subselect; unfortunately that does not work for me.
The query I've created so far puts out about 1300 records. What I need is only the newest one (by wfi.CREATEDDATETIME) of every InvoiceNumber. Here's the example data for ONE InvoiceNumber:

Company | UID  | Approver   | Due | InvoiceDate | InvoiceNumber | Vendor         | Created
S0009   | USR1 | Random Guy | ... | 20.10.2016  | 123456        | Random Vendor1 | 2016-10-30 10:00:22.000
S0009   | USR1 | Random Guy | ... | 20.10.2016  | 123456        | Random Vendor1 | 2016-10-30 10:00:22.000
S0009   | USR2 | Some Guy   | ... | 01.01.2000  | 123456        | Random Vendor1 | 2016-11-01 12:30:16.000
S0009   | USR2 | Some Guy   | ... | 01.01.2000  | 123456        | Random Vendor1 | 2016-11-01 12:30:16.000

The result I need is:

Company | UID  | Approver | Due | InvoiceDate | InvoiceNumber | Vendor         | Created
S0009   | USR2 | Some Guy | ... | 01.01.2000  | 123456        | Random Vendor1 | 2016-11-01 12:30:16.000

Query
SELECT
Upper(WFI.COMPANYID) as Company,
WFI.USERID as UID,
INF.NAME as Approver,
CONVERT(varchar(10),duedatetime,4) as Due,
right(left(Document,20),10) as InvoiceDate,
SUBSTRING(DOCUMENT,22,charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21))) as     InvoiceNumber,
Right(document,len(Document)-(20+charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21)))) as Vendor,
wfi.CREATEDDATETIME as Created

FROM
[TEST].[tst].[WORKFLOWWORKITEMTABLE] WFI
INNER JOIN [TEST].[tst].[Workflowtrackingstatustable] WFS ON WFI.CORRELATIONID=WFS.CORRELATIONID
INNER JOIN [TEST].[tst].[HCMWORKER] HCM on WFI.USERID=HCM.PERSONNELNUMBER
INNER JOIN [TEST].[tst].[DIRPERSONNAME] DPN ON DPN.PERSON=HCM.PERSON
INNER JOIN [TEST].[tst].[LEDGERJOURNALTABLE] LJT ON WFI.REFRECID = LJT.RECID
INNER JOIN [TEST].[tst].[USERINFO] INF ON WFI.USERID = INF.ID

WHERE
DATASOURCENAME Like 'Ledgerjourna%'
AND Datediff(day,Duedatetime,getdate())>3
AND WFS.DOCUMENTTYPE='Special'

ORDER BY
WFI.COMPANYID asc

These tables are standard workflow and employee tables from Dynamics AX 2012 on SQL Server Version 12.0.4100.
The createddatetime could be the same for multiple records. In this case I would like to select just one of them, it doesn't matter which one.

This might work.  Basically it's a ROW_NUMBER function that you will have to identify a key for (you Mentioned InvoiceNumber).  Once you do, it will return an ordered value where all your "duplicates" will be 2+.  Simply adding a where clause where ROWNUM =1 should get you the first record (ordered by the CreatedDate).

SELECT main.* FROM
(
SELECT
Upper(WFI.COMPANYID) as Company,
WFI.USERID as UID,
INF.NAME as Approver,
CONVERT(varchar(10),duedatetime,4) as Due,
right(left(Document,20),10) as InvoiceDate,
SUBSTRING(DOCUMENT,22,charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21))) as         InvoiceNumber,
Right(document,len(Document)-(20+charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21)))) as Vendor,
wfi.CREATEDDATETIME as Created,
--Added RowNumber Function Below
ROW_NUMBER() OVER (PARTITION BY **InsertYourKeyToAUniqueRecordHere** ORDER BY wfi.CREATEDDATETIME DESC) AS ROWNUM

FROM
[TEST].[tst].[WORKFLOWWORKITEMTABLE] WFI
INNER JOIN [TEST].[tst].[Workflowtrackingstatustable] WFS ON WFI.CORRELATIONID=WFS.CORRELATIONID
INNER JOIN [TEST].[tst].[HCMWORKER] HCM on WFI.USERID=HCM.PERSONNELNUMBER
INNER JOIN [TEST].[tst].[DIRPERSONNAME] DPN ON DPN.PERSON=HCM.PERSON
INNER JOIN [TEST].[tst].[LEDGERJOURNALTABLE] LJT ON WFI.REFRECID = LJT.RECID
INNER JOIN [TEST].[tst].[USERINFO] INF ON WFI.USERID = INF.ID

WHERE
DATASOURCENAME Like 'Ledgerjourna%'
AND Datediff(day,Duedatetime,getdate())>3
AND WFS.DOCUMENTTYPE='Special'

)main

WHERE main.ROWNUM =1 --Add this clause to only return the first record
ORDER BY main.Company asc

Feel free to comment out the WHERE main.ROWNUM=1 clause so that you can see your Rownum in action.

If you don't want your ROWNUM column to show in your final result set, then just replace the first SELECT * with the actual columns you want to select (using their aliases).


Finding fuctional dependencies and candidate keys I have number of files, each containing all reviews for a specific hotel (one file corresponds to one hotel, e.g. hotel_2194.dat). Each review is of the type:

<Author>PERKEISHA
<Content> I had a great time at...
<Date>Mar 8, 2008
<No. Reader>-1
<No. Helpful>-1
<Overall>1
<Value>1
<Rooms>1
<Location>5
<Cleanliness>5
<Check in / front desk>5
<Service>5
<Business service>1

I constructed the following relation:

Review (Hotel_ID, Author, Content, Date, No. Readers, No. Helpful, Overall, Value, Rooms, Location, Cleanliness, Check in, Service, Business service)

However, I am having trouble identifying the functional dependencies and candidate keys. I was thinking of something like (Hotel_ID, Author, Content, Date) for a primary key but then I realized that the same Author may post the same Review (in terms of contents) about the same Hotel on the same Date and just change his overall mark. Am I thinking too hard about this? I'd be grateful if someone could suggest some functional dependencies and keys.

Content looks like a free-form text field - that generally makes it a horrible choice for a primary key column. Hotel_ID, Author, and Date are probably reasonable (especially if Date is "date of stay at hotel", rather than "date of posting of review").

I would generally expect that an updated review would replace the original. If you want to retain older version, I would include both "date of stay" and "date of posting" (and both could be part of the key); this would allow the same person to have a review for each stay (as ratings for a stay last month could remain valid, even if a review of a later stay had different ratings).

It would be likely that you would want a Hotel table, and an Author table (to hold information about those entities that wouldn't change from review to review (hotel info, author's account info (assuming some sort of login process).

NOTE: In practice, I would probably want to treat Hotel_ID, Author, and the two date fields as a functional key (must be unique), but use an auto-incrementing integer as the actual, literal primary key (saves space on indexing and in any tables that might need to reference it, easier to use in queries, etc.)


SQL Server manage indexes Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated by SQL to determine what indexes could help performance. Then use another script based on stats to determine which indexes are over kill and can be removed.

We don't have a DBA my main goal is to try to manage based on stats and not black magic.  We just want to do the best we can with our resources (time)

Deciding which indexes you should use and or remove is a very involved process and is not something that can be simply automated.  There are plenty of ways to identify under used or missing indexes, but adding or removing indexes can have a wide variety of impacts and these impacts need to be reviewed.

In order to properly understand how indexes can affect query performance, I strongly recommend you read Grant Fritchey's SQL Server 2012 Query Performance Tuning book.  This is a valuable reference book for many DBAs and, while it is written for SQL 2012, much of the information is applicable to other versions of SQL Server.

Listed below are some tools you can use to identify indexes you can either add or remove.  Do not apply these recommendations without reviewing them first.

Microsoft provides the Database Engine Tuning Adviser to review your database and provide recommendations on actions that can improve your performance.  There are many factors the Tuning Adviser can not evaluate, so make sure you consider the suggestions individually before applying them.

You can also review missing index information from two places within SQL Server.  The first is within the query plans.  If you view a query plan, the green text at the top will typically recommend an index that can help your query.  This data is typically found in the second place, sys.dm_db_missing_index_details.  SQL Server tracks index usage stats and compiles recommendations that can be reviewed for possible addition to improve your performance.  The Tuning Adviser referenced above also makes use of this information.

Glenn Berry has a very helpful query as part of his DMV script that shows you indexes that aren't very helpful:

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]
  , i.name AS [Index Name]
  , i.index_id
  , user_updates AS [Total Writes]
  , user_seeks + user_scans + user_lookups AS [Total Reads]
  , user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
  ON s.[object_id] = i.[object_id]
  AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
  AND s.database_id = DB_ID()
  AND user_updates > (user_seeks + user_scans + user_lookups)
  AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

Please note!

Query tuning and index management is a very deep and involved topic.  It's not something you can simply turn on with a switch and allow an automated process to handle because of all the factors involved.  Many companies have DBAs dedicated solely to the task of managing this, using the tools listed above.  If you have no DBAs at your place of employment, I recommend either taking the time to train yourself in this OR seek a professional who has experience with this to aid you.


Postgresql - roles without linux accounts? Basic question:

what I'm reading describes how to create linux users in parallel with postgresql roles.

However, I don't see the value in creating any of these users in linux - I just want to create a postgresql role - and login to that from my application - or when running psql from the terminal using my own login - but in the role of the project.

e.g. - if the project is 'foo' - I want to have the psql -U foo, but no 'foo' user under linux.  After all - what's the point?  It's a role purely for psql - not for linux.

Is this possible in Postgresql - and if so - how do I do this?

I've trivially created a postgresql user role - without a linux account - but then I cannot ever -U role, as I get an error around peer authentication.

So - I assume I need to change how postgresql is handling permissions - and that's where I'm too green with postgresql to know what I'm doing there.

Perhaps a pointer to a clear little document discussing authentication modes or connection modes and setup for postgresql would help me resolve my issues?

You need to update your pg_hba.conf to allow for a non-peer authentication method.

It’s perfectly normal to create roles which don’t have OS accounts.


Difference in behaviour between `DBCC TRACEON` and `QUERYTRACEON` I am using SQL server 2014, but my database version is SQL server 2012. So I am trying to use trace flag 2312 to force the query optimizer to use cardinality estimator v120.

I have noticed through my own testing on a specific query:

With this trace flag active the query takes about 20 minutes, but in SSMS results come back all at once after 20 minutes
Without the trace flag active the query takes about 30 minutes, but the first few results come back instantly and continue to be added.

I have no issues with trace flags in SSMS, the trace flag seems to be properly set regardless of whether i set a global trace flag using DBCC traceon(2312, -1) or I add the option option (Querytraceon 2312).

The problem is when I try to run this query through my .NET application, if my query has the querytraceon option, then IDBCommand.ExecuteReader() seems to hang until the entire query completes, which seems like what I expect given my results in SMSS. However, if I globally enable the traceflag, but remove the querytraceon option from my query, then IDBCommand.ExecuteReader() finishes right away.

To me this seems like my query is not picking up the trace flag 2312, even though it is globally enabled for all sessions.

What could cause the discrepancy here?

I have added the xml execution plan for my plan with the trace flag enabled globally:
(broken into 2 parts due to size)

https://paste.city/98
https://paste.city/103

The plan for the trace flag enabled using querytraceon:
(broken into 2 parts due to size)

https://paste.city/100
https://paste.city/102

trace flag disabled:
https://paste.city/99

The query you were sending from the .NET application was still likely to be using the "old" plan (which used the legacy cardinality estimator).

When you enabled the trace flag globally, and then removed the QUERYTRACEON hint, the query text was different, resulting in a new plan being compiled for the query.  This newly compiled plan used the newer cardinality estimator, and thus met your expectations related to the plan shape, and having results return immediately.

Changing the query text in this case had the same effect as when you added OPTION (RECOMPILE) in SSMS while doing your testing.

Regarding your follow up question in the comments:

  although I am curious, why is it that we have to do this when a trace flag is enable/disabled?

The reason for this is that enabling trace flag 2312 globally does not clear the execution plan cache.  So for queries that you need to leverage the new CE, you'll need

to manually evict those plans from the cache one by one (with DBCC FREEPROCCACHE (plan_handle)),
temporarily add OPTION (RECOMPILE) to them, or
just clear the entire plan cache (if you need most queries to use the new CE, and your environment can handle this action).


Can I restore the defult SQL Server Public Role? I setup a new user login and attached it to a specific database on my server. I then opened up management studio on my local pc, connected to the database using the server IP and selecting the database and everything worked great!

Then I went messing with the default SQL server Public server role as I wanted only this one database to be shown to the user - not the full list of all our databases on the server. I removed the "connect" option from TSQL Default TCP/TSQL Default VIA/TSQL Local Machine/TSQL Named Pipes. This obviously broke what I had so I tried to re-add these settings but now every time I open the properties tab on the Public server role I see a warning message telling me:

Value does not fall within the expected range (SqlMgmt)

If I try to connect to my database on my local pc, it will connect but just see "System Databases - master and tempdb" and "Database Snapshots" which is empty. I don't see the actual database I mapped the login to.

Is there anyway to either restore the Public role to the default settings or a way to get my database to to actually display in management studio when I connect.

Thanks,
Rich

Well, this would be the script to restore the connection permissions to the defaults.

use [master]
GO
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [public] AS [sa]

If that doesn't work, try stopping SQL Server, starting it in single user mode ("sqlservr.exe -m" from an administrator command prompt), connecting with sqlcmd.exe (which will default to the local default instance with Windows authentication), and running the script that way.


How do I configure MySQL to accept a custom column in mysql.users? I am running MySQL Ver 14.14 Distrib 5.5.57 on Debian Jessie (x86_64).  In my mysql.users table, I have a custom column added for database owner id--which is for my server management system's use.  Somewhere along the way, MySQL started to complain when I try to modify a user's privileges:

ERROR 1547 (HY000): Column count of mysql.user is wrong. Expected 42, found 43. The table is probably corrupted

The first 42 columns are exactly as MySQL expects. The 43rd about which it is complaining is my added column.  How can I tell MySQL that it's "OK" for there to be 43 columns?

And yes, I have run the appropriate upgrade scripts with each upgrade to MySQL, including one extra time "just to make sure":

mysql_upgrade --force -uroot -p

Thanks in advance for any assistance!

YOU ABSOLUTELY, POSITIVELY, CANNOT DO THAT. Why ???
Back on Apr 12, 2012, I wrote the answer to Cannot GRANT privileges as root
I carefully explained how the grant columns existed from MySQL 4.x to 5.6. I have explained this many times (See my others).
The columns in the mysql.user table need to be in the exact position they are published along with the exact column names.
On Oct 10, 2014, I answered MySQL service stops after trying to grant privileges to a user where I gave an example of how you can hack repairs into mysql.user to comply with the next version.
I highly recommend you remove your custom column today !!! Otherwise, you will NEVER get all your grants back. Please go manage your database user identification in another database.


Active Directory Name Change Our Active directory will change the accounts name for example John Doe Jdoe@abcd.com to John.Doe@abcd.com.
My question is do I need to change all the SQL Logins individually or the person can login to SQL server management studio with their old names. What also happens if the login is an owner of a database or job.

SQL Server stores Active Directory accounts using a unique identifier generated by Active Directory.  The unique identifier is generated when the object is created in Active Directory, and never changes.
This means you can change things like the user name, first name, last name, and any other Active Directory attribute for the user, without the need to even touch SQL Server or SSMS in any way.
In other words, you can change their account name in AD, and not worry about SQL Server at all.


SQL Server self recursive horizontal join I need to generate a query which provides hierarchical data in a horizontal way. The levels of depth are variable, it is ok if there are many empty fields. The only rows that should exist should be just children. Here's an example of what I need:

ID   |Value  |ID_Parent
-----|-------|---------
1    |NULL   |NULL
2    |NULL   |1
3    |30     |1
4    |NULL   |2
5    |20     |4
6    |10     |NULL

So the output to this would be something like:

ID   |Value  |Ancestor_1 |Ancestor_2 |Ancestor_3
-----|-------|-----------|-----------|----------
3    |30     |1          |NULL       |NULL
5    |20     |1          |2          |4
6    |10     |NULL       |NULL       |NULL

It is needed that the ancestors are labeled 1-to-n as farest-to-nearest.
A tree view of the data would be this one:

1->/2->4->5
   \3
6

I've found a solution for fixed levels but I was wondering if this can be done in SQL Server.

I feel there are two approaches - left to right starting anywhere that has no parent, and right to left for anywhere that has a value. I'm going to go with the latter because it matches the rows to be produced.

You should start by using a recursive CTE to turn your data into:

RowVal, NodeVal, Value, Level
3, 3, 30, 0
3, 1, 30, 1
5, 5, 20, 0
5, 4, 20, 1
5, 2, 20, 2
5, 1, 20, 3
6, 6, 10, 0

There are plenty of examples for doing this...

WITH theTree AS (
    SELECT ID AS RowVal, ID AS NodeVal, Value, 0 AS Level, ID_Parent
    FROM theTable
    WHERE Value IS NOT NULL
    UNION ALL
    SELECT tr.RowVal, ta.ID AS NodeVal, tr.Value, Level + 1 AS Level, ta.ID_Parent
    FROM theTree AS tr
    JOIN theTable AS ta ON ta.ID = tr.ID_Parent
)
SELECT RowVal, NodeVal, Value, Level
FROM theTree
;

Now you need to adjust the Levels, because we started at the leaves.

MAX(Level) OVER (PARTITION BY RowVal) - Level AS NewLevel

Now it's simple dynamic pivot stuff, which you can find very easily. Group by RowVal, Value. A column per NewLevel. And MAX(NodeVal) in each "cell".


different log file for master and beta mysql Hi i am facing a problem while shipping my database log

I have three database server dev, beta and master
and i maintain log files for dev on github
and every log file has a version given to it like (001 next would be 002 and so on)
and then i would merge my code on github from dev branch to beta branch , clone the code on beta deploy server run this log files on beta while deploying its build(then while deploying it checks for last updated .sql file and start deploying from next file), test it and if all ok
then again
i would merge my code on github from beta branch to master branch, clone the code on master deploy server run this log files on master while deploying its build, test it and if all ok
that would be my final release
sample log file

          20140926.sql
          --add a stored procedure
          --update employee where id=10;
          --alter table .etc

but now i am facing a problem in this
i have a table called as employee
and on this i need to have different changes
like example

          20140926.sql
          --update employee set name="beta" where id=10;--this should go in beta database only BUT NOT IN MASTER
          --update employee set name="master" where id=10;--this should go in MASTER database only BUT NOT IN BETA
          --update sometable set something='somevalue' where nothing='nothing';--but his update is common for both BETA and MASTER branch

but if now i commit it on dev and merge to beta and deploy on beta this the changes which should go ONLY on MASTER are going on BETA
and this is totally screwing my whole exsisting logic

I see one solution to this situation is to have multiple commit of the same file.

let say 20140926.sql file  in this case, Now you have two changes one should go to Beta server and one to Master server.

You can have two seperate commit, changes that suppose to go to Beta in one commit and changes that is suppose to Master in other commit, with proper comment to indentify and versions incrementing count too can help.

Now while merge in Beta branch you can get the only version of commited for it and in Master the other corresponding version commited for it.


In MySQL, Is there a way to display all tables contained in a view I have many views in my database and I wanted to do some statistics about table usage (not dynamic statistics but static ones).

Like for this view:

CREATE VIEW V_LOWER_COST AS
    SELECT SUPPLIER_NUMBER, A.ITEM_NUMBER,UNIT_COST, SUPPLIER_COST
        FROM INVENTORY_LIST A, SUPPLIERS B
        WHERE A.ITEM_NUMBER = B.ITEM_NUMBER
        AND UNIT_COST > SUPPLIER_COST

... I would get something like this:

view-name       | tables
V_LOWER_COST    | INVENTORY_LIST, SUPPLIERS

and for the view:

CREATE VIEW SALESORDERTOP AS
    SELECT X.SALESORDERNUMBER, X.TOTALDUE, X.COMPANYNAME, C.UNIT_COST
    FROM V_LOWER_COST C
       INNER JOIN TABLEX X ON C.KEY = X.KEY
       LEFT JOIN TABLEY ON X.KEY = Y.KEY
    WHERE TOTALDUE > 10000.0
    AND Y.REGION = 'AU'

... I would get something like this:

view-name      | tables
SALESORDERTOP  | INVENTORY_LIST, SUPPLIERS, TABLEX, TABLEY

Note that the second view refers to the first one and the report will mention all implied table including the ones from the first view.

I tried many queries using the information_schema but was blocked at some point as not able to make a like which refers to other records.

Is there any existing command to find that easily otherwise what would be the best solution to do so?

Update: You can use sql parsers for this here is a demo page for the one I tested http://107.170.101.241:8080/getTableColumn/

Here are other useful links: [1]
[2]

I personally found this interesting so here is what I have tried so far:

Basic idea, table names are being preceded by Keywords like Left join , From ,etc (thus same logic can be repeated for other types of joins as well) so what I am trying to do here is locate the keyword and find there position using locate function then using that position (which I got as output) in a substring command after adding some extra sum to the location which is equal to no of digits in keyword eg: 4 for FROM which is in turn is skipping the keyword itself from occurring in results and resulting in the table name followed by some extra followup characters,which i think can be replaced.

Thoughts:The catch here will be to know the length of each table name and then passing it as the variable to the last parameter of the substring function and then iterating the below query for each view may be using a batch script which will yield only the table name in turn as a result.

Note : This is just an work in progress and still needs further improvements.

SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION)+4,15) as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION)+9,15)as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('left join',VIEW_DEFINITION)>0;

Another updated format:

    SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION)+4,15), ',', "\n|"),
 ".", "\n"), "(", "") as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION)+9,15), ',', "\n|"),
 ".", "\n"), "(", "")as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('left join',VIEW_DEFINITION)>0 ;

To also show where it came from for Eg: join,from etc

SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION),20), ',', "\n|"),
"from", "\n|from"), ".", "\n") , "left join", "\n|left join"), "(", "") ,"`","") as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION),20), ',', "\n|"),
"from", "\n|from"), ".", "\n") , "left join", "\n|left join"), "(", "") ,"`","")as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('left join',VIEW_DEFINITION)>0 ;

Please check it here to see what I mean for now the main purpose is finding the table names used by the view,grouping can be done as we move forward using a temporary table may be http://rextester.com/NXFM21955

Please use this link for live cooperation,testing and further improvement http://rextester.com/live/UFEV3010


Move expdp part file before job end I have an Oracle 11gr2 database running on Enterprise Linux 6.6 server.

I use the following command to split the expdp dump file to 10G size chunks:

expdp userid=/ DIRECTORY=dmpdir dumpfile=dump%u.dmp filesize=10G schemas=user1

this creates 4 files that I should move to another server (rsync then rm).

Can I move a file as soon as the expdp starts creating the following one? and how should I do that? I have idea like while (next_file not exists) wait.

Thank you.

It is not advisable to move files before the export job is finished. The reason is that when exporting, the utility "builds and maintains the master table for the duration of the job. At the end of an export job, the content of the master table is written to a file in the dump file set." (see Oracle's documentation at the following address: https://docs.oracle.com/database/121/SUTIL/GUID-6BDC1CC8-8596-402D-B016-602985B97AB6.htm#SUTIL814) If you move a dump file before the job is done, you risk corrupting that process and making the job fail.

Is there a reason you need to move the files before the data pump job is finished? If not, simply wait for the job to complete. While being able to move files as soon as they are not in use would be very useful for large export/import jobs, you will save yourself a lot of time by letting the export finish before manipulating the dump files.


Oracle SYS views missing from DB instance Hi i have a question but not sure what the problem was and how it happened though.

The initial error when i logged in the DB dev machine was ORA-00942: table or view does not exists. Some of the system views that we tried to access gave a ORA-017775: looping chain of synonyms.

Then we noticed some of the ALL_* and DBA_* views and functions were missing, which were causing alot of the dependencies and system packages to throw errors.

We also noticed that the SYSTEM tablespace has reached 100% utilized space.

So my question is, is it possible when the SYSTEM tablespace runs out of space, it would randomly drop the SYS views and functions

We also checked the alert logs, seems like it happened during a non-working period. Or is there any logs that i could further check on what happened that caused SYS views and functions being dropped suddenly ?

Regards,
Thanks

No, the database will not drop its own views.

But I have seen this many times, for example when developers thought naming their own tables as 'T$...' and views as 'V$...' was a good idea, then they wanted to clean up their schema, but they messed up all the V$ synonyms and views.

I would just start the database in upgrade mode and run the below scripts to rebuild these objects:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

Run Scripts to Build Data Dictionary Views


Multiple queries in merge into statement I have a scenario where i want to insert record if it doesn't exist in DB2.
If it already exists update is_active column to 0 of the existing row and insert the new row with is_active as 1.

I cannot use merge into as i cannot run 2 queries in when matched section.

How can i achieve this in batch.

If i were to run queries one by one i could have run them. But since there are millions of such records i want to do this in batch.

I want to do this using java prepared statement.

Using temporary table

create table test_tab (id int not null, is_active int not null) in userspace1;

with stream (id) as (values
--1, 2, 3
--2, 3, 4
3, 4, 5
)
, upd as (
select count(1) cnt
from new table(
  update test_tab t
  set is_active=0
  where is_active=1 and exists (select 1 from stream s where s.id=t.id)
)
)
select count(1) cnt
from new table(
  insert into test_tab(id, is_active)
  select id, 1
  from stream
);

select * from test_tab;

Assuming you accumulate your batch values into the streams table before each attempt to change the base table test_tab.
You can do all your operations using such a single statement as described above.
If you want to use some real table instead of streams, it's worth to collect statistics on it before every such a statement and test the performance using various number of records in the streams table.

Using batch inserts

create table test_tab2 (id int not null, is_active int not null default 1, id_int bigint not null generated always as identity) in userspace1;
create index test_tab2_idx on test_tab2(id);

create trigger test_tab2_air
after insert on test_tab2
referencing new as n
for each row
update test_tab2 t
set is_active=0
where t.is_active=1 and t.id=n.id and t.id_int<>n.id_int;

insert into test_tab2 (id) values
--1, 2, 3
--2, 3, 4
3, 4, 5
;

You can just insert new records in batches in your java program. The trigger deactivates the corresponding old records.


Is it possible to remote connect to SQL Server 2008 database with SQL Server Management Studio 2005? I have SQL Server Management Studio 2005 R2 in my administrator PC, and SQL Server Management Studio 2008 in the Server PC. Each time I tried to connect to the database server (SSMS 2008), it will throw errors that indicated I have different version of database or the software or else (db instance some said?).

I had search the internet. The most correct solution was to use Cumulative update package 5 for SQL Server 2005 Service Pack 2 to connect to 2008.

But my questions is, is there any other solution that doesn't need me to update or install any patch for the SSMS 2005 ? Maybe like tweaks or any other fast solution ? Any comments appreciated! Thanks!

If you are experiencing the problems that are documented as being fixed by installing that update, then there is unlikely to be another way to fix the problem while still keeping that version of the tool.

You don't say why you'd prefer not to install the update. Are you trying to keep yourself at that backwards level to stay specifically identical to a client installation or do you just not have admin rights on the machine so have to get someone else involved to apply the patch?

If you do have access to install software, another option might be to install SSMS 2016 which these days is distributed (for free) separately of the SQL Server engine and other parts and can be install side-by-side with older versions. SSMS 2016 can be obtained from https://msdn.microsoft.com/en-us/library/mt238290.aspx

  it will throw errors that indicated

When describing a problem or asking for help it is important not to skimp on details that you have which might give clues to people trying to help. As such you should provide the text of the error messages you have received, otherwise in this case we don't know if they are the sort of problem the update fixes or indicate another problem that needs a different fix. Help us to help you.


Migrating SQL Server databases We have a handful of SQL Server databases. Probably not relevant but just for info:

OS: Windows Web Server 2008 R2
RDBMS: SQL Server 2012 Express

My question is: When using SQL Server Management Studio to back up a database, is the resulting .bak file all one would need to fully restore the database on another machine?

I've been doing this for years in development environments, so to my mind the answer is yes, but we are migrating a production server so I wouldn't want to risk anything. Are there any log files, config files, [?], that might hinder performance/integrity/[?] if omitted from a restoration?

Basically yes, all you'd need is to backup and restore the databases. However, there's a bunch of stuff that won't do.

Server level settings (MAXDOP/CTFP, etc.)
Agent Jobs
Maintenance plans
Linked Servers
Users/Logins
Any other customization

It's kind of a pain in the butt to find all this stuff, and even if it's a one time move, scripting it all out can be a hassle. I'd recommend something like the free dba tools to help you with that.


SQL query or method to list all sql services running This might be a simple one, but i searched a lot and unable to find one.

Is there a sql query or a way i can run on registered server to find the list of all sql related services running like, ssis , ssas, ssrs, sql, agent, broswer, etc...

Tried googling out and can find sys.dm_server_services dmv but it does not show all the services running

Powershell would be simplest.

Get-Service "sql*"


mysqldump freezing on a specific table I dumped a database (sys_data)  which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376).
My dump command:

mysqldump -uxx -pxx -h192.168.1.xxx --single-transcation sys_data > /home/sys_data.sql

When the dump was blocked:

show processlist;
5306612 | root | 192.168.1.161:57180 | sys_data
   | Query  | 23955 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_trade_376`

On the other hand I can dump the table tb_trade_376 successfully if I just dump the table only.

mysqldump -uxx -pxx -h192.168.1.xxx \
  --single-transcation sys_data tb_trade_376 > /home/tb_trade_376.sql

This works well and quickly!
The table tb_trade_376 has about 700,000-800,000 rows.

What is the next step in investigating why I can't dump the whole database? How can I make it work?

Conjecture #1 : Single Transaction

The dump for the sys_data database has to create far more MVCC information to load and dump from ibdata1. Please Click Here to See the InnoDB Infrastructure Map and notice the section in ibdata1 that has 1023 undo logs.

When you were dumping an entire database, mysqldump will export the tables in alphabetical order. There can times that undo logs are populated with row data in the event of a crash and recovery as needed. Perhaps those logs are being loaded and unloaded from previous dumps. The undo space populated by the dump of previous tables (tables before tb_trade_376) may need some housecleaning performed while the dump of tb_trade_376 is in progress. This would be particularly true for a very busy server where INSERTs, UPDATEs, and DELETEs are being done to the previous tables.

Conjecture #2 : InnoDB Buffer Pool

If you are not writing anything to the database at all, another place to look would be the InnoDB Buffer Pool. Think about it: the data pages of every table being dumped would have to be loaded in the Buffer Pool simply because you are doing a SELECT. The SQL_NO_CACHE prevents the query results from entering the query cache, but does not prevent data movement in and out of the Buffer Pool. The data pages accessed from the previous tables have to be invalidated and overwritten in the Buffer Pool for each and every table prior to tb_trade_376.

Conjecture #3 : innodb_file_per_table

If innodb_file_per_table is disabled, then everything and its grandmother is in ibdata1. What would it contain? ibdata1 would have the following:

Data Pages for Every InnoDB Table
Index Pages for Every InnoDB Table
Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

This means that the Undo Logs are competing for space with the data and index pages of every table. This would cause ibdata1 to grow rapidly. If you have innodb_file_per_table disabled, you need to cleanup the InnoDB Infrastructure to keep InnoDB tables out of ibdata1.

Conjecture #4 : MyISAM Tables

How can MyISAM tables cause a problem? If any of the tables in the mysqldump are MyISAM and still receiving INSERTs, UPDATEs, and DELETEs during the database dump, it could possibly disable the checkpoint mechanism of the --single-transaction option midstream. This is plausible because MyISAM is a non-transactional storage engine. If any of the previous tables are MyISAM and are still receiving writes, all bets are off for all the tables in the dump to have the same point-in-time. Each table being dumped after a MyISAM table in encountered is basically on it own in a transactional sense.

SUMMARY

You may have one or more of these issues going on. Please compensate with one of the following:

Arrange to dump of each table into separate files
Setup MySQL Replication

You have freedom to tune MySQL on the Slave
You can stop the Slave and dump from the Slave. This will make point-in-time simple since a stopped slave means no more writes. YOu can start the slave once all dumps are done.

Deal with MyISAM

Convert all MyISAM tables to InnoDB. Prior to MySQL 5.6, a MyISAM table cannot be converted to InnoDB if there are FULLTEXT indexes.
If you cannot convert a MyISAM table, please stop writing to it during the dump

Do the ibdata1 cleanup
Use a larger buffer pool

Give it a Try !!!


Cannot register SPN, error 0x80090350, state 4 This is my first time trying to configure kerberos. I need it for SQL Server.

Considerations

I'm running SQL Server 2008 R2 on Windows 2008 R2 server.
I have an Active Directory account for SQL Server 2008 R2.
The account is delegated for kerberos.
The account is configured to start SQL Server services. This works without problems.
SPNs are registered for both netbios and FQDN for computername and also computername:1433.
The same SPNS are registered for the server and the account.

I mean, if I do

setspn -L domain\serviceaccount

or

setsqpn -Q MSSSQLSvc/Server.domain.local,

I get

MSSQLSvc/Server:1433

MSSQLSvc/Server.domain.local

MSSQLSvc/Server.domain.local:1433

When I run this query:

select auth_scheme
  from sys.dm_exec_connections
 where session_id=@@spid

...the result is NTLM.

This query I'm running from another server, connecting to the SQL Server that I need to use Kerberos on.

When I restart SQL Server services I see this error in the log:

  The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x80090350, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

Any idea what I'm missing?

The error message you're seeing, 0x80090350, is defined as:

c:\util\Err>err 0x80090350

# for hex 0x80090350 / decimal -2146892976 :
  SEC_E_DOWNGRADE_DETECTED                                        winerror.h
# The system detected a possible attempt to compromise
# security.  Please ensure that you can contact the server
# that authenticated you.
# 1 matches found for "0x80090350"

I'm looking this up with the Exchange Error Lookup Tool.

According to the information in this post, this error is often caused by the MaxTokenSize issue caused by an account (indirectly or directly) being a member of a large number of groups.

Another possibility I'd consider is that a duplicate SPN exists. You can determine which SPNs Windows thinks are duplicates by running setspn -X -F (info here).


MySQL workbench can't deal with MariaDB's new Invisible columns Recently upgraded to MariaDB 10.3 which has a new feature whereby we can create an invisible column like so:

ALTER TABLE MyTable ADD COLUMN `TestA` INT INVISIBLE;

But once I create this column, MySql Workbench will error on this table. Is there any way to workaround this? Or is there another GUI tool that can deal with this new column type?

MySQL Workbench currently supports MySQL version 5.6, 5.7 and 8.0 and has support for MySQL invisible columns indexes, (recursive) CTEs, JSON and all other (for a GUI relevant) features added to MySQL (like cached sha2 passwords).


Is it possible to have two copies of a table clustered in different arrangements seamlessly? I have a table with around 100M rows.  It only gets data inserted once/day but we need to do selects a lot.  The selects are usually simple but need to return 100s of thousands of rows sometimes.

It's unique based on three columns node_id, pricedate, hour which are integer, timestamp, integer respectively.  It was slow for most queries but I clustered it to node_id, pricedate and that fixed the slowness for most queries.  Those queries were of the type:

select * from mytable where node_id in (1,2,3,4)

We still occasionally need to do queries like:

select * from mytable where pricedate>='2016-05-01'

These are still slow because it's clustered by node_id first. We have an index on pricedate already. The issue is that the users often need enough data that the query engine throws out the index and uses a seq scan. Once it's using a seq scan, it benefits greatly from having the data clustered in the way that it is being queried.  This leads to the problem that I have where some queries benefit from one clustering and other queries from the other:

It would be nice if there was a way to have two physical copies of the table where one copy is clustered one way and the other is clustered another but user access to it appears as though there is only 1 table and the DB engine would ensure they're in sync. Obviously there'd be write penalties in doing this but that's inconsequential for our usage.

Would something like this be possible?

I'm guessing there isn't a built in way to do what I describe. To do it anyway I guess I'd make a table called mytable_dup with the same unique key constraint but with the alternate clustering and then setup triggers to insert to it whenever the master is inserted/updated/deleted.  That seems doable but from here, would there be a reasonable way to select from the duplicated table that will be efficient?

I'm running PostgreSQL 9.4 at home and 9.5 on Google.

To have the data held in two different physical sequences one must store the data twice. This can be achieved by defining a second, covering index. A covering index contains all of the columns required by a query. This way the optimiser has no need to refer to the base table to read further values and is unlikely to revert to a base table scan for the query plan. The optimiser performs an index-only scan. As the choice of index is made by the optimiser and not be the programmer no application code need be altered to take advantage during reads. No further objects are required to maintain consistency during writes.

The columns used in the WHERE clause will be the leading columns of the index. The sequence of the other columns is unimportant. As and when PostgreSQL supports the INCLUDE syntax this index can be changed to use it.

The downsides include a) extra disk to store this data b) additional latency during writes to maintain the index c) more system maintenance required for reorgs and such, and d) as queries change the covering index(es) have to change to match e) correspondingly larger and longer backups and restores.


MySql column without aggregates and group by clause Since I am coming from MSSQL world I am confused, what happening in queries where you have group by clause and other columns in select which do not go through aggregates functions.

I was shocked when MySql executed query like this

select * from myTable
group by columnThatIsNotPk

Or fiddle like this
http://sqlfiddle.com/#!9/b30bf5/1

My questions are:

Is there an explanation which data I am going to get for columns that not exists in group by clause?
Is it ok to use queries like that?
Do I lose or gain performances with queries like this?
Does MySql implements First and Last aggregates as it does JetEngine in ACCESS, and unfortunately missing feature in MSSQL engine?

For the question about aggregates:  See only_full_group_by, which was added in MySQL 5.7.5.  Before that, you got random values for the columns not based on the GROUP BY column(s).  After that, the statement is in error (unless you turn off that flag).

As for whether there is a performance gain?  Who cares, if you get garbage?  SELECT * is probably never faster (other than to type) than SELECT ... with specific columns.

As for FIRST, LAST, and other "Windowing functions", they are available in MariaDB 10.2 and MySQL 8.0 (but not yet ready for prime time).  There are workarounds, often using LIMIT 1.

Another thing to learn about if you are moving to MySQL:  Learn about AUTO_INCREMENT as a non-drop-in replacement SEQUENCEs.


You need to use \G as in SHOW CREATE TABLE options \G

EXAMPLE

mysql> show create table mysql.db \G
*************************** 1. row ***************************
       Table: db
Create Table: CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Create an XML Schema collection in SQL Server 2012? I want to create an XML schema collection to validate some XML in a column for the following XML:

DECLARE @xml as xml
SET @xml='

<R C="firstName" O="" N="Fred" />
    <R C="lastName" O="" N="Bloggs" />
    <R C="emailAddress" O="" N="FredBloggs@gmail.com" />
    <R C="phone" O="" N="" />
    <R C="mobile" O="" N="" />
    <R C="fax" O="" N="" />
    <R C="OrganisationId" O="" N="286" />

'

SELECT AuditDetail.Col.value('(@C)[1]','nvarchar(80)')    DatabaseColumnName,
       AuditDetail.Col.value('(@O)[1]','nvarchar(MAX)')   OldValue,
       AuditDetail.Col.value('(@N)[1]','nvarchar(MAX)')   NewValue
  FROM @xml.nodes('/R') AS [AuditDetail](Col)

The validation should be that
D is nvarchar(80)
O is nvarchar(MAX)
N is nvarchar(MAX)

Can someone show me what the schema collection script would look like?

The scheme could look like this.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="R">
        <xs:complexType>
            <xs:attribute name="C">
                <xs:simpleType>
                    <xs:restriction base="xs:string">
                        <xs:maxLength value="80"/>
                    </xs:restriction>
                </xs:simpleType>
            </xs:attribute>
            <xs:attribute name="O" type="xs:string"/>
            <xs:attribute name="N" type="xs:string"/>
        </xs:complexType>
    </xs:element>
</xs:schema>

It defines a single element R with the attributes C, O and N where C is restricted to 80 characters.

A valid XML document according to that schema would contain only one element R as the root node.

What you have here is a XML fragment and SQL Server can handle those just fine. Actually, the default behaviour for the XML datatype is that it will allow XML fragments.

Create the schema:

create xml schema collection dbo.R as
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="R">
        <xs:complexType>
            <xs:attribute name="C">
                <xs:simpleType>
                    <xs:restriction base="xs:string">
                        <xs:maxLength value="80"/>
                    </xs:restriction>
                </xs:simpleType>
            </xs:attribute>
            <xs:attribute name="O" type="xs:string"/>
            <xs:attribute name="N" type="xs:string"/>
        </xs:complexType>
    </xs:element>
</xs:schema>
';

Test:

declare @xml as xml(dbo.R);

set @xml='
  <R C="firstName" O="" N="Fred" />
  <R C="lastName" O="" N="Bloggs" />
  <R C="emailAddress" O="" N="FredBloggs@gmail.com" />
  <R C="phone" O="" N="" />
  <R C="mobile" O="" N="" />
  <R C="fax" O="" N="" />
  <R C="OrganisationId" O="" N="286" />
';

select @xml;

If you want to change the XML to only validate XML documents you need to specify document in the variable declaration.

declare @xml as xml(document dbo.R);


Different runtimes using parameter vs. constant A strange thing is happening to me during execution a specific query.

I have this query:

DECLARE @P0 as varchar(4000)
DECLARE @P1 as varchar(4000)

SET @P0 ='P0'
SET @P1 ='2015-02-21 00:00:00'

Select DISTINCT  top 10 USER_ID, MODULE_TYPE_CLASS, ADDRESS
From VIA_MESSAGE_AGGREGATOR ma
where state = @P0
and DATE_CREATED <= @P1

This take a long to execute (about 23 seconds).

When I substitute the @P0 for 'AA' value the same query is executed in 3 secs.

This is strange because I have an index with the same columns (state and date_created) and when I execute with parameters the index is not used, but when I execute with the 'AA' value instead of parameter then SQL Server uses the index.

Anyone have any idea why its behaves like this? I'm using JPA to execute this queries, the code is just an example of the same behavior.

Ok,

After further analysis and following the recommendation of  Aaron Bertrand and google "parameter sniffing" i found what is causing this its the SQL Server.

This is the "normal" behavior of the SQL Server, and the queries must be analysed and changed individually to resolve its problems.

Here is the link for the best description of what is happening to me:

Parameter Sniffing

Hope this help, as it help me.

Cheers,

Bigster


How to use MAX() to select a single record but prevent GROUP BY from selecting all distinct values and multiple records I have some Line items that have version numbers:

[Doc No]    [Version No]    [Line No]    [Data-a]    [Data-b]
1           1               1000         abc         abc
1           2               1000         xyz         abc
1           1               2000         null        lmnop
1           2               2000         ggg         lmnop

I want to only select the highest version number per [Line No]:

[Doc No]    [Version No]    [Line No]    [Data-a]    [Data-b]
1           2               1000         xyz         abc
1           2               2000         ggg        lmnop

I am doing this using this tsql:

SELECT [Doc No]
      ,max([Version No])
      ,[Line No]
      ,[Data-a]
      ,[Data-b]
FROM [table]
GROUP BY [Doc No], [Line No], [Data-a], [Data-b]

The problem obviously is ... if you notice in the first table the [Data-a] column has changing values (hence the need for version numbers.  So I want to see the latest version and the value contained in [Data-a] for that record. But since I am using max() to get the latest version, I have to put [Data-a] in the GROUP BY clause - which causes each two distinct values to be pulled and thus two records showing both version numbers. How can I solve this problem?

Just incase you need another solution. Below uses row_number() over (partition by .. order by)

CREATE TABLE dbo.docStore
    ([Doc No] int,
     [Version No] int,
     [Line No] int ,
     [Data-a] varchar(10),
     [Data-b] varchar(10))
;

INSERT INTO dbo.docStore
    ([Doc No],[Version No],[Line No],[Data-a],[Data-b])
VALUES
    (1,1,1000,'abc','abc'    ),
    (1,2,1000,'xyz','abc'    ),
    (1,1,2000,null,'lmnop'   ),
    (1,2,2000,'ggg','lmnop'  )
;

with cte as
(
select [Doc No], [Version No], [Line No], [Data-a], [Data-b],
        row_number() over (partition by [Doc No], [Line No]
                           order by [Version No] desc)
        as rn
from dbo.docStore
)
select [Doc No], [Version No], [Line No], [Data-a], [Data-b]
from cte
where rn = 1
;

-- clean up
drop table dbo.docStore
;


SSMA for Access - Migration Assistant Wizard fails at link tables When using Sql Server Migration Assistant for Access with the Migration Wizard, all goes well converting an Access (2016) database to SQL Server Express (2017) until I get to the Link Tables step.  Here the SQL Server connection dialogue appears:

I enter the same credentials used to create the tables.  My understanding is that this dialogue appears to allow for using a different sql server login with Access, so probably nothing out of the ordinary here.  But then I receive the following error:

Connection failed:
SQLState: '01000'
SQL Server Error: 772
[Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionOpen (SECDoClientHandshake()).
Connection failed:
SQLState: '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver]
[DBNETLIB]SSL Security error

I've tried with and without encrypting the connection with the same error, so it doesn't seem like an SSL error.

I have four SQL Server ODBC drivers installed:

ODBC Driver 11 for SQL Server
ODBC Driver 13 for SQL Server
SQL Server
SQL Server Native Client 11.0

If I create a connections using the ODBC Data Source Administrator, I can produce the same error using the SQL Server driver.  The other three work fine.  So it seems that SSMA is using the SQL Server driver (oldest of the four) instead of the SQL Server Native Client, which is supposedly a dependency of SSMA.

I was able to work around the problem by doing the following:

Allow the migration assistant to complete (with link converted tables failing)
Manually add MySQL linked tables to Access

Open Access, select External Data->New Data Source->From Other Sources->ODBC Database
Select Link to the data source by creating a linked table
Select your ODBC driver file or source
Enter credentials
Select tables you want to link

Delete Access tables and rename MySQL tables

From Tables list, right click first Access table and delete
Right-click corresponding MySQL table and rename to exactly match deleted Access table
Repeat for every table you want to re-map

Provided that you rename each MySQL table to match the deleted Access tables, all queries should work.


Hierarchical queries without CTEs I'm new to MySQL. The GetFamilyTree(id) function that Rolando suggested in the Find highest level of a hierarchical field: with vs without CTEs question was great but when the id is greater than 999 it hangs.

Is there a solution that works with larger ids?

There are several things you can do
THING #1
Expand all VARCHAR(1024) to VARCHAR(10240) in all Stored Procedures. This will accommodate longer Family Tree Listings. This is vital because queue_children can grow longer and longer if you have a lopsided tree structure. It also possible that when queue_children was 1024 in length and did not delimit properly, it probably went into some infinite loop trying to terminate properly and never reached the condition when the LENGTH(queue_children) would be 0.
THING #2
GROUP_CONCAT()'s maximum  length is limited by group_concat_max_len (default is 1024). Try expanding group_concat_max_len to something ridiculously large (5000000000).
THING #3
Increase your thread_stack, perhaps 512K or 1M. This is needed for Stored Procedures with either recursive operations or, in this case, longer local variables to make room for.


How to Dynamically change the database using TSQL I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL:

EXEC sys.sp_executesql N'USE db1 ' ;

It executes successfully however the database context of SSMS doesn't change.

I've tried a slight modification to the above like so

DECLARE @sql NVARCHAR(100) DECLARE @db NVARCHAR(50)
SET @db = N'db1' SET @sql = N'Use ' + @db
EXEC sp_executesql @sql

Again, it executes successfully, but the database doesn't change.

SSMS WILL NOT, I REPEAT, WILL NOT SWITCH TO THE CONTEXT OF A USE COMMAND YOU RUN IN DYNAMIC SQL.
If the ultimate goal is to execute some other dynamic SQL inside the chosen database, this is easy enough:
DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME();';

EXEC @exec @sql;

If you need to pass parameters, no problem:
DECLARE @db sysname = N'db1', @i int = 1;

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'SELECT DB_NAME(), @i;';

EXEC @exec @sql, N'@i int', @i;

If the goal is to execute some static SQL inside the chosen database, maybe you should consider storing that static SQL in a stored procedure in each database, and calling it dynamically like this:
DECLARE @db sysname = N'db1';

DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
        @sql  nvarchar(max) = N'EXEC dbo.procedurename;';

EXEC @exec @sql;

And hopefully the ultimate goal is not to run all this code in SSMS just so that SSMS is now in the context of @db... Daniel would really like it if I state explicitly that this is not possible, as @Lothar's comment also stated.


How to copy selected table data from sql server to mysql on a daily basis? I am having a retail pos application with MSSQL database. Now I need to transfer selected table data from SQL server to MYSQL database on a daily basis like job.

How to achive this?

You have a few options:

Set up a linked servers on SQL Server against the MySQL db, and then join the data as needed on the SQL Server side
Having set up the linked server, you can also copy the table over to a new location using standard SQL (or create a table in MySQL and copy data in).
You can also use Data Transformation Services in SQL Server to export into a csv or similar, to import in MySQL using a csv import tool.

Which one is best depends to some extent on size of the record set and how much of it you need. My preference would be to use DTS and go from there.

Source


SQL Server on Linux, an unmount error is output to the screen upon reboot In SQL Server on Linux (Ubuntu 16.04 + SQL Server 2017 RC2), the database is arranged on a different disk from the OS.
(I mounted /dev/sdb1 on /datadir.)

When restarting or Shutdown, the following message will be displayed at restart.

[FAILED] Failed unmouting /datadir

When restarting, I think that unmounting is done before the SQL Server process ends, but what kind of setting should be done to solve this problem?

Is there a setting like changing the order so that unmout is executed after SQL Server service termination or not unmounting the added disk when restarting?

The systemd unit file for the SQL server should be locally extended by a dependency on the /datadir this will make sure the start is done after the mount and the unmount waits for the stop. The most generic option to do this would be

RequiresMountsFor=/datadir

I think it automatically depend on the installation path of the scripts already.

Note that you do not need to modify the systemd unit files in the library directory but you can amend them in the /etc/systemd/system/<sqlserver>.service


How to filter results of a query I am using Postgis 2.4 with posgresql 10 in Windows 10.
I have the following SQL query:

WITH data (the_geom) as (
  SELECT a.gid,
          CASE WHEN ST_Within(a.geom,b.geom) THEN a.geom
               ELSE ST_Intersection(a.geom,b.geom)
          END AS the_geom
  FROM source.g100_wby_lakes_r as a
      JOIN extents.map_areas as b ON ST_Intersects(a.geom,b.geom)
  WHERE b.map_id='AA01'
)
INSERT into public.g100_wby_lakes_r (gid,geom)
select *
from data
where st_GeometryType(the_geom)='MULTIPOLYGON';

It tries to select the intersection between two multipolygons and only insert into a new table the intersected geometries which are also multipolygons (the intersection could also result in points or lines). All works except for the WHERE clause which gives the following error:

ERROR:  column reference "the_geom" is ambiguous
LINE 4: ...id,geom) select * from data where st_GeometryType(the_geom)=...
                                                             ^
SQL state: 42702
Character: 351

Why is the_geom ambiguous?
How can I change the SQL to make it work?
Thank you

Your CTE:

(
  SELECT a.gid,
         ... AS the_geom
  FROM ...
)

produces a table with 2 columns, named gid and the_geom. But the "wrap" has an obvious glitch, it only has one column:

WITH data (the_geom) AS

So it says that the table will be named data and the first column (re)named to the_geom. Now you have 2 columns named the_geom! You could have the same effect - but more clear to spot - if you had written:

WITH data (the_geom, the_geom) AS

The solution is simple. Either remove the column list from that part:

WITH data AS
  ( ...
  )

or (re)name both columns explicitly:

WITH data (gid, the_geom) AS           -- explicit
(
  SELECT a.gid,
         ... AS the_geom
  FROM ...
)
INSERT INTO public.g100_wby_lakes_r (gid,geom)
SELECT gid, the_geom
FROM data
WHERE st_GeometryType(the_geom) = 'MULTIPOLYGON' ;