Using bcp to load data from a flat file into a database table will load the records in the exact same order as they are in the flat file; whereas, a DTS package may not load the records into the table in the exact same order as they are in the flat file. Why? ... the table has no keys or indexes ... the DTS package was created with MS SQL 2000 and is now being used in MS SQL 2005 ...
View 14 RepliesHi There
I am having alot of trouble with the order in which messages are being delivered, here is my scenario.
I have a transaction action table :
message 1 - xml schema A.
message 2 - xml schema A.
message 3 - xml schema B.
message 4 - xml schema D.
I have the following SP:
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
AT the target i get message 1 in the queue first them message 2. But then i try this.
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 3
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 4
COMMIT
At the target i get message 4 first it has the lower queuing_order on the queue, somehow it got ont he queue first.
I have tried turning validation off on the mssage types as i thought it may have something to do with the xml, but same problem.
I then tried to do a commit after each message like this:
BEGIN TRAN
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
COMMIT
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
COMMIT
But message 4 still gets on the queue first . WTF is going on ?
The only way i can get this to work is like this.
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
WAITFOR DELAY '00:00:01'
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
Why does this work ? and why do i not have this issue with message 1 and 2? If it has soemthing to do with the xml what is it?
Please help.
I'm having a bit of an issue with a project I'm working on presently. Originally, I was supposed to be importing Excel files placed into a folder with unpredictable names, but consistent format. That was easy enough, and that's handled.
However, the project has now been expanded; I need to also import CSV files, still with unpredictable names (easy), but both the Excel and CSV files can now have unpredictable columns.
For example, I was originally told I'd be working with columns A,B,C,D,E, but now I'm working with files that can have that format, or A,B,F,G,C,D,E, or any variation as such.
For the Excel files, I can easily just do a SELECT * INTO... with a staging table and OPENROWSET, so that's no problem. The CSVs, however, I'm not so lucky with; I can't seem to use OPENROWSET for it. I could use BULK INSERT, but I can't use it in the form of a SELECT * INTO...
Compounding this is that there can be typos in the column headers, which I also just ran into
Am I missing something here? Is there means of easily adapting to the varying column header names and positions?
Hello,
I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.
SELECT * FROM QuestionMaster.
I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.
SELECT Convert(int,RAND()*97)
Thanks in a million.
Sarika
I have the below requirement:
1. Group records according to docno column.
2. Records will sort in desc order. (According to date1 column)
3. In date1 column if more than one date is same than we ll consider the date2 column.
EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.
4. Number column is the expected output column.
docnodate1 date2 Number
d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1
d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2
d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3
d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4
[Code] ....
I need to create a temp table with an identity column based on the contents of a physical table (CONTACTS) that has no key or unique way to identify which record was inserted first,
I can query CONTACTS with no ORDER clause and everything is in the order it was inserted. However, when I create the temp table with a newid() as ID or IDENTITY(1,1) as ID, the duplicate rows as they existsed in the physical table are not inserted into the temp table in the same order.
Is it possible to select records from a physical table into a temp table in the same order while creating a unique field?
Here is how they exist in the physical table:
CLIENTID CHANGEDATE NEWVALUE
------------ ----------------------- ----------------------------------------------------------------
C0HTRA200005 2007-11-19 21:11:57.000 NULL
C0HTRA200005 2007-11-19 21:11:57.000 NULL
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-21 06:46:17.000 Acquisition
C0HTRA200005 2007-11-22 07:47:56.000 Acquisition
Here is how they exist after being inserted into the temp table using DISTINCT:
ID CLIENTID CHANGEDATE NEWVALUE
----------- ------------ ----------------------- ----------------------------------------------------------------
15 C0HTRA200005 2007-11-19 21:07:20.000 NULL
16 C0HTRA200005 2007-11-19 21:10:52.000 NULL
17 C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
18 C0HTRA200005 2007-11-19 21:11:57.000 NULL
19 C0HTRA200005 2007-11-21 06:46:17.000 Acquisition
20 C0HTRA200005 2007-11-22 07:47:56.000 Acquisition
I've tried this many different ways using variations of ORDER BY/ no ORDER BY.
I've tried creating the temp table first with the IDENTITY field, then insert into it.
I've also tried SELECT INTO syntax while creating the IDENTITY field with the select. I've done this with newid() as well.
Any ideas would be huge.
Thanks much.
Hi,
I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....
DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)
INSERT INTO @Temp
SELECT i.ID, i.Name, Type = 'I' FROM Item i
UNION
SELECT p.ID, p.Name, Type = 'P'FROM Package p
SELECT * FROM @Temp
ORDER BY Name ASC
I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?
Many thanks for any help! :)
We have one single hash (#) table, in which we insert data processingpriority wise (after calculating priority).for. e.g.Company Product Priority Prod. QtyProd_Plan_DateC1 P11100C1 P22 50C1 P33 30C2 P11200C2 P42 40C2 P53 10There is a problem when accessing data for usage priority wise.Problem is as follows:We want to plan production date as per group (company) sorted order andpriority wise.==>With less data, it works fine.==>But when there are more records for e.g. 100000 or more , it changesthe logical order of dataSo plan date calculation gets effected.==Although I have solved this problem with putting identity column andchecking in where condition.But, I want to know why this problem is coming.If anybody have come across this similar problem, please let me knowthe reason and your solution.IS IT SQL SERVER PROBLEM?Thanks & Regards,T.S.Negi
View 5 Replies View RelatedI have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.
I have data in both table like
CompanyTable
ID Name
1 name1
2 name2
OrderTable
OrderId CompanyTabID
1 1
2 1
3 1
4 1
In my query I want to show all orders in single row.
ID Name Orders
1 name1 1,2,3,4
2 name2 null
Is anybody can help on it.
Thanks
Arvind
Hi,
I have few views in SQL Server 2005. In Design View, the results of View are ok. In OPEN VIEW option, records are not sorted correctly, ORDER BY is ignored.
What could be the reason for this ?
Thanks a lot in advance!
Hi all,I have a table with this structure:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/4/2007 | M1 | 1/2/2008 | R2 | 1//5/2007 | M2 | 1/6/2007 | RFor each customer ID, I need to retrive only the records with the mostrecent transaction date, and I did:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/2/2008 | R2 | 1/6/2007 | RThe problem is, each customer can make more than one transaction inthe same day (see above). In these cases, I need to retrieve only onerecord per customer, based on the importance of the transaction: F ismore "important" than R which is more "important" than M. So, in theexample above, I would only retrieve transaction F for customer 1 anddisregard transaction R: they were made on the same date, but I ammore interested in F than in R.Do you have any suggestions on how I could achieve this in SQL?I use Microsoft SQL server 2005.Thank you for your help!
View 1 Replies View RelatedI upsized an access database with a key / index on ordernumber and linenumber.
However if I open the table in the Management Studio the records aren't ordered this way (same goes for select * from table) I get:
Ordernumber
Linenumber
200724001
37
200724004
3
200724006
33
200724001
3
200724011
19
200724014
5
200724006
37
200724011
19
200724006
28
Same goes for my crystal reports files, since the records aren't ordered by ordernumber / linenumber all my formulas go bezerk..
Am I wrong thinking the records should be ordered according to the prim. index?
Please help because I don't want to have to change all my 40+ reports to include an "ORDER BY"
Best regards,
Mike
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
Thanks!
CB
I have a table that has the records as shown below
Id From To
1 Delhi Noida
2 Faridabad Kanpur
3 Noida Delhi
4 Kanpur Faridabad
5 Banglore Mumbai
6 G H
7 I J
8 Mumbai Banglore
I want the results in the following order
5 Banglore Mumbai
8 Mumbai Banglore
1 Delhi Noida
3 Noida Delhi
2 Faridabad Kanpur
4 Kanpur Faridabad
6 G H
7 I J
Suppose as in above records There is a complete roundtrip from Banglore to Mumbai and Mumbai to Banglore.
Hi -
I have a SSIS package that dumps data from an internal table to a flat file output using standard data flow tasks. The entire table is output - no special SQL. Most of the time the records are placed in the output file in the same order as the internal DB table, but occasionally the order appears to be more random. When that happens, the record order in the internal table is correct - it is just the output.
I can find no properties that seem to affect this. I would appreciate any hints and advice that anyone can give me. Has anyone else encountered this same problem?
Thanks in advance,
Jim Sweeney
The database consists of the following tables:
create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
Possible result: [URL] ......
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
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
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
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
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!
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
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?
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 2 Replies View RelatedHi,
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?
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,
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?
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
View 4 Replies View RelatedI noticed the StockDate is not sorted in proper order, like ascending order...
Code:
select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc
I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...
The sample data is MM/DD/YYYY...
So, how do I get it ordered propery by Year, Month then Day??
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
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.