Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1177
  • Last Modified:

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
0
PeterFrb
Asked:
PeterFrb
  • 9
  • 4
1 Solution
 
Brendt HessSenior DBACommented:
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
0
 
David ToddSenior DBACommented:
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
0
 
PeterFrbAuthor Commented:
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
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
PeterFrbAuthor Commented:
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
0
 
David ToddSenior DBACommented:
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
0
 
PeterFrbAuthor Commented:
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
0
 
PeterFrbAuthor Commented:
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
0
 
David ToddSenior DBACommented:
Hi

The screenshot shows how I'd configure a linked server for a ODBC DSN.

You'll need - obviously - a name for the linked server, and possibly a product name.

HTH
  David
sql-linked-server-via-odbc.jpg
0
 
PeterFrbAuthor Commented:
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

0
 
PeterFrbAuthor Commented:
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
0
 
David ToddSenior DBACommented:
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
0
 
PeterFrbAuthor Commented:
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
0
 
PeterFrbAuthor Commented:
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
0
 
PeterFrbAuthor Commented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now