Hi,

I am writing a query which moves information between SQL Server 2000
and Postgres 8.1.3.
Before i modified the script it would very occassionally return the
error that my openquery was
"is too long. Maximum length is 128" but 99% of the time it would
function fine.

I had to add in a extra couple of values and now it returns all the
time, I looked up the error
and it is said to occur when your query is 8kb or higher, but i find it
hard to beleive that the
insert section of the sproc that is failing is 8kb + (insert section
listed below)

        -- Insert Into cs_levels & cs_archive table Statement
        SET QUOTED_IDENTIFIER OFF
        SET XACT_ABORT ON

        declare @ins_var1 varchar(20)
        declare @ins_var2 varchar(20)
        declare @ins_var3 varchar(20)
        declare @ins_var4 varchar(20)
        declare @ins_var5 varchar(20)
        declare @ins_var6 varchar(20)
        declare @ins_var7 varchar(20)
        declare @ins_var8 varchar(20)
        declare @ins_var9 varchar(20)
        declare @ins_var10 varchar(20)
        declare @ins_var11 varchar(20)
        declare @ins_var12 varchar(20)
        declare @insert_levels varchar(4000)
        declare @insert_archive varchar(4000)

        set @ins_var1 = @location_id
        set @ins_var2 = @product_code
        set @ins_var3 = @curr_stock
        set @ins_var4 = @salesorder
        set @ins_var5 = @replenish
        set @ins_var6 = ''0''
        set @ins_var7 = ''0''
        set @ins_var8 = @stock_take_date
        set @ins_var9 = @last_stk
        set @ins_var10 = @customer_id
        set @ins_var11 = @device_id
        set @ins_var12 = @bin_id

--        This inserts the information into the cs_levels table on postgres
        SELECT @insert_levels = "INSERT INTO openquery(MARGE,''select
location_id, product_code, curr_stock, stock_for_invoice,
replenish_stock_amount, replenish_flag, salesorder_flag,
stock_take_date, last_curr_stock, customer_id, device_id, bin_id from
consign.cs_levels WHERE 1=0'')
        VALUES (''" + @ins_var1 + "'',''" + @ins_var2 + "'',''" + @ins_var3 +
"'',''" + @ins_var4 + "'',''" + @ins_var5 + "'',''" + @ins_var6 +
"'',''" + @ins_var7 + "'',''" + @ins_var8 + "'',''" + @ins_var9 +
"'',''" + @ins_var10 + "'',''" + @ins_var11 + "'',''" + @ins_var12 +
"'')";
--            print @insert_levels
            exec (@insert_levels)

and returns the error

Server: Msg 103, Level 15, State 7, Procedure sp_cons_pda_00001, Line
130
The identifier that starts with 'INSERT INTO openquery(MARGE,'select
location_id, product_code, curr_stock, stock_for_invoice,
replenish_stock_amount, replenish_' is too long. Maximum length is 128.


I have tried putting function in postgres to do the insert leaving
Openquery to pass the
variables as follows

        declare @ins_var1 varchar(20)
        declare @ins_var2 varchar(20)
        declare @ins_var3 varchar(20)
        declare @ins_var4 varchar(20)
        declare @ins_var5 varchar(20)
        declare @ins_var6 varchar(20)
        declare @ins_var7 varchar(20)
        declare @ins_var8 varchar(20)
        declare @ins_var9 varchar(20)
        declare @ins_var10 varchar(20)
        declare @ins_var11 varchar(20)
        declare @ins_var12 varchar(20)
        declare @insert_levels varchar(4000)
        --declare @check_size varchar(128)

        set @ins_var1 = '020'
        set @ins_var2 = 'PPP9998888G'
        set @ins_var3 = '950'
        set @ins_var4 = '50'
        set @ins_var5 = '0'
        set @ins_var6 = '0'
        set @ins_var7 = '0'
        set @ins_var8 = '2006-08-18'
        set @ins_var9 = '1000'
        set @ins_var10 = '901'
        set @ins_var11 = '00001'
        set @ins_var12 = 'K0978G'

--        This inserts the information into the cs_levels table on postgres
        SELECT @insert_levels = "INSERT INTO openquery(MARGE,
'''hermes.consign.insert_levels''')
        VALUES (' + @ins_var1 + ',' + @ins_var2 + ',' + @ins_var3 + ',' +
@ins_var4 + ',' + @ins_var5 + ',' + @ins_var6 + ',' + @ins_var7 + ',' +
@ins_var8 + ',' + @ins_var9 + ',' + @ins_var10 + ',' + @ins_var11 + ','
+ @ins_var12 + ')";
        print @insert_levels
        exec (@insert_levels)

but it returns the same error (below)

Server: Msg 103, Level 15, State 7, Line 30
The identifier that starts with 'INSERT INTO openquery(MARGE,
'''hermes.consign.insert_levels''')
        VALUES (' + @ins_var1 + ',' + @ins_var2 + ',' + @ins_var3 + ' is too
long. Maximum length is 128.

so i am open to any suggestions on the best way to do this thefull
sproc is posted at the bottom for reffrence.

many thanks
David Phillips


CREATE PROCEDURE dbo.sp_send_postgres (@device_id varchar(20))  AS

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

EXEC ('CREATE PROCEDURE [dbo].[sp_cons_pda_' + @device_id + ']
(
    @device_id varchar(20)
)
AS

DECLARE @min_tble INT
SET @min_tble = (SELECT min(PDAid) FROM cons_pda_' + @device_id + ')

DECLARE @max_tble INT
SET @max_tble = (SELECT max(PDAid) FROM cons_pda_' + @device_id + ')

WHILE @min_tble <= @max_tble

    BEGIN

        DECLARE @setid INT
        SET @setid = (SELECT PDAid FROM cons_pda_' + @device_id + ' WHERE
PDAid = @min_tble)

        -- declared variables for use
        DECLARE @customer_id nvarchar(20)
        DECLARE @location_id nvarchar(20)
        DECLARE @product_code nvarchar(20)
        DECLARE @curr_stock INT
        DECLARE @min_stk INT
        DECLARE @max_stk INT
        DECLARE @last_stk INT
        DECLARE @salesorder INT
        DECLARE @replenish INT
        DECLARE @last_curr_stock INT
        DECLARE @check_date nvarchar(20)
        DECLARE @stock_take_date DATETIME
        DECLARE @free_stock INT
        DECLARE @crom_pos INT
        DECLARE @curr_free_stock INT
        DECLARE @bin_id nvarchar(20)
        DECLARE @final_curr_stock INT
        DECLARE @stk_type_trans INT

        -- the query string into variables for use
        SET @customer_id = (SELECT customer_id FROM cons_pda_' + @device_id +
' WHERE PDAid = @setid)
        SET @device_id = (SELECT device_id FROM cons_pda_' + @device_id + '
WHERE PDAid = @setid)
        SET @location_id = (SELECT location_id FROM cons_pda_' + @device_id +
' WHERE PDAid = @setid)
        SET @product_code = (SELECT product_code FROM cons_pda_' + @device_id
+ ' WHERE PDAid = @setid)
        SET @curr_stock = (SELECT curr_stock FROM cons_pda_' + @device_id + '
WHERE PDAid = @setid)
        SET @stock_take_date = (SELECT stock_take_date FROM cons_pda_' +
@device_id + ' WHERE PDAid = @setid)
        SET @bin_id = (SELECT bin_id FROM cons_pda_' + @device_id + ' WHERE
PDAid = @setid)

        -- the functions in MS SQL that return working values
        SET @min_stk = (SELECT min_level FROM OPENQUERY (MARGE, ''SELECT *
FROM consign.cs_static'') WHERE location_id = @location_id AND
product_code = @product_code)
        SET @max_stk = (SELECT max_level FROM OPENQUERY (MARGE, ''SELECT *
FROM consign.cs_static'') WHERE location_id = @location_id AND
product_code = @product_code)
        SET @free_stock = (SELECT free_stock FROM OPENQUERY (MARGE, ''SELECT
* FROM consign.cs_static'') WHERE location_id = @location_id AND
product_code = @product_code)
        SET @crom_pos = (SELECT crom_ini_stockpos FROM OPENQUERY (MARGE,
''SELECT * FROM consign.cs_static'') WHERE location_id = @location_id
AND product_code = @product_code)
        SET @stk_type_trans = (SELECT stock_method FROM OPENQUERY (MARGE,
''SELECT * FROM consign.cs_customer'') WHERE customer_id =
@customer_id)


        EXEC sp_get_last_archived_stock @location_id, @product_code,
@last_curr_stock OUTPUT
        SELECT @last_curr_stock

            IF @last_curr_stock IS NULL OR @last_curr_stock = '' ''
                SET @last_stk = @free_stock + @crom_pos
            ELSE
                SET @last_stk = @last_curr_stock

            IF @stk_type_trans = 1
                SET @final_curr_stock = @last_stk - @curr_stock

            SET @curr_free_stock = (@last_stk - @crom_pos)

            -- if the archive stock table has no last date for the transaction
its the first transaction and calculates on first date
            -- otherwise it runs on last current stocktake
            DECLARE @free_position INT
            DECLARE @sales_amount INT

            SET @free_position = @last_stk -  @crom_pos
            IF @free_position >= 0
                IF @curr_stock < @max_stk
                    SET @sales_amount = (@last_stk - @free_position) - @curr_stock
                ELSE
                    SET @sales_amount = 0
            ELSE
                SET @sales_amount = @last_stk - @curr_stock

            SET @salesorder = @sales_amount

            -- if current stock is equal or less that the minimum stock level
then a re-order is issued otherwise its set to Zero
            IF @curr_stock <= @min_stk
                SET @replenish = @max_stk - @curr_stock
            ELSE
                SET @replenish = ''0''


            IF @curr_stock <= @min_stk
                SET @curr_stock = @curr_stock + @replenish


        -- Insert Into cs_levels & cs_archive table Statement
        SET QUOTED_IDENTIFIER OFF
        SET XACT_ABORT ON

        declare @ins_var1 varchar(20)
        declare @ins_var2 varchar(20)
        declare @ins_var3 varchar(20)
        declare @ins_var4 varchar(20)
        declare @ins_var5 varchar(20)
        declare @ins_var6 varchar(20)
        declare @ins_var7 varchar(20)
        declare @ins_var8 varchar(20)
        declare @ins_var9 varchar(20)
        declare @ins_var10 varchar(20)
        declare @ins_var11 varchar(20)
        declare @ins_var12 varchar(20)
        declare @insert_levels varchar(4000)
        declare @insert_archive varchar(4000)

        set @ins_var1 = @location_id
        set @ins_var2 = @product_code
        set @ins_var3 = @curr_stock
        set @ins_var4 = @salesorder
        set @ins_var5 = @replenish
        set @ins_var6 = ''0''
        set @ins_var7 = ''0''
        set @ins_var8 = @stock_take_date
        set @ins_var9 = @last_stk
        set @ins_var10 = @customer_id
        set @ins_var11 = @device_id
        set @ins_var12 = @bin_id

--        This inserts the information into the cs_levels table on postgres
        SELECT @insert_levels = "INSERT INTO openquery(MARGE,''select
location_id, product_code, curr_stock, stock_for_invoice,
replenish_stock_amount, replenish_flag, salesorder_flag,
stock_take_date, last_curr_stock, customer_id, device_id, bin_id from
consign.cs_levels WHERE 1=0'')
        VALUES (''" + @ins_var1 + "'',''" + @ins_var2 + "'',''" + @ins_var3 +
"'',''" + @ins_var4 + "'',''" + @ins_var5 + "'',''" + @ins_var6 +
"'',''" + @ins_var7 + "'',''" + @ins_var8 + "'',''" + @ins_var9 +
"'',''" + @ins_var10 + "'',''" + @ins_var11 + "'',''" + @ins_var12 +
"'')";
--            print @insert_levels
            exec (@insert_levels)


--        This inserts the information into the cs_archive table on postgres
        SELECT @insert_archive = "INSERT INTO openquery(MARGE,''select
location_id, product_code, curr_stock, stock_for_invoice,
replenish_stock_amount, replenish_flag, salesorder_flag,
stock_take_date, last_current_stock, customer_id, device_id, bin_id
from consign.cs_archive WHERE 1=0'')
        VALUES (''" + @ins_var1 + "'',''" + @ins_var2 + "'',''" + @ins_var3 +
"'',''" + @ins_var4 + "'',''" + @ins_var5 + "'',''" + @ins_var6 +
"'',''" + @ins_var7 + "'',''" + @ins_var8 + "'',''" + @ins_var9 +
"'',''" + @ins_var10 + "'',''" + @ins_var11 + "'',''" + @ins_var12 +
"'')";
--            print @insert_archive
            exec (@insert_archive)


        SET @min_tble = @min_tble + 1

    END

DELETE FROM cons_pda_' + @device_id + '')
GO

This error occurs when you use double quotes instead of single quotes to
enclose literals and have QUOTED_IDENTIFIER ON (the recommended setting).  I
suggest you use only single-quotes and double-up embedded quotes so that the
code works regardless of the QUOTED_IDENTIFIER setting.  Untested example:

SELECT @insert_levels = 'INSERT INTO openquery(MARGE,''''select
location_id, product_code, curr_stock, stock_for_invoice,
replenish_stock_amount, replenish_flag, salesorder_flag,
stock_take_date, last_curr_stock, customer_id, device_id, bin_id from
consign.cs_levels WHERE 1=0'''')
  VALUES (''''' + @ins_var1 + ''''',''''' + @ins_var2 + ''''',''''' +
@ins_var3 +
''''',''''' + @ins_var4 + ''''',''''' + @ins_var5 + ''''',''''' + @ins_var6
+
''''',''''' + @ins_var7 + ''''',''''' + @ins_var8 + ''''',''''' + @ins_var9
+
''''',''''' + @ins_var10 + ''''',''''' + @ins_var11 + ''''',''''' +
@ins_var12 +
''''')';
--   print @insert_levels
   exec (@insert_levels)

Show quoteHide quote

I don't think you read the error message closely enough.  This has to do
with an identifier (e.g. object name, column name, etc).

Is there any particular reason you are explicitly setting QUOTED_IDENTIFIER
to OFF?  As Dan mentioned, this is not the recommended setting.  And as he
also mentioned, you can fix it by not mistakenly denoting identifiers by
using double quotes.  Change this:

SELECT @insert_levels = "INSERT INTO
....
VALUES (''" + @ins_var1 + "'',''" + @ins_var2 + "'',''" + @ins_var3 +

To this:

SELECT @insert_levels = 'INSERT INTO
....
VALUES (''' + @ins_var1 + ''',''' + @ins_var2 + ''',''' + @ins_var3 +

Your key is that if you copy and paste that line alone to a Query Analyzer
window (or another window that supports syntax highlighting), with the
default settings, the portions of the strings you hardcode should be red,
and the portions that are variables will be black.  It gets more complex
when you nest it but in this case it should hold true.

A

Fuzzydave (Fuzzyg***@gmail.com) writes:
In addition to the other posts, look at
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for some tips to
simplify the quote mess with OPENQUERY.

tough request
How to combine several long strings and then insert them to a text field
Urgent: Help on query
Copy SQL ResultSet to Variable
User-defined Select list