long SQL lines for osql

We have a very long and complicated SQL script which we run to upgrade
a version of our software from old to new. It works great in Query
Analyzer, but when run through osql it takes errors on lines that are
very long and (I think) stops reading after a certain amount of
characters. I've searched the net but haven't found anyone mentioning
this before. I have tried the -w 5000 parm to no avail.

Any suggestions?

pb648174 (google@webpaul.net)  writes:
> We have a very long and complicated SQL script which we run to upgrade
> a version of our software from old to new. It works great in Query
> Analyzer, but when run through osql it takes errors on lines that are
> very long and (I think) stops reading after a certain amount of
> characters. I've searched the net but haven't found anyone mentioning
> this before. I have tried the -w 5000 parm to no avail.

-w controls the width of the output. As I understand it, you have
problems with the input.

One thing to keep in mind, is by default OSQL runs with QUOTED_IDENTIFIER
off, which is different from Query Analyzer. Run with -I to change this.

It would have helped if you had included any error messages.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

The error messages are bogus, i.e. it is stopping processing in the
middle of a line, i.e. "Ad" is not a column, when the line is doing
something with "Address". I'll try the -l option and see if that makes
a difference, but it doesn't seem like it would make a difference since
I am using standard name, i.e. nothing with brackets, quotes or spaces
in the names.

pb648174 (google@webpaul.net)  writes:
> The error messages are bogus, i.e. it is stopping processing in the
> middle of a line, i.e. "Ad" is not a column, when the line is doing
> something with "Address". I'll try the -l option and see if that makes
> a difference, but it doesn't seem like it would make a difference since
> I am using standard name, i.e. nothing with brackets, quotes or spaces
> in the names.

OK, it sounds like it chokes on something.

It could be the file size, but it could also be the batch size.

If it is the file size, you can split the file into several and then
include the files with ~r. (Well, maybe. It could choke on the total.
But you could try.)

If it is the batch size, maybe you can throw in more "go" of you have
very long batches.

I recall that we had an issue with INSERT-files that we generate
from Excel. I had to fix the tool, so that it added a "go" after
each 40th EXEC or so. But if memory serves, it was the ISQL/W 6.5
that had this problems.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I haven't been able to reproduce it locally - it always happens during
our production push... Then I have to execute it in QA for every
database we have which is a pain. The total file size is about 2 MB and
there is a go after pretty much each statement.

Erland Sommarskog  wrote:
> pb648174 (google@webpaul.net) writes: 
>
> OK, it sounds like it chokes on something.
>
> It could be the file size, but it could also be the batch size.
>
> If it is the file size, you can split the file into several and then
> include the files with ~r. (Well, maybe. It could choke on the total.
> But you could try.)
>
> If it is the batch size, maybe you can throw in more "go" of you have
> very long batches.
>
> I recall that we had an issue with INSERT-files that we generate
> from Excel. I had to fix the tool, so that it added a "go" after
> each 40th EXEC or so. But if memory serves, it was the ISQL/W 6.5
> that had this problems.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx