Redirect Rows Move Good And Bad Recs To Bad Table.
Feb 28, 2007
I use a redirect row method for error of OLE DB Destination For sqlServer2005.
For some resone even though only 1 record has error and should be redirect, all the record in the current batch (I think depending on the "maximum insert commit size") are redirect. the only way for me to get the exact bad record is to set the above parameter to 1, but then it takes hours to run the package.
also I always get the same error in the errorCode column - " -1071607685"
I have a table that among other holds volume data. I need to calculate something called Intelligent Volume based on set of rules. After all rules were followed and I still find data that does not belong to any of the rules this data is bad and needs to be reported on (can not be discarded) I wanted to do this is sql task (insert intel. volume) and I am using Cursor to loop through all the data and all rules. How do I redirect this data to a file so we can report on those records?
We are working on a DataWarehouse app. The DW has been loaded wiith transactional data from the start of September. and we want refresh the DW with a full load from the original source. This full load wil consist largely of the same records that we loaded initially in the DW but some records will be new and others will have changed.
During the load I want to direct input records NOT already in the DW to a "mods" table and ignore those input records that alreayd exist in the DW. Can SSIS help out with this task?
I have a package that works fine when taking data from Oracle and loading it into SQL Server using Ole DB source and Ole DB destination tasks.
However I have a bad record in my source now. Date value for a specific field is less than year 1753. So everytime I run with that record present in the source, the load fails when it hits that record.
Whats the best to deal with this? I tried redirecting the bad record to a file but it doesnt work as SSIS doesnt let you create error output for ole db destination task, so it can sent the record into a flat file or something else for later review.
suggestions on how to trap the bad record and redirect it to a text file? and what would the dataflow look like?
I need to count the records in a table with a datetime field equal to system datetime. It looks like it is trying to match time also since time is in field too. I just want to match date only. My sql is below. Does anyone have some suggestions on how to handle this? Date from code
DateTime todaydt = DateTime.Now;
Table Data format
[cal_str_tm] [datetime] not null, Data in cal_str_tm field - 3/27/2008 9:43:16 PM
Thanks, Ron
ALTER PROCEDURE SP_DpCount
(
@todaydt datetime
)
AS
SELECT @total = COUNT(cal_int_id)
from dpcalldtl
where (cal_callstat != 'COMPLETED' and cal_str_tm = @todaydt)
Hi, i have a OLE DB transformation that executes a stored procedure. The stored procedure RAISERROR when an error is caught. I have set the OLE DB transformation to redirect rows. But instead, the task turns red and fails. Did i do something wrong here ?
I am actually using a Script task to parse and load an XML file into multiple tables, however I want to add to this so that in case of a failed record I want to redirect it to a log table using Script Task, can anyone provide me with a good link or approach to do this.
Can any one please tell me how to get the complete error description for example when i dont Redirect Row for Error in OLEDB Source i get a detailed error message with column name as
[RCheck [385]] Error: There was an error with input column "CHECK_STATUS" (456) on input "OLE DB Destination Input" (398). The column status returned was: "The value could not be converted because of a potential loss of data.".
But when I set Redirect Row for error and use the Script component to log them into a Table with ErrorDescription based on ErrorColumnID it only gives me this.
The data value cannot be converted for reasons other than sign mismatch or data overflow.
I have multiple dropdownlists each one filled with values from a specific column in the table. Also I have multiple textboxes corresponding to dropdownlists. For example, when I select an item from dropdownlistA, all the textboxes are filled with the first row values that contains that selected item and gives the number of rows containing this value……. In addition, I have 2 buttons one is Move Forward Button and the other is Move Previous…I am using a Record Set and don’t know how to move next and back throughout the selected rows…could you help me please? I am using a vb codebehind… Thanks
Is it possible to move rows from Table1 in Database1 to Table2 in Database2?
Say, I have Database1 containg Table1:
ID ¦ Cars --------------- 1 BMW 2 Mercedes 3 Toyota 1 Opel etc....
And Database2 containg Table2 (that is empty just at the moment): ID ¦ Cars --------------- no data added yet...
How can I copy say everything from Table1 that has ID=1 (the parameter I send in with the stored procedure at execution), and insert this in Table 2? This would be, I insert the row with the BMW thingy and the row with the Opel thingy as 2 new rows in table 2. Note that the two tables are located in 2 different databases.
I am using SQL Server 7 and am trying to implement this in a stored procedure.
Need to find a way to do this. I have two tables, One with codes i.e. S,R,T and other with transactions looks like this:
Transaction table Emp Trans Tot... X55677 S 8 X55677 R 2 C22887 S 4 C22887 T 3 F66889 S 9 F66889 R 4 F66889 T 3
Code table S R T
Not sure if the code table even helps. What I want to do is show all three transactions for each employee even if they don't have any (would be zero) like so...
Emp Trans Tot... X55677 S 8 X55677 R 2 X55677 T 0 C22887 S 4 C22887 T 3 C22887 R 0 F66889 S 9 F66889 R 4 F66889 T 3
How can I get to this. I'm sure there must be a solution already posted for something like this but I'm not sure what to search for.
l've written a cursor to koop through a table and then insert the last 100 records into a table.Reason why l want the last 100 records is to monitor and log the last 100 trans avery hr or so.
-- Declare the variables to store the values returned by FETCH. SET ROWCOUNT 100 DECLARE @customer_No char(15), @loan_No char(12), @date_Issued datetime , @maturity_Date datetime , @status int
DECLARE loan_cursor CURSOR FOR SELECT customer_No, loan_No, date_Issued, maturity_Date, status
FROM loan
OPEN loan_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM loan_cursor INTO @customer_No, @loan_No, @date_Issued , @maturity_Date, @status,
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0 BEGIN
-- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM loan_cursor INTO @customer_No , @loan_No , @date_Issued , @maturity_Date , @status END
CLOSE loan_cursor
DEALLOCATE loan_cursor;
insert into Loan1 (customer_No, loan_No, date_Issued , maturity_Date , status )
select @customer_No, @loan_No, @date_Issued, @maturity_Date, @status FROM loan ORDER BY date_Issued desc;
if l want to commit the transactions after every thousand how would l build it into the script?
Begin Transaction
Select a.AccountNo, a.TransactionNo, a.TransactionAmount, a.TransactionDate Into dbo.test1
From Trans_May_14Aug2002 a,Reds_JuL_Trans_08Jul2002 b
Where ltrim(rtrim(left(a.AccountNo,20)))=ltrim(rtrim(lef t(b.AccountNo,20))) AND ltrim(rtrim(left(a.TransactionNo,20)))=ltrim(rtrim (left(b.TransactionNo,20))) AND a.TransactionAmount=b.TransactionAmount AND a.TransactionDate =b.TransactionDate AND ltrim(rtrim(left(a.Product,20))) IN ('PR060','PR061','PR091', 'PR096','PR111','PR121', 'PR122')
AND ltrim(rtrim(left(a.Transactiontype,20))) IN ('TR001','TR003','TR011', 'TR013','TR027','TR028', 'TR042','TR043','TR044', 'TR045','TR998','TR999')
AND ltrim(rtrim(left(a.journaltype,20))) NOT IN ('JT000','JT720','JT721', 'JT722','JT723','JT725', 'JT726','JT729','JT730', 'JT737','JT738','JT739', 'JT740','JT743','JT746', 'JT751')
OR ltrim(rtrim(left(a.JournalType,20))) IS NULL
AND a.TransactionDate > '2002-04-30'AND b.transactionDate < '2002-07-01'
I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.
I am able to successfully export around +- 65,000 rows, but the operation fails after that.
I need to be able to get all the rows out, so using TOP obviously doesn't work.
Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?
This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?
I am using SQL Server 2012 SE.I am trying to delete rows from a couple of tables (GetPersonValue has 250 million rows and I am trying to delete 50Million rows and GetPerson has 35 Million rows and I am trying to delete 20 million rows). These tables are in TX replication.The plan is to delete data older than 400 days old.
I tried to move data to new tables from the last 400 days and it took me like 11 hours. If I delete data in chunks of 500000 then its taking a long time to rebuild indexes(delete plus rebuild indexes 13 hours). Since I am using standard edition partition wont work.
find ddl below:
GO CREATE TABLE [dbo].[GetPerson]( [GetPersonId] [uniqueidentifier] NOT NULL, [LinedActivityPersonId] [uniqueidentifier] NOT NULL, [CTName] [nvarchar](100) NULL, [SNum] [nvarchar](50) NULL, [PHPrimary] [nvarchar](50) NULL,
Hello,I am designing a table of vehicle types, nothing special, just a list ofunique vehicle types such as truck, lorry, bike, motor bike, plane, tractoretc etcFor the table design I am proposing a single column table with a field namecalled vehicle_type and this will contain the vehicle type.Sot it will bevehicle_typecarbiketractorplanetruckvanblahblahblahIs this ok? Or is there a better way to do it?Thanks,td.
Hi, I'm using SQL Server 7.0. I have a table which has had some records double loaded into it and some triple loaded. I need to delete all instances of a record except for 1. The records are identical with no unique identifier. Here is what I'm talking about.
Currently in the table is:
ID DATE STATUS 1 07/07/2000 D 1 07/07/2000 D 1 07/07/2000 D 2 07/07/2000 A 2 07/07/2000 A
Help! Surely this has happened to others before me. A new customer wants to send updates in a fixed-width txt file in which master and detail rows alternate.
How do you do this?
Do you: 1) Painfully muck around with 100,000 rows in the .txt file first--split it into 2 files, one for master records and one for detail records and then process? If so, what do you use? I'd probably hack at it with a VB Script module in a DTS package.
or
2) Is there a way to feed it into 2 tables where rows starting with x go to one table and rows starting with y go to another?
The plan was to use a fast and dirty DTS package to shove this stuff into a table (probably 2 but we might just toss the stuff we don't need and put in in one) but I'd like some advice on how to proceed.
I have a situation where I need a table if bad items to match to. Forexample, The main table may be as:Table Main:fd_Id INT IDENTITY (1, 1)fd_Type VARCHAR(100)Table Matcher:fd_SubType VARCHAR(20)Table Main might have a records like:1 | "This is some full amount of text"2 | "Here is half amount of text"3 | "Some more with a catch word"Table Matcher:"full""catch"I need to only get the records from the main table that do not haveanything in the match table. This should return only record 2.
hi all, is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
I want to move an existing table from another. Example B.mdf dbo.xx to C.mdf dbo.xx B.mdf dbo.xx has records - I created a script and install the script for C.mdf dbo.xx C.mdf dbo.xx is empty - All I need now is to populate the records from B.mdf dbo.xx to the empty C.mdf dbo.xx I was telling I could do a cut and paste to copy the dbo.xx Thank you in advance. ~ Peaches ~
On a nightly basis, this table gets rebuilt in a temporary database. Once the table has been built and scrubbed, i need to move it into our webservers db.
I'd like to do this with minimal interuption to the website.
Possible techniques:
1) I could set up a DTS package to copy the table object overwriting the destination table
2) I could export to a flat file and then bulk import into the live table (after truncating it)
3) I could run a process to update smaller chunks of data at a time running delete queries and insert queries.
Anybody have a thought on the best way to do this so that the web users would be virtually unaware that anything was happening ?
Here is the sql code I am using. However this code adds duplicate records. It adds the record and exactly one extra record... any help would be greatly appreciated...
It's wrapped in a little ASP code:
sql2 = "INSERT INTO prodcampsignup " &_ "(Camp_ID,UserName,Password,CampName,Host,Director ,Contact,Address, " &_ "City,State,Zip,Country,Phone,AlternatePhone,Fax,C ontactEmail,AdminEmail, " &_ "URL,CampProfile, InsertTime)" &_ "SELECT * FROM tempcampsignup WHERE Camp_ID = " & lastid
I will be moving production SQL databases (one is 25Gb, the other is 3Gb) to a new server. One of the reasons is the old server has only IDE disks while the new has raid (I don't know the configuration yet). Database 1 has a primary and one other filegroup. I want to spread the disk I/O by moving table A to it's own filegroup on it's own disk. For the purposes of testing I have 1. brought a database onto a test server (using sp_detach_db, copying the files and attaching using sp_attach_db) 2. created a new filegroup 3. created a physical file on disk, allocated space and associated with my new filegroup
my question is how do I now move table A into this file?
How to move tables to the new filegroup which i created. suppose my database has only primary FG and after that i added one FG and due to performance reasons i want to move the existing table to NEW FG. Thanks.
I have worked mostly in oracle, and new to sql server. I am trying to move a very small table from oracle database to sql server database. I see a different methods I can do this, I guess DTS etc. I tried import wizard in SSMS, I was not succesful in that. Can any expert suggest me any possible ways of doing this the best way? Thank you very much!
I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).
