Last Date Stored Proc Updated???
Is there such a date/time?
I see the Created date on the list of stored procs, but really want a
Date Last Updated. After changing code for 3 hours, I tend to forget
which 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!
View Complete Forum Thread with Replies
Related Forum Messages:
UPDATE Stored Proc -- Updated Over All Of My Records
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. Here is the syntax for my stored proc. CREATE PROC updateResults2 @id int, @address1 nvarchar (50), @address2 nvarchar (50), @CITY nvarchar (33), @ST nvarchar (10), @ZIP_OUT nvarchar (5), @ZIP4_OUT nvarchar (4), @home_phone nvarchar (22), @NEWPhone nvarchar (20) AS UPDATE Results SET address1 = @address1, address2 = @address2, CITY = @CITY, ST = @ST, ZIP_OUT = @ZIP_OUT, ZIP4_OUT = @ZIP4_OUT, home_phone = @home_phone, NEWPhone = @NEWPhone GO As said previously it ran but it updated the WHOLE DATABASE with the same change (WHICH I DIDNT WANT IT TO DO)!! Thanks in advance. RB
View Replies !
Sql Server 2005 And Stored Procedures - Updated Date?
Hi, I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem? Br, Riika
View Replies !
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
Max Date Via Select Not Stored Proc
I have the following sample data. User Date 3 05/01/2000 3 06/03/2000 2 04/08/2000 1 05/01/2000 4 07/15/2000 2 03/02/2000 I want a select that comes back with the following with a view or select, not a stored proc and without adding fields to my table. Can anyone help with this? DaUser DaDate DaMaxUserDate 3 05/01/2000 06/03/2000 3 06/03/2000 06/03/2000 2 04/08/2000 04/08/2000 1 05/01/2000 05/01/2000 4 07/15/2000 07/15/2000 2 03/02/2000 04/08/2000 Note the last field for all DaUser rows showes the max DaDate from within the table. I am using SQL 7 and 2000 so ideally the code should work for both if possible. Thanks in advance.
View Replies !
Stored Proc Date Range Nor Producing RS
I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING??? -- Procedure CREATE PROCEDURE sp_009_SiteLead @sDatenVarChar(10) AS --DECLARE @CurDate datetime DECLARE @StartMonth int DECLARE @StartYear int DECLARE @StartDay int DECLARE @StartDate varchar(10) SET @StartMonth = DATEPART( month, Convert ( datetime, @sDate )) SET @StartYear = DATEPART( year, Convert ( datetime, @sDate )) SET @StartDay = DATEPART( day, Convert ( datetime, @sDate )) SET @startDate = CONVERT( varchar(2), @StartMonth ) + "/" + CONVERT( varchar(2), @StartDay ) + "/" + CONVERT( varchar(4), @StartYear ) Print @startDate SELECT * FROMtbl_Feedback WHERE tbl_Feedback.DateIn >= @startDate GO --- ADO Web page ' Stored Procedure Name: sp_009_SiteLead ' Site Lead Default Query set cmd=server.CreateObject("ADODB.command") set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput) set cmd.ActiveConnection=DBConnection cmd.CommandText="sp_009_SiteLead" cmd.Parameters.Append peDate cmd.Parameters.Append psdate psDate.Value = "10/01/2004" Set rsObj = CreateObject("ADODB.Recordset") rsObj.ActiveConnection = DBConnection rsObj.CursorLocation = adUseClient rsObj.PageSize= 20 rsObj.Open cmd Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it. Can anyone help? Thanks, Chad
View Replies !
Return Formatted Date From Stored Proc?
What is the recommended method of returning a formatted date from a stored procedure? The date is held in a date time field. I wish to return the date formatted as: dd/mm/yyyy hh:mm for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows: dd/mm/yyyy hh:mm:ss I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form. I was looking at: select jobHeaders.DateTimeJobTaken AS [Job Taken], CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4)) from jobHeaders but this gives : 8 /3 /2004 with spaces. Before looking further I thought one of you guys may have the answer. Thanks in advance
View Replies !
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
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
View Replies !
Finding Last Updated/modified Date
Hi, I am using SQL Server 2000 and have the following questions: 1. How do I know the last updated (data) date using system objects or any other method? 2. How do I know the last modified date of a table using system objects or any other method? 3. How do I know when a table is last accessed Any help is appreciated Thanks for your time and help in advance Kumar
View Replies !
Stored Proc - Calling A Remote Stored Proc
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': Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto' 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? Thanks - Amos.
View Replies !
Stored Proc Question : Why If Exisits...Drop...Create Proc?
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
View Replies !
Updating Modified/updated Date Column?
If your tables contain created and modified/updated dates what is the best practice for these? 1. Should you use UTC dates? 2. Do you use a default for the creation date (I assume yes)? 3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data? Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.
View Replies !
ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
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.
View Replies !
How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
Hey All, I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. Thanks in advance. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [SampleStoredProc] @Var1 varchar(20), @Var2 varchar(3), @Var3 varchar(2) = 'Dummy' As declare @selectlist varchar(5000) declare @tableBuild varchar(1000) declare @FieldName varchar(50) declare @FieldSelect varchar(500) declare @FieldTitle varchar(50) declare @TableName varchar(50) declare @holdTable varchar(50) declare @title varchar(50) declare @holdTitle varchar(50) declare @PageName varchar(50) declare @sequence varchar(100) declare @extraCriteria varchar(200) declare @holdCriteria varchar(200) declare @insertSQL varchar(5000) declare @ConvertRoutine varchar(500) declare @loopCtrl1 bit declare @loopCtrl2 bit declare @ConvertSQL varchar(5000) declare @PrevValue varchar(50) declare @NewValue varchar(50) declare @ActionTxt varchar(1) declare @Description varchar(20) declare @effDate varchar(10) declare @transEffDate varchar(10) declare @expDate varchar(10) declare @lastTransDate varchar(10) declare @policyStatus varchar(2) declare @reasAmendDesc varchar(50) declare @policyNumber varchar(20) declare @riskState varchar(20) declare @PriorPrem money declare @AmendPrem money declare @PremDiff money declare mtcursor cursor for select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1 where Column1 = @Var2 order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName open mtcursor fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' set @loopCtrl1 = 0 set @loopCtrl2 = 0 WHILE (@loopCtrl1 = 0) begin set @holdTable = @TableName set @holdCriteria = @extraCriteria set @holdTitle = @title if @FieldSelect = '' set @selectlist = @selectlist + ',' + @FieldName else set @selectlist = @selectlist + ',' + @FieldSelect set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)' fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine if @@fetch_status <> 0 set @loopCtrl2 = 1 if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1) begin set @tableBuild = @tableBuild + ')' set @insertSQL = ' declare mtcursor2 cursor for select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1 where TableName = ''' + @holdTable + ''' and ExtraCriteriaTxt = ''' + @holdCriteria + ''' and PageTitleTxt = ''' + @holdTitle + ''' and Column1 = ''' + @Var2 + ''' order by FieldDisplaySequenceNbr declare @FieldName varchar(50) declare @FieldTitle varchar(50) declare @ExtraUpdateMatch varchar(500) declare @PullUpdate bit declare @PullAdd bit declare @PullDelete bit declare @PullAnyUpdate bit open mtcursor2 fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate WHILE (@@fetch_status = 0) begin if substring(@FieldTitle,1,1) = ''#'' set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1) else set @FieldTitle = '''''''' + @FieldTitle + '''''''' if @PullAnyUpdate = 1 begin exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''') end else begin if @PullUpdate = 1 exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U'''' from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + '' where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null) or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '') end if @PullAdd = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''') if @PullDelete = 1 exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D'''' from #tempTable A where Val1 = ''''D'''''') fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate end close mtcursor2 deallocate mtcursor2' exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL) set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr' set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)' end if @loopCtrl2 = 1 set @loopCtrl1 = 1 end close mtcursor deallocate mtcursor Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 declare deletecursor cursor for select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U' open deletecursor fetch next from deletecursor into @PageName while @@fetch_status = 0 begin if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0 DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' fetch next from deletecursor into @PageName end close deletecursor deallocate deletecursor declare convertcursor cursor for select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a inner join MyTable1 b on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> '' where a.Id = @Var1 open convertcursor fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine while @@fetch_status = 0 begin set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)' set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) ' -- start 33385 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) ' set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + '''' --end 33385 if @ActionTxt = 'A' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + '''' set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' end if @ActionTxt = 'D' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + '''' else begin set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + '''' set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')' end set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted where EntrySequenceNbr = ''' + @Sequence + '''' exec (@ConvertSQL) fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine end close convertcursor deallocate convertcursor /* LOB-specific data conversions */ if @Var2 = 'PA ' --exec PAConfirmCovConversions @Var1 = @Var1 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 -- End issue 33385 Create table #pageSeqTable (PageTitle varchar(50), PageSeq int) insert into #pageSeqTable select distinct PageTitleTxt, PageDisplaySequenceNbr from MyTable1 where Column1 = @Var2 select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt from MyTable2, #pageSeqTable b where Id = @Var1 and PageNm = b.PageTitle order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101), @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes, @policyNumber = PolicyNumber, @riskState = StateName, @AmendPrem = convert(money,PremiumAmount) /* Case 33385 */ from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C where Id = @Var1 AND Val2 = (select max(Val2) from SHPlanInfo where Id = @Var1) AND B.ReasonAmendedCd = A.ReasonAmendedCd AND C.StateCode = A.RiskState Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' Set @PremDiff = @AmendPrem - @PriorPrem select EffectiveDate = @effDate select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate select AmendXPolStat = @policyStatus select ReasonAmendedDes = @reasAmendDesc select PolicyNumber = @policyNumber select RiskState = @riskState select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiff Select ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1 delete from MyTable2 where Id = @Var1 return
View Replies !
Stored Proc Calls Another Stored Proc
Hi all, I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this? Thanks for your help! Cat
View Replies !
Calling Stored Proc B From Stored Proc A
Hi all I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc The SELECT statement in question retrieves a single row from a table containing 10 columns. Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc? I know about stored proc return values and about output parameters, but I think I am looking for something different. Thanks
View Replies !
Calling A Stored Proc From Within Another Stored Proc
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help. I guess I should state my question to the forum ! Is there a way to call a stored proc from within another stored proc? Thanks In Advance. Tony
View Replies !
Calling T SQL Stored Proc From CLR Stored Proc
I would like to know if the following is possible/permissible: myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with INSERT INTO #tmpCLR EXECUTE spSQL } spSQL ( INSERT INTO #tmpABC EXECUTE spSQL2 ) spSQL2 ( // some other t-sql stored proc ) Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
View Replies !
Field Not Being Updated Within Stored Procedure
I have this stored procedure that loops through a table and updates acouple of fields. For some reason one of the fields is not beingupdated. If I run the same code from query analyzer, it works fine.Let me know if anyone can figure out why @lastscandate would ever beNULL. If it is null it should be equal to @maildate. The senerio thatseems to fail is when no records are returned from the select statementto fill in @lastscandate. This should then active the next ifstatement and set the @lastscandate equal to the @maildate. MailDateis always filled in in the database and LastScanDate will be NULL.Thanks for your help.DECLARE c1 CURSOR LOCAL FORSELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid= 0) AND (m.maildate ='' OR m.maildate IS NULL)AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycleAND p.status NOT IN ('BM', 'PM'))OPEN c1FETCH FROM c1 INTO @mid, @acctno, @ordid, @cycleWHILE @@fetch_status = 0BEGIN--Get MailDate from Manifest - if NULL then use GetDateset @maildate = NULLSELECT @maildate = MAX(whenmailed) FROM manifest WITH (nolock)WHERE acctno = @acctno AND ordid = @ordid AND cycle = @cycleif @maildate is NULLset @maildate = getdate()--Get Last Scan Date from Transactions - if NULL then use MailDateset @lastscandate = NULLselect @lastscandate=max(actiondate) from transactions whereacctno=@acctno and ordid=@ordid and cycle=@cycle and actionid=303if @lastscandate is NULLset @lastscandate = @maildateBEGIN TRANSACTIONUPDATE master SET printstatus = 'ML', maildate = @maildate,lastscandate=@lastscandateWHERE id = @midINSERT INTO transactions (initials, actionid, machinelogin, acctno,ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @acctno, @ordid,@cycle, 'Update Mail Dates')COMMIT TRANSACTIONFETCH NEXT FROM c1 INTO @mid, @acctno, @ordid, @cycleENDCLOSE c1
View Replies !
Linked Reports Not Being Updated When Master Report Is Updated
Since updating to SQL Server 2005 SP2 I've noticed two things about Linked Reports. 1. I do a lot of 'Snapshot' reports. With SP-1 if I updated a master report and made any changes to the Parameter List - it undid all my custom parameter changes on linked versions (restored to the Master Reports Defaults). While this is no longer happening with SP2 - it is still 'unhiding' the parameters. 2. With SP-1 if I added/deleted columns or made other changes to the report structure - the linked reports would pick up on the changes with their next refresh. With SP-2 I'm finding that I have to 'Re-link' the linked report back to the master report before the changes are refreshed. This is very time consuming especially with each report having 8 or more Snapshot reports pre-set up. Am I missing something - or is this a 'bug'... Any help would be appreciated...
View Replies !
Stored Procedures Management - Keeping Input Parameters Updated
Hi everyone I have just starting creating some stored procedures for our system and have a question related to management of these. When using input parameters using the following syntax: CREATE PROCEDURE sp_someInputProcedure @Username as varchar(16) @Password as varchar(12) @Name as varchar(50) @Address as varchar(60) @Zip as int @City as varchar(30) ... etc. This is all well and good, but what if I make a change in the datamodel - for instance changing a datatype or the length of a varchar - do I need to remember to manually update all stored procedures that uses these columns/variables? Seems like a bit of a hazzle. Is there an easier way to do this? Many thanks, Stian Danielsen Epizone
View Replies !
Can &"Date Modified&" Col Be Automatically Updated W/o Trigger For Each Table?
Hello, I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated? Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables. Thanks!
View Replies !
Running A Proc. On A Certain Date Help?
DECLARE @returnDay int DECLARE @query varchar(8000) --Looking at current date, SELECT @returnDay = DatePart(day,GetDate()) If @returnDay = 3 SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c: est.txt -c -Sserver -Usa -Ptest EXEC master.dbo.xp_cmdshell @query EXEC master.dbo.xp_sendmail @recipients='test@hotmail.com', @copy_recipients = 'test@hotmail.com', @message='Submitting Results for the previous month.', @subject='BloodBank results for the previous month',@attachments = '\cenc$ est.txt' SELECT @@ERROR As ErrorNumber I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date. Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current date
View Replies !
Proc For Capturing Date And Time
I'm creating a sproc to to count the dates and times users log on to a page. I want the date and time broken out into two columns. When I attempt create the sproc I get the following error: Subqueries are not allowed in this context. Only scalar expressions are allowed.Create PROCEDURE [dbo].[LoginCounter] @Username Nvarchar(50), @IPAddress Nvarchar(50), @BuilderID, INT As INSERT INTO LoginCount ([UserName], [IPAddress], [Date], [Time], [BuilderID]) VALUES (@Username, @IPAddress, (SELECT CONVERT(VarChar(2), MONTH(GETDATE()))+ '/'+ CONVERT(VarChar(2), DAY(GETDATE()))+ '/'+ CONVERT(VarChar(4), YEAR(GETDATE()))), (SELECT Convert (varchar (2), DATEPART(hour, GETDATE())+ 3) +':'+ CONVERT(VarChar(2), DATEPART(minute, GETDATE()))), @BuilderID)
View Replies !
Change A Date In Store Proc
Hello i have this store proc with the syntax below. The getdate get the current date but i need to change the date this one time to 3/20/2005. I was wondering is there a way to do that an not modify my sp. I tried to harcode 3/20/2005 as asofdate and i get all 0 in my table. getDate() AS AsOfDate'
View Replies !
How To Find Out Date/time When Row Was Updated Last Time
Hello, we need to track date/time of last update for each record in a table. As we understand it, we can't use field type Timestamp as this type does not use dates/times. Is there any SQL function available which we can bind to a column or do we really have to use triggers? Greetings from Mannheim, Germany Ricardo
View Replies !
How To Find Out Date/time When Row Was Updated Last Time
Hello, we need to track date/time of last update for each record in a table. As we understand it, we can't use field type Timestamp as this type does not use dates/times. Is there any SQL function available which we can bind to a column or do we really have to use triggers? Greetings from Mannheim, Germany Ricardo
View Replies !
Calling A Stored Procedure From Within A Stored Proc
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
View Replies !
SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View Replies !
Need To CalcuThe Number Of Days Between The Current Date And A Stored Date
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
View Replies !
FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared. Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits. I will do a 1 time DTS from FP into SQL Server tables. I then create INSERT and UPDATE triggers within FoxPro. These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there. In the end - the tables are local to both apps. If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed. Here's the FoxPro and SQL Server code for reference for the Record Insert: FOXPRO employee.dbf InsertTrigger: employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone) FOXPRO corresponding Stored Procedure: FUNCTION EMPLOYEE_INSERT_TRIGGER PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE nhandle=SQLCONNECT('SS_PDITHP3','userid','password ') IF nhandle<0 m.errclose=.f. IF !USED("errorlog") USE tisdata!errorlog IN SELECT(1) m.errclose=.t. ENDIF SELECT errorlog INSERT INTO errorlog (date, time, program,source,user) ; values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username")) IF m.errclose USE IN errorlog ENDIF RETURN ENDIF nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0" nsucc=SQLEXEC(nhandle,nquery) SQLDISCONNECT(nhandle) IF nSucc<0 m.errclose=.f. IF !USED("errorlog") USE tisdata!errorlog IN SELECT(1) m.errclose=.t. ENDIF SELECT errorlog INSERT INTO errorlog (date, time, program,source,user) ; values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username")) IF m.errclose USE IN errorlog ENDIF ENDIF RETURN SQL SERVER Stored Procedure called from FOXPRO Stored Procedure CREATE procedure ewo_sp_insertNewEmployee ( @WEPK int, @WEFNAME char(20), @WELNAME char(20), @WEEMAIL char(50), @WEUSERID char(15), @WEPHONE char(25), @RETCODE int OUTPUT ) AS insert into WO_EMP ( WE_PK, WE_FNAME, WE_LNAME, WE_EMAIL, WE_USERID, WE_PHONE ) VALUES ( @WEPK, @WEFNAME, @WELNAME, @WEEMAIL, @WEUSERID, @WEPHONE ) IF @@ERROR <> 0 BEGIN SET @RETCODE=@@ERROR END ELSE BEGIN -- SUCCESS!! SET @RETCODE=0 END return @RETCODE GO
View Replies !
Help With SQL Stored Proc
I have a stored procedure , where i want to return identity column after insert but before insert i want to check if the record exist then select its identity value to return , after select statement it is retutrning null CREATE PROCEDURE SP_Attendance1 (@DIVISIONID int,@EMPLOYEEID int,@CALLDATE datetime,@RECEIVEDDATE datetime,@DOC datetime,@SYSTEMNAME VARCHAR(20),@DELETED int,@attendanceID int output) AS---DECLARE @ID intIF EXISTS (SELECT ID FROM TBLATTENDANCE WHERE EMPLOYEEID = @EMPLOYEEID AND YEAR ( CALLDATE ) = YEAR ( @CALLDATE)AND MONTH (CALLDATE) = MONTH ( @CALLDATE) AND DAY (CALLDATE) = DAY ( @CALLDATE) )BEGIN SET @attendanceID = SCOPE_IDENTITY()END ELSE BEGIN INSERT INTO TBLATTENDANCE (DIVISIONID ,EMPLOYEEID ,CALLDATE , RECEIVEDDATE ,DOC,SYSTEMNAME ,DELETED )VALUES (@DIVISIONID ,@EMPLOYEEID ,@CALLDATE , @RECEIVEDDATE ,@DOC,@SYSTEMNAME ,@DELETED ) ;SELECT DIVISIONID, EMPLOYEEID, CALLDATE, RECEIVEDDATE, DOC, SYSTEMNAME, DELETED,ID FROM TBLATTENDANCE WHERE (ID = SCOPE_IDENTITY())SELECT @attendanceID = SCOPE_IDENTITY()ENDGO Kindly help with this
View Replies !
Need Help With A Stored Proc
I have a current stored proc that creates records based on certain criteria. One of the fields I have is a SmallDateTime field. To populate this from my stored proc, I have this code (for this one field). SELECT @myLeaveDate = CAST(STR(MONTH(getdate()))+'/'+STR(01)+'/'+STR(YEAR(getdate())) AS DateTime) This always creates a record for the 1st of the current month. This works fine as is. After it runs I can look at the table and it creates dates that look like the following: “2/1/2006� -Notice it doesn’t have any minutes, seconds, etc. Now what I need is to do something similar in another field which is also SmallDateTime, BUT I want the date to be for the 10th day of the following month. I got this working using dateadd, but it also appends the minutes, seconds, etc.
View Replies !
Sql And Stored Proc
Im trying to perform an update with a stored procedure thats all working but an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key I understand whats going on but how do you update some ones details if its protected this way. ?
View Replies !
Need Help With Stored Proc
I have 2 tables, lets say: Table1 and Table2. Table1 has an ID field that is unique (PK). Table2 has the same ID field and a date field which are both unique (PK, FK) I need to write a stored procedure that will query the ID field from table1 like Select distinct(id) from Table1 Would return: ID 1 2 3 4 5 Now on the first of every month I want a job to run that would create a new record for EACH ID from the above query. The new record would be created in Table2 with the ID and a date (that I will determine), resulting in: ID DATE 1 01/01/06 2 01/01/06 3 01/01/06 4 01/01/06 5 01/01/06 The following month, after running it again, the table would look like: ID DATE 1 01/01/06 1 02/01/06 2 01/01/06 2 02/01/06 3 01/01/06 3 02/01/06 4 01/01/06 4 02/01/06 5 01/01/06 5 02/01/06 I have no idea how to “loop� through this record set for each record in Table1 and in turn insert into Table2.
View Replies !
My First Stored Proc
Hello, So I created my first SP today which returned data to populate a datagrid. Worked to perfection. Now I'm trying to do a simple login and I'm having a hard time getting it together. I want the user to enter in a username and pass then if user exists, to return their userid and update their last login date. But I can't get it. Any help would be awesome. Here's my SP... CREATE PROCEDURE [dbo].[userLogin] @username varchar(50), @pass varchar(50) AS declare @x int declare @userid int if exists (SELECT userid FROM users WHERE username = @username AND password = @pass) set @x = 1 else set @x = 0 if @x = 1 UPDATE users SET lastlogin = getdate() WHERE userid = (SELECT userid AS name FROM users WHERE username = @username AND password = @pass) else return '0' if @x = 1 SET @userid = (SELECT userid FROM users WHERE username = @username AND password = @pass) return @userid GO Any constructive criticism on my SP is welcome. Also, should I be using Print or Return if I want to send back a value to my VB code? And my VB: Dim user As String = txtUsername.Text Dim pass As String = txtPassword.Text Dim objDataSet As DataSet Dim objAdapter As SqlDataAdapter Try Dim cmd As New SqlCommand 'Enter Param's here Dim myParam1 As SqlParameter = cmd.Parameters.Add("@username", SqlDbType.VarChar) Dim myParam2 As SqlParameter = cmd.Parameters.Add("@pass", SqlDbType.VarChar) myParam1.Value = user myParam2.Value = pass objAdapter = New SqlDataAdapter objAdapter.SelectCommand = cmd objAdapter.SelectCommand.Connection = SqlConn objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure objAdapter.SelectCommand.CommandText = "userLogin" SqlConn.Open() Dim str As String = cmd.Parameters("@userid").Value() cmd.ExecuteNonQuery() If str = "0" Then 'Error Login Page Response.Redirect("login_err.aspx") Else Session("userid") = str Response.Redirect("home.aspx") End If SqlConn.Close() Catch ex As SqlException SqlConn.Close() End Try
View Replies !
SQL Stored Proc
Hi, I am trying to create a stored proc, that delivers a recordset, per the user requirements BUT, I want to create a Geneirc search Proc that can handle a few criteria I was wondering if it is possible to create a VB like Select case depending on Information supplied to the stored proc i.e @Loc_Thing @Loc_OtherThing SELECT FirstName,LastName,CIty,Job,,Company,Webpage FROM RECORDSET WHERE Select case @Loc_Thing Case "Mickey" LastName = @Loc_OtherThing Case "Walt" Company = @Loc_OtherThing ...... etc is it possible to create a strored proc like this? I have found a Select case in SQL, but it doesn't work I would like it? Any Idea's?
View Replies !
Stored Proc Or Something Else
Hi!I am trying to find stored procedure or a utility that will be able todelete files older than let's say 3 days but I would want to pass a dayparameter, file dir and maybe an extension or something like that.Anybody knows of a tool or a stored procedure that does that?Thank you,T.
View Replies !
Using A Stored Proc Within A Stored Proc
I want to determin if a user belongs to a certain group or not within a stored proc: CREATE PROCEDURE usp_General_IsEstimator @cLogin sysname, @bEstimator bit AS DECLARE groups CURSOR FOR EXEC sp_helpuser @cLogin What I was planning on doing is to see if the user login returns the 'Estimating' group name using the sp_helpuser. If so, set @bEstimator = 1 else 0 How can I accomplish this? I cannot use the cursor with the stored proc. How do I work with the recordset returned by the stored proc? Any ideas? Mike B
View Replies !
Help Stored Proc
Hi, Could any one please help me in creating sp. It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100. Thanks in advance. -ss
View Replies !
Stored Proc
i have a table of the form table tab1( col1 bigint (ForeignKey col1 references mastertable1(col1)) col2 bigint (ForeignKey col2 references mastertable2(col2)) ) when i delete col2 from mastertable2, all the records in tab1 with the col2 value must be deleted. my problem is how to iterate through all the matching records in tab1 when i delete col2 from master table 2, using a SP
View Replies !
|
TRACKER
