I'm trying to get a tree structure from a table. Ex : Security table.
How can I accomplish that task in SQL Server?? I will do it using Level and connect by operators in Oracle to accomplish the task.
Your reply in this regard is highly appreciated.
Thanks,
Navy
I am using a Conditional Split Transformation to find incorrect city listings in a specific list of zipcodes. I have the following condition:
LTRIM([PatientCity]) != 'Wichita Falls' && [PatientZip] IN '76301','76302','76305','76306','76307','76308','76309','76310')
I found the && which is the logical equivalent to AND. I now need a logical operator for IN.
Any ideas or approaches on how I may do this?
I use hieararchical tree with parent id.
I want to select all records that are children of one record.
Oracle has Connect by statment, but sql server doesn't provide this.
Does enyone know a sql script for this problem?
I have 2 SQL statements in Visual Basic(with sybase as backend)
1) "set option DBA.MAX_STATEMENT_COUNT = 1069999900"
2) "set option DBA.MAX_CURSOR_COUNT = 1069999900"
And when I migrated the DB from Sybase to SQL server and try to run the vb code it is giving me error in that SQL statement as MS SQL server might not be recognising the above two statements. Is there an equivalent of this in SQL server.
Thanks
Hi
So far, I have only used Access which has an autonumber data type so that in some of my tables the id is automatically generated.
I guess this is a simple question but is there an equivalent data type in sql server?
Thanks in advance.
In sql server 6.5, standard security......
Will i assign the SA rights to some one/login...?
wincy
what is the equivalent of val() function in SQL server?
and cstr() function
Hello MSSQL professionals,
Please tell me the equavalent of the below MySQL query in SQL Server.
create table temp1(variable1 varchar(24) character set latin1 not null default '')
Immediate help will be highly appreciated
In ORACLE, I can use DESCRIBE PS_JOB to see the columns for that record. What is the SQL-Server equivalent?
TIA,
Joe
Hello all,
I'm making a query for this application that is not in the same server where the database is located.
This query I can easily get results when making the query within the server:
USE ADW_Publish;
SELECT distinct b.Subject
FROM dbo.F_Class_Exam a
LEFT OUTER JOIN dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = '$536870994$' AND a.Class_Exam_Type = 'FIN' AND b.Term = '$536870994$'
ORDER BY b.Subject
Here are the details:
Servername: SERVER01
What would an equivalent query be if I apply this to an external application (located outside the server?)
I've gotten some hints to use:
SELECT *
FROM OPENDATASOURCE('MSDASQL', 'DataSource=DataSourceName;UserID=User;Password=SomePassword').DBName.dbo.TableName
But I dont know how to apply the query above to use that..
Any thoughts? Or are there other ways of doing it?
Thanks!
I am migrating a student database from Access to SQL Server. In AccessI have a query that displays grade information (grades are calculatedon a 12-point scale). In the query I average the students' scores andstore it in a column called Avg. I look up and display the equivalentgrade letter using Access' DLookup function from a table calledGradeTable_tbl. Here is how it's built in Access:Grade: DLookUp("[grade_letter]","GradeTable_tbl","[grade_num]= " &Int([Avg]))Here is the structure of the GradeTable_tbl:grade_num grade_letter0 F1 F2 D-3 D......10 B+11 A-12 AHow would I do the same thing in SQL Server? I want my output to besomething like:Student Score1 Score2 Score3 Avg GradeBob 12 10 8 10 B+Nancy 12 11 11 11 A-etc...I appreciate your feedback!-Paul------"You never know enough to know you don't know"
View 3 Replies View RelatedI am trying to convert a complex function from Oracle to SQL Serverand have come across Oracle's Instr() function. I see SQL Server hasCHARINDEX() which is similar, however it does not provide some keyfunctionality I need. Here is an example of the Oracle code:if Instr( sTg , cDelim, 1, 3 ) > 0 thensd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)end if;Has anybody converted anything similar to this within SQL Server? Anyhelp is GREATLY appreciated!Thanks.
View 5 Replies View RelatedAll,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 RelatedHello,
I'm trying to build a integration service package importing data from XML files and directs this data to different MS SQL server 2005 Database tables.
Can someone suggest me what is equivalent(mapping) data type of DT_UI8 in Sql server 2005 for Integration Services.
Or how to consume DT_UI8 fields in SQL server 2005.
Hi,
I've been looking for the equivalent of the MS Access 2003 field caption property in SQL but it appears as though there isn't one.
(The caption property gives a friendly name on form labels rather than the field name)
How would I do this in SQL so I don't have to overtype all the column names? Could it be with Views?
Any pointers appreciated - I'm moving & learning from Access to SQL
Hi,
I have small question regarding ORDER BY clause.
I wanted some value of the fields should come first before other values….Something like:
+----------+----------------------------------------+
| code | name |
+----------+----------------------------------------+
| UK | United Kingdom |
| US | United States |
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |
I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...)
--SQL
SELECT * FROM countries ORDER by code IN ('UK', 'US') desc
But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server???
Though I can create a new field something like sequence and use it to display these countries but I can not do that…
Please advice.
Thanks,
Firoz Ansari
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 RelatedHi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks
There are three columns I need to query and rank, then group and total. I'm using MS SQL server 2000 and it doesn’t have a ranking function like the newer SQL server and oracle has. Does anyone have a clever way to create or simulate a ranking function? Thanks so much!
Jake :confused:
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
Hello Guys!i have been working with oracle with quite a time. No i migrated to sqlserver 2000 and i want to create a trigger on a table.the trigger function has to update the Modification field to getdate()whenever a row is being updated.i tried lots of thingsif anyone can help i would appreciate a lot!Regards
View 2 Replies View RelatedI 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 RelatedI am migrating a classic ASP application that ran on Access to ASP.NET with SQL Server 2005 DB. I am having some trouble getting a query to work. It runs part of our courses section of the web site.
SELECT CREDIT_DATA_LOCCODE.Loc_Code, CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Fiscal, CREDIT_DATA_COURSE.Term, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Status_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Course_Number2, CREDIT_DATA_COURSE.Section_Number, CREDIT_DATA_COURSE.Course_Title, CREDIT_DATA_COURSE.Credits, CREDIT_DATA_COURSE.Credits_Other, CREDIT_DATA_COURSE.Web_Website_link, CREDIT_DATA_COURSE.Web_Description, CREDIT_DATA_COURSE.Web_Instructor_1, CREDIT_DATA_COURSE.Web_Instructor_2, CREDIT_DATA_COURSE.Web_Location, CREDIT_DATA_COURSE.Web_LocCode, CREDIT_DATA_COURSE.Web_Dates, CREDIT_DATA_COURSE.Web_ETV_Dates_Times, CREDIT_DATA_COURSE.Web_ETN_Dates_Times, CREDIT_DATA_COURSE.Web_Times, CREDIT_DATA_COURSE.Enrollment_Limit, CREDIT_DATA_COURSE.Web_Icon_1, CREDIT_DATA_COURSE.Web_Icon_2, CREDIT_DATA_COURSE.Web_Note, CREDIT_DATA_COURSE.Web_Prereq, CREDIT_DATA_COURSE.Web_Special_Message, CREDIT_DATA_COURSE.Web_Alt_Major_Code, CREDIT_DATA_COURSE.Web_Alt_Course_Number1, CREDIT_DATA_COURSE.Web_Alt_Course_Number2, CREDIT_DATA_COURSE.Web_Alt_Section_Number, CREDIT_DATA_COURSE.Deleted, CREDIT_DATA_COURSE.Update_DateFROM CREDIT_DATA_LOCCODE INNER JOIN CREDIT_DATA_COURSE ON CREDIT_DATA_LOCCODE.Loc_Code = CREDIT_DATA_COURSE.Web_LocCode WHERE (CREDIT_DATA_COURSE.Fiscal = N'078') AND (CREDIT_DATA_COURSE.Term = N'10') AND (CREDIT_DATA_COURSE.Status_Code <> 5) ORDER BY CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Section_Number
But when I run this query with the ORDER BY Clause in place against the SQL 5 tables Sheila dumps into I get this Error:
If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0
How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:
SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0
It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.
hey all,
i want to know Equivalent of "LIMIT" of MySQL in SQL Server 2005?
in mysql we can direclty get data using LIMIT clause.
my question is how to do this in sql server 2005?
thanks.
Hi,
I have developed a web database application using ASP and MS Access, however the requirement for hosting the application is that it must use an MS SQL Server database. I converted the database to SQL without any problems, and many features of the application work under SQL Server except the 'add record' function. I realised there isn't an 'autonumber' field in SQL Server (which i use as the primary key for many tables), but an 'int' field. I considered pulling out the latest int from the database, incrementing it manually and adding the new record with this number... i also noticed there is a 'unique identifier' field.
Is there a quick solution for this problem?
Thanks
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
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 ????
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.
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
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.
Hi all....
I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it?
How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function :
CREATE FUNCTION XOR
(
-- Add the parameters for the function here
@A BIT,
@B BIT
)
RETURNS BIT
AS
BEGIN
RETURN NOT @A AND @B OR @A + NOT @B
END
But it didn't compile.
How can I pass boolean values as parameters? any other hint?
Thanks
Jaime