Error Creating Linked Server To Excel

Jul 20, 2005

Hi,
Please help, I'm getting desperate. Any ideas warmly welcomed!
I'm trying to read from a basic excel file (1000 or so rows from
column A) but am having problems. The code I am using is:

Declare @Return Int
SET NOCOUNT ON

Exec @Return= [master]..[sp_addlinkedServer] 'READ_XLS', 'EXCEL',
'Microsoft.Jet.OleDB.4.0' , 'e:jsbackupRACodes.xls',
NULL, 'EXCEL 8.0'
print 'set up Return : ' + convert(varchar(10),@Return)
--NB E: is the drive as seen oon the server

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'READ_XLS',
@useself = 'true'
print 'login Return : ' + convert(varchar(10),@Return)


When I try to read from the (one) excel sheet in the file, via
Select * from [READ_XLS]...RACodes$
or to list what tables/sheets are available, via
exec sp_tables_ex 'READ_XLS'

I get the following error:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].

What am I missing?

*Many* thanks in advance.
Andy

View 1 Replies


ADVERTISEMENT

Error While Creating The Linked Server

May 19, 2008

Hello,

I am trying to create the linked server to IBM DB2 server, and getting the following error message. Can some one take a look at this and let me know. Thanks!

TCP Provider: No connection could be made because the target machine actively refused it.

OLE DB provider "SQLNCLI" for linked server "Linked server name" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "linked server name" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 10061)


even if I create the linked server using the management studio I am getting the following error when I try to test the connection for the linked server.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "DB2OLEDB" has not been registered. (Microsoft SQL Server, Error: 7403)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7403&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

View 1 Replies View Related

Error Creating View Which Uses Linked Server

Jan 24, 2008

Hellow,

This is the siuation:
I have on server A SQL 2005
on server B i have SQL 2000

I want to create in a database on server A a view which uses a linked server to get data from server B.
The linked server works fine.
But when I want to execute the creation of that view i get this error:

OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Procedure VW_ASSETTABLE, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK". The provider supports the interface, but returns a failure code when it is used.


I have checked and the collations between the to databases are the same.
Anyone an idea on who to do such thing?
The rights that i have an server B or not that much.

Thanx for the info

View 1 Replies View Related

SQL Server 2008 :: Getting Error While Creating Linked Servers

Sep 15, 2015

Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3

/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'

This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".

How to setup the linked server........... Below are the drivers installed on the SQL server

PostgreSQL35W
PostgreSQL30

View 2 Replies View Related

Creating A Linked Server To AS400.

Mar 15, 2001

I wanted to know whether it is possible to query a database in AS400 server from MS SQL Server 7.0 using linked server. If it is possible, could you show some pointers as to how it can/should be implemented.
Thanks in Advance.
Manojkumar
DMT Team
DaimlerChrysler Capital Services
Phone - 203-845-7326
EMail - manoj.kumar@dcxcapital.com

View 1 Replies View Related

Creating Table On A Different Linked Server

May 6, 2007

Using Microsoft SQL 2000

When creating a table I want to be able to specify not only the db to create it on but also which server to create it on. I have two servers that are linked together, I can view all data without issue.

Doing further research it looks like with the create table command you can tell it the new table name and the database but you can't tell it which server to use. Is there a way of doing this?

Example :

CREATE TABLE LAPTOP.database.dbo.tableName (a INT) gives the following error:

The object name 'LAPTOP.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

I am new to linked servers so basically my question is, how do you point to a server within sql before I execute the create table command?

Tx in advance


Mark

View 2 Replies View Related

Creating Linked Server Issues

Nov 17, 2005

I have a Merge Replication Setup. With 3 computers. One computer acts has a central publisher. The 2nd one is a subscriber, and republishes its data to the 3rd computer. Every thing is being done using Windows Authentication method. the 1st PC has SQL SERVER 2000 (Enterprise Manager, the works). The other two are SQL Server MSDE (Rel. A). My process is up and running and works, but heres my issue.

I need to script the replication installation process out., For future installations on machines that will be located in remote parts of the world. The will reside on a secure DSL network.

Anyone familiar with Scripting Merge Replication knows that once you've run certain scripts/procedures on your subscriber, you have to run a final procedure on the publisher to activate or initiate the subscriber as a valid one, and the you can start the Merge Agent on the subscriber, and voila! you're good. The procdure you run is called sp_addmergesubscription . It goes something like this :

BEGIN
exec sp_addmergesubscription @publication = @publicationname, @subscriber = @servername, @subscriber_db = @Attachedbname, @subscription_type = N'pull', @subscriber_type = N'global', @subscription_priority = 56.250000, @sync_type = N'automatic'
END


My intention was to embed this system in a stored proc on the publisher and then call it remotely from the subscriber, before starting the Agent. IT FAILS!!! This is my proc:


Create Procedure RunSp_AM_subscription(@servername Varchar(30), @IAdbname Varchar(20), @Attachedbname Varchar(20))
AS

DECLARE @publicationname Varchar(30)
SET @publicationname = 'REPUBLISH-'+@IAdbname+''

BEGIN
exec sp_addmergesubscription @publication = @publicationname, @subscriber = @servername, @subscriber_db = @Attachedbname, @subscription_type = N'pull', @subscriber_type = N'global', @subscription_priority = 56.250000, @sync_type = N'automatic'
END


When I call it locally ot works fine, but not otherwise........... :eek:

When I try to create a linked server SQL Server seems to think that one already exists (because Replication has already created the said server as a remote server). I think the fact that I am not using SQL Authentication might be a problem too.

Does anyone have any ideas? I mean seriously, any suggestions on what to do. This is qute Urgent..............

Thanks.

'Wale

View 1 Replies View Related

Creating Linked Server To Sybase IQ

Nov 10, 2006

I would like to create a linked server from SQL Server to Sybase IQ. I have created linked servers before so I know how to do that. However, I dont know the specifics of creating a linked server to Sybase IQ.

What are the parameters that are necessary to link IQ to SQL Server. Which Provider do I use? Do I need to install a special driver?

View 2 Replies View Related

Creating A Linked Server To Access DB

Jan 13, 2004

I'm trying to create a linked server to an Access database that resides on a separate machine.

On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.

If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.

The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.

I create the linked server using the following command:
exec sp_addlinkedserver
@server = 'AccLinkedServer',
@provider = 'Microsoft.jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\DatabaseServerAccessDatabase.mdb'


If I run the SQL statement:

SELECT * FROM AccLinkedServer...AnyTableYouLike

I get this error:
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 cannot open the file '\DatabaseServerAccessDatabase.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've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.

Anyone got any other advice, before I go totally insane with this.

Cheers.

View 2 Replies View Related

Creating A View From A Linked Server

Jul 23, 2005

Hii have created a new database and a new linked server that points to anAccessDB using an ODBC DSN.Now inside that new sql db i have create i need to created a new view soi open EM went to views and paste the followingselect * from openquery (AccessLinkedServer,'select * from mytable')i press run and i see the data ok .but when i try to save the view i getthe followingODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operationcould not be performed because the OLE DB provider 'MSDASQL' was unableto begin a distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returnedmessage: [DataDirect][ODBC dBase driver]Optional feature notimplemented.][Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned0x8004d00a].*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Creating A Linked Server To An Oracle Database

Aug 29, 2000

Hi all,

I hope someone can help me with linked servers.

In one of my applications running on a MSSQL database, I need to issue a select call to an Oracle database to pull information into my application.

To accomplish this,

1. the ORacle Admin has created a view of the Oracle database for me.
2. I have installed the Oracle client on the SQl server box.
3. A friend has told me that one way to accomplish this is create a linked server from SQL to use OLE/DB for Oracle to connect to the Oracle database
4. So I create the linked server successfully; create the server login sp_addlinkedsrvlogin successfully
5. When I go to query the database, I get the error below:


Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.

You will be unable to use this provider until these components have been installed.]


Anybody understand this?


Thanks in advance,
Faustina

View 4 Replies View Related

Permissions Problems Creating A Linked Server

Jun 7, 2007

I am trying to create a linked server in the management studio and am getting an error

"A required operation could not be completed. You must be a member of the sysadmin role to perform this operation"

I have tried giving the user rights via

GRANT ALTER ANY LINKED SERVER TO [DOMuser]

as well as adding them to the setupadmin group. No luck.

I can add the linked server via sp_addlinkedserver as the user.

Any ideas?

View 1 Replies View Related

Add Linked Server At Excel VBA

Aug 29, 2006



I had SQLDMO at reference library.

Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""

End With
s.LinkedServers.Add ls
s.Close

'End Sub

I block ProviderString as i don't know what is it.

I got an error message when it's running the line "s.LinkedServers.Add ls" that

"Run-time error '-2147206257 (80043b8f)':

Automation error"

My question:

1. What is providerstring? what should be put here?

2. How to fix the error

View 1 Replies View Related

Excel File As A Linked Server

Apr 25, 2000

I am trying to link an excel file as a linked server. I tried two methods unsuccessfully.

first: using

EXEC sp_addlinkedserver 'Ex',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:list5.xls',
NULL,
'Excel 5.0'
GO

When I run
EXEC sp_tables_ex Ex, I get the error below.

Server: Msg 7303, Level 16, State 2, Procedure sp_tables_ex, Line 20
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]


Second: I link the excel file using ODBC datasource:

EXEC sp_tables_ex mylink, works fine with mylink being the linked server name. I get the table names eg. table1$, table2$.... However when I try to query the tables, I get this error:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.


thanks
FS

View 1 Replies View Related

Linked Server To Excel With UNC Path

Apr 5, 2006

We have been having problems getting a linked server to an excel file
working with an UNC path. If the UNC path is to the SQL server itself
it will work but not if the UNC path is to another server.

The SQL server 2000 SP 4 processes are running under a domain id and we
can logon interactively with that same login id and we can access the
excel file via the same UNC path.


We have tried it with setting up a linked server and also linking to it
'on the fly':


SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\AnotherServerUNCpathexcel.xls";User
ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$
go


The message we are getting is the following:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The
provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not
give any information about the error.].


It appears from reading Microsoft's documentation and other topics that
this should be possible. Any ideas on what we are missing?

View 6 Replies View Related

Linked Server - Excel Spreadsheet

May 12, 2008

I am trying to set up a linked server in SQL Server 2005 to link to an excel spreadsheet.

-I am selecting Jet 4.0 as the provider
-Product name is Excel
-Data Source is the path on our network to the excel file: N:Devon54034.0 Engineering4.01 ProcessLinelistIFCLDT Field.xls
-Provider String is Excel 8.0
-Security | Login not defined is set to Be made using the login's current security context.

The Excel file is an Excel 2003 spreadsheet. The worksheet is titled Pages

I have a query window open in SQL Server Management Studio and the following is my select statement:

SELECT * FROM DEVON_LINE_LIST...Pages$

I get the following error message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" reported an error. Authentication failed.
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 "DEVON_LINE_LIST".

I get similar error messages no matter which security settings I pick.

Any thought as to what I can try to get this to work?

View 3 Replies View Related

Cannot Query Excel Linked Server

Dec 8, 2006

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 \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_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; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_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

View 1 Replies View Related

Linked Server From Excel Problem

Mar 21, 2008

Hello all-

I configured an Excel document as a Linked Server importing into SQL Server using SQL Server Management Studio Express 2005. I run a query like this to set up the desired database:
Use Num1DBase
GO
SELECT * INTO Num1Table FROM LINKSERV1...[Sheet1$]

However, my production Excel document has many, many sheets with spaces in the title [Sheet 2$] that results in this error:

Msg 7314, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERV1" does not contain the table "Sheet 2$". The table either does not exist or the current user does not have permissions on that table.

All I can think of right now is manually going in and removing the spaces from the worksheet names so the query works. Is there a more programmatic way for me to deal with this?

Thanks, MHY

View 9 Replies View Related

Excel Linked Server Problem

Oct 16, 2006

To All

I'm using the following code to create a Linked Excel server.

---

EXEC sp_addlinkedserver 'ExcelSource',

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'\schuette-dcDriveHTempEDICSCNEDI.xls',

NULL,

'Excel 8.0';

GO

-----

The server create correctly, but when I try the following

SELECT * FROM ExcelSource...CSCN

I get the following error

----

[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, Line 15

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

----

I have verified that i have full access to the "Temp" directory. I have even tried creating a "Named Range" in the spreadsheet with no success.



Thanks

David Davis

View 2 Replies View Related

Accessing Linked Excel Server

Jan 18, 2007

I created a linked Excel server that is stored in a SQL2000 database.

I can run the following from the SQL server with no problem.

Select * From CSCNEDI...EDI$

When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)

[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, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

Thanks

David Davis

Schuette Inc.

View 1 Replies View Related

Problem While Creating A Linked Server From SQL-Server2000 To An Access97 Database

Apr 30, 2007

I was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts

EXEC sp_addlinkedserver
@server='REMOTE_OFFICE',
@srvproduct='Jet 4.0',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='F:RealEstate_Office1.mdb'

and

EXEC sp_addlinkedsrvlogin
@rmtsrvname='REMOTE_OFFICE',
@useself='false',
@locallogin='sa',
@rmtuser='Admin',
@rmtpassword=NULL

And while querying the linked server from the query analyzer using the following select command

SELECT *
FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK

I got the following error

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].

could you explain why this happen

View 4 Replies View Related

Creating A Linked Server To AS400 In SQL Server 2005

May 24, 2006

I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.

How do you get the linked tables to display in the Linked Server folder?

David



View 4 Replies View Related

SQL Server 2005 Creating Linked Server With DB2 On ISeries

Dec 18, 2006

Hi,

I am using SQL Express 2005 sp1

When I created a linked Server to the DB2 on iseries ( V4R5) , the SELECT statement fetches me only the first record and the following message appears.

OLE DB provider "MSDASQL" for linked server "DB2TEST" returned message "[IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "DB2TEST".

I am using 'MSDASQL' which refers to the DSN setup on my machine using the ODBC "Client Access Driver 32-bit"

Strangely when I used the same DSN in SQL 2000 within the DTS package, I was able to connect and import data and worked like a charm.

Can anyone advice me where I am going wrong.

Thanks in advance

View 1 Replies View Related

Retrieving Data From Excel Linked Server

May 16, 2007

I'm trying to set up a linked server as in the article

http://www.databasejournal.com/features/mssql/article.php/10894_3085211_3

I use the following SQL to set up a linked server with Excel and attempt to retrieve data. The c: empauthors.xls is downloaded from the second page of the article above.

EXEC sp_addlinkedserver 'EXCEL',
'Excel',
'Microsoft.Jet.OLEDB.4.0',
'c: empauthors.xls',
NULL,
'Excel 8.0',
NULL
GO
SELECT * FROM EXCEL...Sheet1$
GO

The query sets up the linked server OK, and retrieves the field names, but no data is returned. The results in Management Studio are a list of column names:

au_id au_lname au_fname phone address city state zip
contract

then an error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Unknown".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT `Tbl1002`.`au_id` AS `Col1004`,`Tbl1002`.`au_lname` AS `Col1005`,`Tbl1002`.`au_fname` AS `Col1006`,`Tbl1002`.`phone` AS `Col1007`,`Tbl1002`.`address` AS `Col1008`,`Tbl1002`.`city` AS `Col1009`,`Tbl1002`.`state` AS `Col1010`,`Tbl1002`.`zip` AS `Col1011`,`Tbl1002`.`contract` AS `Col1012` FROM `Sheet1$` `Tbl1002`" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL".

Does anyone know how to resolve this? I'm using Windows Authentication to connect to a SQL server (9.0.3054) instance on localhost, and am running from a test database query window.

Thanks!

View 2 Replies View Related

Problems Reading Data From Linked Server To Excel In SQL Server 2005

Oct 4, 2007

I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)

Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?

View 8 Replies View Related

SQL 2005 X64 Linked Server To Excel 2003/2007

Aug 27, 2007

Is there a way to create a SQL 2005 x64 Linked server to an Excel (or Access) 2003/2007 file? In SQL 2005 32bit this was possible. Does anyone know of a solution or a work around?

View 3 Replies View Related

Importing Excel Using Linked Server And Passthrough Query

Jan 24, 2008

What: I am trying to import data from spreadsheets to SQL Server.

Where: Windows Vista, SQL Server 2005 Express, Office 2007.

How: Using linked servers following KB306397.


In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default:


Linked server: XLSX

Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider

Product name: XLSX

Data source: D:XLSX.xlsx

Provider string: Excel 12.0
The linked server was created ok.

I then followed KB321686 and ran this:



select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]')

and got this:


Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".

But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]')

the error is:



Msg 7357, Level 16, State 2, Line 1

Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object.



It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS.

From the same KB article, I also tried:



select * from XLSX...[Sheet1$]

but got this:



Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".

Help help.

Thanks.

View 5 Replies View Related

Problem When Creating SP To Import Big Data From Linked Server Mysql By Using Openquery() To Sql 2005

Oct 27, 2007

I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:

.....
Truncate table sqltblA;

Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....

But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.

However if i try to change the returned result to 100,000 rows by changing script to

Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.


Does anyone know what's going on there when I try to CREATE that SP and any solution to it?

Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(

View 1 Replies View Related

Creating A Linked Server To A Server With A Dash In The Name...

Sep 17, 2007

I need to create a linked server to a SQL 2005 server (B) from another SQL 2005 server (A). Problem is that Server B's name has a dash in it, and SQL Server doesn't like dashes in the name when running a query against the linked server. Server B's network name is Server-B. For example, from Mgt Studio on Server A, I've created a link to Server B whose name is Server-B, so in my query from Server A, I issue: SELECT * FROM Server-B, and get the following error message:



Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.



Any ideas,
Thnx
Roz

View 3 Replies View Related

Linked Server Excel Import Doesn't Work In Vista

Jan 15, 2008

I was using linked servers to import Excel spreadsheets into SQL Server Express 2005. This worked fine with Windows XP and Office 2003.

I have just migrated all my stuff to Vista and Office 2007. Linked servers just can't be created:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------

The parameter values I used are:

Provider: Microsoft Jet 4.0 OLE DB Provider
Product name: Excel
Data source: D:...somelocalfile.xls
Provider string: Excel 8.0

I gave full access rights to the Data source file and folder to NETWORK SERVICE and SQLServer2005MSSQLUser$MOZART$SQLEXPRESS where mozart is my PC name.

If I change Data source or Provider string to some garbage string, the exact same error message appears. So it appears the error might be in the Provider?

Help help please.

View 4 Replies View Related

Create Linked Server In SQL 2005 From Excel Spreadsheet And Have Primary Key?

Sep 6, 2007

Is it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?

Thanks,
--Stan

View 2 Replies View Related

ISAM : Excel Linked Server In Sql Server 2005

Jun 7, 2007

Dear Friends,

I am getting error when trying to make excel file as the linked server in my sql server the details are as under:



os: windows xp and also tried on windows server 2003

server: sql server 2005 sp2

excel: office 2003 and also tried with .xls file of office 2007



tried to add the remote as well as file on the same computer as linked server, but error was:



could not find installable ISAM and error no was 7303.



Please help me up.

I got the idea to make it as linked server from microsoft article and followed the neat steps and got the errors.

Thanks,

View 1 Replies View Related

Transact SQL :: Formatting Excel Sheet While Creating From BCP In Server?

Jul 1, 2015

How can i Format Column Name(Different Color) in Excel Sheet that was Created using Bcp Command in SQL SERVER 2008R2? 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved