check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanksThe IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
--
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> > How can I check one of my db backups result is vaild in vb or transact
> > sql?
> >
> > For example
> >
> > if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> > 'restore it
> > else
> > ' send a message
> > End if
> >
> > thanks
> >
> >
> >
>

Check if a caractere exists in a SQL field

Hi everybody ,
I would like with a Stored procedure check if a caracter exists in a string stored in a SQL server field.
Example :
My field contains the caracters "ABCDEF" ,
I would like to check if "C" is in this string.
Tank Uselect charindex('C', 'ABCDEF') which would return 3. If the first expression is not found in the second, it returns zero.|||Thanks joan but i would like to make the expression 2 a variable wich contains my column name

Remeber the syntax must respect syntax of stored procedure

This is my stored procedure code :
@.level is the caracter i search
@.droits is the caracters contained in my field

IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
IF NOT SELECT charindex(@.Level, @.droits)


BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END
SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END

can you help me please ?|||DECLARE @.pos
IF (@.Level <> "nothing")
BEGIN
SELECT @.droits=droits FROM USERS WHERE UserID=@.UserID
SELECT @.pos = charindex(@.Level, @.droits)
IF @.pos > 0
BEGIN
SELECT Progress=2,Errormsg="Pas l'droits."
RETURN
END

SELECT @.AccessNumber = @.AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(), AccessNumber=@.AccessNumber WHERE SessionID=@.SessionID
SELECT Progress=0,userID=@.userID
RETURN
END|||It works

Thank U guy : ))

Check for Primary Key before Inserting New Record

Hi,

Can someone please tell me the best practices for checking the primary key field before inserting a record into my database?

As an example I have created an asp.net page using VB with an SQL server database. The web page will just insert two fields into a table (Name & Surname into the Names table). The primary key or the Names table is "Name". When I click the Submit button I would like to check to ensure there is not a duplicate primary key. If there is return a user friendly message i.e. A record already exisits, if there no duplicate, add the record.

I guess I could use try, catch within the .APSX page or would a stored procedure be better?

Thanks

Brett

one way you could do this is write a stored proc where you can check :

CREATE PROC ...

@.intResult INT OUTPUT

SET @.intResult = 0

IF NOT EXISTS (SELECT <col> FROM <table> WHERE <condition>

BEGIN

-- do the insert here

-- SET @.intResult to 1

END

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

|||

Thanks for the information.

Please can you let me know how can I check in my ASP.NET page the value of intResult?

Regards,

Brett

|||

ndinakar wrote:

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

Return Codes are not needed in languages supporting exceptions. Instead, throw an exception from your SP within SQL Server ...

IF EXISTS(SELECT * FROM <tb> WHERE <pk> = @.pk) BEGIN
RAISERROR('A Document with a number of %s already exists.', 16, 2, @.pk)
RETURN
END

In the ASP code, use a TRY/CATCH around the Execute method. If the error returned is a user defined error (50000), wrap the message in your own exception and send it directly back to the client.

|||

Thanks again for your help, could you please post me an example of how the code for the Try/Catch would look in ASP.NET using VB.
Regards,

Brett

|||check out the recent articles in my blog..I have some sample code that uses Try/Catch block's.
|||

I have read your article but I still don't understand how I can check the RAISERROR from the stored procedure. I then want to display an error to the user saying for example "Duplicate Name Found" if the RAISERROR occurs but if the record is added I would like a message saying "Record Added".

Are there any book you can recommend that deal with ASP.NET & SQL Stored Procedures.

|||I dont have sample code but am sure you;d find it if you google.

Check for existence of specific value in a dataset

How do I check for the existence of a specific value in a column in a
dataset? For example, I have 'dataset' with 'columnA' and I want to find out
if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
but I'm having trouble. Thanks.
StephanieOn May 22, 12:42 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> How do I check for the existence of a specific value in a column in a
> dataset? For example, I have 'dataset' with 'columnA' and I want to find out
> if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
> but I'm having trouble. Thanks.
> Stephanie
The closest thing to the functionality you want is with and expression
similar to this:
=Max(iif(Fields!columnA.Value = 'valueA', 1, 0))
So if the value exists in the column values, the expression will
return a 1. Otherwise, it will return a 0.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Check Constraint and DTS Problem

Hi,

When using DTS, is it possible trap errors caused by check-constraints?

For example, I have a check constraint in the first column and another in the second column. If my data does not comply to the rules set in the first column, is there a way for me to check whether my data satisfy the second columns condition?

Reason being that I would like to validate all my columns and log all the errors of each field ( if any ). But using check-constraints, DTS would only test until the column that has error, and then throws and exception without checking the rest.

If it is not possible, some expert advice on this issue would be greatly appreciated. :)

Also, is there a way for me to know which column and row DTS is processing?

Thanks!

regards,
Tuantry to anticipate rather than actuate

I mean, try to check first if rows you are going to insert conflict with any constraint and log them accurate

Check checkboxes

Hi,

I have two web pages in one web page i have 5 check boxes. For example if the user checks the Checkbox1, checkbox2 and clicks on button.

On the button click I am storing the selected checkboxes value in database lke the following:

Year Options

xxx 1

xxx 2

in the above format( user selectes checbox1, check box 2).

And in the Second Web page I am showing the 5 checkboxes but in this web page I need to check the first and second checkboxes on the page load because user selectes those two check boxes in the first web page.

my select query returning the results like this:

Options

1

2

based on options I have to check those corresponding check boxes in the second web page.

How to achive this.

Thanks in advance

Saving a user's CheckBoxList selection and re-populating the CheckBoxList from saved data:http://www.mikesdotnetting.com/Article.aspx?ArticleID=53

|||

Hello,

You need to loop thru your result set and set the checkboex to Checked on Page_Load event.

For example:

This is how you get the values and set the checkboxes to Checked.

SqlCommand cmd = new SqlCommand("Select OPtions from <Yourtable>...");

SqlDataReader reader =cmd.ExecuteReader();while( reader.Read())

{

int col = reader.GetOrdinal("Options");

int checkValue = reader.GetInt32(col);switch(checkValue)

{

case 1:

CheckBox1.Checked =true;

case 2:

CheckBox2.Checked =true;

... etc

}

}

Optionaly you can use Page.FindControl() method to get to the checkboxes (instead of using switch statement). The problem with that is that FindControl does not loop thru the Page hierarchy so if you page is complex you may have to write your own FindControl method.

Once you have that method you can call it from the Page_Load event, like this:

if (!IsPostback)
{

// Call the SetCheckBoxes method here ...

}

Hope this helps

regards,

G

Chart URL Action? Easy question ... I hope!

I am trying to create a chart action to do a URL drill into bar chart. But I
can not figure out how to get the name of the clicked bar.
Example: If the diagram below were a chart, when I click on the "two" bar, I
want to create the url: http://www.bonzo.com/foo.aspx&PICKEDBAR=Two
One == Two ==== Three ========
Is it possible to do this?Your Jump to navigate action you can use the RS global collections. Assuming
that One, Two, etc. are category groups, you can use Fields!<your database
field>.Value
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"billd" <billd@.discussions.microsoft.com> wrote in message
news:43521AC0-1DE9-4980-B579-16F0DF651586@.microsoft.com...
> I am trying to create a chart action to do a URL drill into bar chart. But
I
> can not figure out how to get the name of the clicked bar.
> Example: If the diagram below were a chart, when I click on the "two" bar,
I
> want to create the url: http://www.bonzo.com/foo.aspx&PICKEDBAR=Two
> One ==> Two ====> Three ========> Is it possible to do this?|||Thanks ... excactly the answer I needed.
You are the MVP.
"Teo Lachev [MVP]" wrote:
> Your Jump to navigate action you can use the RS global collections. Assuming
> that One, Two, etc. are category groups, you can use Fields!<your database
> field>.Value
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "billd" <billd@.discussions.microsoft.com> wrote in message
> news:43521AC0-1DE9-4980-B579-16F0DF651586@.microsoft.com...
> > I am trying to create a chart action to do a URL drill into bar chart. But
> I
> > can not figure out how to get the name of the clicked bar.
> >
> > Example: If the diagram below were a chart, when I click on the "two" bar,
> I
> > want to create the url: http://www.bonzo.com/foo.aspx&PICKEDBAR=Two
> >
> > One ==> > Two ====> > Three ========> >
> > Is it possible to do this?
>
>

Chart scale max value setting - how to?

Is it possible to set this value dynamically?
Could somebody show an example?
TIA,
KamelResults of my research:
You can set it dynamically in RS2005 (RS2000 not)
Example:
=Max(Fields!Column1.Value, "scope")
Kamel
kamel wrote:
> Is it possible to set this value dynamically?
> Could somebody show an example?
> TIA,
> Kamel

chart question

I want to be able show the numbers on chart incrementally. For example,
where if i have the values as 2, 5, 8 on chart, I would like to show as 2,
7, and 15 .. and so onâ?¦ can you help me with this please?
This is my formula for the chart â?¦
=Sum(Fields!NumberOfCrs.Value)
Thank youDid you try runningValue function?
like
=runningValue(Fields!NumberOfCrs.Value,sum,"chart1_SeriesGroup1")

chart label mapping to strings

How can I convert chart axis labels to strings. For example instead of
showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.You can either add a CASE statement to the SQL query and use that for
your label field, or you can add a calculated field to the dataset.
-Josh
bhanoji wrote:
> How can I convert chart axis labels to strings. For example instead of
> showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.|||I can't create a case statement for label field because if the value is 1 I
say gold and 2 will be silver etc. However if the data is 1.5 I can't map
that to a string. Thanks.
"Josh" wrote:
> You can either add a CASE statement to the SQL query and use that for
> your label field, or you can add a calculated field to the dataset.
> -Josh
> bhanoji wrote:
> > How can I convert chart axis labels to strings. For example instead of
> > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
>|||What do you want it to display if the value is 1.5? Nothing? The actual
value?
-Josh
bhanoji wrote:
> I can't create a case statement for label field because if the value is 1 I
> say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> that to a string. Thanks.
> "Josh" wrote:
> >
> > You can either add a CASE statement to the SQL query and use that for
> > your label field, or you can add a calculated field to the dataset.
> >
> > -Josh
> >
> > bhanoji wrote:
> > > How can I convert chart axis labels to strings. For example instead of
> > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> >
> >|||I have the following data
City Speakers
A 8
B 15
C 3
D 7
The graph is showing A, B, C, D on X axis and 5, 10, 15 on Y axis as major
interval is 5. How can I show Bronze, Silver, Gold instead of Y axis labels
5, 10, 15.
Thanks Josh.
"Josh" wrote:
> What do you want it to display if the value is 1.5? Nothing? The actual
> value?
> -Josh
>
> bhanoji wrote:
> > I can't create a case statement for label field because if the value is 1 I
> > say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> > that to a string. Thanks.
> >
> > "Josh" wrote:
> >
> > >
> > > You can either add a CASE statement to the SQL query and use that for
> > > your label field, or you can add a calculated field to the dataset.
> > >
> > > -Josh
> > >
> > > bhanoji wrote:
> > > > How can I convert chart axis labels to strings. For example instead of
> > > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> > >
> > >
>|||I found another thread that discusses this. Check it out at:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/1a7ac279edf86082/c8207d4c72f15e26?lnk=st&q=%22values+on+y-axis%22&rnum=1#c8207d4c72f15e26
-Josh
bhanoji wrote:
> I have the following data
> City Speakers
> A 8
> B 15
> C 3
> D 7
> The graph is showing A, B, C, D on X axis and 5, 10, 15 on Y axis as major
> interval is 5. How can I show Bronze, Silver, Gold instead of Y axis labels
> 5, 10, 15.
> Thanks Josh.
>
> "Josh" wrote:
> >
> > What do you want it to display if the value is 1.5? Nothing? The actual
> > value?
> >
> > -Josh
> >
> >
> > bhanoji wrote:
> > > I can't create a case statement for label field because if the value is 1 I
> > > say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> > > that to a string. Thanks.
> > >
> > > "Josh" wrote:
> > >
> > > >
> > > > You can either add a CASE statement to the SQL query and use that for
> > > > your label field, or you can add a calculated field to the dataset.
> > > >
> > > > -Josh
> > > >
> > > > bhanoji wrote:
> > > > > How can I convert chart axis labels to strings. For example instead of
> > > > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> > > >
> > > >
> >
> >|||In Y axis tab of the chart, I checked show label and tried to put an
expression in the format code box.
expression in formatcode does not work. expression
"=iif(fields!abc.value=10, "gold",
iif(fields!abc.value=20, "bronze", "silver"))" generates the following error
Report item expressions can only refer to fields within the current data
scope or,
if inside an aggregate, the specified data set scope. Thanks.
"Josh" wrote:
> I found another thread that discusses this. Check it out at:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/1a7ac279edf86082/c8207d4c72f15e26?lnk=st&q=%22values+on+y-axis%22&rnum=1#c8207d4c72f15e26
> -Josh
>
> bhanoji wrote:
> > I have the following data
> > City Speakers
> > A 8
> > B 15
> > C 3
> > D 7
> >
> > The graph is showing A, B, C, D on X axis and 5, 10, 15 on Y axis as major
> > interval is 5. How can I show Bronze, Silver, Gold instead of Y axis labels
> > 5, 10, 15.
> >
> > Thanks Josh.
> >
> >
> >
> > "Josh" wrote:
> >
> > >
> > > What do you want it to display if the value is 1.5? Nothing? The actual
> > > value?
> > >
> > > -Josh
> > >
> > >
> > > bhanoji wrote:
> > > > I can't create a case statement for label field because if the value is 1 I
> > > > say gold and 2 will be silver etc. However if the data is 1.5 I can't map
> > > > that to a string. Thanks.
> > > >
> > > > "Josh" wrote:
> > > >
> > > > >
> > > > > You can either add a CASE statement to the SQL query and use that for
> > > > > your label field, or you can add a calculated field to the dataset.
> > > > >
> > > > > -Josh
> > > > >
> > > > > bhanoji wrote:
> > > > > > How can I convert chart axis labels to strings. For example instead of
> > > > > > showing 1, 2, 3.. on x axis, how can I show gold, silver etc? Thanks.
> > > > >
> > > > >
> > >
> > >
>

Chart Color Sequence (Default Palette)

Hi,
Does anyone know where I can get the series color sequence for the
chart generated? For example, if I have 2 items on the chart, the first
one is in green and the next one will be in blue.
Thanks.
-- JordanMost of the provided chart color palettes have 16 distinct colors. As you
have noticed, the default palette always start with green, blue, magenta,
etc.
Every chart series (essentially legend item) gets its own color. The
following blog article shows an example of how to use your own color
palette. It also uses a custom legend that this is probably not what you are
looking for - you could just rely on the built-in chart legend.
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
You can also download my sample from the blog article.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jordan" <jordanm@.37.com> wrote in message
news:1119838129.392646.116360@.g43g2000cwa.googlegroups.com...
> Hi,
> Does anyone know where I can get the series color sequence for the
> chart generated? For example, if I have 2 items on the chart, the first
> one is in green and the next one will be in blue.
> Thanks.
> -- Jordan
>|||Hi,
Thanks for your reply. I came across your article and it sort of gives
me an idea on how to achieve my required report format (stacked chart
and line). I am using the default color palette and I need to know the
color sequence so that I can built my own legend to represent the chart
generated. I need to know at least 30 of the first color sequence in
order for my charts and legend to display correctly.
Thanks.
Jordan|||Hello Robert,
I'm running RS SP2 and have long known how to easily specify certain
colors be assigned to specic colors. What I have not figured out is how to
make these programatically assigned series colors semi-transparent like the
default series colors in a stacked bar chart. Is this possible?|||Sorry, semi-transparent colors are not possible through RDL at this point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Parker" <BrianParker@.discussions.microsoft.com> wrote in message
news:DD7D9299-87A8-4625-97FF-7178DB004A9A@.microsoft.com...
> Hello Robert,
> I'm running RS SP2 and have long known how to easily specify certain
> colors be assigned to specic colors. What I have not figured out is how
> to
> make these programatically assigned series colors semi-transparent like
> the
> default series colors in a stacked bar chart. Is this possible?

character types

i am using sql server 2000, i am new to databases, could you please tell me what are the different character types, example "varchar , numeric...etc"
How to find out the difference between them...
Where do i find a tutorial for this kind of basic knowledge ?
regardsHomework questions are not permitted on these forums, and ones that are written so brazenly will just invite ridicule at the poster.

Have a good day now.

Regards,|||A great resource for this sort of things is our best friend Google...
Search for "T-SQL data types" and you'll get a lovely handful of links that will tell you exactly what you want to know! :)

Once you've had a read, if you any specific questions post them back here!|||Oh George, you really are far too tolerant.|||That's because I don't think this one is homework (yet) ;)|||homework is indeed allowed and is often some of the more interesting posts.|||homework is indeed allowed and is often some of the more interesting posts.

I'll get my coat.|||Hi there,

Check the SQL server 2000 help file (called books online or BOL), if you do a search on 'data types' you should find everything you need.|||I'll get my coat.
Well, it is allowed, but it is certainly treated differently.
We do our best to help the poster to the answer themselves, but if they're unwilling then so are we.

I can understand why you think this one might be homework - but I personally don't see it yet. If you asked me a year ago what the datatypes were, I could probably name 3 :p
Not to mention I'd have even less of an idea what "BoL" stands for ;)|||The ignorance of youth.

Once you get to my age (24) you tend to show less emotion and occasionally affront a manner of indifference, often unknowingly, when you fine yourself in a social predicament. Such causes of this decline to a dour emotional state vary widely, though one that invariably seems to be effective, and which is notably the most prominent, is when one is asked to answer a painfully trivial question. The immediate reaction is to swiftly determine whether the original post was said in jest, an attempted cure to the illness described above, or if in fact, it was a question. Occasionally in exercising this judgment, we may reach the wrong conclusion.|||Occasionally in exercising this judgment, we may reach the wrong conclusion.Been there, done that, got the blood-stained tee-shirts to prove it! No serious harm done, such is life.

Twenty four years old... Yikes, I remember that wistfully!

-PatP|||Twenty four years old... Yikes, I remember that wistfully!
-PatP

I'll let you know my perspective in the year 2047.|||I'll let you know my perspective in the year 2047.Assuming that I'm still alive at that point, I'll be eagerly awaiting your analysis! ;)

-PatP|||Once you get to my age (24)damn...people around you are gonna be in BIG trouble then, by the time you get to my age (47)|||I thought I was ahead of my age in cynicism, but I think Robert may have taken the gold for that ;)

kc3377, how are you doing with your original question, do you have all the answers you need?

Character encoding

Is there a way to change the character encoding (on the fly) of your results when you run a query, for example, from unicode to iso-8859-1?No. You cannot perform this on the server-side. It is best handled on the client.

char(13) function is not working

Hello everyone,
In SQL Server books online there is this following example:
B. Use CHAR to insert a control character
This example uses CHAR(13) to print name, address, and city information
on separate lines, when the results are returned in text.
USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1
Here is the result set:
Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle WA
But when you run the select statement above in query analyzer the output
does not match the output shown above. The char(13) does not work as a
carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
on Windows 2000.
Thanks,
Raziq.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eOg32A0lEHA.324@.TK2MSFTNGP11.phx.gbl...
> But when you run the select statement above in query analyzer the output
> does not match the output shown above. The char(13) does not work as a
> carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
> on Windows 2000.
Do you have results set to text mode or grid mode? You need to use text
mode to see line breaks. Also, Windows uses CR-LF, which is CHAR(13) +
CHAR(10), some other systems use only CR or LF.

char(13) function is not working

Hello everyone,
In SQL Server books online there is this following example:
B. Use CHAR to insert a control character
This example uses CHAR(13) to print name, address, and city information
on separate lines, when the results are returned in text.
USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1
Here is the result set:
Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle WA
But when you run the select statement above in query analyzer the output
does not match the output shown above. The char(13) does not work as a
carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
on Windows 2000.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eOg32A0lEHA.324@.TK2MSFTNGP11.phx.gbl...
> But when you run the select statement above in query analyzer the output
> does not match the output shown above. The char(13) does not work as a
> carriage return. Any thoughts as to why? I am working on SQL 2000 sp3
> on Windows 2000.
Do you have results set to text mode or grid mode? You need to use text
mode to see line breaks. Also, Windows uses CR-LF, which is CHAR(13) +
CHAR(10), some other systems use only CR or LF.

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value =
'50' and when I do a select on it with single quotes surrounding the value it
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
Thanks in advance,
John Scott.
John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.
|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.
|||Thanks for the help Russell!!
Thanks,
John Scott.
"Russell Fields" wrote:

> Of course, that falls apart if somebody typed leading blanks. Such as would
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>
>

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value =
'50' and when I do a select on it with single quotes surrounding the value i
t
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
Thanks in advance,
John Scott.John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Thanks for the help Russell!!
--
Thanks,
John Scott.
"Russell Fields" wrote:

> Of course, that falls apart if somebody typed leading blanks. Such as wou
ld
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>
>

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value = '50' and when I do a select on it with single quotes surrounding the value it
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
--
Thanks in advance,
John Scott.John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> => '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> => '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Thanks for the help Russell!!
--
Thanks,
John Scott.
"Russell Fields" wrote:
> Of course, that falls apart if somebody typed leading blanks. Such as would
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
> >I noticed that when creating a char(5) field in SQL 2005 it places any
> > padding on the right of the value in there...for example I put in a value
> > => > '50' and when I do a select on it with single quotes surrounding the value
> > it
> > returns '50 ' . I was just wondering if that is a setting in SQL that
> > can
> > be changed to have the padding at the front of the value instead at the
> > end?
> > Or, what is a good way of getting that padding at to the front?
> >
> >
> > --
> > Thanks in advance,
> >
> > John Scott.
>
>|||John,
Although there are ways to do this, you have to ask yourself if it is a
good idea. IMO, it is a bad idea. You would need such code whereever you
are handling the column, or you might get incorrect results. For
example, image a search query like this:
SELECT ...
FROM my_table
WHERE my_char5_column = @.val
If you simply use the standard behavior (right padding of spaces), this
will all work as expected, but if you change the formula, then it is all
up to get to get everything right (including how to handle strings that
are too long).
If you need left padding and no right padding, then you should do this
in the front-end application. If you must do it in the database, then I
suggest you create a computed column for it, just for display purposes.
For example:
ALTER TABLE my_table
ADD my_leftpadded_char5_column AS
RIGHT(SPACE(5)+RTRIM(my_char5_column),5)
HTH,
Gert-Jan
John Scott wrote:
> I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value => '50' and when I do a select on it with single quotes surrounding the value it
> returns '50 ' . I was just wondering if that is a setting in SQL that can
> be changed to have the padding at the front of the value instead at the end?
> Or, what is a good way of getting that padding at to the front?
> --
> Thanks in advance,
> John Scott.