3

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

error message screen shot

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...]
|improve this question
  • Perhaps the session is being blocked. – Max Vernon Oct 22 '15 at 15:16
  • 1
    what is 500 "mo"? Is that supposed to be MB, as in megabytes? If it is 500 MB, are you sure that SQLCMD can process a file of that size? Also, what are the first few lines of that SQL script? – Solomon Rutzky Oct 22 '15 at 15:30
  • If I'm reading the docs correctly the TEXTSIZE should be set by the driver when connecting. As @srutzky mentioned it would be helpful to see the first few lines of your script. Also make sure you're in SQLCMD mode in SSMS. I don't think that would cause this error, but it is good to double check. – Erik Oct 22 '15 at 16:37
  • Try running the script with the -o -> output file and see exactly where your script gets stuck. There might be blocking going on. – Kin Shah Oct 22 '15 at 16:40
  • 1
    Also, did you ever check to see if any of the changes in those initial statements in the script took place? If the "session details" you are looking at in SSMS are coming from DBCC INPUTBUFFER, I believe that only shows the first query in a batch, not all statements, and not necessarily the "current" one. Although I would expect it to move past that if GO statements were placed every 100 lines. The point being, maybe best to not get hung up on "SET TEXTSIZE" and instead focus on "why aren't statements being processed". – Solomon Rutzky Oct 22 '15 at 16:44
1

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.

|improve this answer

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.