November 29, 2010 at 10:00 pm
SET NOCOUNT ON
DECLARE @SQLString1 nvarchar(2000), @SQLString21 nvarchar(1000),@SQLString22 nvarchar(1000)
DECLARE @SQLParams1 nvarchar(300), @SQLParams2 nvarchar(100)
SET @SQLString1 = N'
DECLARE @db_name sysname, @category int
IF OBJECT_ID(''tempdb..#sub_tbl'') IS NOT NULL
DROP TABLE #sub_tbl
CREATE TABLE #sub_tbl ( pub_server sysname, pub_db sysname, pub_name sysname, sub_db sysname, sub_server sysname, description nvarchar(255), pub_type int, sub_type int )
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(1500) IF (@category & 1) <> 0
BEGIN
SET @SQLString = N''USE '' + QUOTENAME(@db_name) + @SQLString21
if (has_dbaccess(@db_name) = 1)
begin
INSERT INTO #sub_tbl 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_tbl
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 cast( pub_name as varchar) as PublicationName,
cast( pub_server + ''-'' + pub_db as varchar) as Publication,
cast( sub_server + ''-'' + sub_db as varchar) as Subscription,
cast( case when pub_type=0 then ''transactional'' when pub_type=1 then ''snapshot'' when pub_type=2 then ''merge'' else ''unknown'' end as varchar) as PublicationType,
cast( case when sub_type=0 then ''push'' when sub_type=1 then ''pull'' when sub_type=2 then ''anonymous'' when sub_type=3 then ''any'' else ''unknown'' end as varchar) as SubscriptionType,
description FROM #sub_tbl ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC, 6 ASC
DROP TABLE #sub_tbl '
SET @SQLParams1 = N'@SQLString21 nvarchar(1000), @SQLString22 nvarchar(1000), @SQLParams2 nvarchar(100)' SET @SQLString21 = N'SELECT srvp.srvname, @db_name, pub.name, sub.dest_db, srvs.srvname, srvs.srvname + '':'' + sub.dest_db + '':'' + pub.name, pub.repl_freq, sub.subscription_type FROM syspublications pub, syssubscriptions sub, sysarticles art, master.dbo.sysservers srvp, master.dbo.sysservers srvs WHERE srvp.srvid = 0 AND srvs.srvid = sub.srvid AND pub.pubid = art.pubid AND sub.artid = art.artid' SET @SQLString22 = N'SELECT srvp.srvname, subp.db_name, pub.name, sub.db_name, srvs.srvname, ISNULL(sub.description, srvs.srvname + '':'' + sub.db_name + '':'' + pub.name), 2, sub.subscription_type FROM sysmergepublications pub, sysmergesubscriptions subp, sysmergesubscriptions sub, master.dbo.sysservers srvp, master.dbo.sysservers srvs WHERE srvp.srvid = subp.srvid AND srvs.srvid = sub.srvid AND sub.subid <> subp.subid AND sub.status <> 2 AND subp.subid = sub.partnerid AND pub.pubid = subp.pubid AND pub.pubid = sub.pubid '
SET @SQLParams2 = N'
@db_name sysname'
EXECUTE sp_executesql @SQLString1, @SQLParams1, @SQLString21, @SQLString22, @SQLParams2
----------------------
error:
msg 451,level 16,state 1 line 2
cannot resolve collation conflict for column 6 in select statement
November 29, 2010 at 10:41 pm
can anyone help me in reolving this.....
November 29, 2010 at 10:45 pm
What is the last version of SQL server you had that script working under?
What was the collation of that server?
What is the collation of the current SQL 2008 server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 29, 2010 at 10:54 pm
It last worked under SQL2005....
But it is not executing on SQL2008.
November 29, 2010 at 11:02 pm
What is the collation of each?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 29, 2010 at 11:04 pm
how do i check that ?can u please teach me
November 29, 2010 at 11:15 pm
Here is an article on how to do that
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 29, 2010 at 11:29 pm
in 2005 it is:French_CI_AS
and in 2008 it is SQL_Latin1_General_CP1_CI_AS
should i rechange that one?????
November 29, 2010 at 11:35 pm
You can change the collation to match (you should evaluate your environment to determine the downstream effects), or you could add
Collate French_CI_AS
after the from statements and after the "on" clauses in join statements.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply