How to remove new line characters?

I want to display the example record below in a text box without the hard
returns. How can I strip them out so the record displays in one nice line?
The hard return shows as a sqaure box in the sql table, however it does not
paste into this web form. Imagine a sqaure box at the end of each line
below...
Bilat heel and ankle pain when walking and running
Medial knee pain bilat
Anterior compartment strain bilat
Bialt hips hurt w/ occassional low back painreplace(yourTextLine, char(13), ' ')
"Chris Patten" wrote:
> I want to display the example record below in a text box without the hard
> returns. How can I strip them out so the record displays in one nice line?
> The hard return shows as a sqaure box in the sql table, however it does not
> paste into this web form. Imagine a sqaure box at the end of each line
> below...
> Bilat heel and ankle pain when walking and running
> Medial knee pain bilat
> Anterior compartment strain bilat
> Bialt hips hurt w/ occassional low back pain

How to remove a recursive relationship

Hi,
I am building a db that contains folders with all info about a folder.
For example :
folder_id PK
folder_name
folder_size etc.
In my case, each folder may have 0 or more childern. A child itself
is unlikely going to have any children in itself. This is a recursive
relationship I'd like to remove in my DB design.
How can I represent this relatonship?
Thank you, DinoPlease elaborate on the issue, if you could. I'm not sure I understand; a
child folder which is the only child of another folder is unlikely to have
any children of its own?
How are you modelling the hierarchy itself? It doesn't HAVE to be recursive
(although that is one option). There are also hierarchical modelling
techniques like enumerated path and nested sets (search Google and you'll
get plenty of info on each) that are not recursive.
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:p2sj50p0pcihhqo9i4f0g0kv1thl98see7@.
4ax.com...
> Hi,
> I am building a db that contains folders with all info about a folder.
> For example :
> folder_id PK
> folder_name
> folder_size etc.
> In my case, each folder may have 0 or more childern. A child itself
> is unlikely going to have any children in itself. This is a recursive
> relationship I'd like to remove in my DB design.
> How can I represent this relatonship?
> Thank you, Dino|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:335813
I am gonna do it just like:
folder_id PK
parent_folder_id FK
folder_name
this way if the folder has a parent I will know it. I think this
solves the problem.
Thanks anyways,
Dino
On Thu, 18 Mar 2004 14:10:38 -0500, "Adam Machanic"
<amachanic@.air-worldwide.nospamallowed.com> wrote:

>Please elaborate on the issue, if you could. I'm not sure I understand; a
>child folder which is the only child of another folder is unlikely to have
>any children of its own?
>How are you modelling the hierarchy itself? It doesn't HAVE to be recursiv
e
>(although that is one option). There are also hierarchical modelling
>techniques like enumerated path and nested sets (search Google and you'll
>get plenty of info on each) that are not recursive.
>
>"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
> news:p2sj50p0pcihhqo9i4f0g0kv1thl98see7@.
4ax.com...
>

How to reference to an image control

Hi,
I have a report. In one column i want to show a certain image depending the
value of another column. For example:
if text1 =1 than text10 = image1
elseif text1 =2 than text10 = image2
else text1 =3 than text10 = image3
end if
Is this possible? Also what i do recall is that in access if you set an
image for one row, the image goes for all rows (in a form). Does this applies
for SQL Reports also?
In general, how can i refence to a control (like a list, a rectangle, etc)
on a report?
Thnx,
StanleyIn the value of your image control, put the following:
=IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
in my solution, my bmp's are stored as part of the solution, so they are
deployed to the server as well.
"Stanley" wrote:
> Hi,
> I have a report. In one column i want to show a certain image depending the
> value of another column. For example:
> if text1 =1 than text10 = image1
> elseif text1 =2 than text10 = image2
> else text1 =3 than text10 = image3
> end if
> Is this possible? Also what i do recall is that in access if you set an
> image for one row, the image goes for all rows (in a form). Does this applies
> for SQL Reports also?
> In general, how can i refence to a control (like a list, a rectangle, etc)
> on a report?
> Thnx,
> Stanley
>|||Hi Pinolian,
Thnx for your response. I have added the pictures to the solution and
deployed it. But instead of seeing the images i get to see a 'red cross' like
it cannot find the image. Do you know why?
When the solution is deployed should i see the images in the report manager
also?
Thnx,
Stanley
"Pinolian" wrote:
> In the value of your image control, put the following:
> =IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
> in my solution, my bmp's are stored as part of the solution, so they are
> deployed to the server as well.
>
> "Stanley" wrote:
> > Hi,
> >
> > I have a report. In one column i want to show a certain image depending the
> > value of another column. For example:
> >
> > if text1 =1 than text10 = image1
> > elseif text1 =2 than text10 = image2
> > else text1 =3 than text10 = image3
> > end if
> >
> > Is this possible? Also what i do recall is that in access if you set an
> > image for one row, the image goes for all rows (in a form). Does this applies
> > for SQL Reports also?
> >
> > In general, how can i refence to a control (like a list, a rectangle, etc)
> > on a report?
> >
> > Thnx,
> >
> > Stanley
> >
> >|||Problem Solved with the image not showing. The picture name should be
mentioned without the extension, like:
=IIF(Fields!Sales.Value = True, "checkedbox-big", "blankbox")
"Pinolian" wrote:
> In the value of your image control, put the following:
> =IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
> in my solution, my bmp's are stored as part of the solution, so they are
> deployed to the server as well.
>
> "Stanley" wrote:
> > Hi,
> >
> > I have a report. In one column i want to show a certain image depending the
> > value of another column. For example:
> >
> > if text1 =1 than text10 = image1
> > elseif text1 =2 than text10 = image2
> > else text1 =3 than text10 = image3
> > end if
> >
> > Is this possible? Also what i do recall is that in access if you set an
> > image for one row, the image goes for all rows (in a form). Does this applies
> > for SQL Reports also?
> >
> > In general, how can i refence to a control (like a list, a rectangle, etc)
> > on a report?
> >
> > Thnx,
> >
> > Stanley
> >
> >

How to reference a shared datasource already on report server for data driven subscription

I see there's MS documentaion on creating a data driven subscription.
The example shows to create a data source for the data retrieval plan.
see
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx
I would like to set the data source to a share data source on the
server. How should I implement that?
I have tried to use the following but failed:
...
delivery.Item = rs.GetDataSourceContents("/Data Sources/
testDataSource");
...I have worked this out: By creating a DataSourceReference object:
DataSourceReference reference = new
DataSourceReference();
reference.Reference = "/Data Sources/testDataSource";
delivery.Item = reference;
But then I got error like:
The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in
the report server database.
My credential information is stored with data source. If I manually
create the data-driven subscription from Report Manager, and use the
same shared data source, I wouldn't get this error. Anyone knows why?
Thanks

How to read third word from a string?

Hallow, everyone:
I want to read third word from a string. For example, the string is,
ABCDE fghijk 031 LPN OPQ
I need 031. How to read it?
Any help will be appreciated.
Thanks
ZYTOk, do I win a prize for finding the ugliest, most cumbersome way to do this? :)

declare @.strtest as varchar(100)
set @.strtest='ABCDE fghijk 031 LPN OPQ'
select substring(@.strtest,charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1), charindex(' ', @.strtest,charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1)+1)-charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1))

Of course, it crashes if there are only three words to begin with.|||I'm sure there is an easier way..but here's my offering

DECLARE @.x varchar(8000)

SET @.x = 'ABCDE fghijk 031 LPN OPQ'

SELECT SUBSTRING(@.x
, CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1
, (CHARINDEX(' ',@.x,CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1)-1)
- (CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1)+1)|||I think you would have to add a space to the end of the string, just to make sure it always works, even if there are only 3 words.|||parsing strings is so much easier in compiled code. forgive me for saying this but sql sucks at it. what sql is good at is set based operations.

you should do this on the client if you can:

string[] s = mystring.Split(' ');
string third = s.Length >= 3 ? s[2] : null;|||that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.|||that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.

any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow. :)|||Here's so more abuse

CREATE FUNCTION udf_GetWord (
@.str nvarchar(4000)
, @.Word int
, @.Delim char(1)
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @.LastPosition int, @.DelimFound int, @.Start int, @.End int, @.WordFound nvarchar(4000)

SELECT @.LastPosition = 0, @.DelimFound = 0
WHILE (@.DelimFound < @.Word-1)
BEGIN
IF (CHARINDEX(@.Delim, @.str, @.LastPosition + 1) = 0)
BREAK

ELSE
BEGIN
SET @.LastPosition = CHARINDEX(@.Delim, @.str, @.LastPosition + 1)
SET @.DelimFound = @.DelimFound + 1
END

END

SET @.Start = @.LastPosition + 1
SET @.End = CHARINDEX(@.Delim, @.str, @.LastPosition + 1) - @.Start
IF @.End < 0 SET @.End = LEN(@.str)-@.start+1
SELECT @.WordFound = SUBSTRING(@.str,@.start,@.end)
RETURN @.WordFound
END|||any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow. :)

the average dba makes more than the average developer and no one is going to pay me to code C# right now. I actually spent the first few years of my programming career as a vb \ java \ classic asp developer but it has been all dba for a while now. lets see, should I take the entry level c# developer job with a paycut or should I take the good paying mid-level\senior dba job? hmmmmmm. I am only into my 2nd sql 2k5 book, so I am far enough behind in what I am decent at.|||I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

in that case stick with parsing strings in sql. it works, it's just not the most efficient way. ;)|||It's a front end issue...let the minions handle it|||I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

in that case stick with parsing strings in sql. it works, it's just not the most efficient way. ;)

I hear you but the only way to get good is to do it everyday and I just do not have that opp. I actually picked up Inside C# and got myself a compiler last summer and frankly I got bored with it.|||fair enough.

about getting bored though, i think that's your own fault (or maybe the books fault - I hardly ever read tech books), not the fault of the language. there are lots of interesting and fun things you can do in C/C++/C#.

Here's just one tiny example: implement an RDBMS. your beloved SQL Server is implemented in those languages. ;)

How to Query to get the Numeric fields Only in the Table

hi Good Day everyone.

how can i query numeric values in the specific fields for example EmployeeID only.

but the field EmployeeID has a datatype of varchar.

for example :

EmployeeID -> Field

001

002

a

b

c

how do i query 001 and 002 only. tnx

here it is,

Code Snippet

Create Table #employee (

[EmployeeID] Varchar(100)

);

Insert Into #employee Values('001');

Insert Into #employee Values('002');

Insert Into #employee Values('a');

Insert Into #employee Values('b');

Insert Into #employee Values('c');

Insert Into #employee Values('1E2');

Insert Into #employee Values('.');

Insert Into #employee Values('$');

Insert Into #employee Values('E0');

Select

*

from

#employee

Where

Isnumeric([EmployeeID]) = 1

And [EmployeeID]not like '%[^0-9]%'

|||Thanks Manivannan.D.Sekaran it helps me a lot.

How to Query for NULL in a database

In my database, in a few tables there are NULL values in some of the columns. This is okay, but I need to know how to query for nulls. For example I tried the following query but it did not work:

select*from Employeewhere DateOfBirth=NULL

This did not work so I also tried the following:

select*from Employeewhere DateOfBirth='NULL'

Neither of these worked. Can someone help me out?

NULL means unknown. So 2 NULLs are not equal. Read up books online about NULLs. Its an important topic. For your query, you would need to write it as:

SELECT * FROM Employee WHERE DateOfBirthIS NULL