Monday, February 20, 2012

Management Studio: connect again to load each SQL file

On Management Studio, can we choose to open any SQL files in the same Query window?

Right now, whenever we open a SQL file, we always have to "Connect to Database Engine" each time. In the new Query window, we have to select a database each time. For example, if we want to run 3 SQL scripts in the database "DB1", we have to do the following steps:
1. Click "Open File" to load the SQL file "S1.sql"; Connect to Database Engine; On the new Query window, select the database "DB1"; Execute;
2. Click "Open File" to load the SQL file "S2.sql"; Connect to Database Engine; On the new Query window, select the database "DB1"; Execute;
3. Click "Open File" to load the SQL file "S3.sql"; Connect to Database Engine; On the new Query window, select the database "DB1"; Execute;

Is there any easier way to run a few SQL files in the same database without signing on and selecting the database so many times?

Thanks,
Tony

You can use the sqlcmd.exe/osql.exe command line tool to run your files.

i.e.

sqlcmd.exe -E -S%computername% -dpubs -ifile1.sql
sqlcmd.exe -E -S%computername% -dpubs -ifile2.sql
sqlcmd.exe -E -S%computername% -dpubs -ifile3.sql
|||Thanks, Greg.
As a DBA, I use "SQL Server 2000 Enterprise Manager" and "Query Analyzer" daily to administer and run SQL scripts among a lot of database servers. I do use your suggested commands to run a few fix scripts by SQL jobs or Task schedulers. But for some ad hoc SQL scripts, query analyzer gives me a nice way to load and run any SQL script file in any database.
Some users also logged a suggestion on MSDN product feedback center to request this for SQL Server 2005. According to Microsoft:


Resolved as By Design by Microsoft on 2005-05-24 at 09:19:03
Hi,
This will significantly improve with the June CTP. The New Query command is more aware of where you are in the UI to guess your connection. If the connection can't be established within 5 seconds, we will then display the connection dialog to allow you to Cancel the connection.
Thank you,
Bill Ramos


We are using the June CTP. The "New Query" command works great. But the "Open File" command is still awkward. Hope Microsoft can improve the "Open File" command also. There is a workaround though. I can use "notepad" to open all SQL script files. Then copy and paste to the same query window to run them one at a time. It is ironic by doing so in this next-generation SQL Server. But at least, this workaround is better than logging on and selecting a database for each "Open File" command.|||

I dug around a bit, and found a solution that may/may not work for you. Here's what I did:

In SQL Enterprise Manager:
- create a new Project.
- Add all your SQL Files to your project in "Queries"
- Create a new connection ( or multiple connections if you have multiple servers to connect to) in "Connections". Make sure to set a default database.

Once this is done, every time you double-click on your file in "Queries", it will open it in the server and database entry specified in "Connections".

As far as doing the same thing in "Open File", my guess is we'd need a whole new icon to do this, similar to how we have "New Query", which opens a new window based on the existing connection, and "Database Engine Query", which opens a new window and prompts for a new connection, regardless what connections you have open.

|||I logged a suggestion with Microsoft to add a "Load SQL Script" command within the current existing query window. See the details:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=8098e484-1019-4331-aaba-1d11828d887a

They will take it under consideration for the next release. At meantime, they also suggested to use "Project" to hold connections and queries. I tried it. The only thing I do not like is that we cannot change its "Associated Connection" in the query properties. That means I cannot run the same query in another server. I have to save a copy of the same query for each different connection.|||Thanks to Erland Sommarskog for his post on my suggestion, we can use "Edit -> Insert File as Text". It is perfect.

|||

Not quite perfect. I'd like to be able to select several files in Windows Explorer at once, hit "Enter", and have them open with my current cnxn or prompt me once for the cnxn to be used for all files.

Even better:
1. If I'm opening several files at once, and I am not currently connected to a server or the tab/window that has focus isn't connected, prompt me for the first file's cnxn and ask if I want to apply that cnxn to the subsequent files.
2. If I am connected then presume I want to use the existing cnxn of the tab/window I have focus on (in the case I have several tabs open with different cnxns).

No comments:

Post a Comment