search

rails-sqlserver / activerecord-sqlserver-adapter

SQL Server Adapter For Rails
MIT License
972 stars 558 forks source link

4.2.2 change_column_null #387

Closed DnaAnalyticsOld closed 9 years ago

DnaAnalyticsOld commented 9 years ago

change_column_null :table_name, :column_name, false

in migration causes:

ODBC::Error: 25000 (3903) [Microsoft][SQL Server Native Client 11.0][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.: ROLLBACK TRANSACTIONC:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.2/lib/active_record/connection_adapters/sqlserver/database_statements.rb:286:in `do'

metaskills commented 9 years ago

I have not had a chance to run the tests on ODBC. It is getting harder and harder to make that stack work. Is this the case when using TinyTDS too?

DnaAnalyticsOld commented 9 years ago

good question. odbc has worked for years. so just kept it. would prefer to use your recommended TinyTDS.

unfortunately, spent last hour trying to configure without success. enabled TCP/IP, TC Port to 1433, and restarted services (as shown below). gotten various errors. with configuration below, get "TinyTds::Error: Login failed for user 'Celadon'." if un-comment dataserver, i get "TinyTds::Error: Server name not found in configuration files". but i can't figure out where configuration file is. have searched forums and have tried many variations on host and dataserver.

can you help with configuration?

development: adapter: sqlserver host: localhost database: development

dataserver: 'localhost\ROADIE\SQLEXPRESS2014'

mode: odbc

dsn: development

username: Celadon password: xxxxxxxxx

[image: Inline image 1]

On Thu, Feb 5, 2015 at 12:03 PM, Ken Collins notifications@github.com wrote:

I have not had a chance to run the tests on ODBC. It is getting harder and harder to make that stack work. Is this the case when using TinyTDS too?

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73083751 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

configure without success

Are you on Windows? If so, TinyTDS for Windows comes with FreeTDS precompiled and statically linked within TinyTDS.

DnaAnalyticsOld commented 9 years ago

yes, I am on windows. i apologize. I don't know implication of precompile and static link. do I need to follow directions in readme.md for ## Compiling Gems for Windows? if so, i would need someone to lead me through that step by step. what would it cost for you to provide that service?

On Thu, Feb 5, 2015 at 3:14 PM, Ken Collins notifications@github.com wrote:

configure without success

Are you on Windows? If so, TinyTDS for Windows comes with FreeTDS precompiled and statically linked within TinyTDS.

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73118176 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

It is much simpler than any of that. Just add the gem to your Gemfile and bundle install. Done. If you need to work with a freetds.conf, then the Windows binary uses --sysconfdir="C:/Sites for that configure arg. So that is where you can put a cont file if needed, but should not be necessary.

DnaAnalyticsOld commented 9 years ago

still get login error for user 'Celadon' with host: localhost.

tiny_tds added to gem file. bundle install and bundle update run. restarted machine. confirmed sql server service running. TCP/IP enabled. TCP Port

  1. development database attached. accessible via Mgmt Studio. sql server name is ROADIE\SQLEXPRESS2014.

database.yml is:

development: adapter: sqlserver host: localhost database: development username: Celadon password: xxxxxxx

tried various incantations of host: localhost\ROADIE\SQLEXPRESS2014 and dataserver: localhost\ROADIE\SQLEXPRESS2014.

C:/Sites/freetds.conf

[global] tds version = 7.2

[myserver] host = 'localhost' port = 1433 username = 'Celadon' password = 'xxxxx'

no effect.

On Thu, Feb 5, 2015 at 3:35 PM, Ken Collins notifications@github.com wrote:

It is much simpler than any of that. Just add the gem to your Gemfile and bundle install. Done. If you need to work with a freetds.conf, then the Windows binary uses --sysconfdir="C:/Sites for that configure arg. So that is where you can put a cont file if needed, but should not be necessary.

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73121490 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

Can you confirm with telnet or some other basic tool that your local server is indeed serving traffic to localhost on 1433. My guess is that it is not. Have you see this section of our test helpers that talk about a few details you might be facing? https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/RUNNING_UNIT_TESTS.md#troubleshooting

DnaAnalyticsOld commented 9 years ago

thanks. probably right, port not open. can't figure out telnet but seems to be telling me 80 is open and 1433 not. opened 1433 using wc.msc. didn't help. i'll try locating windows administrator.

On Thu, Feb 5, 2015 at 4:41 PM, Ken Collins notifications@github.com wrote:

Can you confirm with telnet or some other basic tool that your local server is indeed serving traffic to localhost on 1433. My guess is that it is not. Have you see this section of our test helpers that talk about a few details you might be facing? https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/RUNNING_UNIT_TESTS.md#troubleshooting

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73134677 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

Remember, read that link too.

DnaAnalyticsOld commented 9 years ago

forgot to open outbound rule. have done so. no help. i am on windows dev box for everything - rails app + sql server. not trying to hit sql server from linux box.

On Thu, Feb 5, 2015 at 5:39 PM, Ken Collins notifications@github.com wrote:

Remember, read that link too.

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73145455 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

Please report back once you have TinyTDS working.

DnaAnalyticsOld commented 9 years ago

apologies in advance for being so needy. tried again this am, can't connect. if you want to do webex to see my machine, or log in remotely, fine with me.

here's results of efforts:

command shell netstat -an|find/i"listening" reports 1433 as listening. telnet localhost 1433 generates blank screen. allegedly means port is open and listening.

windows firewall have inputted inbound and outbound rules. perhaps not properly? need to specify sql server as named program, rather than hoping meets firewall's (unnamed) conditions? (see screenshot below).

SqlServer service running; database attached; tcp/ip and pipes enable; port 1433 specified.

TinyTDS development: adapter: sqlserver host: localhost database: development username: Celadon password: xxxx

[image: Inline image 1]

On Thu, Feb 5, 2015 at 10:05 PM, Ken Collins notifications@github.com wrote:

Please report back once you have TinyTDS working.

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73175883 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

Start up an IRB prompt and show me output from the following:

> require 'tiny_tds'
> TinyTds::VERSION
> client = TinyTds::Client.new username: 'Celadon', password: 'xxxx', host: 'localhost'
> client.execute("SELECT 1 AS [one]").each.first
DnaAnalyticsOld commented 9 years ago

Will report in 2 hrs waiting 1 hr to get daughter's sprained snowboard wrist x-rayed sigh On Feb 6, 2015 8:10 AM, "Ken Collins" notifications@github.com wrote:

Start up an IRB prompt and show me output from the following:

require 'tiny_tds'> TinyTds::VERSION> client = TinyTds::Client.new username: 'Celadon', password: 'xxxx', host: 'localhost'> client.execute("SELECT 1 AS [one]").each.first

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73233970 .

DnaAnalyticsOld commented 9 years ago

Client.new generates Login failed for user 'Celadon'

copied below that is info about ODBC connection that is fine.

copied below that is info about Sql server login. had inspiration (still failed). username/pwd combo for Windows ODBC/DNS and not SQL Server? sure enough, no celadon user in sql server based on mgmt studio. so created one. unfortunately, Celadon/65xxxx still fails in tinytds for same reason, login failed for user celadon.

[image: Inline image 1]

production: adapter: sqlserver host: localhost mode: odbc dsn: development username: Celadon password: 65xxxxxxxxxx

[image: Inline image 3]

[image: Inline image 4]

[image: Inline image 5]

[image: Inline image 7] [image: Inline image 6]

On Fri, Feb 6, 2015 at 8:10 AM, Ken Collins notifications@github.com wrote:

Start up an IRB prompt and show me output from the following:

require 'tiny_tds'> TinyTds::VERSION> client = TinyTds::Client.new username: 'Celadon', password: 'xxxx', host: 'localhost'> client.execute("SELECT 1 AS [one]").each.first

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73233970 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

1) Can you please use the Github interface when replying vs email? 2) You did not paste all the info I requested. I did not see what I wanted to see in the version line, etc.

DnaAnalyticsOld commented 9 years ago

report of irb session below. sql server config issue? i can log into sql srvr mgmt studio using windows authentication. but not sql server authentication. i have created 'Celadon' user, specified 'development' as default db, sysadmin role, and user mapping to development.

irb(main):001:0> require 'tiny_tds' => true irb(main):002:0> TinyTds::VERSION => "0.6.2" irb(main):003:0> client = TinyTds::Client.new username: 'Celadon', password: 'xxx', host: 'localhost' TinyTds::Error: Login failed for user 'Celadon'. from C:/Ruby200/lib/ruby/gems/2.0.0/gems/tiny_tds-0.6.2-x86-mingw32/lib/tiny_tds/client.rb:77:in connect' from C:/Ruby200/lib/ruby/gems/2.0.0/gems/tiny_tds-0.6.2-x86-mingw32/lib/tiny_tds/client.rb:77:ininitialize' from (irb):3:in new' from (irb):3 from C:/Ruby200/bin/irb:12:in

' irb(main):004:0> client.execute("SELECT 1 AS [one]").each.first NoMethodError: undefined method execute' for nil:NilClass from (irb):4 from C:/Ruby200/bin/irb:12:in
' irb(main):005:0>

metaskills commented 9 years ago

Celadon, your issue looks pretty simple to me. You just have to allow that user to authenticate via server authentication.

DnaAnalyticsOld commented 9 years ago

yes, that was the case. thank you for operating as my defacto sql server admin. i know that is not your job. would you like me to write up step-by-step instructions as best i can for tcp/ip sql server authentication?

as it turns out, original issue of rails change_column_null persists. while this is not a show stopper for me, my original intent was to contribute to your adapter product. thank you for all your help.

C:\immunQC>rake db:migrate --trace RAILS_ENV=development * Invoke db:migrate (first_time) * Invoke environment (first_time) * Execute environment * Invoke db:load_config (first_time) * Execute db:load_config * Execute db:migrate == 20150205120633 NullFalseTypeColumns: migrating ============================= -- change_column_null(:proventus_arrays_elements_layout_codes, :type, false) rake aborted! StandardError: An error has occurred, this and all later migrations canceled:

TinyTds::Error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.: ROLLBACK TRANSACTIONC:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-s qlserver-adapter-4.2.2/lib/active_record/connection_adapters/sqlserver/database_statements.rb:284:in do' C:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.2/lib/active_record/connection_adapters/sqlserver/database_statements.rb:284:inraw_conn ection_do' C:/Ruby200/lib/ruby/gems/2.0.0/gems/activerecord-sqlserver-adapter-4.2.2/lib/active_record/connection_adapters/sqlserver/database_statements.rb:237:in `block in do_execute'

metaskills commented 9 years ago

yes, that was the case. thank you for operating as my defacto sql server admin.

Glad you made it there!

as it turns out, original issue of rails change_column_null persists my original intent was to contribute to your adapter product.

Did you want to write up a tested patch and do a PR?

DnaAnalyticsOld commented 9 years ago

again i broadcast my ignorance. what is a tested patch and pr? be glad to write up brief description of configs/steps it took to replace odbc with tinytds, for your inspection. one limiter is don't have fresh machine. so don't have means to independently test instructions. also, haven't tried backing out any piece. so while have configuration that works, don't know that all steps/configurations are required/necessary.

metaskills commented 9 years ago

When you said "my original intent was to contribute", I took that as you wanted to add code and fix the bug. Most open source projects like this are not run by a single person (like myself) doing all the work. Github is built so you can fork projects, make them better with your own commits, then give back to the original project using a PR which is short hand for a pull request. This project like many others does require that contributed code be tested too.

If you are not up to that, that's fine. I will eventually find time for this reported bug and do it myself. Cheers.

DnaAnalyticsOld commented 9 years ago

i probably don't have expertise to address problem but i'll take look at source and see if i can make progress.

On Sun, Feb 8, 2015 at 12:03 PM, Ken Collins notifications@github.com wrote:

When you said "my original intent was to contribute", I took that as you wanted to add code and fix the bug. Most open source projects like this are not run by a single person (like myself) doing all the work. Github is built so you can fork projects, make them better with your own commits, then give back to the original project using a PR which is short hand for a pull request https://help.github.com/articles/using-pull-requests/. This project like many others does requires that contributed code be tested too.

If you are not up to that, that's fine. I will eventually find time for this reported bug and do it myself. Cheers.

— Reply to this email directly or view it on GitHub https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/387#issuecomment-73419767 .

Raymond J. Peterson, Ph.D. Founder & Chief Science Officer Celadon 6525 Belcrest Rd., Suite 521 Hyattsville, MD 20782 301.683.2118 www.celadonlabs.com www.linkedin.com/in/raymondjpeterson

metaskills commented 9 years ago

You are using v4.2.2 of the adapter. Please update to the latest, it has a fix for this.