UPDATE Freezing Problem
Jun 2, 2004
Hi, i am having a problem with UPDATE.
I have the following table with about 4 million rows:
CREATE TABLE [newauth] (
[authnumber] [int] NULL ,
[batchnumber] [int] NULL ,
[accountnumber] [varchar] (20) NULL,
[authcode] [varchar] (10) NULL ,
[authused] [char] (1) NULL ,
[loaddate] [int] NULL ,
[trandate] [int] NULL ,
[trantime] [int] NULL ,
[cardtype] [char] (1) NULL ,
[mcc] [char] (4) NULL ,
[amount] [int] NULL ,
[transactionnumber] [int] NULL
) ON [PRIMARY]
GO
There is an index on the authnumber field. I am calling the following stored procedure to update the transactionnumber.
CREATE PROCEDURE UpdateAuthWithTrans(@lAuth int, @lTrans int)
AS
UPDATE NewAuth
SET TransactionNumber = @lTrans
WHERE AuthNumber = @lAuth
GO
I need to update about 1 million rows. Approx 50000 calls to this SP work OK. Then the DB freezes. Enterprise mgr shows the following (screen shot attached)
I cant figure out why this is freezing. I have tried update statistics newauth, dbcc checktable, rebuilding the index, rebooting the server.
Any ideas would be appreciated, thanks in advance for any help.
View 3 Replies
ADVERTISEMENT
Mar 5, 2004
I have a full database backup job that runs every night at 10:00 pm. Normally, this job takes about 20 minutes to complete. Recently, this backup job has been "freezing" up, or hanging, and then I manually have to go in, cancel the job, start the job again, and hope it completes.
Some info about the database: only a MB or two is added to it each day. There are no other backups, loads, dumps,or any other processes running when this backup occurs. There are other databases on this server, but again, they do not load, dump, or have any other processes running at the time of this backup. All of the other scheduled backups for the other databases work fine.
Any ideas on what could be happening and why this particular job is hanging?
Thanks for your time and thoughts.
View 5 Replies
View Related
Apr 30, 2004
My 2000 sp3 sql analyzer keeps freezing when closing a window. This is not during a query, and I may not have even edited anything. The entire app will freeze, and after hitting the close button several times, it will get around to an "End Task" popup, and finally close. Doesn't happen every time, doesn't happen with the same number of windows open or the same query open. I have unloaded, reloaded, etc...Still occurs. :confused: Any ideas? TIA.
View 1 Replies
View Related
Oct 22, 2007
Is it possible to freeze columns in a table elements. Lots of times we are being forced to use matrix to freeze columns - when in fact we should actually be using a table element.
Any idea if this is going to be part of MSRS in the future or should we just use matrixes..
Thanks
View 4 Replies
View Related
Nov 22, 2006
I am completely new to this forum, so forgive me if I am in the wrong place.
I have been running this application for about 3 years with no problems, but over the past couple of weeks I have been having major issues!
Background: I am running an Access 2003 frontend with SQL Server 2000 backend. The SQL Server resides on the server, and each user has a local copy of the Access 2003 application.
Issues: Over the past couple of weeks users have been recieving errors that lock everyone's application up, and the server has to be rebooted. Some of the errors recieved:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). (#10054)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation. (#11)
Microsoft][ODBC SQL Server Driver][DBNETLIB]Timeout Expired (#0)
So after these errors occur, they have to reboot the server. It has happened to different users, never the same user, but once and error occurs, it locks up every user.
I have searched the internet high and low for troubleshooting, but have not found anything to fix these issues. I am not really knowledgable with SQL server. I created the database in Access and then used the upsize wizard to get it into SQL.
Oh, and I have also checked the error logs and found nothing that stands out as an error problem.
View 1 Replies
View Related
Mar 15, 2007
I have report with large records and many columns
I would like to freez header and specific column like excel
I tried to freez header it self it's worked
I tried to freez a column it self it's worked
when I tried to freez both in same report it does not work..
any help??
View 1 Replies
View Related
Apr 30, 2008
I'm experiencing quite an interesting issue with our database. We're currently running SQL Server 2005 Standard (x64) (v9.00.3054.00) and I have a block of code that just insists on not working within a UDF. Everything I've read seems to point that I'm doing this corerctly but apparently I'm missing something.
Executing the query below:
DECLARE @sp DATETIME
DECLARE @ep DATETIME
DECLARE @trackType INT
SET @sp = '4/21/2008'
SET @ep = '4/28/2008'
SET @trackType = 1
SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
Yields no issues however if I put this into a UDF, it runs and never finishes executing. I'm not quite sure what my issue was so I'm hoping someone could point out where my goof is.
Here is the function:
CREATE FUNCTION [dbo].[fnEmployeeSchedulingResults]
(
@sp DATETIME,
@ep DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
)
Any help would be greatly appreciated!
-Justin
View 19 Replies
View Related
May 26, 2008
Hi,
I am experiencing an error where the ssis data flow task would freeze and stop data export from a oledb source to a text file. It doesn't generate any errors the ssis package would just hang. This only happens when I run it in 64 bit mode. When I change the mode to 32 bit the ssis never freezes and runs fine. Has anyone experience this? Is there a fix so I can run my jobs in 64 bit mode?
Thanks,
View 5 Replies
View Related
Jan 23, 2007
I am executing a single package that references 180 other packages , after executing the first 90-100 packages , SSIS designer completely freezes and then i have to kill the session using Task Manager . Is this a limitation of SSIS or is it a system constraint ?
If you have any suggestions or workarounds for de same plz do reply
Thanks in Advance
Shailesh
View 2 Replies
View Related
Jul 7, 2015
I have a weird intermittent issue with an enterprise version of SS2014. When clicking or right clicking around SSMS will lock up and display the 'SSMS is busy - waiting for an internal operation to complete'. It is only specific to the server as when I connect using my local SSMS this doesn't happen. This was happening both pre and post SP1 install.
View 4 Replies
View Related
May 7, 2015
I am running into a problem where Excel 2010 is freezing when I try to work with data coming from a Microsoft Dynamics AX 2012 R3 pre-built cube.
What is happening is that when I go to add a second dimension to my pivot table, excel locks up and becomes completely unresponsive for at least 2 hours. If I add the dimension to the table before the measure though, everything is fine.
For Example, I am trying to build a sales report with the product name and customer name as dimensions, and Sales as the measure. If I add the product name to my pivot table as a column and then sales as a measure, Excel will freeze when I try to add the customer dimension. However, if I add both dimensions to the pivot table first and then the measure, Excel seems to work fine.
View 2 Replies
View Related
Jan 8, 2008
I am running a .Net application (VB.Net, .Net Framework 2.0) on a Windows CE 5.0 handheld device. I am relatively having good success with SQL CE replication, however sometimes (2 users out of 30 every 2 weeks), are freezing up in the application.
Here is what CE SQl software versions I installed:
sqlce30.dev.ENU.wce5.armv4i.CAB
sqlce30.repl.wce5.armv4i.CAB
sqlce30.wce5.armv4i.CAB
I have a trace log in my application, and here is what pops up before the hang (warm rebooting the handheld works)
NativeError: 28037
SQL Msg: A request to send data to the computer running IIS has failed. For more information, see HRESULT.
NativeError: 28600
SQL Msg: The database is currently synchronizing with the server.
I am using a Thread to syncronize (microsofts built in class), as you can see I am well aware of 28600, it just means some other thread is already syncing, so I can wait until next time to syncronize. However, my users apps are freezing just after both error codes occur. Anything in the code below causing issues??? (maybe _repl.Dispose() is freezing up my handheld, because its freezing after this line of code (see below code):
"MTM.SPUD.Utilities.Utilities.Log("[DEBUGQLThread#" + threadId + "]: End of Thread Life.")"
Public Sub AsyncSyncronize(ByRef PendingData As Boolean, ByVal Publication As String, ByVal Subscription As String, ByRef LastSyncDateTime As DateTime)
Dim _repl As New SqlCeReplication()
Try
...
savedAsyncResult = _repl.BeginSynchronize(objDelegate1, objDelegate2, objDelegate3, objDelegate4, _repl)
Catch ex As Exception
Finally
End Try
End Sub
Public Sub OnSynchronizeComplete(ByVal asyncResult As IAsyncResult)
Dim _repl As SqlCeReplication = Nothing
Dim threadId As String = ""
Try
_repl = DirectCast(asyncResult.AsyncState, SqlCeReplication)
threadId = _repl.InternetPassword
_repl.EndSynchronize(asyncResult)
dataIsPending = False
Catch exce As SqlCeException
_repl.CancelSynchronize()
MTM.SPUD.Utilities.Utilities.Log("[DEBUGQLThread#" + threadId + "]: SQLCE OnSynchronizeComplete() NativeError: " + exce.NativeError.ToString + "SQL Msg: " + exce.Message.ToString)
dataIsPending = True
If exce.NativeError.ToString = "28600" Then
MTM.SPUD.Utilities.Utilities.Log("[WARNINGQLThread#" + threadId + "]: Will try again next time")
Return
End If
Catch ex As Exception
Return
'MTM.SPUD.Utilities.Utilities.Log("App Exception: " + ex.Message)
'Throw New Exception("EndSynchronize Failed: " + ex.Message)
Finally
MTM.SPUD.Utilities.Utilities.Log("[DEBUGQLThread#" + threadId + "]: End of Thread Life.")
If _repl IsNot Nothing Then
_repl.Dispose()
_repl = Nothing
End If
End Try
End Sub
View 3 Replies
View Related
Jan 7, 2007
Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT A.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?
View 2 Replies
View Related
Jan 2, 2008
What's up with this?
This takes like 0 secs to complete:
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.
View 5 Replies
View Related
Nov 11, 2007
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
View 2 Replies
View Related
Feb 16, 2006
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies
View Related
Oct 20, 2006
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?
View 1 Replies
View Related
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
May 27, 2008
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
View 2 Replies
View Related
Jul 23, 2005
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 Related
Nov 9, 2007
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 Replies
View Related
Jul 23, 2006
I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
The Stored Procedure:
ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)
/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN
View 2 Replies
View Related
May 13, 2008
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.
View 23 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
Feb 18, 2006
I do realize that his could be posted in a few spots but I think the answer is in the SQL.
I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting.
ex:
<asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox>
I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together.
I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters.
Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update.
There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it.
wbochar
View 1 Replies
View Related
May 10, 2006
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e){
sqlDataSource1.Update();
sqlDateSource2.Update();}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
Zhang
View 2 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
Jul 30, 2007
Hello,
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?
View 6 Replies
View Related
Feb 8, 2008
Hello
I have interesting situation with one of my update statement.
Update takes 2 min to run and usually updates 20000 rows.
However lately update executes for longest time - 10 hours.
After I reindex table the updates run fine again
Please advice what can cause this
I do not think reindexing table before every update is good idea
We are running SQL 2000 SP4, Windows 2003 Server
Thank you in advance for all your help
Armine
View 17 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Oct 19, 2005
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies
View Related
Mar 7, 2005
OK,
This is an Update that I have working, But what do I do, if the customer does not exist already it doesn't add the customer? How should I remedy this? if the customer does exist works great.
UPDATE AC
SET CustId = Left (CustomerId,10),
CustName = Left (CustomerName,25),
Addr1 = Left (Address1,25),
Addr2 = Left (Address2,25),
City = Left (ca.City,15),
Region = Left (State,2),
PostalCode = Left (Zip,5)
FROM RIO.dbo.tblArCust AC INNER JOIN
(SELECT CustomerCode, MAX(LastUpdatedDate) MaxDate
FROM COFFEE.dbo.vueCustomerAddress
GROUP BY CustomerCode) V
ON V.CustomerCode = AC.CustId
INNER JOIN COFFEE.dbo.vueCustomerAddress CA
ON CA.CustomerCode=V.CustomerCode AND
MaxDate=LastUpdatedDate
WHERE CA.addresstypeid = 1
View 1 Replies
View Related