Re: Max query size in osql or management studio?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Andrew (andrew@xxxxxxx) writes:
Unfortunately the scripts are being developed by another team in the
organisation to be delivered to customers so we have no control over
either the scripts or how people run them. I agree that there are many
better methods to migrate data but this method was selected, rightly or
wrongly, as we support both SQLServer and Oracle.

Management studio stops processing with an Out of memory error after
approx 2-3, 000 insert statements. osql stops with an insuffieient
memory error at about 30,000 insert statements.

We tried putting a 'go' after every 1000 inserts in Management studio
and it lasted a little longer - it went to about 6,000 insert statements
before failing.

Do you have a SET NOCOUNT ON in the top of the script?

If that doesn't help, the next thing to try is SQLCMD, which is a
command-line utility just like OSQL. SQLCMD has a few more features,
although I cannot directly see that any of them are useful to you.
Then again, I have not used SQLCMD that much.

If neither SQLCMD does not cut it, you may have to write your own
small application. The SQL part of it is not that really difficult,
just parse out the "go" and send in reasonbly large batches. The
tricky part is probably to read the file in an efficient way.




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Track changes made to SQL Server
    ... Develop scripts and keep them under source control. ... Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... As can xSQL Object from xSQL Software ... free for SQL Server Express and also free for other editions of SQL ...
    (comp.databases.ms-sqlserver)
  • Re: Backup SQL Server
    ... The sqlcmd process is shortly shown in the taskmanager, ... Winwdows Scheduler either. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: Check if a Table is used in any sp or function
    ... I can generate all the scripts and look for it. ... on a chunk border. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: How to Execute SQL Scripts using Batch file?
    ... i think Batch file should contain Username,Password,Database and ... but SQLCMD has some extra bells and whistles. ... look them up in Books Online. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Copy Database Wizard - what would you improve?
    ... "Import and Export Wizard"? ... Another option is Generate Scripts to get the objects. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)