0

I've inherited a SSIS package that has a c# script task that reads failed logins from our SQL Server error logs and reports on them. It's recently started timing out on one of our servers and I want to increase the timeout from the default 30. Right now I've got 

 try
        {                
            string readFromConnection = "Data Source=" + (string)Dts.Variables["OneServer"].Value + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package;";

            ArrayList results = null;

            try
            {
                results = ExecuteQuery("EXEC sys.xp_readerrorlog 0,1, N'Login failed for user'", readFromConnection);
            }

How do I add a CommandTimeout into this? Or is there a better way to do it?

M T
  • 21
  • 8
  • http://stackoverflow.com/questions/4308417/connection-timeout-for-sql-server – Eric Hauenstein Dec 11 '14 at 15:18
  • Fantastic. This didn't come up in the searches I tried, but may have solved my problem. I appreciate it. – M T Dec 11 '14 at 15:48
  • @EricHauenstein That covers the timeout while connecting to SQL Server. Once connected, there's a different timeout which controls how long a query's execution is expected to run, which OP eludes to with CommandTimeout. However, my quick review of [ExecuteQuery](http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext_methods(v=vs.110).aspx) doesn't show a setting – billinkc Dec 11 '14 at 15:48
  • Wait, so it's taking > 30 seconds to acquire the connection to sql server (controlled by your connection string) or your query is running longer than the default (30 seconds) [SqlCommand.CommandTimeout property](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout%28v=vs.110%29.aspx) – billinkc Dec 11 '14 at 15:51
  • Realized just after commenting that billinkc is correct. This doesn't solve the query timeout, and there is no connection timeout problem. Of course it ran just fine when I tested it because the query isn't taking more than 30 seconds right now. – M T Dec 11 '14 at 15:51

1 Answers1

0

To anyone curious, I rewrote the try as a SqlCommand. I used SqlDataReader to read the results into a Hashtable that I put into the empty ArrayList so that the results were the same and the rest of the code worked properly.

M T
  • 21
  • 8