May 15, 2008 at 10:02 am
OK, so one can add
USE
At the top of a script and it will automatically use the DB you have specified, however, can this be extended to tell it to use only a certain server / dababase connection?
I tried something along the lines of
USE
And it didn't work
(As you may have guessed, this is to force certain queries to be run on local copies of live data - where this live data gets downloaded every evening - so database names are the same, but servers are not.)
May 15, 2008 at 11:59 am
The USE statement cannot be used to change servers. It is simply for changing the database context, which occurs after the connection to the server.
If you need to access multiple servers from one connection/script, you can use linked servers.
May 15, 2008 at 12:35 pm
peitech (5/15/2008)
OK, so one can add
USE
At the top of a script and it will automatically use the DB you have specified, however, can this be extended to tell it to use only a certain server / dababase connection?
I tried something along the lines of
USE
And it didn't work(As you may have guessed, this is to force certain queries to be run on local copies of live data - where this live data gets downloaded every evening - so database names are the same, but servers are not.)
You may care to look up OPENQUERY. It allows you to "send" queries to be executed locally by a linked server, so the bulk of the work would happen "at the other end".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 1:13 pm
If I understand you, you want to prevent a particular script from being run on your production servers because you have some developers that are - less than careful.
Something like this would work:
IF @@ServerName = 'ProdServerName'
RAISERROR('Hey dopey - this cannot be run on production',16,9)
May 16, 2008 at 2:26 am
Hey Michael,
Close, its more something for me personally actually! - With identical databases but just different servers its oh to easy to run a query on the local server which has out of date data - as information is changing all the time virtually everything I run should be on the live server - I was really looking for something so that I could choose - so for my 'data requests' I could paste something at the top which made it always choose live, and for my 'testing / experimenting' something which made it always use the local server!
I just tried your code, it does indeed produce an error, however, it does actually do the query too - in the case of SELECT queries this will be fine, but more worrying, in the case of UPDATE and DELETE queries it still actually does the update or delete.
Something which actually stopped as soon as it encountered the error and then did not proceed to run the contents of the script would be more ideal, but this is a good starting point for my data requests - so thanks!
May 16, 2008 at 5:26 am
You probably had a batch separator between the error message and your subsequent statements (either GO or ;).
GO will make the actions two individual executions, so something after the GO will always run. ; is a bit different because variables remain in scope.
So, remove the batch separator.
May 16, 2008 at 5:29 am
To be a bit safer, you could use the else of the IF statement
[font="Courier New"]IF @@ServerName = 'ProdServerName'
RAISERROR('Hey dopey - this cannot be run on production',16,9)
ELSE BEGIN
/* Add your code here */
END[/font]
May 16, 2008 at 5:30 am
Hmm, I'm not too sure what you mean there, however, a colleague advised me that I could get round it by adding an ELSE BEGIN END after the statement, which seemed to work pretty well!
May 16, 2008 at 5:31 am
SNAP! hehe
May 16, 2008 at 5:33 am
I'm now trying to see how I could save this and use it as a template when creating a new query from within Management studio - i.e. create new query and I can select from a range of templates.
The template explorer looks to be focused more on creation of stored procedures, functions etc...
(/me Ducks fearing he may have asked a hideous newbie question!)
May 16, 2008 at 6:24 am
I never use that template explorer, but it is my understanding that you can just right-click and create a folder, and then save your queries into this folder.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply