Regarding the above errors, I decided to apply sp4. A copy of the database was made and placed on a test server and now we cannot start the SQL Server. I have checked the registration and all seems fine. Any ideas ?
I am trying to setup SQL mail and I am getting errors 17903 (MAPI Login failure) and 17951 (Failed to start SQL Mail Session).
There is Exchange 5.5 and SQL 6.5 installed on this server. I have the MSSQL Service logging in as me, Jim Elden. I setup an Exchange profile called Jim Elden, and indicated this in the SQL Mail Configuration.
WE followed the microsoft article: " how to use sql mail in Microsoft SQL Server 6.5 with Microsoft Outlook and Microsoft Exchange Server. WE also followed the suggestions outlined in document Q153159:" Troublshooting SQLMAIL with Microsoft Exchange Server.
After following all of the instuctions in both documents we get an error message of 17951:'Failed to start Microsoft Mail Session' when we click on sqlmail under server name in Enterprise Manager.
My network adminsitrator and I are both stumped why this doesn't work.
Has any one encountered this before? How do we resolve this?
Any information which can be provided will be greatly appreciated. THanks.
I have a problem trying to send email using SQLMail. When I setup a new job under SQL Server Agent I can receive mail okay when a job completes, but when I try to receive mail from a Database Maintenance Plan the following error is reported:
Emailing report to operator 'DBA Group' [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 17903: [Microsoft][ODBC SQL Server Driver][SQL Server]MAPI login failure.
I cannot figure out the problem as mail is getting through using the profile I setup and also when I use outlook and also from SQL Server Agent jobs.
I run the query in the attached file on multiple servers with success but some of them refuse to cooperate. I've tried recreating the linked server on the problem servers but this doesn't help. All I want to do is check db file size and free space and get it to work. Why does it have to be so damn complicated?
Any ideas welcome - including an entirely different way of doing this.
The script and table definitions in question are attached
Could not open table '"Helpdesk_New"."dbo"."LogStats"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
We have 2 instances. One is sqlgpscluster and another is [sqlcdsclustersqlcds]. We have created linked server between sqlgpscluster and [sqlcdsclustersqlcds]. We run the below query on sqlgpscluster instance
UPDATE [sqlcdsclustersqlcds].CDSBusiness.dbo.tblInsertNowAppMonitoring SET NoCabsDateTime = GETDATE(),City=@City ,Area = dbo.fn_FindAreaSubAreaBasedOnLatLong(@PickUpLat,@PickUpLng) WHERE CustomerMobileNo=@CustomerMobileNo
We are seeing the below error: The OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds" could not UPDATE table "[sqlcdsclustersqlcds].[CDSBusiness].[dbo][tblInsertNow AppMonitoring]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
I am using SQL Server 2000 sp4 (standard edition); when I run an Update Stats command against a table I get error 3628:
UPDATE STATISTICS IMSV7.BLITEMRT WITH SAMPLE 20 PERCENT Server: Msg 3628, Level 16, State 1, Line 1 A floating point exception occurred in the user process. Current transaction is canceled.
SQL Server Express update KB921896 fails to install with error code 00002b2. I've tried this update several times with no luck. HELP offered is no help. Can't find this error code, and there is no text with the code.
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
update xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit: Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit: Could it be to do with this linked server setting? Collation Compatible right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane... (0 row(s) affected) (1 row(s) affected) How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect) (1 row(s) affected) If anyone has any ideas where to look next, I'd appreciate it. Thanks
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
I'm new to this forum. This 'problem' has occured many times, but I've always found a way around it. I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values. Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank? Thank you very much.
I am trying to update a value in the aspnet_Users table using a gridview. I'm currently using the vw_aspnet_Users view to select which columns want to display. I have ensured the primary key exists and have it selected as a column to display (although it will serve no purpose). Unfortunately when I click on the advanced tab, it does not let me tick either boxes as they are both greyed out. I have followed "solutions" to this problem in other posts but as I definitely have the primary key source present AND selected, I dont see what the problem is. The only thing I have noticed is that in the "Configure the select statement" window, you are unable to see the aspnet_Users table in the dropdownlist (only the view is present). Non of the views that I choose allow me to update, however, when I choose one of my own tables that I have made (and these appear in the dropdownlist) the tickboxes become available. Is there an underlying problem with editing SQL sources using views? I need a view as I need to display information found in aspnet_Users and aspnet_Membership The following error is what is displayed after I click 'Update' Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified Thanks!
hi , I TRY TO UPDATE THE DATA IN THE DATAGRID, IT THROWS THE ERROR LIKE Specified cast is not valid. BELOW I ATTACHED, PLS GUIDE IS protected void updategrid_UpdateCommand(object source, DataGridCommandEventArgs e) { cmd.CommandText = "Update EMp_master set Empname=@Empname ,pwd=@pwd, emailId=@EmailId, Designation=@Designation,Type=@UserType,Reportsto=@reportsTo where empid=@id";
cmd.Parameters.Add("@Empname",SqlDbType.VarChar).Value = ((TextBox)e.Item.Cells[1].Controls[0]).Text;--- i got this error in this line cmd.Parameters.Add("@pwd", SqlDbType.VarChar).Value = ((TextBox)e.Item.Cells[2].Controls[0]).Text; }
I create the View which consist of multiple same table. When I want to update via the View, it doesn't allow me to update it, it gave out the following message, View or function 'Table1' is not updatable because the modification affects multiple base tables.
I have a prject I am working on that uses a database connection to SQL Express 2005 to look up info of a person. The question I have is that I downloaded some code from a tutorial and it works fine but has some extra info that I dont want to use for example it saves FirstName, LastName, Title, Adress, Phone # in a table but I only want to use the Firstname, LastName and Phone# in the table but when I take these out of the Table with the wizard the Update Button I have has a error saying it is not parameterized and is missing Adress, Title am I missing something here? I can leave the Adress and Title in the table and just not have them on my Form and it works fine but I would like to get rid of the unwanted parameters from the table and have my Update Button work.
The clients are able to connect and pull data on existing accounts. However when users try to create a new version of an existing account or an actual new account, they get:
Db UpDate Error
ERROR 40002 01000: [Microsoft][ODBC SQL Server Driver][SQL Server]Command has been aborted.
Unable To Set Version Info. MODE: NEW
Class[APPOBJ.SerVersion]
Is this some type of version conflict? I've tried finding more information about the Error 40002 but I don't find the one specific to mine. I'm not an SQL expert but I would like to try and find out what the error message is trying to tell me. Any help would be greatly appreicated.
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL WHILE @@ROWCOUNT > 0 BEGIN SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL END SET rowcount 0
Hello I am trying to update a column containing URL's and include the "www." which had previously been omitted on many URL's in the column. But I get an error when trying to UPDATE I have tried: UPDATE table_nameSET URL = http://www.a2zdom.com/*WHERE URL = http://a2zdom.com/* I have tried and left out the http: and also the /* but nothing works. Is this type of update not possible? Thanks
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
string cmdTxt = "Update penberry_SubjectName set SubjectLeaderId IN ( SELECT userid FROM aspnet_users WHERE username = @subjectLeaderName) where SubjectCode = @subjectCode";
SqlConnection sqlconn = new SqlConnection(sqlConnStr); SqlCommand sqlCmd = new SqlCommand(cmdTxt, sqlconn);
hai..i cannot solve this sproblem...i need to update my databse after i edited the textfield.then system will return me 'UPDATE SUCCESS'(because i return value UPDATE SUCCESS).this seem like nothing wrong.but when i check the database,data did not update!!have anybody know that? thanks!!my coding as below, #Region "Edit student Then save" Public Function editstudent(ByVal sIC As String, ByVal school As String, ByVal phone As String, ByVal pic As String, ByVal pwd As String, ByVal sname As String, ByVal gender As String, ByVal add As String, ByVal standard As String, ByVal osic As String) As String ' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim command As String Dim mycommand As SqlCommand command = "Update student_info set sic =@sic, Spassword =@pwd," & _ "sname =@sname, sphone=@phone,saddress=@add, sstandard=@standard,sschool=@school ,Sparent_IC=@pic ,sGender=@gender Where sic =@osic" mycommand = New SqlCommand(command, myConnection) mycommand.CommandType = CommandType.Text '' Add Parameters to Command Dim parameterIC As SqlParameter = New SqlParameter("@sic", SqlDbType.VarChar, 12) parameterIC .Value = sIC mycommand.Parameters.Add(parameterIC ) Dim parameteroIC As SqlParameter = New SqlParameter("@osic", SqlDbType.VarChar, 12) parameteroIC .Value = osic mycommand.Parameters.Add(parameteroIC ) Dim parameterpwd As SqlParameter = New SqlParameter("@pwd", SqlDbType.VarChar, 50) parameterpwd .Value = pwd mycommand.Parameters.Add(parameterpwd ) Dim parametername As SqlParameter = New SqlParameter("@sname", SqlDbType.VarChar, 100) parametername .Value = sname mycommand.Parameters.Add(parametername ) Dim parameteradd As SqlParameter = New SqlParameter("@add", SqlDbType.VarChar, 100) parameteradd .Value = add mycommand.Parameters.Add(parameteradd ) Dim parameterphone As SqlParameter = New SqlParameter("@phone", SqlDbType.VarChar, 12) parameterphone.Value = phone mycommand.Parameters.Add(parameterphone) Dim parameterstandard As SqlParameter = New SqlParameter("@standard", SqlDbType.VarChar, 50) parameterstandard.Value = standard mycommand.Parameters.Add(parameterstandard) Dim parametersschool As SqlParameter = New SqlParameter("@school", SqlDbType.VarChar, 100) parametersschool.Value = school mycommand.Parameters.Add(parametersschool) Dim parameterpic As SqlParameter = New SqlParameter("@pic", SqlDbType.VarChar, 12) parameterpic.Value = pic mycommand.Parameters.Add(parameterpic) Dim parametergender As SqlParameter = New SqlParameter("@gender", SqlDbType.VarChar, 1) parametergender.Value = gender mycommand.Parameters.Add(parametergender) Try myConnection.Open() mycommand.ExecuteNonQuery() myConnection.Close() Return "Update Success !!!" Catch exc As Exception Return exc.ToString End Try End Function#End Region End Class
Im getting this error when i try to change my value of my int RoleID: UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.
Source Error:
Line 373: tryLine 374: {Line 375: rowsAffected = dbCommand.ExecuteNonQuery();Line 376: }Line 377: finallyThe code that courses this error should be this code from my DatabaseHandler class:... queryString = "SELECT unitID FROM Units WHERE containerID = @containerID AND logDateTime = @logDateTime"; dbCommand = new SqlCommand(queryString, dbConnection); //ContainerID Param IDataParameter dbParam_containerID = new SqlParameter(); dbParam_containerID.ParameterName = "@containerID"; dbParam_containerID.Value = containerID; dbParam_containerID.DbType = System.Data.DbType.String; dbCommand.Parameters.Add(dbParam_containerID); //LogDateTime Param IDataParameter dbParam_logDateTime = new SqlParameter(); dbParam_logDateTime.ParameterName = "@logDateTime"; dbParam_logDateTime.Value = logDateTime; dbParam_logDateTime.DbType = System.Data.DbType.DateTime; dbCommand.Parameters.Add(dbParam_logDateTime);...The logDateTime param comes as a param to the method this code i located in. In my database the two tables are created as the following:CREATE TABLE [dbo].[Roles] ( [RoleID] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [RoleLevel] [int] NOT NULL ) CREATE TABLE [dbo].[Users] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [Firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MobilPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IsActive] [bit] NOT NULL , [RoleID] [int] NOT NULL ) To me it all looks fine, but my application doesn't agree with me....can anybody help here?
The trigger does set my CommAudit field to 1 when imaSlsCls is changed, but at the end of the day when I issue the UPDATE command to set all CommAudit back to 0 I get an error. Is there a differant way to make it work?? See trigger, update command & error below. Thanks for any help, Tom
*** TRIGGER *** CREATE TRIGGER [tblItemMaster_utr] ON dbo.tblItemMaster FOR UPDATE AS IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted) BEGIN update tblItemMaster set commaudit = 1 from inserted, tblItemMaster where inserted.imaitnbr = tblItemMaster.imaitnbr END
*** UPDATE COMMAND *** UPDATE tblItemMaster SET CommAudit = 0 WHERE CommAudit = 1
*** ERROR *** Server: Msg 512, Level 16, State 1, Procedure tblItemMaster_utr, Line 5 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
hi, someone please tell me what's wrong with this update statement It's supposed to get values for employee location from a test table, based on the first name and last name match, and update the outer table. I get this error...
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'a'.
update abs_employee a set a.office_location = (select b.Location from abs_emptest b where b.lastname = a.last_name and b.firstname = a.first_name)
I am getting an error message when trying to run this update statement:
update table1 set col1 = (select substring(col2,1,2) + '-' + substring(col2,3,7) from table1)
Error message:
Server: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
I have a following update statement that returns an error:
update job_schedule_rep set tlbkup = (select Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuf f(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3 ,0, ':'), 6, 0, ':')), 100),7) as 'Transaction Log Backup' from (select name, job_id, active_start_time from sysjobschedules where name like 'TLBkup%') as tl join (select name, job_id from sysjobs where name like 'TL Backup%') as sjt on sjt.job_id = tl.job_id where sjt.name not like '%Maintenance%' order by sjt.name)
Here is an error:
Server: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
I have tried replacing '=' with in and exists but it didn't make any differe nce.
I have the following problem: We replicate data from our mainframe (DMS II) to our SQLServer (and Informix) using Databridge and stored procedures. We found that there was a problem with the Update stored procedure when it doesn an update of a row that doesn't exist. Instead of returning an error so that databridge can call the Insert stored procedure, it just says "0 rows updated". We do not have the problem to Informix. Do you have any idea how you can handle this?