April 29, 2008 at 9:34 am
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
April 29, 2008 at 9:42 pm
Lookup sp_ExecuteSql in BOL... it will do as you ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2008 at 7:31 am
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
April 30, 2008 at 8:01 am
Do you have a database with a name like %customer%???
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2008 at 8:07 am
only on my sandbox but yes 🙂
several !
si
April 30, 2008 at 8:20 am
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
April 30, 2008 at 9:44 am
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
April 30, 2008 at 7:26 pm
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
Change is inevitable... Change for the better is not.
May 1, 2008 at 3:00 am
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
May 1, 2008 at 6:18 am
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
Change is inevitable... Change for the better is not.
May 1, 2008 at 9:35 am
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 🙂
May 2, 2008 at 7:42 am
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 threadok 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
May 2, 2008 at 10:16 am
thanks prasad shall give it a go 🙂
si
May 2, 2008 at 8:41 pm
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
Change is inevitable... Change for the better is not.
May 3, 2008 at 1:05 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply