I’m setting up transactional replication, the snapshot generates fine, but I
get error code 1038 “Cannot use empty object or column names. Use a single
space if necessary.” when the distribution agent does the push replication
and reaches a particular SP.
Something to note is that table names wrapped in brackets (found in SQL
scripts in the snapshot folder) are being converted to quotation, is that
caused by SET QUOTED_IDENTIFIER ON?
so this: FROM [Pub_Articles-Keywords] StaArtK
is converted to: FROM "Pub_Articles-Keywords" StaArtK
The following is the detailed error log. Your help is appreciated.
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: SET QUOTED_IDENTIFIER ON
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: drop procedure
"sp_Search_KeywordBrowser"
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: CREATE PROCEDURE
"sp_Search_KeywordBrowser"
@.KeywordRoot int,
@.ArtRoot int,
@.start varchar(4) = '0',
@.quantity varchar(3) = '10',
@.Sort varchar(25) = 'priority',
@.YahooRoot int = 0
AS
if @.yahooroot = 0 set @.YahooRoot = @.ArtRoot
CREATE TABLE #TempTable
(PK int IDENTITY,
ArticleID int,
Link int,
priority int,
created datetime)
SET NOCOUNT ON
Insert INTO #TempTable (articleid, link, priority, created)
SELECT StaArtK.ArticleID ArticleID, A.Link Link,
StaArtK.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
FROM "Pub_Articles-Keywords" StaArtK
JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
left join Pub_Articles B on a.link = b.articleid
JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
StaArtK.ArticleID
WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
AND StaArtK.articleid not in (select articleid from
SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
keywordid = 272 or keywordid = 2471) and StaArtK.block = 0
Insert INTO #TempTable (articleid, link, priority, created)
SELECT StaArtK.ArticleID ArticleID, A.Link Link,
pak.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
FROM SpeedIndexes.dbo."Pub_Articles-Keywords_static" StaArtK
JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
left join Pub_Articles B on a.link = b.articleid
JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
StaArtK.ArticleID
Join "pub_articles-keywords" pak on StaArtK.nativeid = pak.articleid and
StaArtK.keywordid = pak.keywordid
WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
AND StaArtK.articleid not in (select articleid from
SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
keywordid = 272 or keywordid = 2471)
and StaArtK.nativeid not in (select articleid from #temptable)
update t set t.ArticleID = st.nativeid from #TempTable t join
speedindexes.dbo."pub_articles-keywords_static" st on t.articleid =
st.articleid where st.keywordid = 1060 and t.articleid <> st.nativeid
delete #tempTable where pk not in (select Max(pk) from #tempTable group by
articleid)
DELETE #TempTable WHERE (Link <> 0 AND Link IN (SELECT ArticleID FROM
#TempTable))
--Remove Links that have 'cousin links' in the result set
--OR (ArticleID NOT IN
--(SELECT TOP 1 ArticleID
--FROM #TempTable
-- GROUP BY Link, ArticleID
--HAVING COUNT(Link) > 1) AND Link <> 0)
declare @.resultcount int
set @.resultcount = (select count(*) from #TempTable)
declare @.sql nvarchar(500)
set @.sql = 'delete #TempTable where articleid not in (SELECT top ' +
@.quantity + ' articleid FROM #TempTable where pk not in (select top ' +
@.start + ' pk from #TempTable order by ' + @.sort + ' , pk) ORDER BY ' + @.sort
+ ', pk) '
EXEC sp_executesql @.sql
SELECT t.priority, a.ArticleID,
isnull(dbo.SF_PUB_GetAuthorByline(a.articleid),"") toptext,
dbo.SF_Pub_GetArticlePath (a.articleid,@.YahooRoot) path, t.created,
COALESCE (A.Title2, A2.Title2, A.Title1, A2.Title1) Title ,
COALESCE(a.subtitle, a2.subtitle,'') subtitle,
isnull(dbo.SF_PUB_GetInhKeywordID(a.articleid, 6913),0) DocType,
COALESCE(A.Synopsis0, A2.Synopsis0, A.Synopsis1, A2.Synopsis1,
A.Synopsis2, A2.Synopsis2, A.body, A2.Body, '') AS Synopsis
FROM #TempTable t
join pub_articles a on t.articleid = a.articleid left join
pub_articles a2 on a.link = a2.articleid
order by
case when @.sort = 'priority' or @.sort = 'priority,created' then
t.priority else '' end,
case when @.sort = 'created' or @.sort = 'priority,created' then
a.posteddate1 elsAgent message code 20046. Cannot use empty object or column
names. Use a single space if necessary.
[12/20/2005 4:44:00 PM]SERVER1.distribution: {call
sp_MSadd_distribution_history(7, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 20, 0x01,
0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 21,
Transaction Seqno = 000275890000022200b100000001, Command ID = 20
Message: Replication-Replication Distribution Subsystem: agent
SERVER1-Keyword DB-SERVER2-7 failed. Cannot use empty object or column names.
Use a single space if necessary.[12/20/2005 4:44:00 PM]SERVER1.distribution:
{call sp_MSadd_repl_alert(3, 7, 21, 14151, ?, 20, N'SERVER1', N'Keyword DB',
N'SERVER2', N'Keyword DB', ?)}
ErrorId = 21, SourceTypeId = 5
ErrorCode = '1038'
ErrorText = 'Cannot use empty object or column names. Use a single space if
necessary.'
[12/20/2005 4:44:00 PM]SERVER1.distribution: {call sp_MSadd_repl_error(21,
0, 5, ?, N'1038', ?)}
Category:SQLSERVER
Source: SERVER2
Number: 1038
Message: Cannot use empty object or column names. Use a single space if
necessary.
[12/20/2005 4:44:00 PM]SERVER2.Keyword DB: exec dbo.sp_MSupdatelastsyncinfo
N'SERVER1',N'Keyword DB', N'', 0, 6, N'Cannot use empty object or column
names. Use a single space if necessary.'
Disconnecting from Subscriber 'SERVER2'
Disconnecting from Distributor 'SERVER1'
Disconnecting from Distributor History 'SERVER1'
Thanks,
- Moshe
Hi Moshe,
You would need to replace the "" in the following code fragment with '':
SELECT t.priority, a.ArticleID,
isnull(dbo.SF_PUB_GetAuthorByline(a.articleid),"") toptext,
To make your procedure compliant with quoted_identifier on setting. You can
search this newsgroup for some of the responses I made in the past on how
this came to be. Note that this translation should be done automatically for
you by the SQL2005 snapshot agent.
HTH
-Raymond
"Moshe" wrote:
> I’m setting up transactional replication, the snapshot generates fine, but I
> get error code 1038 “Cannot use empty object or column names. Use a single
> space if necessary.” when the distribution agent does the push replication
> and reaches a particular SP.
> Something to note is that table names wrapped in brackets (found in SQL
> scripts in the snapshot folder) are being converted to quotation, is that
> caused by SET QUOTED_IDENTIFIER ON?
> so this: FROM [Pub_Articles-Keywords] StaArtK
> is converted to: FROM "Pub_Articles-Keywords" StaArtK
> The following is the detailed error log. Your help is appreciated.
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: SET QUOTED_IDENTIFIER ON
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: drop procedure
> "sp_Search_KeywordBrowser"
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: CREATE PROCEDURE
> "sp_Search_KeywordBrowser"
> @.KeywordRoot int,
> @.ArtRoot int,
> @.start varchar(4) = '0',
> @.quantity varchar(3) = '10',
> @.Sort varchar(25) = 'priority',
> @.YahooRoot int = 0
> AS
>
> if @.yahooroot = 0 set @.YahooRoot = @.ArtRoot
> CREATE TABLE #TempTable
> (PK int IDENTITY,
> ArticleID int,
> Link int,
> priority int,
> created datetime)
> SET NOCOUNT ON
> Insert INTO #TempTable (articleid, link, priority, created)
> SELECT StaArtK.ArticleID ArticleID, A.Link Link,
> StaArtK.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
> FROM "Pub_Articles-Keywords" StaArtK
> JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
> left join Pub_Articles B on a.link = b.articleid
> JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
> StaArtK.ArticleID
> WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
> AND StaArtK.articleid not in (select articleid from
> SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
> keywordid = 272 or keywordid = 2471) and StaArtK.block = 0
>
> Insert INTO #TempTable (articleid, link, priority, created)
> SELECT StaArtK.ArticleID ArticleID, A.Link Link,
> pak.priority, coalesce (b.posteddate1, a.posteddate1, a.created)
> FROM SpeedIndexes.dbo."Pub_Articles-Keywords_static" StaArtK
> JOIN Pub_Articles AS A ON StaArtK.ArticleID = A.ArticleID
> left join Pub_Articles B on a.link = b.articleid
> JOIN SpeedIndexes.dbo.Pub_Articles_Static AS pas ON Pas.BranchID =
> StaArtK.ArticleID
> Join "pub_articles-keywords" pak on StaArtK.nativeid = pak.articleid and
> StaArtK.keywordid = pak.keywordid
> WHERE StaArtK.KeywordID = @.KeywordRoot AND pas.ParentID = @.ArtRoot
> AND StaArtK.articleid not in (select articleid from
> SpeedIndexes.dbo."Pub_Articles-Keywords_Static" where
> keywordid = 272 or keywordid = 2471)
> and StaArtK.nativeid not in (select articleid from #temptable)
>
> update t set t.ArticleID = st.nativeid from #TempTable t join
> speedindexes.dbo."pub_articles-keywords_static" st on t.articleid =
> st.articleid where st.keywordid = 1060 and t.articleid <> st.nativeid
> delete #tempTable where pk not in (select Max(pk) from #tempTable group by
> articleid)
>
> DELETE #TempTable WHERE (Link <> 0 AND Link IN (SELECT ArticleID FROM
> #TempTable))
> --Remove Links that have 'cousin links' in the result set
> --OR (ArticleID NOT IN
> --(SELECT TOP 1 ArticleID
> --FROM #TempTable
> -- GROUP BY Link, ArticleID
> --HAVING COUNT(Link) > 1) AND Link <> 0)
>
> declare @.resultcount int
> set @.resultcount = (select count(*) from #TempTable)
>
> declare @.sql nvarchar(500)
> set @.sql = 'delete #TempTable where articleid not in (SELECT top ' +
> @.quantity + ' articleid FROM #TempTable where pk not in (select top ' +
> @.start + ' pk from #TempTable order by ' + @.sort + ' , pk) ORDER BY ' + @.sort
> + ', pk) '
> EXEC sp_executesql @.sql
> SELECT t.priority, a.ArticleID,
> isnull(dbo.SF_PUB_GetAuthorByline(a.articleid),"") toptext,
> dbo.SF_Pub_GetArticlePath (a.articleid,@.YahooRoot) path, t.created,
> COALESCE (A.Title2, A2.Title2, A.Title1, A2.Title1) Title ,
> COALESCE(a.subtitle, a2.subtitle,'') subtitle,
> isnull(dbo.SF_PUB_GetInhKeywordID(a.articleid, 6913),0) DocType,
> COALESCE(A.Synopsis0, A2.Synopsis0, A.Synopsis1, A2.Synopsis1,
> A.Synopsis2, A2.Synopsis2, A.body, A2.Body, '') AS Synopsis
> FROM #TempTable t
> join pub_articles a on t.articleid = a.articleid left join
> pub_articles a2 on a.link = a2.articleid
> order by
> case when @.sort = 'priority' or @.sort = 'priority,created' then
> t.priority else '' end,
> case when @.sort = 'created' or @.sort = 'priority,created' then
> a.posteddate1 elsAgent message code 20046. Cannot use empty object or column
> names. Use a single space if necessary.
> [12/20/2005 4:44:00 PM]SERVER1.distribution: {call
> sp_MSadd_distribution_history(7, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 20, 0x01,
> 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 21,
> Transaction Seqno = 000275890000022200b100000001, Command ID = 20
> Message: Replication-Replication Distribution Subsystem: agent
> SERVER1-Keyword DB-SERVER2-7 failed. Cannot use empty object or column names.
> Use a single space if necessary.[12/20/2005 4:44:00 PM]SERVER1.distribution:
> {call sp_MSadd_repl_alert(3, 7, 21, 14151, ?, 20, N'SERVER1', N'Keyword DB',
> N'SERVER2', N'Keyword DB', ?)}
> ErrorId = 21, SourceTypeId = 5
> ErrorCode = '1038'
> ErrorText = 'Cannot use empty object or column names. Use a single space if
> necessary.'
> [12/20/2005 4:44:00 PM]SERVER1.distribution: {call sp_MSadd_repl_error(21,
> 0, 5, ?, N'1038', ?)}
> Category:SQLSERVER
> Source: SERVER2
> Number: 1038
> Message: Cannot use empty object or column names. Use a single space if
> necessary.
> [12/20/2005 4:44:00 PM]SERVER2.Keyword DB: exec dbo.sp_MSupdatelastsyncinfo
> N'SERVER1',N'Keyword DB', N'', 0, 6, N'Cannot use empty object or column
> names. Use a single space if necessary.'
> Disconnecting from Subscriber 'SERVER2'
> Disconnecting from Distributor 'SERVER1'
> Disconnecting from Distributor History 'SERVER1'
> Thanks,
> - Moshe
