default date in sql2k

Having a table with two datetime columns(date_ & time_. One to store the date and the other to store the time (my client's design).

When I insert a date alone to the date_ column the time defaults to 12:00:00 AM (as expected).
But I have a problem when inserting / updating the time in the time_ column. When i insert the time from my asp application / query analyzer the date defaults to 1900-1-1(expected). When i insert the time from enterprise manager the date defaults to 1899-12-30.

Can anybody explain me why the date defaults to 1899-12-30 in enterprise manager

thanksThe following article explains this in detail:

article (http://www.databasejournal.com/features/mssql/article.php/1494281)

If you need further discussion, let me know and I will give you my 2 cents as to what is occurring.sql

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me :(
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > We currently define defaults for to selected columns in tables with
CREATE
> > TABLE command. SQL Server 2000 creates a name for each default, so it
may
> > look like this:
> >
> > DF__Xyz__Abc__59FA5E80
> >
> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
last
> > part is generated by SQL Server.
> >
> > So, my question is:
> >
> > What would be a good approach to generate table conversion
> > script when we need to change a table structure, so that the script can
> > work in another database.
> >
> > Thanks,
> > Vitaliy
> >
> >
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me :(
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you are asking. If you name the constraints in the first
> place, you know what the
>> name is and won't have any problems further down the line...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Vitalik" <address@.domain.com> wrote in message
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>> > Hello,
>> >
>> > We currently define defaults for to selected columns in tables with
> CREATE
>> > TABLE command. SQL Server 2000 creates a name for each default, so it
> may
>> > look like this:
>> >
>> > DF__Xyz__Abc__59FA5E80
>> >
>> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
> last
>> > part is generated by SQL Server.
>> >
>> > So, my question is:
>> >
>> > What would be a good approach to generate table conversion
>> > script when we need to change a table structure, so that the script can
>> > work in another database.
>> >
>> > Thanks,
>> > Vitaliy
>> >
>> >
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
Vitaliy
I'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan
|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last
>
|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>
|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first p
lace, you know what the
name is and won't have any problems further down the line...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl..
.
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
..
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
..
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
CREATE[vbcol=seagreen]
may[vbcol=seagreen]
last[vbcol=seagreen]
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:

> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the cu
rrent name of the
constraint. You can then use dynamic SQL to drop the constraint. And then ad
d it back with a known
name. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations whe
n
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> place, you know what the
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> CREATE
> may
> last
>sql

De-dupe question

Hi,
I need some help to de-duping an orders table. I have an orderitems table
containing OrderID, OrderItemID, (plus a few other columns), and we need to
clean
the data prior to migration. We have quite a few duplicates of orders, and
in moving to a template, or set based model, we want to create typical group
s
from the data.
If I can provide an OrderID as a parameter, can anyone let me know how I can
retrieve the set of all precisely matching candidates. The problem I'm
running into is that
I keep getting partial matches back, for example if OrderID 1 has OrderItems
1, 2 and 3, and OrderID 2 has OrderItems 2 and 3, I get OrderID 2 back as a
match for OrderID 1.
The query I'm using is as follows:
select * from OrderHistory where OrderItemID in (select OrderItemID from
OrderHistory where OrderID = 846863) and OrderID <> 846863.
This seems to match on full or subset rather than on complete match.
The columns of interest are (OrderID int, OrderItemID int)
Any help is greatly appreciated.
Thanks
mkCould you post DDL, sample data, and sample output?
http://www.aspfaq.com/5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mk" <mk@.discussions.microsoft.com> wrote in message
news:E85B6C9F-EC19-4632-9E79-40ABE570AED3@.microsoft.com...
> Hi,
> I need some help to de-duping an orders table. I have an orderitems table
> containing OrderID, OrderItemID, (plus a few other columns), and we need
to
> clean
> the data prior to migration. We have quite a few duplicates of orders,
and
> in moving to a template, or set based model, we want to create typical
groups
> from the data.
> If I can provide an OrderID as a parameter, can anyone let me know how I
can
> retrieve the set of all precisely matching candidates. The problem I'm
> running into is that
> I keep getting partial matches back, for example if OrderID 1 has
OrderItems
> 1, 2 and 3, and OrderID 2 has OrderItems 2 and 3, I get OrderID 2 back as
a
> match for OrderID 1.
> The query I'm using is as follows:
> select * from OrderHistory where OrderItemID in (select OrderItemID from
> OrderHistory where OrderID = 846863) and OrderID <> 846863.
> This seems to match on full or subset rather than on complete match.
> The columns of interest are (OrderID int, OrderItemID int)
> Any help is greatly appreciated.
> Thanks
> mk|||CREATE TABLE [dbo].[OrderHistory] (
[OrderID] [int] NOT NULL ,
[OrderItemID] [int] NOT NULL
) ON [defgrp]
GO
There are other metadata columns but they are not relevant to this question.
A sample of the data I'm using is:
OrderID OrderItemID
846863 191731
846863 201746
846864 191731
846864 201746
846864 201747
846865 191731
846865 201746
846866 191732
846866 201747
846867 191732
846867 201747
846868 191732
846868 201747
My lastest SQL (getting more bloated and convoluted):
declare @.tot int
select @.tot = count(*) from _IntOrder where OrderID = 846863
select distinct i.OrderID from _IntOrder i where i.OrderItemID in (select
OrderItemID from _IntOrder where OrderID = 846863) and i.OrderID <> 846863
group by i.OrderID
having count(i.OrderItemID) = count(*)
I was hoping that this query would match the sets of Orders with the same
number of orderitems as 846863, where the sets were in 864863's membership,
i.e. identical orderitems, identical numbers of orderitems. As you can see
from the results this is not the case, I get 846864 as a match even though i
t
actually has an extra OrderItem - 201747.
Anyway, any help you can provide is greatly appreciated.
Kind regards,
mk
"Adam Machanic" wrote:

> Could you post DDL, sample data, and sample output?
> http://www.aspfaq.com/5006
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mk" <mk@.discussions.microsoft.com> wrote in message
> news:E85B6C9F-EC19-4632-9E79-40ABE570AED3@.microsoft.com...
> to
> and
> groups
> can
> OrderItems
> a
>
>|||I think this should do it:
select o2.orderid
from orderhistory o1
full join orderhistory o2 on o1.orderid = 846863
and o2.orderitemid = o1.orderitemid
and o2.orderid <> o1.orderid
group by o2.orderid
having count(o1.orderitemid) = count(o2.orderitemid)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mk" <mk@.discussions.microsoft.com> wrote in message
news:5A0AFFDF-86D3-423F-A03A-A9FD40AB7448@.microsoft.com...
> CREATE TABLE [dbo].[OrderHistory] (
> [OrderID] [int] NOT NULL ,
> [OrderItemID] [int] NOT NULL
> ) ON [defgrp]
> GO
> There are other metadata columns but they are not relevant to this
question.
> A sample of the data I'm using is:
> OrderID OrderItemID
> 846863 191731
> 846863 201746
> 846864 191731
> 846864 201746
> 846864 201747
> 846865 191731
> 846865 201746
> 846866 191732
> 846866 201747
> 846867 191732
> 846867 201747
> 846868 191732
> 846868 201747
> My lastest SQL (getting more bloated and convoluted):
> declare @.tot int
> select @.tot = count(*) from _IntOrder where OrderID = 846863
> select distinct i.OrderID from _IntOrder i where i.OrderItemID in (select
> OrderItemID from _IntOrder where OrderID = 846863) and i.OrderID <> 846863
> group by i.OrderID
> having count(i.OrderItemID) = count(*)
> I was hoping that this query would match the sets of Orders with the same
> number of orderitems as 846863, where the sets were in 864863's
membership,
> i.e. identical orderitems, identical numbers of orderitems. As you can
see
> from the results this is not the case, I get 846864 as a match even though
it
> actually has an extra OrderItem - 201747.
> Anyway, any help you can provide is greatly appreciated.
> Kind regards,
> mk
> "Adam Machanic" wrote:
>
table
need
orders,
I
I'm
as
from|||Hi,
Thanks for the response. Unfortunately this approach returns 846863 and
846865 as matches for 846864. The solution below is actually working for al
l
combinations, its just damn ugly, if anyone has any suggestions to clean it
up and improve performance or efficiency I'd very much appreciate it:
declare @.Order int, @.num int
declare @.vals table(ID1 int)
select @.Order = 846866
insert @.vals (ID1) select orderitemid from orderhistory where orderid =
@.Order
select @.num = count(*) from @.vals
select o.orderid from orderhistory o full outer join @.vals v on
o.orderitemid = v.id1
where o.orderid <> @.Order
group by o.orderid having count(o.orderitemid) = @.num and count(id1) = @.nu
m
Regards,
mk
"Adam Machanic" wrote:

> I think this should do it:
>
> select o2.orderid
> from orderhistory o1
> full join orderhistory o2 on o1.orderid = 846863
> and o2.orderitemid = o1.orderitemid
> and o2.orderid <> o1.orderid
> group by o2.orderid
> having count(o1.orderitemid) = count(o2.orderitemid)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mk" <mk@.discussions.microsoft.com> wrote in message
> news:5A0AFFDF-86D3-423F-A03A-A9FD40AB7448@.microsoft.com...
> question.
> membership,
> see
> it
> table
> need
> orders,
> I
> I'm
> as
> from
>
>|||Here is a method I believe works... I had to re-read some texts on
Relational Division to get back into the right mindset. It's definitely a
rough thing to get your head around! I recommend you check out this post by
Joe Celko:
http://groups-beta.google.com/group...b1b2c
bb
To solve your problem, I plugged in the Exact Division pattern he provides:
SELECT o1.orderid
FROM orderhistory AS o1
LEFT OUTER JOIN
orderhistory AS o2
ON o1.orderitemid = o2.orderitemid
and o2.orderid = 846863
GROUP BY o1.orderid
HAVING COUNT(o1.orderid) = (SELECT COUNT(orderitemid) FROM orderhistory
where orderid=846863)
AND COUNT(o2.orderitemid) = (SELECT COUNT(orderitemid) FROM orderhistory
where orderid=846863)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mk" <mk@.discussions.microsoft.com> wrote in message
news:286E524E-8D3C-4FDF-A972-E069B76AB01E@.microsoft.com...
> Hi,
> Thanks for the response. Unfortunately this approach returns 846863 and
> 846865 as matches for 846864. The solution below is actually working for
all
> combinations, its just damn ugly, if anyone has any suggestions to clean
it
> up and improve performance or efficiency I'd very much appreciate it:
> declare @.Order int, @.num int
> declare @.vals table(ID1 int)
> select @.Order = 846866
> insert @.vals (ID1) select orderitemid from orderhistory where orderid =
> @.Order
> select @.num = count(*) from @.vals
> select o.orderid from orderhistory o full outer join @.vals v on
> o.orderitemid = v.id1
> where o.orderid <> @.Order
> group by o.orderid having count(o.orderitemid) = @.num and count(id1) =
@.num
>
> Regards,
> mk
>
> "Adam Machanic" wrote:
>
(select
846863
same
can
though
orderitems
we
typical
how
problem
back
OrderItemID
match.|||Thanks Adam, you're a star. That worked a treat (with a minor mod to preven
t
return of original OrderID), and thankfully avoids the hideous, horrendous
and shamefully embarassing use of the temporary table datatype!!!
Thanks also for the link, its certainly not easy to get good information on
these kind of subjects.
regards,
mk
"Adam Machanic" wrote:

> Here is a method I believe works... I had to re-read some texts on
> Relational Division to get back into the right mindset. It's definitely a
> rough thing to get your head around! I recommend you check out this post
by
> Joe Celko:
> http://groups-beta.google.com/group...b1b
2cbb
> To solve your problem, I plugged in the Exact Division pattern he provides
:
>
> SELECT o1.orderid
> FROM orderhistory AS o1
> LEFT OUTER JOIN
> orderhistory AS o2
> ON o1.orderitemid = o2.orderitemid
> and o2.orderid = 846863
> GROUP BY o1.orderid
> HAVING COUNT(o1.orderid) = (SELECT COUNT(orderitemid) FROM orderhistory
> where orderid=846863)
> AND COUNT(o2.orderitemid) = (SELECT COUNT(orderitemid) FROM orderhistor
y
> where orderid=846863)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mk" <mk@.discussions.microsoft.com> wrote in message
> news:286E524E-8D3C-4FDF-A972-E069B76AB01E@.microsoft.com...
> all
> it
> @.num
> (select
> 846863
> same
> can
> though
> orderitems
> we
> typical
> how
> problem
> back
> OrderItemID
> match.
>
>

DecryptByPassPhrase not decrypting varchar columns after copying a database

I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator

Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.

Also this same code works correctly and as expected by decrypting the encrypted fields in production.|||

A couple of observations first:

(1) Why are you converting the columns to varchar(max)? That should not be necessary.
(2) What is the reason you are using the "1, convert(varbinary, 23)" arguments? I don't see how those could be helpful.

From you description, it appears that the data was mangled during the transfer. What is the type of the encrypted columns?

Also, given that this appears to be a copy database problem, your question may be better directed to the SQL Server Tools General forum. If the column contains a different value after the copy, the decryption is expected to fail.

Thanks
Laurentiu

|||

Mr. Cistofor,

1. It made sense at the time.
2. I must have added that to confuse myself and others later on (job security or bad programming - you decide).
3. Data mangled in transfer, yep thats what it be.
4. Wrong msg board. Sorry, I was in a hurry and I used writting the post as a way to think it through instead of prepping more before submission. My bad dog!

Thanks for the time, consideration and consultation,

Mike512

|||

Ok, you are of course free to write code as you wish, but I wanted to point out that you are doing unnecessary operations. (1) may not be costly, but for (2) you are forcing an additional hash computation per value, which is expensive and will degrade the performance of your queries - it also doesn't serve much purpose from a security point of view.

Thanks
Laurentiu

sql

Decreasing the varchar column sizes in a table

I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much...Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.

Thanks.Before doing anything, identify all NON-character fields, sum their storage up, and subtract the result and 8000 from 10468. What you get is the total number of characters that you would have to shrink your character-based fields by.

Next, do a SELECT [field_name Width]=max(datalength(field_name))... on all character-based fields, sum the result across all those fields, and see if you get 8000 or less after addint the sum of NON-character-based fields storage sizes to it. If the result is higher, - you will have to decide if you want to truncate data in some of your character fields.

If the latter is what you get, - consider normalizing the table. For example, if only some of the records contain values for a specific field, take the record key and that field and create a different table using the key as FK to the original (I hope you have a key).|||Here's a proc that might help you analyze your char columns.

DECLARE in SQL CE

Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?

Matt

You do not have to specify a value for fields with primary key/identity constraints. See this help topic for more info:

http://msdn2.microsoft.com/en-us/library/ms174633.aspx

|||Sorry, I didn't mean primary key constraints. I meant, how do I insert into Table1 if it has a Column which is a Foreign Key referencing Table2?

Matt
|||If you are referring to IDENTITY columns, you can query "SELECT @.@.IDENTITY" (on the same open connection where you just did the INSERT), and use this value as the foeign key in the next INSERT.

Decimal problem

Hi guys,

I've got a table with one of the columns 'ConversionRate' declared as decimal. The value for this column is defined as 1.45. In my vb file, I tried to retrieve the value using a SqlParameter as shown below:

myConversionRate = Me.SqlCommand.Parameters.Add(Sql.StoredProcParameter.ConversionRate, SqlDbType.Decimal)
myConversionRate.Direction = ParameterDirection.Output

which will access the following query in my stored proc:

ALTER PROCEDURE dbo.Charge @.PConversionRateDECIMAL = 0OUTPUT ASSELECT @.PConversionRate = ConversionRate,FROM TblCharge

For some reasons, the value returned is always 1 instead of 1.45. Anyone experienced this problem before and knows how to resolve it? Thanks in advance.

The solution is provided by Microsoft support in the link below. Hope this helps.

http://support.microsoft.com/?kbid=892406

|||I see. Will try that out. Thanks again|||Hmmmm...I've installed the SP4 for SqlServer as suggested in the website but it is still not working. The decimal value is still being rounded into Integer type. Any idea what else might have caused the problem?|||Alter PROCEDURE dbo.Charge
@.PConversionRate decimal(18, 2) = 0 OUTPUT
AS
SELECT @.PConversionRate = ConversionRate
FROM TblCharge|||

Silvertype:

Hmmmm...I've installed the SP4 for SqlServer as suggested in the website but it is still not working. The decimal value is still being rounded into Integer type. Any idea what else might have caused the problem?

I did not tell you service pack will fix your problem, you need to go into your SQL Server table and make sure the data type is Decimal and set the precision and scale in your table, in your store proc like the Microsoft code below, in your ADO.NET code and maybe add strings and formatting in your UI and it will not be rounded. Hope this helps.

CREATE PROCEDURE ParameterPrecisionTest(
@.pIn DECIMAL(19,4),
@.pOut DECIMAL(19,4) OUTPUT)
AS
SET @.pOut = @.pIn

ALTER PROCEDURE dbo.Charge
@.PConversionRate DECIMAL = 0 OUTPUT
AS
SELECT @.PConversionRate = ConversionRate,
FROM TblCharge

http://blogs.msdn.com/kathykam/archive/2006/03/29/564426.aspx

|||I see. Thanks.|||

Silvertype:

I see. Thanks.

I am glad I could help.

decimal places

I have many columns of data. They are all using the money datatype. When I
enter a value into a column such as 32.00, it only shows up as 32. When
outputted to an ASP page using a query it also only displays 32. If the
data is 32.33, then the display is 32.33. How do I get it to display 32.00?
Thanks!

Darren
MCPI also want to format the numbers with a comma for thousands in some
calculated fields! Thanks

Darren
MCP

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:vUNyb.153675$1N3.77976@.twister.nyroc.rr.com.. .
> I have many columns of data. They are all using the money datatype. When
I
> enter a value into a column such as 32.00, it only shows up as 32. When
> outputted to an ASP page using a query it also only displays 32. If the
> data is 32.33, then the display is 32.33. How do I get it to display
32.00?
> Thanks!
> Darren
> MCP|||See
http://groups.google.com/groups?sel...8&output=gplain

Gert-Jan

Scrappy wrote:
> I also want to format the numbers with a comma for thousands in some
> calculated fields! Thanks
> Darren
> MCP
> "Scrappy" <celtics@.lan-specialist.com> wrote in message
> news:vUNyb.153675$1N3.77976@.twister.nyroc.rr.com.. .
> > I have many columns of data. They are all using the money datatype. When
> I
> > enter a value into a column such as 32.00, it only shows up as 32. When
> > outputted to an ASP page using a query it also only displays 32. If the
> > data is 32.33, then the display is 32.33. How do I get it to display
> 32.00?
> > Thanks!
> > Darren
> > MCP|||"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:rhOyb.154023$1N3.102544@.twister.nyroc.rr.com. ..
> I also want to format the numbers with a comma for thousands in some
> calculated fields! Thanks

You don't. Formatting is best done in the display layer, not the storage
layer.

> Darren
> MCP
> "Scrappy" <celtics@.lan-specialist.com> wrote in message
> news:vUNyb.153675$1N3.77976@.twister.nyroc.rr.com.. .
> > I have many columns of data. They are all using the money datatype.
When
> I
> > enter a value into a column such as 32.00, it only shows up as 32. When
> > outputted to an ASP page using a query it also only displays 32. If the
> > data is 32.33, then the display is 32.33. How do I get it to display
> 32.00?
> > Thanks!
> > Darren
> > MCP

Decimal not displaying correctly from Excel

I have an Excel spreadsheet with three columns of data, one of which is a "Score". The score will range between 1.0 and 0, going out to two decimal places. I am able to get this data into a global temporary table. I have a script in which a message box pops up, displaying the MIN value of the "Score" field in the temp table. The MIN value is .2. When I try to get the data from the temp table to a staging table, the Scores are all rounded to the nearest whole number. I think I've tried using every numeric data type for the staging table, and I always get the same results. In the temp table, Score is defined as such:

[Score] [decimal](18, 0) NULL

Does anybody know what I need to do to get the score to display acccurately?

Lindsay

Make Score a decimal (18,2)

The "2" refers to the number of decimal places after the decimal point.

(actually you could get away with much less than 18.)

Dylan.

|||

I'm sorry, Score is defined in the temp table as 18,5 not 18,0.

I also changed Score in the staging table to 18,5 as opposed to Float, but I'm still getting the same results.

|||I found the problem. I was changing a different column using Derived Column Transformation, and the Score field was also in there, converting it to a string. Deleted it, and now I'm back in business Thank you for forcing me to look harder|||

Sometimes I find that if I ask the question, I end up finding the solution.

Something about framing the question forces you to really think about the solution.

Please mark the thread as Answered, though!

Dylan.

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19,12) and my result gets rounded around the 5th place of scale. I've tried CASTing, changing sizes on the column(s) and I still seem to get the rounding.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error converting numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ([factorAID] [int] IDENTITY (1, 1) NOT NULL ,[factorA_amt] [decimal](38, 12) NULL ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorB] ([factorBID] [int] IDENTITY (1, 1) NOT NULL ,[factorB_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorC] ([factorCID] [int] IDENTITY (1, 1) NOT NULL ,[factorC_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorD] ([factorDID] [int] IDENTITY (1, 1) NOT NULL ,[factorD_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
FWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my calculations come out correct. I guess that the arithmetic of the table shown in BOL (and below) really needs to be thought through (although I saw a post here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Result scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19
,12) and my result gets rounded around the 5th place of scale. I've tried C
ASTing, changing sizes on the column(s) and I still seem to get the rounding
.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error convertin
g numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I
tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ( [factorAID] [int] IDENTITY (1
, 1) NOT NULL , [factorA_amt] [decimal](38, 12) NULL ON [PRIMAR
Y]
GO
CREATE TABLE [dbo].[factorB] ( [factorBID] [int] IDENTITY (1
, 1) NOT NULL , [factorB_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorC] ( [factorCID] [int] IDENTITY (1
, 1) NOT NULL , [factorC_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorD] ( [factorDID] [int] IDENTITY (1
, 1) NOT NULL , [factorD_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS D
ECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GOFWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my cal
culations come out correct. I guess that the arithmetic of the table shown
in BOL (and below) really needs to be thought through (although I saw a post
here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Resu
lt scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1
+ s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 +
p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a resu
lt precision is greater than 38, the corresponding scale is reduced to preve
nt the integral part of a result from being truncated