?
Solved

How can I execute a T-SQL SET-command for a SQL-server connection from my C#-application?

Posted on 2009-03-30
8
Medium Priority
?
1,457 Views
Last Modified: 2012-06-27
I need to execute two SET-commands for a SQL-server connection from my C#-application, namely:
  SET DATEFORMAT
  SET ARITHABORT

I am wondering where/how I can issue these commands. It doesn't work in the SqlCommand.CommandText. Prepending them to the normal CommandText results in an error.

An optimal way for me would be to include the SET-commands in the connection-string or execute them via a SqlConnection object. Their effect should last for the entire connection. Anyway I couldn't figure out a way to do so.

I am using Visual Studio 2005, .net framework 2.0 and SQL-server 2000.

Any help is greatly appreciated.
Thanks, Wei Xi
0
Comment
Question by:WeiXi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24023423
no way...

you should be doing the sql code with parameters instead of relying on those stuff...
0
 
LVL 3

Author Comment

by:WeiXi
ID: 24023598
Arrghh. Please, don't say "no way" :(

I can live without the SET DATEFORMAT, following your advice about parameters. Lots of code has to be changed, but it can be done.

But I simply don't know how I should do without the SET ARITHABORT. I have to write to a table which is used in an indexed view. This knowledge article applies to my case:
http://support.microsoft.com/kb/305333
It says, that it is only possible to write to such a table when SET ARITHABORT ON is in effect. Removing the index from the view results in severe performance problems due to large data sets, so this is no option either.

Is it really true, that I can't use a database with indexed views from C#? I would consider this a major flaw of the C#/.net platform.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24023735
you can.

note, the priority of the setting(s) are defined like this:
- explicit SET by application
- connection string specification (*)
- ODBC DSN setting
- provider default setting
- database default setting
- server default setting

unfortunately, the SET ARITHABORT is not part of that, so (b) does not apply.
http://support.microsoft.com/kb/305333


0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:WeiXi
ID: 24023915
Well, thanks a lot for the insight so far. However, I still don't know how to solve my problem.

Changing the server or database defaults is out of question, there is too much legacy code built around them. Changing the SqlConnection to OdbcConnection or some other Connection is unfeasible for the same reason.

Remains:
- explicit SET by application
- connection string specification (*)

In the above mentioned knowledge articel it says:
> To resolve this problem, add the following ADO code to your application
> after you open the connection to your database:
> MyConnection.Execute "SET ARITHABORT ON"

I was hoping that something similar could be done with C# and SqlConnection!
If that's not possible, do you have any other idea how I could write to the index-view-table?

Thanks for your help,
Wei Xi
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24023988
the reference was for adodb, indeed...

in ado.net:
SqlCommand cmd = new SqlCommand("SET ARITHABORT ON; INSERT INTO ..... ", conn);

Open in new window

0
 
LVL 3

Author Comment

by:WeiXi
ID: 24024152
Well, that was also my first shot. Just prepend the SET ARITHABORT to the insert statement.
Unfortunately that doesn't work, the error message ist just the same as without the SET ARITHABORT:
> INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.

I guess the reason is due to the following statement in the knowledge base article:
> To successfully insert a row into a table that is referenced in an indexed view,
> the SQL ARITHABORT configuration setting must be set to ON. Furthermore,
> the statement that applies this configuration setting must be executed in its own
> batch.

That is, the SET ARITHABORT must be concluded with a GO. However, a GO is not allowed in SqlCommand.CommandText. For the same reason the SET ARITHABORT cannot be used in a stored procedure.

So, I am still stuck. Thanks god, I am talking to a Sage here. They say, Sages always know a way... :)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24024242
well, I do know the way I will take in a couple of minutes ... the one that leads to my bed (which might explain why I did not yet "solve" the problem :)

anyhow, let me make you try to create a stored proc like this:
SET ARITHABORT ON
GO
CREATE PROCEDURE dbo.DoYourInsert @parameter  ...
AS
INSERT INTO ...
 
GO
 
 
and use that proc in your c#

Open in new window

0
 
LVL 3

Accepted Solution

by:
WeiXi earned 0 total points
ID: 24024493
Defining a stored procedure in a SET ARITHABORT ON environment does not do the trick. Same error.

However, your other answer about the SqlCommand inspired me to the solution that I found out just now: The insert/update/delete-SqlCommand for the indexed-view-table must be executed inside a transaction. At the beginning of the transaction a separate SqlCommand doing the SET must be executed first. See the working code below.

Thanks a lot for your help, I am happy to award you the points for guiding me along.
Wei Xi
SqlConnection connection = new SqlConnection(myConnectionString);
connection.Open();
 
SqlTransaction transaction = connection.BeginTransaction();
 
SqlCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT ...";
 
SqlCommand arithabort = connection.CreateCommand();
arithabort.Transaction = transaction;
arithabort.CommandText = "SET ARITHABORT ON";
arithabort.ExecuteNonQuery();
 
insert.ExecuteNonQuery();
 
transaction.Commit();

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question