How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. When
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT has
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a table?
Thanks,
Tom
Hi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
Thanks
Yogish
|||That will only show whether the table *has* an identity column, not whether IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USEROPTIONS, but that doesn't
expose the information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish
|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
Thanks
Yogish
|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SET
IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
Server? returns an error message that states SET IDENTITY_INSERT is already
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
Thanks
Yogish
|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row in
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:

> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
> Server? returns an error message that states SET IDENTITY_INSERT is already
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>

How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. Whe
n
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT ha
s
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a tabl
e?
Thanks,
TomHi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
Thanks
Yogish|||That will only show whether the table *has* an identity column, not whether
IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USERO
PTIONS, but that doesn't
expose the information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
Thanks
Yogish|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SE
T
IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
Server? returns an error message that states SET IDENTITY_INSERT is alread
y
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
Thanks
Yogish|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but
I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row i
n
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:

> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a
SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
> Server? returns an error message that states SET IDENTITY_INSERT is alre
ady
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40
))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perfor
m
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>

How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. When
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT has
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a table?
Thanks,
TomHi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
--
Thanks
Yogish|||That will only show whether the table *has* an identity column, not whether IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USEROPTIONS, but that doesn't
expose the information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
--
Thanks
Yogish|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SET
IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL
Serverâ?¢ returns an error message that states SET IDENTITY_INSERT is already
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
--
Thanks
Yogish|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row in
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:
> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL
> Serverâ?¢ returns an error message that states SET IDENTITY_INSERT is already
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>

How to define an object in common place instead of every SP in SQL Server

hi,

I want to define a linked server object in one common place instead of every SP. (Because the UAT DB or SIT DB maybe the linked server with different name)

For Oracle we can define one common object in Package like following:

CREATE OR REPLACE PACKAGE FMS_PKG IS

TYPE T_CURSOR IS REF CURSOR;

END FMS_PKG;

Then we can use it in SP like FMS_PKG.T_CURSOR. So I wonder whether SQL Server 2005 has corresponding place can do this.

Thanks

Bily Jiang

Assuming that your Linked Server is another instance of SQL Server then you could create a Linked Server, named, for example, 'MyLinkedServer', and reference this in your stored procs / Views etc...

You can then use the CliConfg.exe application to create an Alias on the server hosting your primary SQL Server instance. Name the Alias 'MyLinkedServer' and specify the Server Alias (the instance of SQL Server to which your Linked Server should point) as appropriate to your environment.

This way you do not have to make changes to the code when you progress through each of the environments - all you have to do is update the Server Alias on the server and any Linked Server security settings on the primary SQL Server instance.

Chris

|||

Yes, I have found this tool.

Could you tell me how to use this tool to create one Linked Server Alias?

After I open it, I click Alias tab --> click Add --> should choose which option?

Thanks

Bily Jiang

|||

It depends on which network protocol you are using.

We use TCP/IP, so I would select 'TCP/IP', enter the Linked Server name in the Server Alias text box (e.g. MyLinkedServer), then enter the name of the server (plus instance name if necessary) that you wish to connect to in the 'Server Name' text box.

Chris

|||If you are using SQL Server 2005 you can create Synonyms for each table, view, procedure or function you are accessing via the linked server. Then you will just use the Synonym name to reference the object on the remote server, not the linked server syntax.|||

Thank you so much.

I feel synonym is the simplest resolution especially when we just access several remoting tables.

But I think package is the advantage of Oracle.

Regards

Ren Jie