cursor through user databases the SEQUEL

  • Ok so thanks to some help in a thread down below I can pass a use statement with a cursor to step through all client db's on a server with the object of returning the version..

    However in the code below @dbversion returns nothing , Im guessing something to do with being out of scope of the dynamic SQL .. any ideas ???????????

    thanks Si

    ---------------------------

    DECLARE

    @dbname varchar(255),

    @parentname varchar(255),

    @SQLSTR VARCHAR (1000),

    @ctrl CHAR (2),

    @dbversion varchar(10)

    SET @ctrl = CHAR (13) + CHAR (10)

    DECLARE DBCUR CURSOR FOR

    select name

    from master..sysdatabases

    where name not in ('master',

    'tempdb',

    'model',

    'msdb',

    'pubs',

    'northwind'

    )

    and name like '%customer%'

    order by 1

    OPEN DBCUR

    FETCH NEXT FROM DBCUR INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sqlstr = 'use '+@dbname +@ctrl

    +'if exists (select 1 from sysobjects where name = ' +char(39)+'DATABASE_VERSION'+char(39)+')'+@ctrl

    +'declare @dbversion varchar(10)'+@ctrl

    +'SELECT @dbVersion = case when len( iv.DB_VERSION ) = 1 then' +char(39)+' 0 '+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_VERSION as varchar(2) )+'+@ctrl

    +'FROM [DATABASE_VERSION] AS iv'+@ctrl

    +'WHERE DB_VERSION = (SELECT MAX (DB_VERSION) FROM [DATABASE_VERSION])'

    exec (@sqlstr)

    print @dbname

    print @dbversion

    FETCH NEXT FROM DBCUR INTO @dbname

    END

    CLOSE DBCUR

    DEALLOCATE DBCUR

    GO

  • Lookup sp_ExecuteSql in BOL... it will do as you ask.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I've had a look at sp_executesql in bol and In not sure that would help, for example the end print statement

    (print @dbname) I want it to "output" from the dynamic sql not use it as a parameter to pass in to the string , thats already done by the cursor no ?

    unless of course more than likely I missing something

  • Do you have a database with a name like %customer%???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • only on my sandbox but yes 🙂

    several !

    si

  • Simon_Lo (4/29/2008)


    Ok so thanks to some help in a thread down below I can pass a use statement with a cursor to step through all client db's on a server with the object of returning the version..

    However in the code below @dbversion returns nothing , Im guessing something to do with being out of scope of the dynamic SQL .. any ideas ???????????

    thanks Si

    ---------------------------

    DECLARE

    @dbname varchar(255),

    @parentname varchar(255),

    @SQLSTR VARCHAR (1000),

    @ctrl CHAR (2),

    @dbversion varchar(10)

    SET @ctrl = CHAR (13) + CHAR (10)

    DECLARE DBCUR CURSOR FOR

    select name

    from master..sysdatabases

    where name not in ('master',

    'tempdb',

    'model',

    'msdb',

    'pubs',

    'northwind'

    )

    and name like '%customer%'

    order by 1

    OPEN DBCUR

    FETCH NEXT FROM DBCUR INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sqlstr = 'use '+@dbname +@ctrl

    +'if exists (select 1 from sysobjects where name = ' +char(39)+'DATABASE_VERSION'+char(39)+')'+@ctrl

    +'declare @dbversion varchar(10)'+@ctrl

    +'SELECT @dbVersion = case when len( iv.DB_VERSION ) = 1 then' +char(39)+' 0 '+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_VERSION as varchar(2) )+'+@ctrl

    +'FROM [DATABASE_VERSION] AS iv'+@ctrl

    +'WHERE DB_VERSION = (SELECT MAX (DB_VERSION) FROM [DATABASE_VERSION])'

    exec (@sqlstr)

    print @dbname

    print @dbversion

    FETCH NEXT FROM DBCUR INTO @dbname

    END

    CLOSE DBCUR

    DEALLOCATE DBCUR

    GO

    When I run this I get a syntax error near FROM when I checked you Dynamic sql I believe you have an additional '+'

    Also it says Invalid object name 'DATABASE_VERSION'.

    DECLARE

    @dbname varchar(255),

    @parentname varchar(255),

    @SQLSTR VARCHAR (1000),

    @ctrl CHAR (2),

    @dbversion varchar(10)

    SET @ctrl = CHAR (13) + CHAR (10)

    DECLARE DBCUR CURSOR FOR

    select name

    from master..sysdatabases

    where name not in ( 'master',

    'tempdb',

    'model',

    'msdb',

    'pubs',

    'northwind'

    )

    and name like '%customer%'

    order by 1

    OPEN DBCUR

    FETCH NEXT FROM DBCUR INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sqlstr = 'use '+@dbname +@ctrl

    +'if exists (select 1 from sysobjects where name = ' +char(39)+'DATABASE_VERSION'+char(39)+')'+@ctrl

    +'declare @dbversion varchar(10)'+@ctrl

    +'SELECT @dbVersion = case when len( iv.DB_VERSION ) = 1 then' +char(39)+' 0 '+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_VERSION as varchar(2) )'+@ctrl

    +'FROM [DATABASE_VERSION] AS iv'+@ctrl

    +'WHERE DB_VERSION = (SELECT MAX (DB_VERSION) FROM [DATABASE_VERSION])'

    exec (@sqlstr)

    print @dbname

    print @dbversion

    FETCH NEXT FROM DBCUR INTO @dbname

    END

    CLOSE DBCUR

    DEALLOCATE DBCUR

    GO

    Try if this helps.

    Prasad Bhogadi
    www.inforaise.com

  • thanks prasad.. I think that was more the cut and paste error (my fault) in putting a simplified version in the thread

    ok below is full syntaxically (?) correct version

    It returns

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    I want it to return db name and version

    help !

    ----------------

    DECLARE

    @dbname varchar(255),

    @parentname varchar(255),

    @SQLSTR VARCHAR (1000),

    @ctrl CHAR (2),

    @dbversion varchar(10)

    SET @ctrl = CHAR (13) + CHAR (10)

    --2. declare cursor

    DECLARE DBCUR CURSOR FOR

    select name

    from master..sysdatabases

    where name not in ('master',

    'tempdb',

    'model',

    'msdb',

    'pubs',

    'northwind'

    )

    and name like '%customer%'

    order by 1

    --3. open cursor

    OPEN DBCUR

    --4. populate cursor

    FETCH NEXT FROM DBCUR INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sqlstr = 'use '+@dbname +@ctrl

    +'if exists (select 1 from sysobjects where name = ' +char(39)+'DATABASE_VERSION'+char(39)+')'+@ctrl

    +'declare @dbversion varchar(10)'+@ctrl

    +'SELECT @dbVersion = case when len( iv.DB_VERSION ) = 1 then' +char(39)+' 0 '+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_VERSION as varchar(2) )+'+@ctrl

    +'case when len( iv.DB_MAJOR ) = 1 then ' +char(39)+'0'+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_MAJOR as varchar(2) )+'+@ctrl

    +'case when len( iv.DB_MINOR ) = 1 then ' +char(39)+'0'+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_MINOR as varchar(2) )'+@ctrl

    +'FROM [DATABASE_VERSION] AS iv'+@ctrl

    +'WHERE DB_VERSION = (SELECT MAX (DB_VERSION) FROM [DATABASE_VERSION])'+@ctrl

    +'AND DB_MAJOR = (SELECT MAX (DB_MAJOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION)'+@ctrl

    +'AND DB_MINOR = (SELECT MAX (DB_MINOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION AND DB_MAJOR = iv.DB_MAJOR)'+@ctrl

    +'print @dbname'+@ctrl

    +'print @dbversion'

    exec (@sqlstr)

    --5. next row for cursor

    FETCH NEXT FROM DBCUR INTO @dbname

    END

    --6close it deallocate and free up the memory

    CLOSE DBCUR

    DEALLOCATE DBCUR

    GO

  • Couple of things...

    First, 'DataBase_Version' does not exist in SysObjects unless you created a table or something called 'DataBase_Version'. What is 'DataBase_Version'????

    Second, look at you IF statement in the dynamic SQL... are you missing a BEGIN/END or do you just want the DECLARE that follows to be conditionally executed?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/30/2008)


    Couple of things...

    First, 'DataBase_Version' does not exist in SysObjects unless you created a table or something called 'DataBase_Version'. What is 'DataBase_Version'????

    Second, look at you IF statement in the dynamic SQL... are you missing a BEGIN/END or do you just want the DECLARE that follows to be conditionally executed?

    Hi Jeff

    database version is a user table , not all db's will have this table and yes the logic is meant to be conditonal i.e if this table exists then do this .. if I can ever get this working I'll add another block to say what to do if a different versioning table exists

    thanks for all your suggestions .. appreciated 🙂

    si

  • Ok... thanks... just trying to figure it all out... I think that's the last piece. I'll see what I can do tonight.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/1/2008)


    Ok... thanks... just trying to figure it all out... I think that's the last piece. I'll see what I can do tonight.

    thanks Jeff appreciated 🙂

  • Basically what you are executing in the dynamic sql does not

    Simon_L (4/30/2008)


    thanks prasad.. I think that was more the cut and paste error (my fault) in putting a simplified version in the thread

    ok below is full syntaxically (?) correct version

    It returns

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    Server: Msg 137, Level 15, State 2, Line 11

    Must declare the variable '@dbname'.

    I want it to return db name and version

    help !

    ----------------

    DECLARE

    @dbname varchar(255),

    @parentname varchar(255),

    @SQLSTR VARCHAR (1000),

    @ctrl CHAR (2),

    @dbversion varchar(10)

    SET @ctrl = CHAR (13) + CHAR (10)

    --2. declare cursor

    DECLARE DBCUR CURSOR FOR

    select name

    from master..sysdatabases

    where name not in ('master',

    'tempdb',

    'model',

    'msdb',

    'pubs',

    'northwind'

    )

    and name like '%customer%'

    order by 1

    --3. open cursor

    OPEN DBCUR

    --4. populate cursor

    FETCH NEXT FROM DBCUR INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sqlstr = 'use '+@dbname +@ctrl

    +'if exists (select 1 from sysobjects where name = ' +char(39)+'DATABASE_VERSION'+char(39)+')'+@ctrl

    +'declare @dbversion varchar(10)'+@ctrl

    +'SELECT @dbVersion = case when len( iv.DB_VERSION ) = 1 then' +char(39)+' 0 '+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_VERSION as varchar(2) )+'+@ctrl

    +'case when len( iv.DB_MAJOR ) = 1 then ' +char(39)+'0'+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_MAJOR as varchar(2) )+'+@ctrl

    +'case when len( iv.DB_MINOR ) = 1 then ' +char(39)+'0'+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_MINOR as varchar(2) )'+@ctrl

    +'FROM [DATABASE_VERSION] AS iv'+@ctrl

    +'WHERE DB_VERSION = (SELECT MAX (DB_VERSION) FROM [DATABASE_VERSION])'+@ctrl

    +'AND DB_MAJOR = (SELECT MAX (DB_MAJOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION)'+@ctrl

    +'AND DB_MINOR = (SELECT MAX (DB_MINOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION AND DB_MAJOR = iv.DB_MAJOR)'+@ctrl

    +'print @dbname'+@ctrl

    +'print @dbversion'

    exec (@sqlstr)

    --5. next row for cursor

    FETCH NEXT FROM DBCUR INTO @dbname

    END

    --6close it deallocate and free up the memory

    CLOSE DBCUR

    DEALLOCATE DBCUR

    GO

    When you print your dynamic sql you will find that @dbname is not in the scope and you are trying to print

    use Customers

    if exists (select 1 from sysobjects where name = 'DATABASE_VERSION')

    declare @dbversion varchar(10)

    SELECT @dbVersion = case when len( iv.DB_VERSION ) = 1 then' 0 ' else '' end + cast( iv.DB_VERSION as varchar(2) )+

    case when len( iv.DB_MAJOR ) = 1 then '0' else '' end + cast( iv.DB_MAJOR as varchar(2) )+

    case when len( iv.DB_MINOR ) = 1 then '0' else '' end + cast( iv.DB_MINOR as varchar(2) )

    FROM [DATABASE_VERSION] AS iv

    WHERE DB_VERSION = (SELECT MAX (DB_VERSION) FROM [DATABASE_VERSION])

    AND DB_MAJOR = (SELECT MAX (DB_MAJOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION)

    AND DB_MINOR = (SELECT MAX (DB_MINOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION AND DB_MAJOR = iv.DB_MAJOR)

    print @dbname

    print @dbversion

    Try this out.

    DECLARE

    @dbname varchar(255),

    @parentname varchar(255),

    @SQLSTR VARCHAR (1000),

    @ctrl CHAR (2),

    @dbversion varchar(10)

    SET @ctrl = CHAR (13) + CHAR (10)

    --2. declare cursor

    DECLARE DBCUR CURSOR FOR

    select name

    from master..sysdatabases

    where name not in ('master',

    'tempdb',

    'model',

    'msdb',

    'pubs',

    'northwind'

    )

    and name like '%customer%'

    order by 1

    --3. open cursor

    OPEN DBCUR

    --4. populate cursor

    FETCH NEXT FROM DBCUR INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sqlstr = 'use '+@dbname +@ctrl

    +'if exists (select 1 from sysobjects where name = ' +char(39)+'DATABASE_VERSION'+char(39)+')'+@ctrl

    +'declare @dbversion varchar(10)'+@ctrl

    +'SELECT @dbVersion = case when len( iv.DB_VERSION ) = 1 then' +char(39)+' 0 '+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_VERSION as varchar(2) )+'+@ctrl

    +'case when len( iv.DB_MAJOR ) = 1 then ' +char(39)+'0'+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_MAJOR as varchar(2) )+'+@ctrl

    +'case when len( iv.DB_MINOR ) = 1 then ' +char(39)+'0'+char(39)+' else '+char(39)+''+char(39)+' end + cast( iv.DB_MINOR as varchar(2) )'+@ctrl

    +'FROM [DATABASE_VERSION] AS iv'+@ctrl

    +'WHERE DB_VERSION = (SELECT MAX (DB_VERSION) FROM [DATABASE_VERSION])'+@ctrl

    +'AND DB_MAJOR = (SELECT MAX (DB_MAJOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION)'+@ctrl

    +'AND DB_MINOR = (SELECT MAX (DB_MINOR) FROM [DATABASE_VERSION] WHERE DB_VERSION = iv.DB_VERSION AND DB_MAJOR = iv.DB_MAJOR)'+@ctrl

    --+'print @dbname'+@ctrl

    +'print @dbversion'

    print @dbname

    exec (@sqlstr)

    --5. next row for cursor

    FETCH NEXT FROM DBCUR INTO @dbname

    END

    --6close it deallocate and free up the memory

    CLOSE DBCUR

    DEALLOCATE DBCUR

    GO

    Also check to see that DB_VERSION, DB_MAJOR and DB_MINOR are not null.

    Prasad Bhogadi
    www.inforaise.com

  • thanks prasad shall give it a go 🙂

    si

  • Simon_L (5/1/2008)


    Jeff Moden (5/1/2008)


    Ok... thanks... just trying to figure it all out... I think that's the last piece. I'll see what I can do tonight.

    thanks Jeff appreciated 🙂

    Sorry, Simon... I lost track of this post. Let us know how Prasad's code works for you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Simon... take a peek at the following URL...

    http://www.sqlservercentral.com/Forums/Topic494388-145-1.aspx#bm494619

    Same technique can be used in SQL Server 2000 using VARCHAR(8000) with some limit, of course.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply