In this article, I will show you common issues when using the OPENROWSET function in SQL Server and I was able to fix these issues.
I was working on another tutorial – How to Insert Stored Procedure Results into a Temporary Table in SQL Server when I encountered these issues. After resolving these issues, I decided to share my solutions with my readers.
Before we dive right in, let me do some clarifications.
What is OPENROWSET?
The OPENROWSET function allows access to remote data from an OLE DB data source or reads data from a file from the BULK provider. Below are some examples:
--Example 1
--read data from OLEDB data source
SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=server_name;Trusted_Connection=yes;'
, 'SELECT * FROM database_name.dbo.table_name');--Example 2
--read data from a file using BULK provider
SELECT *
FROM OPENROWSET(BULK 'C:\folder_name\file_name.txt', SINGLE_CLOB) AS file_name;--Example 3
--read data into temp table using SQL Server authentication
SELECT *
INTO #temp_table
FROM OPENROWSET('SQLNCLI',
'server_name';'user_name';'password',
'EXEC stored_procedure_name @parameter1=N''parameter1_value'', @parameter2=parameter2_value')--Example 4
--read data into temp table using Windows authentication and the AdventureWorks database
SELECT *
INTO #temp_table
FROM OPENROWSET('SQLNCLI'
,'Server=localhost;Trusted_Connection=yes;',
'EXEC [AdventureWorks2014].[dbo].[uspGetBillOfMaterials] @StartProductID = 754, @CheckDate = ''2010-06-19''
');You can learn more about SQL Server’s OPENROWSET on Microsoft.
Common Issues with OPENROWSET & How to Fix Them
Now, let’s check out these common issues and how you can fix them.
1. ‘ad hoc distributed queries’ configuration is turned off
msg 15281, level 16, state 1, line 22 sql server blocked access to statement 'openrowset/opendatasource' of component 'ad hoc distributed queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'ad hoc distributed queries' by using sp_configure. For more information about enabling 'ad hoc distributed queries', search for 'ad hoc distributed queries' in sql server books online.If your SQL Server hasn’t been configured to allow OPENROWSET, you can configure it yourself or get an administrator with valid permission to do the configuration.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO
2. Invalid server name
You will get this error message if your server name is incorrect.
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 17
Named Pipes Provider: Could not open a connection to SQL Server [53].To correct this error, ensure you get the valid server name and input it carefully.
3. Invalid trusted connection value
The trusted connection value can be only yes or no. Any other value will throw errors like the one shown below. To resolve this error, ensure your value is either yes or no.
Msg 0, Level 16, State 1, Line 18
Invalid value specified for connection string attribute 'Trusted_Connection'In addition, if you are not using Windows authentication, ensure you use valid credentials – username and password to avoid or resolve the error shown below.
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 19
The OLE DB provider "MSOLEDBSQL" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 19
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".4. Stored procedure uses temp tables
Msg 11529, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0] The metadata could not be determined because every code path results in an error; see previous errors for some of these.You encounter this error when the stored procedure uses temp table and the metadata doesn’t exist. That is, the temp tables’ metadata cannot be collected through the meta-analysis that works for stored procedures that don’t use temp tables.
The resolution is to add this statement SET FMTONLY OFF; to the beginning of your EXEC query. Unfortunately, this solution may not always work. You may need to solve other related issues and this will resolve on its own.
5. Not using valid, fully-qualified stored procedure name
Remember, the OPENROWSET function gets data from remote data sources. Therefore, you need to use fully-qualified names for your stored procedures. If not, you will get the error below:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'uspGetBillOfMaterials'.To resolve this error, use valid, fully-qualified names when referencing stored procedures.
For example, EXEC [AdventureWorks2014].[dbo].[uspGetBillOfMaterials].
6. Not adding quotes properly
Oftentimes, the stored procedure you want to run requires parameters to execute successfully. These parameters may be date, date time, or other data types that may have special characters or spaces.
If these characters are not put in quotes properly, you may encounter errors like the one below.
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 19
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '-'.To resolve this issue, ensure you add valid quotes to your parameters.
For example, @CheckDate = ”2010-06-19” in example 3 above.
Thanks for reading. I hope you found the article useful.