Our SQL Server 2005 installation has an active database that periodically gets rolled over to an empty database with a new name. The software that inserts and updates the database is aware of the name of the active database. Query users use a shell database with a static name, and it has views into the active database. All this works fine.
Our question is regarding backup. Is there any way to establish a databse name "alias", so that backup routines can remain unchanged but point to the active database?
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server.  Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects?  Or does Server3 need to have DBInstance1 & DBIstance2?  Basically, is the alias just for the database or for the instance too?  Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that 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!
How would I create a database alias name for the following databasetestingdatabase.comThis database exisist on a remote server. I have tried using the databasename asserver.[testingdatabase.com].dbo.tablewere server is a linked server using sp_addlinkedserver[testingdatabase.com] is the database namedbo is the instantancetable is the name of the table.using select * from server.[testingdatabase.com].dbo.table producing thefollowing error.unspecified errorI cannot change or rename the database for many reason's (Original programerhardcoded 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 / tablesetc.ThanksConrad
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 thescript, I just set up an alias at the top of the script and reference them.TIABen
I have added alias datatypes using sp_addtype and would like to view the properties defined in it. please let me knowhow to view the properties of them and also is there a way to list all alias datatypes existing in the current database along with their properties.
We have an alias for dbo in various code areas, development/test/production. It was easy to tell the DBO alias on release 6.5 but now on 7.0, the only way I can figure out if the userid is aliased to dbo is to sign on as the userid and query 'select user_name( )' and if it returns dbo, I have the answer. Is there an easier way to do this by looking in a system catalog or is there something else I am missing? Thanks in advance. Karen Suenram
In 6.5 you could alias someone as dbo, thus preventing an ownership chain break. In 7.0 there are roles, one of which is db_owner. I gave a user db_owner role, and they created a table. But no one else can see it? And he is listed as the owner in EM. How do I more effectivly mimic the dbo alias available in 6.5?
I am trying to filter my result with HAVING clause. vchEditorName is an alias which I get in the result as a column (it gives me correct values). I want to select only those records which have the value in this field like 'akash'
sql Code:
Original - sql Code
SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName, Case a.enumEditorType WHEN 'A' THEN am.vchFirstName WHEN 'M' THEN u.vchScreenName END as vchEditorName FROM tblArticles a LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID
WHERE a.enumStatus='A' GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName HAVING vchEditorName LIKE '%akash%'
SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName, CASE a.enumEditorType WHEN 'A' THEN am.vchFirstName WHEN 'M' THEN u.vchScreenName END AS vchEditorNameFROM tblArticles a LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID WHERE a.enumStatus='A' GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenNameHAVING vchEditorName LIKE '%akash%'
Hi All, I've just installed an active/active sql cluster. Both are named instance hence if i need to connect to it, I need to use NODE1SQLINST1 and NODE2SQLINST2. Is there a way to just use NODE1 or any other single name to connect to the sql instance? I was looking at Alias but I can't get it working. Anyone has any ideas or suggestion for me? Reason why i want to do this is in an odbc connection, instead of using NODE1SQLINST1, I can use NODE1. Thanks
pls someone help me about adding alias column or column alias
here's my code
select
(select count(*) from dto_client_dtl where dto_client_dtl.testStage is null or (dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'POS')) as 'NoOfPendingTransaction', (select count(*) from dto_client_dtl where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'POS') as 'NoOfConfirmedPositive', (select count(*) from dto_client_dtl where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'NEG') as 'NoOfConfirmedNegative', (select count(*) from dto_client_dtl where dto_client_dtl.testStage is not null and dto_client_dtl.testResult = 'POS') as 'TotalNoOfScreeningPositive', (select count(*) from dto_client_dtl where dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'NEG') as 'TotalNoOfScreeningNegative', (select count(*) from dto_client_dtl where dto_client_dtl.tranStat in ('CCn','TCN')) as 'TotalNoOfCancelledTxns', ('NoOfPendingTransaction'+'TotalNoOfScreeningPositive'+'TotalNoOfScreeningNegative') as 'SubTotal'
hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.
( select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @AssetID union all select DateSent ,TestObjectID from RprRepair where TestObjectID = @AssetID union all select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @AssetID ) AS Der
This works fine alone, but when i put it into union i get an error that no more than one value can be in subqueries.
Hi, In my database one user is aliased like a guest. When i run sp_helpuser on the particular database it is giving following result.
username login name database -------- ----------- -------- aaaa NULL XYZ
NOrmally we fill see like this for GUEST User. Now i want to drop this user 'aaaa' only. IF i use sp_dropuser 'null' i think guest user will also drop from XYZ database. So can anyone pls suggest me how to drop this user. I have to add a same user with full permissions. i know in 65 we have sysalternates table gives all alias information. Is there any table gives the same information in sql7. Pls suggest me regarding this.
I am just getting started with SQL Sever 7.0, most things are must easier and simpler. I do have one mind set problem With SQL 6.5 I created a logon(JAWS) , aliased the developers to the logon(JAWS) and all the tables and other objects were owned by ((JAWS). The developers would then grant access to the tables. And the users would open JAWS.tablename. In the SQL 7.0 test are I take an NT group that have the devlopers in them and allow SQL SErver logon for that group. Then I allow the group access to the database and give the group database roles of db_owner, db_ddladmin, db_datareader and db_datawriter. The developers can create objects but the objects are not owned by the role but are owned by the indivudual NT accounts that are in the NT group. Any hint of what to change to have the ownership show up as the NT group??
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.
how to add a alias to a server? I have gone through BOL where it says it can be done thru right click agent -->> connections and configure alias. i want to know if i configure a alias to a server from a client is it valid for other connections also? meaning there are two database servers A and B i add alias to server A by using client network utilities on server B, then is this alias available if i try to connect to the server A thro some other server c? hope this is not that confusing as it sounds. ;)
I have query related to using of Alias names for the Tables in SQL query, will the performance of the query affected if we use Alias name for tables in joins. considering the fact that there was no need to use alias name in those joins.
Hi all, I have a small argument with friend here regarding use of alias names for tables will affect the performance of the query in case of a large database. Please provide your thoughts on the same.
I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.
Hello guys, I need help in something as I don't know if it is possiblewhat I want.I have a select like this...SELECT Cod1 as SQL, Cod2 as Oracle FROM tableand I need to sort by alias SQL or Oracle as the select is composeddinamically so it could be either Cod1 as SQL or Cod2 as SQL and theuser needs to filter the data using SQL or ORACLE.I need something like this:SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' ANDOracle = 'two'Any ideas?Thank you
HiI tried to use the alias a1 for the column f1 like this, but it fails:create table a (f1 int not null primary key)insert into a (f1) values (2)select f1 a1 from a where a1 = 2Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'a1'.In my real situation f1 is a "pretty complex" calculation, and a1 is analias of that.I wanted to use the alias in the query instead of repeating the calculationseveral times.If this use of alias is not legal, then is there a way to get around this?TIAGunnar VøyenliEDB-konsulent asNORWAY
Hello,I found members of this group very helpful for my last queries.Have one problem with CASE. I can use the column name alias in Order By Clausebut unable to use it in WHERE CLAUSE.PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.QUERY:SELECTM.SECS =CASEWHEN NO_OF_SEC IS NULL THEN -1WHEN NO_OF_SEC =0 THEN 1ELSE NO_OF_SECENDFROM DOWNLOAD_MASTER MWHERE M.SECS < 100ORDER BY M.SECSHoping for a immediate reply.thanks in advance
Hi, I have a problem which i need to get the NewDate which the NewDate is Generated thru Computation of DateDiff Here's my Statement
SELECT DISTINCT RBUCode, DCO, Prov, District, DATEDIFF(mi, ReportedDate + ' ' + ReportedTime, CompletedDate + ' ' + CompletedTime) / 60 AS NewDate FROM PostedFaultTable WHERE (DCO = 'La Union')
I need to get the Less than 5 Hrs of the NewDate
I tried This but it's not working RBUCode, DCO, Prov, District, DATEDIFF(mi, ReportedDate + ' ' + ReportedTime, CompletedDate + ' ' + CompletedTime) / 60 AS NewDate FROM PostedFaultTable WHERE (DCO = 'La Union') and NewDate <= 5
Is table alias using in a query only for Readablity? Is there any difference in performence between a query with table alias and a query without table alias?
Maybe somebody has asked this question, but I could not find it....
I used SSIS to transfer data from Oracle database to SQL Server 2005 (with Microsoft OLE DB Provider for Oracle). It worked fine with the TNS alias name said oracle1. Then I changed the TNS alias name to oracle2. I could connect to Oracle database with oracle2 using Oracle's tool. But SSIS could not find (or solve) oracle2, if I changed the name back to oracle1 in SSIS, then SSIS worked.
Is there any way to refer to an Alias in a select statement? For example, if I create an expression that sums to fields together and give it the alias TOTAL, and then in another field I want to create and other expresssion that multiplies a field by TOTAL, what is the syntax?
In MS Access the Alias name is surrounded by brackets, but I can't seem to find a way to do this with SQL Server.
We have a SQL 2000 installation on a server called SATURN that has been around for a while. We've installed a new SQL 2005 installation on a server called DEIMOS. Our intention is to move all the databases that are currently on SATURN, to DEIMOS.
I've just been looking at the config settings, and it's suddenly occurred to me that the ability to set Server Aliases might save me quite a bit of work. What I'm thinking is setting a new alias on DEIMOS called SATURN. Then I won't need to go round the various applications changing the connection properties.
So:
Does this sound reasonable? I intend uninstalling SQL 2000 from SATURN, but the server itself will stay - is that ok? What will happen if SQL comes alive on the server SATURN while the alias is in place - effectively there will be 2 servers responding to SATURN. I ask this because my intention is to shut down all the SQL services on SATURN, set up the alias, and once I'm happy with the testing, then uninstall. Just wondering what will happen if for some reason the services were restarted before I finish. Is there anything else I need to consider?Regards, Tim
