August 16, 2004 at 5:24 am
I'm doing snapshot replication of an entire database (all tables and all stored procedures). The problem is one of the stored procedures fails to load into the target database. The SCH file (downloaded onto target via FTP) is appended below. What's happening is I'm getting a bunch of invalid column errors:
Distribution agent error:
Invalid column: 'SELECT * FROM TB_Job WHERE Job_Close > '
Invalid column: ''
Invalid column: 'And Job_Title LIKE '%'
Invalid column: '%'
Invalid column: 'OR Job_Desc LIKE '%'
When I cut-n-paste the code and try to load it in manually (via Enterprise Manager) it works fine. Anyone know what's going on here?
Regards PLST
- - - - - - - - - - - - - - - -
SET QUOTED_IDENTIFIER ON
GO
drop procedure [sp_Search_Jobs]
GO
CREATE PROCEDURE [sp_Search_Jobs]
(
@TheKeyword varchar(250)=null
)
AS
DECLARE @TheSQl varchar(5000)
SET @TheSQl = "SELECT * FROM TB_Job WHERE Job_Close > '" + convert(varchar(11),getdate()) + "' "
IF @TheKeyword <> null
BEGIN
SET @TheSQl = @TheSQl + " And Job_Title LIKE '%" + Convert(varchar,@TheKeyword) + "%' "
SET @TheSQl = @TheSQl + " OR Job_Desc LIKE '%" + Convert(varchar,@TheKeyword) + "%' "
SET @TheSQl = @TheSQl + " OR Job_Contact LIKE '%" + Convert(varchar,@TheKeyword) + "%' "
SET @TheSQl = @TheSQl + " OR Job_Ref LIKE '%" + Convert(varchar,@TheKeyword) + "%' "
SET @TheSQl = @TheSQl + " OR Job_Location LIKE '%" + Convert(varchar,@TheKeyword) + "%' "
END
SET @TheSQl = @TheSQl + "ORDER BY Job_Close DESC"
exec (@TheSQl)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
August 16, 2004 at 11:21 am
Try adding set quoted_identifier off at the top, or change the double quotes to two single quotes. The @TheKeyword <> null is bad, should be is not null.
August 19, 2004 at 8:33 am
Thanks. I changed the quotes (single quotes to two single quotes, then double-quotes to single quotes), and changed the null statement ... and it worked perfectly. 🙂
Regards PLST
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply