Red Gate forums :: View topic - Problem with Script Length
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Problem with Script Length

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
Adam M



Joined: 03 Feb 2009
Posts: 1

PostPosted: Tue Feb 03, 2009 5:40 am    Post subject: Problem with Script Length Reply with quote

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?
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Tue Feb 10, 2009 6:44 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
christiang



Joined: 03 Feb 2009
Posts: 3

PostPosted: Thu Feb 26, 2009 12:37 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
renepape



Joined: 02 Mar 2009
Posts: 5

PostPosted: Mon Mar 02, 2009 11:00 pm    Post subject: Same problem with long Stored Procedures Reply with quote

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
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Wed Mar 04, 2009 1:26 pm    Post subject: Reply with quote

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

Quote:
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
Back to top
View user's profile Send private message Send e-mail
renepape



Joined: 02 Mar 2009
Posts: 5

PostPosted: Mon Mar 09, 2009 4:45 pm    Post subject: Failing dependency Reply with quote

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)
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Mon Mar 09, 2009 6:06 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
nnodari



Joined: 30 Mar 2009
Posts: 19

PostPosted: Tue Mar 31, 2009 9:06 pm    Post subject: Reply with quote

Chris Auckland,

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

I just bought the version 6...
_________________
Att
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Tue Mar 31, 2009 9:15 pm    Post subject: Reply with quote

Quote:
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
Back to top
View user's profile Send private message Send e-mail
nnodari



Joined: 30 Mar 2009
Posts: 19

PostPosted: Tue Mar 31, 2009 9:36 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Wed Apr 01, 2009 12:35 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
renepape



Joined: 02 Mar 2009
Posts: 5

PostPosted: Wed May 06, 2009 8:23 am    Post subject: Any ideas of maintenance release availability? Reply with quote

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?
Back to top
View user's profile Send private message
rmegal



Joined: 06 May 2009
Posts: 2

PostPosted: Wed May 06, 2009 8:54 pm    Post subject: Large Script block is truncated Reply with quote

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.
Back to top
View user's profile Send private message
nnodari



Joined: 30 Mar 2009
Posts: 19

PostPosted: Mon May 11, 2009 7:02 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
alice.easey



Joined: 27 Feb 2008
Posts: 93
Location: Red Gate

PostPosted: Wed May 13, 2009 12:07 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group