6

I am executing below query but I am facing issue.

Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along with remote connections and I am able to execute the code on local machines (Ex: If I connect to SQL 2008 R2 machine and execute on SQL 2008 R2 Successful, If I connect to SQL 2014 Machine Successful on SSMS on SQL 2014.Issue is only when I connect to SQL 2008 R2 and execute on SQL 2014.When I remove the commented part I am able to execute the code successfully using SQLCMD.) I am not aware of any compatibility issues of this case.

Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

But when I removed the commented part and execute the query it is running successfully in fraction of seconds. Please let me know what is wrong with the commented part. I have pasted the T-SQL query below.

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on the tables
* No other constraints are replicated
* Parameters: 
* tempdbname
* Called by: PROCS.uspEngagement_Import
* Remarks: 
* Copyright: Copyright 2012 by XXXXX XXXXX. All rights reserved.
* Author: XXXXX
* Creation Date: July 07 2012
* Revision History:
* <Modified By> <Date Modified> <Changes made>
* XXXXX     July 31 2012    Removed IF EXISTS as this is required for non sysadmin user
* XXXXX     July 31 2012    Removed reference to sys.database_files as it returns NULL for non sysadmin users
* XXXXX     Aug 26 2012     Added Logic for FILESTREAM, ROWGUIDCOL and UNIQUE Columns. Note FILESTREAM
                                Can be created only if there is a UNIQUE ROWGUIDCOL
* XXXXX     Sep 08 2012     Removed logic to create default constraints. The defaults are not required besides 
                                are hinderance for schema upgrades
* XXXXX     Feb 02 2013     Added logic to avoid further generation of script if DB already exists
* XXXXX     Oct 24 2013     Added logic to create Database mdf and ldf in the path specified by @pstrFilePath
                                Required for Bug# 29221
                                Further removed comments and modified indentation
* XXXXX     Jan 06 2014     Refer Bug 30633. Conversion fails if schemas with '\' are present
                                in the source XXXXX Database. Added square braces '[' to take care of this issue
                                when generating schemas
                                Further removed the redundant code to PRINT 'Database Exists' if the Temp DB 
                                already exists
* XXXXX/XXXXX Dec 09 2014    PaperSizeinBytes column having issues during BCP IN, added logic to manage computed columns and its definition.
                                Computed column definition being hard coded now as xp_cmdshell cannot read from sys.computed_columns
* XXXXX  Dec 18 2015        Bug fix#73849: Conversion creates database in paths not excluded by antivirus
                                (mdf, ldf and filestrTemp DB to be created where current XXXXX DB SQL Server is configured)
*********************************************************************************/
15

Given that the script works without the header comment block but fails with it (but only for remote / TCP connections), the issue is most likely due to a parsing error. For example, SQLCMD and SSMS do their own pre-parsing of the submitted script (to break it into batches), and sometimes that pre-parsing doesn't work correctly (e.g. "GO" in 2nd half of nested block comments breaks batch parsing in SSMS and SQLCMD). To narrow it down, you can try the following tests:

  1. Highlight the entire /*...*/ comment block at the top and then hit Control-K, Control-C to comment each line out via inline comments (i.e. --):

    --/********************************************************************************
    --* Summary: Creates source database schema for BCP Import. Only created PK on ...
    --...
    --*********************************************************************************/
    
  2. Remove just a portion of the comment block. So maybe start by removing most of it (keep the top 3 lines and the bottom ***/ line). Then, if it works, keep adding back in the lines, starting with line #4, until it stops working. Meaning, the comment block would start out as being just the following:

    /********************************************************************************
    * Summary: Creates source database schema for BCP Import. Only created PK on ...
    * No other constraints are replicated
    *********************************************************************************/
    

    If that works, add in the 4th line so that the comment block is now:

    /********************************************************************************
    * Summary: Creates source database schema for BCP Import. Only created PK on ...
    * No other constraints are replicated
    * Parameters: 
    *********************************************************************************/
    

O.P. tried method #2 above and was able to get the script to work remotely with only the following line (4th from the bottom) removed:

/*******...
  Computed column definition being hard coded now as xp_cmdshell cannot read from sys.computed_columns
...*******/

O.P. then used inline comment (method #1 above) to narrow down a subset of the offending line and was able to get the following to work:

/*******...
  Computed column definition being hard coded now as --xp_cmdshell cannot read from sys.computed_columns
...*******/

At this point it is still unclear as to the exact root cause of the issue. I was unable to reproduce this error using the exact same comment block, testing in both SSMS 2008 R2 and SSMS 2012, even enabling SQLCMD Mode and forcing a TCP/IP connection (verifying via SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;). @MartinSmith asked in the discussion if there was "any sort of network security between the client and the server that might take exception to xp_cmdshell". That still could be a possibility, hence more testing is needed in order to determine what layer, specifically, is unhappy with xp_cmdshell (or some other part of that line that is to the right of the newly added --).



UPDATE

While the root cause has not been identified, here is more info to hopefully help narrow this down:

  1. The O.P. ran the following two statements via SSMS, SQLCMD, and .NET SqlClient:

    1. Running just xp_cmdshell (by itself) gets a "missing parameter" error, not the timeout

    2. Running xp_cmdshell 'cls' gets the timeout error. The error includes "HRESULT: -2146232060".

      SELECT CONVERT(VARBINARY(30), -2146232060 );
      -- 0x80131904
      

      Searching on "HRESULT: 0x80131904" finds that it is a not-very-specific SQL Server error (but mostly related to SharePoint, for some reason). Here are some of the variations on it:

  2. The O.P. ran the following three queries in SSMS and received an "Incorrect syntax" error:

    select 1/0; 
    go 
    xp_cmdshell --comment 
    'cls';
    

    and:

    select 1/0; 
    go 
    axp_cmdshell --comment 
    'cls';
    

    and:

    select 1/0; 
    go 
    p_cmdshell 'cls';
    

    All three of those queries received the following error:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ' '.

  3. The O.P. ran the following query in SSMS and received a different "Incorrect syntax" error, but not the timeout error:

    select 1/0; 
    xp_cmdshell 'cls';
    

    That query received the following error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near 'xp_cmdshell'.

  4. @Tom V mentioned that this issue has been submitted on Microsoft Connect, but has also been closed as "Not Reproducible":

    Text 'xp_cmdshell' causes transport-level error: semaphore timeout period has expired from remote system.

  5. Here are two very similar reports (both on S.O. but neither with any resolution):

  • 1
    There is a connect item but it's closed as not reproducible: connect.microsoft.com/SQLServer/feedback/details/1959379/… I posted that as an extra answer to the other question – Tom V Jan 12 '17 at 12:11
  • Nice find. Thanks for mentioning it. I just posted an update to my answer containing a summary of the testing that was noted in the chat room related to this question. I also added a comment to that Connect bug with links to this question and the other one that you added an answer to. – Solomon Rutzky Jan 13 '17 at 19:26
0

I found out the hard way that single quotes (') and double quotes (") can mess up your comments and even render stored procedures unrecoverable in SSMS. They will still run/execute but you will have to retrieve them from the sys.sql_modules dmv if you want to modify them ever again.

However, this does not seem to be the case here, as OP is running an ad-hoc statement with a big comment block.

Even though I was unable to reproduce the issue when logging in to a SQL Server 2008 R2 server and connecting to a SQL Server 2014 instance, I noticed that the issue seems to occur after 2048 bytes of data in the comment.

[...]
* XXXXX/XXXXX Dec 09 2014    PaperSizeinBytes column having issues during BCP IN, added logic to manage computed columns and its definition.
                                Computed column definition being hard coded now as
                                   's' is the 2048th character in the comment  --^

Could this be the issue? Is there a version of SQL Serer out there that has an issue with more than 2048 bytes in the comment?

  • The length of the comment is probably not the issue. The O.P. was able to get the same error running just xp_cmdshell --comment on a single line, in both SSMS and SQLCMD. Very strange. – Solomon Rutzky Jan 13 '17 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.