+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Run same query on multiple databases.

  1. Run same query on multiple databases.

    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



  2. Re: Run same query on multiple databases.

    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" wrote in message
    news:3BA7DD2A-C7CD-4F61-893B-AC51C768AC0Batmicrosoftdotcom...
    >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
    >
    >



  3. Re: Run same query on multiple databases.

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



  4. Re: Run same query on multiple databases.

    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" wrote in message
    news:uOg7SVIFLHA.5736atTK2MSFTNGP02dotphx.gbl...
    > Answered in another newsgroup. Please do not post the same message to
    > multiple newsgroups independently.
    >


  5. Re: Run same query on multiple databases.

    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" wrote in message
    news:590068C6-1280-477E-AE2A-2F545B6D56F4atmicrosoftdotcom:

    > 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" wrote in message
    > news:uOg7SVIFLHA.5736atTK2MSFTNGP02dotphx.gbl...
    >
    > > Answered in another newsgroup. Please do not post the same message to
    > > multiple newsgroups independently.




  6. Re: Run same query on multiple databases.


    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" wrote in message
    news:#3SW7UIFLHA.3732atTK2MSFTNGP02dotphx.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" wrote in message
    > news:3BA7DD2A-C7CD-4F61-893B-AC51C768AC0Batmicrosoftdotcom...
    >>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
    >>
    >>

    >


  7. Re: Run same query on multiple databases.

    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" wrote in message
    news:906632BF-5979-41A7-91BC-4AD86F6B10F3atmicrosoftdotcom...
    > 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" wrote in message
    > news:#3SW7UIFLHA.3732atTK2MSFTNGP02dotphx.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" wrote in message
    >> news:3BA7DD2A-C7CD-4F61-893B-AC51C768AC0Batmicrosoftdotcom...
    >>>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
    >>>
    >>>

    >>



  8. Re: Run same query on multiple databases.

    Sammy (s_commarathotmaildotcom) 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, esquelatsommarskogdotse

    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


  9. Re: Run same query on multiple databases.

    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" wrote in message
    news:Xns9DA476010DC2Yazormanat127dot0.0.1...
    > Sammy (s_commarathotmaildotcom) 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, esquelatsommarskogdotse
    >
    > 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
    >


  10. Re: Run same query on multiple databases.

    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




+ Reply to Thread
Page 1 of 2 1 2 LastLast