0

My scenario is that I am calling multiple "ALTER DATABASE SET " commands followed by some table,index etc. creation commands from a sql script via C# following way:-

How to execute an .SQL script file using c#

string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

SqlConnection conn = new SqlConnection(sqlConnectionString);

Server server = new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(script);

The ALTER DB commands are like 

ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [MyDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [MyDB] SET TRUSTWORTHY OFF 
GO

I have around 20 calls like this and after that calls to create tables, indexes, keys etc.

What I observe is that if I try to alter any of these following db properties

HONOR_BROKER_PRIORITY    
TRUSTWORTHY  
DB_CHAINING

Then I see "exec sp_reset_connection" getting called in SQL Profiler and then it skips remaining commands being sent from the script from C# code.

This behavior has made my procedure not working properly from C# code but works fine from SSMS.

Can someone please explain this behavior of sp_reset_connection getting called after these specific db properties because in future I need to know what such properties can break code from C# while working in SSMS ?

There are few questions on SO regarding sp_reset_connection but those are about why they are called or enabling/disabling it etc. I couldn't find any relation of this SP to these three properties.

Community
  • 1
  • 1
user2224280
  • 29
  • 1
  • 7
  • Can you elaborate on this..`This behavior has made my procedure not working properly from C# code but works fine from SSMS` – TheGameiswar Sep 01 '16 at 07:45
  • The following commands in the sql script are ignored if I call from C#. If I comment out altering these three properties the script works fine. No matter at which line I alter any of these three properties the sp_reset_connection is called and script call stays incomplete. Not sure is any caching of query plan is involved, but my assumption is that since I am changing the order of these in script, so it should not be cached. – user2224280 Sep 01 '16 at 07:57

0 Answers0