Professional Web Applications Themes

how to get a value from an EXEC (SQL) statement - Microsoft SQL / MS SQL Server

I need to include a computed column in the result set of a query. Because I am using a linked-table to get the computed value, I must create a dynamic OPENQUERY statement. I succeeded in doing that, but now I am unable set a variable equal to that value. Here is the query: declare StartDate varchar(20) DECLARE rSQL varchar(300) DECLARE SQL varchar(300) declare actlogdate varchar(20) Declare ActivityLogDate datetime declare StraightTimeHoursMTD float set ActivityLogDate = '8/8/2003' set StartDate = CAST(DATEPART(YYYY, ActivityLogDate) as char(4)) + '-' + RIGHT(CAST(100+Datepart(mm,ActivityLogDate) as char(3)),2) + '-' + RIGHT(cast(100+1 as char(3)),2) set actlogdate = CAST(DATEPART(YYYY, ActivityLogDate) as ...

  1. #1

    Default how to get a value from an EXEC (SQL) statement

    I need to include a computed column in the result set of a query.
    Because I am using a linked-table to get the computed value, I must
    create a dynamic OPENQUERY statement. I succeeded in doing that, but
    now I am unable set a variable equal to that value. Here is the query:

    declare StartDate varchar(20)
    DECLARE rSQL varchar(300)
    DECLARE SQL varchar(300)
    declare actlogdate varchar(20)
    Declare ActivityLogDate datetime
    declare StraightTimeHoursMTD float
    set ActivityLogDate = '8/8/2003'

    set StartDate = CAST(DATEPART(YYYY, ActivityLogDate) as char(4)) +
    '-'
    + RIGHT(CAST(100+Datepart(mm,ActivityLogDate) as char(3)),2) + '-'
    + RIGHT(cast(100+1 as char(3)),2)

    set actlogdate = CAST(DATEPART(YYYY, ActivityLogDate) as char(4)) +
    '-'
    + RIGHT(CAST(100+Datepart(mm,ActivityLogDate) as char(3)),2) + '-'
    + RIGHT(cast(100+datepart(dd,ActivityLogDate) as char(3)),2)

    SELECT rSQL = 'SELECT SUM(TECH_ST_PAID) FROM
    FLTW_ADMIN_FLT_METRIC_DAILY WHERE LOB_ID = 1 AND
    cast(right(FAC_IDU,5) as integer) = ' + cast(4109 as varchar) + ' AND
    EFFECTIVE_DT Between ''''' + cast(StartDate as varchar) + ''''' and
    ''''' + cast(actlogdate as varchar) + ''''''

    SELECT SQL = 'SELECT * FROM OPENQUERY(CorpDB, ''' + rSQL + ''')'

    --SELECT SQL

    EXEC (SQL)

    INSERT INTO #t EXEC (SQL)

    'GENERATES THIS ERROR
    Server: Msg 8501, Level 16, State 3, Line 1
    MSDTC on server 'ALICE' is unavailable.

    Is there some other way to get the return value?
    maxhodges Guest

  2. #2

    Default Re: how to get a value from an EXEC (SQL) statement

    I turned on the DTC service so I got it in the temp table, but what I
    really want is to stuff it in a variable because I need to do more
    with it. Do I need to use a cursor to retrieve it form the temp table
    now, or is there an easier way?

    Thanks!

    com (maxhodges) wrote in message news:<google.com>... 
    maxhodges Guest

  3. #3

    Default Re: how to get a value from an EXEC (SQL) statement

    If you have only a single row in the table, you don't
    need a cursor:

    declare v float -- or appropriate type
    set v = (
    select the_name_of_the_column_of_#t_goes_here
    from #t
    )

    Ultimately, though, sp_executesql may be more useful.

    -- Steve Kass
    -- Drew University
    -- Ref: AEEB377D-5AAF-42CA-918A-465419E5A874


    "maxhodges" <com> wrote in message
    news:google.com... 
    news:<google.com>... [/ref]


    Steve Guest

Similar Threads

  1. exec()
    By manu in forum PHP Development
    Replies: 6
    Last Post: July 13th, 09:53 PM
  2. Convert Exec (SQL) to Exec sp_executesql 'SQL'
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 9th, 10:20 PM
  3. Syntax problem in exec statement
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 6th, 10:30 AM
  4. Replies: 0
    Last Post: December 10th, 09:27 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139