Problem with Script Length
Adam M
Posts: 2
Hi,
We wanted to use SQL Packager to package up a script for distribution to a not-particularly-tech-savvy user. If I run the full script in management studio or query analyzer it works perfectly. When we run it in the packaged executable we get an error, Incorrect syntax near ')'. If I review the script in the error window it seems to be truncated at 225 lines (out of 335), and the last line is consistent with the error we're getting (it reads 'insert into table (field1, field2, ...) ).
I've found that if I reduce the size of the script by dividing it up, the smaller script segments run in SQL Packager successfully.
Is this a known bug or limitation with packager 6?
We wanted to use SQL Packager to package up a script for distribution to a not-particularly-tech-savvy user. If I run the full script in management studio or query analyzer it works perfectly. When we run it in the packaged executable we get an error, Incorrect syntax near ')'. If I review the script in the error window it seems to be truncated at 225 lines (out of 335), and the last line is consistent with the error we're getting (it reads 'insert into table (field1, field2, ...) ).
I've found that if I reduce the size of the script by dividing it up, the smaller script segments run in SQL Packager successfully.
Is this a known bug or limitation with packager 6?
Comments
I'm currently working with Adam M thought a support ticket to try and get to the bottom of this issue.
So far it has not been reproducible with all scripts over 225 lines.
I will update this again when we uncover the problem.
I am also having trouble with some dependencies, but so do the SQL Server itself, so if you are counting on SQL Servers dependency engine, some scripts will fail until I manually restructure the script...
- Rene
I have logged a bug for this which the developers are currently reviewing (SPA-509).
SQL Packager uses its own dependency engine to work out the dependencies. What is getting scripted out of order in your example?
The undiscovered dependency is a table having a scalar user-defined function as a default value:
ALTER TABLE [dbo].[DISASTER]
ADD
CONSTRAINT [DF_DISASTER_DataFolder]
DEFAULT ([dbo].[fnDatabaseName]()) FOR [DataFolder]
Even with this, the fnDatabaseName is being created several batches after the DISASTER table.
fnDatabaseName is not depending on any other objects in this database.
The dependency is also not being recognized by SQL Doc 2 (but IS recognized by ApexSQLDoc 2008)
SQL Doc and SQL Packager both use the SQL Compare engine to interrogate the schema, so when the next version of these tools is released the fix will be included.
I need to buy a new version to see an ERROR corrected?
I just bought the version 6...
SQL Packager 6 is still the curent release, so the fix for the script length issue is not avaliable yet.
The best workaround would be to split your script into smaller batches.
my stored procedures have more then 1 thousand lines...
I understand that is not available. My question was: I have to buy another version (in the future) or a fix will be released ?
by the way, SQL Package is a great product !!!
[]s
Time is running, and this - to me - serious bug of not being able to package larger scripts renders our normal release routines useless - now for 3 months.
Is there any chance that you would correct the errors in a short while, or should I change deployment strategy?
I wanted to add my yea vote for a fix.
Regards !
I need this fix urgently.
something new ?
Sorry for the delay. There is a private build of SQL Packager which will hopefully fix this issue.
If you contact support@red-gate.com, they should be able to give you more details.
I am trying to package up a SQL script which is just 253Kb in size (updating an existing database, then creating new stored procedures in it, etc).
The latest version of SQL Packager still decides to truncate my SQL script after 32Kb, then complain that my SQL has an error in it...
Errr, no.
It only has an error in it as you've cut off my SQL in the middle of a "CREATE PROCEDURE" command.
Come on, this issue was reported over a year ago, and you were telling someone else in this thread that a "private" fix was available in June 2009, nine months ago.
Why hasn't an update been made widely available yet ?
And what do I need to do to get hold of this patch ?
There is still no official new release for SQL Packager, but you can download the patch version from here.
I hope this helps.
Perfect, works a treat.
Thanks for the quick reply.