January 31, 2010 at 10:55 pm
Hello All,
I have a table with 3 fields id(int), request(xml), response(xml). The Id is a primary key in the table. I have two store procedures for each a insert and update. My insert works perfect, I insert a new row into the request field with xml data and return the primary key. Later in my client code, I try to update the table for which the row primary id = previous inserted key.
Here is the problem, my update function is not working at all. The weird part is that sql or my client code is not throwing back any errors. The update field is just empty with no data. I even abstracted this update function and tried to run the same command in sql server directly and the output windows states '(1 row(s) affected)' but when I got to the able and look at the row the 'response' field is empty.
NOTE: The Response message varies in terms of size, there would be multiple xml response message I will be trying to insert into the database.
Any help in this weird issue would be grateful.
UPDATE [TrackerDW].[dbo].[OrchestrationTracker]
SET [ResponseMessage] = '<?xml version="1.0"?>
...
'
WHERE Id = 1
Regards
February 1, 2010 at 9:49 am
The main reasons why I think you didn't get an answer yet:
The "sample data" you posted are obviously so huge that it takes forever to open that thread. I strongly recommend to either attach it as a file or to reduce it to the amount required to show what issues you're struggling with. (The latter is preferred...)
As a second issue your sample data should not / must not include any real life data!!
Edit: comments removed since the issue is nothing to make fun of...
February 1, 2010 at 10:50 am
I have removed the sample data.
As mentioned, this is entirely too much data. You can show some sample data, maybe 5-10 XML rows and that would be enough. Using real data is also not a good idea. IF you are doing this for a client, I am sure they would not be thrilled to see their data posted on a public site. especially contact information.
February 1, 2010 at 2:50 pm
Your last comment might be the key... You won't be able to insert multiple records with the same Id, as then you'd be violating the primary key. If you update that record each time you have a new response message, then the value you see in the response message field will only be the value that was last used in an update. If you're objective is to accumulate new records - one for each response, then Id can be a foreign key, but not a primary key. If you're objective is to concatenate the responses, then you may need some kind of XML delimiter to be able to determine where each response begins and ends, and a simple update would have to look like:
DECLARE @DELIM_START varchar(10), @DELIM_END varchar(10)
SET @DELIM_START = ''<response>'
SET @DELIM_END = ''</response>'
UPDATE [TrackerDW].[dbo].[OrchestrationTracker]
SET [ResponseMessage] = [ResponseMessage] + @DELIM_START + '<?xml version="1.0"?> + @DELIM_END
...
'
WHERE Id = 1
How you choose to deal with that kind of encapsulation is then up to you. If this isn't addressing the problem, let us know...
Steve
(aka sgmunson)
:-):-):-)
VLead Consulting (1/31/2010)
Hello All,I have a table with 3 fields id(int), request(xml), response(xml). The Id is a primary key in the table. I have two store procedures for each a insert and update. My insert works perfect, I insert a new row into the request field with xml data and return the primary key. Later in my client code, I try to update the table for which the row primary id = previous inserted key.
Here is the problem, my update function is not working at all. The weird part is that sql or my client code is not throwing back any errors. The update field is just empty with no data. I even abstracted this update function and tried to run the same command in sql server directly and the output windows states '(1 row(s) affected)' but when I got to the able and look at the row the 'response' field is empty.
NOTE: The Response message varies in terms of size, there would be multiple xml response message I will be trying to insert into the database.
Any help in this weird issue would be grateful.
UPDATE [TrackerDW].[dbo].[OrchestrationTracker]
SET [ResponseMessage] = '<?xml version="1.0"?>
...
'
WHERE Id = 1
Regards
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply