Trapping 'curent Activity; In Batch Overnight?
Hello:
We are using Mssql 6.5, with sp4.
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.
DAvid Spaisman
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
DB Went From 2GB To 3GB Overnight
I changed the Identity column in our 3.5 million row Order Line Items to clustered. Suddenly; the .mdf file grew from 2GB to 3GB. Is this normal? Do I need to reindex or something? Fill Factor is 85% - should I have made that something different? This was done because the new software going in had massive performance improvement on this (it does millions of user function calls all based on this key). All is behaving OK but I'd love to understand more about what I did. Thanks so much for any link or instruction on this matter. DBCC Contig shows the following: DBCC SHOWCONTIG scanning 'tbInvoiceLine' table... Table: 'tbInvoiceLine' (1406732164); index ID: 1, database ID: 7 TABLE level scan performed. - Pages Scanned................................: 134403 - Extents Scanned..............................: 16832 - Extent Switches..............................: 16831 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 99.82% [16801:16832] - Logical Scan Fragmentation ..................: 4.11% - Extent Scan Fragmentation ...................: 7.52% - Avg. Bytes Free per Page.....................: 977.3 - Avg. Page Density (full).....................: 87.93% The table should probably be about 95% fill factor based on how much gets added after initial creation. EDIT: I just checked out the typical Insert and 5% would be high even. So; I just changed the fill factor to 95%. This based on BOL best practices writeup here (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops4.mspx). This table does see a TON of indexed lookups using the Clustered Key, but it also gets FTS quite often (I think ... not absolutely sure). Wonder if there's a way to say "Show me all queries that have a query plan of FTS on this table". I know it's looked at by all the Sales Reports - lookup by an alternate key (Order #). Anyway; I don't see how leaving an extra 15% wasted could have resulted in a 50% growth of the entire database. Granted; this is by far the largest table in the database - it's just not adding up. Why? Thanks.
View Replies !
View Related
Transaction Log Becomes Full Overnight
Hi guys, I am developing this site http://www.onlineacademicadvisor.com and having DB problems for the 3rd time in a row. Whenever the traffic on the site is getting bigger, the transaction log becomes full and no user can login. This problem is described at http://support.microsoft.com/kb/317375 From there, I got the feeling that the problem occurrs if transactions are not committed and last for too long. However, I do not have any explicit transactions, just the usual select, insert, update statements in stored procedures. I do not call COMMIT (or RETURN) explicitly at the end of my stored procedures though. My stored procedures are short. Have you got any ideas about what can cause the problem? I really have not idea what that could be. Your help is much appreciated.
View Replies !
View Related
Indexes Fragmenting Overnight
Over the past few days we noticed severe performance issues on some of our more complicated queries. I ran a DBCC ShowContig on the problematic tables, and noted that the Logical Scan Fragmentation was very high, like over 90%. I ran a DBCC DBREINDEX on the tables, the Logical Scan Fragmentation reduced down to between 0% and 10%, and the queries ran instantly. However...the next day, the queries were causing problems again. Running ShowContig showed the fragmentation was up to over 90% again. Now, these are very static tables I'm dealing with...absolutely no UPDATE, INSERT or DELETE commands have been run against them (we import the data once a month). I set up a job to monitor the state of the index fragmentation overnight. All is well until 0100, when the LSF hits 90% again. I can't figure out what could be causing this, we have no jobs that run on, or affect, this database overnight, except the backup, which runs at 2100. Has anyone experienced anything like this before, or does SQL Server do something on the fly that could cause it to happen? TIA!
View Replies !
View Related
Scripting Overnight Backup Jobs For SQL Server Databases
Hi, I've just been given the task of finding out how to implement a backup procedure for our SQL server databases. Most are running 2000, some 2005. I'm a programmer, and I'm used to having a DBA to help me! I've seen a few methods on the web involving a stored procedure and running the task from task manager. I need to backup and restore all the databases in SQL Server 2000 and work out a way of displaying whether or not it was successful. Can anybody please point me in the right direction as I've no idea how to do any of this really. I guess if I could setup a sproc to loop through the databases that would help, but I'm not sure where to start. Thanks in advance.
View Replies !
View Related
Generating Overnight Data Vs Live - Suggestions Needed.
We have an MIS system which has approx 100 reports. Each of thesereports can take up to several minutes to run due to the complexity ofthe queries (hundreds of lines each in most cases). Each report can berun by many users, so in effect we have a slow system.I want to seperate the complex part of the queries into a process thatis generated each night. Then the reports will only have to querypre-formatted data with minimal parameters as the hard part will havebeen completed for the users when they are not in. Ideally we willgenerate (stored procedure possibly) a set of data for each report andhold this on the server. We can then query with simpler parameterssuch as by date and get the data back quite quickly.The whole process of how we obtain the data is very complex. There arevarious views which gather data from the back office system. These arevery complex and when queries are run against them including othertables to bring in more data, it gets nicely complicated.The only problem is that the users want to have access to LIVE datafrom the back office system, specifically the Sales team who want toaccess this remotely. My method only allows for data from the nightbefore, so is there an option available to me which will allow me todo this ? The queries can't be improved on an awful lot, so they willtake as long as they take. The idea of running them once is the onlyway I can see to improve the performance in any significant way.True I could just let them carry on as they are and let them sufferwith the performance on live data, but I'd like to do something toimprove the situation for them.Any advice would be appreciated.ThanksRyan
View Replies !
View Related
Reporting Services Windows Service Stops Overnight
I'm having a problem with our Reporting Services service stopping overnight. I have the following in the log: ReportingServicesService!library!4!7/24/2007-02:00:37:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams ReportingServicesService!dbpolling!10!7/24/2007-02:02:32:: e ERROR: Polling caught an exception, restarting polling. Error Message System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.DBPoll.PollingFunction() at Microsoft.ReportingServices.Library.DBPoll.PollingStartFunction(). ReportingServicesService!library!10!7/24/2007-02:02:33:: i INFO: Polling started again. ReportingServicesService!library!4!7/24/2007-02:10:47:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams ReportingServicesService!library!4!7/24/2007-02:20:44:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams ReportingServicesService!library!4!7/24/2007-02:30:48:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams ReportingServicesService!library!10!7/24/2007-02:36:23:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection() --- End of inner exception stack trace --- I have a maintenance task running at night so I'm wondering if that is causing it to stop however I don't see any problems in the task log. Any help would be appreciated.
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 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
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 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 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
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 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 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
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 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
Trapping Rows With Truncated Columns
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?
View Replies !
View Related
Performane Tuning And Deadlock Trapping In This Scenario.
SQL2K SP4 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? TIA, ChrisR
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
Trapping Parameter Passing From URL To Stored Procedure
I have a Stored Procedure that takes a hand full of parameters to retrieve the data. I€™m using the exec sp_name @param1, @param2€¦ pattern in my dataset definition. This report is a sub-report and I€™m opening by sing the Jump hyperlink action. To query string contains the parameters to be passed. The problem I€™m having is that for some reason not all the parameters are passed every time. My question is how can I validate what values is passed to the SP from RS when it execute the SP.
View Replies !
View Related
SQLCMD Batch File With Script In Batch File
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express: sqlcmd -S (local)SQLExpress -i C:CreateDB.sql This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work. Thanks, Dave
View Replies !
View Related
How To Log Any Activity
Dear friends, I'm working on an entity relationship diagram about a mice farm. Let's say we have a very very basic diagram like this: MOUSE "is in" CAGE For example, this gives Mouse "n°1500" is in cage "AAA" Mouse "n°1501" is in cage "BBB" One operator can take one mouse and put it into another cage, for example the first line becomes Mouse n°1500 is in cage CCC Now, I was asked to track any movement... I ask myself: do I need to add OPERATOR "moves" MOUSE so that I have Operator "John" moves Mouse "n°1500" on "Monday 3rd, 2007" at "5 PM" from "AAA" to "CCC"? or this kind of logging is provided some way by SQL server? Thanx a lot for any hint.
View Replies !
View Related
Activity Log
What is the best to produce user activity log ? Using triggers (insert, delete, update) is rather heavy (?) operation. Has anyone used SQL Profiler for that ? In log should be: who, what, when, workstation, table, changed fields, some data key values. Harri
View Replies !
View Related
SQL Activity
I have just set up the following http://support.microsoft.com/kb/283696/EN-US/ and http://support.microsoft.com/kb/283886/EN-US/ for the performance So i run my results and get select * from v_sysperfinfo and get ... Workfiles Created/sec Oct 16 2006 11:54AM 632531 Index Searches/sec Oct 16 2006 11:49AM 106329443 Values seem high....for the COUNTER Is there something that needs to be reset when using this tool
View Replies !
View Related
Old && New Values For My Activity Log ??
I have a requirment to create a activity log to log all changes that take place in my tables as they happen. All my Inserts,updates & Deletes take place inside stored procs. A windows .NET application calls the stored procs and passes the data set to the stored procs to execute them on a Sql server 2000 database. When an update statement is run inside my stored proc, the dataset only passes me the new values. but,i will need the old value from the database before the update takes place to report in my activity log. What is the best way to do it ? My activity log shold report a description like this "Name Changed from 'Robert Johnson'(old value) to 'Bob Johnson'(New Value)" Though i can do a select from the tables to get the old values and match the new values to get the differences before running the update statements, it is too much work as i have well over 100 stored procs in my application and i have to check for every single field value in a table. If only one column changed in a table containg 20 columns, i would still have to check for 20 columns before determining which fields changed. Can any one suggest me a better solution to report the old values and new values for all the updates that take place in a database through stored Procedures. Thanks Reo
View Replies !
View Related
Current Activity Not Available? 6.5
When viewing Current Activity, nothing shows up. If i run sp_who, the return is normal. If I run sp_who2, the following message is returned. (it also is returned randomly while using function in EM such as Backup/Restore.) Msg 268, Level 16, State 1 You cannot run SELECT INTO in this database. The DBO would have to run sp_dboption to enable this option. What is it??? It's buggin me...... Dano p.s. I remember a similar problem in the past, it had to do with turning truncate on checkpoint in one of the system databases msdb/master/tempdb or something like that. I turned off all Trunc. and Select/Bulk options on these...
View Replies !
View Related
Current Activity
I have registered a server on to my client machine successfully,but I am not finding the current activity item in the management folder on EM.How do I see the current activity on the server.Do I need to be SA for that server?My machine has NT workstation with SQL server 7.0 on it.Any one to help?
View Replies !
View Related
User Activity
Hello All, I am new at this site and new at using SQL also.... I was wondering if anyone could help me out with my prob... I have set up an SQL 2000 server and made some accounts which my friends are using to log onto my server.. Is there a way I could keep a check on who is doin what or maintain a user log.... Many Thanks,
View Replies !
View Related
SQL 2005 ACTIVITY
I usually run this command in SQL 2000. Which tells me what is running with all the sql code statements. Is there an alternative command to run in SQL 2005...rather than using the Reports - i find it easier to execute sp i called this sp_now. set nocount on declare @handle binary(20), @spid smallint, @rowcnt smallint, @output varchar(500) declare ActiveSpids CURSOR FOR select sql_handle, spid from sysprocesses where sql_handle <> 0x0000000000000000000000000000000000000000 --and spid <> @@SPID order by cpu desc OPEN ActiveSpids FETCH NEXT FROM ActiveSpids INTO @handle, @spid set @rowcnt = @@CURSOR_ROWS print '====================' print '= CURRENT ACTIVITY =' print '====================' print ' ' set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt) print @output WHILE (@@FETCH_STATUS = 0) BEGIN print ' ' print ' ' print 'O' + replicate('x',120) + 'O' print 'O' + replicate('x',120) + 'O' print ' ' print ' ' print ' ' select 'loginame' = left(loginame, 30), 'hostname' = left(hostname,30), 'datagbase' = left(db_name(dbid),30), 'spid' = str(spid,4,0), 'block' = str(blocked,5,0), 'phys_io' = str(physical_io,8,0), 'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END , 'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2), 'program_name' = left(program_name,50), 'command' = cmd, 'lastwaittype' = left(lastwaittype,15), 'login_time' = convert(char(19),login_time,120), 'last_batch' = convert(char(19),last_batch,120), 'status' = left(status, 10), 'nt_username' = left(nt_username,20) from master..sysprocesses where spid = @spid print ' ' print ' ' -- Dump the inputbuffer to get an idea of what the spid is doing dbcc inputbuffer(@spid) print ' ' print ' ' -- Use the built-in function to show the exact SQL that the spid is running select * from ::fn_get_sql(@handle) FETCH NEXT FROM ActiveSpids INTO @handle, @spid END close ActiveSpids deallocate ActiveSpids GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
View Replies !
View Related
|
TRACKER
