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

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 (AT) hotmail (DOT) com> wrote

Answered in another newsgroup. Please do not post the same message to
multiple newsgroups independently.

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 (AT) bogus (DOT) com> wrote

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 (AT) hotmail (DOT) com> wrote

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 (AT) comcast (DOT) net> wrote

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 (AT) hotmail (DOT) com> wrote

Sammy (s_commar (AT) hotmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

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 (AT) sommarskog (DOT) se> wrote

Sammy, this is a syntax thing.

This line is incorrect.
It contains unbalanced quote marks.

Just fix these up.

Geoff