November 27, 2014 at 5:13 am
OK I got a very complex ssis package that needs to import data into tables of certain client system
In total 7 of these packages exist,all work fine except this one which fails on run on the error
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Incorrect syntax near the keyword 'SET'.".
Which is on ole db command task that executes the following stored procedure:
USE [LZ_LotusNotes]
GO
/****** Object: StoredProcedure [dbo].[sp_Update_FAM_Dynamic_SafetyFirst] Script Date: 11/27/2014 11:27:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_Update_FAM_Dynamic_SafetyFirst]
@TABLE nvarchar(1000),
@ServiceOrderID nvarchar(500),
@Question nvarchar(500),
@CheckedIndicator nvarchar(500),
@Comment nvarchar(500),
@CreatedDate nvarchar(500),
@ModifiedDate nvarchar(500),
@FormattedCloseDate nvarchar(500)
as
DECLARE @sql NVARCHAR(max);
set @sql = N'UPDATE LZ_LotusNotes.' + @TABLE
set @sql = @sql + ' SET Question = ''' + @Question + ''''
set @sql = @sql + ',CheckedIndicator = ''' + @CheckedIndicator + ''''
set @sql = @sql + ',Comment = ''' + @Comment + ''''
set @sql = @sql + ',CreatedDate = ''' + @CreatedDate + ''''
set @sql = @sql + ',ModifiedDate = ''' + @ModifiedDate + ''''
set @sql = @sql + ',FormattedCloseDate = ''' + @FormattedCloseDate + ''''
set @sql = @sql + ' WHERE ServiceOrderID = ''' + @ServiceOrderID + ''''
set @sql = @sql + ' AND Archive = 0;'
--print '---------------------------------------------------------------------------------------------'
--print @sql
--print '---------------------------------------------------------------------------------------------'
--execute sp_executesql @sql
--insert into SafetyFirst_UpdateStatement values (@SQL)
The error only occurs if "execute sp_executesql @sql" is used
Example of a update statement
UPDATE LZ_LotusNotes.dbo.SafetyFirst_NFA SET Question = 'Do I know the nearest emergency exits/ escape routes and assembly points?',CheckedIndicator = 'OK',Comment = '-',CreatedDate = '2014-07-09 10:11:08',ModifiedDate = '2014-11-27 08:26:53',FormattedCloseDate = '20141126' WHERE ServiceOrderID = '1A911C' AND Archive = 0;
So anyone got an idea
November 27, 2014 at 8:55 am
November 28, 2014 at 2:23 am
quackhandle1975 (11/27/2014)
Hi Resender, to get more help you may need to repost (for the t-sql code)http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
qh
I just had a look at my post & I saw a mistake,instead of code="sql", I used quote=sql
dbalmf (11/27/2014)
Hi resender,Try using --execute sp_executesql (@SQL)
Rather than --execute sp_executesql @sql
Ta
David
Tried that didn't work
Eventually I found that changing the ole db connection manager solved the issue,still it was not a helpful error message as it kept sending me in the wrong
direction.
November 28, 2014 at 4:03 am
...still it was not a helpful error message as it kept sending me in the wrong
direction.
Welcome to Microsoft error reporting. 😉
qh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply