Geeks With Blogs
Baskar B.V SQL - BI Consultant

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 | Back to top


Comments on this post: Executing SQL file using SQLCMD command

# re: Executing SQL file using SQLCMD command
Requesting Gravatar...
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?
Left by David on Jun 26, 2007 5:20 PM

# re: Executing SQL file using SQLCMD command
Requesting Gravatar...
Hi,

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.
Left by Baskar on Jun 27, 2007 2:12 AM

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

# re: Executing SQL file using SQLCMD command
Requesting Gravatar...
Did you try using -i switch like

-i \\<networkserver>\<foldername>\<filename>

P.S. If the file path has spaces then we need to include them within quotes. Let me know if that works out.
Left by Baskar on Jan 13, 2008 12:54 PM

# re: Executing SQL file using SQLCMD command
Requesting Gravatar...
i want schedule the script. here i have to enable the sqlcmd at runtime using t-sqlscript.
Is there any script for enabling SQL CMD Mode??????
Left by mohan on May 27, 2009 12:54 AM

# re: Executing SQL file using SQLCMD command
Requesting Gravatar...
Hi,

Can we use relative file path Instead of absolute file path.
Suppose I have created a master.sql file in folder ABC.
This ABC folder also contains File1.Sql, File2.sql and File3.sql.
Now, Instead of providing full path, can we use relative path so that my Master.sql file for the files in its own folder i.e. ABC folder
Left by Dheeraj Gupta on Oct 12, 2010 12:40 AM

# re: Executing SQL file using SQLCMD command
Requesting Gravatar...
it is very usefull
thanks
i need script to enable SQLCMD Mode
Left by Shehanaz on Dec 28, 2010 3:33 AM

# re: Executing SQL file using SQLCMD command
Requesting Gravatar...
Hi,

Can we use relative file path Instead of absolute file path.
Suppose I have created a master.sql file in folder ABC.
This ABC folder also contains File1.Sql, File2.sql and File3.sql.
Now, Instead of providing full path, can we use relative path so that my Master.sql file for the files in its own folder i.e. ABC folder

as Above mentioned by Dheeraj above.
Thanks
Left by Happy on Mar 29, 2013 6:56 AM

Your comment:
 (will show your gravatar)


Copyright © Baskar B.V | Powered by: GeeksWithBlogs.net