2

trying to create a clr that writes a sql table to file as pipe delimited.

not tested output yet so not sure if it is working as hit a snag trying to create the connection string. I want it to be dynamic so it works out the connection string based on the sql server it is being executed from.

    using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Security;
using System.Security.Principal;
using Microsoft.SqlServer.Server;

public partial class CLR_uspExportToFiles
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void uspExportToFiles(string TableOrQuery, string Delimiter, int Debug, string FilePath, string Filename)
    {
        var output = FilePath + Filename;
        using (System.IO.StreamWriter file = new System.IO.StreamWriter(output))
        {

            const string DATASOURCE = "Data Source=";
            const string INITIALCATALOG = ";Initial Catalog=";
            const string INTEGRATEDSECURITY = ";Integrated Security=True;Enlist=false;";

            string ConnString;
            string InstanceName;
            string DbName;

            //Establish a connection in the current context to dynamically get the current
            //Server and Database Name.
            using (SqlConnection sysconn = new SqlConnection("context connection=true"))
            {
                SqlCommand GetSQLSystemProperties = new SqlCommand();
                GetSQLSystemProperties.Connection = sysconn;

                sysconn.Open();

                //Get the current SQL Server instance name
                GetSQLSystemProperties.CommandText = "SELECT @@Servername";
                InstanceName = (string)GetSQLSystemProperties.ExecuteScalar();

                //Get the current Database Name
                GetSQLSystemProperties.CommandText = "SELECT DB_NAME()";
                DbName = (string)GetSQLSystemProperties.ExecuteScalar();
                sysconn.Close();


                //Dynamically construct the connection string to establish a connection outside
                //of the current context, so that any error written to the error table won't be
                //rolled back.
                ConnString = DATASOURCE + InstanceName + INITIALCATALOG + DbName + INTEGRATEDSECURITY;

                using (SqlConnection conn = new SqlConnection(ConnString))
                {

                    using (SqlCommand cmd = new SqlCommand("SELECT * FROM @TableOrQuery", conn))
                    {
                        //cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@TableOrQuery", TableOrQuery);
                        //cmd.Parameters.AddWithValue("@Del", Delimiter);
                        //cmd.Parameters.AddWithValue("@Debug", Debug);
                        //cmd.Parameters.AddWithValue("@FilePath", FilePath);
                        //cmd.Parameters.AddWithValue("@Filename", Filename);

                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                //Build the Text file data.
                                string txt = string.Empty;

                                foreach (DataColumn column in dt.Columns)
                                {
                                    //Add the Header row for Text file.
                                    txt += column.ColumnName + "|";
                                }

                                //Add new line.
                                txt += "\r\n";
                                file.WriteLine(txt);
                                foreach (DataRow row in dt.Rows)
                                {
                                    foreach (DataColumn column in dt.Columns)
                                    {
                                        //Add the Data rows.
                                        txt += row[column.ColumnName].ToString() + "|";
                                    }

                                    //Add new line.
                                    txt += "\r\n";
                                    file.WriteLine(txt);
                                }

                            }

                        }
                    };

                }
            }
        }
    }
}

Getting an error though

Msg 6522, Level 16, State 1, Procedure CLR_uspExportToFiles, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CLR_uspExportToFiles": 
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
System.Data.SqlClient.SqlException: 
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePa...

and not sure how to fix

3
  • The context connection is already the current server, why do you need another connection?. Commented Jul 10, 2015 at 14:51
  • 1
    "SELECT * FROM @TableOrQuery" you cannot parametrize db object identifiers Commented Jul 10, 2015 at 14:51
  • Thanks, I tried to build it form code online and changed it work now. also hit that error alex thanks, changed to using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open(); var sqlString = string.Format("SELECT * FROM {0}", TableOrQuery); Commented Jul 10, 2015 at 14:53

1 Answer 1

1

A few notes:

  1. You should use the Sql*** types instead of the native .NET types. Meaning, SqlString (or sometimes SqlChars) instead of string, SqlInt32 instead of int, etc. Please see Mapping CLR Parameter Data for the full list.
  2. I think you are opening your file and database connections in the wrong order. The query might not return anything: not all queries return result sets, and some error. I wouldn't open the file until the command completes successfully and indicates that something was returned.
  3. I don't see any reason to open an external connection to the server. I saw the comment about not wanting something to get rolled back, but that didn't make much sense.
  4. You don't need the two queries (SELECT @@SERVERNAME and SELECT DB_NAME()) since you don't need that second, external connection.
  5. Even if you did need (or just want) those two pieces of information, you only need to query for @@SERVERNAME. You can get the database name from the SqlConnection via the Database property. Yes, when using the Context Connection, it will be set to the current DB (current for that query).
  6. Don't use Parameters.AddWithValue. Create the parameter separately and specify the SqlDbType and max length.
  7. Do not use a DataTable unless you are absolutely, positively sure that any table passed in will never, ever be that large (in terms of data / used space, not indexes). The Fill method sucks the entire result set into memory (i.e. the DataTable). That is rather dangerous. You should instead use a SqlDataReader and write out each row of the result set to the file as it is read from the SqlDataReader.
  8. There is no need for the txt variable. You can write out each field as you read it via the Write method.
  9. Even if you did want to concatenate the pieces into a string to write out once per row, you wouldn't need to append the newline (i.e. \r\n) as that is handled by the WriteLine method (same goes for Console.Write vs Console.WriteLine). By using both WriteLine and appending \r\n, every row of data will be separated by a blank line.

For more information on working with SQLCLR in general, please see the series I am writing on SQL Sever Central: Stairway to SQLCLR (free registration required).

Also, I have written a SQLCLR stored procedure to do exactly this (dump the result set of a query to a file) as part of the SQL# library. Please note that while there is a Free version of SQL#, the DB_BulkExport stored procedure is only available in the Full version.

Sign up to request clarification or add additional context in comments.

4 Comments

@JQuery Glad you have it working. Just keep in mind, at the very least, points #1 and #7. Item #7, in particular, has the potential to have a large, negative impact on your server.
Thanks, It is is only for a invoice extract which will never be a more than a few lines. However it would be good to have it more generic to be used with any table/query. Have read a few stairways before so will give this a look :)
The \r\n was from a code i picked up, removed all that :) One thing though, you say there is no need for the .txt variable, but what if, I wanted to change it to CSV or another extension to meet the requirement of a client? If their package only accepts a certain extension then this would be needed.
@JQuery Regarding not needing the txt variable: don't confuse the variable name with the file extension. I was saying that you don't need to build a string of the full export line since you can write out each field to the file as you get it. If you use the Write method instead of WriteLine, then it won't add the newlines and it will just append to the same row of the output file. Then you would need the file.Write("\r\n"); at the end of each output row, but you would be reducing a useless step. Besides, the file extension is being passed in as part of the Filename input parameter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.