?
Solved

SET IDENTITY_INSERT ON

Posted on 2013-05-12
2
Medium Priority
?
489 Views
Last Modified: 2013-05-14
Hello
for some sync reasons ... i've create a trigger for Insert on a specific table and i need  need to insert the same data in other database
the problem is sql server not allow to use
 SET IDENTITY_INSERT ON  to refer a tble in other database

how i can do that
0
Comment
Question by:ali_alannah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 1500 total points
ID: 39159545
create a stored procedure, in the other database of yours and put the set identity insert on in the stored proc.

Now create a temp table with the same schema as the target table and insert the data into the temp table in the trigger.

Use the same temp table to populate the target table from the stored proc.
As temporary tables can be accessed acorss the databases, they will have the same data as long as it is the same server and the same SPID.

The below code is just a sample, this wont compile 100% in your system, it is just for assisting you.
Example code

[b]trigger[/b]

create trigger trg_abcd 
AS
BEGIN

create table #abcd
(
 a int
, b int
, c int
)

insert into #abcd select * from deleted -- (or inserted)

exec <anotherDatabase>.dbo.usp_abcd
END

[b]Stored procedure[/b]

create procedure usp_abcd
AS
BEGIN
SET IDENTITY_INSERT ON
insert into <target Table>
select * from #abcd
END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39159666
Unrelated to your problem and in case you were not aware SET IDENTITY_INSERT requires the following permissions:
User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.
This requirement, in most shops, disqualifies its use in any user code.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question