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 SQL syntax ??

Dear all,
I am using SQL 2000 with vs2003.
I need to create a store procedure which get the LAST record which has been
written in the database.
What is teh way to get that record ?
thnaks for your help
regards
sergeHi
How many table does the database have?
What is at the same time users insert data in many tables?
One option is create a new columns as datetime (DEFAULT GETDATE()) for
inserting .For updating you will have to create a trigger for update to
track the changes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
> Dear all,
> I am using SQL 2000 with vs2003.
> I need to create a store procedure which get the LAST record which has
> been
> written in the database.
> What is teh way to get that record ?
> thnaks for your help
> regards
> serge|||I only need to return a single row from a database table which already
contains a DateTime field.
I have tried to use the TOP keyword as follow in a strore procedure:
SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
But it return a syntax error message when checking the syntax
Any idea ?
regards
serge
"Uri Dimant" wrote:

> Hi
> How many table does the database have?
> What is at the same time users insert data in many tables?
> One option is create a new columns as datetime (DEFAULT GETDATE()) for
> inserting .For updating you will have to create a trigger for update to
> track the changes.
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:78D429C3-3519-45FA-ADD8-C952D199D47D@.microsoft.com...
>
>|||SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>I only need to return a single row from a database table which already
> contains a DateTime field.
> I have tried to use the TOP keyword as follow in a strore procedure:
> SELECT TOP ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> But it return a syntax error message when checking the syntax
> Any idea ?
> regards
> serge
> "Uri Dimant" wrote:
>|||thnaks,
so simple sometimes :-)
"Uri Dimant" wrote:

> SELECT TOP 1 ID, LEN, START_TIME FROM REELHIST ORDER BY START_TIME DESC
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:486F2059-0EE0-4CF6-8687-3C5F707EE77F@.microsoft.com...
>
>

help on query

Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one quarter
- 1995-06-30)
I want to write a query against this table to find out the missing quarter's
year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000If you use a calendar table, you can join the two tables to find the
missing rows. The calendar table should have every quarter from every
year.
David Gugick
Quest Software
www.imceda.com
www.quest.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one
quarter- 1995-06-30)
I want to write a query against this table to find out the missing
quarter's year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000|||if you have and @.@.identity this will give you above which you are missing
quarter.
that should be in the order.Try this
SELECT p1.IDNO
FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
p.IDNO = P1.IDNO + 1 )) > 3
Regards
R.D
"David Gugick" wrote:

> If you use a calendar table, you can join the two tables to find the
> missing rows. The calendar table should have every quarter from every
> year.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I don't know if this is durable without cursor.
> I have following record set: TABLEA have one column "Date"
> All the dates are in order DESC.
> Assuming every year need to have 4 quarters,
> however in this example 1995 year only have 3 quarters ( missing one
> quarter- 1995-06-30)
> I want to write a query against this table to find out the missing
> quarter's year, in this case, it's 1995
> how can I do that?
>
> Date
> --
> 1999-12-31 00:00:00.000
> 1999-09-30 00:00:00.000
> 1999-06-30 00:00:00.000
> 1999-03-31 00:00:00.000
> 1998-12-31 00:00:00.000
> 1998-09-30 00:00:00.000
> 1998-06-30 00:00:00.000
> 1998-03-31 00:00:00.000
> 1997-12-31 00:00:00.000
> 1997-09-30 00:00:00.000
> 1997-06-30 00:00:00.000
> 1997-03-31 00:00:00.000
> 1996-12-31 00:00:00.000
> 1996-09-30 00:00:00.000
> 1996-06-30 00:00:00.000
> 1996-03-31 00:00:00.000
> 1995-12-31 00:00:00.000
> 1995-09-30 00:00:00.000
> 1995-03-31 00:00:00.000
> 1994-12-31 00:00:00.000
> 1994-09-30 00:00:00.000
> 1994-06-30 00:00:00.000
> 1994-03-31 00:00:00.000
> 1993-12-31 00:00:00.000
> 1993-09-30 00:00:00.000
> 1993-06-30 00:00:00.000
> 1993-03-31 00:00:00.000
> 1992-12-31 00:00:00.000
> 1992-09-30 00:00:00.000
> 1992-06-30 00:00:00.000
> 1992-03-31 00:00:00.000
> 1991-12-31 00:00:00.000
> 1991-09-30 00:00:00.000
> 1991-06-30 00:00:00.000
> 1991-03-31 00:00:00.000
> 1990-12-31 00:00:00.000
> 1990-09-30 00:00:00.000
> 1990-06-30 00:00:00.000
> 1990-03-31 00:00:00.000
>|||I Mean IDENTITY COLUMN. If you dont have one, you can generate on the fly.
"R.D" wrote:
> if you have and @.@.identity this will give you above which you are missing
> quarter.
> that should be in the order.Try this
> SELECT p1.IDNO
> FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
> where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
> p.IDNO = P1.IDNO + 1 )) > 3
> Regards
> R.D
> "David Gugick" wrote:
>

help on join statement

I have table A:
ID int
Name text

And Table B
ID int
Name text

Now, I want to select all records from A where there is no matching record in B based on the ID

I want to do this with a JOIN statement and not a subquery as I understood that the execution plan for JOIN statements is more efficient...

Any help?

Something like this:

select *
from TableA
left outer join TableB
on TableA.ID= TableB.ID
where TableB.ID is null

The inner query does an outer join, all records show up, some with nulls

The outer query gets the records with null

|||

SELECT A.* FROM A LEFT OUTER JOIN B on A.ID = B.ID WHERE B.ID IS NULL

I would strongly advise comparing performance with

SELECT * FROM A WHERE A.ID NOT IN (SELECT ID FROM B)

sql

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS
"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good luck

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D
,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServe
r"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" a
nd
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violat
ed
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good lucksql

help on find duplicates and delete procedure

Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.

my PERSON table is this below:

ID name fname ownerid id2

1 a b
2 c c
3 e f
4 a b 1 10
5 c c 2 11

I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname

select * from ( Select name ,fname, count(1) as cnt from PERSON group by
name,Fname ) where cnt > 1

ID name fname ownerid id2

1 a b
4 a b 1 10

2 c c
5 c c 2 11

With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..

thanks
alexhow do you know which one is the "second" record?

and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?|||how do you know which one is the "second" record?

and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?

the second record is the one with ownerid and id2 well the reason behind is that the second record is inserted from another table and the id of the first one is the one i need due to relationships with other tables|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.

yes thanks for your suggestion will try it then...|||this might work for u ...
[code]
delete from Table1 where ID in
(select ID from Table1, (Select name ,fname, count(*) as cnt from Table1 group by name,Fname having count(*) > 1) as xx
where Table1.name=xx.name and Table1.fname=xx.fname and Table1.ownerid is not null and Table1.id2 is not null)
[code]

Help on creating letter in RS

Hi,
I am trying to create report using Reporting Services 2000. My dataset returns 2 records. I want to generate two reports (one for each record) in a letter format. Can anybody tell me how do I design the report that to generate seperate letter for each record? Thanks.

Am I correct in assuming that you want a seperate page for each record?

HELP ME PLEASE! GODADDY - ASPNETDB Security DB

Hi,

I have two sql db's. One for company record info, other for users (aspnetdb) I created the users one using the login control and web administration wizard. This all worked well until I had to publish to godaddy. I had to rebuild my company db, but eventually I was able to pull info and all that is working. The problem is, how the heck do I add users? I noticed that it put users table in my db to start. So I manually went into the db on godaddy's servers, and added an application. Then when I tried to add a user manually, it kept giving me errors. Is there an easier way to add website users through godaddy? Something like the web admin tool that comes with visual studio? Man it is driving me crazy!!!

For the poor man's approach, you can upload a file from the learnvisualstudio.net tutorials (begennirs lesson 09),

Signup.aspx

<%@. Page Language="C#" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<script runat="server"
</script
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#F7F7DE" BorderColor="#CCCC99"
BorderStyle="Solid" BorderWidth="1px" ContinueDestinationPageUrl="~/Default.aspx"
Font-Names="Verdana" Font-Size="10pt" Style="z-index: 100; left: 241px; position: absolute;
top: 152px">
<WizardSteps>
<asp:CreateUserWizardStep runat="server">
</asp:CreateUserWizardStep>
<asp:CompleteWizardStep runat="server">
</asp:CompleteWizardStep>
</WizardSteps>
<SideBarStyle BackColor="#7C6F57" BorderWidth="0px" Font-Size="0.9em" VerticalAlign="Top" />
<TitleTextStyle BackColor="#6B696B" Font-Bold="True" ForeColor="#FFFFFF" />
<SideBarButtonStyle BorderWidth="0px" Font-Names="Verdana" ForeColor="#FFFFFF" />
<NavigationButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="#FFFFFF" HorizontalAlign="Center" />
<CreateUserButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<ContinueButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<StepStyle BorderWidth="0px" />
</asp:CreateUserWizard>

</div>
</form>
</body>
</html
And here's my Web.config file:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<system.web>
<!--
<trace enabled="true" requestLimit="50" localOnly="false"/>
-->
<compilation debug="true"/>
<roleManager enabled="true"/>
<customErrors mode="Off"/>
<authentication mode="Forms"/
<membership defaultProvider="MySqlMembershipProvider" >
<providers>
<clear/>
<add name="MySqlMembershipProvider"
connectionStringName="MyLocalSQLServer"
applicationName="MyAppName"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
</system.web
<connectionStrings>
<add name="MyLocalSQLServer"
connectionString="Data Source=whsql-v01.prod.mesa1.secureserver.net;Initial Catalog=DB_nnnnn;User ID=user;Password=password;" />
</connectionStrings
</configuration
|||I'd like to know if there is decent user management app for this.

Also when I work with Roles I run into errors. Since I don't have access to the ASP.NET configuration tool I'm notsure if Role Management Enabled is checked on godaddy servers or ifthis would even matter for this app.

Here's my error screen.

Server Error in '/' Application.

---

TheSSE Provider did not find the database file specified in the connectionstring. At the configured trust level (below High trust level), the SSEprovider can not automatically create the database file.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Configuration.Provider.ProviderException:The SSE Provider did not find the database file specified in theconnection string. At the configured trust level (below High trustlevel), the SSE provider can not automatically create the database file.

Source Error:

Line 11: }
Line 12:
Line 13: if (Roles.IsUserInRole("Basic User") == false)
Line 14: Server.Transfer("unauthorized.aspx");
Line 15: }

Source File: d:\Hosting\javagrinder\SecurePage.aspx Line: 13

Stack Trace:

[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.]
 System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2466581
 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87
 System.Web.Security.SqlRoleProvider.GetRolesForUser(String username) +771
 System.Web.Security.RolePrincipal.IsInRole(String role) +272
 System.Web.Security.Roles.IsUserInRole(String username, String roleName) +533
 System.Web.Security.Roles.IsUserInRole(String roleName) +16
 ASP.securepage_aspx.form1_Load(Object sender, EventArgs e) in d:\Hosting\javagrinder\SecurePage.aspx:13
 System.Web.UI.Control.OnLoad(EventArgs e) +99
 System.Web.UI.Control.LoadRecursive() +47
 System.Web.UI.Control.LoadRecursive() +131
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42|||Hi JavaGrinder, have you tried setting the trust level higher (e.g. "Full")?|||

Lori,

Can you explain how to do that? That may solve another problem I am running into...

Thanks!

|||Ah, that's probably the problem. I get the following error, and am contacting godaddy to see if I can have them unlock this and allow overrides for sites upon request.

I suspect there is something more to it since I'm having troubles adding roles to the tables from their SQL server manager. I can add users and applications from that panel, but not Roles.

Server Error in '/' Application.

---

Configuration Error

Description:Anerror occurred during the processing of a configuration file requiredto service this request. Please review the specific error details belowand modify your configuration file appropriately.

Parser Error Message:Thisconfiguration section cannot be used at this path. This happens whenthe site administrator has locked access to this section using<location allowOverride="false"> from an inherited configurationfile.

Source Error:

Line 22: </providers>
Line 23: </membership>
Line 24: <trust level="Full"/>
Line 25: </system.web>
Line 26: </location>

Source File: d:\hosting\javagrinder\web.config Line: 24

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42|||

Java,

Let me know how this goes... I am trying to run the reportviewer control from their servers and it gives me permssion errors. I have heard that the reportviewer control requires full trust. Please let me know if they allow you to use full trust.

Thanks!

|||

Oh and by the way Java, I had asked Godaddy before about the trust level and all they kept saying was that I could create folders myself. I don't think they really had a clue what I was talking about... Good luck and let me know how it goes!! I might have to end up swiching host since I need this reportviewer control to work..

Thanks!

|||They say on shared hosting the trust level is set to medium. You have to have a dedicated hosting account to increase it to full.|||Ok, so after talking to godaddy support (useless) I started searching around on google again. I don't know where I got this web.config, but it works for me. Maybe it will work for report viewer.

-Jon

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"
<location allowOverride="true"
<system.web>
<!--
<trace enabled="true" requestLimit="50" localOnly="false"/>
-->
<compilation debug="true"/>
<!-- roleManager enabled="true"/ -->
<customErrors mode="Off"/>
<authentication mode="Forms"/>
<membership
<providers
<remove name="AspNetSqlMembershipProvider" /
<add name="AspNetSqlMembershipProvider"

type="System.Web.Security.SqlMembershipProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a"

connectionStringName="MyLocalSQLServer"

enablePasswordRetrieval="false"

enablePasswordReset="true"

requiresQuestionAndAnswer="true"

applicationName="/"

requiresUniqueEmail="false"

passwordFormat="Hashed"

maxInvalidPasswordAttempts="5"

minRequiredPasswordLength="7"

minRequiredNonalphanumericCharacters="1"

passwordAttemptWindow="10"

passwordStrengthRegularExpression="" /
</providers
</membership
<profile
<providers
<remove name="AspNetSqlProfileProvider" /
<add name="AspNetSqlProfileProvider"

connectionStringName="MyLocalSQLServer"

applicationName="/"

type="System.Web.Profile.SqlProfileProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" /
</providers>

</profile
<roleManager enabled="true">

<providers
<remove name="AspNetSqlRoleProvider" /
<add name="AspNetSqlRoleProvider"

connectionStringName="MyLocalSQLServer"

applicationName="/"

type="System.Web.Security.SqlRoleProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" /
</providers
</roleManager>
<!--
<membership defaultProvider="MySqlMembershipProvider" >
<providers>
<clear/>
<add name="MySqlMembershipProvider"
connectionStringName="MyLocalSQLServer"
applicationName="MyAppName"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
-->
</system.web>
</location>
<connectionStrings>
<add name="MyLocalSQLServer"
connectionString="Data Source=whsql-v01.prod.mesa1.secureserver.net;Initial Catalog=DB_nnnnnn;User ID=userid;Password=password;" />
</connectionStrings
</configuration>|||

Didn't work for me :( I still get this error...

Required permissions cannot be acquired.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Security.Policy.PolicyException: Required permissions cannot be acquired.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[PolicyException: Required permissions cannot be acquired.] System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Boolean checkExecutionPermission) +2737861 System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Int32& securitySpecialFlags, Boolean checkExecutionPermission) +57[FileLoadException: Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417)] System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +211 System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +141 System.Reflection.Assembly.Load(String assemblyString) +25 System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +32[ConfigurationErrorsException: Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417)] System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +596 System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +3479081 System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +46 System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +177 System.Web.Compilation.WebDirectoryBatchCompiler..ctor(VirtualDirectory vdir) +267 System.Web.Compilation.BuildManager.BatchCompileWebDirectoryInternal(VirtualDirectory vdir, Boolean ignoreErrors) +36 System.Web.Compilation.BuildManager.BatchCompileWebDirectory(VirtualDirectory vdir, VirtualPath virtualDir, Boolean ignoreErrors) +429 System.Web.Compilation.BuildManager.CompileWebFile(VirtualPath virtualPath) +73 System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) +580 System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) +93 System.Web.Compilation.BuildManager.GetVirtualPathObjectFactory(VirtualPath virtualPath, HttpContext context, Boolean allowCrossApp, Boolean noAssert) +111 System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp, Boolean noAssert) +54 System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +31 System.Web.UI.PageHandlerFactory.System.Web.IHttpHandlerFactory2.GetHandler(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +40 System.Web.HttpApplication.MapHttpHandler(HttpContext context, String requestType, VirtualPath path, String pathTranslated, Boolean useAppConfig) +139 System.Web.MapHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +120 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42