Questions tagged [openrowset]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
10 views

OPENROWSET results truncated when reading file from Azure Blob vs Local

I have been experimenting with importing a JSON file from Azure Blob storage. I have created an external data source to the storage container. The file contains multiple JSON objects, but they are not ...
James's user avatar
  • 1
0 votes
1 answer
104 views

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

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 ...
Chris's user avatar
  • 1
2 votes
1 answer
119 views

SQL Server on LInux - OpenRowSet function only has access to files in /var/opt/mssql directory?

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 ...
Dean Richards's user avatar
1 vote
0 answers
61 views

Read a text file using SQLNCLI [closed]

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 ...
alazyworkaholic's user avatar
1 vote
1 answer
611 views

BULK INSERT from DFS shared folder using Kerberos

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 ...
JoeNahmias's user avatar
1 vote
1 answer
308 views

Open Row insert- SQL

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 ...
Lakshmi R's user avatar
1 vote
1 answer
332 views

SQL Lockups after ACE/ODBC Drivers Updated

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. ...
Depth of Field's user avatar
1 vote
1 answer
576 views

Bulk operation prerequisites for non-sysadmins

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 ...
Meyssam Toluie's user avatar
1 vote
1 answer
406 views

Can OPENROWSET authenticate with the signed in user running the query?

My architecture is as follows: On prem sql server database [Datawarehouse] Azure Analysis Services Tabular model [Sales] International report developers that query the [Datawarehouse] to develop ...
Antyan's user avatar
  • 158
9 votes
3 answers
3k views

Is it possible to use OPENROWSET to import fixed width UTF8 encoded files?

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 ...
Martin Smith's user avatar
  • 84.4k
0 votes
0 answers
604 views

Trying to read a json lines file with OPENROWSET and cursor

I've got a pile of json lines files that I'm trying to process into a set of Sql Server database tables. Can't use OPENJSON() because jsonlines is not valid in total, only line by line. Plus the ...
user1664043's user avatar
0 votes
1 answer
311 views

AWS EC2 Opendatasource to the same server - OLE DB provider "SQLNCLI11" for linked server "(null)"

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)....
James's user avatar
  • 93
1 vote
1 answer
495 views

Can SQL Server OpenQuery/OpenRowSet to an LDAP Linked Server be used for Authentication?

I can fetch data back from LDAPS (port 636/3269) by first creating a Linked Server using sp_addlinkedserver and sp_addlinkedsrvlogin. However, what I really need to do is to authenticate a Username/...
Daniel Bragg's user avatar
0 votes
1 answer
96 views

Problem with IF / BEGIN / ELSE

I'm trying to run a select IF there's something inside a folder: INSERT INTO #tb (SubDirectory, Depth, [File]) EXEC master..xp_dirtree 'C:\folder\', 10, 1 This will populate a table with the ...
Racer SQL's user avatar
  • 7,392
3 votes
1 answer
1k views

Error when using OPENROWSET from a View as a nonsysadmin

[SQL Server 2012] I am running into an issue regarding the usage of a non-sysadmin account calling a view which contains a OPENROWSET call that pulls from a stored procedure. I was wondering whether ...
Dray's user avatar
  • 51
0 votes
1 answer
407 views

OPENROWSET Bulk insert Text File NULL Columns

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 ...
Stockburn's user avatar
  • 429
0 votes
1 answer
3k views

How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator?

I have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. I noticed in the execution plan, it's using ...
J.D.'s user avatar
  • 37.1k
1 vote
1 answer
207 views

Are there any security implications with enabling "Data Access" on a sever?

I'm trying to run an OPENQUERY statement within a local server but am getting the following error: Server 'MyLocalServer' is not configured for DATA ACCESS. If I set MyLocalServer's Data Access ...
J.D.'s user avatar
  • 37.1k
2 votes
2 answers
1k views

SSMS - Openrowset maximum file size

Apologies if this has been answered elsewhere but I couldn't find anything here or online. Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if ...
Anonymous's user avatar
  • 145
1 vote
1 answer
776 views

How to bulk insert data in vertical into sql server?

My data in below format. I have to insert into sql server table. agency NO booknbr 06000330 bookdttm 2006-07-19 10:56:00 arrdttm 2006-05-07 05:42:00 aj_id A000009454 casenbr 06006640 ...
B.Muthamizhselvi's user avatar
2 votes
0 answers
303 views

BulkAdmin Role Doesn't Work

There is a query that our BI team use. They can not execute the query because of the error below. Ad hoc access to ole db provider 'MSDASQL' has been denied. You must access this provider ...
Furkan's user avatar
  • 37
1 vote
0 answers
2k views

Import Date in proper format from Excel to SQL Server through OPENROWSET query

I am trying to import huge data without any data loss from excel sheet to SQL Server. The excel sheet has few columns with mixed data types and few column with dates. I have prepared a sample file as ...
Shiva's user avatar
  • 787
2 votes
2 answers
6k views

specify delimiter in openrowset bulk insert

I'm trying to insert data from CSV files into tables using a cursor and open rowset bulk insert. this works for me 99% of the times but if one of the fields in the csv files contains a comma( , ) the ...
sql_girl's user avatar
  • 345
0 votes
2 answers
1k views

How to Use OPENROWSET function within a Transactional SSIS Package

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 "...
user avatar
0 votes
1 answer
133 views

Arithmetic overflow for Variable in Openrowset

I try to create a table to store the used storage Information for every Database. Since theese are hosted on different Cluster / Instances, I created a table with all Instance / Server Information. ...
Gerrit's user avatar
  • 15
1 vote
1 answer
3k views

Using "Where clause" In OPENROWSET as variable

I need to have a where clause in OPENROWSET but it is not always fix. It might be null sometimes. I have a procedure as you can see here : ALTER PROCEDURE [dbo].[OpenExcel] @TableName VARCHAR(2000) ...
user avatar
1 vote
2 answers
132 views

What is the potential fallout of changing SQL Server's log on account?

I need to be able to read files from a UNC path in TSQL and am getting an access denied error. I've read I must change the user SQL Server logs in as to a domain user, it's currently running as NT ...
jreed121's user avatar
  • 273
3 votes
1 answer
5k views

SQL Server 2016 Linked Server OPENQUERY Hangs

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 ...
maitland's user avatar
1 vote
0 answers
190 views

OPENROWSET error accessing XLSX if not manually saved

Good day, Apologies if this is in the wrong forum. Please refer me to the correct one if this is the case, thank you. I have a bunch of excel files that are added to a Dropbox folder on a monthly ...
R. Swanepoel's user avatar
2 votes
1 answer
1k views

Use SPLIT_STRING with OPENROWSET

Is there a way to pass the result from OPENROWSET BULK into a variable so i can use STRING_SPLIT to divide it into rows? Something like: DECLARE @MyString = FROM OPENROWSET (BULK 'C:\Temp\File....
Daarwin's user avatar
  • 159
1 vote
1 answer
7k views

OPENROWSET returns just one row

Im trying to import values from a .csv file which has only one column. I keep getting all the values in just one row. SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Temp\myfile.csv', SINGLE_CLOB) ...
Daarwin's user avatar
  • 159
6 votes
3 answers
6k views

How to retrieve a large text from inside a table?

for my own records I have a table in one of my servers where I like to save my activities and scripts the table definition is: IF OBJECT_ID('[dbo].[activity]') IS NOT NULL DROP TABLE [dbo].[...
Marcello Miorelli's user avatar
3 votes
1 answer
9k views

SQL Server service permissions on network share depending on IntegratedSecurity=true/false

Our company receives a daily file from one of our customers. It is an EDIFACT file translated to XML and downloaded from an SFTP server. We have developed one (c# console) application that: Download ...
McNets's user avatar
  • 23.7k
1 vote
1 answer
4k views

openrowset to connect to SQL server with a password containing single quotes

Can someone help me if there is ever a way to query an Azure SQL database using openrowset with an SQL authenticated account having "single quotes" in it's password string. My statement looks like the ...
Midhun C N's user avatar
-1 votes
1 answer
7k views

SELECT with OPENROWSET from a stored procedure with parameter [closed]

I am trying to do a select with openrowset from a stored procedure with a parameter but it is not working. SELECT * FROM OPENROWSET( 'SQLNCLI', 'Server=SERVERNAME;Trusted_Connection=yes;...
Elio Fernandes's user avatar
-1 votes
1 answer
461 views

How to execute OPENROWSET from SSRS

Trying to create a Dataset in SSRS that executes a stored procedure that imports data from an Excel file. The sproc transforms the imported data and outputs the reformatted data for the SSRS report. (...
SDBurgher's user avatar
1 vote
1 answer
23k views

metadata could not be determined because stored procedure contains dynamic SQL

I have a stored procedure that contains some dynamic SQL. I am trying to execute the stored procedure using OPENROWSET but am getting the error: Msg 11514, Level 16, State 1, Procedure ...
IMTheNachoMan's user avatar
1 vote
0 answers
207 views

OpenRowSet sp_update mystery - Update returns row updated, but does not update row

/* SELECT @@VERSION Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) Jan 6 2017 14:24:37 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 8.1 ...
Alocyte's user avatar
  • 422
0 votes
2 answers
2k views

Can OPENROWSET parameters be built dynamically?

This feels like an ‘obvious “yes” ‘ kind of question, but nevertheless: would it not be possible with OPENROWSET to have sub-queries regarding the file path that import for particular procedurally-...
Ben Williamson's user avatar
8 votes
1 answer
2k views

Where do Linked Server Queries get executed?

I have two instances ServerA and ServerB, and I have created a linked server in ServerA for ServerB as Linksrv_B. I can execute a query on ServerA using the four part naming convention: SELECT * ...
info.sqldba's user avatar
9 votes
2 answers
18k views

The metadata could not be determined because statement invokes an extended stored procedure

in sql server 2012 I used to have a look at the jobs by getting the output of SP_HELP_JOB -- https://www.sqlservercentral.com/Forums/Topic259078-8-1.aspx -- getting data from sp_help_job into a temp ...
Marcello Miorelli's user avatar
0 votes
4 answers
3k views

How to Export Table sql to Excel file in sql server 2014?

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 [...
RedArmy's user avatar
  • 171
2 votes
1 answer
1k views

how to temporarily change the sql server settings in order to do a task and when finished revert back?

when I run the script below all at once I get this error message: Msg 15281, Level 16, State 1, Line 58 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc ...
Marcello Miorelli's user avatar
4 votes
1 answer
5k views

Is there an equivalent to OPENROWSET in Oracle?

Is there an equivalent to OPENROWSET in Oracle? From OPENROWSET (Transact-SQL): This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and ...
User1974's user avatar
  • 1,457
3 votes
3 answers
3k views

9 digit zip codes are reporting LEN of 12

I am attempting to resolve some difficulties in getting zip codes to display properly. The original spreadsheet has zip codes of mixed 5 and 9 digit formats. After the import process, these 9 digit ...
Geoff Dawdy's user avatar
  • 1,123
2 votes
0 answers
427 views

Import from dbf - collation

I select data from dbf files through OPENROWSET Visual FoxPro OLEDB. Despite forcing collation on columns (e.g. SELECT name COLLATE Polish_CI_AS FROM Employees) the polish specific characters keep ...
Przemyslaw Wojda's user avatar
2 votes
0 answers
1k views

Errors while accessing Excel file using openrowset

I'm seeing the below error message while running the following T-SQL statement. select * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=123.xls', 'SELECT * FROM [Sheet1$]') ...
Yogi's user avatar
  • 245
3 votes
2 answers
3k views

How to read an excel file from a network machine using SQL?

I am trying to import some excel file data into a SQL table. I thought of using OPENROWSET for this. The issue is that the csv file is location in a different machine in the same network. How do I ...
AnOldSoul's user avatar
  • 471
8 votes
4 answers
20k views

Get @@SERVERNAME from linked server

This seems like a basic question but I can't find any answers out there - I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things: select <linked ...
dwjv's user avatar
  • 669
4 votes
1 answer
824 views

SSRS - ERROR NUMBER:7357 while passing multiple value parameters using comma delimited string to stored procedure

I am working on a SSRS report to display the logins permissions in a set of databases on a specific server. the server, the logins and the databases are all parameters. NULL shows them all (logins ...
Marcello Miorelli's user avatar