Update Table1 Set Field1=null;commit; Where I Can Look For The User,who Execute This Statment?

Feb 11, 2008

update table1 set field1=null where id=1; commit;

where I can look for the user,who execute this statment?

It was 6.02.2007 or 7.02.2007.
All users connect using SQL Serwer Authentication.
It is test database and from 1 to 3 users connect to it, and submit not more then 10 SQl statmant per day.




View 3 Replies


ADVERTISEMENT

JOIN ON CONTAINS(Table1.Field1, Table2.Field2)

Jul 20, 2005

Hi,I am getting errors in the following... Is it even possible to join onCONTAINS?SELECT ListA.ContentFROM ListA LEFT OUTER JOIN ListBON CONTAINS(ListB.Content, ListA.Content)WHERE ListB.Content IS NULLThanks!

View 4 Replies View Related

Get Records In Table1 Not In Table2 - Not Exists Get Null Result

Jun 9, 2015

I'm trying to get the records in table1 not in table2 the query is

Select * from table1 where not exists (select * from table2)
--table1 and table2 ,structure are same

It will get a null result

Strange thing , while I add a where condition in the subquery, it worked.

Select * from table1 where not exists (select * from table2 where table1.ID =table2.ID)

It will get the result as expected.

I just thought it would compare all the fileds for the outer query and inner query, but seems not. Why, I add a condition, it worked ?

View 4 Replies View Related

How To Update Table1 From Table2

Nov 22, 2007

Hi

I have table1 with tow columns ( col1 and col2)
table2 colums (col1 and col2)

Table1 -- col1--- col2
-----------A ---- dfg
-----------B ---- ght

Table2-- Col1--- col2
-----------A ----- jjk
-----------B ----- lkl


How do I update col2 of Table1 with the data from col2 of Table2 in a Single Query

View 3 Replies View Related

UPDATE Column In Table1 From 2 Columns...

Jan 22, 2007

Hey ya'll...
I need to create a query that I can run that will allow me to essentially take the first initial and last name and combine them from two columns in one table and update one column with that new data in another table. Example of need:
UPDATE tblLogin.UserName with (first character(tblEmployee.FName)+(tblEmployee.LName)) WHERE tblLogin.EmployeeID = tblEmployee.EmployeeID.
That was TOTALLY pseudo code and I know I'll need a join statement in there somewhere. Both tables reside on the same database, such as this:
dbEMPLOYEE (Database)--> tblLogin (Table)----> UserName (Column)--> tblEmployee (Table)----> FName (Column)----> LName (Column)
Any help would be GREATLY appreciated! Hopefully something soon - this was handed to me a few minutes ago and I have a few minutes to get it done.iSheahan

View 5 Replies View Related

Sum Up Values In Table2 Where IDs Are Equal To Table1 And Then Update Value

Dec 17, 2013

I'm trying to update a value into a table a sum. The two tables have ID values. These ID values appear once in Table1 and multiple times in Table2. I'm currently trying to sum up the values in Table2 where the IDs are equal to Table1 and then update the value:

UPDATE [Table1] SET
[Total] =
(SELECT SUM([Table2].[QTY]) FROM [Table2],
[Table1] WHERE [Table1].[ID] = [Table2].[ID]
GROUP BY [Table1].[ID])
FROM [Table1], [Table2] WHERE [Table1].[ID] = [Table2].[ID]

View 2 Replies View Related

Case Sensitivity...Sellect * From Table1 Or Table1... Which One?

Feb 1, 2008

Hi everybodyI'm a newbie to SQL-Server 2005 so please excuse me for my silly questionsRecently i wrote a simple web-Page in which i select data from a table and then bind them to a GridView on the formevery thing was simple as follows:
        SqlConnection Con = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True");        Con.Open();
        SqlDataAdapter da = new SqlDataAdapter("SELECT [ID], [Name], [Date] FROM [Table1] ORDER BY [Date]", Con);        DataSet ds = new DataSet();        da.Fill(ds,"MyTable");
        GridView1.DataSource = ds.Tables["MyTable"];        GridView1.DataBind();
But I have a question , when i change the [Table1] to [table1] (With lowercase T) , i receive the following errormessage:
 Invalid object name 'table1'.
why is that? could anyone help me? even i tried to test this issue in [Microsoft Management Studio] and i got thesame result . realy what's the reason? before and with sql server 2000 i didn't have these issue.
Thanks in advance.Regards.
 

View 5 Replies View Related

Error 209 Ambiguous Column Name ...Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK ....

Aug 28, 2006

i'm getting following exception when i try to execute stored procedure.{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting"Transaction count after EXECUTE indicates that......" In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN/* Its failing at    MemberID = tTempResult.MemberID   bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID    */   here is my stored procCREATE PROCEDURE dbo.ExportFile   @intMonth INT,   @intYear INT,   @dtFirstDayOfMonth DATETIMEAS  BEGINBEGIN TRANSACTION  /*   I have some logic here that will select rows into temporary table   #TEMPRESULT  */    UPDATE    dbo.RebateInstanceItem   SET     ResubmitCreated = @dtmNewCreated  FROM #TEMPRESULT tTempResult  WHERE     MemberID = tTempResult.MemberID       AND RebateInstanceItem.IsResubmit = 'Y'    AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated   OR RebateInstanceItem.ResubmitCreated IS NULL)      IF @@ERROR<>0    GOTO ERR_HANDLER // when error it will goto error_handler that will rollback  DROP TABLE #TEMPRESULT  IF @@ERROR<>0   GOTO ERR_HANDLERCOMMIT TRANSACTIONRETURN 0ENDERR_HANDLER: ROLLBACK TRANSACTION RETURN 1GO

View 5 Replies View Related

Update Table2 With Last Visited Date Based On Table1 Entry

Oct 30, 2013

I have two tables T1 and T2

T1 is having User : Visiting Date
T2 IS having User : Last Visited Date

Now I have to update T2 with last visited date based on T1 Entry.

View 1 Replies View Related

Create Delete Trigger On Table1 To Update A Filed On Table2

Jan 11, 2008

Hi everyoneI am trying to create a DELETE Trigger. I have 2 tables. Table1 andTable2. Table 2 has all the same fields and records as Table1 + 1extra column "date_removed"I would like that when a record is deleted from Table 1, the triggerfinds that record in Table2 and updates the date_removed filed withcurrent time stamp.The primary key on both is combination of domain,admin_group and cn.CREATE TRIGGER [tr_updateTable2] ON [dbo].tbl_Table1FOR DELETEASUpdate Table2SET date_removed = getDate()I'm stuck here, how do I manipulate on Table2 only the records thatwere deleted on Table1, so to only update date_removed filed for themin Table2?I guess i need to compare domain, cn and admin_group, but I don't knowhow.Any help would be greatly appreciatedThanks! :-)

View 3 Replies View Related

Transact SQL :: Transaction Count After EXECUTE Indicates Mismatching Number Of BEGIN And COMMIT Statements

Aug 19, 2015

I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."

View 2 Replies View Related

Update Statment

Aug 25, 2007

Hi,

I have an error for the following statement:-

DECLARE @MONTHNO char
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
DECLARE @batchno int
DECLARE @ACTIVEYEAR int

set @batchno = 5
set @ACTIVEYEAR = 2007

SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)

exec('update bmaster set '
+@DB+'='+@DB+' mysursor.damt,'
+@CR+'='+@CR+' mysursor.camt from bmaster inner join (select transact.year,transact.acctno,'
+' sum(transact.camt) as camt,'
+' sum(transact.damt) as damt,'
+' sum(transact.ccamt) as ccamt,'
+' sum(transact.cdamt) as cdamt'
+' from transact where transact.batchno='+@batchno+ ' and transact.year='+@ACTIVEYEAR
+' group by transact.year,transact.acctno) as mysursor on bmaster.year=mysursor.year and bmaster.acctno=mysursor.acctno')

the error as follows:-

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'mysursor'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

thanks for your help.

View 2 Replies View Related

Where Do I Put My SQL Update Statment

Sep 26, 2006

OK I have my SSIS Dataflow behaving the way it should but how does one map to a table that needs an updatde to a table.

In this case it is an Oracle Table so the SQl would liik like this...

UPDATE PPS_PRINCIPALSSET NAME = @Variable2WHERE (PRINCIPAL_ID = @Variable1)

View 4 Replies View Related

Update Statment

May 9, 2008

I cannot figure out how to update ufd column from this statment. I can select the columns I want to update but how can I change it to update them from the select?



SELECT ufd.* FROM Tbl uf

JOIN Received rr

ON uf.ID = rr.SSN AND uf.Id = 36

JOIN Tbl ufd

ON uf.S_Id = ufd.S_Id

WHERE uf.Col <> 'Employee' AND ufd.Id = 35

View 8 Replies View Related

SQL Server 2012 :: Transaction Count After EXECUTE Indicates Mismatching Number Of BEGIN And COMMIT Statements

Oct 23, 2015

I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.

Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)

[Code] ...

View 2 Replies View Related

UPDATE 2 Tables In One Statment

Sep 26, 2007

Hi guys,

Does anyone who how I update two tables with one UPDATE statment (i.e. joining them with an INNER JOIN). I'm using SQL Server.

These are going to be very large tables, so I'd rather not have to run an UPDATE twice as it's going to be s l o w...

Thank all

Dâi

View 1 Replies View Related

Commit Update To SQL Database Table

Feb 18, 2006

The following code will not update and commit the update to a SQL Database Table.  Now my where statement is looking for a Date field.  Could this be the problem?
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)


'Update a existing row in the table

DBAdd.CommandText = "UPDATE [D12_MIS] SET [CSJ] = @CSJ, [EST_DATE] = @EST_DATE, [RECORD_LOCK_FLAG] = @RECORD_LOCK_FLAG, [EST_CREATE_BY_NAME] = @EST_CREATE_BY_NAME, [EST_REVIEW_BY_NAME] = @EST_REVIEW_BY_NAME, [m2_1] = @m2_1, [m2_2_date] = @m2_2_date, [m2_3_date] = @m2_3_date, [m2_4_date] = @m2_4_date, [m2_5] = @m2_5, [m3_1a] = @m3_1a, [m3_1b] = @m3_1b, [m3_2a] = @m3_2a, [m3_2b] = @m3_2b, [m3_3a] = @m3_3a, [m3_3b] = @m3_3b WHERE [EST_DATE] = " & EstDateText


With DBAdd.Parameters

.AddWithValue("@CSJ", pvCSJ.Text)
.AddWithValue("@EST_DATE", tmp1Date)
.AddWithValue("@RECORD_LOCK_FLAG", tmpRecordLock)
.AddWithValue("@EST_CREATE_BY_NAME", CheckedCreator)
.AddWithValue("@EST_REVIEW_BY_NAME", CheckedReviewer)
.AddWithValue("@m2_1", vb2_1)
.AddWithValue("@m2_2_date", tmp2Date)
.AddWithValue("@m2_3_date", tmp3Date)
.AddWithValue("@m2_4_date", tmp4Date)
.AddWithValue("@m2_5", vb2_5)
.AddWithValue("@m3_1a", vb3_1a)
.AddWithValue("@m3_1b", vb3_1b)
.AddWithValue("@m3_2a", vb3_2a)
.AddWithValue("@m3_2b", vb3_2b)
.AddWithValue("@m3_3a", vb3_3a)
.AddWithValue("@m3_3b", vb3_3b)
End With
 
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
Dim rowsAffected As Integer = 0
Try
rowsAffected = DBAdd.ExecuteNonQuery
Catch ex As Exception
tb2_2.Text = ex.ToString()
Finally
DBAdd.Connection.Close()
End Try
tb2_1.Text = rowsAffected
 

View 4 Replies View Related

Commit Foreign Key/field Update

Jul 26, 2007

is there a way to cascade update to foreign keys where both fields (PK & FK) are located in two different database?

View 2 Replies View Related

Update Statment Taking Long Time

Mar 27, 2008



I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.

this is my sql update statment -
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null

It is using ole db connection. About 36,000 records that it is updating.

I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?


any input greatly appreciated.


View 7 Replies View Related

ADO.NET Transaction Fails To Update Database After .Commit()

Nov 24, 2003

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives???? Anyone else ever heard of such a thing. Please help, before I loose my sanity.

View 4 Replies View Related

Table1(col1) Clustred , Table1(col1,col2,col3) Nonclustered Index , Which One To Keep ?

Jun 27, 2007

For a table 'table1' in sqlserver 2000,

table1(col1) is clustred index 'Ix1' and table1(col1,col2,col3) is nonclustered index 'ix2'



is not tabel1(col1) 'ix1' a duplicate/redundant index ?



which index should be retained ?



or should both indexes remain on the OLTP table ?





Thanks a lot in advance.



View 1 Replies View Related

Newbie - How To Update Multiple Rows With Select Statment

Oct 25, 2004

Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

Pam

View 8 Replies View Related

How To Commit The Rest Of The Transactions Of An Update In Large Bulk?

Feb 8, 2004

I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed.
However, there is no more spare disk space for it to commit the transaction. Anyone can help?

View 2 Replies View Related

How To Replace Single Quote In INSERT Or UPDATE Statment Using SqlDatSource?

Jan 15, 2008

I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior.
For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this?
Thank you
 

View 10 Replies View Related

Transact SQL :: Multiple Update Top On Commit Transaction Doesn't Work

Jul 10, 2015

I have this sql stored procedure in SQL Server 2012:

ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes]
@newBatches as dbo.CreateBatchList READONLY
, @productId int
, @cLevelRatio int
, @nLevelRatio int
AS
set nocount on;

[Code] ....

View 4 Replies View Related

Problm Setting Up User. Error: User Is Null

Jul 20, 2005

On MS-SQL 2k I have a created user. Security for the SQL instance isset to mixed.When I attempt to connect to SQL using Enterprise client on anothermachine on the local network using Windows Auth I get the error: DBName-Login Failed for user ('null')- Reason: Not associated with atrusted SQL Server Connection.What am I over looking?TIA

View 1 Replies View Related

Insert And Delete Statment In One Statment

Jan 18, 2008



Hi all

I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?

Thank you in advance .....

Regards,
sms

View 15 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

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

View 9 Replies View Related

Select * From Something Where Field1 = N'literal'

Jul 23, 2005

What exactly is happening when a query is sent using the N in front ofthe string to be found?Under what conditions would someone use the N' in a query?I have been testing out some chinese text. I set up some fields ofnVarchar, nText and it works with an N. Without the N, it wont work.N also works with fields of varchar and text for english.Would this ever cause a problem to a query depending on how themachines regional settings are set? Why not just put N in all of thequeryies?If anyone has some ideas, I would be grateful for any and allinformation about the N.

View 1 Replies View Related

'isNull(Field1,0)' Should Return 0,but It Didn't, WHY?

Mar 4, 2004

--create a temp table like:
select * into #tmp from
(select 1 as ID
union all select 2
union all select 3
union all select 4
) as A

-then run this query:
select B.ID,isNull(B.GID,0) as GID from
(select #tmp.ID,A.GID
from
#tmp
left outer join
(select ID,1 as GID from #tmp where ID in (2,4)) A
on #tmp.ID = A.ID
) as B

--I thougth it should return:
10
21
30
41
--but it actually returned:
IDGID
11
21
31
41
--if I change GID value in '...(select ID,1 as GID from #tmp where ID in (2,4)...', for example 2, then it return:
IDGID
12
22
32
42

WHY?

View 4 Replies View Related

SELECT All Rows With Max Field1 For Field 2

Mar 19, 2008

I have a Log table which records items found on library shelves during a shelf scanning process. Each time a shelf is scanned all the items found are recorded in the log table and the ScanCount value is incremented (per shelf). This means that if I get the records with the highest available ScanCount value for each ShelfID, then I would have the most up to date picture of my library.

From this sample data I would like to return the latest rows (max scan count) for all shelves (which would be LogID: 7,8 (shelf A) 3,4 (Shelf B) 5,6 (Shelf C):

LogID ShelfID ScanCount ItemName
1 A 1 Dave
2 A 1 Alan
3 B 1 Mike
4 B 1 Andy
5 C 1 Mary
6 C 1 Mark
7 A 2 Sam
8 A 2 Will

I'm not sure how to do this - presumably with an inner select? Could someone help? thanks

View 2 Replies View Related

Error 15185: There Is No Remote User 'sa' Mapped To Local User '(null)' From The Remote Server 'DTS_FSERVER'.

Nov 10, 2006

I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.

I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.

I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.

The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:

Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

I'm sure the two errors are related. Any ideas what is going on?

View 7 Replies View Related

Error With NULL Using EXECUTE SCALAR

Dec 11, 2007

I keep getting an error message when I try to place the result of an execute scalar command into a text box.  The msg is:"conversion from 'DBNull' to type 'String' is not valid"
The code I am using is:
 
Dim con as OLEDBConnection
con =NewOleDB connection("Provider = MIcrosoft.JetOLEDB.4.0, Data Source = "c:caps.mdb")
Dim cmd As OLEDBCommand
cmd= NewOLEDBCommand("Select Product from [Inventory Table] Where [Customer ID] = " & grid View1.SelectdValue
texBox1.Text = cmd.ExecuteScalar
 
The code works fine as long as there is a value for the Product.  However if the value in the database is NULL I get an error message: :"conversion from 'DBNull' to type 'String' is not valid". How do I wok around this?
Chas28
 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved