Link to home
Start Free TrialLog in
Avatar of socom1985
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?


 
//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();

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vipul Patel
Vipul Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of socom1985
socom1985

ASKER

I set Max Pool Size=200;Connect Timeout=30 but I still get timeouts.. when I attach the database with management studio it happens rather quickly..
I solved it with:
using (SqlConnection conn = new SqlConnection("Data Source=" + Properties.Settings.Default.MSSQLServerName + ";Integrated Security=SSPI;Max Pool Size=200;Connect Timeout=30;Async=true"))