I am trying to import a small image from a file located on my MS SQL Server to a column in a database setup as VARBINARY(MAX). I have declared a variable as VARBINARY(MAX) and would like to assign the image to that variable. Once assigned I have to locate the records and store that image to those records. I have a query that works to store the image to the column, but for the life of me I cannot import new images from files on the servers SSD.

USE [Mydb]
DECLARE @ImageFile VARBINARY(MAX)
SELECT @ImageFile = 'Abracon.jpg'
FROM
    OPENROWSET(BULK 'C:\Users\admin\Pictures\Abracon.jpg', SINGLE_BLOB)
    AS BLOB;

Running this query gives me the following error: > Msg 257, Level 16, State 3, Line 16 > >Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. I read the OPENROWSET page at learn.microsoft.com, but I still don't see my error. What am I doing wrong?

I am running this openrowset query about jobs on my current server and I see that the result set is not correct. I was expecting a bunch of jobs but I only get one. when I run the following piece of code I can see why:

select [my current server] = @@servername

select *
from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',
'select [my current server] = @@servername') AS a

that gives me the following result: 'MATHURA` is my server name (machine name) 'MATHURA\SQL2016` is the server name\instance name where I am executing this query obviously when I pass Server=(local); to the openrowset it takes it to be my server name, but I am running on the mathura\sql2016 instance. the question is: how can I pass the currently named instance to the openrowset ?

I've a T-SQL stored proc running in a Microsoft SQL Server 2019 environment. The proc contains temp tables, dynamic SQL and is 2000+ lines. The proc's output has been verified and tested for completeness and accuracy; re-writing the proc is not an option. Attempts using sp_describe_first_result_set and OPENROWSET to retrieve metadata have failed due to the use of temp tables and dynamic SQL. Research links used: and this. Are there any other approaches to retrieve metadata? Thoughts?

I have the task of migrating our databases from a Windows 2012 R2 server with SQL Server 11.0.7507 (2012) to a Windows 2019 server with SQL Server 16.0.1110.1 (2022). I am having issues with opening excel spreadsheets. The following works on the old server but not on the new server. select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Reports\ETAReport.xlsx;', 'SELECT * FROM [DataSheet$]'); I get the error > OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 8 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". I have tried everything here https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null and other similar threads. This is what I have done on the new server: - Installed the 64 bit of Microsoft Access Database Engine 2010 Redistributable (version 14.0.7015.1000 matches the current server). - EXEC sp_configure 'show advanced options', 1 - EXEC sp_configure 'ad hoc distributed queries', 1 - EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 - EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 - Given 'Everyone' rights to the folder, checked the file name. - Restarted the server multiple times. Many say Error 7303 is file open but it definitely is not open (still happens immediately after restarted server) and office is not installed on the server. The one thing I have noticed is that on the old server the SQL Instance user (NT Service\MSSQLSERVER) has a user folder in C:\Users\ and when I run the above query on the old server it writes to C:\Users\MSSQLSERVER\AppData\Local\Temp. The new server has NO user folder for the SQL user. Is SQL 2022 different or is that the missing bit? If so how do I create that folder? Thanks!

Trying to run a Opendatasource query to point to the same server as the source server in AWS EC2, I got this message intermittently (once out of about 20 times, the other 19 times do get results back). Any suggestion will be appreciated. Thanks. Error:

Cannot create an instance of OLE DB provider "SQLNCLI11" for linked server "(null)"

Query:

select top 10 *
from OPENDATASOURCE('SQLNCLI11','User ID=test;Password=test;Data Source=10.11.5.11').[test].dbo.test

I've been trying to figure out this linked server problem for about a week now with no luck. The set up I have is a linked server from an instance of SQL Server 2016 to an instance of SQL Server 2016 Express (exact versions below as retrieved with @@Version). This install is roughly 2 years old. The standard install is a Data Warehouse that pulls in an Order table from a production line scheduling application every 10 minutes and merges the data into the data warehouse. It does this with a stored procedure and a dynamic SQL OPENQUERY. This merge operation usually takes between 1 - 5 seconds to complete depending on the network at the time, however randomly, it just hangs indefinitely placing a lock on the Order table in the scheduling application, causing other applications downstream to choke because of the lock (running sp_who2 against the scheduling application confirms the remote OPENQUERY is locking this table). I've run the SQL Server Profiler against both DBs and have indeed discovered that the OPENQUERY is what gets hung up. The profiler shows the open query is being received by the remote server, but it's almost like it just decides not to do anything about it. More details: - This ETL job runs every 10 minutes and is scheduled with SQL Server Job agent - This exact same procedure is collecting data from 5 other locations perfectly fine. This of course makes me suspect the problem is with the remote server. - If I call the job more frequently (eg every 5 minutes) the lock will happen more often - I've set the query timeout in the driver to 0 to prove that it will hang indefinitely (it does) - When I set the timeout to something like 60 seconds in the driver, I get the following error randomly: > Cannot fetch a row from OLE DB provider "SQLNCLI11" for linked server "MY_LINKED_SERVER". which seems more like a simple permissions problem, but it's not. - I've checked that the remote log in has the proper db_datareader role, and it does. - I've confirmed that another process is not blocking the remote table Current linked server driver settings Allow In Process = TRUE everything else = FALSE Versions:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: ) (Hypervisor)

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: ) (Hypervisor)

Here is also a snip of the 'openquery'. I've removed a bunch of other code to keep it smaller. There is really nothing crazy going on here, but I know people will probably ask to see it. Before people start talking about SQL injection and the dynamic SQL I'm composing not being safe, this code is not exposed to any data entry points, or the Internet at all. -- Bring in the new orders IF OBJECT_ID('tempdb..#tmpNewOrders') IS NOT NULL DROP TABLE #tmpNewOrders CREATE TABLE #tmpNewOrders ( [OrderID] INT IDENTITY NOT NULL PRIMARY KEY, [System] CHAR(1) NULL, [OrderNumber] INT NULL, [FacilityID] INT NULL, [ItemID] INT NULL, [LineID] INT NULL, [CustomerID] INT NULL, [ShortItemNumber] INT NULL, [JDEWorkOrderID] INT NULL, [ScheduledLotID] INT NULL, [ScheduledLotOrder] INT NULL, [ScheduledLineID] INT NULL, [ScheduledDate] DATETIMEOFFSET (7) NULL, [ScheduledRunOrder] SMALLINT NULL, [OrderedQuantity] INT NULL, [PackedQuantity] INT NULL, [OrderRequestedShipDate] DATETIMEOFFSET(7) NULL, [SalesOrderNumber] VARCHAR(50), [EstimatedDuration] INT NULL, [Memo] VARCHAR(MAX) NULL, [JDEWorkOrderStatus] INT NULL, [CompletedDateTime] DATETIMEOFFSET(7) NULL, [IsCompleted] BIT NULL, [IsRemoved] BIT NULL, [TargetCasesperHour] INT NULL, [TargetPoundsperHour] INT NULL, [IsCreatedByScheduler] BIT NULL, [COOLCode] VARCHAR(30), [RecipeID] INT NULL, [LineRecipeID] INT NULL, [LongItemNumber] VARCHAR(30) NULL, [SalesOrderStatusCode] INT NULL ) -- set up what we want the insert to temp table to be SET @Insert = ' INSERT INTO #tmpNewOrders( [OrderNumber] ,[ScheduledLotID] ,[ScheduledLotOrder] ,[ScheduledLineID] ,[ScheduledDate] ,[ScheduledRunOrder] ,[OrderedQuantity] ,[PackedQuantity] ,[EstimatedDuration] ,[CustomerID] ,[JDEWorkOrderID] ,[ShortItemNumber] ,[OrderRequestedShipDate] ,[SalesOrderNumber] ,[Memo] ,[JDEWorkOrderStatus] ,[IsCompleted] ,[IsRemoved] ,[IsCreatedByScheduler] ,[CompletedDateTime] ,[COOLCode] ,[RecipeID] ,[LineRecipeID] ,[TargetCasesperHour] ,[TargetPoundsperHour] ,[LongItemNumber] ,[SalesOrderStatusCode] ) ' -- set up the open query for doing replacement substitution SET @OpenQuery = '@Insert SELECT [OrderID] ,[ScheduledLotID] ,[ScheduledLotOrder] ,[ScheduledLineID] ,[ScheduledDate] ,[ScheduledRunOrder] ,[OrderedQuantity] ,[PackedQuantity] ,[EstimatedDuration] ,[CustomerID] ,[JDEWorkOrderNumber] ,[ShortItemNumber] ,[OrderRequestedShipDate] ,[SalesOrderNumber] ,[Memo] ,[JDEWorkOrderStatusCode] ,[IsCompleted] ,[IsRemoved] ,[IsCreatedByScheduler] ,[CompletedDatetime] ,[COOLCode] ,[RecipeID] ,[LineRecipeID] ,[TargetCasesperHour] ,[TargetPoundsperHour] ,[LongItemNumber] ,[SalesOrderStatusCode] FROM OPENQUERY([@Server], @Query )' -- set the query that we want to pass to the OPENQUERY Statement -- very critical that quoted identifier is set to OFF SET @Query = "'SELECT o.[OrderID] ,o.[ScheduledLotID] ,o.[ScheduledLotOrder] ,o.[ScheduledLineID] ,o.[ScheduledDate] ,o.[ScheduledRunOrder] ,o.[OrderedQuantity] ,o.[PackedQuantity] ,o.[EstimatedDuration] ,jo.[ShipToCustomerNumber] CustomerID ,o.[JDEWorkOrderNumber] ,jo.[ShortItemNumber] ,jo.[OrderRequestedShipDate] ,jo.[SalesOrderNumber] ,o.[Memo] ,o.[JDEWorkOrderStatusCode] ,o.[CompletedDatetime] ,o.[IsCompleted] ,o.[IsRemoved] ,o.[IsCreatedByScheduler] ,o.[Timestamp] ,o.[CreatedSource] ,o.[CreatedDTS] ,o.[CreatedBy] ,o.[UpdateSource] ,o.[UpdateDTS] ,o.[UpdateBy] ,o.[COOLCode] ,jo.[LongItemNumber] ,jo.[SalesOrderStatusCode] ,@DBName.dbo.GetRecipeID(jo.[LongItemNumber], jo.[ShipToCustomerNumber]) AS RecipeID ,lr.[LineRecipeID] ,lr.[TargetCasesperHour] ,lr.[TargetPoundsperHour] FROM @DBName.dbo.[Order] o INNER JOIN @DBName.dbo.[jdeorder] jo ON o.[JDEWorkOrderNumber] = jo.[WorkOrderNumber] LEFT JOIN @DBName.dbo.[LineRecipe] lr ON lr.RecipeID = @DBName.dbo.GetRecipeID(jo.[LongItemNumber], jo.[ShipToCustomerNumber]) AND lr.LineID = ScheduledLineID WHERE jo.LongItemNumber IS NOT NULL AND o.[UpdateDTS] >= ''@StartTime'''" -- set the DB name for our Query SET @Query = REPLACE(@Query, '@DBName', @DbName) -- Sub out our start time SET @Query = REPLACE(@Query, '@StartTime', @StartTime) -- Sub out our server name SET @OpenQuery = REPLACE(@OpenQuery, '@Server', @Server) -- sub out for our query SET @OpenQuery = REPLACE(@OpenQUery, '@Query', @Query) -- sub out our insert SET @OpenQuery = REPLACE(@OpenQuery, '@Insert', @Insert) -- now execute our query EXEC sp_executesql @OpenQuery --- I've installed sp_whoisactive and it's revealed some interesting things. After my remote query is finished, (it takes about 1 - 5 seconds) it seems the spid status is set to DORMANT, which according to the SQL Server docs means SQL Server is resetting the session. This seems to take in the range of 4 - 5 minutes before this spid is cleaned up by SQL Server. However the next time I call the ETL job, it seems to try and use the same spid, which is why I think the hanging is random.

I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the log files is not consistent. The table has 10 columns 9 of which are populated from the log file, last 3 columns are all descriptions but the log file may only have a single description column populated (the max is always 3). In the image below the highlighted rows work because they have the valid 3 column worth of data separated by a comma. Is there a way to force BCP to always populate the 3 columns or mark them as NULL then move onto the next row?

When we run a SSIS package to import data from excel , we get an initial error:

The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.
If the 64-bit driver is not installed, run the package in 32-bit mode.

We were able to fix this by executing the package in 32 bit mode using the option in wizard

SELECT * 
INTO #temp13
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
                    'Data Source=XXXXXXXX;Extended Properties=Excel 8.0')...[Sheet1$];

We get the below error

The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" 
cannot be loaded in-process on a 64-bit SQL Server. [SQLSTATE 42000] (Error 7438).
The step failed.

Is there any option to run the Transaction SQL in 32 bit .

Executing an OPENROWSET query with wildcards in the bulk path and the arguments in the WHERE clause seems not to work when using PolyBase to connect to S3-compatible storage. I have an on-premises SQL Server 2022 (16.0.4120.1) with PolyBase enabled. The external source is an S3-compatible object storage (Minio) running on a Linux Red Hat server. My version of the query: The S3 storage bucket is partitioned in source/year/month/date/file.txt. For the query below, the path is: testbucket/files_txt/year=2024/month=05/date=06/*.txt.

SELECT 
    r.filepath() AS filepath,
    r.filepath(1) AS [year],
    r.filepath(2) AS [month],
    COUNT_BIG(*) AS [rows]
FROM OPENROWSET( 
        BULK '/testbucket/files_txt/year=*/month=*/date=*/*.txt',
        DATA_SOURCE = 's3_ds', 
        FORMAT = 'CSV' ,
        FIRSTROW = 2,
        FIELDTERMINATOR = ';',
        ROWTERMINATOR = '\n'
    ) 
WITH ( 
    col1 varchar(800) 
) AS [r] 
WHERE 
    r.filepath(1) COLLATE Latin1_General_BIN = '2024'
    AND r.filepath(2) COLLATE Latin1_General_BIN = '05'
    AND r.filepath(3) COLLATE Latin1_General_BIN = '06'
GROUP BY 
    r.filepath(),
    r.filepath(1),
    r.filepath(2)
ORDER BY filepath;

When I execute the query as above, it takes several minutes to get back a result. However, when I run the query and replace the wildcards with the year, month, and date figures I have in the WHERE clause to the bulk string, it brings back the result in seconds. SQL Server gave the message that adding collation would enable pushdown, but I’m not sure if that is possible in this case. However, adding the collation did not seem to have any effect. Is this due to the fact that the polybase REST API does not mange to add the wildcard values to the query before it scans the storage partion scheme? I know that I can create a dynamic SQL to fix this. However, I thought this would work better since there are several examples in the documentation. Or have I missed something?

A developer need to use OPENROWSET function. The steps I did to allow him: Enable ad hoc distributed queries Server Configuration Option sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; Then grant Administer Bulk Operations permission to the role, which he is member of. The sample query he has provided: SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=bitabular\tabular; Initial Catalog=NPS;', 'SELECT * FROM $SYSTEM.TMSCHEMA_DATA_SOURCES') as a Also DisallowAdhocAccess registry option is set to 0 for the specified provider. But still user get access error: > Ad hoc access to OLE DB provider 'MSOLAP' has been denied. You must > access this provider through a linked server If I understood the remarks in msdn article the authentication delegation is the missing part. But all our logins are windows authenticate mode. What is the solution?

Can someone give me a working example of using OPENROWSET on SQL Server with dynamic SQL (sp_executesql) executing a stored procedure with two parameters as variables, preferably date and integer? I want to use that output and INSERT INTO a temp table

INSERT INTO #t
SELECT * FROM OPENROWSET
(
    SQL Server
    SP with two parameters date and integer)
   
SELECT * FROM #t

I have a file I want to import into an SQL Server 2019 database running on Ubuntu 20.04. The file resides in the /proc directory and I have done a chmod 777 on that file. However, when trying to read it from a SQL Server query using Openrowset, I get this error:

select * from openrowset(bulk '/proc/loadavg', SINGLE_CLOB) cpu_load

> Msg 12703, Level 16, State 1, Line 1 > > Referenced external data source "(null)" not found. If I copy that file to the /var/opt/mssql directory, it will then work and I can query the file. Why can I not read a file in /proc but I can from /var/opt/mssql? Also, how do I get access to the /proc file from within SQL Server?

I am trying to enable my users to BULK INSERT / OPENROWSET() a CSV file that is stored on our DFS/cifs network shares. I have MS SQL Server 2016 Standard Edition running as a domain account and that account has access to the desired fileshare. So, if I log in to MSSQL using SQL Authentication it is working.

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -U %DB_USERNAME% -P %DB_PASSWORD%
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
auth_type  NumLines
---------- -----------
SQL                 73

(1 rows affected)

However, when I connect to MSSQL from another machine using Kerberos / Windows Authentication, I receive the following error:

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -E
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
Msg 4861, Level 16, State 1, Server DBSERVER, Line 1
Cannot bulk load because the file "\\example.org\myshare\path\to\mydata.csv" could not be opened. Operating system error code 1326(The user name or password is incorrect.).
1> SELECT CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type;
2> GO
auth_type
----------
KERBEROS

(1 rows affected)

Normally, if it's a Kerberos Double-Hop issue, I expect the error to be Access is denied. In this case, I've set up the MSSQLSvc/dbserver.example.org and MSSQLSvc/dbserver.example.org:1433 SPNs, verified that I'm connecting using Kerberos, and enabled Kerberos Constrained Delegation for the mssql domain account to the HOST/FILESERVER1 and HOST/FILESERVER2 SPNs. Any ideas what this error, The user name or password is incorrect., could be about or how I can get more info from Windows or SQL Server? ## Update #1 ## I tried to take DFS out of the equation by using a UNC path directly to one of the file servers, but get the same error:

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -E
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\FILESERVER1.example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\FILESERVER1.example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
Msg 4861, Level 16, State 1, Server DBSERVER, Line 1
Cannot bulk load because the file "\\FILESERVER1.example.org\myshare\path\to\mydata.csv" could not be opened. Operating system error code 1326(The user name or password is incorrect.).

Same result if I just use the netbios name for the file server instead of the FQDN (ie. \\FILESERVER1\myshare\path\to\mydata.csv) ## Update #2 ## I enabled Keberos event logging on the SQL Server, ran the OPENROWSET() query again, and then found the following in the System Event Log:

Log Name:      System
Source:        Microsoft-Windows-Security-Kerberos
Date:          3/9/2023 12:47:56 PM
Event ID:      3
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      DBSERVER.example.org
Description:
A Kerberos error message was received:
 on logon session 
 Client Time: 
 Server Time: 17:47:56.0000 3/9/2023 Z
 Error Code: 0xd KDC_ERR_BADOPTION
 Extended Error: 0xc0000225 KLIN(0)
 Client Realm: 
 Client Name: 
 Server Realm: EXAMPLE.ORG
 Server Name: cifs/DOMAINCONTROLLER01.example.org
 Target Name: cifs/DOMAINCONTROLLER01.example.org@EXAMPLE.ORG
 Error Text: 
 File: onecore\ds\security\protocols\kerberos\client2\kerbtick.cxx
 Line: 1292
 Error Data is in record data.

I want to read a text (in this case, csv) from a file. I'm not trying to bulk-load for now, simply read. As far as I can tell, only the SQLNCLI provider is available. Ad Hoc Distributed Queries are enabled (value_in_use = 1 for select * from sys.configurations where name like '%ad hoc Dis%')

SELECT * FROM OPENROWSET('SQLNCLI', 
'server=localhost;trusted_connection=yes;
Driver={Microsoft Text Driver (*.txt; *.csv)};
data source=\\valid\path\to\my\folder\Test.csv;
Extensions=csv;HDR=yes;FMT=Delimited',
'SELECT * FROM [Test.csv]'
) AS data;

I get this error: >OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.". > >Invalid object name 'Test.csv'. What am I doing wrong? Is [Test.csv] the right way to reference the file? Using: Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

I want to export tbl_category to excel file with code. I can export with the wizard but I want to create procedures to create excel file daily. tbl_category : 2 column contain : [id] is int and [category] is nvarchar(max) **code:** INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\template.xlsx;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]') SELECT [id] ,[Category] FROM [dbo].[TBL_Category] windows 7 32 bit and 64bit. sql server2014. ms office 2010. **error:** >Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object. 

Our SQL server 2014 (has all the latest Service packs & cumulative updates) has been experiencing sporadic memory dumps/lockups (which I posted in another thread). Runs on Windows Server 2012 R2. Our server often uses SQL OPENROWSET statements for importing XLSX/CSV/TXT files. Part of the recommendations to prevent memory dumps, was to update The Microsoft ACE (Access Database engine drivers) and SQL ODBC Drivers to the latest versions. Ever since the driver update 3 days ago, queries with OPENROWSET statements sporadically hang endlessly, this happens every 3-4 hours. Our only workaround is to restart the SQL service. These hangups do NOT generate any memory dumps. And they only affect queries that leverage OPENQUERY (the server works fine otherwise). I've included below: (A) list of all the installed 64-but ODBC drivers and their versions, as well as (B) Some sample OPENROWSET statements that are used, showing the different providers that are used. Any idea if/how we should treat this matter and/or whether any of these driver updates should be rolled back? If so, which ones? Could there be an overlap of the new/old ones causing an issue? SELECT * FROM OPENROWSET ('MSDASQL','DRIVER={MICROSOFT access TEXT DRIVER (*.TXT, *.CSV)};', 'SELECT * FROM F:\IMPORTS\MyFile.CSV') Using ACE 12.0 (2010) provider: SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','TEXT;DATABASE=F:\IMPORTS\;HDR=YES;', 'SELECT * FROM MyFile.CSV') Using ACE 16.0 (2016) provider: SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.16.0','TEXT;DATABASE=F:\IMPORTS\;HDR=YES;', 'SELECT * FROM MyFile.CSV') ODBC Drivers installed: Name Version Company Microsoft Access dBASE Driver ".dbf. ".mdx) 16.00.4999.1000 Microsoft Corporation Microsoft Access Driver (".mdb. '.accdb) 6.00.4999.1000 Microsoft Corporation Microsoft Access Text Driver *.txt, ".csv) 16.00.4999.1000 Microsoft Corporation Microsoft Excel Driver (".xlsx, ".xlsm, ".xlsb) 16.00.4999.1000 Microsoft Corporation ODBC Driver 11 for SQL Server 2014.120.6439.10 Microsoft Corporation ODBC Driver 18 for SQL Server 2018.181.01.01 Microsoft Corporation Oracle in Oraaientllg_home2 11.02.00.01 Oracle Corporation SQL Server 6.03.9600.17415 Microsoft Corporation SQL Server Native Client 11.0 Version 2011.110.7462.06 Microsoft Corporation

I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me. It is working fine using SQL Server authentication: select * FROM OPENROWSET('SQLOLEDB', 'myserver';'monitor';'#J4g4nn4th4#', 'SELECT GETDATE() AS [RADHE]') But the same query I am trying to run using windows authentication, and it is not working: select * FROM OPENROWSET('SQLOLEDB', 'myserver';'Integrated Security=SSPI', 'SELECT GETDATE() AS [RADHE]') Can someone please post an example that works? Here is a good article that describes OPENROWSET usage. **Examples of working scripts using OPENROWSET - please read comments** ---------------------------------------------------------------- -- this works - linked server REPLON1 ---------------------------------------------------------------- select * FROM OPENROWSET('SQLOLEDB', 'Server=REPLON1;Trusted_Connection=yes;', 'SELECT GETDATE() AS [RADHE]') select * FROM OPENROWSET('SQLOLEDB', 'Server=REPLON1;Trusted_Connection=yes;', 'SET FMTONLY OFF select * from sys.dm_exec_requests') SELECT a.* FROM OPENROWSET('SQLOLEDB', 'server=replon1;Trusted_Connection=yes;', 'SET FMTONLY OFF select * from sys.dm_exec_requests') AS a WHERE a.session_id > 50 ORDER BY a.start_time desc ---------------------------------------------------------------- -- this does not work - when using windows authentication -- apparently because windows server 2003 and windows server 2012 have problems connecting - related to SID -- it works fine using SQL Server Authentication ---------------------------------------------------------------- select * FROM OPENROWSET('SQLOLEDB', 'Server=SQLREPLON1\REP;Trusted_Connection=yes;', 'SELECT GETDATE() AS [RADHE]') -- Msg 18456, Level 14, State 1, Line 1 --Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. ---------------------------------------------------------------- -- this works - linked server SQLREPLON1\REP ---------------------------------------------------------------- select * FROM OPENROWSET('SQLOLEDB', 'SQLREPLON1\REP';'monitor';'#J4g4nn4th4#', 'SELECT * from SAReporting.DBO.tblStockLedgerMovement')

My architecture is as follows: 1. On prem sql server database [Datawarehouse] 2. Azure Analysis Services Tabular model [Sales] 3. International report developers that query the [Datawarehouse] to develop reports* 4. RLS on tables in [Datawarehouse] 5. RLS on [Sales] tabular model For testing that our data makes it correctly from [Datawarehouse] to [Sales] I would like to create a view on the [Datawarehouse] that uses OPENROWSET() to connect to the [Sales] tabular model. The only issue with this is I can see the user that runs the OPENROWSET() query on the [Sales] model is our sql admin user, not myself. Is there a way to configure the OPENROWSET() so that the user that is executing the local query is also the user that executes the query on the linked server? As far as I can tell, passthrough credentials would be the best way to ensure proper security is maintained. *the only reason I mention "international" is because the RLS is based on country

I have an SSIS Package and one of its components is "Execute SQL TasK" and I've written an OPNEROWSET query inside this task. When I configure the transaction option of the whole package from "Supported" to "Required" I receive this error: >The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface. When you want to configure the Transaction in SSIS you have to run the service "Distributed Transaction coordinator" and it seems that this service is in conflict with openrowset. What should I do? Has anybody come across this issue?

I have an example data file with following contents and saved with UTF8 encoding. oab~opqr öab~öpqr öab~öpqr The format of this file is fixed width with columns 1 to 3 each being allocated 1 character and column 4 reserved 5 characters. I have created an XML format file as below Disappointingly running the following SQL... SELECT * FROM OPENROWSET ( BULK 'mydata.txt', FORMATFILE = 'myformat_file.xml', CODEPAGE = '65001' ) AS X Produces the following results Col1 Col2 Col3 Col4 ---- ---- ---- ----- o a b ~opqr � � a b~öp � � a b~öp from which I conclude the LENGTH is counting bytes rather than characters. Is there any way I can get this working correctly for fixed *character* widths with UTF8 encoding? (Target environment is Azure SQL Database reading from Blob storage) NB: It was suggested in the comments that adding COLLATION="LATIN1_GENERAL_100_CI_AS_SC_UTF8" to the FIELD elements might help but the results remain unchanged with this.