Solved

Transact SQL Server problem

Posted on 2003-10-29
4
537 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A short article about a problem I had getting the GPS LocationListener working.
This is an explanation of a simple data model to help parse a JSON feed
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now