SQL Replication problem...HELP!!!!
-
Hi everyone!
Replicating a stored procedure on SQL 2000 Enterprise Manager gives me the following error:
The identifier that starts with '
INSERT INTO #tmpWeb (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, ' is too long. Maximum length is 128.
(Source: SARIN (Data source); Error number: 103)
----------------------------------------------------------------------------------------------------------
The identifier that starts with ', l.varLangName,
'7' + ISNULL((SELECT DISTINCT(s.varName) FROM tbl_Sites as s WHERE i.intSiteID = s.intSiteID), '') + t.varMe' is too long. Maximum length is 128.
(Source: SARIN (Data source); Error number: 103)
----------------------------------------------------------------------------------------------------------
The identifier that starts with '
INSERT INTO #tmpDoc (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, ' is too long. Maximum length is 128.
(Source: SARIN (Data source); Error number: 103)
----------------------------------------------------------------------------------------------------------
The identifier that starts with '), '') + ': @@ ' + di.varShortTitle + ', ' + ISNULL(d.varSymbol1, '') + ' - ' + ISNULL(d.varSymbol2, '') + ' ' + ISNULL(d.varSa' is too long. Maximum length is 128.
(Source: SARIN (Data source); Error number: 103)
----------------------------------------------------------------------------------------------------------
The identifier that starts with ', varlangName,
d.dtmPub, '5' + ISNULL((SELECT DISTINCT(di2.varShortTitle) FROM tbl_DocumentInfo as di2 WHERE di2.fkDocItemID ' is too long. Maximum length is 128.
(Source: SARIN (Data source); Error number: 103)
----------------------------------------------------------------------------------------------------------
The source code for the stored procedure is:
BEGIN
SELECT @Statement = "
INSERT INTO #tmpWeb (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, varSort, intLayoutID, varDescr,
bitWebFlyerExistEn, bitWebFlyerExistFr, bitWebFlyerExistSp, intDocTypeID)
SELECT DISTINCT(i.intItemID), t.intTextID, tpl.varURL,
CASE WHEN EXISTS(SELECT t2.intTextID FROM tbl_Text as t2 WHERE t2.intPageID = i.intItemID AND t2.intModeID = 7 AND t2.bitShow = 1 AND t2.intLangID = 1) THEN 1 ELSE 0 END,
CASE WHEN EXISTS(SELECT t2.intTextID FROM tbl_Text as t2 WHERE t2.intPageID = i.intItemID AND t2.intModeID = 7 AND t2.bitShow = 1 AND t2.intLangID = 2) THEN 1 ELSE 0 END,
CASE WHEN EXISTS(SELECT t2.intTextID FROM tbl_Text as t2 WHERE t2.intPageID = i.intItemID AND t2.intModeID = 7 AND t2.bitShow = 1 AND t2.intLangID = 3) THEN 1 ELSE 0 END,
3, '<b>' + ISNULL((SELECT DISTINCT(s.varName) FROM tbl_Sites as s WHERE i.intSiteID = s.intSiteID), '') + '</b>: ' + t.varMenu + '<br><I>' + t.varVignette '</I>'," + CAST(@intLangID as char) + ", l.varLangName,
'7' + ISNULL((SELECT DISTINCT(s.varName) FROM tbl_Sites as s WHERE i.intSiteID = s.intSiteID), '') + t.varMenu, '', '', '', '', '', ''
FROM tbl_Text as t
INNER JOIN tbl_Item as i ON i.intItemID = t.intPageID
INNER JOIN tbl_lang as l ON l.intLangID = t.intLangID
INNER JOIN tbl_Templates as tpl ON tpl.intTemplateID = t.intTemplateID
INNER JOIN tbl_TextProperties as tp On tp.intPageID = i.intItemID AND tp.intLangID = t.intLangID
WHERE (" + @varPageStr +
") AND t.intModeID = 7 AND bitShow = 1 AND t.intLangID ="
+ CAST(@intLangID as char) + "AND i.intSiteID LIKE '"
+ RTRIM(CAST(@varSiteID as char)) + "'"
EXEC(@Statement)
--get number of web pages found
SELECT @intPage = COUNT(intID1) FROM #tmpWeb
--insert to the final result set
END
IF @bitPress = 1 OR @bitAll = 1--search on press
BEGIN
SELECT @Statement = "
INSERT INTO #tmpDoc (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, dtmDate, varSort, intLayoutID, varDescr,
bitWebFlyerExistEn, bitWebFlyerExistFr, bitWebFlyerExistSp, intDocTypeID)
SELECT d.intDocID, di.fkDocItemID, 'Download.asp',
CASE WHEN EXISTS(SELECT d2.intDocID FROM tbl_Doc as d2 WHERE d2.intDocID = d.intDocID and d2.intLangID = 1) THEN 1 ELSE 0 END,
CASE WHEN EXISTS(SELECT d3.intDocID FROM tbl_Doc as d3 WHERE d3.intDocID = d.intDocID and d3.intLangID = 2) THEN 1 ELSE 0 END,
CASE WHEN EXISTS(SELECT d4.intDocID FROM tbl_Doc as d4 WHERE d4.intDocID = d.intDocID and d4.intLangID = 3) THEN 1 ELSE 0 END,
5, '<b>' + ISNULL((SELECT DISTINCT(di2.varShortTitle) + '</b>' FROM tbl_DocumentInfo as di2 WHERE di2.fkDocItemID = ds.intParentDocID AND di2.intLangID = "
+ CAST(@intLangID as char) + "), '') + ': @@ ' + di.varShortTitle + ', ' + ISNULL(d.varSymbol1, '') + ' - ' + ISNULL(d.varSymbol2, '') + ' ' + ISNULL(d.varSalesNo, '') + ', ' + ISNULL(CONVERT(char(12), d.dtmPub, 3), '')," + CAST(@intLangID as char) + ", varlangName,
d.dtmPub, '5' + ISNULL((SELECT DISTINCT(di2.varShortTitle) FROM tbl_DocumentInfo as di2 WHERE di2.fkDocItemID = ds.intParentDocID AND di2.intLangID = "
+ CAST(@intLangID as char) + "), ''),
'', '','','','', di.intDocTypeID
FROM tbl_DocumentInfo as di
INNER JOIN tbl_Documents as ds ON ds.pkDocItemID = di.fkDocItemID
INNER JOIN tbl_Doc as d ON d.intDocID = di.intDocID
INNER JOIN tbl_lang as l ON l.intLangID = di.intLangID
WHERE (" + @varPressStr +
") AND d.intLayoutID = 7
AND di.intDocTypeID = 5
AND di.intLangID = "
+ CAST(@intLangID as char) +
" AND d.intLangID = "
+ CAST(@intLangID as char) +
" AND di.intSiteID LIKE '"
+ RTRIM(CAST(@varSiteID as char)) + "'"
+ " AND di.intModeID = " + CAST(@intPublishMode as char) + " AND ds.bitBin = 0"
--SELECT @Statement
--RETURN
EXEC(@Statement)
--get number of docs found
SELECT @intPress = COUNT(intID1) FROM #tmpDoc
--insert to the final result set
INSERT INTO #tmpWeb SELECT * FROM #tmpDoc
DELETE FROM #tmpDoc
END
IF @bitDoc = 1 OR @bitAll = 1--search on documents
BEGIN
SELECT @Statement = "
INSERT INTO #tmpDoc (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, dtmDate, varSort, intLayoutID, varDescr,
bitWebFlyerExistEn, bitWebFlyerExistFr, bitWebFlyerExistSp, intDocTypeID)
SELECT DISTINCT (css.intDocID),
css.pkDocItemID,
css.varURL,
css.bitEnExist,
css.bitFrExist,
css.bitSpExist,
'1', '<b>' +
css.varShortTitle + '</b>, ' + css.varsubTitle +
+ '<br> ' + ISNULL(css.varSymbol1,'') + ' - ' +
ISNULL(css.varSymbol2,'') + ' ' + ISNULL(css.varSalesNo,'') + ', ' +
ISNULL(CONVERT(char(12),css.dtmPub,3 ),''),
css.intLangID,
varlangName=(SELECT l.varLangName FROM tbl_lang l WHERE l.intLangID=css.intLangID),
css.dtmPub, '4' + css.varShortTitle,
css.intLayoutID, ISNULL(css.varDescr,''),
css.bitWebFlyerExistEn, css.bitWebFlyerExistFr, css.bitWebFlyerExistSp, css.intDocTypeID
FROM tbl_CachedSearchSite as css
WHERE (" + @varDocStr +
") AND css.intLayoutID <> 7
AND css.intDocTypeID IN (1,2,3,4,6,7,8)
AND css.intLangID = "
+ CAST(@intLangID as char) +
" AND css.intSiteID LIKE '"
+ RTRIM(CAST(@varSiteID as char)) + "'"
EXEC(@Statement)
--get number of docs found
SELECT @intDoc = COUNT(intID1) FROM #tmpDoc
--insert to the final result set
INSERT INTO #tmpWeb SELECT * FROM #tmpDoc-- ORDER BY dtmDate DESC
DELETE FROM #tmpDoc
END
IF @bitMeet = 1 OR @bitAll = 1--search on meetings
BEGIN
SELECT @Statement = "
INSERT INTO #tmp (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName,intLangID, varLang, varSort, intLayoutID, varDescr,
bitWebFlyerExistEn, bitWebFlyerExistFr, bitWebFlyerExistSp, intDocTypeID)
SELECT m.fkMeetingID, " + CAST( @intStartMID as char) + ", 'Meeting.asp',
CASE WHEN EXISTS(SELECT m2.fkMeetingID FROM tbl_Meetings as m2 WHERE m2.fkMeetingID = m.fkMeetingID and m2.intLangID = 1) THEN 1 ELSE 0 END,
CASE WHEN EXISTS(SELECT m3.fkMeetingID FROM tbl_Meetings as m3 WHERE m3.fkMeetingID = m.fkMeetingID and m3.intLangID = 2) THEN 1 ELSE 0 END,
CASE WHEN EXISTS(SELECT m4.fkMeetingID FROM tbl_Meetings as m4 WHERE m4.fkMeetingID = m.fkMeetingID and m4.intLangID = 3) THEN 1 ELSE 0 END,
4, '<b>Meeting</b>: ' + ISNULL(m.varName, 'Title missing') + ' (' + ISNULL(CONVERT(char(12), m.dtmStartDate, 3), '') + ' - ' + ISNULL(CONVERT(char(12), m.dtmEndDate, 3), '') + ')', "+ CAST(@intLangID as char) +", l.varlangName,
'6' + ISNULL(m.varName, 'Title missing'),
'', '', '', '', '', ''
FROM tbl_DocumentInfo as di
INNER JOIN tbl_Documents as ds ON ds.pkDocItemID = di.fkDocItemID
INNER JOIN tbl_Meetings as m ON m.fkMeetingID = di.fkDocItemID
INNER JOIN tbl_lang as l ON l.intLangID = di.intLangID
WHERE " + @varMeetStr +
" AND di.intLangID = " + CAST(@intLangID as char) + " AND m.intLangID = " + CAST(@intLangID as char)
+ " AND di.intSiteID LIKE '"
+ RTRIM(CAST(@varSiteID as char)) + "'"
+ " AND di.intModeID = " + CAST(@intPublishMode as char) + " AND ds.bitBin = 0 AND m.bitLink = 1
ORDER BY m.dtmStartDate"
--SELECT Statement = @Statement
EXEC(@Statement)
--get number of meetings found
SELECT @intMeet = COUNT(intID1) FROM #tmp
--insert into #tmpWeb
INSERT INTO #tmpWeb SELECT * FROM #tmp
DELETE FROM #tmp
END
IF @bitProj = 1 OR @bitAll = 1--search on projects
BEGIN
SELECT @Statement = "
INSERT INTO #tmp (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, varSort, intLayoutID, varDescr,
bitWebFlyerExistEn, bitWebFlyerExistFr, bitWebFlyerExistSp, intDocTypeID)
SELECT tc.intprojectID, " + CAST(@intStartTCID as char) + ", 'Projects.asp', NULL, NULL, NULL, 11,
'<b>TC project</b>: ' + di.varShortTitle + ' (' + tc.ProjectID + ' - ' + pc.varName + ') ',"
+ CAST(@intLangID as char) + ", l.varlangName, '3' + di.varShortTitle,
'', '', '', '', '', ''
FROM tbl_DocumentInfo as di
INNER JOIN tbl_Documents as ds ON ds.pkDocItemID = di.fkDocItemID
INNER JOIN tbl_TechCoop as tc ON tc.fkProjectID = di.fkDocItemID
LEFT JOIN tbl_PROMSSubject as s ON s.intSubjectID = tc.intSubjectID
LEFT JOIN tbl_PROMSCountry as pc ON pc.intCountryID = tc.intCountryID LEFT JOIN tbl_Organisations as o ON o.intOrgID = tc.intOrgID
INNER JOIN tbl_lang as l ON l.intLangID = di.intLangID
WHERE " + @varProjStr +
" AND di.intLangID = " + CAST(@intLangID as char) + " AND di.intSiteID LIKE '"
+ RTRIM(CAST(@varSiteID as char))
+ "' AND di.intModeID = " + CAST(@intPublishMode as char) + " AND ds.bitBin = 0"
EXEC(@Statement)
SELECT @intProj = COUNT(intID1) FROM #tmp
--insert into the final resultset
INSERT INTO #tmpWeb SELECT * FROM #tmp
END
IF @bitBA = 1 OR @bitAll = 1--search on Board Actions
BEGIN
SELECT @Statement = "
INSERT INTO #tmpBA (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, dtmDate, varSort, intLayoutID, varDescr,
bitWebFlyerExistEn, bitWebFlyerExistFr, bitWebFlyerExistSp, intDocTypeID)
SELECT DISTINCT (css.intDocID),
css.pkDocItemID,
css.varURL,
css.bitEnAbstractExist,
css.bitFrAbstractExist,
css.bitSpAbstractExist,
'1', '<b>' +
css.varShortTitle + '</b>, ' + css.varsubTitle +
+ '<br> ' + ISNULL(css.varSymbol1,'') + ' - ' +
ISNULL(css.varSymbol2,'') + ' ' + ISNULL(css.varSalesNo,'') + ', ' +
ISNULL(CONVERT(char(12),css.dtmPub,3 ),''),
css.intLangID,
varlangName=(SELECT l.varLangName FROM tbl_lang l WHERE l.intLangID=css.intLangID),
css.dtmPub, '2' + css.varShortTitle,
css.intLayoutID, ISNULL(css.varDescr,''),
css.bitWebFlyerExistEn, css.bitWebFlyerExistFr, css.bitWebFlyerExistSp, css.intDocTypeID
FROM tbl_CachedSearchSite as css
WHERE (" + @varBAStr +
") AND css.intLayoutID <> 7
AND css.intDocTypeID = 10
AND css.intLangID = "
+ CAST(@intLangID as char) +
" AND css.intSiteID LIKE '"
+ RTRIM(CAST(@varSiteID as char)) + "'"
EXEC(@Statement)
--get number of docs found
SELECT @intBA = COUNT(intID1) FROM #tmpBA
--insert to the final result set
INSERT INTO #tmpWeb SELECT * FROM #tmpBA-- ORDER BY dtmDate DESC
DELETE FROM #tmpBA
END
IF @bitSG = 1 OR @bitAll = 1--search on SG Statements
BEGIN
SELECT @Statement = "
INSERT INTO #tmpDoc (intID1, intID2, varURL, bitURLLang1, bitURLLang2, bitURLLang3,
intType, varName, intLangID, varLang, dtmDate, varSort, intLayoutID, varDescr,
bitWebFlyerExistEn, bitWebFlyerExistFr, bitWebFlyerExistSp, intDocTypeID)
SELECT DISTINCT (css.intDocID),
css.pkDocItemID,
css.varURL,
css.bitEnAbstractExist,
css.bitFrAbstractExist,
css.bitSpAbstractExist,
'1', '<b>' +
css.varShortTitle + '</b>, ' + css.varsubTitle +
+ '<br> ' + ISNULL(css.varSymbol1,'') + ' - ' +
ISNULL(css.varSymbol2,'') + ' ' + ISNULL(css.varSalesNo,'') + ', ' +
ISNULL(CONVERT(char(12),css.dtmPub,3 ),''),
css.intLangID,
varlangName=(SELECT l.varLangName FROM tbl_lang l WHERE l.intLangID=css.intLangID),
css.dtmPub, '1' + css.varShortTitle,
css.intLayoutID, ISNULL(css.varDescr,''),
css.bitWebFlyerExistEn, css.bitWebFlyerExistFr, css.bitWebFlyerExistSp, css.intDocTypeID
FROM tbl_CachedSearchSite as css
WHERE (" + @varSGStr +
") AND css.intLayoutID <> 7
AND css.intDocTypeID = 9
AND css.intLangID = "
+ CAST(@intLangID as char) +
" AND css.intSiteID LIKE '"
+ RTRIM(CAST(@varSiteID as char)) + "'"
--SELECT @Statement
EXEC(@Statement)
--get number of docs found
SELECT @intSG = COUNT(intID1) FROM #tmpDoc
--insert to the final result set
INSERT INTO #tmpWeb SELECT * FROM #tmpDoc-- ORDER BY dtmDate DESC
END
--Select all rows
SELECT *, @intDoc as intDoc, @intMeet as intMeet, @intPage as intPage, @intProj as intProj, @intPress as intPress, @intBA as intBA, @intSG as intSG
FROM #tmpWeb
ORDER BY varSort DESC, dtmDate DESC
DROP TABLE #tmp
DROP TABLE #tmpWeb
DROP TABLE #tmpDoc
DROP TABLE #tmpBA
GO
Can someone help on this issue? This is giving me a headache!!!
Thanks!
Gabriel
-
Are you using replication, or are you creating this procedure in Enterprise Manager?
-
The error occurs when I replicate the stored procedure with the agent. However, if I create the stored procedure directly on the production side, I don't get any error.
Would you have an idea?
Gabriel
-
Repost (I don't think my previous post took)
We had a similiar problem replicating sprocs. It seems the method the sproc is created may cause a problem. Enterprise manager seems to have a different default file format when saving/compiling. Query Analyzer saves as either "unicode", "ansi", or "oem". The sproces created in QueryAnalyzer seem to replicate fine, but the ones created in EnterpriseManager posed problems.
We decided not to replicate the sprocs and just scripted them; then ran the script on the subscriber. Everything works fine now. I suppose if you have many subscribers, then this might be more cumbersome.
Steve
-
Steve
Thank you very much for your reply. I will probably script as well unless someone has another idea.
Thanks again.
Gabriel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply