Cannot Query Excel Linked Server

Hello,

I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;

-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts

I have the linked server configured as follows;

-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\\Cdnbwfin1\Data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls;Persist Security Info=False

When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')

The following message appears, and no results are returned;

[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?

Thank you for your help!

cdun2

Hi,

how are you connecting to the SQL Server while using this query ? If you are connecting to the server using a SQL Server login, the process will try to impersonate the SQL Server account (that one that runs the service of SQL Server) to acces the network file. If the Service account is not priviledged to access the file nor the network share, the process will fail. Could you describe your used enviuronment for a better understanding if the above description is your problem ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Cannot open database "testDB" requested by the login. The login failed.

I have created a new database within SQLServer Express 2005 and have an ASP page in IIS. Any time i attempt to access the database I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "testDB" requested by the login. The login failed.

The ODBC connection that I have created works fine if connecting to the same table (literally three sample fields with two records) in an Access database.

The database connection that I am using is:

Set DatabaseConnection = Server.CreateObject("ADODB.Connection")
DatabaseConnection.Open "DSN=testDB;UID=EINSTEIN\Administrator&pwd=password;DATABASE=testDB;APP=ASP Script"
Set rs = Server.CreateObject("ADODB.recordset")
SQLString = "SELECT * FROM testTable"
rs.Open SQLString, DatabaseConnection
if NOT rs.EOF then
Response.Write(rs("testField1") & " - " & rs("testField2"))
end if
rs.Close
DatabaseConnection.Close
Set DatabaseConnection = Nothing

EINSTEIN is the name of the server, rs = Recordset. I have tried changing to just Administrator instead of machinename\Administrator with no success. I have got SQL Server Management Studio Express CTP, and can log in fine to SQL Server using EINSTEIN\Administrator.

Please can anyone help!

Suggesting that you are going the DSN-less way (Connectionstrings can be found here www.connectionstrings.com) the database which is requested is not available for this user. You cannot provide a Windows User name if you want to use trusted authentication (it some time ago, that I used DSN, but I guess this is just possible for passing over SQL Server authentication. Try using a DSN-less connection with specifying a database that is accessible for the user.

See if that works, Jens Suessmeyer.

|||

It looks like your connection string may be a little incorrect, the current string is:

"DSN=testDB;UID=EINSTEIN\Administrator&pwd=password;DATABASE=testDB;APP=ASP Script"

It should be

"DSN=testDB;UID=EINSTEIN\Administrator;pwd=password;DATABASE=testDB;APP=ASP Script"

No Ampersand between Administrator&pwd, instead it should be a semi-colon

See if that works

Cannot Open / Create SQL Express Databases (.mdf files) in Visual Studio 2005 Professional

Hi, I am trying to open or create a sqlexpress database within Visual Studio 2005 Professional in the App_Data folder. If I attempt either method, I get the following dialog box:

Required Components Missing

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkID=49251

The link above just takes you to the download page for Sql Server Express 2005.

I have both SQL Server 2005 Developer and SQL Server 2005 Expression instances running. The existing database will work properly in my web application, however I cannot access it through Visual Studio.

As background, I did have a problem connecting to the database via the web application, receiving a "Failed to generate a User Instance of SQL Server" error. But I was able to fix that by renaming my SQLEXPRESS folder in C:\Documents and Settings\user\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data. When the web page ran, it created another SQLEXPRESS folder.

However, I cannot figure out why I am having the other issue.

Thanks in advance

Patrick

Hi,

This error means that you are not pointing to the correct instance.

Right click the mdf database > Modify Connection > Advanced > Data Source string.

Check this string and see if it points to the correct instance

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

cannot install AdventureWorks

I downloaded and installed SQL Server 2005 express Edition with advanced services.

My attempt to install the AdventureWorks from "Add or Remove Programs" failed for I was not able to see the "Documentation, Samples, and Sample Databases" node from "Feature Selection".

After reading a little bit I discovered that I had to download AdventureWorks from http://msdn2.microsoft.com/en-us/librry/ms144235.aspx

First I downoladed and installed SqlServerSamples.msi and then AdventureWorksDB.msi however I cannot find AdventureWorks.mdf to attach to my instance? Inside the folder C:\Program Files\Microsoft SQL Server\MSSQ.1\MSSQL\Data I have AdventureWorks_Data.mdf and AdventureWorks_Log

Can you please help?

thanks

Rick

Instead of installing AdventureWorksDB I installed AdventureWorksLT and I was able to attach the db at the first attempt.|||AdventureWorks_Data.mdf IS the AdventureWorks data file -the one to attach to your server. (And you will need the log file (*.ldf)|||

I had both files .mdf and .ldf but when I was trying to attch .mdf I was getting an error. I tried several times, I even uninstalled and re-installed a couple of times and this is the error that I got:

TITLE: Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

BUTTONS:

OK

|||Please verify that the two files are not READ ONLY.|||

nope, they are not.

By the way, I had the same problem on two separate PCs. One is running XP pro and the other one Vista. Both installation were fresh.

|||

Hi Rick,

Verify that your explicit user account has Full Control on both the data and log files. Permission via group membership is not enough, at least on Vista; if your name isn't on the file it fails to attach. Not sure what's happening on the XP system, but this will prevent things from working on Vista because of User Account Control.

Mike

|||Hi Rick,

I am not sure if you have solved the problem or not. But I ran into the same problem and found out that the problem was caused by permission to access the file.

When I installed SQL Server Express, I chose used both Windows Authentication and SQL Server's. I login using SQL Server authentication (sa). When I tried to run the attach database query, I ran into the same problem as yours. I then quit the database and re-login using Windows Authentication. Afterwards, I reran the query and succeeded.

Hope this helps.

Suparman Widjaja
DOOR3.COM
|||That worked!!! I was able to attach the AdventureWorks database by logging in to SqlServer Management Studio using Windows Authentication instead of "sa". Thanks.

cannot install AdventureWorks

I downloaded and installed SQL Server 2005 express Edition with advanced services.

My attempt to install the AdventureWorks from "Add or Remove Programs" failed for I was not able to see the "Documentation, Samples, and Sample Databases" node from "Feature Selection".

After reading a little bit I discovered that I had to download AdventureWorks from http://msdn2.microsoft.com/en-us/librry/ms144235.aspx

First I downoladed and installed SqlServerSamples.msi and then AdventureWorksDB.msi however I cannot find AdventureWorks.mdf to attach to my instance? Inside the folder C:\Program Files\Microsoft SQL Server\MSSQ.1\MSSQL\Data I have AdventureWorks_Data.mdf and AdventureWorks_Log

Can you please help?

thanks

Rick

Instead of installing AdventureWorksDB I installed AdventureWorksLT and I was able to attach the db at the first attempt.|||AdventureWorks_Data.mdf IS the AdventureWorks data file -the one to attach to your server. (And you will need the log file (*.ldf)|||

I had both files .mdf and .ldf but when I was trying to attch .mdf I was getting an error. I tried several times, I even uninstalled and re-installed a couple of times and this is the error that I got:

TITLE: Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

BUTTONS:

OK

|||Please verify that the two files are not READ ONLY.|||

nope, they are not.

By the way, I had the same problem on two separate PCs. One is running XP pro and the other one Vista. Both installation were fresh.

|||

Hi Rick,

Verify that your explicit user account has Full Control on both the data and log files. Permission via group membership is not enough, at least on Vista; if your name isn't on the file it fails to attach. Not sure what's happening on the XP system, but this will prevent things from working on Vista because of User Account Control.

Mike

|||Hi Rick,

I am not sure if you have solved the problem or not. But I ran into the same problem and found out that the problem was caused by permission to access the file.

When I installed SQL Server Express, I chose used both Windows Authentication and SQL Server's. I login using SQL Server authentication (sa). When I tried to run the attach database query, I ran into the same problem as yours. I then quit the database and re-login using Windows Authentication. Afterwards, I reran the query and succeeded.

Hope this helps.

Suparman Widjaja
DOOR3.COM
|||That worked!!! I was able to attach the AdventureWorks database by logging in to SqlServer Management Studio using Windows Authentication instead of "sa". Thanks.