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?
>>
>>
>

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, bu
t
>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 th
e
>id, definition, and what table the custom field extends and the second tabl
e
>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 t
o
>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 colum
n
>by column, but the 8000 character limit on strings kills this idea since
>each filter could possibly contain 8000 chars. Even without the filter, th
e
>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...
> because
> but
> a
> defined
> the
> table
> extending,
> to
> column
> the
> if
> killing
> a
> times
>

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...
> because
> but
> a
> defined
> the
> table
> extending,
> to
> column
> the
> if
> killing
> a
> times
>
|||Please explain the nature of the "temp table deadlocking" problem you are talking about. Seems kinda weird that you would get deadlocking problems solely because of using temporary tables. (if you are using the standard "Create #TABLE (...)" for the temporary tables, of course)
If you are creating a temporary table in such a manner as to explicitly refer to the tempdb, like "create table tempdb.dbo.TABLE (...) ", then I see where the deadlock problem would be, but using #TABLE automaticially uniqueifies the name of ACTUAL table that gets created in tempdb.
good luck!
jg

Need advice, new to VB 6.0 and SQL server 7

I'm young in programming, i need somebody to tell me the minimum requirement for a software developed in VB 6.0 and SQL server 7
Thanks alotHi there,

a standard Pentium 4 with 256 RAM will do i guess, take care my fren.. :)

Need Advice on Reporting, End User Query and Data warehousing Tools

Hi:
I need an advice on Reporting, End User Query and Data warehousing
Tools
Below is my customer requirement:
Reporting:
- Flexible Reporting and Web Publishing
- Development and / or customization of reports by systems users using
a GUI based reports designer
- Export Capability to Office Automation Software
- Charting Facility
- Browser-Based User Interface
End User Query:
- Export Capability to Office Automation Software
- Web Publishing Output
- Charting Facility
- Reporting Facility
- Browser-Based User Interface
- Performance Monitoring/Tracking
- Administration
- Row and Column Level Security
Data warehouse:
- ETL and Analyst Tools
SQL2005 Enterprise, SSIS, SSAS, SSRS and Report Builder seem to meet
the requirement, but the problem is the target database is Oracle10g,
can i use those services on Oracle database ?
How about the solution from BusinessObject and Microstrategy, compare
with the services from SQL2005 Enterprise ?
Please Help.
Thanks
JCvoonHi
Reporting Services will certainly cover all your reporting needs including
the automatic generation and delivery that you have specified. You may want
to read the articles at
http://www.microsoft.com/sql/technologies/reporting/default.mspx if you have
not already. It sounds like you will not be running reports directly of you
Oracle database (which I think is possible although I have no experience of
doing this), but using Analysis Services will eliminate the need query this
data http://www.microsoft.com/sql/technologies/analysis/default.mspx.
An important factor will be a good design of the data warehouse. A poor
design will make reporting significantly more difficult, regardless of the
tool used. After using Business Objects a little I would say that for report
generation I do prefer Reporting Services.
One of the biggest factors may be licencing costs, if you are already a SQL
Server house, you will have not extra licence costs for AS or RS.
HTH
John
"jcvoon" wrote:
> Hi:
> I need an advice on Reporting, End User Query and Data warehousing
> Tools
> Below is my customer requirement:
> Reporting:
> - Flexible Reporting and Web Publishing
> - Development and / or customization of reports by systems users using
> a GUI based reports designer
> - Export Capability to Office Automation Software
> - Charting Facility
> - Browser-Based User Interface
> End User Query:
> - Export Capability to Office Automation Software
> - Web Publishing Output
> - Charting Facility
> - Reporting Facility
> - Browser-Based User Interface
> - Performance Monitoring/Tracking
> - Administration
> - Row and Column Level Security
> Data warehouse:
> - ETL and Analyst Tools
>
> SQL2005 Enterprise, SSIS, SSAS, SSRS and Report Builder seem to meet
> the requirement, but the problem is the target database is Oracle10g,
> can i use those services on Oracle database ?
> How about the solution from BusinessObject and Microstrategy, compare
> with the services from SQL2005 Enterprise ?
> Please Help.
> Thanks
> JCvoon
>|||John Bell:
Thanks for your reply.
In fact I plan to use the SSRS to generate report from Oracle database,
and use SSIS to build the Oracle data warehouse, for SSRS I think it
shouldn't be a problem, but for SSIS and SSAS I'm not sure.
Regards
JCVoon