Script_01.sql will contain statements like:
Update SASI.AACT set schoolnum='071' where schoolnum in ('000',' ')
update SASI.AATD set schoolnum='071' where schoolnum in ('000',' ')
update SASI.AATP set schoolnum='071' where schoolnum in ('000',' ')
update SASI.ACHS set schoolnum='071' where schoolnum in ('000',' ')
update SASI.ACLS set schoolnum='071' where schoolnum in ('000',' ')
If one of those tables should not exist, how could I have it continue,
but hopefully the log would have a reference to the error?
I am experimenting, but I am unsuccessfull with something like:
BEGIN TRAN
select count(*) from sasi.aact --this could be an update
statement
if @@ERROR =208 GOTO err_handle
select count(*) from sasi.astu
if @@ERROR <> 0 GOTO err_handle
select count(*) from sasi.astu
if @@ERROR <> 0 GOTO err_handle
select count(*) from sasi.astu
if @@ERROR <> 0 GOTO err_handle
err_handle:
return
commit Tran
Error: -2147217390 (80041012); Provider Error: 0 (0) Error string: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). Error source: Help file: Help context: 0
What I have done is created a package called ACPAY_New and saved the package in two places, the SQL Server and the Meta Data Services (Repository). The name of the Database is ThedaCare1, the name of the server itself is local or rrojas, the user name is rrojas. I have no idea as to what this error message means. If I run the job in SQL Server Agent, the job does not run at all.
If I run the package on it's own within Data Transformation Services, by right clicking and execute it runs fine.
Hi, I need your help to solve this error. I am running the open query against Oracle server and this shows blow.
UPDATE Lp.dbo.CB SET oldest_invoice_date = x.oldest_invoice_date FROM ( SELECT MIN(INVOICE_DATE) as oldest_invoice_date, I.ACCOUNT_NUM FROM CDWD..CDW.INVOICE I WHERE I.INVOICE_NUM >= 0 AND INVOICE_DATE IS NOT NULL GROUP BY (I.ACCOUNT_NUM) )x WHERE account_num = x.ACCOUNT_NUM
I am getting the below error
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'oldest_invoice_date'.
Anybody give solution what I am doing wrong?. Thanks, Ravi
If "my_linked_server" is e sql server 2005 it works fine. If "my_linked_server" is a sql server 2000 I get this message: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('cmdb_get_infos') does not exist.
I have been running service broker between 3 initaitors and a single target witha forwarder for some time.
Everything works 100%, i use persistent conversation and all queues and transmission_queues are clear. Everything works 100%.
However while running profiler on the forwarder i see the following errors every now and again. Actually every time a message succesfully gets to central i see this error:
On the forwarder: Broker Connection: EventSubClass 4 - Closing :
An error occurred while receiving data: '64(The specified network name is no longer available.)'.
Database ID 1 : master:
ObjectName : tcp:11.45.101.100 - this is the target
i sometimes see the exact same error with a initiator IP.
I am very confused becuase everything is working 100%, queues and transmission queues are all clear.
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.
hi all, I have installed sql server 2000 personal on a pc running Windows XP service pack 2. I have set authentication to sql server and windows.
When I created a system dsn on the same machine it's working fine. But when I try to create a dsn from another machine on the same network, its giving error like "connection faile , error 10061, sql server does not exit or access denied."
Hi all,I have a table in the DB which holds ISBN book nubers .. which, as you knowhave dashes (examlple: 0-98765-8-9).When I run a query from an ASP page to find the number, it doesn't returnanything:sISBN = "0-98765-8-9"OpenSQL(select URL from ISBN_Redirect where ISBN=" & sISBN )Can you give me a hind of what I am doing wrong?Thanks so much in advance.S
here is my error message wich can also be seen live at www.aspdoug.com/menu log in with user: doug pass: macromedia! -------------------------------------------------------------------------------------------------------------------------------- Server Error in '/menu' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 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. SQLExpress database file auto-creation error: The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +421 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +133 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +68
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Below is my web.config file. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------<?xml version="1.0"?><configuration>
<system.web><!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. Visual Basic options: Set strict="true" to disallow all data type conversions where data loss can occur. Set explicit="true" to force declaration of all variables.--> <compilation debug="false" strict="false" explicit="true"></compilation> <pages><namespaces> <clear /><add namespace="System" /> <add namespace="System.Collections" /><add namespace="System.Collections.Generic" /> <add namespace="System.Collections.Specialized" /><add namespace="System.Configuration" /> <add namespace="System.Text" /><add namespace="System.Text.RegularExpressions" /> <add namespace="System.Web" /><add namespace="System.Web.Caching" /> <add namespace="System.Web.SessionState" /><add namespace="System.Web.Security" /> <add namespace="System.Web.Profile" /><add namespace="System.Web.UI" /> <add namespace="System.Web.UI.WebControls" /><add namespace="System.Web.UI.WebControls.WebParts" /> <add namespace="System.Web.UI.HtmlControls" /></namespaces> </pages> <!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Forms" /> <!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors>--> </system.web> </configuration>
------------------------------------------------------------------------------------------------------------------------- MY host told me that they do not support sqlexpress and to change it MySql 2005 or something. im using hostmysite.com builder plan.
I thought this SP would return 547(foreign key constraint voilation) when column 'thing' was being referenced in another table. Instead, when the front-end application code calls this SP it gets a 1 from the delete statement itself. In other words, my return statement never seems to get executed. Is there any way of achieving this? In other words, I want to trap the error 547 and return that to the front-end.
Hi,I have a stored proc StoredProc1 ={INSERT INTO Table1SELECT *FROM View1Return @@ERROR}StoredProc1 is used in another sp StoredProcMain ={(some code before)...EXEC @iResult = StoredProc1If @iResult <> 0BEGINROLLBACK TRANSACTIONReturn @iResultEND.... (continue)}So I want to rollback if StoredProc1 is not successful.Then I ran into a problem. I added a column to Table1 but forgot toupdate View1 to add the equivalent column. When I executedStoredProc1, I got the "Insert Error: Column name or number ofsupplied values does not match table definition." But the error isNOT trapped. It seems the instruction "Return @@ERROR" returns 0 andStoredProcMain goes on as if there wasn't an error.How can I trap this error?ThanksWalter
HII am using the following code in an SP, it seems like an ugly hack Ihave done to check if the BCP was working or not, I check the table itshold have filled instead of checking the error from BCP itself.Does anyone know how I can check the BCP errors directly?this is the code I am using.--------------------------------------create procedure q_spr_autoinventeringAScreate table ##q_tbl_autoinventering (ean13 varchar(13),antal decimal,signatur varchar(10),lagstalle int)exec master..xp_cmdshell"bcp ##q_tbl_autoinventering inc:outpathhd1invent.txt -t ; -Usa -P13hla -c -C "declare@invjournal int,@lagstalle int,@invdatum datetime,@ean13 varchar(15),@antal decimal,@artnr varchar(50),@lagplats varchar(20),@lagsaldo decimal,@mysubject nvarchar(4000)IF EXISTS (select * from ##q_tbl_autoinventering)begin-----------------------------------------------it seems so ugly to check the table instead of the BCP error itself soany pointers would be gladly appreciated, I tried to check @@error butthat did not seem to worked how I needed it.in short what I want is thisif bcp did not work break out of the SP and wait until called nexttime by sql server agent. and by not working I only mean that therewas no file to fetch, if there is a file to fetch and the table iscreated I have lots of checks in the SP to make sure the values arecorrectrgdsMatt
I am panning to write a DTS package whcih alter the table and output any error messages if the alter statement fails.
I have created Execute SQL Task in which I have wrote the following command.
Alter table Employee ADD EmpStatus char(4) DEFAULT A not null;
I have created a work flow to write the error message to a text file. But I am having trouble to trap the error message prduced by the Alter statement (like "column names in each table must be unique. Column name specified int the table more than once").
I have read some ideas on this, but nothing is working for me.I have an SQLDataSource bound to a FormView. I need to use the FormView to Insert new rows. When I type new values, all is well. When I type a duplicate, a get a runtime primary key error. That's fine, but how do I trap that? Overriding Page_Error doesn't work for me.Anyone please?
Hi:I am trying to update a UserInfo record using a stored procedure. It uses a uniqueidentifier UserId as the primary key. I keep getting an error and am trying to trip it using try-catch statements in both SQL Server Express and VWD 2005.My challenge is that I cannot enter a sample UserId to test the query in SQL Server because it sees my unique identifier as a string and I cannot get the error back to VWD to see where the problem is. The stored procedure looks something like:ALTER PROCEDURE [dbo].[UpDateUserInfo] @Userid uniqueidentifier, @FirstName nvarchar(50), @LastName nvarchar(70), @WorkPhone nvarchar(50),ASBEGIN TRY SET NOCOUNT OFF; UPDATE Members SET FirstName = @FirstName, LastName = @LastName, WorkPhone = @WorkPhone, CellPhone = @CellPhone WHERE UserID = @Userid;END TRYBEGIN CATCH EXECUTE usp_GetErrorInfo;END CATCH; CREATE PROCEDURE [dbo].[usp_GetErrorInfo]AS SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; When I put in the value d2dbf5-409d-4ef4-9d35-0a938f6ac608 which is an actual UserId in SQL server when I execute, the program tells me there incorrect syntax. So I would greatly appreciate it if somebody could help me with the following two questions: 1. How do I input a uniqueidentifier when executing a query in SQL Server Express?2. How can I get any errors that I trap (I think I have the right set up here) to show up back in my ASP.Net application? Any help greatly appreciate.Roger Swetnam
I have a DTS package (AdIns) that inserts to an administrative table. The Administrative table utilizes the "with ignore_dup_key" option on the index. There are other admin jobs in the DTS that are based on the return code of a parent package.
The "3604:duplicate key ignored" is an expected result of the parent package, yet it sends an failure return code to the dependent (AdIns) package, causing erroneous entries to the final audit table.
How can I reset the return code from the parent package?
I would like to trap a return value from a cmdexec that is scheduled. The cmdexec returns 0 if it is a success and something other than 0 if it doesn't.
Can I raise an error from a command file. The command file calls a console application ( i.e. no interface ).
Hi everybody,I need to trap error messages in my stored procedures and log them. I canuse @@ERROR global variable to get the error code and look it up insysmessages table to get the description. Then using xp_logevent I log theerror.The problem is this description needs to be formatted. For example if I tryto insert NULL into a column which is not nullable, I'll get error #515. Thedescription of error #515 in sysmessages is:Cannot insert the value NULL into column '%.*ls', table '%.*ls'; columndoes not allow nulls. %ls fails.Is there a way to get the formatted message? What is the best approach totrap errors, filter them, add some additional information to the message andsend it to server's event logger?TIA,Shervin
I have the following line in a select statement which comes up with adivide by zero error.CAST(CASE Splinter_StatusWHEN 'SUR' THEN 0ELSE CASE WHEN Sacrifice>=1THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/(m.Gross+m.Sacrifice)ELSE 0ENDEND AS Float)AS Bond2,The error happens on the section (m.Gross + m.Sacrifice) as this canequal zero and throws out the part of the calc that divides by it. Itis correct in some instances that it does so. The full SQL statementhas a large number of these expressions so I need a method I can applyto any line if possible.I know that it is mathmatically correct to error where this value iszero, but what I want to do is set the output of the entire expressionto zero if there is an error.Realistically an error such as this could happen at a few points inthe expression (or one of many others), so I need to find a way ofcatching any error in the expression and setting the return value to0. I thought of using a CASE statement, but wondered if there was abetter way of looking at this as the case statement would have tocheck each variation where it could throw an error.Any ideas ?ThanksRyan
Hello, I encountered an interesting situation. I have a gridview and a sqldatasource. It has delete function. When I delete a record an error of foreign key violation is raised. I would like to trap this error and give a user friendly message to the user. If I use ADO.Net I can use Try/Catch, but it seems there is no way to do the same thing using datasource. Anyone knows? Thank you, J
Is it possible to trap the SQL user who updated a record in a table through a trigger. For example if jqpublic updates a record in table1 can a trigger fire off updating a last_user update field in the same record?
Hi I am running some scripts in files using sqlcmd via a SQL Server Agent job. If sqlcmd generates an error (for example if it is unable to connect) then the job fails. However, if the T-SQL within the script is invalid (syntax, name resolution etc etc) the job completes reporting success. If sqlcmd is invoked directly via the query window then no error is raised however there is a result set returned reporting the error. Anyone know why and whether is it possible to get the error to be recognised by the job? invalid_sql.sql--The below is not actually valid SQL.do SOME stuff, innit! sqlcmdEXEC master.dbo.xp_cmdshell "sqlcmd -S my_server -i C:invalid_sql.sql" Cheers
Hello to all,I've fallow problem. I've a sp called as a job of SA each minute. Thisruns pretty nice, but from time to time, the job is aborted, and Idon't know why.Considering my logging, which is implemented in DB, I know, in whichpoint it is happening, but I don't know the exact error.This one is for sure any SQL server exception.I wanted to track this error, but reading all news, and help, andperforming some tests, I've find out, that this is almost likeimpossible, to catch the error in t-sql code (for example in this sp),and wirte it to any table for futher review.Reading great documentation from Erland Sommarskog, I know, there isno way to catch this error in t-sql, because, usualy the sql terminatesexecution of the code immieadetly (so I found it also by my tests).Now, my question is: sice I'm calling this sp continously in ServerAgent as a job scheduled to be called each one minute, is it any way,to trap this error on this level? In SA? and THEN save it somewhere inmy db?I'm calling the sp as a 'command' in job step as 'execsp_name_of_procedure'.If I'll try like this:declare @err intset @err = 0exec sp_name_of_procedureset @err = @@errorif @err <> 0begininsert into tbl_logger (sql_error, msg) values (@err, 'SQL raised anerror')endwill it work, or the sql will assume the whole code as a one batch, andwill terminate after call of sp?Thank you in advance for reply.GreatingsMateusz
Hi there,I am converting a large PL/SQL project into Transact-SQL and have hitan issue as follows:I have a PL/SQL procedure that converts a string to a date. Theprocedure does not know the format of the date in the string so ittries loads of formats in converting the string to a date until itsucceeds.After trying each potential format it uses the Oracle 'EXCEPTION WHENOTHERS' construct to trap the failure so it can try another format.Is it possible to do this with SQLServer ? If I do a CONVERT and it isnot one of the standard formats it fails. This is part of a backgroundscheduled process and I cannot afford the procedure to bomb out.I suspect the answer is I cannot do this and will need to impose somecontrol over the string being received (from various externalsystems!!) to ensure it is a specific known format. Even if I know itwill be one of the known SQLServer formats this will not be enoughsince if the first one I try is not correct the process will crash.Any ideas ?Thanks
I am writing a package where, at one step, I need to copy data from a source with text columns of 150 characters to a destination with matching text columns of only 60 characters. The data present in the source is all less than 60 characters in length, but if this changes in the future and data begins to be truncated, I want to be informed of this.
This raises two problems. First, because I'm not pre-emptively truncating the columns, my Data Flow Destination allows shows a validation warning. Is there any way I can tell SSIS "I know that data might be truncated, but I want to deal with that at run-time, not as design-time"?
Second, I'm not sure how to pass myself a notification using the options provided by Error Output on the Data Flow Destination. Fail Component would allow me to alter the control flow, but I would prefer not to have the process fail utterly because of one truncated record. I'm not sure if Ignore Failure will simply omit the row that would be truncated (not an acceptable solution) or truncate the data (could work temporarily, but I still need to be warned). Redirect Row is appealing but has a different problem - if I redirect the error/truncated rows to a separate table, that Data Flow Task no longer fails, which means I have no way to raise a notification.
Is there a better way to do this? The only option I can think is to do Redirect Row, and then have the next step in the Control Flow be a script that checks for the presence of records in my error table, and send a notification if there are, but that seems unnecessarily circuituous. Is there a way in SSIS to arbitrarily send a Failure message if a given step is hit (possibly with Events), or is that case reserved strictly for halting failures?
I created a new SSIS Package. I want to send an e-mail when an error occurrs.
I set the OnError event to send an e-mail. I then decided to test this so I dropped my input SQL table. When I drop the import SQL table I get a Package Validation Error and I don't get my e-mail.
Am I making a mistake. I want to always send an e-mail when an error occurrs in my SSIS package.
By the way I did add an e-mail at the end of my SSIS package to verify my SMTP is working - it did.
I have read several posting about various modes of trapping errors, but none seem to directly address what I am looking for (SQLIS.com.MSDN, etc)
Coming from a Java/C# background, I am looking for a way to trap errors that arise within the ssis control flow much like the said languges:
try {
do something
} catch(AnExceptionType myException) {
handle my exception
}
/ ** my code at this point is unperterbed by the exception unless I explicitly raise the exception out of the scope of the exception handler. */
To make the analogy in SSIS, I want to be able to handle an error within a "container" and not have to handle the same error in surrounding containers.
Example:
I have a "Foreach" container (call it container FEC) that contains several other containers. One of the subordinate containers is a "For Loop" (call it FLC). The FLC in turn has some nested tasks, some of which are expected to fail and therefore I want to handle in a graceful manner. The tasks that are expected to fail have a "fail" constraint that links them to a task that I want to occur when the failure occurs, and that works, but the failure is not trapped as it percolates out of the container to the FEC. I also tried to trap it with event handler, but that is also an incorrrect trail to follow.
I don't want the failure to percolate up to the FEC. I have set the max errors to a reasonable value for FLC and my "program" is not exceeding that value; however, the FEC still sees that error so it fails. How do I keep FEC from seeing the error (without upping the max errors for the FEC)?
BTW, I am using the script task to set a variable value to indicate successes or fails for those tasks where I can set the max errors to a high enough level (allow the error to occur, then let the fail/success precedent constraint pass control to the script task so that the variable can be set). This is only a partial solution.
I am new to SSIS, in fact to the MS world having been a code slinger for Java and Oracle. So far I have been very impressed with SSIS. Analogous structures that I expect to find in modern development environments have been within easy reach. This is my first serious challenge. Please help.
I have a ForEach loop that processes a list of databases. Inside the loop I many steps, one of which is a sequence that contains two steps. Either of these steps may fail (they are attempting to start mirroring and could fail for any number of reasons). I would like to trap this error and ignore it so the For loop will continue, but still fail if other steps than this one fail. The only thing I've been able to do so far is to tell the whole loop to continue through some insane number of errors. Is there a way to identify or actually ignore the error? In the sequence I have have on completion and from the sequence to the next step (which checks if mirroring actually started) is running on completion.
During the day I frequently use watch the current activity window under Enterprise Manager to see who is doing what and when.
However, overnight there are various users running various jobs that I am not always informed about. Wactcing the current activity isn't an option here.
Does anyone have a job that I could periodically run overnight to perfrom the same function as the current activty box? Which system tables does the current activity functionality use?
Thanks. Any information that can be provided here will be appreciated.
Howdy all. I have done a fair amount of performance tuning and deadlock resolving in my day, but never under these circumstances. Here is the scenario:
A sproc written in Dynamic SQL (with a dynamic WHERE clause I should add) queries a View. This View is joined to several more Views, that are joined to several more Views, that are joined to several more Views, that are.......
So, what I've been having to do to find out if my column is indexed or not is read View1, then View2, and so on and so forth until I figure out what the table really is.
This is extremely time consuming. I can actually have 10 - 20 joins by the time all is said and done.
Now, I am aware that Dynamic SQL is bad, and I'm also aware that 10 - 20 joins is bad. But that doesn't solve anything for me. Can anyone provide any ideas on how to troublshoot perfomance issues and deadlocks in this scenario?
I have a DTS package that I'm moving over to SSIS. In place of migrating this package, I've choosen to recreate it. This package moves data from an Informix database to a SQL database.
In the old package the first task was to make a simple connection to the Informix database and if the task failed, it would send an email and stop the package.
The biggest reason for this is because the Unix server that I'm getting the Informix data from forces the user passwords to be reset ever 90 days. So in my old package, if I forgot to change the password and the connection started to fail it would send me an email.
In my new package, SSIS performs a validation before starting. There are a number of task that uses the connection to the Informix database. Under testing, if I put in a bad password, the validation process generates a validation error. I've tried catching this validation error using the Error Handling events but I've had no luck. I can send out errors PreValidation and PostValidation but OnError appears not to fire under a validation error.
Might anyone have any suggestions on a proper way to validate and be able to send out email notification if a connection fails? Any assistance would be appreciated.
