Archive

Posts Tagged ‘Linked Server’

How resolve error OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error – MSDN TSQL forum


–> Question:

Im getting following error:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

I am using the following query:

select * 
from openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=\\abc\k\excel.xlsx',[sheet1$])

driver Microsoft.ACE.OLEDB.12.0 installed and configure properly.

Any help ?
 

–> Answer:

Check this link: https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/

You might need to register the ACE OLEDB provider, go thru all the steps I’ve mentioned in my blog, may help.
 

Ref link.


Error Msg 7302, Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server – MSDN TSQL forum

April 10, 2015 Leave a comment

–> Question:

Below is the script to export data from Access 2003 using SQL server 2012 in SQL Server Management Studio:

EXEC sp_addlinkedserver 
   @server = 'SNE_SNAP2014', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'T:\Data Management\Project -SNAP SNE\SNE SNAP2014 T-Drive.mdb'
GO

this results into following error:

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “SNE_SNAP2014”.

 

–> My Answer:

Please make sure that both Access & SQL Server 2012 should be on same 32 or 64 bit architecture.

Check this blog post for resolution and more info: https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/
 

Ref Link.


SQLNCLI11 for linked server “XYZ” returned message “Requested conversion is not supported” | SQL Server 2012 upgrade behavior changes

March 27, 2015 1 comment

This was a year long pending post that got lost in my blog posts archives. Today while filtering the Posts on my Dashboard I saw this in Edit mode. So I thought to make it live after doing some edits. So, here it goes 🙂

While upgrading to SQL Server 2012 from SQL Server 2008 R2 on my DEV box, I observed some behavioral changes with SQL 2012, one ETL job that was working fine on SQL 2008 R2 was not running and throwing following error in SQL 2012:

OLE DB provider “SQLNCLI11” for linked server “MyLocal” returned message “Requested conversion is not supported.”.
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column “(user generated expression).Expr1003” from OLE DB provider “SQLNCLI11” for linked server “MyLocal”.

The above error depicts that there is something wrong with the Linked Server. But the same Linked Server was working for other tables, and it was failing for a specific table only. I checked the table and the ETL script where the Linked Server was being used. That script had a SELECT list fetching records from source table via Linked Server. The column list was having a computed column in the end like: CAST(NULL as UNIQUEIDENTIFIER) AS U_ID. I was not sure why anybody would write that code and what was the need to add this computed column. I removed this column and the error was gone. So it was clear that the CASTing of NULL to UNIQUEIDENTIFIER datatype was throwing this error, but the same code was working fine in SQL Server 2008 R2.

(Please check at the bottom of the post for more such behavioral changes)
 

–> Here is the issue that I’ve reproduced:

This will run fine in previous versions of SQL Server, but will throw error in 2012:

–> CREATE Linked Server

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver 
	@server = N'MyLocal', 
	@srvproduct=N'MSSQL', 
	@provider=N'SQLNCLI', 
	@datasrc=N'MANOJPANDEY-PC', -- plz change the server name here.
	@provstr=N'PROVIDER=SQLOLEDB;SERVER=MY-PC' -- plz change the server name here.
 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLocal',@useself=N'True',
	@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

–> Create a new table:

USE [AdventureWorks2012]
GO

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
INTO dbo.Person
FROM [AdventureWorks2012].[Person].[Person]

–> Query table via Linked Server:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
FROM [MyLocal].[AdventureWorks2012].dbo.[Person]

–> Query it after adding a column via Linked Server:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(NULL as UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

OLE DB provider "SQLNCLI11" for linked server "MyLocal" returned message "Requested 
conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "(user generated expression).Expr1003" 
from OLE DB provider "SQLNCLI11" for linked server "MyLocal".

 

–> Creating a View in Remote Server:

CREATE VIEW [dbo].[vwPerson]
AS
SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(NULL as UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

–> View also fails, as it is also expanded (same error as above):

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate, U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[vwPerson]

 

–> Workaround 1:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

–> Workaround 2:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(0x AS UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

 

–> Final Cleanup:

DROP TABLE dbo.Person
DROP VIEW dbo.vwPerson
GO

USE [master]
GO

EXEC master.dbo.sp_dropserver @server=N'MyLocal', @droplogins='droplogins'
GO

 
–> I’ve documented more behavioral changes after SQL Server 2012 upgrade, and here is the list:

1. IDENTITY column value hop

2. Temp #Tables created with negative IDs

3. No native Linked Server support to SQL Server 2000


Linked Server error: Named Pipes Provider: Could not open a connection to SQL Server [53] – MSDN TSQL forum


–> Question:

I am trying to create a linked server of a remote server in my local system.

and getting following error:

Linked Server error

Named Pipes Provider: Could not open a connection to SQL Server [53]
OLE DB provider “SQLNCLI10” for linked server “MY_LINKED_SERVER” returned message “Login timeout expired”…
 

–> My Answer:

What SQL Server version you are working on?

Do you have proper access rights and permission to connect to the Remote server?

Check, if TCP/IP and Named Pipes are enabled in Remote Server, in Config Manager?

And, are you executing Linked Server from the same machine, or any remote client?

The error number is 53, which means Network Path is not Found, try to ping the server, check Server Name in your Linked Server DDL script, Also check for Windows Firewall settings.
 

Ref Link.


SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround

December 10, 2012 24 comments

Yes, you read it right, “SQL Server 2012” has stopped connecting to “SQL Server 2000” via linked Servers. As this new version uses a new Native Client version i.e. SQLNCLI11, instead of the old SQLNCLI10. This new client only connects back to 2008R2, 2008 and 2005 only.

– I upgraded my Database Servers from “SQL Server 2008 R2” to “SQL Server 2012”.

– Restored the databases from the backup taken from 2008R2.

– Ran the jobs and found that one of them was failing with following error:
 

Error Message:
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Client unable to establish connection”.
Msg 22, Level 16, State 1, Line 0
SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Invalid connection string attribute”.


 

As I have upgraded from 2008 R2, so I checked Providers under the Linked Server and found that with the new SQLNCLI11, I still have the “SQL Server Native Client 10.0” i.e. “SQLNCLI10”.

So, I tried to create the Linked Server by using “SQLNCLI10”, but it again gave an error, as follows:

Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.


 

I scripted out the DDL of my existing Linked Server, as below:

USE [master]
GO

-- Existing LinkedServer [NorthWind2000]:
EXEC sp_addlinkedserver	@server = N'NorthWind2000', 
						@srvproduct=N'MSSQL', 
						@provider=N'SQLNCLI', 
						@provstr=N'PROVIDER=SQLOLEDB;SERVER=NorthWind'

EXEC sp_addlinkedsrvlogin   @rmtsrvname=N'NorthWind2000', 
							@useself=N'True', 
							@locallogin=NULL, 
							@rmtuser=NULL, 
							@rmtpassword=NULL
GO

 

=> WORKAROUND / FIX:

Now as a workaround to make this Linked Server work we have an option to use the ODBC Data Source which will connect to our remote server.
There are 2 approaches:

1. Either we create an ODBC Data Source (DSN) and use it in our Linked Server.

2. Or, use the Data Source (DSN) connection string directly in the Linker Server Provider.
 

–> Using appraoch #1:

Create an ODBC Data Source:
1. Open Control Panel, go to Administrative Tools, then “Data Sources (ODBC)”.
2. On “ODBC Data Source Administrator” window go to “System DSN” Tab.
3. Here click on Add to create a new DSN.
4. Choose “SQL Server” and click Finish.
5. On the new window, give a proper name for the Source DSN (like: NorthWind2000DSN), we will use this name while creating our Linked Server. Provide the Server name which is on SQL Server 2000, here “NorthWind”. Click Next.
6. Choose the Authentication Type, either Windows or SQL Server auth. Click Next.
7. Change the default database, not necessary. Click Next.
8. Click Finish. You will see a new DSN created under System DSN tab.

Now, create Linked Server and provide this DSN in the @datasrc param and provide the @provider param “MSDASQL”.

You can use the below query to create the same:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO

-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
						@srvproduct=N'MSDASQL', 
						@provider=N'MSDASQL', 
						@datasrc = N'NorthWind2000DSN', 
						@location=N'System';

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
						  @useself=N'True', 
						  @locallogin=NULL, 
						  @rmtuser=NULL, 
						  @rmtpassword=NULL
GO

 

–> Using appraoch #2:

We can also directly put the DSN connection String in the Provider String @provstr param.
Let’s check it below:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO
-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
						@srvproduct=N'', 
						@provider=N'MSDASQL', 
						@provstr=N'DRIVER={SQL Server};SERVER=NorthWind;Trusted_Connection=yes;'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
						  @useself=N'True', 
						  @locallogin=NULL, 
						  @rmtuser=NULL, 
						  @rmtpassword=NULL
GO

This way you can query SQL Server 2000 data from SQL Server 2012 via Linked Servers by using ODBC DSN.

This seems seamless but it is an indirect process and a workaround to query SQL Server 2000 database.
To make your queries or ETLs efficient it is advisable to upgrade to a higher version, at-least SQL Server 2005.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.