CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Anaiah
Anaiah

Reputation: 633

What is the execute command to use here

protected void Page_Load(object sender, EventArgs e)
    {
        string sqlConnectionString = @"Data Source=phriz-webapp01;Initial Catalog=PFTracking;Integrated Security=True";
        string script = "if not exists(select * from sys.servers where name=N'CNCTC-WEB01')begin exec sp_addlinkedserver @server='CNCTC-WEB01'exec sp_addlinkedsrvlogin 'CNCTC-WEB01','false',null,'svc_Phils','Apple@6' end INSERT INTO [PFTracking].[dbo].[TempTable] SELECT c.[pf_id],a.[RequestDate],c.[pf_carrierUsed],b.[PiecePrice] * b.[PartQuantity] as [Amount] ,c.[pf_type],c.[pf_resSupplier] ,c.[pf_resCustomer],c.[pf_trailerNum] ,b.[PartDesc]  ,c.[pf_chargeBack]  ,c.[pf_chargetoPlant] FROM [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a JOIN [CNCTC-WEB01].[NOP_PR].[dbo].[Parts] b on a.[RequestID] = b.[RequestID] JOIN [PHRIZ-WEBAPP01].[PFTracking].[dbo].[Tbl_PFExcel] c on  b.[PartNumber] like '%'+c.pf_id+'%'where a.[EntityName] like '%PTA' AND a.[RequestDate] between '2015-04-20 00:00:00.000' AND GETDATE() ";
        SqlConnection conn = new SqlConnection(sqlConnectionString);
        conn.Open();
        SqlCommand comm = new SqlCommand(script, conn);
        comm.ExecuteNonQuery();
        conn.Close();
    }

I wanted to execute this command to the server so that each time the page is accessed the db loads.

comm.ExecuteNonQuery()

Additional :

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.`

Line 21:             conn.Open();
Line 22:             SqlCommand comm = new SqlCommand(script, conn);
Line 23:             **comm.ExecuteNonQuery();**
Line 24:             conn.Close();
Line 25:         }

That's the error it gives .. Any help ?

Upvotes: 1

Views: 90

Answers (1)

YaBCK
YaBCK

Reputation: 3029

Have a look at this and tell me if this works for you - I've added a try catch to check if the command is timing out.

Just few tips:

I would always use a try catch because it'll tell you exactly what you need to know and why your code isn't working.

Also I would always use the using statement because using statements simply ensure that non-managed resources are disposed, they cannot handle exceptions.

Also,SqlCommand implements IDisposable, so I'd suggest putting that in a using block as well. 

protected void Page_Load(object sender, EventArgs e)
{
   using (SqlConnection conn = new SqlConnection("Data Source=phriz-webapp01;Initial Catalog=PFTracking;Integrated Security=True";))
   {
      conn.Open();
      string script = "if not exists(select * from sys.servers where name=N'CNCTC-WEB01')begin exec sp_addlinkedserver @server='CNCTC-WEB01'exec sp_addlinkedsrvlogin 'CNCTC-WEB01','false',null,'svc_Phils','Apple@6' end INSERT INTO [PFTracking].[dbo].[TempTable] SELECT c.[pf_id],a.[RequestDate],c.[pf_carrierUsed],b.[PiecePrice] * b.[PartQuantity] as [Amount] ,c.[pf_type],c.[pf_resSupplier] ,c.[pf_resCustomer],c.[pf_trailerNum] ,b.[PartDesc]  ,c.[pf_chargeBack]  ,c.[pf_chargetoPlant] FROM [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a JOIN [CNCTC-WEB01].[NOP_PR].[dbo].[Parts] b on a.[RequestID] = b.[RequestID] JOIN [PHRIZ-WEBAPP01].[PFTracking].[dbo].[Tbl_PFExcel] c on  b.[PartNumber] like '%'+c.pf_id+'%'where a.[EntityName] like '%PTA' AND a.[RequestDate] between '2015-04-20 00:00:00.000' AND GETDATE() ";

      using (var comm = new SqlCommand(script, conn))
      {
        // Setting command timeout to 2 minutes
        comm.CommandTimeout = 120;
        try 
        {
           command.ExecuteNonQuery();
        }
        catch (SqlException e) 
        {
           Console.WriteLine("Got expected SqlException due to command timeout ");
           Console.WriteLine(e);
        }
      }
      conn.Close();
   }
}

Upvotes: 2

Related Questions