Bulk Load XML file to SQL Server (Express) Table

Hi All,

I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?

Thanks,

Claude.

Here is a working sample from Microsoft. Hope this helps.

http://support.microsoft.com/default.aspx/kb/316005/en-us

|||

Thanks for your reply.

Are there any examples of doing this in asp.net 2.0 using version 2 of the .Net framework? I am currently writing my app in c#.

TIA,

Claude.

|||

The data is going into SQL Server so you have to use XML SQL Server will accept. The links below covers SQL Server 2005 XML. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

http://msdn2.microsoft.com/en-us/library/ms191184.aspx

sql

Bulk Insert of multiple, associated tables

Hi, hope this is the right place for me to post this, but I couldn't find a
more appropriate group.
In a C# app, I have a large set of structured data that will map to multiple
tables, associated with foreign keys, similar in structure to:
The xxxID columns are Identity columns
TABLE Item (ItemID )
TABLE Exchange ( ExchangeID )
TABLE Batch( BatchID, fkExchange )
TABLE Group( GroupID, fkBatch )
TABLE Transaction ( TransactionID, fkGroup )
TABLE ItemToTransactionVector ( fkItem, fkTransaction )
'Item' rows already exist, and I'm creating the rest in my app to be added
to the database en-mass. Exchanges have a single Batch;Batches have multipl
e
Groups; Groups have multiple Transactions, etc.
For a single table, I would use the ADODB SqlBulkCopy class, unfortunately,
I need to set the foreign keys between the tables.
At the moment, I have an initial implementation that uses individual
inserts, but this is way too slow, even wrapping them inside a transaction.
My thoughts after a little research were:
-Create matching temporary tables, or permanent staging tables for each of
those to be imported.
-Preset the row data with 'fake' foreign keys in my application, to maintain
the relationship between the rows in different tables.
-Process the data somehow from the temporary tables into the real tables
Is this a ridiculous scheme? How else can I insert all that data and
maintain all the relationships - and do it fast?
Thanks! Ross"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:DE44B64C-5790-4D15-A35F-E82ECA15FF9A@.microsoft.com...
> Hi, hope this is the right place for me to post this, but I couldn't find
> a
> more appropriate group.
> In a C# app, I have a large set of structured data that will map to
> multiple
> tables, associated with foreign keys, similar in structure to:
> The xxxID columns are Identity columns
> TABLE Item (ItemID )
> TABLE Exchange ( ExchangeID )
> TABLE Batch( BatchID, fkExchange )
> TABLE Group( GroupID, fkBatch )
> TABLE Transaction ( TransactionID, fkGroup )
> TABLE ItemToTransactionVector ( fkItem, fkTransaction )
> 'Item' rows already exist, and I'm creating the rest in my app to be added
> to the database en-mass. Exchanges have a single Batch;Batches have
> multiple
> Groups; Groups have multiple Transactions, etc.
> For a single table, I would use the ADODB SqlBulkCopy class,
> unfortunately,
> I need to set the foreign keys between the tables.
> At the moment, I have an initial implementation that uses individual
> inserts, but this is way too slow, even wrapping them inside a
> transaction.
> My thoughts after a little research were:
> -Create matching temporary tables, or permanent staging tables for each of
> those to be imported.
> -Preset the row data with 'fake' foreign keys in my application, to
> maintain
> the relationship between the rows in different tables.
> -Process the data somehow from the temporary tables into the real tables
> Is this a ridiculous scheme? How else can I insert all that data and
> maintain all the relationships - and do it fast?
> Thanks! Ross
I can't help you with the ADO bulk load part but here's an example of how
you can load the data into a table variable and then populate the related
tables. Depending on the size of the tables you may prefer to try a temp
table or a permanent one.
/* Sample tables */
CREATE TABLE T1 (x INT IDENTITY PRIMARY KEY, z VARCHAR(10) NOT NULL UNIQUE);
CREATE TABLE T2 (x INT NOT NULL REFERENCES T1 (x), z1 VARCHAR(10) NOT NULL,
PRIMARY KEY (x,z1));
/* temp table variable */
DECLARE @.t TABLE (z VARCHAR(10) NOT NULL, z1 VARCHAR(10) NOT NULL, PRIMARY
KEY (z,z1));
/* Insert the raw data */
INSERT INTO @.t (z,z1)
SELECT 'XX', 'ABC' UNION ALL
SELECT 'XXX', 'DEF' UNION ALL
SELECT 'XXX', 'GHI' ;
/* Populate the parent table */
INSERT INTO T1 (z)
SELECT DISTINCT z
FROM @.t;
/* Join in the foreign key and populate the referencing table */
INSERT INTO T2 (x,z1)
SELECT T1.x, T.z1
FROM @.t AS T
JOIN T1
ON T.z = T1.z;
/* Result */
SELECT * FROM T1;
SELECT * FROM T2;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you time David. I'll look at adapting that and see how well it
works for my case.
Regards,
Ross
"David Portas" wrote:

> "Ross" <Ross@.discussions.microsoft.com> wrote in message
...
> I can't help you with the ADO bulk load part but here's an example of how
> you can load the data into a table variable and then populate the related
> tables. Depending on the size of the tables you may prefer to try a temp
> table or a permanent one.
>
...

> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

Building custom Reporting Services Toolbar

We have an ASP.Net web app that uses RS for reporting. We can't use the RS
toolbar since we will be generating reports using the SOAP API. Does
anybody have any ideas/suggestions/examples on building a custom toolbar for
RS? Any input will be greatly appreciated. TIA.You just need to create a user control that calls the RS SOAP API for the
functionality you need, e.g. Parameters, etc. The AwReporterWeb project
included in my book source code
(http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
demonstrates how you can get a list of reports and parameters. More involved
scenarios are implemented in the AwReporterWin WinForm demo (e.g. obtaining
the parameters values, multi-value parameters, etc.).
As a side note, you may find my AwReportViewer control useful
(http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
handle images, dataset binding (using my custom dataset extension) and more
:-)
Finally, please note that by going SOAP you are losing not only the report
toolbar but also report interactive features (at least in version 1.0 of
RS), as well as simplicity.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> We have an ASP.Net web app that uses RS for reporting. We can't use the
RS
> toolbar since we will be generating reports using the SOAP API. Does
> anybody have any ideas/suggestions/examples on building a custom toolbar
for
> RS? Any input will be greatly appreciated. TIA.
>
>|||One more thing... you can save yourself quite a bit of development time if
you could postpone the toolbar implementation until the RS 2005 controls are
out. They will include a custom toolbar, events, and will support both
connected and disconnected mode. The first public drop of the controls will
be in Yukon Beta 3 which is just a few months away.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
> You just need to create a user control that calls the RS SOAP API for the
> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
> included in my book source code
> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
> demonstrates how you can get a list of reports and parameters. More
involved
> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
obtaining
> the parameters values, multi-value parameters, etc.).
> As a side note, you may find my AwReportViewer control useful
> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
> handle images, dataset binding (using my custom dataset extension) and
more
> :-)
> Finally, please note that by going SOAP you are losing not only the report
> toolbar but also report interactive features (at least in version 1.0 of
> RS), as well as simplicity.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> > We have an ASP.Net web app that uses RS for reporting. We can't use the
> RS
> > toolbar since we will be generating reports using the SOAP API. Does
> > anybody have any ideas/suggestions/examples on building a custom toolbar
> for
> > RS? Any input will be greatly appreciated. TIA.
> >
> >
> >
>|||Thanks Leo. I've been planning on getting your book for some time now, but
haven't gotten around to it... yet. Now I will have to :-)
I would have prefered to use URL access, and custom authentication, but we
need to upgrade to Enterprise Edition to do that, and the cost is a bit too
much. I understand that the reports will lose some of its interactive
features if I use SOAP API.
"Teo Lachev" <teo@.prologika.com> wrote in message
news:uSDp6g4yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> One more thing... you can save yourself quite a bit of development time if
> you could postpone the toolbar implementation until the RS 2005 controls
> are
> out. They will include a custom toolbar, events, and will support both
> connected and disconnected mode. The first public drop of the controls
> will
> be in Yukon Beta 3 which is just a few months away.
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
>> You just need to create a user control that calls the RS SOAP API for the
>> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
>> included in my book source code
>> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
>> demonstrates how you can get a list of reports and parameters. More
> involved
>> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
> obtaining
>> the parameters values, multi-value parameters, etc.).
>> As a side note, you may find my AwReportViewer control useful
>> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
>> handle images, dataset binding (using my custom dataset extension) and
> more
>> :-)
>> Finally, please note that by going SOAP you are losing not only the
>> report
>> toolbar but also report interactive features (at least in version 1.0 of
>> RS), as well as simplicity.
>> --
>> Hope this helps.
>> ---
>> Teo Lachev, MVP [SQL Server], MCSD, MCT
>> Author: "Microsoft Reporting Services in Action"
>> Publisher website: http://www.manning.com/lachev
>> Buy it from Amazon.com: http://shrinkster.com/eq
>> Home page and blog: http://www.prologika.com/
>> ---
>> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
>> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
>> > We have an ASP.Net web app that uses RS for reporting. We can't use
>> > the
>> RS
>> > toolbar since we will be generating reports using the SOAP API. Does
>> > anybody have any ideas/suggestions/examples on building a custom
>> > toolbar
>> for
>> > RS? Any input will be greatly appreciated. TIA.
>> >
>> >
>> >
>>
>|||You don't have to :-)
I guess at the end you would you need to determine if the money you will
save by using Standard justifies the development effort getting around SOAP
addressability limitations. On the upside, it looks like the 2005 controls
will support drilldown with SOAP.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:ej40s59yEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Leo. I've been planning on getting your book for some time now,
but
> haven't gotten around to it... yet. Now I will have to :-)
> I would have prefered to use URL access, and custom authentication, but we
> need to upgrade to Enterprise Edition to do that, and the cost is a bit
too
> much. I understand that the reports will lose some of its interactive
> features if I use SOAP API.
> "Teo Lachev" <teo@.prologika.com> wrote in message
> news:uSDp6g4yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> > One more thing... you can save yourself quite a bit of development time
if
> > you could postpone the toolbar implementation until the RS 2005 controls
> > are
> > out. They will include a custom toolbar, events, and will support both
> > connected and disconnected mode. The first public drop of the controls
> > will
> > be in Yukon Beta 3 which is just a few months away.
> >
> > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
> >> You just need to create a user control that calls the RS SOAP API for
the
> >> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
> >> included in my book source code
> >> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
> >> demonstrates how you can get a list of reports and parameters. More
> > involved
> >> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
> > obtaining
> >> the parameters values, multi-value parameters, etc.).
> >>
> >> As a side note, you may find my AwReportViewer control useful
> >> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45)
to
> >> handle images, dataset binding (using my custom dataset extension) and
> > more
> >> :-)
> >>
> >> Finally, please note that by going SOAP you are losing not only the
> >> report
> >> toolbar but also report interactive features (at least in version 1.0
of
> >> RS), as well as simplicity.
> >>
> >> --
> >> Hope this helps.
> >>
> >> ---
> >> Teo Lachev, MVP [SQL Server], MCSD, MCT
> >> Author: "Microsoft Reporting Services in Action"
> >> Publisher website: http://www.manning.com/lachev
> >> Buy it from Amazon.com: http://shrinkster.com/eq
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >>
> >> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> >> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> >> > We have an ASP.Net web app that uses RS for reporting. We can't use
> >> > the
> >> RS
> >> > toolbar since we will be generating reports using the SOAP API. Does
> >> > anybody have any ideas/suggestions/examples on building a custom
> >> > toolbar
> >> for
> >> > RS? Any input will be greatly appreciated. TIA.
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>

Building a Generic Report Viewer

I am just learning SSRS. I am coming from a Crystal Reports background. In
the app that I am upgrading to SSRS, I had a generic report viewer that I
fed the name of the report. The viewer than opened the report pulled out
the data source and set it and passed through any selection criteria from
the app and then ran the report in the viewer.
Is this possible with SSRS?
Will the report viewer sit in the client side app and I set the processing
mode to remote? I am doing a remote app connecting through a VPN to a SQL
Server 2005 database.
Thanks
BillGWhat you want is the report viewer control (winform or webform) that ships
with VS 2005. There is no viewer control that ships with RS. You have to get
the control via VS 2005 (or VS 2008). It does exactly as you describe.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BillG" <billgower@.charter.net> wrote in message
news:DC9683FB-6354-44AD-89C9-B1A577541939@.microsoft.com...
>I am just learning SSRS. I am coming from a Crystal Reports background.
>In the app that I am upgrading to SSRS, I had a generic report viewer that
>I fed the name of the report. The viewer than opened the report pulled out
>the data source and set it and passed through any selection criteria from
>the app and then ran the report in the viewer.
> Is this possible with SSRS?
> Will the report viewer sit in the client side app and I set the processing
> mode to remote? I am doing a remote app connecting through a VPN to a SQL
> Server 2005 database.
>
> Thanks
> BillG
>