Post reply

Referencing Different Schemas without code changes

  • October 6, 2011 at 2:43 pm

    #390562

    I have an application that was ported over from Oracle so it's heavily schema dependant. (JD Edwards-Enterprise One). The problem is with code migration. For each environment the application requires the schemas to differ. So for example in Dev and PROD we have the same table with a different schema (ie. TESTDTA.F1234, PRODDTA.F1234). So you can see if you have a bunch of home grown sql code and stored procs you could not easily promote then without changing code each time. I tried changing the default schema for the service account but that only resolves part of the issue.

    So now the user can select from tables directly without having to specify a schema but stored procedures are still stuck. If you put the stored procedure inside the schema (ie. TESTDTA.usp_someproc) then you don’t need to specify schema within the proc but you still can’t move from DEV to PROD without a code change because the DEV schema is different in PROD.

    Any ideas?


    thanks, ERH

  • October 6, 2011 at 5:57 pm

    #1391534

    I have a similar issue, so I created a table called dbo.DataSources

    Create a column for Environemnt (PROD or DEV)

    and a column for server, schema and tables. You may need some kind of control column, I use the package name, call the values with a sql statement and push them into variables that you use in your package. This way you can also change the schemas after deployment without having to redeploy 200 packages.

  • October 11, 2011 at 3:03 pm

    #1393179

    it looks SYNONYMs may be my best bet for this as you can create then via script for each environment. You can create a "short name" for each table that take the schema requirement out of the query.

    One issue. For some reason when I reference that SYNONYM remotely via a LINKED Server it doesn't seem to be able to find it. I use the same account both locally on the instance and on the LINKED Server but get different results. The error is this on the remote server. Very strange.

    Msg 7357, Level 16, State 2, Line 1

    Cannot process the object ""XXXX"."dbo"."Table1"". The OLE DB provider "SQLNCLI10" for linked server "SQLDEV01" indicates that either the object has no columns or the current user does not have permissions on that object.

    Any ideas


    thanks, ERH

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

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