We've just moved our CRM database from Sybase to SQL Server 2008 r2. The migration went fine. My question relates to the schemas in SQL Server 2008 r2. In the Sybase, all calls for report data came in from various applications with a specific schema (lets call it "CustSchema") so that the SQL calls went something like this: "Select Fld1, Fld2, Fld3 From CustSchema.TblA Where Blah and Blah and Blah;". Unfortunately, I've inherited some of these legacy applications without the source code so I'm not able to just go in and change the calls from "...CustSchema.TblA..." to "...dbo.CustSchema.TblA..." and re-compile and re-deploy the applications. Re-creating them is NOT on the table as it would take months if not years.
Is there a way in SQL Server 2008 to set up an alias, synonym or something that would automatically associate the "CustSchema" to "dbo" on an incoming database request? I've tried a synonym but that won't work on Schemas, only Schema objects (i.e. Tables, Views, etc.). I've also tried changing the default schema on the CustSchema user account to "dbo" but that didn't work either (big surprise).

May be you should create that schema and move these tables in that schema, so your old application will work.

PluralSight Learning Library

It might be worth trying just creating the blank schema.

If a table is not found within the schema its being run under, i believe it will then default to dbo.

Dan

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

No, that's not true. If the SELECT statement explicitly lists the schema, the only way to make it working is to have tables in that exact schema

PluralSight Learning Library

this should do the trick

CREATE SCHEMA CustSchema AUTHORIZATION dbo
CREATE SYNONYM CustSchema.tbla FOR dbo.TblA
select * from CustSchema.TblA

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Hi Frederico,
Thank you for your post. I set up the Schema and Synonym like you specified and it works in the SSMS but the application (web forms) returns the following error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "CustSchema.TblA.CallTypeID" could not be bound.

I'm not averse to creating synonmyms for as many tables as I need to in the database but I need to know they'll work in the application.

Any thoughts?

hum... that "CustSchema.TblA.CallTypeID" looks to be somehow not what you said originally.

that id database.schema.tablename - which is not the same as having schema.tablename hardcoded

If indeed that is the database name, then you will need to create a database with the required name, and then add the synonym there linking to the correct database

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?