SQLTeam.com | Weblogs | Forums

Linked Server using ODBC 64 bit on SQL server 2016

Other SQL Server Topics
#1

Hi Experts,

I am trying to set up a linked server on my SQL 2016 using ODBC from ServiceNow (external application).
I have two ODBC set up. ODBC 32 bit and 64 bit. I need 32 bit because that what my other SSIS process.

The ODBC connection on both version are successful.

However, I keep getting error: 7303 every time I am trying to setup Linked server. According to some of the article, that issue because the SQL server that suppose to use 64 bit keep using ODBC 24 bit.
I am struggling to force SQL server to use ODBC 64 even though I have specify the System DNS of 64 bit.

This is the error message that I am having using ODBC 64 bit.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVICENOW".
OLE DB provider "MSDASQL" for linked server "SERVICENOW" returned message "[SN][ODBC ServiceNow driver][ServiceNow Client]Failed to initialize the Service component.". (Microsoft SQL Server, Error: 7303)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7303-database-engine-error

This is the snapshot of my ODBC 64 bit:
image

This is snapshot of my Linked server setup:
image

Can someone help please?

Cheers,

Susan

#2

I found this link:

https://social.msdn.microsoft.com/forums/sqlserver/en-US/96ca13a3-a39e-4271-aa93-86e20067544f/linked-server-error-7303

And I have a suggestion: Try running SSMS as Administrator (right-click the icon, it should have an option on the pop-up) and try creating the linked server again.

I'm thinking there is a local permission issue stemming from SSMS, rather than ODBC or SQL Server side.

If that does not work, consider a DSN-less connection string for your linked server. You'd enter all the connection details in the "Provider string" box in the linked server wizard. There's an example here:

You may need to modify the DataSource and Product name values to work properly for DSN-less connections. My experience is old, based on doing this in MS Access and oddball DB2 drivers, you may need to set them to empty strings. It will be a lot easier to do this as a T-SQL script, you can script out the linked server definition and then modify it.

1 Like
#3

Thank you so much @robert_volk . I have tried your solution but I still unable to connect.
However, the project is postponed now so I think I will try again when it back.
Really appreciate for your help.