SqlParameter limit of 127 characters 1

[PAH]

I am developing a watcher service that picks up an xml file from a directory .
using System.XML I read through the XML to provide a string

XMLStr = XMLStr+"'"+(pda.ReadInnerXml())+"'";

I then pass this string to my SQL Command to provide the Parameter to a stored procedure


cmdnew.Parameters.Add(XMLStr, SqlDbType.NText,);
(the stored procedure uses openxml to load the database)
when I run the application I get the error
The length of the parameter '' all the xml code------''
exceeds the limit of 127 characters

I have tried to split the xml file into 128 character chunks but this will be dificult as the xml file lenth will be variable.

I have also tried various SqlDbTypes all with the same result.

http://msdn.microsoft.com/library/d...f/html/frlrfSystemDataSqlDbTypeClassTopic.asp

Just to check things out I pased the XML as a parameter for the Stored procedure in a query Window and it works so that points to the c# code being the problem

anyone any Ideas??

Most appreciated
Paul

 

[AlexCuse]

Have you tried using nvarchar(max) for your parameter's data type? What other data types have you tried? Also, splitting into 128 char chunks will not help you if the limit is 127 ;-)

If that if that does not work, Maybe you need to add a SQL connection, and assign/execute your command through that? I have had no problem sending very large strings to stored procedures using this method.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PAH]

I have tried
Text (2,147,483,647 characters)
Varchar (8,000 characters)
Ntext (1,073,741,823) characters)

Here is a copy of the code

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Data.SqlClient;
using System.Data;

namespace ReadXml
{
class Program
{
static void Main(string[] args)
// the watcher code goes here
{
try
{
string fpath = Properties.Settings1.Default.Setting;
string tpath = Properties.Settings1.Default.Setting1;
// get names of files in from directory
DirectoryInfo di = new DirectoryInfo(fpath);
FileInfo[] rgFiles = di.GetFiles("*.xml");
foreach (FileInfo fi in rgFiles)
{
//Console.Write( fi.Name + "" + fi.Name );
// File.Copy(fpath + fi.Name, tpath + fi.Name);

//xml stuff
//test xml
string filename = fpath + fi.Name;

XPathDocument document = new XPathDocument(filename);
XPathNavigator navigator = document.CreateNavigator();

// Stream the entire XML document to the XmlReader.

XmlReader pda = navigator.ReadSubtree();
String XMLStr = null;
SqlConnection conn = null;

conn = new SqlConnection(Properties.Settings1.Default.connection);

conn.Open();
SqlCommand cmdnew = new SqlCommand("Handle_XML", conn);
cmdnew.CommandType = CommandType.StoredProcedure;
while (pda.Read())
{
XMLStr = XMLStr+"'"+(pda.ReadInnerXml())+"'";

Console.WriteLine(XMLStr);//Show the xml
}
cmdnew.Parameters.Add(XMLStr, SqlDbType.NText);//add the parameter to the stored procedure

cmdnew.ExecuteNonQuery();
conn.Close();
conn.Dispose();
cmdnew.Dispose();
pda.Close();
Console.Read();
Console.Read();
}
}
catch (Exception ex)
{
Console.WriteLine("Error occured " + ex);
Console.Read();
}
Console.Read();
}
}
}

and the XML FILE is

<?xml version="1.0" encoding="UTF-8"?>

<PPCC UUID="A3BCBA3A-5D56-4458-A3E5-09BA8336EC43">
<UnitID>Paul</UnitID>
<Timesheet>
<StandardHours>8.5</StandardHours>
</Timesheet>
<StaffonJob>
<SOJ>Start of Job</SOJ>
<today>13-03-2007</today>
<WSD>13-Mar-2007</WSD>
<Job>E100</Job>
<jce>Saltcotes house</jce>
<IS1>Paul Hughes</IS1>
<OSI1>Yes</OSI1>
<ISID1>1</ISID1>
<IstId1>01</IstId1>
<scI1>1</scI1>
<IS2>Hugo Hughes</IS2>
<ISID2>2</ISID2>
<IstId2>02</IstId2>
<scI2>1</scI2>
<IStaffCount>2</IStaffCount>
</StaffonJob>
<AgencyStaff>
<AGENCY>AGENCY1; Agency2; </AGENCY>
<Anm>AGENCY1; Agency2; </Anm>
<AID>1; 2; </AID>
<ARN>1; 2; </ARN>
<A1staff1>Burke Michelle</A1staff1>
<O1SA1>Yes</O1SA1>
<A1TSID1>4</A1TSID1>
<Acy1>AGENCY1</Acy1>
<A1SId1>Acy01-01</A1SId1>
<s1c1>1</s1c1>
<A2staff1>Callum Forbes</A2staff1>
<acy2>Agency2</acy2>
<O2S1>Yes</O2S1>
<A2TSID1>5</A2TSID1>
<A2SId1>Acy2-01</A2SId1>
<s2c1>1</s2c1>
<AtaffCount>2</AtaffCount>
<StaffCount>4</StaffCount>
</AgencyStaff>
</PPCC>
not very big as you can see

 

[AlexCuse]

I am sorry, I meant SQL Data Source, not SQL Connection in my post above. Have you tried using one of these, and manipulating the commands?

Not sure waht you're proc is doing, but you can set the commands like this (this may not be spot on, I don't have VS.net at work):

sqlDataSource1.SelectCommand = "exec ProcName '" + xmlString + "'"

Update commands and such work about the same.

AFAIK, there is no limit to the lengh of string you can use.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PAH]

Looks like I got the Answer here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1350344&SiteID=1

Thanks Alex

 

[AlexCuse]

Hey, thanks for posting the answer that you got somewhere else! Lots of people leave their threads unresolved, it is good you posted that link so that if someone finds this thread through the search utility they will get an answer.

Glad you got it working, I think this will be helpful to me in the future as well

Ignorance of certain subjects is a great part of wisdom