Re: Need an elegant solution around SQL Server Limitations
From: Mike Epprecht \(SQL MVP\) (mike_at_epprecht.net)
Date: 11/17/04
- Next message: Michael C: "Re: RADiest Client for SQL Server"
- Previous message: _adrian: "Need to sum columns"
- In reply to: Stephen Brown: "Need an elegant solution around SQL Server Limitations"
- Next in thread: Stephen Brown: "Re: Need an elegant solution around SQL Server Limitations"
- Reply: Stephen Brown: "Re: Need an elegant solution around SQL Server Limitations"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 18 Nov 2004 00:10:48 +0100
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?
>
>
>
>
>
- Next message: Michael C: "Re: RADiest Client for SQL Server"
- Previous message: _adrian: "Need to sum columns"
- In reply to: Stephen Brown: "Need an elegant solution around SQL Server Limitations"
- Next in thread: Stephen Brown: "Re: Need an elegant solution around SQL Server Limitations"
- Reply: Stephen Brown: "Re: Need an elegant solution around SQL Server Limitations"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
