Please explain Transaction for me

I found this on http://msdn2.microsoft.com/en-us/library/86773566.aspx which i am hoping may be able to resolve my problem but I am not sure I actually understand it.

Where it shows

// Start a local transaction.
 transaction = connection.BeginTransaction("SampleTransaction");

is the "SampleTransaction" nothing more then just a name of the transaction that is being created through this code or is it actually pointing to something?

I believe that I understand the rest.

Oh and in case you have a better way of doing what I am needing here is the SQL Query that is pulling info for me to display on a page

SELECT p.product_ID, p.class_ID, p.category_ID, p.product_name, p.product_desc, p.product_image, p.product_dimension, p.product_o1, p.product_o2,
p.product_o3, p.product_ac, p.product_ph, p.product_photo, pcl.class_name, pca.category_name
FROM products AS p INNER JOIN
productClass AS pcl ON p.class_ID = pcl.class_ID INNER JOIN
productCategories AS pca ON p.category_ID = pca.category_ID

Basically I am using the transaction to perform multiple updates seeing as how I will need to update multiple tables.

privatestaticvoid ExecuteSqlTransaction(string connectionString)
{
using (SqlConnection connection =new SqlConnection(connectionString))
 {
 connection.Open();
 SqlCommand command = connection.CreateCommand();
 SqlTransaction transaction;
// Start a local transaction.
 transaction = connection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
 command.Connection = connection;
 command.Transaction = transaction;
try
 {
 command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
 command.ExecuteNonQuery();
 command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
 command.ExecuteNonQuery();
// Attempt to commit the transaction.
 transaction.Commit();
 Console.WriteLine("Both records are written to database.");
 }
catch (Exception ex)
 {
 Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
 Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
 {
 transaction.Rollback();
 }
catch (Exception ex2)
 {
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
 Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
 Console.WriteLine(" Message: {0}", ex2.Message);
 }
 }
 }
}

It's a name given to the transaction to be used in RollBack and SavePoint

SqlTransaction.Rollback (String)

Save method.

Read this inRemarksection atMSDNhttp://msdn2.microsoft.com/en-us/library/k1562zz1.aspx

sql

Please explain this.., why is PasswordRecovery creating another sqlservr process....

Why is the PasswordRecovery control creating another sqlservr process after I successfully enter the user name, and for a reason I have not found yet it keeps the database in a read only modem then when I answer my security question correctly it gives me the error:

Failed to update database "C:\INETPUB\WWWROOT\VER1.0.0.1\APP_DATA\ASPNETDB.MDF" because the database is read-only.

I read other post regarding the read only message and I don't if there doing the same thing, but why does this happen and what is the solution to this other not using the control?

cancel that!... found solution from another post...

Please explain this SQL Trigger

I am new to triggers.
I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @.counter = 1
When does this counter change value?

USE Northwind
GO
CREATE TRIGGER PriceCheck
ON [Order Details]
FOR INSERT
AS
DECLARE @.counter int
SET @.counter= @.@.ROWCOUNT
DECLARE @.unitprice money
DECLARE order_details_insert_cursor CURSOR FOR
SELECT Unitprice
FROM inserted
IF @.counter = 1
BEGIN
IF(SELECT UnitPrice FROM inserted) > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
OPEN order_details_insert_cursor
FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.unitprice > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
RETURN
--Exit trigger immediately
END

FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
END

CLOSE order_details_insert_cursor
END

DEALLOCATE order_details_insert_cursor
The select statement checks to see what the inserted value is, and ifit's greater than 300, its rolling back the attempted insert as beingoutside of the range of allowed values. As an aside, this isn'treally a good use of a trigger. A check constraint would be justas good, and would perform better.
The counter is set to the number of rows being inserted, so it'll beequal to the number of rows in the set being inserted into thetable. Theoretically, it could have a different value for everyattempted insert.

|||Thanks - I am trying to follow the logic of it and wondered if it works from top to bottom how does it come back to evaluate the counter? I can't see a looping structure in there.
Also could you explain what is meant by 'inserted' as in SELECT Unitprice
FROM inserted
Is 'inserted' a reserved word. Usually I would expect to see a table name there.|||The counter is set when the trigger is run. It isn't changedduring the execution of the of the trigger, so the value that isinitally set is the only value that it'll have until the next time thatthe trigger is fired.
"inserted" is a table, as you would expect. "inserted" and"deleted" are the names of two special internal tables that SQL Serveruses specifically to expose data being changed by INSERT, UPDATE, andDELETE statements to triggers.

Please explain automatic identity range management

Hi,
MS SQL Server 2005 RTM. Could anyone please point me to a good source
describing how the automatic identity range management works for merge
repliaction or explain it? In particular, how the options of
Publisher/Subscriber identity range size in Managemen Studio work,
preferrably with examples. I use merge replication between Express and
Standart editions and non-overlapping partitions. Having read some
documentation on this subject I came to conclusions, which contradict the way
everything actually works. For example, if i have an empty table with an
identity column, and I define Publisher identity range size 20000 and
Subscriber identity range size 20000, I expect that the first 20k rows on
Publisher will have identities 1-20000, but the first subscriber will get
ranges 20001 - 40000 and 40001 - 60000. What I get is pretty much different
from that. Although no records were inserted into the table at the Publisher
the first Subscriber starts numbering at 160022, the second - at 80002 and so
on. What's going on?
-- Many thanks, Oskar
Basically there is a 100% buffer in the ranges to prevent the ranges from
being blown between syncs. So the first range is 20,001-40,000 (but with the
buffer it really is 20,001 to 60,000) and the second range is 80,0001 to
100,000 (but with the buffer it is 80,000 to 120,000). It further more seems
like there is a third subscriber somewhere where owns the range from 120,001
to 140,000 (but with the buffer it is 120,001 to 160,000), and then you are
looking at a 4th subscriber which is from 160,001 to 180,000 (but with the
buffer it is from 160,001 to 200,000).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:BB90CCE0-55E0-4C39-9F8E-5E13DC4FFD2F@.microsoft.com...
> Hi,
> MS SQL Server 2005 RTM. Could anyone please point me to a good source
> describing how the automatic identity range management works for merge
> repliaction or explain it? In particular, how the options of
> Publisher/Subscriber identity range size in Managemen Studio work,
> preferrably with examples. I use merge replication between Express and
> Standart editions and non-overlapping partitions. Having read some
> documentation on this subject I came to conclusions, which contradict the
> way
> everything actually works. For example, if i have an empty table with an
> identity column, and I define Publisher identity range size 20000 and
> Subscriber identity range size 20000, I expect that the first 20k rows on
> Publisher will have identities 1-20000, but the first subscriber will get
> ranges 20001 - 40000 and 40001 - 60000. What I get is pretty much
> different
> from that. Although no records were inserted into the table at the
> Publisher
> the first Subscriber starts numbering at 160022, the second - at 80002 and
> so
> on. What's going on?
> -- Many thanks, Oskar
>
|||Thanks Hilary. In fact I found the answer to my question almost myself. Thing
is that MS SQL Server 2005 keeps track of every identity range it has ever
assigned to a subscriber at each reinitialization. Each time a subscriber is
initialized, new identity range is assigned to it and no longer can be used
by other subscribers or the same subscriber if it needs to be reinitialized.
So this explains, why identity ranges didn't start at values I expected.
-- Thanks, Oskar.
"Hilary Cotter" wrote:

> Basically there is a 100% buffer in the ranges to prevent the ranges from
> being blown between syncs. So the first range is 20,001-40,000 (but with the
> buffer it really is 20,001 to 60,000) and the second range is 80,0001 to
> 100,000 (but with the buffer it is 80,000 to 120,000). It further more seems
> like there is a third subscriber somewhere where owns the range from 120,001
> to 140,000 (but with the buffer it is 120,001 to 160,000), and then you are
> looking at a 4th subscriber which is from 160,001 to 180,000 (but with the
> buffer it is from 160,001 to 200,000).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:BB90CCE0-55E0-4C39-9F8E-5E13DC4FFD2F@.microsoft.com...
>
>

Please advise on approach

Hi,
Apologies for the vague subject title but I couldn't think of a snappy
title to describe what I am doing. Please bear with me as I explain...
I am developing an application that records data from several
different sources operating at relatively high rates (e.g. >5 times
per sec). The data from the various sources is diverse, in terms of
the type (e.g. data from source A may comprise of 5 parameters in
binary format, whereas data from source B may comprise of a single
parameter in ascii format). The one thing they have in common is a
timestamp. The timestamp although unique for each source is not unique
amongst all sources. For example although there will be only ever be a
single row in TableA with a particular timestamp representing a
particular point in time, there may also be a row in TableB with the
same timestamp. In an attempt to have the writes as efficient as
possible and to prevent the tables growing too big I record data from
each source to different tables. These tables are created at runtime
before I begin to record the data. Following a period of recording
some tables may still have in excess of 500,000 rows. I now wish to
reconstruct the sequence of events in chronological order, i.e.
retrieve the earliest data recorded from all the sources, followed by
the next and so on. I'm wondering how to approach this. I was thinking
of creating a single large table into which I would insert the
timestamp and ID of every row from each source as well as the table
name, then ceating a clustered index on the timestamp field, so that
the rows would be physically sorted in chronlogical order. This table
may look like the following when complete
1 09/22/2004 16:00:00 Table1
2 09/22/2004 16:00:01 Table1
1 09/22/2004 16:00:02 Table2
3 09/22/2004 16:00:03 Table1
2 09/22/2004 16:00:03 Table2
etc.
This would only ever have to happen once (I appreciate it may take
some time to construct) - then each time I need to I could simply move
through the table row by row retrieving the ID and table name which I
could then use as parameters in a query that would retrieve the data.
I'd appreciate any feedback on this approach i.e. is it madness! I was
wondering about views etc. but I don't know if these could help at all
Thanks,
Paul
First, timestamp has no relationship to date or time; the synonym is
rowversion. It is unique for a db and appears to be currently be
implemented in such a way as to be predictable. Based on the current
implementation, one can make comparisons (involving greater than and less
than) that give correct results. However, I don't think the current
implementation can be safely relied on and, IIRC, MS only supports equal /
not equal comparisons. Note - since you are posting in a db-related NG, I
assume that your reference to timestamp is actually a reference to the sql
server datatype. If this assumption is incorrect - well that's your fault
for using an ambiguous term outside of the implied context.
Second, datetime values are accurate to 3 milliseconds. If your term
"timestamp" refers to a datatype of this nature, is this accuracy
sufficient? Note that there are two aspects to accuracy. First is the
relative difference in accuracy between your datasource timestamp values and
this datatype. The second aspect is relative to a difference in accuracy.
Your timestamp data might be more accurate (e.g., 1 ms) but occurs at
intervals that are significantly less accurate (e.g., > 3 ms). Of course,
you could use a different datatype, losing the ability to use the builtin
datetime functions and creating potential ordering problems.
Third, a table is, be definition, unordered. Your narrative assumes and
implies otherwise. Clustering does affect physical ordering. However,
there is nothing that can guarantee you can access rows in physical
insertion order unless that order can be determined by the data itself. In
other words, you must provide a way to include the appropriate information
in an order by clause. Even with a heap and a cursor, I'm not certain that
there is any way to guarantee the desired order.
Given this information, how do you intend to store the data (regardless of
number of tables) in such a way that one can reconstruct the events in
chrono. order? There are two implications in your narrative that might be
problematic. First, is that "reconstruction" implies a single thread of
execution. Is this valid? If not, then you might also need to record
additional information to differentiate the multiple streams of data (which
just might be adding data at the same instant in time). The other potential
pitfall is that reconstruction often implies "re-running" (e.g.,
reconstruction of the data in the same order/timeframe). Fear this!
So now we're down to identifying chronological order. Assume that source A
has data with timestamp 10:22:01 (ignoring the date portion for simplicity)
and source B has data with the same timestamp (you said this was possible).
They both get inserted into the DB (regardless of table) at roughly the same
time. Based on data alone, it is not possible to determine which is first.
What does the DB have to assist? You could use a timestamp for the table
(or tables) and rely on the current implementation to derive order. Somone
recently posted that this type of solution was working. You could use a
datetime column to mark each row with the datetime of insert. This approach
may suffer due to accuracy limitations. Lastly, you could use a single
table with an identity. This should logically work (and it is what you
considered) but may be problematic due to contention / locking. Hotspots
will definitely be something to avoid. Note that this approach is also
predicated on the immediate insertion of data, implying that every data
"event" is recorded on a one-by-one basis. This is not the best approach in
terms of network usage (and does not scale well).
You might want to investigate any potential client-side approaches as well.
In a single-threaded application, you could also impose your own serial
numbering on the data before insertion.
Lastly, you might want to indicate what your ultimate goal is. Relational
databases are often slower than file-based approaches for data of this type
(there is a lot of overhead that you may not need for recording data).
There are other ways to record "data" in fifo order that might later to
imported into a real database for analysis or reporting. If there are no
real relationships in your data, then perhaps you don't need a rdbms.
Without knowing what you intend to do with the information, this is the
approach I would investigate first.
"Paul" <paulsmith5@.hotmail.com> wrote in message
news:ca236fb1.0409220850.2058abc9@.posting.google.c om...
> Hi,
> Apologies for the vague subject title but I couldn't think of a snappy
> title to describe what I am doing. Please bear with me as I explain...
> I am developing an application that records data from several
> different sources operating at relatively high rates (e.g. >5 times
> per sec). The data from the various sources is diverse, in terms of
> the type (e.g. data from source A may comprise of 5 parameters in
> binary format, whereas data from source B may comprise of a single
> parameter in ascii format). The one thing they have in common is a
> timestamp. The timestamp although unique for each source is not unique
> amongst all sources. For example although there will be only ever be a
> single row in TableA with a particular timestamp representing a
> particular point in time, there may also be a row in TableB with the
> same timestamp. In an attempt to have the writes as efficient as
> possible and to prevent the tables growing too big I record data from
> each source to different tables. These tables are created at runtime
> before I begin to record the data. Following a period of recording
> some tables may still have in excess of 500,000 rows. I now wish to
> reconstruct the sequence of events in chronological order, i.e.
> retrieve the earliest data recorded from all the sources, followed by
> the next and so on. I'm wondering how to approach this. I was thinking
> of creating a single large table into which I would insert the
> timestamp and ID of every row from each source as well as the table
> name, then ceating a clustered index on the timestamp field, so that
> the rows would be physically sorted in chronlogical order. This table
> may look like the following when complete
> 1 09/22/2004 16:00:00 Table1
> 2 09/22/2004 16:00:01 Table1
> 1 09/22/2004 16:00:02 Table2
> 3 09/22/2004 16:00:03 Table1
> 2 09/22/2004 16:00:03 Table2
> etc.
> This would only ever have to happen once (I appreciate it may take
> some time to construct) - then each time I need to I could simply move
> through the table row by row retrieving the ID and table name which I
> could then use as parameters in a query that would retrieve the data.
> I'd appreciate any feedback on this approach i.e. is it madness! I was
> wondering about views etc. but I don't know if these could help at all
> Thanks,
> Paul
|||Hi Scott,
Thanks for the reply. Apologies for any confusion arising from my use
of the term timestamp - it does not refer to the sql data type in this
case, rather to the datetime at which the source produced the data -
which is provided by the source itself. Therefore the limitations in
terms of sql server accuracy you refer to do not apply in this case.
Limitations in terms of accuracy emanate from the sources themselves
and I don't wish to concern myself with them at this point as much as
to say that action has been taken to ensure the are synchronised as
much as possible. I did take the precaution (whether right or wrong)
of recording however the millisecond portion of the 'timestamp' to a
separate field to maintain its integrity. I note your point on the
clustered index/ordering thanks. I think that the order in which the
data was produced by the source can be determined from the data itself
- notably the timestamp. The physical insertion order does not concern
me but perhaps if needed I could use an identity field - but if a
single thread is involved as is the case this is a mute point (I
think). Regarding identifying chronological order you are correct when
you ask where can the database assist in differentiating between two
records with the same timestamp/datetimes but I'm thinking it doesn't
have to. I'm only interested in it differentiating between records
with different datetimes i.e. a record with a datetime preceding
another one will be higher up in the order. When I replay the data in
sequence and I encounter two records with the same datetime the delay
in displaying them will be down to how quickly they can be processed
and when you are talking about portions of a second the result will
often be invisible to the naked eye - not perfect but not critical
either. As far as using a non-relational system that's out of the
question at this point.
Again thanks for the reply, I found it useful and appreciate your
time. Perhaps I will refrain from asking such general questions in
future - I think they can raise more questions then they answer - a
problem hard to address through this medium.
Paul
Assume that source A
> has data with timestamp 10:22:01 (ignoring the date portion for simplicity)
> and source B has data with the same timestamp (you said this was possible).
> They both get inserted into the DB (regardless of table) at roughly the same
> time. Based on data alone, it is not possible to determine which is first.
> What does the DB have to assist?
"Scott Morris" <bogus@.bogus.com> wrote in message news:<uNMwmBNoEHA.3324@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> First, timestamp has no relationship to date or time; the synonym is
> rowversion. It is unique for a db and appears to be currently be
> implemented in such a way as to be predictable. Based on the current
> implementation, one can make comparisons (involving greater than and less
> than) that give correct results. However, I don't think the current
> implementation can be safely relied on and, IIRC, MS only supports equal /
> not equal comparisons. Note - since you are posting in a db-related NG, I
> assume that your reference to timestamp is actually a reference to the sql
> server datatype. If this assumption is incorrect - well that's your fault
> for using an ambiguous term outside of the implied context.
> Second, datetime values are accurate to 3 milliseconds. If your term
> "timestamp" refers to a datatype of this nature, is this accuracy
> sufficient? Note that there are two aspects to accuracy. First is the
> relative difference in accuracy between your datasource timestamp values and
> this datatype. The second aspect is relative to a difference in accuracy.
> Your timestamp data might be more accurate (e.g., 1 ms) but occurs at
> intervals that are significantly less accurate (e.g., > 3 ms). Of course,
> you could use a different datatype, losing the ability to use the builtin
> datetime functions and creating potential ordering problems.
> Third, a table is, be definition, unordered. Your narrative assumes and
> implies otherwise. Clustering does affect physical ordering. However,
> there is nothing that can guarantee you can access rows in physical
> insertion order unless that order can be determined by the data itself. In
> other words, you must provide a way to include the appropriate information
> in an order by clause. Even with a heap and a cursor, I'm not certain that
> there is any way to guarantee the desired order.
> Given this information, how do you intend to store the data (regardless of
> number of tables) in such a way that one can reconstruct the events in
> chrono. order? There are two implications in your narrative that might be
> problematic. First, is that "reconstruction" implies a single thread of
> execution. Is this valid? If not, then you might also need to record
> additional information to differentiate the multiple streams of data (which
> just might be adding data at the same instant in time). The other potential
> pitfall is that reconstruction often implies "re-running" (e.g.,
> reconstruction of the data in the same order/timeframe). Fear this!
> So now we're down to identifying chronological order. Assume that source A
> has data with timestamp 10:22:01 (ignoring the date portion for simplicity)
> and source B has data with the same timestamp (you said this was possible).
> They both get inserted into the DB (regardless of table) at roughly the same
> time. Based on data alone, it is not possible to determine which is first.
> What does the DB have to assist? You could use a timestamp for the table
> (or tables) and rely on the current implementation to derive order. Somone
> recently posted that this type of solution was working. You could use a
> datetime column to mark each row with the datetime of insert. This approach
> may suffer due to accuracy limitations. Lastly, you could use a single
> table with an identity. This should logically work (and it is what you
> considered) but may be problematic due to contention / locking. Hotspots
> will definitely be something to avoid. Note that this approach is also
> predicated on the immediate insertion of data, implying that every data
> "event" is recorded on a one-by-one basis. This is not the best approach in
> terms of network usage (and does not scale well).
> You might want to investigate any potential client-side approaches as well.
> In a single-threaded application, you could also impose your own serial
> numbering on the data before insertion.
> Lastly, you might want to indicate what your ultimate goal is. Relational
> databases are often slower than file-based approaches for data of this type
> (there is a lot of overhead that you may not need for recording data).
> There are other ways to record "data" in fifo order that might later to
> imported into a real database for analysis or reporting. If there are no
> real relationships in your data, then perhaps you don't need a rdbms.
> Without knowing what you intend to do with the information, this is the
> approach I would investigate first.
> "Paul" <paulsmith5@.hotmail.com> wrote in message
> news:ca236fb1.0409220850.2058abc9@.posting.google.c om...
|||It's very difficult to answer general questions in a NG. Context usually
has direct bearing on the issues and the context is difficult to communicate
(as well as lengthy). From the short discussion, it looks like you need to
conduct a thorough analysis of the data (a good bit you've already done).
The characteristics of the data will lead (or force!) you to the appropriate
design. Just be aware of the datatype issues (some of which I mentioned)
and their potential effects on your system. One last comment - you might
want to conduct some testing involving a typically loaded machine / network
to verify that it can handle the expected volume of inserts. Better to know
this ahead of time (where you can design around it) then discover it
afterwards.
Good luck.
"Paul" <paulsmith5@.hotmail.com> wrote in message
news:ca236fb1.0409230256.6a4876b0@.posting.google.c om...[vbcol=seagreen]
> Hi Scott,
> Thanks for the reply. Apologies for any confusion arising from my use
> of the term timestamp - it does not refer to the sql data type in this
> case, rather to the datetime at which the source produced the data -
> which is provided by the source itself. Therefore the limitations in
> terms of sql server accuracy you refer to do not apply in this case.
> Limitations in terms of accuracy emanate from the sources themselves
> and I don't wish to concern myself with them at this point as much as
> to say that action has been taken to ensure the are synchronised as
> much as possible. I did take the precaution (whether right or wrong)
> of recording however the millisecond portion of the 'timestamp' to a
> separate field to maintain its integrity. I note your point on the
> clustered index/ordering thanks. I think that the order in which the
> data was produced by the source can be determined from the data itself
> - notably the timestamp. The physical insertion order does not concern
> me but perhaps if needed I could use an identity field - but if a
> single thread is involved as is the case this is a mute point (I
> think). Regarding identifying chronological order you are correct when
> you ask where can the database assist in differentiating between two
> records with the same timestamp/datetimes but I'm thinking it doesn't
> have to. I'm only interested in it differentiating between records
> with different datetimes i.e. a record with a datetime preceding
> another one will be higher up in the order. When I replay the data in
> sequence and I encounter two records with the same datetime the delay
> in displaying them will be down to how quickly they can be processed
> and when you are talking about portions of a second the result will
> often be invisible to the naked eye - not perfect but not critical
> either. As far as using a non-relational system that's out of the
> question at this point.
> Again thanks for the reply, I found it useful and appreciate your
> time. Perhaps I will refrain from asking such general questions in
> future - I think they can raise more questions then they answer - a
> problem hard to address through this medium.
> Paul
>
> Assume that source A
simplicity)[vbcol=seagreen]
possible).[vbcol=seagreen]
same[vbcol=seagreen]
first.
> "Scott Morris" <bogus@.bogus.com> wrote in message
news:<uNMwmBNoEHA.3324@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
less[vbcol=seagreen]
/[vbcol=seagreen]
I[vbcol=seagreen]
sql[vbcol=seagreen]
fault[vbcol=seagreen]
and[vbcol=seagreen]
accuracy.[vbcol=seagreen]
course,[vbcol=seagreen]
builtin[vbcol=seagreen]
In[vbcol=seagreen]
information[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
be[vbcol=seagreen]
(which[vbcol=seagreen]
potential[vbcol=seagreen]
source A[vbcol=seagreen]
simplicity)[vbcol=seagreen]
possible).[vbcol=seagreen]
same[vbcol=seagreen]
first.[vbcol=seagreen]
table[vbcol=seagreen]
Somone[vbcol=seagreen]
approach[vbcol=seagreen]
Hotspots[vbcol=seagreen]
approach in[vbcol=seagreen]
well.[vbcol=seagreen]
Relational[vbcol=seagreen]
type[vbcol=seagreen]
no[vbcol=seagreen]

Please Advice !

Hi,

I am trying to solve this procedure.

Let me try to explain it...I am getting DEGREEID from one of the SELECT query . I want to OUTPUT (ie , COUNT) from procedure,the number of departments with the degreeid, got from the above query.

With below procedure, Since an employee can have multiple DEGREEID , the cursor is giving OUPTUT ie, COUNT for the LAST Degreeid. Eventhough the previous DEGREEID dont have any DEPARTMENT...but only for the LAST DEGREEID...!

How can I solve this.... whether I can solve this with CURSOR or I have to use someother way...Please advice me !

DATA
---
DEGREE_EARNED
-------
EMPID DEGREEID
-- ----
201 12
201 3
201 250
202 3
202 10
203 17

DEPARTMENT
-----
DEPID DEGREEID
-- ----
10 1 12
111 250
111 12
121 3
121 12
121 250
-----------



---------------------
DECLARE @.vchid int
DECLARE testcursor CURSOR FOR

SELECT degree_id
FROM degree_earned WHERE emp_id= @.empid

OPEN testcursor
FETCH NEXT FROM testcursor INTO @.vchid

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN
Select @.outresult = COUNT(*)
from
department
where degree_id = @.vchid

FETCH NEXT FROM testcursor INTO @.vchid
END
---------------------Can somebody help me,please (:|||So is what you want something like:EMPID DEGREEID DEPT_COUNT
-- --- ----
201 12 2
201 3 1
201 250 2
202 3 1
202 10 0
203 17 0I don't understand exactly what you want, so I can't be much help until I do. Sorry!

-PatP|||Hey Pat,

Thats what i wanted...Can u please show me , how to solve this...Thanks!|||USE Northwind
GO

CREATE TABLE DEGREE_EARNED(EMPID int, DEGREEID int)
CREATE TABLE DEPARTMENT(DEPID int, DEGREEID int)
GO
INSERT INTO DEGREE_EARNED(EMPID, DEGREEID)
SELECT 201, 12 UNION ALL
SELECT 201, 3 UNION ALL
SELECT 201, 250 UNION ALL
SELECT 202, 3 UNION ALL
SELECT 202, 10 UNION ALL
SELECT 203, 17

INSERT INTO DEPARTMENT(DEPID, DEGREEID)
SELECT 101, 12 UNION ALL
SELECT 111, 250 UNION ALL
SELECT 111, 12 UNION ALL
SELECT 121, 3 UNION ALL
SELECT 121, 12 UNION ALL
SELECT 121, 250
GO

SELECT A.DEGREEID, ISNULL(ROW_OCCURS,0)
FROM ( SELECT DISTINCT DEGREEID FROM DEGREE_EARNED) AS A
LEFT JOIN ( SELECT DEGREEID, COUNT(*) AS ROW_OCCURS
FROM DEPARTMENT GROUP BY DEGREEID) AS B
ON A.DEGREEID = B.DEGREEID
GO

--DROP TABLE DEGREE_EARNED
--DROP TABLE DEPARTMENT
GOsql