Getting The Latest Record

Hello Everyone,

I would like advise on what I am doing. I am trying to get the maximum date per record from a table. It is a very simple query but I am wondering why I am not getting the right result. Please look at the following query:

select account_id, amount, max(tran_date) from evalucheck_history group by account_id, check_amount order by account_id

This query is supposed to give me the record with the latest date but instead I get the following (snap shot of the result):

Account ID Amt Date
9999999999000100 174.8 1-Dec-2006
9999999999000100 223.69 25-Oct-2006
9999999999000100 358.5 9-Nov-2006
9999999999000100 393.5 14-Nov-2006
9999999999000100 441.98 24-Oct-2006
9999999999000100 476.93 20-Oct-2006
9999999999000100 552.07 10-Jan-2007
9999999999000100 627.23 2-Nov-2006
9999999999000100 705.94 19-Oct-2006
9999999999000100 713.61 4-Dec-2006
9999999999000100 729.71 30-Oct-2006
9999999999000100 747.24 13-Mar-2007
9999999999000100 998.97 19-Apr-2007

Can you please help me on this?

Thank you all.

RandyI think the lack of a WHERE clause is why this isn't giving you what you want.
Here's a quick guess

SELECT account_id
,amount
,tran_date
FROM evalucheck_history
WHERE tran_date =
(
SELECT Max(x.tran_date)
FROM evalucheck_history As x
WHERE x.account_id = account_id
)
ORDER BY account_id|||um, george, what is that funky "x dot" notation supposed to be doing?

... FROM x.evalucheck_history As x|||SELECT account_id
, amount
, tran_date
FROM evalucheck_history as X
WHERE tran_date =
( SELECT Max(tran_date)
FROM evalucheck_history
WHERE account_id = X.account_id )
ORDER
BY account_id|||EDIT: Typo :)
Isn't yours the same as mine..?
(Except we aliased differently)|||test 'em and see :cool:|||Thank you for your help guys. I appreciate it. I had the problem sorted out.

Sincerely,

Randy|||Oooh my aliasing doesn't work!
It only returns a single record...

I'm not sure I get why either!|||Oooh my aliasing doesn't work!
It only returns a single record...

I'm not sure I get why either!maybe start a new thread? and show us your test data...|||No need for a new thread really?
I'm happy to bolt on here :p

SELECT x.*
FROM career As x
WHERE x.career_date =
(
SELECT Max(career_date)
FROM career
WHERE parent_identifier = x.parent_identifier
)

SELECT *
FROM career
WHERE career_date =
(
SELECT Max(x.career_date)
FROM career As x
WHERE parent_identifier = x.parent_identifier
)

(46223 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

Interesting, no?|||1 row, eh

:)|||I don't really get why though... I thought I had it a minute ago but other logic told me not to be stupid :p|||which row is it? my money is on the MAX(career_date) in the table ;)|||I thought that was obvious ;)
I just can't explain why!|||Your original alias was inside the subquery, which mean any testing in the subquery was NOT dependent on the outer query... thus

SELECT Max(x.tran_date)
FROM evalucheck_history As x
WHERE x.account_id = account_id

is the same as

SELECT Max(tran_date)
FROM evalucheck_history
WHERE account_id = account_id

As you can see account_id = account_id for every row. You may as well have left off the WHERE clause there because it'll give you the same result.

Thus, it returns one result only which is ALWAYS the max tran_date from the table, which will only every match one row in your outer query.

hope that helps ;)|||In summation your subquery should have been dependent, but it wasn't ...|||aschk, nice analysis

you should take up writing, you're good at it

:)|||Aha - now I see!
Thank you aschk and Rudy.

Oh and yes - very complete and concise answers - Rudy's suggestion is a good 'un!|||Thanks rudy, a very flattering comment. I probably wouldn't consider writing to be my best skill, but I do like analysing work and trying to better explain it (providing I understand it) ;)

Getting the id generated by SQL Server for a new record

I am using ASP.NET 2.0 to add records to a database table in an SQL Server
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.
Nathan Sokalski
njsokalski@.hotmail.com
http://www.nathansokalski.com/
On Nov 29, 10:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
Look in the Books Online for the SCOPE_IDENTITY function. Lots of
good examples there.
|||On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
You would want to return the SCOPE_IDENTITY() value as an output
parameter from your DbCommand after you perform the insert.
|||On 30 Nov., 07:18, Dan Gartner <dgart...@.gmail.com> wrote:
> On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> You would want to return the SCOPE_IDENTITY() value as an output
You could use the OUTPUT feature of sql server 2005. But in this case
SCOPE_IDENTITY() and @.@.IDENTITY give back odd results while
IDENT_CURRENT() and inserted.id seem to give correct values.
inserted.id is the way the output feature is meant.
Example:
begin tran
create table tmp (id int identity, xyz varchar)
insert into tmp (xyz) output inserted.id values ('w')
insert into tmp (xyz) output @.@.identity values ('x')
insert into tmp (xyz) output ident_current('tmp') values ('y')
insert into tmp (xyz) output scope_identity() values ('z')
select * from tmp
drop table tmp
rollback
Results:
1
1
3
3
id xyz
-- --
1 w
2 x
3 y
4 z
(4 Zeile(n) betroffen)
|||Hi
You can use the output parameter of the stored procedure and return the
value that is being inserted. If you are using identity column then you can
use @.@.IDENTITY to return the last inserted indentity value.
--
Thanks,
Ibrahim
Software Consultant - Web Development, GB
"Nathan Sokalski" wrote:

> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokalski@.hotmail.com
> http://www.nathansokalski.com/
>
>
|||This method obviously requires using stored procs (which is almost always a
good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
bit of a bind I think. IIRC you can't issue an insert statement and get a
select back out in a single Execute... type command.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...[vbcol=seagreen]
> Hi
> You can use the output parameter of the stored procedure and return the
> value that is being inserted. If you are using identity column then you
> can
> use @.@.IDENTITY to return the last inserted indentity value.
> --
> --
> Thanks,
> Ibrahim
> Software Consultant - Web Development, GB
>
> "Nathan Sokalski" wrote:
|||Kevin,
Sure you can, like this:
cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
(@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
Then:
ID = cmd.ExecuteScalar
Kerry Moorman
"TheSQLGuru" wrote:

> This method obviously requires using stored procs (which is almost always a
> good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
> bit of a bind I think. IIRC you can't issue an insert statement and get a
> select back out in a single Execute... type command.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
> message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...
>
>
|||I seem to recall a client trying to do that recently (using ADO classic) and
it not working. Perhaps they missed the semicolon. I will recheck their
attempts and see if that does it.
One additional question since I am not an ADO guru. Does the Select
Scope_identity() not return a single-column single-row result set, which the
executescalar isn't expecting?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:D12AFECB-DC31-4CE3-A2A2-14F3AB3CD95D@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Sure you can, like this:
> cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
> (@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
> Then:
> ID = cmd.ExecuteScalar
> Kerry Moorman
>
> "TheSQLGuru" wrote:
|||Kevin,
ExecuteScalar returns the first column of the first row in the result set
returned by the query.
Kerry Moorman
"TheSQLGuru" wrote:

> One additional question since I am not an ADO guru. Does the Select
> Scope_identity() not return a single-column single-row result set, which the
> executescalar isn't expecting?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
|||I just checked back with the developer that had the issue. He swears that
using VB6 and ADO classic your example fails. Were you using ADOc or
ADO.NET?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:6AD6FF28-692A-4DFA-9BCB-7D8E9A2302C1@.microsoft.com...
> Kevin,
> ExecuteScalar returns the first column of the first row in the result set
> returned by the query.
> Kerry Moorman
>
> "TheSQLGuru" wrote:
>

getting table counts

I want to get a resultset of every table in the database, with the
current record count of each. What is the easiest way to do this?

I can get the list of tables with:

Select s.name from sysobjects s where xtype = 'U'

each s.name is a table name, but I'm not sure how to join a record count
column to the resultset.

Thanks,
RickNAssuming your statistics are up to date you can use

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('<table_name>') AND indid < 2

This will perform better than

SELECT COUNT(*) from <table_name
This info is from http://www.sql-server-performance.com/

You could use a cursor to loop through the list of tables and stuff the
counts into a temp table. Perhaps someone else will have a way to do this
without a cursor.

Hope this helps,

CJ

"Rick" <rick@.abasoftware.com> wrote in message
news:28d7cbb9.0309231030.13e8f503@.posting.google.c om...
> I want to get a resultset of every table in the database, with the
> current record count of each. What is the easiest way to do this?
> I can get the list of tables with:
> Select s.name from sysobjects s where xtype = 'U'
> each s.name is a table name, but I'm not sure how to join a record count
> column to the resultset.
> Thanks,
> RickN|||So this would be the non-cursor solution:

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.name)
and i.indid = 0

Shervin

"CJ" <chris@.hrn.org> wrote in message news:bkqdqr$34q$1@.reader2.nmix.net...
> Assuming your statistics are up to date you can use
> SELECT rows
> FROM sysindexes
> WHERE id = OBJECT_ID('<table_name>') AND indid < 2
> This will perform better than
> SELECT COUNT(*) from <table_name>
> This info is from http://www.sql-server-performance.com/
> You could use a cursor to loop through the list of tables and stuff the
> counts into a temp table. Perhaps someone else will have a way to do this
> without a cursor.
> Hope this helps,
> CJ
>
> "Rick" <rick@.abasoftware.com> wrote in message
> news:28d7cbb9.0309231030.13e8f503@.posting.google.c om...
> > I want to get a resultset of every table in the database, with the
> > current record count of each. What is the easiest way to do this?
> > I can get the list of tables with:
> > Select s.name from sysobjects s where xtype = 'U'
> > each s.name is a table name, but I'm not sure how to join a record count
> > column to the resultset.
> > Thanks,
> > RickN|||Rick (rick@.abasoftware.com) writes:
> I want to get a resultset of every table in the database, with the
> current record count of each. What is the easiest way to do this?
> I can get the list of tables with:
> Select s.name from sysobjects s where xtype = 'U'
> each s.name is a table name, but I'm not sure how to join a record count
> column to the resultset.

SELECT 'SELECT ''name'', COUNT(*) FROM ' + name
FROM sysobjects
WHERE xtype = 'U'
AND objectproperty(id, 'IsMSShipped') = 1
ORDER BY name

Cut and paste.

If you want to run it unattended, you can use the stored procedure
sp_MSforeachtable:

EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

Note that this procedure is undocumetned.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks everyone for the good ideas.
I've implemented the following and it gets me exactly what I need.

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.name)
and i.indid < 2 and o.xtype = 'u'

RickN

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

getting source records table name?

Hello,
I need some help on getting the name of a table, or even creating an id
of some kind, that relates to where a record was pulled from. Ive
included a *simplified* example so you can see what Im talking about
say I have 3 tables, and a query that returns 2 rows, each row made up
of 1 column from table1 and 1 column from either table2 or table3.
There is no unique identifier I can use in table2 or table3.
Ive played with using UNION but it doesnt suit a complex query like
the actual query Im using. Maybe theres some stored procedure, or
another hack to solve this?
Any help is appreciated,
Craig.
table1
--
table1_id
1
2
table2
--
table2_id, table2.table1_id
1,1
2,NULL
table3
--
table3_id, table3.table1_id
1,NULL
2,1
query
--
select table1.table1_id, X
from table1
left join table2
on table1.table1_id = table2.table1_id
left join table3
on table1.table1_id = table3.table3_id
result
--
table1_id
1, X
1, X
(where X displays the source table name)
"The only way to get rid of temptation is to yield to it...""Craig H." <spam@.thehurley.com> wrote in message
news:OAnj6GEIFHA.2656@.TK2MSFTNGP09.phx.gbl...
> query
> --
> select table1.table1_id, X
> from table1
> left join table2
> on table1.table1_id = table2.table1_id
> left join table3
> on table1.table1_id = table3.table3_id
> result
> --
> table1_id
> 1, X
> 1, X
Craig,
I'm -- the value in column 1, in your query, will ALWAYS come
from table1. Is your real query different in some way? Please post the
actual code you're trying to work with.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||On 03/03/2005 14:47, Adam Machanic wrote:
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."

> "Craig H." <spam@.thehurley.com> wrote in message
> news:OAnj6GEIFHA.2656@.TK2MSFTNGP09.phx.gbl...
>
>
> Craig,
> I'm -- the value in column 1, in your query, will ALWAYS come
> from table1. Is your real query different in some way? Please post the
> actual code you're trying to work with.
>
That's correct Adam, but it's the source of column 2 in the result that
I need to obtain (bear in mind that there is nothing to identify what
the table represents in the data contained within table2 & table3). The
actual query looks like:
select le.Name as Client, lec.Name as CompanyType, div.name as Division,
p.Name as PayeeName
from lentity le
inner join contract c
on ((c.contracting_lentity_id = le.lentity_id) and (c.enddate IS NULL or
c.enddate > getdate()))
inner join lentity div
on div.lentity_ID = c.providing_lentity_id
inner join lentityCode lec
on lec.lentityCode = le.lentityCode
inner join payee p
on ((p.contract_id = c.contract_id) and (p.active = 'true'))
inner join paymentrouting pr
on pr.payee_id = p.payee_id
left join aPaymentChannel apc
on ((apc.paymentrouting_id = pr.paymentrouting_id) and (apc.active =
'true'))
left join bPaymentChannel bpc
on ((bpc.paymentrouting_id = pr.paymentrouting_id) and (bpc.active =
'true'))
left join cPaymentChannel cpc
on ((cpc.paymentrouting_id = pr.paymentrouting_id) and (cpc.active =
'true'))
left join dPaymentChannel dpc
on ((dpc.paymentrouting_id = pr.paymentrouting_id) and (dpc.active =
'true'))
left join ePaymentChannel gpc
on ((epc.paymentrouting_id = pr.paymentrouting_id) and (epc.active =
'true'))
left join fPaymentChannel fpc
on ((fpc.paymentrouting_id = pr.paymentrouting_id) and (fpc.active =
'true'))
order by Client|||"Craig H." <spam@.thehurley.com> wrote in message
news:O%23DbBdEIFHA.2564@.tk2msftngp13.phx.gbl...
> select le.Name as Client, lec.Name as CompanyType, div.name as Division,
> p.Name as PayeeName
Which column are you concerned with?
The first will always come from le, the second from lec, the third from
div, and the fourth from p...
Can you show me exactly what the issue is?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||On 03/03/2005 16:46, Adam Machanic wrote:
> "Craig H." <spam@.thehurley.com> wrote in message
> news:O%23DbBdEIFHA.2564@.tk2msftngp13.phx.gbl...
>
>
> Which column are you concerned with?
> The first will always come from le, the second from lec, the third fro
m
> div, and the fourth from p...
> Can you show me exactly what the issue is?
>
O.K. I didn't explain myself clearly enough. Every time the 1st, 2nd,
3rd and 4th columns are returned in a result, I would also like to
return a column indicating which left join statement caused a match (or
which XPaymentChannel a match was was found in):
left join XPaymentChannel X
on ((X.paymentrouting_id = pr.paymentrouting_id) and (X.active = 'true'))
The reason I'm finding this difficult is because the XPaymentChannel
tables have no column that differentiate it from the other
XPaymentChannel tables.
Is that clearer? Thanks for your time so far Adam.
Craig.
"You can't build a reputation on what you are going to do."|||>> ..each row made up of 1 column from table1 and 1 column from either
table2 or table3.
There is no unique identifier I can use in table2 or table3. <<
This sounds like the design is screwed up and you have either put the
same data in two places, or the table you return is made of mixed
tuples. And two tables without a key is just plain wrong!
Next, you will be using a lot of NULLs and setting flags all over the
place, or using dynamic SQL :)|||Okay... I think you want to add:
CASE
WHEN apc.paymentrouting_id IS NOT NULL THEN 'apc'
WHEN bpc.paymentrouting_id IS NOT NULL THEN 'bpc'
WHEN cpc.paymentrouting_id IS NOT NULL THEN 'cpc'
..
ELSE NULL
END AS PaymentRoutingTbl
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Craig H." <spam@.thehurley.com> wrote in message
news:ui66XaFIFHA.2704@.tk2msftngp13.phx.gbl...
> On 03/03/2005 16:46, Adam Machanic wrote:
from
> O.K. I didn't explain myself clearly enough. Every time the 1st, 2nd,
> 3rd and 4th columns are returned in a result, I would also like to
> return a column indicating which left join statement caused a match (or
> which XPaymentChannel a match was was found in):
> left join XPaymentChannel X
> on ((X.paymentrouting_id = pr.paymentrouting_id) and (X.active = 'true'))
> The reason I'm finding this difficult is because the XPaymentChannel
> tables have no column that differentiate it from the other
> XPaymentChannel tables.
> Is that clearer? Thanks for your time so far Adam.
> Craig.
>
> --
> "You can't build a reputation on what you are going to do."|||On 03/03/2005 17:40, Adam Machanic wrote:
> Okay... I think you want to add:
> CASE
> WHEN apc.paymentrouting_id IS NOT NULL THEN 'apc'
> WHEN bpc.paymentrouting_id IS NOT NULL THEN 'bpc'
> WHEN cpc.paymentrouting_id IS NOT NULL THEN 'cpc'
> ...
> ELSE NULL
> END AS PaymentRoutingTbl
>
Perfect, thank you Adam!
"The only way to get rid of temptation is to yield to it..."

Getting Row Number in the Db

Hi,
How to select a record which has the row number 2694 in the database?
Thanks
--
pmudBased on what criteria?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
Hi,
How to select a record which has the row number 2694 in the database?
Thanks
--
pmud|||Just to add to Tom's response some, SQL Server 2000 has no concept of a row
number. The order in which the rows are stored is totally up to SQL Server.
Further more the order in which they are returned is also totally up to SQL
Server unless you specify an ORDER BY clause. So row number xxxx means
nothing without any other context.
Andrew J. Kelly SQL MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
> Hi,
> How to select a record which has the row number 2694 in the database?
> Thanks
> --
> pmud|||> How to select a record which has the row number 2694 in the database?
How the heck are we supposed to determine what row number 2694 means? Can
you grab me the 82nd toothpick in that box of 250 toothpicks? You might
have a better chance if I tell you to order by length, or width, or hue. If
I don't tell you what my ordering method is, good luck!|||Yes. Thats my mistake. The actual problem I am facing is that I am trying to
convert a varchar field to a datetime field . The reason it was originally
varchar was that the data was initially imported from spreadsheets so the Db
automatically made it a varchar field.
So, i created another table and set the ActivationDate to a datetime type
instead of a varchar and then I try to import data to this table from the
existing one, but I am getting error:
"Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
error: Conversion invalid for datatypes on column pair 8 ( source column
ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
(DBTYPE_DBTIMESTAMP). "
So I was trying to find out how can I know which is row Number 2694 in the
table. Also, in the varchar activationdate field, all dates are in the forma
t
like:
May 4 2005 12:00 AM
Any ideas on this?
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> How the heck are we supposed to determine what row number 2694 means? Can
> you grab me the 82nd toothpick in that box of 250 toothpicks? You might
> have a better chance if I tell you to order by length, or width, or hue.
If
> I don't tell you what my ordering method is, good luck!
>
>|||Usually, these things are imported in the order in which they occurred in
the source files. Can you go to row 2694 in the spreadsheet itself?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
Yes. Thats my mistake. The actual problem I am facing is that I am trying to
convert a varchar field to a datetime field . The reason it was originally
varchar was that the data was initially imported from spreadsheets so the Db
automatically made it a varchar field.
So, i created another table and set the ActivationDate to a datetime type
instead of a varchar and then I try to import data to this table from the
existing one, but I am getting error:
"Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
error: Conversion invalid for datatypes on column pair 8 ( source column
ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
(DBTYPE_DBTIMESTAMP). "
So I was trying to find out how can I know which is row Number 2694 in the
table. Also, in the varchar activationdate field, all dates are in the
format
like:
May 4 2005 12:00 AM
Any ideas on this?
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> How the heck are we supposed to determine what row number 2694 means? Can
> you grab me the 82nd toothpick in that box of 250 toothpicks? You might
> have a better chance if I tell you to order by length, or width, or hue.
> If
> I don't tell you what my ordering method is, good luck!
>
>|||Oh. dont have the spreadsheet from which this was imported. Is there any
other way of finding out?
Thanks
--
pmud
"Tom Moreau" wrote:

> Usually, these things are imported in the order in which they occurred in
> the source files. Can you go to row 2694 in the spreadsheet itself?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
> Yes. Thats my mistake. The actual problem I am facing is that I am trying
to
> convert a varchar field to a datetime field . The reason it was originally
> varchar was that the data was initially imported from spreadsheets so the
Db
> automatically made it a varchar field.
> So, i created another table and set the ActivationDate to a datetime type
> instead of a varchar and then I try to import data to this table from the
> existing one, but I am getting error:
> "Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
> encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
> error: Conversion invalid for datatypes on column pair 8 ( source column
> ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
> (DBTYPE_DBTIMESTAMP). "
> So I was trying to find out how can I know which is row Number 2694 in the
> table. Also, in the varchar activationdate field, all dates are in the
> format
> like:
> May 4 2005 12:00 AM
> Any ideas on this?
> Thanks
> --
> pmud
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||Not that I know of.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:37A6EC15-7D5B-47D5-A169-BC8A5289D092@.microsoft.com...
Oh. dont have the spreadsheet from which this was imported. Is there any
other way of finding out?
Thanks
--
pmud
"Tom Moreau" wrote:

> Usually, these things are imported in the order in which they occurred in
> the source files. Can you go to row 2694 in the spreadsheet itself?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:CD74C94E-69ED-44F0-B9D4-25E472A82683@.microsoft.com...
> Yes. Thats my mistake. The actual problem I am facing is that I am trying
> to
> convert a varchar field to a datetime field . The reason it was originally
> varchar was that the data was initially imported from spreadsheets so the
> Db
> automatically made it a varchar field.
> So, i created another table and set the ActivationDate to a datetime type
> instead of a varchar and then I try to import data to this table from the
> existing one, but I am getting error:
> "Error during Transformation 'DirectCopyXform' for Row number 2694. Errors
> encountered in this task: 1. TransformCopy 'DirectCopyXform' conversion
> error: Conversion invalid for datatypes on column pair 8 ( source column
> ActivationDate'(DBTYPE_STR), destination column 'ActivationDate'
> (DBTYPE_DBTIMESTAMP). "
> So I was trying to find out how can I know which is row Number 2694 in the
> table. Also, in the varchar activationdate field, all dates are in the
> format
> like:
> May 4 2005 12:00 AM
> Any ideas on this?
> Thanks
> --
> pmud
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||Look up the ISDATE() function in Books Online, and check which rows contain
invalid values before inserting them in the new table.
ML
http://milambda.blogspot.com/|||simple.
select id = identity(int,1,1), *
into #idRecord
from yourtable
later...
select *
from #idRecord
where id = idCriteria.
See you Later...
Nemir Labopaz
"pmud" <pmud@.discussions.microsoft.com> escribi en el mensaje
news:A2C04422-8F68-4D89-A19C-60703BEA559F@.microsoft.com...
> Hi,
> How to select a record which has the row number 2694 in the database?
> Thanks
> --
> pmud

Getting report to create new page for each record retrieved from

I have a stored procedure to pull out the data of all the employees at
a company, and if I use a table, it lists the all the profiles.
However, the table is too big to view on one page and print, so I
created a report with textboxes and fields that fits onto one page
instead.
Using the same stored procedure, all I could see is the profile of the
first employee returned by the stored procedure.
Is there a way to get the report to create a new page for each person
returned by the stored procedure so that the final report would
consists of all the profiles?
Not sure if I should mention this, but I'm working with a web form on
VS2005.
Thanks.You put the text boxes right on the drawing surface. You need to put them on
a list control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"k-chan" <kitty.sham@.gmail.com> wrote in message
news:c83e7712-1c9e-49fc-b551-4399c96e6936@.e4g2000hsg.googlegroups.com...
> I have a stored procedure to pull out the data of all the employees at
> a company, and if I use a table, it lists the all the profiles.
> However, the table is too big to view on one page and print, so I
> created a report with textboxes and fields that fits onto one page
> instead.
> Using the same stored procedure, all I could see is the profile of the
> first employee returned by the stored procedure.
> Is there a way to get the report to create a new page for each person
> returned by the stored procedure so that the final report would
> consists of all the profiles?
> Not sure if I should mention this, but I'm working with a web form on
> VS2005.
> Thanks.|||Oh! That worked!
Thank you!
On Jan 14, 3:23 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You put the text boxes right on the drawing surface. You need to put them on
> a list control.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "k-chan" <kitty.s...@.gmail.com> wrote in message
> news:c83e7712-1c9e-49fc-b551-4399c96e6936@.e4g2000hsg.googlegroups.com...
>
> > I have a stored procedure to pull out the data of all the employees at
> > a company, and if I use a table, it lists the all the profiles.
> > However, the table is too big to view on one page and print, so I
> > created a report with textboxes and fields that fits onto one page
> > instead.
> > Using the same stored procedure, all I could see is the profile of the
> > first employee returned by the stored procedure.
> > Is there a way to get the report to create a new page for each person
> > returned by the stored procedure so that the final report would
> > consists of all the profiles?
> > Not sure if I should mention this, but I'm working with a web form on
> > VS2005.
> > Thanks.

Getting Records not in both tables

I have 2 tables and I want to retrieve all record that are in table 1, but
not in table 2. Here's my data.
Table 1: EstEquipment
Columns: EstEqpPK, EstimatePK, Description
Table 2: CrewEquipment
Columns: CrewEqpPK, EstEqpPK, quantity
I need all records in EstEquipment with a certain EstimatePK that are not in
CrewEquipment. I've tried all sorts of combinations of inner joins, outer
join, and not exists, but I can't seem to get it. This is my first attempt a
t
SQL, so bear with me.
This was my last attempt that got close:
SELECT EstEquipment.* FROM EstEquipment
LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK = CrewEquipment
.EstEqpPK
WHERE EstimatePK = '4947A70B4396448C'
Thanks!Try this one:
SELECT
e.EstEqpPK,
e.EstimatePK,
e.Description
FROM
EstEquipment e
LEFT OUTER JOIN
CrewEquipment c
ON
e.EstEqpPK = c.EstEqpPK
WHERE
e.EstimatePK = '4947A70B4396448C'
AND c.EstEqpPK IS NULL;
"Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
news:CCD86D0D-60BC-4658-9E3A-4F9C49F63592@.microsoft.com...
>I have 2 tables and I want to retrieve all record that are in table 1, but
> not in table 2. Here's my data.
> Table 1: EstEquipment
> Columns: EstEqpPK, EstimatePK, Description
> Table 2: CrewEquipment
> Columns: CrewEqpPK, EstEqpPK, quantity
> I need all records in EstEquipment with a certain EstimatePK that are not
> in
> CrewEquipment. I've tried all sorts of combinations of inner joins, outer
> join, and not exists, but I can't seem to get it. This is my first attempt
> at
> SQL, so bear with me.
> This was my last attempt that got close:
> SELECT EstEquipment.* FROM EstEquipment
> LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK = CrewEquipment
> .EstEqpPK
> WHERE EstimatePK = '4947A70B4396448C'
> Thanks!|||Try
SELECT EstEquipment.* FROM EstEquipment
LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK =
CrewEquipment
.EstEqpPK
WHERE EstimatePK = '4947A70B4396448C'
AND CreqEquipment.EstEqpPK is NULL
HTH,
Stu|||Thanks, that got it. I had tried = NULL, but that didn't return anything. I'
d
better study up on the syntax.
"Stu" wrote:

> Try
> SELECT EstEquipment.* FROM EstEquipment
> LEFT OUTER JOIN CrewEquipment ON EstEquipment.EstEqpPK =
> CrewEquipment
> ..EstEqpPK
> WHERE EstimatePK = '4947A70B4396448C'
> AND CreqEquipment.EstEqpPK is NULL
> HTH,
> Stu
>|||Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
"Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
news:E8475694-5E80-444B-BFDE-1F938DF76DBC@.microsoft.com...
> Thanks, that got it. I had tried = NULL, but that didn't return anything.
> I'd
> better study up on the syntax.|||I got that from my C background. You use to do this:
IF (x == null)
Thanks for the explanation.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Nothing will ever = NULL, since the definition of NULL is unknown.
> Think about it this way, if I have a form with a field that says "gender"
> and I forget to check either male or female, can you say with any certaint
y
> that I am:
> (a) male?
> (b) female?
> (c) not male?
> (d) not female?
> Further, can you say with any certainty that someone else, who also forgot
> to specify their gender, is:
> (a) the same gender as me?
> (b) the opposite gender from me?
> (c) not the same gender as me?
> (d) not the opposite gender from me?
>
>
> "Irvin McCoy" <IrvinMcCoy@.discussions.microsoft.com> wrote in message
> news:E8475694-5E80-444B-BFDE-1F938DF76DBC@.microsoft.com...
>
>

getting records horizontally

Hi there,
I have a record set
SELECT testingInfo1.xColumn, testingInfo2.tColumn
FROM testing1 testingInfo1
INNER JOIN testing2 testingInfo2
ON testingInfo1.column1 = testingInfo2.fColumn
xColumn tColumn
x1 t1
x1 t2
x1 t3
x2 t2
x3 t1
x3 t2
x4 t1
I would like to display
xColumn t1 t2 t3 t4 t5 ....
x1 1 1 1 0 0
x2 0 1 0 0 0
x3 1 1 0 0 0
etc
I could possibly do that using temp and cursors etc but any good
solutions to share?
Thanks,
Toby
yootaeho@.gmail.com wrote:
> Hi there,
> I have a record set
> SELECT testingInfo1.xColumn, testingInfo2.tColumn
> FROM testing1 testingInfo1
> INNER JOIN testing2 testingInfo2
> ON testingInfo1.column1 = testingInfo2.fColumn
> xColumn tColumn
> x1 t1
> x1 t2
> x1 t3
> x2 t2
> x3 t1
> x3 t2
> x4 t1
> I would like to display
> xColumn t1 t2 t3 t4 t5 ....
> x1 1 1 1 0 0
> x2 0 1 0 0 0
> x3 1 1 0 0 0
> etc
> I could possibly do that using temp and cursors etc but any good
> solutions to share?
> Thanks,
> Toby
see PIVOT and UNPIVOT in BOL

getting records horizontally

Hi there,
I have a record set
SELECT testingInfo1.xColumn, testingInfo2.tColumn
FROM testing1 testingInfo1
INNER JOIN testing2 testingInfo2
ON testingInfo1.column1 = testingInfo2.fColumn
xColumn tColumn
x1 t1
x1 t2
x1 t3
x2 t2
x3 t1
x3 t2
x4 t1
I would like to display
xColumn t1 t2 t3 t4 t5 ....
x1 1 1 1 0 0
x2 0 1 0 0 0
x3 1 1 0 0 0
etc
I could possibly do that using temp and cursors etc but any good
solutions to share?
Thanks,
Tobyyootaeho@.gmail.com wrote:
> Hi there,
> I have a record set
> SELECT testingInfo1.xColumn, testingInfo2.tColumn
> FROM testing1 testingInfo1
> INNER JOIN testing2 testingInfo2
> ON testingInfo1.column1 = testingInfo2.fColumn
> xColumn tColumn
> x1 t1
> x1 t2
> x1 t3
> x2 t2
> x3 t1
> x3 t2
> x4 t1
> I would like to display
> xColumn t1 t2 t3 t4 t5 ....
> x1 1 1 1 0 0
> x2 0 1 0 0 0
> x3 1 1 0 0 0
> etc
> I could possibly do that using temp and cursors etc but any good
> solutions to share?
> Thanks,
> Toby
see PIVOT and UNPIVOT in BOL

Getting records for latest 4 dates

Hi,
I would like to retrieve all the records in a table that are in the
latest 4 dates.
Each record has a date field. The latest date in the table may be today,
yesterday or last w - it varies.
I would like to be able to retrieve all records that belong to the
latest 4 dates in the table. Is it possible to do this in a stored
procedure?
Thanks,
DarenHi Daren
You may want to try something like:
CREATE TABLE #datedata ( dateval datetime, val varchar(10))
insert intO #datedata ( dateval, val )
select getdate(), 'a' as dateval
UNION ALL select getdate() -1, 'F'
UNION ALL select getdate() -2, 'b'
UNION ALL select getdate() -3, 'C'
UNION ALL select getdate() -4, 'E'
UNION ALL select getdate() -5, 'D'
SELECT * from #datedata
WHERE DATEVAL IN (
SELECT TOP 4 dateval FROM #datedata order by dateval DESC )
John
"Daren" <pearcy@.-removethis-gmail.com> wrote in message
news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
> Hi,
> I would like to retrieve all the records in a table that are in the latest
> 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the latest
> 4 dates in the table. Is it possible to do this in a stored procedure?
> Thanks,
> Daren|||Hi John,
I don't think this does what I'm after. I'm not looking to get all
records with dates in the last 4 days. I'm looking to get records with
dates in the last 4 days in the table.
Looking at the example table below, the SQL I'm after would retrieve
record ids 3999 to 3991 inclusive.
RecordID Date Added
3999 25-May-2005
3998 25-May-2005
3997 23-May-2005
3996 21-May-2005
3995 21-May-2005
3994 21-May-2005
3993 21-May-2005
3993 21-May-2005
3992 18-May-2005
3991 18-May-2005
3990 16-May-2005
3989 16-May-2005
3988 15-May-2005
Regards,
Daren
John Bell wrote:
> Hi Daren
> You may want to try something like:
>
> CREATE TABLE #datedata ( dateval datetime, val varchar(10))
> insert intO #datedata ( dateval, val )
> select getdate(), 'a' as dateval
> UNION ALL select getdate() -1, 'F'
> UNION ALL select getdate() -2, 'b'
> UNION ALL select getdate() -3, 'C'
> UNION ALL select getdate() -4, 'E'
> UNION ALL select getdate() -5, 'D'
> SELECT * from #datedata
> WHERE DATEVAL IN (
> SELECT TOP 4 dateval FROM #datedata order by dateval DESC )

> John
> "Daren" <pearcy@.-removethis-gmail.com> wrote in message
> news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
>
>
>|||This is why posting DDL and example data is important
(http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006 ) because it removes
this sort of ambiguity.
CREATE TABLE MyWork ( RecordID int, [Date Added] datetime )
INSERT INTO MyWork ( RecordID, [Date Added] )
SELECT 3999, '25-May-2005'
UNION ALL SELECT 3998, '25-May-2005'
UNION ALL SELECT 3997, '23-May-2005'
UNION ALL SELECT 3996, '21-May-2005'
UNION ALL SELECT 3995, '21-May-2005'
UNION ALL SELECT 3994, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3992, '18-May-2005'
UNION ALL SELECT 3991, '18-May-2005'
UNION ALL SELECT 3990, '16-May-2005'
UNION ALL SELECT 3989, '16-May-2005'
UNION ALL SELECT 3988, '15-May-2005'
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT TOP 4 [Date Added] FROM ( SELECT DISTINCT [Date Added] FROM
MyWork ) A order by [Date Added] DESC )
This also seems to work as TOP is applied after building the result
set.
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
DESC )=20
John|||John Bell wrote:
> This is why posting DDL and example data is important
> (http://www.aspfaq.com/etiquett_e.asp?id=5006 ) because it removes
> this sort of ambiguity.
>
Thanks for that advice, I'll try to remember it for future postings.

> This also seems to work as TOP is applied after building the result
> set.
> SELECT * from MyWork
> WHERE [Date Added] IN (
> SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
> DESC )
>
D'oh! Why didn't I think of that? Thanks, much appreciated!
Daren|||SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
"Daren" wrote:

> Hi,
> I would like to retrieve all the records in a table that are in the
> latest 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the
> latest 4 dates in the table. Is it possible to do this in a stored
> procedure?
> Thanks,
> Daren
>|||OOPS, you might need:
SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM (SELECT DISTINCT d FROM
tbl) a ORDER BY d DESC)
"Brian Selzer" wrote:
> SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
> "Daren" wrote:
>

Getting one record at a time

I have a stored procedure that I call from a VB6 program:
DECLARE @.myid UNIQUEIDENTIFIER
BEGIN TRAN
SET @.myid = NEWID()
UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
WHERE ID=
(SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and ready=1)
COMMIT TRAN
SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
The ID column is a clustered index.
This gives me the next available row for each user (there are 40 users), I
am regularly getting deadlocks.
Any help would be greatly appreciated.
Thanks,
JkJamz
I'm not sure undertsabd what you are trying to do but read up please this
chapter
SET TRANSACTION ISOLATION LEVEL in the BOL
"Jamz" <Jamz@.discussions.microsoft.com> wrote in message
news:D406B8AE-1DF6-4425-A4C0-DCEC4A328B87@.microsoft.com...
> I have a stored procedure that I call from a VB6 program:
> DECLARE @.myid UNIQUEIDENTIFIER
> BEGIN TRAN
> SET @.myid = NEWID()
> UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
> WHERE ID=
> (SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and
ready=1)
> COMMIT TRAN
> SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
>
> The ID column is a clustered index.
> This gives me the next available row for each user (there are 40 users), I
> am regularly getting deadlocks.
> Any help would be greatly appreciated.
> Thanks,
> Jk|||Because XLOCK = <snip from Books OnLine>Use an exclusive lock that will be
held until the end of the transaction on all data processed by the statement
.
This lock can be specified with either PAGLOCK or TABLOCK, in which case the
exclusive lock applies to the appropriate level of granularity.</snip from
Books OnLine>
"HELD UNTIL THE END OF THE TRANSACTION ON ALL DATA PROCESSED... "
Your statement "Select Top 1..." Processes all rows in the table With Done
= 0, and Ready = 1, So it will pur a row Lock on every one of those rows an
d
hold it until the Commit Tran call...
This is a good example of why, unless you know exactly what you are doing,
and have carefully and identified a specific scenario where a lock hint is
necessary and appropriate, you should avoid them...
now, Next point...
Your query updates the row whose id is equal to the output of the subquery,
which returns the TOP 1 id from the table... But there is no Order By in the
subquery... This means that the value returned could be any ID in the table,
and SQL is under no oblication to return any one of the rows rather than any
other. Unless Order By clause is included, the Order of the rows in a
resultset is not guaranteed, and so the row returned by a TOP 1 select is
also not gauranteed...
Next point... assuming you put in an Order By (What that might be I don;t
know Order By PostCode, or what...) But assuming it's Order By ID, and that
you really want the Lowest id value (or the highest?) then you don;t need
Order By or top 1... and you can do all this without any of the Lock Hints o
r
explicit Transactions...
Select @.ID = Min(ID) -- [Or Max(ID)]
FROM data
WHERE done=0 and ready=1
-- --
UPDATE data SET
UID = NewID(), Done=1,
DateTimeDone = GetDate()
WHERE ID=@.ID
-- --
Select PostCode, MPN, ID
FROM data
WHERE ID = @.ID
-- --
"Jamz" wrote:

> I have a stored procedure that I call from a VB6 program:
> DECLARE @.myid UNIQUEIDENTIFIER
> BEGIN TRAN
> SET @.myid = NEWID()
> UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
> WHERE ID=
> (SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and ready=
1)
> COMMIT TRAN
> SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
>
> The ID column is a clustered index.
> This gives me the next available row for each user (there are 40 users), I
> am regularly getting deadlocks.
> Any help would be greatly appreciated.
> Thanks,
> Jk|||Thanks for your help, I'll try that.
I don't really care which record is brought back, I only care that it is not
used by anyone else.
When I wrote something similar to what you are suggesting I got the same
record being returned to different users.
Admittedly I had not ordered (or done a Min(x)) on it.
When I wrapped things in a transaction it did not help either.
Thanks,
JK
"CBretana" wrote:
> Because XLOCK = <snip from Books OnLine>Use an exclusive lock that will b
e
> held until the end of the transaction on all data processed by the stateme
nt.
> This lock can be specified with either PAGLOCK or TABLOCK, in which case t
he
> exclusive lock applies to the appropriate level of granularity.</snip from
> Books OnLine>
> "HELD UNTIL THE END OF THE TRANSACTION ON ALL DATA PROCESSED... "
> Your statement "Select Top 1..." Processes all rows in the table With Do
ne
> = 0, and Ready = 1, So it will pur a row Lock on every one of those rows
and
> hold it until the Commit Tran call...
> This is a good example of why, unless you know exactly what you are doing,
> and have carefully and identified a specific scenario where a lock hint is
> necessary and appropriate, you should avoid them...
> now, Next point...
> Your query updates the row whose id is equal to the output of the subquery
,
> which returns the TOP 1 id from the table... But there is no Order By in t
he
> subquery... This means that the value returned could be any ID in the tabl
e,
> and SQL is under no oblication to return any one of the rows rather than a
ny
> other. Unless Order By clause is included, the Order of the rows in a
> resultset is not guaranteed, and so the row returned by a TOP 1 select is
> also not gauranteed...
> Next point... assuming you put in an Order By (What that might be I don;t
> know Order By PostCode, or what...) But assuming it's Order By ID, and tha
t
> you really want the Lowest id value (or the highest?) then you don;t need
> Order By or top 1... and you can do all this without any of the Lock Hints
or
> explicit Transactions...
> Select @.ID = Min(ID) -- [Or Max(ID)]
> FROM data
> WHERE done=0 and ready=1
> -- --
> UPDATE data SET
> UID = NewID(), Done=1,
> DateTimeDone = GetDate()
> WHERE ID=@.ID
> -- --
> Select PostCode, MPN, ID
> FROM data
> WHERE ID = @.ID
> -- --
>
> "Jamz" wrote:
>|||Sorry, I understand what you are doing... Then you do need a transaction, bu
t
more importantly, you need to set the Isolation level one level higher than
Read Committed, (which is the default), to precvent concurrent users from
attempting t oread the min (ID) until you have set the done flag...
try this:
NOTE: Remember to reset the Isolation level after the commit...
Declare @.ID Integer
Set Isolation Level Repeatable Read
Begin Transaction
Select @.ID = Min(ID) FROM data
WHERE done=0 and ready=1
-- --
UPDATE data SET
UID = NewID(), Done=1,
DateTimeDone = GetDate()
WHERE ID=@.ID
Commit Transaction
Set Isolation Level Read Committed
-- --
Select PostCode, MPN, ID
FROM data
WHERE ID = @.ID
-- --
"Jamz" wrote:
> Thanks for your help, I'll try that.
> I don't really care which record is brought back, I only care that it is n
ot
> used by anyone else.
> When I wrote something similar to what you are suggesting I got the same
> record being returned to different users.
> Admittedly I had not ordered (or done a Min(x)) on it.
> When I wrapped things in a transaction it did not help either.
> Thanks,
> JK
> "CBretana" wrote:
>|||Thanks for that. I think that will fix it. I was missing the transaction
level setting, I went down the locking hint route instead.
Cheers
JK
"CBretana" wrote:
> Sorry, I understand what you are doing... Then you do need a transaction,
but
> more importantly, you need to set the Isolation level one level higher tha
n
> Read Committed, (which is the default), to precvent concurrent users from
> attempting t oread the min (ID) until you have set the done flag...
> try this:
> NOTE: Remember to reset the Isolation level after the commit...
> Declare @.ID Integer
> Set Isolation Level Repeatable Read
> Begin Transaction
> Select @.ID = Min(ID) FROM data
> WHERE done=0 and ready=1
> -- --
> UPDATE data SET
> UID = NewID(), Done=1,
> DateTimeDone = GetDate()
> WHERE ID=@.ID
> Commit Transaction
> Set Isolation Level Read Committed
> -- --
> Select PostCode, MPN, ID
> FROM data
> WHERE ID = @.ID
> -- --
>
> "Jamz" wrote:
>

Getting names from RID: db_id:file_id:page_no:row_no

Is there any way to retrieve table name and record data from RID:
db_id:file_id:page_no:row_no, for example,
RID: 1:1:1253:0 in my program?
Please reply me. Thanks in advance.
Regards,
Hyun-jik BaeYou may use the DBCC PAGE statement (undocumented). See:
http://www.sqlservercentral.com/col...ngdeadlocks.asp
http://www.microsoft.com/technet/pr...ks/inside6.mspx
Razvan

getting MAX value and displaying record

Hi all,
I basically want to display the single row that has the highest 'jobid' using a SP. I was playing with MAX(jobid) but getting errors about no group by, etc. Where do I begin with this?
SELECT Purchord, JobNo, Descr, customer, jobid
FROM JobsSELECT Purchord, JobNo, Descr, customer, jobid
FROM Jobs
INNER JOIN (SELECT MAX(jobid) jobid FOMR Jobs) HighestID ON Jobs.jobid = HighestID.jobid

-or-

SELECT TOP 1 Purchord, JobNo, Descr, customer, jobid
FROM Jobs
order by jobid desc|||select top 1 Purchord, JobNo, Descr, customer, jobid
from Jobs
order by jobid desc

Edit: beaten!!|||Ohhhh......!!!! Thank you very much guys!|||2 MINUTES 2 LATE, RUDY!

BOO-YAH!

Fastest fingers in the midwest...

Getting latest record from trigger

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

Quote:

Originally Posted by mccax

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

try getting the value from inserted/deleted tables

getting last record(s) inserted into a table

hi,
i want to get the rows last inserted into a table, there is no
identifier/auto incr column to determine? is it possible to do this? i think
physical order the records inserted may help, but how could we get the
records in the reverse physical order they inserted?
thanksif you don't have the primary key for the table then you can have a column
in the table with 'timestamp' datat type. Then you can enlist the last
record by
select top 1 * from mytable order by timestamp_column desc
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> hi,
> i want to get the rows last inserted into a table, there is no
> identifier/auto incr column to determine? is it possible to do this? i
think
> physical order the records inserted may help, but how could we get the
> records in the reverse physical order they inserted?
> thanks
>|||Actually, the previous suggestion will not work if you are doing any
updating... See example
drop table test
go
create table test (id int identity(1,1), ti timestamp, a varchar(100) not
null )
go
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
insert into test(a) values ('hi')
go
select * from test
go
update test set a = 'lo' where id = 3
go
select * from test
You'll notice that the timestamp column value for id = 3 is the largest...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> hi,
> i want to get the rows last inserted into a table, there is no
> identifier/auto incr column to determine? is it possible to do this? i
think
> physical order the records inserted may help, but how could we get the
> records in the reverse physical order they inserted?
> thanks
>|||u r right . because the timestamp value is updated automatically whenever
the record is inserted or updated.
poor solution for poor table design. (how come there be a table without
identifer (PK) ?)
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> Actually, the previous suggestion will not work if you are doing any
> updating... See example
> drop table test
> go
> create table test (id int identity(1,1), ti timestamp, a varchar(100) not
> null )
> go
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> insert into test(a) values ('hi')
> go
> select * from test
> go
> update test set a = 'lo' where id = 3
> go
> select * from test
> You'll notice that the timestamp column value for id = 3 is the largest...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Philip" <philipfairheight_@.hotmail.com> wrote in message
> news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > hi,
> > i want to get the rows last inserted into a table, there is no
> > identifier/auto incr column to determine? is it possible to do this? i
> think
> > physical order the records inserted may help, but how could we get the
> > records in the reverse physical order they inserted?
> > thanks
> >
> >
>|||You also have the option of including and update trigger
on the table to update a date/time field to the time of
the update. These operations can start to get a bit
expensive depending on how many updates you are doing. If
you are doing primarily inserts a default will work fine.
I do not know of any way to get this information with the
current internal storage structures.
Skippy|||anyway, shall we depend on the structure of the data, i mean sql server
returns always the records in the order they are inserted or this order
changes when pages optimized/compressed?
"I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
news:eqMtgmMmDHA.3688@.TK2MSFTNGP11.phx.gbl...
> u r right . because the timestamp value is updated automatically whenever
> the record is inserted or updated.
> poor solution for poor table design. (how come there be a table without
> identifer (PK) ?)
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> > Actually, the previous suggestion will not work if you are doing any
> > updating... See example
> >
> > drop table test
> > go
> > create table test (id int identity(1,1), ti timestamp, a varchar(100)
not
> > null )
> > go
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > insert into test(a) values ('hi')
> > go
> > select * from test
> > go
> > update test set a = 'lo' where id = 3
> > go
> > select * from test
> >
> > You'll notice that the timestamp column value for id = 3 is the
largest...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Philip" <philipfairheight_@.hotmail.com> wrote in message
> > news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > hi,
> > > i want to get the rows last inserted into a table, there is no
> > > identifier/auto incr column to determine? is it possible to do this? i
> > think
> > > physical order the records inserted may help, but how could we get the
> > > records in the reverse physical order they inserted?
> > > thanks
> > >
> > >
> >
> >
>|||> anyway, shall we depend on the structure of the data
No. The optimizer is free to process the query in any way it wants. You cannot rely on anything
(indexes, order of inserts etc). If you don't have ORDER BY, you get the rows in the order the
optimizer will find most efficient.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Philip" <philipfairheight_@.hotmail.com> wrote in message
news:eOlOPaSmDHA.744@.tk2msftngp13.phx.gbl...
> anyway, shall we depend on the structure of the data, i mean sql server
> returns always the records in the order they are inserted or this order
> changes when pages optimized/compressed?
> "I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
> news:eqMtgmMmDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > u r right . because the timestamp value is updated automatically whenever
> > the record is inserted or updated.
> >
> > poor solution for poor table design. (how come there be a table without
> > identifer (PK) ?)
> >
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:ugZ%23$fMmDHA.372@.TK2MSFTNGP11.phx.gbl...
> > > Actually, the previous suggestion will not work if you are doing any
> > > updating... See example
> > >
> > > drop table test
> > > go
> > > create table test (id int identity(1,1), ti timestamp, a varchar(100)
> not
> > > null )
> > > go
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > insert into test(a) values ('hi')
> > > go
> > > select * from test
> > > go
> > > update test set a = 'lo' where id = 3
> > > go
> > > select * from test
> > >
> > > You'll notice that the timestamp column value for id = 3 is the
> largest...
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > >
> > > "Philip" <philipfairheight_@.hotmail.com> wrote in message
> > > news:ej1tNkLmDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > > hi,
> > > > i want to get the rows last inserted into a table, there is no
> > > > identifier/auto incr column to determine? is it possible to do this? i
> > > think
> > > > physical order the records inserted may help, but how could we get the
> > > > records in the reverse physical order they inserted?
> > > > thanks
> > > >
> > > >
> > >
> > >
> >
> >
>

Getting last Identity value inserted in a table

Suppose we have a table with an identity field. Someone adds a record and wants to retrieve the value of the identity field from the just added record. I have seen code like this:

BEGIN TRAN

.... INSERT data

select newlyAddedID from table

END TRANS

In other words, they think that surrounding the INSERT and SELECT in a transaction insures they will get the correct value (even when other apps are updating this table).

Is this approach sound?

TIA,

barkingdog

Immediately after your insert, do:

SELECT SCOPE_IDENTITY()

Getting last (newest) one record (datetime column or id)

Hello everybody,

---------------

CREATE TABLE [T1] (
[IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[DateEvt] [datetime] NOT NULL,
[Value] [varchar] (10) NOT NULL ,
[DataX] [varchar] (10) NULL ,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[IDX]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00',
'0000000001', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01',
'0000000002', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02',
'0000000003', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00',
'0000000001', 'BBBBBBBBBB')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00',
'0000000001', 'CCCCCCCCCC')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00',
'0000000001', 'DDDDDDDDDD')
GO

---------------

and the question is:
In which fastes and best for the preformance way, get the last IDX of
specified Value.

I could do this like this:

---------------
declare @.nIDX numeric
declare @.sValue varchar(10)

select top 1 @.nIDX = IDX from T1
where Value = @.sValue
order by DateEVT desc
---------------

But I know, this is not fast (even if I have index on DateEVT field),
and I'm quite sure, that there is better way to get this IDX.
Anyway, this table can be big (like 20 milions records).

I could take the max of IDX, but is it a sure way?
Any help? Thanks in advance

Matik> declare @.nIDX numeric
> declare @.sValue varchar(10)
> select top 1 @.nIDX = IDX from T1
> where Value = @.sValue
> order by DateEVT desc

To optimize this query, consider creating a non-clustered index on Value and
using MAX. This index will cover the query becuase the clustered index
value (IDX) is also stored in the non-clustered index.

DECLARE @.nIDX numeric
DECLARE @.sValue varchar(10)
SELECT @.nIDX = MAX(IDX)
FROM T1
WHERE Value = @.sValue

Also, if you are using SQL 2000, consider bigint instead of numeric(18, 0).
This will save a little space.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Matik" <marzec@.sauron.xo.pl> wrote in message
news:1102374422.534762.91540@.f14g2000cwb.googlegro ups.com...
> Hello everybody,
> ---------------
> CREATE TABLE [T1] (
> [IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [DateEvt] [datetime] NOT NULL,
> [Value] [varchar] (10) NOT NULL ,
> [DataX] [varchar] (10) NULL ,
> CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
> (
> [IDX]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00',
> '0000000001', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01',
> '0000000002', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02',
> '0000000003', 'AAAAAAAAAA')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00',
> '0000000001', 'BBBBBBBBBB')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00',
> '0000000001', 'CCCCCCCCCC')
> insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00',
> '0000000001', 'DDDDDDDDDD')
> GO
> ---------------
> and the question is:
> In which fastes and best for the preformance way, get the last IDX of
> specified Value.
> I could do this like this:
> ---------------
> declare @.nIDX numeric
> declare @.sValue varchar(10)
> select top 1 @.nIDX = IDX from T1
> where Value = @.sValue
> order by DateEVT desc
> ---------------
> But I know, this is not fast (even if I have index on DateEVT field),
> and I'm quite sure, that there is better way to get this IDX.
> Anyway, this table can be big (like 20 milions records).
> I could take the max of IDX, but is it a sure way?
> Any help? Thanks in advance
> Matik

Getting Identity when inserting a record from Stored Procedure....

I have a stored procedure that inserts a record into a table.
I need to get the Identity seed or Key that was just created by this
insert. Can I do this inside the stored procedure, returning the just
created key?
Here is the stored procedure...
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1
AS
INSERT INTO a_TixDetail
(SvcTixKey, Date, [Performed by], [Detail Summary],
External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
RETURN
TixKey is a field in the table that is the Primary key. It will be
genereated when the Insert is executed. How can I get this returned from
this procedure?
Thanks,
RogIn SQL Server 2000 use SCOPE_IDENTITY() to return the inserted IDENTITY
value. In SQL Server 7.0 use @.@.IDENTITY.
David Portas
SQL Server MVP
--|||Roger,
Create an output parameter and get the value from the function
SCOPE_IDENTITY().
Example:
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1,
@.new_id int output
AS
set nocount on
INSERT INTO a_TixDetail (SvcTixKey, Date, [Performed by], [Detail
Summary],External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
set @.new_id = scope_identity()
RETURN
go
declare @.i int
declare @.rv int
exec @.rv = R_InsertTicketDetail ..., @.i output
print @.rv
print @.i
go
AMB
"Roger" wrote:

> I have a stored procedure that inserts a record into a table.
> I need to get the Identity seed or Key that was just created by this
> insert. Can I do this inside the stored procedure, returning the just
> created key?
> Here is the stored procedure...
> ALTER PROCEDURE R_InsertTicketDetail
> @.SvcTixKey int,
> @.OpenDate datetime,
> @.PerformedBy nvarchar(50),
> @.Detail ntext,
> @.External bit = -1
> AS
> INSERT INTO a_TixDetail
> (SvcTixKey, Date, [Performed by], [Detail Summary],
> External)
> VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
> RETURN
>
> TixKey is a field in the table that is the Primary key. It will be
> genereated when the Insert is executed. How can I get this returned from
> this procedure?
> Thanks,
> Rog
>
>

Getting Identity of Inserted Record using inserted event??

is there any way of getting the identity without using the "@.@.idemtity" in sql? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me?use scope_identity() is the best way. do not try to get last identity from VB, C#.

Hope this help