Running multiple SQL files in Microsoft SQL Server
Your case 2. is working:
SET SQLCMD=sqlcmd -S<Servername> -d<databasename> -E
for %%d in (*.sql) do %SQLCMD% -i%%d
OR
SET SQLCMD=sqlcmd -S<Servername> -d<databasename> -U<Username> -P<Password>
for %%d in (*.sql) do %SQLCMD% -i%%d
Opening .sql files in SSMS 2012 as default program and with existing instance
- To change to default entry I utilized an application that displayed the DDE (dynamic data exchange) values. I personally used FileTypesMan.
I then changed the command line for SSMS to:
"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe" /dde "%1"
The reason a new instance is always opened with editing seems to be that windows needs to associate the file with a program so that it can identity if the existing program is running and use this instance. This seems to be achieved by using DDE.
/dde
indicates that the command line should use DDE. "%1" passes the first string (ssms.exe path) to the DDE protocol for usage as a parameter.DDE Message: was kept at the SSMS 2008 R2's value I found:
Open("%1")
DDE Application: Change the Application to
ssms.11.0
Why? This was the difficult part. Where is this found? I could not find a tool for displaying the DDE application name. What I found was when I reviewed the registry editor that
sqlwb.sql.9.0
is the entry to open a new .sql file with 2008 R2. This matched the registry entry forsqlwb.9.0
that SSMS 2008 R2 entry was showing before I started making my changes in FileTypesMan. I removed the .sql and found that theHKEY_CLASSES_ROOT
had an entry forssms.sql.11.0
.
= File association now set to new installation, and if SSMS.EXE is open, it should use the existing instance with no additional work.
Tip: To further speed up the initial load if you don't have it open, you can use other command line options on the load. Just go to the RUN dialog and type: ssms.exe -?
for a list of startup parameters. I personally use -nosplash
to eliminate the splash screen load.
Additionally, I use SSMSBoost, the single greatest tool I've had to help with using SSMS daily (basic code formatting, snippet w/cursor placement, preferred connections setup, full keyboard shortcut programming (along with the option for multiple steps in once shortcut, ie basic macros". Andre and his team are extremely responsive to new ideas in their forum, its very much developers helping improve other developer's life.
How can I execute a set of .SQL files from within SSMS?
While SQLCMD.exe is the best way, SSMS also has a SQLCMD mode where you can execute a SQLCMD script. To enable this mode click Query in menu bar then select SQLCMD Mode.
The ":r filename.sql" command is the SQLCMD script command to import and execute a sql script file. You know you are in SQLCMD mode because any lines that are SQLCMD script commands will appear with colored (gray I think) background.
:setvar path "c:\Path_to_scripts\"
:r $(path)\file1.sql
:r $(path)\file2.sql
Related Topics
Date Split-Up Based on Fiscal Year
Grouping by Date, Return Row Even If No Records Found
Oracle Text Escaping with Curly Braces and Wildcards
How to Relate 3 Tables Depending on Event
Retrieve The Most Recent Record for Each Customer
How to Count Most Consecutive Occurrences of a Value in a Column in SQL Server
Why Google's Bigtable Referred as a Nosql Database
Query to Check Overlapping Ranges in SQL Server
How to Use Oracle Outer Join with a Filter Where Clause
What Is The Query to Get "Related Tags" Like in
Wordpress: Automatically Delete Posts That Are X Days Old
Orm or Something to Handle SQL Tables with an Order Column Efficiently
Delete ... from ... Where ... In
Setting Identity to on or Off in SQL Server
Oracle SQL Syntax - Check Multiple Columns for Is Not Null
Sql Datetime Format to Date Only
Need to Convert Text Field to Varchar Temporarily So That I Can Pass to a Stored Procedure