Delete Duplicate Record

I have Changed my database schema and transform Data with new keys using
this Query :
SELECT Case_No, Case_Date, Ma7akem_ID, COUNT(*) AS Expr1
FROM Master_Ahkam
GROUP BY Case_No, Case_Date, Ma7akem_ID
HAVING (COUNT(*) > 1)
Due to a Design error in the old one there was a "ModifyDate" in the
primarykey. so the whole record are the same except the Modifydate.. I need
to delete these rows..
Is there anyway to do so'Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. If you had a proper relational
design in the first place, you could not have dups in the first place.
Can you kill the guy that did this? That will improve the overall
quality of your software.|||Hiiiiiiii
The one who designed this is my boss so if I Kill him I 'll Have to Find new
Job, Can you help -;)
Here is the DDL
----
--
CREATE TABLE [AH_Tasneef] (
[ID] [PKInt] NOT NULL ,
[Parent_ID] [PKInt] NOT NULL CONSTRAINT [DF__Tasneef__Parent___0519C6AF]
DEFAULT (0),
[Ma7kama_ID] [PKInt] NOT NULL ,
[Text] [varchar] (200) COLLATE Arabic_BIN NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_Tasneef] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_AH_Tasneef_AH_Ma7akem] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_TasnFakaraat] (
[Tasneef_ID] [PKInt] NOT NULL ,
[Master_ID] [PKInt] NOT NULL ,
[Fakra_No] [int] NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_TasnFakaraat] PRIMARY KEY NONCLUSTERED
(
[Tasneef_ID],
[Master_ID],
[Fakra_No]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
(
[Master_ID]
) REFERENCES [AH_Master] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_SUBMA foreign key (Master_ID,
Fakra_No)
references dbo.AH_SubMaster (Master_ID, Fakra_No)
go
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_TASNE foreign key (Tasneef_ID)
references dbo.AH_Tasneef (ID)
go
----
--
I have duplicate records in table AH_TasnFakaraat which i need to clean...
how could i do so'
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133664266.448284.115290@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. If you had a proper relational
> design in the first place, you could not have dups in the first place.
> Can you kill the guy that did this? That will improve the overall
> quality of your software.
>|||On Sun, 4 Dec 2005 11:38:16 +0200, Islamegy wrote:

>Hiiiiiiii
>The one who designed this is my boss so if I Kill him I 'll Have to Find ne
w
>Job, Can you help -;)
Hi Islamegy,
I can't help you killing your boss or finding you a new job, but I might
be able to help with the duplicates.
I'm not sure exactly which columns are duplicated and which are not, nor
how to decide which rows to retain and which to remove, so I'll post a
generic example for you to modify.
CREATE TABLE Test
(Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
CONSTRAINT TestKey PRIMARY KEY (Col1, Col2, Col3)
)
INSERT INTO Test (Col1, Col2, Col3)
SELECT 1, 2, 3
UNION ALL
SELECT 1, 2, 4
go
The primary key should have been (Col1, Col2). This means that the data
above is incorrect. The lowest value for Col3 should be retained, all
others removed. In the end, only the (1, 2, 3) row should survive.
-- Remove unwanted data
DELETE FROM Test
WHERE EXISTS
(SELECT *
FROM Test AS t2
WHERE t2.Col1 = Test.Col1
AND t2.Col2 = Test.Col2
AND t2.Col3 < Test.Col3)
-- Change key
ALTER TABLE Test
DROP CONSTRAINT TestKey
ALTER TABLE Test
ADD CONSTRAINT TestKey PRIMARY KEY (Col1, Col2)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Delete Duplicate Rec

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

Quote:

Originally Posted by amitmant

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

this is the Qry to delete duplicate records from table(Database)

===================QRY======================
delete from table1 where Field1=(select field1 from table1 group by field1 having count(field1)>1)|||This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.|||

Quote:

Originally Posted by iburyak

This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.

Can you explain the concept of single instance it is dangerous proposition.sql

Delete duplicate entries from tables in my database using Query Analyzer

Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.Does this table contains any unique key or any other key field?|||Hi,

There is seperate id for each entries but duplicate entries have the same id number.

Thanks in advance,
Uday.|||One solution could be adding identity column to this and then deleting the non relevent data.|||You can move all the duplicate ones into a separate temp table using GROUP BY HAVING COUNT(*)>1

Then you delete them using the same clause can use a SELECT DISTINCT to copy them back from the temp table.

Of course if your table is small you can just copy the lot and do a SELECT DISTINCT back!

delete duplicate email addresses

I have an email list that I need to delete duplicates from. How is this
done easily?
This query will tell me that I have duplicates:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) > 1)
This tells me how many clean addresses I have:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) < 2)
How can I delete duplicates? Sometimese there are 3 or 4 of the same
email address in the file.William,
Does the Table have a unique number to identify the Email Addresses?
If so, you could use something like this..
delete from Table
where EmailID in (select max(EmailID)
from Table
group by Email
having (Count(Email)) > 1)
Thanks
Barry|||> I have an email list that I need to delete duplicates from. How is this
> done easily?
The easiest way is to SELECT DISTINCT into another table, and add a primary
key or unique index/constraint to that column so that this doesn't happen.
http://www.aspfaq.com/2431
http://www.aspfaq.com/2509|||First, make sure that the entire record is a duplicate and not just the
email. Otherwise, deleting records containing duplicate emails may result in
losing information. For example, if you have 2 records for John Doe with
same email but different phone numbers, then which record should you delete?
If it is just a list of emails, then you can select distinct from one table
into another table. For example:
select distinct email into TableB from TableA
If you do not want to deal with inserting into a 2nd table but rather
perform regular maintenance on the existing table, then you can delete out
duplicates using the method below. This requires that the table have a
unique key column. Test in the NorthWind sample database:
begin transaction
select count(*) from [Order Details]
delete
from
[Order Details]
where
OrderID <> (select max(X.OrderID) from [Order Details] as X where
X.ProductID = [Order Details].ProductID)
select count(*) from [Order Details]
rollback transaction
"William" <da@.northernit.net> wrote in message
news:NmHTd.40252$H05.29776@.twister.nyroc.rr.com...
> I have an email list that I need to delete duplicates from. How is this
> done easily?
> This query will tell me that I have duplicates:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) > 1)
> This tells me how many clean addresses I have:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) < 2)
> How can I delete duplicates? Sometimese there are 3 or 4 of the same
> email address in the file.
>
>|||Thanks. I did have an autonumber key field. The first method works well if
I execute the query several times to get rid of folks who have their emails
2-4 times in that database.
The addresses are collected when users take a diagnostics test on our
website. Duplicate come about because users find our content really useful.
They take the test or assessment quickly the first time and then 50% of the
time take the assessment again answering the questions with more thought.
We ask the persons age and usually the women lie about their age on the
first pass. When they find out that their results are compared to women
their age they take the test again usually answering the age question 2-5
years older (go figure...). We keep all of the data on every customer over
time. Lots of duplicats...
Anyway, thanks for the help. Worked great.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23a9Or90GFHA.2804@.TK2MSFTNGP10.phx.gbl...
> The easiest way is to SELECT DISTINCT into another table, and add a
primary
> key or unique index/constraint to that column so that this doesn't happen.
> http://www.aspfaq.com/2431
> http://www.aspfaq.com/2509
>

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Delete Column Problems

I created a report with say 5 columns, then decide I only want 4 of them.
My approach was:
1) go to data area and remove from query
2) go to Layout Tab and remove from display
But when I preview it - I get error - it still seems to always be looking
for the column I deleted - what can I do?make sure there are no referneces to that column or field thru any filters
or the where statement in the data area.
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
>I created a report with say 5 columns, then decide I only want 4 of them.
> My approach was:
> 1) go to data area and remove from query
> 2) go to Layout Tab and remove from display
> But when I preview it - I get error - it still seems to always be looking
> for the column I deleted - what can I do?
>|||Go to DataSet,Go to Fields,remove the unwanted column.it should work
fine now
Regards
Raj Deep.A
Ben Watts wrote:
> make sure there are no referneces to that column or field thru any filters
> or the where statement in the data area.
> "Joe" <hortoristic@.gmail dot com> wrote in message
> news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
> >I created a report with say 5 columns, then decide I only want 4 of them.
> >
> > My approach was:
> > 1) go to data area and remove from query
> > 2) go to Layout Tab and remove from display
> >
> > But when I preview it - I get error - it still seems to always be looking
> > for the column I deleted - what can I do?
> >sql

Delete a Substring

Good morning,
I need to delete the first ten characters from a field, I don't know how to write this query to allow me to do so.update table
set COLUMN = LTRIM(NAME, SUBSTR(COLUMN, 0, 10));|||UPDATE conitemdescrip
SET descrip= LTRIM(descrip,SUBSTRING(descrip,0,10));

Msg 174, level 15 State 1
the function 'ltrim' requires 1 argument

Suggestions?|||You are using MS SQL Server 2000.

update table
set column = Right(column, Len(column) - 1)

- From Visual Basic|||No, I am using
SQL 6.5...sql

Delete

Can somebody help me with a query.
carLogin is the id of the trader.
carReference is unique for the trader
Exemple:
data in my database
099 - A
099 - B
100 - A
100 - B
100 - C
100 - D
100 - E
100 - F
100 - G
100 - H
100 - I
108 - A
108 - B
Now i want delete for 1 trader (100) all the record who are not in the list
below.
ok(0)="A"
ok(1)="B"
c must be deleted
ok(2)="D"
ok(3)="E"
ok(4)="F"
g must be deleted
h must be deleted
ok(5)="I"
With other words, all the references in the list ok() must be in the
database, all the other be deleted.
Can i do this i one query.
Thx. Grard.Hi
Can you post DDL+ sample data + expected result?
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:m_q5f.26913$ny5.1137796@.phobos.telenet-ops.be...
> Can somebody help me with a query.
> carLogin is the id of the trader.
> carReference is unique for the trader
> Exemple:
> data in my database
> 099 - A
> 099 - B
> 100 - A
> 100 - B
> 100 - C
> 100 - D
> 100 - E
> 100 - F
> 100 - G
> 100 - H
> 100 - I
> 108 - A
> 108 - B
>
> Now i want delete for 1 trader (100) all the record who are not in the
> list
> below.
> ok(0)="A"
> ok(1)="B"
> c must be deleted
> ok(2)="D"
> ok(3)="E"
> ok(4)="F"
> g must be deleted
> h must be deleted
> ok(5)="I"
> With other words, all the references in the list ok() must be in the
> database, all the other be deleted.
> Can i do this i one query.
> Thx. Grard.
>
>
>
>
>sql

Defualt Value Question?

Hi,everyone
I have a report.
I List a employees in a table,I use a parameter "DepartmentID"(0-n) to
query employees dataset1.
I have other dataset2 ,the department from a sql text.
the parameter "departmentId" from dataset2,and I need a default value
"All" to list all employees.
But the default value not work.
RedmoonIf you use available values, they should include possible default value (in
your case "All'). If default value does not match any of available values it
is rejected.
--
Lev
http://blogs.msdn.com/levs
This posting is provided "AS IS" with no warranties, and confers no rights.
"Redmoon" <heavenwing@.163.com> wrote in message
news:Xns952DBA9506D1Fheavenwing163com@.207.46.248.16...
> Hi,everyone
> I have a report.
> I List a employees in a table,I use a parameter "DepartmentID"(0-n) to
> query employees dataset1.
> I have other dataset2 ,the department from a sql text.
> the parameter "departmentId" from dataset2,and I need a default value
> "All" to list all employees.
> But the default value not work.
> Redmoon

Defining dataset query in vb.net

Hi all,
there are lots of objects about defining the report data source ,data set,
query etc.
but is there any examples of defining them and assign to a report?
i feel confuse cos i dun quite know how to use them.(i want to construct the
Query in web form first then pass to the report.)
thanks in advance!hi all,
may be i should post my questions clearly,
i know how to assign a datasource to the report using setreportdatasource(),
but|||BUT
HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
HOW TO ASSIGN A QUERY TO A DATASET?
i dun know if my concept is corroect or not, please let me know if i have
said something wrong
thanks in advance.|||Two points. First, the way you are envisioning it today can not easily be
done. People do this but it is not an out of the box experience. By that I
mean, it is not a built in capability but it is possible to use the
extensions and write your own data extension to do this.
The way it is really designed today is for you to create the reports and the
dataset from within Reporting Services. The name matches the design. It is
designed to be a service that you can invoke. You can use the product three
ways. First, it ships with its own portal. This is the quickest way to use
it and is the best way to at least get your feet wet. Then you can integrate
with your own web app by using either URL integration (your app creates the
URL string passing any parameters). You can also integrate with soap (web
services).
Version 2 (Yukon and Widbey) will come with new controls that should allow
you to do this more the way you are envisioning: create a recordset and
passing it to the control. There will be both a winform and a webform
control.
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> BUT
> HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> HOW TO ASSIGN A QUERY TO A DATASET?
> i dun know if my concept is corroect or not, please let me know if i have
> said something wrong
> thanks in advance.|||thank you so much bruce,
Does it mean that what i can do for the time being is just passing
paramenters to the query built in the rs designer, not passing a query to a
dataset?
"Bruce Loehle-Conger" wrote:
> Two points. First, the way you are envisioning it today can not easily be
> done. People do this but it is not an out of the box experience. By that I
> mean, it is not a built in capability but it is possible to use the
> extensions and write your own data extension to do this.
> The way it is really designed today is for you to create the reports and the
> dataset from within Reporting Services. The name matches the design. It is
> designed to be a service that you can invoke. You can use the product three
> ways. First, it ships with its own portal. This is the quickest way to use
> it and is the best way to at least get your feet wet. Then you can integrate
> with your own web app by using either URL integration (your app creates the
> URL string passing any parameters). You can also integrate with soap (web
> services).
> Version 2 (Yukon and Widbey) will come with new controls that should allow
> you to do this more the way you are envisioning: create a recordset and
> passing it to the control. There will be both a winform and a webform
> control.
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> > BUT
> > HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> > HOW TO ASSIGN A QUERY TO A DATASET?
> >
> > i dun know if my concept is corroect or not, please let me know if i have
> > said something wrong
> >
> > thanks in advance.
>
>|||by the way, could you please explain more about the method you suggest?
as i really have to make a dynamic query string (not just for the data
value, also the whole query)|||RS has a lot of flexibility in how the query is designed. You can have query
parameters (which map to report parameters). You can refer to global
variable (for instance user!userid), you can base the query on an expression
(expressions are very powerful so there is a whole lot you can do once you
do that).
I think before you jump all the way to where you want to end up you should
play around with it a little bit so you understand the capabilities. Create
a few simple reports, have a query parameter, try out a simple query based
on an expression.
After you understand the capabilities you can decide if it meets your needs.
As I said you can integrate with URL if you want to have your own web site
or you can also use a very full featured web services (soap). Then there are
also extentions possible (data extensions, security extensions).
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> by the way, could you please explain more about the method you suggest?
> as i really have to make a dynamic query string (not just for the data
> value, also the whole query)|||Thanks a lot Bruce,
you said by expression, do you mean by SQL command?
my situation is like this , i use the Render() function to generate reports
with the query "SELECT * FROM table WHERE value=@.parameter" in the report
project, i have tried to pass the parameter to the query by inputting it to
the render function.
thats the most i can do, but the parameters are only limited to a value, not
a clause(esp WHERE clause)...what if i wanna get the data with "WHERE value2
= @.para2 AND value3 =@.para3"'
will URL solve my problem? i have found a method , which is about editing
the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
"Bruce Loehle-Conger" wrote:
> RS has a lot of flexibility in how the query is designed. You can have query
> parameters (which map to report parameters). You can refer to global
> variable (for instance user!userid), you can base the query on an expression
> (expressions are very powerful so there is a whole lot you can do once you
> do that).
> I think before you jump all the way to where you want to end up you should
> play around with it a little bit so you understand the capabilities. Create
> a few simple reports, have a query parameter, try out a simple query based
> on an expression.
> After you understand the capabilities you can decide if it meets your needs.
> As I said you can integrate with URL if you want to have your own web site
> or you can also use a very full featured web services (soap). Then there are
> also extentions possible (data extensions, security extensions).
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> > by the way, could you please explain more about the method you suggest?
> >
> > as i really have to make a dynamic query string (not just for the data
> > value, also the whole query)
>
>|||My suggestion is to back up and first learn the capabilities of Reporting
Services before you start doing integration work. Spend a little time
experimenting. Do some sample reports. Although you can edit RDl and deploy
when you deploy it it will be accesible to all users so you would have to
come up with a unique naming scenario. Plus you have the performance hit of
deploying every time the user requests the report.
Read up on dynamic SQL. Read up and play with expressions and make sure you
understand the power of expressions. Be sure you understand the difference
between query parameter and report parameter. Read up and try a creating a
url to pass a simple parameter to a report. Then get into creating a dynamic
query.
And no, an expression is not a SQL command. It will evaluate to an sql
command but it can have iif statements, nested iif statements etc so you can
create a SQL command with a varying amount of clauses in the where command.
So far I have not heard anything you are trying to do that requires editing
the rdl file directly or writing a data extension. Custom assemblies are
also possible but again, in most cases are not necessary. I write a little
code behind but have found that normally an expression solves my needs.
Bruce L-C
MVP Reporting Services
Again, you are jumping to fast to the end result without first learning
Reporting Services.
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> Thanks a lot Bruce,
> you said by expression, do you mean by SQL command?
> my situation is like this , i use the Render() function to generate
> reports
> with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> project, i have tried to pass the parameter to the query by inputting it
> to
> the render function.
> thats the most i can do, but the parameters are only limited to a value,
> not
> a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> value2
> = @.para2 AND value3 =@.para3"'
> will URL solve my problem? i have found a method , which is about editing
> the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
>
> "Bruce Loehle-Conger" wrote:
>> RS has a lot of flexibility in how the query is designed. You can have
>> query
>> parameters (which map to report parameters). You can refer to global
>> variable (for instance user!userid), you can base the query on an
>> expression
>> (expressions are very powerful so there is a whole lot you can do once
>> you
>> do that).
>> I think before you jump all the way to where you want to end up you
>> should
>> play around with it a little bit so you understand the capabilities.
>> Create
>> a few simple reports, have a query parameter, try out a simple query
>> based
>> on an expression.
>> After you understand the capabilities you can decide if it meets your
>> needs.
>> As I said you can integrate with URL if you want to have your own web
>> site
>> or you can also use a very full featured web services (soap). Then there
>> are
>> also extentions possible (data extensions, security extensions).
>> Bruce L-C
>> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
>> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
>> > by the way, could you please explain more about the method you suggest?
>> >
>> > as i really have to make a dynamic query string (not just for the data
>> > value, also the whole query)
>>|||thank you so much bruce,
after a day's study, i start to understand now, and i think nested iif maybe
the direction that i am heading to~~~
thank you sooo much for showing me the way!
"Bruce Loehle-Conger" wrote:
> My suggestion is to back up and first learn the capabilities of Reporting
> Services before you start doing integration work. Spend a little time
> experimenting. Do some sample reports. Although you can edit RDl and deploy
> when you deploy it it will be accesible to all users so you would have to
> come up with a unique naming scenario. Plus you have the performance hit of
> deploying every time the user requests the report.
> Read up on dynamic SQL. Read up and play with expressions and make sure you
> understand the power of expressions. Be sure you understand the difference
> between query parameter and report parameter. Read up and try a creating a
> url to pass a simple parameter to a report. Then get into creating a dynamic
> query.
> And no, an expression is not a SQL command. It will evaluate to an sql
> command but it can have iif statements, nested iif statements etc so you can
> create a SQL command with a varying amount of clauses in the where command.
> So far I have not heard anything you are trying to do that requires editing
> the rdl file directly or writing a data extension. Custom assemblies are
> also possible but again, in most cases are not necessary. I write a little
> code behind but have found that normally an expression solves my needs.
> Bruce L-C
> MVP Reporting Services
> Again, you are jumping to fast to the end result without first learning
> Reporting Services.
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> > Thanks a lot Bruce,
> >
> > you said by expression, do you mean by SQL command?
> >
> > my situation is like this , i use the Render() function to generate
> > reports
> > with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> > project, i have tried to pass the parameter to the query by inputting it
> > to
> > the render function.
> >
> > thats the most i can do, but the parameters are only limited to a value,
> > not
> > a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> > value2
> > = @.para2 AND value3 =@.para3"'
> >
> > will URL solve my problem? i have found a method , which is about editing
> > the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
> >
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> >> RS has a lot of flexibility in how the query is designed. You can have
> >> query
> >> parameters (which map to report parameters). You can refer to global
> >> variable (for instance user!userid), you can base the query on an
> >> expression
> >> (expressions are very powerful so there is a whole lot you can do once
> >> you
> >> do that).
> >>
> >> I think before you jump all the way to where you want to end up you
> >> should
> >> play around with it a little bit so you understand the capabilities.
> >> Create
> >> a few simple reports, have a query parameter, try out a simple query
> >> based
> >> on an expression.
> >>
> >> After you understand the capabilities you can decide if it meets your
> >> needs.
> >> As I said you can integrate with URL if you want to have your own web
> >> site
> >> or you can also use a very full featured web services (soap). Then there
> >> are
> >> also extentions possible (data extensions, security extensions).
> >>
> >> Bruce L-C
> >>
> >> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> >> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> >> > by the way, could you please explain more about the method you suggest?
> >> >
> >> > as i really have to make a dynamic query string (not just for the data
> >> > value, also the whole query)
> >>
> >>
> >>
>
>

Defining a Query in a Table Adapter

Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like:

SELECT * FROM facility_table WHERE (approved IN (1,2,3));

What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@.approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@.approval" should be an int32.

Any suggestions?

If you have the flexibility to do so, I would recommend changing the datatype to string. Remember you'll need to delimit the individual values when you pass them in - '1','2','3'.

Good luck.

define/set parameter values in Management Studio?

If you have a query w/ a parameter in it (copied from reporting services, but now working with management studio), how do you declare the parameter and set its value in sql server management studio? I've had to unfortunately find all occurrences of the parameters and replace their values each time I want to change them when executing the mdx.

Unfortunately, I don't believe their is an easy and straightforward way to do this. About the only option I've been able to find is wrapping the MDX query in an XMLA query, which allows you to have parameters and define their values. The problem with this approach is that the result of the XMLA query is an XML response which contains a lot of metadata as well as the data (but it is not in any type of format that would allow you to easily look at just the query results).

Here's a link to a topic in BOL that shows an example of this:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/a4754d16-d9c4-49f6-9be0-392180b912e4.htm

If your query is a relatively simple one that returns a relatively simple result, this approach might work...

HTH,

Dave Fackler

define query parameters ?

when I'm defining a dataset based on stored procedure I type in the name of
my stored proc and hit the !
then define query parameters comes up. My stored proc has default values
defined for the parameter. Why not have the define query parameters fill in
those default values? It's really anoying to type in 15 parameters almost
everytime I need to re-run the query.HI,
"letuce dance" <letucedance@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C5B2E705-D5B4-47F7-8595-FDC9266B917F@.microsoft.com...
> when I'm defining a dataset based on stored procedure I type in the name
> of
> my stored proc and hit the !
> then define query parameters comes up. My stored proc has default values
> defined for the parameter. Why not have the define query parameters fill
> in
> those default values? It's really anoying to type in 15 parameters almost
> everytime I need to re-run the query.
you can set the default values in the report itself.
witch to layout, click on the upper left corner (report properties), context
menu "report parameters" and there you can set the default settings
"not-queried" and store all your default-parameter values.
hth, Tony

define number of rows returned from query

Is there a way to limit the number of rows returned from a reporting services query?

as in my query will return 500 rows but i only want the 1st 5 rows.

right after the select you would use "TOP 5". So it would look something like

select top 5 * from table_name

Define a Key - "select * from "

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

thanks for your time...On Jul 9, 10:39 am, Stagnight1 <stagnig...@.yahoo.comwrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.
>
thanks for your time...

You can assign it to Ctrl-V by putting it on the clipboard.|||Stagnight1 wrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

I haven't tried it, but
http://www.regsoft.com/keys/
others may be available
http://www.google.com/search?hl=en&...G=Google+Search
Andrew

Default Values not Selected on Report Manager

I have a report with quite a few parameters, both multivalue and single, all populated from a query. In the Report Designer I have set up default values and everything works great. When I deploy these reports the default values are not being set. Am I missing a step?It's a SQL report or an MDX report?

In the report manager when you choose the parameters and execute the report. It's rendered fine?|||

The server has behavior that it keeps the old parameter defaults that are set, even after republishing. Try deleting the report and republishing.

|||I have not seen the Report Server maintain default parameter values when a report is redeployed. After redeploying the report I have to manually reset the defaults (Properties tab --> Parameters page --> Override Default button).

If there is some property of the server that would persist the defaults between deployments, I'd love to learn about it. Thanks.

default value for SUM

I have a sql query that sums a database column. When the data in the column in '0' it prints out to my datagrid nothing, blank.

Is there a way in the SQL statment to somehow assign a default '0' value so that it prints out to my datagrid. Here is the SUM part of my sql code

SUM(nonconformance.nc_wafer_qty) as 'wafers'

ISNULL(SUM(nonconformance.nc_wafer_qty),0) as wafers

|||thanks doug.

Default Value for Parameter with Query

I am trying to use the User!UserID field as a default value for a parameter. The list for the parameter is populated using a dataset that contains a list of names and User IDs. The value field for the query is User_ID. This field matches exactly with the User!UserID field, but it won't seem to default to that value. I'm sure I'm missing something obvious here. How can I get that list to default to my default value.If you keep User_ID in a field of fixed length (like char[X], not
varchar[X]), you may have spaces appended at the end of values which are
hard to notice. If you have trailing spaces in valid values and no trailing
spaces in the default value, default value won't be selected. See if this is
the case.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"leehaak" <leehaak@.discussions.microsoft.com> wrote in message
news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> I am trying to use the User!UserID field as a default value for a
parameter. The list for the parameter is populated using a dataset that
contains a list of names and User IDs. The value field for the query is
User_ID. This field matches exactly with the User!UserID field, but it
won't seem to default to that value. I'm sure I'm missing something obvious
here. How can I get that list to default to my default value.|||Thanks, unfortunately, that doesn't appear to be the problem, my User_ID field is a varchar field. I know that I can use User!UserID as a parameter in my query, and it works exactly as intended and has no trailing spaces. The value I'm setting as the default value should match my value field, correct? Are there any other requirements I'm missing to make those synch up?
"Dmitry Vasilevsky [MSFT]" wrote:
> If you keep User_ID in a field of fixed length (like char[X], not
> varchar[X]), you may have spaces appended at the end of values which are
> hard to notice. If you have trailing spaces in valid values and no trailing
> spaces in the default value, default value won't be selected. See if this is
> the case.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > I am trying to use the User!UserID field as a default value for a
> parameter. The list for the parameter is populated using a dataset that
> contains a list of names and User IDs. The value field for the query is
> User_ID. This field matches exactly with the User!UserID field, but it
> won't seem to default to that value. I'm sure I'm missing something obvious
> here. How can I get that list to default to my default value.
>
>|||Here are a few things you can try.
1. Create a report with three text boxes. First text box should show value
of parameter. Second textbox should show value of Users!UserID. Last textbox
should show expression like (Parameters!Param.Value == Users!UserID). Run
this report and select the user you think is current, see if expression
returns true.
2. Make default value a query that would return first value from your table.
See if it is selected in this case.
Please, tell me you findings.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"leehaak" <leehaak@.discussions.microsoft.com> wrote in message
news:69A7ECF1-3BC4-4B5D-B411-81DC6347C44A@.microsoft.com...
> Thanks, unfortunately, that doesn't appear to be the problem, my User_ID
field is a varchar field. I know that I can use User!UserID as a parameter
in my query, and it works exactly as intended and has no trailing spaces.
The value I'm setting as the default value should match my value field,
correct? Are there any other requirements I'm missing to make those synch
up?
> "Dmitry Vasilevsky [MSFT]" wrote:
> > If you keep User_ID in a field of fixed length (like char[X], not
> > varchar[X]), you may have spaces appended at the end of values which are
> > hard to notice. If you have trailing spaces in valid values and no
trailing
> > spaces in the default value, default value won't be selected. See if
this is
> > the case.
> >
> > --
> > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> >
> > ---
> > "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> > news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > > I am trying to use the User!UserID field as a default value for a
> > parameter. The list for the parameter is populated using a dataset that
> > contains a list of names and User IDs. The value field for the query is
> > User_ID. This field matches exactly with the User!UserID field, but it
> > won't seem to default to that value. I'm sure I'm missing something
obvious
> > here. How can I get that list to default to my default value.
> >
> >
> >|||Thanks, I did this as a diagnostic, and discovered that the problem was case.
Apparently, the when determining whether the value matches a value in the
list, case is evaluated. I changed the case of the default value, and it
worked exactly as expected.
"Dmitry Vasilevsky [MSFT]" wrote:
> Here are a few things you can try.
> 1. Create a report with three text boxes. First text box should show value
> of parameter. Second textbox should show value of Users!UserID. Last textbox
> should show expression like (Parameters!Param.Value == Users!UserID). Run
> this report and select the user you think is current, see if expression
> returns true.
> 2. Make default value a query that would return first value from your table.
> See if it is selected in this case.
> Please, tell me you findings.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> news:69A7ECF1-3BC4-4B5D-B411-81DC6347C44A@.microsoft.com...
> > Thanks, unfortunately, that doesn't appear to be the problem, my User_ID
> field is a varchar field. I know that I can use User!UserID as a parameter
> in my query, and it works exactly as intended and has no trailing spaces.
> The value I'm setting as the default value should match my value field,
> correct? Are there any other requirements I'm missing to make those synch
> up?
> >
> > "Dmitry Vasilevsky [MSFT]" wrote:
> >
> > > If you keep User_ID in a field of fixed length (like char[X], not
> > > varchar[X]), you may have spaces appended at the end of values which are
> > > hard to notice. If you have trailing spaces in valid values and no
> trailing
> > > spaces in the default value, default value won't be selected. See if
> this is
> > > the case.
> > >
> > > --
> > > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > --
> > >
> > > ---
> > > "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> > > news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > > > I am trying to use the User!UserID field as a default value for a
> > > parameter. The list for the parameter is populated using a dataset that
> > > contains a list of names and User IDs. The value field for the query is
> > > User_ID. This field matches exactly with the User!UserID field, but it
> > > won't seem to default to that value. I'm sure I'm missing something
> obvious
> > > here. How can I get that list to default to my default value.
> > >
> > >
> > >
>
>