Prev: Dealing with BAD Dates in SSIS
Next: Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle
From: Sammy on 25 Jun 2010 03:43
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 25 Jun 2010 08:11
Sammy (s_commar(a)hotmail.com) writes:
> 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 **'

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 set up a cursor over all databases. I then
make use of that a system stored procedure runs in the database
you prefix it with, and I also use that you can dynamically
which procedure to run through a variable. I run two dynamic
batches, the first determins whether the database has the table,
the second runs the UPDATE.

Before you run this in production, get a few of those database over
to a test system and run there.

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

 | 
Pages: 1
Prev: Dealing with BAD Dates in SSIS
Next: Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle