Hello All,
My challenge is to create a process that will bring together data from two
SQL servers (one local, one remote through the internet). The task is to
create a Stored Procedure that will perform a relational query based on data
from the two databases and return the resulting record set to a Crystal
Report. The remote database is part of our company's CRM HOSTED solution
which provides data accessible via a SOAP/XML interface.
I have implemented solutions in the past using a SOAP/XML data feed, whereby
I used VBScript, MSXML, MSSOAP to connect, query, parse and insert data into
an SQL database. For this implementation, however, I want to avoid this
method at all costs since there would be a tremendous overhead of converting,
maintaining and updating dozens of Crystal Reports to this new environment.
Instead, I would like to be able to make an SQL Stored Procedure invoke the
SOAP call and, in turn, load and process the resulting XML dataset as though
it were an SQL Record Set. I have found technical leads supporting the theory
that I might be able to achieve this goal. The following are technical
"bread-crumbs" I found or technical areas I have investigated thus far:
(1) SOAP/XML can be delivered from an MSSQL server in several ways via "AS
XML" functionality provided in SELECT verb. I am hypothesizing that the CRM
can deliver the XML data per a direct SQL query through this method. The nut
to crack here is that our XML data would be delivered back to the caller
"shrink-wrapped" in a SOAP Envelope and thus there would be the task of
unwrapping the XML dataset from the SOAP envelope upon the return of data to
the caller.
(2) MSSQL provides a method via "OPENXML" to open and parse an XML source
file and associate data returned with SELECT / Relational logic. CAVEAT: I
have only seen the OPENXML deal with XML data stored in FILE resources...not
an XML object such as that found implemented in the MSXML object classes
which I have used in VBSCRIPT.
I am trying to efficiently architect a solution without having to develop
too many components and excessive rework of prior development. My goal is to
perform this data query task ALL within the MSSQL environment. Any direction
or validation is greatly appreciated.
Sincerely,
Karl Engvold
Website: http://www.mcint.net
In SQLServer 2005 we have Native Web Services Support. This allows one to expose a stored procedure as a web service. The stored procedcure could be written in CLR or T-SQL. We also generate WSDL on demand. The SOAP responses contain "rowsets" to captu
re the output of select statements. These rowsets use the diffgram format on the wire and as such can be mapped to datasets on the client. Using a VS>net proxy generator to generate the client code does this automatically for you.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||as mentioned earlier this fucntionality is available in SQL2005 Beta bits. If you can get a hold of these bits and .Net framewwork Beta1 you can do what I suggested.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Showing posts with label svr. Show all posts
Showing posts with label svr. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
Possible to join results from two seperate datasets?
If I have two datasources, one pointing to Oracle and another pointing to
SQL Svr and consequently, two datasets (one from each datasource); is it
possible to create a third dataset from the two or somehow combine the two
into a single table?
Thanks,
JamesYou need to use subreports. Note that you can put a subreport into a cell of
the table control. But also note, the subreport gets executed for each row.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:%23U92zmSVHHA.1200@.TK2MSFTNGP04.phx.gbl...
> If I have two datasources, one pointing to Oracle and another pointing to
> SQL Svr and consequently, two datasets (one from each datasource); is it
> possible to create a third dataset from the two or somehow combine the two
> into a single table?
> Thanks,
> James
>|||I'm trying to figure my way through this, but it just isn't that
intuitive. Would you possibly have a simple example that you could
share?
Thanks.
--|||A subreport is a regular report with parameters. Design each report and test
independently. Then drag and drop the subreport onto the main report. Then
do a right mouse click on the subreport, properties and map the subreport
properties to a field in your dataset (you can also map to the main report
parameters, an expression, etc). I suggest trying something simple first.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:%23rozAGcVHHA.5092@.TK2MSFTNGP03.phx.gbl...
> I'm trying to figure my way through this, but it just isn't that
> intuitive. Would you possibly have a simple example that you could
> share?
> Thanks.
> --
>|||Bruce L-C [MVP] wrote:
> A subreport is a regular report with parameters. Design each report
> and test independently. Then drag and drop the subreport onto the
> main report. Then do a right mouse click on the subreport, properties
> and map the subreport properties to a field in your dataset (you can
> also map to the main report parameters, an expression, etc). I
> suggest trying something simple first.
I guess the part that's confusing me is, to design what will be the sub
report, I need to create a dataset that that report will use. I design
the report, test it and once I'm happy with it, drag it onto my main
report so that it because an actual subreport. Then, define the
parameters that it will receive from the main report, whether that be
values that are parameters that the main report receives from user
input or data from the datasets the main report uses. I don't see how
that will allow me to join/combine the two datasets that the main
report has (One populated from Oracle and the other from SQL) and
populate a single table in the subreport. Am I totally missing the
obvious'
My issue is our HR department has a user table with employee id's and
user names and other info and another department has a table that just
has employee id's but no other user info. I need to pull user info
from Oracle and Project info from SQL and join the two into a single
table based on the employee id. When I'm designing what will become
the subreport with the two datasets combined... what am I using as a
dataset at design time?
I appreciate your time and effort in helping me work through this!
--|||Yes, you are missing the point. You have one report with the Oracle dataset
and the other report has the SQL dataset. Your choice which is the main
report and which is the subreport. Design each report independently with the
appropriate parameters and test it.
Subreports are how you handle a join like this(1-many or 1-1).
OK, so in your case have the Oracle user info be the main report. Design and
test the report. Then have another report using the SQL dataset with a
parameter of employee ID. Test the second report by running it and giving it
the employee id. Then do as I mentioned before.
In this case I would suggest using the list control in the main report. A
table control in the report that will be the subreport.
This is a classic one to many, employee information and then multiple
records of the project info.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:OtZ$GfdVHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Bruce L-C [MVP] wrote:
>> A subreport is a regular report with parameters. Design each report
>> and test independently. Then drag and drop the subreport onto the
>> main report. Then do a right mouse click on the subreport, properties
>> and map the subreport properties to a field in your dataset (you can
>> also map to the main report parameters, an expression, etc). I
>> suggest trying something simple first.
> I guess the part that's confusing me is, to design what will be the sub
> report, I need to create a dataset that that report will use. I design
> the report, test it and once I'm happy with it, drag it onto my main
> report so that it because an actual subreport. Then, define the
> parameters that it will receive from the main report, whether that be
> values that are parameters that the main report receives from user
> input or data from the datasets the main report uses. I don't see how
> that will allow me to join/combine the two datasets that the main
> report has (One populated from Oracle and the other from SQL) and
> populate a single table in the subreport. Am I totally missing the
> obvious'
> My issue is our HR department has a user table with employee id's and
> user names and other info and another department has a table that just
> has employee id's but no other user info. I need to pull user info
> from Oracle and Project info from SQL and join the two into a single
> table based on the employee id. When I'm designing what will become
> the subreport with the two datasets combined... what am I using as a
> dataset at design time?
> I appreciate your time and effort in helping me work through this!
> --
>|||Bruce L-C [MVP] wrote:
> Yes, you are missing the point. You have one report with the Oracle
> dataset and the other report has the SQL dataset. Your choice which
> is the main report and which is the subreport. Design each report
> independently with the appropriate parameters and test it.
> Subreports are how you handle a join like this(1-many or 1-1).
> OK, so in your case have the Oracle user info be the main report.
> Design and test the report. Then have another report using the SQL
> dataset with a parameter of employee ID. Test the second report by
> running it and giving it the employee id. Then do as I mentioned
> before.
> In this case I would suggest using the list control in the main
> report. A table control in the report that will be the subreport.
> This is a classic one to many, employee information and then multiple
> records of the project info.
Now it makes sense. Thanks!
--|||Its possible to use a linked server (to oracle database) to run the query
via SQL Server and reference tables in each database as part of a single
query. Users can also be granted access/mapped to oracle users.
"James" <jptyree@.duke-energy.com> wrote in message
news:eO4F5neVHHA.528@.TK2MSFTNGP03.phx.gbl...
> Bruce L-C [MVP] wrote:
>> Yes, you are missing the point. You have one report with the Oracle
>> dataset and the other report has the SQL dataset. Your choice which
>> is the main report and which is the subreport. Design each report
>> independently with the appropriate parameters and test it.
>> Subreports are how you handle a join like this(1-many or 1-1).
>> OK, so in your case have the Oracle user info be the main report.
>> Design and test the report. Then have another report using the SQL
>> dataset with a parameter of employee ID. Test the second report by
>> running it and giving it the employee id. Then do as I mentioned
>> before.
>> In this case I would suggest using the list control in the main
>> report. A table control in the report that will be the subreport.
>> This is a classic one to many, employee information and then multiple
>> records of the project info.
> Now it makes sense. Thanks!
> --
>
SQL Svr and consequently, two datasets (one from each datasource); is it
possible to create a third dataset from the two or somehow combine the two
into a single table?
Thanks,
JamesYou need to use subreports. Note that you can put a subreport into a cell of
the table control. But also note, the subreport gets executed for each row.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:%23U92zmSVHHA.1200@.TK2MSFTNGP04.phx.gbl...
> If I have two datasources, one pointing to Oracle and another pointing to
> SQL Svr and consequently, two datasets (one from each datasource); is it
> possible to create a third dataset from the two or somehow combine the two
> into a single table?
> Thanks,
> James
>|||I'm trying to figure my way through this, but it just isn't that
intuitive. Would you possibly have a simple example that you could
share?
Thanks.
--|||A subreport is a regular report with parameters. Design each report and test
independently. Then drag and drop the subreport onto the main report. Then
do a right mouse click on the subreport, properties and map the subreport
properties to a field in your dataset (you can also map to the main report
parameters, an expression, etc). I suggest trying something simple first.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:%23rozAGcVHHA.5092@.TK2MSFTNGP03.phx.gbl...
> I'm trying to figure my way through this, but it just isn't that
> intuitive. Would you possibly have a simple example that you could
> share?
> Thanks.
> --
>|||Bruce L-C [MVP] wrote:
> A subreport is a regular report with parameters. Design each report
> and test independently. Then drag and drop the subreport onto the
> main report. Then do a right mouse click on the subreport, properties
> and map the subreport properties to a field in your dataset (you can
> also map to the main report parameters, an expression, etc). I
> suggest trying something simple first.
I guess the part that's confusing me is, to design what will be the sub
report, I need to create a dataset that that report will use. I design
the report, test it and once I'm happy with it, drag it onto my main
report so that it because an actual subreport. Then, define the
parameters that it will receive from the main report, whether that be
values that are parameters that the main report receives from user
input or data from the datasets the main report uses. I don't see how
that will allow me to join/combine the two datasets that the main
report has (One populated from Oracle and the other from SQL) and
populate a single table in the subreport. Am I totally missing the
obvious'
My issue is our HR department has a user table with employee id's and
user names and other info and another department has a table that just
has employee id's but no other user info. I need to pull user info
from Oracle and Project info from SQL and join the two into a single
table based on the employee id. When I'm designing what will become
the subreport with the two datasets combined... what am I using as a
dataset at design time?
I appreciate your time and effort in helping me work through this!
--|||Yes, you are missing the point. You have one report with the Oracle dataset
and the other report has the SQL dataset. Your choice which is the main
report and which is the subreport. Design each report independently with the
appropriate parameters and test it.
Subreports are how you handle a join like this(1-many or 1-1).
OK, so in your case have the Oracle user info be the main report. Design and
test the report. Then have another report using the SQL dataset with a
parameter of employee ID. Test the second report by running it and giving it
the employee id. Then do as I mentioned before.
In this case I would suggest using the list control in the main report. A
table control in the report that will be the subreport.
This is a classic one to many, employee information and then multiple
records of the project info.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:OtZ$GfdVHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Bruce L-C [MVP] wrote:
>> A subreport is a regular report with parameters. Design each report
>> and test independently. Then drag and drop the subreport onto the
>> main report. Then do a right mouse click on the subreport, properties
>> and map the subreport properties to a field in your dataset (you can
>> also map to the main report parameters, an expression, etc). I
>> suggest trying something simple first.
> I guess the part that's confusing me is, to design what will be the sub
> report, I need to create a dataset that that report will use. I design
> the report, test it and once I'm happy with it, drag it onto my main
> report so that it because an actual subreport. Then, define the
> parameters that it will receive from the main report, whether that be
> values that are parameters that the main report receives from user
> input or data from the datasets the main report uses. I don't see how
> that will allow me to join/combine the two datasets that the main
> report has (One populated from Oracle and the other from SQL) and
> populate a single table in the subreport. Am I totally missing the
> obvious'
> My issue is our HR department has a user table with employee id's and
> user names and other info and another department has a table that just
> has employee id's but no other user info. I need to pull user info
> from Oracle and Project info from SQL and join the two into a single
> table based on the employee id. When I'm designing what will become
> the subreport with the two datasets combined... what am I using as a
> dataset at design time?
> I appreciate your time and effort in helping me work through this!
> --
>|||Bruce L-C [MVP] wrote:
> Yes, you are missing the point. You have one report with the Oracle
> dataset and the other report has the SQL dataset. Your choice which
> is the main report and which is the subreport. Design each report
> independently with the appropriate parameters and test it.
> Subreports are how you handle a join like this(1-many or 1-1).
> OK, so in your case have the Oracle user info be the main report.
> Design and test the report. Then have another report using the SQL
> dataset with a parameter of employee ID. Test the second report by
> running it and giving it the employee id. Then do as I mentioned
> before.
> In this case I would suggest using the list control in the main
> report. A table control in the report that will be the subreport.
> This is a classic one to many, employee information and then multiple
> records of the project info.
Now it makes sense. Thanks!
--|||Its possible to use a linked server (to oracle database) to run the query
via SQL Server and reference tables in each database as part of a single
query. Users can also be granted access/mapped to oracle users.
"James" <jptyree@.duke-energy.com> wrote in message
news:eO4F5neVHHA.528@.TK2MSFTNGP03.phx.gbl...
> Bruce L-C [MVP] wrote:
>> Yes, you are missing the point. You have one report with the Oracle
>> dataset and the other report has the SQL dataset. Your choice which
>> is the main report and which is the subreport. Design each report
>> independently with the appropriate parameters and test it.
>> Subreports are how you handle a join like this(1-many or 1-1).
>> OK, so in your case have the Oracle user info be the main report.
>> Design and test the report. Then have another report using the SQL
>> dataset with a parameter of employee ID. Test the second report by
>> running it and giving it the employee id. Then do as I mentioned
>> before.
>> In this case I would suggest using the list control in the main
>> report. A table control in the report that will be the subreport.
>> This is a classic one to many, employee information and then multiple
>> records of the project info.
> Now it makes sense. Thanks!
> --
>
Subscribe to:
Posts (Atom)