Announcement

As of April 17, 2018, this site is now in read-only mode. To start new Blackbaud Developer’s discussions, you will find Developer Discussions in the Blackbaud CRM Community.

To post or respond to Blackbaud Community discussions, you will have to login using a www.blackbaud.com login. Learn how to login here. If you have questions or need assistance, please email community@blackbaud.com.

Using SQLCMD to execute large SQL batches

By: Joseph Styons

Posted on: February 8, 2013

Have you ever had a very large SQL script in a .SQL file, and you wanted to run it against your favorite database?

You may have naively thought (as I did) that you could just crack that baby open in SQL Server Management Studio, and hit F5.

If you did that, you may have found that SSMS doesn’t take well to those.  It will sometimes give you an “out of memory” error.

That can be overcome by running the script from inside SQLCMD.

 

This is an example script for doing that

   1: set sqlcmdexe=C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe

   2: set domain=BLACKBAUDHOST

   3: set user=YourUserName

   4: set scriptfile=c:\My Huge Sql File.sql

   5: set outputfile=c:\output file.log

   6: set dbname=291

   7: set server=(local)

   8:

   9: ::remove old output file (if any)

  10: if exist "%outputfile%" del "%outputfile%"

  11: "%sqlcmdexe%" -d %dbname% -S %server% -i "%scriptfile%" -o "%outputfile%"

  12: pause

 

it gets slightly more difficult with hosted environments, since they live on a different domain.

You have to combine “runas” and “sqlcmd” together.  Here’s an example:

 

   1: set sqlcmdexe=C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe

   2: set domain=BLACKBAUDHOST

   3: set user=YourUserName

   4: set scriptfile=c:\My Huge Sql File.sql

   5: set outputfile=c:\output.log

   6: set dbname=DATABASE_NAME_FROM_HOSTING

   7: set server=IP_ADDRESS\SERVER_NAME_FROM_HOSTING

   8:

   9: ::remove old output file (if any)

  10: if exist "%outputfile%" del "%outputfile%"

  11: runas /netonly /user:%domain%\%user% "%sqlcmdexe% -d %dbname% -S %server% -i \"%scriptfile%\" -o \"%outputfile%\""

 

 

One final note: SQLCMD.exe doesn’t make the same default assumptions about SQL collation and quote options.

You may find that including this block at the beginning of large scripts helps you avoid errors about obscure language and text settings:

   1: SET NUMERIC_ROUNDABORT OFF

   2: GO

   3: SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON

   4: GO

Leave a Reply

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean