Sql-server – SQLCMD stuck on “set textsize 4096”

sql serversql-server-2012sqlcmd

I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012).

The script is around 500mb and I added a GO every 100 lines.
When I start the script with:

sqlcmd -E -S [server] -d [db] -i "[path to file]"

the connection is made but it is stuck on the first sql query made by sqlcmd and not the script with the error message:

SET TEXTSIZE 4096

I tried to let it run one afternoon just to see if I get an error or a timeout etc but I get nothing it just get stuck on it. All I can do is stop sqlcmd (CTRL+C in the console).

I tried with Management Studio 2008 and 2012.

Does anyone have an idea why I get this error?

Update :
I tried running it with the -o but nothing was written to the file even after 3 hours.
These are the first lines of the generated script :

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
[delete update or insert...]

Best Answer

I see in your script, there is a

BEGIN TRANSACTION

and followed by delete/insert/update. I guess if you have a big volume change, this transaction may take longer depending on your destination db's status, such as whether there is conflicting sessions blocking your current session, or your destination db's log file growth is set to 1024KB. So I'd suggest that you can start Profiler to check which sql statement is started, and also check whether there is any blocking issue.

Related Solutions

Sql-server – Error 3154 while restoring a backup using WITH REPLACE

You should use WITH REPLACE and in general avoid using the point-and-click thingies in Management Studio - they're inflexible and often have bugs.

This worked for me:

USE [master];
GO

CREATE DATABASE test;
GO

CREATE DATABASE test2;
GO

BACKUP DATABASE test TO DISK = 'c:\temp\test.bak' WITH INIT, COMPRESSION;
GO

RESTORE DATABASE test2
  FROM DISK = 'c:\temp\test.bak'
  WITH REPLACE,
  MOVE 'test' TO 'c:\temp\test2.mdf',
  MOVE 'test_log' TO 'c:\temp\test2.ldf';

Also you should make sure when you backup databases you use WITH INIT and/or don't point the device at a file that already contains a backup (since it might not be the same database you're backing up now - especially if you reuse names like test...).

Sql-server – Strange behaviours with SET options in SSMS

Among the SET statements in your script, SET QUOTED_IDENTIFIER is special in that it is processed at parse time rather than at execute time.

From SET Statements (Transact-SQL) (emphasis mine):

Considerations When You Use the SET Statements

All SET statements are implemented at execute or run time, except for SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER. These statements are implemented at parse time.

Confirmed in SET QUOTED_IDENTIFIER (Transact-SQL):

Remarks

[…]

SET QUOTED_IDENTIFIER is set at parse time. […]

So, in your case QUOTED_IDENTIFIER gets reset at parse time and is, therefore, OFF by the time CREATE INDEX is executed. The engine complains about it as expected.

To resolve this, you don't have to resort to dynamic SQL, it is enough to put a GO line just after the CREATE INDEX statement to make it a different batch from the subsequent SETs, which would cause it to be parsed separately from them:

…

CREATE UNIQUE CLUSTERED INDEX i1 ON dbo.v1(c1);

GO

-- make SET options the opposite of what is required
SET ANSI_NULLS    OFF;
SET ANSI_PADDING  OFF;
SET ANSI_WARNINGS OFF;
SET ARITHABORT    OFF;
SET CONCAT_NULL_YIELDS_NULL   OFF;
SET NUMERIC_ROUNDABORT    ON;
SET QUOTED_IDENTIFIER OFF;

…

Note, however, that SET Statements (Transact-SQL) also includes the following (emphasis mine):

Considerations When You Use the SET Statements

[…]

When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.

Which means that the uncommented SET statements would still break your script, only not at CREATE INDEX but at INSERT.

Best Answer

Related Solutions

Sql-server – Error 3154 while restoring a backup using WITH REPLACE

Sql-server – Strange behaviours with SET options in SSMS

Considerations When You Use the SET Statements

Remarks

Considerations When You Use the SET Statements

Related Question