SQL query failing because of commented part of T-SQL query 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:
* &lt;Modified By&gt; &lt;Date Modified&gt; &lt;Changes made&gt;
* 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)
*********************************************************************************/
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:


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 ...
--...
--*********************************************************************************/

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:


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


Running just xp_cmdshell (by itself) gets a "missing parameter" error, not the timeout
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:


https://social.technet.microsoft.com/Forums/office/en-US/24e3d1ea-7827-4e19-b099-fd44e7c0f9ec/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious


  My problem was solved by rebooting all servers in the farm, including SQL Server, which would have truncated TEMPDB, as one article suggests.

https://social.technet.microsoft.com/Forums/sharepoint/en-US/823ce071-72a8-4d6d-9bef-068cbe2b602d/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious


  We saw several errors on the SharePoint application showing up for Application EventIDs that led us to a VMWare forum that suggested disconnecting the CD/DVD drive from the SQL and SharePoint servers.  Since doing that the environment has been stable and there have been no  HRESULT: 0x80131904 errors.  From other forums it appears the issue is related to the location from which the media is used for installation.

https://social.technet.microsoft.com/Forums/office/en-US/21627022-f910-4bc8-a6f6-6e70f9b0ebff/exception-from-hresult-0x80131904?forum=sharepointadminprevious


  the root cause of the problem was that the location where our SQL server log files are being written to ran out of drive space (i.e. this being the SQL Server that's hosting our SharePoint content database).  We were able to resolve the problem by removing many of the older log files from that directory in order to free up some drive space.

https://stackoverflow.com/questions/23216704/sharepoint-2010-web-part-error-exception-from-hresult-0x80131904


  The comparers are case sensitive. should be . In my case, it was , changed to and it works now!


Meaning: this might be related to Locale / default language, Code Page, or Collation.


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 ' '.

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'.

@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.
Here are two very similar reports (both on S.O. but neither with any resolution):


SQL bcp The semaphore timeout period has expired
SQL Strange Timeout Issue