return duplicate records

Hello experts,

I'm trying the run the following query with specific intentions.

I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.

I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?

Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);

alexAlex,

There are a few solutions. Two are (might have typos, but you should be
able to get the idea):

select Employee_last_name, Employee_first_name
from tbl_employee
join (
select '009' as id union all
select '008' as id union all
select '007' as id union all
select '007' as id union all
select '006' as id
) as IDs
on IDs.id = tbl_employee.employee_id

or to make the specification of ids simpler:

declare @.ids varchar(1000)
set @.ids = '009008007007006'
declare @.idlength int
set @.idlength = 3

select Employee_last_name, Employee_first_name
from tbl_employee
join a_permanent_table_of_integers_from_0_to_whatever as Nums
on employee_id = substring(@.ids,@.idlength*n+1,@.idlength)
and n < len(@.ids)/@.idlength
-- [n] is the column name for the permanent table and should
-- be that tables primary key

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
alex wrote:

Quote:

Originally Posted by

Hello experts,
>
I'm trying the run the following query with specific intentions.
>
I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.
>
I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?
>
Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);
>
alex
>

|||>I would like the query to return 5 results; i.e., 4 distinct and one duplicate. <<

The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:

SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>I would like the query to return 5 results; i.e., 4 distinct and one

duplicate. <<

Quote:

Originally Posted by

>
The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:
>
SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'

Joe, I thought you knew SQL? This query will not return the results
that Alex was asking for.

Why is left as an exercise to the reader.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
ChrisHi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:
> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

Retriveing the process ID for a SQL Server Agent job being run

We have created a SQL Server Job called 'Asynchronous Batch Agent'
which is run Asynchronously on a set of databases each containing a
batch.
What I would like to do is to retrieve the Process ID of a particular
instance of this agent when its being called and write that to a table
with the ID for the individual database.
Im having little luck at finding this information. I look at sp_who2
and sysprocesses...but get a link to the job.
Thanks for your help on this
Chris
Hi Chris,
Try @.@.spid, db_id() and db_name().
Hope this helps,
Ben Nevarez
"chris.asaipillai@.gmail.com" wrote:

> We have created a SQL Server Job called 'Asynchronous Batch Agent'
> which is run Asynchronously on a set of databases each containing a
> batch.
> What I would like to do is to retrieve the Process ID of a particular
> instance of this agent when its being called and write that to a table
> with the ID for the individual database.
> Im having little luck at finding this information. I look at sp_who2
> and sysprocesses...but get a link to the job.
> Thanks for your help on this
> Chris
>

Retrieving ID after insert

Hi, I have a Stored procedure doind an INSERT which then returns @.@.Identity.

I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..

Not sure where I am going wrong...

Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?

Many ThanksShow your SP, and how you call it. You can get info like htis back as an output parameter, or as a return code. Absent knowing what you are doing, it is impossible to help.|||This is just a suggestion.

First define the input parameters and assign its values.

'Define the output parameter
With cmd.Parameters.Add("@.outputField", SqlDbType.Int)
.Direction = ParameterDirection.Output
End With

'Read the result
Dim dr As SqlDataReader = cmd.ExecuteReader
Dim returnId As Integer
If dr.Read Then
returnId = dr("OutputField")
End If
dr.Close()

My cmd is the reference to the SqlCommand.
OutputField – Field which you want to return.

Hope this might give you some help.|||Use Scope_Identity() instead of @.@.Identity.

Also..

cmd.ExecuteNonReader()

returnID = cmd.Parameters("@.outputField").Value
cmd.Dipose()

retrieve records affected count from ADO?

Hello,

If I run an action SP from MS Access using ADO:
...
cmd.execute

where the SP is something like Create...
Update tbl1 set fld1 = 'something' where...

how can I retrive the count of records affected like from Query
analyzer?

Thanks,
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rich Protzel (rpng123@.aol.com) writes:
> If I run an action SP from MS Access using ADO:
> ..
> cmd.execute
> where the SP is something like Create...
> Update tbl1 set fld1 = 'something' where...
> how can I retrive the count of records affected like from Query
> analyzer?

The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.

--
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 for your reply.

>>The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.
<<

May I ask how I go about retrieving the Count of records affected back
into MS Access?

Dim CountRecsAffected As Long
...
cmd.Parameters("@.bDate").Value = sDate
cmd.Execute
CountRecsAffected = cmd.?
or
CountRecsAffected = ?
or
CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
the count of parameters being used?

Thanks again for your reply.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Rich Protzel (rpng123@.aol.com) writes:
> May I ask how I go about retrieving the Count of records affected back
> into MS Access?
> Dim CountRecsAffected As Long
> ..
> cmd.Parameters("@.bDate").Value = sDate
> cmd.Execute
> CountRecsAffected = cmd.?
> or
> CountRecsAffected = ?
> or
> CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
> the count of parameters being used?

cmd.Execute CountRecsAffected

Assuming that Access works like Visual Basic.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 3 Aug 2003 16:08:14 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar@.algonet.se>
wrote:

>Assuming that Access works like Visual Basic.

For most things, including ADO, it does.

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)|||Thank you all for your replies. I think I get the idea now about how to
retrieve the count of records affected from an action sp.

One more question if I may:

If I set my sp to

SET NOCOUNT ON

would that improve the performance of my sp? It is not critical for me
to retrieve the count of records affected, mostly just a check. But if
the sp works consistently, and setting

SET NOCOUNT ON

significantly improve performance, then maybe I should consider that.
Most of my action sp's are affecting over 100,000 records of tables with
nearly 200 fields (no redundant fields) with over 1,000,000 records.

Thanks again,

Rich

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

Retrieve multiple variables from Stored Procedure (SQLHelper)

Hi all,

I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:

ie:

SELECT @.Hits = COUNT(DISTINCT StatID) FROM Stats

...etc...

The SP is currently called as below:

SqlParameter[] sqlParams = new SqlParameter[]
 {
 new SqlParameter("@.FromDate", Request["FromDate"]),
 new SqlParameter("@.ToDate", Request["ToDate"]),
 };

My question is this: How do I retrieve these variables?

I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.

Thanks in advance,

PeteHi.

Could u please expain how you are returning the values.. all 3 as output parameter?

as the code will depend on the way the values are returned..|||my full Stored Procedure looks as so:

CREATE PROC GetGeneralStats
@.FromDate smalldatetime,
@.ToDate smalldatetime
AS
Declare @.uniqueHits int
Declare @.noOfSearches int
SELECT @.uniqueHits = COUNT(DISTINCT StatID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
-- No of times Searchresults Shown
SELECT @.NoOfSearches = COUNT(SectionID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
AND SectionID = 3
Go

Cheers,

Pete|||This procedure is retieving and storing values in 2 variables and returning nothing.

you need to add 2 output parameters to the procedure..

CREATE PROC GetGeneralStats
@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.uniqueHits int OUTPUT,
@.noOfSearches int OUTPUT
AS
SELECT @.uniqueHits = COUNT(DISTINCT StatID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
-- No of times Searchresults Shown
SELECT @.NoOfSearches = COUNT(SectionID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
AND SectionID = 3

|||Hi,

Thanks for the reply.

As you said (and now having looked in the documentation) this is how it works but....

Im getting

Procedure 'GetGeneralStats' expects parameter '@.uniqueHits', which was not supplied.

So its thinking that I want them as Input variables? Even though I have them as:

@.uniqueHits int OUTPUT,
@.noOfSearches int OUTPUT
AS

pete|||Arrgghhh!!! Everywhere I look Im told to do it this way but Im getting this stupid error message!

Im tearing my hair out over this!!!!!!!