Problem with Script Length

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Problem with Script Length

Postby Adam M » Tue Feb 03, 2009 5:40 am

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?
Adam M
 
Posts: 1
Joined: Tue Feb 03, 2009 5:08 am

Postby Chris Auckland » Tue Feb 10, 2009 6:44 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby christiang » Thu Feb 26, 2009 12:37 am

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?
christiang
 
Posts: 5
Joined: Tue Feb 03, 2009 7:42 pm

Same problem with long Stored Procedures

Postby renepape » Mon Mar 02, 2009 11:00 pm

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
renepape
 
Posts: 5
Joined: Mon Mar 02, 2009 10:47 pm

Postby Chris Auckland » Wed Mar 04, 2009 1:26 pm

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?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Failing dependency

Postby renepape » Mon Mar 09, 2009 4:45 pm

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)
renepape
 
Posts: 5
Joined: Mon Mar 02, 2009 10:47 pm

Postby Chris Auckland » Mon Mar 09, 2009 6:06 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby nnodari » Tue Mar 31, 2009 9:06 pm

Chris Auckland,

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

I just bought the version 6...
Att
nnodari
 
Posts: 19
Joined: Mon Mar 30, 2009 9:04 pm

Postby Chris Auckland » Tue Mar 31, 2009 9:15 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby nnodari » Tue Mar 31, 2009 9:36 pm

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
Att
nnodari
 
Posts: 19
Joined: Mon Mar 30, 2009 9:04 pm

Postby Chris Auckland » Wed Apr 01, 2009 12:35 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Any ideas of maintenance release availability?

Postby renepape » Wed May 06, 2009 8:23 am

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?
renepape
 
Posts: 5
Joined: Mon Mar 02, 2009 10:47 pm

Large Script block is truncated

Postby rmegal » Wed May 06, 2009 8:54 pm

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.
rmegal
 
Posts: 2
Joined: Wed May 06, 2009 8:24 pm

Postby nnodari » Mon May 11, 2009 7:02 pm

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

Regards !
Att
nnodari
 
Posts: 19
Joined: Mon Mar 30, 2009 9:04 pm

Postby alice.easey » Wed May 13, 2009 12:07 pm

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).
alice.easey
 
Posts: 94
Joined: Wed Feb 27, 2008 12:56 pm
Location: Red Gate

Next

Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests