Solved

Transact SQL Server problem

Posted on 2003-10-29
4
543 Views
Last Modified: 2008-03-06
In SQL Server there are no arrays, so if you want to insert an array into a table you must step through array and to call each step a stored procedure to insert in table each line from array.
How this can be avoided WITHOUT creating extended stored procedures ?
If I try to format the array as string and to pass it to an parameter of the stored procedure then the string cannot be longer than 8000, this is the size of nvarchar, varchar...
If I set the type of a column from an table to binay or image then I can insert into this column images of any size, but if I try to declare a binary or image variable they cannot have more than 8000.
How can I use this - binary/image type (if it can be used) ?
Or if someone have another solution on how to pass an array to a parameter of a stored procedure please reply (but not using extended stored procedures).
Thanks.

0
Comment
Question by:DanLazar
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:SRigney
ID: 9641790
If you are using SQL2000, then you can convert your array to an XML string and pass it to the stored procedure.  Then within the stored procedure you can use OpenXML, which will have access to the values in your xml string like it is a temp table.  This will be able to be used without being an extended stored procedure.

Also you can pass your array into the procedure the same as you do now and you can create the temp table by hand with a create table.  Then you can populate it with the values from your array by looping through them using PATINDEX.  You could then use that temp table in the actual working portion of the stored procedure.  Again without having an extended store procedure.

In order to pass values larger than 8000 characters I think you need to do an appendchunk within your code while adding it to the command object.  I've not had to deal with that though, so the best I can do is point you at a reference to it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdamth01_3.asp
0
 

Author Comment

by:DanLazar
ID: 9642327
Hello , thanks for comment.

Can you send me a little code ? I don't know how to work with SQL XML queries. The xml string will be passed to an parameter of the stored procedure
but the parameters should be nvarchar or varchar and this types allow only 8000 size. What if my string is longer than 8000 ?


0
 
LVL 15

Accepted Solution

by:
SRigney earned 210 total points
ID: 9642401
if your string is longer than 8000 characters then set your stored procedure up to accept a text parameter.

Here is a link for the XML functionality.  I haven't done it in much detail so I can't guide you step by step.  I hope it helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_1cx8.asp

The following one is an article from SQL Server magazine.  It also has code samples that show ntext being used to pass in the xml string which allows for strings up to 2 GB.  But lets face it.  2GB is going to cause you a problem when passing it across a network.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/OvercomingOpenXMLHangups.asp

0
 

Author Comment

by:DanLazar
ID: 9700300
Ok, it works with ntext.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question