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
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?
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.
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 ?
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
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.
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?
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
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)
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.
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?
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.
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?
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.
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?
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.
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.
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?
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
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?
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.
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?
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?
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:
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
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.
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?
