Run same query on multiple databases.
in [Server Configuration]
|
Prev: Cross Server Queries
Next: Database Encryption
From: Sammy on 27 Jun 2010 06:42 Geoff After I added the ' I get the error as below Msg 156, Level 15, State 1, Line 37 Incorrect syntax near the keyword 'DEALL 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 OCATE'. I used the updated query as below "Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> wrote in message news:_MFVn.704$vD2.538(a)news-server.bigpond.net.au... > 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 > > >
From: Geoff Schaller on 27 Jun 2010 06:58 You're trying to do too much at once :-). The SQL you provided has all sorts of syntax issues but I don't know whether that is in the cut and paste to the forum or whether they are genuine. What happens when you run the query directly in SSMS without all the wrapper stuff. > Incorrect syntax near the keyword 'DEALL DECLARE @sql nvarchar(MAX), Well this is clear isn't it? A syntax error. What is DEALL DECLARE... ? It isn't in your post so what other syntax issues are you going to make us find for you when you can't cut/paste properly for us. Check your syntax. Verify every component in SSMS first. Geoff "Sammy" <s_commar(a)hotmail.com> wrote in message news:#iJCoUeFLHA.1868(a)TK2MSFTNGP05.phx.gbl: > Geoff > After I added the ' I get the error as below > > Msg 156, Level 15, State 1, Line 37 > Incorrect syntax near the keyword 'DEALL 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 > OCATE'. > > > > I used the updated query as below > > > "Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> wrote in message > news:_MFVn.704$vD2.538(a)news-server.bigpond.net.au... > > > 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 > > > > > >
From: Erland Sommarskog on 27 Jun 2010 09:56 Sammy (s_commar(a)hotmail.com) writes: > The problem is as below that I was getting with your solution. If you can > help me that will be great My posting also included this caveat: This may work. Well, most likely it does not, because it is not tested, or even checked for syntax correctness, but it should get you going. I also made the effort to explain the idea. > Msg 102, Level 15, State 1, Line 15 > Incorrect syntax near '�'. OK, so maybe I should not expect everyone to understand all syntax errors, but if this is a complaint I have to ask: are you prepared to make the slightest effort yourself to find a solution?` Excuse me, if I'm harsh, but I love to help people to help themselves. It is my hope that each time I make a suggestion that the person that I help will learn something, and next time a similar problem appears you are able to find the solution on your own - or at least ask a question one step later in the process. But if you are not willing to deal with a syntax error from a stray character (on my keyboard "�" and "'" are next to each other), then I get the uneasy feeling that you want to be spoon-fed. And I will have to admit that is far less appealing. At least not as long it's a consultancy gig with a pay-check. (But even when I work as a consultant I like to see my client to learn something in the process.) Here is a cleaned-up version which I've tested so that that it compiles and the loop runs. But since I don't have any XBANKINFO table on my system, I can't test that part. So again, I urge you: run this on a test system (copy a few of the Dynamics databases to that server). If you are unacquainted with dynamic SQL, I have a longer article on my web site about the topic: http://www.sommarskog.se/dynamic_sql.html. 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 WHERE state_desc = 'ONLINE' 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_id(''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 28 Jun 2010 13:05
Thanks so much Tom. That worked perfectly. Really appreciate your help on this "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:O24MpwcFLHA.4504(a)TK2MSFTNGP02.phx.gbl... > 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 >>>> >>>> >>> > |