Problem with Script Length

Adam M

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?

Chris Auckland

I though I would update this post in case anybody finds it and wonders about the status.

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.

christiang

I believe we maybe experiencing a similar problem with one of our scripts. The script runs fine in query anaylizer/management studio but fails in the packager and returns an error message that makes it seem like the particular query is being truncated. Do the individual queries within a script have a maximum length limit?

renepape

Hello, I'm experiencing the same problem with very long stored procedures. It seems like it is cutting a batch that contains more than 32K non-whitespace chars. The script runs OK in SQL Manager, but when packaged with SQL Packager 6.0.0.107 PRO, it cuts the long SP's. If I remove the first long SP from the script, DB creation just fails at the next long SP.
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

Chris Auckland

I meant to update this earlier, but it turns out that SQL Packager will split a SQL script over a certain size into batches (I'm not sure of the exact maximum size for a batch), but if the SQL in a single block is larger than the maximum size of the batch, then it will split the single block over multiple batches. This causes invalid batches to be created.

I have logged a bug for this which the developers are currently reviewing (SPA-509).

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

SQL Packager uses its own dependency engine to work out the dependencies. What is getting scripted out of order in your example?

renepape

Thanks for looking into this.
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)

Chris Auckland

Yes, this dependency problem was found to be a bug in the SQL Compare 7 engine. The problem was that if a function was referenced from within a computed column/default, the function would incorrectly be scripted after the table. This dependency issue has now been fixed in SQL Compare 8.0.

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.

nnodari

Chris Auckland,

I need to buy a new version to see an ERROR corrected?

I just bought the version 6...

Chris Auckland

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.

nnodari

smaller than 255 is impossible...
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

Chris Auckland

I'm pretty sure the fix will be included in a free maintenence release, however I don't have an estimate on when it will be avaliable.

renepape

Hello again
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?

rmegal

I think that I have run into the same problem. I tried to package up a large SQL script for upgrading a database. The script has many blocks of code delineated by GO statements that the Packager puts into what it calls a batch. However, there are some blocks that are particularly long (e.g. longer than 1000 lines) that get split into multiple batches and then fail to run.

I wanted to add my yea vote for a fix.

nnodari

I reinforce my request because one of the reasons for purchase it was the packaging of scripts, what I am not able to.

Regards !

Alice E

I'm sorry you're experiencing these problems with SQL Packager - we're looking into it at the moment and hope to be able to give you an update on this issue soon (hopefully in the next couple of weeks).

nnodari

1 month later (in fact 4 months after the first error report) ...
I need this fix urgently.

something new ?

Alice E

Hi,

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.

SwissMike

Errrm, has a fix for this bug been released yet ?

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 ?

Chris Auckland

Hi SwissMike,

There is still no official new release for SQL Packager, but you can download the patch version from here.

I hope this helps.

SwissMike

Chris Auckland wrote:

Hi SwissMike,

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.