Questions tagged [openrowset]
69 questions
9
votes
3 answers
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 character and column 4 reserved 5 characters.
I have…
Martin Smith
- 87,941
- 15
- 255
- 354
8
votes
1 answer
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 * FROM Linksrv_B.master.sys.databases
or…
info.sqldba
- 327
- 4
- 15
8
votes
2 answers
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 table
-- marcelo miorelli
-- 01-april-2013
IF…
Marcello Miorelli
- 17,274
- 53
- 180
- 320
8
votes
4 answers
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 .@@SERVERNAME;
select
dwjv
- 679
- 2
- 9
- 15
6
votes
3 answers
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].[activity]
GO
CREATE TABLE [dbo].[activity] (
[dt] …
Marcello Miorelli
- 17,274
- 53
- 180
- 320
4
votes
1 answer
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 idea was to have the names of these servers…
datagod
- 7,141
- 4
- 38
- 58
4
votes
3 answers
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 I want to have a SELECT from the Excel…
Sky
- 3,744
- 18
- 53
- 68
4
votes
1 answer
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 accessing remote data by using OLE DB.
User1974
- 1,517
- 25
- 54
4
votes
1 answer
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 and DBS) server - only 1 - must be specified.
The…
Marcello Miorelli
- 17,274
- 53
- 180
- 320
3
votes
2 answers
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 *
FROM
OPENROWSET('SQLOLEDB',
…
Marcello Miorelli
- 17,274
- 53
- 180
- 320
3
votes
1 answer
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 statements on the Access database without any problem…
Alexandre Jobin
- 405
- 1
- 6
- 13
3
votes
1 answer
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 server database. I have used the following query…
M.Ali
- 1,970
- 10
- 27
- 38
3
votes
1 answer
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 varchar(200),
@sql varchar(max);
SET…
Kermit
- 1,194
- 13
- 27
3
votes
1 answer
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 anyone has a possible fix to what I am currently…
Dray
- 51
- 5
3
votes
2 answers
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 log files is not consistent.
The table has 10…
Stockburn
- 501
- 4
- 22