February 2, 2007 at 4:16 pm
Can QA be set to deliver a longer text stream in the Query Result? My result is being truncated. I tried expanding the Max characters per column, but that didn't change anything. Then I tried sending the results to a text file, but the truncation still occurs.
I don't see anything in BOL about the max length of QA results. Does anyone have any info on this?
Elliott
February 2, 2007 at 4:18 pm
How many characters are you talking about per row?
UPDATE: Forgot to ask....are you SURE the data is really in the field? Run this against the column you are having problems with:
SELECT MAX(DATALENGTH(columnname))
FROM tablename
-SQLBill
February 2, 2007 at 4:23 pm
I'm expecting a concatenated text string of about 4000 characters in length
February 2, 2007 at 4:52 pm
Try Tools -> Options -> Results tab -> Maximium characters per column...
see if that works?
February 2, 2007 at 5:10 pm
I've written a proc which captures the column names and datatypes for a db and table input to the proc. The proc concatenates these "words" into the desired text string.
The proc works fine, but for a very wide table (108 columns) with long column names, the QA just poops out and truncates my text string.
Increasing the Max character per column setting does not affect this result.
Thanks,
Elliott
February 2, 2007 at 5:45 pm
Change your MAX CHARACTER PER COLUMN setting to 8192 and change our result set to GRID MODE...
SQL will not display > 8000 characters per column....
MohammedU
Microsoft SQL Server MVP
February 3, 2007 at 8:17 am
Mohammed,
Thanks. I'm not sure why we're getting different results. I changed the Max Char per Column setting to 8192 and set the results to Grid. On my SQL 2000 system, the result is exactly 2000 characters, which truncates the expected result string.
I then tried re-setting the results to a file, but the resulting file(string) was truncated in exactly the same place.
Normally, 2000 characters would be more than enough. But in this case I'm expecting a string of about 3000 characters.
Elliott
February 5, 2007 at 9:12 am
Is the result that is being truncated produced by concatenating two or more values togther? If so, can you post the actual query?
February 5, 2007 at 9:31 am
Yes, the query result is a concatenation of string values. Here is the stored procedure that takes a table name and produces a list of the column names and datatypes:
CREATE PROCEDURE admin_Write_Columns_Datatypes
@db varchar(50),
@tb varchar(50)
AS
Set Concat_Null_Yields_Null off
Declare @sqlcmd varchar(8000)
Set @sqlcmd='
Declare @columns varchar(2000)
Declare @column varchar(40)
Declare @datatype varchar(20)
Declare @datalength varchar(10)
Declare curColumn Cursor LOCAL READ_ONLY
For
Select column_name, data_type, character_maximum_length from ' + @db + '.Information_schema.columns
where table_name like ' + Char(39) + @tb + Char(39) + '
/************************************************************************************************
The line of code below can be modified to exclude unwanted columns
************************************************************************************************/
and column_name NOT IN(' + Char(39)+ 'dRev' + Char(39) + ', ' + Char(39)+ 'sRev' + Char(39) + ')
Open curColumn
Fetch Next from curColumn into @column, @datatype, @datalength
WHILE @@FETCH_STATUS=0
BEGIN
If @datalength is null
Set @columns=@columns + @column + ' + Char(39) + ' ' + Char(39) + ' + @datatype +' + Char(39) + ', ' + Char(39) + '
ELSE
Set @columns=@columns + @column + ' + Char(39) + ' ' + Char(39) + '+ @datatype + ' + Char(39) + '(' + Char(39) + ' + @datalength + ' + Char(39) + '), ' + Char(39) + '
Fetch Next from CurColumn into @column, @datatype, @datalength
END
Close curColumn
Deallocate curColumn
Set @columns=Left(@columns,(Len(@columns)-1))
PRINT @columns'
exec(@sqlcmd)
GO
The query result for a 108 column table truncates right in the middle of a column named "iPrevDayCount". For this reason, it seems like a problem with the length of the result string, rather than any logic in the query.
Elliott
February 5, 2007 at 9:40 am
The problem is with the first DECLARE in the dynamic code:
Declare @columns varchar(2000)
change that to
Declare @columns varchar(8000)
February 5, 2007 at 9:59 am
Oh No!
I feel like that person in the Alaska Airlines commercial: "Wanna Get Away??"
I thought that it was the Query Analyzer that was limiting my result.
Thanks for the help,
Elliott
February 6, 2007 at 2:56 pm
That's why it is SO helpful to have the code posted and why we ask for it so often.
-SQLBill
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply