Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Troubleshooting : Error – Incorrect syntax near ‘GO’

Posted by blakhani on August 28, 2014


Sometimes we are very comfortable and used to with certain things that if they change, we become nervous and uneasy. I have a lovely daughter and she always greets me when I come back home from office. That one “hello” takes away all my worries and I feel alive. Yesterday she didn’t do that and I was worried. I checked with my wife and she told that there was a mild fever and her mood is little different today. That made me little nervous.

Same feeling happened when I saw below in management studio of my colleague:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘GO’.

image

 

and management studio intellisense feature was also complaining about syntax. “Incorrect syntax near ‘End Of File’. Expecting ‘=’. As per documentation “GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor”

I went to Tools > Option in Management studio and found that there was a customization done on batch separator. SSMS> Tools > Options >Query Execution > SQL Server > General > Batch Separator.

Which means if I run with “come” it should work and as expected.

image

Perfect! This mystery is solved now. This made me think that can we customize SQLCMD as well. If we look into help of SQLCMD we can see parameter -c cmdend

image

Here is the usage of parameter –c . I have used hello as command end parameter and hello works same as go in true sense.

image

I hope this clears some confusion about batch separator.

If you are getting this error while using ExecuteNonQuery in .net program and running script then refer http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx which has a workaround.

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SSMS, Troubleshooting | Tagged: , , , , , , | Leave a Comment »

    Help : Getting error – Cannot resolve the collation conflict

    Posted by blakhani on August 26, 2014


    Recently someone posted on MSDN forum about collation related error which I have seen multiple times. Today I am taking time to write notes about that. Here is the famous collation conflict error which every DBA would encounter at least once in their career.

    Msg 468, Level 16, State 9, Line 29

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

    Let’s understand the error first. There are two collation in the error message. If you read in a flick they look similar but they are not. First one has CS (stands for case sensitive) and second one has CI (stands for case insensitive). From the message it’s clear that SQL can’t convert value implicitly between these two collations. Next action would be to you capture a profiler trace or some other troubleshooting technique to identify offending query. In other words, we get this error when queries use two or more columns with different collations for join and comparison purposes. So there could be two possibilities of getting this error message:

    1. Columns are in two different databases that have a different default collation.
    2. Columns are in same database that have collation explicitly specified as different.

    In most of the cases, this error has been caused when database was moved from one server to another server and this falls under first category. Here is the quick script to get same error.

     

    CREATE DATABASE [CaseSensitiveCollation]
     COLLATE SQL_Latin1_General_CP1_CS_AS
    go
    Use [CaseSensitiveCollation]
    go
    Create table MyTableInCaseSensitiveDatabase (vc varchar(100))
    go
    insert into MyTableInCaseSensitiveDatabase
    values ('sysobjects')
    go
    SELECT
    * FROM master.sys.objects a INNER JOIN CaseSensitiveCollation.dbo.MyTableInCaseSensitiveDatabase b ON a.NAME = b.vc

    Here is the screenshot.

     

    image

     

    The collation of master database is SQL_Latin1_General_CP1_CI_AS and for column vc in the table its different and hence the error.  Now, how do you resolve this error? We have multiple options.

     

    • Help SQL Server in identifying which column we want to convert to different collation.
    SELECT * 
    FROM   master.sys.objects a 
           INNER JOIN CaseSensitiveCollation.dbo.MyTableInCaseSensitiveDatabase b 
                   ON a.name = b.vc   collate SQL_Latin1_General_CP1_CI_AS
    • If this is due to restore of the database from different server then check server collation of source server. In this situation, we might have to rebuild the target server to match the source server for the collation. This is as good as reinstalling SQL Server.
    • If we are getting this error for a database which is created as a part of a product installation then we should review the product documentation for details on supported collations.

    I have seen few DBA suggesting to change database collation. It is important to understand that column collation is specified during creation of table. We can find that using below

    image

    Even if we alter the database the collation of already created table would NOT change. Only way to change the collation of existing tables is

    • Move the data to a new table with new collation.
    • Get the script of the table and create same index, stats etc. on new tables.
    • Drop the old table
    • Rename new table as old table. 

    If error is due to temporary tables created in tempdb database then you need to give a thought to contained database feature. Other thing Another option would be to provide the column level collation while creating table as below.

    CREATE TABLE #SQLServerHelp
       (iPK int PRIMARY KEY,
        nCol nchar COLLATE SQL_Latin1_General_CP1_CS_AS
       );
    

    Here are other error which you might get.

    • Cannot resolve collation conflict between "%ls" and "%ls" in %ls operator for %ls operation.
    • Collation conflict caused by collate clauses with different collation ‘%.*ls’ and ‘%.*ls’.
    • Cannot resolve collation conflict between "%ls" and "%ls" in %ls operator occurring in %ls statement column %d.
    • Implicit conversion of %ls value to %ls cannot be performed because the resulting collation is unresolved due to collation conflict between "%ls" and "%ls" in %ls operator.
    • Implicit conversion of %ls value to %ls cannot be performed because the collation of the value is unresolved due to a collation conflict between "%ls" and "%ls" in %ls operator.
    • Cannot resolve the collation conflict between "%.*ls" and "%.*ls" in the %ls operation.

    Hope this would help you in troubleshooting and fixing collation errors.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error, Troubleshooting | Tagged: , , , , | Leave a Comment »

    Log Shipping Message : Skipping log backup file since load delay period has not expired

    Posted by blakhani on August 21, 2014


    During my last visit to one of my friend’s home I was surprised to hear that he had a problem in log shipping where he wanted my help. It was a family meet-up and I never thought it would become technical. Anyways, I asked the problem and he mentioned that he has configured log-shipping for one database between two servers. He told me that he was getting some “delay” error and I asked to give the exact error message. Below is what we saw in “Restore Job history”

    2014-06-21 06:14:30.45     Starting transaction log restore. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0′
    2014-06-21 06:14:30.45     Retrieving restore settings. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0′
    2014-06-21 06:14:30.45     Retrieved common restore settings. Primary Server: ‘SQLPAPA’, Primary Database: ‘LogShipDemo’, Backup Destination Directory: ‘E:\LS\Secondary’, File Retention Period: 4320 minute(s)
    2014-06-21 06:14:30.45     Retrieved database restore settings. Secondary Database: ‘SomeName’, Restore Delay: 2, Restore All: True, Restore Mode: No Recovery, Disconnect Users: False, Last Restored File: E:\LS\Secondary\SomeName_20140621004200.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
    2014-06-21 06:14:30.51     Skipping log backup file since load delay period has not expired. Secondary DB: ‘SomeName’, File: ‘E:\LS\Secondary\SomeName_20140621004231.trn’
    2014-06-21 06:14:30.51     The restore operation was successful. Secondary Database: ‘SomeName’, Number of log backup files restored: 0
    2014-06-21 06:14:30.51     Deleting old log backup files. Primary Database: ‘SomeName’
    2014-06-21 06:14:30.51     The restore operation was successful. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0′
    2014-06-21 06:14:30.51     —– END OF TRANSACTION LOG RESTORE     —–

    So the question he asked were:

    • What is this "Load delay period" and what’s the meaning of the error?
    • Can we configure this somehow, somewhere?

    Answer

    • Load delay is a period what is given to secondary server to wait to restore the backup file. We can use this if we want to wait upon the copy job.
    • We can set this value in the SSMS
      Database > Right click > properties -> Transaction Log shipping -> Secondary databases -> Restore Transaction Log

    image

    If we want to check via T-SQL then we can see that in MSDB database of secondary server.

    Select  secondary_database, 
            restore_delay 
    from    msdb.dbo.log_shipping_secondary_databases

    If the load delay is set to zero and still we are seeing a long delay in restore job picking the file to restore then next thing to check would be if there is a time difference between primary and secondary servers. Having different time zone is not a problem but but UTC time should match else we would see such problems.

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Logshipping | Tagged: , , , | 3 Comments »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 794 other followers