I am using the following code to retrieve data from an AS400 datasource. I have already setup the DSN connection(TestDSN) which is working just fine and I am able to connect and look at the data by creating linked-table in MS Access.
SELECT a.* from OPENROWSET('MSDASQL', 'DSN=TestDSN', 'select * from mylibrary.mytable') as a
Now the problem is I need to browse the data from SQL server, but when I use the above TSQL I get the following error message.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
Anybody knows how to get this to work?
BTW, I tried MSDASQL provider to access a SQL server and it worked so it does not seem to be a problem with the driver registeration.
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.
I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.
I am trying to get the count of records in a file in a single round trip to the server. I tried T-SQL code similar to below and used SQLBindColumn to bind to argument #1. The code executes without error, but the result is never returned.
Any ideas what I am doing wrong?
Thanks! CB
DECLARE @i AS INTEGER SELECT @i COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)
We have purchased an ERP system from a vendor which uses system DSN for all the reports. The system automatically creates DSN with Sa with SQL Server. The problem is the DSN is not working with AD users.
Active Directory server: Windows Server 2008 32 Bit.
SQL Server: Windows Server 2012 64 Bit. This server is already member of my Domain. e.g. CompDomain.com
What should I need to do in client PCs or Server to avail ODBC to AD users.
I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.
When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.
Microsoft Visual Studio 2012 Shell (Integrated) Version 11.0.50727.1 RTMREL Microsoft .NET Framework Version 4.5.51650
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 12.0.1524.0
And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.
I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.
Microsoft Visual Studio 2012 Shell (Integrated) Version 11.0.50727.1 RTMREL Microsoft .NET Framework Version 4.5.50938
SQL Server Integration Services Microsoft SQL Server Integration Services Designer Version 12.0.1524.0
why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?
I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".
When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.
How can I use DDL statements via ODBC?
I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for XP. However, when I try to add a new data source through ODBC Connection Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the SQL Native Client to not show up in the list of available ODBC data sources?
Hi all,I am having trouble getting linked Oracle 9 server in MS SQL Server2005 Express to work properly. My machine is running Windows XP.The Microsoft and Oracle OLE DB Providers have problems dealing withOracle's Numeric Data Type, so I decided to use Microsoft's OLE DB forODBC Provider and an Oracle ODBC source. When using the Microsoft ODBCfor Oracle Driver in my ODBC source I have inconsistent behavior.Sometimes my queries are processed properly, then other times I get thefollowing errorOLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttrfailed".OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returnedmessage "[Microsoft][ODBC driver for Oracle][Oracle]".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDASQL"for linked server "ODBCBEAST".I have no idea why sometimes I can connect to the linked server with noproblems andwhy other times it performs like this. I'm not changing anything aboutthe system I can think of. When I use an Oracle client (PL/SQL) I haveabsolutely no problems connecting. TNSPING returns that the connectionis good.This is unacceptable so I decided to try my luck with the Oracle 10gODBC driver. However when I use this and perform an openquery selectagainst the linked server I get back only 11 rows, when I know that thedatabase has over 100 rows (in fact when using the Microsoft ODBCdriver and it works that's what I get). I figured maybe the buffersetting needed to be raised in the ODBC configuration so I took it from64000 to 600000 (a magnitude of 10) but I still get back only 11 rows.I'm at my wit's end.Any suggestions on resolving one or the other problem would be muchappreciated.Thanks much
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
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 ?
