# How to connect and retrieve data from a MS SQL SERVER 2000 d **URL:** \ **Category:** Rails\ **Created:** [April 18, 2007, 2:08pm UTC](https://www.ruby-forum.com/t/how-to-connect-and-retrieve-data-from-a-ms-sql-server-2000-d/92340 "2007-04-18T14:08:42Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![jeremy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/82dd89/32.png) [@jeremy](https://www.ruby-forum.com/u/jeremy)\ **Post date:** [April 18, 2007, 2:08pm UTC](https://www.ruby-forum.com/t/how-to-connect-and-retrieve-data-from-a-ms-sql-server-2000-d/92340/1 "2007-04-18T14:08:42Z")
Good evening ~~HO HO~~ It’s 19:51 in China now. 🙂 I’m new to Ruby on Rails. I wanna know how to connect to a MS SQL SERVER 2000 database. Can I use a T-SQL query in Ruby on Rails directly? If I can do it directly, how to do it? For example, I wanna retrieve all data from a SQL SERVER 2000 table and display it on a page on my site, I use a T-SQL “select \* from table”, how to add it into a ROR-based web application, and I wanna see a table-view result. Thanks… ---
**Author:** ![jeremy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/82dd89/32.png) [@jeremy](https://www.ruby-forum.com/u/jeremy)\ **Post date:** [April 18, 2007, 2:35pm UTC](https://www.ruby-forum.com/t/how-to-connect-and-retrieve-data-from-a-ms-sql-server-2000-d/92340/2 "2007-04-18T14:35:44Z")
Hey Jeremy, Yes can easily connect rails(+ active record of course… 😃 we like active record!!!) up to SQL Server, you just need to install Ruby’s DBI library, along with its support for either ADO or ODBC database drivers depending on how you want to connect. > From there you just set it up like most other database connections in > rails > :adapter =\> “sqlserver”, > :mode =\> “ado”, # or “odbc” depending on your connection. > :database =\> “required for ado”, > :host =\> “localhost”, > :dsn =\> “required for odbc” > From there all you need to do is use a model find, so in your case if > your table was called people you’d create a model called Person and > the a find like this > @people = Person.find(:all) will create an array of all the record in > your MS SQL database. > If you really want to write SQL statements, you could also do > @people = Person.find\_by\_sql('select \* from ‘people’) > and this would return the same result. The problem with that is that > if you ever move your database into another system if you have sql > statement dependencies it’ll kill your app, where as if you use > find(:all) and then later the db got moved to say oracle you could > just change your database.yml file and voila it’ll all work. Bear in mind that Rails tends to work better with convention over configuration, so if your new to rails you might want to play with it and a MySQL or SQL database first, just to get used to it. For instance, rails will expect that your table name is the plural of your model name… Now if your intergrating rails into an existing schema, this can be a little tricky, of course it’s all configurable, but it’s not the simplest way to learn rails. Good luck with it. Cam ---
**Author:** ![jeremy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/82dd89/32.png) [@jeremy](https://www.ruby-forum.com/u/jeremy)\ **Post date:** [April 18, 2007, 2:49pm UTC](https://www.ruby-forum.com/t/how-to-connect-and-retrieve-data-from-a-ms-sql-server-2000-d/92340/3 "2007-04-18T14:49:45Z")
Thanks, cammo! I’ll try your method. And there comes another question, my SQL SERVER database has some different users, for example, a user named “administrator”, he can retrieve or modify all data from Person table; another user named “guest”, he only can see all data from Person, but he can’t modify the date in Person table. How to design a login form? Different users can login via their different user names. ---
**Author:** ![jeremy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/82dd89/32.png) [@jeremy](https://www.ruby-forum.com/u/jeremy)\ **Post date:** [April 19, 2007, 12:14am UTC](https://www.ruby-forum.com/t/how-to-connect-and-retrieve-data-from-a-ms-sql-server-2000-d/92340/4 "2007-04-19T00:14:11Z")
heres a link to the ruby dbi - [http://rubyforge.org/projects/ruby-dbi/](http://rubyforge.org/projects/ruby-dbi/) If your users are pure database users, then this is quite a strange setup, normally I’d imagine you’d give permissions to users with LDAP/ AD and you can access and athenticate these users with ruby ActiveLDAP library, however if they are pure database users(are u sure they are?) then you’ll need to use something like acts\_as\_authenticated and replicate those users in a new users table and give them permissions accordingly. Cam ---
**Author:** ![jeremy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/82dd89/32.png) [@jeremy](https://www.ruby-forum.com/u/jeremy)\ **Post date:** [April 18, 2007, 4:53pm UTC](https://www.ruby-forum.com/t/how-to-connect-and-retrieve-data-from-a-ms-sql-server-2000-d/92340/5 "2007-04-18T16:53:53Z")
BTW, where and how can I get this “Ruby’s DBI library”? I can’t “gem install dbi”. thanks.