|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
 Posted - 2001-02-20 : 11:33:01
|
| Background : SQL7, Stored Proc, Called from VB + Query Analyser Works from QA...not VB. VB (Seems to) chop/truncate a variable after pos 128.Stored Proc:CREATE PROCEDURE USP_GET_USERS@INUSERLIST VARCHAR(1000)AS BEGIN SET DATEFORMAT DMY DECLARE @SQL VARCHAR(1000) SET @INUSERLIST = "'" + REPLACE(@INUSERLIST,",", "','") + "'" SET @SQL = "SELECT * FROM VALID_USER WHERE CODE IN (" + @INUSERLIST + ")" EXEC (@SQL)ENDRETURNQA Code:EXEC USP_GET_USERS @INUSERLIST="23249,24389,88841,82670,88679,18501,85809,50192,18056,99999,99993,99990,99991,87297,81454,84992,39474,58520,87296,86713,83786,87743,84946,19743,84736,50194,35258,21296,84936,28070,50193"VB Code: strSQL = "USP_GET_USERS" _ & " @INUSERLIST=""" & strLocalList & """" Set adorsLocal = AccessADO.Execute(strSQL)' strsql and strlocallist are both string variables.Above QA code will work, but the VB version will fail. But the VB version will work when the length of the content of strlocallist is < 128. any ideas? does VB have a problem passing long variables to SP?I'm also not entirely happy with having to build the dynamic sql in the SP above..(would prefer proper SQL statements with execution plan's ,etc)....I've had problems trying to get the IN condition to work with a string variable passed to it....even when called from QA....so if you have any hints on tackling that sort of problem as well...much appreciated.Andrew |
|
VB calling SP...variable ending after length 128