Showing posts with label table. Show all posts
Showing posts with label table. Show all posts

Friday, March 30, 2012

HELP!!-Urgent

"I've created a customer table and I want to give each customer a
unique id e.g 'ES001' But sql server won't let me I've looked at
various books to no avail.

My second question is when I delete a row from a table which had an ID
of 2 when I put in a new row it gives the new row and ID of 3 how do i
make it give the row an ID of 2."You can use a view, trigger or a calculated column to derive a
meaningful surrogate key from your data, or you can generate it in your
application. What exactly do you have a problem with? What do you mean
by "sql server won't let me"?

As for your second question, the whole point of an IDENTITY key (I
assume that's what you are referring to) is that it is internal,
auto-generated and meaningless. If you care about what the value should
be then you shouldn't use IDENTITY. If you are just trying to number
each row then why put that information in the table at all - just
display the numbers when you query it or print a report.
--
David Portas
SQL Server MVP
--|||Salim (s_aboobaker@.hotmail.com) writes:
> "I've created a customer table and I want to give each customer a
> unique id e.g 'ES001' But sql server won't let me I've looked at
> various books to no avail.

CREATE TABLE customers (id char(5) NOT NULLL,
...
CONSTRAINT pk_customers PRIMARY KEY (id))
go
INSERT customers (id, ...)
VALUES ('ES001', ...

> My second question is when I delete a row from a table which had an ID
> of 2 when I put in a new row it gives the new row and ID of 3 how do i
> make it give the row an ID of 2."

INSERT tbl (id, ...)
VALUES (2, ...)

As David said, don't use IDENTITY if you want to specify the key values
yourself.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ehere to begin? You are doing everything wrong because you do not
understand the basic concepts of RDBMS. A table is not a file; it
models an entity in reality. To find a unique identifier, you need to
look at your data model.

**by definition** the IDENTITY extension in T-SQL can never be a key.
It is a sequential numbering that id derived from the state of the
hardware at the time of insertion. It has nothing whatsoever to do
with your data model or the reality being modelled.

What you have is a file system written in SQL. We used to re-use fixed
length preallocated records in a lot of the early disk OS, just like
you are trying to do.

Please get a book on the basics before you learn all the kludges that
people will give you and learn to do it right.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Ehere to begin? You are doing everything wrong because you do not
> understand the basic concepts of RDBMS. A table is not a file; it
> models an entity in reality. To find a unique identifier, you need to
> look at your data model.
> **by definition** the IDENTITY extension in T-SQL can never be a key.
> It is a sequential numbering that id derived from the state of the
> hardware at the time of insertion. It has nothing whatsoever to do
> with your data model or the reality being modelled.

Where do you get all the crap from?

This is the original post?

"I've created a customer table and I want to give each customer a
unique id e.g 'ES001' But sql server won't let me I've looked at
various books to no avail.

My second question is when I delete a row from a table which had an ID
of 2 when I put in a new row it gives the new row and ID of 3 how do i
make it give the row an ID of 2."

No talk of IDENTITY, no talk about files. In fact it is obvious that
ES001 has nothing to do with IDENTITY.

> What you have is a file system written in SQL. We used to re-use fixed
> length preallocated records in a lot of the early disk OS, just like
> you are trying to do.
> Please get a book on the basics before you learn all the kludges that
> people will give you and learn to do it right.

All I can say to unexperienced people who are following this group is
to put Joe Celko in your kill files. He can't read the posts he replies
to properly. He repeats misconceptions about SQL Server all over again.
His examples are full of sloppy errors. His tone against unexperienced
users is unacceptable.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||s_aboobaker@.hotmail.com (Salim) wrote in news:d84b91a3.0501280315.1f9a13c4
@.posting.google.com:

> "I've created a customer table and I want to give each customer a
> unique id e.g 'ES001' But sql server won't let me I've looked at
> various books to no avail.
> My second question is when I delete a row from a table which had an ID
> of 2 when I put in a new row it gives the new row and ID of 3 how do i
> make it give the row an ID of 2."

Somebody's not getting their money's worth out of *this* consultant.

Help!!!! Restore file mdf after exec "Drop table..." to previous state.

Hi all.
First, sorry about my poor english.

I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks

Since you are in simple recovery model, you can't use transaction log backups and none of the recovery tools on the market will be able to help since they rely on the contents of the transaction log to get the data back. If you don't have a backup of the database, the data is gone.

|||

There is actually a VERY brute force way that you really aren't going to like. Dropping objects in SQL Server works very similar to deleting a file on the operating system. You don't actually wipe out the file, you just remove an 8 byte header that essentially says "I'm a valid file". In SQL Server, you remove the object from the SQL Server metadata. Once the entries for the object have been removed from sysindexes, SQL Server will no longer find the data. Unless you have done something to reallocate those pages to another object in which case everything will be wiped out on that page, you can actually get to the raw pages. This is NOT pretty. You can use the DBCC PAGE command to read a page. You would wind up running this one page at a time and extracting the data from each page as you go. Then you would have to reconstruct everything and manually reload the data. (Did I mention, you REALLY don't want to have to do this which is why you always take frequent backups.)

So, step 1 in this process is to immediately create a backup of the database. Step 2 is to immediately put a backup plan in place so that the database is being backed up on a routine basis. Then you can use the following set of commands to manually extract the data. Below is an example. You would substitute your database name. You would then start at file = 1 and page = 1 and incrementally walk through page 2, 3, 4, ... and then repeat the process for any other files present in the database. The final step in the process would be to test your backup strategy and continue to test it on a frequent basis. (If you don't keep a backup of a database, you are basically saying that it is unimportant and you don't care about losing it.)

--dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

dbcc traceon (3604)

dbcc page (pubs,1,88,3)

go

|||Thanks very much.
As you mentioned, i try to use DBCC PAGE and it has a lot of thing which i don't understand.
Please show me the way to read info from what DBCC Page return and meaning of those.
Thanks again.|||You are reading the raw storage on a page using this command. It is easier to just point you at a book. Go out and pick up a copy of "Inside SQL Server 2000" by Kalen Delaney. There are about 100 pages of material that relate back to interpreting and utilizing the output from DBCC PAGE.|||I will read it and find what i can do with that book.
You help me very much, thanks. :)|||BTW, the DBCC PAGE command is an undocumented command which means it is not supported by Microsoft (but nobody is stopping you from not using them). Try Googling around for some references on these undocumented commands, stored procedures and functions and you will find a lot of useful ones.|||I have read the book which you mentioned. I try to find out exactly what you mean when you said i can read pages in mdf file one by one but i don't get it.
Could you tell me which tools or language or anything else to perform that idea.
Thanks|||

If you query sysfiles within a database, you get the list of FileIDs. In general, if you have an mdf file, it will be fileID = 1.

So you use DBCC page to read each page manually by doing the following:

1. Open a query window.

2. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,1,3)

3. Read the contents of the page and extract any data that you need

4. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,2,3)

5. Read the contents of the page and extract any data that you need

6. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,3,3)

7. Read the contents of the page and extract any data that you need

8. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,4,3)

Etc. There isn't any tool to do this. You do this in Transact-SQL. Like I said when I brought this up, this is an absolute last resort if you can access the database, it is EXTREMELY ugly, EXTREMELY resource intensive, and EXTREMELY time consuming. Why, because you pull page 1, then page 2, then page 3, then page 4, then page 5, then.... until you get an error message that the page doesn't exist. Even in a small database, you can have hundreds or thousands of pages, so that means that in even a small database, you would have to execute DBCC PAGE hundreds or thousands of times, read the contents of the page, and manually extract the data. In production databases, you can easily have millions or billions or pages.

Now you get the idea why everyone jumps up and down about backups and why it would be nice if Microsoft could ship a 100 foot tall poster in the box with SQL Server that has exactly one message on it, backup your databases. If you don't have backups and you need to recover data, there are few, if any options, and you are not going to like ANY of the options.

|||Michael Hotek,
I already exec Dbcc page and i get a lot of things which i don't understand.
So, what do you mean when you said:"Read the contents of the page and extract any data that you need". How to read and extract it? By T-SQL or something else?
About my database, I give up.So, right now,I just want to know more about SQL and the way to extract the contents of the page.
Thanks
|||Copy it from the results window and paste it into Notepad, another Query window etc. This is a 100% manual process. There are zero tools. There are no shortcuts. There is no easy way to do this. In essence you are going to manually type back in every single piece of data within the database. If you go through about 100 pages in your database and look at the results, the data is pretty self-explanatory. Since you are starting at page 1, there are going to be entire pages that you aren't going to be able to do things with such as the PFS page, GAM page, SGAM page, index pages, etc. But, there are data pages in your database and when you hit one, it will be very apparent where the data is and what it means. Other than that, it's up to you. You have to look at the results pane and you have to highlight the data and copy it back out into something else in order to stick it back into your database.|||Michael Hotek
Now, I understand what you mean, thanks. :)sql

HELP!!!

please help me create the table for the data in the following data lines
after this
i am out of my wits to thing for what data type to use. because for
everything i tried i got the error below the column name will be for the
table i appreciate a lot if someone can help me .
column 1 column 2 column 3
OMIM_Name No.of av Description
100650 .0001 ALCOHOL INTOLERANCE, ACUTE ACETALDEHYDE DEHYDROGENASE 2, ALLELE
2, INCLUDED; ALDH2*2, INCLUDED ALDH2, GLU487LYS The ALDH2*2-encoded protein
has a change from glutamic acid (glutamate) to lysine at residue 487 (Yoshid
a
et al., 1984). Hempel et al. (1985) and Hsu et al. (1985) also showed that
the catalytic deficiency in mitochondrial ALDH in Orientals can be traced to
a structural point mutation at amino acid position 487 of the polypeptide. A
substitution of lysine for glutamic acid results from a transition of G-C to
A-T. To study the mechanism by which the ALDH2*2 allele exerts its dominant
effect in decreasing ALDH2 activity in liver extracts and producing cutaneou
s
flushing when the subject drinks alcohol, Xiao et al. (1995) cloned ALDH2*1
cDNA and generated the ALDH2*2 allele by site-directed mutagenesis. These
cDNAs were transduced using retroviral vectors into HeLa and CV1 cells, whic
h
do not express ALDH2. The normal allele directed synthesis of immunoreactive
ALDH2 protein with the expected isoelectric point and increased aldehyde
dehydrogenase activity. The ALDH2*2 allele directed synthesis of mRNA and
immunoreactive protein, but the protein lacked enzymatic activity. When
ALDH2*1-expressing cells were transduced with ALDH2*2 vectors, both mRNAs
were expressed and immunoreactive proteins with isoelectric points ranging
between those of the 2 gene products were present, indicating that the
subunits formed heteromers. ALDH2 activity in these cells was reduced below
that of the parental ALDH2*1-expressing cells. Thus, the authors concluded
that ALDH2*2 allele is sufficient to cause ALDH2 deficiency in vitro. Xiao e
t
al. (1996) referred to the ALDH2 enzyme encoded by the ALDH2*1 allele (the
wildtype form) as ALDH2E and the enzyme subunit encoded by ALDH2*2 as ALDH2K
.
They found that the ALDH2E enzyme was very stable, with a half-life of at
least 22 hours. ALDH2K, on the other hand, had an enzyme half-life of only 1
4
hours. In cells expressing both subunits, most of the subunits assemble as
heterotetramers, and these enzymes had a half-life of 13 hours. Thus, the
effect of ALDH2K on enzyme turnover is dominant. Their studies indicated tha
t
ALDH2*2 exerts its dominant effect both by interfering with the catalytic
activity of the enzyme and by increasing its turnover. Because genetic
epidemiologic studies have suggested a mechanism by which homozygosity for
the ALDH2*2 allele inhibits the development of alcoholism in Asians, Peng et
al. (1999) recruited 18 adult Han Chinese men, matched by age, body-mass
index, nutritional state, and homozygosity at the ALDH2 gene loci from a
population of 273 men. Six individuals were chosen for each of the 3 ALDH2
allotypes, i.e., 2 homozygotes and 1 heterozygote. Following a low dose of
ethanol, homozygous ALDH2*2 individuals were found to be strikingly
responsive with pronounced cardiovascular hemodynamic effects as well as
subjective perception of general discomfort for as long as 2 hours following
ingestion. Among 71 Japanese nondrinkers and 268 drinkers of alcohol, Liu et
al. (2005) found that drinkers had a significantly higher frequency of the
487glu allele. Individuals with the 487lys allele had an increased risk of
alcohol-induced flushing (odds ratio of 33.0)
100690 .0001 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1, ASN217LYS
In a 30-year-old woman with slow-channel congenital myasthenic syndrome
(601462), Engel et al. (1996) identified a heterozygous 651C-G transversion
in exon 6 of the CHRNA1 gene, resulting in an asn217-to-lys (N217K)
substitution at a conserved residue in the M1 transmembrane domain. The
mutation cosegregated with the disease through 3 generations. Functional
expression studies showed that the N217K mutation slowed the rate of AChR
channel closure, increased the apparent affinity for ACh, and enhanced
desensitization. Cationic overload of the postsynaptic region caused an
endplate myopathy
100690 .0002 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1, VAL156MET
In a patient with SCCMS (601462), Croxen et al. (1997) identified a
heterozygous 466G-A transition in the CHRNA1 gene, resulting in a
val156-to-met (V156M) substitution in a putative ACh-binding region of the
protein. Functional studies suggested that the V156M mutation stabilizes the
open state of the AChR channel
100690 .0003 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1, THR254ILE
In a 60-year-old woman whose myasthenic syndrome (SCCMS; 601462) had first
become symptomatic at the age of 16, Croxen et al. (1997) identified a
heterozygous 761C-T transition in the CHRNA1 gene, resulting in a
thr254-to-ile (T254I) substitution in the M2 transmembrane domain which line
s
the AChR channel pore. The patient was previously reported by Chauplannaz an
d
Bady (1994). Functional expression studies suggested that the T254I mutation
stabilized the open state of the AChR channel
C:\Disease_Database_TongBoon_Dec2005_Mar
ch2006\Results>bcp OMIM.dbo.av in
AVoutp
ut_less_then_100.txt -c -T
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
BCP copy in failedGood god! Please post a description of the source data, rather than posting
a
big blob. Also see http://www.aspfaq.com/etiquette.asp?id=5006 for some
pointers.
How are you importing this? Where are you importing it to? Where is it from?
What is the structure of the source, and what of the destination?
ML
http://milambda.blogspot.com/|||What vesrion are you using?
"urgent" <urgent@.discussions.microsoft.com> wrote in message
news:89CCAC1D-8F47-4D01-A35D-25254C26BD9B@.microsoft.com...
> please help me create the table for the data in the following data lines
> after this
> i am out of my wits to thing for what data type to use. because for
> everything i tried i got the error below the column name will be for the
> table i appreciate a lot if someone can help me .
>
> column 1 column 2 column 3
> OMIM_Name No.of av Description
> 100650 .0001 ALCOHOL INTOLERANCE, ACUTE ACETALDEHYDE DEHYDROGENASE 2,
> ALLELE
> 2, INCLUDED; ALDH2*2, INCLUDED ALDH2, GLU487LYS The ALDH2*2-encoded
> protein
> has a change from glutamic acid (glutamate) to lysine at residue 487
> (Yoshida
> et al., 1984). Hempel et al. (1985) and Hsu et al. (1985) also showed that
> the catalytic deficiency in mitochondrial ALDH in Orientals can be traced
> to
> a structural point mutation at amino acid position 487 of the polypeptide.
> A
> substitution of lysine for glutamic acid results from a transition of G-C
> to
> A-T. To study the mechanism by which the ALDH2*2 allele exerts its
> dominant
> effect in decreasing ALDH2 activity in liver extracts and producing
> cutaneous
> flushing when the subject drinks alcohol, Xiao et al. (1995) cloned
> ALDH2*1
> cDNA and generated the ALDH2*2 allele by site-directed mutagenesis. These
> cDNAs were transduced using retroviral vectors into HeLa and CV1 cells,
> which
> do not express ALDH2. The normal allele directed synthesis of
> immunoreactive
> ALDH2 protein with the expected isoelectric point and increased aldehyde
> dehydrogenase activity. The ALDH2*2 allele directed synthesis of mRNA and
> immunoreactive protein, but the protein lacked enzymatic activity. When
> ALDH2*1-expressing cells were transduced with ALDH2*2 vectors, both mRNAs
> were expressed and immunoreactive proteins with isoelectric points ranging
> between those of the 2 gene products were present, indicating that the
> subunits formed heteromers. ALDH2 activity in these cells was reduced
> below
> that of the parental ALDH2*1-expressing cells. Thus, the authors concluded
> that ALDH2*2 allele is sufficient to cause ALDH2 deficiency in vitro. Xiao
> et
> al. (1996) referred to the ALDH2 enzyme encoded by the ALDH2*1 allele (the
> wildtype form) as ALDH2E and the enzyme subunit encoded by ALDH2*2 as
> ALDH2K.
> They found that the ALDH2E enzyme was very stable, with a half-life of at
> least 22 hours. ALDH2K, on the other hand, had an enzyme half-life of only
> 14
> hours. In cells expressing both subunits, most of the subunits assemble as
> heterotetramers, and these enzymes had a half-life of 13 hours. Thus, the
> effect of ALDH2K on enzyme turnover is dominant. Their studies indicated
> that
> ALDH2*2 exerts its dominant effect both by interfering with the catalytic
> activity of the enzyme and by increasing its turnover. Because genetic
> epidemiologic studies have suggested a mechanism by which homozygosity for
> the ALDH2*2 allele inhibits the development of alcoholism in Asians, Peng
> et
> al. (1999) recruited 18 adult Han Chinese men, matched by age, body-mass
> index, nutritional state, and homozygosity at the ALDH2 gene loci from a
> population of 273 men. Six individuals were chosen for each of the 3 ALDH2
> allotypes, i.e., 2 homozygotes and 1 heterozygote. Following a low dose of
> ethanol, homozygous ALDH2*2 individuals were found to be strikingly
> responsive with pronounced cardiovascular hemodynamic effects as well as
> subjective perception of general discomfort for as long as 2 hours
> following
> ingestion. Among 71 Japanese nondrinkers and 268 drinkers of alcohol, Liu
> et
> al. (2005) found that drinkers had a significantly higher frequency of the
> 487glu allele. Individuals with the 487lys allele had an increased risk of
> alcohol-induced flushing (odds ratio of 33.0)
> 100690 .0001 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1,
> ASN217LYS
> In a 30-year-old woman with slow-channel congenital myasthenic syndrome
> (601462), Engel et al. (1996) identified a heterozygous 651C-G
> transversion
> in exon 6 of the CHRNA1 gene, resulting in an asn217-to-lys (N217K)
> substitution at a conserved residue in the M1 transmembrane domain. The
> mutation cosegregated with the disease through 3 generations. Functional
> expression studies showed that the N217K mutation slowed the rate of AChR
> channel closure, increased the apparent affinity for ACh, and enhanced
> desensitization. Cationic overload of the postsynaptic region caused an
> endplate myopathy
> 100690 .0002 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1,
> VAL156MET
> In a patient with SCCMS (601462), Croxen et al. (1997) identified a
> heterozygous 466G-A transition in the CHRNA1 gene, resulting in a
> val156-to-met (V156M) substitution in a putative ACh-binding region of the
> protein. Functional studies suggested that the V156M mutation stabilizes
> the
> open state of the AChR channel
> 100690 .0003 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1,
> THR254ILE
> In a 60-year-old woman whose myasthenic syndrome (SCCMS; 601462) had first
> become symptomatic at the age of 16, Croxen et al. (1997) identified a
> heterozygous 761C-T transition in the CHRNA1 gene, resulting in a
> thr254-to-ile (T254I) substitution in the M2 transmembrane domain which
> lines
> the AChR channel pore. The patient was previously reported by Chauplannaz
> and
> Bady (1994). Functional expression studies suggested that the T254I
> mutation
> stabilized the open state of the AChR channel
>
> C:\Disease_Database_TongBoon_Dec2005_Mar
ch2006\Results>bcp OMIM.dbo.av in
> AVoutp
> ut_less_then_100.txt -c -T
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> BCP copy in failed
>|||
this is my new nick i am urgen log in with another email thi post is being
transfer to another title call trouble with bcp ....2005

Wednesday, March 28, 2012

Help! Two-Part SQL Update


I have a child table with semicolon-delimited data in a single
column. Based on what is in the first part of the semicolon-delimited
data, I need to write a value to a column in the parent table and
then remove that first part of the child-table column. I guess I want
to do a
two-part SQL Update, but am not sure where to begin. The tables
look like this:
Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
)
Insert Into #ParentInfo( KeyCode )
Values( 'E1B296FCSYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '85829EDESYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( 'A5CB9CF5SYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '4CCF9C15SYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '40B5DB72SYSTEM' )
Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
VarChar(900) )
Insert Into #ChildInfo( KeyCode , Comments )
Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
in the Status column if "Demo Import" is in #ChildInfo.Comments, and
then remove
"Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.
Thanks.something like this should do:
begin tran
update p
set status = case when c.comments like 'Demo Import%' then 'Imported' else
status end
from #ParentInfo p, #ChildInfo c
where p.KeyCode=c.KeyCode
and p.KeyCode='E1B296FCSYSTEM'
if @.@.error<>0 rollback tran
update #ChildInfo
set comments = replace(comments,'Demo Import','')
where KeyCode='E1B296FCSYSTEM'
if @.@.error=0 commit tran
else rollback tran
-oj
"xenophon" <xenophon@.online.nospam> wrote in message
news:vfp0a1t3mn9r022b5r96h79srejao9ec05@.
4ax.com...
>
> I have a child table with semicolon-delimited data in a single
> column. Based on what is in the first part of the semicolon-delimited
> data, I need to write a value to a column in the parent table and
> then remove that first part of the child-table column. I guess I want
> to do a
> two-part SQL Update, but am not sure where to begin. The tables
> look like this:
>
> Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
> )
> Insert Into #ParentInfo( KeyCode )
> Values( 'E1B296FCSYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '85829EDESYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( 'A5CB9CF5SYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '4CCF9C15SYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '40B5DB72SYSTEM' )
> Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
> VarChar(900) )
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>
> So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
> in the Status column if "Demo Import" is in #ChildInfo.Comments, and
> then remove
> "Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.
> Thanks.
>
>
>|||I hope those are not the real names. A data element can be a code, but
it is *used* as a key, so key_code is nonsense. You NEVER name a data
element for how it is used in the physical schema; you name it for what
it is in the data model.
ParentInfo is also weird -- singlular so we have only one parent and
are there tables that do not store information? Likewise, a name like
status does not tell us "status of what?" when we read it.
Try something like this and avoid dangerous proprietary UPDATE ..
FROM.. syntax.
BEGIN
UPDATE Parents
SET foobar_status
= 'Imported'
WHERE EXISTS
(SELECT *
FROM Children AS C
WHERE Partents.foobar_id = C.foobar_id
AND comments LIKE 'Demo Import ; ' + '%');
UPDATE Children
SET comments = REPLACE(comments, 'Demo Import ; ','');
END;
While we have no DDL or specs, this scares me. You are in violation of
First Normal Form and it look like you are physically moving data from
table to table. That is how we did it with punch cards in the
1950's. You might want to talk to someone with RDBMS design
experience.|||1.
These are not real names. All names were changed and stripped
down to protect the guilty. :)
2.
The data was originally a mess, and what I was looking for was
a streamlined way to clean it up - this is a large multi-multi-
step process.
So there it is.
Thanks.
On 3 Jun 2005 11:59:46 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>I hope those are not the real names. A data element can be a code, but
>it is *used* as a key, so key_code is nonsense. You NEVER name a data
>element for how it is used in the physical schema; you name it for what
>it is in the data model.
[snip]

Help! T-SQL, Calling multiple stored procs?

I have to load a webpage table up with values from a database as such:

The Webpage Expects a Final result set of rows with 5 columns each built as below:

For each Row Returned by StoredProc1

Write Columns 1-3 = StoredProc2

Write Column 4 = StoredProc3

Write Column 5 = StoredProc4

Next Row


But I am not sure how to write the conditionals to CALL the stored procs i wrote and loop by a return value from StoredProc1.

Any help is much appreciated!

THANKS!

I would prefer to create separate stored proc with entire logic encapsulated within it, if I were you. Loops are fast and typical in client languages, but in SQL it's better not to be overused.|||So create just one big stored proc to do it all?
|||Yes. And calling stored procs from within stored procs is not so fast.|||

Can you be a little more specific about what each stored proc does? I mean, if it is a set of procs you are writing, then I doubt this is the best way to do it. I would be more inclined to write one stored procedure with one SELECT statement that joins 3 larger sets together (perhaps in temp tables) on the common value from a fourth set.

select *

from QueryFromSP1

outer join (QueryFromSP2) as QueryFromSP2
on QueryFromSP1.key = QueryFromSP2.key

...

The queries could be temp tables, or function calls, depending on the need, but a cursor like you have suggested will probably be a dog in performance (and not one of those really fast dogs either. Think hound dog on a hot Mississippi morning dogs.)

Help! Trying to create a trigger in SQL 2000...

Hello All!

I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.

Looks basically like this(This is my test tables):

CREATE TRIGGER EileenTest ON OrgCouponTestMain
FOR Insert
AS
declare @.emailaddress varchar(50)
declare @.body varchar(300)
declare @.fname varchar(50)
declare @.coupon varbinary(4000)

if update(emailaddress)
begin

Select
@.emailaddress=(select EmailAddress from OrgCouponTestMain as str),
@.fname=(select EmailAddress from OrgCouponTestMain as str)
@.Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)

SET @.body= 'Thank you' +' '+ @.fname +' '+ ',Here is the coupon you requested' +' ' + @.coupon
exec master.dbo.xp_sendmail
@.recipients = @.emailaddress,
@.subject = 'Coupon',
@.message = @.body
END

Hello my friend,

Try the following: -

SET @.body = 'Thank you ' + @.fname + ', Here is the coupon you requested ' + CAST(@.coupon AS VARCHAR(8000))

Kind regards

Scotty

|||

Hi there,

I've recently developed a functionality at my work place that is very similar to the one you need.

My suggestion is that you make things other way (very much simpler i think):

Instead of coding in T-SQL, you can develop your application in any .NET language. I suggest you to develop a Web Service or an ASP.NET page that retrieves the "destinations" from the SQL Server, loads the coupon and sends it to them.

Then you just have to develop a very simple COM Class that the ONLY thing it does is calling the Web Service (our ASP.NET page) you developed. There is many documentation on the Web on how to call a COM Class from an SQL Trigger / Job.

It's a bit tricky at the beggining but when you figure it out, it's easy as a walk in the park. And remember, when you need to change your application, you just have to change the .NET Web Service that it's called by the COM Class (much more friendly than coding in T-SQL).

Even if this isn't a web server, you can always create a website on localhost to host the service/page.

Hope I've helped you out!

gonzzas

|||

Thank you both for your input, Ask scotty, it didn't work, got the email out but no image, and of course there is that other option, which unless i can figure this out i will be doing instead. Thank you!

|||

So anyone else have an idea? This is what comes up in the email message, notice the symbols instead of the image...

Thank you Eileen, Here is the coupon you requested /

|||

Anyone else have a suggestion? Here is what comes in the email , notice the symbols instead of the image:

Thank you Eileen, Here is the coupon you requested /

|||

Hi,

The problem is focused on how to display the image which saved as Image Data type in database. Because what you get from database directly is binary steam of the image, it can't be displayed in your email directly. What I suggest is pass the steam (and other information ) from Stored Procedure to .NET, and use SqlDataReader to read the image and send the mail to your customer.

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image" ?https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=531518&SiteID=1

Hope it can helps. Thanks.

sql

Help! Top N in SQL Server?

Hi,

I'm working on a SQL Server project right now, and I'm not sure how to
approach one part. Basically I have a table full of orders for a
software program to process, then mark with the date/time to show it's
been finished.

What's in the Q at any time could be a few orders, or several hundred
thousand. So I don't want to return the whole query; only the first
chunk, then when the program is done with that it can move on and grab
more.

That means SELECT TOP N, except that N needs to be a variable. When
CPU and network traffic are free it should grab more rows, and when
demand is high, it should have a coffee break.

I've tried:

Create Procedure vwAutoQ
@.GrabRowCount Int = 100
As

Select Top @.GrabRowCount
[...]

From
[...]

Where
[...]

Order By
[...]

And I get the following error:

Server: Msg 170, Level 15, State 1, Procedure vwAutoQ, Line 5
Line 5: Incorrect syntax near '@.GrabRowCount'.

Is this possible, what I'm trying to do? Otherwise I'll need to drop
it down to ~15 and fire the proc a bunch of times...On 22 Nov 2004 18:17:28 -0800, Thug Passion wrote:

(snip)
>That means SELECT TOP N, except that N needs to be a variable. When
>CPU and network traffic are free it should grab more rows, and when
>demand is high, it should have a coffee break.

Hi Thug,

You can't use a variable on the TOP keyword. But there is a workaround:
use SET ROWCOUNT. This will take a variable.

SET ROWCOUNT @.GrabRowCount
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
SET ROWCOUNT 0

(Don't forget to set rowcount back to 0 after the query, as this is a
sticky setting: the limited rowcount remains active until you reset it or
drop the connection)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Various paging techniques discussed here:
http://www.aspfaq.com/2120

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@.giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @.sSQL NVARCHAR(500)

SELECT @.sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@.iNoRows) + ' rest of
string ' ...

EXEC(@.sSQL)|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@.giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @.sSQL NVARCHAR(500)

SELECT @.sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@.iNoRows) + ' rest of
string ' ...

EXEC(@.sSQL)|||> Hi Thug,

Hi!

> You can't use a variable on the TOP keyword. But there is a workaround:
> use SET ROWCOUNT. This will take a variable.

Awesome! I love it! That gets me exactly what I need, and except for
those two lines it doesn't change my SQL at all. I had no idea I
could use a variable with that type of (non-relational) command -
thanks very much!!|||> DECLARE @.sSQL NVARCHAR(500)

Hi,

Thanks for the response! I try to avoid this approach whenever
possible, it's gotten me in trouble in the past. I had a search
function in an SP that built a dynamic SQL command to take advantage
of indexes on whatever fields were passed in ( instead of a bunch of
like '%' statements ).

I declared a varchar(2000) to hold my command, and if I passed in
enough parameters, it came up to about 2300. But that was the last
thing I ever thought of to check...

help! substract two columns from two tables

I hope you can understand what I mean !
my english is not very good , i live in taiwan! i have a problem
table A :(income of year 2004) table B: (income of year 2003)
ITEM NAME INCOME INCOME growth
_______________________________________________
A 1000 500 -500
B 2000 2500 500
C 3000 1000 -200
How do I get then value "growth" , table A and table B are from
diferent datasource , if you know , please help me!! thank you very much!sorry ! growth of the third line(ITEM NAME = C) IS -2000
"Kline" wrote:
> I hope you can understand what I mean !
> my english is not very good , i live in taiwan! i have a problem
> table A :(income of year 2004) table B: (income of year 2003)
> ITEM NAME INCOME INCOME growth
> _______________________________________________
> A 1000 500 -500
> B 2000 2500 500
> C 3000 1000 -200
> How do I get then value "growth" , table A and table B are from
> diferent datasource , if you know , please help me!! thank you very much!|||Hello! Chris , your answer is helpful to me !! thanks a lot!
"Chris McGuigan" wrote:
> Kline,
> If by 'Table' you mean a Reporting Services Table, then you can't refer to
> items from different tables like that. Data regions in RS can only reference
> one dataset.
> When you say 'datasources' I am assuming you mean data on different servers.
> If so in MS SQL Server you can create "Linked Servers" to any OLE DB
> compliant datasource.
> If it's merely different databases on the same server, you just prefix the
> table name with the database and owner name, for example
> db2004.dbo.IncomeTable.
> The trick is to make one RS dataset pull the data from all the 'physical'
> datasources. Then you will have access to all your fields in one table.
> Your query may look something like this;
> SELECT A.Item as Item, A.Income as Income2004, B.Income as Income2003,
> B.Income - A.Income as Growth
> FROM ServerA.DB2004.dbo.IncomeTable as A
> JOIN ServerB.DB2003.dbo.IncomeTable as B
> You need to get all the data in one query and hence one RS table.
> Hope that helps.
> Chris McGuigan
> "Kline" wrote:
> > sorry ! growth of the third line(ITEM NAME = C) IS -2000
> >
> > "Kline" wrote:
> >
> > > I hope you can understand what I mean !
> > > my english is not very good , i live in taiwan! i have a problem
> > >
> > > table A :(income of year 2004) table B: (income of year 2003)
> > > ITEM NAME INCOME INCOME growth
> > > _______________________________________________
> > > A 1000 500 -500
> > > B 2000 2500 500
> > > C 3000 1000 -200
> > >
> > > How do I get then value "growth" , table A and table B are from
> > > diferent datasource , if you know , please help me!! thank you very much!

Friday, March 23, 2012

help! question about hardware request for SQL Server and Analysis Services

I built a database with a huge table which has 18 billion lines for 6(or 7) columns.

And in the Analysis Services the table is used to create a dimention and a measure.

One of the other two dimentions is made form a 4000 line table,and the other is

made from a 3 million line table(time dimension table).

The specification of my Server is Windows Server 2003 with Xeon Intel Cpu 5160 @.3.00GHz,

2.99GHz,4.00G RAM.

The problem comes out when i start the processing of Analysis Services.

the "time out" error comes out after 1 hour, however, the performance i need

is completing the Processing for one cube within 10 munites.

I want to know the hardware request to reach my needs and how to speed the

processing of Analysis Services.

Thank you

So, to be clear, you have an 18 billion row fact table, you have built a cube on top of it and you want a full process to complete in 10 minutes?

Chris

|||

Dear Mr

yes! I wanna a full process within 10 minutes.

Can it be done?

thanks in advance

tomigisi

|||

The short answer is no, not if you're using MOLAP storage for your measure group. The best throughput I've ever seen for cube processing with MOLAP storage is around 200000 rows per second, and that was a maximum rather than something that could be consistently achieved throughout the whole process; you should be happy with 60-70000 rows per second if you've got a top-end server and a properly-tuned relational datasource.

I would experiment with using ROLAP or HOLAP storage, or with pre-aggregating your fact table in some way to reduce the overall number of rows.

Sorry,

Chris

|||

We have a monster server: 8 - 3.2GHZ processors, a disk frame that reads/writes 400 MB/sec, 32 GB of ram, etc and we couldn't even process 18 Billion rows that fast. The slowdown is reading rows from the DB, and most likely not the server (unless you have slow disks).

sql

Help! Query Question.

Hi all,
I'm new to SQL programming, and I am having a hard time figuring this one ou
t.
I have a table containing the following information:
Activity Cost Account Hours
1 A 100
1 B 200
1 C 250
1 D 100
2 A 600
2 F 200
3 B 100
3 C 200
3 D 400
I would like to create a view that will show the Cost Account with the
highest hours per activity. For the table above, the end result would be
something like this.
Activity Cost Account
1 C
2 A
3 D
I hope this makes sense. Can someone please help me on how to write the SQL
statement for this? Thank you very much in advance!
RogerI'm not sure what you want to do in the case where 2 accounts have the same
number of hours (tied for the most). I'm going to assume you want two rows
returned.
SELECT outer_table.Activity, derived_table.[Cost Account]
From Unnamed_table as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM Unnamed_table
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:

> Hi all,
> I'm new to SQL programming, and I am having a hard time figuring this one
out.
> I have a table containing the following information:
> Activity Cost Account Hours
> 1 A 100
> 1 B 200
> 1 C 250
> 1 D 100
> 2 A 600
> 2 F 200
> 3 B 100
> 3 C 200
> 3 D 400
> I would like to create a view that will show the Cost Account with the
> highest hours per activity. For the table above, the end result would be
> something like this.
> Activity Cost Account
> 1 C
> 2 A
> 3 D
> I hope this makes sense. Can someone please help me on how to write the S
QL
> statement for this? Thank you very much in advance!
> Roger|||Do:
SELECT t1.activity, t1.Cost
FROM tbl t1
WHERE t1.hours = ( SELECT MAX( t2.hours )
FROM tbl t2
WHERE t2.Activity = t1.Activity )
ORDER BY t1.Activity ;
-- Or
SELECT t1.activity, t1.Cost
FROM tbl t1
INNER JOIN ( SELECT Activity, MAX( hours )
FROM tbl
GROUP BY Activity ) t2 ( Activity, hours )
ON t1.Activity = t2.Activity
AND t1.hours = t2.hours
ORDER BY t1.activity ;
Anith|||Hi Anith,
Thank you for the quick response. I am a little about the SQL
statement. I only have one table and I want to create a view from that
table. But your SQL statement has 2 tables. Can you please clarify? I
think maybe most post wasn't clear. Thank you very much.
Roger
"Anith Sen" wrote:

> Do:
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> WHERE t1.hours = ( SELECT MAX( t2.hours )
> FROM tbl t2
> WHERE t2.Activity = t1.Activity )
> ORDER BY t1.Activity ;
> -- Or
>
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> INNER JOIN ( SELECT Activity, MAX( hours )
> FROM tbl
> GROUP BY Activity ) t2 ( Activity, hours )
> ON t1.Activity = t2.Activity
> AND t1.hours = t2.hours
> ORDER BY t1.activity ;
> --
> Anith
>
>|||Did you try to execute the SQL that either of us posted?
It returns what you want.
Anith gave 2 versions.
The first is called a correlated subquery. But, it basically joining your
table to a sql statement against the same table to figure out the max per
Activity.
The second (which is the same as what I posted) is called a derived table or
inline view. Basically, on the fly create a table that contains the activit
y
and its maximum hours from all rows for that activity. Then join this
derived table with the regular table, to only find the rows that match the
activity and the max hours found in the derived table.
There is no way to do what you want with SQL with only the one table in the
FROM. Instead you need join your table to some sql that gives you some
specific information about your table in this case, you need to figure out
the maximum hours for each activity so that you can figure out the correct
rows to return from your table.
My solution, and both of Anith's solution would work fine as the source of a
view to do exactly what you are trying to do.
Hope this makes sense.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Anith,
> Thank you for the quick response. I am a little about the SQL
> statement. I only have one table and I want to create a view from that
> table. But your SQL statement has 2 tables. Can you please clarify? I
> think maybe most post wasn't clear. Thank you very much.
> Roger
>
> "Anith Sen" wrote:
>|||Hi Ryan,
Thank you for the response. Yes, I want to show both cost accounts if there
is a tie. I am a little about the outer_table and derived_table.
Supposed my table is called t1, how would the SQL be written? Thank you ver
y
much.
Roger
"Ryan Powers" wrote:
> I'm not sure what you want to do in the case where 2 accounts have the sam
e
> number of hours (tied for the most). I'm going to assume you want two row
s
> returned.
> SELECT outer_table.Activity, derived_table.[Cost Account]
> From Unnamed_table as outer_table
> INNER JOIN
> (SELECT Activity, MAX(Hours) as Hours
> FROM Unnamed_table
> GROUP BY Activity) as derived_table
> ON outer_table.Activity = derived_table.Activity
> AND outer_table.Hours = derived_table.Hours
> ORDER BY Activity
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>|||just replace unnamed_table with t1.
as in
SELECT outer_table.Activity, derived_table.[Cost Account]
From t1 as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
Also, see my other post to you. I tried to explain the derived table and
Anith's correlated subquery to you. Neither of which is easy to explain in
a
couple sentences if you are new.
But, basically here is another attempt
Think of the table within the () as its own table completely separate from
your t1.
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity)
This will give you each activity and the max hours for that activity. One
row per activity.
Now you join your table t1 to that new table (which I called derived_table)
on both activity and hours. This helps you identify the rows in t1 that you
are interested in.
Hope this helps clear it up.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Ryan,
> Thank you for the response. Yes, I want to show both cost accounts if the
re
> is a tie. I am a little about the outer_table and derived_table.
> Supposed my table is called t1, how would the SQL be written? Thank you v
ery
> much.
> Roger
> "Ryan Powers" wrote:
>|||Got it! Thank you very much!!!
"Ryan Powers" wrote:
> Did you try to execute the SQL that either of us posted?
> It returns what you want.
> Anith gave 2 versions.
> The first is called a correlated subquery. But, it basically joining your
> table to a sql statement against the same table to figure out the max per
> Activity.
> The second (which is the same as what I posted) is called a derived table
or
> inline view. Basically, on the fly create a table that contains the activ
ity
> and its maximum hours from all rows for that activity. Then join this
> derived table with the regular table, to only find the rows that match the
> activity and the max hours found in the derived table.
> There is no way to do what you want with SQL with only the one table in th
e
> FROM. Instead you need join your table to some sql that gives you some
> specific information about your table in this case, you need to figure out
> the maximum hours for each activity so that you can figure out the correct
> rows to return from your table.
> My solution, and both of Anith's solution would work fine as the source of
a
> view to do exactly what you are trying to do.
> Hope this makes sense.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>

Wednesday, March 21, 2012

Help! Need to extract date/time from SQL database table

Please help! How do I extract mm/dd/yyyy hh:nn from a timestamp field located in an MS SQL 2000 database table?

I can do this in MS Access where , for example only, I can convert 56323938 to "07/24/2006 11:47"

But when i attemp to extract the same info from the same field in SQL Query Analyzer, I only get "07/24/2006 00:00".

Can someone help me ? Is there a stored procedure or sql string that I can use that will in fact return the "mm/dd/yyyy hh:nn" from a timetamp field (where the hh:nn is actual and not fillers like "00:00"?

:)Dear,
The ODBC and OLE DB timestamp data type records dates and times. The Transact-SQL timestamp data type is a binary data type with no time-related values.
May You get some idea from this.otherwise there should be no problem in retrieving time through (select <date_column> from <table>),i suppose.
Regards,
Lipsa|||Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg|||I now recall how i got the mm/dd/yyyy hh:mm am/pm from a timestamp

I used the following string:

select convert(datetime,cast( [timestamp] as decimal)/1440 + 1,101) from table

(timestamp is the sql encoded stamp integer field stored on a table. By converting the [timestamp] field to decimal instead of "datetime" allows the hh:mm:ss to be extracted where the "/ 1440 + 1" extracts the date from the timestamp.

:)

Quote:

Originally Posted by MikeG

Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg

Help! Need assistance!

I have a table with following columns:
Tutorial Number
Lesson Number
Order Number
where each tutorial has many lessons and there is a order number for
each lesson within that tutorial.
In the UI, once the user re-sorts the lessons in any manner which they
like, I am to store the new order as is the order in the datagrid.
How can I do that?
what should the Stored Procedure do?
thanks in advance,On 30 Sep 2005 14:15:08 -0700, nashak@.hotmail.com wrote:

>I have a table with following columns:
>Tutorial Number
>Lesson Number
>Order Number
>where each tutorial has many lessons and there is a order number for
>each lesson within that tutorial.
>In the UI, once the user re-sorts the lessons in any manner which they
>like, I am to store the new order as is the order in the datagrid.
>How can I do that?
>what should the Stored Procedure do?
>thanks in advance,
Hi nashak,
Homework assignment for school?
I think you need one stored procedure, to move one lesson to a new
location. Input parameters would be the tutorial number, the lesson to
be moved and the new location (either "before" or "after" another
lessen; in both cases you'll need a special value for "end of list" or
"start of list").
Draw some examples, think about possible cases (move forward - move
backward - "move", but new location = old location - etc), and work out
how in each of these cases the assigned Order Numbers should change.
Then translate those rules into SQL, hand in the assignment to your
teacher, and hope he or she doesn't read this group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Wish it were just as simple as homework assigment.. :-)
The actual columns are different. I thought of these just as a sample.
What I am trying to do is as follows:
My table stores the Tutorial, lessons in that tutorial and the order of
the lessons.
The datagrid in my UI has been bound to the dataset that I created with
the above table. This grid has buttons to move the lessons up or
down(and thereby changing the order) as well as a checkbox to delete
the lesson from the tutorial. I need to first delete all the deleted
lessons and then capture the order of lessons and store this new order
in the "OrderOfLesson" column.
One way I think is to put a client side script that would change order
at every button click and store that order as well as change status of
rows that have been deleted. Then just update the database by calling
update() of the datatable. Is there some example code of the above that
i could look at?|||Some more stuff:
The table stores Order of lessons. However the Datagrid does not
display the order number. Is there anyway for the dataset to contain
order number column but for the Datagrid to not display that column?
In the above case, I could then at the Save event
(1) Go thru dataset and re-order all the rows and then call Update() of
dataset to update the rows in the database.
Does this sound possible?|||On 1 Oct 2005 08:06:35 -0700, nashak@.hotmail.com wrote:

>Hugo,
>Wish it were just as simple as homework assigment.. :-)
>The actual columns are different. I thought of these just as a sample.
>What I am trying to do is as follows:
>My table stores the Tutorial, lessons in that tutorial and the order of
>the lessons.
>The datagrid in my UI has been bound to the dataset that I created with
>the above table. This grid has buttons to move the lessons up or
>down(and thereby changing the order) as well as a checkbox to delete
>the lesson from the tutorial. I need to first delete all the deleted
>lessons and then capture the order of lessons and store this new order
>in the "OrderOfLesson" column.
>One way I think is to put a client side script that would change order
>at every button click and store that order as well as change status of
>rows that have been deleted. Then just update the database by calling
>update() of the datatable. Is there some example code of the above that
>i could look at?
Hi nashak,
Not a homework assignment, then. Okay. Sorry for being suspicious.
Anyway, that doesn't change the rest of my answer: you should have a
stored procedure that you can call to move a lesson around in the order.
The body of the stored procedure would probably consist of some IF's to
determine which of the various cases applies (giving one order a higher
place in the list, giving one order a lower place in the list, maybe
some more as well), and one UPDATE statement for each of the cases.
Here's the outline of an UPDATE statement that will move the lesson that
held the 6th place to the 3rd place (and simulteneously move the lessons
that were at 3rd, 4th and 5th place one place down):
UPDATE Lessons
SET Place = CASE WHEN Place = 6 THEN 3 ELSE Place + 1 END
WHERE Place BETWEEN 3 AND 6
AND other criteria
The real procedure would use variables, of course :-)
If you need more help, then please provide some more information about
your tables (as CREATE TABLE statements) and sample data (as INSERT
statements) that I can use to build and test the procedure. For tips on
how to assemble this information: www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello,
I did think of that. However the concern that I have is that this SP is
called everytime the user moves a lesson up and down. i.e if lesson
goes up I do order-- for that and order++ for the previous one. Too
many db calls.
Instead, I would like to see if I can let the user do whatever he wants
and then try to store the complete data from grid/dataset with a new
order starting from the first row.
Thanks,|||A general SP for re-ordering a sequential column has been posted
before.|||On 1 Oct 2005 15:13:03 -0700, nashak@.hotmail.com wrote:

>Hello,
>I did think of that. However the concern that I have is that this SP is
>called everytime the user moves a lesson up and down. i.e if lesson
>goes up I do order-- for that and order++ for the previous one. Too
>many db calls.
>Instead, I would like to see if I can let the user do whatever he wants
>and then try to store the complete data from grid/dataset with a new
>order starting from the first row.
>Thanks,
Hi nashak,
The advantage of using "too many db calls" is that changes are persisted
directly. No data loss if the client crashes. Of course, that's a down
side as well, as you'll either have to settle for no "abort all changes"
button, or use a long running transaction that will cause blocking and
hurt concurrency.
If you prefer using less db calls, build an app roughly like this:
- Get lessons and their relative positions from DB; store into array.
- Allow user to change order. Note changes in array.
- If user decides to abort changes, do nothing.
- If user decides to accept changes, call stored procedure that updates
order column for all lessons in one UPDATE statement. Do check if
someone else made changes in the meantime (you can use a rowversion
column for this - google "optimistic locking" for more details).
- If stored procedure reports changes by other users, go back to step 1
(or allow user to choose to override changes by other, or ...)
Getting the changed information back to the stored procedure requires an
array parameter. Unfortunately, there is no such thing in SQL Server.
See http://www.sommarskog.se/arrays-in-sql.html for some alternatives.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

HELP! More deleted records!

I have a replicated database and hundreds of records keep "disappearing" from
one table in particular. The table is a child table storing the answers to a
number of questions. Also;
1. The tables have referential integrity enforced, with cascade delete.
2. The relationships are enforced for replication.
3. There are no stored procedures which delete records from the offending
table.
4. There is no way of manually deleting records directly from the offending
table using the front-end application.
5. The parent tables have exactly the same records in as previously.
6. There are no replication conflicts.
Any ideas?
I am keeping transaction logs for 3 weeks. Is there any way that I can find
out from the transaction logs when the records were deleted? Is there any way
I can find out why the records would have been deleted?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
Nigel,
you don't say if this is on the publisher or on the subscriber, but I'm
guessing the latter. It could be that some updates are replicated as a
delete-insert pair for transactional replication in ceertain cases (see
http://support.microsoft.com/default...NoWebContent=1).
Perhaps this is your case?
Also I'm not clear why you're using cascading constraints for the subscriber
which is normally treated as RO - or maybe I have misunderstood?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
It's an ftp merge replication. I'm not sure where the records are
disappearing from first but the publisher is definately causing the other
subscribers (of which there are about 12) to lose the records too.
The relationships (i.e. cascade delete and enforce for replication) were
automatically created when the subscription was first created from the
publisher.
With the delete-insert pair the records would be recreated wouldn't they?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
|||In fact, when I use your spBrowseMergeChanges procedure on the offending
table, there are a load more records marked for deletion and without a
replacement!
Nigel Taylor
http://www.tinit.co.uk/
|||Nigel,
as you're using merge, and assuming you have all the related tables being
replicated, I'd modify all relationships to be not enforced for replication.
The delete-insert pairs are a problem for transactional and not merge, but
with merge there are other reasons the FK constraints are not applied (see
http://www.replicationanswers.com/Me...derArticle.asp). This
will simplify the setup somewhat and hopefully allow us to see if this is
the cause of the problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, thanks Paul. I've removed the 'enforce relationship for replication'
flag and removed the cascade deletes on the offending tables. Hopefully it'll
work and I can get back to doing something else.
Cheers,
Nigel Taylor
http://www.tinit.co.uk/

Help! Join question

Hi, All
I have two tables as below, TABLE1 and TABLE2.
TABLE 1: Base
ID PName PPrice
--
1 A 30
2 B 20
TABLE 2: History
ID Ldate Amount
--
1 2005/8/7 50
The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
it will return the result as below:
Ldate PName Amount
--
2005/8/7 A 50
2005/8/7 B null
and when I pass the date of 2005/8/8, it will return the result as below:
Ldate PName Amount
--
2005/8/8 A null
2005/8/8 B nullHere you go..
CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
CREATE TABLE #History(id int, Ldate datetime, amount int)
INSERT INTO #Base VALUES(1, 'A',30)
INSERT INTO #Base VALUES(2, 'B',20)
INSERT INTO #History VALUES(1,'20050807',50)
DECLARE @.DateParam datetime
SET @.DateParam = '20050807'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
SET @.DateParam = '20050808'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Cool! Thomas. That is what i need.
Many Thanks
"Roji. P. Thomas" wrote:

> Here you go..
>
> CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
> CREATE TABLE #History(id int, Ldate datetime, amount int)
> INSERT INTO #Base VALUES(1, 'A',30)
> INSERT INTO #Base VALUES(2, 'B',20)
> INSERT INTO #History VALUES(1,'20050807',50)
> DECLARE @.DateParam datetime
> SET @.DateParam = '20050807'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
> SET @.DateParam = '20050808'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "OKLover" <OKLover@.discussions.microsoft.com> wrote in message
> news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
>
>|||Hi
CREATE TABLE #t1
(
rowid int not null primary key,
pname char(1) not null,
pprice decimal(5,2)
)
insert into #t1 values (1,'A',20)
insert into #t1 values (2,'B',30)
CREATE TABLE #t2
(
rowid int ,
ldate datetime not null,
amn decimal(5,2)
)
insert into #t2 values (1,'20050807',20)
select coalesce(Ldate,'20050808'), PName,sum(pprice+amn)
from #t1 left join #t2
on #t2.rowid=#t1.rowid
and #t2.ldate='20050808'
group by Ldate, PName
Note: you will have to change a coded date value to the parameter.
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Hi
Probably you can try this
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
where B.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"OKLover" wrote:

> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign ke
y.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Your solution will not work because you put the joining condition in the
where clause.
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
> Hi
> Probably you can try this
> declare
> @.compDate datetime
> set @.compDate = '20050807'
> select ISNULL(Ldate,@.compDate), PName, Amount
> from Base B
> full join History H on H.ID=B.ID
> where B.ldate=@.compDate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "OKLover" wrote:
>|||sorry! thank you for the correction
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
and H.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roji. P. Thomas" wrote:

> Your solution will not work because you put the joining condition in the
> where clause.
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
>
>

HELP! Is this a potential locking nightmare??

The following table is used to store the points earned each month by
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:

>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.

>if
>100 customers purchasing from the same salesman are on-line at the same tim
e
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).

> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5

>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.
4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:

>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increas
e
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
Hugo Kornelis, SQL Server MVP

HELP! Is this a potential locking nightmare??

The following table is used to store the points earned each month by
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.
>if
>100 customers purchasing from the same salesman are on-line at the same time
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).
> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5
>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
--
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>>We used to add to this table as each customers application was
>>completed.
>>For some reason we have been asked to make it realtime.
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>>if
>>100 customers purchasing from the same salesman are on-line at the same
>>time
>>is
>>this going to be a potential locking issue?
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>> These updates are done within a
>>transaction. Since the sales structure does have an "upline" type of
>>structure,
>>these points have to trickle up to several peoples entries in an
>>all-or-nothing fashion.
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>>CREATE TABLE [dbo].[tblMonthlyPoints](
>> [lngSalesPersonID] [int] NOT NULL,
>> [intMonth] [tinyint] NOT NULL,
>> [intYear] [int] NOT NULL,
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increase
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
--
Hugo Kornelis, SQL Server MVP

Help! INSERT Replication not working on Subscriber FOR ONE TABLE ONLY

Everything is working great except ONE table is not replicating INSERTS from
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison

Monday, March 19, 2012

Help! Indexing LARGE table

OK, I imported 680 million records into an unindexed table. That went well.

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Help! Thanks in advance!OK, I imported 680 million records into an unindexed table. That went well.

Well what? Into the night?

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

Yeah, you'd want to do that with almost a billion records (hey let's not split hairs)

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

Pray?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Nice box...do you have to buy it dinner?

Help! Thanks in advance!

Help with what? You defenitley DONT want to kill it...it'll take twice as long to rollback.

Seems you get a lot of these. I would definetly start padding my estimates...

OR, I would think about performance before...

Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

But that's just me

OH

MOO

Good Luck|||I agree with Brett that killing the job will be bad. A clustered index looks to order the rows of the table on the disk itself. If SQL Server decides that these rows are in exactly the revers order of what they should be, then you are going to see an awful lot of data move around. Your transaction log is probably going nuts, too.
As for speeding it up, there isn't a lot you can do while the process is in flight. If this import is a normal thing, then you can consider partitioning the table on some unique key, and cutting it down to seven 100 million row tables. Also, for non-clustered indexes, you can get a boost from the SORT_IN_TEMPDB option. Take a look in BOL for that.|||Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

Yes, horizontal partitioning will be a necessary move. My data isn't primarily organized by date so that wouldn't make an ideal partition criteria. I haven't done this before and am nervous about doing it correctly; mistakes at the planning phase will be hard to correct.

Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.|||ok, it just finished rolling back. The data file was on a 150GB SAN partition and ran out of space. It was previously like 73GB so I'm surprised that happened. any way, at least I have the database back...

thanks guys|||Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.

Well I was trying to get you to laugh..not to make fun of you

Why not post the DDL of the table...|||Got me to laugh, anyway. Does that count as a near miss?

Roger: At this point, maybe you can try the index as a non-clustered index. Let us know what happens.|||Is there any chance the import file is already sorted? If so you can create the clustered index on the empty table and use the ORDERED argument with BCP to prevent SQL from resorting the data.|||Well I was trying to get you to laugh..not to make fun of you

Brett's gone off to Flordia for vacation. We can make fun of him while he is gone. :p