September 13, 2011 at 7:41 pm
I have been racking my brain on this and I hope it's something simple to fix. I have this first query which works just fine against an Oracle db:
DECLARE @PIDM VARCHAR(6),
@sql_str NVARCHAR(4000),
@DateofAddressChange VARCHAR(30)
SET @DateofAddressChange = '2011-08-24 13:02:24'
SET @PIDM = '640141'
SET @sql_str = 'select SPRCMNT_CMTT_CODE from SPRCMNT
WHERE SPRCMNT_PIDM = ''' + @PIDM + ''' AND SPRCMNT_DATE = '''+ @DateofAddressChange + ''''
SET @sql_str = N'UPDATE OPENQUERY (PPRD_ODP, ''' + REPLACE(@sql_str, '''', '''''')
+ ''') SET SPRCMNT_CMTT_CODE = ''RFN'''
PRINT @sql_str
EXEC (@sql_str)
**The result of the print is this**
UPDATE OPENQUERY (PPRD_ODP, 'select SPRCMNT_CMTT_CODE from SPRCMNT
WHERE SPRCMNT_PIDM = ''640141'' AND SPRCMNT_DATE = ''2011-08-24 13:02:24''') SET SPRCMNT_CMTT_CODE = 'RFN'
That above query works fine, no problem but when I add more columns like this:
DECLARE @PIDM VARCHAR(6),
@DateofAddressChange VARCHAR(30),
@SPRCMNT_TEXT VARCHAR(MAX),
@SPRCMNT_CTYP_CODE VARCHAR(4),
@AdminADUserName VARCHAR(30),
@sql_str nvarchar (4000)
SET @PIDM ='640141'
SET @DateofAddressChange = '2011-08-24 13:02:24'
SET @SPRCMNT_TEXT = 'test'
SET @SPRCMNT_CTYP_CODE = 'RFH'
SET @AdminADUserName = 'jeordonez'
SET @sql_str =
'select SPRCMNT_CMTT_CODE, SPRCMNT_TEXT, SPRCMNT_ORIG_CODE, SPRCMNT_ACTIVITY_DATE, SPRCMNT_CTYP_CODE,
SPRCMNT_CONTACT_DATE, SPRCMNT_USER_ID from SPRCMNT
WHERE SPRCMNT_PIDM = ''' + @PIDM + ''' AND SPRCMNT_DATE = ''' + @DateofAddressChange + ''''
SET @sql_str = N'UPDATE OPENQUERY (PPRD_ODP, ''' + REPLACE(@sql_str, '''', '''''') + ''') SET SPRCMNT_CMTT_CODE = ''RFN'' ,
SPRCMNT_TEXT = ''' + @SPRCMNT_TEXT + ''' ,
SPRCMNT_ORIG_CODE = ''RFS'' ,
SPRCMNT_ACTIVITY_DATE = ''' + GETDATE() + ''' ,
SPRCMNT_CTYP_CODE = ''' + @SPRCMNT_CTYP_CODE + ''' ,
SPRCMNT_CONTACT_DATE = ''' + GETDATE()+ ''' ,
SPRCMNT_USER_ID = ''' + @AdminADUserName+ ''''
PRINT @sql_str
I get 'Conversion failed when converting date and/or time from character string.'
Is it the GetDate() causing the issue?
September 13, 2011 at 7:59 pm
It was the GetDate(). Something so simple.
Winded up declaring a variable and setting them like this:
SET @SPRCMNT_ACTIVITY_DATE = GETDATE()
SET @SPRCMNT_CONTACT_DATE = GETDATE()
Then in the query, changed it to this:
SET @sql_str = N'UPDATE OPENQUERY (PPRD_ODP, ''' + REPLACE(@sql_str, '''', '''''') + ''') SET SPRCMNT_CMTT_CODE = ''RFN'',
SPRCMNT_TEXT = ''' + @SPRCMNT_TEXT + ''' ,
SPRCMNT_ORIG_CODE = ''RFS'' ,
SPRCMNT_ACTIVITY_DATE = ''' + @SPRCMNT_ACTIVITY_DATE + ''' ,
SPRCMNT_CTYP_CODE = ''' + @SPRCMNT_CTYP_CODE + ''' ,
SPRCMNT_CONTACT_DATE = ''' + @SPRCMNT_CONTACT_DATE+ ''' ,
SPRCMNT_USER_ID = ''' + @AdminADUserName+ ''''
October 2, 2011 at 9:02 pm
I get this too sometimes even when there are no date cols anywhere in any select. HIGHLY annoying.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply