Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unanswered: Changing Databases in while loop - (RESOLVED)

    I am trying to run an update query for 2 identical databases. I am using SQL 2000. Here is the code for the loop.
    --*****************************
    USE DB1
    Declare @Locnum1 integer
    set @Locnum1 = 0
    While @locnum1 < 2
    BEGIN
    Update OpenJob
    Set JobN = 15
    WHERE (OpenJob.CustomerN = 183)
    USE DB2
    Set @Locnum1 = @Locnum1 + 1
    END
    --*****************************
    When I run this code it runs the update query on DB1 twice instead of switching to db2. Any help you can provide would be greatly appreciated.

    Thanks,

    Jason
    Last edited by jmanthei; 05-01-08 at 15:56.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Instead of using the USE command, why not simply resort to 3 part naming?
    Code:
    Update db1.dbo.OpenJob
    ...
    Update db2.dbo.OpenJob
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2005
    Posts
    5
    The query that is actually used is much larger and there are nearly 100 of different update queries i need to run on the 2 databases so I was hoping to not have to write the queries twice or keep changing the database in query analyzer to accomplish this. I am just looking for a more efficient way of running these.

    Thanks,

    Jason

  4. #4
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by jmanthei
    The query that is actually used is much larger and there are nearly 100 of different update queries i need to run on the 2 databases so I was hoping to not have to write the queries twice or keep changing the database in query analyzer to accomplish this. I am just looking for a more efficient way of running these.

    Thanks,

    Jason

    everytime I do something like this, I've always end up rewriting the update query as dynamic SQL and inserting the db name into the text string using the FQN as mentioned in the previous post. There's no way to put a use statement in the middle of a loop. I'm usually running the queries against a lot more than 2 databases (hundreds), so I keep a separate table of the server/database names and query from that into a cursor and place the dynamic update statement inside the cursor.

    If the DBs are on the same server, you can query master..sysdatabases for the names and build your cursor from there.
    Last edited by jeebustrain; 05-01-08 at 11:31.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Dynamic SQL is a no-no for this.

    Is this a one off problem?
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2005
    Posts
    5
    George,

    I don't know what you mean by a one off problem?

    Jason

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Interesting
    Code:
    DECLARE @i int
        SET @i = 0
    
    USE master
    
    WHILE @i <= 1 BEGIN
      SELECT db_name()
      USE model
      SET @i = @i + 1
    END
    And I mean, is this something you are only going to run once, ever, or are you planning on putting this into production?
    George
    Home | Blog

  8. #8
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by georgev
    Dynamic SQL is a no-no for this.

    Is this a one off problem?

    why? I do this all the time and use dynamic sql for it.


    Code:
    declare @sql nvarchar(4000)
    declare @dbname nvarchar(100)
    
    declare dbcursor cursor for
    select name from master.dbo.sysdatabases where name in ('database1','database2')
    
    open cursor
    
    fetch next from dbcursor into @dbname
    while @@fetch_status=0
    begin
    
    set @sql='Update [' + @dbname + '].dbo.OpenJob
    	Set JobN = 15
    	WHERE     (OpenJob.CustomerN = 183)'
    
    print @sql
    
    exec sp_executesql @sql
    print @dbname + ' updated'
    
    fetch next from dbcursor into @dbname
    end
    
    close dbcursor
    deallocate dbcursor

  9. #9
    Join Date
    Sep 2005
    Posts
    5
    George,
    This is something that will be used in a production environment. I have been running these queries on both databases by changing the drop down in query analyzer.

    Jason

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, let me rephrase, only use dynamic SQL as a very last resort.

    I feel there is a more elegant solution to this problem, but before going further I would like to understand more about the "why". Why do you feel the need to update two databases like this; are you trying to keep them in synch with eachother? Is it something that you are going to do continually? Depending on your answers there may be better solutions available.
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dynamic SQL is pretty standard for repeating admin tasks across servers\ databases. As are cursors (although I use loops - I can't be @r5ed learning cursor syntax ).

    You can use 3 part naming as shown or just concatenate the db name at the beginning of your sql statement.

    e.g.
    Code:
    EXEC ('USE ' + @dbname + '; ' + @sql)
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by pootle flump
    Dynamic SQL is pretty standard for repeating admin tasks across servers\ databases. As are cursors (although I use loops - I can't be @r5ed learning cursor syntax ).

    You can use 3 part naming as shown or just concatenate the db name at the beginning of your sql statement.

    e.g.
    Code:
    EXEC ('USE ' + @dbname + '; ' + @sql)
    HTH

    yeah, I'd obviously find a better, non-cursor, non-dynamic solution if this was going to be incorporated into a piece of software or something that has the potential to be hammered by end users.. But if this is just some automated maintenance job or something you manually run, there's nothing wrong with doing it as I wrote. In my opinion the 2-3 seconds you might save by writing the query some other way aren't worth the extra time and headache involved in figuring it out the most 100% efficient way.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nah. For an admin process like this, dynamic sql is appropriate. Its just going to be difficult to debug, that's all. Sine you don't use any input parameters or expose it to the public, SQL Injection is not an issue (assuming you don't name a database "[Drop Database]")...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Sep 2005
    Posts
    5
    We run multiple ERP databases for each of our plant locations. When customers make changes to the requirements we need to update all jobs across all plants. All the databases are structurally the same. So When I get these updates I need to run the same update against multiple databases.

    I think the dynamic sql will work for now until I am able to devlope a custom app for the end users to be able to accomplish this themselves.

    Thanks for all the help.

    Jason

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jeebustrain
    yeah, I'd obviously find a better, non-cursor, non-dynamic solution if this was going to be incorporated into a piece of software or something that has the potential to be hammered by end users.. But if this is just some automated maintenance job or something you manually run, there's nothing wrong with doing it as I wrote. In my opinion the 2-3 seconds you might save by writing the query some other way aren't worth the extra time and headache involved in figuring it out the most 100% efficient way.
    I wasn't disagreeing with you - quite the opposite - I was saying you don't have to justify using dynamic SQL - it is perfectly appropriate for this! And I'm not advocating loops over cursors - they are a much of a muchness - I just can't be bothered learning two syntaxes for iterative processing when one can do the job of both that's all

    The SQL I showed was just an alternative for the OP - cat skinning and all that. It is a little less verbose but I'm certainly not saying it is "better".
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •