That is super informative!! I tried both ways you mentioned and both work like a charm!
*Thank you Tim!* On Friday, July 10, 2020 at 8:51:09 PM UTC+3, Tim Tilberg wrote: > > 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/78c22e03-faf1-4184-a805-5ecf4fec5edeo%40googlegroups.com.
