OSQL Input File Size

22 Jun 2006 8:11 PM

DP

Running SQL Server SP4 / Win2K SP4

Have input file (containing all stored procedures batch separated with GO)
of about 7MB. When running this file using OSQL utility, only about 80% of
the objects get applied. No errors in the output (only sysdepends warnings).

If I split the file in half and run OSQL on the two halves, works just fine.
The script file works just fine in ISQL and ISQLW.

Any ideas?

27 Jun 2006 1:04 AM

Ciprian Gerea [MSFT]

What exactly does not get created?

--
Ciprian Gerea
SDE, SqlServer

This posting is provided "AS IS" with no warranties, and confers no rights.

Show quote

"DP" <D*@discussions.microsoft.com> wrote in message
news:36EF0657-89F5-4948-B6AF-4CBB6800E912@microsoft.com...
> Running SQL Server SP4 / Win2K SP4
>
> Have input file (containing all stored procedures batch separated with GO)
> of about 7MB. When running this file using OSQL utility, only about 80%
> of
> the objects get applied. No errors in the output (only sysdepends
> warnings).
>
> If I split the file in half and run OSQL on the two halves, works just
> fine.
> The script file works just fine in ISQL and ISQLW.
>
> Any ideas?
>
>

5 Jul 2006 1:32 PM

DP

The last portion of the stored procedures.

In other words, the script file looks like:

IF EXISTS (sp_A) DROP sp_A
GO
CREATE PROCEDURE sp_A AS ....
GO
IF EXISTS (sp_B) DROP sp_B
GO
CREATE PROCEDURE sp_B AS ....
GO

Then, assume there are 1000 stored procedures in this script file. Only the
first 800 of those stored procedures are actually created. To further
diagnose the problem, I added PRINT statements around the end of the script
(and at the very end) and the PRINT statements were being output. So, the
script seems to run in its entirety; however, it does not seem to actually
apply the last 200 or so objects.
None of the last objects are created.

Also note that I've seen this same type of behavior with other large script
files containing other types of objects (i.e., triggers).

Show quote

"Ciprian Gerea [MSFT]" wrote:

> What exactly does not get created?
>
> --
> Ciprian Gerea
> SDE, SqlServer
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "DP" <D*@discussions.microsoft.com> wrote in message
> news:36EF0657-89F5-4948-B6AF-4CBB6800E912@microsoft.com...
> > Running SQL Server SP4 / Win2K SP4
> >
> > Have input file (containing all stored procedures batch separated with GO)
> > of about 7MB. When running this file using OSQL utility, only about 80%
> > of
> > the objects get applied. No errors in the output (only sysdepends
> > warnings).
> >
> > If I split the file in half and run OSQL on the two halves, works just
> > fine.
> > The script file works just fine in ISQL and ISQLW.
> >
> > Any ideas?
> >
> >
>
>
>

5 Sep 2006 4:09 PM

DP

For the benefit of the community:

This particular item has been a thorn at my side for quite some time, and
I've finally figured out the problem.

OSQL utilizes ODBC. ODBC has a really bad problem handling result sets.
If, inside the script, rowcounts are returned to the caller (OSQL), at a
certain point -- I'm guessing past a particular buffer size -- OSQL will puke
without telling you anything. Thus, to solve the problem, at the top of the
series of batch statements, you MUST execute:

SET NOCOUNT ON

in order to prevent any rowcounts from being returned to the caller. Of
course, this wouldn't typically be necessary if your script doesn't do
anything that would return a rowset count; however, our particular script
inserts a record into a "logging" table for each object that gets created.
Thus, after a certain point, these "x rows affected" message was preventing
further objects from being applied. In general, from a network utilization
perspective, it is a good idea to utilize "SET NOCOUNT ON" unless you
specifically need it. The default in 6.5/7.0/2K is OFF.

I'm hoping this information helps SOMEONE out down the road.

Show quote

"DP" wrote:

> The last portion of the stored procedures.
>
> In other words, the script file looks like:
>
>
> IF EXISTS (sp_A) DROP sp_A
> GO
> CREATE PROCEDURE sp_A AS ....
> GO
> IF EXISTS (sp_B) DROP sp_B
> GO
> CREATE PROCEDURE sp_B AS ....
> GO
>
> Then, assume there are 1000 stored procedures in this script file. Only the
> first 800 of those stored procedures are actually created. To further
> diagnose the problem, I added PRINT statements around the end of the script
> (and at the very end) and the PRINT statements were being output. So, the
> script seems to run in its entirety; however, it does not seem to actually
> apply the last 200 or so objects.
> None of the last objects are created.
>
> Also note that I've seen this same type of behavior with other large script
> files containing other types of objects (i.e., triggers).
>
>
> "Ciprian Gerea [MSFT]" wrote:
>
> > What exactly does not get created?
> >
> > --
> > Ciprian Gerea
> > SDE, SqlServer
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "DP" <D*@discussions.microsoft.com> wrote in message
> > news:36EF0657-89F5-4948-B6AF-4CBB6800E912@microsoft.com...
> > > Running SQL Server SP4 / Win2K SP4
> > >
> > > Have input file (containing all stored procedures batch separated with GO)
> > > of about 7MB. When running this file using OSQL utility, only about 80%
> > > of
> > > the objects get applied. No errors in the output (only sysdepends
> > > warnings).
> > >
> > > If I split the file in half and run OSQL on the two halves, works just
> > > fine.
> > > The script file works just fine in ISQL and ISQLW.
> > >
> > > Any ideas?
> > >
> > >
> >
> >
> >