September 25, 2013 at 1:13 pm
Hi all,
SqlServer 2008R2.
Anyone of you ever dealt with an issue when you're trying to concatenate several columns (including varchar(max)), the result is truncated?
I am going to provide an example of it. Sorry for the long text.
--drop table A;
CREATE TABLE A(
[col_1] [nvarchar](30) NOT NULL,
[col_2] [varchar](256) NULL,
[col_3] [varchar](max) NULL,
[col_4] [varchar](max) NULL)
;
insert into A (col_1, col_2, col_3, col_4)
values('AAA-111'
,'Vendor'
,'
The duties and responsibilities of a Database Administrator (DBA) make a long and
dynamically changing list, ranging from offering query tuning advice, to cutting stored
procedures, all the way through to system process design and implementation for high
availability. A DBA''s tasks, from day to day, are rarely constant; with one exception: the
need to ensure each and every day that any database in their charge can be restored and
recovered, in the event of error or disaster. This means that if a database, for whatever
reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA''s
responsibility to restore that database to the state it was in before the problem occurred,
or as close as is possible.
Of course, this doesn''t mean that a DBA is required to restore a database each and every
day, just that, if disaster does strike, the DBA must be prepared to deal with it, regardless
of when or why it occurs. If a DBA isn''t prepared, and significant data is lost or databases
become unavailable to end-users for long periods of time, then that DBA probably won''t
be in their job for too long. This is why a good, and tested, SQL Server backup and restore
plan must be at the top of every administrative DBA''s list of tasks.
'
,'
Each of these factors will help decide the types of backup required, how often they need
to be taken, how many days'' worth of backup files need to be stored locally, and so on. All
of this should be clearly documented so that all parties, both the DBAs and application/
database owners, understand the level of service that is expected for each database, and
what''s required in the plan to achieve it.
At one end of the scale, for a non-frontline, infrequently-modified database, the backup
and recovery scheme may be simplicity itself, involving a nightly full database backup,
containing a complete copy of all data files, which can be restored if and when necessary.
At the opposite end of the scale, a financial database with more or less zero tolerance to
data loss will require a complex scheme consisting of regular (daily) full database backups,
probably interspersed with differential database backups, capturing all changes since
the last full database backup, as well as very regular transaction log backups, capturing
the contents added in the database log file, since the last log backup. For very large
databases (VLDBs), where it may not be possible to back up the entire database in one
go, the backup and restore scheme may become more complex still, involving backup of
individual data files, for filegroups, as well as transaction logs. All of these backups will
need to be carefully planned and scheduled, the files stored securely, and then restored
in the correct sequence, to allow the database to be restored to the exact state in which it
existed at any point in time in its history, such as the point just before a disaster occurred.
'
);
When I run the following, all columns contain the right data.
select * from A;
However, when I try to concat the fields, the output is truncated
select col_1
,'Alert: Blah, blah, blah ....'
,'Attention:' + REPLICATE(' ', (20 - LEN('Attention:'))) +
CASE col_2
WHEN 'Vendor' THEN 'Not our fault'
ELSE 'Our fault'
END+ CHAR(13)+CHAR(10)
+'Col_1:' + REPLICATE(' ', (20 - LEN('Col_1:'))) + col_1 + CHAR(13)+CHAR(10)
+'Date:' + REPLICATE(' ', (20 - LEN('Date:'))) + CONVERT(VARCHAR,GETDATE()) + CHAR(13)+CHAR(10)
+'Out for:' + REPLICATE(' ', (20 - LEN('Out for:'))) + CONVERT(VARCHAR,DATEDIFF(MINUTE,GETDATE()-1,getdate()))+ ' minutes' + CHAR(13)+CHAR(10)
+'Description:' + REPLICATE(' ', (20 - LEN('Description:'))) + 'Is this a bug???' + CHAR(13)+CHAR(10)
+'Col_3:' + REPLICATE(' ', (20 - LEN('Col3'))) + col_2 + CHAR(13)+CHAR(10)
+'Col_4:' + REPLICATE(' ', (20 - LEN('Col_4'))) + col_3 + CHAR(13)+CHAR(10)
,''
,0
,''
from A
;
Anyone?
Thanks,
September 25, 2013 at 1:17 pm
I assume you are running this in SSMS and the results panel has the value truncated? That is because SSMS truncates long text. You can change the amount of text (up to a point). Tools -> Options -> Query Results -> Results to Grid or Text.
If you used these values as an insert or something like the value is just fine, it just doesn't get returned to the front end.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2013 at 1:23 pm
I've tried that before.
I sent the output to a file and here is what I get:
col_1
------------------------------ ---------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ----------- ----
AAA-111 Alert: Blah, blah, blah .... Attention: Our fault
Col_1: AAA-111
Date: Sep 25 2013 12:19PM
Out for: 1440 minutes
Description: Is this a bug???
Col_3:
The duties and responsibilities of a Database Administrator (DBA) make a long and
dynamically changing list, ranging from offering query tuning advice, to cutting stored
procedures, all the way through to system process design and implementation for high
availability. A DBA's tasks, from day to day, are rarely constant; with one exception: the
need to ensure each and every day that any database in their charge can be restored and
recovered, in the event of error or disaster. This means that if a database, for whatever
reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA's
responsibility to restore that database to the state it was in before the problem occurred,
or as close as is possible.
Of course, this doesn't mean that a DBA is required to restore a database each and every
day, just that, if disaster does strike, the DBA must be prepared to deal with it, regardless
of when or why it occurs. If a DBA isn't prepared, and significant data is lost or databases
become unavailable to end-users for long periods of time, then that DBA probably won't
be in their job for too long. This is why a good, and tested, SQL Server backup and restore
plan must be at the top of every administrative DBA's list of tasks.
Col_4:
Each of these factors will help decide the types of backup required, how often they need
to be taken, how many days' worth of backup files need to be stored locally, and so on. All
of this should be clearly documented so that all parties, both the DBAs and application/
database owners, understand the level of service that is expected for each database, and
what's required in the plan to achieve it.
At one end of the scale, for a non-frontline, infrequently-modified database, the backup
and recovery sc 0
(1 row(s) affected)
September 25, 2013 at 1:25 pm
FYI, the sample ddl you provided had an error
I think insert into A (col_1, col_2, col_3)
should really be insert into A (col_1, col_2, col_3, col4)
All that said, No truncation, simple mistake... try this:
select col_1
,'Alert: Blah, blah, blah ....'
,'Attention:' + REPLICATE(' ', (20 - LEN('Attention:'))) +
CASE col_2
WHEN 'Vendor' THEN 'Not our fault'
ELSE 'Our fault'
END+ CHAR(13)+CHAR(10)
+'Col_1:' + REPLICATE(' ', (20 - LEN('Col_1:'))) + col_1 + CHAR(13)+CHAR(10)
+'Date:' + REPLICATE(' ', (20 - LEN('Date:'))) + CONVERT(VARCHAR,GETDATE()) + CHAR(13)+CHAR(10)
+'Out for:' + REPLICATE(' ', (20 - LEN('Out for:'))) + CONVERT(VARCHAR,DATEDIFF(MINUTE,GETDATE()-1,getdate()))+ ' minutes' + CHAR(13)+CHAR(10)
+'Description:' + REPLICATE(' ', (20 - LEN('Description:'))) + 'Is this a bug???' + CHAR(13)+CHAR(10)
+'Col_3:' + REPLICATE(' ', (20 - LEN('Col3'))) + col_2 + CHAR(13)+CHAR(10)
+'Col_4:' + REPLICATE(' ', (20 - LEN('Col_4'))) + col_3 + CHAR(13)+CHAR(10)
+'Col_5:' + REPLICATE(' ', (20 - LEN('Col_4'))) + col_4 + CHAR(13)+CHAR(10)
,''
,0
,''
from A
;
Edit: updated code formatting to show my change, removed the OP quote.
-- Itzik Ben-Gan 2001
September 25, 2013 at 1:41 pm
My bad, I messed up.
Here is what I need to run, but the output is truncated:
drop table A;
CREATE TABLE A(
[col_1] [nvarchar](30) NOT NULL,
[col_2] [varchar](256) NULL,
[col_3] [varchar](max) NULL,
[col_4] [varchar](max) NULL)
;
insert into A (col_1, col_2, col_3, col_4)
values('AAA-111'
,'Vendor'
,'
The duties and responsibilities of a Database Administrator (DBA) make a long and
dynamically changing list, ranging from offering query tuning advice, to cutting stored
procedures, all the way through to system process design and implementation for high
availability. A DBA''s tasks, from day to day, are rarely constant; with one exception: the
need to ensure each and every day that any database in their charge can be restored and
recovered, in the event of error or disaster. This means that if a database, for whatever
reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA''s
responsibility to restore that database to the state it was in before the problem occurred,
or as close as is possible.
Of course, this doesn''t mean that a DBA is required to restore a database each and every
day, just that, if disaster does strike, the DBA must be prepared to deal with it, regardless
of when or why it occurs. If a DBA isn''t prepared, and significant data is lost or databases
become unavailable to end-users for long periods of time, then that DBA probably won''t
be in their job for too long. This is why a good, and tested, SQL Server backup and restore
plan must be at the top of every administrative DBA''s list of tasks.
'
,'
Each of these factors will help decide the types of backup required, how often they need
to be taken, how many days'' worth of backup files need to be stored locally, and so on. All
of this should be clearly documented so that all parties, both the DBAs and application/
database owners, understand the level of service that is expected for each database, and
what''s required in the plan to achieve it.
At one end of the scale, for a non-frontline, infrequently-modified database, the backup
and recovery scheme may be simplicity itself, involving a nightly full database backup,
containing a complete copy of all data files, which can be restored if and when necessary.
At the opposite end of the scale, a financial database with more or less zero tolerance to
data loss will require a complex scheme consisting of regular (daily) full database backups,
probably interspersed with differential database backups, capturing all changes since
the last full database backup, as well as very regular transaction log backups, capturing
the contents added in the database log file, since the last log backup. For very large
databases (VLDBs), where it may not be possible to back up the entire database in one
go, the backup and restore scheme may become more complex still, involving backup of
individual data files, for filegroups, as well as transaction logs. All of these backups will
need to be carefully planned and scheduled, the files stored securely, and then restored
in the correct sequence, to allow the database to be restored to the exact state in which it
existed at any point in time in its history, such as the point just before a disaster occurred.
'
);
select col_1
,'Alert: Blah, blah, blah ....'
,'Attention:' + REPLICATE(' ', (20 - LEN('Attention:'))) +
CASE col_2
WHEN 'Vendor' THEN 'Not our fault'
ELSE 'Our fault'
END+ CHAR(13)+CHAR(10)
+'Col_1:' + REPLICATE(' ', (20 - LEN('Col_1:'))) + col_1 + CHAR(13)+CHAR(10)
+'Date:' + REPLICATE(' ', (20 - LEN('Date:'))) + CONVERT(VARCHAR,GETDATE()) + CHAR(13)+CHAR(10)
+'Out for:' + REPLICATE(' ', (20 - LEN('Out for:'))) + CONVERT(VARCHAR,DATEDIFF(MINUTE,GETDATE()-1,getdate()))+ ' minutes' + CHAR(13)+CHAR(10)
+'Description:' + REPLICATE(' ', (20 - LEN('Description:'))) + 'Is this a bug???' + CHAR(13)+CHAR(10)
+'Col_3:' + REPLICATE(' ', (20 - LEN('Col3'))) + col_3 + CHAR(13)+CHAR(10)
+'Col_4:' + REPLICATE(' ', (20 - LEN('Col_4'))) + col_4 + CHAR(13)+CHAR(10)
,''
,0
,''
from A
;
September 25, 2013 at 1:52 pm
rightontarget (9/25/2013)
My bad, I messed up.Here is what I need to run, but the output is truncated:
drop table A;
CREATE TABLE A(
[col_1] [nvarchar](30) NOT NULL,
[col_2] [varchar](256) NULL,
[col_3] [varchar](max) NULL,
[col_4] [varchar](max) NULL)
;
insert into A (col_1, col_2, col_3, col_4)
values('AAA-111'
,'Vendor'
,'
The duties and responsibilities of a Database Administrator (DBA) make a long and
dynamically changing list, ranging from offering query tuning advice, to cutting stored
procedures, all the way through to system process design and implementation for high
availability. A DBA''s tasks, from day to day, are rarely constant; with one exception: the
need to ensure each and every day that any database in their charge can be restored and
recovered, in the event of error or disaster. This means that if a database, for whatever
reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA''s
responsibility to restore that database to the state it was in before the problem occurred,
or as close as is possible.
Of course, this doesn''t mean that a DBA is required to restore a database each and every
day, just that, if disaster does strike, the DBA must be prepared to deal with it, regardless
of when or why it occurs. If a DBA isn''t prepared, and significant data is lost or databases
become unavailable to end-users for long periods of time, then that DBA probably won''t
be in their job for too long. This is why a good, and tested, SQL Server backup and restore
plan must be at the top of every administrative DBA''s list of tasks.
'
,'
Each of these factors will help decide the types of backup required, how often they need
to be taken, how many days'' worth of backup files need to be stored locally, and so on. All
of this should be clearly documented so that all parties, both the DBAs and application/
database owners, understand the level of service that is expected for each database, and
what''s required in the plan to achieve it.
At one end of the scale, for a non-frontline, infrequently-modified database, the backup
and recovery scheme may be simplicity itself, involving a nightly full database backup,
containing a complete copy of all data files, which can be restored if and when necessary.
At the opposite end of the scale, a financial database with more or less zero tolerance to
data loss will require a complex scheme consisting of regular (daily) full database backups,
probably interspersed with differential database backups, capturing all changes since
the last full database backup, as well as very regular transaction log backups, capturing
the contents added in the database log file, since the last log backup. For very large
databases (VLDBs), where it may not be possible to back up the entire database in one
go, the backup and restore scheme may become more complex still, involving backup of
individual data files, for filegroups, as well as transaction logs. All of these backups will
need to be carefully planned and scheduled, the files stored securely, and then restored
in the correct sequence, to allow the database to be restored to the exact state in which it
existed at any point in time in its history, such as the point just before a disaster occurred.
'
);
select col_1
,'Alert: Blah, blah, blah ....'
,'Attention:' + REPLICATE(' ', (20 - LEN('Attention:'))) +
CASE col_2
WHEN 'Vendor' THEN 'Not our fault'
ELSE 'Our fault'
END+ CHAR(13)+CHAR(10)
+'Col_1:' + REPLICATE(' ', (20 - LEN('Col_1:'))) + col_1 + CHAR(13)+CHAR(10)
+'Date:' + REPLICATE(' ', (20 - LEN('Date:'))) + CONVERT(VARCHAR,GETDATE()) + CHAR(13)+CHAR(10)
+'Out for:' + REPLICATE(' ', (20 - LEN('Out for:'))) + CONVERT(VARCHAR,DATEDIFF(MINUTE,GETDATE()-1,getdate()))+ ' minutes' + CHAR(13)+CHAR(10)
+'Description:' + REPLICATE(' ', (20 - LEN('Description:'))) + 'Is this a bug???' + CHAR(13)+CHAR(10)
+'Col_3:' + REPLICATE(' ', (20 - LEN('Col3'))) + col_3 + CHAR(13)+CHAR(10)
+'Col_4:' + REPLICATE(' ', (20 - LEN('Col_4'))) + col_4 + CHAR(13)+CHAR(10)
,''
,0
,''
from A
;
What you posted is working for me. I am not getting any truncation.
-- Itzik Ben-Gan 2001
September 25, 2013 at 2:05 pm
Alan, not sure how it's possible, but I think I found a resolution.
I changed datatype of the col_1 from nvarchar(30) to varchar(30).
Thanks,
September 25, 2013 at 2:08 pm
When using the send results to file it is limited to the same settings as output to text. At least it was in 2005. I just tried and it seems to be limited to 256 characters no matter what that setting is.
I have to admit that I never use the results to file option. Can you just export your data instead?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply