Can Create Alias Server1DBInstance1 On Server3DBInstance3 And Assign Objects To That Alias
Nov 24, 2015
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?
View 16 Replies
ADVERTISEMENT
Dec 18, 2014
I have a query from an ACCESS database that I want to move over to SISS, but it will not run and says the syntax is wrong. How to correct the syntax on the query below?
ACCESS Query:
UPDATE PROPERTYOWNERS INNER JOIN PROPERTYOWNERS AS PROPERTYOWNERS_1 ON PROPERTYOWNERS.gislink = PROPERTYOWNERS_1.gislink SET PROPERTYOWNERS.mixedownership = "MIXED"
WHERE (((PROPERTYOWNERS_1.secondaryclass)<>[PROPERTYOWNERS]![secondaryclass]));
View 6 Replies
View Related
Sep 16, 2014
I want to create column name dynamically in select list.
create table #temp(name varchar(10), sale int)
insert into #temp values('john',1000)
insert into #temp values('Mike',500)
insert into #temp values('Abhas',200)
select name,sale as sale from #temp
Now i want change column alias only , not value. I need to concatenate sale with year value of getdate(). i.e. column header concatenation only, not a output value.
so my output would be as
name Sales2014
john 1000
Mike 5000
Abhas 2000
View 4 Replies
View Related
Jan 11, 2008
Hi ppl
I'm building a simple SSIS that t reads data from a flat file and appends it to a table. The problem is that the file has no headers and it has a considerable amount of columns.
I need to make a column mapping between "column 1" in the file and the actual column name in the table.
To avoid doing it one by one, I was thinking on introducing a script in the data pipeline to change the aliases of the columns of the file.
The problem is the script designer is in VB and I'm not proficient with that.
Can any one tell me what is the VB statement to change the name of the column, or educate me on an alternative way to do it?
This was easy in sql2000 but I can't find a way to do it in sql2005
Many Thanks
JR
View 1 Replies
View Related
Mar 30, 2000
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
View 7 Replies
View Related
Jul 1, 1999
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?
View 1 Replies
View Related
Jun 25, 2007
Hi All,
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%'
View 5 Replies
View Related
Apr 21, 2008
I want an alias instead of display the column name for the below query..
select STE = case when STE ='AT'then'01'
when STE ='AT1'then'02'
......
.......
.......
........
else STE
end
from tableinfo
Output for the query
STE
---
01
02
Desired output:
STEDN
-----
01
02
Thanks for the help in advance !!!
View 4 Replies
View Related
Jun 2, 2008
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
Regards
Ken
View 6 Replies
View Related
Aug 15, 2007
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'
from dto_client_dtl
when i run this code the output is
Truncated incorrect DOUBLE value: 'NoOfPendingTransaction'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningPositive'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningNegative'
View 2 Replies
View Related
Oct 5, 2006
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.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Select_Bill_]
@payment_or_bill nvarchar(2),
@spcode nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @fieldname varchar(10) DECLARE @stmt varchar(4000)
DECLARE Fields CURSOR FOR
SELECT Amounttype FROM BILL_Amounttypes
SET @stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'
OPEN Fields
FETCH Next FROM Fields INTO @fieldname
WHILE @@Fetch_Status = 0
BEGIN
SET @stmt = @stmt + ', (select billid_detailed from
bill_Detailed where billid = bill1.billid and
amounttype = ''' + @fieldname + ''''
SET @stmt = @stmt + ') As ' + @fieldname
FETCH Next
FROM Fields
INTO @fieldname END
CLOSE Fields
DEALLOCATE Fields
SET @stmt = @stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)
from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'
SET @stmt = @stmt + ',(Select case when
(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =
Total then ''Yes'' else ''No'' end ) as Processed'
SET @stmt = @stmt + ' From bill_ as bill1'
exec(@stmt)
select (@stmt)
END
View 5 Replies
View Related
Dec 11, 2006
Hi all,
I was able to get my mirroring setup to work only when I use Alias instead of IP address. Any idea why it is so?
Thanks,
Avi
View 3 Replies
View Related
May 18, 2006
How to get more columns within same alias?
(
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.
View 3 Replies
View Related
Jun 27, 2000
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.
thank u
raj
View 1 Replies
View Related
Feb 16, 1999
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??
View 1 Replies
View Related
Mar 1, 2001
Can we do
Select BookNo as Catalog from Books
where Catalog = 12356
I need to find the way to use alias in "where" for very complex query
Is anyone has way around it ?
Thank you
View 1 Replies
View Related
Mar 2, 2005
In SQLServer I can't change my SQL column name.
But I need to see another field name in query tool (Excel)
How can I do that (alias..., description ?
Thanks
View 1 Replies
View Related
Aug 30, 2006
Hello everyone.
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.
View 14 Replies
View Related
Sep 27, 2006
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.
John
View 1 Replies
View Related
May 13, 2004
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. ;)
View 2 Replies
View Related
Dec 5, 2006
Hi ,
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.
Your response is highly appreciated.
Regrads
View 6 Replies
View Related
Mar 27, 2007
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.
Necessity is the mother of all inventions!
View 20 Replies
View Related
Jul 23, 2005
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.
View 5 Replies
View Related
Mar 24, 2006
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
View 4 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
May 13, 2008
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
Regards
View 4 Replies
View Related
May 30, 2008
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?
View 1 Replies
View Related
Jan 22, 2007
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.
Any idea?
Thanks!
View 7 Replies
View Related
Mar 17, 2008
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.
Thanks
View 1 Replies
View Related
Dec 24, 2007
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
View 6 Replies
View Related
