Database alias name

[news]

How would I create a database alias name for the following database
testingdatabase.com
This database exisist on a remote server. I have tried using the database
name as

server.[testingdatabase.com].dbo.table

were server is a linked server using sp_addlinkedserver
[testingdatabase.com] is the database name
dbo is the instantance
table is the name of the table.

using select * from server.[testingdatabase.com].dbo.table producing the
following error.

unspecified error

I cannot change or rename the database for many reason's (Original programer
hardcoded this in a compiled app) don't have access to source code anyway.
I have not found any docs on database alias, found docs for server / tables
etc.

Thanks

Conrad

[Erland Sommarskog]

news (ca*@digitalinc.net) writes:

How would I create a database alias name for the following database
testingdatabase.com
This database exisist on a remote server. I have tried using the database
name as

server.[testingdatabase.com].dbo.table

were server is a linked server using sp_addlinkedserver
[testingdatabase.com] is the database name
dbo is the instantance
table is the name of the table.

using select * from server.[testingdatabase.com].dbo.table producing the
following error.

unspecified error

I investigated the case, and it appears that neither MSDASQL (which
judging from the error message you are using) nor SQLOLEDB handles
this database name correctly.

One workaround is to use OPENQUERY:

SELECT * FROM OPENQUERY(server,
'SELECT * FROM [testingdatabase.com].dbo.table')

This I've tested and it works.

I was also playing with specifying a defalt database to sp_addlinkedserver,
but I could not get this to work. Yet an other idea, which I did not try, is
to use sp_addlinkedsrvlogin to map to a user which has .com database as
its default database.

I should also add that I have reported the problems with your query,
since it is obviously a bug, at least in case of SQLOLEDB.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Conrad]

Thnak you for your quick response.
Your workaround works but I have now hit anther limit the openquery method
only allows fro a 128 char string. This is ok for simple queries but some of
the queries are approx 180 characters.

Thanks...
"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

news (ca*@digitalinc.net) writes:

How would I create a database alias name for the following database
testingdatabase.com
This database exisist on a remote server. I have tried using the database name as

server.[testingdatabase.com].dbo.table

were server is a linked server using sp_addlinkedserver
[testingdatabase.com] is the database name
dbo is the instantance
table is the name of the table.

using select * from server.[testingdatabase.com].dbo.table producing the
following error.

unspecified error
I investigated the case, and it appears that neither MSDASQL (which
judging from the error message you are using) nor SQLOLEDB handles
this database name correctly.

One workaround is to use OPENQUERY:

SELECT * FROM OPENQUERY(server,
'SELECT * FROM

[testingdatabase.com].dbo.table')
This I've tested and it works.

I was also playing with specifying a defalt database to sp_addlinkedserver, but I could not get this to work. Yet an other idea, which I did not try, is to use sp_addlinkedsrvlogin to map to a user which has .com database as
its default database.

I should also add that I have reported the problems with your query,
since it is obviously a bug, at least in case of SQLOLEDB.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Conrad (ca*@digitalinc.net) writes:

Thnak you for your quick response. Your workaround works but I have now
hit anther limit the openquery method only allows fro a 128 char string.
This is ok for simple queries but some of the queries are approx 180
characters.

That seems strange. I can't find any such limitation, and I tried this
statement without problem:

SET QUOTED_IDENTIFIER OFF
go
SELECT * FROM OPENQUERY(REMOTSRV,
"SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Northwind..Orders o
JOIN Northwind..[Order Details] od ON o.OrderID = od.OrderID
JOIN Northwind..Customers c ON o.CustomerID = c.CustomerID
JOIN Northwind..Products p ON p.ProductID = od.ProductID
WHERE (o.OrderDate >= '19960101')
AND (o.OrderDate <= '19990601')
AND (od.UnitPrice >= 10)
AND (od.UnitPrice <= 100)
AND (o.CustomerID = 'ALFKI')
AND (c.CompanyName LIKE 'Alfred' + '%')
AND (c.City = 'Berlin')
AND (c.Region IS NULL)
AND (c.Country = 'Germany')
AND (od.ProductID = 76)
AND (p.ProductName LIKE 'Lakka' + '%')
ORDER BY o.OrderID")

I tried with both MSDASQL and SOLOLEDB.

I will however research if there is some workaround that permits you
to access the remote database without specifying the database name.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[xAvailx]

Conrad:

I cannot change or rename the database for many reason's (Original

programer
hardcoded this in a compiled app)

You may be able to circumvent this by creating an alias thru the SQL
Server Client network utility of "testingdatabase.com" that points to
a different database. Ofcourse this is not optimal if you have many
client machines.

I haven't tried this so don't take my word for it. But you may want to
play around with it...

HTH

BZ

"news" <ca*@digitalinc.net> wrote in message news:<vi************@corp.supernews.com>... How would I create a database alias name for the following database
testingdatabase.com
This database exisist on a remote server. I have tried using the database
name as

server.[testingdatabase.com].dbo.table

were server is a linked server using sp_addlinkedserver
[testingdatabase.com] is the database name
dbo is the instantance
table is the name of the table.

using select * from server.[testingdatabase.com].dbo.table producing the
following error.

unspecified error

I cannot change or rename the database for many reason's (Original programer
hardcoded this in a compiled app) don't have access to source code anyway.
I have not found any docs on database alias, found docs for server / tables
etc.

Thanks

Conrad