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.
