Duplicate Rows !

In my ad-hoc reports created by Report Builder, my data is grouped if there are more than one records corresponding to some value.Is there any way to override this behaviour?
In the report designer I can do this by using the 'Hide Duplicates' property of the textbox. But doing so will make the report unusable through Report Builder.
I would like to see all values even if they are repeating when they are exported to excel.

Any help is appreciated !

I'm assuming you have created a table with multiple groups.

Try creating a table that only has a single group, or make sure that the group with the duplicate date is the right-most group.

|||

Hi,

My report is an ad-hoc report and I have not defined any groups.
I need to avoid changing the column order in the report too. So is there any property in Report Builder tool corresponding to the 'Hide Duplicates' property that is available in the Report Designer ?

Duplicate Reference Numbers using MAX()+1

Hi

Within a stored procedure I'm getting the next value of a reference
number using (simplified):

BEGIN TRANSACTION
@.next_ref=select max(ref) from table
insert into table (ref) values (@.next_ref+1)

create related records in other tables.

COMMIT TRANSACTION

I'm getting duplicate values in a multi-user network, presumably
because the new record is not commited until the transaction is
complete and another user starts another transaction and reads the same
max value.

Can anyone suggest a way of ensuring unique values? Perhaps by locking
the table for the duration.
There is already a separate identity column that increments ok.If you have an IDENTITY column, why do you want an incrementing "ref"
as well? Sure, you can lock the table each time but then you'll block
other inserts and turn your multi user system into a single user
system. The IDENTITY feature exists precisely to solve that problem.

--
David Portas
SQL Server MVP
--|||JohnSouth (jsouth@.cix.co.uk) writes:
> Within a stored procedure I'm getting the next value of a reference
> number using (simplified):
> BEGIN TRANSACTION
> @.next_ref=select max(ref) from table
> insert into table (ref) values (@.next_ref+1)
> create related records in other tables.
> COMMIT TRANSACTION
> I'm getting duplicate values in a multi-user network, presumably
> because the new record is not commited until the transaction is
> complete and another user starts another transaction and reads the same
> max value.

Add "WITH (UPDLOCK)" after the table name in the first query.

If you have a requirement that these values should be unique, you should
also add a UNIQUE constraint on this column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> JohnSouth (jsouth@.cix.co.uk) writes:
> > Within a stored procedure I'm getting the next value of a reference
> > number using (simplified):
> > BEGIN TRANSACTION
> > @.next_ref=select max(ref) from table
> > insert into table (ref) values (@.next_ref+1)
> > create related records in other tables.
> > COMMIT TRANSACTION
> > I'm getting duplicate values in a multi-user network, presumably
> > because the new record is not commited until the transaction is
> > complete and another user starts another transaction and reads the
same
> > max value.
> Add "WITH (UPDLOCK)" after the table name in the first query.
> If you have a requirement that these values should be unique, you
should
> also add a UNIQUE constraint on this column.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks Erland
As I understand it, the UPDLOCK hint will still allow other users to
read the table but will stop another transaction from doing the same
select max()until the first transaction has done the update and is
complete.
Hopefully it won't have too much impact on performance.|||JohnSouth (jsouth@.cix.co.uk) writes:
> As I understand it, the UPDLOCK hint will still allow other users to
> read the table but will stop another transaction from doing the same
> select max()until the first transaction has done the update and is
> complete.

Correct. UPDLOCK is a shared lock, other processe can still read the
value. But if they use UPDLOCK they get stuck.

What you really do is to upgrade the transaction isolation level to
Serializable instead of the default READ COMMITTED. UPDLOCK is a
special tweak to prevent deadlocks. Regular serializable would have meant
that two processes could have read the max value, and then they
would have deadlocked on the INSERT statements. Thanks to the UPDLOCK
this does not happen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

duplicate identity

Hi there,
we are using merge replication on sql2000.We are using
partial replicas.
We have different identity value set for each of our
remote subscirbers. all tables are fine they all have the
key value as what they should have except one table.
this particular table on all our servers is using one
identity value which belongs to one of our remote
location. as a result when plants are replicating we get
conflicts on this table as the violation of primary key
value.
as this table is only keeping track of the user security
so its not causing us any problems right now.
but i am curious as why the identity value gets changed
only for this one particular table.
Does that table have the identity column set with the "not for replication"
option? If not, then the identity column will get reseeded and cause this
problem.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Dummy Where Clause Allowing Dummy Select Of Data - Utilizing Where value = 1

Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.

The function of this, I believe was to make the select work but not retrieve any actual values.

I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.

What I am thinking may not even apply to what I need to do here.

If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.

Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table

but I was hoping this would work.

Example:

Insert Into table

Select

1 as RecId,

' ' as field1,

field2

From test1

Where field2 = 'CA'

Union

Select

2 as RecId,

' ' as field1,

field2

From test1

Where field2 = 'NJ'

Union

/*Final Select */

Select

6 as RecId,

' ' as field1,

field2

From test1

Where 'some value' = 1'

Thanks much for your assistance!!!

TADEG

If you want to create a dummy select that never returns rows, just add WHERE 0=1 to the end.

Code Snippet

SELECT x,y,x FROM Table WHERE 0=1

dumb question

Hi all, yes this is a dumb question, but I cant find a formatting answer...
A zero value eg 0, or 0.0 is supressed on RS reports by default.
How can I stop this, the report must show all zeros regardless in a total
field.
If I use default as the format type then I will see 0, but as the field is a
percentage, I have to use the % attribute for the format type..
So, how can I force the showing of a zero in a total fiel ie. 0%
Thanx in advanceHow about =iif(Fields!Total.value>0,Fields!Total.value,0) in the expression
of that text box. Then right click the text box and pick properties format
and put in P (for percent or P0 or P1 according to how many places you want
after the decimal. Hope that works for you.
"Scotchy" wrote:
> Hi all, yes this is a dumb question, but I cant find a formatting answer...
> A zero value eg 0, or 0.0 is supressed on RS reports by default.
> How can I stop this, the report must show all zeros regardless in a total
> field.
> If I use default as the format type then I will see 0, but as the field is a
> percentage, I have to use the % attribute for the format type..
> So, how can I force the showing of a zero in a total fiel ie. 0%
> Thanx in advance|||Hi KimB, thanks for your suggestion, actuall tried that approach to no avail.
cheers
Paul
"KimB" wrote:
> How about =iif(Fields!Total.value>0,Fields!Total.value,0) in the expression
> of that text box. Then right click the text box and pick properties format
> and put in P (for percent or P0 or P1 according to how many places you want
> after the decimal. Hope that works for you.
>
> "Scotchy" wrote:
> > Hi all, yes this is a dumb question, but I cant find a formatting answer...
> >
> > A zero value eg 0, or 0.0 is supressed on RS reports by default.
> >
> > How can I stop this, the report must show all zeros regardless in a total
> > field.
> >
> > If I use default as the format type then I will see 0, but as the field is a
> > percentage, I have to use the % attribute for the format type..
> >
> > So, how can I force the showing of a zero in a total fiel ie. 0%
> >
> > Thanx in advance|||Have you tried =iif(Fields!Total.value>0,Fields!Total.value,"0.0%")? I
think that should work for you.

DTSProperty.GetValue

Hi,
Seems a pretty straightforward problem however, I have now spent some time on it. I have a DTSProperty object and want to get its value. I am able to get all the other attributes like Name, Type just fine. The GetValue method on the DTSProperty class takes a object and returns a object. If I want to get a value of the current property what do I pass to GetValue?
I have something like

foreach (DtsProperty property in controlFlow.Properties)
{
string propName = property.Name;
//I want to do this
if(property.Get)
property.GetValue(?)//what do I pass in here

}

Thanks,
-Suri.

object x = property.GetValue(controlFlow);|||Thanks Darren. It works just fine. However I am not sure why the GetValue method was designed to take a TaskHost object as opposed to the IDTSCustomProperty90 which has a value property on it.|||Yes I know what you mean. I read the BOL section and thought that, but looking at code I have that works, well that is what worked.|||This is an optimization. Each object type (more accurately, interface) is chached internally for property objects. Were it not so, property objects would multiply geometrically. When you get a pointer to the properties collection, you're actually getting a pointer to a template collection for the interface. When you get the value, then the template asks the object you pass in for the value.
Make sense?
K

dtsconfig file is binary?

Hello all,

I'm trying to easily change a value in 80 ssis configuration files from "localhost" to "myservername". I downloaded WinGrep to do this, but it's balking on some of the files, saying that they are binary files.

Does anyone know how to make the *.dtsconfig files NOT binary in SSIS?

Any suggestions GREATLY appreciated.

I won't go into the reason I have my servername in 80 places...

Andy

The .dtsconfig files are XML files - i.e. mostly text, but sometimes you need to be careful with the encoding (typically utf-8) to keep it valid XML. I have no experience with WinGrep, or why it thinks it is "binary". I would suggest using XML-aware tools to modify .dtsconfig.|||

Thanks for the tip Michael. Do you know if notepad is XML-aware? Because that's the only tool I used to edit them.

Also, I tried just creating a clean .dtsconfig file from the package configurations wizard and then pointing WinGrep at that file and I got the same thing, saying it was binary. I didn't modify it at all outside of the wizard.

|||The .dtsConfig file is a text file, in UTF-8 encoding. If you don't configure any localized (non-English) properties, UTF-8 is identical to ASCII encoding - in that case any text processor can work with it. Notepad can typically detect the encoding correctly even if you have non-English properties, so it should be OK in any case.

Again, I have no idea why WinGrep thinks it is binary, maybe because it is just one line.|||

Hi Michael,

You hit the nail on the head with that last sentence. I just put a carriage return in the file so it was more than 1 line and now WinGrep doesn't think it's binary anymore. Weird. Anyway to make it so that a configuration file is created on more than 1 line?

|||

Ok, so in order to make it so it's not just 1 line, in BI studio, with your config file open in then center pane and your cursor inside the file, go to Edit/Advanced/Format Document. This will format the document better as well as put it on more than 1 line. Now WinGrep has no problem.

DTS Transform

I get this error:

column 6 ('CheckDate', dbType_dbtimestamp), status 6: dat over flow
invalid character value for cast specification

Here is the sql generated :

CREATE TABLE [DMS].[dbo].[Master] (
[Date_Entered] smalldatetime NULL,
[Initials] nvarchar (10) NULL,
[VendorName] nvarchar (50) NULL,
[CheckNum] nvarchar (20) NULL,
[ExpenseType] nvarchar (10) NULL,
[CheckDate] smalldatetime NULL,
[CheckAmount] money NULL
)

IM guessing i need to change date_enteed type or do a cast

any help pls

!~<Mis there anybody who has any idea about this?|||Is this the table DTS is inserting into, or is it actually trying to create the table? If it's trying to insert, change the CheckDate to datetime instead of smalldatetime and see if that fixes it.|||smalldatetime to datetime will not make a difference if value in the source is not a date. I'd suggest to change CheckDate and possibly Dtae_Entered to varchar(25), but add 2 calculated fields that would convert a valid date to date or NULL if it's not. Put them at the end of the table and do not insert anything into them (it'll fail if you try).

CREATE TABLE [DMS].[dbo].[Master] (
[Date_Entered] varchar(25) NULL,
[Initials] nvarchar (10) NULL,
[VendorName] nvarchar (50) NULL,
[CheckNum] nvarchar (20) NULL,
[ExpenseType] nvarchar (10) NULL,
[CheckDate] varchar(25) NULL,
[CheckAmount] money NULL,
[Date_Entered_Calc] as case when isdate([Date_Entered])=1 then cast([Date_Entered] as datetime) else cast(null as datetime) end,
[CheckDate_Calc] as case when isdate([CheckDate])=1 then cast([CheckDate] as datetime) else cast(null as datetime) end
)