Help! What is wrong here ?

[Guest]

I am trying to pass a filename or file location to a stored proc and am
getting an SqlException. I am using C# in .NET with SQL 2000. I
believe the error is in the line where I set the value but then again what
do I know?

Here is part of my code:
SqlCommand myCommand = new SqlCommand("sp_test", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter myParam = new SqlParameter("@xmlFile", SqlDbType.Text, 1000);
myParam.value = "c:\\test.xml";
myCommand.Parameters.Add(myParam);

myConnection.Open();
try {
myCommand.ExecuteNonQuery();
etc etc

It gets an error at the above execution point with the error:
SqlException: System.Data.SqlClient.SqlError: XML Parsing error: Invalid at
the top level of the document.

I think I need to give it some command where I specify the value to say find
the location of the file......

Why is is so difficult to pass a filename or location of file to a SPROC ?

Please help!
LW

 

[Jon Skeet [C# MVP]]

I am trying to pass a filename or file location to a stored proc and am
getting an SqlException. I am using C# in .NET with SQL 2000. I
believe the error is in the line where I set the value but then again what
do I know?

Here is part of my code:
SqlCommand myCommand = new SqlCommand("sp_test", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter myParam = new SqlParameter("@xmlFile", SqlDbType.Text, 1000);
myParam.value = "c:\\test.xml";
myCommand.Parameters.Add(myParam);

myConnection.Open();
try {
myCommand.ExecuteNonQuery();
etc etc

It gets an error at the above execution point with the error:
SqlException: System.Data.SqlClient.SqlError: XML Parsing error: Invalid at
the top level of the document.

I think I need to give it some command where I specify the value to say find
the location of the file......

Why is is so difficult to pass a filename or location of file to a SPROC ?

Are you sure that the stored procedure is expecting a filename rather
than the actual contents of the file?

What does the stored procedure look like?

 

[Guest]

Jon,
Is my C# code ok ? I have never sent a filename to a stored proc so I believe
the error is how I assign a value to the sqlparam.

The beginning of my stored proc is:

CREATE PROCEDUE dbo.sp_test(@xmlFile text)
AS
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlFile
INSERT INTO etc etc...

Is the reference to @xmlFile correct ? I get the same error no matter
what I assign to sql parameter value
e.g., myParam.value = "C:\\imaginaryfile.xml";

Any ideas ? Appreciate the help!
LW

 

[Jon Skeet [C# MVP]]

Is my C# code ok ? I have never sent a filename to a stored proc so I believe
the error is how I assign a value to the sqlparam.

Nope - it's what you expect the stored procedure to do which is the
problem.

If you look at the docs for sp_xml_preparedocument (and the examples),
they show xmlText as being the text of the document, not a filename.

If you make your parameter have a value which is the actual XML text,
you'll find it works.

 

[Guest]

Turns out the error was really obvious.. In SQL2000 you need to still pass the
contents of a file rather than location. Duh!!

myParam.value = "c:\\test.xml"; <= that is wrong
but
myParam.value=sr.ReadtoEnd(); {where sr is the StreamReader}

cheers,
LW