Need an elegant solution around SQL Server Limitations

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?Stephen,
I don't have any good suggestions for what to do with these "custom
fields", but I'll mention that the 8000 character limitation is only for
varchar data. Perhaps you can use text or ntext instead. You won't be
able to concatenate with +, and the only string functions that work
reliably for text and ntext are SUBSTRING (which returns a varchar but
accepts text/ntext) and PATINDEX. Other string functions, like REPLACE,
CHARINDEX, and so on, will implicitly convert the text data to varchar.
Text and ntext can be used as function and procedure parameter types,
and literal text and ntext values are specified just as varchar and
nvarchar values, delimited with ' characters, and with an initial N' in
the case of ntext. Local variables of type text and ntext are not an
option.
Steve Kass
Drew University
Stephen Brown wrote:
>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?
>
>
>
>|||Hi
If your reports vary so much, why don't you build a data warehouse from
which to report on. Some users might find OLAP useful too.
De-normalizing data usually makes it lot easier. Reporting against an OLTP
design is just very difficult.
Crystal Decisions and other tools in that area should be considered, why do
your own report engine when the tools are out there already?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stephen Brown" <nospam@.telusplanet.net> wrote in message
news:cngfci$jij$1@.utornnr1pp.grouptelecom.net...
> 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?
>
>
>|||I am using Crystal Decisions, but I am using the stored procedure to
simplify the reporting. Crystal Reports was struggling to cope with all the
joins and links required for some of the larger queries and there was no
possible way it can interpret the custom field data.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23mpWvqPzEHA.2656@.TK2MSFTNGP14.phx.gbl...
> Hi
> If your reports vary so much, why don't you build a data warehouse from
> which to report on. Some users might find OLAP useful too.
> De-normalizing data usually makes it lot easier. Reporting against an OLTP
> design is just very difficult.
> Crystal Decisions and other tools in that area should be considered, why
> do
> your own report engine when the tools are out there already?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Stephen Brown" <nospam@.telusplanet.net> wrote in message
> news:cngfci$jij$1@.utornnr1pp.grouptelecom.net...
>> 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?
>>
>>
>