Brandie Tarvin (5/10/2013)
j2cagle (5/9/2013)
Yes that helps...but I'm trying to resolve this without touching the VB code.I don't think you have a choice in the matter. The problem probably isn't within the SQL but within the VB code. It would help us to help you if you posted both the VB code and the SQL code so we could verify.
Are you using the DTS.Variable.ToString to pass the string from the VB code to the SQL?
Bevan's suggestion is the best (exactly what I would do). I.E., using CHARINDEX() and then LEFT() in the T-SQL code, but if that's not working for you, then your issue is in the VB code, not in the T-SQL.
I think the likely scenario is that the vb code is doing something like this:
ocmd = ADODB.Command
ocmd.CommandText = "EXEC dbo.[Procedure] " + sVariable
ocmd.Execute()
If you notice, that is building dynamic sql (very common in the VB6 world). With Numerics this is fine and not an issue. With strings it's a huge problem. So, really, until we get the actual VB code, we can't determine the cause.