June 21, 2008 at 1:11 pm
I am trying to create a linked SS2K db in SS2K5, and I need the link server to automatically convert requests from that SS2K DB into case-insenstive. The goal is to be able to query against the SS2K schema database with a schema name that is in all uppercase (even though the SS2K database is case-sensitive collation).
I've spent two days on this searching all of the internet trying to find some option?
Any ideas?
June 21, 2008 at 3:51 pm
If you create a schema called 'db', then create a synonym for a table in your other database as:
CREATE SYNONYM db.Test FOR linkedserver.Database.dbo.Test;
You can then query it using:
SELECT * FROM db.test;
SELECT * FROM db.Test;
SELECT * FROM db.TEST;
etc...
Is that what you are looking for? You cannot create a schema called DBO because you have a case-insensitive collation in that database.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2008 at 5:53 pm
But I would have to do with for every single table in the database, right? The problem is that there are hundreds of tables. Is there a dynamic way to do this? Perhaps some procedure/function that will generate these schemas automatically whenever there is a query made on that schema? What about a way to create views automatically in the case-insenstive database?
June 21, 2008 at 7:14 pm
Garfi61416 (6/21/2008)
But I would have to do with for every single table in the database, right? The problem is that there are hundreds of tables. Is there a dynamic way to do this? Perhaps some procedure/function that will generate these schemas automatically whenever there is a query made on that schema? What about a way to create views automatically in the case-insenstive database?
Yes. Yes. No, you have to do them ahead of time. And, yes, but I am not sure if it would address your issue or not.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 21, 2008 at 9:51 pm
Here is an example of automating cross-database View creation, off the top of my head:
USE CaseInsensitiveDB;
GO
Declare @sql nvarchar(max)
Set @sql = ''
Select @sql = @sql + '
EXEC(''CREATE VIEW [dbo].[' + TABLE_NAME + '] as
Select *
from CaseSensitiveDB.dbo.[' + TABLE_NAME + '];'');
'
From CaseSensitiveDB.INFORMATION_SCHEMA.TABLES
Where TABLE_SCHEMA = 'dbo'
print @sql
EXEC (@sql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 22, 2008 at 4:33 am
great, that's what I needed. Thanks.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply