Access and SQL Server 2008

Discussion:

(too old to reply)

Patrick Jackman

2008-05-17 15:43:40 UTC

Will it be possible to use ADO 3.5 from Access 2002 to make use of the new
Table parameter type in SQL Server 2008?

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

unknown

2008-05-17 17:01:41 UTC

Permalink

Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.

However, you can merge the two worlds of Access and the .NET Framework but
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Will it be possible to use ADO 3.5 from Access 2002 to make use of the new
Table parameter type in SQL Server 2008?
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

Patrick Jackman

2008-05-17 17:33:16 UTC

Permalink

Thanks Sylvain.

I'm working on a plan to upsize and was hoping to get my primary/secondary
table inserts and updates into a single stored procedure where the secondary
table rows would come in through this new Table parameter type.

Perhaps I could get secondary table rows from my temp tables in Access to a
SQL Server stored procedure string parameter via XML. Is it possible to get
the contents of an ADO recordset returned as XML without first persisting it
to file?

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:u5v8v%***@TK2MSFTNGP02.phx.gbl...
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.

However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

unknown

2008-05-17 19:45:00 UTC

Permalink

With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.

In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my primary/secondary
table inserts and updates into a single stored procedure where the secondary
table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access to a
SQL Server stored procedure string parameter via XML. Is it possible to get
the contents of an ADO recordset returned as XML without first persisting it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Patrick Jackman

2008-05-17 20:03:38 UTC

Permalink

Thanks for the advice.

How then do you recommend I get a primary+secondary table(s) insert or
update completed under transaction control by SQL Server?

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:***@TK2MSFTNGP02.phx.gbl...
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.

In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Patrick Jackman" <***@wimse.com> wrote in message news:***@TK2MSFTNGP05.phx.gbl...
Thanks Sylvain.

I'm working on a plan to upsize and was hoping to get my primary/secondary
table inserts and updates into a single stored procedure where the
secondary table rows would come in through this new Table parameter type.

Perhaps I could get secondary table rows from my temp tables in Access to
a SQL Server stored procedure string parameter via XML. Is it possible to
get
the contents of an ADO recordset returned as XML without first persisting it
to file?

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:u5v8v%***@TK2MSFTNGP02.phx.gbl...
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.

However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"Patrick Jackman" <***@wimse.com> wrote in message news:***@TK2MSFTNGP02.phx.gbl...
Will it be possible to use ADO 3.5 from Access 2002 to make use of the new
Table parameter type in SQL Server 2008?

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

unknown

2008-05-17 23:14:13 UTC

Permalink

Why not using the simple way? Open a transaction, send insert statements
and then commit the transaction. Test the @@rowcount value to make sure
that everything is OK. This will duplicate what you would have done inside
a SP.

You can also open a transaction and make multiple call to one or more SP.

There are some problems here; for exemple the way you will to detect for the
possibility of an error and how and where you will deal with this. Either
or not you will be using SP and if these SP can be called from an opened
transaction or not or both, the solution will be a different. You find a
way that you like and you go with it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Thanks for the advice.
How then do you recommend I get a primary+secondary table(s) insert or
update completed under transaction control by SQL Server?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.
In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my primary/secondary
table inserts and updates into a single stored procedure where the
secondary table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access to
a SQL Server stored procedure string parameter via XML. Is it possible to
get
the contents of an ADO recordset returned as XML without first persisting it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Will it be possible to use ADO 3.5 from Access 2002 to make use of the new
Table parameter type in SQL Server 2008?
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

Patrick Jackman

2008-05-18 00:19:05 UTC

Permalink

By depending on the client for database integrity am I not running the risk
of leaving the database in an inconsistent state if a client fails during a
commit trans?

When the SP manages the transaction then SQL Server is in control. There is
reduced likelyhood of the server going down versus a client and if it does
it's covered by the transaction log.

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:***@TK2MSFTNGP02.phx.gbl...
Why not using the simple way? Open a transaction, send insert statements
and then commit the transaction. Test the @@rowcount value to make sure
that everything is OK. This will duplicate what you would have done inside
a SP.

You can also open a transaction and make multiple call to one or more SP.

There are some problems here; for exemple the way you will to detect for the
possibility of an error and how and where you will deal with this. Either
or not you will be using SP and if these SP can be called from an opened
transaction or not or both, the solution will be a different. You find a
way that you like and you go with it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

unknown

2008-05-18 01:50:45 UTC

Permalink

The database integrity is not dependant of the client in any way. All
transactions are tightly related to their connection and when a connection
is closed before the transaction is committed, it's automatically rolled
back by the SQL-Server. Using or not one or more SP change nothing to that.

The problem here is to be very careful about your transaction count,
@@rowcount and @@error and how you return any error condition to the client
but using or not SP will not change anything fundamentally here.

I agree that using a single SP is probably the simplest way of making a
transaction on the SQL-Server; however, chances are high that the simplicty
that you will get on one side will be lost on another side because using a
single SP will make the passing of parameters much more complicated.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
By depending on the client for database integrity am I not running the risk
of leaving the database in an inconsistent state if a client fails during a
commit trans?
When the SP manages the transaction then SQL Server is in control. There is
reduced likelyhood of the server going down versus a client and if it does
it's covered by the transaction log.
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Why not using the simple way? Open a transaction, send insert statements
that everything is OK. This will duplicate what you would have done inside
a SP.
You can also open a transaction and make multiple call to one or more SP.
There are some problems here; for exemple the way you will to detect for the
possibility of an error and how and where you will deal with this. Either
or not you will be using SP and if these SP can be called from an opened
transaction or not or both, the solution will be a different. You find a
way that you like and you go with it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Thanks for the advice.
How then do you recommend I get a primary+secondary table(s) insert or
update completed under transaction control by SQL Server?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.
In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my
primary/secondary
table inserts and updates into a single stored procedure where the
secondary table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access to
a SQL Server stored procedure string parameter via XML. Is it possible to
get
the contents of an ADO recordset returned as XML without first persisting it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Will it be possible to use ADO 3.5 from Access 2002 to make use of the new
Table parameter type in SQL Server 2008?
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

Patrick Jackman

2008-05-18 21:46:22 UTC

Permalink

Sylvain, I really appreciate you taking the time to contribute your
suggestions here. I am starting to come around to your way of approaching
the problem. I did not realize that SQL Server would roll back under the
conditions you've described. Your solution is much easier and closer to my
existing Access/Jet coding too.

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%***@TK2MSFTNGP06.phx.gbl...
The database integrity is not dependant of the client in any way. All
transactions are tightly related to their connection and when a connection
is closed before the transaction is committed, it's automatically rolled
back by the SQL-Server. Using or not one or more SP change nothing to that.

The problem here is to be very careful about your transaction count,
@@rowcount and @@error and how you return any error condition to the client
but using or not SP will not change anything fundamentally here.

I agree that using a single SP is probably the simplest way of making a
transaction on the SQL-Server; however, chances are high that the simplicty
that you will get on one side will be lost on another side because using a
single SP will make the passing of parameters much more complicated.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Thanks for the advice.
How then do you recommend I get a primary+secondary table(s) insert or
update completed under transaction control by SQL Server?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.
In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my
primary/secondary
table inserts and updates into a single stored procedure where the
secondary table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access to
a SQL Server stored procedure string parameter via XML. Is it possible to
get
the contents of an ADO recordset returned as XML without first persisting it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Will it be possible to use ADO 3.5 from Access 2002 to make use of the new
Table parameter type in SQL Server 2008?
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

Patrick Jackman

2008-05-21 16:13:10 UTC

Permalink

A further reason that I am pursuing other approaches is I've been advised to
use ADO client side transactions only as a last resort as they hold locks.

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:***@TK2MSFTNGP02.phx.gbl...
Why not using the simple way? Open a transaction, send insert statements
and then commit the transaction. Test the @@rowcount value to make sure
that everything is OK. This will duplicate what you would have done inside
a SP.

You can also open a transaction and make multiple call to one or more SP.

There are some problems here; for exemple the way you will to detect for the
possibility of an error and how and where you will deal with this. Either
or not you will be using SP and if these SP can be called from an opened
transaction or not or both, the solution will be a different. You find a
way that you like and you go with it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

unknown

2008-05-21 17:34:59 UTC

Permalink

There is not such a thing as client side transaction and like I said
earlier, using or not SPs change nothing to the fundamental effect of a
transaction. It's true that some locks will be kept until the end of a
transaction and that the code used on the client side can have an effect on
the length of time that a transaction will be kept open. It's also true
that transactions should be kept open as short as possible and that using a
single SP is a good way toward that goal

However and unless you voluntary keep open a transaction, I don't see why
this could lead to having more concurrency or performance issues simply by
not using a SP. If you have a concurrency or a dead-lock issue; it's likely
more because of the design of your database then because you will use or not
a SP. Using a single SP might lower the risk of hitting a deadlock;
however, you should code your stuff in order to have none of these.

There is much more to say abut to transactions, locks, concurrency,
dead-locking, isolation levels and performance issues than using simple
catch phrases like « Use ADO client side transactions only as a last resort
as they hold locks ». Learning how to control transactions and locks on a
sql-server is a big and important topic and cannot be reduced to the use of
a little magic recipe.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
A further reason that I am pursuing other approaches is I've been advised to
use ADO client side transactions only as a last resort as they hold locks.
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Why not using the simple way? Open a transaction, send insert statements
that everything is OK. This will duplicate what you would have done inside
a SP.
You can also open a transaction and make multiple call to one or more SP.
There are some problems here; for exemple the way you will to detect for the
possibility of an error and how and where you will deal with this. Either
or not you will be using SP and if these SP can be called from an opened
transaction or not or both, the solution will be a different. You find a
way that you like and you go with it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Thanks for the advice.
How then do you recommend I get a primary+secondary table(s) insert or
update completed under transaction control by SQL Server?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.
In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my
primary/secondary
table inserts and updates into a single stored procedure where the
secondary table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access to
a SQL Server stored procedure string parameter via XML. Is it possible to
get
the contents of an ADO recordset returned as XML without first persisting it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Will it be possible to use ADO 3.5 from Access 2002 to make use of the new
Table parameter type in SQL Server 2008?
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

Patrick Jackman

2008-05-18 00:28:53 UTC

Permalink

In the SQL Server Bible, Paul Nielsen provides an example of "unstreaming"
XML that doesn't look overly difficult.

EXEC sp_xmlprepareddocument @iDOM OUTPUT, @XML

SELECT *
FROM OPENXML (@iDOM, 'node path', 8)
WITH ([Fields])

EXEC sp_xml_removedocument @iDOM

Something like this would allow me to get the secondary table data into a
temp table then manage the primary and secondary insert/updates inside a
server side transaction.

Getting XML from an ADO recordset doesn't appear to be that easy though.

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:***@TK2MSFTNGP02.phx.gbl...
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.

In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Patrick Jackman

2008-05-18 00:44:20 UTC

Permalink

Maybe it's not too hard to get the XML with ADO 2.5 after all. I found this
on the MS site:

Dim oStream As ADODB.Stream
Set oStream = New ADODB.Stream

oRecordset.Save oStream, adPersistXML

Dim sXML As String
sXML = oStream.ReadText

oStream.Close
Set oStream = Nothing

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:***@TK2MSFTNGP02.phx.gbl...
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.

In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

unknown

2008-05-18 01:57:28 UTC

Permalink

I did not really investigate the problem of streaming and un-streaming a XML
document; especially with ADO 2.5.

The problem with using XML for the transfert of your parameters will be that
later, everytime you will make a change to your schema, you will have a lot
more of work to resynchronise many pieces of your code. The simplicity that
you'll get on one side for using a single SP for managing a transaction will
be probably lost on many other sides.

Also, the performance of your application will diminush considerably.

But in all cases, you're the final judge of your own work.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Maybe it's not too hard to get the XML with ADO 2.5 after all. I found this
Dim oStream As ADODB.Stream
Set oStream = New ADODB.Stream
oRecordset.Save oStream, adPersistXML
Dim sXML As String
sXML = oStream.ReadText
oStream.Close
Set oStream = Nothing
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
With ADO.NET, it's possible to stream directly into memory but with ADO, I
don't know. In your case, using a single SP to do everything might be
tempting but I would advise you against doing so; because it will be a lot
of trouble to streaming that and after that, in the SP, to unstream it.
Streaming into XML might be a good idea when you have to transport your data
using an intermediary format, for example for emailing it or for going over
the WAN but when you have a direct connection to the SQL-Server, I don't see
any point of doing this.
In your case, using the regular features of ADO is quite probably your best
bet.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my
primary/secondary
table inserts and updates into a single stored procedure where the secondary
table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access to a
SQL Server stored procedure string parameter via XML. Is it possible to get
the contents of an ADO recordset returned as XML without first persisting it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Patrick Jackman

2008-05-18 21:53:20 UTC

Permalink

Good points Sylvain. It would be considerably more work, difficult to
maintain and potentially much slower.

Patrick.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:***@TK2MSFTNGP02.phx.gbl...
I did not really investigate the problem of streaming and un-streaming a XML
document; especially with ADO 2.5.

The problem with using XML for the transfert of your parameters will be that
later, everytime you will make a change to your schema, you will have a lot
more of work to resynchronise many pieces of your code. The simplicity that
you'll get on one side for using a single SP for managing a transaction will
be probably lost on many other sides.

Also, the performance of your application will diminush considerably.

But in all cases, you're the final judge of your own work.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Patrick Jackman
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my
primary/secondary
table inserts and updates into a single stored procedure where the secondary
table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access to a
SQL Server stored procedure string parameter via XML. Is it possible to get
the contents of an ADO recordset returned as XML without first persisting it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access only to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

d***@epsomdotcomdotau

2008-06-04 00:37:10 UTC

Permalink

Post by Patrick Jackman
oRecordset.Save oStream, adPersistXML

It would be interesting to see how it turns out. On past (long past)
experience, no one is using streaming to do complex transactions,
and there was no guidance at all when it inexplicably did not work.

(david)

Post by Patrick Jackman
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my

primary/secondary

Post by Patrick Jackman

Post by Patrick Jackman
table inserts and updates into a single stored procedure where the secondary
table rows would come in through this new Table parameter type.
Perhaps I could get secondary table rows from my temp tables in Access

Post by Patrick Jackman

Post by Patrick Jackman
a
SQL Server stored procedure string parameter via XML. Is it possible to get
the contents of an ADO recordset returned as XML without first

persisting

Post by Patrick Jackman

Post by Patrick Jackman
it
to file?
Patrick.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
Yes and no. ADO and ADO.NET 3.5 are two very different things with
practically nothing in common between them; excerpt for the first part of
their names. With VBA code and COM/DCOM/ActiveX, you are using ADO; not
ADO.NET, so the answer will be no.
However, you can merge the two worlds of Access and the .NET Framework by
using the .NET Interoperability and VSTO (2008?) ; so the answer would be
yes in this second case. But, of course, probably that it would be a bad
idea to enter the complexity of using the .NET Framework with Access

only

Post by Patrick Jackman

Post by Patrick Jackman
to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Patrick Jackman

2008-08-08 01:20:22 UTC

Permalink

It didn't; turn out well unfortunately. It inexplicably did not work
consistently.

Patrick.

Post by Patrick Jackman
oRecordset.Save oStream, adPersistXML

Post by Patrick Jackman
Thanks Sylvain.
I'm working on a plan to upsize and was hoping to get my

primary/secondary

Post by Patrick Jackman

Post by Patrick Jackman
a
SQL Server stored procedure string parameter via XML. Is it possible to get
the contents of an ADO recordset returned as XML without first

persisting

Post by Patrick Jackman

only

Post by Patrick Jackman

Post by Patrick Jackman
to
make use of the table parameter type.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)