stored procedure

• billy24

  Old Hand

  Points: 301

  More actions

  February 5, 2011 at 12:27 pm

  #231046

  Hello,

  I am running following below stored procedure, I am using MSSQL 2008 64 bit, below stored procedure inserts resultset into excel file and email it to the receipient in as an attachment.

  But I am receiving error as "Msg 7308, Level 16, State 1, Line 35

  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode."

  can anybody help me on this please !!

  BEGIN

  SET NOCOUNT ON;

  DECLARE @rc int

  DECLARE @RA int

  DECLARE @server nvarchar(128)

  DECLARE @srvproduct nvarchar(128)

  DECLARE @provider nvarchar(128)

  DECLARE @datasrc nvarchar(4000)

  DECLARE @location nvarchar(4000)

  DECLARE @provstr nvarchar(4000)

  DECLARE @catalog nvarchar(128)

  DECLARE @optname varchar(35)

  DECLARE @optvalue nvarchar(128)

  --sp_serveroption [@server = ] 'server',[@optname = ] 'option_name',[@optvalue = ] 'option_value';

  SET @server = 'xxx'

  SET @srvproduct = 'Excel'

  SET @provider = 'Microsoft.Jet.OLEDB.4.0'

  SET @datasrc = 'd:\xxxx\backend\ExcelFormat\feed.xls'

  SET @provstr = 'Excel 8.0'

  EXEC @rc = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

  @datasrc, @location, @provstr, @catalog

  SET @server = 'xxx'

  SET @optname = 'data access'

  SET @optvalue = 'ON'

  EXEC @RA = [master].[dbo].[sp_serveroption] @server, @optname, @optvalue

  INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=d:\xxxx\backend\ExcelFormat\feed.xls;HDR=YES;IMEX=1',

  'SELECT * FROM [Sheet1$]')

  select categoryname, productname, colorcode, brandname from dbo.productstb order by categoryname asc

  DECLARE @body VARCHAR(1024)

  SET @body = 'dosyalar '+

  CONVERT(VARCHAR, GETDATE())

  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxx',

  @recipients='xxxx.com',

  @body = 'dosyalar' ,

  @subject = 'dosyalar',

  @file_attachments = 'd:\xxxx\backend\ExcelFormat\feed.xls'

  END

• Johan Bijnens

  SSC Guru

  Points: 135368

  More actions

  February 5, 2011 at 1:26 pm

  #1282814

  Are there any special formats in your xls ?

  Why don't you ship a csv file directly from dbmail.

  e.g.

  declare @subject varchar(1000)

  set @subject = @@servername + ': Count of master sysfiles'

  EXEC msdb.dbo.sp_send_dbmail

  -- @profile_name = 'yourmailprofile',

  @recipients = 'yourreceiver@yourcomp.com',

  @execute_query_database='master'

  @query = 'SELECT * FROM sys.sysfiles ',

  @subject = @subject,

  @body='check the data',

  @query_result_width = 8000,

  @query_result_separator = ' ',

  @attach_query_result_as_file = 1 ,

  @query_attachment_filename='deQueryResultFileName.csv',

  @append_query_error = 1 ;

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• billy24

  Old Hand

  Points: 301

  More actions

  February 5, 2011 at 5:53 pm

  #1282832

  because in .csv file the columns containing values such as '0008' takes as '8' so i want to avoid using .csv format.