help out of control query

Hi,
I jsut had a big problem, a table of 15000 was updated on 10000 for a
bit changed to true and all the record show the same datetime for the
change, the only query how can do this change is this :
/**
CREATE PROCEDURE OdM1
@.InvoicesPassID [numeric]
AS
update InvoicesPassive
set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
where ID=@.InvoicesPassID
/**
How this query can do that ?
Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
Thanks for any suggestion.
Paolol
Only the affected rows should reflect the new datetime.
jens Suessmeyer.
|||Hi
Can you show us a table structure and how do you assign a parameter to the
SP?
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
> changed to true and all the record show the same datetime for the change,
> the only query how can do this change is this :
> /**
> CREATE PROCEDURE OdM1
> @.InvoicesPassID [numeric]
> AS
> update InvoicesPassive
> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
> where ID=@.InvoicesPassID
> /**
> How this query can do that ?
> Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
> Thanks for any suggestion.
> Paolol
|||Uri Dimant ha scritto:
> Hi
> Can you show us a table structure and how do you assign a parameter to the
> SP?
>
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
>
Hi NP
/** TABLE
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SitesID] [numeric](18, 0) NOT NULL ,
[CompanyID] [numeric](18, 0) NOT NULL ,
[Via] [numeric](18, 0) NOT NULL ,
[Deleted] [bit] NOT NULL ,
[Void] [bit] NOT NULL ,
[CheckSigla] [bit] NOT NULL ,
[UserIDIN] [numeric](18, 0) NOT NULL ,
[UserIDMOD] [numeric](18, 0) NOT NULL ,
[DataIN] [datetime] NOT NULL ,
[DataMod] [datetime] NOT NULL ,
[VendorID] [numeric](18, 0) NOT NULL ,
[VendorIDD] [numeric](18, 0) NOT NULL ,
[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InvoiceDate] [datetime] NOT NULL ,
[TotImponibile] [float] NOT NULL ,
[TotIva] [float] NOT NULL ,
[TotInvoice] [float] NOT NULL ,
[TotImponibileF] [float] NOT NULL ,
[TotIvaF] [float] NOT NULL ,
[TotInvoiceF] [float] NOT NULL ,
[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InvoiceToCheck] [float] NOT NULL ,
[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[VendorType] [numeric](18, 0) NOT NULL ,
[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Checked] [bit] NOT NULL ,
[NotaCredito] [bit] NOT NULL ,
[AutoFattura] [bit] NOT NULL ,
[WhoDeleted] [numeric](18, 0) NOT NULL ,
[DataDeleted] [datetime] NOT NULL ,
[PaymentTermsID] [numeric](18, 0) NOT NULL
/**
from program is
"execute ODM1 iID "
iID is the numeric for the record ID and goes by adoQuery
Thanks,
Paolol
|||Hi
Well ,looks strange.Do you have any triggers defined on the table?
create table #test
(
col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
col2 char(1)
)
insert into #test (col2) values ('a')
insert into #test (col2)values ('b')
insert into #test (col2) values ('c')
select * from #test
declare @.p numeric
set @.p=1
update #test set col2 ='g' where col1=@.p.
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant ha scritto:
> Hi NP
> /** TABLE
> [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SitesID] [numeric](18, 0) NOT NULL ,
> [CompanyID] [numeric](18, 0) NOT NULL ,
> [Via] [numeric](18, 0) NOT NULL ,
> [Deleted] [bit] NOT NULL ,
> [Void] [bit] NOT NULL ,
> [CheckSigla] [bit] NOT NULL ,
> [UserIDIN] [numeric](18, 0) NOT NULL ,
> [UserIDMOD] [numeric](18, 0) NOT NULL ,
> [DataIN] [datetime] NOT NULL ,
> [DataMod] [datetime] NOT NULL ,
> [VendorID] [numeric](18, 0) NOT NULL ,
> [VendorIDD] [numeric](18, 0) NOT NULL ,
> [InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [InvoiceDate] [datetime] NOT NULL ,
> [TotImponibile] [float] NOT NULL ,
> [TotIva] [float] NOT NULL ,
> [TotInvoice] [float] NOT NULL ,
> [TotImponibileF] [float] NOT NULL ,
> [TotIvaF] [float] NOT NULL ,
> [TotInvoiceF] [float] NOT NULL ,
> [CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [InvoiceToCheck] [float] NOT NULL ,
> [VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [VendorType] [numeric](18, 0) NOT NULL ,
> [TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Checked] [bit] NOT NULL ,
> [NotaCredito] [bit] NOT NULL ,
> [AutoFattura] [bit] NOT NULL ,
> [WhoDeleted] [numeric](18, 0) NOT NULL ,
> [DataDeleted] [datetime] NOT NULL ,
> [PaymentTermsID] [numeric](18, 0) NOT NULL
> /**
> from program is
> "execute ODM1 iID "
> iID is the numeric for the record ID and goes by adoQuery
> Thanks,
> Paolol
|||Hi Uri,
yes very strange
No no trigger at all, and this procedure is not called by any other, is
a stand alone procedure and the most strange think is the DataDeleted
that is exactly the same for all deleted record !!! up to the millisecond !!
Uri Dimant ha scritto:
> Hi
> Well ,looks strange.Do you have any triggers defined on the table?
>
> create table #test
> (
> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> col2 char(1)
> )
> insert into #test (col2) values ('a')
> insert into #test (col2)values ('b')
> insert into #test (col2) values ('c')
> select * from #test
> declare @.p numeric
> set @.p=1
> update #test set col2 ='g' where col1=@.p.
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>
>
|||My guess is that someone executed some other UPDATE or stored procedure to perform this action...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:%23b1zOXWAGHA.3064@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Uri,
> yes very strange
> No no trigger at all, and this procedure is not called by any other, is a stand alone procedure
> and the most strange think is the DataDeleted that is exactly the same for all deleted record !!!
> up to the millisecond !!
>
> Uri Dimant ha scritto:

help out of control query

Hi,
I jsut had a big problem, a table of 15000 was updated on 10000 for a
bit changed to true and all the record show the same datetime for the
change, the only query how can do this change is this :
/**
CREATE PROCEDURE OdM1
@.InvoicesPassID [numeric]
AS
update InvoicesPassive
set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
where ID=@.InvoicesPassID
/**
How this query can do that ?
Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
Thanks for any suggestion.
PaololOnly the affected rows should reflect the new datetime.
jens Suessmeyer.|||Hi
Can you show us a table structure and how do you assign a parameter to the
SP?
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
> changed to true and all the record show the same datetime for the change,
> the only query how can do this change is this :
> /**
> CREATE PROCEDURE OdM1
> @.InvoicesPassID [numeric]
> AS
> update InvoicesPassive
> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
> where ID=@.InvoicesPassID
> /**
> How this query can do that ?
> Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
> Thanks for any suggestion.
> Paolol|||Uri Dimant ha scritto:
> Hi
> Can you show us a table structure and how do you assign a parameter to the
> SP?
>
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
>>changed to true and all the record show the same datetime for the change,
>>the only query how can do this change is this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>> update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>
>
Hi NP :)
/** TABLE
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SitesID] [numeric](18, 0) NOT NULL ,
[CompanyID] [numeric](18, 0) NOT NULL ,
[Via] [numeric](18, 0) NOT NULL ,
[Deleted] [bit] NOT NULL ,
[Void] [bit] NOT NULL ,
[CheckSigla] [bit] NOT NULL ,
[UserIDIN] [numeric](18, 0) NOT NULL ,
[UserIDMOD] [numeric](18, 0) NOT NULL ,
[DataIN] [datetime] NOT NULL ,
[DataMod] [datetime] NOT NULL ,
[VendorID] [numeric](18, 0) NOT NULL ,
[VendorIDD] [numeric](18, 0) NOT NULL ,
[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InvoiceDate] [datetime] NOT NULL ,
[TotImponibile] [float] NOT NULL ,
[TotIva] [float] NOT NULL ,
[TotInvoice] [float] NOT NULL ,
[TotImponibileF] [float] NOT NULL ,
[TotIvaF] [float] NOT NULL ,
[TotInvoiceF] [float] NOT NULL ,
[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InvoiceToCheck] [float] NOT NULL ,
[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[VendorType] [numeric](18, 0) NOT NULL ,
[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Checked] [bit] NOT NULL ,
[NotaCredito] [bit] NOT NULL ,
[AutoFattura] [bit] NOT NULL ,
[WhoDeleted] [numeric](18, 0) NOT NULL ,
[DataDeleted] [datetime] NOT NULL ,
[PaymentTermsID] [numeric](18, 0) NOT NULL
/**
from program is
"execute ODM1 iID "
iID is the numeric for the record ID and goes by adoQuery
Thanks,
Paolol|||Hi
Well ,looks strange.Do you have any triggers defined on the table?
create table #test
(
col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
col2 char(1)
)
insert into #test (col2) values ('a')
insert into #test (col2)values ('b')
insert into #test (col2) values ('c')
select * from #test
declare @.p numeric
set @.p=1
update #test set col2 ='g' where col1=@.p.
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant ha scritto:
>> Hi
>> Can you show us a table structure and how do you assign a parameter to
>> the SP?
>>
>>
>> "paolol" <paolol@._nospamma_salsan.net> wrote in message
>> news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
>>changed to true and all the record show the same datetime for the change,
>>the only query how can do this change is this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>> update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>>
> Hi NP :)
> /** TABLE
> [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SitesID] [numeric](18, 0) NOT NULL ,
> [CompanyID] [numeric](18, 0) NOT NULL ,
> [Via] [numeric](18, 0) NOT NULL ,
> [Deleted] [bit] NOT NULL ,
> [Void] [bit] NOT NULL ,
> [CheckSigla] [bit] NOT NULL ,
> [UserIDIN] [numeric](18, 0) NOT NULL ,
> [UserIDMOD] [numeric](18, 0) NOT NULL ,
> [DataIN] [datetime] NOT NULL ,
> [DataMod] [datetime] NOT NULL ,
> [VendorID] [numeric](18, 0) NOT NULL ,
> [VendorIDD] [numeric](18, 0) NOT NULL ,
> [InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [InvoiceDate] [datetime] NOT NULL ,
> [TotImponibile] [float] NOT NULL ,
> [TotIva] [float] NOT NULL ,
> [TotInvoice] [float] NOT NULL ,
> [TotImponibileF] [float] NOT NULL ,
> [TotIvaF] [float] NOT NULL ,
> [TotInvoiceF] [float] NOT NULL ,
> [CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [InvoiceToCheck] [float] NOT NULL ,
> [VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [VendorType] [numeric](18, 0) NOT NULL ,
> [TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Checked] [bit] NOT NULL ,
> [NotaCredito] [bit] NOT NULL ,
> [AutoFattura] [bit] NOT NULL ,
> [WhoDeleted] [numeric](18, 0) NOT NULL ,
> [DataDeleted] [datetime] NOT NULL ,
> [PaymentTermsID] [numeric](18, 0) NOT NULL
> /**
> from program is
> "execute ODM1 iID "
> iID is the numeric for the record ID and goes by adoQuery
> Thanks,
> Paolol|||Hi Uri,
yes very strange :(
No no trigger at all, and this procedure is not called by any other, is
a stand alone procedure and the most strange think is the DataDeleted
that is exactly the same for all deleted record !!! up to the millisecond !!
Uri Dimant ha scritto:
> Hi
> Well ,looks strange.Do you have any triggers defined on the table?
>
> create table #test
> (
> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> col2 char(1)
> )
> insert into #test (col2) values ('a')
> insert into #test (col2)values ('b')
> insert into #test (col2) values ('c')
> select * from #test
> declare @.p numeric
> set @.p=1
> update #test set col2 ='g' where col1=@.p.
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>>Uri Dimant ha scritto:
>>Hi
>>Can you show us a table structure and how do you assign a parameter to
>>the SP?
>>
>>
>>"paolol" <paolol@._nospamma_salsan.net> wrote in message
>>news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
>>changed to true and all the record show the same datetime for the change,
>>the only query how can do this change is this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>>update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>>
>>Hi NP :)
>>/** TABLE
>>[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
>>[SitesID] [numeric](18, 0) NOT NULL ,
>>[CompanyID] [numeric](18, 0) NOT NULL ,
>>[Via] [numeric](18, 0) NOT NULL ,
>>[Deleted] [bit] NOT NULL ,
>>[Void] [bit] NOT NULL ,
>>[CheckSigla] [bit] NOT NULL ,
>>[UserIDIN] [numeric](18, 0) NOT NULL ,
>>[UserIDMOD] [numeric](18, 0) NOT NULL ,
>>[DataIN] [datetime] NOT NULL ,
>>[DataMod] [datetime] NOT NULL ,
>>[VendorID] [numeric](18, 0) NOT NULL ,
>>[VendorIDD] [numeric](18, 0) NOT NULL ,
>>[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
>>NULL ,
>>[InvoiceDate] [datetime] NOT NULL ,
>>[TotImponibile] [float] NOT NULL ,
>>[TotIva] [float] NOT NULL ,
>>[TotInvoice] [float] NOT NULL ,
>>[TotImponibileF] [float] NOT NULL ,
>>[TotIvaF] [float] NOT NULL ,
>>[TotInvoiceF] [float] NOT NULL ,
>>[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>>,
>>[InvoiceToCheck] [float] NOT NULL ,
>>[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>>,
>>[VendorType] [numeric](18, 0) NOT NULL ,
>>[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[Checked] [bit] NOT NULL ,
>>[NotaCredito] [bit] NOT NULL ,
>>[AutoFattura] [bit] NOT NULL ,
>>[WhoDeleted] [numeric](18, 0) NOT NULL ,
>>[DataDeleted] [datetime] NOT NULL ,
>>[PaymentTermsID] [numeric](18, 0) NOT NULL
>>/**
>>from program is
>>"execute ODM1 iID "
>>iID is the numeric for the record ID and goes by adoQuery
>>Thanks,
>>Paolol
>
>|||My guess is that someone executed some other UPDATE or stored procedure to perform this action...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:%23b1zOXWAGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Hi Uri,
> yes very strange :(
> No no trigger at all, and this procedure is not called by any other, is a stand alone procedure
> and the most strange think is the DataDeleted that is exactly the same for all deleted record !!!
> up to the millisecond !!
>
> Uri Dimant ha scritto:
>> Hi
>> Well ,looks strange.Do you have any triggers defined on the table?
>>
>> create table #test
>> (
>> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
>> col2 char(1)
>> )
>> insert into #test (col2) values ('a')
>> insert into #test (col2)values ('b')
>> insert into #test (col2) values ('c')
>> select * from #test
>> declare @.p numeric
>> set @.p=1
>> update #test set col2 ='g' where col1=@.p.
>>
>> "paolol" <paolol@._nospamma_salsan.net> wrote in message
>> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>>Uri Dimant ha scritto:
>>Hi
>>Can you show us a table structure and how do you assign a parameter to the SP?
>>
>>
>>"paolol" <paolol@._nospamma_salsan.net> wrote in message
>>news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit changed to true and
>>all the record show the same datetime for the change, the only query how can do this change is
>>this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>>update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>>
>>Hi NP :)
>>/** TABLE
>>[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
>>[SitesID] [numeric](18, 0) NOT NULL ,
>>[CompanyID] [numeric](18, 0) NOT NULL ,
>>[Via] [numeric](18, 0) NOT NULL ,
>>[Deleted] [bit] NOT NULL ,
>>[Void] [bit] NOT NULL ,
>>[CheckSigla] [bit] NOT NULL ,
>>[UserIDIN] [numeric](18, 0) NOT NULL ,
>>[UserIDMOD] [numeric](18, 0) NOT NULL ,
>>[DataIN] [datetime] NOT NULL ,
>>[DataMod] [datetime] NOT NULL ,
>>[VendorID] [numeric](18, 0) NOT NULL ,
>>[VendorIDD] [numeric](18, 0) NOT NULL ,
>>[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[InvoiceDate] [datetime] NOT NULL ,
>>[TotImponibile] [float] NOT NULL ,
>>[TotIva] [float] NOT NULL ,
>>[TotInvoice] [float] NOT NULL ,
>>[TotImponibileF] [float] NOT NULL ,
>>[TotIvaF] [float] NOT NULL ,
>>[TotInvoiceF] [float] NOT NULL ,
>>[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[InvoiceToCheck] [float] NOT NULL ,
>>[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[VendorType] [numeric](18, 0) NOT NULL ,
>>[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[Checked] [bit] NOT NULL ,
>>[NotaCredito] [bit] NOT NULL ,
>>[AutoFattura] [bit] NOT NULL ,
>>[WhoDeleted] [numeric](18, 0) NOT NULL ,
>>[DataDeleted] [datetime] NOT NULL ,
>>[PaymentTermsID] [numeric](18, 0) NOT NULL
>>/**
>>from program is
>>"execute ODM1 iID "
>>iID is the numeric for the record ID and goes by adoQuery
>>Thanks,
>>Paolol
>>

help out of control query

Hi,
I jsut had a big problem, a table of 15000 was updated on 10000 for a
bit changed to true and all the record show the same datetime for the
change, the only query how can do this change is this :
/**
CREATE PROCEDURE OdM1
@.InvoicesPassID [numeric]
AS
update InvoicesPassive
set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
where ID=@.InvoicesPassID
/**
How this query can do that ?
Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
Thanks for any suggestion.
PaololOnly the affected rows should reflect the new datetime.
jens Suessmeyer.|||Hi
Can you show us a table structure and how do you assign a parameter to the
SP?
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
> changed to true and all the record show the same datetime for the change,
> the only query how can do this change is this :
> /**
> CREATE PROCEDURE OdM1
> @.InvoicesPassID [numeric]
> AS
> update InvoicesPassive
> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
> where ID=@.InvoicesPassID
> /**
> How this query can do that ?
> Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
> Thanks for any suggestion.
> Paolol|||Uri Dimant ha scritto:
> Hi
> Can you show us a table structure and how do you assign a parameter to th
e
> SP?
>
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
>
>
Hi NP
/** TABLE
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SitesID] [numeric](18, 0) NOT NULL ,
[CompanyID] [numeric](18, 0) NOT NULL ,
[Via] [numeric](18, 0) NOT NULL ,
[Deleted] [bit] NOT NULL ,
[Void] [bit] NOT NULL ,
[CheckSigla] [bit] NOT NULL ,
[UserIDIN] [numeric](18, 0) NOT NULL ,
[UserIDMOD] [numeric](18, 0) NOT NULL ,
[DataIN] [datetime] NOT NULL ,
[DataMod] [datetime] NOT NULL ,
[VendorID] [numeric](18, 0) NOT NULL ,
[VendorIDD] [numeric](18, 0) NOT NULL ,
[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[InvoiceDate] [datetime] NOT NULL ,
[TotImponibile] [float] NOT NULL ,
[TotIva] [float] NOT NULL ,
[TotInvoice] [float] NOT NULL ,
[TotImponibileF] [float] NOT NULL ,
[TotIvaF] [float] NOT NULL ,
[TotInvoiceF] [float] NOT NULL ,
[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InvoiceToCheck] [float] NOT NULL ,
[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T
NULL ,
[VendorType] [numeric](18, 0) NOT NULL ,
[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[Checked] [bit] NOT NULL ,
[NotaCredito] [bit] NOT NULL ,
[AutoFattura] [bit] NOT NULL ,
[WhoDeleted] [numeric](18, 0) NOT NULL ,
[DataDeleted] [datetime] NOT NULL ,
[PaymentTermsID] [numeric](18, 0) NOT NULL
/**
from program is
"execute ODM1 iID "
iID is the numeric for the record ID and goes by adoQuery
Thanks,
Paolol|||Hi
Well ,looks strange.Do you have any triggers defined on the table?
create table #test
(
col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
col2 char(1)
)
insert into #test (col2) values ('a')
insert into #test (col2)values ('b')
insert into #test (col2) values ('c')
select * from #test
declare @.p numeric
set @.p=1
update #test set col2 ='g' where col1=@.p.
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant ha scritto:
> Hi NP
> /** TABLE
> [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SitesID] [numeric](18, 0) NOT NULL ,
> [CompanyID] [numeric](18, 0) NOT NULL ,
> [Via] [numeric](18, 0) NOT NULL ,
> [Deleted] [bit] NOT NULL ,
> [Void] [bit] NOT NULL ,
> [CheckSigla] [bit] NOT NULL ,
> [UserIDIN] [numeric](18, 0) NOT NULL ,
> [UserIDMOD] [numeric](18, 0) NOT NULL ,
> [DataIN] [datetime] NOT NULL ,
> [DataMod] [datetime] NOT NULL ,
> [VendorID] [numeric](18, 0) NOT NULL ,
> [VendorIDD] [numeric](18, 0) NOT NULL ,
> [InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [InvoiceDate] [datetime] NOT NULL ,
> [TotImponibile] [float] NOT NULL ,
> [TotIva] [float] NOT NULL ,
> [TotInvoice] [float] NOT NULL ,
> [TotImponibileF] [float] NOT NULL ,
> [TotIvaF] [float] NOT NULL ,
> [TotInvoiceF] [float] NOT NULL ,
> [CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
> ,
> [InvoiceToCheck] [float] NOT NULL ,
> [VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
> ,
> [VendorType] [numeric](18, 0) NOT NULL ,
> [TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [Checked] [bit] NOT NULL ,
> [NotaCredito] [bit] NOT NULL ,
> [AutoFattura] [bit] NOT NULL ,
> [WhoDeleted] [numeric](18, 0) NOT NULL ,
> [DataDeleted] [datetime] NOT NULL ,
> [PaymentTermsID] [numeric](18, 0) NOT NULL
> /**
> from program is
> "execute ODM1 iID "
> iID is the numeric for the record ID and goes by adoQuery
> Thanks,
> Paolol|||Hi Uri,
yes very strange
No no trigger at all, and this procedure is not called by any other, is
a stand alone procedure and the most strange think is the DataDeleted
that is exactly the same for all deleted record !!! up to the millisecond !!
Uri Dimant ha scritto:
> Hi
> Well ,looks strange.Do you have any triggers defined on the table?
>
> create table #test
> (
> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> col2 char(1)
> )
> insert into #test (col2) values ('a')
> insert into #test (col2)values ('b')
> insert into #test (col2) values ('c')
> select * from #test
> declare @.p numeric
> set @.p=1
> update #test set col2 ='g' where col1=@.p.
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>
>
>|||My guess is that someone executed some other UPDATE or stored procedure to p
erform this action...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:%23b1zOXWAGHA.3064@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Uri,
> yes very strange
> No no trigger at all, and this procedure is not called by any other, is a
stand alone procedure
> and the most strange think is the DataDeleted that is exactly the same for
all deleted record !!!
> up to the millisecond !!
>
> Uri Dimant ha scritto:

Help on Dates

I'm new to SQL server...
I'm trying to create a date (only) with no time.
I'm importing the data from navchar to datetime and getting the time stamp. How do I configure the table to show only the date part?
I did not see a date datatype...
ThanksThat creature does not exist. You will have to use the convert function using a style - look at sql server books online under 'Cast and Convert'.
The time value stored when no time is provided is midnight - so you would have a date followed by 00:00:00.000. You might also consider smalldatetime.

For example, using northwind's orders table:

select convert(varchar(10), orderdate,101) from orders|||If my date field is indexed (2-3 million rows), then will the request read all records before converting and selecting?|||/*
--replace "origDateTime" with our datetime value/column

_ You can use datetime column with check constraint
( "origDateTime"=convert(datetime,convert(int,"origDateTime")) )
to ensure that there is no time saved, in the future you could need
time, so you only remove this constraint. Linking to date analysis
table will be done by indexed computed column on table level
( "compOnlyDate" AS convert(int,"origDateTime") )
to get additional precomputed information about date
(year,month,day in week,season,...) faster.
_ If you NEVER use time, consider using only int
or smallint ( convert(int,getdate())-30000 ),
but be prepared on problems with user reports and applications.
*/

Help needed with DATETIME Arithmetic

Hi,

I have the following query:

SELECT dbo._Fault.id, dbo._Fault.date_created, dbo._Fault.reference,
dbo._Fault.class, dbo._Fault.owner, dbo._Fault.Contact_Name, dbo._Fault.
Product,
dbo._Fault.Problem_Description,
dbo._Fault.Action_Taken, dbo._Fault.Problem_Solution,
dbo._Fault.Duration_start_date, dbo._Fault.Duration_duration,
dbo._Fault.Duration_date_summary,
dbo._Fault.Fault_Status, dbo._Fault.Fault_Source, dbo._Fault.Part_Number,
dbo._Fault.Serial_No,
dbo._Product.id AS PRODUCTID, dbo._Product.description
FROM dbo._Fault LEFT OUTER JOIN
dbo._Product ON dbo._Fault. Product = dbo._Product.id

I need an additional field appended to the end of this (called
'DATECOMPLETED') that will take the 'Duration_start_date' and add the
'Duration_duration' to it - effectively giving me a new datetime field that
is newer than the start date.

I have tried unseccessfully to amend the code but I am afraid my knowledge
of SQL is limited. I would greatly appreciate if someone could show me how
to do this.

Thanks,

DarrenMintyman wrote:

Quote:

Originally Posted by

Hi,
>
I have the following query:
>
SELECT dbo._Fault.id, dbo._Fault.date_created, dbo._Fault.reference,
dbo._Fault.class, dbo._Fault.owner, dbo._Fault.Contact_Name, dbo._Fault.
Product,
dbo._Fault.Problem_Description,
dbo._Fault.Action_Taken, dbo._Fault.Problem_Solution,
dbo._Fault.Duration_start_date, dbo._Fault.Duration_duration,
dbo._Fault.Duration_date_summary,
dbo._Fault.Fault_Status, dbo._Fault.Fault_Source, dbo._Fault.Part_Number,
dbo._Fault.Serial_No,
dbo._Product.id AS PRODUCTID, dbo._Product.description
FROM dbo._Fault LEFT OUTER JOIN
dbo._Product ON dbo._Fault. Product = dbo._Product.id
>
I need an additional field appended to the end of this (called
'DATECOMPLETED') that will take the 'Duration_start_date' and add the
'Duration_duration' to it - effectively giving me a new datetime field that
is newer than the start date.
>
I have tried unseccessfully to amend the code but I am afraid my knowledge
of SQL is limited. I would greatly appreciate if someone could show me how
to do this.
>
Thanks,
>
Darren

If Duration_duration is a number and you want it to be the number of
days added to Duration_start_date you would add something like this to
the select list:

dateadd(dd, Duration_duration, Duration_start__date)|||Thanks for the reply. That seems to have done the trick :o)

"ZeldorBlat" <zeldorblat@.gmail.comwrote in message
news:1158695486.220267.171700@.m73g2000cwd.googlegr oups.com...

Quote:

Originally Posted by

>
Mintyman wrote:

Quote:

Originally Posted by

>Hi,
>>
>I have the following query:
>>
>SELECT dbo._Fault.id, dbo._Fault.date_created, dbo._Fault.reference,
>dbo._Fault.class, dbo._Fault.owner, dbo._Fault.Contact_Name, dbo._Fault.
>Product,
> dbo._Fault.Problem_Description,
>dbo._Fault.Action_Taken, dbo._Fault.Problem_Solution,
>dbo._Fault.Duration_start_date, dbo._Fault.Duration_duration,
> dbo._Fault.Duration_date_summary,
>dbo._Fault.Fault_Status, dbo._Fault.Fault_Source, dbo._Fault.Part_Number,
>dbo._Fault.Serial_No,
> dbo._Product.id AS PRODUCTID,
>dbo._Product.description
>FROM dbo._Fault LEFT OUTER JOIN
> dbo._Product ON dbo._Fault. Product =
>dbo._Product.id
>>
>I need an additional field appended to the end of this (called
>'DATECOMPLETED') that will take the 'Duration_start_date' and add the
>'Duration_duration' to it - effectively giving me a new datetime field
>that
>is newer than the start date.
>>
>I have tried unseccessfully to amend the code but I am afraid my
>knowledge
>of SQL is limited. I would greatly appreciate if someone could show me
>how
>to do this.
>>
>Thanks,
>>
>Darren

>
If Duration_duration is a number and you want it to be the number of
days added to Duration_start_date you would add something like this to
the select list:
>
dateadd(dd, Duration_duration, Duration_start__date)
>