socom1985
asked on
c# How to handle multiple MSSQL attach
two part question
I have a WCF webservice which attaches MSQL DB files (templates) to the server.
first question:
Does anybody know why this could be very slow on the server? when I run the service on my client machine the attach is like 10 times faster.
second question:
I have problems with the timeout as when my service tries to handle multiple requests I get a timeout because apparently the sql server is busy with handling my previous request. What is the correct way to handle this?
I have a WCF webservice which attaches MSQL DB files (templates) to the server.
first question:
Does anybody know why this could be very slow on the server? when I run the service on my client machine the attach is like 10 times faster.
second question:
I have problems with the timeout as when my service tries to handle multiple requests I get a timeout because apparently the sql server is busy with handling my previous request. What is the correct way to handle this?
//Creating connection
SqlConnection conn = new SqlConnection("Data Source=" + Properties.Settings.Default.MSSQLServerName + ";Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("", conn);
//Creating SQL command
cmd.CommandText = "exec sys.sp_attach_db " + name + " , '" + MDFpath + "','" + LDFpath + "'";
//Open connection
conn.Open();
//Execute query
cmd.ExecuteNonQuery();
//Dispose of connection
cmd.Dispose();
conn.Dispose();
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I solved it with:
using (SqlConnection conn = new SqlConnection("Data Source=" + Properties.Settings.Defaul t.MSSQLSer verName + ";Integrated Security=SSPI;Max Pool Size=200;Connect Timeout=30;Async=true"))
using (SqlConnection conn = new SqlConnection("Data Source=" + Properties.Settings.Defaul
ASKER