Questions tagged [openrowset]
The openrowset tag has no usage guidance.
49
questions
2
votes
1answer
67 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 ...
1
vote
1answer
61 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
...
2
votes
0answers
73 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 ...
1
vote
0answers
534 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 ...
2
votes
2answers
749 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 ...
0
votes
1answer
260 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 "...
0
votes
1answer
50 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.
...
1
vote
1answer
714 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)
...
1
vote
2answers
51 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 ...
3
votes
1answer
1k 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 ...
1
vote
0answers
99 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 ...
2
votes
1answer
363 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....
1
vote
1answer
2k 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) ...
1
vote
1answer
2k views
How to save a varchar(MAX) to a file?
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].[...
3
votes
3answers
2k 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].[...
3
votes
1answer
1k 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 ...
1
vote
1answer
879 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 ...
-1
votes
1answer
2k 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;...
-1
votes
1answer
243 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. (...
1
vote
1answer
8k 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 ...
1
vote
0answers
109 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 ...
0
votes
2answers
708 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-...
7
votes
1answer
1k 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 * ...
6
votes
1answer
10k 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 ...
0
votes
3answers
1k 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 [...
2
votes
1answer
831 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 ...
2
votes
1answer
3k 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 ...
3
votes
3answers
2k 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 ...
2
votes
0answers
281 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 ...
2
votes
0answers
935 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$]')
...
3
votes
2answers
2k 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 ...
8
votes
4answers
14k 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 ...
4
votes
1answer
581 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 ...
3
votes
1answer
2k views
OPENQUERY and server permissions
when I run my stored procedure it is a complete success, even if I run it through exec.
Running these:
sp_GetServerLogins
GO
exec ( 'sp_GetServerLogins ')
GO
I get this:
Needless ...
1
vote
0answers
2k views
Linked Server vs. Ad Hoc (OpenRowset / OpenDatasource) Distributed Queries
I have an application which needs to grab data from different clients' databases that reside at the client's location. So alongside their normal details such as company name, address etc, I also store ...
1
vote
0answers
583 views
Using loops inside Openrowset
I'm trying to execute a query across around 200 servers using openrowset function.
This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
2
votes
1answer
16k views
SQL Server - Linked Server - Using OPENROWSET with windows integrated security
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 *
...
1
vote
0answers
169 views
Update Linked Server Provider
Is there anyway to update a linked server provider without dropping and recreating the linked server?
The linked servers' provider need to be updated from SQLNCLI10 to SQLNCLI11
A coworker has ...
1
vote
0answers
934 views
Exporting to excel from OPENROWSET with cell formatting
I am trying to generate a report from SSMS to Excel, using OpenRowSet.
All is working fine, still I have to export a specific column with a barcode font.
I already have the column calculated from ...
3
votes
1answer
2k views
How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?
I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete ...
0
votes
1answer
393 views
Backup/restore using OPENDATASOURCE OR OPENROWSET
Has anyone ever successfully taken a backup or restore to another server using OPENDATASOURCE/OPENROWSET? I am not able to use linked servers so I am exploring this idea.
3
votes
1answer
37k views
distributed queries are configured to run in single-threaded apartment mode
I have sql server 2008 R2 64bit Developer's Edition Installed on my machine.
And Microsoft Office 2010 Professional 32bit.
I have been trying to import some Excel data from an Excel sheet into a sql ...
1
vote
1answer
909 views
type recognition with MonetDB, *create* tables from CSVs
I am confused whether you can copy records into a new table in MonetDB.
The official documentation seems to talk only about copying into existing tables. I have quite a large universe of billions of ...
1
vote
0answers
1k views
Loading image from file system into SQL query using openrowset
I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:
...
0
votes
1answer
861 views
SQL Server 2005 - Generate and Bulk Insert 3 million rows across 2 tables
I have a task to programmatically generate 3 million different rows of data and insert them into a database table for test purposes.
I have written a VB.NET program that handles the generation of row ...
4
votes
3answers
39k views
OpenRowSet - How to configure OLE DB Provider to be used for for distributed queries
I have an Excel file with data and would like to update a table in database based on the data the Excel file contains.
To do that I want to use OpenRowSet command. But I get the error below when even ...
-1
votes
1answer
2k views
SQL Server : openrowset problem [closed]
I'm looking for a solution to use openrowset with variables, without an execute,
so that I can use it in a table function.
I know that openrowset doesn't accept '+' and that I can't call exec() to ...
3
votes
1answer
3k views
Dynamic file name for file import
I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found.
DECLARE @fileName ...
3
votes
1answer
29k views
Working example of OPENDATASOURCE command using SQL Authentication
I need to run some ad-hoc queries on several hundred servers that are not part of a domain. Each server has a low-privilege SQL user account that has read-only access to a few tables of interest.
My ...
