September 16, 2008 at 2:19 am
Hi,
I have a sql server 2005 database on a DMZ server, where gprs data is stored. there is only one table with one column (varchar(max)), just to save information from user interface (a string between 200 and 10000 characters). one string, sent from the interface, can contain several records.
a string can contain more than one record, one record consists of 18 coma separated numbers
i implemented the following steps:
1) interface calls a proc which inserts the whole string in one column
2) there is a function, which counts the coma an returns position index after 18 comas and the last value
2) trigger is fired, which separates data in several records (with a while loop, calling the function mentioned above). in the loop a procedure is called, which is located on a sql2000 server.
while f_oneRecord(@data) <> 0
begin
set @rec = LEFT(@data,f_oneRecord(@data))
exec [linkedServer].dbo.pTransfer @rec
set @data = substring(@data, len(@rec)+1, len(@data))
end
3) the proc pTransfer checks what kind of message it is and calls another proc, which then seperates the string in 18 columns and inserts them in a table on the sql 2000 server.
the following problem now occurs:
on my test environment this works nicely, but on the real server only a few records are stored in sql2005 server and NO data is sent to sql2000 server.
if i tested it with one hand written string, it works. but when data is sent automatically, it doesn't. there is about 1 record a second from the user interface.
can this be a performance problem because of the linked server or the amount of data?
glad about any ideas,
sue
Susanne
September 16, 2008 at 7:07 am
There could be several issues. Is your UI program getting an error messages? Are you guaranteed to have connectivity between the 2005 server and the linked 2000 server?
My best guess is that you are experiencing connectivity issues between the source and the linked server which is causing the trigger to fail and the entire transaction to rollback.
I recommend against using linked servers or anything else that relies on resources outside the database within triggers for this reason. If I were designing your system I would add a processed flag or date to the table on the 2005 server and have service or job that processes the data asynchronously.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2008 at 8:12 am
thank you for your ideas.
i don't think that there are connectivity problems in the meaning of lost connectivity, because both sql servers are in the same network and it always works, if i try it with an insert in the management studio. the interface has an error handler, but no error occurs.
do you think it is possible that the trigger lasts too long so that the proc is not finished until the next record is sent from the interface? can this interrupt the insert procedure?
Susanne
September 16, 2008 at 8:22 am
Sure that is possible. Just to reiterate, I recommend doing something asynchronously with a job or windows service instead of a trigger. Even if you have to add a queue table to the 2005 server I think you'd be better off. It is dangerous to count on network connectivity or really any outside resource within a trigger. You may event be able to use Service Broker, but I have no experience with that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2008 at 8:33 am
While I agree with Jack (especially about Service Broker, which can address both the synchronus and the remote server issues), I think that your first step here should be to setup a profiler trace and watch what is happening when the UI is trying to insert a record.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 8:47 am
i have no idea about service broker... 😀
do you think i should try to run a job every minute to synchronise data with the sql2000 server?
Susanne
September 16, 2008 at 9:39 am
I would go with the job. I agree with Barry that using Profiler would help you identify what is currently happening, I also believe that the current design is flawed, which I think Barry agrees with, and should be changed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2008 at 10:11 am
Agreed Jack. Generally, you only want to use triggers when there is no other reasonable and reliable way to do something. If polling is workable for you, then use polling.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply