SQL 2008 passing Table-Value-parameters to stored proc via ODBC

[Leon McCalla]

Can we use row-wise binding instead of column binding with TVPs? Basically
the C code I have generates a 25 column temp-array with 1-16 rows. The
documentation for TVPs seems require 25 separate single column arrays.

Help..........

Leon

[m]

I have not used TVPs in ODBC, but in C code, it is irrelevant which is
necessary since the only effect on the code necessary is the way that the
parameters are passed in.

Passing parameters by ordinal is the usual practice in C and it is trivial
to construct a transpose function - two loops if you want to make it fully
portable, and just one if you know about the internal layout of arrays in C

"Leon McCalla" <leonm...@drop.hotmail.com> wrote in message
news:#iGGadSg...@TK2MSFTNGP05.phx.gbl...

[Leon McCalla]

thanks for replying but there is a significant difference in the way the
info is laid out and passed to the drivers. With typical array binding you
indicate to the drivers the first position of each element. as rows of data
are passed to the driver, in row-wise binding, the same element in the next
row is approximately sizeof(ROW) further along in memory. In column-wise
binding, the same element in the next row is approximately sizeof(element)
further along in memory. this is why there is a switch for row-wise or
column-wise binding.

in passing TVP parameters to the driver, all the examples use row-wise
binding. If you can show me how to get this to work I'll appreciate a great
deal.

Leon

"m" <m@b.c> wrote in message news:O6tl26cg...@TK2MSFTNGP02.phx.gbl...

[m]

Maybe I am confused, but I assumed that you were writing some parameter
mapping logic and the input array was columns of rows whereas the needed
format for TVPs is rows of columns. If that is the case, then assuming
that the total size of the array is reasonable, then you can simply
transpose the array and use it (If the array is large, then you can use data
at exec and pass it in pieces to avoid double buffering)

I have never used TVPs in ODBC, but the transpose (piecewise or complete) is
simple and requires only memcpy + modulus in C (or x86 / x64 ASM)

"Leon McCalla" <leonm...@drop.hotmail.com> wrote in message

news:#NXR19pg...@TK2MSFTNGP02.phx.gbl...

[Michael Coles]

OK, you say you have C code. Would that be C++ (managed/unmanaged), C#, or
do you really mean unmanaged "C"? If you really do mean unmanaged "C", pop
the link up here to the docs you're looking at. If you're actually using
managed code (managed C++ or C#), your best bet is probably to use a .NET
DataTable.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Leon McCalla" <leonm...@drop.hotmail.com> wrote in message

news:%23iGGadS...@TK2MSFTNGP05.phx.gbl...

[Michael Coles]

If you're using unmanaged C/C++ and ODBC, check this out:
http://msdn.microsoft.com/en-us/library/bb510467.aspx

It looks similar to the ODBC Bulk Copy API.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Michael Coles" <ad...@geocodenet.com> wrote in message
news:FA6618EF-02C9-4D65...@microsoft.com...

[Leon McCalla]

thanks..
ultimately this is what i had to do since my data was in rows and TVP
required the array to be in columns.

anyway is DATA-AT-EXEC much slower than ALL-IN-MEMORY?

Leon

"Michael Coles" <ad...@geocodenet.com> wrote in message

news:12BD8664-2D78-4EC4...@microsoft.com...

[Leon McCalla]

I'm programming in C and I'm migrating to TVPs with a SP in order to avoid
round-trips to the server for individual inserts. Is data-at-exec much
slower than all-in-memory? do these round trips go between the driver and
my program or the program and the server?

Thanks

Leon

"m" <m@b.c> wrote in message news:ee7K0Kqg...@TK2MSFTNGP05.phx.gbl...

[Michael Coles]

There can be a performance difference, but the real factor is how much data
you're actually pushing using this method. As an alternative you could pass
the data as an XML string and shred it on the server using the XML .nodes()
method. I used to do this on SQL 2005, before TVPs were available on 2008.
It might definitely be easier in your case, especially since you're writing
unmanaged code using ODBC. I don't know if you can convince your boss to
let you write .NET code, but this is a prime example of where managed code
would be much easier to create in the short term and a lot easier to
maintain in the long term.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Leon McCalla" <leonm...@drop.hotmail.com> wrote in message

news:uMeteexg...@TK2MSFTNGP04.phx.gbl...

[m]

IIRC, data at exec does not increase round-trips. The only difference is
that the app provides the values to the driver in pieces when it actually
needs to be sent to the server instead of in a complete buffer beforehand.

"Leon McCalla" <leonm...@drop.hotmail.com> wrote in message

news:OxhkSbyg...@TK2MSFTNGP04.phx.gbl...