Totally newbie help for MSSQL

[nettproffen]

Hi.

I have just upsized my access db, and need some advice/help on some issues. I have searched different forums, but have not found any helpful info yes.

1.
After upsize the all access table ID fields (primary key) is no longer primary key. If I try to enable it to be primary key in MSSQL i get the error;

'kropp' table
- Unable to create index 'PK_kropp'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Field ID is a int.

2.
In access I used the default value "NOW()" to get a auto insert date/time in a row. How can I do this in MSSQL?

Thanx for all advice.

Bjørn-Toe

 

[SQLDenis]

This is because you have duplicate values

run this to find out the duplicates

select id, count(*) as HowMany
from kropp
group by id
having count(id) >1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[nettproffen]

Hi.

Yes that works.

How can i get the auto insert date on new insert record?
I have used now() in access. What field type and default value can I use in MSSQL ?

Bjørn-Tore

 

[SQLDenis]

getdate(), try it out by running the following statement
select getdate()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[nettproffen]

And field type "smalldate" ?

 

[SQLDenis]

smalldatetime or datetime
It depends what you need
If you need to go back begor 1900 use datetime or if you need seconds
Run this to see the difference in ouput
select convert(smalldatetime,getdate())as SmallDateTimeType,getdate() as DateTimeType

Below are the differences (from BOL)

The datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/