Table ' does not have the identity property. Cannot perform SET operation.

Error Message:


System.Data.SqlClient.SqlException: Cannot find the object "table" because it does not exist or you do not have permissions.

try again
System.Data.SqlClient.SqlException: Table table does not have the identity property. Cannot perform SET operation.
at table.DatabaseIO.UpdateKey(Key key, KeyGroup keyGroup)
at table.Interface.AddKey.btnSave_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
Description:
This error message appears when you try to use the SET IDENTITY_INSERT setting for a table that does not contain a column, for which the IDENTITY property was declared.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and are corrigible by the user. The SET IDENTITY_INSERT setting cannot be used on such a table.

Versions:
All versions of SQL Server.

Example(s):
USE test
GO

CREATE TABLE test2
(
test1 int)
GO
SET IDENTITY_INSERT t ON
INSERT INTO test1 SELECT 1
SET IDENTITY_INSERT test1 OFF
DROP TABLE test1
GO

Remarks:
In the above example we try to turn on the IDENTITY_INSERT setting für the table test1 to insert an explicite value into a column for which the IDENTITY property was declared. Because there no such column in table test1, the error is raised.

How to enable access to sql proflier without sysadmin

If a member of your team wants to have trace rights to diag a issue you can grant alter trace.

grant Alter trace to username
go

to stop this right

deny alter trace to username
go

Setting up transactional replication without a snapshot

We had a case where a client needed replication from London to Hong Kong and New York, needless to say we setup the transaction replication we a snapshot and due to the latency issues the snapshot never worked. I decedied to set up replication without the snapshotm heres how to do it if you need to know.

Normally its very easy to setup the publisher and subscriber by default. Thanks to the handy Wizard interface. However, we faced a tiny bit of a problem when using the wizard.

If you are replicating on the same domain and in the same country using the wizzard is great but this is probably only a small about of situations.

I had a problem with the initial snapshot creation as said above when setting up the transactional replication. This is using SQL Server 2008. The initial snapshot creation took a very long time and would not complete. Even after running it over a weekend, for 48 hours, it did not complete. The thing is, while the snapshot agent was running, the database was not accessible to other users. Is there a way to make the snapshot run under low priority?

Since I had to allow users access to the database, I had to stop the snapshot job. Will it start from scratch if restarted or will it continue from where it stopped? I tested and it seemed to start from scratch once again. Another 8+ hours? I don’t think so. Thus, I set out to find a way to create a transactional replication without a snapshot.

I did a little bit of checking around to find out how to do this as we are all DBAs but a DBA does not know everything hence why you are also reading this site. I found a stored procedure called sp_addsubscription(), this will allow one to initialize a subscription without the need to create a snapshot of the publishing database. sp_addsubscription()you will be able to run and create replication with a backup with this and without the snapshot ment not many hours of waiting. I said to myself. So I did. I did the first test in dev and then in prod after it worked,

Here is how to do it
Following are the steps to create a SQL Server 2005 Transactional Replication without an initial snapshot.

1. Create a new publication using the New Publication Wizard. you are able to still do this by using the wizard to create the publication. Just apply the right information in the fields and settings as required according to your environment. However, when you reach the SnapShot Agent dialog, you will need to leave all the checkboxes unticked. The reason being is that you dont want a snapshot remember this? That’s the only thing to note while going through the New Publication Wizard dialogs.

2. Upon completion of the New Publication Wizard, open the property dialog to your publication. You now need to modify a property setting on the publication. You need to set the publication to allow initialization from backup files. Yeah, that’s all there is to it. Just set this setting to True. It really would have been even more dandier if this could be performed within the New Publication Wizard though.


3. you will now need to Perform a full backup of your database. Backup the database to a directory or drive accessible by the SQL Server, such as e:\ted\ted.bak in my case.

4. Restore the backup into the subscriber sql server instance. Don’t know
how to restore from a SQL backup? Google it up. Easy way to restore is from the managemt studio.

5. Run the following stored procedure in the SQL Server Management Studio Query Window on your publisher server.

sp_addsubscription
@publication ='YourPublicationName', --ie tedpub
@subscriber='SubscriberServerDBInstance', --servername in my case tedserver02@destination_db='SubscriberDatabase', -- teddb
@sync_type = 'initialize with backup',
@backupdevicetype = 'disk',
@backupdevicename = d:\ted.bak'
go


You will need to disable or change the job of the cleanup job to run as SA, I would just disable this job before you run the above command as you may encounter an error.

you will now see a new job in the sql agent just go to the job and right click go to properties and then set the job to run as sa.

round up
backup publication database
copy backup to subscriber server
restoring subscriber database
The transactional replication is now happily running between the two servers. Of course, the above process creates only a basic full database transactional replication. If your requirements are a litte bit more complicated, you might want to take a look at the options you can configure in the sp_addsubscription stored procedure.