> First, I welcome any alternate solutions to this problem, but please, no
> lectures on proper SQL or how a database works.
>
> The problem:
> I am using SQL Server 2005 Std to gather data from multiple data sources
> (SQL, Oracle and DB/2) for reporting needs.  I have many, many reports that
> will hit these sources in various combinations.  Some reports are related,
> some are completely different views and transformations of the data.  I've
> run into some problems with SQL Server though.
>
> There's a problem with SQL Server linked servers (I know, and I'm not
> blaming MS for this) where, if an Oracle field is simply defined as Numeric
> without the precision, you can't use a linked table type query (select ...
> from <linked>..schem.table), so, for a dynamic query (like when the date
> varies), you have to resort to EXEC (<sql>) AT <linked>.  Similar problem if
> the Oracle data has a bad date (like 1/1/0001).  OPENQUERY only accepts a
> static statement, so that doesn't work when you need to vary your criteria.
>
> Since I need to pull data from multiple sources, I'm trying to figure out a
> way I can union these sources without going through creating a table (perm
> or temp) for each report.  We have hundreds of reports and trying to figure
> out the common attributes across all of them, while it might be "best" to
> do, is rather impractical at the time.  I went down a path with the CLR only
> to find out that it imposes a restriction on functions where you have to
> define the table return structure.
>
> So what I'm after is a way to pass what I'm calling a dynamic SQL statement
> to a remote server and work with that data without defining the temporary
> holder just like OPENQUERY does.  I understand what that last statement
> implies - I'm just trying to keep from coding the temp table in every single
> stored procedure for each report.  Call it laziness, but for reports where
> we have 30+ fields returning and possibly more in the inital select from the
> source, coding the CREATE TABLE statement for every SP is a real pain in the
> rear when an TVF can do it.  I realize that OPENQUERY isn't a normal TVF and
> a TVF itself is always the same return structure (at least to my knowledge
> it is), so there may not be a way to do it.
>
> Example of how a static query works like I want:
> SELECT ... FROM OPENQUERY(<linked>, '<sql>') a UNION ...
>
> Thanks for any suggestions.
> Scott

"Stu" <stuart.ainswo***@gmail.com> wrote in message
news:1158081684.785040.91990@i3g2000cwc.googlegroups.com...
> Scott,
>
> Does the data need to be "live", or can it be a delayed process?  If
> it's delayed, you could use Integration Services to populate a
> reporting structure from each of your data sources.
>
> Just a suggestion.
>
> Stu
>
>
> Scott wrote:
>> First, I welcome any alternate solutions to this problem, but please, no
>> lectures on proper SQL or how a database works.
>>
>> The problem:
>> I am using SQL Server 2005 Std to gather data from multiple data sources
>> (SQL, Oracle and DB/2) for reporting needs.  I have many, many reports
>> that
>> will hit these sources in various combinations.  Some reports are
>> related,
>> some are completely different views and transformations of the data.
>> I've
>> run into some problems with SQL Server though.
>>
>> There's a problem with SQL Server linked servers (I know, and I'm not
>> blaming MS for this) where, if an Oracle field is simply defined as
>> Numeric
>> without the precision, you can't use a linked table type query (select
>> ...
>> from <linked>..schem.table), so, for a dynamic query (like when the date
>> varies), you have to resort to EXEC (<sql>) AT <linked>.  Similar problem
>> if
>> the Oracle data has a bad date (like 1/1/0001).  OPENQUERY only accepts a
>> static statement, so that doesn't work when you need to vary your
>> criteria.
>>
>> Since I need to pull data from multiple sources, I'm trying to figure out
>> a
>> way I can union these sources without going through creating a table
>> (perm
>> or temp) for each report.  We have hundreds of reports and trying to
>> figure
>> out the common attributes across all of them, while it might be "best" to
>> do, is rather impractical at the time.  I went down a path with the CLR
>> only
>> to find out that it imposes a restriction on functions where you have to
>> define the table return structure.
>>
>> So what I'm after is a way to pass what I'm calling a dynamic SQL
>> statement
>> to a remote server and work with that data without defining the temporary
>> holder just like OPENQUERY does.  I understand what that last statement
>> implies - I'm just trying to keep from coding the temp table in every
>> single
>> stored procedure for each report.  Call it laziness, but for reports
>> where
>> we have 30+ fields returning and possibly more in the inital select from
>> the
>> source, coding the CREATE TABLE statement for every SP is a real pain in
>> the
>> rear when an TVF can do it.  I realize that OPENQUERY isn't a normal TVF
>> and
>> a TVF itself is always the same return structure (at least to my
>> knowledge
>> it is), so there may not be a way to do it.
>>
>> Example of how a static query works like I want:
>> SELECT ... FROM OPENQUERY(<linked>, '<sql>') a UNION ...
>>
>> Thanks for any suggestions.
>> Scott
>

> So what I'm after is a way to pass what I'm calling a dynamic SQL
> statement to a remote server and work with that data without defining
> the temporary holder just like OPENQUERY does.  I understand what that
> last statement implies - I'm just trying to keep from coding the temp
> table in every single stored procedure for each report.  Call it
> laziness, but for reports where we have 30+ fields returning and
> possibly more in the inital select from the source, coding the CREATE
> TABLE statement for every SP is a real pain in the rear when an TVF can
> do it.  I realize that OPENQUERY isn't a normal TVF and a TVF itself is
> always the same return structure (at least to my knowledge it is), so
> there may not be a way to do it.
>
> Example of how a static query works like I want:
> SELECT ... FROM OPENQUERY(<linked>, '<sql>') a UNION ...

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns983D5F64153AYazorman@127.0.0.1...
> Scott (j***@doe.com) writes:
>> So what I'm after is a way to pass what I'm calling a dynamic SQL
>> statement to a remote server and work with that data without defining
>> the temporary holder just like OPENQUERY does.  I understand what that
>> last statement implies - I'm just trying to keep from coding the temp
>> table in every single stored procedure for each report.  Call it
>> laziness, but for reports where we have 30+ fields returning and
>> possibly more in the inital select from the source, coding the CREATE
>> TABLE statement for every SP is a real pain in the rear when an TVF can
>> do it.  I realize that OPENQUERY isn't a normal TVF and a TVF itself is
>> always the same return structure (at least to my knowledge it is), so
>> there may not be a way to do it.
>>
>> Example of how a static query works like I want:
>> SELECT ... FROM OPENQUERY(<linked>, '<sql>') a UNION ...
>
> What about CLR stored procedures? You have them connect to the
> remote server, and then pass the data up to client?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx