SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS
Goto page Previous  1, 2
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS
Author Message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
HRMMM

So I just profiled me opening a new tab in SSMS and this is one of the statements that came through:

Code:

SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON


The same thing with SQL Editor just does some of the DB Queries to figure out tables and indexes and such. No defaults set at all....



It seems like there should be some sane defaults...
Tue Nov 08, 2016 5:39 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Mindflux wrote:
It just seems with how much trouble and how much talk there is about the impact (positive or negative) about ARITHABORT it would seem like it would make sense to set it on or off (depending on circumstances).


Yes, but if you suspect it could be an issue you better play safe and set them explicitly rather than relying on settings that might not have the desired value. Imho.
Tue Nov 08, 2016 5:40 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
gemisigo wrote:
Mindflux wrote:
It just seems with how much trouble and how much talk there is about the impact (positive or negative) about ARITHABORT it would seem like it would make sense to set it on or off (depending on circumstances).


Yes, but if you suspect it could be an issue you better play safe and set them explicitly rather than relying on settings that might not have the desired value. Imho.


Interestingly enough if your Compatibility level is set to 90 or more SET ANSI_WARNINGS ON already implies ARITHABORT ON.

Though that doesn't really fix our issue with the connections coming from SQL Assistance.
Tue Nov 08, 2016 5:47 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
To the best of my knowledge, if you use ODBC for the connections, you can set most of these options in DSN profile settings as defaults (there is a bunch of check boxes for that). In SQL Assistant, you can connect to your database by DSN name. With ADO.NET connections, you can use the custom settings section in SQL Assistant Options to specify additional parameters in the connection string, but you are limited to the options supported by ADO.NET data provider (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396)
Wed Nov 09, 2016 10:52 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
ODBC DSN's only allow you to set a few options, ANSI NULLS, QUOTED IDENTIFIERS and one or two others. It's not really feature parity with what SSMS sets for defaults. That sucks... that and having to rely on a DSN rather than a DSNLESS connection we are used to using with SQL Assistant.

Can you think of another way to set some connection defaults like SSMS does via ODBC or ADO?
Wed Nov 09, 2016 10:56 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.