Nothing Like 'DESC'
Jan 22, 2004hi
Like Oracle SQL, does SQL server has a 'desc' command which describes the tables of a database?
It would be very helpful instead of using Enterprise Manager
ADVERTISEMENT
How To Get Column Desc..
Oct 15, 2001How and where to get table and column descriptions of all system
table and system objects.....etc.,
How Do I Get DESC Order?
Sep 21, 2007
Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.
How do I keep it in DESC order for viewing? Here's the statement:
SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC
How Do You Do ASC And DESC With Dynamic OrderBy
Mar 21, 2006In the followinf portion of my SQL Statement, I cannot figure out how to specify ASC or DESC for the OrderBy... If I Put OrderDate ASC after the THEN, I get an error and if I put @SortDir after the END I get an error... Can Anyone help with this?
WHERE OrderID IN(SELECT OrderID FROM #rsltTable WHERE ID >= @l_FirstRecord AND ID <= @l_LastRecord)ORDER BYCASE @OrderByWHEN 'OrderDate ASC' THEN OrderDateWHEN 'OrderDate DESC' THEN OrderDateEND
Thank You,Jason
Order By Asc Then Desc Links?
Aug 5, 2007hi, i have headers at the top of my list and would like for people to click the pubs link and it orders the pubs alphabetically descending and when they click again to ascend also to do this with towns and addresses and postcodes?!
can someone please help?
J x
Creating Index In DESC Order
Apr 15, 2003Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.
View 1 Replies View RelatedInserting The Data In DESC Column
Oct 17, 2007I have a column name "DESC" in SQL Server table and I am getting an error when trying to insert data into this column. I cannot rename this column as it's not in my hand.
Please anybody reply me with good solution I am new to SQL.
Thanks
Does DESC/DESCRIBE Have An Equivalent In Transact-SQL
May 7, 2008Hi,
I have been training in SQL on MySQL, and now that I am on TSQL, I can't seem to find an equivalent to the command:
Code Snippet
DESC table_name;--OR
DESCRIBE table_name;
In MySQL, either command returns a list of the columns of the table, as well specifications about these columns (whether or not they can accept NULL values, their default values, etc.)
Sorting GridView With ASC/DESC When Using Stored Procedure???
Feb 19, 2007I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView id="gv" runat="server" AutoGenerateColumns="true" DataSourceID="DS" AllowSorting="true" DataKeyNames="ID"/><asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:CS1 %>" SelectCommand="Foo" SelectCommandType="StoredProcedure" OnSelecting="DS_Selecting"> <asp:Parameter Name="paramA" Type="String" /> <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately: ALTER PROCEDURE Foo @paramA nvarchar(64), @paramB nvarchar(64), @SortColumn nvarchar(16) = 'SearchCount' AS SELECT * FROM Searches ORDER BY CASE WHEN @SortColumn='SearchCount' THEN SearchCount WHEN @SortColumn='PartnerName' THEN PartnerName ELSE ID ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo: ... SELECT * From Searchces ORDER BY CASE /* Keep in mind that CASE short-circuits */ WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC WHEN @SortColumn='SearchCount' SearchCount WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC WHEN @SortColumn='PartnerName' PartnerName WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC ELSE ID END ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?
View 2 Replies View RelatedSorting Desc On Amount Of Blog Comments
Aug 26, 2007The following statement seems to ignore my sort expression....how can I fix this? Select TB.* FROM TblBlogs TB JOIN (select top 20 blogId, count(*) cfrom tblCommentsgroup by blogIdorder by count(*) desc) T20 ON TB.BlogId = T20.BlogId
View 3 Replies View RelatedHow To Find If Index Key Is ASC Or DESC From System Tables?
Apr 29, 2003There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.
Thank you.
Regards,
Anuj Goyal
How To Find If Index Key Is ASC Or DESC From System Tables?
Apr 29, 2003There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.
Thank you.
Regards,
Anuj Goyal
Paging And Dynamic Sort Order (ASC/DESC)
Apr 14, 2007Hi all,I have a SQL statement that allows paging and dynamic sorting of thecolumns, but what I can't figure out without making the SQL a dynamicstring and executing it, or duplicating the SQL statement between anIF and ELSE statement.Following is the SQL statement;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_search]@search VARCHAR( 80 ), @startRow INT = 1, @endRow INT = NULL, @postcode AS CHAR( 4 ) = NULL, @suburb AS VARCHAR( 40 ) = NULL, @stateIdentity AS TINYINT = NULL, @fromLatitude AS REAL = NULL -- latitude the user is located in, @fromLongitude AS REAL = NULL -- longitude the user is located in, @sort TINYINT = 1ASBEGINSET NOCOUNT ON;DECLARE @calculateDistance BIT;SET @calculateDistance = 0;-- get the longitude and latitude if requiredIF ( NOT @postcode IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_postalcodeWHERE(postalcode = @postcode)SET @calculateDistance = 1ENDELSE IF ( NOT @suburb IS NULL AND NOT @stateIdentity IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_localityWHERE(locality = @suburb)AND(stateIdentity = @stateIdentity)SET @calculateDistance = 1END/*ELSE IF ( @fromLatitude IS NULL AND @fromLongitude IS NULL )BEGINRAISERROR( 'You need to pass a valid combination to this storedprocedure, example: postcode or suburb and state identity or longitudeand latitude', 18, 1 );END*/SELECT D1.[row], D1.[totalRecordCount], D1.[classifiedIdentity], D1.[title], D1.[summary], D1.[price], D1.[locality], D1.[state], D1.[postcode], D1.[addedLast24], D1.[dateStamp], D1.[t2Rank], D1.[t3Rank], D1.[tRank], D1.[distance], F.[originalName], F.[extension], F.[uniqueName]FROM(-- derived tableSELECT ROW_NUMBER() OVER ( ORDER BY CASE @sort WHEN 0 THENCAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row, COUNT( * ) OVER() AS totalRecordCount, C.[classifiedIdentity], C.[title], C.[summary], C.[price], C.[locality], C.[state], C.[postcode], CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )THEN 1 ELSE 0 END AS addedLast24, C.[dateStamp]/* , t1.RANK AS t1Rank */, t2.RANK AS t2Rank, t3.RANK AS t3Rank, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS tRank, CASE @calculateDistance WHEN 1 THEN CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) ELSE 0 END AS distanceFROM [tbl_classified] AS CINNER JOINtbl_locality LONC.localityIdentity = L.localityIdentity/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @keyword ) ASt1ON FT_TBL.categoryIdentity = t1.[KEY] */LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @search ) ASt2ON C.classifiedIdentity = t2.[KEY]LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,@search ) AS t3ON C.classifiedIdentity = t3.[KEY]WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) ) != 0) AS D1LEFT OUTER JOINtbl_classified_file CFOND1.classifiedIdentity = CF.classifiedIdentityLEFT OUTER JOINtbl_file FONF.fileIdentity = CF.fileIdentityWHERE( row >= @startRow )AND( @endRow IS NULL OR row <= @endRow )ENDThe part I'm having trouble with is making the sort order in thefollowing line dynamicORDER BY CASE @sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THENCAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude,@fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price ASCHAR( 10 ) ) END ASCany help would be greatly apprecaited.Thanks
View 1 Replies View RelatedProblem With Where Clause When Multiple DESC Indexes
May 12, 2007I have come across a problem executing a select with a multi-part where clause that only shows up if there are multiple indexes on the table. The situation using a simplified table is shown below
create table tblTest( utcTimestamp datetime NOT NULL, testType int NOT NULL)go
insert into tblTest (utcTimestamp, testType) VALUES('6/1/2003 0:0:0', 100)go
Now, without adding any indexes to the table, I can execute the following select and it works fine, returning the single row in 2003:
select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0' and testType = 100go
Furthermore, if I introduce a single descending index on just the utcTimestamp:
CREATE INDEX IX_tblTest_Timestamp ON tblTest (utcTimestamp DESC)go
the search still works.
HOWEVER, if I now introduce another index:
CREATE INDEX IX_tblTest_EntryType_Timestamp ON tblTest ( testType, utcTimestamp DESC)go
the search does **not** return the row.
However, if I change the where clause to remove the test of testType:
select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0'go
it works.
Also, strangely, if I populate the table with a number of records with different dates and execute the following search:
select * from tblTest where utcTimestamp > '1/1/2004 0:0:0' and testType = 100go
I get records from **earlier** than 1/1/2004 (i.e. like the sense of the compare is wrong)
Finally, as I was writing this report, I discovered that all of these problems go away if the DESC is removed from the indexes - so that's my workaround, but it still looks like a bug.
Thanks
Lionel
ORDER BY NOTESDATE DESC, CREATEDBY ASC --Not Sorting Properly
Mar 15, 2008I am using sql statement like SELECT CREATEDBY,FIRSTNAME,BUSINESS,NOTES,NOTESDATE FROM BUSINESS ORDER BY NOTESDATE DESC, CREATEDBY ASC
But NotesDate is sorting descending order, but only sorting based on the date and month not on year
Please help me
ASC/DESC Clustered Index - Will It Make A Difference In This Scenario?
Sep 7, 2007Imagine the following scenario-
Identity(1,1) column ID is primary key and only clustered index key.
Rows will be inserted regularly into this table, hundreds per day.
Queries will be mostly selecting on the most recent records.
In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.
Does the direction of the ID column in the clustered index make a difference?
I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.
I've read this
http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html
but it didn't address (or perhaps didn't need to) this sort of scenario.
The Server Failed To Resume The Transaction. Desc:d200000003
Apr 18, 2007Hi,
We recently migrated our application from SQL Server 2000 to SQL Server 2005 and we also migrated from WAS5 to WAS6.
We are getting the following exception when we call any of the stored procedures.
The server failed to resume the transaction. Desc200000003.
Basically we call these stored procedures frequently using the Java thread scheduler.
We didnt face any problem when we use IBM drivers
I guess this is the problem with the drivers.We use JDBC 2005 driver Version 1.1.
But we didnt face this problems when we use JDBC 2000 driver when we used to connect to SQL Server 2000
The Microsoft says that it has fixed the date problems as well as the transaction problems in the version 1.1.But still are facing the issues.
Can anyone of you please help me in getting the solution for the same?
Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number
Aug 21, 2007I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
OLEDB Destination Error In SSIS Package Not Returning Error Column/desc
Aug 1, 2006I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code.
Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information.
Error Code -1071607685 Error Column 0 Error Desc No status is available.
The only thing I can find for the above error code is
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out.
Any help is appreciated.
Thanks
Derrick
How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)
Mar 28, 2007Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007