Microsoft SQL Server

Intro

- http://www.microsoft.com/sql/ - MS SQL Server (search also for Exchange and Access).
- http://directory.google.com/Top/Computers/Software/Databases/Microsoft_SQL_Server/ - MS SQL Server
- http://www.sqlmag.com/ - SQL Server Magazine
- http://www.sql-server-performance.com/sg_from_sybase_to_sql_server.asp - Making the Move from Sybase to SQL Server - by Sayed Geneidy

books

MS SQL Server:

• The Guru's Guide to Transact-SQL (2000) by Kenneth W. Henderson, Ken Henderson
• Professional SQL Server 2000 Programming - by Robert Vieira(2003)
• http://www.sqlmag.com/ - SQL Server Magazine

---------------------------------

Sybase vs MS SQL

Today there are many differences between MS SQL Server and Sybase.

For example, difference in triggers:

Sybase	MS SQL Server
you can not create temporary tables inside a trigger	you can create temp table inside a trigger
Only 3 triggers (insert, update, delete) for a table	multiple triggers for each action (starting ver.7.0) In SQL Server 2000 you can specify the order of firing using sp_settriggerorder
Triggers fire after the data modification (post triggers only)	Pre- and post- triggers (starting with MS SQL Server 2000)

For more info, search for INSTEAD OF triggers, 'inserted table', 'deleted table' and COLUMNS_UPDATED()

unix => MS SQL Server

• http://technet.microsoft.com/en-us/library/bb496496.aspx - Sybase/UNIX to SQL Server 2000: Database Engine Migration and Application Interoperation
• http://www.datadirect.com/products/odbc/unix-linux/index.ssp - commercial solution, has license from Microsoft for ODBC Driver Manager, supports new features for MS SQL Server 2008
• unixODBC.org (= easysoft.com) - ODBC, free / commercial, via TDS (also ODBC-ODBC bridge). Written by Nick Gorham, paid and supported by Easy Soft. Project started in ~1999.
• iodbc.org (= openlinksw.com) - ODBC, free / commercial. Good. Read this ODBC story.
• freeTDS - Open source implementation of Tabular Data Stream protocol which is used by clients for Sybase and Microsoft SQL Server (TDS specs).

More:

• http://www.interopsystems.com/tools/warehouse.htm - software for unix on windows (SFU, not cygwin).
• Automation Technology - OpenRDA ODBC Driver (driver talks to their server on the Windows side, which in its turn accesses the real ODBC driver).
• ODBTP (Open Database Transport Protocol) - talks from unix to ODBC on Windows side. Open-source.

Note: Originally Sybase and Microsoft SQL Server were the same (SQL Server 4.2 was developed as a joint effort by both companies in late 1980s). So it is natural to think that you may use Sybase client to connect to MS SQL Server. And it does work - but will not support all features and will have problems, because over the years Microsoft has changed the connectin protocol several times.

Some relevant links:

• http://www.sqlsummit.com/ODBCVend.HTM - many ODBC drivers and servers
• http://www.sommarskog.se/mssqlperl/unix.html
• http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
• http://www.easysoft.com/developer/languages/perl/index.html - Use Easysoft ODBC drivers with the Perl DBI and DBD::ODBC modules
• http://www.peppler.org/
• http://search.cpan.org/~mewp/sybperl-2.18/DBlib/DBlib.pm
• http://search.cpan.org/~mewp/DBD-Sybase-1.09/Sybase.pm

Access MS SQL and Sybase from one perl script

In perl we use DBI and separate drivers for different databases. Unfortunately there is no DBD::MSSQL - it doesn't exist. So we can use DBD::ODBC, DBD::Sybase, DBD::FreeTDS, DBD::Proxy, etc. The 3 most frequently used solutions are:

• DBD::ODBC with datadirect.com, unixODBC.org , iodbc.org , or freeTDS
• freeTDS with other options. freeTDS web site recommends 4 options for using freeTDS and Perl to connect to a Sybase or MSSQL database:
  • DBD::Sybase - is the recommended option. It works with MSSQL (with some limitations). You will need to install the perl DBI module and the FreeTDS package, particularly the CTLib portion. Set your SYBASE environment variable to /usr/local/freetds and install DBD::Sybase. mailing list archives - you can ask for help.
  • DBD::ODBC - less robust than DBD::Sybase
  • DBD::FreeTDS - minimally functional, alpha-level, not actively developed/supported
  • Sybperl - thin wrapper around the Sybase C APIs developed and supported by Michael Peppler.

Note: there are known problems with freeTDS (http://www.freetds.org/faq.html):

• BCP of text and image types is broken for Microsoft servers.
• BCP does not support TDS 4.2. You must use a more modern protocol version.
• Server-side cursors work only in ct-lib.
• ODBC lacks a client-side cursor implementation.
• DBD::Sybase dynamic SQL placeholders don't work, pending a client-side SQL parser. (The ODBC ones do.)
• There are no plans to implement db-lib's browse mode.

For commercial use you can consider one of 3 solutions with commercial support:

• datadirect.com
• unixODBC.org (= easysoft.com)
• iodbc.org (= openlinksw.com)

Note: There are no native bcp or isql utilities for MS SQL Server on Unix.

perl programming

We should have 2 low-level procedures for commonly used functions.

• $self->connect_sybase() - returns $dbh
• $self->connect_mssql() - returns $dbh
• $self->sql_do($dbh,$sql), $self->sql_query($dbh,$sql), etc.
  • first, check the driver $dbh->{Driver}->{Name} - gives you "Sybase" or "ODBC" ??
  • second, call corresponding sub, for example: do_sql_sybase(), or do_sql_odb

==============
==============
Yet another unixodbc: http://www.cpan.org/authors/id/R/RK/RKIES/ - UnixODBC-0.34.tar.gz (21-Jan-2008 04:25)