how open a excel from stored procedure

• anonimato36

  SSC Enthusiast

  Points: 171

  More actions

  December 17, 2013 at 12:31 pm

  #283606

  Hi all

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

  claudia

• Lowell

  SSC Guru

  Points: 323522

  More actions

  December 17, 2013 at 12:40 pm

  #1674208

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!