Post reply

update through openquery

  • December 19, 2011 at 10:25 am

    #249977

    Hi All,

    I'm trying to do an update through openquery on a linked server. I have been able to do deletes and selects through openquery before but this is the first time I've been trying to update.

    DECLARE @lc_sql NVARCHAR(1500)

    DECLARE @lc_department varchar(12)

    DECLARE @pc_linkedserver varchar(30)

    DECLARE @pc_factory varchar(6)

    DECLARE @pc_company char(1)

    SET @pc_linkedserver = 'AGILtest'

    SET @pc_company = 'A'

    SET @lc_department = '24'

    SET @pc_factory = '2'

    SET @lc_sql='UPDATE OPENQUERY( '+@pc_linkedserver + ', ''SELECT BOM_REQUIRED FROM AP_GENERATE_PICK WHERE Company_Code ='''+ @pc_company +''' AND FACTORY = ''' + @pc_factory + ''' AND BOM_REQUIRED=''Y'' AND DEPARTMENT='''+@lc_department+''''')

    SET BOM_REQUIRED=''N''; '

    PRINT @lc_sql

    EXEC (@lc_sql)

    The code reads as follows when printed out

    UPDATE OPENQUERY( AGILtest, 'SELECT BOM_REQUIRED FROM AP_GENERATE_PICK WHERE Company_Code ='A' AND FACTORY = '2' AND BOM_REQUIRED='Y' AND DEPARTMENT='24'')

    SET BOM_REQUIRED='N';

    I get the error Incorrect syntax near 'A'. I've been looking at this for ages and have no idea what I'm doing wrong.

    I also tried replacing changing the singe quotes to doubles so that it looks like

    UPDATE OPENQUERY( AGILtest, 'SELECT BOM_REQUIRED FROM AP_GENERATE_PICK WHERE Company_Code =''A'' AND FACTORY = ''2'' AND BOM_REQUIRED=''Y'' AND DEPARTMENT=''24''')

    SET BOM_REQUIRED='N';

    When I execute this I don't get an error but 0 rows get updated/effected.

  • December 20, 2011 at 2:24 am

    #1423493

    I should add - the data is there to be updated, it's not that the query is wrong.

  • December 20, 2011 at 4:29 am

    #1423551

    interesting -

    The following returns nothing

    SELECT BOM_REQUIRED,Company_Code,FACTORY,DEPARTMENT from OPENQUERY( AGILtest, 'SELECT BOM_REQUIRED,Company_Code,FACTORY,DEPARTMENT FROM AP_GENERATE_PICK WHERE Company_Code =''A'' AND FACTORY = ''2'' AND (BOM_REQUIRED=''N'' OR DEPARTMENT=''24'')')

    however this does return a value

    SELECT BOM_REQUIRED,Company_Code,FACTORY,DEPARTMENT from OPENQUERY( AGILtest, 'SELECT BOM_REQUIRED,Company_Code,FACTORY,DEPARTMENT FROM AP_GENERATE_PICK WHERE Company_Code =''A'' AND FACTORY = ''2'' AND (BOM_REQUIRED=''N'' OR DEPARTMENT=24)')

    Taking the quotes off the 24 - as far as I know the department is a text field though so don't understand what's going on here. I tried to cast the department col to a varchar but no joy.

    Anyone got any ideas?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply