Prev: i just want table order to create tables
Next: query help
From: Martin Arvidsson, Visual Systems AB on 1 Jul 2008 06:35
Hi!

I have created a Linked Server and a stored procedure.

In the stored procedure i create a temporary table
i fill the temporary table with data from the linked server.

use the temp table to issue a couple of things

if i execute this sproc directly on the server it works just fine.
But if i open SQL Management Studio localaly and executes the stored
procedure i get an error
from the Linked Server. i do have full access to the hqfs1 server as i have
administrator priviliges on my account.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FRODINGE"
returned message "'\\hqfs1\CustSystem\Frodinge\BravadR9\Prislistor' is not a
valid path. Make sure that the path name is spelled correctly and that you
are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Procedure spSoderBergHaak, Line 23
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "FRODINGE".

the sql server is on one computer and the data is in another (hqfs1)

Schema files for the textfile is in the same location and is correct

so my linked server config is like this. (Yes, rights are applied to the
linked server)

EXEC master.dbo.sp_addlinkedserver @server = N'FRODINGE',
@srvproduct=N'Text', @provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=N'\\hqfs1\CustSystem\Frodinge\BravadR9\Prislistor'

My stored procedure looks like this...

USE [FM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spSoderBergHaak]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

/*Create temporary table*/
CREATE TABLE [dbo].[#SoderBergHaak](
[Artikel] [nchar](19) NULL,
[Benamning] [nchar](30) NULL,
[Rabattkod] [nchar](6) NULL,
[Pris] [numeric](9, 2) NULL,
[Alternativ_Artikel] [nchar](20) NULL
) ON [PRIMARY];

INSERT INTO [#SoderBergHaak]
SELECT * FROM [FRODINGE]...[prisp#txt];

SELECT * FROM [#SoderBergHaak];

DROP TABLE [#SoderBergHaak];
END

Any hints, would be greatly appreciated.

Regards
Martin


 | 
Pages: 1
Prev: i just want table order to create tables
Next: query help