November 24, 2010 at 9:55 pm
DECLARE @SQLString1 nvarchar(2000);
DECLARE @SQLString21 nvarchar(300);
DECLARE @SQLString22 nvarchar(300);
DECLARE @SQLParams1 nvarchar(300);
DECLARE @SQLParams2 nvarchar(100);
SET @SQLString1 = N'
DECLARE @db_name sysname, @category int
CREATE table #sub_tbl1
( pub_server sysname,pub_db sysname,pub_name sysname,pub_type int,
retention int,
status int,
description varchar(255))
DECLARE #db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name, category FROM master.dbo.sysdatabases
WHERE (category & 5) <> 0 AND (status & 512) = 0
FOR READ ONLY
OPEN #db_cursor
FETCH NEXT FROM #db_cursor INTO @db_name, @category
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQLString nvarchar(500)
IF (@category & 1) <> 0
BEGIN
SET @SQLString = N''USE '' + QUOTENAME(@db_name) + @SQLString21
if (has_dbaccess(@db_name) = 1)
begin
INSERT INTO #sub_tbl1
EXECUTE sp_executesql @SQLString, @SQLParams2, @db_name
end
END
IF (@category & 4) <> 0
BEGIN
SET @SQLString = N''USE '' + QUOTENAME(@db_name) + @SQLString22
if (has_dbaccess(@db_name) = 1)
begin
INSERT INTO #sub_tbl1
EXECUTE sp_executesql @SQLString, @SQLParams2, @db_name
end
END
FETCH NEXT FROM #db_cursor INTO @db_name, @category
END
CLOSE #db_cursor
DEALLOCATE #db_cursor
SELECT DISTINCT
pub_name as Publication,
cast( pub_server + ''-'' + pub_db as varchar) as PublicationSource,
cast( case when pub_type=0 then ''transactional'' when pub_type=1 then ''snapshot'' when pub_type=2 then ''merge'' end as varchar) as PublicationType,
cast( retention as varchar) as RetentionInHours,
cast( status as varchar) as Status,
description as Description
FROM #sub_tbl1
ORDER BY 1 ASC, 2 ASC, 3 ASC
'
SET @SQLString21 = N'
SELECT
srvp.srvname, @db_name, pub.name,
pub.repl_freq,
pub.retention,
pub.status,
pub.description
FROM
syspublications pub,
master.dbo.sysservers srvp
WHERE
srvp.srvid = 0
'
SET @SQLString22 = N'
SELECT
pub.publisher, pub.publisher_db, pub.name,
2, pub.retention, pub.status,
pub.description,
FROM
sysmergepublications pub
'
SET @SQLParams2 = N'
@db_name sysname
'
EXECUTE sp_executesql @SQLString1, @SQLString21, @SQLString22, @SQLParams2
I am getting the following error:
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@db_name".
Msg 137, Level 15, State 2, Line 30
Must declare the scalar variable "@SQLString21".
Msg 137, Level 15, State 2, Line 34
Must declare the scalar variable "@SQLParams2".
Msg 137, Level 15, State 2, Line 39
Must declare the scalar variable "@SQLString22".
Msg 137, Level 15, State 2, Line 43
Must declare the scalar variable "@SQLParams2".
November 24, 2010 at 10:10 pm
Hi,
U should give only one parameter.
Please look into the following Example....
Declare @SQLString1 NVArchar(Max),@SQLString21 NVArchar(Max)
SET @SQLString21='Select * from sysobjects where type=''V'''
Set @SQLString1='Select * from sysobjects'
EXECUTE sp_executesql @SQLString1
EXECUTE sp_executesql @SQLString21
or U may use as follows ..depends on Ur requirement
Declare @SQLString1 NVArchar(Max),@SQLString21 NVArchar(Max),@SQLString3 NVArchar(Max)
SET @SQLString21=' where type=''V'''
Set @SQLString1='Select * from sysobjects'
SET @SQLString3=@SQLString1+@SQLString21
EXECUTE sp_executesql @SQLString3
Regards,
Sasidhar Pulivarthi
November 24, 2010 at 10:29 pm
Thanks sasidhar for the reply ...
Even this is not working ...
Its popping the error at this line ...
"SET @SQLString = N''USE '' + QUOTENAME(@db_name) + @SQLString21"
error:
Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@SQLString21".
please help me on this ......
November 25, 2010 at 3:34 am
this is running fine on 2005 but not on 2008 can anyone help in this
November 25, 2010 at 6:31 am
think syntax error..
try this
Declare @SQLString1 NVArchar(Max),@SQLString21 NVArchar(Max),@SQLString3 NVArchar(Max) , @SQLSTring nvarchar(max)
declare @db_name varchar(20); set @db_name = 'master';
SET @SQLString21=' where type=''U'''
Set @SQLString1='Select * from sysobjects'
SET @SQLString3=@SQLString1+@SQLString21
SET @SQLString = N'USE ' + QUOTENAME(@db_name) + @SQLString3+';'
print @sqlstring
EXECUTE sp_executesql @SQLString
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply