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_addlinkedser ver
[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]

Re: Database alias name

news (cag@digitalinc .net) writes:[color=blue]
> 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_addlinkedser ver
> [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[/color]

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(serve r,
'SELECT * FROM [testingdatabase .com].dbo.table')

This I've tested and it works.

I was also playing with specifying a defalt database to sp_addlinkedser ver,
but I could not get this to work. Yet an other idea, which I did not try, is
to use sp_addlinkedsrv login 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/techinfo/productdoc/2000/books.asp

[Conrad]

Re: Database alias name

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" <sommar@algonet .se> wrote in message
news:Xns93C3100 5EC2BYazorman@1 27.0.0.1...[color=blue]
> news (cag@digitalinc .net) writes:[color=green]
> > 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[/color][/color]
database[color=blue][color=green]
> > name as
> >
> > server.[testingdatabase .com].dbo.table
> >
> > were server is a linked server using sp_addlinkedser ver
> > [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[/color]
>
> 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(serve r,
> 'SELECT * FROM[/color]
[testingdatabase .com].dbo.table')[color=blue]
>
> This I've tested and it works.
>
> I was also playing with specifying a defalt database to[/color]
sp_addlinkedser ver,[color=blue]
> but I could not get this to work. Yet an other idea, which I did not try,[/color]
is[color=blue]
> to use sp_addlinkedsrv login 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[/color]

[Erland Sommarskog]

Re: Database alias name

Conrad (cag@digitalinc .net) writes:[color=blue]
> 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.[/color]

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

SET QUOTED_IDENTIFI ER OFF
go
SELECT * FROM OPENQUERY(REMOT SRV,
"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..Orde rs o
JOIN Northwind..[Order Details] od ON o.OrderID = od.OrderID
JOIN Northwind..Cust omers c ON o.CustomerID = c.CustomerID
JOIN Northwind..Prod ucts 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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[xAvailx]

Re: Database alias name

Conrad:
[color=blue][color=green]
>>I cannot change or rename the database for many reason's (Original[/color][/color]
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 "testingdatabas e.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" <cag@digitalinc .net> wrote in message news:<vi063a6aa 9qb37@corp.supe rnews.com>...[color=blue]
> 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_addlinkedser ver
> [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[/color]