Distributed insert on oracle DB does not work from within a transaction.

Dear All,

I have MS SQL 2000 on Windows 2000 box and Oracle 9i on RH Linux 9.

I wanna write a trigger on MS SQL which inserts a record in the Oracle table.

So I have configured a Linked server on MS SQL using Microsoft OLE DB provider MSDAORA and am able to insert into (Single insert st) oracle table successfully from the Query Analyzer.

However the insert does not work when I code it in the trigger. Basically insert on the Oracle DB does not work from a transaction.
I receive the foll error when the trigger executes -

Server: Msg 7391, Level 16, State 1, Procedure AIN_sql2ora, Line 14
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

Can anyone help me on this ?

Regards,
Azhar.set implicit transactions off

DISTINCT values from a table

Hi,

I am trying to output a list of data from a table, showing only one record of each TypeID.

So, for instance, I have a simple SQL query that says:

SELECT DISTINCT AlbumTypeIDFROM AlbumORDER BY AlbumTypeIDDESC

This works correctly, and gives a list of 1,2,3. But I need more information than that, I want the Description field output with the ID, but how can I do this without assigning that to be Distinc also?

When I try: SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

The output is completely wrong.

Many thanks

My guess is that Description doesn't belong to AlbumTypeID, but to something like AlbumID. If you have an AblumType table with an AlbumTypeID and a Description, change you query to run against that table instead:

SELECT DISTINCT AlbumTypeID, Description FROM AlbumType ORDER BY AlbumTypeID DESC

|||

SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

Presumably you have lots of album types and lots of descriptions, so this query will only return results where the combination of the two fields is different to all the other results. For example, two AlbumTypeId of 1 records with a description of "Cars" would result in a single record.

What did you want to get back?

|||

Try

SELECT DISTINCT dbo.GetFirstAlbumDescription(AlbumTypeId), AlbumTypeId FROM Album
where
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetFirstAlbumDescription
(
@.AlbumTypeId INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @.RETURN VARCHAR(100)
SELECT @.RETURN = DESCRIPTION FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END
GO|||When I ran it I got
AlbumTypeId
-------- ----
Album 1 of 1 1
Album 1 of 2 2
Album 1 of 3 3

(3 row(s) affected) [excess apces deleted]|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Using the additional function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[GetFirstAlbumId]
(
@.AlbumTypeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.RETURN INT
SELECT @.RETURN = Id FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END

SELECT DISTINCT dbo.GetFirstAlbumId(AlbumTypeId) as a, dbo.GetFirstAlbumDescription(AlbumTypeId) as B, AlbumTypeId FROM Album

gave

a B AlbumTypeId
---- ---------- ----
1 Album 1 of 1 1
3 Album 1 of 2 2
6 Album 1 of 3 3

|||Can you provide some more sample data with same AlbumId and different AlbumTypeId's..with expected output.|||

SELECT *

FROM Albumns a

JOIN (

SELECT AlbumnTypeID,MIN(AlbumnID) AS LowestAlbumnID

FROM Albumns

GROUP BY AlbumnTypeID) t1 ON a.AlbumnID=t1.LowestAlbumnID

DISTINCT to ShortDateString, not DISTINCT to the DateTime; How?

Hello,

I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a table

CREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )

This app also calculates the total number of proposals coming from a specific branch in a given date by
SELECTing COUNT(BranchID) FROM Proposals WHEREBranchID=@.prmBranchID ANDProposal_Date=@.prmDate
and prints them in a table (my target table).

This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"
and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the
"SELECT DISTINCT BranchID FROM Proposals".
This target table converts the DateTime values ToShortDateString so
that we are able to see comfortably which branch office has sent how many proposals in a given day.

So far so good, and everything works fine except one thing:

Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and
11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)

What I need to do is to get a result like (in a neat fashion :)

"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"

So, how to do it in a suitable way?

Thanks in advance.

Try this -

Select Distinct Convert(char(10), Proposal_Date, 101)

It will eliminate the time part from the selection

Regards

Ash

|||

This one will solve your problem:

SELECT

DISTINCTCONVERT(NCHAR(8), Proposal_Date, 112)AS Proposal_Date

FROM

Proposals

You can look up from Books Online for the date, convert functions to help you understand more about the date and time. If you need more help, please post again.

|||

Hello,

Yeah that was what I neeed to know, and it solved the problem.

(apparently, I'm still new to T-SQL :)

Thanks.

|||

I know your problem is resolved but try the link below for more about the SQL Server DateTime convert function codes. And if you want Short Datetime you use SmallDateTime data type it gives you DateTime less seconds because it has less resolution. Hope this helps.

http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?
How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

distinct select statement of households

I'm trying to match households based on last name, house no., street
and apt no. and select only 1 record so that I get something like 'the
DOE Family' when all criteria match. I still want to keep Frank Black
results.
Here is how the data is structured:
+--+--+--+--+
| Last Name | First Name | Initial | House No | Street | Apt |
+--+--+--+--+
| DOE | JOHN | | 100 | MAIN STREET | 3 |
| DOE | JANE | | 100 | MAIN STREET | 3 |
| BLACK | FRANK | M | 104 | CHESTNUT DR | |
I initially tried doing a subselect statement but I'm still not
getting just one result for 'Doe':
SELECT DISTINCT
[Last_Name],[First_Name],[Initial],
CONVERT(INT, [House_Number]) AS 'House',[Street_Name],[Apt_Number]
FROM address_tbl T
WHERE
freq >= 3 AND
Street_Name = (
SELECT DISTINCT TOP 1 Street_Name FROM address_tbl T1 WHERE
T.House_Number = T1.House_Number AND
T.Street_Name = T1.Street_Name AND
T.Apt_Number = T1.Apt_Number AND
T.Zip_Code = T1.Zip_Code
)
ORDER BY Street_Name, House
What am I doing wrong?How do you define what First Name/Middle Initial to select?
Try:
SELECT Last_Name,
MAX(First_Name) AS First_Name,
MAX(Initial) AS Initial,
CONVERT(INT, House_Number) AS House,
Street_Name,
Apt_Number
FROM address_tbl T
WHERE freq >= 3
GROUP BY Last_Name, House_Number, Street_Name, Apt_Number
ORDER BY Street_Name, House
HTH,
Plamen Ratchev
http://www.SQLStudio.com

DISTINCT OR GROUP BY

I have some records in my Sql Db.
There are some duplicate record in my email and adsoyad fields
Email Adsoyad
a@.a.com Savas
a@.a.com Pele
b@.b.com Savas
c@.c.om Ilim
d@.d.com Hasan
d@.d.com Hasan
I want to eliminate email address which can be duplicate and its Adsoyad
field.
There can be different adsoyad records for duplicate email records. I want
to select one of them which doesnt have any importance for me .
I also have soma email records which doesnt have @. character. I want to
eliminate those records too. Is there any command in SQL like INSTR ?Do you want to delete the row or update the fields?
For deleting the rows with email without '@.'
Delete from table where email not like '_%@._%'
I put the additional requirement that there must be at least one char in
front of and 1 char behind the @. sign...
The second thing you wanted to do is delete the dupe emails by picking the
Adsoyad which does not have meaning for you... You'll have to repst and
describe how you determine which Adsoyad doesn't have meaning and someone
will help you with the SQL...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Savas Ates" wrote:

> I have some records in my Sql Db.
> There are some duplicate record in my email and adsoyad fields
> Email Adsoyad
> a@.a.com Savas
> a@.a.com Pele
> b@.b.com Savas
> c@.c.om Ilim
> d@.d.com Hasan
> d@.d.com Hasan
> I want to eliminate email address which can be duplicate and its Adsoyad
> field.
> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
>
>|||> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
Use DISTINCT, then you can tell from your query that that is your purpose.
GROUP BY is typically used for aggregation.

> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
Yes, look at CHARINDEX, PATINDEX. You might also consider a function or
even a check constraint that actually validates the format of an e-mail
address. Then you can't get any crap in there in the first place. Search
groups.google.com, there are plenty of examples out there ready to use.|||IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
one of adsoyad records ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com>, haber iletisinde
unlar yazd:E9100F46-9958-4408-B527-2209F25B40B5@.microsoft.com...
> Do you want to delete the row or update the fields?
> For deleting the rows with email without '@.'
> Delete from table where email not like '_%@._%'
> I put the additional requirement that there must be at least one char in
> front of and 1 char behind the @. sign...
> The second thing you wanted to do is delete the dupe emails by picking the
> Adsoyad which does not have meaning for you... You'll have to repst and
> describe how you determine which Adsoyad doesn't have meaning and someone
> will help you with the SQL...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Savas Ates" wrote:
>|||> IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
> one of adsoyad records ?
Do you need adsoyad in the result? Can you provide more clear requirements
so we don't have to ask 80 follow-up questions? Please see
http://www.aspfaq.com/5006|||Yep I need adsoyad records too. But it doesnt matter which one i can come
up. I want to elimitate email addresses which are dublicate and adsoyad
records whics is tied to one of the duplicate email addresses. I should say
that i dont need to chooese any adsoyad records exactly . Just wanna pick
up one of them .
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2366CMQ1TGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Do you need adsoyad in the result? Can you provide more clear
> requirements so we don't have to ask 80 follow-up questions? Please see
> http://www.aspfaq.com/5006
>|||(reposting using the Microsoft's site, since the Google Groups post was not
found here)
Hi, Savas
Try something like this (untested):
SELECT Email, MIN(Adsoyad)
FROM YourTable
WHERE Email LIKE '_%@._%._%' AND Email NOT LIKE '%@.%@.%'
GROUP BY Email
Note that the LIKE expression above (although more complex than what
you have suggested), still doesn't ensure a valid e-mail address (as
per RFC 822).
Razvan

Distinct on only one column, return all columns

I have a table with many columns. I want to return all columns, and I want
only the first record for each distinct value in the salutation column. I
come up with the following:
select c1.* from complainer as c1
join (select distinct salutation from complainer) as c2
on c1.complainerid = c2.complainerid
The code above returns the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'complainerid'.
Suggestions greatly appreciated.
RandyHow do you determine "first"? I'm assuming that you are using an
IDENTITY column as your id, so try something like this:
select c1.* from complainer as c1
join (select complainerid = MIN(complainerid), salutation
from complainer GROUP BY salutation) as c2
on c1.complainerid = c2.complainerid
HTH,
Stu

Distinct insert

I am trying to insert a record id primary at the same time that I am trying
to insert a Distinct Account number.
The insert statement is something kind of like this:
insert into Accounts (AccountsOID,Account)
select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
AutoExpt
In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
of which need to be put into the Accounts table, however there must be only
one entry for the Lic tag in the accounts table. The Rec No +100 is just to
create a unique primary key for the AccountsOID.
Is there anyway I can get only the Distinct Lic Tag's, but at the same time
get a unique primary key for them.
Thanks,
On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:

>I am trying to insert a record id primary at the same time that I am trying
>to insert a Distinct Account number.
>The insert statement is something kind of like this:
>insert into Accounts (AccountsOID,Account)
>select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
>AutoExpt
>In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
>of which need to be put into the Accounts table, however there must be only
>one entry for the Lic tag in the accounts table. The Rec No +100 is just to
>create a unique primary key for the AccountsOID.
>Is there anyway I can get only the Distinct Lic Tag's, but at the same time
>get a unique primary key for them.
>Thanks,
Hi Robert,
Please provide DDL (CREATE TABLE statements, with constraints), sample
data (as INSERT statements), expected output and a description of the
business problem you're trying to solve.
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Unfortunately I do not have the create table SQL statements
What I am trying to accomplish is importing data from an excel file into a
SQL database. To make it easier to work with I simply straight imported the
excel file into a new table in the SQL database (AutoExpt). Accounts is one
of the tables that I need to import into. I do know that the OID for the
accounts table is not automatically generated, and must be generated manually.
In the AutoExpt table there is a listing of vechiles and what accounts
maintenance should be charged to. I can add the vechiles fine to the
Equipment table (Not listed here because it works just fine). However another
table EquipmentFeature is linked to the Equipment table. It contains things
such as account numbers, and the department that the vechile belongs to.
From what I can tell I can not add records to the Equipment feature table
such as the account number because they do not exist in the Accounts table.
So the solution is to first add all the distinct account numbers into the
accounts table. This also applies to the departments table as well.
I do have a backup of the database so I can fool aroudn with it all I want
without worry of data loss or damage, plus I am working on a dev server so no
actual users are accessing the database. I found that I can add duplicate
records in the accounts table and the departments table, however I have found
that I can't remove some duplicates in the departments table because it
claims that a record needs to exist in the General table first.
My theory is that if I add only the distinct account numbers and departments
I should be fine, and it should stop telling me that "A record must exist in
the accounts table first"
Unfortunately the company that makes the software is not offering much
support on importing the data. So I am left to figure it out myself.
"Hugo Kornelis" wrote:

> On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:
>
> Hi Robert,
> Please provide DDL (CREATE TABLE statements, with constraints), sample
> data (as INSERT statements), expected output and a description of the
> business problem you're trying to solve.
> http://www.aspfaq.com/etiquette.asp?id=5006
> http://vyaskn.tripod.com/code.htm#inserts
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:

>Unfortunately I do not have the create table SQL statements
(snip)
Hi Robert,
Did you read the links I posted in my message? They contain instructions
on how you can generate the create table statements from the existing
tables (the ewb site shows how it's done with Query Analyzer; there's an
option to generate SQL in Enterprise Manager as well).
I read your description, but I can't figure out what you're trying to do
and what problems you are trying to tackle. I really need the table
structure and sample data to understand your problem.
(BTW, if therre are legal or other issues that prohibit you from posting
the table structure, you may also create a fake table that recreates the
problem).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I can't post the structure or data because it's against our policies. I can
provide you something that looks similair. I basically need to be able to
insert only the unique account numbers from one table into another table,
however at the same time inserting a unique primary key into the same
destination table.
For example I kinda have something like this:
Primary Key From Table 1, Account Number
1,A
2,A
3,B
4,C
5,B
I need to put that information in a seperate table that is similair to this:
Primary From table2, Account Number
-564564,K
564578,D
7246,S
54567,F
The second table can not have any duplicate account numbers and of course
the Primary key.
What I want to do is select from table1 something looking like this:
Primary Key From Table 1, Account Number
1,A
3,B
4,C
Leaving me only with unique primary keys and account numbers.
The end result for table2 should look something like this:
-564564,K
564578,D
7246,S
54567,F
1,A
3,B
4,C
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:
> (snip)
> Hi Robert,
> Did you read the links I posted in my message? They contain instructions
> on how you can generate the create table statements from the existing
> tables (the ewb site shows how it's done with Query Analyzer; there's an
> option to generate SQL in Enterprise Manager as well).
> I read your description, but I can't figure out what you're trying to do
> and what problems you are trying to tackle. I really need the table
> structure and sample data to understand your problem.
> (BTW, if therre are legal or other issues that prohibit you from posting
> the table structure, you may also create a fake table that recreates the
> problem).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:

>I can't post the structure or data because it's against our policies. I can
>provide you something that looks similair. I basically need to be able to
>insert only the unique account numbers from one table into another table,
>however at the same time inserting a unique primary key into the same
>destination table.
>For example I kinda have something like this:
>Primary Key From Table 1, Account Number
>1,A
>2,A
>3,B
>4,C
>5,B
>I need to put that information in a seperate table that is similair to this:
>Primary From table2, Account Number
>-564564,K
>564578,D
>7246,S
>54567,F
>The second table can not have any duplicate account numbers and of course
>the Primary key.
>What I want to do is select from table1 something looking like this:
>Primary Key From Table 1, Account Number
>1,A
>3,B
>4,C
>Leaving me only with unique primary keys and account numbers.
>The end result for table2 should look something like this:
>-564564,K
>564578,D
>7246,S
>54567,F
>1,A
>3,B
>4,C
Hi Robert,
The following is untested as I only test solutions I provide in newsgroups
if I can copy and paste CREATE TABLE and INSERT statements. I like solving
problems and helping people, but I detest typing - I have to do plenty of
typing in my job :-)
But try if this gives you what you need:
INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
SELECT MIN(PrimKeyFromTable1), AccountNumber
FROM Table1
GROUP BY AccountNumber
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That did the trick, I should have thought about grouping them.
Hugo... Your a genius!
Thanks,
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:
>
> Hi Robert,
> The following is untested as I only test solutions I provide in newsgroups
> if I can copy and paste CREATE TABLE and INSERT statements. I like solving
> problems and helping people, but I detest typing - I have to do plenty of
> typing in my job :-)
> But try if this gives you what you need:
> INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
> SELECT MIN(PrimKeyFromTable1), AccountNumber
> FROM Table1
> GROUP BY AccountNumber
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

distibuted transations in linked server

please solve this problem.

when i am executing any transaction from one server to update record in other linked server database.

create PROCEDURE [dbo].[aTest]

@.iNum as int

AS

BEGIN

SET NOCOUNT ON;

begin tran

update aaa set sName='Deepak'

where iNum=@.iNum

exec [REPORTSRVR\SQL2005].HDFCCUSTOMER.dbo.aProc @.iNum

if @.@.error <> 0

begin

rollback tran

return

end

else

begin

commit tran

end

SET NOCOUNT OFF;

END

it shows following error

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPORTSRVR\SQL2005" was unable to begin a distributed transaction.

1. Check/change your server option "remote proc trans" via sp_configure or Connections tab in SQL Server Instance Properties window

2. If you need MS DTC for distributed transactions: http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx

|||

thanks for response.

i m using transaction statement. is it possible that single transaction can talk from two server?

displaying one data record

Hi all, this is a very basic question of diplaying a data.
on my aspx page I have datasource that will return only ONE record.

<asp:SqlDataSource ID="sdsCategoryName" runat="server" ConnectionString="<%$ ConnectionStrings:KaruselaConnectionString%>"
SelectCommand="SELECT Title FROM tbh_Categories WHERE CategoryID=@.categoryID ">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="-1" Name="CategoryID" QueryStringField="id" Type="int32"/>
</SelectParameters>
</asp:SqlDataSource>

on the server side I would like to manipulate the title of the page according to the data returned from the query:

and on the behind code

if (!this.IsPostBack && !string.IsNullOrEmpty(this.Request.QueryString["ID"])) { DataView dv2 = (DataView)sdsCategoryName.Select(arg);this.Title =string.Format(this.Title, dv2.Table.Columns[0].ToString()); dv2.Dispose(); }

of course it doesn't work. my question is this. do we really have to put the query datasource on the client side?

and secondly, how can I view the recorsd I recieves from the query?

Thanks for the help.

on yours code behind file write this code>>>>

add namespace

System.Data.SqlClient;

and on load event write

SqlConnection sqlconn = new SqlConnection("yourconnectionstring");

SqlCommand sqlcomd=new SqlCommad("SELECT Title FROM tbh_Categories WHERE CategoryID=@.categoryID ",sqlconn);

sqlcomd.CommandType= CommandType.Text;

sqlcomd.Parameter.Add(new SqlParameter("@.catergoryID") ,SqlDbType.NChar ,10);

sqlcomd.Parameter["@.categoryID"].Value = Request.QueryString["ID"];

SqlDataAdapter myadp = new SqlDataAdapter(sqlcomd);

DataTable dt = new DataTable();
myadp.Fill(dt);

Label1.Text= dt.Rows[0].ItemArray.GetValue (0).ToString ();

Mark post as answer if it helped u>>>

Have a great day!

Displaying multiple records from one record

Hi there,

I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.

Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?

User table
Userid Username

1 Bob
2 Luke
3 John

Store procedure result
Companyname Username

Acrm Corp Null
Maxwellarm Ltd Null

Regards,

Since my last post, I found that the CMS is storing individual ids.

Still it would be nice to know how others are doing this.

An addition to my post above, I believe if I wanted more than one firstname and surname to appear in the columns, that I would have to create a different stored procedure retrieving the unique id from the first stored procedure.

displaying more then one record from the same table in the same field

I was wondering if there was any way to make crystal reports grab multiple records from the same table and displahy them in one database field. Basicly I have a table that users would type info into each line of text that they type would equal one record on the table. When I run the report I need to display all the lines of text that they typed in. If I just refrence the table in the database field in crystal it will only pull the first record from the table and thats it.

Here is an example of the table
00000101 0001 This is a tes of the emergency broadcast system, if there was an actual emergency you would hear
00000101 0002 about it after the tones.This is a tes of the emergency broadcast system, if there was an actual
00000101 0003 emergency you would here about it after the tones.This is a tes of the emergency broadcast system

Now if I run the report the only thing it will dislay is the first line (record number "0001")

Thanks for any helpAdd a Textbox to your report. Then drag the first DB Field into the textbox, then drag the second DB Field into the same textbox.

I attached a picture of an Address field from one of my reports. It's a textbox and I dropped the 5 DB fields onto it (Name, Address, City, State, Zip)

Displaying last record in SQL database table

Got a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.

Basically, I have a form I developed in Visual Studio using ASP.NET VB. I submit the form and now I want to recall the last entry into the database I would have just made and display it on the following page (thank you page).
Here is the code I have:

PublicClass WebForm1

Inherits System.Web.UI.Page

ProtectedWithEvents SqlSelectCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlInsertCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlUpdateCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlDeleteCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlConnection1As System.Data.SqlClient.SqlConnection

ProtectedWithEvents SqlDataAdapter1As System.Data.SqlClient.SqlDataAdapter

ProtectedWithEvents DataSet1As System.Data.DataSet

ProtectedWithEvents DataList1As System.Web.UI.WebControls.DataList

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.

<System.Diagnostics.DebuggerStepThrough()>PrivateSub InitializeComponent()

Me.SqlSelectCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlInsertCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlUpdateCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlDeleteCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlConnection1 =New System.Data.SqlClient.SqlConnection()

Me.SqlDataAdapter1 =New System.Data.SqlClient.SqlDataAdapter()

Me.DataSet1 =New System.Data.DataSet()

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).BeginInit()

'

'SqlSelectCommand1

'

Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM" & _

" authors"

Me.SqlSelectCommand1.Connection =Me.SqlConnection1

'

'SqlInsertCommand1

'

Me.SqlInsertCommand1.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, " & _

"contract) VALUES (@.au_id, @.au_lname, @.au_fname, @.phone, @.address, @.city, @.state," & _

" @.zip, @.contract); SELECT au_id, au_lname, au_fname, phone, address, city, state" & _

", zip, contract FROM authors WHERE (au_id = @.au_id)"

Me.SqlInsertCommand1.Connection =Me.SqlConnection1

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

'

'SqlUpdateCommand1

'

Me.SqlUpdateCommand1.CommandText = "UPDATE authors SET au_id = @.au_id, au_lname = @.au_lname, au_fname = @.au_fname, ph" & _

"one = @.phone, address = @.address, city = @.city, state = @.state, zip = @.zip, cont" & _

"ract = @.contract WHERE (au_id = @.Original_au_id) AND (address = @.Original_addres" & _

"s OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_au" & _

"_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Origi" & _

"nal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (phon" & _

"e = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL AND" & _

" state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS NUL" & _

"L); SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract" & _

" FROM authors WHERE (au_id = @.au_id)"

Me.SqlUpdateCommand1.Connection =Me.SqlConnection1

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlDeleteCommand1

'

Me.SqlDeleteCommand1.CommandText = "DELETE FROM authors WHERE (au_id = @.Original_au_id) AND (address = @.Original_addr" & _

"ess OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_" & _

"au_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Ori" & _

"ginal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (ph" & _

"one = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL A" & _

"ND state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS N" & _

"ULL)"

Me.SqlDeleteCommand1.Connection =Me.SqlConnection1

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlConnection1

'

Me.SqlConnection1.ConnectionString = "data source=NDAVENPORT2;initial catalog=pubs;persist security info=False;user id=" & _

"sa;workstation id=NDAVENPORT2;packet size=4096"

'

'SqlDataAdapter1

'

Me.SqlDataAdapter1.DeleteCommand =Me.SqlDeleteCommand1

Me.SqlDataAdapter1.InsertCommand =Me.SqlInsertCommand1

Me.SqlDataAdapter1.SelectCommand =Me.SqlSelectCommand1

Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "authors",New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("au_id", "au_id"),New System.Data.Common.DataColumnMapping("au_lname", "au_lname"),New System.Data.Common.DataColumnMapping("au_fname", "au_fname"),New System.Data.Common.DataColumnMapping("phone", "phone"),New System.Data.Common.DataColumnMapping("address", "address"),New System.Data.Common.DataColumnMapping("city", "city"),New System.Data.Common.DataColumnMapping("state", "state"),New System.Data.Common.DataColumnMapping("zip", "zip"),New System.Data.Common.DataColumnMapping("contract", "contract")})})

Me.SqlDataAdapter1.UpdateCommand =Me.SqlUpdateCommand1

'

'DataSet1

'

Me.DataSet1.DataSetName = "NewDataSet"

Me.DataSet1.Locale =New System.Globalization.CultureInfo("en-US")

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).EndInit()

EndSub

PrivateSub Page_Init(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Init

'CODEGEN: This method call is required by the Web Form Designer

'Do not modify it using the code editor.

InitializeComponent()

EndSub

#EndRegion

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

SqlDataAdapter1.Fill(DataSet1)

DataList1.DataSource = DataSet1

DataList1.DataBind()

EndSub

EndClass

Any corrections, thoughts, comments, ideas, or criticism is welcome. Thanks.

Try sorting it usingdesc andtop 1 inselect statement.|||

Simple...great! Thanks for redirection.

Found that I could not order descending o_id field in my query. The syntax was fine, but it would not return the rows in descending order. I simply changed it wihin SQL Server itself (design) and returned top 1 and it's very nice now. Thanks!

Displaying distinct record

Hi
I have two tables, linked together in my report. Report_Master is the master table and Report_Details is the detail table. The following is the SQL Query (I right click on Database Field in the Field Explorer and select Show SQL Query)
SELECT `Report_Master`.`Rpt_No`, `Report_Master`.`Rpt_Date`, `Report_Master`.`Rpt_Supervisor`,
`Report_Details`.`Rpt_Description`, `Report_Details`.`Rpt_Note`
FROM `Report_Details` `Report_Details`
INNER JOIN `Report_Master` `Report_Master`
ON `Report_Details`.`Rpt_No`=`Report_Master`.`Rpt_No`
I'd like to display all this like a normal report where the report will start with the name of the person writing the report, the date and followed by the description of the report. I dont find any problem when generating a report with only one record of description (from the Report_Details) because it will look just like what I wanted. When there are more than one description of a certain date in the details table, say three records, whatever in the master table will be displayed 3 times. How to correct this problem?
Thank you in advanceYou need to add a grouping to your report from one of the fields in the master table.

Linda|||I did.
I right clicked on Group Name Field and selected Report_Master.Rpt_No as the field to group but it shows me the same output. Where did i do wrong?|||Did you put the field(s) you don't want repeated in the group header line and remove them from the detail line in the report?

displaying "No Record found" Message

hi all,

i want to show a message when no rows is being returned from the data set,

i.e when there is no data to display the report should display

"No Record Found"

In the properties tab of table or matric or list ,you can find the property named "No Rows" , assign "No records found".

If you dont like the visibility the way it shows the "No records found".

1)Have a text box "No Records found"

2)Control the visibility of "No Records found" text box, either by the no of rows returned" i.e count(fields!field.value) or check for null,whcih ever is suitable to your scneario.

Thank you

|||thanks

display TODAY's records...

I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.

I tried this code but didn't work..

[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]

? Try: SELECT * FROM files_news WHERE news_date_time >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND news_date_time < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1) ORDER BY news_date_time DESC -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jassim Rahma@.discussions.microsoft.com> wrote in message news:a33dde1c-a329-4bf2-a66b-f2b842fdfd8c@.discussions.microsoft.com... I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time. I tried this code but didn't work.. [code]SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);[/code]|||use northwind
select * from orders
where convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualify
convert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||? The only problem with that method is that it's quite bad for performance. An index on "orderdate" will not be usable by the query engine to help satisfy the query. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <joeydj@.discussions.microsoft.com> wrote in message news:d496ce31-0a56-41f7-902f-bcd35c0ab29f@.discussions.microsoft.com...use northwindselect * from orderswhere convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualifyconvert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||Conversion will lead to bad execution plans or even table scans. You should always use the built-in functionality of the appropiate data types to achieve your goals and the best queryplan.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

i see.

thanks

|||

SELECT*FROM Employees

WHERECONVERT(varchar(15), hiredate, 112)=CONVERT(varchar(15),GETDATE(), 112)

Adamus

|||? Once again: Do not use that if you care about performance! Assuming that an index exists on "hiredate" in your example, SQL Server will not be able to use it. Please see my previous reply to this thread for an example of how to correctly phrase the query so that it can use an index. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:00a1169a-7acd-4c96-b6db-b130024a5e77@.discussions.microsoft.com... SELECT * FROM Employees WHERE CONVERT(varchar(15), hiredate, 112) = CONVERT(varchar(15), GETDATE(), 112) Adamus|||

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

|||? No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com... There's a performance issue converting a date into a string and then comparing a string? Can this performance difference even be measured? Adamus|||

NNTP User wrote:

?

No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

<Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com...

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place.

Good luck,

Adamus

|||

Yes. See the last few messages in this thread: http://groups.google.com/groups/search?q=%22function+to+return+today+at+midnight%22 And see http://groups.google.com/groups/search?q=strik+kass+%22small+improvement%22 Steve Kass Drew University www.stevekass.com Adamus Turner@.discussions.microsoft.com wrote:
> There's a performance issue converting a date into a string and then
> comparing a string?
>
> Can this performance difference even be measured?
>
> Adamus
>
>

|||? Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place. Good luck, Adamus No, nothing is removed. The index is simply not usable. I highly recommend that you do some background reading on indexes and how they work; "Inside SQL Server 2000" by Kalen Delaney would be a very good place to start. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--

display TODAY's records...

I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.

I tried this code but didn't work..

[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]

? Try: SELECT * FROM files_news WHERE news_date_time >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND news_date_time < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1) ORDER BY news_date_time DESC -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jassim Rahma@.discussions.microsoft.com> wrote in message news:a33dde1c-a329-4bf2-a66b-f2b842fdfd8c@.discussions.microsoft.com... I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time. I tried this code but didn't work.. [code]SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);[/code]|||use northwind
select * from orders
where convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualify
convert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||? The only problem with that method is that it's quite bad for performance. An index on "orderdate" will not be usable by the query engine to help satisfy the query. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <joeydj@.discussions.microsoft.com> wrote in message news:d496ce31-0a56-41f7-902f-bcd35c0ab29f@.discussions.microsoft.com...use northwindselect * from orderswhere convert(datetime,floor(CONVERT(FLOAT,orderdate)))= --<-- susbstitute the columns to qualifyconvert(datetime,floor(CONVERT(FLOAT,GETDATE())))|||Conversion will lead to bad execution plans or even table scans. You should always use the built-in functionality of the appropiate data types to achieve your goals and the best queryplan.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

i see.

thanks

|||

SELECT*FROM Employees

WHERECONVERT(varchar(15), hiredate, 112)=CONVERT(varchar(15),GETDATE(), 112)

Adamus

|||? Once again: Do not use that if you care about performance! Assuming that an index exists on "hiredate" in your example, SQL Server will not be able to use it. Please see my previous reply to this thread for an example of how to correctly phrase the query so that it can use an index. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:00a1169a-7acd-4c96-b6db-b130024a5e77@.discussions.microsoft.com... SELECT * FROM Employees WHERE CONVERT(varchar(15), hiredate, 112) = CONVERT(varchar(15), GETDATE(), 112) Adamus|||

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

|||? No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com... There's a performance issue converting a date into a string and then comparing a string? Can this performance difference even be measured? Adamus|||

NNTP User wrote:

?

No, the performance issue is that SQL Server doesn't have an index that helps it find the value of the column when converted to a string; it has an index on the actual date value. When you convert it to a string, that index is no longer usable -- so instead of being able to seek against the index to find the rows you want, SQL Server has to scan every row of the table, one-by-one, converting each of the hiredate values into a string, then comparing that string. Might not be too bad for a few hundred or even a few thousand rows, but imagine it having to do that for millions of rows.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

<Adamus Turner@.discussions.microsoft.com> wrote in message news:d59ca8af-b2cc-46f5-9d3f-24f113d9bb01@.discussions.microsoft.com...

There's a performance issue converting a date into a string and then comparing a string?

Can this performance difference even be measured?

Adamus

Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place.

Good luck,

Adamus

|||

Yes. See the last few messages in this thread: http://groups.google.com/groups/search?q=%22function+to+return+today+at+midnight%22 And see http://groups.google.com/groups/search?q=strik+kass+%22small+improvement%22 Steve Kass Drew University www.stevekass.com Adamus Turner@.discussions.microsoft.com wrote:
> There's a performance issue converting a date into a string and then
> comparing a string?
>
> Can this performance difference even be measured?
>
> Adamus
>
>

|||? Well then you need to contact MS to inform them and all SQL users that an index is removed from a field where a conversion takes place. Good luck, Adamus No, nothing is removed. The index is simply not usable. I highly recommend that you do some background reading on indexes and how they work; "Inside SQL Server 2000" by Kalen Delaney would be a very good place to start. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--

display record count in footer

I have the following data: name, grade
and am displaying it in a table like the following:
table header: Monthly Attendance by Student
group header: ="Grade: " & Fields!grade.Value (grade 1-12)
group detail: =Fields!Name.Value
group footer: need a count of the number of students in grade
table footer: =Count(Fields!Name.Value, "AttendanceDetail") (total number of
students in all grades)
Right now if I do the same =Count that I am doing in the table footer in the
group footer, I get the total count of records returned, not the count for
the group (grade). How do I do this?
Thank you for your help!In the group footer, just use the following expression:
=Count(Fields!Name.Value)
Alternatively, specify the scope name explicitly as the name of the table
group.
E.g. =Count(Fields!Name.Value, "TableGroupFooterName")
Both expressions should give you the desired result in the group footer.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
news:73422EA7-E49A-44A5-8EBD-EEE50842CB46@.microsoft.com...
>I have the following data: name, grade
> and am displaying it in a table like the following:
> table header: Monthly Attendance by Student
> group header: ="Grade: " & Fields!grade.Value (grade 1-12)
> group detail: =Fields!Name.Value
> group footer: need a count of the number of students in grade
> table footer: =Count(Fields!Name.Value, "AttendanceDetail") (total number
> of
> students in all grades)
> Right now if I do the same =Count that I am doing in the table footer in
> the
> group footer, I get the total count of records returned, not the count for
> the group (grade). How do I do this?
> Thank you for your help!

display record by retrieving parameter value in module

in my report, i have user name, test name, start time, end time, full marks and grade. when user click on "report this test" after finishing a certain test, i want the report displays this record which contains current user, test and result details. my sql query is:

SELECT CYMUSER.UNAME, TEST.TNAME, RESULT.STARTTIME, RESULT.ENDTIME, RESULT.TOTALMARKS, RESULT.GRADE
FROM CYMUSER INNER JOIN
TEST ON CYMUSER.UID = TEST.UID INNER JOIN
RESULT ON CYMUSER.UID = RESULT.UID AND TEST.TID = RESULT.TID
WHERE (RESULT.RID = @.RID)

the @.RID parameter is held in my moduleUser.curRID.

How can i pass the @.RID value to report and let it display the only one record that i want?I created a sqldataadapter and a dataset, set the select command as the query mentioned above, and get the dataset from CrystalReportCurResult.rpt

it seems working. However, when i open the form that contains the report viewer at run time, it asks for server and database name, user name and password for the database. In fact my database is local and no username and password at all.

can anyone help with the problem plz? being waiting for reply for past 5 hrs.|||if i get the field data from the database straight way, it wont ask for anything, but will display all the data in the requried column. If i get field data from Project Data --> ADO.NET Datasets, no matter which dataset i use, it asks for database login details (i.e. server, db, name and pw) at runtime. annoying, help plz!|||You need to use Windows Authentication mode of SQL Server

Display null values

Hello,

I'm facing a problem in my reporting.

I have a Customer table where is record various events like CustomerEventId, DateTime, StatusId, StatusTime, GroupId, ...

I also have a status table (Id, Description) and a group table (Id, Description).

I want to create a report where for a selected date range (From ... To ...) i can see (grouped by date) all status's the customer

went in. The possible status are :

Id Description

-

1 status 1

2 status 2

3 status 3

4 status 4

My query looks something like this :

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers inner join status on customers.StatusId = status.id

inner join group on customers.GroupId = group.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

My reports has 3 parameters (From date, To date, Group)

In my report i have a table with two groups : GroupByDate (grouped on DATEVAL) and GroupByStatus

now my problem : let's say i have values for statusid 1,2 and 4

then my report will only display those 3 status.

How can i display the status where there is no data for :

now it shows :

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 4 8 66

I would like it to show:

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 3 0 0

Status 4 8 66

Anybody (i hope i have provide enough details ...)

Vinnie

Hello Vinnie,

You're going to need to modify your dataset to return those records (in your example data, "Status 3") with NULL for the rest of the values. Then you can replace the NULL's with 0, either in your SQL query or in the table.

Use an outer join to get all the status records back whether or not there are matching customer records. Something like this:

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers

inner join group on customers.GroupId = group.id

right outer join status on customers.StatusId = status.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

Hope this helps.

Jarret