August 23, 2005 at 8:30 am
I'm trying to make a dynamic SQL string that is roughly 450 characters long (insert / select piece) and am getting the string cut-off.
Anyone have a quick work-around to solve this? I can break it into 2 strings, but I'm either brain dead or having some other sort of issue trying to get the 2 pieces to work together in the same execute piece.
August 23, 2005 at 8:55 am
If you are using SQL Server 2000 the length of VARCHAR is 8000.
If you are with a version limits to 256 character length you can combine two variables to execute dynamic query.
Hope this example helps ==>
SET NOCOUNT ON
CREATE TABLE #MyTable
(
myID INT IDENTITY,
MyDesc VARCHAR(25)
)
DECLARE @SQL1 VARCHAR(255)
DECLARE @SQL2 VARCHAR(255)
DECLARE @Ctr INT
SET @Ctr = 1
WHILE @Ctr < 101
BEGIN
SELECT @SQL1 = 'INSERT INTO #MyTable (MyDesc)'
SELECT @SQL2 = 'VALUES (''MyDesc ' + CONVERT(VARCHAR, @Ctr) + ''')'
EXEC (@SQL1 + @SQL2)
SELECT @Ctr = @Ctr + 1
END
SELECT * FROM #MyTable
DROP TABLE #MyTable
Regards,
gova
August 23, 2005 at 9:00 am
What do you want to do exactly??
August 23, 2005 at 9:25 am
How can you tell your string is getting cut off?
is sql returning an error when you execute.
Please post your code, and someone can point out the error in 2 seconds or less.
PS, Make sure your output length in query analyzer is > 256 characters
Tools -> Options |Results |Maximum Character per column SB 8000
August 24, 2005 at 3:07 am
If you are doing this in Query Analyzer, make sure your settings are correct for output. Oh, Like Ray has pointed out already and of course Query--> results in text
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply