| Author |
Message
|
| szab |
 Posted: Tue Aug 04, 2009 5:10 am Post subject: Timestamp inserted into SQLServer2005 fails |
 |
|
Novice
Joined: 01 May 2008
Posts: 14
|
Hello!
I got a weird problem. I'm gonna tell everything that can be important to know.
- Everything worked fine until last week when we installed the SP3 for SQLServer2005.
- We have WMB6.1, Windows2003Server.
- And also reinstall the BrokerToolkit on a different drive,because the SQLServer SP3 needed more space, so we deleted the Toolkit from that drive:) ( Workspaces are on another drive).
There's a flow, that gets a message via HTTP Input node, an XML with TIMESTAMPs.
We convert the message via ASBITSTREAM, then insert this bitstream into an xml datatype field in MSSQL( We call a stored procedure for that insert)
Call InsertMessage(CURRENT_TIMESTAMP,ASBITSTREAM(InputRoot.XMLNSC CCSID 912));
The symptoms:
Insert fails with this exception:
| Code: |
| [Microsoft][SQL Native Client][SQL Server]Unclosed quotation mark after the character string '2009-08-03T17:28:24.364)'. |
| Code: |
| [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2009-08-03T17:28:24.364)'. |
When I'm using the DEBUGGER, I can see that the TIMESTAMP is correct...
I have another similar flow, but in that the exception points to a value of an XML field. As below:
| Code: |
| [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near 'EBK)'. |
Any idea why or how that close bracket occurs at the end of TIMESTAMP via calling that stored procedure for MSSQL?
It's very annoying...  |
|
| Back to top |
|
 |
| AkankshA |
| Posted: Wed Aug 05, 2009 8:31 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Can you also paste the code of "InsertMessage' procedure here ?
I assume this is a DB stored procedure ???
did u debug that ??? is that trimming the date ?
_________________
Cheers |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Thu Aug 06, 2009 2:15 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| AkankshA wrote: |
did u debug that |
A user trace will show exactly and beyond doubt what your flow is sending to the stored proc. If you dont have a ')' at this point.... its PMR time as it must be being added by the driver / DB.
Last edited by WMBDEV1 on Thu Aug 06, 2009 2:59 am; edited 1 time in total |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Thu Aug 06, 2009 2:46 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
whats the data type that you have used in SQL side for storing the XML Date Time value
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| szab |
| Posted: Thu Aug 06, 2009 6:23 am Post subject: |
|
|
Novice
Joined: 01 May 2008
Posts: 14
|
okay. The problem was that somehow the procedure trimmed too many characters from the message...
It's funny that SQLServer2005 doesn't have a DEBUGer( for stored procedures), and you have to use VisualStudio for that. ( but unfortunately the one installed with the MSSQL is not right for that job )
So I had to insert every declared parameter that i thought could be important to another table. Then I found which line made this messy exception.
Sorry to bother you all:) |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Thu Aug 06, 2009 9:16 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| szab wrote: |
okay. The problem was that somehow the procedure trimmed too many characters from the message...
|
but it didnt do this before you applied SP3....
| Quote: |
- Everything worked fine until last week when we installed the SP3 for SQLServer2005.
|
Most odd and this probably led me up the garden path! |
|
| Back to top |
|
|
|
|
