Trapping Cmdexec Errors
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 ).
Any help is appreciated.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
CmdExec Errors!
I have tried to make a DTS task running a batc-file on the same server as the SQL program is running using the CmdExec command. My Command is as follows: CmdExec serverpathatchname.bat The problem is that everytime the step runs it report the following error: "The system cannot find the file specified." In the bat file I am also trying the de-map/map a network drive with another loginname/password than the one used on the SQL-server -> is this i problem? Hope to hear from you soon, this is a bit urgent!! Please provide tips to this adresse: qtip@bigfoot.com
View Replies !
View Related
Trapping Errors
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
View Replies !
View Related
Trapping Errors In SPs
Hi, Here is what I am trying to do: CREATE PROCEDURE usp_deleteSomething @theThing_i char(11) AS BEGIN SET NOCOUNT ON delete from myTable where thing=@theThing_i return(@@ERROR) END 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. Any replies would be greatly appreciated. Thanks in advance, Nishi
View Replies !
View Related
DTS - Trapping SQL Errors
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"). Any help will be appreciated. Thanks Sankar
View Replies !
View Related
Trapping SQLDataSource Errors
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?
View Replies !
View Related
SQL Errors, Trapping In Server Agent
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
View Replies !
View Related
Trapping Errors In Stored Procedure
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
View Replies !
View Related
Trapping Sqlcmd Errors When Executed From A Job
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
View Replies !
View Related
Trapping Errors In EXEC Statements
There are a few threads already with similar questions, but such replies as have been posted don't address the basic problem. I have a stored procedure which ddeletes related records from a large number of tables, which involves using many EXECs as I need to substitute parameters for table owners & WHERE conditions. These basically work fine, but in development I inevitably dropped the odd character and ended up with an incorrect table name after substitution in one EXEC statement. This resulted in error 208 (invalid object name). I have error trappimg in place (save @@error after each EXEC & check & at various points). This was not triggered by the error, although the check was straight after the EXEC. I put a 'print @@error' there instead, but it just returns 0. I then tried putting the 'print @@error' INSIDE the EXEC, immediately after the failed DELETE, but it doesn't print anything! i.e. it looks like the EXEC exits instantly on getting the error without going on to the next statement in the block. Unfortunately, execution of the procedure continues after the EXEC, so I can't find a way of stopping execution & rolling back the transaction. I have tried everything I can think of - has anyone any ideas? Is there a way round this? The statements are something like this (assume the DELETE causes a 208 error)... EXEC (' DELETE ' + @tablename + 'FROM blah blah WHERE blah = ' + @RecId ) print @@error This prints 0. EXEC (' DELETE ' + @tablename + 'FROM blah blah WHERE blah = ' + @RecId + 'print @@error' ) This doesn't print at all! Please help preserve my sanity. This prints 0.
View Replies !
View Related
Trapping Errors In The Control Flow
SSIS GURUS: 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. SCott
View Replies !
View Related
Trapping Validation Errors And Sending Email
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.
View Replies !
View Related
Cmdexec Job
I have created a VB 6.0 executable with the purpose of reviewing email sent to a certain MAPI mailbox, and if the mail has a specific attachment, that attachment (a text file) is saved to the hard drive. The program then runs (using a shell command) a DTS package on my SQL Server (7.0) to import the text file into an existing table, and then deletes the hard disk copy of the file. The imported information is then processed using ADO calls to a stored procedure on the same SQL Server. My goal is to schedule this EXE to run on a regular basis using SQL Server Agent. However... When I run the EXE from the system with the SQL Server Agent that I would like to use (not the same server as the SQL Server the code is being run against, but it has an ODBC DSN set up for the SQL Server with the data and the SQL Server Agent user has full permissions on that SQL Server) it runs fine. But when I add the exe as a cmdexec job in SQL Server and start it, it just hangs there. It says it is executing, and it allows me to stop execution, but it doesn't actually run the program itself, as far as I can tell. Anyone have any thoughts? Any input would be very much appreciated! Thanks! Heather Witt
View Replies !
View Related
CmdExec Task
Hello everybody. I scheduled task to run daily with dbcc. I run this task manually to test, task was running saccesfully and already 30 minuts is active and running. Our Lab test server is not busy and I don't understand what is problem, why it's running so long time. Are you experienced with problem like that? Thank you. Alona.
View Replies !
View Related
DTS Scheduling (CmdExec)
I have a DTS local package that consists of a single Process Task. I call a locally stored VB executable which opens a local Excel file and deletes certain rows. When I run the package manually, it runs fine. When I schedule the package for execution the package says it executed successfully, but it doesn't actually invoke the executable. Any suggestions on how to correct this situation would be greatly appreciated.
View Replies !
View Related
CmdExec In A Nightly Job
I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues. The step being executed by the Job is "Ren C:MyTestFile.csv C:MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed. I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed." As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure. I would appreciate any assistance anyone could provide. Thanks in advance!
View Replies !
View Related
CmdExec Job Step In Sql Server2000
Thanks for the invitation to post a question, so I will post one. I need to create a job step that uses cmdExec. This is the command line I entering: D:odbcTimeClockUpdatesinReleaseTimeKeepingNo nLogouts.exe When I run this job the job fails. When I look at the job history, the only information I get is the date and time, user that ran the job and the fact that it failed. I haven't been able to get any CmdExec job to run at all. Can anyone tell me what I'm doing wrong? Facts. 1. This exact same command is used by my network administrator using windows scheduler on the server. The only reason he wants me to create an sql server job is because it's mostly sql functions. 2. I know the Sql Server agent is running, because I have other jobs that are run. 3. I have verified that I have permission to run the file because I can go to the actual directory and run the exe. 4. Do I need to enclose my command in quotes i.e. “D:odbcTimeClockUpdatesinReleaseTimeKeepingN onLogouts.exe” 5. the path of the file I need to run is the path on the server and not the path on my local machine. If you need any other information, please let me know Thanks for you help GEM
View Replies !
View Related
Tasks Using Cmdexec Failing
WE have a number of SQL 6.5 servers all of which run many scheduled tasks. However on one all tasks which use cmdexec to run batch files fail after 2-3 seconds. The history returns : 'No message' and the error logs just indicate that the tasks have faILED. The identical tasks run on servers with what appears to be identical configurations and setup. Tasks scheduled using Tsql run OK ! I would be grateful for any advice or help Many thanks in advance Tom
View Replies !
View Related
Cmdexec Job Fails In Sql 2000
I have a Job that runs a cmdexec job step which executes a batch file in sql server 7.0 that runs fine In sql 2000 when i try to run that job it gives the following error and fails ---- the process could not be created for step 1 of job 0x677EF599B13FA743AA2D501D4C211AC4 (reason: The system cannot find the file specified). The step failed. ------ In fact i am not able to execute any cmdexec job in sql 2000 The owner of the above job is sa Does it have to do with SqlAgentCmdExec account which is set to corporate/administrator and has required permission. Help will be greatly appreciated.
View Replies !
View Related
Jobs - Using CmdExec (OSQL)
Hi Guys, Ive created a job in sqlserver 2000 (Management, SQL Server Agents, Jobs). One of my steps execs a script (.sql) using OSQL (thru CmdExec) and outputs to another file (using the 'o-' parameter in the same command line. Ok heres the problem: On execution - sometimes that process get deadlocked. It then raises the errormsg & writes to the output file. HOWEVER, the SQL Server Agent reports back that the STEP completed successfully, when it is really not successful at all. Is this the way it actually behaves when using CmdExec? Do you think if I change the step from 'CmdExec' to use 'Transact SQL' and output to a file (inside that step, and not through OSQL), And if it gets deadlocked again - Would the SQL SErver Agent report back the ERROR, instead of returning 'Successful' ? OR IS there any other way around this problem. thnx for ur help. Ill be trying to find another way as well.
View Replies !
View Related
Creating Cmdexec Type Job
Hi everybody, I have a bcp job in .cmd file. But it is the d drive like 'd:foldernamefile.cmd' I use Cmdexec type and have used the following syntax: start d:foldernamefile.cmd or start "d:foldernamefile.cmd" or d:foldernamefile.cmd But non of these syntax work. The job either failed or job keep executing but no output file being seen. Something wrong with the syntax? But I can type: start d:foldernamefile.cmd in command promp it works. Can any body tell me what is wrong here? By the way I have parameter for the .cmd file I put it after the path e.g., start d:foldernamefile.cmd SQLSERVERNAME. Could this parameter cause the problem. If so how I put the parameter? Thanks for the help. Wilson
View Replies !
View Related
JOBS Using Steps/CmdExec
Hi All, In creating 'steps' in JOBS, is it possible to execute many DOS CmdExec in one step, instead of creating several steps with a single DOS-cmd in each. For example: Step1: bcp sourcedb..sourcetbl out source.dat -n -T -Ssourceserver Step2: isql -T -Stargetserver -Q "truncate table targettbl" Step3: bcp targetdb..targettbl in source.dat -n -T -Stargetserver If I created a job executing those 3 functions in 3 separate steps then it works fine. But if I put all those 3 DOS command in one step, it won't work. Somehow, SQL doesn't 'understand' it should execute after the end of each command OR I missed something here (apparently so!). I know if I put all those 3 DOS commands into a DOIT.BAT and execute it, it will work. But I want to use SQL Job to schedule it to run on a regular basis. Anyone has run into this same problem? Thanks in advance. David Nguyen.
View Replies !
View Related
Cmdexec Subsystem Failure
I have a scheduled job that has been running fine for 2 years, but this week I keep getting the following error: 0x469A75B7998F8142A910FA7E9983CCDF has caused an exception in the CmdExec subsystem and has been terminated. the enterprise manager shows that the job failed, but the app still shows up in task manager. any suggestions? thanks
View Replies !
View Related
Dtexec - Can Run In A Job (CmdExec) But Not From Xp_cmshell
Hi all, I've working a while, not at full time, but seeking the solution...Here what I want to do and what I've done till now: I want to build a sql job, so I can run a package (witch loads 2 excel sheets into 2 tables) passing "dynamic" parameters, like convert(varchar,getdate(),112) in the format YYYYMMDD. From what I've found, I can do this with an Stored Procedure, which first set this variable, building a statement so it can be run by xp_cmdshell. For example: EXEC master.dbo.xp_cmdshell 'DTEXEC /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101' And this statement runs with no problem in the SP until it reaches the step of loading the Excel Sheets into tables. Here it gives an error. It's about the JET driver. From what I've read in the forum and from the output error the problem seems to be that this statement executes the dtexec.exe 64bits, even the suggestion to change the property Run64BitRuntime set to False, it stills running from the dtexec.exe 64bits. So, I changed the statement to point to dtexec.exe 32bits: EXEC master.dbo.xp_cmdshell 'C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101' But executing this statement it does not even run. The errors are: 'C:Program' is not recognized as an internal or external command operable program or batch file This was very wird to me, because this was very mentioned by the moderators. So, I copy this same statement and created a job with a step type of "Operating System (CmdExec)" and it runs great...With no problem with the extraction from excel source. Now my questions are: 1. Why the step job CmdExec recognizes the path of dtexec.exe 32bits ('C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe ), but trying to run with xp_cmdshell it gives the error mencioned above. And if there is another way to set dtexec.exe 32bits besides this way? 2. If I cannot run it throught xp_cmdshell, how could I pass a parameter like convert(varchar,getdate(),112) in the format YYYYMMDD instead of the static parameter 20070101. Thanks in advance. Marco Francisco (Portugal)
View Replies !
View Related
CmdExec "COPY" Command Fail To Run
Hi: I want to copy a backup from the production server to another server periodically. The following is the DOS command and it works in DOS propmt d: is the local drive and g: is the mapping server --------------------------------------------------- "copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat" I try to use the above command in the executive task with CmdExec as type, if failed. Then try on "cmd c/ copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat" this time, it started to run with no error. But it run 40 miniutes and I cancelled it, since it should only run 10 minuts. thanks for the help David
View Replies !
View Related
Identifying Failure Of CmdExec Jobs
I have a job that executes an Operating System batch script which in turn executes a number of osql tasks. i.e. osql [various options] -i query1.sql osql [various options] -i query2.sql osql [various options] -i query3.sql If for example query2 fails, the job still reports success. Is there a simple method to report failure. I have tried splitting the above into 3 steps eaching executing a batch file and specifying an action of quit with failure for each, but it still reports as successful!! Any ideas?
View Replies !
View Related
Using CmdExec To Execute Dtexec In Sqlagent
I'm a bit confused. On the command line of the job step property I entered dtexec /SQL... and got an error saying file not found, I assumed dtexec itself couldnt be found. So I tried /SQL .... by itself and got something that looked more like a security error. If I make the step property type "ssis" job appears to run fine, I receive my pkg's on success (rather than on failure) email but I know everything isnt fine because even if no data is ETL'd, first executable is supposed to (and always has in client) insert a row into an audit table and it doesnt. If I set job step "type" to t-sql and simply db email myself with t-sql command, everything is fine. The first question is "Wouldnt dtexec need to be specified, how else could sqlagent know what I'm trying to run?" If answer is yes, what's wrong with my syntax or environment?
View Replies !
View Related
Trapping BCP Error From SP
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
View Replies !
View Related
Operationg System Command (CmdExec) Job Failing
I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainNameSQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine. The job just copies files from one directory to another, here's the code. D: cd MSSQLBACKUPAPAP_Primary xcopy *.* D:MSSQLBACKUPAP /s/y/d When run as a job, with the owner of the job being DomainNameSQLAdmin the job fails with the following error message: Executed as user: DomainNameSQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed. Here's the weird part if I open a command window on the sql server and run the batch it works fine. I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied. Anyone have an idea whats going on?
View Replies !
View Related
Problem With Process Exit Code (Cmdexec)
Can someone tell me how i can generate an exit code (Cmdexec). Ive tried running an .exe file in a job but when i do that the job hangs. When i check the history it says the exit code is somewhere in 27000. Now i'm wondering if it is possible for me to generate an exit code if the executable closes. PS. When i run the file from a batch file it works great
View Replies !
View Related
Configuration Files Do Not Work In CmdExec Mode
I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig (on my dev machine). Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile. However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:....myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig cannot be found. What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine. Is there a way around this?
View Replies !
View Related
SQL2K CmdExec Proxy Account Setup
Hi, I have the following setup: - Win 2K Server, SP4 - SQL Srv 2K, SP4 - A 'DEV' domain, with an 'Administrator' account with all possible rights on the system I need to configure a CmdExec proxy account in order to allow non-sys-admin users to execute the master.dbo.xp_CmdShell procedure. When attempting to do this via QA as follows: EXEC master.dbo.xp_sqlagent_proxy_account N'SET', N'DEV', -- agent_domain_name N'Administrator', -- agent_username N'password' -- agent_password ...I get the following error: "The system cannot find the path specified." When attempting to do this via QA as follows (note: only change is adding domain to agent_username arg): EXEC master.dbo.xp_sqlagent_proxy_account N'SET', N'DEV', -- agent_domain_name N'DEVAdministrator', -- agent_username N'password' -- agent_password ...I get the following error: "Error executing extended stored procedure: Specified user can not login" I have tried this through Enterprise Manager and get identical results, of course. I have also tried all of the following: - different OS user accounts, including local system accounts with local admin rights; - assigning the OS account to a SQL login with System Admin role/rights; - specifically assigning the above SQL login with EXEC rights on the master.dbo.xp_CmdShell procedure; - verifying local security policy settings, as per the following link: http://support.microsoft.com/?id=283811; - pulling out my hair and banging my head against the wall. Can anyone H E L P ? ! ! ! Thanks, Joe
View Replies !
View Related
Trapping SQL UpDate Error In VWD
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
View Replies !
View Related
Trapping Error Messages
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
View Replies !
View Related
Divide By Zero Error Trapping
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
View Replies !
View Related
Erro Trapping Question
I have a batch file that runs SQL Server scripts using commands like:OSQL -Umyname -Pmypassword -iScript_01.sql -w200 -e -n[color=blue][color=green]>>Consolidation.log[/color][/color]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 TRANselect count(*) from sasi.aact --this could be an updatestatementif @@ERROR =208 GOTO err_handleselect count(*) from sasi.astuif @@ERROR <> 0 GOTO err_handleselect count(*) from sasi.astuif @@ERROR <> 0 GOTO err_handleselect count(*) from sasi.astuif @@ERROR <> 0 GOTO err_handleerr_handle:returncommit Tran
View Replies !
View Related
Trapping SQL Messages Using LOGMON
We want to setup message traps using a product called LOGMON, so that we can notify support groups of problems. The Microsoft documentation seems to indicate that severity level 19-25 is recorded in the Windows NT event log. Severity level 22-23 seem to relate to SQL Server database problems. Has anyone setup any products to trap messages ? If so, is it possible to obtain a list of the messages numbers they have trapped ? We are using SQL Server 6.5. Thanks in advance.
View Replies !
View Related
Error Trapping In StoredProcedure
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? TIA!:mad:
View Replies !
View Related
Linkedserver & Error Trapping
Hello, Could someone please tell me how to trap an OLE/DB error while using a Stored Procedure that executes the OPENQUERY command? What I want to be able to do is know when a table in the Linked Server is not available. The data is in a VFP table and I can cause the error if I first get the table exclusive in FoxPro. It will return the following when I call the sp in Query Analyzer: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file rolls.dbf.] The sp then stops after the line EXEC (SqlString) in my sp and returns to the Query Analyzer displaying the above message. I'm uncertain as to how to trap the OLE/DB error since @@Error does not seem to set anything. Thanks for any help. Darrell
View Replies !
View Related
Error Trapping Of Datasource Control
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
View Replies !
View Related
Trapping Keyviolation Error Code
I have the following code that will trap a keyviolation error message. Id prefer to trap the actual error code so i can give users a more relevant error message. Ive checked the exception object on MSDN but can find no reference to an actaul error code, anyone have any ideas ? TIA try { conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); //Response.Redirect raises an exception //For Response.Redirect, use an overload, //Response.Redirect(String url, bool endResponse) that passes false for the //endResponse parameter to suppress the internal call to Response.End Response.Redirect("PDFForm.aspx",false); } catch ( Exception e ) { conn.Close(); //ErrorLabel.Text = (e.Message); Response.Redirect("PostError.aspx?em=" + e.ToString(),false); //Response.Redirect("PostError.aspx?em=" + e.Message,false); }
View Replies !
View Related
Trapping Package Validation Error
Hello, 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. Thanks, Michael
View Replies !
View Related
ForEach Trapping An Error And Continuing
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. Thanks.
View Replies !
View Related
|
TRACKER
