I have one SP in which I am opening transaction and on fail I am rolling back transaction.
Is there any way I can preserve data in temp table before failing record.
I means to say At record no 21 get failed I need to see 20 records in any temp tables.
Hi,
I'm just starting off in SSIS and have a question that I can't find an answer to...
I'm loading in a number of files (in separate Data Flows) and performing some transformations on them before merging them back together. What I'm not sure about is what I should be doing with the data at the end of each of my "Import Data From XXXX Flat File" Data Flows. Am I better off using OLE DB Destinations (or SQL Server Destinations) and saving this intermediate data to temporary tables, or am I better off using a Raw File Destinations and saving this intermediate data to files? Or is there, perhaps, a better option that I'm currently unaware of?
If the Raw File Destination is the way to go, then isn't there a maintenance issue with cleaning up all the files created? And will there not be a management issue to ensure that there is sufficient disc space available on the drive you are saving to?
I'm a bit confused and overwhelmed by SSIS at the moment, so any help would be much appreciated!
Thanks in advance,
Lawrie.
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
Hi All
I have parent package which is having the Execute Sql task , which execute the script provided by file connection , after that it calls the child package thru Exceute Package task , I have set the Parent package's Transaction property as "Required" & child's as "Support" , as I run the package child package gets hang .. but it started running when I put the direct input instead of script in my parent package . Can you plz help why this is happening & what is the solution because I require that script file
Kind Regards
Renuka
Renuka.M.Popli
My transaction log backup is failing with the following error:
Does anyone know how I can fix it?
(null)
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194 Copyright (C) Microsoft Corporation
(null)
Logged on to SQL Server as 'NT AUTHORITYSYSTEM' (trusted) Starting maintenance plan 'Maint Plan - TLogs' on 5/7/2001 8:18:11 AM Backup can not be performed on database. This sub task is ignored. (null)
Thanks
Our Transaction Log backup job runs every two hours from 7:00am to 8:59pm. Today, our Full backup job and the first Transaction Lob backup job ran successfully. Subsequent Transaction Log Backups have failed with the following error message under Job History:
"The job failed. The Job was invoked by Schedule 13 (Schedule 1). The last step to run was step 1 (Step 1)."
The caveats are that the database is in Full recovery mode and a .TRN file is generated on the server. Also, the SQL Server log says the job ran successfully. Any ideas?
Hi,
I'm a clueless SQL noob trying to set a maintenance plan to backup my databases.
This is SQL 2005 running on Windows SBS 2003 R2.
I've created two maintenance plans one to do a full backup of all DBs and one to do transaction log backups every 4 hours of the databases.
The full backups run but the transaction log backup fails with
DTExec: The package execution returned DTSER_SUCCESS (0). in the logs.
I can run the backup manually from the SQL Studio but not automatically.
Any clues on what can cause this not to work?
Thanks,
nline
I have setup a SQL maintenance plan to backup the transaction log file to be backup every one hour M-F.
But now I have been getting the following error:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Any clues what does this error mean ?
Thanks.
:eek:
Okay. I changed the times that the transaction logs are backed up, via the built in maintenance schedule. It was then that I started to get failures only on the Transaction log backup for the master. The error that I get from the history log is Backup can not be performed on this database. This sub task is ignored. If I look in the file that is saved to disk :
Starting maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:00
Backup can not be performed on database 'master'. This sub task is ignored.
[1] Database onyx: Transaction Log Backup...
Destination: [R:BACKUPonyxonyx_tlog_200411090230.TRN]
** Execution Time: 0 hrs, 0 mins, 23 secs **
[2] Database onyx: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:31
SQLMAINT.EXE Process Exit Code: 1 (Failed)
I changed the backup back to its original time as this was the only change made. This has not resolved the problem. As I am new to SQL and still finding my feet All the other SQL maintenance plans that I changed are working fine.
Thanks
Steve
Hi,
I'm running SQL 2005 SP2 and having a problem with a Database Maintenance task created in SSMS doing transaction log backups of all user databases.
When I look at dbo.sysmaintplan_logdetail I see the message:
Database 'Database1' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Great - that's what we want! Skip the SIMPLE recovery mode databases. However, the next entry is the following error message:
Executing the query "BACKUP LOG [Database1] TO DISK = N'D:\MSSQL$Windows\MSSQL.2\MSSQL\Backup\Database1\Database1_backup_200707231600.trn' WITH NOFORMAT, NOINIT, NAME = N'Database1_backup_20070723160023', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Which says to me it is trying to backup the same database(s) it just said it was skipping!
What's going on? I see that there is a fix to a similar error message with the cumulative update KB936305 (http://support.microsoft.com/kb/936305) for maintenance cleanup tasks.
Does anybody else have such a problem?
Hi guys,
I have some triggers that insert data in a table located in a remote server. If the remote server happens to be down I want to continue with the original transaction anyway. Can I use a BENGIN TRY BEGIN CATCH block to continue with the transaction in the trigger and also insert the data into a temporal local table if the remote insert fails?
Regards
SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives???? Anyone else ever heard of such a thing. Please help, before I loose my sanity.
View 4 Replies View RelatedIf Transaction Log Backup fails due to a conflict could it break the Log Chain?
View 3 Replies View RelatedHi,
I am writing a BI solution for a recruitment company. In their
business, the can be n number of participants from different
dimensions linked to the same fact record. For example, a client can
be sent the CV of 50 candidates. That's my first problem. My second
problem is the variety of dimension participant types for a given fact
record. This results in the need for nullable dimension FK's - which
I'm trying to avoid. For example, consider the following two business
events. In the first one, a candidate fills a job. Easy, we have a
record in the fact table where the fact table has the following
columns: DateKey, EventType, CandidateKey, VacancyKey. No nullable
columns, great. But there are other events that I want to store in
the fact table too. Let's go back to my first example: The client is
sent CV's of 50 candidates in one transaction. So there is one client
linked to the fact, but 50 candidates. So now I need to extend the
fact table and add another column: CandidateGroupKey (which links to
and Intermediate Fact Table). But in this case there was no vacancy
involved. So do I now have to make the VacancyKey column nullable?
That doesn't seem like a good idea...
Or do I have to go for a completely different approach and have
different fact tables instead of just one?
Anyone have any suggestions?
I've been working through a programming diploma the last few years and have a solid foundation in Normalization, SQL, SQL Server, MySQL, Procs, Triggers, etc. Basically I'd say I'm at least a competent junior programmer on the back-end.. able to build simple joins, update data, and research/read/understand documentation.
Right now, and at the start of the year I was in a co-op term where I got some industry level SQL experience, so I know what a legitimate database looks like, and I've seen some scary looking procs. And now I've found that I quite enjoy working in SQL land and I'd like to take my skills up a notch.
With that said, the only method I can think of to improve my SQL skills is to find a sizeable sample database to load into SQL server, hopefully with corresponding exercises, and then to code those exercises.
Good Afternoon.
I have one question about clustered index.
The clustered index have one architeture such as be-tree...
What's the intermediate level on the clustered index?
Thanks..
We have a rather large environment and have just a couple of boxes out there that we are getting cannot begin distributed transaction on inserts and updates but works fine on selects. Inserts and updates work fine outside the begin tran / commit so it's definitely DTC
We have checked the configuration on and the source box is set to No authentication required same for destination.
We have: Verified credentials running the service, changed them, same problem. Uninstalled and re-installed MSDTC per Microsoft instructions.
Have run all the tools for checking DTC DTCPing etc and followed those procedures which typically in the past has resolved any DTC issues. Other than swapping out the offending pc for a new one we are at a loss.
Is it possible for to send an email if a job fails as an alert? Can the email tell me which step failed?
I know how the code to send an email, but unsure how to trigger this for an agent job failure.
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.
In May of this year I graduated from Penn State with a BS in IST (Information Sciences and Technology). Right after graduation I got a database programming job with a company that uses Delphi 6 and MS SQL Server 2000.
I've been working with this company for six months now but I'm still not very good with SQL. I can do basic SQL queries and table joins (as well as use datetime functions and cursors), but I'd say I'm only at an intermediate level (at best).
So... I'm looking to learn more about SQL. I'm guessing a good SQL reference book would help, but I'd really prefer a good book that actually teaches you and guides you along. The only problem is that I don't want a basic/beginner level SQL book since I already know all of the basics.
Can anybody recommend anything for me?
Thanks!
Quick question. I am in the NYC area (Westchester) and I am using Cognos DecisionStream to build my SQL Server 2000 Enterprise Data Warehouse.
I want to learn more about complex SQL, specifically for SQL Server.
What do you reccommend? Should I go for strictly an advanced SQL course, or should I go for SQL Server training.
Does anyone know of a really good site where I can find Advanced SQL training?
Also, I want to learn more about scripting and VB. Can anyone reccommend a place where I can get this beginner training.
Sorry to take up your time, but to sum up:
1. Advanced SQL training in NYC area
2. Beginner VB training in NYC area.
Cost is not necessarily an issue.
Thanks!
In May of this year I graduated from Penn State with a BS in IST(Information Sciences and Technology). Right after graduation I got adatabase programming job with a company that uses Delphi 6 and MS SQLServer 2000.I've been working with this company for six months now but I'm stillnot very good with SQL. I can do basic SQL queries and table joins (aswell as use datetime functions and cursors), but I'd say I'm only at anintermediate level (at best).So... I'm looking to learn more about SQL. I'm guessing a good SQLreference book would help, but I'd really prefer a good book thatactually teaches you and guides you along. The only problem is that Idon't want a basic/beginner level SQL book since I already know all ofthe basics.Can anybody recommend anything for me?Thanks!
View 2 Replies View RelatedI've created an SSIS package that contains a Sequence Container with TransactionOption = Required. Within the container, there are a number of Execute Package Task components running in a serial fashion which are responsible for performing "Upserts" to dimension and fact tables on our production server. The destination db configuration is loaded into each of these packages using an XML configuration file. The structure of these "Upsert" packages are nearly identical, while some execute correctly and others fail. Those that fail all provide the same error messages.
These messages appear during Pre-Execute
[Insert new dimension record [1627]] Error: The AcquireConnection method call to the connection manager "DW" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Insert new dimension record" (1627) failed the pre-execute phase and returned error code 0xC020801C.
... which are followed by
[Connection manager "DW"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".
[Connection manager "DW"] Error: An OLE DB error has occurred. Error code: 0x8004D00A.
While still in debug mode, I can check the properties of the "DW" connection and successfully test the connection within the packages that fail.
The same packages run successfully when tested outside the container (i.e. no transaction) or when the configuration file is modified to point the "DW" connection to a development version of the db which is running on the same server as the source database.
I have successfully used DTCtester to verify that transactions from source to destination server are working correctly. Also tried setting DelayValidation = True with no change. I have opened a case with Microsoft and am awaiting a reply so I thought I'd throw a post out here to see if anyone else has encountered this and might have a resolution. Here's some more on the environment:
Source Server:
Windows Server 2003 Enterprise Edition SP1
SQL Server 2005 Enterprise Edition SP0
Destination Server:
Windows Server 2003 Enterprise Edition SP1
SQL Server 2000 Enterprise Edition SP3 (clustered)
Thank you in advance for any feedback you might be able to provide.
KS
I'm updating one column using trigger but i am getting below error .
UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
At this link here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dwsqlsy.mspx
I read this snippet here:
Push data from the Integration Services pipeline or a custom application. The data can flow directly into an Analysis Services partition from the Integration Services package pipeline, without intermediate storage. This scenario can be used to reduce the latency (and storage cost) of analytical data.
What I think this means is that using SSIS I could bypass porting the data from ODS to DW, and instead use SSIS control flow/data flow components to direct the data straight to the Analysis Services Cube database storage partition.
I was hoping by posting this thread on this MSDN forum I could get details or links from those more knowledeable than I on this subject pointing me to how I may go about using SSIS to implement this approach. Perhaps package code examples or instructions about the components I could use to do this?
I have a table that i'm trying to pull the first transaction and last transaction per prodid. The first transaction, the ctrid should be 'TOPPEntry'.
sample raw data result
prodid--------lineid------itemid---------ctrid----------createddate--
=================================================================
TOP02296228--Line15-----TTC3071-IR------TOPPEntry------2015-01-03 07:45:31.000--input
TOP02296228--Line15-----TTC3071-IR------TOPCBFG--------2015-01-07 16:18:26.000--fg--done processing
TOP02296229--Line15-----TTC3071-IR------TOPPEntry------2015-01-04 07:45:31.000--input
TOP02296229--Line15-----TTC3071-IR------TOPLens--------2015-01-05 12:12:31.000--wip--wip means still in process
TOP02296230--Line15-----TTC3071-IR------TOPPEntry------2015-01-05 08:45:31.000--input
TOP02296230--Line15-----TTC3071-IR------TOPCBSCP-------2015-01-06 14:18:42.000--scrap--done processing
[code]...
Is it possible to take any type of backup during transaction or we can say in b/w the transaction?
View 9 Replies View RelatedWe have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error: "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table. The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.
I have started working on SSAS since last week, I need to perform some calculations on the data fetched from the cube based on the parameters. SSRS is used to display the output and SSAS is used as a data source.
How i could perform the operations on the data fetched from the cube in SSAS? Does SSAS provides the storage structure like temp table in stored procedure where we can perform the various operations before sending final data back to the client side tool(SSRS)?
OR Is there any alternative way to perform the operations based on the input provided through the parameters
select TOP 10 rec_id,trans_id,number_id,card_no,message_id,trans_datetimefrom [dbo].[trans_log]
order by trans_datetime desc101, 1,34343, 99999, 200, 2015-11-23 12:27:25.710101,2,34343,99999,210,2015-11-23
12:27:26.710102,3,43434,88888,200,2015-11-23 12:28:26.714102,4,43434,88888,233,2015-11-23 12:28:27.710expected result:34343,99999,datediff(ss,'2015-11-23 12:27:26.710','2015-11-23 12:27:25.710') --difference between row 2 and row 143434,88888,datediff(ss,'2015-11-23 12:28:26.714','2015-11-23 12:28:27.710') --
difference between row 4 and row 3difference between row 6 and row 5...In the above query, I always want to find the difference in transaction date time between second and first row in a moving window.I have the unique id as rec_id to compare the next row with the previous row.
My data is in 4 columns and multiple rows, like this
PartNo Quantity TransactionType TransactionDate
aaa 25 Incoming 2015-03-01
aaa 25 Incoming 2015-03-01
aaa 50 Transfer 2015-03-02
bbb 30 Incoming 2015-03-03
bbb 30 Transfer 2015-03-03
ccc 50 Incoming 2015-05-15
ccc 75 Incoming 2015-05-20
ccc 50 Transfer 2015-05-18
ccc 75 Transfer 2015-05-21
What I need to achieve is sum the quantities for a given transaction type, group it by Part Number and add an additional column where the Transaction Date for Transfer Type rows would become the Transfer Date. Each part would have one row. The resulting data would look like this.
PartNo Quantity IncomingDate TransferDate
aaa 50 2015-03-01 2015-03-02
bbb 30 2015-03-03 2015-03-03
ccc 125 2015-05-15 2015-05-21
How to achieve this goal.
I have heard about fn_dblog() function, Is it possible to find a transaction in a backup using fn_dblog() function?
View 3 Replies View Relatedis there a way to query an excel spreadsheet directly from sql without using ssis or excel macros?...and without saving the spreadsheet to a table first?
View 5 Replies View Related