SQL Server Performance Forum – Threads Archive

Using Transact SQL and ODBC

Hello, I have a MySQL 4.1.8 server and a SQL 7 server. What I want to do is access the data tables from the MySQL server from stored procedures in the SQL 7 server. The reason for this is so I can run Transact SQL SP’s for Crystal Reports and combinde the two databases data without merging them. I have the MySQL ODBC driver so I can make a system DNS if needed. If anyone else has any other ideas of how to accomplish what I am needing please let me know. I would like to be able to do this directly from the SP’s if possible Thanks Brad West

I doubt on the ODBC version compatibility on MySQL version, may check under MySQL forums for more information. I’m sure you can achieve the same using linked servers on the SQL server side of it.
http://forums.mysql.com/read.php?60,20697,20697
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=245918
Fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks…I will give that a try and let you know what I find out. Brad

It is working without a problem. All I needed to do was Create a DSN on the SQL 7 server for the MySQL connection. Them create a linked server using EXEC sp_addlinkedserver. Once I had that created I had to use OPENQUERY(LinkedServer, ‘select * from tblMyTable’) in the SP. One trick is for this type of connection the SET ANSI_NULLS needs to be ON which is not standard to what SQL 7 does when creating a SP with the wizard. Thanks Brad

BOL mentiones:
For a script to work as intended, regardless of the ANSI nulls database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that may contain null values. For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed. SET ANSI_NULLS should be set to ON for executing distributed queries. SET ANSI_NULLS also must be ON when creating or manipulating indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error listing all SET options violating the required values. In addition, when executing a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select as though there were no such indexes on the tables or views. Note ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL also must be set to ON, while NUMERIC_ROUNDABORT must be set to OFF.
The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to SQL Server. SET ANSI_NULLS defaults to OFF for connections from DB-Library applications. When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled. The setting of SET ANSI_NULLS is set at execute or run time and not at parse time. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>