Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how open a excel from stored procedure Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 1:10 PM
Points: 1, Visits: 1
Hi all

i need open a excel file using store procedure, someone know how do it?

claudia
Post #1523848
Posted Tuesday, December 17, 2013 12:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
you posted in SQL2000, so be aware that if you meant 2008 or above, the ACE drivers would b eused instead.

typically, you will create a linked server, and reference theworksheet(s) via a four part naming convention inside the stored proc or other code.

--#################################################################################################
--Linked server Syntax for Excel
--#################################################################################################
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)

--add an excel spreadsheet as a linked server.
SET @server = 'HENNXLS'
SET @srvproduct = 'Jet 4.0'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
set @provstr = 'Excel 5.0;'
SET @datasrc ='C:\Clients\Hennepin\Scripts\Activity Types Client FINAL APPROVAL.xls'
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL
--you MUST know the name of the spreadsheet;
--spreadsheet name has a dollar sign at the end of it!
--I've personally never got a spreadsheet that has a space in it
--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work
--to work, so I end up editing the spreadsheet to remove spaces if that happens.
IF OBJECT_ID('dbo.HENN', 'SN') IS NOT NULL
DROP SYNONYM dbo.HENN;
CREATE SYNONYM dbo.HENN FOR HENNXLS...ActivityData$;
--list all the spreadsheets and their names
EXEC sp_tables_ex 'HENNXLS'
/*
cleanly access data taking advantage of synonyms in 2005
select * from HENNXLS...ActivityData$;
select * from HENN
*/
GO



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1523855
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse