[thelist] SQL apostrophe disorder

darren darren at web-bitch.co.uk
Mon Dec 8 06:04:43 CST 2003


On Sunday, December 7, 2003 at 02:17, Joel D Canfield wrote:

JDC> I'm accessing an app's stored procedure to write our HelpDesk requests
JDC> direct to the MSSQL db. I just can't seem to get the apostrophical fix
JDC> right. 

hokay, i think this is it.....

set @columns = 'WO_NUM, PARENTWOID, WOID, TASK, TYPE,
REQUEST, REQDATE, OPENBY, OPENDATE, MODIBY,
MODIDATE, CLSDBY, CLSDDATE, ELAPSETIME, ELAPSEMIN,
PRIORITY, DUEDATE, RESPONS, ASSNDATE, COMPLETED,
HOURS, RATE, CHARGE, WS_NUM, DEPT_NUM,
DEPT, PHONE, PHONE_EXT, LOCATION, DESCRIPT,
WO_TEXT1, WO_TEXT2, WO_TEXT3, WO_TEXT4, WO_TEXT5,
WO_TEXT6, WO_DATE1, WO_DATE2, WO_NUM1, WO_INT1,
NOTE, FT, COMPFLAG, STATUS, AGENTDATE,
AGENTLEVEL, AWS_NUM, LOOKUP1, LOOKUP2, GUIDO,
EMAILADDR, USERID, WOTYPE2, WOTYPE3'

set @values = ' ''apostrophe between one and two 1''''2'', ''Non-PX2'', ''Canfield, Joel'', ''12/2/03 1:12:01 PM'', ''web'',
''12/2/03 1:12:01 PM'', ''web'', ''12/2/03 1:12:01 PM'', NULL, NULL,
NULL, NULL, ''99 - FYI'', ''3/10/04 1:12:01 PM'', ''HelpDesk'',
''12/2/03 1:12:01 PM'', NULL, NULL, ''0'', ''0'',
NULL, NULL, NULL, NULL, NULL,
NULL, ''Repeatability: AlwaysDescription: apostrophe between three and four 3''''4'', NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, ''0'',
NULL, ''Pending'', NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
''Computer'', '''' '

exec @rc = spAddTask @columns, @values, @WorkOrderNumber OUTPUT


you might need to check the proc so that brackets are put around the
columns field in the exec:

   EXEC ('INSERT  INTO TASKS (' + @Columns + ') VALUES (' + @WO2 + @Values + ')' )

as it was throwing an error on my mssql2k without them.  also make sure
@wo2 is being populated correctly.  it may be null, due to the
@workOrderNumber being null.

hth,

darren.




More information about the thelist mailing list