A linked server configuration enables Microsoft SQL Server (MSSQL) to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages: Remote server access ; The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise ; and The ability to address diverse data sources similarly.
Theese are the real world scenario : Server A (Windows Server ) is installed with Microsoft SQLServer 2008 (MSSQL 2008) Database equip with SQL Server Management Studio (SSMS), Server B (Windows Server,*NIX, etc) is intalled with MySQL Database.
Next, how we setup a linked server ?
Theese steps are setup on Server A.
1.Download and Install Connector/ODBC Driver (5.1.8 or latest) form MySQL
http://www.mysql.com/downloads/connector/odbc/
2.Create and Configure DSN above ODBC Driver
GoTo START : > Settings > Control Panel > Administartif Tools > Data Source (ODBC) > User DSN > Add > MySQL ODBC 3.51 Driver > Finish
Fill in the blank : Data Source Name, Description , Server, User, Password, Database
GoTo TAB: > Connect Options >
Fill in the blank: Port (MySQL Default Port = 3306)
Click ‘OK’
3.SetUp MSDASQL Provider Properties
Open MSSQL database on Server A, via SQL Server Management Studio (SSMS) 2008.
Goto : SSMS > Server Objects > Linked Servers > Providers > Right Click “MSDASQL“
Check (activate) / tick theese items : Nested Queries, Level Zero Only, Allow Inpocess, Supports “LIKE” Operator
4.Create Linked Server
Open MSSQL database on Server A, via SQL Server Management Studio (SSMS) 2008.
Create Linked Server Object : SSMS > Server Objects > Linked Servers > Right Click > New Linked Server
Fill in the blank : Linked Server, Provider, Product Name, Data Source
Provider: Microsoft OLEDB Provider for ODBC Drivers
5.Create and test it with SELECT statement.
Open “New Query” and test it with “SELECT … OPENQUERY” statement
SELECT * FROM OPENQUERY([FOO], ‘SELECT * FROM FOOTABLE LIMIT 10′)
and..here we go, first problem
Msg 7342, Level 16, State 1, Line 2
An unexpected NULL value was returned for column “[MSDASQL].dtadded” from OLE DB provider “MSDASQL” for linked server “FOO”. This column cannot be NULL.
Ups, i think the driver default configuration cannot handle NULL value properly, and luckly there is a open configuration to manage NULL value.
GOTO : DataSource > ODBC > FOODSN > ADVANCED > CURSOR/RESULT >
Then tick/check
Return SQL_NULL_DATA for zero date
Then again, test it with SQL/OPENQUERY statement. If other probelem (error message) still persist.. , like this one
Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to date.
and it’s time to use ODBC scalar convertion
Explicit data type conversion is specified in terms of SQL data type definitions. The ODBC syntax for the explicit data type conversion function does not restrict conversions. The validity of specific conversions of one data type to another data type will be determined by each driver-specific implementation. The driver will, as it translates the ODBC syntax into the native syntax, reject those conversions that, although legal in the ODBC syntax, are not supported by the data source. The ODBC function SQLGetInfo, with the conversion options (such as SQL_CONVERT_BIGINT, SQL_CONVERT_BINARY, SQL_CONVERT_INTERVAL_YEAR_MONTH, and so on), provides a way to inquire about conversions supported by the data source.
Example:
SELECT dtadded FROM OPENQUERY([FOO], ‘SELECT CONVERT(dtadded, CHAR) dtadded FROM FOOTABLE LIMIT 10′)
That should do the work … Good Luck!