Sorry, I must have edited this little bit out: SSMS is the primary interface most people interact with SQL Server from -- so to me it would make sense to have behavior similar to what you'd find when running queries in SSMS.
On Friday, March 10, 2017 at 12:04:02 PM UTC-6, Tim Tilberg wrote: > > In the short term, do you think it would be helpful to add some of this > information into the docs somewhere? (Here? > <https://github.com/jeremyevans/sequel/blob/master/doc/opening_databases.rdoc#tinytds>) > > I was surprised and frustrated when I couldn't figure out what was going > on, since it was acting unlike any query interface I've used to interact > with SQL Server. > > In the long term, maybe something like > > `ssms_defaults: true` or `query_defaults: true` > > or something as a config option for `Connect`? > > I don't really know what makes sense here without being overreaching. It > probably shouldn't be `:ansi_defaults` because that actually means > something in SQL that doesn't match what either SSMS or ActiveRecordAdapter > are doing (implies `CURSOR_CLOSE_ON_COMMIT` and `IMPLICIT_TRANSACTIONS`). > > Here's some general information that might be helpful as we explore~ > > Looking at the defaults SSMS uses for queries, the following are set on: > > ANSI_NULL_DFLT_ON > ANSI_NULLS > ANSI_PADDING > ANSI_WARNINGS > CONCAT_NULL_YIELDS_NULL > QUOTED_IDENTIFIER > > > SQLServerAdapter seems to use the same settings... > > ANSI_NULL_DFLT_ON > ANSI_NULLS > ANSI_PADDING > > *ANSI_WARNINGS*CONCAT_NULL_YIELDS_NULL > QUOTED_IDENTIFIER > > (In the source, it looks at first like azure has different options set, > but in the end it seems to evaluate to the same configuration... Maybe > azure doesn't support the `SET ANSI_DEFAULTS ON` statement... I'm not > familiar with Azure). > > > Anyway, thanks for your contribution. I use SQL Server at work and have > been learning Ruby the last year or two on the side. I'm starting to use it > at work a lot to automate various tasks. I've really enjoyed using Sequel! > > > > On Friday, March 3, 2017 at 12:24:06 PM UTC-6, Jeremy Evans wrote: >> >> On Friday, March 3, 2017 at 10:18:30 AM UTC-8, Tim Tilberg wrote: >>> >>> Yesterday I was using Sequel to export some data from MS SQL Server >>> using a bunch of sprocs. Within these sprocs were statements that create >>> temp tables and I kept coming across: >>> >>> column does not allow nulls. INSERT fails. >>> >>> >>> >>> /home/ttilberg/.rvm/gems/ruby-2.2.5/gems/sequel-4.41.0/lib/sequel/adapters/tinytds.rb:218:in >>> `fields': TinyTds::Error: Cannot insert the value NULL into column >>> 'tire_category', table 'tempdb.dbo.#custom_out___________0000000003EA'; >>> column does not allow nulls. INSERT fails. >>> (Sequel::NotNullConstraintViolation) >>> >>> >>> >>> I tracked the issue down to the TinyTDS level and asked the maintainer >>> for his take on it, with lots of description of the issue. >>> >>> >>> https://github.com/rails-sqlserver/tiny_tds/issues/248#issuecomment-283763324 >>> >>> He mentioned: >>> >>> I think the issue is that a pure DBLIB connection needs a few sane >>>> configurations set. We do this in the ActiveRecord adapter for TinyTDS. >>> >>> >>> and proceeds to show a few settings that get set by default in the >>> activerecord-sqlserver gem >>> <https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver_adapter.rb#L373-L379> >>> >>> It turns out executing `SET ANSI_DEFAULTS ON` fixed my issue. >>> >>> I am curious if some of these same defaults seen in the >>> activerecrd-sqlserver gem would also make sense in sequel/adapters/tinytds >>> (or even shared/mssql)? I see that `TEXTSIZE` is set if it's specified, but >>> I didn't see any matches for `ANSI` or some others that Metaskills sets in >>> activerecord. >>> >>> This could potentially be a hazardous change, but I'm curious what your >>> take on it is. >>> >> >> This is potentially something that could be supported as an option, but >> is not a change I feel comfortable making the default due to backwards >> compatibility issues. >> >> Thanks, >> Jeremy >> > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
