Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dshehan
Starting Member
2 Posts |
 Posted - 2008-07-23 : 19:55:44
|
| We are a SQL Server 2005 house that has one sqlbase db that we would like to link to. We really just need to read the data in the sqlbase db. We have tried using an ODBC driver that we found online that did not work. Has anybody done this successfully? Thanks for your help! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-23 : 23:30:26
|
| You mean linked server doesn't work? Did you check with sqlbase vendor for driver? |
 |
|
|
dshehan
Starting Member
2 Posts |
Posted - 2008-07-29 : 09:58:36
|
| It is an older version of the driver supplied by ADP with their solution. Something like 3.1.xxx. We are getting no help from ADP and are probably going to try the DBMoto replication tool from Unify.Thanks! |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2008-07-29 : 11:51:31
|
| Follow these steps. I was able to create one and run queries. These steps were designed for SQL 2000 but if you are familiar with SQL Linked Server then you should not have any problem.---------------------------------------------------------------------Configuring a MicroSoft SQL Server Linked Server with Sybase ASE(TDS) OLEDB Provider Using NT AuthenticationIntroductionThis document tells you how to configure a MicroSoft linked server to connect to Sybase Adaptive Server Enterprise (ASE). This configuration enables you to log into the MicroSoft SQL Server and run queries and stored procedures against ASE, as well as transfer data between the MicroSoft SQL Server and ASE. The configuration described here allows you to use NT Authentication to connect to the MicroSoft linked server if desired. However, if you do not require NT Authentication to the linked server, Sybase ASE and MicroSoft SQL Server can use their own logins to communicate with each other through the linked server. This documentation applies to MicroSoft SQL Server 2000 and Sybase ASE OLEDB Provider 12.5 and 12.5.1. The configuration described here can be used with any ASE server supported by Sybase as of the date of this white paper.Configuring ASEPerform the following steps to configure ASE to communicate with the MicroSoft SQL Server:1. Create a user login on the Sybase ASE. The user needs select permission on the database to be accessed. This example uses the user ID “linked” and the password “server”.2. Create a Data Source in the OLEDB Configuration Manager. To run the Configuration Manager, go to Start -> Programs, -> Sybase -> OLEDB Provider ->Configuration Manager. Configure the Data Source to connect to the desired Sybase ASE. This example demonstrates the creation of a Data Source called SYB_LINK. Configure MicroSoft SQL ServerPerform the following steps to configure MicroSoft SQL Server to connect with ASE:1. Run the the MicroSoft SQL Server Enterprise Manager by going to Start -> Programs -> MicroSoft Sql Server -> Enterprise Manager. On the left you will see a folder called Console Root. Below that is the MicroSoft SQL Servers folder. Click the “+” to expand the MicroSoft SQL Servers folder, then expand the following items: - SQL Server Group- (local) (Windows NT)- Security- Linked Servers2. Right mouse click on Linked Servers, then select “New Linked Server…” This brings up the “Linked Server Properties – New Linked Server” window. 3. Give the linked server a name. In this example, “SYB_LINK” is chosen as the name of the linked server. 4. Under “Server type”, choose “Other data source”. In the Provider name scroll list, select “Sybase ASE OLE DB Provider”. 5. In the “Data source” box enter the name of the OLEDB data source you set up previously. In this example it is also called “SYB_LINK”. Note: Not all fields are required in the Linked Server Properties window. The following fields are not required for the connection to function correctly:- “Product Name” is an identifier for your own use.- “Provider String” can be used to pass any connection parameters if needed. - “Catalog” is to identify the primary Database to be used as the default connection. 6. Next, click the Provider Options Button. This opens a window named “Provider Options - Sybase ASE OLEDB Provider”.7. In the “Provider options” list choose “Allow InProcess”, then click OK.Note: When you choose “Allow InProcess”, SQL Server allows the OLE DB Provider to be instantiated as an in-process server. By default, OLE DB Provider is instantiated outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the OLE DB provider. However, MicroSoft SQL Server requires an in-process server for handling specific types of data including long columns, text, and image data. 8. Click OK on the Provider Options window to close it. 9. Next, in the Linked Server Properties window, choose the Security Tab. 10. Using the radio buttons in the lower half of the window, set “For a login not defined in the list above, connections will:” to “Be made using this security context”.11. In the field “Remote login:” enter “linked”. This is the login you created earlier in the section “Configuring ASE”. 12. In the field “With Password” enter “ server”. This is the password you created in earlier in the section “Configuring ASE”.Note: This Linked Server parameter cannot save a NULL password. You must enter a password. 13. Click OK. This creates the Linked Server. You will now see the new linked server under the Security – Linked Servers folder: Test the ConnectionFollow these steps to test the connection to the linked ASE:1. Run the query analyzer. The executable usually resides in:C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isqlw.exe 2. When you see the window “Connect to SQL Server”, select the MicroSoft SQL Server of your choice from the drop down selection box next to SQL Server, then choose “Windows authentication”. Click OK. |
|
|
|
|
|
|
|
|
Linking sqlbase to sql server 2005