Need an elegant solution around SQL Server Limitations

From: Stephen Brown (nospam_at_telusplanet.net)
Date: 11/17/04

  • Next message: Michael C: "Re: RADiest Client for SQL Server" 
    Date: Wed, 17 Nov 2004 14:21:43 -0700

    I have a seemingly simple requirement that turned into a big kludge because
    of limitations in SQL Server and it now turns out that the kludge has a
    unrecoverable deadlock problem (which should never happen in SQL Server, but
    it is reproducable every time).

    The requirement I have is to have a stored procedure accept some filtering
    parameters and return a flat dataview of several tables. My system has a
    table for custom fields the user wants to tack on to data and it returns
    these fields with the dataview. In a nutshell, I need users to be able to
    run a report without having to worry about inner joins and table
    definitions.

    There are 2 things that make this a little trickier. The first thing is
    that the filtering parameters could be huge. One of the simplest filters
    allows filtering on the user by userid, jobid, department. There are over
    100,000 users in the database, so the 8000 character limit quickly becomes a
    major problem if the user wants to go to town on filtering. The second
    trickiness is that my app allows "custom fields" to be added to any table
    and I want these to be returned with column names matching the user defined
    name of the field. Custom fields are stored in 2 tables, one containing the
    id, definition, and what table the custom field extends and the second table
    contains the id of the custom field, the id from the table it is extending,
    and the data the user wants in it.

    A very scaled down example for clarity:

    UserTable
    UserID, UserName
    1, Bob

    CustomField
    FieldID, Table, FieldName
    2, User, Eyecolor

    CustomFieldLU
    FieldID, TableID, Value
    2, 1, Blue

    The report stored procedure should then return:
    UserID, UserName, Eyecolor
    1, Bob, Blue

    Currently, I am doing this by creating a temp table for the custom fields,
    then walking a cursor of the custom fields and using dynamic sql to add a
    column with the custom field name to the temp table. Dynamic sql is then
    run to populate the column with the proper data, the temp table is joined to
    the user table and the data is returned.

    Every time I try to find a more elegant method to do this, I encounter a
    limitation in SQL Server. Ideally, I should be able to just create a
    function that would create and return the custom field data but functions
    don't allow dynamic sql or altering tables. I could create a single huge
    dynamic sql statement with inner selects to get the custom field data column
    by column, but the 8000 character limit on strings kills this idea since
    each filter could possibly contain 8000 chars. Even without the filter, the
    main select statements typically contain between 1000 and 3000 chars and if
    each custom field added column added 50 to 100 chars to the sql command I
    would have to have a maximum of 50 custom fields. The fact I am using a
    temp table has now become a huge issue. If the same stored procedure is
    used twice in the same Crystal Report, SQL Server gets a deadlocked
    condition on the temp table and is completely unrecoverable without killing
    the process (we discovered a deadlock on one server 6 days after the
    condition occurred - the moment we killed the process we saw the time
    started on the query in Profiler).

    Are there any gurus that have any recommendations on this? If I could
    resolve the deadlock temp tables issue, at least it works but it still is a
    huge kludge where I have to copy and paste a section of code multiple times
    into every report procedure. It would be better if I could somehow run a
    query > 8000 characters or somehow return the table from a function or
    procedure. Is there another method that I've missed?

  • Next message: Michael C: "Re: RADiest Client for SQL Server"

    Relevant Pages