I am migrating a student database from Access to SQL Server. In Access
I have a query that displays grade information (grades are calculated
on a 12-point scale). In the query I average the students' scores and
store it in a column called Avg. I look up and display the equivalent
grade letter using Access' DLookup function from a table called
GradeTable_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_letter
0 F
1 F
2 D-
3 D
..
..
..
10 B+
11 A-
12 A
How would I do the same thing in SQL Server? I want my output to be
something like:
UPDATE 13_Together_Dec_final_ICNs SET [13_Together_Dec_final_ICNs].PL = Right(Nz(DLookUp("PH_LEVEL_02_CD","dbo_DIM_PRODUCT","PIN = '")),2) WHERE ((([13_Together_Dec_final_ICNs].flag)="delete"));
I have a VB.NET Class called 'Approve' with a public function 'CanApprove' which returns a boolean value and accepts an input parameter of 'CurrentUserID' of type integer. I want to use something like this that I have usedin Access with VBA: lngID = Nz(DLookup("PermissionID", "tblPermissions", "ManagerID=" & UserID & " AND CurrentUserID=" & CurrentUserID & " AND Permission='A'"), 0) So that if the person who is currently logged in has permissions over the person whose invoice he/she is viewing to approve, it will return a positive value so that if lngID > 0 then set CanApprove=false or vice versa. How do I do this in ASP.net?
I have a multi value field into which I have placed several different values. I wish to compare a value that is entered by the user with the multi value field, to find if the value entered is there. I am assuming ( and also hoping) that I can use DLookUp to do this. At the moment I get all of the multi values returned by DlookUp and they are separated by commas and the value that I want to find is in there. However this is giving me an error because of the separating commas. How can I isolate/extract the value that I want so that I can make a comparison ? Is it something like MyFieldName.Value?
Does anybody knows how to create a DLOOKUP (dynamic lookup) in SSIS withour writing any kind of script? I need to test records existance in destination from the source before inserting or updating in the destination (if the record exist in destination then update, else insert).
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
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?
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?
I 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.
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
Hello, 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.
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.
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:
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 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.
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'
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
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.
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.
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.
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)
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
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
