-2

I have written a stored procedure that returns XML. When I run the stored procedure in SQL Server management studio, it returns the xml as it should but, when I run it in my C# code, no xml is returned. My code looks like this:

using (SqlConnection conn = new SqlConnection(connStr)) {
    using (SqlCommand cmnd = new SqlCommand("dbo.spMyStoredProcXML", conn)) {
        cmnd.CommandType = CommandType.StoredProcedure;
        cmnd.Parameters.Add("@Param1", SqlDbType.VarChar, 50);
        cmnd.Parameters["@Param1"].Value = "Some value";
        //more parameters...
        conn.Open();
        XmlReader xrdr = cmnd.ExecuteXmlReader();
        //do stuff...
    }
}

I have checked the permissions for the user that I created, and they look like this: At the server level, in the Login Properties dialog, under User Mapping, I have mapped the user to the correct database, and "checked" the public, db_datareader, db_datawriter, and then even added them to db_owner roles.

At the database level, I have also gone into the "Securables" section of the Database User dialog and explicitly given the user Grant and With Grant permissions to Execute the stored procedure in question. I have also explicitly selected "Grant" permissions for the user to Delete, Insert, Select, and Update all the tables in the database.

Still, when the "XmlReader xrdr = cmnd.ExecuteXmlReader();" line runs, the xrdr variable is empty. No error is reported. Can someone give suggestions as to what else I need to examine?

Thanks in advance for any help you can provide.

Here is a simplified version of the stored procedure (which works perfectly in SQL Server Management Studio):

CREATE PROCEDURE [dbo].[spGetXML]
(@param1 varchar(50), @param2 varchar(1))

AS BEGIN

SET NOCOUNT ON;
with xmlnamespaces ('http://schemas.xmlsoap.org/soap/encoding/' as SOAP_ENC)
select 
    d.Col1,
    d.Col2,
from MyTable d
where d.Col1 = @param1 and 
      d.Col2 = @param2
FOR XML PATH('ObjectName'), root('DOCUMENT'), type

END

rogdawg
  • 687
  • 3
  • 11
  • 33
  • 1
    Do you have two databases? And are possibly accessing the wrong one? – Steve Wellens Aug 10 '16 at 14:35
  • 1
    Can you post the procedure? Without something to go on this is pretty challenging. – Sean Lange Aug 10 '16 at 14:36
  • 3
    Run the Sql Profiler. Is the query being executed? Is it returning the proper values? – stuartd Aug 10 '16 at 14:40
  • What happens if you change ExecuteXmlReader() to ExecuteReader()? – sr28 Aug 10 '16 at 14:41
  • What are you doing with the Reader in `//do stuff...` – HardCode Aug 10 '16 at 14:42
  • Can you confirm that connStr has the value you would expect? – sr28 Aug 10 '16 at 14:44
  • We need to see the procedure. It may be that what it returns is not seen as XML by .net. Are you performing any other actions in the stored proc which might return a result to the client prior to the XML. Use SET NOCOUNT ON in the proc to ensure that .net isnt interpreting the rowcount to be the result. Also, after you return the reader, does string s=reader.ReadOuterXML() return anything? – Jonathan Shields Aug 10 '16 at 15:06
  • ok, I added a simplified version of the stored procedure to my post. I have verified that the connection string is connecting to the correct database. The connection string looks correct, and I get no connection-related errors. I am passing in the User Id and Password for the user I created, and it connects with no problem. – rogdawg Aug 10 '16 at 15:08
  • string s = xrdr.ReadOuterXML() returns an empty string. – rogdawg Aug 10 '16 at 15:14
  • I created a version of the stored procedure that did not return XML, it just returns a normal result set. Then I switched my C# code to use a data adapter to fill a dataset with the stored procedures results. That works as it should! So, it doesn't appear to be a connection/permissions problem. It appears to be a problem with the way I am getting XML data specifically. I will update as I find out more. – rogdawg Aug 10 '16 at 16:30
  • That's it...I was accessing the data incorrectly. I was trying to immediately access the data by using xrdr.ReadOuterXML() but, you have to wrap that in a statement like "while (xrdr.Read()) { string s = xrdr.ReadOuterXML(); }". Stupid, rookie error...and I'm not a rookie. ugh. – rogdawg Aug 10 '16 at 16:36

2 Answers2

-1

I suppose your StoredProcedure has already the necessary fields. So, try this:

using (SqlConnection conn = new SqlConnection(connStr)) {
   conn.Open();
   using (SqlCommand cmnd = new SqlCommand("dbo.spMyStoredProcXML", conn)) {
     cmnd.CommandType = CommandType.StoredProcedure;
     cmnd.Parameters.AddWithValue("@Param1", "Some value";)
      //more parameters...
     XmlReader xrdr = cmnd.ExecuteXmlReader();
      //do stuff...
     }
  }
David BS
  • 1,822
  • 1
  • 19
  • 35
  • 1
    This is not an answer to the question: as far as I can see, all you've done is moved the call to `Open()` and substituted `AddWithValue(…)`? – stuartd Aug 10 '16 at 14:41
  • The answer of the question may provide some best approaches (like AddWithValue) and, more important, provide a solution to the problem. If the routine is wrong and I see a better way to do it, what's the problem? Anyway, I really suppose the StoredProcedure has already the necessary parameters within it. So, the original code will fail if it is the situation. – David BS Aug 10 '16 at 14:47
  • AddWithValue is certainly _convenient_, but [considered harmful](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and not thus a "best approach", except in perhaps your opinion. Where does your answer "provide a solution to the problem"? You may think your way is a better way, but if you cannot answer the question, then you should post it as a comment instead. – stuartd Aug 10 '16 at 14:53
  • @stuartd, thanks. I really hadn't knowledgement of the problem related to the AddWithValue - I utilize here for many systems and I had never got any problem. – David BS Aug 10 '16 at 14:59
-1

Maybe try this:

In your procedure make sure your parameter is defined as an output param:

CREATE PROCEDURE [dbo].[MyStoredProcedure]
 @Param1 xml OUTPUT

SET @Param1 = (SELECT XML from TABLE)

Then in your C# code:

cm.Parameters.Add("@Param1", SqlDbType.Xml).Direction = ParameterDirection.Output;

Param1 = Convert.ToString(cm.Parameters["@Param1"].Value);

Obviously, adjust your datatypes to whatever is fitting. Additionally, if you're doing a bunch of work in this procedure I would personally use ExecuteNonQuery as opposed to using an XMLReader

Matthew Alltop
  • 501
  • 4
  • 20