January 3, 2013 at 5:07 pm
So, I'm currently looking over some 2k5 to 2k8 upgrades we're doing (yeah, I know) and I'm reviewing using table parameters. I'm having a bit of confusion over the implementation.
Now, I know within the same server that types are passed byref instead of byval and thus cannot travel between different databases. That alone is a headscratching design decision but whatever.
Now, what I'm trying to do is cleanup a linked server call that is currently doing loop-the-loops back to the originating DB for some information. Now, 2k5 you'd use XML and pass that along to your foreign procedure to use in data limitations, but that's not a simple technique. So what's happening is it's calling the foreign proc, which calls back for a data limiter in a local #tmp, which it then uses to send information back to the originating structure. This is necessary else the foreign call would dump way too much data back to be limited after the fact.
What I figured I'd do was implement the new table variable parameters and just send it along in the call, but for the life of me I can't figure out how to byval the pass. The .NET calls off the sqlclient library almost have to pass byval, as they can't send their byref down the connection pipe, unless I'm seriously misunderstanding something about that connection object.
Can anyone point out to me what I've missed?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2013 at 4:59 pm
Evil Kraig F (1/3/2013)
So, I'm currently looking over some 2k5 to 2k8 upgrades we're doing (yeah, I know) and I'm reviewing using table parameters. I'm having a bit of confusion over the implementation.Now, I know within the same server that types are passed byref instead of byval and thus cannot travel between different databases. That alone is a headscratching design decision but whatever.
Now, what I'm trying to do is cleanup a linked server call that is currently doing loop-the-loops back to the originating DB for some information. Now, 2k5 you'd use XML and pass that along to your foreign procedure to use in data limitations, but that's not a simple technique. So what's happening is it's calling the foreign proc, which calls back for a data limiter in a local #tmp, which it then uses to send information back to the originating structure. This is necessary else the foreign call would dump way too much data back to be limited after the fact.
What I figured I'd do was implement the new table variable parameters and just send it along in the call, but for the life of me I can't figure out how to byval the pass. The .NET calls off the sqlclient library almost have to pass byval, as they can't send their byref down the connection pipe, unless I'm seriously misunderstanding something about that connection object.
Can anyone point out to me what I've missed?
Not sure I am completely getting the concern, but I thought I would trace a stored proc call from .NET and show what SQL the SqlClient actually generates on the server side whenever a TVP is used. I had done this once before when TVPs first were released but the refresher was nice.
Reference: http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters
USE [SampleDB]
GO
/****** Object: UserDefinedTableType [dbo].[SampleDataType] Script Date: 1/5/2013 4:50:50 PM ******/
CREATE TYPE [dbo].[SampleDataType] AS TABLE(
[SampleString] [nvarchar](64) NOT NULL,
[SampleInt] [int] NULL
)
GO
[font="Courier New"]//in this example we create a data table with same name as the type we have in the DB
DataTable dataTable = new DataTable("SampleDataType");
//we create column names as per the type in DB
dataTable.Columns.Add("SampleString", typeof(string));
dataTable.Columns.Add("SampleInt", typeof(Int32));
//and fill in some values
dataTable.Rows.Add("99", 99);
dataTable.Rows.Add("98", null);
dataTable.Rows.Add("97", 99);
SqlConnection sqlConnection =
new SqlConnection(
ConfigurationManager.ConnectionStrings["SampleDBConnectionString"].ConnectionString);
SqlCommand command = sqlConnection.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "[dbo].[SampleProcedure]";
SqlParameter parameter = new SqlParameter();
//The parameter for the SP must be of SqlDbType.Structured
parameter.ParameterName="@Sample";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = dataTable;
command.Parameters.Add(parameter);
sqlConnection.Open();
int numberOfRowsUpdated = command.ExecuteNonQuery();[/font]
declare @p1 dbo.SampleDataType
insert into @p1 values(N'99',99)
insert into @p1 values(N'98',NULL)
insert into @p1 values(N'97',99)
exec [dbo].[SampleProcedure] @Sample=@p1
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 7, 2013 at 1:37 pm
opc.three (1/5/2013)
Not sure I am completely getting the concern, but I thought I would trace a stored proc call from .NET and show what SQL the SqlClient actually generates on the server side whenever a TVP is used. I had done this once before when TVPs first were released but the refresher was nice.
Trying to make it worked across linked servers is the primary problem. I'd assumed since .NET went byval, linked servers would too.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 7, 2013 at 2:12 pm
It is likely something to do with the User-defined Type not being present locally. Even though I have dbo.SampleDataType defined locally defined the same as the remote server I still cannot pass it across the Linked Server boundary using 4-part naming.
This:
declare @p1 dbo.SampleDataType
insert into @p1 values(N'99',99)
insert into @p1 values(N'98',NULL)
insert into @p1 values(N'97',99)
EXEC [SomeServer\SQL2008R2].test.dbo.SampleProcedure @Sample=@p1
Yields this:
Msg 7380, Level 16, State 1, Line 8
Table-valued parameters are not allowed in remote calls between servers.
This works OK though which should be expected as it parallels what .NET is doing by passing the table declaration and population within batch to be executed on the remote server:
DECLARE @sql NVARCHAR(MAX) = '
use test;
declare @p1 dbo.SampleDataType
insert into @p1 values(N''99'',99)
insert into @p1 values(N''98'',NULL)
insert into @p1 values(N''97'',99)
EXEC dbo.SampleProcedure @Sample=@p1'
EXEC(@sql) AT [SomeServer\SQL2008R2];
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 8, 2013 at 12:56 pm
I think we're finally drilling down to my confusion, Orlando, so thanks for the help. There's assumptions I was making about the connection object that are obviously inaccurate. I also can't help but feel I'm just not explaining myself well, as our conversation is going down two semi-parallel paths, at least to me.
What I know about TVPs and their instancing:
A table type is a construct that is loaded into a Database's CLR cache and is then passed internal to the database byref within that CLR. Because each DB contains its own CLR cache, you cannot pass a TVP between databases on the same server, as the byref cannot be transferred.
Because of this, I'd assumed there was some kind of byval functionality somewhere because .NET can pass a TVP down to a proc via a connection, but you have to include a particular library to do it. Now, my assumption (there's that word again) was that it built the type locally and then byval passed the TVP to the proc on the server. What I'm understanding from what you've said though is that it's not doing that, but is actually constructing the type on the server itself from the connection to the database, including data into it, and then passing the byref to the proc in the TVP.
That seems rather chatty to me.
Also annoying, I'm still stuck at passing XML around between my servers/databases when I have what seems like the perfect tool sitting right there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 8, 2013 at 3:41 pm
On a side note... the XML datatype will not allow itself to be passed as a parameter between linked servers.
Seriously.
You have to pass a VARCHAR(MAX) then shove it into a local XML variable at the target. Microsoft, your inter-server communications decisions continue to boggle me.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 8, 2013 at 4:17 pm
I wasn't sure the crux of your issue so I threw some code out there thinking it might initiate a discussion. I am happy to help move the ball down the field so to speak, even if I cannot always get a handle on where the boundaries of the field are 😀
Evil Kraig F (1/8/2013)
Because of this, I'd assumed there was some kind of byval functionality somewhere because .NET can pass a TVP down to a proc via a connection, but you have to include a particular library to do it. Now, my assumption (there's that word again) was that it built the type locally and then byval passed the TVP to the proc on the server. What I'm understanding from what you've said though is that it's not doing that, but is actually constructing the type on the server itself from the connection to the database, including data into it, and then passing the byref to the proc in the TVP.
I think that was a fair assumption but they chose to go a different route I guess. Giving the benefit of the doubt one could say it left room for other non-.NET implementations. The C# code sample above could easily be ported to Java and the only "plumbing" that would need to be written is the implicit conversion of the DataSet (or whatever the cousin of that is called in Java) to be iterated over and converted to the DDL needed to declare and populate the TVP prior to shipping the batch with the proc-execution over the connection to be executed. I would say that if they developed the native approach you sort of outlined using some type of serialization of the DataSet to ship down the pipe they would still want to leave the more chatty (I agree it appears chatty) method of doing the same thing for the non-.NET app clients.
That seems rather chatty to me.
Agreed. Would you say it is more chatty than passing an XML document though? How about when you factor in the need to serialize a DataSet (.NET or tabular) to XML in the code, and then the deserialize it XML into a tabular structure (XQuery or whatever) inside the proc? It would probably depend on the XML schema but it's looks to be pretty close to the same level of verbosity in general, maybe even favoring TVPs when you consider the serialization and deserialization aspects (my opinion).
On a side note... the XML datatype will not allow itself to be passed as a parameter between linked servers.
Seriously.
You have to pass a VARCHAR(MAX) then shove it into a local XML variable at the target. Microsoft, your inter-server communications decisions continue to boggle me.
I can't say I have run into that one in my own development efforts but have seen some folks on SSC knock Linked Servers for having limitations around XML types. Passing them in is not the only one either. There is a nasty limitation surrounding retrieving data from columns with XML columns too. It has been that way since the XML type was introduced and they have ignored requests to streamline it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 8, 2013 at 4:30 pm
opc.three (1/8/2013)
I wasn't sure the crux of your issue so I threw some code out there thinking it might initiate a discussion. I am happy to help move the ball down the field so to speak, even if I cannot always get a handle on where the boundaries of the field are 😀
Fair enough. When I get frustrated my Technology to English skills tend to get a bit wonky. :hehe:
Agreed. Would you say it is more chatty than passing an XML document though? How about when you factor in the need to serialize a DataSet (.NET or tabular) to XML in the code, and then the deserialize it XML into a tabular structure (XQuery or whatever) inside the proc? It would probably depend on the XML schema but it's looks to be pretty close to the same level of verbosity in general, maybe even favoring TVPs when you consider the serialization and deserialization aspects (my opinion).
Oh, the TVP is probably less chatty, but still, the connection is almost dynamic in the way it's passing data that way instead of single call-response. One of the things I press on my front end developers to do is to make sure that connection pooling is most effeciently utilized. Something like this, where you have to build your type on the connection THEN pass it to the proc is less efficient as far as I can tell, particularly since you pretty much have to dedicate that connection to that instance of the process, similar to externally controlled transactions.
In that way, I actually consider XML (within reason) to be a better choice.
There is a nasty limitation surrounding retrieving data from columns with XML columns too.[/url][/u] It has been that way since the XML type was introduced and they have ignored requests to streamline it.
Yeah, I've seen that. It's wierd. I can pass around VARCHAR(MAX) and VARBINARY(MAX) all day long on a linked server, but XML chokes it. Makes no sense. It's like Microsoft said "Here, fine, shut up. Here's some XML. Now GO AWAY." "Daddy it doesn't work." "Not my problem, you got your pony. GO AWAY."
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply