Unable to run Pull Subscription 1

[MikeBronner]

Hi All,

I have set up a pull subscription, but everytime I run it, I receive the following error (I have captured this to an output file):

[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_distribution_history(16, 3, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01, 0x00)}
Applied script 'ModalidadVenta_Select_425.sch'
[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_distribution_history(16, 3, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01, 0x00)}
Applied script 'TabAux_Traduccione28f8f28d_429.sch'
[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_distribution_history(16, 3, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01, 0x00)}
Applied script 'TR_ERRORES_433.sch'
[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_distribution_history(16, 3, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01, 0x00)}
Applied script 'HD_Problemas_Productos_437.sch'
[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_distribution_history(16, 3, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01, 0x00)}
Agent message code 20046. Invalid column name '-1'.
[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_distribution_history(16, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 115, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 17,
Transaction Seqno = 0004f6b4000002ad001800000001, Command ID = 115
Message: Replication-Replication Distribution Subsystem: agent MEDUSA-INNET-INNET-TXCORP01\SQL2000-16 failed. Invalid column name '-1'.[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_repl_alert(3, 16, 17, 14151, ?, 115, N'MEDUSA', N'INNET', N'TXCORP01\SQL2000', N'innet', ?)}
ErrorId = 17, SourceTypeId = 5
ErrorCode = '207'
ErrorText = 'Invalid column name '-1'.'
[1/4/2008 8:07:57 AM]MEDUSA.distribution: {call sp_MSadd_repl_error(17, 0, 5, ?, N'207', ?)}

Category:SQLSERVER
Source: TXCORP01\SQL2000
Number: 207
Message: Invalid column name '-1'.
Disconnecting from Subscriber 'TXCORP01\SQL2000'
Disconnecting from Distributor 'MEDUSA'
Disconnecting from Distributor History 'MEDUSA'

I have been absolutely unable to find any help googling the error. Does anyone have any ideas?

Thanks!

Take Care,
Mike

 

[MikeBronner]

As a follow-up: the error references HD_Problemas_Productos_437.sch. Here's the content of that script:

SET QUOTED_IDENTIFIER ON
GO
drop table [HD_Problemas_Productos]
GO
SET ANSI_PADDING OFF
GO

CREATE TABLE [HD_Problemas_Productos] (
	[IdProblemaProducto] [int] NOT NULL ,
	[IdProblema] [int] NOT NULL ,
	[IdProducto] [int] NOT NULL ,
	[FechaBaja] [datetime] NULL 
)
GO

I see no problem with the script looking at the syntax.

Take Care,
Mike

 

[mrdenny]

That script appears to have been applied without error. What is the next script in the folder? I think it's the next script that is the one that failed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MikeBronner]

HI mrdenny,

Thanks for your response. The next file in the sequence seems to be TabAux_Traducciones, and it has the following code:

SET QUOTED_IDENTIFIER ON
GO
drop procedure [TabAux_Traducciones]
GO
CREATE PROCEDURE [TabAux_Traducciones] 
   @TABLA AS VARCHAR(50), -- Nombre de la tabla de idioma
   @PK    AS VARCHAR(50), -- Nombre del campo PK de la tabla de idioma
   @intVALOR AS INTEGER,     -- Valor para filtrar por @PK (-1 no hace filtro)
   @strVALOR AS VARCHAR(10)  -- Valor para filtrar por @PK ('-1' no hace filtro)
AS

----------------------
-- * COMENTARIOS  * --
----------------------
-- El que no haya homogeneidad en cuanto a los campos clave de las tablas según el tipo y
-- tamaño hace que el parámetro inicial @VALOR tenga que ser sustituído por @intVALOR y
-- @strVALOR. Sucede con DIVISAS y otras que el campo clave es alfanumérico.

----------------------------------
-- * DECLARACIÓN DE VARIABLES * --
----------------------------------
DECLARE @SQL AS VARCHAR(1000)

-----------------
-- * PROCESO * --
-----------------
SET @SQL = ''
SET @SQL = @SQL + 'SELECT T.' + @PK + ', T.CodigoIdioma, I.NativeName, T.Traduccion ' + CHAR(13)
SET @SQL = @SQL + 'FROM ' + CHAR(13)
SET @SQL = @SQL + @TABLA + ' T ' + CHAR(13)
SET @SQL = @SQL + 'LEFT OUTER JOIN ' + CHAR(13)
SET @SQL = @SQL + 'IDIOMASISO I ' + CHAR(13)
SET @SQL = @SQL + 'ON T.CODIGOIDIOMA = I.CODIGOIDIOMA ' + CHAR(13)

IF @intVALOR IS NOT NULL 
BEGIN
   IF @intVALOR <> -1
   BEGIN
      SET @SQL = @SQL + 'AND T.' + @PK + ' = ' + CAST(@intVALOR AS VARCHAR) + CHAR(13)
   END
END

IF @strVALOR IS NOT NULL 
BEGIN
   IF @strVALOR <> "-1"
   BEGIN
      SET @SQL = @SQL + 'AND T.' + @PK + ' = ''' + @strVALOR + '''' + CHAR(13)
   END
END

SET @SQL = @SQL + 'ORDER BY I.NATIVENAME, T.TRADUCCION '

EXECUTE(@SQL)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

One thing that immediately jumps out here is that the -1 is in quotes, and not in apostrophes, like SQL literals should be.

Thanks again for any help, I'm stumped as how to resolve this.

Take Care,
Mike

 

[mrdenny]

The QUOTED_IDENTIFIER is ON, so that's ok.

Is there a script with a 441.sch? If so what's in that script?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MikeBronner]

Yes, this is the one.

Take Care,
Mike

 

[mrdenny]

Can you post that script?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MikeBronner]

Sorry for the confusion. The last post with code (above) is the actual script. The file name is TabAux_Traducciones_441.sch.

Take Care,
Mike

 

[MikeBronner]

Looking at this article (http://doc.ddart.net/mssql/sql70/set-set_30.htm), it seems that QUOTED_IDENTIFIERS should be turned off, as otherwise the quoted text will be interpreted as a database object (i.e. column or table).

Take Care,
Mike

 

[MikeBronner]

Following this thought through, I should theoretically then turn QUOTED_IDENTIFIER off in the replication scripts. Is there any way to do that? I have not see a flag that enables this setting under the various replication areas.

Take Care,
Mike

 

[mrdenny]

The settings in the scripts should be the same settings that were used to create the objects on the source machine. Check the stored procedure on the source server and see if that's how it's setup.

You can edit the script, you have to catch it between creating the file and when the file is executed against the second server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MikeBronner]

Thanks so much for your help! You pointed me in the right direction. The actual problem is not with the database settings, but the developers of the database. There are only two "rogue" stored procedures that use the double-quotes (erroneously). I am assuming these are unused stored procedures, or bugs that have gone undetected.

I will submit these issues to the developers and hopefully they will correct the issue. Thanks again! You made my day!

Take Care,
Mike

 

[mrdenny]

That should do the trick then.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog