Database alias name

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

Conradnews (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/techin.../2000/books.asp|||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:Xns93C31005EC2BYazorman@.127.0.0.1...
> 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/techin.../2000/books.asp|||Conrad (cag@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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" <cag@.digitalinc.net> wrote in message news:<vi063a6aa9qb37@.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

Database Alias

Hi,
Is there any way to set up an alias for a database within an SQL script?
What I'm trying to acheive is... instead of having -
<DatabaseName>.dbo.table, <DatabaseName2>.dbo.table, etc. - throughout the
script, I just set up an alias at the top of the script and reference them.

TIA
BenPrefix your query with USE...

USE dbname
Select * from tblName

You can also alias table names by putting the alias after the table name in
the from clause...

Select p.firstName, p.lastName from tblPeople p

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Ben" <ben.kinslow.nospam@.baesystems.plznospam.com> wrote in message
news:3fbb5e8e$1@.baen1673807.greenlnk.net...
> Hi,
> Is there any way to set up an alias for a database within an SQL script?
> What I'm trying to acheive is... instead of having -
> <DatabaseName>.dbo.table, <DatabaseName2>.dbo.table, etc. - throughout the
> script, I just set up an alias at the top of the script and reference
them.
> TIA
> Ben|||Thanks a lot... but...
I have a script that accesses two databases throughout. I *guessed* that
you could only have one USE statement in effect at any one point.

I am new to this, so any help is useful.

Jerry Boone <jerry@.antech.biz.killspam> wrote in message
news:N7Nub.1447$5x1.4@.newssvr22.news.prodigy.com.. .
> Prefix your query with USE...
> USE dbname
> Select * from tblName
>
> You can also alias table names by putting the alias after the table name
in
> the from clause...
> Select p.firstName, p.lastName from tblPeople p
>
> --
> Jerry Boone
> Analytical Technologies, Inc.
> http://www.antech.biz
>
> "Ben" <ben.kinslow.nospam@.baesystems.plznospam.com> wrote in message
> news:3fbb5e8e$1@.baen1673807.greenlnk.net...
> > Hi,
> > Is there any way to set up an alias for a database within an SQL script?
> > What I'm trying to acheive is... instead of having -
> > <DatabaseName>.dbo.table, <DatabaseName2>.dbo.table, etc. - throughout
the
> > script, I just set up an alias at the top of the script and reference
> them.
> > TIA
> > Ben|||Also

declare @.tblname varchar(100)
set @.tblname = '<DatabaseName>.dbo.table'
exec ( 'select * from ' + @.tblname)

On Wed, 19 Nov 2003 12:17:44 -0000, "Ben"
<ben.kinslow.nospam@.baesystems.plznospam.com> wrote:

>Hi,
>Is there any way to set up an alias for a database within an SQL script?
>What I'm trying to acheive is... instead of having -
><DatabaseName>.dbo.table, <DatabaseName2>.dbo.table, etc. - throughout the
>script, I just set up an alias at the top of the script and reference them.
>TIA
>Ben

Database Alias

Is there any way in SQLServer (2000) to setup an alias to another SQLServer database?

What I want/need to do is this:

.i have a database for each project but many of the tables schemas/triggers/stored procedures/etc. are common so what I want to do is run one set scripts. I want to essentially set up a project structure so that all specific app databases also include some core stuff.

.for example, consider a Users table (simple example). For two applications we have exactly the same users structure, sprocs, triggers, etc. but we have physically different data. I just want to run the same ddl.

.but the users table has a log associated with it in an associated log database. The trigger (and some historical queries) refer to the explicit log database (App1Log.Users, for example).

.what i'm figuring is that if i could set up an alias to the log database and have the sql code refer to that I'd be able to reuse all the code very neatly:

set logDBAlias = 'App1Log'

insert into logDBAlias.Users etc...

.trouble is I cannot figure out how to alias the log database!

Any ideas?

Thank you.
JohnI don't think this is possible.

In 2005 you can createa a synonym, but a synonym is an alias for an object in a database, not a database.

From BOL:

Synonyms can be created for the following types of objects:

Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate Functions
Replication-filter-procedure
Extended Stored Procedure
SQL Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
View
Table (User-defined)

Database Alias

Hello everyone,
Does anyone know if it is possible to create a database
aliase for an existing database name in SQL Server 2000
Enterprise Edition.Can you give an example of what you would want to do with this? There isn't
a specific ALIAS DATABASE command. But perhaps we can offer a suggestion
that meets your need if we know why you would want to alias the DB name.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Liz" <lrodriguez@.sanantonio.gov> wrote in message
news:06a101c37724$7668e620$a301280a@.phx.gbl...
> Hello everyone,
> Does anyone know if it is possible to create a database
> aliase for an existing database name in SQL Server 2000
> Enterprise Edition.|||We have over 100 users linking to SQL Server via MS Access and oppose to
modifying their odbc connection with new database name I was wondering
if there was a way to create a database alias name.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!