Formatting Problem

I would like to change the background of a cell if its value is 'Test'
I have tried this thru both custom code and just thru a simple iif statement both which produce errors. I put the following iif statemnt in

=iif(Fields!change_type.value="Test","Cyan","Red")

and get the following error:

c:\variset4\reports_microsoft\ToolPredByOp.rdl The background color expression for the textbox 'CHANGE_TYPE' refers to the field 'change_type'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Any suggestions?disregard my last message - my line was correct - the problem was that Reporting services is Case sensitive. I should have had my field names in all caps. I found a good ei was trying to do at:

http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

if anyone is interested...|||

Hi.....

May be ur Prblm is Caps letter..or U Can also try Like this....
=iif(Fields!change_type.value="Test","Cyan","Red")
As
use ur Text Box Name instead of Field value.... ok write this Code in BackGround -- <Expression...>

=iif(ReportItems!textbox1.value="Test","Cyan","Red")

Best Regards......

Formatting Issues

Hi all,
Im using the custom code to format the string returned by the
stored procedure in the reports.
Public Function ResourceCategory(ByVal stat As String) As String
Dim str As String
Dim arr As String()
arr = stat.Split(",")
Dim i As Integer
For i = 0 To arr.Length -1
If arr(i) = "'PART'" Then
str = str & "Partner/"
End If
If arr(i) = "'ADM'" Then
str = str & "Administration/"
End If
If arr(i) = "'DIR'" Then
str = str & "Director/"
End If
If arr(i) = "'ENG'" Then
str = str & "Engineer/"
End If
if arr(i) = "'PM'" Then
str = str & "Project Manager/"
End If
if arr(i) = "'SE'" Then
str = str & "Senior Engineer/"
End If
if arr(i) = "'VALGRE'" Then
str = str & "Validation Engineer Greenville/"
End If
if arr(i) = "'VALSEN'" Then
str = str & "Senior Validation Engineer SFO/"
End If
if arr(i) = "'VALSFO'" Then
str = str & "Validation Engineer/"
End If
if arr(i) = "All" Then
str = str & "All/"
End If
Next
str = str.Remove(str.LastIndexOf("/"), 1)
Return str
End Function
But if the condition fails the report prints "#Error"
Can I in any way avoid this'
hanks In Advance,Provide an Else condition for anything that does NOT satisfy the other
conditions like "NA"... But if the function fails, you can not override the
error...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> Hi all,
> Im using the custom code to format the string returned by the
> stored procedure in the reports.
> Public Function ResourceCategory(ByVal stat As String) As String
> Dim str As String
> Dim arr As String()
> arr = stat.Split(",")
> Dim i As Integer
> For i = 0 To arr.Length -1
> If arr(i) = "'PART'" Then
> str = str & "Partner/"
> End If
> If arr(i) = "'ADM'" Then
> str = str & "Administration/"
> End If
> If arr(i) = "'DIR'" Then
> str = str & "Director/"
> End If
> If arr(i) = "'ENG'" Then
> str = str & "Engineer/"
> End If
> if arr(i) = "'PM'" Then
> str = str & "Project Manager/"
> End If
> if arr(i) = "'SE'" Then
> str = str & "Senior Engineer/"
> End If
> if arr(i) = "'VALGRE'" Then
> str = str & "Validation Engineer Greenville/"
> End If
> if arr(i) = "'VALSEN'" Then
> str = str & "Senior Validation Engineer SFO/"
> End If
> if arr(i) = "'VALSFO'" Then
> str = str & "Validation Engineer/"
> End If
> if arr(i) = "All" Then
> str = str & "All/"
> End If
> Next
> str = str.Remove(str.LastIndexOf("/"), 1)
> Return str
> End Function
> But if the condition fails the report prints "#Error"
> Can I in any way avoid this'
> hanks In Advance,|||Thanks Wayne ,
this is wat im trying to accomplish.
Hope u will help me out in this'
Im displaying the parameters selected by the user in a textbox.
The selection mode is multiple.
Im passing the code selected to the stored procedure,so it displays the
code.
if i give,
Parameters!Category.Value
So i wrote the code which i posted previously,
IS there a way i can load the description from the database '
Can i connect to the Database from the Code to achieve this'
"Wayne Snyder" wrote:
> Provide an Else condition for anything that does NOT satisfy the other
> conditions like "NA"... But if the function fails, you can not override the
> error...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> > Hi all,
> > Im using the custom code to format the string returned by the
> > stored procedure in the reports.
> >
> > Public Function ResourceCategory(ByVal stat As String) As String
> > Dim str As String
> > Dim arr As String()
> > arr = stat.Split(",")
> > Dim i As Integer
> > For i = 0 To arr.Length -1
> > If arr(i) = "'PART'" Then
> > str = str & "Partner/"
> > End If
> > If arr(i) = "'ADM'" Then
> > str = str & "Administration/"
> > End If
> > If arr(i) = "'DIR'" Then
> > str = str & "Director/"
> > End If
> > If arr(i) = "'ENG'" Then
> > str = str & "Engineer/"
> > End If
> > if arr(i) = "'PM'" Then
> > str = str & "Project Manager/"
> > End If
> > if arr(i) = "'SE'" Then
> > str = str & "Senior Engineer/"
> > End If
> > if arr(i) = "'VALGRE'" Then
> > str = str & "Validation Engineer Greenville/"
> > End If
> > if arr(i) = "'VALSEN'" Then
> > str = str & "Senior Validation Engineer SFO/"
> > End If
> > if arr(i) = "'VALSFO'" Then
> > str = str & "Validation Engineer/"
> > End If
> > if arr(i) = "All" Then
> > str = str & "All/"
> > End If
> > Next
> > str = str.Remove(str.LastIndexOf("/"), 1)
> > Return str
> > End Function
> >
> > But if the condition fails the report prints "#Error"
> > Can I in any way avoid this'
> > hanks In Advance,
>

formatting ints in table

Hi I am storing a customer code in my db that will always be 9 digits long. Sometimes the first digit is zero which gets knocked off when added in.

e.g code 050101111 is stored as 50101111

Can anyone suggest a way of formatting it so it will always be 9 digits long. A similar thing can be done in Excel, anyone have any ideas?

much appreciated

TomUse char(9) as the data type in SQL Server.|||thanks for your help

Tom

Formatting Date as integer

I want to for that format the date in YYYYMMDD and MMDDYY, with no '-' as data type is integer

I have used the following code (not the conversion function as I don’t need Hyphen '-')

for YYYYDDMM

SET @.DATE = CONVERT(INT,(CONVERT(VARCHAR(4),DATEPART(YYYY,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(MM,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(DD,GETDATE())) ))

& for MMDDYY

SET @.DATEUS = CONVERT(INT,(CONVERT(VARCHAR(3),DATEPART(MM,GETDATE()))+

CONVERT(VARCHAR(3),DATEPART(DD,GETDATE())) +

SUBSTRING(CONVERT(VARCHAR(4), DATEPART(YY,GETDATE())),3,4) ))

I am getting the result

YYYYMMDD= 200688

MMDDYY =8806

but i want result in

YYYYDDMM = 20060808

MMDDYY = 080806

note: I need to convert in integer, finally, caz database data type is integer.

can any one give me solution

waiting for quick reply

regards,

Anas

Just use convert:

select cast(convert(varchar(8),getdate(),112) as integer)

I use this for our date_dimension/calendar table surrogate keys all of the time.

|||thanx for your quick reply.

Formatting C# code, specifically curlys {}

1. For some reason, my Debug menu had many options missing. I found some
help that said go to Tools/Import & Export Settings and Choose Reset All. I
did it, and chose C# Developer and now my Debug menu is fully restored.
However, I did lose all of my other customzied settings, i.e. fonts, colors,
etc...
2. In my environment, before I did #1 above, when I keyed in two curly's
like this: {}, it would automatically move the ending curly to a new line
underneath. So, example:
I type this: {}
I get this after pressing enter:
{
}
For some reason, this no longer works.
3. I've changed settings in: Tools/Options/Text Editor/C#/Formatting.
Specifically the 'Automatically format completed block on }'. I'm not sure
if this is even the correct setting, but I tried anyway. I've also tried
the other settings in that screen and nothing happens.
Can someone advise if they've seen or fixed this problem? It worked for me
this morning, I reset my environment to get my Debug menu, and now it doesnt
work anymore.
Kind regards,
JrWhoops. Wrong group. Sorry.
"JrMcG" <JrM@.noemail.noemail> wrote in message
news:%23KRJGv$qGHA.4508@.TK2MSFTNGP04.phx.gbl...
> 1. For some reason, my Debug menu had many options missing. I found some
> help that said go to Tools/Import & Export Settings and Choose Reset All.
> I did it, and chose C# Developer and now my Debug menu is fully restored.
> However, I did lose all of my other customzied settings, i.e. fonts,
> colors, etc...
> 2. In my environment, before I did #1 above, when I keyed in two curly's
> like this: {}, it would automatically move the ending curly to a new line
> underneath. So, example:
> I type this: {}
> I get this after pressing enter:
> {
> }
> For some reason, this no longer works.
> 3. I've changed settings in: Tools/Options/Text Editor/C#/Formatting.
> Specifically the 'Automatically format completed block on }'. I'm not
> sure if this is even the correct setting, but I tried anyway. I've also
> tried the other settings in that screen and nothing happens.
> Can someone advise if they've seen or fixed this problem? It worked for
> me this morning, I reset my environment to get my Debug menu, and now it
> doesnt work anymore.
> Kind regards,
> Jr
>

Formatting a string from the code

I have a textbox that displays the amout of free disk space in megabytes.
I'm working on converting it to gigabytes and if it's less that one gig, I
want to display the string "Critical" bold and in red. Can this be done?
Thanks.This can certainly be done. Where do you want to display the text
'Critical'? If you have another textbox you would use an expression to
determine the value of that checkbox. for example if you were getting the
value from a dataset you would have a similar code to the following in the
expression editor:
=IIf(Fields!Number_Of_Megs < 1024,"","Critical")
Hope this helps
"chalexan" wrote:
> I have a textbox that displays the amout of free disk space in megabytes.
> I'm working on converting it to gigabytes and if it's less that one gig, I
> want to display the string "Critical" bold and in red. Can this be done?
> Thanks.|||Bret or anyone else,
Thanks for the reply. Any suggestions on changing the font's color to red
if it's critical and making it bold?
--Chad|||You will have to do this from the report designer. You can select the
textbox that the text is in and go to the textbox properties. (right click
and properties) From here you can select the format for this text box... A
nice feature is using the Expression Editor to select the font color. This
will allow you to use an IIF() statement to have the font red or black. You
can allow do this with the font weight.
Example of formatting font color in expression editor: (you would put this
in the format expression for the font-color in the designer)
=IIf(Fields!Number_Of_Megs < 1024,"black","red")
"chalexan" wrote:
> Bret or anyone else,
> Thanks for the reply. Any suggestions on changing the font's color to red
> if it's critical and making it bold?
> --Chad|||Bret,
Wow! I thought that the "IIf(" was a typo. That functionality can be very
useful. Thank you so much for explaining that to me.
--Chad

Formating number and code

Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.
I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)
The problem is that the function should return Integer and if the
value is zero I need to return empty string.
It does't say any thing about returning the string but when it trying
to use the format on a string it give me a worning and print #ERORR in
the cell.
is there a solution for this?
Do you know the syntax to format the number in the function before the
return?
(Sorry for the dumb question but I know C# not VB.net and there is no
intelisance in that editor).
Thanks a lot!On Jul 2, 8:35 am, nicknack <roezo...@.gmail.com> wrote:
> Hi, I have another question about code in RS.
> I have a table cell with FORMAT of "$#,##0,0".
> If I have any number in the cell I want to display it.
> If I have 0 (zero) I want the cell to be empty.
> I know how to do it with an expression:
> iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
> Now I want to do it with a code section:
> =Code.hideIfZero(Fileds!myFiled.Value)
> The problem is that the function should return Integer and if the
> value is zero I need to return empty string.
> It does't say any thing about returning the string but when it trying
> to use the format on a string it give me a worning and print #ERORR in
> the cell.
> is there a solution for this?
> Do you know the syntax to format the number in the function before the
> return?
> (Sorry for the dumb question but I know C# not VB.net and there is no
> intelisance in that editor).
> Thanks a lot!
You will most likely want to return a null (or Nothing in terms of
SSRS-VB.NET), that way there is no error when trying to convert an
empty string to an integer/decimal/etc. Or you could try using
InScope. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||you could add 2 more fields, one with the formatted text, and another
that tests whether the original is 0, then displays an empty string or
the formatted field.

Formating bound data

How do I change, or add to, the following code so as to be able to format the display:

this.txtAppointmentDate.DataBindings.Add(new Binding ("Text", dsData.Tables["Results"],"AppointmentDate")

The data is stored in SQL Server as datetime and I want only the date portion to show in the TextBox.

Thanks in advance

Hi,

thats more a GUI related question, but anyway:

http://www.codeproject.com/vb/net/databindingconcepts.asp

HTH;, Jens Suessmeyer.

http://ww.sqlserver2005.de

Formating a number in VB code

Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.

I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)

Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)

The problem is that the function should return Integer and if the value is zero I need to return empty string.

It does't say any thing about returning the string but when it tryingto use the format on a string it give me a worning and print #ERORR inthe cell.

is there a solution for this?
Do you know the syntax to format the number in the function before the return?
(Sorry for the dumb question but I know C# not VB.net and there is no intelisance in that editor).

Thanks a lot!

Hi,

had a similar problem recently.

Try returning "Nothing" (it's a keyword, sth like null), instead of empty string.

|||

Hi,

I tried that but if I return a number it showen as 0.0 instead of an empty cell :(

Any more ideas?

Thanks.

|||

Hmm,

it works from me. However, I set the value as an expression "Iif(...)" no custom functions defined.

And I've set the "Format" property of that cell to "n2".

|||

Well, its didn't worked for me. maybe it because I'm useing Cutom code function to return the value.

I also trued th N2 format but same resualt.

Thanks for the help!

Format Y axis label as percentage

I have entered the code as P0 when formatting my Y axis to show 0% to 100%. This showed me percent values as 0% to 10,000%. Then after reading the forums I put the major gridline interval as 0.2 to get jumps of 20% but this just puts a lot of lines on the chart as 0,0.2,0.4 till 10,000%.

How can i fix this one ..

Thanks

Kiran

Kiranvukkadala wrote:

I have entered the code as P0 when formatting my Y axis to show 0% to 100%. This showed me percent values as 0% to 10,000%. Then after reading the forums I put the major gridline interval as 0.2 to get jumps of 20% but this just puts a lot of lines on the chart as 0,0.2,0.4 till 10,000%.

How can i fix this one ..

Thanks

Kiran

Have you set the minimum value on the scale to zero and the maximum value on the scale of the Y-axis to 100?

|||

Yes I have done so. I have tried removing that too

No luck

|||

Possibly the issue is your data. What is the maximum value of your data? Have you divided this into your field?

For example, if you try to display change as a fraction of a dollar:

Then one penny should be 1% of a dollar. To get 1%, you take 1/100 = .01 (1%)

If you didn't divide by 100, however, then one penny would be 1.00 (100%).

A dime would be 1000% and so on.

One dollar would be BEHOLD 10000% (There's that 10000 percent).

Most likely you have just put the field as raw data, not as a percentage and expected the format expression to do the division for you.

Try dividing by 100!!!!

|||

I have got a stacked chart and on the data fields I have put in 2 fields

% received and % latewhic combine together to give 100%.

The data points is actuallys et to give teh actual counts of these fields.

So would this count as raw data?

If I divide this percentage number by 100 it goes really small on the graph but the scale remains the same. How can I modify the scale.

I appreciate the time.

Kiran

|||

Oh I thought you were saying your data was going to 10,000%. Try changing your Y-scale to a minimum of zero and maximum of 1.

|||

Thanks for the replies.

I have tried that possibility too. This option is successfull in creating the axis from 1% to 100% but the data itself goes wrong. There is no split between the received and late numbers and its either 100% received or 100% late. I dont know why it does that. My assumption is that since maximum value of 1 is specified only the first 1% of the data is displayed.

Kiran

format in pdf

Hy,

I would like to convert my report in pdf. Do you have the code to convert it? I found a lot of code for crystal report but not for Reporting service.

thank you

oolon

In fact, I try to use this kind of code

ReportingService rs = new ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] ResultStream;

string[] StreamIdentifiers;

string OptionalParam = null, filename = "NorthwindCustomers.pdf";

ParameterValue[] optionalParams = null;

Warning[] optionalWarnings = null;

ResultStream = rs.Render("/Northwind Customers", "PDF", null,

"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", null, null,

null, out OptionalParam, out OptionalParam, out optionalParams,

out optionalWarnings, out StreamIdentifiers);

// Creating a verbatim string.

FileStream stream = File.OpenWrite(@."C:\Articles\SQL Server Reporting

Services\SourceCode\RSWebServiceXS\NorthwindCustomers\" + filename);

stream.Write(ResultStream, 0, ResultStream.Length);

stream.Close();

But my code doesn't recognize The class Reporting Service. Why? i put the librairies :

using System;

using System.IO;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

What librairy's missing?

Thank you

|||You need to create the proxy class, if you haven't already done this. Take a look at this page for instructions:

http://msdn2.microsoft.com/en-us/library/ms155134.aspx

If you use the Web Reference method, the proxy classes are generated in the default namespace of your project with the reference name you specified in the Web Reference tool.

Format function in SQL

Hi ... i'm sorry to hassle this user group but i have an urgent need
for some code - i have tried and tried to find a solutionn elsewhere -
but the problem is i don't really know what i am looking for.

What is the equivalent SLQ code for the below statement which works in
MS Access?

Format(7,"00")

Result: 07

or alternatively ...

what i am actually trying to do is return the string yyyymm based on
the current date. eg 200506 (June 2006)

Is there a simple way of doing this?

The code i am currently using is

CONVERT (char, DATEPART(yyyy, GETDATE())) + CONVERT (char, DATEPART(mm,
GETDATE()))

Result: 2005 !!!!

At least i thought i would get 20056. But obv what i am aiming for is
200506.

Thanks in advance.

TCSee the various formats under CONVERT in Books Online:

select convert(char(6), current_timestamp, 112)

But in general, it's better to format output in the front end, not in
the database.

Simon|||To format a number as text with a leading zero, you can use the
following trick:

SELECT RIGHT('0'+CONVERT(varchar(2),YourNumber),2)

To get the year and month of a date, formatted as "yyyymm" you can use:

SELECT CONVERT(varchar(4),YEAR(GETDATE()))
+RIGHT('0'+CONVERT(varchar(2),MONTH(GETDATE())),2)

or:

SELECT CONVERT(varchar(6),GETDATE(),112)

By the way, your code doesn't return '2005', as you think; it returns:
'2005 6 '
This is because the char data type has a fixed length (it doesn't trim
trailing blanks) and because the default size of a char is 30.

For more informations about datetime data types, see:
http://www.karaszi.com/SQLServer/info_datetime.asp

Razvan|||thanks heaps - the SELECT CONVERT(varchar(6),GETDATE(),1*12) worked a
treat!

exactly what i needed

cheers,

TC|||(tcumming@.smorgonsteel.com.au) writes:
> Hi ... i'm sorry to hassle this user group but i have an urgent need
> for some code - i have tried and tried to find a solutionn elsewhere -
> but the problem is i don't really know what i am looking for.
> What is the equivalent SLQ code for the below statement which works in
> MS Access?
> Format(7,"00")

While you already have gotten help with your urgent needs, permit me
to point out that Functions->String Functions in the T-SQL Reference
of Books Online is a good place to start. That and the CAST and
CONVERT topic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Format for a bigint parameter in Raiserror

Having a small problem with RAISERROR that I've isolated to a small code sample:

The objective is to have the message string in Raiserror correctly report the value of a parameter that is a bigint. The following extract shows the problem:

ALTER PROCEDURE [dbo].[pr_Test]

@.SomeNumber bigint

AS

BEGIN

SET NOCOUNT ON;

RAISERROR('Test Error Number: %d.',16,1,@.SomeNumber)

END

As written, when the procedure is executed with the value 1 passed in the @.SomeNumber parameter, the result is:

Msg 2786, Level 16, State 1, Procedure pr_Test, Line 19

The data type of substitution parameter 1 does not match the expected type of the format specification.

If the datatype for @.SomeNumber is changed to int, then the procedure executes correctly and reports 'Test Error Number: 1'. Any suggestions on how to fix this problem?

Regards,

Flavelle

There seems to a limitation with the numeric data type while using the Raiserror statement.

My suggestion is to cast the bigint to a varchar Here is the modified code block

alter PROCEDURE [dbo].[pr_Test]

@.SomeNumber BIGINT

AS

BEGIN

DECLARE @.NewNumber VARCHAR(1000)

SET @.NewNumber=CAST(@.SomeNumber AS VARCHAR(100))

SET NOCOUNT ON;

RAISERROR('Test Error Number: %s',16,1, @.NewNumber)

END

|||

Have you looked at the definition for RAISERROR in Books Online? It says bigint is not a valid datatype for that parameter.

argument

Is the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4, char, varchar, binary, or varbinary. No other data types are supported.

|||yes, i misread your post, and yes, casting/converting to a varchar datatype is a simple fix/workaround.|||

My thanks to you both - brokenrulz for the solution to the problem and Greg for reminding me that I have to remember to read the ENTIRE help file. Your reference to the supported datatypes for the parameters is deeply buried within the help topic.

Regards,

Flavelle

|||

Hello,

Help files have been updated on July 2006 (http://msdn2.microsoft.com/en-us/library/ms178592.aspx) and it says "To convert a value to the Transact-SQL bigint data type, specify %I64d" (the letter before 64 begin a capital i).

Not need to cast the bigint into an nvarchar. I tested under SQL 2005 + SP (version 9.00.3054.00) and it works great.

Laurent

|||I was facing the same problem and your suggestion saved the day for me. A big thanks.

Format for a bigint parameter in Raiserror

Having a small problem with RAISERROR that I've isolated to a small code sample:

The objective is to have the message string in Raiserror correctly report the value of a parameter that is a bigint. The following extract shows the problem:

ALTER PROCEDURE [dbo].[pr_Test]

@.SomeNumber bigint

AS

BEGIN

SET NOCOUNT ON;

RAISERROR('Test Error Number: %d.',16,1,@.SomeNumber)

END

As written, when the procedure is executed with the value 1 passed in the @.SomeNumber parameter, the result is:

Msg 2786, Level 16, State 1, Procedure pr_Test, Line 19

The data type of substitution parameter 1 does not match the expected type of the format specification.

If the datatype for @.SomeNumber is changed to int, then the procedure executes correctly and reports 'Test Error Number: 1'. Any suggestions on how to fix this problem?

Regards,

Flavelle

There seems to a limitation with the numeric data type while using the Raiserror statement.

My suggestion is to cast the bigint to a varchar Here is the modified code block

alter PROCEDURE [dbo].[pr_Test]

@.SomeNumber BIGINT

AS

BEGIN

DECLARE @.NewNumber VARCHAR(1000)

SET @.NewNumber=CAST(@.SomeNumber AS VARCHAR(100))

SET NOCOUNT ON;

RAISERROR('Test Error Number: %s',16,1, @.NewNumber)

END

|||

Have you looked at the definition for RAISERROR in Books Online? It says bigint is not a valid datatype for that parameter.

argument

Is the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4, char, varchar, binary, or varbinary. No other data types are supported.

|||yes, i misread your post, and yes, casting/converting to a varchar datatype is a simple fix/workaround.|||

My thanks to you both - brokenrulz for the solution to the problem and Greg for reminding me that I have to remember to read the ENTIRE help file. Your reference to the supported datatypes for the parameters is deeply buried within the help topic.

Regards,

Flavelle

|||

Hello,

Help files have been updated on July 2006 (http://msdn2.microsoft.com/en-us/library/ms178592.aspx) and it says "To convert a value to the Transact-SQL bigint data type, specify %I64d" (the letter before 64 begin a capital i).

Not need to cast the bigint into an nvarchar. I tested under SQL 2005 + SP (version 9.00.3054.00) and it works great.

Laurent

|||I was facing the same problem and your suggestion saved the day for me. A big thanks.

format expression is being ignored in table cell

I am consolidating multiple reports into one and wish to set the formatting
on the cell dependent upon the data.
The following code does not work
= iif(Parameters!RevenueColumns.Value=1,
"#,#"
,"")
&
iif(Parameters!RevenueColumns.Value=2,
"N2"
,"")
&
iif(Parameters!RevenueColumns.Value=3,
"N2"
,"")
When RevenueColumns is 1, the number is being quoted to about 20 decimal
places.
There is nothing in 'format' for the column or row, this is the only place
where format is set. Even if I change it back to just "#,#" it is being
ignored but i know it is accepting changes as i have varied the colour. I
have also tried N0 as the format and also 0.0 but nothing seems to change it,
even changing it back to the original value has not solved the issue. I have
deleted and redeployed the report, cleared out my IE cache and rebooted but
to no avail.
Can anyone help?
thanksthe numeric and the two null strs are being converted to a string, on which
it is not able to perform the formatting
coerce my arce
"adolf garlic" wrote:
> I am consolidating multiple reports into one and wish to set the formatting
> on the cell dependent upon the data.
> The following code does not work
> => iif(Parameters!RevenueColumns.Value=1,
> "#,#"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=2,
> "N2"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=3,
> "N2"
> ,"")
> When RevenueColumns is 1, the number is being quoted to about 20 decimal
> places.
> There is nothing in 'format' for the column or row, this is the only place
> where format is set. Even if I change it back to just "#,#" it is being
> ignored but i know it is accepting changes as i have varied the colour. I
> have also tried N0 as the format and also 0.0 but nothing seems to change it,
> even changing it back to the original value has not solved the issue. I have
> deleted and redeployed the report, cleared out my IE cache and rebooted but
> to no avail.
> Can anyone help?
> thanks

format datetime without characters

I need to convert the date format with an output of yyyymmdd'
I have tried the following code but it doesn't produce the desired output
CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
Any suggestions?
Thanks in advanceThat should work if supp_creation_date is a DATETIME or SMALLDATETIME. What
result do you get? Can you post some code to reproduce it? What version of
SQL Server? Here's an example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112
)
Result:
20050502
(1 row(s) affected)
If you get something else then I'd guess that in the context in which you
are using it the result is being implicitly cast to some other datatype.
David Portas
SQL Server MVP
--|||That seems to work for me:
Declare @.Date DateTime
Set @.Date = Current_TimeStamp
Select Convert(VarChar(8), @.Date, 112)
Produces:
20050502
What output are you getting?
Thomas
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry,
What is the type of column [supp_creation_date]?. If it is not a datetime
then you have to convert it to, before using the statement you posted.
Example:
declare @.s varchar(10)
set @.s = '05/02/2005'
select convert(char(8), convert(datetime, @.s, 101), 112)
go
-- this works
select convert(char(8), getdate(), 112)
go
AMB
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry, Your column in your table must not be a datetime... you'll have to
cast it to a datetime first.
Try this
Select CONVERT(VARCHAR(8), Cast(supp_creation_date As DateTime) , 112 )
From YourTable
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Does it produce the proper format in Query Analyzer? This is always the
first place to try stuff out, as QA is very good not to insert it's own
formatting to the output:
create table test
(
supp_creation_date datetime
)
insert into test
select getdate()
go
select CONVERT(VARCHAR(8),supp_creation_date , 112 )
from test
returns:
20050502
However, if this value gets put back into a datetime variable before the
client recieves it, or the client puts it inot a date container, the
formatting goes away. Formatting only works on textual values, not date
values.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance

Format Dates in TSQL - My code below

Howdy,
Is this proc:
SELECT ID, CID, dtDate, strTime, dtRSVP,
CASE
WHEN dtRSVP > 1/1/1900 THEN
'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
ELSE
NULL
END AS niceRSVP
FROM tblDates
the best solution to get these results:
ID EID dtDate strTimes
dtRSVP niceRSVP
3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
00:00:00 RSVP by 11/25/2005
4 4 2005-12-02 00:00:00 12-4 PM
1900-01-01 00:00:00 NULL
Basically, I want the dates formatted like 11/25/2005, and empty date fields
to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP fields, but I
made the niceRSVP field to compensate.
Please advise!
Thanks!
David Lozzi
Web Applications Developer
dlozzi@.(remove-this)delphi-ts.comDavid,
Is it required to be a NULL or the text NULL. If the latter, the following
will work. See as example:
CREATE TABLE TBL_DATES
(ID INT NOT NULL,
DTVAL DATETIME )
GO
INSERT TBL_DATES(ID,DTVAL)
VALUES(1, '10/12/05')
INSERT TBL_DATES(ID,DTVAL)
VALUES(2, '')
INSERT TBL_DATES(ID)
VALUES(3)
GO
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
> Howdy,
> Is this proc:
> SELECT ID, CID, dtDate, strTime, dtRSVP,
> CASE
> WHEN dtRSVP > 1/1/1900 THEN
> 'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
> as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
> ELSE
> NULL
> END AS niceRSVP
> FROM tblDates
> the best solution to get these results:
> ID EID dtDate strTimes dtRSVP
> niceRSVP
> 3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
> 00:00:00 RSVP by 11/25/2005
> 4 4 2005-12-02 00:00:00 12-4 PM 1900-01-01 00:00:00
> NULL
> Basically, I want the dates formatted like 11/25/2005, and empty date
> fields to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP
> fields, but I made the niceRSVP field to compensate.
> Please advise!
> Thanks!
> --
> David Lozzi
> Web Applications Developer
> dlozzi@.(remove-this)delphi-ts.com
>
>|||Or this with NULL.
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN
NULLIF('01/01/1900',DTVAL) ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Or even more succinctly:
SELECT
niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
FROM
tblDates
A null value in dtRSVP will pass through CONVERT as a null, and adding
anything to a null equals null.
JR
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Jim,
How does this code account for the NULL if 01/01/1900?
HTH
Jerry
"Jim Ross" <jratwork_at_hotmail.com@.nowhwere.com> wrote in message
news:OMTaUPfyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or even more succinctly:
> SELECT
> niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
> FROM
> tblDates
> A null value in dtRSVP will pass through CONVERT as a null, and adding
> anything to a null equals null.
> JR
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Format Date

Don't know why this has to be so difficult and why MS can't give us more date
formats to begin with . . . what is the format code for monthname, day year.
For example: April, 4 2008.
I know I can write an expression to do this using datepart, but isn't there
a simple format code to use like Crystal has.On Apr 4, 1:36=A0pm, Anonymous <Anonym...@.discussions.microsoft.com>
wrote:
> Don't know why this has to be so difficult and why MS can't give us more d=ate
> formats to begin with . . . what is the format code for monthname, day yea=r.
> For example: April, 4 2008.
> I know I can write an expression to do this using datepart, but isn't ther=e
> a simple format code to use like Crystal has.
=3D Format (Fields!FieldName, "MMMM d, yyyy")

Format Code

I can't seem to get the format codes to work in RS 2005. I assign one to a
field, but it just ignores it and doesn't do any formatting. Is this a 2005
bug? Am I missing a trick?
Thanks,
Leon.Where are you assigning it and how. What type of field is it and what
format are you wanting to give to it?
"Leon Chuck Gosslin" <LeonChuckGosslin@.discussions.microsoft.com> wrote in
message news:61B383DF-BBDB-45C6-9026-C72363B8D529@.microsoft.com...
>I can't seem to get the format codes to work in RS 2005. I assign one to a
> field, but it just ignores it and doesn't do any formatting. Is this a
> 2005
> bug? Am I missing a trick?
> Thanks,
> Leon.|||Give us an example such that we can say that where are you missing
Regards
Raj Deep.A
Ben Watts wrote:
> Where are you assigning it and how. What type of field is it and what
> format are you wanting to give to it?
> "Leon Chuck Gosslin" <LeonChuckGosslin@.discussions.microsoft.com> wrote in
> message news:61B383DF-BBDB-45C6-9026-C72363B8D529@.microsoft.com...
> >I can't seem to get the format codes to work in RS 2005. I assign one to a
> > field, but it just ignores it and doesn't do any formatting. Is this a
> > 2005
> > bug? Am I missing a trick?
> >
> > Thanks,
> >
> > Leon.