nested update triggers

HI all,
I have a question about update triggers. I have a update trigger that
updates a 'dupdated' column to the current date and time. I this so that I
have an accurate record of when the row was updated. due to my mistake,
users where changing the dupdated column to cover their sloppiness.
My question is if I have an update command in an update trigger, will it
recursively call itself.
Thanks
RobertRobert Bravery wrote:

> HI all,
> I have a question about update triggers. I have a update trigger that
> updates a 'dupdated' column to the current date and time. I this so that I
> have an accurate record of when the row was updated. due to my mistake,
> users where changing the dupdated column to cover their sloppiness.
> My question is if I have an update command in an update trigger, will it
> recursively call itself.
> Thanks
> Robert
Not if you have set recursive triggers off (which is also the default
state):
ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
If you allow users to update tables directly then I wouldn't expect a
dependable audit trail. Triggers aren't the whole answer - a better
security implementation is the answer.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
Thanks David,
Yes, this table was created durring my learning process, I'm still learning
but was very very green at the time
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139304916.973413.224930@.g44g2000cwa.googlegroups.com...
> Robert Bravery wrote:
>
I
> Not if you have set recursive triggers off (which is also the default
> state):
> ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
> If you allow users to update tables directly then I wouldn't expect a
> dependable audit trail. Triggers aren't the whole answer - a better
> security implementation is the answer.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>sql

negative values...

calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

How could 'Cost' come negative..?|||How could 'Cost' come negative..?when the supplier pays you to take his product

next question: how can sale be 0?

answer: when you give your product away|||Sounds like an absolute value to me.

sale - abs(cost)|||calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?

Why would it be wrong? Sounds like simple accounting

My wife say I have a lot of negative values|||I worked my way around it... the profit was calculating correct, it was the Profit% that was wrong...soz:

UPDATE SALES
SET [Profit%] = [Profit%] * -1
FROM SALES
WHERE Qty < 0

So that when the profit% is negative when qty is negative... thanks :)|||So that when the profit% is negative when qty is negative... thanks :)you're welcome :)

you sell negative quantities?|||Those are called Returns!|||Those are called Returns!

yup :) :beer:|||Our sales monkeys are good at generating negative GP, too! :D

Need Update Scripts

Hi All!
I need UPDATE scripts for my data. I know several tool's
which create INSERT Scripts ... but this do not help me.
For example i need scripts like that:
UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
.
.
.
Any assistance or suggestions would be appreciated.
Thanks in Advance
SusanneHi John
thanks for you answer.
I need a software or script which created T-SQL Update
statements for different tables/fields (Must be dynamic !
different tables and fields, n records).
Examples (only for demo):
If you choose the master.dbo.sysusers table, the result
must be like that:
UPDATE sysusers SET name = 'xxx' WHERE uid = 1
UPDATE sysusers SET name = 'yyy' WHERE uid = 2
..
.. (for all data)
..
If you choose the master.dbo.sysobjects table, the result
must be like that:
UPDATE sysobjects SET name = 'abc' WHERE id = 1
..
.. (for all data)
..
I need this scripts to send them via eMail and update a
different server/database ! I can't use standard methods
like "UPDATE ... FROM...", DTS, linked servers or
whatever ... i need "simple Update" commands.
Susanne
>--Original Message--
>Hi
>Without knowing more detail it is hard to know or advise
you on what to do.
>If there are a finite number of idxFld values you could
create a lookup
>table and use the FROM clause in the update statement to
populate the new
>values. If this does not help please post DDL and
example data.
>John
>
>"Susanne" <spam@.hotmail.com> wrote in message
>news:04e001c35a5a$85a5add0$a101280a@.phx.gbl...
>> Hi All!
>> I need UPDATE scripts for my data. I know several
tool's
>> which create INSERT Scripts ... but this do not help
me.
>> For example i need scripts like that:
>> UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
>> UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
>> UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
>> .
>> .
>> .
>> Any assistance or suggestions would be appreciated.
>> Thanks in Advance
>> Susanne
>>
>
>.
>|||Hi Susanne !
I think SQL Scripter is the right software for you.
http://www.sqlscripter.com
Regards
Michael
>--Original Message--
>Hi All!
>I need UPDATE scripts for my data. I know several tool's
>which create INSERT Scripts ... but this do not help me.
>For example i need scripts like that:
>UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
>UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
>UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
>..
>..
>..
>Any assistance or suggestions would be appreciated.
>Thanks in Advance
>Susanne
>
>.
>|||Hi
I guess you could write this yourself using dynamic SQL. As you seem to have
to do this regularly, I have the feeling that what may be required is a
redesign of the database(s) as they are probably not normalised.
John
"Susanne" <spam@.hotmail.com> wrote in message
news:043e01c35a63$ccc48840$a601280a@.phx.gbl...
> Hi John
> thanks for you answer.
> I need a software or script which created T-SQL Update
> statements for different tables/fields (Must be dynamic !
> different tables and fields, n records).
> Examples (only for demo):
> If you choose the master.dbo.sysusers table, the result
> must be like that:
> UPDATE sysusers SET name = 'xxx' WHERE uid = 1
> UPDATE sysusers SET name = 'yyy' WHERE uid = 2
> ..
> .. (for all data)
> ..
> If you choose the master.dbo.sysobjects table, the result
> must be like that:
> UPDATE sysobjects SET name = 'abc' WHERE id = 1
> ..
> .. (for all data)
> ..
>
> I need this scripts to send them via eMail and update a
> different server/database ! I can't use standard methods
> like "UPDATE ... FROM...", DTS, linked servers or
> whatever ... i need "simple Update" commands.
> Susanne
>
>
> >--Original Message--
> >Hi
> >
> >Without knowing more detail it is hard to know or advise
> you on what to do.
> >
> >If there are a finite number of idxFld values you could
> create a lookup
> >table and use the FROM clause in the update statement to
> populate the new
> >values. If this does not help please post DDL and
> example data.
> >
> >John
> >
> >
> >"Susanne" <spam@.hotmail.com> wrote in message
> >news:04e001c35a5a$85a5add0$a101280a@.phx.gbl...
> >> Hi All!
> >> I need UPDATE scripts for my data. I know several
> tool's
> >> which create INSERT Scripts ... but this do not help
> me.
> >>
> >> For example i need scripts like that:
> >> UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
> >> UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
> >> UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
> >> .
> >> .
> >> .
> >>
> >> Any assistance or suggestions would be appreciated.
> >>
> >> Thanks in Advance
> >> Susanne
> >>
> >>
> >
> >
> >.
> >

Need UPDATE query help

I have a table that now holds data about operator actions. The table is
updated each night from a .csv using DTS. The problem is that the rows do no
t
include all the columns I need. I need to caculate a column from two other
rows and add it to the first. For Example, I am trying to add "New Column":
User Encoder1 Encoder2 Action
"New Column"
John 30 26354 7
26398-26354=44
John 30 26354 2
John 30 26398 3
The Encoder1 positions will always be the same as well as the user names
between the three rows. This table is large and I am going to need to change
all of the rows in this table and all the rows that are added each night. Is
this possible? any suggestions would be appreciated
ThanksI will make some guesses about which rows you are manipulating there, but
something like this will work
UPDATE u
SET u.NewColum = A3.Encoder2 - A2.Encoder2
FROM User u
INNER JOIN User A3 ON u.User = A3.User
INNER JOIN User A2 ON u.User = A2.User
WHERE u.User = 'John' AND u.Action = 7 AND A3.Action = 3 AND A2.Action = 2
I am assumingthat for the row with Action 7 you are calculating from tows
with Action 3 and 2. If not, then modify as you se fit, but I think the
query gets you in the right direction. You just need to alias the calls to
the same table to get the various rows.
HTH
John Scragg
"A.B." wrote:

> I have a table that now holds data about operator actions. The table is
> updated each night from a .csv using DTS. The problem is that the rows do
not
> include all the columns I need. I need to caculate a column from two other
> rows and add it to the first. For Example, I am trying to add "New Column"
:
> User Encoder1 Encoder2 Action
> "New Column"
> John 30 26354 7
> 26398-26354=44
> John 30 26354 2
> John 30 26398 3
> The Encoder1 positions will always be the same as well as the user names
> between the three rows. This table is large and I am going to need to chan
ge
> all of the rows in this table and all the rows that are added each night.
Is
> this possible? any suggestions would be appreciated
> Thanks|||>> I have a table that now holds data about operator actions. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
include all the columns I need. I need to caculate a column from two
other rows and add it to the first. <<
Let's get back to the basics of an RDBMS. there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless. If you want an ordering, then you need to havs a
column that defines that ordering in a column.
CREATE TABLE Foobar
(user_name CHAR(15) NOT NULL
encoder1 INTEGER NOT NULL,
encoder2 INTEGER NOT NULL,
foobar_action INTEGER NOT NULL,
PRIMARY KEY ('? ));
Unfortunately, you did not tell how to do the calculation. Would you
like to try again with a usable spec?

need Update of Select using TOP 1

I need to update a table as follows:

Update item1, item2, item3, item4 in a table where column1="email_address" and column2 is ordered acending so the most recent entry for a specific user is updated.

column2 is a time stamp.

Pseudo code something like this:

UPDATE Table1 SET item1, item2, item3, item4 WHERE Table1 is TOP 1 ORDERED BY column2 ASC column1="email_address"

So i want to update only the most recent entry of a customer with a specific email address. I can't get the SQL command together that will do that.

I aint that bright so specific syntax always helps.

Thanks,

Bill

Are you using SQL Server 2005 ? Then you should have a look on:

Limiting Updated Data by Using TOP

If you have to use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. The following example updates the vacation hours of the 10 employees with the earliest hire dates.

UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 EmployeeID FROM HumanResources.Employee
ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.EmployeeID = th.EmployeeID;
GO

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Need Trigger to update table

I need to create a trigger that will modify an address field. This is what the trigger needs to do.
1. Abbreviate address suffiexes i.e. STREET to ST, Street to St, BOULEVARD to BLVD, Boulevard to Blvd. and so on...
2. Must be able to distinguish between HIGHWAY AND WAY or Highway and way and BROADWAY AND ROAD or Broadway and Road as a couple of examples. I don't want to end up with values like HIGHWay or BRoadWAY.

So far here's what I have.

I've declared a cursor and set it equal to this select statement -
SELECT d.Address1 COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS, i.IDNumber
FROM dirtydb d INNER JOIN
inserted i ON d.idnumber = i.idnumber

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Index = patindex('%WAY%', @.address1)
IF @.Index > 0
BEGIN
--PRINT 'Index value: ' + STR(@.Index)
IF @.address1 NOT LIKE '%HIGHWAY%' COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS AND @.address1 LIKE '%WAY%' COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
UPDATE DirtyDB
SET Address1 = replace(@.address1, 'WAY', 'WAY')
WHERE IDNumber = @.IDNumber
ELSE
UPDATE DirtyDB
SET Address1 = replace(@.address1, 'WAY', 'Way')
WHERE IDNumber = @.IDNumber
--PRINT 'Replaced ' + @.Address1 + ' with ' + replace(@.address1, 'WAY', 'WAY')
END

My problem is I am still ending up with Values like HIGHWay. I know its the LIKE key word but I havn't had any luck with fixing it.

Any insights are welcome and thanks in advance.Is your instance set up as case sensetive?|||No, that's why I'm changing the collation to case sensitive accent sensitive in my comparisons.

Need to UPDATE with PARAMETER / PARTITION / RANK. Please help.

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.

Assuming ID is the primary key, you can just do something like this:

update dbo.acmdtn
set Active = 1
where id in (
SELECT id
FROM (SELECT dbo.acmdtn.*,
row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1) )
and active <> 1

Need to update up to 3000 user DB

Hi,
We have an SQL 2005 Server with the whole enterprise data.
We are building an application that requires a per user database with its
own data (horizontal filtering).
Our first idea is to create 3000 mdf files, one db for each user. This DB is
created by copying a "model" db with the data structure, then using
SqlExpress's AttachDbFilename feature to connect to user db and fill it with
filtered data from the main server. Each user DB has the main server in its
linked servers in order to be able to query the main server from the user DB
and compare data from both sides (to find the differences). Subsequent
updates are done using the same SQL queries. MDF files are stored on a
server that has SQL Express installed to which the app connects.
This solution works well but the problem is that each user DB update (near
300 Sql requests for 50 mb results DB) requires at least 30 seconds to
execute. 3000 users means 25 hours to complete the whole process. The main
server stay at low CPU level, but the "express" server quickly increase its
CPU usage (near 100% when 4 updating threads are working simultaneously),
but we cannot buy a 64 cpu computer ;).
We are looking for a faster solution. Do you have any idea which could help
us either to optimize our solution or even another method ?
We do not want to use the Sql Server replication because it is not
applicable in our environment.
Buying other SQL Server is possible but must be justified.
Thanks,
SteveSteve
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
Actually you have not explained what are you trying to achive?
What is if the data will be stored in one database? Where do user locate?
How do they access the database?
We have many customers installed on their workstations MSDE/EXPRESS and just
pushed the data (as a publisher) from pur main server . Yep , it is a
replication which sometimes has some problems that hard to solve.
"Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We have an SQL 2005 Server with the whole enterprise data.
> We are building an application that requires a per user database with its
> own data (horizontal filtering).
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
> SqlExpress's AttachDbFilename feature to connect to user db and fill it
> with filtered data from the main server. Each user DB has the main server
> in its linked servers in order to be able to query the main server from
> the user DB and compare data from both sides (to find the differences).
> Subsequent updates are done using the same SQL queries. MDF files are
> stored on a server that has SQL Express installed to which the app
> connects.
> This solution works well but the problem is that each user DB update (near
> 300 Sql requests for 50 mb results DB) requires at least 30 seconds to
> execute. 3000 users means 25 hours to complete the whole process. The main
> server stay at low CPU level, but the "express" server quickly increase
> its CPU usage (near 100% when 4 updating threads are working
> simultaneously), but we cannot buy a 64 cpu computer ;).
> We are looking for a faster solution. Do you have any idea which could
> help us either to optimize our solution or even another method ?
> We do not want to use the Sql Server replication because it is not
> applicable in our environment.
> Buying other SQL Server is possible but must be justified.
> Thanks,
> Steve
>|||The Sql express db are used to store an image of data that each user
require.
The actual "end user" application is a mobile application in which a sql
mobile DB is synchronized against a http application that will make all the
required work to ensure the sql mobile DB has exactly the same data than the
Sql express one.
This "intermediary" sql express db is required since the MS Merge
replication between sql 2005 and mobile device can't be used because of
replication limitations.
Since the main DB is quite big, we want to compute differences for each user
within the lan, before sending it to the device (that's why we want to use
mdf files that are exact copy of the data for the user, which is easier to
synchronize since it is 1-1 with the device).
Thanks,
Steve
"Uri Dimant" <urid@.iscar.co.il> a écrit dans le message de news:
%23LSWwGoTGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Steve
>> Our first idea is to create 3000 mdf files, one db for each user. This DB
>> is created by copying a "model" db with the data structure, then using
> Actually you have not explained what are you trying to achive?
> What is if the data will be stored in one database? Where do user locate?
> How do they access the database?
> We have many customers installed on their workstations MSDE/EXPRESS and
> just pushed the data (as a publisher) from pur main server . Yep , it is a
> replication which sometimes has some problems that hard to solve.
>
>
>
> "Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
> news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> We have an SQL 2005 Server with the whole enterprise data.
>> We are building an application that requires a per user database with its
>> own data (horizontal filtering).
>> Our first idea is to create 3000 mdf files, one db for each user. This DB
>> is created by copying a "model" db with the data structure, then using
>> SqlExpress's AttachDbFilename feature to connect to user db and fill it
>> with filtered data from the main server. Each user DB has the main server
>> in its linked servers in order to be able to query the main server from
>> the user DB and compare data from both sides (to find the differences).
>> Subsequent updates are done using the same SQL queries. MDF files are
>> stored on a server that has SQL Express installed to which the app
>> connects.
>> This solution works well but the problem is that each user DB update
>> (near 300 Sql requests for 50 mb results DB) requires at least 30 seconds
>> to execute. 3000 users means 25 hours to complete the whole process. The
>> main server stay at low CPU level, but the "express" server quickly
>> increase its CPU usage (near 100% when 4 updating threads are working
>> simultaneously), but we cannot buy a 64 cpu computer ;).
>> We are looking for a faster solution. Do you have any idea which could
>> help us either to optimize our solution or even another method ?
>> We do not want to use the Sql Server replication because it is not
>> applicable in our environment.
>> Buying other SQL Server is possible but must be justified.
>> Thanks,
>> Steve
>

Need to update up to 3000 user DB

Hi,
We have an SQL 2005 Server with the whole enterprise data.
We are building an application that requires a per user database with its
own data (horizontal filtering).
Our first idea is to create 3000 mdf files, one db for each user. This DB is
created by copying a "model" db with the data structure, then using
SqlExpress's AttachDbFilename feature to connect to user db and fill it with
filtered data from the main server. Each user DB has the main server in its
linked servers in order to be able to query the main server from the user DB
and compare data from both sides (to find the differences). Subsequent
updates are done using the same SQL queries. MDF files are stored on a
server that has SQL Express installed to which the app connects.
This solution works well but the problem is that each user DB update (near
300 Sql requests for 50 mb results DB) requires at least 30 seconds to
execute. 3000 users means 25 hours to complete the whole process. The main
server stay at low CPU level, but the "express" server quickly increase its
CPU usage (near 100% when 4 updating threads are working simultaneously),
but we cannot buy a 64 cpu computer ;).
We are looking for a faster solution. Do you have any idea which could help
us either to optimize our solution or even another method ?
We do not want to use the Sql Server replication because it is not
applicable in our environment.
Buying other SQL Server is possible but must be justified.
Thanks,
SteveSteve
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
Actually you have not explained what are you trying to achive?
What is if the data will be stored in one database? Where do user locate?
How do they access the database?
We have many customers installed on their workstations MSDE/EXPRESS and just
pushed the data (as a publisher) from pur main server . Yep , it is a
replication which sometimes has some problems that hard to solve.
"Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We have an SQL 2005 Server with the whole enterprise data.
> We are building an application that requires a per user database with its
> own data (horizontal filtering).
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
> SqlExpress's AttachDbFilename feature to connect to user db and fill it
> with filtered data from the main server. Each user DB has the main server
> in its linked servers in order to be able to query the main server from
> the user DB and compare data from both sides (to find the differences).
> Subsequent updates are done using the same SQL queries. MDF files are
> stored on a server that has SQL Express installed to which the app
> connects.
> This solution works well but the problem is that each user DB update (near
> 300 Sql requests for 50 mb results DB) requires at least 30 seconds to
> execute. 3000 users means 25 hours to complete the whole process. The main
> server stay at low CPU level, but the "express" server quickly increase
> its CPU usage (near 100% when 4 updating threads are working
> simultaneously), but we cannot buy a 64 cpu computer ;).
> We are looking for a faster solution. Do you have any idea which could
> help us either to optimize our solution or even another method ?
> We do not want to use the Sql Server replication because it is not
> applicable in our environment.
> Buying other SQL Server is possible but must be justified.
> Thanks,
> Steve
>|||The Sql express db are used to store an image of data that each user
require.
The actual "end user" application is a mobile application in which a sql
mobile DB is synchronized against a http application that will make all the
required work to ensure the sql mobile DB has exactly the same data than the
Sql express one.
This "intermediary" sql express db is required since the MS Merge
replication between sql 2005 and mobile device can't be used because of
replication limitations.
Since the main DB is quite big, we want to compute differences for each user
within the lan, before sending it to the device (that's why we want to use
mdf files that are exact copy of the data for the user, which is easier to
synchronize since it is 1-1 with the device).
Thanks,
Steve
"Uri Dimant" <urid@.iscar.co.il> a crit dans le message de news:
%23LSWwGoTGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Steve
> Actually you have not explained what are you trying to achive?
> What is if the data will be stored in one database? Where do user locate?
> How do they access the database?
> We have many customers installed on their workstations MSDE/EXPRESS and
> just pushed the data (as a publisher) from pur main server . Yep , it is a
> replication which sometimes has some problems that hard to solve.
>
>
>
> "Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
> news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
>

Need to update up to 3000 user DB

Hi,
We have an SQL 2005 Server with the whole enterprise data.
We are building an application that requires a per user database with its
own data (horizontal filtering).
Our first idea is to create 3000 mdf files, one db for each user. This DB is
created by copying a "model" db with the data structure, then using
SqlExpress's AttachDbFilename feature to connect to user db and fill it with
filtered data from the main server. Each user DB has the main server in its
linked servers in order to be able to query the main server from the user DB
and compare data from both sides (to find the differences). Subsequent
updates are done using the same SQL queries. MDF files are stored on a
server that has SQL Express installed to which the app connects.
This solution works well but the problem is that each user DB update (near
300 Sql requests for 50 mb results DB) requires at least 30 seconds to
execute. 3000 users means 25 hours to complete the whole process. The main
server stay at low CPU level, but the "express" server quickly increase its
CPU usage (near 100% when 4 updating threads are working simultaneously),
but we cannot buy a 64 cpu computer ;).
We are looking for a faster solution. Do you have any idea which could help
us either to optimize our solution or even another method ?
We do not want to use the Sql Server replication because it is not
applicable in our environment.
Buying other SQL Server is possible but must be justified.
Thanks,
Steve
Steve
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
Actually you have not explained what are you trying to achive?
What is if the data will be stored in one database? Where do user locate?
How do they access the database?
We have many customers installed on their workstations MSDE/EXPRESS and just
pushed the data (as a publisher) from pur main server . Yep , it is a
replication which sometimes has some problems that hard to solve.
"Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We have an SQL 2005 Server with the whole enterprise data.
> We are building an application that requires a per user database with its
> own data (horizontal filtering).
> Our first idea is to create 3000 mdf files, one db for each user. This DB
> is created by copying a "model" db with the data structure, then using
> SqlExpress's AttachDbFilename feature to connect to user db and fill it
> with filtered data from the main server. Each user DB has the main server
> in its linked servers in order to be able to query the main server from
> the user DB and compare data from both sides (to find the differences).
> Subsequent updates are done using the same SQL queries. MDF files are
> stored on a server that has SQL Express installed to which the app
> connects.
> This solution works well but the problem is that each user DB update (near
> 300 Sql requests for 50 mb results DB) requires at least 30 seconds to
> execute. 3000 users means 25 hours to complete the whole process. The main
> server stay at low CPU level, but the "express" server quickly increase
> its CPU usage (near 100% when 4 updating threads are working
> simultaneously), but we cannot buy a 64 cpu computer ;).
> We are looking for a faster solution. Do you have any idea which could
> help us either to optimize our solution or even another method ?
> We do not want to use the Sql Server replication because it is not
> applicable in our environment.
> Buying other SQL Server is possible but must be justified.
> Thanks,
> Steve
>
|||The Sql express db are used to store an image of data that each user
require.
The actual "end user" application is a mobile application in which a sql
mobile DB is synchronized against a http application that will make all the
required work to ensure the sql mobile DB has exactly the same data than the
Sql express one.
This "intermediary" sql express db is required since the MS Merge
replication between sql 2005 and mobile device can't be used because of
replication limitations.
Since the main DB is quite big, we want to compute differences for each user
within the lan, before sending it to the device (that's why we want to use
mdf files that are exact copy of the data for the user, which is easier to
synchronize since it is 1-1 with the device).
Thanks,
Steve
"Uri Dimant" <urid@.iscar.co.il> a crit dans le message de news:
%23LSWwGoTGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Steve
> Actually you have not explained what are you trying to achive?
> What is if the data will be stored in one database? Where do user locate?
> How do they access the database?
> We have many customers installed on their workstations MSDE/EXPRESS and
> just pushed the data (as a publisher) from pur main server . Yep , it is a
> replication which sometimes has some problems that hard to solve.
>
>
>
> "Steve B." <steve_beauge@.com.msn_swap_com_and_msn> wrote in message
> news:%23%23SBw8nTGHA.6048@.TK2MSFTNGP11.phx.gbl...
>

Need to update old area codes

I have files with area codes that are several years old. Everything I've seen about updating area codes deals with area codes that are current and are about to split in the near future. How would I go about bringing old area codes up to date?It's based on the area code and the exchange...go to usps site...|||Er, Brett, are you sure you want to point sync to the Postal Service? Why would they give a rat's ass about area codes?

Could you provide a URL?

I would love to have a consistant source of Area codes, not to mention international codes. right now I have to search around scrubbing various web sites for up to date lists.|||bpdWork, Which web sites are you using to get your lists? At the moment I'm not even sure what kind of data I need to do the job.|||Originally posted by sync
bpdWork, Which web sites are you using to get your lists? At the moment I'm not even sure what kind of data I need to do the job.
http://www.nanpa.com/nanp1/AllCodes.zip|||Thank you jbroyles. It seems that in order to use the NANPA data, the area codes that need updating must have dates associated with them. Is that correct? My data does not have dates.

Peter|||Originally posted by sync
Thank you jbroyles. It seems that in order to use the NANPA data, the area codes that need updating must have dates associated with them. Is that correct? My data does not have dates.

Peter

It seems you would be correct.|||bump|||[doooh]
postal service
[/dooh]

I used to have a linke...it might have been ATT...

try this

http://www.vzmultimedia.com/customersupport/areacodes/recent_changes.html

or this

http://www.consumer.att.com/global/english/usa_codes.html

There used to be a conversion list using exchanges....

Need to update all but first record in group by

I'd like to use an UPDATE statement to set 'Y' and 'N' values to a flag
field in a table called SVCrossSales for each set of records keyed by
the CSNum field. The first record in each group would get a CSFlg
value of 'N' and the subsequent records would get a 'Y'. Here's a
sample of what the CSFlg values should end up being for each CSNum:
INum CSNum CSFlg
-- -- --
2 1 N
2 1 Y
2 1 Y
2 2 N
2 2 Y
2 3 N
2 3 Y
2 3 Y
Instead of using a cursor to cycle through each CSNum, I'd like to use
an UPDATE statement with an aggregate join. I can't think of anything
else except using TOP to pick out the first record in the set.
Obviously, if I can set the 'N' values for CSFlg, I would do a simple
UPDATE to set the 'Y' values for the rest of the records.
This UPDATE fails:
UPDATE
SVCrossSales
SET
CSFlg = 'N'
FROM
SVCrossSales
INNER JOIN (
SELECT TOP 1 CSNum
FROM SVCrossSales
WHERE INum = 2
GROUP BY CSNum) GroupedSales ON (
SVCrossSales.CSNum = GroupedSales.CSNum)
WHERE
SVCrossSales.INum = 2
What is the proper syntax to do my UPDATE?>> I'd like to use an UPDATE statement to set 'Y' and 'N' values to a flag
Since there is no column values that can uniquely identify a row, this is
logically impossible.
Since a well designed table is a set of rows, the concept of
first/second/last etc. does not really apply. In a relational database,
you'd identify a row using the key values, not by positional descriptions.
TOP 1 without ORDER BY clause relies on certain internal materialization of
values, and it is much better to have
Once you have the keys & constraints explicitly declared, you can formulate
an UPDATE statement along the lines of:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales .INum
AND <predicate involving keys> )
AND INum = 2 ;
Anith|||Anith, I'm not sure how a WHERE EXISTS will help. It returns a boolean
result (true or false). How can that help identify the first record of
each set of CSNums? I need something to pick out the records marked
with an x below and set the CSFlg = 'N':
INum CSNum CSFlg
-- -- --
x 2 1 N
2 1 Y
2 1 Y
x 2 2 N
2 2 Y
x 2 3 N
2 3 Y
2 3 Y|||Steve,
I am not sure if you have taken time to read what I wrote. Note that it is
impossible to accomplish what you are asking to do, since there is no
logical way of identify a row without a key.
Also, you have not posted you table structures & sample data (
www.aspfaq.com/5006 ). It is not clear whether the sample data you posted
includes all the columns in the table or not. It is not clear whether you
have keys and constraints in your table.
The UPDATE statement I posted include a correlation denoted as <predicate
involving keys>. This is the most critical part is solving your problem. If
you have no keys, all bets are off.
Anith|||Sorry...should have posted this to begin with...
DDL for the SVCrossSales table:
CREATE TABLE [dbo].[SVCrossSales] (
[INum] [int] NOT NULL ,
[SalesNum] [int] NOT NULL ,
[CSNum] [int] NULL ,
[CSFlg] [char] (1) NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SVCrossSales] WITH NOCHECK ADD
CONSTRAINT [PK_SVCrossSales] PRIMARY KEY CLUSTERED
(
[INum],
[SalesNum]
) ON [PRIMARY]
GO
The INum is an ID field used in various tables of ours. The SalesNum
field is like an indentity seed in that it's a unique value for each
record in the table. Together, with INum, it forms the primary key for
the SVCrossSales table. I have a stored procedure that updates the
CSNum and then cycles through a cursor to update the CSFlg for each set
of recods with the same CSNum.
Sample data:
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
So, is there a way to use a single update to set the 'N' values?|||I should have also mentioned that the INum and SalesNum fields link to
a sales table (SVSales) where my stored procedure's logic figures out
which sales to group together and assign the next available CSNum to in
SVCrossSales. The last step in the sproc is to update the CSFlg.|||Based on the sample data you posted, if the CSFlag column already has the
values 'Y', then do:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE NOT EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum < SVCrossSales.SalesNum )
AND INum = 2 ;
If both the values 'Y' and 'N' are to be set, then do:
UPDATE SVCrossSales
SET CSFlg = CASE WHEN EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum <
SVCrossSales.SalesNum )
THEN 'Y' ELSE 'N' END
WHERE INum = 2 ;
You can re-write both these statements using a maxima function, but this
should give you what you are looking for.
Anith|||I replaced the cursor logic for updating CSFlg with the UPDATE
statement above. It didn't work. Also, it more than doubled the
execution time of the sproc. I don't understand what the EXISTS
subquery does. Shouldn't there be something to select the top 1 record
for each group set of records with the same CSNum?|||>> I replaced the cursor logic for updating CSFlg with the UPDATE statement
You will have to expand on that. Here is the data before & after update
based on the sample data you posted.
-- Before update
SELECT * FROM SVCrossSales ORDER BY INum, CSNum DESC, SalesNum
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 Y
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 Y
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 Y
2 336121 13255 Y
2 336122 13255 Y
-- Do the update
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE NOT EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum < SVCrossSales.SalesNum )
AND INum = 2 ;
-- After update
SELECT * FROM SVCrossSales ORDER BY INum, CSNum DESC, SalesNum
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
That might have something to do with the overall construction of the stored
procedure and suboptimal indexing.
Well, as I said, there are several ways you can derive the solution. In this
case, you can use TOP 1 as well like:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE SalesNum = ( SELECT TOP 1 s1.SalesNum
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
ORDER BY s1.SalesNum )
AND INum = 2 ;
Anith|||Hi There,
Let us See if this can help your cause
--
Update yourTable Set CSFlag = 'N' Where SalesNum In (Select
Min(SalesNum) From yourTable Group By INum,CSNum)
--
Please let me know if it worked for You.
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
With Warm regards
Jatinder Singh