Post reply

Can I call a file to execute via the Query window in Mgmt Studio?

  • March 4, 2009 at 7:57 am

    #197666

    I'm trying to run a large sql script (too big to just declare as a variable) against all my databases and was wondering if I can do something like this (obviously below wouldn't work as is, but I'm just looking for suggestions):

    EXECUTE sp_msforeachdb 'USE ?

    IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

    exec c:\script.sql

    Any guidance is greatly appreciated.

    thanks,

    Mike

  • March 4, 2009 at 8:09 am

    #953231

    You can use SQLCMD to do that.

    As per BOL

    Many sqlcmd options can be controlled in a script by using the setvar command. In the following example, the script test.sql is created in which the SQLCMDLOGINTIMEOUT variable is set to 60 seconds and another scripting variable, server, is set to testserver. The following code is in test.sql.

    :setvar SQLCMDLOGINTIMEOUT 60

    :setvar server "testserver"

    :connect $(server) -l $(SQLCMDLOGINTIMEOUT)

    USE AdventureWorks;

    SELECT FirstName, LastName

    FROM Person.Contact;

    The script is then called by using sqlcmd:

    sqlcmd -i c:\test.sql

    You can look at BOL for more details

    -Roy

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply