Always Encrypted column validation How can I validate a SSN column that is encrypted in a table using AE without passing one by one value as a parameter? I have to validate if the SSN is formatted properly and numbers and '-' etc.

I tried inserting the SSN column values into a #table to perform the validations on the decrypted values but that fails.

This is being implemented in a stored procedure which does all validations on the encrypted table in bulk and fails for SSN. Any suggestions that can help me perform this task?

I understand that validation has to be done by app. The requirement is that a third party tool loads a file to the DB which has SSN in it and they don't do any validation for the file upfront before loading it into the table. We have encrypted the column on the table and then after we want to validate the ssn to check if it's 10 digits or not etc. How do we achieve this? It's not defeating the purpose as anyone who has the master key can perform required actions as per the need.

You are fighting against the technology that you're using. The point of Always Encrypted is that data is only decrypted on the client. A few quotes from the documentation:

  To successfully update the column [ed: as opposed to updating in
  T-SQL], do the following:

  SELECT the data out of the SSN column, and store it as a result set in the application. This will allow for the application (client
  driver) to decrypt the column.
      INSERT the data from the result set into SQL Server.

...

  Queries can perform equality comparison on columns encrypted using
  deterministic encryption, but no other operations (for example,
  greater/less than, pattern matching using the LIKE operator, or
  arithmetical operations).

...

  Always Encrypted is not supported for the columns with the below
  characteristics (for example, the Encrypted WITH clause cannot be used
  in CREATE TABLE/ALTER TABLE for a column, if any of the following
  conditions apply to the column):

  Columns referenced by computed columns (when the expression does    unsupported operations for Always Encrypted)
  Columns referenced by check constraints

Put more simply, you can't do things like pattern matching or inserting data from any column, even the non-encrypted ones, into a temp table. If you could find a way to do what you want within a stored procedure with no other involved tables then you've broken the security of Always Encrypted and should report that issue to Microsoft. Whoever is asking you to do this is asking for you to do something unreasonable. Do the validation on the client because that's where you have access to unencrypted data.

The below is a bad idea, and you should not do this.

Meeting your requirements is possible if your SSN column was encrypted using deterministic encryption. From the documentation:

  Use deterministic encryption for columns that will be used as search
  or grouping parameters, for example a government ID number.

This is important because it allows joins. The trick is to load all valid SSNs into a different table and to encrypt that column in the same way. You can then join the two tables to find any SSNS that aren't valid. Loading a billion valid SSNs into a table is pretty easy and takes about five minutes on my machine. You can reduce the result set further if you want to do more robust validation. The problem is applying the encryption to a billion row table can take a long time. To go through a proof of concept with just one million rows:

Here's one valid row and one invalid row loaded into the table that needs to be validated:

DROP TABLE IF EXISTS dbo.SSNS;

CREATE TABLE dbo.SSNS (
ID BIGINT NOT NULL,
SSN VARCHAR(25) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = SSNS__SSN,
        ENCRYPTION_TYPE = DETERMINISTIC ,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

GO
DECLARE @ssn VARCHAR(25) = '111-11-1111';
INSERT INTO dbo.SSNS VALUES (1, @ssn);
GO
DECLARE @ssn VARCHAR(25) = 'SEAN GALLARDY WAS HERE';
INSERT INTO dbo.SSNS VALUES (2, @ssn);
GO

Next populate the table of all allowed SSNs. In the code below I only insert 0.1% of the data.

DROP TABLE IF EXISTS #t_100;
DROP TABLE IF EXISTS #t_1000;
DROP TABLE IF EXISTS #t_10000;

SELECT TOP (100)
    RIGHT('0' + CAST(t.RN AS VARCHAR(10)), 2) NUM
INTO #t_100
FROM
(
    SELECT -1 + ROW_NUMBER()
        OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

-- only insert 0.1% of data as a proof of concept
SELECT '111' NUM
INTO #t_1000;

--SELECT TOP (1000)
--  RIGHT('00' + CAST(t.RN AS VARCHAR(10)), 3) NUM
--INTO #t_1000
--FROM
--(
--  SELECT -1 + ROW_NUMBER()
--      OVER (ORDER BY (SELECT NULL)) RN
--  FROM master..spt_values t1
--  CROSS JOIN master..spt_values t2
--) t;

SELECT TOP (10000)
    RIGHT('000' + CAST(t.RN AS VARCHAR(10)), 4) NUM
INTO #t_10000
FROM
(
    SELECT -1 + ROW_NUMBER()
        OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

DROP TABLE IF EXISTS dbo.ALL_VALID_SSNS_POC;

CREATE TABLE dbo.ALL_VALID_SSNS_POC (
SSN VARCHAR(25) COLLATE  Latin1_General_BIN2
);

-- about 5 minutes for all data, significantly faster for 1 million rows
INSERT INTO dbo.ALL_VALID_SSNS_POC WITH (TABLOCK)
SELECT
     t1000.NUM
        + '-' + t100.NUM
        + '-' + t10000.NUM AS SSN
FROM (SELECT TOP (1000) * FROM #t_1000) t1000
CROSS JOIN #t_100 t100
CROSS JOIN #t_10000 t10000
OPTION (MAXDOP 8, NO_PERFORMANCE_SPOOL, FORCE ORDER);

CREATE CLUSTERED INDEX CI ON dbo.ALL_VALID_SSNS_POC (SSN);

Now the data needs to be encrypted which is the problem. The process took about an hour and grew the transaction log by 250 GB before I killed it. Among other things, updating a billion rows like this will be expensive:

UPDATE [dbo].[ALL_VALID_SSNS] SET
[dbo].[ALL_VALID_SSNS].[tceGuidCol1] = NEWID()

It seems possible to do if you're willing to wait long enough and you have enough log space. Be sure to increase your timeout. I also had better results generating powershell code through the "Encrypt Columns..." SSMS database task and running the code that way.

Once the encryption is done you can get all of the rows with valid SSNs with a query like this:

SELECT s.*
FROM dbo.SSNS s
WHERE EXISTS (
    SELECT 1
    FROM dbo.ALL_VALID_SSNS_POC vs
    WHERE s.SSN = vs.SSN
);

and you can get all of the rows with invalid SSNs with a query like this:

SELECT s.*
FROM dbo.SSNS s
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ALL_VALID_SSNS_POC vs
    WHERE s.SSN = vs.SSN
);


Simplified/automated datafile free disk space reclaim On Oracle version 11g:

After Googling, I can't find a simple way to reclaim free space after deleting a table.

I've found a lot of explanations, telling how the datafile becomes fragmented, the big stack of boring queries you have to run in order to move the "empty space" at end of the datafile (table by table... even when you have 200 tables !?).

Then you have to reduce the datafile size by "guessing" by how much you can reduce it, or you must know exactly what is your "block size"... And finally you should not forget to "rebuild the indexes".

See for example : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899

and http://www.oracle-base.com/articles/misc/ReclaimingUnusedSpace.php

Is there a simple PL/SQL procedure which, given a tablespace name or datafile name, would to that job? Or any similar Oracle tool?

The short answer is No.  Unfortunately the way to do this in Oracle does require the "big stack of boring queries".   The articles you linked to are some of the best information available on the subject.  The datafile does indeed become fragmented, so that even if free space exists below the highest segment, Oracle will not automatically consolidate it when a RESIZE is done.

To "defragment" the tablespace you need to move these segments to the start of the datafile rather than at the end.  For tables this is an offline process meaning the table will be unavailable while the move is taking place.  Indexes can be moved either offline or with Enterprise Edition they can be moved online.  Since you have an outage window I recommend you follow these steps.

A. Shrink datafiles with free space beyond the high water mark.  This can be done as follows (the query is similar to Frosty Z's procedure):

SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
   ceil( blocks*(a.BlockSize)/1024/1024) -
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
   'alter database datafile '''|| file_name || ''' resize ' ||
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 )
   > 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;

B. After shrinking things above the high water mark, find out what tablespaces would still benefit from having segments moved.

SELECT DISTINCT tablespace_name FROM
(
    SELECT tablespace_name, block_id + blocks LastBlock,
       lead(block_id) OVER (PARTITION BY File_ID
          ORDER BY tablespace_name, file_id, block_id) NextBlock
       FROM dba_free_space
) WHERE LastBlock <> NextBlock AND NextBlock IS NOT NULL;

C. For each of these tablespaces determine which segments need to be moved. (Replace USERS with the name of your tablespace or join it with the previous query)

SELECT distinct de.segment_name
FROM dba_extents de
JOIN
(
   SELECT tablespace_name, file_id, MIN(block_id) LowestFreeBlock
   FROM dba_free_space
   WHERE tablespace_name = 'USERS'
  GROUP BY tablespace_name, file_id
) dfs ON dfs.tablespace_name = de.tablespace_name AND dfs.file_id = de.file_id
WHERE de.tablespace_name = 'USERS'
AND de.block_id > dfs.LowestFreeBlock;

D. Move each table and rebuild the indexes and statistics.

E. Repeat step A.

I just built most of these queries, so you will want to thoroughly test them before use.  I suppose you could create a procedure that would use EXECUTE IMMEDIATE to create the actual statements to run dynamically, but because queries will receive ORA-08103: Object no longer exists while the move is in progress, I think it is best to control that process manually even if it does mean a bit more time/effort.


How to turn off logging of "Software Usage Metrics enabled" and ESENT errors I am running SQL Server 2017 Standard CU 5 in a failover cluster with two nodes (single instance cluster). I have followed these two manuals to deactivate CEIP aka spyware features:

The CEIP service is disabled and stopped,
the CEIP role removed from the cluster
and all registry entries regarding CustomerFeedback and EnableErrorReporting are set to 0.

However I still get the message "Software Usage Metrics is enabled." in SQL Server error logs after a service restart. Additionally there are frequent errors from ESENT like the following in my windows event log:

There is a MS Knowledge Base article for this problem. However it is for SQL Server 2012. It says that this error is related to the "Software Usage Metrics feature". I already followed the advice to grant permissions for the SQL Server service account. However the error message keeps appearing and I don't want Microsoft to collect data anyway.

Here are my questions:

Is that the same feature as the CEIP stuff or something else?
How do I deactivate Software Usage Metrics correctly?

I'll point out that disabling SQLCEIP's service is not a supported configuration, so while you can disable sending information to Microsoft in the paid versions of SQL Server 2016+, the proper mechanism for doing this is documented here:

https://docs.microsoft.com/en-us/sql/sql-server/customer-experience-improvement-program-for-sql-server-data-tools?view=sql-server-2017

(This would leave the SQLCEIP service running and not emitting information to Microsoft).  Microsoft recommends you follow documented procedures to make sure that when you are engaged with customer support on an issue they can help fix that issue quickly.

As to your specific question about what this "extra" session is, that feature is not something that is emitting information out to Microsoft.  It is a feature that enables a tool that customers can use to determine how many copies of SQL they have running in their organization.  The data emitted from SQL goes into a file on the local Windows machine.  You can use the tool yourself to see what it does - it is called the Microsoft Assessment and Planning Toolkit, and you can download it here:

https://www.microsoft.com/en-us/download/details.aspx?id=7826

As for whether SQLCEIP is "spyware", I'll try to make sure that you have full information about what kinds of information is collected/not collected through the SQLCEIP mechanism.  Microsoft does not try to capture keystrokes, customer data, or otherwise use information we collect to market to customers at all (as you might find in a traditional definition of spyware such as https://simple.wikipedia.org/wiki/Spyware ).  The information collected through SQLCEIP is used to make sure that features are working well for customers both during the development cycle of a new version of SQL/new features and to make sure that we understand how our customers use the product so we can make it better.  We don't collect information to personally identify anyone and we don't want to know who you are or what you do individually.  We are interested in "how many people use Columnstore indexes?" and "how many rowgroups inside of a Columnstore index are optimally packed so that the customer is likely to get a good performance experience?".  The information collected help us make sure that our engineering teams have done a complete job at making each feature work for customers.

You can audit all the information collected by SQLCEIP to see this for yourself.  The instructions are here:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/local-audit-for-sql-server-usage-feedback-collection?view=sql-server-2017

Furthermore, we document our privacy policies about what kinds of information we collect/do not collect.  Microsoft recently re-vamped all parts of the company to use a single privacy statement, but SQL has a supplement which is aligned closely with its former separate privacy statement since we wanted it to be easy to read/understand and not get lost in the data collection policies for the other consumer services Microsoft provides.

Here's the top-level Microsoft privacy statement:

https://privacy.microsoft.com/en-US/privacystatement

Here's the privacy supplement for SQL Server:

https://docs.microsoft.com/en-us/sql/sql-server/sql-server-privacy?view=sql-server-2017

I hope that this answers your questions as to what the software usage metrics mechanism is and why it is not sending information to Microsoft/related to SQLCEIP.


AG (SQLServer 2014) - Suspend Data Movement I have a primary replica and one secondary. Multiple availability databases. Secondary replica is located at DR site.

For DR testing, is it possible to failover to the secondary replica, make (test) data changes on the secondary (now primary) replica and then failback but without moving back the test data?

I was thinking this may be possible through suspending data movement i.e.

Suspend data movement
Failover to secondary (DR server) replica
Insert test data
Failback to primary without synchronizing test data
Resume data movement

The short answer is No you cannot do that. First, if you suspend the data movement in one of the availability group database, you no longer able to failover it to your secondary node. Unless you make a forced failover. If you want to suspend the data movement in one of your databases, and also want to make the failover, you have to remove that specific database from the availability group and change the data. However, to add it back to the availability group, first you need to synchronize it back again.


The merge process could not connect to the Publisher ' '. Check to ensure that the server is running. ( I have 2 servers with 2 kinds of replications between them.
One is Transactional and the other is merge.

The Transactional replication is working fine but in the merge replication, I'm getting error messages.

Error messages:

  The merge process could not connect to the Publisher ' '. Check to
  ensure that the server is running. (Source: MSSQL_REPL, Error number:
  MSSQL_REPL-2147198719) Get help: http://help/MSSQL_REPL-2147198719 The
  process could not connect to Publisher ' '. (Source: MSSQL_REPL, Error
  number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084 Login
  failed. The login is from an untrusted domain and cannot be used with
  Windows authentication. (Source: MSSQLServer, Error number: 18452) Get
  help: http://help/18452

Two weeks ago everything was working fine (we didn't change anything and they didn't change as well).

I've read and check that the user is db_owner.
Telnet is open (and the transactional replication is working) so there are not any network problems.
I deleted and recreated both replications.

when I'm looking at the publisher server events log I see the error

  SSPI handshake failed with error code 0x80090301, state 14 while
  establishing a connection with integrated security; the connection has
  been closed. Reason: AcceptSecurityContext failed. The Windows error
  code indicates the cause of failure.

Any ideas?

The error message is stated plainly:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Try logging in to your Publisher as the replication agent account running on the subscriber (connecting from the subscriber through SSMS). There could have been changes to domain trusts, changes to domain membership, broken SPNs etc that could be causing this.

Validate your domains have a trust between them, your SPNs are configured correctly and that your replication accounts can actually log in to each instance. Also, double check that the accounts themselves are not disabled and\or have expired passwords.


Collate issues with wrong characters Well, the problem is well known, but I'm looking for a smarter solution if there's one.

For some reason the system doesn't recognize some characters and I can't compare the columns

Here is an example of the text:

Right

  ASPIRADOR ULTRASSONICO-LOCAÇAO (NOTA FISCAL SERVIÇO)

Wrong

  ASPIRADOR ULTRASSONICO-LOCA€AO (NOTA FISCAL SERVI€O)

Actually I'm fixing this through this function

create function fixcollation(@ps_Texto VARCHAR(4000)) returns VARCHAR(4000)

as

begin

    declare @vlgsv1itu INT declare @nxn68ezzi INT declare @dw17rsyva  VARCHAR(50) declare @iw8a2z01i VARCHAR(50) declare @t64e98xq6 VARCHAR(50) declare @zwjs2imy3 INT declare @jsyt85sy8 VARCHAR(4000)

    ----------------------------------------------------

    set @dw17rsyva = ' …Æƒ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãåâ™£—–éëêš‡€§'
    set @iw8a2z01i = 'áàãâäÁÀÃÂÄéèêëÈÉÊËíìïÍÌÏóòõôöÓÒÕÔÖúùûüÚÙÛÜçÇºØ'
    set @jsyt85sy8 = @ps_Texto set @zwjs2imy3 = IsNull(datalength(@ps_Texto), 0)
    set @nxn68ezzi = 1
    while(@nxn68ezzi <= IsNull(datalength( @ps_Texto), 0))

    begin

        set @vlgsv1itu = 1

        while(@vlgsv1itu <= IsNull(datalength(@dw17rsyva), 0))
        begin

            IF(ASCII(SUBSTRING(@ps_Texto, @nxn68ezzi, 1) COLLATE LATIN1_GENERAL_CS_AS) = ASCII(SUBSTRING(@dw17rsyva, @vlgsv1itu, 1) COLLATE LATIN1_GENERAL_CS_AS))
            BEGIN
                set @t64e98xq6 = SUBSTRING( @iw8a2z01i, @vlgsv1itu, 1) set @jsyt85sy8 = SUBSTRING(@jsyt85sy8, 1, @nxn68ezzi -1) + @t64e98xq6 + SUBSTRING(@jsyt85sy8, @nxn68ezzi + 1, @zwjs2imy3 -  @nxn68ezzi)
                break
            end
            set @vlgsv1itu = @vlgsv1itu + 1
        end
        set @nxn68ezzi = @nxn68ezzi + 1
    end
    return @jsyt85sy8
end

So, my question is: is this the best way or have I missed something here?

EDIT

Just a complementary test

select dbo.fixcollation(' …Æƒ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãåâ™£—–éëêš‡€§')
select dbo.FixCodePage850toCodePage1252(' …Æƒ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãåâ™£—–éëêš‡€§')

And this is the result in my production environment

fixcollation

FixCodePage850toCodePage1252

My personal thanks to Solomon Rutzky

This is an incorrect encoding issue. The characters are coming in encoded as DOS Code Page 850 yet the target Code Page you are using (based on the Latin1_General Collations) is Windows Code Page 1252. For example, in DOS Code Page 850, the Ç character has a value of 0x80 (or 128 in Decimal). However, that same value of 0x80 in Windows Code Page 1252 gives you €. Likewise, Ã in DOS Code Page 850 has a value of 0xC7 (or 199 in Decimal). However, that same value of 0xC7 in Windows Code Page 1252 gives you Ç.

The incorrect characters are incorrect due to being imported into SQL Server with the wrong encoding being specified for the source. This is not happening within SQL Server as that would be a code page conversion issue, in which case the same "character" would have its value translated for the same character in the target Code Page (if the character exists in the target Code Page, else you get ?). For example:

SELECT ASCII('Ç' COLLATE Latin1_General_CI_AS) AS [CP1252 Value],
       'Ç' COLLATE SQL_Latin1_General_CP850_CI_AS AS [CharacterInCP850],
       ASCII('Ç' COLLATE SQL_Latin1_General_CP850_CI_AS) AS [CP850 Value];

Returns:

CP1252 Value     CharacterInCP850     CP850 Value
199              Ç                    128

Meaning, this is happening most likely during a file import — BCP.exe, SQLCMD.exe, BULK INSERT, OPENROWSET(BULK...), custom app code that reads a file, etc — where either the wrong source Code Page is being specified, or no Code Page at all is being specified for the source. If an import is being done that specifies Code Page 1252 for this file, it will have the effect that you are seeing here since those bytes are encoded for Code Page 850, not Code Page 1252.

It should be noted that this could also happen with data coming in from app code if the driver (ODBC, etc) is being told to use the wrong code page.

Now, regarding the method of fixing this:

Ideally the method of importing the data would be updated / fixed to properly account for the actual code page with which the data has been encoded.
If it is not possible to fix the import process, then the function that the other company provided is not the best way to go. In fact, it is probably the slowest, most convoluted approach which is also prone to errors (if they didn't map all of the characters). There is no reason to do two loops along with SUBSTRING when loading the characters, in pairs, into a table variable would have allowed for a single loop using the REPLACE function. And using the ASCII function and a case-sensitive, accent-sensitive Collation is unnecessary and prone to error (if two characters match what is being searched for) when using a _BIN2 Collation would have been better.
Use the following function which does the conversion. First it gets the bytes of the current string, then it injects those bytes into a VARCHAR column that uses Code Page 850, then it selects that value from the table variable into a local variable (necessary anyway to return the value) which has the effect of converting the string into the Code Page used by the default Collation of the Database (which here would have to be Code Page 1252 else you would not be getting the "correct" string out of the function):

USE [tempdb];
GO
CREATE FUNCTION dbo.FixCodePage850toCodePage1252
(
    @CodePage850String VARCHAR(8000)
)
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Convert850to1252 TABLE
  (
    [String] VARCHAR(8000) COLLATE SQL_Latin1_General_CP850_CI_AS
  );
  DECLARE @ReturnValue VARCHAR(8000);

  INSERT INTO @Convert850to1252 ([String])
  VALUES (CONVERT(VARBINARY(8000), @CodePage850String, 0));

  SELECT @ReturnValue = [String] -- automatic conversion to Code Page of database
  FROM @Convert850to1252;

  RETURN @ReturnValue;
END;
GO

Testing both functions returns the same results:

SELECT dbo.fixcollation('Ç‰§ ULTRASSONICO-LOCA€AO (NOTA SERVI€O)');
-- Ãëº ULTRASSONICO-LOCAÇAO (NOTA SERVIÇO)

SELECT dbo.FixCodePage850toCodePage1252('Ç‰§ ULTRASSONICO-LOCA€AO (NOTA SERVI€O)');
-- Ãëº ULTRASSONICO-LOCAÇAO (NOTA SERVIÇO)

I came up with a test to check the mappings of all characters just in case the company providing the translation function missed any mappings. I filtered out the graphics characters and dotless "i" that are only found in Code Page 850.

USE [tempdb];
GO
;WITH nums AS
(
    SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [num]
    FROM   master.sys.columns
), vals AS
(
  SELECT nums.[num] AS [Value],
         CHAR(nums.[num]) AS [Character],
         dbo.fixcollation(CHAR(nums.[num])) AS [OldWay],
         dbo.FixCodePage850toCodePage1252(CHAR(nums.[num])) AS [NewWay]
  FROM   nums
)
SELECT vals.*,
       ASCII(vals.[NewWay]) AS [NewValue]
FROM   vals
WHERE  vals.[Character] <> vals.[NewWay] COLLATE Latin1_General_BIN2
AND    vals.[OldWay] <> vals.[NewWay] COLLATE Latin1_General_BIN2
AND    vals.[Value] NOT IN (176, 177, 178, 179, 180, 185, 186, 187, 188,
                            191, 192, 193, 194, 195, 196, 197, 200, 201,
                            202, 203, 204, 205, 206, 217, 218, 219, 220,
                            223, 254, 213); -- characters only in CP850

That returns a list of 52 characters that could have come through the import process mistranslated like the others, but skipped by the UDF that you were provided by that other company that only handles 46 of the apparently 98 possible characters.


Couldn't find the long running SQL under v$session_longops I have following SQL running from a long time. I couldn't see the status When I tried to look the time remaining for the query to complete. Can someone explain me why I couldn't find the SQL status

insert into SCOTT.EMP select * from SCOTT.EMP@dblink;

Query used:
SELECT USERNAME, sid, SERIAL#, OPNAME, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;

Thanks in advance!

The best view (for me) to see the status of a long query is V$SQL_MONITOR.
This view include all query that has consumed at least 5 seconds of CPU or I/O time.
This view is very volatile, so you have to answer it faster after it's execution.
If you can't find it into this view, you can find it in a awr report


MySQL in RHEL shows different versions I was given a RHEL 6.9 server with MySQL installed and I just wanted to check the MySQL version running on it.

When I tried the rpm -qa | grep mysql or mysql --version or SHOW VARIABLES LIKE "%version%"; commands they show the version as 5.6.36

However when I run yum info mysql-server it shows a different version (5.1.73) than the other commands.

What could be the reason for this.

The command rpm -qa shows what is installed. If it does not give what you look for then it is not installed with yum.


Load database from Solaris (Sybase 15.0.3) to VMWare Solaris (Sybase 15.7.0) I need to perform a load of a Sybase (15.0.3) Database from a Sun Solaris 5.10 environment   (on sparc architecture) to Sybase (15.7.0) on Solaris 10 (on i86pc architecture) running on my vmware virtual machine.

Does anyone have a recipe (correct steps) ?

Recently I had a similar problem …

Here goes all the required steps with comments … !

  [On Source]

  ============================================================================

  1) Checks on Source

  a.  Run (on solaris prompt) On source machine

  Execute:

uname -a

  In my case the output was:

SunOS clusterz1 5.10 Generic_142900-03 sun4u sparc SUNW,SPARC-Enterprise*

  b.  On source Sybase

  Execute:

select @@version
go

  In my case the output was:

Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS 5.8
/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009

  2) Checks on Target

  a.   Run (on solaris prompt) On target machine

  Execute:

uname –a

  In my case the output was:

SunOS s_wcli0070 5.10 Generic_142910-17 i86pc i386 i86pc

  b.  On Target Sybase

  Execute:

select @@version
go

  In my case the output was:

Adaptive Server Enterprise/15.7.0/EBF 19499 SMP /P/Solaris AMD64/OS 5.10
/ase157/2820/64-bit/FBO/Fri Sep 16 01:47:15 2011

  3)  Operations on Source

  a.   Set requested Database to “single”  user
  mode

  Execute:

sp_dboption Audit, "single", true go

  You should get something similar to:

Database option 'single user' turned ON for database 'Audit'.
Running CHECKPOINT on database 'Audit' for option 'single user' to take effect.
Execution time: 0,437 seconds

  b.  Position yourself on the requested database

  Execute:

use Audit
go

  You should get something similar to:

Execution time: 0,016 seconds

  c.  Flush Statistics

  Execute:

sp_flushstats
go

  You should get something similar to:

DBCC execution completed. If DBCC printed error messages, contact a
user with System Administrator (SA) role. DBCC execution completed. If
DBCC printed error messages, contact a user with System Administrator
(SA) role. Execution time: 0,094 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs109.htm

  d.  Lets write all dirty pages …

  Execute:

checkpoint
go

  You should get something similar to:

Execution time: 0,031 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands10.htm

  e.  Lets suspend updates on the requested database

quiesce database qaudit hold Audit
go

  You should get something similar to:

Execution time: 0,016 seconds

  f.  Lets resume updates on the requested database

  Execute:

quiesce database qaudit release
go

  You should get something similar to:

Execution time: 0,016 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands73.htm

  Note: If you don't do the commands in the right sequence you can get
  the following error when trying to load the database dump:

load database Audit from "compress::9::/dumps/PRODUCTION.Audit_zdump"
go

Adaptive Server cannot load this database because the database that
was dumped was not quiescent when the dump was performed. Run
sp_flushstats before DUMP DATABASE and ensure that the database is not
updated during the dump.

Sybase error code=3151 Severity Level=16, State=2, Transaction State=0
Line 1"

  g.  Lets perform the compressed dump of the requested database

  Execute:

dump database Audit to
"compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump"
go

  You should get something similar to:

Backup Server session id is:  39.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume
change request from the Backup Server. Backup Server: 4.132.1.1:
Attempting to open byte stream device:
'compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump::00' Backup
Server: 6.28.1.1: Dumpfile name 'Audit12139079A9  ' section number 1
mounted on byte stream
'compress::9::/DATADUMPS/daily/PRODUCTION.Audit_zdump::00' Backup
Server: 4.188.1.1: Database Audit: 3456 kilobytes (2%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 8918 kilobytes (9%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9090 kilobytes (18%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9348 kilobytes (26%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9520 kilobytes (34%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9690 kilobytes (43%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 9862 kilobytes (51%) DUMPED. Backup
Server: 4.188.1.1: Database Audit: 10032 kilobytes (60%) DUMPED.
Backup Server: 4.188.1.1: Database Audit: 10204 kilobytes (68%)
DUMPED. Backup Server: 4.188.1.1: Database Audit: 13422 kilobytes
(76%) DUMPED. Backup Server: 4.188.1.1: Database Audit: 13594
kilobytes (85%) DUMPED. Backup Server: 4.188.1.1: Database Audit:
13764 kilobytes (93%) DUMPED. Backup Server: 4.188.1.1: Database
Audit: 13916 kilobytes (100%) DUMPED. Backup Server: 3.43.1.1: Dump
phase number 1 completed. Backup Server: 3.43.1.1: Dump phase number 2
completed. Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database Audit: 13932 kilobytes (100%)
DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database Audit).
Execution time: 12,078 seconds

  For more details check the following URL:

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands54.htm

  h.  Lets take out the single user mode on the requested database …

  Execute:

use master
go

sp_dboption Audit, "single", false
go

  You should get something similar to:

Database option 'single user' turned OFF for database 'Audit'. Running
CHECKPOINT on database 'Audit' for option 'single user' to take
effect. Execution time: 0,015 seconds Execution time: 0,047 seconds

  [On Target]

  4)  Operations on target

  a.  Load compressed dump

  Execute (on Sybase):

load database Audit from "compress::9::/dumps/PRODUCTION.Audit_zdump"
go

  You should get something similar to:

Backup Server session id is: 10. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume
change request from the Backup Server. Backup Server: 4.132.1.1:
Attempting to open byte stream device:
'compress::9::/dumps/PRODUCTION.Audit_zdump::000' Backup Server:
6.28.1.1: Dumpfile name 'Audit12139081CC  ' section number 1 mounted on byte stream 'compress::9::/dumps/PRODUCTION.Audit_zdump::000'
Backup Server: 4.188.1.1: Database Audit: 8836 kilobytes (1%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 44038 kilobytes (8%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 87050 kilobytes (17%)LOADED.
Backup Server: 4.188.1.1: Database Audit: 130058 kilobytes (25%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 173070 kilobytes (33%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 216590 kilobytes (42%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 259602 kilobytes (50%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 303122 kilobytes (59%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 346134 kilobytes (67%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 386582 kilobytes (75%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 429594 kilobytes (83%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 473114 kilobytes (92%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 512028 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database Audit: 512036 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database Audit).
Started cross-platform conversion for database Audit.
Started cross-platform conversion for system objects.
Cross-platform conversion for database Audit: 1136 pages completed.
Completed cross-platform conversion for system objects.
Started cross-platform conversion for user objects.
Cross-platform conversion for database Audit: 10049 pages completed.
Cross-platform conversion for database Audit: 20249 pages completed.
Cross-platform conversion for database Audit: 30449 pages completed.
Cross-platform conversion for database Audit: 40649 pages completed.
Cross-platform conversion for database Audit: 50849 pages completed.
Cross-platform conversion for database Audit: 61049 pages completed.
Cross-platform conversion for database Audit: 71249 pages completed.
Cross-platform conversion for database Audit: 81449 pages completed.
Cross-platform conversion for database Audit: 91649 pages completed.
Cross-platform conversion for database Audit: 101849 pages completed.
Cross-platform conversion for database Audit: 112049 pages completed.
Cross-platform conversion for database Audit: 122249 pages completed.
Cross-platform conversion for database Audit: 132449 pages completed.
Cross-platform conversion for database Audit: 142649 pages completed.
Cross-platform conversion for database Audit: 152849 pages completed.
Cross-platform conversion for database Audit: 163049 pages completed.
Cross-platform conversion for database Audit: 173249 pages completed.
Cross-platform conversion for database Audit: 183449 pages completed.
Cross-platform conversion for database Audit: 193649 pages completed.
Cross-platform conversion for database Audit: 203849 pages completed.
Cross-platform conversion for database Audit: 214049 pages completed.
Cross-platform conversion for database Audit: 224249 pages completed.
Cross-platform conversion for database Audit: 234449 pages completed.
Cross-platform conversion for database Audit: 244649 pages completed.
Cross-platform conversion for database Audit: 254849 pages completed.
Completed cross-platform  conversion for user objects.
Started cross-platform conversion for log records.
Cross-platform conversion for database Audit: 35 pages completed.
Completed cross-platform conversion for log records.
Completed cross-platform conversion for database Audit.
Started estimating recovery log boundaries for database 'Audit'.
Database 'Audit', checkpoint=(179295, 3), first=(179295, 3), last=(179295, 6).
Completed estimating recovery log boundaries for database 'Audit'.
Started ANALYSIS pass for database 'Audit'.
Completed ANALYSIS pass for database 'Audit'.
Started REDO pass for database 'Audit'.
The total number of log records to process is 4.
Redo pass of recovery has processed 1 committed and 0 aborted transactions.
Completed REDO pass for database 'Audit'.
Use the ONLINE DATABASE command to bring this database online;
ASE will not bring it online automatically.
Execution time: 57,64 seconds

  b.  Just put the requested database online

  Execute (on Sybase):

online database Audit go

  You should get something similar to:

Started estimating recovery log boundaries for database 'Audit'.
Database 'Audit', checkpoint=(179295, 3), first=(179295, 3), last=(179295, 6).
Completed estimating recovery log boundaries for database 'Audit'.
Started ANALYSIS pass for database 'Audit'.
Completed ANALYSIS pass for database 'Audit'.
Recovery of database 'Audit' will undo incomplete nested top actions.
Database 'Audit' appears to be at an older version '15030' than the present installation at version '15700'; ASE will assess it, and upgrade it as required.
Database 'Audit': beginning upgrade step [ID  1564]: executing SQL statement ("declare @dbid int select @dbid = db...")
Database 'Audit': beginning upgrade step [ID     2]: validate basic system type data
Database 'Audit': beginning upgrade step [ID     6]: alter table (table syspartitions)
Database 'Audit': beginning upgrade step [ID     9]: executing SQL statement ("if exists (select 1 from syscolumns...")
Database 'Audit': beginning upgrade step [ID    10]: alter table (table sysobjects)
Database 'Audit': beginning upgrade step [ID    11]: alter table (table syscolumns)
Database 'Audit': beginning upgrade step [ID    69]: executing SQL statement ("if 2 = (select count(name) from sys...")
Database 'Audit': beginning upgrade step [ID    27]: alter table (table syscomments)
Database 'Audit': beginning upgrade step [ID    80]: alter table (table sysattributes)
Database 'Audit': beginning upgrade step [ID    91]: executing SQL statement ("update systypes set length=@@maxpag...")
Database 'Audit': beginning upgrade step [ID   245]: Update system roles in system tables.
Database 'Audit': beginning upgrade step [ID   246]: clear unused columns (table sysindexes)
Database 'Audit': beginning upgrade step [ID   248]: update system catalog protection data
Database 'Audit': beginning upgrade step [ID  1105]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1132]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1299]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1400]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1502]: executing SQL statement ("if exists (select 1 from sysobjects...")
Database 'Audit': beginning upgrade step [ID  1503]: executing SQL statement ("create view sysquerymetrics (uid, g...")
Database 'Audit': beginning upgrade step [ID    73]: executing SQL statement ("update sysobjects set sysstat = sys...")
Database 'Audit': beginning upgrade step [ID  1550]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1702]: noting the present database upgrade level
Database 'Audit': beginning upgrade step [ID  1719]: executing SQL statement ("update sysattributes set object_inf...")
Database 'Audit': beginning upgrade step [ID  1720]: noting the present database upgrade level
Upgrade has finished in database 'Audit'. ASE is now updating table level statistics for its system tables. Please be patient.
Checking table 'sysstatistics' (object ID 24): Logical page size is 2048 bytes.

Checking partition 'sysstatistics_24' (partition ID 24) of table 'sysstatistics'. The logical page size of this table is 2048 bytes.

The indexes for 'sysstatistics' are already correct.  They will not be rebuilt.
Checking table 'sysobjects' (object ID 1): Logical page size is 2048 bytes.

Checking partition 'sysobjects_1' (partition ID 1) of table 'sysobjects'. The logical page size of this table is 2048 bytes.

The indexes for 'sysobjects' are already correct.  They will not be rebuilt.
Checking table 'sysindexes' (object ID 2): Logical page size is 2048 bytes.

Checking partition 'sysindexes_2' (partition ID 2) of table 'sysindexes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysindexes' are already correct.  They will not be rebuilt.
Checking table 'syscolumns' (object ID 3): Logical page size is 2048 bytes.

Checking partition 'syscolumns_3' (partition ID 3) of table 'syscolumns'. The logical page size of this table is 2048 bytes.

The indexes for 'syscolumns' are already correct.  They will not be rebuilt.
Checking table 'systypes' (object ID 4): Logical page size is 2048 bytes.

Checking partition 'systypes_4' (partition ID 4) of table 'systypes'. The logical page size of this table is 2048 bytes.

The indexes for 'systypes' are already correct.  They will not be rebuilt.
Checking table 'sysprocedures' (object ID 5): Logical page size is 2048 bytes.

Checking partition 'sysprocedures_5' (partition ID 5) of table 'sysprocedures'. The logical page size of this table is 2048 bytes.

The indexes for 'sysprocedures' are already correct.  They will not be rebuilt.
Checking table 'syscomments' (object ID 6): Logical page size is 2048 bytes.

Checking partition 'syscomments_6' (partition ID 6) of table 'syscomments'. The logical page size of this table is 2048 bytes.

The indexes for 'syscomments' are already correct.  They will not be rebuilt.
Checking table 'syssegments' (object ID 7): Logical page size is 2048 bytes.

Checking partition 'syssegments_7' (partition ID 7) of table 'syssegments'. The logical page size of this table is 2048 bytes.

The table 'syssegments' has no indexes.
Checking table 'syslogs' (object ID 8): Logical page size is 2048 bytes.

Checking partition 'syslogs_8' (partition ID 8) of table 'syslogs'. The logical page size of this table is 2048 bytes.

*** NOTICE: Space used on the log segment is 401 pages (0.78 MB), 0.39%.
*** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
*** NOTICE: Space free on the log segment is 101999 pages (199.22 MB), 99.61%.
The table 'syslogs' has no indexes.
Checking table 'sysprotects' (object ID 9): Logical page size is 2048 bytes.

Checking partition 'sysprotects_9' (partition ID 9) of table 'sysprotects'. The logical page size of this table is 2048 bytes.

The indexes for 'sysprotects' are already correct.  They will not be rebuilt.
Checking table 'sysusers' (object ID 10): Logical page size is 2048 bytes.

Checking partition 'sysusers_10' (partition ID 10) of table 'sysusers'. The logical page size of this table is 2048 bytes.

The indexes for 'sysusers' are already correct.  They will not be rebuilt.
Checking table 'sysalternates' (object ID 11): Logical page size is 2048 bytes.

Checking partition 'sysalternates_11' (partition ID 11) of table 'sysalternates'. The logical page size of this table is 2048 bytes.

The indexes for 'sysalternates' are already correct.  They will not be rebuilt.
Checking table 'sysdepends' (object ID 12): Logical page size is 2048 bytes.

Checking partition 'sysdepends_12' (partition ID 12) of table 'sysdepends'. The logical page size of this table is 2048 bytes.

The indexes for 'sysdepends' are already correct.  They will not be rebuilt.
Checking table 'syskeys' (object ID 13): Logical page size is 2048 bytes.

Checking partition 'syskeys_13' (partition ID 13) of table 'syskeys'. The logical page size of this table is 2048 bytes.

The indexes for 'syskeys' are already correct.  They will not be rebuilt.
Checking table 'sysgams' (object ID 14): Logical page size is 2048 bytes.
The total number of data pages in this table is 8.
The indexes for 'sysgams' are already correct.  They will not be rebuilt.
Checking table 'sysusermessages' (object ID 15): Logical page size is 2048 bytes.

Checking partition 'csysusermessages_15' (partition ID 15) of table 'sysusermessages'. The logical page size of this table is 2048 bytes.

The indexes for 'sysusermessages' are already correct.  They will not be rebuilt.
Checking table 'sysreferences' (object ID 16): Logical page size is 2048 bytes.

Checking partition 'sysreferences_16' (partition ID 16) of table 'sysreferences'. The logical page size of this table is 2048 bytes.

The indexes for 'sysreferences' are already correct.  They will not be rebuilt.
Checking table 'sysconstraints' (object ID 17): Logical page size is 2048 bytes.

Checking partition 'sysconstraints_17' (partition ID 17) of table 'sysconstraints'. The logical page size of this table is 2048 bytes.

The indexes for 'sysconstraints' are already correct.  They will not be rebuilt.
Checking table 'systhresholds' (object ID 18): Logical page size is 2048 bytes.

Checking partition 'systhresholds_18' (partition ID 18) of table 'systhresholds'. The logical page size of this table is 2048 bytes.

The table 'systhresholds' has no indexes.
Checking table 'sysroles' (object ID 19): Logical page size is 2048 bytes.

Checking partition 'sysroles_19' (partition ID 19) of table 'sysroles'. The logical page size of this table is 2048 bytes.

The indexes for 'sysroles' are already correct.  They will not be rebuilt.
Checking table 'sysattributes' (object ID 21): Logical page size is 2048 bytes.

Checking partition 'sysattributes_21' (partition ID 21) of table 'sysattributes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysattributes' are already correct.  They will not be rebuilt.
Checking table 'sysslices' (object ID 22): Logical page size is 2048 bytes.

Checking partition 'csysslices_22' (partition ID 22) of table 'sysslices'. The logical page size of this table is 2048 bytes.

The indexes for 'sysslices' are already correct.  They will not be rebuilt.
Checking table 'systabstats' (object ID 23): Logical page size is 2048 bytes.

Checking partition 'systabstats_23' (partition ID 23) of table 'systabstats'. The logical page size of this table is 2048 bytes.

The indexes for 'systabstats' are already correct.  They will not be rebuilt.
Checking table 'sysstatistics' (object ID 24): Logical page size is 2048 bytes.

Checking partition 'sysstatistics_24' (partition ID 24) of table 'sysstatistics'. The logical page size of this table is 2048 bytes.

The indexes for 'sysstatistics' are already correct.  They will not be rebuilt.
Checking table 'sysxtypes' (object ID 25): Logical page size is 2048 bytes.

Checking partition 'sysxtypes_25' (partition ID 25) of table 'sysxtypes'. The logical page size of this table is 2048 bytes.

The indexes for 'sysxtypes' are already correct.  They will not be rebuilt.
Checking table 'sysjars' (object ID 26): Logical page size is 2048 bytes.

Checking partition 'sysjars_26' (partition ID 26) of table 'sysjars'. The logical page size of this table is 2048 bytes.

The indexes for 'sysjars' are already correct.  They will not be rebuilt.
Checking table 'sysqueryplans' (object ID 27): Logical page size is 2048 bytes.

Checking partition 'sysqueryplans_27' (partition ID 27) of table 'sysqueryplans'. The logical page size of this table is 2048 bytes.

The indexes for 'sysqueryplans' are already correct.  They will not be rebuilt.
Checking table 'syspartitions' (object ID 28): Logical page size is 2048 bytes.

Checking partition 'syspartitions_28' (partition ID 28) of table 'syspartitions'. The logical page size of this table is 2048 bytes.

The indexes for 'syspartitions' are already correct.  They will not be rebuilt.
Checking table 'syspartitionkeys' (object ID 29): Logical page size is 2048 bytes.

Checking partition 'syspartitionkeys_29' (partition ID 29) of table 'syspartitionkeys'. The logical page size of this table is 2048 bytes.

The indexes for 'syspartitionkeys' are already correct.  They will not be rebuilt.
Checking table 'sysencryptkeys' (object ID 98): Logical page size is 2048 bytes.

Checking partition 'sysencryptkeys_98' (partition ID 98) of table 'sysencryptkeys'. The logical page size of this table is 2048 bytes.

The indexes for 'sysencryptkeys' are already correct.  They will not be rebuilt.
WARNING: One or more indexes on user tables may have been marked as 'suspect' making these indexes unusable. Use the sp_post_xpload stored procedure to check and rebuild these indexes.
Database 'Audit' is now online.
Execution time: 6,516 seconds

  */


Something like, if your acctno has empty strings you will need to elaborate this query a bit
create table #t (name varchar(20), acctno varchar(30))

insert #t values ('Tim', '000')
insert #t values ('Jim', '00750')
insert #t values ('Bim', '0000000')

select * from #t where replace(acctno, '0','') = ''


Large set of execution plans hangs up SSMS I have a stored procedure that takes about 3-5 seconds that I'm trying to understand so I want an execution plan for it.  When I run it in SQL Server Management Studio with Execution Plan enabled, it takes over 15 minutes and then, I get a tab that says "Execution Plan" but the tab is empty. Any further attempts to run any SQL at all (even select * from foo) no longer works.  I have a broken SQL server management studio app, I must shut it down and try again. I've done it three times and wasted 45 minutes, and I am now ready to learn about some alternatives.

First is there a reliable command line way to generate an SQLPLAN file for a particular sql script, maybe from the command line, and then I could investigate using some other tool than SSMS to generate my plans.

Secondly, if there isn't a built-in reliable command-line way to generate and store an SQL execution plan as text or XML, then I'd like to know if there exists some other third party tools that would do a good job on very large SQLPlans, in particular not choking and dying when the GUI-drawing parts of it get overwhelmed.

What do you do when SSMS won't generate and show you an execution plan? I'm using SQL 2008 R2 Standard and its included SSMS version, and I don't have any extra plugins.

Update I'm invoking a stored procedure which creates a cursor, which does subqueries, and does great evil, in a loop, generating in excess of 10K individual subquery statements.  It looks like I really need to refactor this down to generate less of a storm of output.

Update2 It really looks like server-side-tracing (to zero in on problem areas) and then a return to profiling then a return to query plans  might be required. I am zoomed in far too deep in a big picture, and I need to zoom the heck out a bit.

Have you looked at SentryOne Plan Explorer? It can handle humongous plans that Management Studio chokes on. You can also generate both actual and estimated plans from within the tool, so you don't even have to bother with SSMS.


Manipulate duplicated records In a table ( in Microsoft Access), I have a large number of records that have duplicated field, for example field-1. I have to change the table and set field-1 as primary key. But as you know, it is not possible. So I want to change the value of field-1 for duplicated data. For example, now I have a table like this:

field-1  | field-2
1        | f1
1        | f2
2        | f3
2        | f4

And want to change to this:

field-1  | field-2
1        | f1
201      | f2
2        | f3
202      | f4

Note:

Maximum number of duplicated record is 2.

How I can do that?

This is kind of complicated, but you could try this.

Create a duplicate table to store the duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 2

Create a nonDuplicate table to store the non duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 1

Create a duplicateValue table to store the modified values of the duplicates. You will need to modify the case statement below to work with Access, before using this query in an insert statement. Here are some links that should help you.

How to add row number in Access

Access case statements

--Modify the 2nd instance of the duplicate field
--If the row number is Odd, then the value is the same
--If the row number is Even, then 200 is added to the value
SELECT
  --This is how you would do it with SQL Server
  --You will need to figure out how to modify this code to work with Access
  CASE WHEN ROW_NUMBER() OVER (ORDER BY field-1 ASC) % 2 <> 0
  THEN field-1 ELSE field-1 + 200 END AS 'field-1'
  --THE ABOVE CASE STATEMENT NEEDS TO BE MODIFIED FOR ACCESS
  ,field-2
FROM yourtable
    JOIN duplicateTable ON yourtable.field-1 = duplicateTable.field-1

Create a nonDuplicateValue table to store the non duplicate values. Use this query in your insert statement:

--Get the values of field 1 and field 2
--from non duplicate rows
SELECT field-1,field-2
FROM yourtable
    JOIN nonDuplicateTable ON yourtable.field-1 = nonDuplicateTable.field-1

Query to union the results together and to use for the insert into the final table

SELECT field-1,field-2
FROM duplicateValues

UNION

SELECT field-1,field-2
FROM nonDuplicateValues


set global variable parameter? My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time.
I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need to set the variables into the my.cnf, but my prod_server is critical can't take my server restart is there any way we set variables dynamically for certain period of time?

Thank u in advance please help.

You can set any global variables to any value on the fly using below statement in MySQL prompt:

set global <variable> = <value>;

In your case use the following to turn on slow query log:

 set global slow_query_log=1;

By default the "slow wait time" is set to 10 sec, but you can change it as well.

Hope it helps.


Count number of occurrences of a phrase in ntext I am trying to create a function for counting the number of occurrences of a phrase in a ntext value.

This my function's code:

alter FUNCTION [dbo].[PhraseCounter] ( @pInput NTEXT, @pSearchPhrase NVARCHAR(100) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = DataLength(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
    IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchPhrase
        SET @vCount = @vCount + 1

    SET @vIndex = @vIndex + 1
END

RETURN @vCount

END

But it can't find anything in the input string.

Where is the problem in my function?

ALTER FUNCTION [dbo].[PhraseCounter] ( @pInput NTEXT, @pSearchPhrase NVARCHAR(100) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT
DECLARE @vSerchLength        INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = DataLength(@pInput)
SET @vSerchLength  = LEN(@pSearchPhrase)

WHILE @vIndex <= @vInputLength
BEGIN

    IF SUBSTRING(@pInput, @vIndex, @vSerchLength) = @pSearchPhrase
    BEGIN
        SET @vIndex = @vIndex + @vSerchLength
        SET @vCount = @vCount + 1
    END
    ELSE
    SET @vIndex = @vIndex + 1
END

RETURN @vCount

END
GO

If you do not need NTEXT, try:

DECLARE
    @s1 NVARCHAR(MAX) = 'aaaabbbssss',
    @s2 NVARCHAR(MAX) = 'a'
SELECT (LEN(@s1) - LEN(REPLACE(@s1,@s2,'')))/LEN(@S2)


Oracle RMAN Back-up files in red hat can be read in windows? We have performed a cold back-up of a database using Oracle RMAN. The back-up ran on a Red Hat Server and then the files were copied to a mounted external hard-drive. Can this back-up files then be read in a windows PC or there will be some file system compatibility issue? When restoring the back-up with RMAN, does the hdd needs to be mounted in a linux server or it can be any server?

RMAN can be used for cross-platform tasks but you cannot restore your classic full backup from Linux to Windows


Read Committed Snapshot Isolation blocking during table modification This is using SQL server 2014 SP2 (it was true in SP1 as well).

The database is configured to have Snapshot Isolation on and Is Read Committed Snapshot Isolation is set to true. The processes accessing the database do not set their isolation level, so they are defaulting to read committed.

I have a process that is modifying a specific table by deleting values (80,000 or so on average) from it and then inserting new ones, in a transaction.

I have an application that reads this same table, and only this table, in the database in Read Committed Snapshot Isolation.

The read is blocked with a LCK_M_IS. The Activity monitor shows that it is blocking on Object ID 1171535257 which is this table. The lock in the table is exclusive (Lock Mode X).

There is no foreign key on the table to explain the lock.

Can I prevent the Read process from issuing the LCK_M_IS or otherwise blocking, and have it read the committed data instead?

In a previous version of the question, an index was suspected as the culprit. Further testing has shown that even without any indexes this behavior is observed.

Are you doing online rebuilds of this index at the same time as the transaction?
I have RCSI block if there is an index rebuild simultaneous with a large or long running transaction.
Paul Randal has a great explanation here: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-830-unicorns-rainbows-and-online-index-operations/


Change postgres default behavior for connection to database when dbname not supplied According to postgres documentation:

https://www.postgresql.org/docs/10/libpq-connect.html

  dbname
  The database name. Defaults to be the same as the user name. In certain contexts, the value is checked for extended formats; see Section 33.1.1 for more details on those.

for instance, issuing the following command:

psql -U username -h 0.0.0.0 -c "SELECT * from some.table"

will automatically issue the command on a database named 'username' if that database exists.

Is there a way to change the default behavior to require an explicit database name rather than the default behavior?

According to the same Postgres documentation

The following environment variables can be used to select default connection parameter values, which will be used by PQconnectdb, PQsetdbLogin and PQsetdb if no value is directly specified by the calling code[...]

PGDATABASE behaves the same as the dbname connection parameter.

If you want psql to fail if the database name is not explicitly given on the command line, set the variable PGDATABASE to point to a non-existent database, e.g. export PGDATABASE=" " or export PGDATABASE="".
As to your question:

whether or not the default behavior can be modified to not connect to a database named after the user if no database name is supplied either through an environment variable, or the psql command line utility.

we can find a definitive answer in the libpq source code:
/*
 * If database name was not given, default it to equal user name
 */
if (conn->dbName == NULL || conn->dbName[0] == '\0')
{
    if (conn->dbName)
        free(conn->dbName);
    conn->dbName = strdup(conn->pguser);
    if (!conn->dbName)
        goto oom_error;
}

The only way to modify the default behaviour is to edit the code fragment shown above and recompile the library.


How to enable NULL values in MySQL timestamp fields? I need to restore backup from one MariaDB server (version 10.1.23) to another MariaDB server (version 10.1.37). But this version has a much stricter mode by default and does not allow NULL values in timestamp fields and throws the below error when I restore the backup.

  1067 - Invalid default value for 'dt'

I try to add this values to *.cnf file in /etc/mysql/conf.d/

[mysqld]

sql_mode=ALLOW_INVALID_DATES,IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=OFF

Queries:

show variables like 'sql_mode';
show variables like 'explicit_defaults_for_timestamp';

show changed values of variables but it does not work. Why?

ALTER TABLE table_name
  MODIFY dt TIMESTAMP NULL;

This will alter the table with name table_name to set the column dt to nullable.


How to store a date without the year component I have to store climate (temperature) data for a few cities. This would mean that each city will have 365 values  (ex: The value for Jan 1, is the average temperature for that city for all Jan 1 for 50 years (1955-2004).

Hence I am not at all concerned with year. How do I store the date in a date column? Do I store Jan 1 as Jan 1 2009 ( a non-leap year)? Or is there any better way out?

One idea:

Use "day of year", but offset

March 1st is 1
Feb 28th is 365
Feb 29th is 366


What is the right time to use an ENUM versus a normalized types table in MySQL? Possible Duplicate:
  Advantages and Disadvantages to using ENUM vs Integer types?

Considering MySQL only...

I'm curious to know what the community thinks the proper use-case for an ENUM type vs. a FK to a types table.

I've read this question: Advantages and Disadvantages to using ENUM vs Integer types?, however the use-cases aren't really covered well enough for me.

When using ENUM to have a range of values, you have to properly plan what values you will use. Once those values are put in place, it can be rather challenging to perform any kind of simple maintenance should you want to redefine the ENUM range.

While there are bait-and-switch tricks you can perform to extend a range for an ENUM, those same tricks become very cumbersome (maybe even impossible) if you want to do other things such as reducing or altering an ENUM range.

Using foreign keys makes a great deal of sense when you know that the types you are establishing can be extended, reduced, or altered. Just by adding new entries into a table with the types needed, keys can be passed around to other tables without worrying about the underlying value being properly represented in the foreign tables. In addition, you would not be worried about those values matching up exactly with the base table definition of the type. Such concerrns would definitely apply if using ENUM.

Foreign Key Constraints would server to protect a row from absorbing invalid types. Hence, this would not be suitable for ENUMs because the constraint checking would have to be done in your application rather than in the database.

Here is what should be considered about ENUM usage:

ENUMs work best when

the type you are representing is local to the table only
it must represent a type that is never changed
it will never likely experience redefinition
its cardinality needs to be low
its portability must always be based on logcial dumps of the table, never physical. Otherwise, bait-and-switch methods of maintenance would have to accompany the use of the ENUM.
you can allow for NULL (Example : game_rating ENUM('EC','E','E10+','T','M','A') NULL This allows a new game to be entered into a table without a default rating)

CAVEAT

OK game_rating may be a bad example since ESRB could create more game ratings. In that case, a FK would be better but the base table would have to have 'unknown' as a default type and define game_rating as NOT NULL.


Error connecting to 10.0.17-MariaDB-1~trusty I'm having problem connecting remotely to my MariaDB server.

I've tried more or less everything, and can open a socket with nc(nc myserver 3307) but with the mysql (mariadb) client I only get the following error:

mysql -h myserver -P 3307 -u user -pPassword
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 54

I have looked all over, but can't find the cause of this error.

My server is running Ubuntu 14.04.2 LTS (Trusty Tahr) and I installed mariaDB with:

apt-get install mariadb-server-10.0

Any suggestions or ideas? Local connections (both over socket and ip works fine).

I started getting this "Lost connection to MySQL server at 'reading authorization packet'", and after reading https://bugs.mysql.com/bug.php?id=28359 I found out that the client machine (debian 64bits) network interface was in 100Mbps (it is a 1Gb interface and was working in 1Gb, not sure why it fell back to 100Mbps).

I could "normal ping" and nc, without problems, but a "ping -f -s 10000" from the client to the server would show 1% lost packets.

I disconnected its RJ45, connected it back "pumping" the connector back and forth (it's an old cable/machine, maybe some oxidation had settled in), and changed the switch port, and it auto-negotiated back to 1Gbps.

Then the "ping -f -s 10000" ran ok, and the mysql errors stopped.

So you might check for the connection between client/server. The "normal ping" (small packets may be ok) and nc (may be getting retransmissions, transparent to you) may be working, but a "ping -f -s 10000" can show how it responds under more load. It seems the "Server greeting" phase of the MySql auth does not like dropped packets.

Best regards,


SQL Job Step timeout If a SQL Agent job step executing a stored procedure on a linked server times out,  does the agent kill the SPID on the linked server or does it continue to run on the linked server?

Whenever a client disconnects from SQL Server, the database engine rolls-back any open transactions prior to the spid terminating.

This applies to all clients, even a "client" Agent Job running via a linked server.

If a stored procedure running via a linked server times out, the connection to the target server will disconnect, causing all open transactions in that session to roll back.

This answer shows many of the ways an open transaction can be rolled back.


How to determine queries per hour executed by a particular MySQL user? I am trying to implement user based quota system in MySQL to limit MAX_QUERIES_PER_HOUR  for a particular user. I have an option to set this limit via MAX_UPDATES_PER_HOUR 10.
But my problem is that I want know that how many queries a user has executed this hour.  I followed this thread but this is for global statistics of dba.  I want this information for every user of my database. I am sure that MySQL is storing this schema somewhere but after a lot of scrutinizing some of databases like information_schema , performance_schema , mysql I can't figure out how it will be achieved.

I talked to the MySQL engineers, and the philosophy of the metrics you will find on performance_schema is to store only global metrics (even if it can also store individual queries and events), as in, no temporal (differential) breakdown. The way they suggested would be to take hourly snapshots of the interesting metrics (query or user statistics) and then store those somewhere else. Because performance_schema stores metrics on memory since restart, you would only have to sample those every hour, or average them over the SHOW GLOBAL STATUS like 'Uptime' seconds.

I normally use performance_schema using sys, as it is way easier to query (but all data there comes from performance_schema). You can install it even if you are using an older MySQL version and it will use the performance_schema data already stored, assuming it is enabled (default).

sys.user_sumary contains the summary status per user, including total statements, total_latency, total_connections, etc.
sys.user_summary_by_statement_type contains the breakdown per user and statement type.

You could enable individual query<->user storage, but you would convert
performance_schema into the general log, and either storing only a small window, or -if increased the backlog- taking a huge amount of memory.

So to "know that how many queries a user has executed this hour" query the above tables, wait 1 hour, and query it again, and substract the first value from the second. You will have available, the amount of queries, inserts, updates, deletes, rows read, and other resources used. There are tools that will automate the collection for you. If for some reason you cannot use performance_schema, there are plugins such as user_statistics that will create new tables in information_schema with similar information (this was the only way on older MySQL versions).


Migration query taking time I'm doing a migration from Oracle to SQL Server and, for one of the tables where we have 3 million records, it is taking a lot of time to complete.

When I check the activity monitor, I can see the a wait type of PREEMPTIVE_COM_SEQSTRMREAD. Please help me understand what could be the issue.

I'm using linked server to connect Oracle from SQL Server.

To find out more about the PREEMPTIVE_COM_SEQSTRMREAD wait type I would read this great article at sqlauthority, but as Shanky said in the comments, SSIS would more than likely serve you better for a large migration.


SQL Login keeps losing access to databases We created a SQL Login account for reporting use only (SSRS), we'll call it 'MyReportAcct'.

We've used this account for some time and it is created for every newly installed instance.  It has been working as you'd expect, except on a couple of mirrored instances.  I've not seen this issue on any standalone instances in my environment.

Randomly, reports will stop working and there is nothing being changed based on our internal processes for Change Management, Windows Logs, and SQL Error logs.  Here are two of the error messages we receive:

Upon investigation of the account, it shows up under the Security object folder in SSMS.  Also, we find that the account is associated to all the databases under User Mapping for the account properties.  I've verified that 'MyReportAcct' has public and db_datareader, so on the surface every looks as expected.

The rub comes when I try to alter the account or remove the mappings.  I'm met with:

Cannot alter the user because it does not exists or you do not have
permission (Error 15151).
Cannot drop the user dbo.. (Error 15150)

What I've had to do to fix the issue is delete every instance of 'MyReportAcct' on the server, then recreate it, then add to each database.  This doesn't happen all the time, but often enough that it is irritating.  Further, we shouldn't have to do this at all.

I think that we should change this account to an Windows Account instead of a local SQL Login in every instance.  I have a feeling that this might fix the issue, but I need to prove why or provide a reasonable answer.  I think part of the problem is the mirroring in some way, but I don't know how to prove that either.

Security is a weakness of mine and I'm employing steps to change this, so any insight you can provide would be greatly appreciated.  Please let me know if you need more information.

Short answer:

The SIDs for the ‘MyReportAcct’ SQL login are different on each server instance.

Long answer:

The master database houses the security database for each instance of SQL Server (think Active Directory for a database server).  In this scenario we have two different master databases, since it is a mirrored pair.  Each time you create a SQL Login, there is a different SID created along with the account, this is no different than how Active Directory has always worked.

When a mirrored database fails over, the SID in the database matches the SID stored in the master database of the principal server (SERVER1), not the mirrored server (SERVER2).  This natural, and default process of failover causes the SQL Login (MyReportAcct) to become an orphaned account on the principal server (SERVER1).  This orphanhood process happens EVERY time there is a failover.  The start of authority for SQL Logins are the instance that they were created (SERVER1).  The failover to the mirrored server (SERVER2) is now a different start of authority, so the SIDs will be different, hence why the reports fail.

Solution:

Create the SQL Login ‘MyReportAcct’ with the same SID every time.
This process requires using TSQL code in the query analyzer by
someone who is a sysadmin.
Create an Active Directory service account named ‘uic\MyReportAcct’.
This way, the SID is managed by AD and we never have to worry about
the aforementioned issue again.


Linked Server already exists in sys.server I created Linked Server in ABC server and I can test the connection successfully to Destination server XYZ (Which is part of AOAG).
I created a Linked server from ABC --> XYZ .
I used AOAG Listener (AOAGListener) as server name while creating Linked server from ABC --> XYZ.

I am facing issue :

  Could not find server 'AOAGListener' in sys.servers.Verify that the
  correct server name was specified.If necessary,execute the stored
  procedure sp_addlinkedserver to add server to sys.servers.

Please help as I can see server name in sys.servers

Issue was resolved.

Actually it was for some ETL process for which I need to access a server which is not in AOAG and a server which is part of AOAG.I have added the listener only in one remote server as Linked server but not AOAG servers.

  I have to add Listener as Linked server in each AOAG servers(Replicas) and the remote server which is a stand-alone
  to make the ETL process work.

Hope this helps someone.


Can I trim values on COPY from csv? I used csvsql to generate CREATE statements, and it looks like it assumed trimmed fields though the data has some spaces padding the field:

ERROR:  value too long for type character varying(11)
CONTEXT:  COPY example, line 1314, column state: " South Korea"

I'm not entirely sure why the data has padding -- it definitely shouldn't but about half the values in the state column have some padding around them.

I could use ALTER to make more space in the field, and then UPDATE example SET state=trim(state); after I COPY the csv in, I'm wondering if there's a clean way to just trim the padding as I import the data?

Possibly the --no-constraints option to csvsql may solve that problem simply.

Otherwise COPY has a PROGRAM clause that can pre-process the data. It receives the lines it on its standard input and must output the filtered result on its standard output.

https://www.postgresql.org/docs/current/static/sql-copy.html

  PROGRAM

  A command to execute. In COPY FROM, the input is read from standard output of the command, and in COPY TO, the output is written
  to the standard input of the command.

When called from psql with \copy tablename FROM PROGRAM ... the program refers to a client-side command to launch by psql. When COPY is used, it's a server-side command run by the backend.

But the trimming itself is non-trivial. For instance, see How to manipulate a CSV file with sed or awk? and Trim leading and trailing spaces from a string in awk.


How to migrate from MySQL to MariaDB or Percona with minimum downtime? I want to migrate my MySQL 5.1 database to one of these databases: MariaDB, PerconaDB or MySQL 5.6

Is there any way to do this operation with no downtime or with minimum downtime possible? How?

The database is 40GB size, has does hundreds of writes and reads / minute. There are 10 servers connecting to it and it is a sing db server right now.

In order to perform an upgrade with (almost) no downtime, you need to add some extra infrastructure for high availability. The most common way to perform that is with the help of MySQL built-in replication:

Setup a slave with a clone of the data of the original master. It is technically possible to setup a master-slave replication within the same node by creating a new separate instance on the same hardware. However, I would recommend -in a general case- to set it up on a separate node if possible. This is specially true if the server is very busy.
Put your slave down and perform the upgrade process offline, without stress (the master, your original mysql, is serving your application). Then you upgrade that to MySQL/Percona/MariaDB.
Restart your slave an prepare for switchover from the master to the slave (there are several ways to do that, but if it is not a stable thing, it can be done in places like the connector/driver configuration of your application). There are many things to do on this phase, like waiting for slave lag to become 0, validation testing and warming the slave's buffer pool, etc.
Set your master as read-only and wait for all changes to be applied to the slave server (if you have done enough preparations, this should be almost immediately). This is the only part that produces non-availability on the server. This is because the slave is not 100% synchronous with the master. If 1 second of unavailability is too much for you, there are alternatives for synchronous replication (like semi-sync and galera).
Set your slave as the "active server". Congratulations, you are now on production with 5.6
If you want your original slave to be the final production server, you can repeat steps 2-5 for the old master (which should be the new slave now)

There are many things to have into account here: an upgrade from 5.1 to 5.6 (even if you follow upstream MySQL, and with more reason if you change provider) does not guarantee that it will go smoothly. You will suffer configuration changes, query optimiser changes, SQL syntax changes. You may want to do both both load and validation tests. There are many tools for load testing from your own logs (mysqlslap, percona playback), but I would recommend at least pt-upgrade for compatibility testing. You may also want to watch closely the "What's new" MySQL documents to take advantage of the new features and its equivalent for MariaDB/Percona.

Even if you decide to perform the upgrade not using replication (doing a cold restart), it is still a very useful tool to test the new server version beforehand.

Regarding the upgrade itself, 5.1 -> 5.6 itself can be done in binary format (I would recommend transitioning through 5.5 for easier fix of errors), but if you do not have a lot of data I would recommend you alternatively thinking if a logical export/import is feasible to avoid later rebuilds. This is very dependent on your actual setup.

Final words: remember to perform a backup beforehand and execute mysql_upgrade afterwards.

Check a recent article of how GitHub did a datacenter migration doing a similar process.


Some help with a crosstab query in postgresql I have a data set that looks like this:

CREATE TABLE game
AS
  SELECT team_name, match_id, sections_won
  FROM ( VALUES
    ( 'Team A', 6  , 4 ),
    ( 'Team B', 6  , 1 ),
    ( 'Team A', 19 , 4 ),
    ( 'Team A', 8  , 4 ),
    ( 'Team B', 19 , 1 ),
    ( 'Team B', 8  , 1 ),
    ( 'Team A', 7  , 4 ),
    ( 'Team B', 7  , 1 )
  ) AS t(team_name, match_id, sections_won);

It's for a game, so teams compete in matches.  The match has a possible 5 points and in these examples 1 team is winning 4 of the 5 sections

I'd like to pivot this into a scoreboard where the report looks like this:

Name    Match 6 Match 7 Match 8 Match 19
Team A  4       4       4       4
Team B  1       1       1       1

I've done this kind of thing before but I can't get this one to work for some reason.

The query looks a little like this.  I tried to simplify for examples sake.

select *
from crosstab(
  $$
    select game.team_name::text, game.match_id, sum(game.rank)::int4
    from game
    where rank = 1 -- i only want the sections the team won, then sum above.
    group by 1,2
 $$
  ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

The resulting data set looks like this though, which I don't get:

Name    Match 6 Match 7 Match 8 Match 19
team a  4       null    null    null
team b  1       null    null    null
team a  4       4       null    null
team b  1       1       null    null
team a  4       null    null    null
team b  1       null    null    null

Any thoughts?  I've spent several hours on this puppy so far.  Driving me nuts!  :)

You don't need the GROUP BY in a pivot... And, if you put that WHERE clause in there you're filtering out stuff you need.

SELECT *
FROM crosstab(
  $$
    SELECT game.team_name::text, game.match_id, game.sections_won
    FROM game
    ORDER by 1,2
 $$
  ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

  Team  | Match 6 | Match 7 | Match 8 | Match 19
--------+---------+---------+---------+----------
 Team A |       4 |       4 |       4 |        4
 Team B |       1 |       1 |       1 |        1
(2 rows)

As to why you need the ORDER BY, from the docs

  In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.


How to identify when to change Indexes We are running a relative small database which when download through bacpac comes as 80MB. But when it runs on Azure it expand to 2GB. In this database, we have couple of main table, but for this question I am focusing on this one table that hold all Customer Record who apply/register with us.
We get about 1000 entry in this database on average, 2000 when it is really busy. However the table is now 1.5M record old. Structure of table is:

CallID bigint Identity(1,1),
firstname varchar(50),
lastname varchar(50),
responsetime DateTime not null,
...
CompanyID bigint
EventID bigint

We have 80 columns in database, mainly containing address and other data. Now, in our reports on web, we need to show

How many new entry we get in last 14 days,
We list latest (today) entry in a Grid, so we query based on time and few data field.
We list/fiter our Grid based on given date range and/or event Id and/or companyid.

For this we over time we add 5-6 indexes, all non-clustered with 1, 2 field (like one is for companyid, other is eventid, third one is for ResponseTime) including other ID field

so most indexes are like

Create Index idxname on Leads (ResponseTime) include (companyId, eventID)

Now, the problem: We are not getting Good Performance on Azure SQL (s1) database tier. On Local Installation of Database our queries usually take <3 sec, but on Azure S1 it is taking 18-20 second. When we were on business Plan which is now retired (almost). it use to take < 2/3 second there as well.

We want to optimize the database, and certainly I believe that Indexes are not in good shape.
Most article/tutorial I found they says to create index, but not how to optimize the them. I spent last 8 hrs, trying to create new index and removing some old. but hardly can get a conclusive new index been create, because if I get performance benefit on one query it slows down other.

Now, is there any way, I can find out how to find best optimization of Indexes. I see azure/Sql server give missing index information, but it is asking to create 10 index on my table which already have 6 index, and most of them on single or two column and most of columns are repeated in multiple Suggested index.

Hope I make sense and might not get down vote for my question. Thanks.

EDIT: I found missing indexes using query from this article: https://msdn.microsoft.com/en-us/library/azure/dn369873.aspx

Since you are on S1 you can use two features from Azure:

Query Store
Index Advisor.

With Query Store you'll be able to identify the bottlenecks, the most heavily usage queries and the plans they use. This will allow you to prioritize what queries to optimize, and which existing indexes are important.

Index Advisor is more than missing indexes, see this video. It has some ML modeling that allows it to pick the most impactful index recommendation, eliminate duplicates, and it does A/B testing to measure the difference.

In general tables that already have 5-6 indexes and you find yourself adding more are good candidates for clustered columnstore indexes (supported on Azure and on premise post 2014). Segment elimination can do miracles for you.


Risk of disabling page locking In another question a user asks how to force row level locking. The answer that he receives tell them how to force row level locking by altering the table and the indexes. The user that answered the question does also mentions that there can be a side effect of preventing index reorganization.

What i want to know is if someone could explain this side effect further, because our worry is that a table the is inserted into and deleted from a lot, could become slow over time if the side effect of forcing row level locking does what we think it does.

Orginal post:

How to force the use of row locks?

Many thanks in advance

In the referenced link How to force the use of row locks? Paul White demonstrates that index reorganization cannot happen if that index has page locks disabled.

You can, of course, reset Page locking back on before doing a reorganization and then turn it off again afterward:

-- Allow page locks on Index
ALTER INDEX indexname ON tablename SET (ALLOW_PAGE_LOCKS = ON);

For what it is worth, in response to this question https://stackoverflow.com/questions/3114826/is-it-possible-to-force-row-level-locking-in-sql-server Remus Rusanu gives the statement on disabling page locking on an index.  But he also comments: "I would never turn this OFF on my database. The solution is always to properly design the schema and the queries so that scans (which are the culprits for escalation) don't occur to start with."

This does not mean that it is wrong to disable page locking, but it points out that there are other ways to work through the problem.

You also might be interested in reading about Lock Escalation (SQL Server 2008 R2 is the most recent document I can find) at: http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx


Stored procedure optimization In my application I am using the below stored procedure, but it is taking a long time to execute. Both tables contain millions of records. Please guide me to optimize this procedure. I have searched a lot but not found any better way.

 CREATE PROCEDURE [dbo].[sp_SearchReportAdminPanel]
 @UserName nvarchar(max),
 @FromDate datetime,
 @ToDate datetime,
 @RecepientNumber nvarchar(max),
 @Status nvarchar(max),
 @SenderId nvarchar(max),
 @PanelMasterId BIGINT = 1,
 @PageIndex INT = 1,
 @PageSize INT = 10,
 @RecordCount INT OUTPUT
 WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SkipPageRank INT = (@PageIndex - 1) * @PageSize ;

SELECT temp.*,um.usr_Mobile_No as UserName INTO  #Results FROM
(
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id
 FROM  Table1 xmd WITH (NOLOCK)
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and
    (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '') and
    (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and
    (xmd.Sender_Id = @SenderId OR @SenderId = '')
 UNION ALL
 SELECT XML_Id, Temp_Id, RIGHT(Mobile_number,10) as Recepient, Message_Text as Text,
        Coalesce(Message_Status, 'Sent') as Status, CreatedDate,DeliveryDate,User_Id
 FROM  Table2 xmd WITH (NOLOCK)
 WHERE (xmd.CreatedDate between @FromDate AND @ToDate) and
    (RIGHT(xmd.Mobile_number,10) = @RecepientNumber OR @RecepientNumber = '')   and
    (Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '') and
    (xmd.Sender_Id = @SenderId OR @SenderId = '')
) temp INNER JOIN Table3 um WITH (NOLOCK) on temp.User_Id = um.usr_Id
WHERE
    (um.usr_Mobile_No = @UserName OR @UserName = '') and
    (um.PanelMasterId=@PanelMasterId)

   SELECT @RecordCount = COUNT(*) FROM #Results

   select * from #Results
   ORDER BY XML_Id DESC
   OFFSET @SkipPageRank ROWS
   FETCH NEXT @PageSize ROWS ONLY

    DROP TABLE #Results
END

Your use of this term

(Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '')

in your inner WHERE clauses is non SARG-able, so no index can be used. Change both occurrences as shown below, to make this term SARG-able:

(xmd.Message_Status  = @Status
    OR (xmd.Message_Status is NULL AND @Status = 'Sent')
    OR @Status = '')


SQL Server 2014 (12.0.4439.1) to SP2 (12.5000) service start failure In what seems to be eerily familiar to the oh-so-famous SSISDB SP1 fiasco, I've hit a snag while upgrading our DEV server to SQL 2014 SP2.

The base version was SP1+CU5 (12.0.4439.1).  We have SSISDB installed and it's in an AG.  I ran the upgrade package on the secondary and received an error that SQL Agent was unable to start.

I went and checked the services via config manager and saw that none of the services came back up.  Here's what the log file has given me:

2016-07-25 15:02:44.76 spid5s      Database 'master' is upgrading script 'SSIS_hotfix_install.sql' from level 201331031 to level 201331592.
2016-07-25 15:02:45.89 spid5s      Error: 945, Severity: 14, State: 2.
2016-07-25 15:02:45.89 spid5s      Database 'SSISDB' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2016-07-25 15:02:45.95 spid5s      Error: 912, Severity: 21, State: 2.
2016-07-25 15:02:45.95 spid5s      Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 945, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-07-25 15:02:45.95 spid5s      Error: 3417, Severity: 21, State: 3.
2016-07-25 15:02:45.95 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-07-25 15:02:45.95 spid5s      SQL Server shutdown has been initiated
2016-07-25 15:02:45.95 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Anyone else experience this?  How do I go about bringing this thing back up?  Do I actually need to restore the master DB?

If there's any additional information you need, just let me know.  This is a non-production secondary that this happened to so we're not dealing with downtime or breaking any SLAs.  If this is the same issue we ran into with SP1, I'd definitely like to get the word out.

We have SSISDB installed and it's in an AG. I ran the upgrade package on the secondary and received an error

Secondary user databases in an availability group are not available to be written to (readable at best). Thus the upgrade failed as the database is part of the AG and not able to be written to.

  How do I go about bringing this thing back up? Do I actually need to restore the master DB?

I haven't tested it but you should be able to recover using -m, remove the SSISDB from the AG, then restart as normal.

Going forward you'll want to remove the SSISDB from the AG before running the upgrade and bringing it back in after the upgrade. Is it a pain? Yep. Will it stop you from having issues? Yep.


SQL Server connection fails over VPN I have an OpenVPN network consisting of VPN server (Linux), client (win8) and SQL Server (win 2008 server, SQL Server 2005), I'm trying to (of course) connect client to server.

When I try to connect using SQL Server Management Studio, it displays an odd error:

  Connection Timeout Expired.  The timeout period elapsed during the post-login phase.
  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to cre ate multiple active connections.
  The duration spent while attempting to connect  to this server was - [Pre-Login] initialization=580; handshake=865; [Login] ini tialization=0; authentication=2; [Post-Login] complete=58013;

The same happens when I try to connect from my (.NET) application - just hangs until the ConnectionTimeout has been reached throwing the same exception.

Curious thing is that I CAN successfully connect using sqlcmd from client and sqlsh+jTDS from OpenVPN server. Nothing shows up in SQL Server error logs or event log, netstat tells that the connection is in established state. Firewalls are off and I have tried every concise mix of connection string parameters, network parameters tweaking and still nothing. I have even tried bouncing the connection off a vpn server - same results.

Any ideas?

For a better analysis please provide a network dump (wireshark) from the client.
A typical problem with vpn is ip fragmentation. This would explain the established state in netstat and that it is working with another client (which might using smaller packets). But without trace it is just a wild guess.


What permissions does the SQL Agent's user need to execute Powershell scripts? I'm trying to create a job with a Powershell step.  To recreate this:

create new job, give it a name
add a new job step, name it, type powershell, run as the SQL Agent, script: $test="test"
set the target as the local server.
save and execute it.

When I run it, it fails with:

  Executed as user: DOM\ServiceAcct. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'set-executionpolicy RemoteSigned -scope process -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Security error.  '.  Process Exit Code -1.  The step failed.

The trouble is, that's not a line in my script.

From what I've read, it has to do with the SQLPS provider - I suspect it's executing a wrapper script first.  Which is fine, but it sounds like the service account is not permitted to run this line?

I did log into the machine as this account and Get-ExecutionPolicy -List shows:

        Scope ExecutionPolicy
        ----- ---------------
MachinePolicy    Unrestricted
   UserPolicy       Undefined
      Process       Undefined
  CurrentUser       Undefined
 LocalMachine    RemoteSigned

I can set the policy to remotesigned, but it doesn't persist - the next powershell window I open is reverted.

Anyone have any tips?  Oh, this is SQL 2012, I don't know if that matters though.

Additionally:
I found an answer to a similar question that suggests editing the registry.  However, my HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110\ExecutionPolicy value was already RemoteSigned.  I set it to Unrestricted and my test works, but that doesn't seem like best practice.  I'd expect a simple script to run by default.

Don't use the PowerShell job step type. It doesn't behave the same as "regular" PowerShell, it's stuck at a specific (old) version of PowerShell, and it's got a host of other limitations. TL;DR: You won't be happy with the results.

Instead, use a CmdExec job step and have it execute powershell.exe. This will let you run the latest PowerShell, load any module, and have total flexibility in how things execute. Full details, including using a Proxy, are available at https://dbatools.io/agent/


Fast copy/rename database, not mysqldump I would like to keep a "default" setup for a database on a testing server, which can be used to "restore" a "dirty" testing database back to the default state.

mysqldump is very fast, done in a few seconds. But restoring from the dump takes 5-10 minutes. If I could drop the test database, copy the "default" over to the test database directly somehow, perhaps by copying the database's system files, that would be awesome.

It sounds like you have a lot of indexes and/or you have not set innodb_buffer_pool_size is too small on your testing server.  If you have "indexed every column", repent!

A way to get a full disk copy instantly, regardless of its size, is to use LVM.  You would have the database sitting there, then you would take a "snapshot" and use the snapshot.  Eventually would would toss the snapshot.


MySQL Return Column Types from Select Statement Is is possible to get the column types and names from a select statement in MySQL?

E.g.,

SELECT t.first_col, t.third_col
FROM my_table t
WHERE t.id = 1

And then I would send that to MySQL and have it tell me.

column_name  |  type     | length
first_col    |  varchar  | 5
third_col    |  int      | null

I know I can do it in SQL Server I was hoping I can do it in MySQL too.

Why is that Questions come in pairs?  This recent one discussed the solution you want:

MySQL data precision confusion