Saturday, December 6, 2008

Executing SQL file using SQLCMD command

Executing SQL file using SQLCMD command

In this post i am sharing my experience in executing SQL scripts using the utility SQLCMD of SQL-SERVER 2005. In Sql-Server 2000 osql was used to achieve this.

In projects we come across situation wherein we need to decide about the solution of how to run our database scripts. One way is to Generate Scripts in Sql-Server management studio and save these as .sql files and running from the database server. This post is going to explain a similar approach for this.

Consider for e.g we had created separate scripts for dropping tables,constraints etc., table creation scripts, stored procedure scripts all compiled in separate .sql files. Instead of running these .sql files separately in Management Studio we can create a separate master sql script .sql wherein we could call all the child scripts using sqlcmd command.

The below snapshot shows how to achieve this.

The command used to call .sql file is :r. Before you execute you need to enable SQLCMD mode either from toolbar or from Query Menu. After :r command pass the .SQL path with file name within quotes.

One weird this i found is when we are executing stored procedures written as .SQL files if it does not find an GO statement at the end of the first .SQL file it would take the script from the other .SQL file till GO statement and appends that script at the end of the first stored procedure.

Hope this post helps. Provide your comments.

Happy Coding!

posted on Tuesday, June 26, 2007 12:50 PM


# re: Executing SQL file using SQLCMD command 6/26/2007 5:20 PM David
Why not just use a SQL stored procedure? It will mean that you don't have to have any sort of files, and unlimited number of users can modify the *latest* version of the SP at any time.

This sounds like the hard way of doing things. Can you explain why you are using files over SP?

# re: Executing SQL file using SQLCMD command 6/27/2007 2:12 AM Baskar

The solution i talked about was running scripts when moving database to production or so. The reason i prefer SQL over SP is easy to run scripts when moving it production. Suppose you have 100 SP in your project and how do u deploy it in the server? You cannot open all the SP's over there and run each one by one. This SQL file is nothing but SP only saved as .SQL file. Even for modification people can take the latest SQL file and modify the SP inside it and run the SQL file.

# re: Executing SQL file using SQLCMD command 1/12/2008 1:12 AM Scott Bateman
Any tricks on how to run .sql files on a network? By default, it appears that SQLCMD doesn't like UNC paths.

# re: Executing SQL file using SQLCMD command 1/13/2008 12:54 PM Baskar
Did you try using -i switch like

-i \\\\

P.S. If the file path has spaces then we need to include them within quotes. Let me know if that works out.

1 comment:

Unknown said...

I think that you may be interested in another application that quickly eliminates data corruption issues in database files, please take a look at how repaire database not connected to sql server tool and let me know what do you think