Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Sending multiple rows to the Database from an Application: Part I Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 4:56 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, Visits: 198
Comments posted to this topic are about the item Sending multiple rows to the Database from an Application: Part I
Post #769023
Posted Tuesday, August 18, 2009 2:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:03 PM
Points: 17, Visits: 129
Good to see an article on a tricky and quite important piece of code.

Although, with SQL Server 2008 around, I am surprised that you did not mention the Table user defined type and the MERGE statement?
Post #772466
Posted Tuesday, August 18, 2009 2:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, Visits: 198
Thanks Daniel,

Although, with SQL Server 2008 around, I am surprised that you did not mention the Table user defined type and the MERGE statement?


Actually this is based on SQL Server 2005. Yes, now (in Sql Server 2008) we have a feature where we can pass table object to the SP.
Post #772476
Posted Tuesday, August 18, 2009 2:48 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:40 PM
Points: 682, Visits: 893
The technical issues are well thought out but I hope that you won't mind if I point out a couple of key spelling mistakes.

The word is delimiter and not delimeter, delimited and not delimeted. (The words are derived from the word 'limit' meaning boundary.)

You were introduced as a new author so 'welcome' and you have obviously taken care with the article.

I have only ever mastered one language so I'm full of respect for you if English is not your 'first' language. Well done.

Martin




Post #772489
Posted Tuesday, August 18, 2009 4:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:08 AM
Points: 77, Visits: 169
Thank you for exploring an approach that I hadn't considered. I actually advised a colleague this month that there isn't a practical way to send bulk rows to SQL Server 2005 - he had around 100 rows to put in. But I'm particularly excited about the XML approach.

We have a 3-tier application design (rhymes with "beer"), with desktop and a server both running Java application code, as well as some 2-tier where there's just a desktop program (SQL Server is the other tier). The server is right next to the SQL Server on a fast server-room network (could even be blades), and I assume that just submitting a series of INSERT... VALUES (...) statements, with or without parameterization of the statement, is almost the best we can do. BCP and BULK INSERT won't really do, although I suppose we could do those from our Java server.

The great trap that I see with delimited data is where you want to use a delimiter character as a character in a data field. Again, XML, which is really just a sophisticated form of delimited data, comes with tools to handle that.

Post #772549
Posted Tuesday, August 18, 2009 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 20, 2014 1:52 PM
Points: 49, Visits: 139
The table-valued function [dbo].[TableFormDelimetedStringWithoutNumberList] will choke on lists longer than 100 elements. Here's the error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


Using OPTION(MAXRECURSION xxx) is not allowed within a UDF, so the numbered list seems to be the only alternative for large lists. Am I missing something?
Post #772645
Posted Tuesday, August 18, 2009 7:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, January 6, 2011 7:01 AM
Points: 80, Visits: 92
Good approach given the lack of alternatives, I've used the XML approach before and it can really provide a huge performance boost - but how would you handle error cases - say I'm whacking in 1000 rows and the 999th insert fails for some reason - what would you think would be the best way to get information back about the specific failure to the user?

Looking forward to part II!
Post #772659
Posted Tuesday, August 18, 2009 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:08 AM
Points: 77, Visits: 169
I think the goal is to make it a set-based operation, so by SQL standard the error message should be as uninformative as possible ("String or binary data would be truncated"), whilst also throwing blame directly on the application developer!
Post #772693
Posted Tuesday, August 18, 2009 7:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 207, Visits: 210
I have used the XML approach to do some integration between databases. I would pass to an SP in a different database a set of reference numbers as an XML param and then join to that XML to the data in that database. Although this worked fine, when it started getting up to over 700 - 1000 records, I saw a major performance problem. It was very slow. Maybe it was my implementation, but I ended up having to rewrite it to eliminate the XML approach (which is what I should have done the first time, but I wanted to try out the XML approach). Just make sure you test your implementation using more data than you ever think you will need to process.


Post #772695
Posted Tuesday, August 18, 2009 8:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 22, 2010 8:24 AM
Points: 17, Visits: 17
i use a pretty same script to send multiple rows in sql server 2005 with charindex but i have an issue... and was that CHARINDEX just accept 8000 chars and i was sending sometimes more than 10 textareas with nvarchar(max) :__

i was thinking about the xml approach
Post #772746
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse