news (cag@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, sommar@algonet.se

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