f1 -- need help

declare @.execution_string varchar(1000),
@.email_id varchar(500),
@.filename varchar(200),
@.reportname varchar(100),
@.osql_query varchar(1000),
@.fieldname varchar(8000),
@.fieldnametemp varchar(100),
@.query varchar(8000),
@.seperator1 varchar(2),
@.seperator2 varchar(2)

select @.fieldname =''
select @.fieldnametemp = ''
exec sp_serveroption [myserver] , 'data access', 'true'
set nocount on
declare reports_scheduled cursor for
select 'exec mydb.dbo.'+a.parameter_string , b.report_name from report_schedule a , report_master b
where a.Active='Y'
AND a.frequency like '%-'+SUBSTRING(datename(weekday,getDate()),0,3)+'-%' and a.report_no = b.report_no

open reports_scheduled
fetch next from reports_scheduled into @.execution_string,@.reportname
while @.@.fetch_status = 0
begin

declare @.l_str nvarchar(555)
select @.l_str ="CREATE procedure temp_proc as select top 100 * into temp_table from openquery ([myserver], """ + @.execution_string + """)"
exec (@.l_str)
exec temp_proc
select @.filename = 'c:\' + @.reportname+ convert(varchar(10),getdate(),112)+'.csv'
select @.filename= rtrim(ltrim(replace(@.filename,' ','')))
declare fnames cursor for
select name from syscolumns where id in (select id from sysobjects where name = 'temp_table')
open fnames
fetch next from fnames into @.fieldnametemp
while @.@.fetch_status = 0
select @.seperator1 = '"'
select @.seperator2 = '" '
begin
select @.fieldname = '''"'''+'+convert(varchar,replace(['+@.fieldnametemp + '],'+@.seperator1+','+@.seperator2+'))+'+ '''"'''+ ','+ @.fieldname
fetch next from fnames into @.fieldnametemp
end
close fnames
deallocate fnames
select @.fieldname = left(@.fieldname,len(@.fieldname)-1)
select @.fieldname
select @.query = 'echo select '+@.fieldname+ ' from temp_table > c:\temp_query.txt'
select @.query
exec master..xp_cmdshell @.query
select @.osql_query = 'osql -E -d mydb -i c:\temp_query.txt -s , -n -w 10000 -o '+ @.filename + ' -c'
select @.osql_query
exec master..xp_cmdshell @.osql_query

exec master..xp_sendmail @.recipients = '123@.123.com
,@.message ='This is a system generated mail. Please do not reply to this mail'
,@.attachments = @.filename
,@.subject = @.reportname

drop table temp_table
drop procedure temp_proc
fetch next from reports_scheduled into @.execution_string ,@.reportname
end
close reports_scheduled
deallocate reports_scheduled

The reults from any sp are taken and send to a given mail address by mail in excel format

This procedure gives me a error when i try to run it

Server: Msg 7391, Level 16, State 1, Procedure temp_proc, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

DTC is up and running and no problems in that.

Any idea what might be causing this error .

It was running earlier and changes I made in the server were enabling replicationselect @.l_str ="CREATE procedure temp_proc as select top 100 * into temp_table from openquery ([myserver], """ + @.execution_string + """)"
exec (@.l_str)
exec temp_proc
exec temp_proc

What do you expect this to do?
Besides being bad practice to create SPs like this - why have you made it an SP when it looks like it could be dynamic sql.
execing it twice will give an error as will running your procedure again if it fails after the creation and before the drop.

Tables and SPs are meant to be permaneny objects and not created/destroyed dynamically.

If you need to do something like this I would recommend a global temp table - and as your structure does not change create it once outside the loop (using and 1=0 appended to the where clause) and truncate it before populating with an insert inside the loop.

I'm guessing your error is due to the provider for myserver whatever that is. If it's a self linked server check how it's set up.|||Tables and SPs are meant to be permaneny objects and not created/destroyed dynamically.

If you need to do something like this I would recommend a global temp table - and as your structure does not change create it once outside the loop (using and 1=0 appended to the where clause) and truncate it before populating with an insert inside the loop.

Thats the whole point ... I do not know the structure of the temp table ... its the resultset returned by the stored procedure executed in the openquery part.|||while @.@.fetch_status = 0
select @.seperator1 = '"'

select @.seperator2 = '" '
begin
select @.fieldname = '''"'''+'+convert(varchar,replace(['+@.fieldnametemp + '],'+@.seperator1+','+@.seperator2+'))+'+ '''"'''+ ','+ @.fieldname
fetch next from fnames into @.fieldnametemp
end

found the error