TSQL ? Order By Combination(varchar + Int)
Trying to sort varchar data with values that can be int as well
i.e. 104, 27, AW345, 113, 1M001...
Some brilliant "application specialist" set these up a way long time ago, now I have to deal with it. How would someone separate these values to have the "numeric-like" data sort in numerical order, while the character values sort and group together as text? The app is a result of running data from a legacy system in a sql7 driven e-commerce solution.
View Complete Forum Thread with Replies
Related Forum Messages:
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View Replies !
How To Order By Varchar
Dear all, I have a varchar field which have data: ID_trans --------- T1 T10 T11 T2 T3 everytime i use select statement with order by, the result is above Does somebody know..? HOw to make the result like below ID_trans --------- T1 T2 T3 T10 T11 Sorry i'm not good in english... Thanks
View Replies !
ORDER BY &&<VarChar Field&&>
Hi group, I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar. Question: Is it possible to ORDER THEM as if they where of type DateTime? EG select [year], [month] from tbl_WeightedAverageGenerated where [Year] = 2006 ORDER BY [Month] Returns: 2006, 10 2006, 11 2006, 12 2006, 5 2006, 6 etc... I need it to return: 2006 5 2006 6 2006 7 2006 8 2006 9 2006 10 2006 11 2006 12 Is this possible....and how?? TIA Regards, SDerix
View Replies !
HOw To Order Fields Of Type Varchar?
I have a varchar field which holds IDs like (1, 3, 5, 19, 23) when I order it, i get it ordered in ASCII order like (1, 19, 23, 3, 5) rather than (1, 3, 5, 19, 23) Even if I convert it to int, I won't be able to order it. is there any way I can order a varchar field numerically? Angel
View Replies !
Select As VarChar But Order By DateTime
Hello, I'm trying to CONVERT some DateTime column to VarChar in the SELECT part but still do a DateTime ORDER BY while using a UNION. In other words, does anyone know how to make this work: create table #temp1 ( d datetime ) create table #temp2 ( d datetime ) insert into #temp1(d) select '2001-12-12' union select '2002-11-11' insert into #temp2(d) select '2003-10-10' union select '2004-09-09' --works fine------------------------------- select convert(varchar, d, 101) dd from #temp1 order by d ------------------------------------------- --but can't make this work------------------- select convert(varchar, d, 101) dd from #temp1 union select convert(varchar, d, 101) dd from #temp2 order by dd -- wrong type of sorting --order by d-- error --order by cast(dd as datetime) -- error ------------------------------------------- drop table #temp1 drop table #temp2 I would really appreciate your help. Thanks in advance, Thomas
View Replies !
ORDER BY [Date] Isn't Working Since It's Not A DateTime (it's A Varchar!)
Hi everyone. I know, I know, it should have been a datetime from the start...but here's the problem. I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using: ORDER BY [Date] Are there any work arounds for this? Is there some way of doing: ORDER BY covert(datetime, [Date], 103) or something? Cheers Andrew
View Replies !
Correctly ORDER BY Date That Has Been Cast As A Varchar
how do you get it to sort correctly for the date? at the minute its sorting on the characters rather than actual date value Code Snippet INSERT INTO @TempItems (OrderID) SELECT OrderID FROM Orders o INNER JOIN Customers c ON c.CustomerID = o.CustomerID INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID ORDER BY CASE @SortOrder --Order ASC WHEN 0 THEN cast(OrderID as varchar(100)) WHEN 1 THEN cast(c.CompanyName as varchar(100)) WHEN 2 THEN cast(e.FirstName as varchar(100)) WHEN 3 THEN cast(o.OrderDate as varchar(100)) WHEN 4 THEN cast(o.RequiredDate as varchar(100)) WHEN 5 THEN cast(o.ShippedDate as varchar(100)) ELSE '1' END,
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Insert Into Combination
I would like to select data from two different fields within the same row and insert them into a new table in different rows. For example: Source table contains the fields: DateTime, FirstValue, SecondValue Destination table would have the fields: DateTime, Value Sample source data: 2006-01-01T00:00:00, 25.OO, 20.OO 2006-01-02T00:00:00, 28.00, 24.OO Sample destination data (inserted): 2006-01-01T00:00:00, 25.00 2006-01-01T00:00:00, 20.00 2006-01-02T00:00:00, 28.00 2006-01-02T00:00:00, 24.00 Is this doable in T-SQL?
View Replies !
Help Me Getting A Unique Combination From Table
Hi, I have following table BetId GameId ==== ====== 500 108 500 109 501 108 501 109 501 110 502 108 502 109 My query would have the form: select BetId where GameId in(108,109) from Bets then it has to get me BetId : 500 and 502. Not 501,since this is different combination(108,109,110) ;)
View Replies !
How To Get Unique Combination Of Rows
Hi, Following is my table:Bets BetId GameID 500 108 500 109 501 108 501 109 501 110 502 108 502 109 I want BetId 500 and 502 to be returned as result if i give select criteria where game id = 108,109. Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110). Similarly if i give, select criteria where game id =(108,109,110) it should return BetId 501.not the 500 and 502..which is different combination.. Hope i clarified my problem..pls help me in this regard.Thanks a lot...
View Replies !
Counting Based On A Field Combination
Having a brainfart....I need a query that returns a record count, based on two distinct fields.For example:Order Revision Customer001 1 Bob001 2 Bob002 1 John003 1 John004 1 John005 1 Bob006 1 Bob006 2 BobThe query on the above data should return a count of orders, regardless ofthe revision numbers (each order number should only be counted once).So WHERE Customer = 'Bob', it should return OrderCount = 3TIA!Calan
View Replies !
Create View With Field Combination
Hi there, my situation is I have a table x with 3 filed a nvarchar(100), b smalldatetime, c text(16) . I want to create a view like this: select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str% So, I always get a message error said wrong datatype, how can i do, please help me.
View Replies !
Simple Fiedls Combination Question ???
Dear all, I have the follwing procedure: Code Snippet ALTER PROCEDURE [dbo].[Read_Current_User] -- Add the parameters for the stored procedure here @LoginUserName varchar(50) OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- get current user name SET @LoginUserName=(SELECT [NomosConfig].[dbo].[Users].[UserName] ,[NomosConfig].[dbo].[Users].[Id] ,[NomosConfig].[dbo].[Users].[FirstName] ,[NomosConfig].[dbo].[Users].[LastName] ,[NomosConfig].[dbo].[Users].[Email] FROM [NomosConfig].[dbo].[Users] WHERE [NomosConfig].[dbo].[Users].[LoggedIn]=1 END How can I set my @LoginUserName output parameter to be a combination of fields [NomosConfig].[dbo].[Users].[FirstName] + [NomosConfig].[dbo].[Users].[LastName] ? As a result I should get @LoginUserName = FirstName + ' ' + Lastname Thanks regards serge
View Replies !
CONTAINS And WHERE Clause Combination Taking Too Long
Hi, I have a table with 3 columns and 20 million records. first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000). I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column. Now when i try to search SELECT TOP 20 col1, col3 FROM tbl WHERE col1 = '1234' AND CONTAINS(col3,'"market*"') I am facing following problems 1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second. 2- Some time it show criteria is too complex, although i am only requesting a single word in col3. I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information. Please assist.
View Replies !
GETDATE.In A Combination Of Year And Month
I am trying to calculate the work experiance of an employee. I am using the function DATEDIFF for that. For eg: Code SnippetDATEDIFF(yy,JoiningDate,GETDATE()) But this will display the date difference in years, if I use mm, instead of yy then I will get the result in months. But I want to dislay the result as a combination of year and month, like if an employee has 13 months of experiance then i want to get the result as 1.1 years. Is that possible using SQL? Please help. All your helps will be appriciated.
View Replies !
SQL Help Needed - Popular Combination Problem
For a class, I need to write SQL statements for an assortment of problems and I'm stuck on the last few: What is the most popular combination of 2 products that are ordered simultaneously? The most popular combination is defined as follows: Consider any combination of two products such as (LS200A, ATM50A). Looking at all orders, count the number of times these two products were present in an order. An order could of course have other products in it than just these two, so you are not limited to examining only orders with exactly two products. If you repeat this process for all possible combinations of two products, then that combination which was present the highest number of times in all orders is the most popular combination of two products that were ordered simultaneously. The quantities ordered are not relevant in this context. The result of the query should look something like the following: First Product ----- Second Product ------ Max Number of Times --- LS200A --------- ATM50A ------------------- 7 ----------- I know little to no SQL, just the basics of it and my professor is pretty terrible at teaching it. I'm thinking I'll need to make a virtual table with a CREATE VIEW but I'm not sure where to go after that. I also need to write statements for the most popular combination of three products. If anyone could give me a hand, I would really appreciate it. Thanks a lot.
View Replies !
Primary Key On Combination Of Nullable Fields, At Least One Not-null
I have a case where a table has two candidate primary keys,but either (but not both) may be NULL. I don't want to storea copy of the concatenated ISNULL'ed fields as an additionalcolumn, though that would work if necessary. Instead, I triedthe following (this is a related simplified example, not myreal one):CREATE FUNCTION ApplyActionPK(@IP int = NULL,@DNS varchar(64) = NULL)RETURNS varchar(74) -- NOT NULLASBEGINdeclare @val varchar(74)set @val = str(ISNULL(@IP, 0), 10)set @val = @val + ISNULL(@DNS, '')return @val-- Also tried "return str(ISNULL(@IP, 0), 10)+ISNULL(@DNS, '')"-- Also tried "return ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"-- ... and other things...ENDGOcreate table ApplyAction(-- An action applies to a computerAct varchar(16) NOT NULL,-- The action to applyIP int NULL,-- The computer IP address, orDNS varchar(64) NULL,-- The DNS name of the computerTarget as dbo.ApplyActionPK(ComputerID, DNS), -- PK value-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target))SQL Server always complains that the primary key constraint cannot becreated over a nullable field - even though in no case will the 'Target'field be NULL.Please don't explain that I should store an IP address as a string.Though that would suffice for this example, it doesn't solve myactual problem (where there are four nullable fields, two of whichare FKs into other tables).What's the reason for SQL Server deciding that the value is NULLable?What's the usual way of handling such alternate PKs?Clifford Heath.
View Replies !
Combination Problem SQL Express, Crystal Reports, VB.NET
I do not know where to post this strange problem. I have a perfectly working application, accessing a SQL Express database, displaying data and images. Now I am trying to add a Crystal Report. I add a new connection (OLE DB (ADO)) The connection points to my .MDF database. I drag the fields I want to print to the report, including an image. Now I click the Report Preview. I get a message saying "The database table "Table-Name" can not be found, Proceed to remove this table from the report?" Yes/No. I select NO, and the preview shows up correctly. Happily I continue to execute the program in debug mode. At startup I get errors saying that login failed. To get around this error, I have to close down Visual Studio and then open it up again, running the program without touching the Crystal Report Design. Now the program starts up normaly without problems. Now comes the real strange behavior. I click on my link to request the Crystal report and I get a request to login to the database. I try to login but nothing is accepted. I again close down Visual Studio and open up SQL Server Management Studio Express. I connect to SQL Express and expand "Databases" MY .MDF file is listed but the + sign in front of it is missing and I have to delete the database and attach it again to get it back. Where is this problem occuring????? I have no idea. Please help.
View Replies !
Impossible? Sort On Dynamic Field (in Combination With Row_number)
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View Replies !
Combination Chart With Time Axis In Report Services
Hi please excuse my ignorance but if someone could point me in the right direction i would be grateful. i have produce a chart with some data against date points on x axis. i have another date field which i want to combine with the orignal date axis is this possible . ie table results planned date Acutual date 10 02/03/07 01/03/07 46 03/03/07 03/03/07 60 06/03/07 07/03/07 i want to combine the planned date and actual date as an x axis thanks
View Replies !
Failed Assertion = 'm_activeSdesList.Head () == NULL' (in Combination With Error: 1203)
On our datawarehouse server we are regularly having a 1203 error, causing the sql-server to hang. We get this message in the errorlog: Failed Assertion = 'm_activeSdesList.Head () == NULL'. In the knowledgebase I found a bug description that is very lookalike to our problem. Article: Q240853 FIX: Lock Escalation With Parallel Query May Cause 1203 Error And Server Shutdown *** part of the article *** SYMPTOMS If a lock escalation occurs while running a parallel query, it is possible to encounter error message 1203 as follows: spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd) spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd) spid7 Error: 1203, Severity: 20, State: 1 spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd). spid7 Error: 1203, Severity: 20, State: 1 spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd). The error message included in the error log probably mentions the same lock resource in several of the error messages. Once the error is printed, an assertion message similar to the following is also printed: 1999-08-09 13:15:26.79 kernel SQL Server Assertion: File: <proc.c>, line=1866 Failed Assertion = 'm_activeSdesList.Head () == NULL'. After a dump of the stack for all threads, the server initiates a shutdown of the SQL Server process. ... *** end *** You can find the complete article on: http://support.microsoft.com/support/kb/articles/q240/8/53.asp?LN=EN-US&SD=gn&FR=0 We can't use the workaround, because that would shut out parallelism, which is necessary for the project. There is a fix, but in the article Microsoft says: "A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem.". You understan,d this is not one of my favorite type of fixes... Does anyone have already installed the fix mentioned? Had any problems with it, or did it cause some other troubles? Thx, Kurt De Cauwsemaecker Database Administrator Telepolis Antwerpen
View Replies !
SQL Server 2005 Issue - The Cursor Type/concurrency Combination Is Not Supported.
Hi I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this. Below is the exception from my log file The cursor type/concurrency combination is not supported. com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source) The following is the piece of code where the problem I'm assuming is happening, how can I correct it. varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); Have tried using both JDC v1.1 and 1.2 but of no use.
View Replies !
Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS
I have looked far and wide and have not found anything that works to allow me to resolve this issue. I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package. I get the error: [OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". [OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD". [OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
View Replies !
The Data Types Varchar And Varchar Are Incompatible In The Modulo Operator
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure. Code Block CREATE PROCEDURE [dbo].[sqlOutlookSearch] -- Add the parameters for the stored procedure here @OLIssueID int = NULL, @searchString varchar(1000) = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @OLIssueID <> 11111 SELECT * FROM [OLissue], [Outlook] WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' ELSE SELECT * FROM [Outlook] WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' END And the error I kept getting is: Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18 The data types varchar and varchar are incompatible in the modulo operator. Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21 The data types varchar and varchar are incompatible in the modulo operator. Any help is appreciated.
View Replies !
SSIS - Importing Varchar From Access Into SQL2005 As Varchar
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications. The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question.... I get the following error Validating (Error) Messages Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard) DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference... Any help would be appreciated.
View Replies !
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005 1 -- Full Table Structure select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id 2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
View Replies !
Datatype Question Varchar(max), Varchar(250), Or Char(250)
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this. How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space? Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better? Should I just go with char(250) and watch my db size explode? Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long. Any insight to this would be appreciated.
View Replies !
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following... SQL Server Express edition Visual Studios 2005 Jet 4.0 newest upgrade .Net Framework 2.0 (or should I use 3.0) VS2005 Security upgrade Anything else I need for just creating a database for my VS2005 Visual Basic project? I was trying to use MS Access as my backend db but would like to try SQL Express Thank you, Mark
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number. My table relationship is PurchaseOrder ->PurchaseOrderItem. below is a Stored Procedure that i have wrote in creating a PO: CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT) AS BEGIN INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID) END SET @POno = @@IDENTITY RETURN However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem? CREATE TRIGGER trgInsertPOItem ON PurchaseOrderItem FOR INSERT AS BEGIN 'What do i entered???' END RETURN help is needed asap! thanks!
View Replies !
Find Order By Date Range Or Order Id
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
View Replies !
Recordset's Order And Database's Physical Order?
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
View Replies !
Default Sort Order When Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek: Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons'); then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75)); select * from test_sort order by cast( description as nvarchar(75)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause. The sql generated is as follows SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth If I run sql the results are displayed in proper order but the view only order by first item in order by clause. Has somebody experience same thing? How to fix this issue? Thanks,
View Replies !
Send Mail Task Problem Using A Combination Of ForEach Loop, Recordset Destination, Execute SQL Task And Script Task
OK. I give up and need help. Hopefully it's something minor ... I have a dataflow which returns email addresses to a recordset. I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0. I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0. I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...). The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt. Try ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet) My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task. part number leadtime x 5 y 9 .... Does anyone have any idea what I might be doing wrong? thanks John
View Replies !
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
Order ID For Latest Order For Every Customer
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID. Any ideas? Thanks, Assaf
View Replies !
In-Order/Level Order Etc. Traversal Using CTE
Hi, I have some hierarchical data in a table. Say for example: Parent Child ------------------------ NULL 1 1 2 1 3 2 4 2 5 3 6 3 7 5 8 5 9 7 10 7 11 11 12 11 13 Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13... What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well. with Tree (id) as ( select id from WithTest where parent is null union all select a.id from Tree b join WithTest a on b.id = a.parent ) select * from Tree Any ideas? Thanks.
View Replies !
Difference VarChar(50) And VarChar(500)
Hey, I was doing some research on how SQL stores data on disk. MSDN states that when storring a varchar, only the length of the data itself is used plus two bytes. So, if you store "car" in a VarChar(50) it will take 5 bytes. But when you store "car" in a VarChar(500) it will also take 5 bytes. What is the reason users should define the parameter lenght? Can you use VarChar(8000) whole the time, without any drawback? Thanks
View Replies !
|
TRACKER
