These are chat archives for rails-sqlserver/activerecord-sqlserver-adapter

18th
Jan 2017
Chris Reister
@chrisftw
Jan 18 2017 01:43
I really wish there was some kind of chart that showed which versions of rails can use which versions sqlserver. Does anyone have a link to such a chart?
Ken Collins
@metaskills
Jan 18 2017 01:45
That would be a huge chart. Most old version shold work with new version of the DB.
Since T-SQL of yore works just fine on modern DBs.
What version of SQL Server are you using?
Each x-x-stable bracnh contains docs in the readme of the min version.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 19:29

Hello
I am trying to load a view using ActiveRecord::Base.connection.execute, and I am seeing an interesting message.

string = "DROP VIEW IF EXISTS dbo.bros;
GO

CREATE VIEW dbo.bros
AS
SELECT 'true' = 'true';"

ActiveRecord::Base.connection.execute string

Pretty much it boils down to the above code throwing the following error:

ActiveRecord::StatementInvalid: TinyTds::Error: 'CREATE VIEW' must be the first statement in a query batch.: DROP VIEW IF EXISTS dbo.bros;

Are we not able to use the keyword GO inside of an ActiveRecord::Base.connection.execute?

Ken Collins
@metaskills
Jan 18 2017 19:47
Hey @kanobt61_twitter
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 19:48
Hello
I am thinking that I might need to split on 'GO' and run it as two commands
Ken Collins
@metaskills
Jan 18 2017 19:48
Agreed. However, I think you should put this in a migration.
And remove the GO.
Do one statement or two, does not matter.
Migrations have an execute method.
And they are wrapped in transactions.
And since SQL Server supports DDL transactoins. A migration is both the locical place and the right one techincally.
YOu can see some examples of execute here too.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 19:51
Creating a view in the a migration will update the schema file? I thought views did not show in the schema, so I will not be able to re-produce this in a test database if it doesn't show in the schema.rb
Ken Collins
@metaskills
Jan 18 2017 19:52
What version of Rails are you on?
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 19:53
Well... I wish that question had an easy answer. lol.
I'm on 3.1 but we are migrating to 4.0 and at the same time I'm moving from postgresql to MS SQL.
So let's just say I'm using 4.0
Ken Collins
@metaskills
Jan 18 2017 19:53
Then may I suggest using
Agreed on the schema.rb Till you go to SQL vNext/2016, there is no way to easily switch to :sql format dumper becuase there is no x-platform binary to dump a schema
But that is changing in 2016
So gems like Scenic should fill that gap.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 19:55
Oh cool. good tip!
Ken Collins
@metaskills
Jan 18 2017 19:55
It may be PG specific, not sure.
But there are other gems.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 19:57
Maybe that... Googling should yield a few.
Finding a gem that auto adds to the schema.rb would be a benefit.
Dumping views to structure files is where Rails says switch to :sql format. But again, we only have a ray of hope on that under 2016 DB.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 20:00
ActiveRecord >= 4.2
I suspect I won't find a good one until I'm on a newer rails.
Ken Collins
@metaskills
Jan 18 2017 20:00
Actually working on updating the adapter's Docker image tonight to work on that.
Bummer
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 20:00
Cool
Ken Collins
@metaskills
Jan 18 2017 20:00
Maybe you can a) do the migration. then just manually append to a file.
Then make a rake task that shadows schema:loadto execute that schema plus file.
Since rake tasks naturally stack up.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 20:03

This might be getting off topic a bit, but right now for my old rails with postgresql I have a directory for custom sql that the schema file couldn't account for, and I patched schema:load to load all the files in that directory with ActiveRecord::Base.connection.execute File.read(file_name)

So I was hoping to do the same for SQL Server.

Which is how I ran into this issue with the keyword GO
Ken Collins
@metaskills
Jan 18 2017 20:04
Right right. So yea... execute cant use the GO word.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 20:06
OK cool, thanks for all of the info. Sounds like I will need to find a Gem that works with my old version or split my files on GO... or just not use GO in any of the files.
Ben Kanouse
@kanobt61_twitter
Jan 18 2017 20:30

@metaskills This ended up working out for me

 File.read(file_name).split(/GO\r*$/).each { |s| ActiveRecord::Base.connection.execute s }

Thanks again.