kuff82
2006-04-21T01:00:00Z
When I add a new contact to Outlook 2003 the contact is added to a SQL2005 database.

However I can not -
Update: makes a duplicate entry
Delete: The follwing item was not found..:ItemName
Load Item: No Data found in db...
- from the database.

IF I delete all contacts from Outlook first then Load All,
the contact will load but the errors remain.
If I Store Item it inserts a new record, which still gives the same errors.

I have Upsized my data from Access 2003 to SQL2005
And did not have this issue when in Access.
An use Autonumber for Primary key.

I have used the 2005 ODBC Driver and legacy ODBC Driver.

I have set my primary keys and used:
SELECT @@Idenity : returns blank
SELECT SCOPE_IDENTITY() : returns blank
SELECT max(ContactID) From Contacts : returns the last ContactID
SELECT IDENT_CURRENT('Contacts') : returns the last ContactID

I am using max() right now.

I am using a View of the Contacts and Company Table and have no issues updating or loading the 1700 + records in the db
as long as I update only Company or Contact table.

I plan to create a trigger to handle updates to both tables but first I need to find what my issue is with associating a new item.

Thanks

- Mike G
kuff82
2006-04-21T01:18:00Z
I see a a few "NO Primary key..." erors when I LOADD ALL into an empty Outlook folder.

- Mike G
Administrator
2006-04-21T11:17:00Z
Hi,

can you mail us your table creation script and your mappings (export button in Assign Table Dialog).
We will check your settings.
kuff82
2006-04-21T15:09:00Z
Thanks for the help!

Took care of two issues,
1. auto increment in SQL2005
2. Updating from a view with two tables.

Took care of both with a trigger on the view.

CREATE TRIGGER [iolContactsTrigger] ON [dbo].[OutLookContacts]
INSTEAD OF INSERT
AS
BEGIN

SET IDENTITY_INSERT Company ON

INSERT INTO Company (CompanyID, CompanyName)
SELECT CompanyID, CompanyName
FROM inserted

SET IDENTITY_INSERT Company OFF

DECLARE @newCompanyID int
SELECT @newCompanyID = IDENT_CURRENT('Company')

SET IDENTITY_INSERT Contacts ON

INSERT INTO Contacts (ContactID, CompanyID, NameFirst, NameLast)
SELECT ContactID, CoID, NameFirst, NameLast
FROM inserted

SET IDENTITY_INSERT Contacts OFF

DECLARE @newContactID int
SELECT @newContactID= IDENT_CURRENT('Contacts')

UPDATE Contacts
SET CompanyID = @newCompanyID
WHERE ContactID = @newContactID;

END

- Mike G