December 21, 2012 at 7:15 am
Eugene Elutin (12/21/2012)
If your input parameter is just a list of character-separated values, then you can make it just VARCHAR(MAX), then use split function to split it into table inside of proc (you can use J.Moden classic: http://www.sqlservercentral.com/articles/Tally+Table/72993/).That at least 100% will work if SP is called from Access...
It's one of my parameters, the one that caused me to start this thread, but there are others, text, number and date. All could certainly be packed into one large string, but I don't see that as being especially effective, and could lead to numerous hard-to-find headaches if I mismatch the packing and unpacking routines. I've yet to look up about passing tables as parameters from Access, but it seems to it should be possible. I hope, anyway.
December 21, 2012 at 7:33 am
pdanes (12/21/2012)
Eugene Elutin (12/21/2012)
If your input parameter is just a list of character-separated values, then you can make it just VARCHAR(MAX), then use split function to split it into table inside of proc (you can use J.Moden classic: http://www.sqlservercentral.com/articles/Tally+Table/72993/).That at least 100% will work if SP is called from Access...
It's one of my parameters, the one that caused me to start this thread, but there are others, text, number and date. All could certainly be packed into one large string, but I don't see that as being especially effective, and could lead to numerous hard-to-find headaches if I mismatch the packing and unpacking routines. I've yet to look up about passing tables as parameters from Access, but it seems to it should be possible. I hope, anyway.
I'm not suggesting to pass all different parameters as a single string. Your other parameters such as text, number and date should be definitely remain separate. I was only talking about the list of same-type values which you want to filter by in your WHERE clause.
What does make you to say "I don't see that as being especially effective"?
Have you tried it? You might be surprised...
What kind of "packing and unpacking routines" are you talking about?
At the end it's not a big difference to use of table-valued parameter anyway, so it will "lead to numerous hard-to-find headaches" with exactly the same probability... (again, I've never had any problem with this, did you? Could you advise what exact issue you had?)
Using table-valued parameters should be possible from Access using pass-through query. But it has few limitations...
BTW. This thread would really benefit if you could post full stored proc script. Otherwise we only can see partial details and may not clearly see your problem.
December 21, 2012 at 7:58 am
To paraphrase the problem originally posted. I have a parameter @MyParameter in a stored procedure that is defined, for example, as varchar(100). Occasionally the string value being passed to this stored procedure in @MyParameter is truncated as the string being passed from the application is more than 100 characters in length. There is no error message that truncation of the string has occurred.
Solutions:
1) Define this parameter as varchar(8000) and use a split routine such as the DelimitedSplit8K routine to turn the csv string into a table. Quick and easy.
2) Since it is known that @MyParameter is defined as varchar(100), test the length of the string in the applicatioin prior to making the call to the procedure to see if it is greater than 100 characters and if it is return an error to the user instead of making the call to the stored procedure.
3) Use a table valued parameter for the call to the stored procedure and let the application load the table valued parameter.
December 21, 2012 at 8:12 am
Eugene Elutin (12/21/2012)
What does make you to say "I don't see that as being especially effective"?
I meant putting all DIFFERENT TYPES of values together into a single string. As you say, those should remain separate.
What kind of "packing and unpacking routines" are you talking about?
The routines that would assemble all this stuff into a single string on the client and disassemble it on the server. At the very least, the packing routine would also have to check for the presence of the delimiting character in the data. Passing a table obviates that.
At the end it's not a big difference to use of table-valued parameter anyway, so it will "lead to numerous hard-to-find headaches" with exactly the same probability... (again, I've never had any problem with this, did you? Could you advise what exact issue you had?)
Not specifically here, I haven't used the method in talking to SQL Server, but I've done similar bonehead maneuvers, like mixing up the order of parameters. I now used named parameters exclusively, after several rounds of chasing weird behavior caused by the wong value landing in a parameter (Genus, Locality getting passed, Locality, Genus received...) I've also incorrectly parsed strings within applications, by forgetting to remove the delimiter, or put them in an array and then screwed up the array dimensions. Routine programming screwups.
Using table-valued parameters should be possible from Access using pass-through query. But it has few limitations...
What kind? Have you done this. How do you do it? Create a local table? ADO/DAO recordset? I've never passed a table as a parameter from Access to SQL Server.
BTW. This thread would really benefit if you could post full stored proc script. Otherwise we only can see partial details and may not clearly see your problem.
Well, my only real, concrete problem was that I specified a varchar(100) parameter in my stored procedure and then put in a string longer than 100 in my application - surely you don't need to see the code for that. SQL Server chopped it and the procedure did strange things, but the strange things were not the problem, nor was the code that did the strange things. It works fine, when it get its parameters correctly. The rest of the discussion since then has been theoretical, so I don't have any specific problems to solve, nor any code to post.
December 21, 2012 at 8:24 am
If your values potentially may contain separator characters then you have two choices:
use more complex multi-character separator, for example ~^~. (very often used practice eg. for BCP) or you could use XML type (then you don't even need to split it using split function).
I cannot really comment on "creating the character separated list string" bit, as it's really depends. In C#, for example, it could be just one single line of code...
I have not used Access for few years now, so you will need to google it.
Again, I don't really know for sure if table-valued parameters work good when sp is called from Access.
December 23, 2012 at 2:13 pm
Lynn Pettis (12/21/2012)
To paraphrase the problem originally posted. I have a parameter @MyParameter in a stored procedure that is defined, for example, as varchar(100). Occasionally the string value being passed to this stored procedure in @MyParameter is truncated as the string being passed from the application is more than 100 characters in length. There is no error message that truncation of the string has occurred.Solutions:
1) Define this parameter as varchar(8000) and use a split routine such as the DelimitedSplit8K routine to turn the csv string into a table. Quick and easy.
2) Since it is known that @MyParameter is defined as varchar(100), test the length of the string in the applicatioin prior to making the call to the procedure to see if it is greater than 100 characters and if it is return an error to the user instead of making the call to the stored procedure.
3) Use a table valued parameter for the call to the stored procedure and let the application load the table valued parameter.
Yeah, that pretty much sums up the options that I've been able to dig up.
Well, a bit of reading answered one thing - Access is not very good at handling table-valued parameters:
http://msdn.microsoft.com/en-us/library/bb675163.aspx
Specifically, I can pass such a parameter (and it even looks like maybe only one), but then I can't get output back, which pretty much relegates it to simple commands. When I'm looking for something, this provides no easy way to get the results back. It kind of bites, but it seems I'm stuck with it, so that blows #3 out of the water.
#2 is essentially just a way to say 'pay attention to what you're doing', since I'm still stuck with the fact that there is no way to bind the SQL Server stored procedure parameter declarations to Access. I can still do something on one side and something different on the other, which is exactly what I did, which is exactly what led to this whole conversation.
That pretty much leaves me with #1, declare the varchar parameters to a size that can't possibly be too small, no matter what data I feed the procedure. (Normal data, anyway - if I have a logic mistake in the application that puts junk into the parameter, naturally, all bets are off. But that's an entirely different problem.)
I'm still not sure if the varchar(max) issues apply to parameters the same way they do to field declarations, but 4000/8000 should be plenty for just about anything I need to do anyway.
Appreciate the thoughts from everyone.
December 23, 2012 at 6:05 pm
Hi, I think you have missed a very simple suggestion from Jeff, which was to check the length in the stored procedure you are calling.
If you are expecting anything up to 100 characters in the parameter, declare it longer, then test the length inside the procedure.
If the length passed in is greater than you actually want, you have a problem and can raise an error.
For example,
CREATE PROC test @MyList VARCHAR(101) -- wanted 100
AS
-- test for invalid length
IF LEN(@MyList)=101
-- RAISERROR or RETURN error code
-- or SELECT result
-- or SET @Result OUTPUT parameter
-- ....whatever
OR
CREATE PROC test @MyList VARCHAR(101) -- wanted 100
AS
-- test for invalid length
IF @MyList = @MyList + '.'
-- RAISERROR or RETURN error code
-- or SELECT result
-- or SET @Result OUTPUT parameter
-- ....whatever
This is a fairly simple thing to implement that should cover your situation.
Not my idea (Jeff Moden suggested it pages back...), but I heartily approve - thinking inside the box as it were...:-D
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);December 23, 2012 at 6:21 pm
pdanes (12/23/2012)
Lynn Pettis (12/21/2012)
...Yeah, that pretty much sums up the options that I've been able to dig up.
Well, a bit of reading answered one thing - Access is not very good at handling table-valued parameters:
http://msdn.microsoft.com/en-us/library/bb675163.aspx
Specifically, I can pass such a parameter (and it even looks like maybe only one), but then I can't get output back, which pretty much relegates it to simple commands. When I'm looking for something, this provides no easy way to get the results back. It kind of bites, but it seems I'm stuck with it, so that blows #3 out of the water.
#2 is essentially just a way to say 'pay attention to what you're doing', since I'm still stuck with the fact that there is no way to bind the SQL Server stored procedure parameter declarations to Access. I can still do something on one side and something different on the other, which is exactly what I did, which is exactly what led to this whole conversation.
That pretty much leaves me with #1, declare the varchar parameters to a size that can't possibly be too small, no matter what data I feed the procedure. (Normal data, anyway - if I have a logic mistake in the application that puts junk into the parameter, naturally, all bets are off. But that's an entirely different problem.)
I'm still not sure if the varchar(max) issues apply to parameters the same way they do to field declarations, but 4000/8000 should be plenty for just about anything I need to do anyway.
Appreciate the thoughts from everyone.
I emphasized #2 for a reason. This is something programmers should be doing regardless of language, not just in Access call SQL stored procedures. It shouldn't matter if it is Access, VB.NET C#, J#, C++, or any other language. Before making a call to another procedure, you should valdiate the what you are passing the other procedure regardless of what that procedure is written in.
Viewing 8 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply