question

mjharper avatar image
mjharper asked

SQLCMD no longer returns text from PRINT statements

Hi, I have a stored procedure that returns a load of text through PRINT statements. Up to now that's worked fine. Today we roughly doubled the amount of text returned by the stored procedure (previously it returned data for 6 months, now it returns data for 12 months). If I run the stored procedure in Management Studio, in SQLCMD mode, there's no issue - all data is returned. If I run it from either PowerShell or cmd line using SQLCMD shown below no text is returned. The stored procedure is run as it's taking 6 seconds before returning nothing - which is exactly the time it takes to run the stored proc from management studio if I select "discard results after execution" in the query options. This seems to suggest that there's a maximum amount of data that can be returned - but I can't see any options that allow me to extend that. Any advice apreciated. Thanks sqlcmd -S 'localhost' -d 'databaseName' -Q "exec myStoredProcedure 0;"
sqlcmd
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Maybe the answer is to just go another way round. You could try the Invoke-Sqlcmd cmdlet ( https://msdn.microsoft.com/en-us/library/cc281720.aspx). It should be very similar but might not drop the output. NB: Apparently you need to add the -Verbose parameter or the output of PRINT statements won't be included.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper avatar image mjharper commented ·
This worked. No idea why Invoke-Sqlcmd works and sqlcmd doesn't. I had to increase width of the output file to stop it wrapping the text. So the new line in the Powershell file, replacing the line in the comments above is now: Invoke-Sqlcmd -Query "$ThisCommand;" -ServerInstance $MySQLServer -Database $MyDB -Verbose 4>&1 | Out-File "$ThisOutputFile" -Width 4096 Thanks for your help David.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
That's odd. I can't see anything about limiting the output size. I don't know how big your proc's output is but I have a SQL Agent job step that runs a script with sqlcmd and it outputs 350,000 lines of text (about 24MB) to the step's output file. Is it possible that something else has changed as well and your code is the victim rather than the cause? Have you tried running the previous version just to make sure that still outputs correctly?
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper avatar image mjharper commented ·
Hi - nothing else has changed and if i run the old proc it runs fine. In fact I've tried slowly incrementing the date range from the original 6 months to 12 months. It stops working when I go from 231 days of data to 232 days of data! Very odd. I've looked at the day in question and there doesn't look to be any odd data that could be causing the issue. If I run in to file from management studio the file for 231 days is 4184KB. The file for 232 days is 4205KB. So it's not like the file is going over some obvious threshold value.
0 Likes 0 ·
David Wimbush avatar image David Wimbush commented ·
Sorry, but I'm stumped. The only think that's nagging me is that you haven't fully described how you're running it in PowerShell or SQLCMD and particularly how you're collecting the output. Are you piping to a file or displaying on screen? And is the output lots of PRINT statements and stuff or are there some big resultsets involved? That might ring a bell with somebody.
0 Likes 0 ·
mjharper avatar image mjharper commented ·
The actual process is within a PowerShell script - but within there it's just calling sqlcmd. It pipes the results to a file using the -o option with sqlcmd. The actual line in PowerShell is: sqlcmd -S $MySQLServer -d $MyDB -Q "$ThisCommand;" -o "$ThisOutputFile" -u -h -1 However if I run the code block in the original question from a cmd prompt window it also returns nothing. Thanks for your help - I'm stumped too!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.