Query for second maximum salary from employee table
how to find out second maximum salary from all employee in employee table ? please give me sql query. ...
sql-server-2005
Using the same function twice in a query (SQL Server)
In SQL Server 2005, when I write a query like SELECT m.*, a.price p1, b.price p2 FROM mytable m LEFT JOIN products_table_1 a ON my_hash_function(m.name) = a.hash LEFT JOIN products_table_2 b ON my_hash_function(m.name) = b.hash is my_hash_function(m.name) calculated twice or just once? If twice, how can I use a variable to avoid that?...
Tips for improving performance of DB that is above size 40 GB (Sql Server 2005) and growing monthly by around 3GB
The current DB or our project has crossed over 40 GB this month and on an average it is growing monthly by around 3 GB. Now all the tables are best normalized and proper indexing has been used. But still as the size is growing it is taking more time to fire even basic queries like 'select count(1) from table'. So can u share some more po...
How to Catch Particular DB error in ASP.NET
In my SP (Sql Server 2005) I'm raising an error using Raiserror ('No Records Fetched' , 16 ,1) I want to catch this particular error in ASP.NET.. how do I do it ? ...
SQL query with multiple indexes - SQL server 2000
I use a similar query like this select.....from.. with... (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)... I get the following error Only one list of index hints per table is allowed This seems to be working well with SQL Server 2005. Is it an issue with SQL server? ...
Unselect the previous select
I don't know it may sound weird... but here is my situation... I have to select rows from two tables (Table1) and (Table2) Select * from Table1 Where <SomeCondition> Select * from Table2 Where <SomeCondition> But my constraint is If Table1 / Table2 return 0(zero) Rows... I should not return any results at all. That ie... If 1st sele...
Returning non-printable sybol (enter) to SQL Server from C# via CLR
Hello. I have the following CLR function: [Microsoft.SqlServer.Server.SqlFunction] public static SqlString PrintText(SqlString text) { // Put your code here return new SqlString(text.Value); } And I want to get an enter symbol when passing '\r\n' to it. But insteat I get '\r\n' Could you please tell me w...
Why do multiple WHERE conditions slow query rather than speed it up?
The problem is that the query in question runs very slow when compared to the query run with one or two, rather than all three of its conditions. Now the query. Select Count(*) From SearchTable Where [Date] >= '8/1/2009' AND [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150)) AND FreeText([D...
get the formatted output
Having data as shown below ItemCode SalesPrice PricingLevel ITEM-000001 451.000000 Barons ITEM-000001 432.000000 Guild Is there is a way to get the following output: ItemCode Barons Guild ITEM-000001 451 432 Kind regards, Om ...
Calculate total running time from start/stop timestamps
I have a table containing a number of timestamps per day, they represents start and stop events. ID TimeStamp ---------------------- 1 2008-01-01 07:00:00 1 2008-01-01 08:15:00 1 2008-01-01 10:00:00 1 2008-01-01 11:00:00 1 2008-01-02 10:30:00 1 2008-01-02 12:00:00 I would like to calcuate the total running time per day, like th...
How to find recursively self-joined records from a table.
I've got a simple problem that really has me stumped. I have a master table Table X Table X ID _________ 1 2 3 4 5 I have a join table for Table X that allows records to be self joined. Let's call this JoinTableX JoinTableX RecordAID RecordBID --------- -------- 1 2 (So Record 1 from Table X has a link to Record 2 fr...
IF UPDATE() in SQL server trigger
If there's: IF UPDATE (col1) ...in the SQL server trigger on a table, does it return true only if col1 has been changed or been updated? I have a regular update query like UPDATE table-name SET col1 = 'x', col2 = 'y' WHERE id = 999 Now what my concern is if the "col1" was 'x' previously then again we updated it to '...
Inserting very many rows in to SQL 2005
I have a project where I need to build a two tables in sql 2005 and fill them with data (they are look-up tables for weather a name is a male's name or a female's name) I have 7354 male names to insert and 7776 female names to insert. currently I do it CREATE TABLE #NamesMale ( [FirstName] [varchar](50) NOT NULL, [MaxRank] [smallint...
SQL Server 2005 Stored procedure problem contatenating string with wildcard
I have a lengthy stored procedure that builds a query string. It works fine until I add in a 'LIKE' description field (text) which has a wildcard in it, see below: IF @AdDescription IS NOT NULL IF @AdSection IS NOT NULL BEGIN SET @SQL = @SQL + @Wand + 'na.Section = '' + @AdDescription + ''' SET @W...
SQL Query to get records of parent table that have a list of child records
I have two tables in an MS SQL Server 2005 database, parent and child, where the parent may be related to many child records. [Child.parent_id] is related to [parent.id]. The child table also has column [foo] I need to bring back all records in the parent table where [child.foo] matches on each of one to many parameters. For example I wo...
How to backup transaction log after database backup everyday in SQL server 2005
How to backup transaction log after database backup everyday in SQL server 2005 ...
Need help on rewriting a query that Uses a Cursor
I have a query that looks like this: DECLARE Match_Cursor CURSOR FOR SELECT ID,UserKey,TypeCode FROM [DB1].Table1 as t1 OPEN Match_Cursor FETCH NEXT FROM Match_cursor INTO @ID,@UserKey,@TypeCode; WHILE (@@FETCH_STATUS <> -1) BEGIN INSERT INTO #TempTable SELECT t2.Name, t2.Address, t2.Country, @UserKey, @Typ...
Caching in SSRS before clicking View Report
I know that there is Sql Server Reporting Services uses caching. But, I would like to now if it caches the report before clicking the View Report button i.e. does it already cache the data for the report by merely changing the values of the dropdown Report Parameters. Here is the scenario: I call stored procedures to get the labels a...
C# Applications Dependencies Check
I have an application that i wrote in C# and it does databases. So to run this app on any machine i need .NET 3.5 and SQL Server 2005 installed. Is there a way that when the application starts. It first checks whether SQL Server 2005 and .Net 3.5 exist on the machine or not. If not then it may exit and if they do they may continue. ...
Date Time format problem in sql server
I have an application in asp.net which worked fine untill recently when i changed the datetime format in Regional and Language Settings in Control Panel. The date time format was default when i installed XP. I chose Indian standard time while installing XP. I changed the date time format to dd/MM/yyyy HH:mm:ss. And my application start...