.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

slective order by in sql server 2005 query

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server
except ascending or decending can we define our own choice of order by, like if i set order by hour it display 0(zero) first, but if time start form 1800 then how to display 18 first then 19 then 20 then so on to 5am can any body help in sql 2005 server

View Complete Post

More Related Resource Links

Sql Server 2005 ( remove header information from query output file "Job 'bustd_Daily_Query_Output_1

Dear All,I have scheduled job for querying output & asve it into particuler folder....but in the top of file i getting the message like "Job 'bustd_Daily_Query_Output_13July09' : Step 1, 'Query1' : Began Executing 2009-07-13 16:32:06"I want to remove this & want output in txt file format without above header....RegardsRavendra Cindia

How to query the Active directory using SQL Server 2005

Hi, I am trying to query Active server from Sql Server 2005 and getting error I used following script to create link server EXEC sp_addlinkedserver 'ADSI3', 'Active Directory Services 111', 'ADSDSOObject', 'adsdatasource' and then trying to connect using following statement SELECT   * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=local.mycompany.ca'' WHERE objectCategory=''User'' ') and getting following error An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=local.resound.ca' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". Please let me know if you know How to query the Active directory using SQL Server 2005 Thanks, Raj    Dreaming a world without any war in anywhere

Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration



We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

our client Decided to stop migration, Please some help is there any other alternative.



Tracing of query in sql server 2005

hi friends,

Is it possible that while a query is running in sql server 2005 but cann't be possible to trace it in profiler or in activity monitor.

Thanks in Advance

select query sql server 2005


i have three table

1)    tbl_pages              primary key column is  (intPk_PageID)

2)   tbl_pagecontents   foreign key colum (intPk_PageContentID) reference with tbl_pages

3)   tbl_banners           foreign key colum  (intFk_pageid) reference with tbl_pages

select * from tbl_pages
intPk_PageID   vcr_PageTitle    vcr_PageUrl         int_PageStatus
1                      Contact Us          contactus.aspx             1
2                      News                   news.aspx       

how to store query string in sql server 2005


Hi all,


how to store query string in sql server 20005 

[Sql Server 2005] Need help to query with joins


Hi all :)

I hope anyone save me from madness ...

here is my query

                      t1.date, t1.employeeID, t2.name AS customer,

                     t3.description AS service, t4.total as amount

FROM            Trg_EmployeeCalendar AS t1 LEFT OUTER JOIN
                    EmployeeJobs AS t4 ON t1.employeeID = t4.employeeID AND t1.date = t4.date LEFT OUTER JOIN
                    Customers AS t2 ON t2.id_location = t4.locationID LEFT OUTER JOIN
                    ServiceCategory AS t3 ON t3.id_service = t4.serviceID LEFT OUTER JOIN

With this query, if an employee has two jobs in different l

Executing XML query from SQL 2005 pointing to SQL 2000 server

Occurs with root command.
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= ADODB.Command  Error Description: Item cannot be found in the collection corresponding to the requested name or ordinal.    Error on Line 18.  The step failed.
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= Microsoft OLE DB Provider for SQL Server  Error Description: Line 1: Incorrect syntax near 'ROOT'.    Error on Line 20.  The step failed.
Dim oCmd, sSQL, oDom
   Set oDom = CreateObject("Microsoft.XMLDOM")
   Set oCmd = CreateObject("ADODB.Command")
  oCmd.ActiveConnection ="Driver={SQL Native Client};Database=Validation;Server=server1;Uid=sa;Pwd=;"
   sSQL = "<?xml version=""1.0"" ?>"
   sSQL = sSQL &

How to write a SQL query to present values horizontally sql server 2005

Suppose I have one table called Jobs:

    [JOBID] [int] IDENTITY(1,1) NOT NULL,
    [PARTDESC] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OrderPlacedBy] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [SpecialistName] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Priority] [int] NOT NULL,
    [Symptoms] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CustomerNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ShopNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobType] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobState] [nvarchar](32) COLLATE Latin1_General_CI_AS NULL,
    [JobAddedDate] [datetime] NOT NULL,
    [JobStartedDate] [datetime] NULL,
    [JobFinishedDate] [datetime] NULL,
    [JobShippedDate] [datetime] NULL,
    [RecievedDate] [datetime] NULL

I want to see the specialist name and his jobs IDs horizontally.

ANA 201,502,605,701,774
BEN 102,103,051
JEN 705,401,402,509,409,408

A s

Help converting an Acesss SQL query to SQL Server 2005 query


I am trying to convert this microsoft access query to sql 2005 query. Thank you for taking the time to look.

(This is taking a markup field (MultiplierCode) with a value like 1.45/1.5 and splitting into 2 numbers (1.45 would be the regular markup (based on the pay rate) and the 1.5 would be the overtime markup of the Bill Rate. Then updating orders and assignments based on that.)

UPDATE Order_Root INNER JOIN assignmentroot ON Order_Root.OrderID = assignmentroot.OrderID SET Order_Root.Bill = [order_root]![pay]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1)), Order_Root.OBillRate = [order_root]![pay]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1))*(Right([order_root]![MultiplierCode],Len([order_root]![MultiplierCode])-InStr(1,[order_root]![MultiplierCode],"/"))), Order_Root.DBillRate = [pay]*(Left([order_root]![MultiplierCode],(Len(Left("order_root]![[MultiplierCode]",InStr([order_root]![MultiplierCode],"/"))))-1)*2), assignmentroot.BillRate = [payrate]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1)), assignmentroot.OBillRate = [payrate]*(Left([order_root]![MultiplierCo

SQL Server 2005/2008 Query Monitoring via DMVs


My understanding is that sys.dm_exec_query_stats is for archive aggreated query statistics so I’ve been using the query below to monitor active long running queries and it works fine for single statements – however it seems to return hugely inflated total_elapsed_time values with some procs or functions where multiple statements are run. I’m also interested if anyone know how to find query completion times from DMVs?


Select …<some columns>
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
INNER JOIN sys.dm_exec_sessions AS es
ON es.session_id = er.session_id
INNER JOIN sys.dm_exec_connections AS ec
ON ec.session_id = es.session_id
where er.total_elapsed_time / 1000.0 > @ElapsedThreshold
ORDER BY er.total_elapsed_time DESC;

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

SQL Server 2005 Try and Catch Exception Handling

I'm pretty excited to see that there is some real error handling for T-SQL code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being the addition of TRY..CATCH blocks. Let's take a look:

sql server 2000 vs 2005


i would like to ask what the difference between sql server 2000 and 2005 

How to save Unicode in SQL Server 2005

I am using google transliteration to display the text in Hindi/Tamil

I tried to store the text appear as Hindi from textarea in an nvarchar
field of database (SQL Server 2005) , the text saved as "???????".

After retireving text from the same it display the text as "???????".

Can anyone help me how to store and retrieve the actual data from SQL
Server 2005?

I am using ASP.net,VB.Net and SQL Server 2005

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.


I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

sql server 2005 enterprise edition is required


sql serve 2005 enterprise edition is required to work with microsoft enterprise blocks


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend