Professional Web Applications Themes

Is it true?? - Microsoft SQL / MS SQL Server

His guys, Is it true that I cannot use sp_executesql from inside table data type udf. My udf looks like: CREATE FUNCTION [dbo].[MixLiqtest] ( cPlant char(3), dtStartdatetime datetime, dtEndDatetime datetime, Res varchar(20) ) RETURNS tblMixLiq table ([datetime] smalldatetime, value real) AS BEGIN declare nvcSQL nvarchar(4000), tblName varchar(50) Select tblName = case Res When 'Hourly' Then 'fn_PIGetHourlyDataForPlant' When 'Daily' Then 'fn_PIGetDailyDataForPlant' End set nvcSQL = N'Insert tblMixLiq select at09.datetime,isnull(at09.Value,0)+isnull(at10.val ue,0) as [Total] from ' + tblName + '(cPlant,dtStartdatetime,dtEnddatetime,''MetNES ecAT09' ') as AT09, ' + tblname + '(cPlant,dtStartdatetime,dtEnddatetime,''MetNES ecAt10' ') as AT10 ' Exec sp_executesql nvcSQL, N'cPlant char (3),dtStartDatetime datetime, dtEnddatetime datetime',cPlant,dtStartdatetime,dtEnddatetime ...

  1. #1

    Default Is it true??

    His guys,

    Is it true that I cannot use sp_executesql from inside
    table data type udf.

    My udf looks like:

    CREATE FUNCTION [dbo].[MixLiqtest] ( cPlant char(3),
    dtStartdatetime datetime, dtEndDatetime datetime, Res
    varchar(20) )
    RETURNS tblMixLiq table ([datetime] smalldatetime, value
    real) AS

    BEGIN
    declare nvcSQL nvarchar(4000), tblName varchar(50)
    Select tblName = case Res When 'Hourly'
    Then 'fn_PIGetHourlyDataForPlant' When 'Daily'
    Then 'fn_PIGetDailyDataForPlant' End

    set nvcSQL = N'Insert tblMixLiq select
    at09.datetime,isnull(at09.Value,0)+isnull(at10.val ue,0) as
    [Total] from ' + tblName
    + '(cPlant,dtStartdatetime,dtEnddatetime,''MetNES ecAT09'
    ') as AT09, ' + tblname
    + '(cPlant,dtStartdatetime,dtEnddatetime,''MetNES ecAt10'
    ') as AT10 '
    Exec sp_executesql nvcSQL, N'cPlant char
    (3),dtStartDatetime datetime, dtEnddatetime
    datetime',cPlant,dtStartdatetime,dtEnddatetime
    Return
    END

    I get the error:
    Server: Msg 557, Level 16, State 2, Procedure MixLiqtest,
    Line 9
    Only functions and extended stored procedures can be
    executed from within a function.

    I cudn't find any doentation that says I cannot use
    dynamic sql using sp_excutesql from within udf...

    Thanks
    Ricky Guest

  2. #2

    Default Re: Is it true??

    Here is an excerpt from bol (create function):

    Calling extended stored procedures from functions
    The extended stored procedure, when called from inside a function, cannot
    return result sets to the client. Any ODS APIs that return result sets to
    the client will return FAIL.


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Ricky" <state.mn.us> wrote in message
    news:023401c35df1$247402b0$gbl... 


    oj Guest

  3. #3

    Default Re: Is it true??

    Did you think that perhaps this convoluted code is the result of a bad
    design? Without specs all we can do is guess. Cleaning up the silly
    prefixes that violate ISO-11179 data element naming rules, get rid of
    the NVARCHAR(n) so we don’t have Chinese in the parameters and database,
    see that FLOAT datatype is used in the COALESCE() functions that feed a
    FLOAT result, stop using reserved words for names, etc., etc. it would
    look like PigetHourlyDataForPlant is somehow related to
    PigetDailyDataForPlant by a different unit of time determined by a
    parameter res (resolution?). So, why are you using two different
    functions instead of some math – it would be insane to keep tables of
    the same facts in multiple units of measure. There would be one table
    and VIEWs to convert to the courser units, or you will have no data
    integrity.

    So, what is the LOGICAL difference in the two? And if they are TOTALLY
    different entities, why are they going into the SAME function? I have
    to be misunderstanding those names, or this makes no sense at all.

    Returning a table is highly proprietary and procedural when you could
    probably write a VIEW, or a simple stored procedure. Never pass a table
    name as a parameter.

    1) This is dangerous because some user can insert pretty much whatever
    they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
    FROM Floob' in your statement string.

    2) It says that you have no idea what you are doing, so you are giving
    control of the application to any user, present or future. Remember the
    basics of Software Engineering? Modules need weak coupling and strong
    cohesion, etc.

    3) If you have tables with the same structure which represent the same
    kind of entities, then your schema is not orthogonal. Look up what
    Chris Date has to say about this design flaw; I call it attribute
    splitting – you take the values of an attribute and make each one into
    a table.

    Making a stab at this, I would guess that the summary query would be
    something like this:

    SELECT AT09.plant, start_time , end_time, res,
    SUM (COALESCE(AT09.something_value, 0.00) +
    COALESCE(AT10.something_value, 0.00)) AS total,
    <<res expression >>
    FROM some_kind_of_history AS AT09,
    some_kind_of_history AS AT10
    WHERE <<AT09 predicate>>
    AND <<AT10 predicate>>
    AND << join predicate >>
    AND <<start_time , end_time, res predicates>>
    GROUP BY AT09.plant;

    I assume that AT09 and AT10 are meaningful names in your industry.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: Is it true??

    Thanks for poining out all the flaws and potential fixes.
    Here is how our Mr. dba designed it. I work in waste water
    treatment industry. We collect data from field every 10
    seconds and we use industrial SQL server from wonderware
    as our primary data warehouse. From this industrail sql
    server the 10 second resolution data is averaged to
    minutely,hourly and daily and moved to microsoft sql
    server. Our data is in terms of tagnames,datetime,value
    and quality. Each tagname represents a single instrument
    in the field eg. flow meter, venturi meter etc. that
    collects data at PLC level.

    The main table is called the tag table. It has about
    50,000 tags. The structure is

    Tagkey,tagname,description,units,Plant,...etc. etc....

    Since it will be highly inefficient to store the data at
    10 second resolution and use aggregate queries every
    single time user requests a minutely resolution or hourly
    resolution or daily resolution for any of the tags, we at
    every hour aggregate these values previous hour's values
    and store in 3 separate tables called tagdataminutely,
    tagdatahourly and tagdatadaily. The structure of these are
    identical except the data are stored at different
    resolutions. The structure for these table is

    tagkey (from tag table), datetime,value,quality

    so in all these tables there are thousands and thousands
    of different tags with values at different resolutions.
    This is primarily chosen so that retrieval times are
    minimal and data could be easily used for reporting and
    SPC purposes.

    so when I referet to at09 and at10 those meant aeration
    tank 09 and aeration tank 10 for east primary influent
    flow calculation.

    tag table has entry for these two entities as
    12,'MetAT09', 'Aeration Tank 09 Ras Flow',.....
    13, 'MetAT10', 'Aeration Tank 10 Ras flow',.....


    In tagdatahourly the data for a specific datetime is as
    follows for a 24 hour period:

    12,'2003-08-01 00:00:00',23.2,192
    12,'2003-08-01 01:00:00' 24.23,192
    12,'2003-08-01 02:00:00',21.34,192
    ....
    ..... for 24 hours and same for tag 13 and all other 50,000
    tags...

    In tagdatadaily table the same tag has only one value i.e.
    one value per day which is the daily average.

    12,'2003-08-01 00:00:00',23.45,192
    12,'2003-08-02 00:00:00', 24.34,192
    ....
    ....
    13,'2003-08-01 00:00:00',456.78,192
    13,'2003-08-02 00:00:00', 458.92,192
    .....
    .....

    now I defined to two functions separately one called
    PI_GetHourlyDataForPlant, where PI just to denote that its
    a generic process information function that could be used
    to query any tag data for hourly resolution for any plant.
    Similarly, PI_GetDailyDataForPlant. These two udfs accept
    Plant,startdate,enddate and tagkey as parameters and get
    the data from respective tables.

    Now I have to do tons of calculations and I chose the
    simplest one where I have to add two aeration tanks 09 and
    10 to get total flow and I want an hourly number and daily
    number thats where I defined the function I mentioned to
    get the appropriate resolution based on res parameter.

    I am not sure if this gives you any idea of the beast I am
    dealing with. Feel free to ask any more Q's and
    suggestions in designing such calculations...

    Thanks Joe



     
    result of a bad 
    up the silly 
    rules, get rid of 
    parameters and database, 
    functions that feed a 
    etc. it would 
    determined by a 
    different 
    keep tables of 
    be one table 
    have no data 
    they are TOTALLY 
    function? I have 
    sense at all. 
    when you could 
    Never pass a table 
    much whatever 
    Foobar; SELECT * 
    you are giving 
    future. Remember the 
    coupling and strong 
    represent the same 
    Look up what 
    attribute 
    each one into 
    query would be 
    industry. 
    what the keys, 
    datatypes, etc. in your 
    *** 
    Ricky Guest

  5. #5

    Default Re: Is it true??

    Ricky,

    If the data is moved to SQL Server at somewhat reduced intervals (several
    times a day vs. every 10 seconds) you might consider using indexed views to
    pre-aggregate the values. Then your queries will be lightning fast.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Ricky" <state.mn.us> wrote in message
    news:06c201c35e37$b2a7a7d0$gbl... 
    > result of a bad 
    > up the silly 
    > rules, get rid of 
    > parameters and database, 
    > functions that feed a 
    > etc. it would 
    > determined by a 
    > different 
    > keep tables of 
    > be one table 
    > have no data 
    > they are TOTALLY 
    > function? I have 
    > sense at all. 
    > when you could 
    > Never pass a table 
    > much whatever 
    > Foobar; SELECT * 
    > you are giving 
    > future. Remember the 
    > coupling and strong 
    > represent the same 
    > Look up what 
    > attribute 
    > each one into 
    > query would be 
    > industry. 
    > what the keys, 
    > datatypes, etc. in your 
    > *** [/ref]


    Andrew Guest

  6. #6

    Default Re: Is it true??

    Ricky,

    Another way to pre-aggregate your values is to use
    in-table summaries. This essentially puts your minutely,
    hourly, and daily tables together. Typically you then add
    a column to represent the granularity of aggregation, and
    put that as the first column of the table's primary key. For
    a warehouse (inserts, but no updates), this should be very
    fast. You could also define your existing summary tables
    as views against the in-table summarized table, so migrating
    code would be smooth.

    Here's an example with sample data gathered from Northwind.

    select
    CustomerID as Station,
    OrderDate + (EmployeeID)/24. as ReadingTime,
    Freight as Reading, 'D' as AggPeriod
    into #Readings
    from Northwind..Orders

    declare basis datetime
    set basis = '19000101'

    insert into #Readings
    select Station,
    dateadd(month,datediff(month,basis,ReadingTime), basis), sum(Reading) , 'M'
    from #Readings
    where AggPeriod = 'D'
    group by Station, dateadd(month,datediff(month,basis,ReadingTime), basis)
    -- A row for every month could be added, if summary rows are needed
    -- whether or not there is data

    insert into #Readings
    select Station,
    dateadd(year,datediff(year,basis,ReadingTime),ba sis), sum(Reading), 'Y'
    from #Readings
    where AggPeriod = 'D'
    group by Station, dateadd(year,datediff(year,basis,ReadingTime),ba sis)
    go
    create unique clustered index #Readings_ci on #Readings(AggPeriod,
    Station, ReadingTime)
    go

    -- Show Monthly summaries for one station:
    select Station, ReadingTime as MonthBeginning, Reading
    from #Readings
    where AggPeriod = 'M'
    and Station = 'BERGS'
    order by ReadingTime

    -- Show all data
    select
    Station,
    case AggPeriod
    when 'D' then ' SingleReading:'
    when 'M' then ' MonthBeginning'
    when 'Y' then 'YearBeginning'
    end,
    ReadingTime as [Time],
    Reading
    from #Readings
    order by
    Station,
    ReadingTime,
    case AggPeriod when 'Y' then 1 when 'M' then 2 when 'D' then 3 end


    go
    drop table #Readings


    -- Steve Kass
    -- Drew University
    -- Ref: 013669EB-1BD2-41B2-BDED-297C9F669774

    Ricky wrote:
     
    >result of a bad
    >

    >up the silly
    >

    >rules, get rid of
    >

    >parameters and database,
    >

    >functions that feed a
    >

    >etc. it would
    >

    >determined by a
    >

    >different
    >

    >keep tables of
    >

    >be one table
    >

    >have no data
    >

    >they are TOTALLY
    >

    >function? I have
    >

    >sense at all.
    >

    >when you could
    >

    >Never pass a table
    >

    >much whatever
    >

    >Foobar; SELECT * 
    >

    >you are giving
    >

    >future. Remember the
    >

    >coupling and strong
    >

    >represent the same
    >

    >Look up what
    >

    >attribute
    >

    >each one into
    >

    >query would be
    >

    >industry.
    >

    >what the keys,
    >

    >datatypes, etc. in your
    >

    >***
    >
    > [/ref]

    Steve Guest

Similar Threads

  1. Replies: 0
    Last Post: November 14th, 03:22 PM
  2. Replies: 0
    Last Post: November 14th, 07:45 AM
  3. Replies: 0
    Last Post: November 14th, 12:36 AM
  4. Replies: 0
    Last Post: November 13th, 03:58 PM
  5. Replies: 0
    Last Post: November 13th, 08:29 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