Is There A SQL Server Equivalent For Oracle Synonyms?
Jul 9, 2004
I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
All,Oracle 9i provides a "USING" clause option for inner joins, thatallows me to say:SELECT * FROM TBL1 JOIN TBL2 USING KeyColumnassuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirablefor our software make use of, but we also support SQL Server. Thereis no USING option available, andSELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumncauses an ambiguous column error on KeyColumn.Is there any equivalent to this Oracle functionality on SQL Server?KingGreg
View 7 Replies
View Related
Jan 15, 2001
Is there an equivalent to Oracle's ROWNUM in SQL Server. ROWNUM, when added to a select statement as a column - the query would return an automatic counter, numbering each row returned.
View 1 Replies
View Related
Apr 22, 2003
Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks
View 11 Replies
View Related
Sep 2, 2004
Hi,
Can any one tell me is there anything in SQL Server thats equivalent
to Oracle's ROWNUM.
Note that the Identity Property or TOP n will not solve my problem.
I want to asign a sequence no. to each row when its being fetched.
For example if in the emp table there are 2000 rows and I write
the following query in Oracle ,
SELECT rownum , empno, empname FROM emp Where rownum < =3
I get the result like this
Rownum----Empno--------------Empname
------------------------------------------
1-----------2345---------------ABCD
2-----------3334---------------EFGH
3-----------4484---------------IJKL
I know I can limit the output rows in SQL Server by using TOP n. But
I also want to generate a sequence no. The identity property of SQL Server
will not be usefull here because my actaul WHERE clause will be more
complex like WHERE resigndate = '01-jan-2004'
Thanks
Asim Naveed
3
View 2 Replies
View Related
Jul 20, 2005
I am having trouble creating an INSTEAD OF trigger in SQL Server toreplicate a BEFORE UPDATE trigger from ORACLE.Here is a sample of the ORACLE BEFORE UPDATE trigger:CREATE TRIGGER myTRIGGER ON MYTABLEbegin:new.DT := SYSDATE;if :new.NM is NULL then:new.NM := USER;end if;end myTRIGGER;It seems as though I have to jump through hoops in SQL Server AND Icannot come up with correct results.Here is a snippet from SQL SERVER (this is what I figured I needed todo after reading various articles,questions):CREATE TRIGGER myTRIGGER on THETABLEINSTEAD OF UPDATEASSELECT * INTO #MYTABLE FROM INSERTEDUPDATE #MYTABLE SET DT = GETDATE()UPDATE #MYTABLE SET NM = USER WHERE NM IS NULLUPDATE THETABLESETDT = (SELECT DT FROM #MYTABLE),NM = (SELECT NM FROM #MYTABLE)WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)Can anyone please shed some light on this? Thanks in advance.
View 4 Replies
View Related
Nov 30, 2004
Hi,
Kindly give the SQL Server equivalent for the below Oracle query :
select *from t1, t2, t3 where
t1.t1col1 (+) = t2.t2col1 and
t2.t2col1 (+) = t3.t3col1
Thanks,
Sam
View 14 Replies
View Related
Feb 2, 2005
The syntax for Oracle's INSTR function is
instr (string1, string2, [start_position], [nth_appearance])
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.
nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.
In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)
Do anybody know the solution for this ????
View 1 Replies
View Related
May 5, 2006
Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
View 14 Replies
View Related
Sep 23, 2006
hi,
There is no direct equivalent to Oracle's rownum or row id in SQL Server
there is any possiblity are alternate thing is exist then please reply
regards
santosh
View 10 Replies
View Related
May 5, 2006
Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
View 4 Replies
View Related
Jul 31, 2006
Hi All,
I need time in the formate HH:mm AM (ex: 06:25 AM, 08:30 PM)
I have tryed with the following query
"SELECT right(CONVERT( varchar, getDate(), 100),7)"
it is giving time like 6:25 AM, But I need in the format 06:25 AM.
I need exaclty equivalent of following oracle query. TO_CHAR(sysdate,'HH:mm AM')
Please reply me.
Thanks & Regards
Dutt
View 4 Replies
View Related
Dec 21, 2000
i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..
with regards,
vijay.
sql server 7.0 on winnt
pl/sql code on oracle 8.0
View 2 Replies
View Related
Sep 12, 2006
We are in the process of supporting two databases (Oracle 10g, SQL Server 2005) for our application.
I want to know what is the equivalent Tables/Views in SQL Server for the Oracle System tables dba_tab_comments, dba_tab_cols
Thanks in advance
View 4 Replies
View Related
Feb 4, 2006
Hi.I'm a casual sql user. I have found a situation where I need to convert anoracle statement to tsql, one I can just fire off in any sql tool against anms sql server database.I studied the exists statement and I think I understand it somewhat, however Iwas not sure how to get it quite right. If you have an idea and a minute ortwo I'd appreciate any insight or tutorial.insert into authorization (program, optiontitle, usergroup, authorizationid)select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextvalfrom allgroups where exists (select * from authorizationwhere authorization.USERGROUP = allgroups.USERGROUP andauthorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')I believe that because in my data, three values of usergroup from allgroupsreturn true from the exists, that this is supposed to insert three rows intoauthorization.But I can't figure out what to do about the authorization.nextval.. I triedvarious max(authorization)+1etc but nothing seemed to compile/workthanksJeff Kish
View 6 Replies
View Related
Dec 18, 2001
Hi Guys,
Anyone knows if there is a SQL equivalent of Oracle SET TIMING ON to give the excution time in milli/centi Seconds?
Cheers,
xiaobing
View 1 Replies
View Related
Jul 1, 2004
Hi all,
I'ld like to get the last record inserted into my DB. In oracle I use:
select * from <tabella>
where rowid = (select max(rowid) from <tabella>.
It is an equivalent of rowid in MS SQL?
Thanx
TT
View 2 Replies
View Related
Nov 8, 2004
Haii Friends,
Is there any equivalent for Oracle's NEXT_DAY function in sqlserver.I need it for the following query...
SELECT NEXT_DAY( SYSDATE ,'THURSDAY') FROM DUAL;
ur Help in this regard is really appreciated....
Regards,
Vicky
View 1 Replies
View Related
May 7, 2004
I need to know the SQL Server equivalent for the below Oracle query :
select t.* from table(cast(c1.table1 as create_type_of_table))
The above query will be used in PL/SQLs in ORACLE.
What is the SQL Server equivalent for the above query ?
View 2 Replies
View Related
Nov 30, 2004
Hi,
Could anyone tell me what is the MSSQLServer equivalent of the following Oracle query?.
SELECT v1.cat, v1.gnr, TA1.desccde, v1.type, v1.cde
FROM view1 v1, table1 TA,
table1 TA1, table1 TA2, table1 TA3
WHERE
TA.txtcde (+) = TRIM(v1.cat)
ANDTA1.txtcde (+) = TRIM(v1.gnr)
ANDTA2.txtcde (+) = v1.cde
ANDTA3.txtcde (+) = TRIM(v1.type)
Thanks,
Gopi.
Follow your DREAMS...
View 3 Replies
View Related
Sep 20, 2006
CREATE TABLE [omx].[UserAuthorization]( [MemberAuthorizationRecordIdentifier] [varchar](50) NOT NULL, CONSTRAINT [PK_UserAuthorization] PRIMARY KEY CLUSTERED( [MemberAuthorizationRecordIdentifier] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
View 4 Replies
View Related
Jul 23, 2005
Hello,I would like to know if the equivalent Oracle rownum exist inSQLServer. Here is a sample SQL code to explain what I want to do :selectjobs.name,jobs.job_id,jobs.description,hist.message,hist.step_name,hist.step_id,hist.run_status,hist.run_date,hist.run_time,hist.run_durationfrommsdb.dbo.sysjobs jobs,msdb.dbo.sysjobhistory histwherejobs.job_id=hist.job_idand hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'order byrun_date, run_timeI just want the first or second row returned by this query. In Oracle Ican simply add rownum=1 or rownum=2 in the where clause to obtain thedesired answer. I don't know how to do in SQLServer.Thank in advance,Pierig.
View 4 Replies
View Related
Mar 11, 2008
I know you can start SQLCMD with the -e option to have the SQL code echoed in the output when running. Is there a way to turn this on or off within a script file?
Example:
File1.sql = select 'hi' as HI
go
File2.sql = select 'by' as BY_
go
Runfile.sql =
set (like echo on, -e sign on parm) ? what command
:r File1.sql
set (like echo off, -e sign on parm not used) ? what command
:r File2.sql
want to get output like
select 'hi' as HI (echoed the select statement)
HI
--
hi
(1 rows affected)
BY_ ( did not echo the select statment)
---
by
View 1 Replies
View Related
Apr 25, 2008
Hi all,
I am currently migrating my database from oracle to MSSQL. My problem is that i have encountered the UTL_FILE function in Oracle and i am not finding its equivalent for MSSQL.
Can anyone help me with this plz? Is there any other way of reading and writting OS files in MSSQL?
Regards
Dhiraj
Software Engineer.
View 3 Replies
View Related
Apr 15, 2004
is there a command in ms sql server 2000 equivalent to this oracle table copy command?
create table myTable_bak as select * from myTable;
View 1 Replies
View Related
May 19, 2006
Hi.I need to give my customer an sql file that they can run in query analyzer.All the stuff they need to run is in a set of existing files.I'd like to just tell them to load this file (this is oracle syntax):@file1.sql@file2.sql@file3.sqlis there some way of calling these files (that are in the same dir) from amaster sql file?ThanksJeff Kish
View 2 Replies
View Related
Jun 29, 2015
I'm looking for a "God-script" to search for a given string (case invariant) in all DB objects (esp synonyms) in all DBs on a server?
View 9 Replies
View Related
Apr 21, 2006
From what I'm seeing in MS documentation I would like to use SYNONYM to obtain information from a remote servers tables. I've tried to find some decient documentation on using it, but can't see to find a clear example for remote servers and in my testing I can make the SYNONYM but the conneciton is failing.
Any helpful hints or references would be welcomed.
Thanks,Ken..
View 8 Replies
View Related
Mar 28, 2007
Hi Experts:
I am writing a general API which would fetch synonyms from any database
providers (and would filter for a given schema).
I am using GetSchema method on DBConnection object and it works fine for
oracle.
SQL Server 2005 has a support for synonyms, but the above piece of code does
not work. Is there any API by which i can get the list?.
Thanks
AK
View 1 Replies
View Related
Nov 20, 2006
Hi all,
Just as a curiosity (and thoroughness) thing, why are there synonyms in SQL Server? When I say synonyms, i mean INT and INTEGER, OUT and OUTPUT: These word pairs mean the same thing!
And in some examples they chop and change between the use of these words... It adds to the confusion.
Anyhows I'm curious as to the "why" behind this.
Thanks
Chris
View 3 Replies
View Related
Nov 26, 2004
Hello ,
Where can I download synonyms database ?
Thank you !
View 5 Replies
View Related
Oct 28, 2015
I currently have a 2 node Availability Group that houses some big warehouse type of DB’s. It’s not uncommon to see DB1 populate DB2:
Use db1
Go
Insert into db2.dbo.table_1 values ‘ssfsfsfsf’
This all works as expected. However, we want to split these DB’s away from each other to reduce CPU impact on any one instance. Of course though we don’t want to have to go and change all the connection strings in our SSIS Packages. By default this would now require going to a 4 part naming convention like so: Insert into newServerName.db2.dbo.table_1 values ‘ssfsfsfsf’Again though, we are trying to avoid this. That said I was thinking to use synonyms. This works as expected, until a failover occurs, and the synonym is redirected to the local Read Only version of DB2.So in other words, SQL Server is behaving exactly as it should, but I need a way to work around it.
Example below:
USE [master]
GO
/****** Object: LinkedServer [DB2] Script Date: 10/28/2015 11:43:46 AM ******/
--note this is pointed to the AG Listener name, for failover purposes.
EXEC
master.dbo.sp_addlinkedserver
@server = N'DB2', @srvproduct=N'sqlserver', @provider=N'SQLNCLI',
@datasrc=N'myAGListenerName', @catalog=N'DB2'
[code]....
Again, SQL Server is behaving exactly as it should. However, this synonym is now fairly worthless, as it can't handle failing from one node to the other in the AG
View 2 Replies
View Related
Oct 26, 2006
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
View 1 Replies
View Related
