Results 1 to 4 of 4

Thread: f1 -- need help

  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: 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 replication
    Last edited by Enigma; 08-28-03 at 02:09.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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
    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.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •