DTS Import - Error Handling And Scheduling- SQL Server 2000
Mar 10, 2002
Hi,
I would like to transfer data from an oracle database to SQL server database 2000. I am using DTS Export/ Import Wizard for this purpose.
My queries are:
1. How can I write the write the Start time, End time, data records that failed to a log file with minimal amount of coding. (I found that Active X scripts can be written although the coding is quite complex).
Can I get some kind of pointers to simple coding that can ensure I log time of run of DTS and errors if they occurred.
2. There is an error log written by DTS, but this is written only if an error occurs and the details of error are not written. I am looking to log successful transfers too. Can the DTS package be modified, with minimal code to achieve this objective?
3. I tried scheduling the DTS through the Edit window that SQL Server 2000 provides but am not able to change the time of schedule.
How do I schedule it?
We have trigger on a table. The trigger implementation is to insert a record in another table. If any error occurred in the trigger then the trigger should log the error to a file on the file system.
CREATE TRIGGER [myTRIGGER] ON [dbo].[MyTest] AFTER INSERT AS declare @errorcode int; INSERT INTO MySecondTable (id, myvalue) values (null, 'hey') set @errorcode=@@ERROR if (@errorcode <>0) print 'Error occurred with error code "' + CONVERT(varchar, @@ERROR) + '"'
Problem If the insert statement fails, for example because of a null violation, then trigger aborts and never reaches the next step in the trigger T-SQL code. IS this a limitation or there's somthing wrong on the above code? Thanks
I'm going to be creating a few triggers on tables to add new records to a seperate table (AlertData). Come 6pm or so I want a scheduled job to automaticaly run to make an HTTP wbe request to my SendAlerts.ASPX page. The page will simply make a database call for all users and the AlertData table contents, build what it needs and send out all the email alerts. My question is is how do i create such a scheduled job under Sql Server 2000? It should run everyday at 6pm EST (even if the dbase server is restarted) and make an HTTP web request to my URL. Thank you.
Hi all,I have a case :I have an employee SQL-View (not table) in my sql server 2000 database. This view actually connect to an oracle database server, because all our employee data is located on the oracle database server.This linked server soon proven to be the cause of poor application performance (response time is very slow). So we decide to put a local copy of employee table in our sql server 2000 database. We have successfully copy it to our database. But the most up-to-date employee data is still reside on oracle server. To deal with this problem we need to scheduling a sinchronization between sqlserver 2000 and oracle everyday at 01:00 o'clock midnight.I never get task like this before, how to make a scheduling in sql server 2000? my friend suggest a dts scheduling but unfortunately I haven't found any tutorial about this for beginnerthanks
I want to schedule a task that checks if a file(s) exists on d:Acc and then run the sql script shown below i.e. with the file(s) to load the content of the file(s) every day new files will be loaded. after the file(s) are loaded the file(s) are deleted.
I currently can achieve the above using Java and sql server and running the program as a scheduled task... (only reason for change would be that i wouldnt require java, JDBC etc to run the software it would be easy manage, migrate etc)
I wanted to find out if it can be done with sql server directly? (i.e. check if file exist and ased on that carryout the task or other wise just ignore) What do i need to look for? does sql server have a procedural language in it to be used for such thing? does sql server have an inbuilt task scheduler?
I am currently using the following script in a java program please note that i have used fixed values in this script for readabliity (of what i am doing)..
@bulk_cmd varchar(1000) SET @bulk_cmd = 'BULK INSERT AccInv..VW_AccInvoice FROM ''d:Invbatch2001.txt'' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''+CHAR(10)+''')' EXEC(@bulk_cmd)
I've tried several different possibilities but don't have enough basicunderstanding of the ownerships and permissions that are involved soI'm looking for someone to explain what is required:I created a DTS package that includes a drop table, create table, anddata transformation task. Originally, my owner was local to the pcworkstation with administrator priveleges. When I executed thepackage on demand (manually, not scheduled), it runs just fine. Noerrors. When I schedule the package to run at a scheduled day/time,it fails with an access is denied message.I originally was using the sql admin user and password in theconnection but the view job history kept referencing "Executed asuser: sqlservernameSYSTEM...." and errored with an access is deniedmessage. Where did it get the SYSTEM user from since my connectionused the sql admin user in my DTS package? Do I need to determine howto get the sql admin user or should the SYSTEM user really have to runthis scheduled job? How do I get the sql admin user to show as the"executed as user"?I created another admin user to see if it had someone to do with thesql admin account but I got similar results. I tried creating thepackage as an NT domain user but got similar results.I'm sure it has something to do with permissions and ownerships but Ireally don't have a clear understanding of how it was intended towork.Any advise you can give is greatly appreciated. I've read alot ofpostings and I'm still not clear on the requirements.
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1. Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification.
Could you please look into this and guide me Thanks in advance venkatesh imtesh@gmail.com
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error) Messages Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.". (SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. (SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C. (SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
Hi I think by virtue of not being able to find reference to this I have my answer however.... You trap an error, check it and know that you are happy with it - it isn't an issue. Is it possible to prevent that specific error (number, message - the lot) being passed to the client so developers don't need to handle the error a second time? Ta db chucks :D
Hi, i have a package that uses a ForEach loop component to import flat files, and uses an OLE DB Destination component to insert the data into some staging tables (using table fast load with a max insert commit of 1000 rows), the biggest individual table import would be circa 5000 rows. At the end of each file import a stored proc is called to transfer the data into production tables, then the next file is imported.
Periodically (when importing more than 5 or 6 files) the process fails with the error message:
The transaction log for database 'blah' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
This occurs when committing the data to staging. I do not start any transactions during this process, and the staging tables are cleaned out by truncating them, so i'm not sure exactly what is causing the log file to fill up (i'm not a DBA).
I know this is not specifically a SSIS problem, but can anyone give me a suggestion about the best way to handle the log file during an SSIS import? Should i execute a DBCC SHRINKFILE before each flat file is imported? Is there some other approach i should be taking to either insert the data or to move it from staging to production?
I'm taking the liberty to announce the availability of a suite of articles on my web site about error and transaction handling in SQL Server. In total there are three main parts and three appendixes.
The first part is a short jumpstart, while Part Two is a long in-depth discussion of what can happen in SQL Server in case of an error and what commands that are available. Part Three covers implementation and has lot of examples as well as a facility for logging and raising errors.
The appendixes cover special areas: linked servers, the CLR and Service Broker.
We have several jobs that if any step fails, we need to continue with the next step. Due to this we have the both 'On Success Action' and 'On Failure Action' set to 'Go to the next step'. Our only issue is that when all the steps are done we still require the job itself to fail.
So my question is this, is there anyway the status of a job can be queried to determine if any of it's steps failed?
I would like to capture any errors thrown by linked server in my stored procedure, How do I do this? eg: Select statement in sp to remote server fails if login credentials were changed for some reason on remote server. @@error is not able to capture this error and stored procedure is terminated when this type of error occurs.
I'm trying to get to grips with the formview control and have a problem regarding where an exception is raised if SQL server raises an error when an insert is taking place. I am new to both asp.net & c# I have put the following code in the SqlDataSource2_Inserted & Formview1_Inserted events as a way of determining where the exception is raised: if (e.ExceptionHandled){ string exceptionmessage = e.Exception.Message; lblStatus.Text = exceptionmessage;} These two events are the only one's that have the ExceptionHandled and Exception properties. I run the code with data that will raise a duplicate key error from SQL Server but the error is not being caught by either of these two handlers. All I get is a page that starts with Server Error in '/SourceCode' Application.
Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated.... Is there somewhere else I should be looking to handle this sort of thing? I thought using the databound controls would make life easier but I'm actually finding it more of a pain than coding everything myself.
Hello,I am trying to import a data from a DB called Reference Manager. Itwas sent as an XML file.How do I import it? I only have the client tools.Thanks,Tmuld.
Yao, Could anyone help me with, or point me to information regarding, scheduling jobs for MSSQL 2000? I need to execute a SQL Command, monthly, which sets a budget field to a fixed ammount, something like: UPDATE Programs SET Budget = 500. --Kyle Johnson
I am a final year uni student and as part of my project I've developed a .Net site with a SQL Server 2000 backend.
This week a virus hit our house and my PC got infected (despite anitvirus and firewall!). Anyway, I have had to format my hard disk and reinstall everything.
My site is back in the wwwroot folder and looks fine, trouble is I need to get the database back up and running.
Before I wiped the disk I took the .mdf and .ldf files from the data file in the SQL Server folder in Program Files.
How can I get my database backup and running.
Ive tried creating a new database (with the same name as the .mdf and .ldf files) and replacing the default created files with the copies I saved but this didnt work.
Any help would really be appreciated as I have to demo this to my lecturers soon and my whole grade is riding on it! (no pressure)
I'm trying to import a Paradox 8 db into SQL Server 2000 using DTS but the list of data sources in the connections dialog only caters for version 5.x or older. Does anybody know how to get the data source for v8?
If not, is there any other quick and efficient way to import a paradox 8 db as I have to import around 15 of them to do.
Hi all,I have a file with an extension of .sdf. I "believe" it is a text fileof some sort but I am uncertain. The source agency hasn't returned anyof my calls so I'm wondering if anyone is familiar with this extension?I'd like to import the file into my database - when I use DTS and chosea text format, regardless of what delimiter I choose, the format isstill really ugly. when I pull it up in a huge text editor, it is hardfor me to tell what it is there.I saw in one of my searches that it could be a comma delimited (it'snot) .. could be a unisys file? I know it's not much information to goon - but where should I start in trying to get this into my databasewithout knowing the format? Any suggestions would be greatlyappreciated.Thanks!Bethany*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I need to import some excel files in the Database via DTS. The problem is, that I don't know the names of the Excel Sheets (there are multiple sheets in the excel file). Is it possible to get the names of those sheets? (via activeX or SQL Query or anything, but from a DTS package)
I'm trying to do exception handling in a stored procedure, such as IF (@@ERROR <> 0) execute and insert on to an error log table. I have set the volatile @@ERROR global to a variable, but the proc is still throwing a primary key exception; hence, it's not being trapped. My question is why isn't my exception being handled?
Here's my code, your help is much obliged. (my error proc is below the caller)
CREATE PROCEDURE [dbo].[INSERT_STATS_NO_GROUP_PROC] @stat_type_id int, @stat_delimited_file_id smallint, @time_interval datetime, @call_volume int, @Err int OUTPUT AS SET @Err = 0 DECLARE @Error int BEGIN TRANSACTION INSERT INTO DAILY_SUMMARY_STATISTICS ( stat_type_id, stat_delimited_file_id, ssda_customer_id, time_interval, call_volume ) VALUES ( @stat_type_id, @stat_delimited_file_id, NULL, @time_interval, @call_volume )
SET @Error = @@ERROR IF(@Error <> 0) BEGIN GOTO abort END COMMIT TRANSACTION
