How to Saving Multiple ID in One Column

Dear Sir,
I need one Table Design information.
My requirement is:
I have some products (say 10 products) in my master table. Each product has
Product ID (PK).
ID Code Desc
--
1 A1 AAAA
2 B1 BBBBBB
etc. etc...
I have one interface where the user will select the products that he is
interestered with. Mean One user may select product ID 1, 3, 5 and other may
select 2, 7.
I have to save the product info selected by that user in one Column of the
user table.
(I have to save the selected product in one column only)
So What Type will be for that Column and how do i implement logic to save
all selected product so that it will be easy to save and extract the values
also.
I think I can use some binary kind of valu for each product and save the
selected binary combination or similar logic to store and retrieve.
Please suggest.
Thanks
PrabhatPrabhat
Use RDBMS. Split the User table into produtsSeclected and use usertable for
storing base information only. then you will have three tables
Products where product id is primary
users where users id is primary
and Junction Table : Products selected with foreign keys of productid and
userid. thus participating in many to many relationship.
Regards
R.D
"Prabhat" wrote:

> Dear Sir,
> I need one Table Design information.
> My requirement is:
> I have some products (say 10 products) in my master table. Each product ha
s
> Product ID (PK).
> ID Code Desc
> --
> 1 A1 AAAA
> 2 B1 BBBBBB
> etc. etc...
> I have one interface where the user will select the products that he is
> interestered with. Mean One user may select product ID 1, 3, 5 and other m
ay
> select 2, 7.
> I have to save the product info selected by that user in one Column of the
> user table.
> (I have to save the selected product in one column only)
> So What Type will be for that Column and how do i implement logic to save
> all selected product so that it will be easy to save and extract the value
s
> also.
> I think I can use some binary kind of valu for each product and save the
> selected binary combination or similar logic to store and retrieve.
> Please suggest.
> Thanks
> Prabhat
>
>|||However this is the example of bad DB design where you keep multiple
data in single column, you can make that column of varchar type and
build a string of product ID and use comma (,) separator to saperate
each Product ID.
While fetching you ll only need to split IDs from comma|||What is Bad Design ? Normalization or CSVs for OLTP:
IF you forget about Data intigrity and future intigration problems only
then you can use comma seperated values
this sipmle scenario is not possible with csv.
If you want to write a query to select particular product selected by
multiple users,
is it possible when u use CSV.
Regards
R.D
"Zero.NULL" wrote:

> However this is the example of bad DB design where you keep multiple
> data in single column, you can make that column of varchar type and
> build a string of product ID and use comma (,) separator to saperate
> each Product ID.
> While fetching you ll only need to split IDs from comma
>|||Hi R.D.,
You are right. I know the concept and I know we can use 3 tables for that.
But My question was, Is there a better way of calculation / procedure that
we can store all selected value in one column?
Also the Comma separated method is very critical, as if products will
increase we need to increase the width of the field.
Thanks
Prabhat
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:4A593B0A-B72F-4D7F-9FD4-43E851E2C70B@.microsoft.com...
> Prabhat
> Use RDBMS. Split the User table into produtsSeclected and use usertable
for
> storing base information only. then you will have three tables
> Products where product id is primary
> users where users id is primary
> and Junction Table : Products selected with foreign keys of productid and
> userid. thus participating in many to many relationship.
> Regards
> R.D
>

How to save headings in query results?

In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KKConsider running the same query via a DTS data pump to a flat file. You can
check on the option to include headers.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"KK" <anonymous@.discussions.microsoft.com> wrote in message
news:191d01c49ccc$d8ffca00$a301280a@.phx.gbl...
In query analyzer when I run a query it displays
the query results in the reulsts pane with column
headings, but when I save the file, it doesn't save
column headings. I already checked in Tools -> Options ->
Results - Print Column Headers(*) is checked.
How can I save the column headers? Otherwise I have to
manually enter it in the spreasheet.
Thank you,
KK

How to save FOR XML results into TEXT column?

Help! It appears that the FOR XML clause doesn't 100% get done what I
need to accommplish.
Problem: With Orders table (no Order Details) as:
OrderID int
OrderData ntext
OrderDate smalldatetime
OrderProcessed bit
I have another query that does a select on an Order Details and
Customer table which wnds with FOR XML.
I need to get the results of that query stored in the OrderData column
from above in XML format.
How do I accomplish ths using SQL server tools?
"CD" <doober@.family.us> wrote in message
news:s535f0pk4c6sro2puv3h783t7femfj1gms@.4ax.com...
[snip]
> I need to get the results of that query stored in the OrderData column
> from above in XML format.
> How do I accomplish ths using SQL server tools?
You can't without a lot of effort. If you're storing the XML for caching
purposes you're better off using something like the caching in ASP.Net.
Bryant
|||
>You can't without a lot of effort. If you're storing the
XML for caching
>purposes you're better off using something like the
caching in ASP.Net.
>
No, I need to use it to post orders to another downsteam
system that requires the order data in XML in a single
field, one record for each order.
|||Wait for Yukon.
Read How XML is treated in Yukon
[vbcol=seagreen]
>--Original Message--
the
>XML for caching
>caching in ASP.Net.
>No, I need to use it to post orders to another downsteam
>system that requires the order data in XML in a single
>field, one record for each order.
>.
>
|||To add some more information: In SQL Server 2005, you can nest FOR XML
expressions and thus could write an expression that provides XML data in XML
in a field, one per row.
Best regards
Michael
"Nitin" <anonymous@.discussions.microsoft.com> wrote in message
news:2b75901c46894$9b9861c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Wait for Yukon.
> Read How XML is treated in Yukon
>
> the

How to save an HTML doc in SQL 2005

I would like to save an HTML doc in a column of a SQL 2005 database.
However, in order to make it more legible, there are carriage returns
--
</head>
<body lang=EN-US>
--
and all I can get using nvarchar or text is the part before the carriage
return. How can I put the full fledged HTML doc into SQL 2005?
TIA,
PaulWhat do you mean by "all I can get using nvarchar or text is the part before
the carriage return"? Are you referring to the resultset in SSMS? Try
returning it as text.
ML
http://milambda.blogspot.com/|||Thank you.
I did.
I made the column Text, and then tried to past in:
<html>
<head>
</head>
<body>
</body>
</html>
And it only saves <html>.
Thanks again,
Paul
"ML" <ML@.discussions.microsoft.com> wrote in message
news:DBE0C195-F9FF-4D40-949E-3B98A2069EE6@.microsoft.com...
> What do you mean by "all I can get using nvarchar or text is the part
> before
> the carriage return"? Are you referring to the resultset in SSMS? Try
> returning it as text.
>
> ML
> --
> http://milambda.blogspot.com/|||How are you doing this? By pasting values through SSMS? I'd suggest writing
an insert procedure in T-SQL instead.
declare @.html nvarchar(max)
set @.html = N'<html>
<head>
</head>
<body>
</body>
</html>'
insert <table>
(
<column>
)
select @.html as <column>
ML
http://milambda.blogspot.com/|||Yes, I was. What I am trying to do is to have users of a ASP.net 2 website
be able to use an HTML generating program like Dreamweaver or FrontPage, or
MS Word, and then save as HTML, to make up a brief web page, then paste it
from the source of the program, and upload it to Sql Server 2005.
I am also trying to just put the location of the file in the column
(~/CustomerWebPages/SomeWebPageByCustomer.htm), and then resolve the rest in
VB.Net code, but that seems hard too.
Thanks,
Paul
"ML" <ML@.discussions.microsoft.com> wrote in message
news:F61A3EAD-AD56-4522-89CC-16AA2F04898D@.microsoft.com...
> How are you doing this? By pasting values through SSMS? I'd suggest
> writing
> an insert procedure in T-SQL instead.
> declare @.html nvarchar(max)
> set @.html = N'<html>
> <head>
> </head>
> <body>
> </body>
> </html>'
> insert <table>
> (
> <column>
> )
> select @.html as <column>
>
> ML
> --
> http://milambda.blogspot.com/|||On Fri, 17 Mar 2006 10:11:29 -0500, Paul wrote:

>Thank you.
>I did.
>I made the column Text, and then tried to past in:
><html>
><head>
></head>
><body>
></body>
></html>
>And it only saves <html>.
Hi Paul,
How did you verify this? If you look at the output in the grid form,
you'll see only the part up to the first newline.
Switch to "output as text" and re-run the query. Do you still see only
the first part of your text?
Hugo Kornelis, SQL Server MVP|||Hello, and Thanks.
yes, I just see the first line. I pasted:
<html>
<head>
</head>
<body>
</body>
</html>
to a table.
Then I ran a query, and got:
InvitationID AlbumID Message CreateDate
-- -- ----
----
----
---- --
-
1 1 <html> 2006-01-01 00:00:00.000
(1 row(s) affected)
What I decided to do was to just reference the address of the file in my tab
le (something like ~/Directory/Filename.htm). All works fine now. I was ju
st curious about doing it.
Paolo
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message news:hjim12llsmhnk8
kqos25nubll80dv1h5if@.4ax.com...
> On Fri, 17 Mar 2006 10:11:29 -0500, Paul wrote:
>
>
> Hi Paul,
>
> How did you verify this? If you look at the output in the grid form,
> you'll see only the part up to the first newline.
>
> Switch to "output as text" and re-run the query. Do you still see only
> the first part of your text?
>
> --
> Hugo Kornelis, SQL Server MVP

How to run system stored procedure

Hi,
After deleting the Merge Publication from Replication, Publications,
there are still remains of the publication in the database. (the
rowguid column in every table and the shared sign on the database
icon) We would like to remove them without creating a new database. It
looks like running the sp_removedbreplication is the best option
here. We are trying to run the procedure but cannot find how to. Books
online not very helpful.
Please help!
Martin
Martin,
the syntax is exec sp_removedbreplication 'yourdatabasename'. The only thing
you need to be careful of is that it will remove all traces of replication -
if this database is used as a publisher for several publications and a
subscriber also, all this will be removed. Sometimes this procedure doesn't
remove all system objects, and Hilary Cotter has previously posted up his
own script (on http://www.ava.co.uk) that will do the extra bits, but I'd
try sp_removedbreplication first.
HTH,
Paul Ibison
|||in your publication database issue a sp_replicationdboption 'merge','false'
This will get rid of most of the merge replication metadata and unshare your
database is you don't have any transactional or snapshot publications
present there. To get rid of the transactional publications issue a
sp_replicationdboption 'PublicationDatabaseName', 'publish', 'false'
sp_replicationdboption 'PublicationDatabaseName', 'merge publish', 'false'
Getting rid of the Rowguid columns is more complex.
Try this
select 'alter table ['+sysusers.name+'].'+sysobjects.name +' drop column
rowguid'+char(13)+'GO' from sysobjects, sysusers, syscolumns where
sysobjects.type='u'
and sysobjects.uid=sysusers.uid
and syscolumns.name='rowguid'
and syscolumns.id=sysobjects.id
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:3hc9j09ufhe2v7nonkpmnrbafr4i5qk011@.4ax.com...
> Hi,
> After deleting the Merge Publication from Replication, Publications,
> there are still remains of the publication in the database. (the
> rowguid column in every table and the shared sign on the database
> icon) We would like to remove them without creating a new database. It
> looks like running the sp_removedbreplication is the best option
> here. We are trying to run the procedure but cannot find how to. Books
> online not very helpful.
> Please help!
> Martin
>

How to run replace on all columns

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.

Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')

Thanks,
JPJackpipE (pipe.jack@.gmail.com) writes:

Quote:

Originally Posted by

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.
>
Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')

There is no way to loop through the columns in a table in a simple
fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.

For a thing like this I would do:

SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'

and the copy, paste and run result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||There is no way to loop through the columns in a table in a simple

Quote:

Originally Posted by

fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.
>
For a thing like this I would do:
>
SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'
>
and the copy, paste and run result.

Well that simplify my job but still does not automate the process to a
point where one query execution will take care of entire table.

Thanks.|||I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @.tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.

declare @.tblname nvarchar(60)
set @.tblname = 'PROFILE'

SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid

Output from one test exeuction:

UPDATE HoldEventsTable
SET TelephoneCallID= replace(cast(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');

Roy Harvey
Beacon Falls, CT

On 22 Feb 2007 16:01:40 -0800, "JackpipE" <pipe.jack@.gmail.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>There is no way to loop through the columns in a table in a simple
>fashion. This is because that it would rarely make any sense; columns
>in a table are supposed to described distinct attribuets.
>>
>For a thing like this I would do:
>>
> SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
> name + ', 1, 255), char(13) + char(10), '':'')'
> FROM syscolumns
> WHERE id = object_id('PROFILE')
> and type_name(xtype) like '%char'
>>
>and the copy, paste and run result.

>
>
>Well that simplify my job but still does not automate the process to a
>point where one query execution will take care of entire table.
>
>Thanks.

|||On Feb 22, 8:52 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @.tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.
>
declare @.tblname nvarchar(60)
set @.tblname = 'PROFILE'
>
SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid
>
Output from one test exeuction:
>
UPDATE HoldEventsTable
SET TelephoneCallID= replace(cast(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');
>
Roy Harvey
Beacon Falls, CT
>

Roy,

I had different output when I ran your query:
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');

It looked like select statement output with 17 rows (17 columns in the
table) like the above. No UPDATE or SET function.|||On 22 Feb 2007 19:33:42 -0800, "JackpipE" <pipe.jack@.gmail.comwrote:

Quote:

Originally Posted by

Roy,
>
>I had different output when I ran your query:
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
>_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');
>
>It looked like select statement output with 17 rows (17 columns in the
>table) like the above. No UPDATE or SET function.

I assume you changed the column names to all be _NAME, rather than the
query actually returning that.

The missing UPDATE and SET is what I would expect if the query was
written to filter out the first column. The specification said every
column, so I did not write the query to allow for that. In the first
CASE the first WHEN test would have to change from the simple:

WHEN C.colid = 1

To something like:

WHEN C.colid = (SELECT MIN(x.colid) FROM syscolumns as X WHERE X.id =
O.id AND <whatever filtering was used in the outer WHERE clause>)

Likewise the subquery in the last CASE would have to add the same
tests to match the WHERE clause.

Roy Harvey
Beacon Falls, CT|||Please post responses to the newsgroup, not to email. This promotes
the basic function of newsgroups, sharing information. It also means
that more than one person is reading and thinking about your problem.

If you did not add a WHERE clause test to limit the columns then I am
quite surprised that the UPDATE line did not appear in the output.
That would seem to indicate that there is no colid = 1 for the table.
I was able to create that condition by doing an ALTER TABLE to drop
the first column, but it is a condition my original query did not
allow for.

Did you try the alternate syntax I provided? What do you get from
this query?

SELECT MIN(colid), MAX(colod), count(colid), count(distinct colid)
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname

As for executing the code from inside the stored procedure, it is
possible. The command is spread over many rows, so the first step is
to turn that query into a cursor and step through the rows
concatenating all of them into a single string. Then you would have
to use dynamic SQL to execute it. Before doing that I suggest reading
this article very carefully: http://www.sommarskog.se/dynamic_sql.html
Roy Harvey
Beacon Falls, CT

Quote:

Originally Posted by

>Roy,
>
>Thank you for your time and helping me out. In my last reply the
>output I copied was wrong. Here is the code and output I get from your
>script:
>declare @.tblname nvarchar(60)
>set @.tblname = '_PHYSICIAN_PROFILE'
>
>SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @.tblname
ORDER BY C.id, C.colid
>
>======= output 42 rows =========
>
_NAME= replace(cast(_NAME as
>nvarchar(255)),char(13)+char(10),':'),
_SPECIALTY= replace(cast(_SPECIALTY as
>nvarchar(255)),char(13)+char(10),':'),
_GENDER= replace(cast(_GENDER as
>nvarchar(255)),char(13)+char(10),':'),
_SPECIAL_INTERESTS= replace(cast(_SPECIAL_INTERESTS as
>nvarchar(255)),char(13)+char(10),':'),
_PRACTICE_HIGHLIGHTS= replace(cast(_PRACTICE_HIGHLIGHTS as
>nvarchar(255)),char(13)+char(10),':'),
_TRAINING_POST_GRADUATE_EDUCATION=
>replace(cast(_TRAINING_POST_GRADUATE_EDUCATION as
>nvarchar(255)),char(13)+char(10),':'),
_BOARD_CERTIFICATION= replace(cast(_BOARD_CERTIFICATION as
>nvarchar(255)),char(13)+char(10),':'),
_LANGUAGES_SPOKEN= replace(cast(_LANGUAGES_SPOKEN as
>nvarchar(255)),char(13)+char(10),':'),
_INSURANCE_ACCEPTED= replace(cast(_INSURANCE_ACCEPTED as
>nvarchar(255)),char(13)+char(10),':'),
_PERSONAL_INFORMATION= replace(cast(_PERSONAL_INFORMATION as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_1= replace(cast(_ADDRESS1_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_2= replace(cast(_ADDRESS1_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_3= replace(cast(_ADDRESS1_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE1= replace(cast(_PHONE1 as
>nvarchar(255)),char(13)+char(10),':'),
_FAX1= replace(cast(_FAX1 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK1= replace(cast(_IN_NETWORK1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_1= replace(cast(_ADDRESS2_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_2= replace(cast(_ADDRESS2_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_3= replace(cast(_ADDRESS2_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE2= replace(cast(_PHONE2 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK2= replace(cast(_IN_NETWORK2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_1= replace(cast(_ADDRESS3_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_2= replace(cast(_ADDRESS3_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_3= replace(cast(_ADDRESS3_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE3= replace(cast(_PHONE3 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK3= replace(cast(_IN_NETWORK3 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_1= replace(cast(_ADDRESS4_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_2= replace(cast(_ADDRESS4_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_3= replace(cast(_ADDRESS4_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE4= replace(cast(_PHONE4 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK4= replace(cast(_IN_NETWORK4 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_1= replace(cast(_ADDRESS5_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_2= replace(cast(_ADDRESS5_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_3= replace(cast(_ADDRESS5_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE5= replace(cast(_PHONE5 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK5= replace(cast(_IN_NETWORK5 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_1= replace(cast(_ADDRESS6_1 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_2= replace(cast(_ADDRESS6_2 as
>nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_3= replace(cast(_ADDRESS6_3 as
>nvarchar(255)),char(13)+char(10),':'),
_PHONE6= replace(cast(_PHONE6 as
>nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK6= replace(cast(_IN_NETWORK6 as
>nvarchar(255)),char(13)+char(10),':'),
META_SRC_URI= replace(cast(META_SRC_URI as
>nvarchar(255)),char(13)+char(10),':');
>
>I don't think I filter out anything yet I don't have UPDATE or SET
>function and the output from this query is just like a select
>statement that does not execute the replacement.
>
>Is there a way actually execute the replace from this stored procedure?

How To Round With Negative Numbers?

I am using a select statement to obtain a result set back with aggregated
data. The problem is that I am seeing column data with 11 to 13 digits
after the decimal point. I tried using the STR function, but then the Order
By clause does not sort properly because there are negative numbers in the
aggregated data... I tried using Round, but that does no good either - it
still ends up displaying too many digits after the decimal point. Right now
I'm just using Query Analyzer to display the data, so I can live with it for
now. But, in the future, my app will be getting a result set back and I
would prefer not to have to go through each row and do a round on it from
the program. Does anyone know how to solve this problem?

Thanks for any help,

BobBob, what is the actual problem? What does how the numbers display
have to do with the sort order? Do you want the negatvie numbers and
the positive values to sort alike? ABS function? Round in select list
but not in the order by?

Perhaps a sample SQL and output would help someone provide the right
solution.

HTH -- Mark D Powell --|||Bob Bryan (RobertGBryanREMOVETHIS@.yahoo.com) writes:
> I am using a select statement to obtain a result set back with
> aggregated data. The problem is that I am seeing column data with 11 to
> 13 digits after the decimal point. I tried using the STR function, but
> then the Order By clause does not sort properly because there are
> negative numbers in the aggregated data... I tried using Round, but
> that does no good either - it still ends up displaying too many digits
> after the decimal point. Right now I'm just using Query Analyzer to
> display the data, so I can live with it for now. But, in the future, my
> app will be getting a result set back and I would prefer not to have to
> go through each row and do a round on it from the program. Does anyone
> know how to solve this problem?

If I could understand the problem, maybe I could solve it. :-)

It sounds as if you are working with floats, which are approxamite
numbers. You can round a value, but you may still see many decimals,
because there may be no exact represenation of the number. You could
convert to decimal, which is a precise type. You could also consider
handling the formatting of the data in the client.

But without knowledge about your data and their data types it's difficult
to say anything more intelligent.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Mark D Powell" <Mark.Powell@.eds.com> wrote in message
news:1106753337.073272.7040@.z14g2000cwz.googlegrou ps.com...
> Bob, what is the actual problem? What does how the numbers display
> have to do with the sort order? Do you want the negatvie numbers and
> the positive values to sort alike? ABS function? Round in select list
> but not in the order by?
> Perhaps a sample SQL and output would help someone provide the right
> solution.
> HTH -- Mark D Powell --

Ok, here is my query:

select [TS Bars Back] as BB, [TS Bars Within] as BW,
str([TS Move %], 6, 4) as "Move %", str([TS Entry %], 6, 4) as "Entry T",
str([TS ATR Profit], 7, 2) as "P Goal", sum([P/L Comm]) as "P/L $",
str(sum(Risk), 7, 2) as "Risk",
str(avg([P/L Avg %]), 7, 2) as "P/L %",
str(sum([P/L %]), 10, 2) as "P/L % Sum",
str(avg([P/L Long]), 7, 4) as "Long $", str(avg([P/L Short]), 7, 4) as
"Short $",
Count([# of trades]) as "# Runs", Sum([# of trades]) as "# Trades",
str(sum([Max Drawdown]), 10, 2) as "Max $ DD"
from [Table1].dbo.SumResults
where Symbol = 'GE_1/1m' and [SE Time] = 3600 and [TS Entry %] = .005 and
[TS ATR Stop] = 0
group by [TS Move %], [TS Entry %], [TS Bars Within], [TS Bars Back], [TS
ATR Profit]
order by [P/L $] desc

The output looks like this:

BB BW Move % Entry T P Goal P/L $ Risk
P/L % P/L % Sum Long $ Short $ # Runs # Trades Max $ DD
30 8 0.0075 0.0050 1.20 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07
30 8 0.0075 0.0050 1.30 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07

So, you can see that the str function works well to limit the # of decimal
points displayed for the other fields.
However, if I use it for the "P/L $" field, then the sort does not come out
right because the order by sorts based
upon the resulting character string and not the number in the field. I need
to limit the number of digits displayed in
the P/L $ field without affecting the sort order. Anybody know how to do
that?

Bob|||Thank you for the idea of using a decimal field instead of a float. Most of
my columns are floats (or reals). So, I tried doing a cast of the real
column to a decimal and it worked like a charm.

For those interested in the syntax, this is what worked:

sum(cast ([P/L Comm] as decimal(10,3))) as "P/L $",

Thanks again,

Bob

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95EB168E856Yazorman@.127.0.0.1...
> Bob Bryan (RobertGBryanREMOVETHIS@.yahoo.com) writes:
> > I am using a select statement to obtain a result set back with
> > aggregated data. The problem is that I am seeing column data with 11 to
> > 13 digits after the decimal point. I tried using the STR function, but
> > then the Order By clause does not sort properly because there are
> > negative numbers in the aggregated data... I tried using Round, but
> > that does no good either - it still ends up displaying too many digits
> > after the decimal point. Right now I'm just using Query Analyzer to
> > display the data, so I can live with it for now. But, in the future, my
> > app will be getting a result set back and I would prefer not to have to
> > go through each row and do a round on it from the program. Does anyone
> > know how to solve this problem?
> If I could understand the problem, maybe I could solve it. :-)
> It sounds as if you are working with floats, which are approxamite
> numbers. You can round a value, but you may still see many decimals,
> because there may be no exact represenation of the number. You could
> convert to decimal, which is a precise type. You could also consider
> handling the formatting of the data in the client.
> But without knowledge about your data and their data types it's difficult
> to say anything more intelligent.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

How to roll back the replication?

Hi, all.
I make a db replacated as distributor.
I decided later removing replication.
But, I don't know how to remove rowguid column from all tables.
How can I set back to the initial state of DB before replication?
thank you..You can't that I know of. You will need to alter the tables and drop the columns not needed.

How to return xml datatype column values ?

Hi ,

I want to return the xml datatype values in the output of my query.

I am querrying on one table which has xml datatype by selelcting the column in select clause and would like to get the values in output.

How do i get output with out/with including the column name in group by clause?

Can some one help me here.

--Smita.

You have a XML column and you want to break the XML as columns ? is this what you want ?|||

Rick,

I have a coulmn with xml datatype and want to get entire column value in one output.

-Smitha.

|||

Something like this ?

declare @.x xml

set @.x='<Root>

<Node>

<Node name="S.No">1</Node>

<Node name="S.Level">1</Node>

<Node name="S.FName">Data1</Node>

<Node name="S.LName">Data2</Node>

<Node name="Sl #" />

</Node>

<Node>

<Node name="S.No">2</Node>

<Node name="S.Level">2</Node>

<Node name="S.FName">Data22</Node>

<Node name="S.LName">Data33</Node>

<Node name="Sl #" />

</Node>

</Root>'

select cast(r.query('for $data in ./Node return data($data) ') as varchar(max)) as [Column]

from @.x.nodes('/Root/Node') as X(r)

How to return text data type from stored procedure.

Hi all,
I am having one stored procedure which is returing parameter having
text data type.
This paramter has to take value from table which have column with
datatype as text.
How will i set value to parameter having text datatype to value
present in table?
Any help will be truely appreciated.> I am having one stored procedure which is returing parameter having
> text data type.
You can't return anything but an INT, and it is *NOT* meant to return
*DATA* -- return values are meant to return error/status. Single data
elements that are not part of a resultset should be "returned" via an OUTPUT
parameter.
And you can't store TEXT as a local variable... maybe you could use
VARCHAR(MAX) in SQL Server 2005. Otherwise all you can do is SELECT
TextColumn FROM table and have the application consume the result that way.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

How to return specific Row in sql server

Hi,
Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.
Thanks,
Sajidin relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/|||Originally posted by r937
in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/

Thanks for answering my question.

Say suppose I have Name column and i want the 5th record on name column.

Thanks
Sajid|||Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW|||Originally posted by GWilliy
Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW

Yes I need the same.|||select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy|||Originally posted by r937
select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy

Thanks, It works.

If suppose i want to retrieve all columns, then do I need to specific each column.|||U could actualy do a sraight select into a cursor & then go straight to the Row

DECLARE @.a VarChar(20),@.b,@.c

DECLARE myCursor CURSOR SCROLL
FOR
SELECT a,b,c FROM myTable
OPEN myCursor
FETCH ABSOLUTE 5 INTO @.a,@.b,@.c
BEGIN
PRINT @.a
PRINT @.b
PRINT @.c
END
CLOSE myCursor
DEALLOCATE myCursor

example Code to give U an Idea - There are many options available when declaring Cursors etc.

May be more suitable for whatever Sajidrep is Doing

GW

How to return FTS results from varchar(MAX) or Text data type column?

I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).

For instance this works, and will return all applicable results.

CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](1000) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED

SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);

And this does not. It returns zero results what so ever.

CREATE TABLE [dbo].[TestHtml](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PageText] [varchar](MAX) NOT NULL,

CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED

SELECT * FROM TestHTML WHERE Contains(PageText, @.searchterm);

Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?

Did you create a fulltext index on the column and made it language specific / language neutral ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to return a data list from the attributes on xml column?

I have a table which contain a xml column.
Currently, I use for-each-row strategy to store xml value apply OPENXML
function to scan xml-row.
It is very very spend-time when there are many rows on table.
Is there any another method?Hi,
Do you mean that the table already has an SQL Server 2005 native XML in it?
You can always (even in SQL Server 2000) use SELECT ... FOR XML to store
rowset information in XML format, without using a for-each-row strategy. SQL
Server 2005 has many enhancements to SELECT... FOR XML, including the
ability to store rowset information as a native XML SQL Server data types.
In SQL Server 2005, the XML data type's nodes() method can be used instead
of OPENXML. It's usually quicker than OPENXML and takes less memory than
OPENXML to process.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ABC" <abc@.abc.com> wrote in message
news:O4l40pEPHHA.5000@.TK2MSFTNGP03.phx.gbl...
>I have a table which contain a xml column.
> Currently, I use for-each-row strategy to store xml value apply OPENXML
> function to scan xml-row.
> It is very very spend-time when there are many rows on table.
> Is there any another method?
>
>

How to return a data list from the attributes on xml column?

I have a table which contain a xml column.
Currently, I use for-each-row strategy to store xml value apply OPENXML
function to scan xml-row.
It is very very spend-time when there are many rows on table.
Is there any another method?
Hi,
Do you mean that the table already has an SQL Server 2005 native XML in it?
You can always (even in SQL Server 2000) use SELECT ... FOR XML to store
rowset information in XML format, without using a for-each-row strategy. SQL
Server 2005 has many enhancements to SELECT... FOR XML, including the
ability to store rowset information as a native XML SQL Server data types.
In SQL Server 2005, the XML data type's nodes() method can be used instead
of OPENXML. It's usually quicker than OPENXML and takes less memory than
OPENXML to process.
Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ABC" <abc@.abc.com> wrote in message
news:O4l40pEPHHA.5000@.TK2MSFTNGP03.phx.gbl...
>I have a table which contain a xml column.
> Currently, I use for-each-row strategy to store xml value apply OPENXML
> function to scan xml-row.
> It is very very spend-time when there are many rows on table.
> Is there any another method?
>
>

How to retrieve timestamp

Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanksCan any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.

Is there anything I need to set it up.

thanks

I don't believe the timestamp datatype actually contains a Date/Time, but instead, a unique binary number which represents an update to the row.

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.|||If you need a field to hold date/time information, use the datetime or smalldatetime datatypes.
=============================================

System is in production now. Even though I create a column with datetime the program will not update the column. So I thought setting up a column which is being automatically updated.

I like to delete some records based on the date last modified.

Thanks|||This is right out of the SQL Server help guide:

timestamp
Is used to indicate the sequence of SQL Server activity on a row, represented as an increasing number in a binary format. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @.@.DBTS function. timestamp data is not related to the date and time of an insert or change to data. To automatically record times that data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers.

You'll never get actual date/time values from the timestamp datatype. You're going to have to change something if you want to know exactly when a row was last touched.

How to Retrieve the Rows where particular Column Data equals NULL

Hi all,

I have a datatable Employee which is containg columns Emp_ID, Emp_Name, Manager_ID

I want to retrieve all the rows whose Manager_ID=NULL

I wirte the query select * from Employee where Manager_ID=NULL

but it is not working. How to do this. Please help me.

with regards

Ravinder Reddy.K

You have to use IS operator on NULL..

select * from Employee where Manager_ID is NULL

remember,

NULL = NULL => always False

NULL IS NULL = > True

|||

>NULL = NULL => always False

Not always... this is dependent on the setting of ANSI_NULLS. Try the following as an example:

SET ANSI_NULLS ON
GO
SELECT CASE WHEN NULL = NULL THEN 'YES'
ELSE 'NO'
END
GO
--Result: 'No'


SET ANSI_NULLS OFF
GO
SELECT CASE WHEN NULL = NULL THEN 'YES'
ELSE 'NO'
END
GO
--Result: 'Yes'

Chris

||| Forget the Settings BUDDY

how to retrieve the largest value from a column?

lets say a column using int as datatype

for eg:

the current method i used to retrieve the largest value is by...

select TOP 1 ...from....order by desc

so for eg:
let say the column contains
10
60
40
25
40

i will only retrieve 60. are there any loophole in the above method?Why not try this:

SELECT MAX(<field>) as MaxValue <from <TABLE> where <CLAUSE>|||Originally posted by Bascy
Why not try this:

SELECT MAX(<field>) as MaxValue <from <TABLE> where <CLAUSE>

Thanks. :)|||TOp n gives u the number of rows.
Max gives u the maximum value in the column|||Originally posted by cyrus
TOp n gives u the number of rows.
Max gives u the maximum value in the column

That is not quite correct...

TOP n gives you the first n rows of the resultset, so TOP 1 is the same as MAX as long as the order is on the same field.|||Originally posted by Bascy
That is not quite correct...

TOP n gives you the first n rows of the resultset, so TOP 1 is the same as MAX as long as the order is on the same field.

i am saying the same thing without order by

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve primary key columns from db?

Hello all. I need to extract the column names that form the primary key group on a table in SQL Server. I have a table called Account and it contains ten columns. The primary key consists of two columns - MasterAccountID, AccountID. This primary key is a unique constraint and is clustered (it acts as an index as well as a primary key group). I have tried the following to no avail:

exec sp_pkeys Account -> returns no rows

exec sp_helpindex Account -> throws an error stating that the object 'Account' does not exist

If I run the following SQL statement, I can see all of the PK_* constraints in the database, so I know they are there:

select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')

Again, I need to be able to specify a table name and have it return the columns (don't care if it returns extra fields) that make up the primary key fields for that table. Thanks!

Perhaps something like this:

select

*fromsyscolumnswhere id=object_id('yourTable')and colidin(select colidfromsysindexkeyswhere id=object_id('yourTable'))

|||

declare

@.TableNamevarchar(255)

set

@.TableName='tblPersons'

SELECT

CCU.*

FROM

INFORMATION_SCHEMA.Table_Constraints TCJOININFORMATION_SCHEMA.Constraint_Column_Usage CCUON CCU.CONSTRAINT_NAME= TC.CONSTRAINT_NAME

WHERE

CONSTRAINT_TYPE

='PRIMARY KEY'AND TC.TABLE_NAME= @.TableName

but this also works: look at the quotes ''

execsp_pkeys'tblPersons'