Access Deleting from MSDE - Help!

Hi
I am a newbie to MSDE and am having a problem deleting records from an MSDE
backend using an Access query front end.
Our main data is stored on a UNIX server (informix type database) and we
link to it using ODBC. We load records to be updated into an Access database
and then use the Access table as a reference for updating the MSDE database.
This means I have a reference table in Access that is joined to the MSDE
table. The records that exist in the Access table are the ones to be deleted
from MSDE. When I use the Access UI to create a 'delete' query, I get an
error message (cannot delete records from this table) when I try to run the
query. I can delete the records individually.
Can anyone help with this please?
Regards
Tom
hi Tom,
Tom wrote:
> Hi
> I am a newbie to MSDE and am having a problem deleting records from
> an MSDE backend using an Access query front end.
> Our main data is stored on a UNIX server (informix type database) and
> we link to it using ODBC. We load records to be updated into an
> Access database and then use the Access table as a reference for
> updating the MSDE database. This means I have a reference table in
> Access that is joined to the MSDE table. The records that exist in
> the Access table are the ones to be deleted from MSDE. When I use the
> Access UI to create a 'delete' query, I get an error message (cannot
> delete records from this table) when I try to run the query. I can
> delete the records individually.
> Can anyone help with this please?
when defining the linked table in Access, did you identify the column(s)
uniquelly idenitying each row (primary key)?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
In the Access UI I have the two tables. The local Access table is called
'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is a
works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
where it joins to a field of the same name, [WORKS_ORDER]. The join is set
to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on the
join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
query as a 'delete', so the only field in the UI grid is labelled
'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
'From'.
I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
database and I get the same error message, so the problem is nothing to do
with MSDE: it seems to be when you are identifying the records to delete
through a join. I have also tried specifying all the columns individually in
the records to be deleted, with the same result. I have also added the
single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set it's
delete option as 'Where', both with and without criteria
('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
Sorry to be so long winded answering your question, but I thought it best to
give all the details rather than to try and guess at what you meant.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cadgbF6mrrenU1@.individual.net...
> hi Tom,
> Tom wrote:
> when defining the linked table in Access, did you identify the column(s)
> uniquelly idenitying each row (primary key)?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Tom,
Tom wrote:
> Hi Andrea
> In the Access UI I have the two tables. The local Access table is
> called 'WORKS_ORDERS_TO_DELETE'. This has a single field,
> [WORKS_ORDER], which is a works order number. It joins to the MSDE
> table ('tblLOCAL_WORKS_ORDER') where it joins to a field of the same
> name, [WORKS_ORDER]. The join is set to select all from
> 'WORKS_ORDER_TO_DELETE', and only those that match on the join in
> 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then
> set the query as a 'delete', so the only field in the UI grid is
> labelled 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option
> is set to 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing
> to do with MSDE: it seems to be when you are identifying the records
> to delete through a join. I have also tried specifying all the
> columns individually in the records to be deleted, with the same
> result. I have also added the single field in the table
> 'WORKS_ORDERS_TO_DELETE' to the grid and set it's delete option as
> 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it
> best to give all the details rather than to try and guess at what you
> meant.
unfortunately I'm not an Access guy and I do not feel very confortable with
Access UI... perhaps you'll have better luck posting in Office NG
hierarchy...
apologise
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more,
I do not know the syntax you got, but
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.tb1 (
ID int
)
CREATE TABLE dbo.tb2 (
ID int ,
data varchar(10)
)
INSERT INTO dbo.tb1 VALUES ( 1 )
INSERT INTO dbo.tb1 VALUES ( 2 )
INSERT INTO dbo.tb1 VALUES ( 3 )
INSERT INTO dbo.tb2 VALUES ( 1 , 'a' )
INSERT INTO dbo.tb2 VALUES ( 2 , 'b' )
INSERT INTO dbo.tb2 VALUES ( 3 , 'c' )
INSERT INTO dbo.tb2 VALUES ( 4 , 'd' )
SELECT a.*, b.*
FROM dbo.tb1 a JOIN dbo.tb2 b
ON a.ID = b.ID
DELETE dbo.tb2
FROM dbo.tb2 a JOIN dbo.tb1 b
ON a.ID = b.ID
GO
DROP TABLE dbo.tb1, dbo.tb2
is a valid T-SQL syntax...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Tom,
I don't know if this will help....
but here are some simple example
queries for deleting records in one
table based on another table (in an mdb):
'Based on field in another table (slow on large data)
DELETE * FROM t1 WHERE t1.F1 IN (SELECT Afield FROM t2)
'Based on field in another table (faster way)
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK=t2.PK
'delete all records from tbl1 which do not have a correspoinding record in
tbl2
DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK = t2.PK WHERE t2.PK
Is Null
Throw SQL Server (MSDE) in the mix,
and I am not sure of the complications.
OTOMH, I might "copy" the Access table to MSDE
and use pass-through query(s).
If you go that route, remember in MSDE that the
DELETE construct does not use "*"
(I spent nearly a whole morning once trying to empty
a SQL Server table using "DELETE * FROM ..."!!!)
gary
"Tom" wrote

> Hi Andrea
> In the Access UI I have the two tables. The local Access table is called
> 'WORKS_ORDERS_TO_DELETE'. This has a single field, [WORKS_ORDER], which is
> a works order number. It joins to the MSDE table ('tblLOCAL_WORKS_ORDER')
> where it joins to a field of the same name, [WORKS_ORDER]. The join is set
> to select all from 'WORKS_ORDER_TO_DELETE', and only those that match on
> the join in 'tblLOCAL_WORKS_ORDER'. I selected all fields in
> 'tblLOCAL_WORKS_ORDER' by double-clicking the asterisk (*). I then set the
> query as a 'delete', so the only field in the UI grid is labelled
> 'tblLOCAL_WORKS_ORDER.*', and that column's 'Delete' option is set to
> 'From'.
> I have now copied the table 'tblLOCAL_WORKS_ORDER' to the local Access
> database and I get the same error message, so the problem is nothing to do
> with MSDE: it seems to be when you are identifying the records to delete
> through a join. I have also tried specifying all the columns individually
> in the records to be deleted, with the same result. I have also added the
> single field in the table 'WORKS_ORDERS_TO_DELETE' to the grid and set
> it's delete option as 'Where', both with and without criteria
> ('[WORKS_ORDERS_TO_DELETE].[WORKS_ORDER]=[tblLOCAL_WORKS_ORDER].[WORKS_ORDER]').
> Sorry to be so long winded answering your question, but I thought it best
> to give all the details rather than to try and guess at what you meant.
> Regards
> Tom
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3cadgbF6mrrenU1@.individual.net...
>
|||For "Access part,"
ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
http://support.microsoft.com/default...b;en-us;207761
"Delete Queries
*****************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
******************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.
To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query. "
|||You are a star, Gary.
All I had to do was to open the properties in the query and set 'unique
queries' to 'yes' and it worked!
Many thanks - I know you said in your earlier message you spent hours trying
to resolve this, but you have just saved me a few.
Regards
Tom
"Gary Walter" <garylwplease@.nospamwamego.net> wrote in message
news:eLGCHSoQFHA.1476@.TK2MSFTNGP09.phx.gbl...
> For "Access part,"
> ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
> http://support.microsoft.com/default...b;en-us;207761
> "Delete Queries
> *****************************************
> When a delete query contains more than one table,
> such as a query that deletes duplicate records from one of the tables,
> the UniqueRecords property must be set to Yes for all versions of
> Microsoft Access.
> ******************************************
> However, because the default value for UniqueRecords is No in Access 2000,
> you must set the value of this property manually when you create a new
> delete query in Access 2000.
> To do so, follow these steps:
> Open the delete query in Design view.
> If the property sheet is not already open, on the View menu, click
> Properties.
> Click an empty area in the upper half of the query window so that the
> property sheet displays "Query Properties" in the title bar.
> Set the UniqueRecords property to Yes.
> Save the query, close it, and then run the query. "
>
|||Hi Andrea
Gary Walter's suggestion worked of changing the 'Unique Record' property in
the UI to 'yes'.
However, I would like to thank you for your replies and your time. I went on
to your website and downloaded your SQL2000 manager, and its great! It seems
more flexible than the one I paid for from somewhere else in that it allows
me to change table structures (add/remove fields etc).
Many thanks for that.
Regards
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3cc9f6F6dtrtmU1@.individual.net...
> hi Tom,
> Tom wrote:
> unfortunately I'm not an Access guy and I do not feel very confortable
> with Access UI... perhaps you'll have better luck posting in Office NG
> hierarchy...
> apologise
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||:D
found another way...
modifying the SQL statement to
DELETE DISTINCTROW SQLtable.*
FROM SQLtable a JOIN AccessTable b
ON a.ID = b.ID
did the trick, even with no primary key
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Access 97 / VBA Users...

...should NEVER be allowed to touch EM, no matter how much T-SQL they know.
She sent 7800 records into an external "black-box" system -- records whose
status wee need to track.
And then she deleted them from SQL Server. Now she's talking to one of our
C# guys about using a hex editor to crack the LDF file format to extract the
records that were deleted and restore them.
PLEASE have more intelligent suggestions?
Anyone who's an MCDBA will recall the MOC 2072 course in database
administration. As a former MCT, I still have the CD for that course, if
anyone has a chapter or lesson for me to refer to?
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneSounds like the DBA needs to be a bit more stingy with the access
permissions and management needs to spend a bit more time peering over their
shoulders.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:O9sXfhcxFHA.916@.TK2MSFTNGP10.phx.gbl...
> ...should NEVER be allowed to touch EM, no matter how much T-SQL they
> know.
> She sent 7800 records into an external "black-box" system -- records whose
> status wee need to track.
> And then she deleted them from SQL Server. Now she's talking to one of
> our C# guys about using a hex editor to crack the LDF file format to
> extract the records that were deleted and restore them.
> PLEASE have more intelligent suggestions?
> Anyone who's an MCDBA will recall the MOC 2072 course in database
> administration. As a former MCT, I still have the CD for that course, if
> anyone has a chapter or lesson for me to refer to?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> Sounds like the DBA needs to be a bit more stingy with the access
> permissions and management needs to spend a bit more time peering over
> their shoulders.
HAH! What DBA?!? Why do you think me, a developer is on this group so
much'! I'm just an MCSD code monkey, and I'm probably the one here that's
most qualified as a DBA.
Did that send a shiver up your spine?
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Mike,
The MS-2072 curriculum is not going to help. If she could delete the
records in EM then she could do it in any app. So it is more of a
permissions issue here.
A fix...POINT IN TIME recoverability using the transaction log. Or possibly
one of the third-party log exploring tools (could help minimize data loss
after the DELETE occured).
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OHOXzDdxFHA.720@.TK2MSFTNGP15.phx.gbl...
> HAH! What DBA?!? Why do you think me, a developer is on this group so
> much'! I'm just an MCSD code monkey, and I'm probably the one here
> that's most qualified as a DBA.
> Did that send a shiver up your spine?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||All good software engineers start out as common code monkeys but eventually
evolve into a DBA.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OHOXzDdxFHA.720@.TK2MSFTNGP15.phx.gbl...
> HAH! What DBA?!? Why do you think me, a developer is on this group so
> much'! I'm just an MCSD code monkey, and I'm probably the one here
> that's most qualified as a DBA.
> Did that send a shiver up your spine?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> A fix...POINT IN TIME recoverability using the transaction log. Or
> possibly one of the third-party log exploring tools (could help minimize
> data loss after the DELETE occured).
Yes, that's precisely what I'm looking for. I just found it at Module 7,
page 20.
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Some thoughts here... http://www.karaszi.com/SQLServer/in...
mes.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:O9sXfhcxFHA.916@.TK2MSFTNGP10.phx.
gbl...
> ...should NEVER be allowed to touch EM, no matter how much T-SQL they know
.
> She sent 7800 records into an external "black-box" system -- records whose
> status wee need to track.
> And then she deleted them from SQL Server. Now she's talking to one of ou
r
> C# guys about using a hex editor to crack the LDF file format to extract t
he
> records that were deleted and restore them.
> PLEASE have more intelligent suggestions?
> Anyone who's an MCDBA will recall the MOC 2072 course in database
> administration. As a former MCT, I still have the CD for that course, if
> anyone has a chapter or lesson for me to refer to?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

Access 2000 to Sql2000 : file size change?

I have an Access 2000 database with 640,000 records.
(growing weekly) File size is hovering around 245MB.
I plan to import/convert this database into a new database
on our SQL2000 server. (As soon as I finished that
chapter of the book!)
Implementation planning question: Will the database size
change significantly either way once it's imported in SQL?
TIA,
TeresaIt will probably me less than with Access.
When you import the access stuff, take a minute to check the column-type
definitions and optimize it for your needs.
You might also want to check indexing (wizard) when running on SQL2000 with
your applications, so you can provide propre and fast usage.
jobi
"teresa" <teresaj@.vvh.org> wrote in message
news:06c301c35550$497ded40$3501280a@.phx.gbl...
> I have an Access 2000 database with 640,000 records.
> (growing weekly) File size is hovering around 245MB.
> I plan to import/convert this database into a new database
> on our SQL2000 server. (As soon as I finished that
> chapter of the book!)
> Implementation planning question: Will the database size
> change significantly either way once it's imported in SQL?
> TIA,
> Teresa
>sql

Access 2000 project

Hi,

I am using Access 2000 to access a SQL Server 2000 database. The records are
read only, and I can't make any changes to them. How can I change this? I've
checked Help, but don't seem to be able to find what I need to know.

Thanks! JillJill,

Make sure the tables in SQL Server had a "primary key". If it does not,
Access can not modify the records. I hope this solves your problem.

Oscar...

"JA" <jarmour@.kc.rr.com> wrote in message
news:tLF8g.68068$Tf1.27575@.tornado.rdc-kc.rr.com...
> Hi,
> I am using Access 2000 to access a SQL Server 2000 database. The records
are
> read only, and I can't make any changes to them. How can I change this?
I've
> checked Help, but don't seem to be able to find what I need to know.
> Thanks! Jill

Access / SQL Server or Filemaker from Paradox?

I've been programming in Paradox since DOS, but one mail-order database is getting rather large (100K client records, and associated orders, etc).

Our client wants us to move to Filemaker. In fact he'd like to have a 3rd party write the programme and me maintain it. The current package has been written & maintained by myself, with an awful lot lot going on in the Object PAL code.

I already have a copy (legal) of SQL Server & 10 licences for Access, and think that's a better option to re-develop in (although I could do with some training in both).

What are the pros & cons for each app, and what would people recommend. I'm going to cross-post into Access, Paradox & Filemaker forums to get an equal view.

Thanks

MarkIf he wants an efficient, robust, scalable solution than develop the database in SQL Server. Use MS Access as front-end for the time being, but you will eventually want to migrate to other interfaces.

Access .adp :How to INSERT all but KEY violations

I am trying to append records from one table to another in a db running on
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.

In a regular Access mdb, an INSERT statement (append query) would do just
that. Of course it warns you of the violation but a DoCmd.SetWarnings FALSE
takes care of that.

Any ideas as to what I need to do to achieve that same thing?For example:

INSERT INTO TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

(where key_col is the primary key).

--
David Portas
SQL Server MVP
--

About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

I want my trigger to copy the newly added records from our ERP table...The ERP table contains all records for materials receiving and returning, sales orders, and purchases....I want my trigger to only copy those newly added records for materials receiving and returning...After studying the table, I managed to get the keys on how to get only the records for materials receiving and returning...So my trigger goes like this

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

SELECT @.ID = ID,
@.DATUM = DATUM,
@.SUPCODE = CRDNR,
@.ITEMCODE = ARTCODE,
@.QTY = AANTAL,
@.PONUM = BKSTNR_SUB,
@.UNIT = UNITCODE,
@.JENTRY = REKNR,
@.LINK = LINKEDLINE,
@.TYPE = OORSPRONG,
@.SUBTYPE = TRANSSUBTYPE
FROM GBKMUT

IF (@.PONUM IS NOT NULL)
BEGIN
IF (@.JENTRY <> 2140) the
BEGIN
IF (((@.TYPE = 'R') AND (@.SUBTYPE <> 'J') AND (@.LINK IS NOT NULL)) OR ((@.TYPE = 'U') AND (@.SUBTYPE = 'J') AND (@.LINK IS NULL)))
BEGIN
INSERT INTO I_DUMMY VALUES(@.ID, @.DATUM, @.SUPCODE, @.ITEMCODE, @.QTY, @.PONUM, @.UNIT, @.JENTRY, @.LINK, @.TYPE, @.SUBTYPE,'I')
END
END
END

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In a TRIGGER, there are two virtual tables, one named inserted, and one named deleted. The inserted table has new rows for inserts, and new values for updates. The deleted table has rows that were deleted, and old valued for updates.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

IF @.@.ROWCOUNT = 0
RETURN
INSERT INTO I_DUMMY
SELECT
ID,
DATUM,
CRDNR,
ARTCODE,
AANTAL,
BKSTNR_SUB,
UNITCODE,
REKNR,
LINKEDLINE,
OORSPRONG,
TRANSSUBTYPE,
'I')
FROM inserted i
WHERE ( BKSTNR_SUB IS NOT NULL
AND REKNR <> 2140
AND ( ( OORSPRONG = 'R'
AND TRANSSUBTYPE <> 'J'
AND LINKEDLINE IS NOT NULL
)
OR ( OORSPRONG = 'U'
AND TRANSSUBTYPE = 'J'
AND LINKEDLINE IS NULL
)
)
)

|||

Oh i see..So you mean I have to copy the newly added records from the virtual table inserted into my destination table I_Dummy....Thanks for that info...I really thought the trigger would automatically know what are the newly added fileds on my main table...Well on what I have understood from you, it will be stored on a virtual table...

By the way, a follow-up question regarding the virtual table...For example, my ERP table got a newly added record...Tha record will then be stored to the virtual table INSERTED...then my trigger would copy that record into the table I_Dummy....What if a new record will be added again? Will my trigger still copy the record it had already added plus the new record added again?

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

If another user inserts a row into the table at almost the same exact moment, the TRIGGER will fire (for that user) and the inserted table will be created and available to the TRIGGER, containing ONLY the rows inserted by this user.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

To confirm what I have understood, suppose i have only one user, she inserted a new record...the INSERTED table will be created and would contain the newly inserted record (newrecord1)...based from my trigger's code, record1 will be copied to I_Dummy table...

If my user(the same user) would insert another record, the INSERTED table for that user would now contain record1 and record2...But how about my I_Dummy table? Will it still copy record1? or just record2?

|||

You have almost got the idea.

Each time the TRIGGER fires (On each separate INSERT) a new inserted table is created, containing ONLY the rows from that specific INSERT. It does not contain previous rows from previous inserts by the same user. It does not contain rows from inserts from other users. It does not contain rows from a previous INSERT at all.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

I want my trigger to copy the newly added records from our ERP table...The ERP table contains all records for materials receiving and returning, sales orders, and purchases....I want my trigger to only copy those newly added records for materials receiving and returning...After studying the table, I managed to get the keys on how to get only the records for materials receiving and returning...So my trigger goes like this

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

SELECT @.ID = ID,
@.DATUM = DATUM,
@.SUPCODE = CRDNR,
@.ITEMCODE = ARTCODE,
@.QTY = AANTAL,
@.PONUM = BKSTNR_SUB,
@.UNIT = UNITCODE,
@.JENTRY = REKNR,
@.LINK = LINKEDLINE,
@.TYPE = OORSPRONG,
@.SUBTYPE = TRANSSUBTYPE
FROM GBKMUT

IF (@.PONUM IS NOT NULL)
BEGIN
IF (@.JENTRY <> 2140) the
BEGIN
IF (((@.TYPE = 'R') AND (@.SUBTYPE <> 'J') AND (@.LINK IS NOT NULL)) OR ((@.TYPE = 'U') AND (@.SUBTYPE = 'J') AND (@.LINK IS NULL)))
BEGIN
INSERT INTO I_DUMMY VALUES(@.ID, @.DATUM, @.SUPCODE, @.ITEMCODE, @.QTY, @.PONUM, @.UNIT, @.JENTRY, @.LINK, @.TYPE, @.SUBTYPE,'I')
END
END
END

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In a TRIGGER, there are two virtual tables, one named inserted, and one named deleted. The inserted table has new rows for inserts, and new values for updates. The deleted table has rows that were deleted, and old valued for updates.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

IF @.@.ROWCOUNT = 0
RETURN
INSERT INTO I_DUMMY
SELECT
ID,
DATUM,
CRDNR,
ARTCODE,
AANTAL,
BKSTNR_SUB,
UNITCODE,
REKNR,
LINKEDLINE,
OORSPRONG,
TRANSSUBTYPE,
'I')
FROM inserted i
WHERE ( BKSTNR_SUB IS NOT NULL
AND REKNR <> 2140
AND ( ( OORSPRONG = 'R'
AND TRANSSUBTYPE <> 'J'
AND LINKEDLINE IS NOT NULL
)
OR ( OORSPRONG = 'U'
AND TRANSSUBTYPE = 'J'
AND LINKEDLINE IS NULL
)
)
)

|||

Oh i see..So you mean I have to copy the newly added records from the virtual table inserted into my destination table I_Dummy....Thanks for that info...I really thought the trigger would automatically know what are the newly added fileds on my main table...Well on what I have understood from you, it will be stored on a virtual table...

By the way, a follow-up question regarding the virtual table...For example, my ERP table got a newly added record...Tha record will then be stored to the virtual table INSERTED...then my trigger would copy that record into the table I_Dummy....What if a new record will be added again? Will my trigger still copy the record it had already added plus the new record added again?

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

If another user inserts a row into the table at almost the same exact moment, the TRIGGER will fire (for that user) and the inserted table will be created and available to the TRIGGER, containing ONLY the rows inserted by this user.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

To confirm what I have understood, suppose i have only one user, she inserted a new record...the INSERTED table will be created and would contain the newly inserted record (newrecord1)...based from my trigger's code, record1 will be copied to I_Dummy table...

If my user(the same user) would insert another record, the INSERTED table for that user would now contain record1 and record2...But how about my I_Dummy table? Will it still copy record1? or just record2?

|||

You have almost got the idea.

Each time the TRIGGER fires (On each separate INSERT) a new inserted table is created, containing ONLY the rows from that specific INSERT. It does not contain previous rows from previous inserts by the same user. It does not contain rows from inserts from other users. It does not contain rows from a previous INSERT at all.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

About the use of TRUNCATE TABLE command

Hi all,
I have a big table with 54 Million of records. I want to use the TRUNCATE TABLE command to eliminate all the records. I don't want to drop the table, just delete records.

But I have questions about the use of this command:

1) How much time does this command execution could it take?
2) I want to this (I need to this) in production enviroment. Will you do it?

Thanks in advance!

Hi,

1) This depends on your machine, Truncate table just deallocates the data pages and uses a minimum of log space, it should be quiet fast for your 54m rows.
2) What do you mean, if we WOULD (or will ? You probably will have to do it :-)) do it ? You will have to keep in mind that there is NO way back after truncating, the only way would be to restore the data from a backup.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi and thanks Jens,

1) Ok, the machine is in production environment. So, that's my worry. I'm asking this to have an idea about the time this command spends in an production environment. I don't want to get the server slow for this command.
2) And yes, I know that this action delete rows and don't log to much: that's what I need.

Thanks again.

about MSRepl_commands Table

Hi,
We're running sql2000 with transactional replication. I found that the
system table distribution.msrepl_commands has over 3 million records. So, I
execute the command "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
0, @.max_distretention = 0" manually. As the result, the no. of records still
remain 2.5 million records. Why? I checked with distribution clean jobs was
success everytime. How can I minimize the table size?
Thx!
Ron
are you using anonymous subscriptions? If so this might explain what you are
seeing? Also are your distribution agents running frequently? Are any of
them stopped. The clean up normally happens when they run.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:utuLdwbhFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We're running sql2000 with transactional replication. I found that the
> system table distribution.msrepl_commands has over 3 million records. So,
> I execute the command "EXEC dbo.sp_MSdistribution_cleanup
> @.min_distretention = 0, @.max_distretention = 0" manually. As the result,
> the no. of records still remain 2.5 million records. Why? I checked with
> distribution clean jobs was success everytime. How can I minimize the
> table size?
> Thx!
> Ron
>
|||Yes! I'm using anonymous subscriptions and clean up every 3 hours. The clean
up jobs seems to work success according to the status of SQLServer Agent.
Any things I can do to minimize the size?
Ron
"Hilary Cotter" <hilary.cotter@.gmail.com> glsD:%23sBgbIchFHA.1252@.TK2MSFTNGP09.phx .gbl...
> are you using anonymous subscriptions? If so this might explain what you
> are seeing? Also are your distribution agents running frequently? Are any
> of them stopped. The clean up normally happens when they run.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "555 Rider" <ronchukw@.yahoo.com> wrote in message
> news:utuLdwbhFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||query distribution.dbo.MSdistribution_status
Are most the command delivered or waiting to be delivered? If they are
waiting to be delivered you should look at improving the performance of your
distribution agents, like using pull subscriptions and changing the
PollingInterval to 1,
If the commands are mostly delivered, your problem truly is with them being
cleaned up; try to have the distribution clean up agent run every 10
minutes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:ex7unWchFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Yes! I'm using anonymous subscriptions and clean up every 3 hours. The
clean
> up jobs seems to work success according to the status of SQLServer Agent.
> Any things I can do to minimize the size?
> Ron
>
> "Hilary Cotter" <hilary.cotter@.gmail.com>
glsD:%23sBgbIchFHA.1252@.TK2MSFTNGP09.phx .gbl...[vbcol=seagreen]
any[vbcol=seagreen]
So,[vbcol=seagreen]
result,[vbcol=seagreen]
with
>

About Merge Join Component

Hi, I'm using a Merge Join Component of Inner Join type to retrieve from the right pipeline some records to append to the ones coming from left pipeline according to the join citerias defined on the compnent.

Is there any way to know which are the records coming from the left pipeline that doesn't match the join criterias?

In the following I'll try to do an axample.

LF pipeline:

Column0 Column1 Column2

1 aaaa aa11

2 bbbb bb11

3 cccc cc11

4 dddd dd11

RT pipeline:

ColumnA ColumnB

1 aa22

4 dd22

On exiting from the MergeJoin, defining “Column0” for LT as join key and “ColumnA” for RT and as output data all the columns of the LT pipeline and the only ColumnB from the RT pipeline it should be obtained the following records:

Column0 Column1 Column2 ColumnB

1 aaaa aa11 aa22

4 dddd dd11 dd22

and the records from the LT pipeline:

2 bbbb bb11

3 cccc cc11

shouldn't go in the output from the Merge Join Component.

What I need to know is which are these last lines because I need to manage them.

Thanks!

If you need to get both matches and mismatches; shouldn't you be using an left or full outer join instead? then you could place a conditional split to redirect and manage rows that did not match.

BTW, Have you loked at the Looukup transformation instead?

You could have a lookup based on the 2 set of row (assuming is an OLE DB data source) and then use the error output to redirect the rows that don't match. That way you get 2 outputs; the matches and the mismatches.

|||

Hi,

I read your answer and I think I need more details about it.

When you say:

“….shouldn't you be using an left or full outer join instead?”

I tell you:

If I use a left outer join, on output I have both matches and mismatches records. Can you tell me on what I have to do the conditional split? Can you show me any example to let me understand better?

When you say:

“…BTW, Have you loked at the Looukup transformation instead?”

I tell you:

In the package both the two sets of rows (LT pipeline and RT pipeline) doesn't came from a datasource but they are a result of an intermediate elaboration of data read at the beginning of package.

Thanks again!

|||When you use the LEFT OUTER JOIN, take columnB and test if it is NULL or not. If it is NULL then you are looking at a record that didn't have a match.|||

Thanks,

I'll try to do that!

|||Actually, in thinking about it, the better thing to do is to take COLUMNA and pass it through the merge join transformation. Check that for NULL, not COLUMNB.|||

SabAlo wrote:

Hi,

I read your answer and I think I need more details about it.

When you say:

“….shouldn't you be using an left or full outer join instead?”

I tell you:

If I use a left outer join, on output I have both matches and mismatches records. Can you tell me on what I have to do the conditional split? Can you show me any example to let me understand better?

just add a conditional split after the merge join to split the rows. You can evaluate if the the columnX is null or not; if it is null means there was not a match.

SabAlo wrote:

When you say:

“…BTW, Have you loked at the Looukup transformation instead?”

I tell you:

In the package both the two sets of rows (LT pipeline and RT pipeline) doesn't came from a datasource but they are a result of an intermediate elaboration of data read at the beginning of package.

Thanks again!

Fair enough.

|||

Hi,

I followed the suggestion to consider the columnA in the output results and it is working! I test the its value and it is really null if it doesn't match the merge criteria.

As regard the testing of the other column (B), my problem is that could contain null value and should be imppossible to distinguish one record from the other.

Anyway, thanks for the hint!!

About loop and cells

How do i do a loop to insert excel records to new excel worksheet as i onli want a certain of the columns from the existince excel. For the existince excel mi got record of column A - M then i just need to copy data of A - L then copy from the same existince column H data to the new excel worksheet. For those who know pls put up codes for me so tat i can have a better understanding over it . THankzAre you sure you have the right forum?|||oh so sorry coz i need to do this first then i can have a standard excel to push it into mssql database. SO i need to solve this situation before i can proceed. If i really post wrong so sorry as i am new .