asked on

Attempting to create Linked Server in SQL Server

Hello:

I have a SQL Server application, in which I've created a linked server, but I can not see the resultant tables in the database. I have an established ODBC connection, and I am able to see the tables using WinSQL. Included below in the code section is the connection string that a colleague passed along to me, and he suggested I use the "Microsoft OLE DB provider for ODBC Drivers" as my connection choice. When I attempt to look at the catalogue, the system makes an attempt and then fails after about ten-to-fifteen minutes of trying. The final screenshot in the image shows the error message I finally get after SQL Server's marathon attempt.
Best, ~Peter Ferber

Driver={NetezzaSQL};servername=nantz19.nielsen.com;port=5480;database=FSCOREXXXXBP;username=ferbpe01;password=<mypassword>;

Open in new window

ODBC-SQLServer-Combined.bmp

Brendt Hess

Is this a 32-bit or 64-bit SQL Server instance? If it is 64-bit, you have a challenge ahead of you, as 32-bit ODBC drivers don't work with 64-bit SQL Server

David Todd

Hi,

What is the other server you are attempting to connect to?

I'm in the process of rolling out a 64bit server, and as some of the applications and their databases are older, I can only find 32bit drivers for them, so I'm having to link through a 32bit instance. Amazingly we don't appear to be overly penalised by the second layer on openquery's.

Something I found: If your linked server uses an ODBC DSN, it may not be able to write - insert/update/delete - to that linked server, while selects work okay.

HTH
David

PS Details
SQL Server 2008 R2 64bit
Sybase 11.9
Progress 9.1

PeterFrb

ASKER

Thanks for your responses. I am using a 32-bit machine.

Regarding my ODBC Driver setup, is there a way that I can leverage the work of the successful ODBC connection and tie directly to that? I've seen short and sweet connection strings that do just that, something to the effect of "ODBC=Reefs Prod2".

~Peter

PeterFrb

ASKER

I've been doing a little more research, and I find that with Excel, the connection could not be easier. The set of attached screenshots shows the path for importing, and as you can see, the connection completely leverages the existing ODBC connection to do all of its behind-the-curtain work. I would love to find something equally simple and elegant in either SQL Server, Visual Studio, or both!

Thanks, ~Peter Ferber

ExcelImport.bmp

David Todd

Hi Peter,

ONE way is to create an ODBC DSN, then use that in your linked server setup.

I've found that while this works for selects, it doesn't seem to work so well for inserts/updates/deletes.

Regards
David

PeterFrb

ASKER

Sounds great! How do you do that? I'm happy, at this point, for a read-only look at the data via SQL Server.

Thanks, ~Peter

PeterFrb

ASKER

Actually, I take it back. I do have an ODBC connection to the data that works (see image below). My question is: how do I leverage it in SQL Server, even on a read-only basis?

~Peter

ODBC-DSN.bmp

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterFrb

ASKER

If you go to my very first entry into this question, you'll see my screenshot that shows the "New Linked Server" screen as I filled it out. The irony is that my command succeeded in creating the link, and although the "Test Connection" shows as "Succeeded", I still get the error. Please look carefully at my combined screenshot: I've fully laid out everything I've tried thus far and the point at which it is failing. That represents the starting point from which I would like to move forward.

Thanks, ~Peter

PeterFrb

ASKER

I have more information about my inquiry. In attempting to create a linked server using the OLE DB Provider for ODBC Drivers, the DBA responded to my inquiry saying that Netezza "is not an OLEDB ODBC compliant driver", which probably explains why my attempts to use it haven't worked. If SQL Server could simply leverage the information embodied in the DSN, I would be good to go. Is this possible?
~Peter

David Todd

Hi Peter,

If you look at my screen shot, I don't have anything on the provider line - you are not using the DSN that you created.

HTH
David

PeterFrb

ASKER

OK. I'm heading to work tomorrow. I'll try your method. I'll be amazed and celebratory if it proves to be that simple!
Thanks, ~Peter

PeterFrb

ASKER

Hi, David:

I did try your method, and I got the same error I got previously (see image below). Interestingly, the DSN dialogue includes a place for me to insert the password, but, for some reason, it's failing here. Good try, though.

~Peter

DSN-Failure.bmp

PeterFrb

ASKER

I have subsequently found out that the data source I'm accessing has limitations on it about which I was previously unaware. Thanks for your combined helps, though. I'm on a new job and wandering through the jungle: the exploration has revealed several key issues that have helped me make forward strides, and I apprecate your support.
~Peter