Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Linked Server and IDENTITY_INSERT

    I need to change IDENTITY_INSERT on remote server. I use Linked Server and everything is good except that I can not use IDENTITY_INSERT.
    First I try with
    IF (IDENT_SEED('[Server IP].[database].dbo.[Table]') IS NOT NULL )SET IDENTITY_INSERT [Server IP].[database].dbo.[Table] ON
    but I recived error message:
    The object name '....' contains more than the maximum number of prefixes. The maximum is 2.

    Second I try
    to make stored procedure (sp) at Linked Server which have only one line
    IF (IDENT_SEED('[Table]') IS NOT NULL )
    SET IDENTITY_INSERT [Table] ON
    and I called this sp from sp which is on my local SQL Server. Everything is ok but IDENTITY_INSERT is still OFF. Probably there is a store procedure scope after which IDENTITY_INSERT is set automaticly to OFF.

    Any help.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try to use special sp on remote server for executing any command like yours.

    create proc JustDoIt @sqlcommand varchar(8000)
    as
    exec(@sqlcommand)
    go

    exec remote.db.dbowner.JstDoIt 'any your command'

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    "Try to use special sp on remote server for executing any command like yours.

    create proc JustDoIt @sqlcommand varchar(8000)
    as
    exec(@sqlcommand)
    go

    exec remote.db.dbowner.JstDoIt 'any your command'"


    I've already tried something like that. I run that sp on remote server and that's fine, but when I want to insert some values I get error.
    I run
    exec remote.db.dbowner.JustDoIt 'IF (IDENT_SEED(''dbo.Building'') IS NOT NULL ) SET IDENTITY_INSERT dbo.Building ON'
    and then in same sp in which I called this remote sp I want to make insert to that table but I get error message:
    Cannot insert explicit value for identity column in table 'Building' when IDENTITY_INSERT is set to OFF. So, I think that there is a scope (sp scope) or context in which this command (IDENTITY_INSERT) can be set.

    Anything else.
    I appreciate any help.
    Thanks in advance.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by bigor
    "Try to use special sp on remote server for executing any command like yours.

    create proc JustDoIt @sqlcommand varchar(8000)
    as
    exec(@sqlcommand)
    go

    exec remote.db.dbowner.JstDoIt 'any your command'"


    I've already tried something like that. I run that sp on remote server and that's fine, but when I want to insert some values I get error.
    I run
    exec remote.db.dbowner.JustDoIt 'IF (IDENT_SEED(''dbo.Building'') IS NOT NULL ) SET IDENTITY_INSERT dbo.Building ON'
    and then in same sp in which I called this remote sp I want to make insert to that table but I get error message:
    Cannot insert explicit value for identity column in table 'Building' when IDENTITY_INSERT is set to OFF. So, I think that there is a scope (sp scope) or context in which this command (IDENTITY_INSERT) can be set.

    Anything else.
    I appreciate any help.
    Thanks in advance.
    This is working .

    exec MSSQL.testDB.dbo.JustDoIt 'SET IDENTITY_INSERT testDB.dbo.ftable ON
    insert ftable(id,code) values(133,''r'')
    SET IDENTITY_INSERT testDB.dbo.ftable OFF
    '

  5. #5
    Join Date
    Feb 2004
    Posts
    3
    OK,
    That works. But I can not use something like that.
    I want to insert data something like this.
    INSERT table1 (....)
    SELECT .....
    from Table2
    where .....
    and I don not have Table2 in that remote database. I can do it with CURSORS. To can go through second SELECT with CURSOR and do INSERT. But it could be slow. I'll try.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •