UPDATE Stored Proc -- Updated Over All Of My Records
Jan 31, 2005
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD..
IF anyone could shead some light on what I'm doing wrong that would be great.
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
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
I need to understand this a bit more and how to get all records out of the sp.For instance, SELECT @Name = objName FROM Structure WHERE Id = @IdI need this to return all the records, not just the last one as it seems to be doing.Do I need to adjust the code behind or the statement above?Thanks,Zath
Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected.
Current code(works) Public Function DelAll() MZKDB = MZKHRFin If sloption = "L" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;" ElseIf sloption = "S" Then sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";" End If sqlConn.Open() sqlTrans = sqlConn.BeginTransaction() sqlCmd.Connection = sqlConn sqlCmd.Transaction = sqlTrans Try sqlCmd.CommandText = sqlStr sqlCmd.ExecuteNonQuery() sqlTrans.Commit() frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf SetCursor() Catch e As Exception Try sqlTrans.Rollback() Catch ex As SqlException If Not sqlTrans.Connection Is Nothing Then frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf SetCursor() End If End Try frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf SetCursor() Finally sqlConn.Close() End Try ResetID() End Function If cbGenFY.Checked Then sqlStr = "DELETE FROM FIN_FiscalYear" TableName = "dbo.FIN_FiscalYear" DelAll() ClearCounts() timeStepStart = Date.Now GenFY() timeStepStop = Date.Now
DispOneCounts() End If If cbGenFund.Checked Then sqlStr = "DELETE FROM FIN_Fund" TableName = "dbo.FIN_Fund" DelAll() ClearCounts() timeStepStart = Date.Now GenFund() timeStepStop = Date.Now DispOneCounts() End If If cbGenFunc.Checked Then sqlStr = "DELETE FROM FIN_Function" TableName = "dbo.FIN_Function" DelAll() ClearCounts() timeStepStart = Date.Now GenFunc() timeStepStop = Date.Now DispOneCounts()
End If If cbGenObject.Checked Then sqlStr = "DELETE FROM FIN_Object" TableName = "dbo.FIN_Object" DelAll() ClearCounts() timeStepStart = Date.Now GenObject() timeStepStop = Date.Now DispOneCounts() End If If cbGenCenter.Checked Then sqlStr = "DELETE FROM FIN_Center" TableName = "dbo.FIN_Center" DelAll() ClearCounts() timeStepStart = Date.Now GenCenter() timeStepStop = Date.Now DispOneCounts() End If If cbGenProject.Checked Then sqlStr = "DELETE FROM FIN_CodeBook" TableName = "dbo.FIN_CodeBook" DelAll() sqlStr = "DELETE FROM FIN_BudgetAccnt" TableName = "dbo.FIN_BudgetAccnt" DelAll() sqlStr = "DELETE FROM FIN_Budget" TableName = "dbo.FIN_Budget" DelAll() sqlStr = "DELETE FROM FIN_Project" TableName = "dbo.FIN_Project" DelAll() ClearCounts() timeStepStart = Date.Now GenProject() timeStepStop = Date.Now TableName = "dbo.FIN_Project" DispOneCounts() End If If cbGenProgram.Checked Then sqlStr = "DELETE FROM FIN_Program" TableName = "dbo.FIN_Program" DelAll() ClearCounts() timeStepStart = Date.Now GenProgram() timeStepStop = Date.Now DispOneCounts() End If If cbGenGL.Checked Then sqlStr = "DELETE FROM FIN_gl" TableName = "FIN_gl" DelAll() ClearCounts() timeStepStart = Date.Now GenGL() timeStepStop = Date.Now DispOneCounts() End If If cbGenRevenue.Checked Then sqlStr = "DELETE FROM FIN_Revenue" TableName = "FIN_Revenue" DelAll() ClearCounts() timeStepStart = Date.Now GenRevenue() timeStepStop = Date.Now DispOneCounts() End If If cbGenBank.Checked Then sqlStr = "DELETE FROM FIN_VendorBankAccnt" TableName = "dbo.FIN_VendorBankAccnt" DelAll() sqlStr = "DELETE FROM FIN_VendorBank" TableName = "dbo.FIN_VendorBank" DelAll() sqlStr = "DELETE FROM FIN_bankAdd" TableName = "dbo.FIN_bankAdd" DelAll() sqlStr = "DELETE FROM FIN_bankTERMS" TableName = "dbo.FIN_bankTerms" DelAll() sqlStr = "DELETE FROM FIN_bank" TableName = "dbo.FIN_bank" DelAll() ClearCounts() timeStepStart = Date.Now GenBank() timeStepStop = Date.Now TableName2 = "dbo.FIN_bankTERMS" TableName3 = "dbo.FIN_BankAdd" TableName4 = "dbo.FIN_VendorBank" TableName5 = "dbo.FIN_VendorBankAccnt" DispTwoCounts() End If If cbFinAP.Checked Then sqlStr = "DELETE FROM FIN_Period" TableName = "FIN_Period" DelAll() ClearCounts() timeStepStart = Date.Now GenPeriod() timeStepStop = Date.Now DispOneCounts() End If
This seems so easy....change the join to show all records, but thezero records still do not showI want to join 2 tables....basically Customers and Orders....get thetotal number of orders for each Customer within a date range...but Ican't seem to show records where the total for a particular Customeris zero (which is very important info)There must be an easy way???thanks,Paul
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc create procedure return_data_and_value @return int output as set nocount on ... ... select * from #Table select @return = count(*) from #Table drop table #Table go
This is asp.net code
Dim nRecords as Int32 Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn) cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int) prm.Direction = ParameterDirection.Output cmd.Parameters.Add(prm)
Hello,,,I need to update table but not all fields in stored proc have to be with value (some of fields will be NULL), so i need to avoid updating any field that his parameter with null value.I need the syntax for (IF) statement to optionally update that field.something like this :CREATE PROCEDURE [dbo].[MyUpdate] ( @ID int, @Field1 nvarchar(50), @Field2 nvarchar(50)=null, @Field3 nvarchar(50)=null, @Field4 nvarchar(50) ) AS
UPDATE MyTable SET Field1 = @Field1, if (@Field2<>null) Field2 = @Field2, if (@Field2<>null) Field3 = @Field3, Field4 = @Field4 WHERE ID = @ID I saw example before but i can't remember where. Thank you in advance
I have a stored procedure that updates about a dozen rows.
I have some overloaded functions that I should update different combinations of the rows - 1 function might update 3 rows, another 7 rows.
Do I have to write a stored procedure for each function or I can I handle it in the Stored Procedure. I realise I can have default values but I the default values could overwrite actual data if the values are not supplied but have been previously written.
Hi, I've been reading all sorts of info on the ntext field. I needthis to store xml documents in sql server via a stored proc.Because of its size, I apparently can not use SET (as in UPDATE)therefore I'm trying to do an INSERT of the row with this field (afterdeleting the old row).CREATE PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50)@strWF ntext@varCust varchar(50)@varAssy varchar(50))ASINSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES(@varWO, @varCust, @varAssy, @strWF)I'm using MSDE so I can't tell what's wrong...it just won't save theproc.PLEASE HELP!Thanks, Kathy
we just moved a database from a shared SQL 2000 server to SQLExpress on a VPS server. Every thing seems to work great, really no performance loss at all, except for one stored proc that is giving us problems. It's function is to update a history table, and then update the production table. On the old server, it would take less than a second to run this, now it takes anywhere from 45 seconds to 1 minute or it times out. This database is used on a classic asp web app. ANY help at all on this would be appreciated as I am pulling my hair out trying to figure out what's wrong here. here is the proc.
------------------------------------------ code ---------------------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON go
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
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am trying to put together a Stored Procedure that runs other SP's based on a records found condition. Sometimes my update table either by deleting duplicates or for other reasons is empty. Running a SP to update records on a table is not needed.
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT1') BEGIN EXEC IMB_Trace_Data.dbo.Update_CT1_Trace_Data END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT2') BEGIN EXEC IMB_Trace_Data.dbo.Update_CT2_Trace_Data END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'FULF') BEGIN EXEC IMB_Trace_Data.dbo.Update_FULF_Trace_Data END
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €śgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1 FROM dbo.ReportTable INNER JOIN dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget HAVING (dbo.ReportTable.Budget < 1) ORDER BY dbo.ReportTable.ProjectNo
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated?
CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS
SET @dtCast = convert(char(10), getdate(), 120)
DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)'
EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1)
SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID
GO
-- Is there a way to return the recordset that were modified in the update?
Hi all, I have a data of applicants. Everyday we dump this data into SQL server. Now I need to generate reports everyday so that we can track how many records get updated everyday. Now the thing is that the applicants are in various stages. So my reports need to track the how many applicants changed from stage "abc" to "pqr" and how many changed from "pqr" to "xyz". Now it is not necessary that all the records change stages everyday.
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table. Maybe a tool someone knows of?
I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level. I have no experience with it and any precise hints/solutions would be very appreciated
Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted.
In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles?
I tried making the database read-only, but then of course i cant add new records.
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.
So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.
How can i do that in SSIS 2005 using different data flow tasks ?
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I am creating update trigger(After) on a table.I want to catch an updated row and insert the values of this row into a new table.How can i catch this row while creating a trigger after update.