Thursday, March 22, 2012
Does RS recognize CHAR(13)?
Services. I have a SQL Server stored procedure that returns the address using
SQL like this
SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
+ Zip AS FullAddress
FROM ...
Assume the data is:
Address: 123 Main Street
Address2: Suite 3
City: Bellevue
State: WA
Zip: 98111
I assign the FullAddress field to a textbox in Reporting Services. I resize
it to be several lines high. The result I get is something like this:
123 Main StreetSuite3Bellevue
WA 98111
I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
double spaces. Does RS handle CHAR() output? Do I need to use separate fields?I've done this before but in the actual field expression not in the query.
Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
"Rick" wrote:
> I am making a query for mailing labels that I will generate in Reporting
> Services. I have a SQL Server stored procedure that returns the address using
> SQL like this
> SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> + Zip AS FullAddress
> FROM ...
> Assume the data is:
> Address: 123 Main Street
> Address2: Suite 3
> City: Bellevue
> State: WA
> Zip: 98111
> I assign the FullAddress field to a textbox in Reporting Services. I resize
> it to be several lines high. The result I get is something like this:
> 123 Main StreetSuite3Bellevue
> WA 98111
> I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> double spaces. Does RS handle CHAR() output? Do I need to use separate fields?|||Thanks. Your answer got me going in the right direction. I didn't get the
Chr(10) to work in the actual field expression, but chr(10) + chr(13) did.
For the benefit of future thread readers...I went a step further and created
a code block in the Report|Report Parameters command to make things a little
cleaner. It easily handles the common case where the second address line is
empty (Note: my query returns an empty string rather than a NULL if there is
no Address2). My code is this:
Function BuildAddress(ByVal A1 As String, ByVal A2 As String, ByVal C AS
String) As String
Dim strReturn As String
=Code.BuildAddress(Fields!Address.Value, Fields!Address2.Value,
Fields!CSZ.Value)
If A2.Length > 0 Then
strReturn += A2 + chr(10) + chr(13)
End If
strReturn += C
Return strReturn
End Function
And then I used the following expression in the textbox:
=Code.BuildAddress(
"David Bienstock" wrote:
> I've done this before but in the actual field expression not in the query.
> Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
> "Rick" wrote:
> > I am making a query for mailing labels that I will generate in Reporting
> > Services. I have a SQL Server stored procedure that returns the address using
> > SQL like this
> >
> > SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> > + Zip AS FullAddress
> > FROM ...
> >
> > Assume the data is:
> > Address: 123 Main Street
> > Address2: Suite 3
> > City: Bellevue
> > State: WA
> > Zip: 98111
> >
> > I assign the FullAddress field to a textbox in Reporting Services. I resize
> > it to be several lines high. The result I get is something like this:
> >
> > 123 Main StreetSuite3Bellevue
> > WA 98111
> >
> > I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> > double spaces. Does RS handle CHAR() output? Do I need to use separate fields?
Sunday, February 26, 2012
Does ALTER COLUMN effect the data?
I just want to change the length of 2 fields. They're CHAR and NVARCHAR type. I want to change the length from 50 to 75.
Will doing this effect the data? Will it delete the data in those columns? I just want to make sure before I do anything. The table contains about 2.5 million rows, so I don't want to mess anything up. I'm aware that the script may take a while to run and finish, but I'm only concerned about data integrity.
Here is my example script syntax to show what I'm planning to do:
Code Snippet
alter table credit alter column writeoffreason char(75) not null;
Does it matter what type of data is in the columns? For now, it's only CHAR and NVARCHAR.
I also read somewhere that you can increase length but cannot decrease without repercussions?
Thanks!
It will not delete, but SS has to allocate space in order to accomodate the CHAR one, because it has to pad with spaces to the right. This statement will not only affect metadata, it will affect actual space allocation. Check table fragmentation after it has finished.
AMB
|||thanks for the confirmation, i had thought so that the data integrity would remain intact.