EzDevInfo.com

Which ports are required to connect to SQL Server from tiny_tds? [closed]

There is not route between the web server (CentOS, 192.168.10.100) and the SQL Server (192.168.12.200).

I have installed tiny_tds on the web server. Which network ports are required to add to the routes in order for tiny_tds to read/write SQL Server data?

Error installing TinyTDS on OSX 10.6 via gem install tiny_tds

I have been trying to install the TinyTDS gem on a Macbook with OSX 10.6. I was successful in installing Free TDS -- confirmed that it works via:

tsql -H SERVER -p 1433 -U username.

The error I'm getting from gem install tiny_tds is:

Building native extensions.  This could take a while...
ERROR:  Error installing tiny_tds:
    ERROR: Failed to build gem native extension.

        /Users/jason/.rvm/rubies/ruby-1.9.3-p194/bin/ruby extconf.rb
checking for iconv_open() in iconv.h... no
checking for iconv_open() in -liconv... yes
checking for sybfront.h... yes
checking for sybdb.h... yes
checking for tdsdbopen() in -lsybdb... no
-----
freetds is missing.
-----
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

I've tried specifying the TDS lib and include dirs (/usr/local/lib and /usr/local/include) without luck. I've tried reinstalling FreeTDS from source manually as well as via homebrew, but that made no difference.

I've seen a few similar issues posted, but they seem to reference an issue with locating iconv libraries which I appear to be moving beyond.

Any suggestions or references I may have overlooked? Thanks in advance.

Rails with different two Databases

I have used two different databases for my rails application: MongoDB and MsSQL using mongoid and activerecord-sqlserver-adapter adaptor respectively. Everything is well but there is a problem while generate Model. The problem is "how can I generate the model that relats to MongoDB or MsSQL differently?"
For example: I want to generate People model relates to MongoID and Animal model with MsSQL. While I generate with command: rails g model Animal name:string it generates the model related to mongoid. How can I generate the model Animal with ActiveRecord that means related to MsSQL.
Please help me. Thanks

Tiny_tds: Connect: Server name not found in the configuration files

require "rubygems"
require "tiny_tds"

client = TinyTds::Client.new(:username => 'sa', :password => '', :host => 'RICHARD_PC\SQLEXPRESS')
result = client.execute("SELECT * FROM [Contacts]")

result.each do |row|
   //Do something
 end

I keep getting the same error: "Connect: Server name not found in the configuration files". All I need to do is to be at least be able to connect with Sql Server. So if the host is not the sqlexpress instance installed on my machine, what is it then? In the Github website it said this host => 'mydb.host.net' (:host - Used if :dataserver blank. Can be an host name or IP.)

Thanks for helping.

TinyTds::Error: Adaptive Server connection failed

This is an odd one. I have some ruby code on my machine which uses the tiny_tds version: 0.6.0.rc1

and everything works fine. When a co-worker attempt to run the same code he gets the following error:

TinyTds::Error: Adaptive Server connection failed

We are connecting without providing username or password as it's not needed on my machine. Any ideas? could this be a rights thing on the sql database?

Thanks in advance

It should be noted on the machine with issues we have SQL 2008 R2 and SQL Express installed. We can connect to SQL express but not SQL 2008 R2

Here's the code we are using

def self.GetTestMprsFromDB(dataServer,database,query)

mprids = Array.new

client = TinyTds::Client.new(:dataserver => dataServer, :database => database, :timeout => 1000)

When stepping into tiny_tds on initialize

def initialize(opts={})
  if opts[:password] && opts[:password].to_s.strip != ''
    opts[:password] = opts[:password].to_s
    warn 'FreeTDS may have issues with passwords longer than 30 characters!' if opts[:password].length > 30
  end
  raise ArgumentError, 'missing :host option if no :dataserver given' if opts[:dataserver].to_s.empty? && opts[:host].to_s.empty?
  @query_options = @@default_query_options.dup
  opts[:appname] ||= 'TinyTds'
  opts[:tds_version] = TDS_VERSIONS_SETTERS[opts[:tds_version].to_s] || TDS_VERSIONS_SETTERS['71']
  opts[:login_timeout] ||= 60
  opts[:timeout] ||= 5
  opts[:encoding] = (opts[:encoding].nil? || opts[:encoding].downcase == 'utf8') ? 'UTF-8' : opts[:encoding].upcase
  opts[:port] ||= 1433
  opts[:dataserver] = "#{opts[:host]}:#{opts[:port]}" if opts[:dataserver].to_s.empty?
  connect(opts)
end

At end it throws the error

Loop through and list a 2 column db table from Tinytds via ruby with column1 values as headings and column2 values as a list

I've been trying to learn the basics of loops and I'm getting there but I'm struggling to get my brain around the following requirement where, for example, I've got data from a database table via tinytds, such that key/values are hashed into an array it seems(? - beginner here, so hope my terminology is correct!):-

The data is such that the values from one 'column' of the db table is repeated many times, whilst the values of the 2nd 'column' are unique.

Therefore, rather than simply creating a table in HTML for output that simply has 2 columns with column1 showing the same line of text over and over (before the next unique string lists over and over), I'd like to present the information so that unique string values from column1 are headings and the values of column2 are then listed beneath.

In my mind, I think what I'm trying to achieve is to loop through column1 for each unique string, output that value, whilst running an inner loop to list all values for column2 where column1 is 'string', moving on to the next unique value of column1,output that value, then loop through column2 again to list all values where column1 is 'string2' etc.

Hope that makes sense as I'm struggling to know how to explain in the correct terminology.

Thanks.

For example, if my 2 columns from my database table were as follows:-.

Column1
Q1
Q1
Q1
Q2
Q2
Q3
Q3
Q3

Column2
A1
A2
A3
A4
A5
A6
A7
A8

How could I loop (or whatever is best) through to be able to present the output such as:-

Q1
A1
A2
A3

Q2
A4
A5

Q3
A6
A7
A8

I can write the HTML side to format the output, I just can't figure out the ruby side.

For a single loop I've picked up from tiny_tds examples the following ruby (the html here is just to test placement for now):-

<% narrative.each do |question| %>
<span><%=question.values[1] %></span>
<br/>   
<% end %>

That allows me to list all the values for slot 2 (is that correct terminology?) and I could do something like:-

<% narrative.each do |question| %>
<span><%=question.values[0] %></span><span><%=question.values[1] %></span>
<br/>   
<% end %>

To show both side by side, but what I'm wanting to do is be more like:-

<h1>Question1 goes here</h1>
<ul>
<li>Answer 1</li>
<li>Answer 2</li>
<li>Answer 3</li>
</ul>
<h1>Question2 goes here</h1>
<ul>
<li>Answer 4</li>
<li>Answer 5</li>
</ul>

etc.

How to keep a persistent connection to SQL Server using Ruby Sequel and Tiny_TDS while in a loop

I have a ruby script that needs to run continually on the server. I've daemonized it using the daemon gem, and in my script I have it running in an infinite loop, since the daemon gem handles starting and stopping of the process that kicks off my script. In my script, I start out by setting up my DB instance using the Sequel gem and tiny_tds. Like so:

DB = Sequel.connect(adapter: 'tinytds', host: MSSQLHost, database: MSSQLDatabase, user: MSSQLUser, password: MSSQLPassword)

Then I have a loop do that is my infinite loop. Inside that, I test to see if I have a connection using DB.test_connection and then I query the DB every second or so to check if there is new content using a query such as:

DB['SELECT * FROM dbo.[MyTable]'].all do |row|
    # MY logic here
    # As part of my logic I test to see if I need to delete this row in the table and if so I use
    DB.run('DELETE FROM dbo.[MyTable] WHERE some condition')
end

Then at the end of my logic, just before I loop again, I do:

sleep 1
DB.disconnect

All of this works great for about an hour to an hour and a half with everything checking the table, doing the logic, deleting rows, etc., then it dies and gives me this error message TinyTds::Error: Adaptive Server connection timed out

My question, why is that happening? Do I need to reformat my code in a different way? Why doesn't the DB.test_connection do what it is advertised to do? The documentation on that says it checks for a connection in the connection pool, and uses it if it finds it, and creates a new one otherwise.

Any help would be much appreciated

Is method of generating sql query for orderby changed between tiny_tds 0.2.3 and 0.4.3

I had rails 3.0.1 and tiny_tds 0.2.3 and activerecord-sqlserver-adapter 3.0.7 and the query below was working fine. After upgrading to rails 3.0.20, tiny_tds 0.4.3, activerecord-sqlserver-adapter 3.0.19 it stopped working due to order by clause.

There is a table events with a column starts_at which is of type datetime.

Ruby code:

@events = Event.where("archived = 'False' and starts_at >= ? and event_company_id in (1,2,3)", start_date).select(" distinct top(14) convert(date, starts_at, 112) as start_date").order("convert(date, starts_at, 112)")

used to generate a sql query as follows:

SELECT distinct top(14) convert(date, starts_at, 112) as start_date FROM [events] WHERE (archived = 'False' and starts_at >= '2013-02-04' and event_company_id in (1,2,3)) ORDER BY convert(date, starts_at, 112)

but now, the query being generated is :

SELECT distinct top(14) convert(date, starts_at, 112) as start_date FROM [events] WHERE (archived = 'False' and starts_at >= '2013-02-05' and event_company_id in (1,2,3)) ORDER BY convert(date ASC, starts_at ASC, 112) ASC

Note the part around order by:

ORDER BY convert(date ASC, starts_at ASC, 112) ASC

instead of ORDER BY convert(date, starts_at, 112)

Due to this, I am getting the following error:

TinyTds::Error: Incorrect syntax near the keyword 'ASC'.:

Is there some change in syntax that needs to be followed for such queries or are the versions that I am using not correct? I had to upgrade tiny_tds and activerecord-sqlserver-adapter as a part of migrating from rails 3.0.1 to rails 3.0.20

Thank you.

Connecting to SQL-Azure in rails using tiny_TDS: [BUG] Segmentation fault

We're trying to get rails to talk to a sqlserver db on Azure, we install freeTDS with openssl and libiconv:

./configure --prefix=/usr/local --with-libiconv-prefix=DIR --with-openssl=DIR
make
make install

We then add tiny_TDS and activerecord-sqlserver-adapter to the gemfile:

gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'

$ bundle install

Configure the database:

development:  
  adapter: sqlserver
  host: xxxxxxx.database.windows.net
  mode: DBLIB
  port: 1433
  database: xxxxx
  username: xxxxxxxx
  password: x
  azure: true

Run the server:

$ rails s

Everything works great up until this point, but as soon as you visit the site ruby crashes.

steven@jenny:~/irr$ rails server -p 3001
=> Booting WEBrick
=> Rails 3.0.4 application starting in development on http://0.0.0.0:3001
=> Call with -d to detach
=> Ctrl-C to shutdown server
[2012-07-24 15:37:00] INFO  WEBrick 1.3.1
[2012-07-24 15:37:00] INFO  ruby 1.9.2 (2012-02-14) [x86_64-linux]
[2012-07-24 15:37:00] INFO  WEBrick::HTTPServer#start: pid=7586 port=3001
/home/steven/.rvm/gems/ruby-1.9.2-p318/gems/tiny_tds-0.5.1/lib/tiny_tds/client.rb:68: [BUG] Segmentation fault
ruby 1.9.2p318 (2012-02-14 revision 34678) [x86_64-linux]

-- control frame ----------
c:0048 p:---- s:0232 b:0232 l:000231 d:000231 CFUNC  :connect
c:0047 p:0429 s:0228 b:0228 l:000227 d:000227 METHOD /home/steven/.rvm/gems/ruby-1.9.2-p318/gems/tiny_tds-0.5.1/lib/tiny_tds/client.rb:68

We've tried several versions of ruby: 1.9.2-p318, 1.9.2-p320, 1.9.3-p125. Same error with all of them.

This is on ubuntu 11.10 using rvm and FreeTDS-0.9.1.

Any Ideas on a workaround?

Have I made an error somewhere along the way?

Edit

Output from IRB:

require 'tiny_tds'
client = TinyTds::Client.new(:username =>'XXXXX@XXXXXXX.database.windows.net', :password => 'XXXXXXX', :host => 'XXXXXX.database.windows.net',  :mode => 'DBLIB', :azure => 'true')
SystemStackError: stack level too deep from /home/martinr/.rvm/rubies/ruby-1.9.2-p320/lib/ruby/1.9.1/irb/workspace.rb:80
Maybe IRB bug!!

It seems to be a bug with ruby itself:

/home/martinr/.rvm/gems/ruby-1.9.2-p320/gems/tiny_tds-0.5.1/lib/tiny_tds/client.rb:68: [BUG] Segmentation fault (core dumped)

Unable to `gem install tiny_tds` OS X Mavericks

The Error

$ sudo gem install tiny_tds

Building native extensions. This could take a while...

ERROR: Error installing tiny_tds:

ERROR: Failed to build gem native extension.

/System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/bin/ruby extconf.rb

checking for iconv_open() in iconv.h... no

checking for iconv_open() in -liconv... yes

checking for sybfront.h... yes

checking for sybdb.h... yes

checking for tdsdbopen() in -lsybdb... no

HomeBrew

The above error occurs even after a

$ brew install freetds

Manual Installation

$ tar zxf freetds-stable.tgz

$ cd freetds-0.91

$ ./configure

$ make

$ sudo make install

It appears to be missing the following file in /usr/local/lib/:

• libsybdb.so

Instead I have the following files:

• libsybdb.a
• libsybdb.dylib
• libsybdb.la

Research

• https://github.com/Homebrew/homebrew/issues/24550
• ROR + Unable to install tiny_tds

TinyTds::Error: Unable to open socket

I've researched this for a few hours now and I can't seem to find a solution.

I have a Rails 2 app that uses the TinyTds gem ( tiny_tds ) to connect to an SQL 2000 server hosted locally in our company.

The app has been connecting to SQL Server almost every day for the past 6 months with no problem.

Suddenly, when trying to connect yesterday, I started getting an error:

TinyTds::Error: Unable to open socket
from /var/lib/gems/1.8/gems/tiny_tds-0.4.5/lib/tiny_tds/client.rb:60:in `connect'
from /var/lib/gems/1.8/gems/tiny_tds-0.4.5/lib/tiny_tds/client.rb:60:in `initialize'

From my research, I've seen some people suggesting that I use SO_REUSEADDR to allow it to connect and ignore timeout or "usage" restrictions, like this: setsockopt(sock, SOL_SOCKET, SO_REUSEADDR) but I cannot figure out how to use that command within the current context. I tried calling it in commmandline on the server and it won't work, but then again, I'm not sure what it does or if I can even call that from commandline.

I've tried calling netstat -a which shows current sockets, but I'm not sure what to do with that information.

I've also seen that service restart
can restart a socket, but I'm not sure which socket to restart.

Lastly, in my freetds.conf configuration file, I have these settings:

host = 192.168.0.220 
port = 1433
tds version = 8.0

I'm not really sure which road to take. I'm comfortable with Rails but this socket stuff is way outside my current understanding. This is also a Rails 2 app ( legacy ) which is key to some core processes our business uses. We can't upgrade to Rails 3 due to SQL server adapter gem for SQL 2000 Server won't work with Rails 3.

Can anyone help ?

`rake db:schema:dump` creates schema with empty system tables [duplicate]

I am creating test and development databases that mirror the schema of an existing production database. First, I create db/schema.rb by dumping the production schema:

RAILS_ENV=production rake db:schema:dump

This creates a schema.rb which has an empty table for each system table:

ActiveRecord::Schema.define(version: 0) do

  create_table "MSreplication_objects", id: false, force: :cascade do |t|
  end

  ...    

These empty tables cause an error when I try to create the test and development databases:

$ rake db:reset
-- create_table("MSreplication_objects", {:id=>false, :force=>:cascade})
rake aborted!
ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near ')'.: CREATE TABLE [MSreplication_objects] ()

If, in schema.rb, I delete the definitions for the system tables, then the databases are created normally:

$ rake db:reset
-- create_table("Org", {:primary_key=>"org_id", :force=>:cascade})
   -> 0.0434s
   -> -1 rows
...

How can I keep rake db:schema:dump from dumping the definitions for empty system tables that cannot be created?

Versions:

• Microsoft SQL Server 2014 - 12.0.2000.8 (x64)
• rails (4.2.1)
• activerecord-sqlserver-adapter (4.2.4)
• tiny_tds from github:
  • git://github.com/rails-sqlserver/tiny_tds.git
  • Commit c4e59ba82c0cc55a5587cec1b7d5100d1b1ccaf4

Does TinyTDS support Ruby 2.0.0?

I am having real trouble trying to get TinyTDS working with Ruby 2.0.0 on Windows 7.

When i run a rails c (or require tiny_tds from an irb), i get the following:

D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:251:in `require': cannot load such file -- tiny_tds/tiny_tds (LoadError)
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:251:in `block in require'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:236:in `load_dependency'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/activesupport-3.2.13/lib/active_support/dependencies.rb:251:in `require'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/tiny_tds-0.5.1-x86-mingw32/lib/tiny_tds.rb:16:in `rescue in <top (required)>'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/tiny_tds-0.5.1-x86-mingw32/lib/tiny_tds.rb:12:in `<top (required)>'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/bundler-1.3.5/lib/bundler/runtime.rb:72:in `require'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/bundler-1.3.5/lib/bundler/runtime.rb:72:in `block (2 levels) in require'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/bundler-1.3.5/lib/bundler/runtime.rb:70:in `each'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/bundler-1.3.5/lib/bundler/runtime.rb:70:in `block in require'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/bundler-1.3.5/lib/bundler/runtime.rb:59:in `each'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/bundler-1.3.5/lib/bundler/runtime.rb:59:in `require'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/bundler-1.3.5/lib/bundler.rb:132:in `require'
            from S:/code/user/app/ruby2_test/config/application.rb:7:in `<top (required)>'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/commands.rb:39:in `require'
            from D:/Ruby/Ruby200/lib/ruby/gems/2.0.0/gems/railties-3.2.13/lib/rails/commands.rb:39:in `<top (required)>'
            from script/rails:6:in `require'
            from script/rails:6:in `<main>'

I have tried installing the latest version of the gem:

gem install tiny_tds --pre

And looking inside:

D:\Ruby\Ruby200\lib\ruby\gems\2.0.0\gems\tiny_tds-0.6.0.rc1-x86-mingw32\lib\tiny_tds

I can see there is no 2.0 directory, which leads me to believe version 2.0 isn't supported. However i can see no mention on the TinyTDS github or any other sites about this issue.

So, am i missing something or does TinyTDS currently just not work with Ruby 2.0?

ArgumentError (invalid byte sequence in UTF-8)

In my Rails 3.1 application (runs on Ubuntu 11.10) , I am using tiny-tds, sqlserver active record adapter. My default database is mysql but I also connect to SqlServer 2005 for business logic. When I connect to sql server 2005 and query the database I get an error which says "ArgumentError (invalid byte sequence in UTF-8)" How can I solve this problem ?

By the way my development machine is Mac Os X lion and there isn't any problem.

Tiny TDS load error whilst running rails server

I get this error message when I run "rails server"

 C:/RailsInstaller/Ruby1.9.3/lib/ruby/gems/1.9.1/gems/activesupport-3.2.1/lib/active_support/dependencies.rb:251:in `require': cannot load such file -- tiny_tds (LoadError)

I have ruby-odbc 0.99994 and activerecord-sqlserver-adapter 3.2.1 installed. My rails server version is 3.2.1.

can someone help me with this error message and how to solve it. Thanks