Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [openrowset]

58 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
  • 77,689
  • 15
  • 224
  • 316
9
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
  • 14,544
  • 46
  • 126
  • 249
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
  • 307
  • 2
  • 14
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
  • 659
  • 2
  • 8
  • 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
  • 14,544
  • 46
  • 126
  • 249
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,634
  • 15
  • 45
  • 67
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
  • 14,544
  • 46
  • 126
  • 249
3
votes
1 answer

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
  • 14,544
  • 46
  • 126
  • 249
3
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,006
  • 4
  • 33
  • 56
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
  • 375
  • 1
  • 4
  • 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,800
  • 8
  • 24
  • 36
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
  • 26
3
votes
1 answer

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 Express (exact versions below as retrieved with…
maitland
  • 31
  • 1
  • 2
3
votes
1 answer

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 the file. Upload it to one SQL Server (2005)…
McNets
  • 22,549
  • 9
  • 43
  • 80
3
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,323
  • 16
  • 42
1
2 3 4