stored procedure 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)

stored procedure help

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-19 : 15:11:32

I need a stored procedure that will update records based on data in an excel spreadsheet. The update is very basic,

Update member set primarymarketgroup = **column 1** where member_id = **column2**

where column 1 and 2 would be read from an excel file. Is this possible? Any help would be great.

Thanks
Travis

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-19 : 15:17:47

Sure it's possible, but is the excel file loaded into a table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-19 : 15:30:18

no. Here is the scenario. A co-worker will use business map to map our member base or particular geographic area. He then will export the member_ids and market names of the members into excel. He then will send it to me via email. I then will update the records. However, we map and remap quite often so he is always bombarding me with these request. He has full control and it completely responsible how these members are mapped so I would like to give him the power to do it. So the excel file would be saved either on his desktop or on the AD server.

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-20 : 11:10:02

so is this possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 11:20:14

quote:
Originally posted by blackX

so is this possible?

I think its possible using OPENDATASOURCE

somethink like:-

UPDATE t
SET t.Field=s.field,
....
FROM YourTable t
INNER JOIN (SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="filepathhere";User ID=youruserid;Password=;Extended properties=Excel 5.0')...yoursheetname)s
ON s.linkfield=t.Linkfield

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-20 : 11:40:58

Typically the excel sheets have 1000-3000 records in it, will that matter? I would also like the procedure to take the file name and sheet as a parameter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 11:46:44

quote:
Originally posted by blackX

Typically the excel sheets have 1000-3000 records in it, will that matter? I would also like the procedure to take the file name and sheet as a parameter

Nope. that wont be a problem.
For passinh filename and sheet as parameter you need to use dynamic sql and build OPENDATASOURCE string dynamically.

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-20 : 12:34:35

quote:
Originally posted by visakh16

quote:
Originally posted by blackX

Typically the excel sheets have 1000-3000 records in it, will that matter? I would also like the procedure to take the file name and sheet as a parameter

Nope. that wont be a problem.
For passinh filename and sheet as parameter you need to use dynamic sql and build OPENDATASOURCE string dynamically.

Thanks alot. Is there any way you can help me with the dynamic sql query. Also this procedure will be called from a remote machine.
Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 12:47:35

something like:-

CREATE PROC ExportExcelData
@FilePath varchar(100),
@SheetName varchar(20)
AS
DECLARE @Sql varchar(8000)

SET @Sql='UPDATE t
SET t.Field=s.field,
....
FROM YourTable t
INNER JOIN (SELECT * 
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
  ''Data Source="'+ @FilePath +'";User ID=youruserid;Password=;Extended properties=Excel 5.0'')...'+@SheetName+')s
ON s.linkfield=t.Linkfield'

EXEC(@Sql)

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-20 : 13:23:06

I used your code and it compiled fine but when I tried to test it I got this

[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.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.

The procedure ended up looking like this

CREATE PROC 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 *
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source=PMGSQL"'+ 'C:\Documents and Settings\theath\Desktop\Test.xls' +'";User ID=sa;Password=MyPwrd;Extended properties=Excel 5.0'')...'+'Test1'')s
ON s.member_id=t.member_id'
EXEC(@Sql)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 13:58:39

refer this. It discusses various ways of exporting from excel using OPENDATASOOURCE:-

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-20 : 14:22:57

quote:
Originally posted by visakh16

refer this. It discusses various ways of exporting from excel using OPENDATASOOURCE:-

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

I have tweak the query a little, but now I am getting this

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.].
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.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER 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 primarymarketgroup
FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+ @FilePath +'";Extended properties=Excel 5.0'')...'+@SheetName +')s
ON s.member_id=t.member_id'

exec (@Sql)

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-20 : 14:39:32

ok thanks for your help. After some checking I figured it out. I had to have excel as a linked server.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:52:35

quote:
Originally posted by blackX

ok thanks for your help. After some checking I figured it out. I had to have excel as a linked server.

Nope. You dont need server to be linked for using OPENDATASOURCE.

blackX
Posting Yak Master

102 Posts

Posted - 2008-06-20 : 15:12:53

well, i dont know then. I added it and it worked. Anyway thanks for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-20 : 19:11:55

You dont need a Linked Server if you use OPENROWSET
Refer what was used here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources