When I execute queries that utilize the OPENROWSET command they fail on both our Production and Test servers (they are identical installs of OS and SQL server 2005 sp2). Checked permissions, file is on local drive, connection string is correct, etc. If I restart the SQL service it works OK for acouple of weeks, then fails agin until I restart the service.
I have searched and posted this issue to couple of Newsgroups without any luck. The response I am seeing is restart the service. The issue seems to be limited to the the Jet and MDASQL providers. Below are some examples of queries that are failing until the service is restarted. Any information would be helpful.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:Test_MailLogData.mdb'; 'admin'; '', tblMailLog)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir= D:;',
I have a Windows 2003 64-bit server running SQL 2005. I am trying to run an OpenRowSet query, but I am getting ""cannot create instance of linked server" (null)" The OpenRowSet I am trying to run was running perfectly fine on the same machine before we moved to SQL 2005 and runs fine on many other machines - so I do not think it is an issue with the openrowset itself.
When I've had his issue on other machines, I have run the following:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure €˜Ad Hoc Distributed Queries€™, 1;
GO
RECONFIGURE;
GO
And I could then run the Ad Hoc queries fine. But, when I try on this machine, I get the SQL error message:
"The configuration option does not exist, or it may be an advanced option."
I looked at the settings in SQL Server Surface Area Configuration and Ad Hoc was selected; so it should be working. I am logged in as Administrator and have sysAdmin permissions in the SQL database. I'm not sure where else to look or what I am missing.
I've looked many places on the web and have tried many 'solutions' without effect.
select * from openrowset(bulk '\server1c$file.txt', SINGLE_BLOB) as t works from sql server itself, but doesn't work from any other machine. I got "Operating system error code 5(Access is denied.)." I am running as the domain admin, the file.txt has full control for everyone. In server1 even log, I see Anonymous Logon.
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
How come when I run the script: select * from openrowset ('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=\inawwwpro01d$atrUploadACK102.xls' , 'select * from [sheet1$]') from Server A I get a RESULT, and when I run from Server B I get the following error: Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server. Both servers are using IDENTICAL SQL Logins. Both servers are SQL 2000 SP2.
When I run the script on Server B logged in as SA, then I get a Result!!!
how can i use openrowset. my aim is to develop a import and export data from diff. server . i am using vb.net 2003 , sql sever 2000. when i am running openroeset function with window or server authontication it is showing 'OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
anybuddy help me , pls. tell me the right solution.
Hi! I decided to use OPENROWSET for importing data from an excel file into a sql table.When I import the data,I have a problem:
-not all the data is imported to my sql table -some values in the sql table are different from the ones in the excel file For example,a value that in the excel file is:87878787 will be in the SQL table:8.78788e+007.
Hi Folks,I am trying to load data from a table in MS Access to SQL Server 2000using T-SQL OPENROWSET. When I select data from the remote database (MSAccess) using SQL Query analyzer, the columns do NOT appear in the sameorder as seen in Access directly.For e.g. if Access table has columns Cy, Cx, Cz the output in Queryanalyzer appears as Cx, Cy, Cz. It appears to arrange the fieldsalphabetically. This causes problems when I do a 'insert into select *from' as the field definitions do not agree.Is this a bug or is there a setting in Access/SQL which I am missing?Also, please let me know if there is a workaround for this issue.Thanks in advance!Bhaskar
I am trying to use OPENROWSET in SQL Server to connect to my Access database, but I keep getting the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file 'X:SetupDatabaseKDB_X2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I have checked the database, it is not in use, and there is no password set on the database that would prevent me from getting access to it. Any ideas? Here's the syntax I'm using:
SELECT * FROM OpenRowset('Microsoft.Jet.OLEDB.4.0', 'X:SetupDatabaseKDB_X2.mdb';'Admin';'', subPSEL_PList))
I having an excel file called TEST.XLS (in c: drive) which has 3 columns (c0,c1,c2) & 8 rows c0 c1 c2 1 a A 2 b B 3 c C 4 d D 5 e E 6 f F 7 g G 8 h H
In sql server i have one table called TEST which has three columns c0 char(10) c1 char(10) c2 char(10)
when i runnig the following query
select * into TEST from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:TEST.xls;HDR=YES', 'select * from [Sheet1$]')
i am getting error like this
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Version : Sql server 2000 & Excel 2002
Anybody having any idea why this error occuring? thanks in advance... Philkos
I am having trouble exporting the correct data from sql server to an excel spreadsheet using 'OPENROWSET'
The problem is that although the data from my sql table is say : 'a',1,2,3 , the excel spreadsheet sees the data as : 'a','1,'2,'3 ..1,2,3 are of course numbers NOT text, its just that the driver has put a single appostraphy before the number !
I know there is a bug with the ISAM driver but has anyone managed to solve this or has anyone have any alternatives ?
I'm doing an openrowset query on an excel sheet. (Using SQL Server 2005) Everything works great, except that I have one column that has both numeric and text data in the spreadsheet. The query returns that column as datatype varchar but puts nulls in the rows that have numeric data in the spreadsheet.
Any suggestions?
I run:
select * from openrowset('microsoft.jet.oledb.4.0', 'Excel 8.0;database=[filepathandname], 'select * from [Sheet1$A4:G5000]')
and would like to create views for each distinct table, using openrowset. An added complexity is that the library name depends on the company code (i.e. BISxxSET.ALPA0A turns to BIS03SET.ALPA0A for company 03 whereas BSFFBUA.ALPA1A remains intact).
Hy Forum !I tried to do somsthing like this:CREATE PROCEDURE dbo.maches ASselect * from openrowset('Microsoft.Jet.OLEDB.4.0','C: empFehler.mdb'; 'Administrator'; ' ' , Fehlerliste)This user 'Admistrator' exists and there is no password for the .mdbfile. In the end the syntax checker tells me:Fehler 7303: Datenquellenoblekt von OLE DB Provider'Microsoft.Jet.OLEDB.4.0' konnte nicht initialisiert werden.I'm using Access 2000 and SQL Server 7.Thank you
I have an excel file on a web server on a different location to the sql server. I want to use OPENROWSET to query the excel file, but it doesn't seem to like using a URL.
Is this the case, or am I doing it wrong? Works fine when I use a local address (eg c:excelfile.xls) when testing locally, but I need to use a URL in production.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=http://mydot.com/excelfile.xls','SELECT * FROM [page1$]')
I have an access database from which i want to use data in sql server if a table has column with values like 6.32404E-244 i've got an error message like:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)' returned invalid data for column '[Microsoft.Jet.OLEDB.4.0].Stoc_Max'
a simple select like 'SELECT * FROM OPENROWSET(.....,table) gives me this error.
We have lots of OPENROWSET usage here, all with embedded user id's and passwords. In an attempt to find a way to eliminate such a foolish practice I created a system DSN on my PC and tried to use it to connect to our sqlserver 2000 database as follows:
SELECT a.* FROM OPENROWSET('MSDASQL','DSN=TargetDB', 'SELECT * FROM table1') AS a
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. Authentication failed. [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I thought the reason to create an ODBC system data source was to not have to enter credentials in code but use the credentials in the system DSN. I guess I was wrong. Is there any way to not embed user id's and passwords in OPENROWSET code? Having user id's and passwords in code is dangerous and foolish and I am surprised people do it.
Hi everyone, I have some doubts about OPENROWSET T-SQL function . According to my knowledges about this, openrowset function provide us to use a data from a different Server only that time that we use this function. After we use this function, there is no any linked server trace on the local server. So is this true ? I see that everyone use different parameter for this function. For instance, everyone firstly use OLE DB name and then the second srvprovider name changes programmer to programmer, so why ? And lastly, I wonder about the relationship between ad hoc method and openrowSet function.
Hi! I'm using OPENROWSET to import data from an excel file into a MS SQL table.I got it to work.....Now,my problem is that not all the data from the SQL table is imported and some values are different than the ones in the excel file. For example,in the excel file,I have the value:87987845.In the SQL table,the value is:8.79878e+007.Any idea on what causes this?
I have to loop through number of tables on a remote servers. Now i have witten a script(simple sql script) which loops though tables names and gets data using OpenRowSet().
My alternative way is through SSIS, keeping RetainSameConnecion=True, and then looping.
My question is,
Does calling OpenRowSet, creates a new connection each time the statement is executed? If Not, then how's SSIS better in this case? (please confirm that, since i was not able to see any audit login/logout in profiler)
I'm intersted only to see if the login happens three time, but i'm not able to see that. Also, there was no Audit Logout. Does SQL Server automatically pool connections ? If i run Openrowset 3 times, shouldn't it open & close connection 3 times ?
I have a text file, that I need to import to SQL. I am trying to do this using OPENROWSET. This is the qry i use below.
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=c:edge;', 'select * from vwConstrLendingLoanDefiDetailReady.txt')
I have created a System DSN, For the microsoft text driver. Anyhow it throws this error:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Seeing that the old system table syslang is no longer updateable(Not even in DAC) i decided to take a look at the definition of it, just to see where the languages are stored.(out of curiosity)
can someone tell me why the upper part of the dynamic sql doesn't work while the lower does? i use openrowset to connect to Oracle. thanks!
declare @abc varchar(10)
select @abc = 'table20070921'
execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'', ''select * from ' + @abc + ' where flag = ''abc'') as a')
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'abc'. Msg 105, Level 15, State 1, Line 2 Unclosed quotation mark after the character string ') as a'.
I'm working with SQL 2005 and I want to connect to other server but i can't use linked server option because of secutirty policies.
In the remot server I have a stored procedure which retrieve me information depending on one header and the date i'm consulting. Then I use that information in my local server and i need to automate the process.
I have been trying with OPENROWSET and it works ok, but just with the first header, when I execute it with other header it doesn't work.
Select * from OPENROWSET('SQLOLEDB',Server; 'user';'pwd', 'Exec servidor.esquema.sp_Consulta 2,''20070101'',''20071031''') ______________________________
Mens. 7355, Nivel 16, Estado 1, LĂnea 1
The OLE DB provider "SQLNCLI" for linked server "(null)" supplied inconsistent metadata for a column. The name was changed at execution time.
Another issue i want to solve is that i want to sent the date as a parameter, 'cause i need the information montly. I was reading that the openrowset and opendatasource are macros not functions, I think that's why it doesn't accept parameters in the string of the query, but now i don't know how to do to work with all the information (remote and local) without OPENROWSET, OPENDATASOURCE, LINK SERVER or an SSIS.
I am trying to execute the following ad-hoc query (I have already configured the remote server to allow ad-hoc queries):
--EXECUTE AS USER = suser_name() SELECT c.* INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=MyRemoteServer;Trusted_Connection=yes;', 'select * from mydb..mytable (nolock) where PurchaseOrderID in (''19509114'', ''24075854'')) AS c;
I receive the following error message: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I have admin privileges on the remote box-how can I get this to execute in the context of my account rather than this NT AUTHORITYANONYMOUS LOGON account?
Hi! Can openrowset be used in a scheduled job?I'm asking this because I created a scheduled job for importing data from an excel file into an sql table.The job contains a single command(it's supposed to import data at a certain time,daily).When I check the job status,it constantly appears as 'not running'.Further more,when I check the reason for the failure,I get: Executed as user: <my id>. Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000] (Error 7410). The step failed. Could someone tell me what's causing this error?
