Connection issues in linked servers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Connection issues in linked servers?

Author

Topic

mack5511
Starting Member

3 Posts

Posted - 2007-01-25 : 15:23:59

To all,

I am trying to resolve an issue of linked servers. I am creating a linked server to an Excel file and have followed all the directions I can find from MS and other sources. I have used the following to create the linked server:

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'REPORTTABLEDATA_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = '\\mplsfs01\public\JimP\ReportData\ReportTableData.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog

The linked server shows up in enterprise manager. But when I try to query it with the following queries:

SELECT * FROM REPORTTABLEDATA_DMO...REPORTTABLEDATA$

SELECT * FROM OPENQUERY(REPORTTABLEDATA_DMO, 'SELECT * FROM [Source$]')

I keep getting the error:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'REPORTTABLEDATA_DMO' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Any input from someone familar with linked servers would be appreciated.

Thanks,

Jim

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-25 : 16:14:30

i know this isn't any help but...

why would u add a linked server to an excel file? why not just import the data into mssql?

that said, i tested adding spreadsheet as linked server and got a different error. may try to use the gui to add linked server in SSMS

mack5511
Starting Member

3 Posts

Posted - 2007-01-25 : 16:33:02

Russell: The reason I need to link to an Excel file stems from the project I am working on. I have a large amount of data in SQL db and need to return calculated fields for an Accounting report. The person I am working with is very familiar with Excel and has tables that need to be user edited. We do not want them to change SQL tables or utilize further resources having others update tables based on changing data ie. Currency rates So by setting up a link to the Excel data she uses and then using a query in MS Query in Excel I can give her the Pivot table reports needed with out having to maintain a ton of SQL tables. I am not a SQL expert by any means but am working through this the best I can. The irony of this is most of the data will go to SQL report services later this year. But the checks aren't bouncing so here I am.

Thanks for any help.

Jim

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-25 : 16:39:40

make sure service account has permissions on \\mplsfs01\public\JimP\ReportData\ReportTableData.xls

mack5511
Starting Member

3 Posts

Posted - 2007-01-25 : 17:56:02

Russell: It does have permissions for \\mplsfs01\public\JimP\ReportData\ReportTableData.xls

I wish it had been that simple.

Thanks,

Jim

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-25 : 19:00:24

Looks simple to me.

You named your linked server REPORTTABLEDATA_SP,
but you are trying to query linked server REPORTTABLEDATA_DMO

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources