Ah, sorry. The correct option I was thinking of was `QUOTED_IDENTIFIER` (not plural).
There are a few other options you might want to set as well. I find them important for consistency between tools. If you are using a version of Sequel >= 5.23.0 (2019-08-01), you can pass `ansi: true` into the connection options: https://github.com/jeremyevans/sequel/pull/1629 DB = Sequel.connect('tinytds://user:pass@host/db', ansi: true) If Sequel is a bit older, you should consider setting each of those options in an `after_connect` block. Here's a variation of what I use: # You could put this in a more appropriate module, # inline it directly into the connect statement, # merge it with options as a config helper, whatever. # A series of commands to make the TinyTDS driver # operate like other client tools as described in ms docs # SET_MSSQL_DEFAULTS = -> (db) { db.execute <<-SQL SET ANSI_NULL_DFLT_ON ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SQL } DB = Sequel.connect('tinytds://user:password@host/database', after_connect: SET_MSSQL_DEFAULTS) # or DB = Sequel.connect(options.merge(after_connect: SET_MSSQL_DEFAULTS)) If you're interested in a little bit more background, this earlier thread describes what you are experiencing: https://groups.google.com/forum/#!topic/sequel-talk/cbkCTIjiA8o Good luck! On Friday, July 10, 2020 at 12:00:15 PM UTC-5, KING SABRI wrote: > > Hi Tim > Thank you for taking the time to response > > After your advice, I changed the quotes to square-brackets then I got a > new error, as the following > >> TinyTds::Error: Heterogeneous queries require the ANSI_NULLS and >> ANSI_WARNINGS options to be set for the connection. This ensures consistent >> query semantics. Enable these options and then reissue your query. > > > > I tried to execute SET QUOTED_IDENTIFIERS ON before my query, but it > returns > >> TinyTds::Error: 'QUOTED_IDENTIFIERS' is not a recognized SET option. > > > I did some research on the first error and I found that I need to set both > of them on, the ANSI_NULLS and ANSI_WARNINGS *respectively* to get it to > work. > First I've to enable the properties before executing the query > *separately* > > *@db["SET ANSI_NULLS ON;SET ANSI_WARNINGS ON;"].all* > > then execute my query > > *@db["SELECT * FROM OPENQUERY([SQL02.DEV <http://sql02.dev/>] , 'SELECT > @@version')"].all* > > > Thanks again! > > > > On Friday, July 10, 2020 at 7:14:15 PM UTC+3, Tim Tilberg wrote: >> >> Try executing `SET QUOTED_IDENTIFIERS ON` before running this query. >> Recent versions of Sequel included the `ansi` connection option, which >> causes Sequel to behave more like most other clients -- this is one of the >> settings it affects. >> >> If this doesn't do the trick, SQL Server uses `[]` for identifier >> quoting, so perhaps this will work: >> >> @db["SELECT * FROM OPENQUERY([SQL02.DEV <http://sql02.dev/>] , 'SELECT >> @@version')"].all >> >> >> >> On Wednesday, July 8, 2020 at 8:35:10 PM UTC-5, KING SABRI wrote: >>> >>> Thank you for your response >>> In the official documentation, they are using a variable not an actual >>> hostname. I tried to use the hostname *without* quotes on another SQL >>> client and it returns "incorrect synatx" >>> >>> I also tried the same query on other GUI SQL client (such as Sqlectron >>> and heidisql) and it works well. >>> It seems that using anything but double-quotes with the hostname raise >>> an error. Also only single quotes are accepted for the query. (tested on >>> GUI client) >>> >>> >>> On Thursday, July 9, 2020 at 1:47:47 AM UTC+3, Jeremy Evans wrote: >>>> >>>> On Wednesday, July 8, 2020 at 3:38:00 PM UTC-7, KING SABRI wrote: >>>>> >>>>> Hi all >>>>> >>>>> I'm trying to use OPENQUERY using sequel and I'm always getting >>>>> syntax error >>>>> >>>>> >>>>>> @db["SELECT * FROM OPENQUERY(\"SQL02.DEV\" , 'SELECT >>>>>> @@version')"].all >>>>> >>>>> >>>>> *The error is * >>>>> >>>>> Sequel::DatabaseError: TinyTds::Error: Incorrect syntax near ' >>>>> SQL02.DEV'. >>>>> from >>>>> /var/lib/gems/2.7.0/gems/sequel-5.34.0/lib/sequel/adapters/tinytds.rb:217:in >>>>> >>>>> `fields' >>>>> Caused by TinyTds::Error: Incorrect syntax near 'SQL02.DEV'. >>>>> from >>>>> /var/lib/gems/2.7.0/gems/sequel-5.34.0/lib/sequel/adapters/tinytds.rb:217:in >>>>> >>>>> `fields' >>>>> Caused by Sequel::DatabaseError: TinyTds::Error: Unclosed quotation >>>>> mark after the character string '''. >>>>> from >>>>> /var/lib/gems/2.7.0/gems/sequel-5.34.0/lib/sequel/adapters/tinytds.rb:217:in >>>>> >>>>> `fields' >>>>> Caused by TinyTds::Error: Unclosed quotation mark after the character >>>>> string '''. >>>>> from >>>>> /var/lib/gems/2.7.0/gems/sequel-5.34.0/lib/sequel/adapters/tinytds.rb:217:in >>>>> >>>>> `fields' >>>>> >>>>> I've tried various ways to by changing double-quotes to single-quotes, >>>>> quotes escaping but no luck. >>>>> >>>>> PS: Queries such as SELECT @@version work well. The issue I'm facing >>>>> is only with OPENQUERY. >>>>> >>>>> -> Sequel::VERSION >>>>> => 5.34.0 >>>>> >>>> >>>> From looking at the OPENQUERY documentation, the first argument does >>>> not appear to need quoting: >>>> https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-ver15 >>>> >>>> In any case, this doesn't appear to be a problem with Sequel, as Sequel >>>> is raising an error because your SQL is invalid. If you continue to need >>>> help on producing valid SQL for OPENQUERY, you should probably ask on an >>>> MSSQL-specific forum. >>>> >>>> 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 view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/f51083eb-9112-4454-af13-01c1c544b146o%40googlegroups.com.
