Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2007
    Posts
    10

    Unanswered: 'use' database syntax help

    I have database called 'test1' and when i use the code below im getting this error
    Server: Msg 170, Level 15, State 1, Line 9
    Line 11: Incorrect syntax near '@dbName'.


    plz help, here is the code

    DECLARE @dbName varchar(50)
    DECLARE @index int

    SET @index = 1
    SET @dbName ='test'

    set @dbName = @dbName + CAST(@index as varchar)
    use @dbName
    Last edited by smith000monday; 03-19-07 at 06:27.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    u cannot do that. if u want to select rows from table1 of test1 database
    select * from test1..table1

    or u can use dynamic sql like
    Code:
    DECLARE @dbName varchar(50)
    DECLARE @index int
    
    SET @index = 1
    SET @dbName ='test'
    
    set @dbName = @dbName + CAST(@index as varchar)
    exec ('select * from ' + @dbName + '..table1')
    remember dynamic SQLs r having security issues if not managed carefully

  3. #3
    Join Date
    Mar 2007
    Posts
    10
    thanks for replying. but i want to use 'use' syntax not 'exec'

    this works fine
    use test1
    select * from table1


    but when i use this i get the error
    use 'test' + '1'
    select * from table1

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Everything Upsalen has said is correct. His code is equivalent to what you requrest.

    If you really must use USE then:
    Code:
    DECLARE @dbName varchar(50)
    DECLARE @index int
     
    SET @index = 1
    SET @dbName ='test'
     
    set @dbName = @dbName + CAST(@index as varchar)
    exec ('USE ' + @dbname + ' GO select * from table1')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2007
    Posts
    10
    The reason i want to use 'USE' is i have around 20 database (test1,test2,test3,...test20) with same schema, everytime when i make update or fix scripts i have to run it 20 times. now i want to use loop and run it once for all the databases.

    here is what the code looks like...

    Code:
    DECLARE @dbName varchar(50)
    DECLARE @index int
    DECLARE @NUM_OF_DB int
    
    SET @NUM_OF_DB = 20
    
    SET @dbName = 'test'
    set @index = 1
    
    while @index <= @NUM_OF_DB
    begin
    	
    	set @dbName = dbName + CAST(@index as varchar)
    	USE @dbName
    	
    	-- paste here the scripts (script is thousands of lines long)
    
    
        set @index = @index + 1
        SET @dbName = 'test'
    end
    or if you know easy way to do this plz ...
    Last edited by smith000monday; 03-19-07 at 08:14.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i know easy way to do this

    if you have 20 databases which are essentially identical in structure (they would have to be if you can run the same thousand-line script on their tables), then just combine them into one database and vwalah, your USE problem goes away

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rudy's suggestion. My Code. Upsalen's code. Your code does not, and cannot, work. Unless perhaps you write something in .NET or similar and execute it from there. You cannot do what you want to do from QA\ SSMS.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2007
    Posts
    10
    Quote Originally Posted by r937
    yes, i know easy way to do this

    if you have 20 databases which are essentially identical in structure (they would have to be if you can run the same thousand-line script on their tables), then just combine them into one database and vwalah, your USE problem goes away

    c'mon.......i have good reason not to do that

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, you have good reason

    how about this: write 20 scripts, each with a different USE, each calling the same common script

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You may be able to write some code using ADO\ ADO.NET - execute your script, looping through the various databases, changing the database at each pass.

    I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed.... Perhaps it isn't. But perhaps it is.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2007
    Posts
    10
    Quote Originally Posted by pootle flump
    You may be able to write some code using ADO\ ADO.NET - execute your script, looping through the various databases, changing the database at each pass.

    I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed.... Perhaps it isn't. But perhaps it is.
    about design, 20 of them are same database. i just use them for training, testing,etc which means at the end it is one database.

    where can i find code for ADO\ ADO.NET.
    Last edited by smith000monday; 03-19-07 at 09:26.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by smith000monday
    were can i find code for ADO\ ADO.NET.
    You'll have to write it I am afraid. I don't have time to come up with any right now.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed.... Perhaps it isn't. But perhaps it is.
    well, yes, that was my point in post #6

    but then, after having been assured that there is good reason for 20 databases, i put forth in post #9 a modest programming suggestion (not requiring ADD or whatever that was)

    this suggestion, had it been undertaken, would have solved the problem elegantly

    and about half an hour ago

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2007
    Posts
    10
    Quote Originally Posted by r937
    well, yes, that was my point in post #6

    but then, after having been assured that there is good reason for 20 databases, i put forth in post #9 a modest programming suggestion (not requiring ADD or whatever that was)

    this suggestion, had it been undertaken, would have solved the problem elegantly

    and about half an hour ago

    okey im just looking for easy way. how can i call common script?
    to explain about db design i have one database copied 20 times for different purpose.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ADO - ActiveX Data Objects
    not to be confused with Data Access Objects which are totally different

    Yes - I missed the calling thingy - probably easier than ADO.
    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
  •