We have had a few customers who want to deploy their assemblies using Vistual Studio. They encountered various errors similar to below. But when the use SQL Server management studio to manually CREATE ASSEMBLY, everything works fine.
Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The above two errors are actually caused by connection and query timeouts. Default for connection is 15 seconds and for query timeout is 30 seconds. Most of the time, query timeout is caused by the fact assembly may be very large. This leads to CREATE ASSEMBLY statement to time out. So you will need to increase the values.
But configuring conneciton and query timeouts are not obvious with SQL CLR projects. here are the ways on how to do this.
Change query timeout
Tools | Options
Expand “Database Tools”
Select “query and View Designers”
You can uncheck “Cancel long running query” or change the value for “Cancel after” to a larger value
Change connection timeout
View | “Server Explorer”
Right click your connection and choose “Modify Connection”
Click on “Advanced”
Then you can change Connect Timeout
Jack Li | Senior Escalation Engineer |Microsoft SQL Server Support