Run same query on multiple databases.
in [Server Configuration]
|
Prev: Cross Server Queries
Next: Database Encryption
From: Sammy on 25 Jun 2010 12:03 I have about 230 databases on my sql server. Out of which 220 or so are Dynamics Databases on which I want to run the same standard query as below. I don't want to select each db individually and run the query. Is there any way that I can run the query on the 220 dbs without selecting each one individually. The query should ignore or bomb out on the remaining 10 dbs as they don't have the table mentioned in the query below update XBANKINFO set BankReqSig2 = '1', Signature1 = 'X:\SIGNATURES\NEOSPINE\SIGNER1\DDEAN1.BMP', Signature1always = '1', Signature1Limit = '0', Signature2 = 'X:\SIGNATURES\NEOSPINE\SIGNER2\DBELL1.BMP', Signature2Limit = '0.01', Signature2Valid = '1', Signature2ValidMsg = '** Not valid without two signatures **', Void = '1', VoidMsg = '** void after 90 days **' I would really appreciate help on how to run this on the multiple dbs without having to go through each db Thanks very much Sammy C
From: Tom Cooper on 25 Jun 2010 12:41 First as always, carefully test any update before running in production. And make sure that you have adquate backups. Particularily for this update which will update every row in XBANKINFO for every database that has a table named XBANKINFO with those columns. Microsoft has a stored procedure named sp_msForEachDB which does this, it is undocumented, but works just fine. So you could do sp_msForEachDb 'update XBANKINFO set BankReqSig2 = ''1'', Signature1 = ''X:\SIGNATURES\NEOSPINE\SIGNER1\DDEAN1.BMP'', Signature1always = ''1'', Signature1Limit = ''0'', Signature2 = ''X:\SIGNATURES\NEOSPINE\SIGNER2\DBELL1.BMP'', Signature2Limit = ''0.01'', Signature2Valid = ''1'', Signature2ValidMsg = ''** Not valid without two signatures **'', Void = ''1'', VoidMsg = ''** void after 90 days **''' This will update every database (if the database does not have a table named XBANKINFO, the update for that database will error out, but that won't stop the process, it will just go on to the next database). And be very, very careful. Updates to every row of a table in every database frighten me. One mistake and you're going to have a lot of work undoing it. You probably want to do a begin tran before running the stored proc, then run a number of queries to make sure everything worked well (those queries need to be done in the same query window or you con do them in another query window if you use the READUNCOMMITED hint). When your sure it worked, go back to the original query window and do a commit. If anything went wrong, go back to the original query window and do a rollback. Tom "Sammy" <s_commar(a)hotmail.com> wrote in message news:3BA7DD2A-C7CD-4F61-893B-AC51C768AC0B(a)microsoft.com... >I have about 230 databases on my sql server. > > Out of which 220 or so are Dynamics Databases on which I want to run the > same standard query as below. I don't want to select each db individually > and run the query. > Is there any way that I can run the query on the 220 dbs without selecting > each one individually. The query should ignore or bomb out on the > remaining > 10 dbs as they don't have the table mentioned in the query below > > update XBANKINFO set BankReqSig2 = '1', > Signature1 = 'X:\SIGNATURES\NEOSPINE\SIGNER1\DDEAN1.BMP', > Signature1always = '1', > Signature1Limit = '0', > Signature2 = 'X:\SIGNATURES\NEOSPINE\SIGNER2\DBELL1.BMP', > Signature2Limit = '0.01', > Signature2Valid = '1', > Signature2ValidMsg = '** Not valid without two signatures **', > Void = '1', > VoidMsg = '** void after 90 days **' > > > I would really appreciate help on how to run this on the multiple dbs > without having to go through each db > > Thanks very much > > Sammy C > >
From: Scott Morris on 25 Jun 2010 12:41 Answered in another newsgroup. Please do not post the same message to multiple newsgroups independently.
From: Sammy on 26 Jun 2010 21:47 Why should I not post it. I was looking for a better way of doing what I needed than I got in the other newsgroup. So what's your problem if I post it in multiple newsgroups. "Scott Morris" <bogus(a)bogus.com> wrote in message news:uOg7SVIFLHA.5736(a)TK2MSFTNGP02.phx.gbl... > Answered in another newsgroup. Please do not post the same message to > multiple newsgroups independently. >
From: Geoff Schaller on 26 Jun 2010 22:28
Sammy, There is a very simple reason you should not cross-post. Mainly because the most of the people who inhabit this NG inhabit the cross-posted ones as well. It is quite tedious to see the same question poll in multiple places. To which should we respond? Do we increase everyone's traffic for your sake or do we have to go to each of the other NGs to make reference that the question was answered elsewhere? So I think it is just good manners to ask in one forum. If after a reasonable time you don't get a satisfactory response, then try another forum but spamming all of them for your immediate benefit just increases the frustration for the rest of us. Imagine if all questioners chose this line of support? We'd have an NG nightmare. This is the reason for having specific-topic forums. And really, if we think the question could be better answered elsewhere, please trust us to advise you accordingly. Cheers. Geoff Schaller Software Objectives "Sammy" <s_commar(a)hotmail.com> wrote in message news:590068C6-1280-477E-AE2A-2F545B6D56F4(a)microsoft.com: > Why should I not post it. > I was looking for a better way of doing what I needed than I got in the > other newsgroup. > So what's your problem if I post it in multiple newsgroups. > > "Scott Morris" <bogus(a)bogus.com> wrote in message > news:uOg7SVIFLHA.5736(a)TK2MSFTNGP02.phx.gbl... > > > Answered in another newsgroup. Please do not post the same message to > > multiple newsgroups independently. |