Need Help - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Need Help

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-21 : 10:10:16

About a month ago I got help on here writing a procedure that would update a database table based on data in an excel file. And it has worked since day one. I came to work today and tried to use it and I got this error

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

Nothing has changed so I am really confused what the problem it. Here is the query

Create PROC [dbo].[PMGLOAD]
@FilePath varchar(100),
@SheetName varchar(20)
AS
DECLARE @Sql varchar(8000)

SET @Sql='UPDATE t
SET t.primarymarketgroup=s.primarymarketgroup
FROM member t
INNER JOIN (SELECT member_id, primarymarketgroup
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+ @FilePath +'";Extended properties=Excel 8.0'')...' +@SheetName +')s
ON s.member_id=t.member_id'

exec(@Sql)

With the parameters filled out

UPDATE t
SET t.primarymarketgroup=s.primarymarketgroup
FROM member t
INNER JOIN (SELECT member_id, primarymarketgroup
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Redwood\Users\Public Folders\MIS\Test.xls";Extended properties=Excel 8.0')...Test2$)s
ON s.member_id=t.member_id

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 10:20:14

Did you recently change your excel version?

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-21 : 10:22:43

quote:
Originally posted by visakh16

Did you recently change your excel version?

no, only pc update was sp3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 10:24:09

Make sure the file is closed or not used by other applications when you run the query
More details
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-21 : 10:25:23

quote:
Originally posted by madhivanan

Make sure the file is closed or not used by other applications when you run the query
More details
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

i am the only one with access to the file and I verified that is was closed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 10:38:16

Go thru the link and read all pages. You may find the answer

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-21 : 11:38:34

quote:
Originally posted by madhivanan

Go thru the link and read all pages. You may find the answer

Madhivanan

Failing to plan is Planning to fail

I am not seeing anything

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-21 : 11:53:14

I moved the file to another folder and it works, but the permissions on the inital wern't changed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources