Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Location
    Bangalore
    Posts
    3

    Unanswered: Sql-Server Error

    Hi
    I tried to execute a dynamic query in sql server and got the error below.

    I have used opendata source to connect to server. But the records are inserting into the table.

    ERROR: Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.


    Please help in resolving this issue.




    CREATE PROCEDURE P_DM_INS_SECURITYIDTYPE
    (
    @p_source_dbname_i VARCHAR(100),
    @p_error_dbname_i VARCHAR(100),
    @p_source_con_i VARCHAR(100),
    @p_error_con_i VARCHAR(100)
    )
    AS
    SET NOCOUNT ON
    BEGIN

    DECLARE @v_source_dbname2 VARCHAR(100)
    DECLARE @v_source_dbname1 VARCHAR(100)
    DECLARE @v_vcddescr VARCHAR(1000)
    DECLARE @v_sqlstr AS VARCHAR(8000)
    DECLARE @v_securityidtype VARCHAR(3)
    DECLARE @v_int_errnum numeric(16)
    SET @v_int_errnum = 0


    SET @v_sqlstr = ''
    SET @v_sqlstr = @v_sqlstr + ''
    SET @v_source_dbname1 =''
    SET @v_source_dbname1 = LTRIM(RTRIM(@p_source_dbname_i)) + '.dbo.SECURITYIDTYPE'

    SET @v_sqlstr = @v_sqlstr + ' DECLARE CUS_SECURITYIDTYPE CURSOR FOR SELECT SECURITYIDTYPE FROM '
    + 'OPENDATASOURCE (''SQLOLEDB'',' + '''' + @p_source_con_i +''''+ ').'
    + '' + @v_source_dbname1 + ''

    EXEC(@v_sqlstr)

    OPEN CUS_SECURITYIDTYPE
    FETCH NEXT FROM CUS_SECURITYIDTYPE INTO @v_securityidtype

    WHILE @@FETCH_STATUS = 0
    BEGIN
    --BEGIN TRANSACTION
    SET @v_source_dbname1 =''
    SET @v_source_dbname1 = LTRIM(RTRIM(@p_source_dbname_i)) + '.dbo.SECURITYIDTYPE'
    SET @v_sqlstr = ''
    SET @v_sqlstr = @v_sqlstr + ''

    SET @v_sqlstr = @v_sqlstr + '
    INSERT INTO SECURITYIDTYPE
    (
    SECURITYIDTYPE,
    CLASSIFICATION,
    COUNTRY,
    NUMBERINGSYSTEM,
    MAXLENGTH,
    MINLENGTH,
    PRIORITY,
    ORIGINALNUMSYSTEM
    )

    SELECT
    SECURITYIDTYPE,
    CLASSIFICATION,
    COUNTRY,
    NUMBERINGSYSTEM,
    MAXLENGTH,
    MINLENGTH,
    PRIORITY,
    ORIGINALNUMSYSTEM FROM '
    + 'OPENDATASOURCE (''SQLOLEDB'',' + '''' + @p_source_con_i +''''+ ').'
    + '' + @v_source_dbname1 + '' +
    ' WHERE SECURITYIDTYPE = ' + '''' + @v_securityidtype + ''''

    EXEC (@v_sqlstr)


    FETCH NEXT FROM CUS_SECURITYIDTYPE INTO @v_securityidtype
    END
    CLOSE CUS_SECURITYIDTYPE
    DEALLOCATE CUS_SECURITYIDTYPE

    END

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    sorry, just have to ask this.

    what is the purpose of this code:

    SET @v_sqlstr = ''
    SET @v_sqlstr = @v_sqlstr + ''

    ??

Posting Permissions

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