It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 RepliesWhile troubleshooting a problem at one of our customers, we noticed the following consistent erratic behaviour of our product with the 1.1.105 JDBC driver (as well as the 1.2 CTP) when the XACT_ABORT user option has been set on the SQL server:
Set the user option XACT_ABORT on a database server with the following SQL query:
exec sp_configure 'user options', '16384'
reconfigure
Now run the following
Code Snippet
import java.sql.*;
import java.util.*;
public class Test {
public static void main(String[] args) {
try {
// Load the JDBC driver.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Establish the connection to the database.
String url = "jdbc:sqlserver://server:1433";
Properties props = new Properties() ;
props.setProperty("user","user");
props.setProperty("password","password");
props.setProperty("DatabaseName","databasename");
props.setProperty("selectMethod","cursor");
Connection conn = DriverManager.getConnection(url, props);
// Set up simple prepared Update statement
PreparedStatement pstmt = conn.prepareStatement("UPDATE table SET field=0 where field=1", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
Boolean isresultset = pstmt.execute();
System.out.println("IsUpdatecount? " + !(isresultset));
Integer updatedrecords=pstmt.getUpdateCount();
/*updatedrecords should be 0 or higher if isresultset is false
if updatedrecords=-1, the result should be a resultset, which it cannot be, but seems to be*/
System.out.println(updatedrecords+ " row(s) affected");
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}
You can revert the database server back to the correct behavior bij executing the following SQL query
exec sp_configure 'user options', '0'
reconfigure
As you can see, when the SQL server user option XACT_ABORT is set, prepared UPDATE statements through the SQL JDBC driver will fail consistently when selectMethod=cursor and ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY are set, with the execute method indicating an UpdateCount is returned, but the UpdateCount itself claiming a resultset has been returned. On checking that, there is no resultset.
Somehow the option, which should do the following:
xact abort
Rolls back a transaction if a Transact-SQL statement raises a run-time error.
seems to roll back the results of an update statement in place ?
Is this a bug or something I seem to be doing wrong ?
Kind regards,
Niels
PS: With the 1.0 JDBC driver with this setting another problem occured, which pointed to a rollback done within a transaction without any exception being raised through JDBC, consistent with Angel Saenz-Badillos post on http://www.mombu.com/microsoft/sql-server-jdbc-driver/t-behavior-of-connectioncommit-249105.html
We had hoped the 1.1 driver would at least raise the exception through JDBC, but alas, the result mentioned above happened, which in the end we could track back to the server setting.
--Update--
When an SQL Trace is active on the server the following two error messages appear in the log, none of which are visible through the JDBC driver error handler:
Exception Error: 156, Severity: 15, State: 1
Exception Error: 16954, Severity: 10, State: 1
I'm trying to update rows in a simple table that has a UNIQUE KEY CONSTRAINT defined on one of its columns. Here is the DDL for the table:
CREATE TABLE [dbo].[SEC_USER](
[SEC_USER_ID] [int] IDENTITY(1,1) NOT NULL,
[USER_CODE] [varchar](100) NOT NULL,
[USER_NAME] [varchar](128) NOT NULL,
[EMP_CODE] [varchar](6) NOT NULL,
[Code] ....
When trying to execute the UPDATE statement the query fails with a constraint violation error:
Violation of UNIQUE KEY constraint 'UQ__SEC_USER__A039F1EE62FE8444'. Cannot insert duplicate key in object 'dbo.SEC_USER'. The duplicate key value is (34337).
What has me baffled is that I'm not doing any insert. Also, the value that it's referencing - 34337 - doesn't exist in the table at all. I'd rather not drop the constraint.
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
When updating automaticallly, this update always fail, I have put the configuration manager /server browser : the only one that was disabled, on manual (I have not the automatic option for this one) but nothing works, what can I do ? This is a new laptop I have got (XP pro) and I am going through the process of updating many things, only this one fails, any help appreciated.
View 5 Replies View Relatedthe stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies View RelatedHello,
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
I just wanted to post a difference I found between SQL 2000 and SQL 2005 regarding UDPATE statements that are done on a join. I understand that if tables are designed correctly this won't be a problem. But, when you inherit a bad design, you are unfortunately stuck with it. Hopefully this will help ease data differences in your migration from SQL 2000 to SQL 2005.
Run this code on a SQL 2000 connection, then run on SQL 2005. My guess on the behavior difference is strictly performance based since 2005 pulls the top result. Either way it can cause a lot of head scratching if you're not aware of it.
IF OBJECT_ID('tempdb..#UpdateTestA') IS NOT NULL
DROP TABLE #UpdateTestA
IF OBJECT_ID('tempdb..#UpdateTestB') IS NOT NULL
DROP TABLE #UpdateTestB
CREATE TABLE #UpdateTestA(
UpdateTestA int identity(1, 1),
FullName varchar(20),
UpdateData varchar(10))
CREATE TABLE #UpdateTestB(
UpdateTestB int identity(1, 1),
FullName varchar(20),
UpdateData varchar(10))
INSERT INTO #UpdateTestA(
FullName)
VALUES ('Barney Rubble')
INSERT INTO #UpdateTestB(
FullName,
UpdateData)
VALUES ('Barney Rubble', 'First')
INSERT INTO #UpdateTestB(
FullName,
UpdateData)
VALUES ('Barney Rubble', 'Second')
SELECT * FROM #UpdateTestA
UPDATE a
SET a.UpdateData = b.UpdateData
FROM #UpdateTestA a
INNER JOIN #UpdateTestB b on b.FullName = a.FullName
SELECT * FROM #UpdateTestA
DROP TABLE #UpdateTestA
DROP TABLE #UpdateTestB
Hope this solves a problem that you were having too.
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
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
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.
The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)
Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
QM_UID count(*)
25 2
26 3
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 5
36 4
37 4
38 4
39 4
40 3
...
--- Script to replicate problem
-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant
-- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error
-- at 100000 rows but does at 1000000 rows.
-- Create a table
CREATE TABLE tmp (
[QM_ADD_OP] [char](6) DEFAULT '' NOT NULL,
[QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ADD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL,
[QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL,
[QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_NUMBER] [char](12) DEFAULT '' NOT NULL,
[QM_REV_NUM] [char](6) DEFAULT '' NOT NULL,
[QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REV_TIME] [int] DEFAULT -1 NOT NULL,
[QM_REV_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_PART] [char](6) DEFAULT '' NOT NULL,
[QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_RUN_NUM] [int] DEFAULT 0 NOT NULL,
[QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL,
[QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL,
[QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL,
[QM_PRINTED] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL,
[QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CRED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CRED_OP] [char](6) DEFAULT '' NOT NULL,
[QM_HELD] [smallint] DEFAULT 0 NOT NULL,
[QM_PROOF] [char](12) DEFAULT '' NOT NULL,
[QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REC_TIME] [int] DEFAULT -1 NOT NULL,
[QM_OWN_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RESP_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RESP_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL,
[QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PREF_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_LATE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_TITLE] [char](72) DEFAULT '' NOT NULL,
[QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL,
[QM_TAX_REF] [char](22) DEFAULT '' NOT NULL,
[QM_CONTACT] [char](36) DEFAULT '' NOT NULL,
[QM_PHONE] [char](22) DEFAULT '' NOT NULL,
[QM_FAX] [char](22) DEFAULT '' NOT NULL,
[QM_ORDER] [char](20) DEFAULT '' NOT NULL,
[QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL,
[QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL,
[QM_REP] [char](12) DEFAULT '' NOT NULL,
[QM_REP_1] [char](12) DEFAULT '' NOT NULL,
[QM_REP_2] [char](12) DEFAULT '' NOT NULL,
[QM_REP_3] [char](12) DEFAULT '' NOT NULL,
[QM_REP_4] [char](12) DEFAULT '' NOT NULL,
[QM_REP_5] [char](12) DEFAULT '' NOT NULL,
[QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL,
[QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL,
[QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_GRADE] [smallint] DEFAULT 0 NOT NULL,
[QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_ORIENT] [smallint] DEFAULT 0 NOT NULL,
[QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL,
[QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL,
[QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_FOLIO] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL,
[QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL,
[QM_PROFILE] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PO_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PO_OP] [char](6) DEFAULT '' NOT NULL,
[QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_DLY_TIME] [int] DEFAULT -1 NOT NULL,
[QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL,
[QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_INV_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL,
[QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL,
[QM_CURR] [char](6) DEFAULT '' NOT NULL,
[QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL,
[QM_NC_REASON] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL,
[QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL,
[QM_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_CODE] [char](36) DEFAULT '' NOT NULL,
[QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL,
[QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL,
[QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL,
[QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL,
[QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_UID] [int] DEFAULT 0 NOT NULL,
[QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL
) ON [PRIMARY]
GO
-- Create an index on the table
CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]
GO
-- Populate the table
declare @Counter as int
SET NOCOUNT ON
set @Counter = 1
while @Counter < 100000
begin
insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);
set @Counter = @Counter + 1
end
GO
-- Update QM_UID to a sequential value
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
-- Find rows with a duplicate QM_UID (there should be no duplicate)
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
--drop table tmp
-- output from select @@VERSION
-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
When I make a call to a stored procedure to update a record, the update does not happen. No error is thrown, there are just no rows updated. When I look at SQL Server Profiler, this is what I see:
exec TTN_Update_RecurringIssueVehicle @RecurringIssueVehicleID='00962233-6EC3-42CE-ABBD-1851F1176D63',@RecurringIssueID='66FE821A-9881-4368-B975-5B04975C5E16',@RecurringIssueProblemID='ABED20CD-CB37-4491-903F-553555EEE47C',@MarketID=1,@VehicleNumber=268,@AddedDate=N'12/14/2006 3:30:00 PM',@AddedBy=12,@ResolvedDate=N'12/15/2006 10:19:45 AM',@ResolvedBy=12
It seems to be putting a carriage return in the middle of one of my date parameter values, and also seems to be putting those values in double quotes. If I try to run this in SQL Management Studio, it throws an error. If I take out the carriage return, and fix the quotes, it succeeds. Here is my VB.Net code:
<DataObjectMethod(DataObjectMethodType.Update)> _
Public Function Update_RecurringIssueVehicle( _
ByVal RecurringIssueVehicleID As Guid, _
ByVal RecurringIssueID As Guid, _
ByVal RecurringIssueProblemID As Guid, _
ByVal MarketID As Integer, _
ByVal VehicleNumber As Integer, _
ByVal AddedDate As DateTime, _
ByVal AddedBy As Integer, _
ByVal ResolvedDate As DateTime, _
ByVal ResolvedBy As Integer) As Integer
dbCmd.CommandText = "TTN_Update_RecurringIssueVehicle"
dbCmd.Parameters.Clear()
dbCmd.Parameters.AddWithValue("@RecurringIssueVehicleID", RecurringIssueVehicleID)
dbCmd.Parameters.AddWithValue("@RecurringIssueID", RecurringIssueID)
If RecurringIssueProblemID <> Guid.Empty Then dbCmd.Parameters.AddWithValue("@RecurringIssueProblemID", RecurringIssueProblemID)
dbCmd.Parameters.AddWithValue("@MarketID", MarketID)
dbCmd.Parameters.AddWithValue("@VehicleNumber", VehicleNumber)
dbCmd.Parameters.AddWithValue("@AddedDate", AddedDate.ToString())
dbCmd.Parameters.AddWithValue("@AddedBy", AddedBy)
If ResolvedDate <> DateTime.MinValue Then dbCmd.Parameters.AddWithValue("@ResolvedDate", ResolvedDate.ToString())
If ResolvedBy <> -1 Then dbCmd.Parameters.AddWithValue("@ResolvedBy", ResolvedBy)
Dim retVal As New SqlParameter("@RetVal", SqlDbType.Int)
retVal.Direction = ParameterDirection.ReturnValue
dbConn.Open()
dbCmd.ExecuteNonQuery()
dbConn.Close()
Return CInt(retVal.Value)
End Function
Any ideas are greatly appreciated - thanks
We have an UPDATE trigger that is failing. This seems like a basic task - we want to write a record to a separate tracking table when our main transaction table is updated for any reason. Our assumption is that we have a reference to the data from the "inserted" record that was just updated. The scenario here is that we are running a batch process which READS several thousand records in our transaction table each evening.
We then mark each individual record as processed on the transaction table and expect that the UPDATE trigger will successfully fire (it is not). The version of our trigger listed below shows our attempt to deal with the fact that TransactionID does NOT exist from "inserted." We also have a version of this trigger that deals with INSERTS - it works flawlessly.
ON [dbo].[FPS_Transaction]
AFTER UPDATE
AS declare @trxId uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON
[code]...
Starting saturday all of our SSIS packages on a server (64-bit) starting failing (hundreds of them) the error is:
Precompiled script failed to load. Attempting to reload the script with updated data. For more information, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).
That Knowledgebase link talks about SP2 fixing the issue but we have SP2 already on the server. The sysdtslog90 table is just packed with these as each script inside each package is getting the same error. Looking at the system log the following were installed as part of windows update shortly before the errors started occuring:
- Update for Windows Server 2003 x64 Edition (KB936357)
- Security Update for Windows Server 2003 x64 Edition (KB926122)
- Microsoft .NET Framework 3.0: x64 (KB928416)
- Security Update for Microsoft .NET Framework, Version 2.0 (KB928365)
- Security Update for Excel 2003 (KB936507)
- Update for Outlook 2003 Junk Email Filter (KB936557)
Hi All,
I am trying to update a huge table with about 70 million records and the table do not have a primary key.
I want to update a "FLAG" field in the table with value 'N' default value of flag fields for all records is NULL.
I want to update the whole table in chunks say 50,000 records a time...
like
update tablename (first 50000)
set flag = 'N' where flag = NULL.
Hope I am clear..:-)
thanks
hardlyworking..
I built a small package two years ago that uses Flat File Sources to copy in small text data files. Each source connection object has a UNC path to flat text files on another server. The source system changed, so I opened the package and updated the UNC path in one Connection Manager object, and clicked OK. The Flat File Source Editor that uses this source seemed to be able to see the new location when I clicked "Preview". Then I went back to the file source, and the connection had reverted back to the original one. it would not save the new UNC path.
I am using SQL Server 2012 SP2 with SSDT (run as admin). I closed the package in SSDT, edited the connection strings using XMLnotepad, and was then able to open, test, build and deploy the package.
It seems that the Source object will not let itself be changed. The other option is to delete it and recreate it, but I didn't want to remap the fields.
Why updating a connection object is not working?
Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?
What is the alternatives??
I have to match two tables up and use records from one to update the other and creating a view isn't working....
Hi,
I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.
DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product
DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty
I made the following trigger for handling single row updates, which works fine.
UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).
Hi,
I have the following case:-
File a(transaction File)__File b(summary File)___File c(summary)
acctno___Value DB/CR_____Acctno DB,CR_______acctno DB,CR
1___________100db___________1______________________1
1___________300cr___________2______________________2
1___________600db
2___________700db
I want to update the account on table b and c from table a.
My current update is just for one table as follows:-
UPDATE amaster
SET cmdb = cmdb +
(SELECT SUM(transact.damt) AS Expr1
FROM transact
WHERE (acctno = amaster.acctno)
GROUP BY acctno),
cmcr = cmcr +
(SELECT SUM(transact.camt) AS Expr1
FROM transact
WHERE (acctno = amaster.acctno)
GROUP BY acctno)
FROM amaster INNER JOIN
transact ON transact.acctno = amaster.acctno
thanks
I'm new to adp w/ sql server but I have to use it on a project i'mdoing...One of the MUSTS for this project is the ability to update a 00 - 09text value with the appropriate text description from another table...Easy as pie in .mdb. Of course In the stored procedure it barks at meand tells me that an update query can only have one table.. ouch thathurts...I'm currently reading on the subject but this group has been veryhelpful in the past.....I found this link...http://www.sqlservercentral.com/col...stheeasyway.aspUnfortunetly I'm using MSDE not Enterprise so I don't think I can usethe query analyser.. But I tryed it in my Access ADP anywayit barked at me..I tried to go from this....SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1FROM dbo.TEST INNER JOINdbo.LU_SEX ON dbo.TEST.SEX_DP1 =dbo.LU_SEX.SEX_DECTo this...UPDATE dbo.TEST.SEX_DP1SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODEFROM dbo.LU_SEX INNER JOINdbo.TEST ON dbo.LU_SEX.SEX_DEC =dbo.TEST.SEX_DP1Maybe I need a good book on this?Thanks,Charles
View 2 Replies View RelatedHi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies View RelatedLet me set the scene:
I have an update trigger on a table. When a specific column is updated, I get the rowid from 'inserted' and then pass it via service broker to another database that will fire off a maintenance routine at a later time. This whole process seems to work fine if I update a single row at a time through Query Analyzer.
During testing (of the service broker part) I found that if in Query Analyzer I run an update that updates all of the records at once, then the trigger seems to fire only once for the entire process, therefore killing the rest of my process.
I would have thought that regardless of how a record was being updated the trigger would fire atomically for each row.
Any guidance on this would be MOST appreciated!
How to update column with multiline comments.
eg. I need to update Notification column in my table as below.
Exit Formalities Notification sent to:
Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress
I use the Microsoft SQL Server 2005 JDBC Driver (1.0.809.102 and 1.1.1320.0) to connect to a SQL Server 2005 database. I'm currently implementing a generic data access layer that executes an arbitrary SQL statement:
public void prepareQuery(String sql) throws SQLException, ClassNotFoundException {
PreparedStatement stm = getConnection().prepareStatement(sql);
ParameterMetaData pmd = stm.getParameterMetaData();
int numPar = pmd.getParameterCount();
System.out.println("Number of parameters: " + numPar);
// ... acquire and process 'numPar' parameters ...
}
Exemplarily, I created a table named 'TEST_TABLE' with three Integer columns ('C1', 'C2' and 'C3') and a Varchar column ('C4'). Calling
prepareQuery("INSERT INTO [TEST_TABLE] ( [C1], [C2], [C3], [C4] ) VALUES ( 1, 2, ?, ? )")
gives the following result:
Number of parameters: 4
This is definitely wrong because that statement has only two parameters, one of type Integer and one of type Varchar. How can I get the correct number and types of the parameters?
how to do this
i have table of employee ,evry employee have a unique ID "empid"
empid VAL_OK
--------------------------
111 0
222 0
333 0
now insert multiple insert to my work_table shifts for all month for evry employee
like this
(this is work_table)
empid date val
--------------------------------------------------
111 01/02/2008 1
111 02/02/2008 2
...............
111 29/02/2008 5
--next employee
222 01/02/2008 1
222 02/02/2008 4
...............
222 29/02/2008 6
--next employee
333
--next employee
444
--next employee
555
-------------------------------------------------------------
now i need for evry OK insert (for all month) each employee
go to the TB_Employee
and update each employee once !!
from VAL_OK=0 to VAL_OK=1
like this
empid VAL_OK
--------------------------
111 1
222 1
333 1
----------------------
like this i know who is the employee have shift for all month and who NOT !
i think it like this
Code Snippet
Create trigger for_insert on tb_work
For insert
begin
if @@rowcount = 1
Update tb_employee
Set
val_ok= 1
else
/* when @@rowcount is greater than 1,
use a group by clause */
Update tb_employee
set
val_ok= 1
select empid from tb_work
group by tb_work.empid
End
TNX
hi need help
i have this stored procedure
the problem is that
i canot update like this not more than 20 - 30 rows
(i send it from a web page from check box)
it work but it limited rows for update not more than 20 - 30 rows
in one time
Code Snippet
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [sto_update_snha]
@id varchar(1000)
as
UPDATE Snha
SET fld5 = 3
WHERE charindex(','+CONVERT(varchar,[id])+',',','+@id+',') > 0 AND (fld5 = 2)
TNX
spaces problem on multi update delete stored procedure
on the the insert i get it like this
58830803, 55329429, 308962604, 55696314, 309128726
as you see only the first empid=58830803 is without spaces
how to fix this
my code
Code Snippet
DECLARE @empid varchar(500)
set @empid ='58830803, 55329429, 308962604, 55696314, 309128726'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedOk I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
We have an application that runs Jobs, each of which affect ## number of child objects (usually around 1M). When a thread gets to 5000 updated child objects it bulk inserts into a table called ActionLog with the child Id and JobId.
When the job is complete a sproc SUMs the children from the ActionLog table:
select sum(id) from ACTIONLOG where JOBID = @JobId;
It then updates the Jobs table AffectedObjectCount column with the sum(*) from above.
Instead of writing to the ActionLog table and calculating the SUM at the end I would like to do this 'real time'. After the bulk insert I would like to update the AffectedObjectCount column with the number of rows that were just bulk inserted. I tried this in the past and ran into major contention issues. There are usually 20 threads running a job so there exists a lot of potential for deadlocks.
Is there a recommended way to handle updating one column on one row from multiple threads? What is the best practice for a counter like this?