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)
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.
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.
