Alerts Do Not Work For Error Numbers - Why
Aug 6, 2007
I'm unable to get an alert to fire for Error 2627, Primary Key Violation. I have followed BOL's instructions for creating an alert, which appears to be nearly identical to 2000, however the alert will not work. I can see the error number in a Profiler trace along with the Severity number. I've also tried creating alerts for other error numbers and had the same result. The BOL instructions are as follows:
To create an alert using an error number
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent
3. Right-click Alerts and then click New Alert
4. In the Name box, enter a name for this alert.
5. Check the Enable check box to enable the alert to run. By default, Enable is checked
6. In the Type box, click SQL Server event alert.
7. In the Database name list, select a database to restrict the alert to a specific database
8. Click Error number, and then type a valid error number for the alert
9. To restrict the alert to a particular character sequence, check the box corresponding to Raise alert when message contains, and enter a keyword or character string for the Message text. The maximum number of characters is 100
What am I doing wrong?
Thanks, Dave
View 4 Replies
ADVERTISEMENT
Jul 11, 2006
Hi
I have 2 columns the first column is a counter thats always counting up like a meter, the second column is a formula that calculate the difference between the counter(column one). like
Counter
Difference
1
1
3
2
6
3
9
3
11
2
13
2
17
4
25
8
The first row is taken as it is, but the second row will be difference = 3-1 the third one will be 3-6 and so on.
How can I do this in a SQL table or any other away?
Your help will be highly appreciated.
View 9 Replies
View Related
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Jul 20, 2005
Hi. I am searching for a reference with all error numbers an descriptionsfor ms sql odbc.Has anybody something for me?Thanks a lot.Greeting Jan Entzminger
View 1 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Apr 15, 2008
I am using a linked server to retrieve data from an off site source.
I am connecting via a Microsoft OLE DB Provider for ODBC Drivers connection to a local DSN.
I am executing a query like this in Management Studio.
SELECT * FROM OPENQUERY (MY_DSN_NAME,'SELECT * FROM MY_TABLE_NAME')
42949622.96--------> -50.00
42949660.46--------> -12.50
42949671.80--------> -1.16
42949431.65--------> -241.31
My negative numbers are coming out all messed up and in some instances it gives me an error of
Invalid data for type "numeric".
Any comments or ideas?
View 1 Replies
View Related
Apr 20, 2015
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
View 2 Replies
View Related
May 21, 2008
i have an excel sheet
in it there is a column that holds values of item numbers
some of these values are preceeded with zeros E.G "00123" (with out the ")
when i view the data in excel i get this little green corner whice states (when pressed) that these values are numbers stoerd as text.
no inside the excel sheet i have no problem with that
but when i try to import the excel sheet into ssis using an excel source task
all these values are imported as nulls!!!
i am lost
i tried converting the format of these cells to numbers but then i loose the leading zeros
what i done temporarly to solve this problem is to accept excel's suggestion and turn these values into numbers
i then import them and convert them to strings in ssis and then ad dthe zeros.
now althouge this works, this isnt realy a solution.
i canot manualy correct each excel file each time i get a new copy
and in the future i will not have the luxuery of having fixed lenght values (so i wont be able to know how many zeroe i"d need to add)
there must be a better way
please help
thanks in advance
Daniel
View 3 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Mar 27, 2007
I have some ASP.NET C# code which executes a stored procedure in SQL Server via the SqlCommand and SqlConnection classes.
One of the stored procedures that gets executed is giving the error: "Transaction (Process ID 272) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." This only happens occassionally.
Is there a way to get around this in my ASP.Net application? One thing I tried is ensuring that no 2 users entered the stored procedure concurrently:object synclock = new object() ;
lock (synclock) {
// execute SQL stored procedure
...
} This did not solve the problem, and I'm not even sure if that is the correct implementation to ensure sequential execution of the stored procedure.
View 1 Replies
View Related
Dec 3, 1999
I'm trying to use schedulled task to automate restore and i keep getting the error database in use, it is set to dbo only and single user, and it has me as the user that has the lock, i have tried to kill it but it won't go, i have even started and stopped the server. any ideas?
View 2 Replies
View Related
Jan 11, 2007
i followed all the instruction to add the database of the adventure works to the database server. but when i attach the database from the managment sql server this error comes
Couldnt find row in sysindexes for database ID 8, object ID1 ..........etc
and couldnt open new database 'adventureworks'. create database is aborted.
please i need a qucik help. cuz i tried the msdn and i failed.
thanks
View 1 Replies
View Related
Feb 9, 2000
Is there any recommendations on setting up Alerts. What are the standard set alerts that should be setup on every server?
View 1 Replies
View Related
May 9, 2002
I have to send the alerts when the database(s) will be filled more than 90% and 95%. Please help me if you have some samples.
Thanks,
Rau
View 1 Replies
View Related
Jun 20, 2008
I ignored this nice little feature till now, probably because mail configuration complexities. Now that we have DB mail, it has become very smooth.
I just implemented an alert for log growth, and found that it works very well.
Any of you using, alerts for , other interesting events?
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
Jun 20, 2006
Hi everyone,
I also could not find any definition about Alerts and their purpose and usage.
Would you please explain it to me ??
Thanks
View 4 Replies
View Related
May 29, 2006
I€™m importing a Flat file (delimited text) into a sql server database table and trying to trap any import errors in another output Flat file.
I create a Flat File Source task. After tweaking the task, the Source €œError Output€? tab shows all the input columns and all €œError€? and €œTruncation€? values are set to €œRedirect row€?
Next I add a €œSQL Server Destination€? task and connect the Source green arrow to it.
Finally I add a Flat File Destination task (error Output task), connect the Source red arrow to it, click €œOK€? in the €œConfigure Error Output€?, and finally add a connection manager to the Error output task. When I look at the Mappings tab of the Error Output file it shows only three available input columns: €œFlat File Error Output Column€?, ErrorCode, and ErrorColumn
I€™m not sure where they came from but that info is not very useful to me. I want to know which line the error occurred on as well as the bad column(s). If nothing else, I need to see at least the actual row that was bad. How can I get that information?
Barkingdog
View 8 Replies
View Related
Nov 7, 2007
Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[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)]"
as an error. This is what I've got:
I€™m using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what I€™ve used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider.
SQL Server 2005 Surface Area Configuration
Database Engine Remote Connections €“ set to €œLocal and Remote Connections €“ Using TCP/IP Only€?
SQL Server Browser is Enabled and Running
- is set to Active, under Built In Account €“ Network Service
I have created Windows firewall exceptions for:
sqlservr.exe
sqlbrowser.exe
udp port 1434
SQL Server Configuration Manager
both SQL Server and SQL Server Browser are running.
Under SQL Server 2005 Network Configuration
Shared Memory and TCP/IP are enabled only.
SQL Native Client Configuration
Shared Memory 1
TCP/IP 2
Named Pipes 3
all enabled
I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.
I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.
View 9 Replies
View Related
Oct 31, 2000
Is there a way that I can get SQL Server to tell me (Alert?) when it has grabbed another chunk of space for a database that has the "Automatically grow file" option set?
View 1 Replies
View Related
Dec 2, 1999
I was wondering if anyone knew of some key error numbers that I should look for to fire alerts off of.
For example, ERROR 926 "Database cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server error log for more information."
I am looking for some key error messages (Error Numbers) like the one above that are good to initially setup within SQL Server for alerting Administrators of potential issues.
Thanks,
Peter
View 1 Replies
View Related
Dec 4, 2002
Hi,
I just realized there are several severity alerts on the Server Agent, from Severity 19 to 25.
Is there anyways to delete those messages? If I don't delete those, would it be affected later?
The Online Book mentions those are some of the system error messages, but they don't show how to delete them.
Can anyone helps?
Thanks a lot.
View 6 Replies
View Related
Nov 3, 2005
Hi,
I am having trouble with locks and performance issues in one application. The SQL server is specific to this application. What settings should i choose to set an alert to let me know when i get too many locks? the locks i am interested in ar ethe ones that show up in SQL EM. ie where a process is blocking another process(es).
I have tried New Alert | lock requests /sec but that is a huge figure when the server is running OK, so i cant guess what value to set for the counter. Is there a better object/counter i should use?
thanks
fatherjack
View 5 Replies
View Related
Dec 14, 1998
I've setup alerts and an operator to email on a particular event code.
I can successfully test the operator. (It e-mails fine)
The problem I am having is that despite having a particular event
log to the NT application log, no SQL Alert is triggered.
I've tried logging to the NT Application log, as well as the SQL errorlog.
The SQLExecutive is running.
Thanks in advance.
John
View 1 Replies
View Related
May 4, 2001
I am working with alerts and trying to see if there is a way that I can control the message on the notification. I have the job set to send an email and a net message when the job succeeds. Since this message will be going to the user, I owuld like to be able to make the message as clear as possible for them. Does anyone know if the message being sent can be modified or altered with an alert? And how?
Thanks!!
View 1 Replies
View Related
Feb 17, 2005
Hey guys,
I was just thinking, is it possible to send alerts via the Managment/SQL Server Agent/Operators/Alerts to an SMS system?
Is it even technically posibble?
Actually I am trying to develop an alert system that reads the database table periodcally, the data being read is a datetime, once it reaches the time, I need to send an sms to the person.
Any suggestions? ideas? Quickest solution?
View 1 Replies
View Related
Mar 22, 2004
I defined an Alert in Enterprise Manager for Severity 19 - Fatal Error In Resource. The alert is enabled, the type is "sql server event alert" and the alert is defined against a specific database. SQLSERVERAGENT and SQL Mail session are both started. I test the alert using the following statement in the specified database.
raiserror (50001,19,1) with log
A message is placed in the SQL Server log, however no alert is fired. The alert history indicates the alert has never occurred. What am I missing?
Thanks, Dave
View 8 Replies
View Related
May 29, 2008
hi
how are the use alerts in sql server2005,
and which alert type used in maximum and how to performaing our database
I would like to get user experiences
View 3 Replies
View Related
Jul 12, 2007
Been doing some research after getting this error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot create a row of size 8297 which is greater than the allowable maximum of 8060.
And realised that my nice responsive varchars had a maximum total size. so I changed them for 'slower' text data types. but my DB still won't allow any more input.
has the limit been reached now regardless of what I change or can I rebuild the DB to recover the space or something?
View 14 Replies
View Related
Oct 3, 2007
Hi Everyone,
I'm having some trouble with the below trigger. When I add a row of data either manually or using an INSERT query, it just doesn't do anything. It doesn't provide any error messages either. This makes me think that it's aborting the operation because rowsAffected are 0 or some other simple error. My row manipulation code could be suspect also. This is my first time writing a trigger or using T-SQL for that matter.
What I'm trying to do is to have the trigger add +1 to the Iter field of all rows where BouleID is equal to the BouleID of the row being inserted. So let's say I have the following table:
BouleID CurrentLocation Iter
A01 Inventory 1
A01 Cutting 0
A01 WIP 2
B01 WIP 0
B02 WIP 1
B02 Inventory 0
Now, if I insert a row with BouleID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.
I am using SQL Management Studio Express, and SQL Server Express.
Any thoughts on anything wrong with my selection code and iteration code? Could I adapt this to handle more than one row by using a GROUP BY BouleID somehow?
Code Block
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <this trigger will iterate the Iter field by one for each rows that has the BouleID matching the one of
-- the row being inserted>
-- =============================================
CREATE TRIGGER dbo.trgCG_DispoIterate$InsertTrigger
ON dbo.dbCG_Disposition
AFTER INSERT AS
BEGIN
DECLARE @rowsAffected int,
@msg varchar(2000), --for error message
@BouleID varchar(6) -- and do I need to enter more than one?
SET @rowsAffected = @@rowcount
IF (@rowsAffected = 0 or @rowsAffected > 1 ) RETURN --don't continue if no rows changed or doing more than one at a time.
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
SET ROWCOUNT 0
BEGIN TRY
--VALIDATION BLOCK Leave this alone for now
SELECT @BouleID = BouleID FROM Inserted --sets @bouleID equal to the BouleID of the row being inserted.
UPDATE dbo.dbCG_Disposition --This block sets the Iter field to it's previous value +1
SET Iter = ( Iter + 1 ) --
WHERE BouleID = @BouleID --
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK TRANSACTION
--or this will get rolled back
EXECUTE dbo.ErrorLog$Insert -- this function creates an errorlog table which gets filled up if there is an error in the try block.
DECLARE @ERROR_MESSAGE nvarchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE, 16, 1)
END CATCH
View 6 Replies
View Related
Aug 9, 2001
Does anyone know of a way, either by 3rd party tools or using SQL server, to have alerts get emailed to you in a clustered active/active environment? I work as the DBA at a quickly-growing company, whose production databases need to be online 24X7. I want to set up the alerts to email/page me if there are problems with the databases, but since (according to microsoft) SQL mail is not supported in a clustered environment I'm at a loss.
I'm looking for some help in trying to resolve this issue either by 3rd party means or a SQL Server work around.
Your help will be greatly appreciated.
Sincerely,
Tony
View 1 Replies
View Related
Sep 28, 2001
Can anyone point me in the direction of a good resource
that will help us set up our system so that
notifications from alerts/jobs send emails to our
Groupwise email system?
View 1 Replies
View Related