Get data from Microsoft Azure using SQL Server without using linked server

0
votes

I have data stored in Microsoft Azure and want to retrieve and store into Microsoft sql server 2008 r2 without creating linked server.

Try:

Step 1: Configured Firewall settings in Azure by adding Client IP.

Step 2: Running following query from SQL Server:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

SELECT * 
FROM  OPENROWSET('MSDASQL', 'Driver={SQL SERVER}; Server=server.database.windows.net;Database=DBName;UID=Admin; PWD=********;', 'select * from tablename')

Note: The above steps works fine but after sometime getting following error:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

1
sql-serverazuresql-server-2008-r2azure-sql-database
Why you still use the old version of SQL Server? I guess it may be caused by the database compatibility level. Please update the SQL Server to the latest version, and check if the error will happen again. - Leon Yue

1 Answers

0
votes

The "SQL Server" ODBC driver that ships with Windows is deprecated and cannot be used with Azure SQL Database. It's generally best to use OLE DB with linked servers instead of ODBC when possible.

Below is and example using a SQL Server Native Client OLE DB driver:

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=server.database.windows.net;Database=DBName;UID=Admin;PWD=********, 'select * from tablename;')