I am trying to save a copy of a shared calendar onto a database table and am having a problem with knowing how to specify the primary key.

I have attempted to use the subject plus the start time (SELECT subject + convert(char(10),start_time,1)), but this resulted in all the calendar entries going into a single data base item.

Any help or example is greatly appreciated.


GCUser

It depends on datatype of your primary key column?

The primary key column data type is varchar(50), during synch. a blank key record is inserted and and all calendar entries were updated to a single row.

Must the primary key be an integer?

GCUser

It is not a good idea to have varchar(50) as primary key!
Please choose something like integer, GUID etc.

I am still lost as to how to specify the SQL command so that the store all will put a unique primary key to the data base. I have reset the primary key for the data base table to be an integer field, but do not know what SQL command to specify.

Can you suggest a sample SQL command? I have tried things like "SELECT @@ROWCOUNT", but that resulted in just 2 rows when storing over 3000 calendar entries. The key values are just 0 and 1.
I even tried a random number function, but it appears that the function is only calculated once so insteading of inserting the calendar entries, they are updated.

I have also tried to used an identity primary key on the target data base table, but am still lost as to what to put in the SQL command field for the Primary key specification.

Thanks in advance for your help.

GCUser

Hello,

Please try:
select max(YOUR_COLUMN_NAME) + 1 from YOUR_TABLE_NAME

I must be still missing something. I set up the data table with integer primary key column of ID (not identity). I also inserted a dummry row with ID value of 0. I specified the SQL command for the Primary key as SELECT MAX(ID) from (data table name) for the data base field of ID.

After the Store All, the row with ID value of 0 was overwritten, and multiple events from the calendar updated the second row with ID value of 1. I am still not able to insert the events into the data table.


GCUser

It is "MAX(ID) + 1" (plus 1)

I mis-typed in the previous message. I did use SELECT MAX(ID) + 1 FROM (data table). The result is as I described.


GCUser

Is this possible?:
if your prev. SQL Statement was "SELECT @@ROWCOUNT" and the statement returns 0 or 1, and you have use it to store
3000 items, al items al linked to value 1 or 0!!!!

I think the problem is in my not understanding how the mandatory and primary key works in the Store All operation. Currently I only have the primary key defined as SELECT MAX(ID) + 1 from (table). I think I should also have a mandatory key defined to determine when a calendar event is a new entry and insert and not update. I have tried adding Start time, end time, and subject all as mandatory keys, but that did not work. Should I specify a mandatory key of ID also and have the SQL command for it as SELECT ID from (table)?


GCUser

Please can you send us your datamodel (creation script) with some test data and
your mappings (Assing Table Dialog->Export). We will create a template for your.

I was able to figure out how to make it work after looking at the examples provided with the installation. I changed the Primary Key SQL to select newid(), and added assigned that to a CalID data base field which I setup as a 16-byte uniqueidentifier field.

Thank you for your help.

GCUser