How to pass parameters for OPENDATASOURCE

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)

How to pass parameters for OPENDATASOURCE

Author

Topic

kd12345
Starting Member

12 Posts

Posted - 2008-01-04 : 15:46:59

WE are trying to find out if the worsheet exist in excel using stored procedure. if it exist go ahead and do the insert on excel worksheet else create the worksheet.
Following is stored procedure is written to do this. It throws an error if the worksheets is not there...

CREATE PROCEDURE XXXX
AS
DECLARE cursor1 CURSOR FOR
SELECT distinct OPRID FROM TEMP_TABLE

OPEN cursor1

declare @OPRID1 varchar(18)

FETCH NEXT FROM cursor1 INTO @OPRID1;

WHILE (@@FETCH_STATUS <> -1)
BEGIN

if exist (select 1 from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\Book3.xls";Extended properties=Excel 8.0')...OPRID1$))

begin

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Book3.xls";Extended properties=Excel 8.0')...@OPRID1
(DEPARTMENT, EMPLOYEE_POSITION, EMPLOYEE_NAME, CASE_ID, CREATION_DATE_TIME, CUSTOMER_NAME, ADDRESS, ZIP_CODE, PHONE, CASE_TYPE, DESCRIPTION)
SELECT DEPARTMENT, EMPLOYEE_POSITION, EMPLOYEE_NAME, CASE_ID, CREATION_DATE_TIME, CUSTOMER_NAME, ADDRESS, ZIP_CODE, PHONE, CASE_TYPE, [DESCRIPTION]
from TEMP_TABLE WHERE OPRID = @OPRID1
end

else

begin
create table OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Book3.xls";Extended properties=Excel 8.0')...@OPRID1
(DEPARTMENT nchar(20) null,
EMPLOYEE_POSITION varchar (50) null,
EMPLOYEE_NAME varchar(120) null,
CASE_ID decimal(9) null,
CREATION_DATE_TIME datetime null,
CUSTOMER_NAME varchar (30) null,
ADDRESS varchar(50) null,
ZIP_CODE varchar(20) null,
PHONE varchar(15) null,
CASE_TYPE nchar(40) null)
end
end
FETCH NEXT FROM cursor1 INTO @OPRID1;

CLOSE cursor1
DEALLOCATE cursor1

We are using SQL Server 2000 and trying to export the data to excel.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-04 : 15:49:23

Not sure why you chose to post your question in the Script Library, but this does not qualify as a working script. I'm moving your thread to a 2000 forum.

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

kd12345
Starting Member

12 Posts

Posted - 2008-01-04 : 15:51:53

Thanks. I'm a new user and didnt realise where i created the question.

Thanks again!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-07 : 01:11:08

Somewhere here you can find it 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