Strored Proc Change Timestamp?
Jul 12, 2004Does anyone know of an internal procedure to return the timestamp of the last time a storedprocedure or table was modified?
Currently the object only lists the creation timestamp.
ADVERTISEMENT
Problem With Strored Proc
Jan 28, 2008I have a stored Proc, which takes 30 min for execution. If I execute it twice it runs properly but when i do the same the third time, it gets stuck.. any reasoning for this..
I am using Insert into statements a lot here...
Transact SQL :: Using CAST To Change Timestamp Into Date?
Nov 6, 2015I have a server on SQL Server 2008 R2.
I have the following code:
SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROM PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID
However when I run it I get a message:
Change A Date In Store Proc
Apr 4, 2008Hello 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'
How To Tell The Last Change Date Of A Stored Proc?
Jan 21, 2008Is there someplace in the database I could check to see when the last time a stored proc was altered?
Thanks in advance.
Error While Converting Oracle Timestamp To Sql Server Timestamp (datetime) - Invalid Date Format
Jun 19, 2007I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
Transact SQL :: Query To Display Avg Values For Each Timestamp And Count Of Timestamp
Jun 23, 2015date time s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date time s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400 3
6/9/2015 11:56:26 TestSite 900 2
6/12/2015 12:56:26 TestSite 700 1
Strored Procedure Question
Oct 23, 2007
Hi All
I am using SQL server 2005.
I have wriiten the Stored Procedure.
Once I modified anything in the Stored Procedure I use ALTER command and save it.
But I am unable to save the comments with this ALTER command which I write in the beginnign of the Stored Procedure.
I have given the example below.
Also please tell me without using ALTER is there any way to save the stored procedure.
For Ex:
/*************************************************************************************************************************************************************************************
* Procedure : pr_Generate_PaymentApproval_List
* * Called By : PPComponent.PPPayment.GetPaymentApprovalList
* Description : This proc is use to retrieve payment approval info
*/
ALTER PROCEDURE dbo.pr_Generate_PaymentApproval_List (
@ins_id BIGINT, @disti_id BIGINT,
@geo_cd NCHAR(4) )
--
--
Regards
Abdul
TSQL Strored Procedure
Apr 26, 2006I am having an issue when trying to pull data from Oracle into a sqlSERVER TABLE. I'm running into an issue because I am using to much temporary space in Oracle because the return set is so large. New to sqlserver, but in Oracle the solution would be to commmit the insert after so many records have been retrieved.
Here's the procedure I wrote I would think I need to add a cursor and a counter which be used to do a commit after so many rows have been retrieved. DOes so one have a procedure I could model mine after?
ALTER PROCEDURE [dbo].[SP_Load_BO]
-- Add the parameters for the stored procedure here
@p1 int = 0,
@p2 int = 0
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO MERCHANT_BACKOFFICE_SQLSERVER
(MONTHYEAR,ALLIANCEID, merchantid,rptservid,scid,tabletype,tablenum,VOLUME, COST, COSTPLUS,DATELOAD)
SELECT convert(DATETIME,PERIOD) as PERIOD,
ALLIANCEID,
merchantid,
rpTSERVID,
scid,
tabletype,
tablenum,
Convert(numeric(10),Count) AS VOLUME,
Convert(numeric(24,6),COST) as COST,
Convert(numeric(24,6),COSTPLUS) as COSTPLUS,
convert(datetime,DATE_LOAD) as DATE_LOAD
from
OPENQUERY(INV,'
SELECT MER.MONTHYEAR AS PERIOD,
MER.ALLIANCEID,
MER.merchantid,
MER.RPTSERVID,
MER.SCID,
MER.TABLETYPE,
MER.TABLENUM,
SUM(MER.VOLUME) AS Count,
SUM(MER.COST) as Cost,
SUM(MER.COSTPLUS) as Costplus,
to_char((last_day(add_months(sysdate,-1))+1),''MM/DD/YYYY'') as DATE_LOAD
FROM
MERCHANT_BACKOFFICE_SERVICE MER
WHERE MER.allianceid <>516
group by MER.MONTHYEAR,
MER.ALLIANCEID,
MER.merchantid,
MER.RPTSERVID,
MER.SCID,
MER.TABLETYPE,
MER.TABLENUM')
END
Can I Fill A Cursor From A Strored Procedure?
Nov 13, 2003Basically, I have a complex stored procedure that combines two tables and fills a cursor.
I would like to fill another cursor in another stored procedure from the results of this first stored proc, rather than have to type it all in again.
The reason being that I am doing a one time import of some data from two tables into one new table based on some complex linking/querying.
Can I fill a cursor from the output of another stored procedure rather than an inline SELECT statement?
Does the sp I am using have to have cursor as an out parameter?
Help With Error Handling Please Strored Procedures
Dec 31, 2004I've got the folloing stored procedure
ALTER PROCEDURE AddUserData
(@login varchar (8),
@password varchar (8),
@fullName varchar (50),
@RoleID Int,
@statusID Int)
AS
if exists
(SELECT login, @errorReturn
FROM tbEmployee
WHERE login = @login)
return 55555
Else
INSERT INTO tbEmployee (login, password, fullname, RoleID, statusId)
VALUES (@login, @password, @fullName, @RoleID, @statusID)
RETURN @@error
the function that calls this stored procedure is this
Public Shared Function InsertUserData(ByVal login As String, ByVal password As String, ByVal fullName As String, ByVal StatusID As Integer, ByVal RoleID As Integer)
' Create the connection object
Dim connection As New SqlConnection(connectionString)
' Create and initilise the command object
Dim command As New SqlCommand("AddUserData", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@password", SqlDbType.VarChar)
command.Parameters("@password").Value = password
command.Parameters.Add("@login", SqlDbType.VarChar)
command.Parameters("@login").Value = login
command.Parameters.Add("@fullName", SqlDbType.VarChar)
command.Parameters("@fullName").Value = fullName
command.Parameters.Add("@roleID", SqlDbType.Int)
command.Parameters("@roleID").Value = RoleID
command.Parameters.Add("@statusID", SqlDbType.Int)
command.Parameters("@statusID").Value = StatusID
' Open the connection
Try
connection.Open()
command.ExecuteNonQuery()
Finally
connection.Close()
End Try
End Function
now I'm trying to get ahold of the return value from the stored procedure so I can output a message to the user in a label control.
e.g the soted procedure checks to see if a user already exists if it does it returns 55555. how can I get ahold of this 55555 and place it in a variable in the function so I can then send it to another error function to display the approperate lable message ??
thanks all
Strored Procedure For Adding Play List
Dec 20, 2007Hi...
Am working with asp.net with vb for a MusicProject..
In this I have playlist for particular User..ie User Selects Some Songs and clicks on AddToMyPlayList Button....
Here He can insert those songs into a new playlist or update the earlier..
the StroedProcedure is as follws: 'N' means NewPlayList and 'E' means Existing
***************************************************************************************
CREATE PROCEDURE MUSIC_ADD_PLAYLIST ( @PLAYLIST_NAME VARCHAR(255), @USER_ID VARCHAR(255), @ItemList NVARCHAR(4000), @delimiter CHAR(1), @FOLDERNAME VARCHAR(255) ,@PLAYLISTTYPE CHAR(1)) AS SET NOCOUNT ON
DECLARE @IDENT INT
IF @PLAYLISTTYPE = 'N'
BEGIN INSERT INTO MUSIC_PLAYLIST ( MUSIC_PLAYLIST_NAME, MUSIC_PLAYLIST_USER ) VALUES ( @PLAYLIST_NAME, @USER_ID )
SELECT @IDENT=@@IDENTITY FROM MUSIC_PLAYLIST END IF @PLAYLISTTYPE='E'
BEGIN
SELECT @IDENT=MUSIC_PLAYLIST_ID FROM MUSIC_PLAYLIST WHERE MUSIC_PLAYLIST_USER=@USER_ID AND MUSIC_PLAYLIST_NAME=@PLAYLIST_NAME
END
DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) -- INSERT INTO @IDTable(Item) VALUES(@Item)
INSERT INTO MUSIC_SONGSLIST (Music_PlayList_Id,Music_SongName,Music_Song_Location) VALUES (@IDENT,@ITEM,@FOLDERNAME+''+@ITEM) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END
GO
***************************************************************************************
Here the problem is am not getting the exact result means if i add 6 songs to a playlist it is adding only 4 songs to that particular playlist..
Please help me out
Thanks in Advance,
Madhavi
Strored Procedure Within SQL Transaction Executes Much Longer
Jul 20, 2005Hi,I have stored procedure (MS SQL Server 2000) which operateson around 600 000 rows (SELECT, UPDATE, INSERT)and executes in 5 minutes,when I put it in SQL transaction it slows down to more than 5 hours (!!)I have to admit that it is not problem with data locks (beside thatprocedurenothing else is executed on db),It is not also problem with that exact procedure, other proceduresalso slow down heavily when wrapped by SQL transactionvery very seldom stored procedure within transaction executescomparably long that its copy without transactionI guess it could be MS SQL Server 2000 configuration/tuning problem.Any ideas ?Chris
View 1 Replies View RelatedDifference B/w User Defined Function And Strored Procedure
Jan 3, 2008Pls tell me the main differences b/w user defined function and stored procedure .
View 2 Replies View RelatedExport Strored Procedures In MS Sql Server Management Studio
Jul 18, 2007Hi,
Can anyone tell me how to export a batch of stored procedures in MS sql server Management studio?
Thanks.
Get Row Timestamp With No Timestamp Column
Jul 10, 2007For starters, please feel free to move this if it is in the wrong forum.
The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi 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 3 Replies View RelatedASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
Feb 23, 2007I 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 1 Replies View RelatedProcessing The Resultset Of Another Proc From A Proc
Apr 8, 2004Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000.
Basically I am calling proc2 from the inside of proc1.
proc2 returns 2 resultsets. I want to process these two resultsets
from within proc1.
If its possible , please provide sample code.
thanks in advance,
Alok.
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I 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
SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions
May 12, 2015Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View RelatedDB Engine :: Collation Change And DDL Change In Same Script
Nov 18, 2015We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
Resultset Of Proc In Another Proc
Oct 4, 2005Hello,i want to use the result set from a stored proc in another stored proc, forexample:create stored procedure proc1 (@x int) asdeclare @tbl (y1 int, y2 int)insert into @tbl values(@ * @x, @x * @x * @x)select * from @tblGO--create stored procedure proc2 (@x int) asdeclare @tbl (y1 int, y2 int)while @x > 0 begininsert into @tbl select (exec proc1 @x) <-- this is my problemset @x = @x - 1endselect * from @tblGO--I know i could use output parameters. But i want to know if something ispossible with SQL-Server?thanks,Helmut
View 4 Replies View RelatedCalling A Proc From A Proc
Jan 10, 2007I'm having problems calling my second proc (MyProcedure2) from within anexisting proc. MyProcedure2 does not seeem to fire this way. My code isbelow. Help appreciated.Thanks,TrevorALTER procedure dbo.MyProcedure1(@newsletterid int)ASSET NOCOUNT ON-- Return Subscribersdeclare @howmany intset @howmany=isnull((select count(subscriberid) from mySubscribers wherenewsletterid=@newsletterid),0)update Mynewsletters set status=3,howmany=@howmany wherenewsletterid=@newsletteridselect @howmanyexec MyProcedure2*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedTimestamp
Apr 2, 2007how to insert a value into table where the fieldname uses a datatype timestamp.(asp.net2.0)
View 1 Replies View RelatedDb Timestamp
Jun 6, 2007I have a timestamp in a date field of a table I have but when run it does not enter the date automatically and all I get is system.byte[] display where the dtae and time should be, how can i get it so this field places the date in automatically.???
View 1 Replies View RelatedTimestamp
Jun 20, 2007I am retrieving data from a database, and have been noticing some really strange timestamps. Has anyone ever seen a timestamp that looks like this: 16777215Karls
View 1 Replies View RelatedSQL TimeStamp Help
Jul 12, 2004Hello,
I just started using SQL and i don't really know too much syntax.
I have an ASP page that is inserting data into a table called YellowAlerts.
The first field in this table is AlertDate. I thought I read somewhere that there is a timestamp feature. If anyone could help me out that would be great.
Thanks.
TIMESTAMP --- HELP
Jul 6, 2000How can I add a timestamp column to a table that will let me know when
a record was inserted? (perhaps by setting a default to that column?)
For some reason, @@currenttimestamp gives me
some binary garbage.
Please help with any suggestions.
TimeStamp
Aug 23, 1999How do I "disable" the timestamp so that when I copy my data into a new
database it will use the timestamp from the last time the table was effected?
Thanks,
Tracy
Timestamp
Jun 2, 1999Hi All,
I believe that all created tables have got by default a timestamp field.
I tried to find out how to read this field with a Transact-SQL statement.
And so I don't know the syntax, is anyone can help me ?
Thanks,
Herve
Timestamp
Feb 10, 2005If i have a table with a 3 fields and one of them has a timestamp as a field type and I want to do an insert or update what is the value I am inserting for the column timestamp.
Help appreciated
Timestamp
Sep 23, 2006hello I put a time stamp in one of my column and it says binary data in the field. How do i make that binary data turn into a real date and time. If this is possible how do I separate the date from the time in the results or would I have to do this myself. Also, I need to sort the results by date can this be done?
Sorry for the stupid questions my sql book (sql demystified) does not elaborate. Thanks