2

I have to SET ANSI_NULLS and few other database options before I run some queries. So in my code 

const string query_prefix = "SET ANSI_NULLS ON \n GO \n SET ANSI_PADDING ON \n GO \n SET ANSI_WARNINGS ON \n GO \n SET CONCAT_NULL_YIELDS_NULL ON \n GO \n SET QUOTED_IDENTIFIER ON \n GO \n SET NUMERIC_ROUNDABORT OFF \n GO \n ";

which I use as 

SqlCommand cmd = new SqlCommand(query_prefix + "SELECT UserID,FirstName,MiddleName,LastName FROM mydb.dbo.UserInfo where UserID=10277",con);

I added the "\n" above because SET ANSI_NULLS ON GO SET ANSI_PADDING ON GO was giving error "Incorrect syntax near GO". In management studio if GO is in a newline then I do not get the incorrect syntax error, otherwise I do. So I added "\n" but it still throws the error.

So how do I set these database options from code? I cannot use a procedure.

Emil Vikström
  • 90,431
  • 16
  • 141
  • 175
shashi
  • 4,616
  • 9
  • 50
  • 77

2 Answers2

4

GO is a batch separator in SSMS, it is not a SQL command and cannot be included in a query like this.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
3
  • Open the connection
  • Create an SqlCommand object to execute SET ANSI_NULLS ON and SET_ANSI_PADDING_ON with ExecuteNonQuery method
  • Execute your select statement as separate SqlCommand in the same connection

Sample code can be found in this SO post. You can also set the default for these settings at the database level if appropriate for your environment.

Community
  • 1
  • 1
Bryan
  • 17,112
  • 7
  • 57
  • 80