The Order Of Insertion Of Rows Into Destination Is Not Same As The Order Of Incoming Rows
Hi ,
i am dealing with around 14000 rows which need to be put into the sql destination.,But what i see is that the order of the rows in the desination is not the same as in the source,
However it is same for smaller number of rows.
Please help ...i want the order to be same.
View Complete Forum Thread with Replies
Related Forum Messages:
Rows Insert Out Of Order
I am currently trying to insert or import some rows into a table and sql server always seems to sort it by one of the columns in a different order that I insert the data. I would appreciate any feedback on this issue. Here is my table structure. columnA columnB columnC columnD columnE columnF columnG char char int int int smallint char it keeps sorting by column F and seperates them by odds and evens. Does any have a clue why this is happening? I am just using these two inserts. insert into tableNAME values('AA', 'A55', 0, 31, 1, 1, 3) insert into tableNAME values('AA', 'A55', 0, 31, 1, 2, 2) These two rows would be seperated by any other rows already contained in the table. If I add more rows. It lumps them by odds and evens. Thanks
View Replies !
Different Rows Order In Result Grid
Hello! I have two similar servers (hardware) with SQL Server 2000 (SP1) on Win2000 (SP2). (sp_helpsort - default) When I run the same SELECT statement from Query Analyzer on each machine I get different rows order in result Grid (the order of rows stays the same for each machine but different for machine1 and machine2) SELECT table1.a, table1.b, table1.c, table1.d FROM table2, table1, table3 WHERE table2.nTestDefnId = 1034 AND table2.nDefnId = table1.nDefnId AND table3.szClinDiscType = 'X' AND table2.nDiscId = table3.nDiscId What is a reason for such behavior and how I can fix it? Thank you in advance, Elena.
View Replies !
Flat File To Table - Rows Out Of Order
Hi, I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table. In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file. All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case. Is this normal?? Thanks
View Replies !
How To Change The Order Of Rows In Datatable Randomly?
Hello! I would like randomly change the order of the rows in my table. Is there any way to do that? I also have a question about random generator. Is it possible to get a repeatable sequence of random numbers between 1 and 10 in T-SQL? (for example 2,7,6,5,8,9,3,2,....each state with the same probability). But i need the same sequence every time i run my procedure. I know this is just a pseudo generator. I tried to use function rand([seed]) and change the seed value, but I got some strange results...(floor(rand([seed])*100)) Thanx for any idea, Z.
View Replies !
MS SQL Query, What's The Default Order The Rows Returned Are Sorted By?
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted? this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
View Replies !
Order Of Rows In The Inserted And Deleted Psuedo Tables
I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers. The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made... So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)? I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct. Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152". FYI, I've lost the debate that such auditing is better handled by the application, not the database server... Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation? Thanks Mike
View Replies !
Order Of Rows And Columns Returned When Reading With Excel ODBC Driver
Hi, I'm reading values from a named range within an Excel spreadsheet using the Excel ODBC driver. If I ask for all columns within a range, using e.g. select * from 'named range', does the driver ensure that the returned rowset has the same column and row ordering as in the spreadsheet? In other words, if a named range on a spreadsheet is the block of cells: name age richard 54 jemima 27 I want to make sure that my returned rowset is not going to be: age name jemima 27 richard 54 I know that proper databases do not guarantee the order of returned values (unless you specify it) but since Excel is a fixed view of data I was hoping that a returned rowset of values would retain their spreadsheet ordering. Thanks, aionaut
View Replies !
To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal
Hi, When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message. How can we handle transactions in SSIS 1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS. Any sort of help would be highly appreciated. Thanks,
View Replies !
Select Records By Order Of Insertion???
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.
View Replies !
Capture System.IO Rows From Incoming File And Insert Into Table
Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be. 1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String) If sIncomingfile <> "" And sOutputDirectory <> "" Then Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None) f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read Dim file As New IO.FileInfo(sIncomingfile) Dim filefs As IO.FileStream = Nothing If file.Exists Then Try filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1 Catch ex As Exception SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1") Application.Exit() End Try End If Dim reader As New IO.StreamReader(filefs) Dim counter As Integer = 0 Dim CurrentFS As IO.FileStream Dim CurrentWriter As IO.StreamWriter Dim extension As String = IO.Path.GetExtension(file.FullName) If extension = ".mnt" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If IsNumeric(Line.Substring(0, 1)) Then Dim Parts() As String = Line.Split(" "c) ' split row into parts If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If End If End While If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() MoveFilesFTP(sOutputDirectory, "mnt") ElseIf extension = ".naf" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If End While If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() MoveFilesFTP(sOutputDirectory, "naf") End If Else 'input file not valid SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1") End If End Sub
View Replies !
DTS - Destination Record Set Is Out Of Order...
Hi, I am using DTS to transfer data from one DB to another on a separate server using the Data Pump Task. My query to return the source recordset is ordered correctly by a datetime field, however the records are inserted into the destination table incorrectly. The first record in my source recordset becomes the last in my destination table. ie. source dataset = row1,row2,row3 but the destination table = row2,row3,row1 Does anyone know why this would be? I'd appeciate reading any ideas anyone has on this. Thanks, M :)
View Replies !
How Do I Order Columns In My Custom Destination Component?
Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas? K. Lyles
View Replies !
Order Of Columns In Flat File Destination
I need to create a number of flat files, all with the same layout and sourced from the the same table, but with different criteria. The first set of (three) flat files file is created out of a simple Conditional Split transformation: If Source Table row number > 40,000 route to File 3; if row number > 20,000, route to file 2, otherwise route to file 1. This gives me 20,000 rows in files 1 & 2 and the remainder in file 3. I also want to create a fourth flat file by joining the Source Table with a sample table and selecting only those rows where the Customer numbers match. I'm currently doing this in two stages: An Execute SQL Task performs the join and inserts the selected rows into a Destination table (identical layout to source table), and then a simple data flow moves the rows from the Destination table into the fourth flat file. My problem is that the order of the columns in the first three flat files is different from the fourth file. I've tried creating the fourth flat file with a single data flow using a Merge Join transformation which didn't work because the tables aren't sorted in the correct sequence, and I couldn't get an OLE DB Command transformation to work either. I'm not sure why the column order of the 4th file should be different seeing as how its contents are sourced from the same Source table, but is there a cunning way of setting this up so that the columns end up in the same order?
View Replies !
OLEDB To Flat File Destination - Comes Out In Wrong Order
Hello, My OLE DB Source is getting data from the following column types: ID varchar(50), Name varchar(100), Date datetime, Currency char(3), Cost numeric(30,10) My OLE DB Source outputs my information in the following order when I click Preview: ID Name Date Currency Cost When I connect the OLE DB Sorce to a Flat File Destination, it comes out in the wrong order.When I examine the "line" between them (Data Flow Path Editor) I get: Currency DT_STR Length: 3 ID DT_STR Lenght: 50 Name DT_STR Lenght: 100 Date DT_DBTIMESTAMP Cost DT_NUMERIC What is the easiest way for me to change this so the Flat File Destination will output my data in the same order as the OLE DB Source: ID Name Date Currency Cost Thank you very much!
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 !
OLE DB Destination - Redirecting Rows
I'm getting some strange error handling behaviour when I tried to redirect rows when an error is encountered at the OLE DB Destination. I enabled the fastload option and set the maximum insert commit size to 10,000. My package is trying to insert 1,000,000 rows. When the row containing bad data is encountered, the entire batch is redirected to a separate table with no table constraints. So I have approx 990,000 rows of good data inserted, and approx 10,000 rows in an error table. However, only 2 of the 10,000 rows are actually bad data. I changed the filter in the data source to reload 5 rows from the 10,000 rows of bad data. The 5 rows include the 2 rows of bad data. The fastload option and max insert commit size at the OLE DB Destination are not changed. When the package completed, 3 rows were inserted to the destination and the 2 bad rows are redirected again. Even though this is the outcome I wanted (only the real bad data is redirected), shouldn't all 5 rows be redirected since they should all be treated as a single batch? My second question is, is there a way to get the best of both worlds - be able to load lots of data as fast as possible and only redirect the rows that are actually bad? Thanks.
View Replies !
SQL Server Destination Not Loading All Rows
I have a SSIS package that transfers data from three SQL Server 7 servers to a SQL Server 2005 database. This package has about 30 different tables it copies. The table structures in the source database and destination tables are identical. About 25 different tables load without any issues. I have about 5 tables that load some nights without a problem. On other nights, the data transfers seem to randomly (though usually the most recent records) ignore some of the data. I have logging turned on and receive no errors. It just appears to stop loading data. I should also mention that I truncate each destination table before begining and each table is loaded from data from each of the 13 source database (I am combining data from 13 regional database for reporting purposes). This is done using a Foreach Loop Container that updates the Server/Region connection string for each region. I am using the OLE DB Source connection to the SQL Server Destination. I have tried as well with the OLE DB Destination with the same result (and no error). I do not do any manipulation to the data on the transfer, but added a "RowCount" transformation between the source and destination and it gives the correct number of rows, but not all the rows get loaded.
View Replies !
Delete Excel Destination Rows
Hey all I am exporting table rows (based on a query) into an excel file but I don't want to append to the file. I would like to delete the rows that were previously added and then add the new data. The file has column headings and I would like these to exist all the time. I know how to export the data but don't know how to delete 'old' data rows from excel. Any guidance will be highly appreciated. Many thanks, Rupa
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 !
SSIS Oledb Destination Not Writing Any Rows
I have a Dataflow task with oledb source that is using SqlCommand to retrieve data and oledb destination to write the source output to a table. I have access to both the source and destination databases. The problem is the destination component is not writing any rows to the destination table eventhough the Source component is returning rows (I can see them in the Preview and the source database table as well). I'm using "Table/View Name from Variable" for destination. The Package executes without any errors but there is no output. Any ideas? Thanks.
View Replies !
Losing Rows From File To Destination Table - Need Troubleshooting Help
I am losing data from time to time and can not figure out where the rows are vanishing to. In essence, I have 5 files that I process. The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work. I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table. A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination. Along the way, I reject some records based on the business rules applied. Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute). Their are no errors being generated. No rows are being rejected to my reject tables either. Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour? I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded. No patterns around volumes for specific source files. There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms. I have noticed I had my final desination load not using fast load. However sometimes the records disappear before even getting to my final staging table which is inserting using fast load. I am going to turn on logging the pipelinerowssent event. Any other suggestions for troubleshooting/tracking down these disappearing records? Thanks
View Replies !
Custom Destination Component Logging - Wrote 0 Rows
I wrote a custom destination component. Everything works fine, except there is a logging message that is displayed that I cannot get rid of or correct. Here is the end of the output of a package containing my component: Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x0 at Data Flow Task, MyDestination: Inserted 40315 rows into C: empfile.txt Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "MyDestination" (9)" wrote 0 rows. SSIS package "Package.dtsx" finished: Success. I inserted a custom information message that contains the correct number of rows written by the component. I would like to either get rid of the last message "... wrote 0 rows", or figure out what to set to put the correct number of rows into that message. This message seems to happen in the Cleanup phase. It appears whether I override the Cleanup method of the Pipeline component and do nothing, or not. Any ideas? public override void Cleanup() { ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "Inserted " + m_rowCount.ToString() + " rows into " + m_fileName, "", 0, ref m_cancel); base.Cleanup(); // or not }
View Replies !
How To Feed Specific Rows From Excel Spreadsheet To OLE DB Destination
Hi, I am in a bit of a quandry. I have an Excel spreadsheet source, but I only want certain rows from the spreadsheet, which will be delineated by a header and footer row (Start and End). Here is my script thus far (see below). My question is, what is the correct way to do this? I guess I am stuck on syntax. i.e.) Do I need to create an OUTPUT BUFFER? Thanks ============================== Sample data: blah Start Frank 1234 1234 1234 Sue 2345 2345 2345 End blah blah ============================== Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Static Section As String 'Set sections If Row.Name = "Start" Then Section = "Start" End If If Row.Name = "End" Then Section = "End" End If If Section = "Start" Then 'GRAB THESE ROWS AND SEND THROUGH THE PIPELINE 'IGNORE ALL OTHER ROWS! 'What syntax goes here? 'Output buffer? End If End Sub
View Replies !
Problems With Deleting Rows In Destination Table When Exporting To Excel
Hi, I export one table to Excel using Export Wizard. Second time I have used this creator I have checked option "Delete rows in destination table" because table already exists in Excel file. During execution I get an error: "Error 0xc002f210: Preparation SQL Task: Executing the query "DELETE FROM 'MyTable'" failed with following error: " and here is my translation from my language: "Deleting data from linked table is not supported by this version of ISAM" I have also saved this package to a file and here is the full connection string to my Excel file: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsprzemekPulpitmyfile.xls;Extended Properties="EXCEL 8.0;HDR=YES"; Few days ago I have been using SQL2000 on the same machine and the same export was working properly. Any suggestions? Thanks. Przemo
View Replies !
Only Create Destination Flat File When There Are Rows Returned From Ole DB Source
Hi, I have a data task with the following requirements: 1) Run query against database to retrieve rows 2) Add header and footer row to the result set. The footer row must contain a count of the records. 3) Write the rows to a fixed width file if there were any data rows I have got to the point that I can create the file (using a set of tasks that includes derived columns, sorts, aggregation and merges). However the file is created regardless of whether there were data rows returned. I can't check the row count before proceeding as this isn't set until the data task ends. And if I try to split them into separate data tasks (so that I can access this variable and perform conditional execution) it becomes harder to access the original rows. Do you have any recommendations on the best way to achieve this? It all seems to be very complex and I'm starting to feel that it would be easier to do this outside of SSIS... Please help me to keep the faith! For those interested this is a slightly simplified version of what I have so far (all within a single data task): 1.Run dummy sql to create header row 2.Run main SQL to retrieve rows | | | 3.Multicast | | | | | 4.Create footer row by doing sum() in aggregate task | | | | 5.Merge body and footer | | 6. Merge header with body and footer | 7. Create fixed width file
View Replies !
After SSIS Package Runs All Rows, All Fields Are NULL In Destination Table ???
I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database. I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the fields are NULL in the destination database. I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL. Does anyone have a clue as to why it would be doing this? Any help would be much appreciated. Thanks...
View Replies !
How To Load A Unicode File Into The Database In The Same Order As The File Order
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View Replies !
Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Hi All, I am using sql server 2005. I stuck out in a strange problem. I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468. But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this. There are indexes in the tables. Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving. But nothing is improving
View Replies !
Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
View Replies !
Compare Values In Consecutive Rows And Print Rows Based On Some Conditions
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState. VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card). The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed. I also would like to add two derived variables. 1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID. 2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day. My report should look like: VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE Can someone please help me here? Thanks, Romakanta
View Replies !
Ssis Package Design To Load Only Rows Which Are Changed From Exisiting Rows.
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID ) and the newly inserted row with a insert time stamp so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond. example of Data would be like exisiting data StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 NULL 14 hgS 17 M ABC ST 3/4/07 NULL New row to insert would be 12 DDS 15 M DFG ST 4/5/07 the data should reflect StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 4/5/07 12 DDS 15 M DFG ST 4/5/07 NULL 14 hgS 17 M ABC ST 3/4/07 NULL Please provide your input as much as you can even though it might not be a 100% solution.
View Replies !
Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source
Environment: Running this code on my PC via VS 2005 .Net version 2.0.50727 on the server (shown in IIS) Code is in ASP.NET 2.0 and is a VB.NET Console application SSIS 2005 Problem & Info: I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel. Desired Help: How to perform 1) stripping out all undesired rows 2) importing each column into sql table
View Replies !
Force The &"ORDER BY&" To Be In Ascending Order??
I 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??
View Replies !
Retriving Previous 5 Rows And Next 5 Rows And The Searched Record
Dear All I have a table with the following structure in sql server 2005 create table app( sno int, name varchar(50), add varchar(50), city varchar(50), state varchar(50) ) it contains the follwing data ------------------------------------------ sno name add city state ------------------------------------------ 1 mark street no1 newcity newstate 2 mark street no1 newcity newstate 3 mark street no1 newcity newstate 4 mark street no1 newcity newstate 5 mark street no1 newcity newstate 6 mark street no1 newcity newstate 7 mark street no1 newcity newstate 8 mark street no1 newcity newstate 9 mark street no1 newcity newstate 10 mark street no1 newcity newstate 11 mark street no1 newcity newstate 12 mark street no1 newcity newstate 13 mark street no1 newcity newstate 14 mark street no1 newcity newstate 15 mark street no1 newcity newstate 16 mark street no1 newcity newstate 17 mark street no1 newcity newstate 18 mark street no1 newcity newstate 19 mark street no1 newcity newstate 20 mark street no1 newcity newstate ---------------------------------------- I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query. When I run select sno,add,name,city,state from app where sno=7 I want the following result ------------------------------------------ sno name add city state ------------------------------------------ 2 mark street no1 newcity newstate | 3 mark street no1 newcity newstate | 4 mark street no1 newcity newstate | -- previous 5 records 5 mark street no1 newcity newstate | 6 mark street no1 newcity newstate | 7 mark street no1 newcity newstate --- searched record 8 mark street no1 newcity newstate | 9 mark street no1 newcity newstate | 10 mark street no1 newcity newstate |--- next 5 records 11 mark street no1 newcity newstate | 12 mark street no1 newcity newstate | ---------------------------------------- if there is a method to get the above result set, kindly post the query.
View Replies !
|
TRACKER
