Two Basic Questions.
in [Server Configuration]
|
From: John Bell on 2 Mar 2010 02:36 On Mon, 1 Mar 2010 22:32:51 -0600, "Maura Jenkins" <mj(a)nospam.com> wrote: > >"Marian Henna" <marion.henna(a)yacobe.net> wrote in message >news:OJqKpnXuKHA.5940(a)TK2MSFTNGP02.phx.gbl... > >> 1) If we run the script to create the DB on the Server, can we drop the DB >> and load the original DB from the XP machine? I think this is the correct >> terminology. > >this is not too clear; drop what DB and run what script? what you should be >able to do is detach the DB on the XP machine, copy it to the second machine >and then attach it there ... haven't done one in a while so I'm short on >details here ... point is that it's not too difficult .. but there are some >potential gotchas if your FROM and TO server configurations vary from one >another > >> 2) We have a column in a table that we need to update via a calculation of >> two fields. So we need to do something like this: >> >> --->>> WO_Total = WO_Item_Cost * WO_Item_QTY. > >assume you have an identity column "id" as your primary key; your trigger >can be this simple: > > > >CREATE TRIGGER calc > >ON yourTable > >FOR INSERT > >AS > > > >UPDATE yourTable SET WO_Total = WO_Item_Cost * WO_Item_QTY > > WHERE id = @@IDENTITY > > > >you don't have to have an IDENTITY column; but you will need some predicate >for the WHERE that updates the current row only > > > >another example for WHERE: > >WHERE emp_id = inserted.emp_id > Your trigger assumes that a single row has been inserted if you use @@IDENTITY and even if your application only inserts a single row it will cause problems when you have to do things en-mass such as releases or if your application changes. Therefore you should never assume that more than one row will never be inserted/updated/deleted.. John
From: John Bell on 2 Mar 2010 02:37 On Mon, 1 Mar 2010 20:36:07 -0700, "Marian Henna" <marion.henna(a)yacobe.net> wrote: >Thank you John. > >The reason is that my company owns SQL 2000 so we want to do this enterimly. >We are going to be evaluating SQL2K5 and SQL2K8. We obviously want to make >the right decision as we are having some serious drawbacks with MySQL. > >So, for the short term we were thinking SQL2K now, then take a few courses, >get the right SQL consultant to assist to make the best move in about 3 >months. > >Thanks again for the information and links. > >Also, the gear we have is not up to the SQL2K5/8 minimum requirements, so we >will be investing in that as well. > >"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message >news:drboo5521isarbvvf2adf6a14q953ib8q3(a)4ax.com... >> On Mon, 1 Mar 2010 13:00:52 -0700, "Marian Henna" >> <marion.henna(a)yacobe.net> wrote: >> >>>Hello, I am new to MS SQL 2K, migrating from MySQL. >>> >>>First of all, this company I just started with has an MS SQL2K database >>>sitting on a windows xp pro machine. We are moving it to MS Sever instead, >>>don't know why it was on XP Pro - bad decision we think. >>> >>>Second, in MS SQL we need to update a field during an insert event. So my >>>two questions are: >>> >>>1) If we run the script to create the DB on the Server, can we drop the DB >>>and load the original DB from the XP machine? I think this is the correct >>>terminology. >>>2) We have a column in a table that we need to update via a calculation of >>>two fields. So we need to do something like this: >>> >>>--->>> WO_Total = WO_Item_Cost * WO_Item_QTY. >>> >>>Not sure how to put this in to a trigger. We do this fine in MySQL, but >>>can't seem to figure out how to make this trigger. >>> >>>Thanks, and if I'm not clear enough I certainly can provide more detail. >>> >> >> Hi >> >> I would not move to SQL 2000 as there is no mainstream support for it >> see >> http://blogs.msdn.com/sqlreleaseservices/archive/2008/02/15/end-of-mainstream-support-for-sql-server-2005-sp1-and-sql-server-2000-sp4.aspx >> >> For moving the database look at >> http://support.microsoft.com/kb/224071, make sure that you move >> logins, jobs, packages etc as well. >> >> If you look in Books online or at >> http://msdn.microsoft.com/en-us/library/ms189799.aspx >> you'll find the INSERTED and DELETED logical (conceptual) tables, so >> you will need to add the sum of the products of Cost and Qry from the >> INSERTED logical table to the current total and substract the sum of >> the product from the DELETED logical table grouping by the key >> columns. Post DDL and example data if you want an example. >> >> John >> >> I've assumed that the table to be updated is not the one where you WO_Total is not the table that has WO_Item_Cost and WO_Item_QTY. If it is then use a calculated column as Hugo has said. John
From: Fred BROUARD on 2 Mar 2010 11:40 Hugo Kornelis a �crit : > On Mon, 1 Mar 2010 13:00:52 -0700, Marian Henna wrote: > >> 2) We have a column in a table that we need to update via a calculation of >> two fields. So we need to do something like this: >> >> --->>> WO_Total = WO_Item_Cost * WO_Item_QTY. >> >> Not sure how to put this in to a trigger. We do this fine in MySQL, but >> can't seem to figure out how to make this trigger. > > Hi Marian, > > No need for a trigger, just create a computed column in the table: > > CREATE TABLE Demo > (PrimKey int NOT NULL PRIMARY KEY, > Col1 int NOT NULL, > Col2 int NOT NULL, > Col3 AS Col1 * Col2); > INSERT INTO Demo (PrimKey, Col1, Col2) > VALUES (1, 2, 3); > SELECT PrimKey, Col1, Col2, Col3 FROM Demo; > DROP TABLE Demo; And best will be with a PERSISTED calcuted column.... A + > -- Fr�d�ric BROUARD, MVP SQL Server, expert bases de donn�es et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, mod�lisation, tuning, optimisation Enseignant aux Arts & M�tiers PACA et � L'ISEN Toulon - Var Technologies *********************** http://www.sqlspot.com *************************
From: Maura Jenkins on 3 Mar 2010 00:47 "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:tmfpo5lqiuggi6396r8ltljjcg5rrbvjj7(a)4ax.com... > Your trigger assumes that a single row has been inserted if you use > @@IDENTITY and even if your application only inserts a single row it > will cause problems when you have to do things en-mass such as > releases or if your application changes. Therefore you should never > assume that more than one row will never be inserted/updated/deleted.. > > John my reply was, pretty clearly, a very basic suggestion to someone who appeared to be floundering in search of some basic information; it's a way to get started, not a final solution covering all contingencies; my experience is that people having difficulty with a new technology are well served by getting a simple "win" under their belt by making something work, no matter how simple ... it helps them get to the next step and pursue the more nuanced and complex issues; if you throw all of the ifs, ands, and buts at someone at an early stage, they have a tendency to get overwhelmed and throw their hands up in the air ... Maura
From: Marian Henna on 3 Mar 2010 07:55 Yes guys, the three columns are all in the same table. WO_Total only gets calculated once and that is during an insert. So would this still work, or would it recalculate for updates as well? Thanks guys, I appreciate that these are pretty rookie questions, but we want to make sure it works as this will be pretty much that last change made before moving ahead. Marion. "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:9vfpo59k9mbv7dgmmo1vom4ov4l58pgj3o(a)4ax.com... > On Mon, 1 Mar 2010 20:36:07 -0700, "Marian Henna" > <marion.henna(a)yacobe.net> wrote: > >>Thank you John. >> >>The reason is that my company owns SQL 2000 so we want to do this >>enterimly. >>We are going to be evaluating SQL2K5 and SQL2K8. We obviously want to make >>the right decision as we are having some serious drawbacks with MySQL. >> >>So, for the short term we were thinking SQL2K now, then take a few >>courses, >>get the right SQL consultant to assist to make the best move in about 3 >>months. >> >>Thanks again for the information and links. >> >>Also, the gear we have is not up to the SQL2K5/8 minimum requirements, so >>we >>will be investing in that as well. >> >>"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message >>news:drboo5521isarbvvf2adf6a14q953ib8q3(a)4ax.com... >>> On Mon, 1 Mar 2010 13:00:52 -0700, "Marian Henna" >>> <marion.henna(a)yacobe.net> wrote: >>> >>>>Hello, I am new to MS SQL 2K, migrating from MySQL. >>>> >>>>First of all, this company I just started with has an MS SQL2K database >>>>sitting on a windows xp pro machine. We are moving it to MS Sever >>>>instead, >>>>don't know why it was on XP Pro - bad decision we think. >>>> >>>>Second, in MS SQL we need to update a field during an insert event. So >>>>my >>>>two questions are: >>>> >>>>1) If we run the script to create the DB on the Server, can we drop the >>>>DB >>>>and load the original DB from the XP machine? I think this is the >>>>correct >>>>terminology. >>>>2) We have a column in a table that we need to update via a calculation >>>>of >>>>two fields. So we need to do something like this: >>>> >>>>--->>> WO_Total = WO_Item_Cost * WO_Item_QTY. >>>> >>>>Not sure how to put this in to a trigger. We do this fine in MySQL, but >>>>can't seem to figure out how to make this trigger. >>>> >>>>Thanks, and if I'm not clear enough I certainly can provide more detail. >>>> >>> >>> Hi >>> >>> I would not move to SQL 2000 as there is no mainstream support for it >>> see >>> http://blogs.msdn.com/sqlreleaseservices/archive/2008/02/15/end-of-mainstream-support-for-sql-server-2005-sp1-and-sql-server-2000-sp4.aspx >>> >>> For moving the database look at >>> http://support.microsoft.com/kb/224071, make sure that you move >>> logins, jobs, packages etc as well. >>> >>> If you look in Books online or at >>> http://msdn.microsoft.com/en-us/library/ms189799.aspx >>> you'll find the INSERTED and DELETED logical (conceptual) tables, so >>> you will need to add the sum of the products of Cost and Qry from the >>> INSERTED logical table to the current total and substract the sum of >>> the product from the DELETED logical table grouping by the key >>> columns. Post DDL and example data if you want an example. >>> >>> John >>> >>> > I've assumed that the table to be updated is not the one where you > WO_Total is not the table that has WO_Item_Cost and WO_Item_QTY. > If it is then use a calculated column as Hugo has said. > > John
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: FTS: can I find texts not containing a specified word? Next: maintenance plan log files |