sql server replication: This error likely came from a query\stored procedure executed by the
snapshot agent. It would be great if you can use SQL Profiler to find out
what the failing command is and perhaps that will allow us to narrow down
the root cause further. I am guessing you somehow wind up with inconsistent
meta data at your publisher (missing rowguidcol?) or our code doesn't handle
certain special characters (e.g. spaces) in your column\table names.

-Raymond

[quoted text, click to view]

Can you script out your publication script and post it here? Also, if
possible script out the relevant articles and post them here as well.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Hi,

SQL 2000 (SP3a)
Windows 2003

Getting the following error when running a Snapshot agent which
replicates 27 tables (no metadata) to a database on the same server
(Tandoori).

Cannot use empty object or column names. Use a single space if
necessary.
(Source: TANDOORI (Data source); Error number: 1038)
---------------------------------------------------------------------------------------------------------------


I've added logging to the Agent step as per the Microsoft article but
that tells me nothing. I've also eyeballed all the 27 tables and all
looks to be in order.

Anyone got any suggestions about this?

Thanks in advance.

Dave.


This is the agent log.

Microsoft SQL Server Snapshot Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
TANDOORI-XPS-2-SQL-XPS2SQL-BAP-10

Connecting to Distributor 'TANDOORI'
Connecting to Publisher 'TANDOORI.XPS-2-SQL'

Server:
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[2/6/2007 1:58:06 PM]TANDOORI.XPS-2-SQL: sp_MSgetversion
Initializing the publication 'XPS2SQL-BAP'
Fetching the articles from the publisher.
Generating schema script for article 'INVOICE_DESPATCH_NOTE'

*** [Article:'INVOICE_DESPATCH_NOTE'] Time generating all schema
scripts: 891 (ms) ***

Generating schema script for article 'INVOICE_HEADER'

*** [Article:'INVOICE_HEADER'] Time generating all schema scripts: 359
(ms) ***

Generating schema script for article 'INVOICE_LINE'

*** [Article:'INVOICE_LINE'] Time generating all schema scripts: 406
(ms) ***

Generating schema script for article 'PRICE_GROUP'

*** [Article:'PRICE_GROUP'] Time generating all schema scripts: 203
(ms) ***

Generating schema script for article 'PRODUCT_COMPONENT'

*** [Article:'PRODUCT_COMPONENT'] Time generating all schema scripts:
187 (ms) ***

Generating schema script for article 'PRODUCT_DESPATCH_METHOD'

*** [Article:'PRODUCT_DESPATCH_METHOD'] Time generating all schema
scripts: 203 (ms) ***

Generating schema script for article 'PRODUCT_GROUP'

*** [Article:'PRODUCT_GROUP'] Time generating all schema scripts: 250
(ms) ***

Generating schema script for article 'PRODUCT_LINK'

*** [Article:'PRODUCT_LINK'] Time generating all schema scripts: 187
(ms) ***

Generating schema script for article 'PRODUCT_PRICE'

*** [Article:'PRODUCT_PRICE'] Time generating all schema scripts: 235
(ms) ***

Generating schema script for article 'PRODUCT_PROFILE_CODE'

*** [Article:'PRODUCT_PROFILE_CODE'] Time generating all schema
scripts: 187 (ms) ***

Generating schema script for article 'PRODUCT_TAX'

*** [Article:'PRODUCT_TAX'] Time generating all schema scripts: 204
(ms) ***

Generating schema script for article 'PRODUCT_YEAR'

*** [Article:'PRODUCT_YEAR'] Time generating all schema scripts: 187
(ms) ***

Generating schema script for article 'PUBLISHER'

*** [Article:'PUBLISHER'] Time generating all schema scripts: 250 (ms)
***

Generating schema script for article 'PRODUCT'

*** [Article:'PRODUCT'] Time generating all schema scripts: 344 (ms)
***

Generating schema script for article 'PRODUCT_INCREMENT'

*** [Article:'PRODUCT_INCREMENT'] Time generating all schema scripts:
250 (ms) ***

Generating schema script for article 'SALES_ANALYSIS'

*** [Article:'SALES_ANALYSIS'] Time generating all schema scripts: 312
(ms) ***

Generating schema script for article 'STOCK_ADJUSTMENT'

*** [Article:'STOCK_ADJUSTMENT'] Time generating all schema scripts:
329 (ms) ***

Generating schema script for article 'STOCK_ANALYSIS'

*** [Article:'STOCK_ANALYSIS'] Time generating all schema scripts: 203
(ms) ***

Generating schema script for article 'STOCK_AT_LOCATION'

*** [Article:'STOCK_AT_LOCATION'] Time generating all schema scripts:
203 (ms) ***

Generating schema script for article 'SYSTEM_STATUS'

*** [Article:'SYSTEM_STATUS'] Time generating all schema scripts: 203
(ms) ***

Generating schema script for article 'TITLE'

*** [Article:'TITLE'] Time generating all schema scripts: 250 (ms) ***

Generating schema script for article 'ZZZ'

*** [Article:'ZZZ'] Time generating all schema scripts: 188 (ms) ***

Generating schema script for article 'CUSTOMER_QUERY'

*** [Article:'CUSTOMER_QUERY'] Time generating all schema scripts: 297
(ms) ***

Generating schema script for article 'AAA'

*** [Article:'AAA'] Time generating all schema scripts: 203 (ms) ***

Generating schema script for article 'CUSTOMER_DELIVERABLE'

*** [Article:'CUSTOMER_DELIVERABLE'] Time generating all schema
scripts: 266 (ms) ***

Generating schema script for article 'CUSTOMER_PUBLISHER'

*** [Article:'CUSTOMER_PUBLISHER'] Time generating all schema scripts:
219 (ms) ***

Generating schema script for article 'DESPATCH_AREA_METHOD'

*** [Article:'DESPATCH_AREA_METHOD'] Time generating all schema
scripts: 203 (ms) ***

Generating snapshots of the articles in the publication.

*** [Publication:'XPS2SQL-BAP'] Total snapshot generation time
excluding publication setup: 10047 (ms) ***

SourceTypeId = 5 SourceName = TANDOORI ErrorCode = 1038 ErrorText =
Cannot use empty object or column names. Use a single space if
necessary.
Cannot use empty object or column names. Use a single space if
necessary.
Disconnecting from Publisher 'TANDOORI'

Here is the publication script which contains the articles.



-- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'XPS-2-SQL', @optname =
N'publish', @value = N'true'
GO

use [XPS-2-SQL]
GO

-- Adding the snapshot publication
exec sp_addpublication @publication = N'XPS2SQL-BAP', @restricted =
N'false', @sync_method = N'native', @repl_freq = N'snapshot',
@description = N'Snapshot publication of XPS-2-SQL database from
Publisher TANDOORI.', @status = N'active', @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'true',
@enabled_for_internet = N'false', @independent_agent = N'true',
@immediate_sync = N'true', @allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @retention = 0, @allow_queued_tran =
N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot =
N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts =
N'false', @allow_subscription_copy = N'false',
@add_to_active_directory = N'false'
exec sp_addpublication_snapshot @publication =
N'XPS2SQL-BAP',@frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 8, @frequency_subday_interval = 1,
@active_start_date = 0, @active_end_date = 0,
@active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@snapshot_job_name = N'TANDOORI-XPS-2-SQL-XPS2SQL-BAP-10'
GO

exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'EXTENZA-W1\Administrator'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'EXTENZA-W1\GrayD'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'EXTENZA-W1\IngramC'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'EXTENZA-W1\MeddA'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'EXTENZA-W1\TandooriMail'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'EXTENZA-W1\TortoiseJ'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'EXTENZA-W1\WhiteM'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'sa'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'SQSDBA'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'TURPIN\Administrator'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'TURPIN\TandooriMail'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'TURPIN-BG\ITServ'
GO
exec sp_grant_publication_access @publication = N'XPS2SQL-BAP', @login
= N'TURPIN-BG\TandooriMail'
GO

-- Adding the snapshot articles
exec sp_addarticle @publication = N'XPS2SQL-BAP', @article = N'AAA',
@source_owner = N'dbo', @source_object = N'AAA', @destination_table =
N'AAA', @type = N'logbased', @creation_script = null, @description =
null, @pre_creation_cmd = N'drop', @schema_option =
0x00000000000000F1, @status = 0, @vertical_partition = N'false',
@ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter =
null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_DELIVERABLE', @source_owner = N'dbo', @source_object =
N'CUSTOMER_DELIVERABLE', @destination_table = N'CUSTOMER_DELIVERABLE',
@type = N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1,
@status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL',
@del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object =
null, @auto_identity_range = N'false', @filter_clause = N'company_code
= ''BAP'''
GO

-- Adding the article filter
EXEC sp_articlefilter @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_DELIVERABLE', @filter_name = null, @filter_clause =
N'company_code = ''BAP'''
GO

-- Adding the article synchronization object
EXEC sp_articleview @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_DELIVERABLE', @view_name =
N'SYNC_XPS2SQL-BAP_CUSTOMER_DELIVERABLE', @filter_clause =
N'company_code = ''BAP'''
GO

exec sp_addarticle @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_PUBLISHER', @source_owner = N'dbo', @source_object =
N'CUSTOMER_PUBLISHER', @destination_table = N'CUSTOMER_PUBLISHER',
@type = N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1,
@status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL',
@del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object =
null, @auto_identity_range = N'false', @filter_clause = N'company_code
= ''BAP'''
GO

-- Adding the article filter
EXEC sp_articlefilter @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_PUBLISHER', @filter_name = null, @filter_clause =
N'company_code = ''BAP'''
GO

-- Adding the article synchronization object
EXEC sp_articleview @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_PUBLISHER', @view_name =
N'SYNC_XPS2SQL-BAP_CUSTOMER_PUBLISHER', @filter_clause =
N'company_code = ''BAP'''
GO

exec sp_addarticle @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_QUERY', @source_owner = N'dbo', @source_object =
N'CUSTOMER_QUERY', @destination_table = N'CUSTOMER_QUERY', @type =
N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1,
@status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL',
@del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object =
null, @auto_identity_range = N'false', @filter_clause = N'company_code
= ''BAP'''
GO

-- Adding the article filter
EXEC sp_articlefilter @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_QUERY', @filter_name = null, @filter_clause =
N'company_code = ''BAP'''
GO

-- Adding the article synchronization object
EXEC sp_articleview @publication = N'XPS2SQL-BAP', @article =
N'CUSTOMER_QUERY', @view_name = N'SYNC_XPS2SQL-BAP_CUSTOMER_QUERY',
@filter_clause = N'company_code = ''BAP'''
GO

exec sp_addarticle @publication = N'XPS2SQL-BAP', @article =
N'DESPATCH_AREA_METHOD', @source_owner = N'dbo', @source_object =
N'DESPATCH_AREA_METHOD', @destination_table = N'DESPATCH_AREA_METHOD',
@type = N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1,
@status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL',
@del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object =
null, @auto_identity_range = N'false', @filter_clause = N'product_code
in (select p.product_code from product p where p.company_code =
''BAP'')'
GO

Hi all,

The Distribution tab showed agents for subscriptions which had been
removed a while ago (probably not correctly). because of this and the
error in my original post I decided to drop all pubs/subs, disable
replication and start from scratch (using scripts). Fingers crossed
all seems to be ok now.

Thanks to all who responded.

Dave




On Tue, 06 Feb 2007 14:07:59 +0000, David Gray
[quoted text, click to view]