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 optmizing a stored proc

I have this

CREATE PROCEDURE dbo.cmsGetTaskOrdersAndFunding2
(
@.FundingDate SMALLDATETIME,
@.BillingContractID INT, -- null for all contracts
@.Filter BIT = NULL

)
AS
-- get list of taskorders with their respective fundingtotals as of
specified date
IF @.Filter IS NULL
BEGIN
SELECT TO1.TaskOrderID
FROM TaskOrder TO1
LEFT OUTER JOIN
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
END
ELSE
BEGIN
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID) AND TO1.Retired <> @.Filter
END

RETURN
GO
------
Is there a less redundant way to write this? basically @.Filter is an
optional parameter, if it isn't present, I want to return all records and if
it is present all records where Retired <> @.Filter. Any ideas? Can I wrap
the WHERE clause in an if statement? Or is there a better way?

TIA,
ChrisJust one block:

-- get list of taskorders with their respective fundingtotals as of
specified date
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)

Note this last line. If @.Filter is NULL, the entire block is ALWAYS
true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
null, "TO1.Retired <> @.Filter" is the part that matters.|||Sweet! Thanks man.

"figital" <mharen@.gmail.com> wrote in message
news:1141927222.299970.274010@.i39g2000cwa.googlegr oups.com...
> Just one block:
> -- get list of taskorders with their respective fundingtotals as of
> specified date
> SELECT TO1.TaskOrderID,
> FROM TaskOrder TO1
> WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
> @.BillingContractID)
> AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)
> Note this last line. If @.Filter is NULL, the entire block is ALWAYS
> true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
> null, "TO1.Retired <> @.Filter" is the part that matters.

Help needed on select and insert query

a bit of a begginer's request here.
I have a table called t_user in which username is the primary key and one of
the fields is a decimal called bsl.
I'm trying to write a stored procedure that selects the right record based
on the username (input from a cookie) and then inserts the bsl value based o
n
the users input into a textbox on the asp.net page
the stored proc code is
CREATE PROCEDURE addbsl
@.bsl decimal,
@.username varchar(20)
AS
SELECT username FROM t_user
WHERE username = @.username
insert into t_user (bsl) values (@.bsl)
GO
and when i run it in the query analyser it tells me:
Server: Msg 515, Level 16, State 2, Procedure addbsl, Line 8
Cannot insert the value NULL into column 'username', table
'FYProj.dbo.t_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Stored Procedure: FYProj.dbo.addbsl
Return Code = -6
I understand that it is trying to insert a username as well but cannot
because you cannot enter a null value but I don't want it to input a sername
i just want it to insert data into the record with the specified username
Is there a way to change the sproc to get it to work or am i just taking the
wrong approach?You will have to use an UPDATE statement to update an existing row. INSERT
always insert a new row.
Hello neil_pat,

> a bit of a begginer's request here.
<snip>
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B

help me with a bit of a practice issue...

Ok.

I recently started developing a web site for a client using storefront.net and ms sql server.

the db schema of storefront.net has autonumbers as the PKs for the products table (even though the products table contains an additional field for product_number.)

So here's my dilemma if you care to read:

I typically develop local, deploy remote (after testing). I have a local SQL server, and then the remote SQL server.

When I'm developing for this project, I'll insert data such as products to the products table (sometimes several times while i'm working out routines to import data to the products table.) this has the effect of creating a unique ID for each product based upon SQL auto-incrementing INTs.

This StoreFront.net (SF.NET) has another table that is a lookup table. For each part number, it has a corresponding categoryID number.

Now, if i have product_ID 1234, and I set the category ID to say 10 and get it working on my local box, every thing is fine.

Here's where the problem comes in: When I use DTS to transfer the database during remote deployment, each product is inserted into the remote DBs products table and gets a NEW product id. Same with the categories.

This has the effect of breaking the relationships. (SF.NET has no ref integrity nor relationships defined in the db.) let's say my product_id 1234 gets put into the remote copy, it'll get a new product_ID (PK). let's say it's now 5775. now my category ID will also get a new value. so my data is now not related.

I don't know how to handle this situation. The unique IDs generated on my local sql will nearly almost always be different from those generated on the remote db.

How do i handle this situatoin is my question? advice, guys?why are you using the product_id in the relation...shouldn't you be relating the product_number to the categoryId... rather ? which will stay fixed... ?|||well, not to be a smart alec, but duh. that's what should be done.

problem is the team who wrote this SF.NET app don't do that. they use the productID autonumber and categoryID autonumber.

so for example, when you use their merchant tools to set a product into a category, behind the scenes they are using the autonumber id of the product and not the REAL product number. same with category ID.|||I personally prefer the way StoreFront has implemented a surrogate key. This is somewhat of a religious topic in that proponents of each method are pretty adamant that "their" way is right :-)

Anyway, with DTS there is an option to "Enable identity insert" on the Options tab. Turning this on should cure your problem.

Terri