none
C# .NET - Return informational messages using sqlclient infomessage with cursor support RRS feed

  • Question

  • I am trying to execute T-Sql from .NET and returning the informational messages (the same output that running a .sql script from the command line with sqlcmd would produce).  I am trying to do this without shelling out to the command line and grabbing the standard output and without using in memory temp tables.  Part of this requirement also requires cursor support for the library I am building (so for this optional parameter set based is not an option).  I have this working fine without a cursor.

    using (SqlConnection connection = new SqlConnection(_Conn))
                        {
                            connection.InfoMessage += new SqlInfoMessageEventHandler(InfoMessage.InfoMessageHandler);                 
                            connection.Open();
                            using (SqlCommand cmd = connection.CreateCommand())
                            {
                                TSql = BuildTSql(); // Returns the dynamical sql passed in

                                cmd.CommandText = TSql;

                                cmd.CommandType = System.Data.CommandType.Text;

                                cmd.ExecuteNonQuery();

                                InfoMsg += InfoMessage.output;

                                File.WriteAllText(@"c:\InfoMsgOut.txt", InfoMsg);
                            }
                        }


    But with a cursor it doesn't output the data:


                    "declare @sql nvarchar(1000) \r\n " +
                    "declare @id int \r\n " +
                    "declare @return_xml xml \r\n " +            
                    "SET @sql = 'SELECT emp_id from emp where hire_date > ''10-jun-2000''' \r\n " +
                    "Set @sql='declare cur cursor fast_forward forward_only read_only for '+ @sql \r\n " +
                    "exec (@sql) \r\n " +
                    "open cur \r\n " +
                    "fetch next from cur into @id \r\n " +
                    "while @@FETCH_STATUS=0 \r\n " +
                    "Begin \r\n " +
                    "SET @return_xml = \r\n " +
                    "(SELECT * from emp_detail where emp_id = @id for xml raw \r\n ) \r\n " +                   
                    "print cast(@return_xml as nvarchar(max)) \r\n " +     // This is informational message is not getting output.  // For testing I also tried writing it to a temp table and only ended up with the last result.  But when I run this as is from the command line with sqlcmd all of the informational output is written to screen.
                    "fetch next from cur into @id \r\n " +
                    "End \r\n " +
                    "close cur \r\n " +
                    "deallocate cur \r\n "

    Is there any other way to capture informational message from a tsql script in .NET or something I am missing?  It may have to do with caching on the client side for curors but am not sure how to get at this cache with sqlclient.

    • Moved by Mike FengModerator Thursday, June 28, 2012 3:13 AM Ado .net (From:.NET Base Class Library)
    Monday, June 25, 2012 9:31 PM

All replies

  • The first method that is working is executing a command in the database that automatically saves the results to the file c:\InfoMsgOut.txt.  There is nothing in the code you posted that specifes the filename.

    Witn Net you can either do one of the following

    1) Bind the results of the SQL to an object like List<List<string>>

    2) Have the SQL return a recordset (really a List<List<string>>) and then output the results of the recordset in any format you want.


    jdweng

    Monday, June 25, 2012 10:34 PM
  • The first method is executing a string literal which is a T-Sql statement like the one below it.  It's not calling an object on the database (stored proc or function).  In lieu of parsing a sql script and reading the text the library allows a user to pass the sql query into the constructor when they instantiate it their code.    TSql = BuildTSql(); returns the sql they passed in and then it gets executed as it would from parsing a .sql script file.
    Monday, June 25, 2012 11:39 PM
  • Hi,

    I don't see any difference between the output from SSMS and from running the code.

    If you need further assistance resolving the issue, you may want to consider opening a support incident.

    Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    If Microsoft determines that a problem is the result of a defect in a Microsoft product, you will not be charged for that incident.

    Thanks,

    Cathy Miller

    Monday, July 9, 2012 8:10 PM
    Moderator