clear DB fields at midnight

I have a dataBase named pricesDB. I would like to clear all the prices column everyday at midnight.
How should i do that?Scheduled task using either the Windows Scheduler or a SQL Agent tasksqlsql

cleaing up text type fields

Hello.
I have a text type field in an SQL2000 table I need to clean up. After
converting from MySQL I am finding that I have leading Tabs and when I
display the text field in an asp textbox I end up see the record
double spaced.
Any suggestion on how I can clean the field up?
Also, as I test an application I just migrated from apache/php/mysql
to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
pentium 4 with 512mb I am finding the the MS solution is like 3 times
slower?
Thanks for any help or info.> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
Trim it on the client before displaying it?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
We don't even have a fraction of the information required to analyze your
environment and point out potential reasons for any performance differences.|||Assuming that each record has two leading tabs, you could do something like:
DECLARE @.ptr binary(16)
SELECT @.ptr=textptr(textColumn) FROM yourTable
UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
That will delete the first two characters from each record. If you need to
do more complex evaluation, you can run a cursor over the table and use
CHARINDEX to determine where the replacement(s) should be made.
<jason@.cyberpine.com> wrote in message
news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> Hello.
> I have a text type field in an SQL2000 table I need to clean up. After
> converting from MySQL I am finding that I have leading Tabs and when I
> display the text field in an asp textbox I end up see the record
> double spaced.
> Any suggestion on how I can clean the field up?
> Also, as I test an application I just migrated from apache/php/mysql
> to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> pentium 4 with 512mb I am finding the the MS solution is like 3 times
> slower?
> Thanks for any help or info.|||On second thought, you will need a CURSOR - Running that code as-is will
only strip the first two characters from the last record selected.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> Assuming that each record has two leading tabs, you could do something
like:
> DECLARE @.ptr binary(16)
> SELECT @.ptr=textptr(textColumn) FROM yourTable
> UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> That will delete the first two characters from each record. If you need
to
> do more complex evaluation, you can run a cursor over the table and use
> CHARINDEX to determine where the replacement(s) should be made.
> <jason@.cyberpine.com> wrote in message
> news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > Hello.
> >
> > I have a text type field in an SQL2000 table I need to clean up. After
> > converting from MySQL I am finding that I have leading Tabs and when I
> > display the text field in an asp textbox I end up see the record
> > double spaced.
> >
> > Any suggestion on how I can clean the field up?
> >
> > Also, as I test an application I just migrated from apache/php/mysql
> > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > slower?
> >
> > Thanks for any help or info.
>|||You can see this example for using replace in this case:
http://www.aspfaq.com/2445
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> On second thought, you will need a CURSOR - Running that code as-is will
> only strip the first two characters from the last record selected.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > Assuming that each record has two leading tabs, you could do something
> like:
> >
> > DECLARE @.ptr binary(16)
> > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> >
> > That will delete the first two characters from each record. If you need
> to
> > do more complex evaluation, you can run a cursor over the table and use
> > CHARINDEX to determine where the replacement(s) should be made.
> >
> > <jason@.cyberpine.com> wrote in message
> > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > Hello.
> > >
> > > I have a text type field in an SQL2000 table I need to clean up. After
> > > converting from MySQL I am finding that I have leading Tabs and when I
> > > display the text field in an asp textbox I end up see the record
> > > double spaced.
> > >
> > > Any suggestion on how I can clean the field up?
> > >
> > > Also, as I test an application I just migrated from apache/php/mysql
> > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > pentium 4 with 512mb I am finding the the MS solution is like 3 times
> > > slower?
> > >
> > > Thanks for any help or info.
> >
> >
>|||Ahhh, nice... I didn't realize this slightly annoying caveat:
"Initially I used CHARINDEX, but that failed if the pattern was deeper than
8,000 characters into the value. PATINDEX never dies."
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAGRfqUpDHA.2772@.TK2MSFTNGP12.phx.gbl...
> You can see this example for using replace in this case:
> http://www.aspfaq.com/2445
>
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eZxpAnUpDHA.684@.TK2MSFTNGP09.phx.gbl...
> > On second thought, you will need a CURSOR - Running that code as-is will
> > only strip the first two characters from the last record selected.
> >
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:eB#svjUpDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > > Assuming that each record has two leading tabs, you could do something
> > like:
> > >
> > > DECLARE @.ptr binary(16)
> > > SELECT @.ptr=textptr(textColumn) FROM yourTable
> > > UPDATETEXT yourTable.textColumn @.ptr 0 2 ''
> > >
> > > That will delete the first two characters from each record. If you
need
> > to
> > > do more complex evaluation, you can run a cursor over the table and
use
> > > CHARINDEX to determine where the replacement(s) should be made.
> > >
> > > <jason@.cyberpine.com> wrote in message
> > > news:ef0a04d7.0311070754.5567591d@.posting.google.com...
> > > > Hello.
> > > >
> > > > I have a text type field in an SQL2000 table I need to clean up.
After
> > > > converting from MySQL I am finding that I have leading Tabs and when
I
> > > > display the text field in an asp textbox I end up see the record
> > > > double spaced.
> > > >
> > > > Any suggestion on how I can clean the field up?
> > > >
> > > > Also, as I test an application I just migrated from apache/php/mysql
> > > > to iis/asp.net/sql2000 and from hardware pentium 1 with 64mb mem to
> > > > pentium 4 with 512mb I am finding the the MS solution is like 3
times
> > > > slower?
> > > >
> > > > Thanks for any help or info.
> > >
> > >
> >
> >
>|||It took a few practical uses before I cleaned that up. The code is a little
simpler for this specific case, because you don't have to find the index,
and you don't have to loop through... you just replace the first two
characters if they're what you think they are.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ePbcpuUpDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Ahhh, nice... I didn't realize this slightly annoying caveat:
> "Initially I used CHARINDEX, but that failed if the pattern was deeper
than
> 8,000 characters into the value. PATINDEX never dies."sqlsql

choosing the right datatype

It was hard to tell if this was the correct forum, but here goes:
My browser-driven Intranet App has 3 fields where users can type in a very
long description. It has a size of 5000, and is a varchar. People will
typically type in alphanumeric characters in it. (For the web-savvy, the
users types into a Textarea).
A co-worker tells me I should change the datatype from varchar to text. I
had it at varchar specifically because I understood that varchar will only
take up what disc space that it needs to. I asked him for an explanation of
why to go to text. He replied:
"There is an 8k limitation on a row when you use standard datatypes. You
have 3 columns with 5000 characters which pretty much doubles the amount of
allowed storage in a row. I'm surprised you haven't run into any problems.
Text datatypes are stored separately and allow up to 2GB."
Is this correct? Under my described scenario, which would be best?I would stick with Varchar. Varchar can hold up to 8,000 characters.
You're not storing more than 8,000 characters, so I don't see the point of
switching to a Text field. Also, if you use a text field, unless you
specify that you're storing the data "in row", it will only store a pointer
to the separate page or pages to access your string. You also can't
directly reference a text column in a WHERE clause. Unless you're storing
unusually large amount of characters (defined as 8,001+ characters) per row
in this column there's no real point to going with text.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
> of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
> of
> allowed storage in a row. I'm surprised you haven't run into any
> problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||He is right, but the limit for a row is 8060 and not 8k.
Example:
use northwind
go
create table t (
colA varchar(8000),
colB varchar(8000),
colC varchar(8000)
)
go
insert into t values(replicate('a', 8000), replicate('b', 8000),
replicate('c', 8000))
go
drop table t
go
Result:
Server: Msg 511, Level 16, State 1, Line 2
Cannot create a row of size 24015 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
AMB
"middletree" wrote:

> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||It is, generally, correct. However, there are perfomance and usage penaltie
s
for using Text datatype, that make it worthwhile t oavoid them if possible..
.
What are the three fields used for? An Alternative which MAY be worth
investigating, is putting these three fields in another table...
Assuming your existing Table has a PK called PKID,
Create Table Comments
(PKID Integer Not Null
WhichField TinyInt Not Null,
Description VarChar(5000),
CONSTRAINT Comment_PK PRIMARY KEY (PKId, WhichField)
)
-- (The WHichField column identifies which one of the web page's Description
fields this is for... 1,2, or 3)
"middletree" wrote:

> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||Hey, 'tree. If it matters, "text" columns can be a pain to deal with in DW,
depending on what you're trying to do of course. The default recordset
options don't always work, you've got to watch your field order, etc.
I know it's not strictly database relevant, but I know you use DW, so it
might be relevant for you.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
of
> allowed storage in a row. I'm surprised you haven't run into any
problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang out
at the DW forum to ask and answer questions about code, not DW. And to
discuss various theological and political topics ;)
Hope you haven't left the DW forums for good.
"CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> Hey, 'tree. If it matters, "text" columns can be a pain to deal with in
DW,
> depending on what you're trying to do of course. The default recordset
> options don't always work, you've got to watch your field order, etc.
> I know it's not strictly database relevant, but I know you use DW, so it
> might be relevant for you.
> "middletree" <middletree@.htomail.com> wrote in message
> news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
very
I
only
> of
You
> of
> problems.
>|||For a while, yes. Probably not for good though.
I'd elaborate, but the "on-topic" rules here seem quite a bit stricter.
"middletree" <middletree@.htomail.com> wrote in message
news:%23cpX5hZKFHA.572@.tk2msftngp13.phx.gbl...
> Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang
out
> at the DW forum to ask and answer questions about code, not DW. And to
> discuss various theological and political topics ;)
> Hope you haven't left the DW forums for good.
>
> "CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
> news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> DW,
> very
the
text.
> I
> only
explanation
> You
amount
>

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?
Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It see
ms
> a bit inpractical to keep several copies of these fields in several tables
,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Chinese character in the field (Sql 7)

Guys,
I have to replace some old chinese characters (words) with some new
ones. In the database, the fields are displayed as ?.
The current chinese characters will display fine when you retrieved the
fields through the website. As soon as I cut and paste the new chinese
characters onto the field that I want to replace, the website will
display ? with the fields that I just updated.
I have installed the Chinese (Taiwan) letters and enable my language
toolbar. During the cut and paste process, I also switched to "CH".
Any idea?
Please help.
Thanks in advance,
TonyHi,
You can try to use CAST(0xABCD1234 as NVARCHAR(4)) etc. instead of copy and
paste.
Replace the 0xABCD1234 with the binary code of the Chinese characters.
Kenneth
<tractng@.gmail.com> wrote in message
news:1132790630.847208.228190@.z14g2000cwz.googlegroups.com...
> Guys,
> I have to replace some old chinese characters (words) with some new
> ones. In the database, the fields are displayed as ?.
> The current chinese characters will display fine when you retrieved the
> fields through the website. As soon as I cut and paste the new chinese
> characters onto the field that I want to replace, the website will
> display ? with the fields that I just updated.
>
> I have installed the Chinese (Taiwan) letters and enable my language
> toolbar. During the cut and paste process, I also switched to "CH".
> Any idea?
> Please help.
>
> Thanks in advance,
> Tony
>|||Kenneth,
Can you explain, how I can do this. I am a newbie.
Thanks,
Tony

CHECKSUM() of binary data

Hello,

I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.

How can I generate checksums of such datatype.

KaramCan anybody help me?|||CHECKSUM() by parts.
split ntext to blocks of nvarchar(4000) and use check sum.
Originally posted by karam_chand03
Hello,

I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.

How can I generate checksums of such datatype.

Karam|||Thanks for the answer.

Can I use it for datatypes like image, sql_variant?

A simple code on how to break it up and get a hash value will be helpful.|||Hello,

I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?

Karam|||you can generate checksum() to whole column
like
select checksum(*) from tablename
provided none of the columns are of text ntext image data types
Originally posted by karam_chand03
Hello,

I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?

Karam|||Hello,

Thats the problem. I do have those unsupported columns and I need to generate hash value from it. In MySQL (where I come from) has a md5() function to generate hash value dfor every type of data it supports.

What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.

Is it feasible?

Karam|||I don't think so.
convert text to varchar and ntext to nvarchar....
and check.
Originally posted by karam_chand03
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.

Is it feasible?

Karam

checking the existance of fields and types

I'm trying to write a program in cold fusion to check the existance of
fields and data types according to requirements

I was looking at the syscolumns table for some of this information but
I've discovered that in my playing (creating and deleting tables) thre
are multiple entries for a field that is in table that is created then
deleted then created again.

Is this a problem? Is there a better way to get at the information ie
the table, field, and type exist in a database?Use the INFORMATION_SCHEMA views. See Books Online for details.

Most metadata can be retrieved more easily from the info schema than
from system tables. There are some exceptions but system tables are
best left alone unless you really have to use them. They will be
supported only for backwards compatibility and won't reflect new
features in future versions.

--
David Portas
SQL Server MVP
--|||William Kossack (kossackw@.njc.org) writes:
> I'm trying to write a program in cold fusion to check the existance of
> fields and data types according to requirements
> I was looking at the syscolumns table for some of this information but
> I've discovered that in my playing (creating and deleting tables) thre
> are multiple entries for a field that is in table that is created then
> deleted then created again.

This sounds very strange. My guess is that you are joining syscolumns
with systypes incorrectly. (Those two tables are indeed a bit tricky
to match up.) Care to post a query that gives funny result?

> Is this a problem? Is there a better way to get at the information ie
> the table, field, and type exist in a database?

Some people tout the INFORMATION_SCHEMA views, but since they only
give a subset of the metadata information, they're pretty useless in
my opinion. They are mainly interesting if you want to write portable
meta-data queries.

There are also functions like object_id, columnproperty which can be
useful at times, but they have the drawback that they are restricted
to the current database.

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

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

Checking for duplicate row before inserting

I need to know how to check/compareall fields in a row based on Cust Id before inserting a new record. As it is right now, if the Add/Update button is pressed continually, it increments the record number and adds a duplicate record. And you know you'll never stop people from double clicking a button that only needs to be clicked once.

I thought about just disabling the Update button after the insert occurs, then re-enable it if they go back and edit a field, but that's not a solid solution.I'd do it in your stored procedure.

You've got something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
GO

I'd change it to something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
IF EXISTS
 (SELECT CustID FROM CustTable
 WHERE SomeField = @.OneVal AND OtherField = @.OtherVal)
 BEGIN
 RETURN
 END
ELSE
 BEGIN
 INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
 END
GO

That's pretty simple, obviously...You'll likely want to return something if a match is found, or even if the new record gets inserted...But it shows you the basics, I hope.

Regards,

Xander|||That looks like what I'm looking for. Since the add buttons click event runs 5 functions to insert data into other tables; if I call the SP in the first function, that should intercept the insertion process all together and break right?

InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

I suppose that I could code a boolean to catch if the SP returns a duplicate using True/False, then use it to display a message in my errmsg label; notifying the user that they have attempted to store a duplicate record.

Thanks for the help, any other info would be greatly appreciated.
PD|||InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

No, the fact that a record wasn't inserted will not, in and of itself prevent the other functions from firing.

You've got the idea though...If you pass a boolean value back, you can use it to decide whether to fire the other functions or not.

You could pass an output parameter from the stored procedure, for instance, or return a scalar value that lets you know what the stored procedure did, and wrap your four subsequent functions in an IF block, (perhaps even inside the first function on successful insertion?) and that should give you what you want.|||I got it to work, but it does prevent the updating of the fields that are not part of the SP in one of my functions (Strange).

I am going to check it out. I thought about appending the table to store all the field data and re-code the SP to check all the fields, fire it first, and then let it yield to the other functions if no duplicate exists.

Thanks.|||Well, it has turned into a proverbial nightmare. If I yield to the other insert functions from my SP, then duplicate records can be created in those tables. So I guess I am going to have to write SP's for each of those functions to prevent duplicates there as well.

I inherited this app a couple of months ago from an outside firm, and what a mess it has been.