Run same query on multiple databases.
in [Server Configuration]
|
Prev: Cross Server Queries
Next: Database Encryption
From: Sammy on 26 Jun 2010 22:53 Tom Thanks so much for this. However when I run this in test I only get error messages and it does not update the one db that has the tables. Only when I have this one db as the default db it updates the db but then it does not show any erorr messages implying that it only ran for the one db I would really appreciate your guidance on this. Thanks Sammy "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:#3SW7UIFLHA.3732(a)TK2MSFTNGP02.phx.gbl... > 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: Tom Cooper on 27 Jun 2010 03:41 Sorry, should have been, sp_msForEachDb 'Use [?] 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 **''' Tom "Sammy" <s_commar(a)hotmail.com> wrote in message news:906632BF-5979-41A7-91BC-4AD86F6B10F3(a)microsoft.com... > Tom > Thanks so much for this. However when I run this in test I only get error > messages and it does not update the one db that has the tables. Only when > I have this one db as the default db it updates the db but then it does > not show any erorr messages implying that it only ran for the one db > > I would really appreciate your guidance on this. > > Thanks > > Sammy > > "Tom Cooper" <tomcooper(a)comcast.net> wrote in message > news:#3SW7UIFLHA.3732(a)TK2MSFTNGP02.phx.gbl... >> 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: Erland Sommarskog on 27 Jun 2010 05:36 Sammy (s_commar(a)hotmail.com) writes: > 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. Because people who respond to questions in these newsgroups do not have an infinite amount of time on their hands. It's frustrating to spend time of composing an answer to a post, only to find that the question has already been answered in another newsgroup. As I recall I posted the answer in .programming, that apparently did not fit your tastes. Rather than going elsewhere, it would be decent to tell us what was wrong about it. I purposely avoided sp_MSforeachdb because it is undocumented and unsupported, and it seemed more difficult to fulfil the requirement of not getting errors in databases without the table using it. And remember: in these newsgroups, you *never* get less help than what you pay for! For reference, here is my solution again: DECLARE @sql nvarchar(MAX), @db sysname, @sp_executesql nvarchar(512), @hastable bit SELECT @sql = '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 **'� DECLARE dbcur CURSOR STATIC LOCAL FOR SELECT name FROM sys.databases OPEN dbcur WHILE 1 = 1 BEGIN FETCH dbcur INTO @db IF @@fetch_status <> 0 BREAK SELECT @sp_executesql = quotename(@db) + ..sp_executesql' EXEC @sp_executesql N'SELECT @id = object_name(''XBANKINFO'')', N'@id int OUTPUT', @hastable OUTPUT IF @hastable = 1 EXEC @sp_executesql @sql END DEALLOCATE dbcur -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Sammy on 27 Jun 2010 05:53 Erland The problem is as below that I was getting with your solution. If you can help me that will be great Msg 102, Level 15, State 1, Line 15 Incorrect syntax near '�'. Msg 102, Level 15, State 1, Line 28 Incorrect syntax near ' EXEC @sp_executesql N'. Msg 102, Level 15, State 1, Line 30 Incorrect syntax near 'XBANKINFO'. Msg 105, Level 15, State 1, Line 31 Unclosed quotation mark after the character string ', @hastable OUTPUT IF @hastable = 1 EXEC @sp_executesql @sql END DEALLOCATE dbcur '. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9DA476010DC2Yazorman(a)127.0.0.1... > Sammy (s_commar(a)hotmail.com) writes: >> 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. > > Because people who respond to questions in these newsgroups do not have > an infinite amount of time on their hands. It's frustrating to spend time > of composing an answer to a post, only to find that the question has > already been answered in another newsgroup. > > As I recall I posted the answer in .programming, that apparently did > not fit your tastes. Rather than going elsewhere, it would be decent > to tell us what was wrong about it. > > I purposely avoided sp_MSforeachdb because it is undocumented and > unsupported, and it seemed more difficult to fulfil the requirement of > not getting errors in databases without the table using it. > > And remember: in these newsgroups, you *never* get less help than what > you pay for! > > For reference, here is my solution again: > > DECLARE @sql nvarchar(MAX), > @db sysname, > @sp_executesql nvarchar(512), > @hastable bit > > SELECT @sql = '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 **'� > > DECLARE dbcur CURSOR STATIC LOCAL FOR > SELECT name FROM sys.databases > > OPEN dbcur > > WHILE 1 = 1 > BEGIN > FETCH dbcur INTO @db > IF @@fetch_status <> 0 > BREAK > > SELECT @sp_executesql = quotename(@db) + ..sp_executesql' > > EXEC @sp_executesql N'SELECT @id = object_name(''XBANKINFO'')', > N'@id int OUTPUT', @hastable OUTPUT > > IF @hastable = 1 > EXEC @sp_executesql @sql > END > > DEALLOCATE dbcur > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Geoff Schaller on 27 Jun 2010 06:32
Sammy, this is a syntax thing. > > VoidMsg = ''** void after 90 days **'� This line is incorrect. It contains unbalanced quote marks. Just fix these up. Geoff |