Post reply

Workaround for returning an nvarchar(max) from linked server SP

  • May 4, 2016 at 6:24 am

    #326792

    Hoping someone can help on this one...

    A previous consultant wrote a 'Change request' system for us which allows us to check-in and group object changes relating to specific change requests. We use this for an ERP implementation we are currently doing. (The new ERP system is lacking functionality in this area)

    He created many SPs which is where the bulk of the functionality is and the whole thing works fine with our Dev, Test and Live DBs on the same SQL server.

    Now we want to seperate Dev and Live on to different SQL Servers and I am running in to a problem with a particular SP which returns an nvarchar(max).

    I am getting the following error:

    Msg 7396, Level 16, State 1, Line 1

    Varchar(max), nvarchar(max), varbinary(max) and large CLR type data types are not supported as return value or output parameter to remote queries.

    The SP resides in the Master DB, I call it using sp_executesql as follows:

    EXEC dbo.sp_executesql @SQL, @params, @DbName = @DbName, @ObjectType = @ObjectType, @ObjectName = @ObjectName, @SQLObjectScript = @SQLObjectScript OUTPUT, @EventSeq = @EventSeq

    This is what is executed:

    ServerName.master.dbo.sp_SCR_GenerateScript 'DB_Name', 'Form', 'Form_Name', @SQLObjectScript OUTPUT, 0

    I think the solution is to put the @SQLObjectScript contents into a temporary table but I am struggling with how to go about it. I am more of a DBA rather than a developer!

    Any help or suggestions would be appreciated.

    We are using SQL 2012, Ent edition.

    Thanks in advance

    David

  • May 4, 2016 at 6:36 am

    #1876556

    Regarding the 'check-in' process – are you using an in-house system for all of this? Or a standard VCS?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 4, 2016 at 6:53 am

    #1876568

    Its completely in house, it generates a script to re-create the object from the Dev DB. The script then gets attached to the SCR it was checked in on and when we promote the SCR to Test and eventually Live, the script is executed to recreate the Object.

    Thanks

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

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