How To Write A Simple, Accurate Stored Proc Timer?
Oct 25, 2006
I want to test some times it takes for a proc to run.
Since I work for a large company, I rarely have access to the SQL server in a capacity where I can use profiler and such.
Are there any quick and easy ways to just surround blocks of code in a T-SQL statement to get an accurate reading on how long it takes?
I.e., if I surround with GETDATE() before and after, does that also measure the round trips to the server, or just the execution time.
I want to just compare some different methods and see what is quicker.
THX.
View 4 Replies
ADVERTISEMENT
Sep 21, 2005
I have a stored procedure that looks like this-
IF NOT EXISTS
(Expression evaluates to true)
BEGIN
INSERT INTO Table
(Account, CustomerName )
VALUES
(@Account, @CustomerName)
SELECT Column FROM Table
WHERE PrimaryKey = PrimaryKeyJustCreatedAbove
END
I'm creating a new record in the table, after I create this record I want to select the columns from the newly created record. Problem is the only unique value is the primarykey that is created with the record. Is there a way to identify that value within the statement?
View 7 Replies
View Related
Sep 6, 2007
Can someone help me rewrite this query? Basically I need to check if the name stored in "CustomerName" already exist in the table "Renter". If it does not then I ncan insert the new customer name
into the data table "Renter". If the value in "CustomerName" already exists in the database, then I need to bypass the "INSERT" and somehow return a value indicating that the insert was not performed. How can I do this. Here is the
query I currently have for performing the insert.
CREATE PROCEDURE [dbo].[VacancyGet]
(
@CustomerName nvarchar(100),
@ClientCode nvarchar(10)
)
AS
INSERT INTO Renter
(
CustomerName,
ClientCode,
)
VALUES
(
@CustomerName,
@ClientCode,
)
View 1 Replies
View Related
Jul 20, 2005
HI,I need to run same kind of transactions (basically deleting records)in a loop but I have only 1 hour in a day to run my procedure. So Ineed to set a timer in a SP so that SP terminates after one hour andthen rest of the transactions will be done next day.Can anybody suggest as how to check execution time in a storedprocedure? The execution of the SP will be scheduled every night.If u need any further info pls ask.Thanks,Subodh
View 2 Replies
View Related
Dec 3, 2006
I need to check the databes on the server side every 3 days and delete old data.
I am using SQL Express.
View 5 Replies
View Related
Feb 13, 2008
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 3 Replies
View Related
Jan 16, 2007
I'm looking to write my own simple SQL Client via TCP/IP. We have some hardware that does not run an OS of sorts, but I can create TCP Connectons. We's like to be able to Query our SQL Server with this hardware.
The SQL Servers we are talking to are MS SQL2000 and MS SQL 2005 both via TCP port 1433. We can telnet to the port of the SQL Server. Any resources to the SQL Protocol you could pass along would be great.
Thank you,
Scott<-
View 4 Replies
View Related
Feb 3, 2006
hey guys very new to sql server - i can take care of the basic CRUD but,i'm not familiar with some of the syntax to handle stuff right in the proc. i've been making multiple round trips to accomplish what i think i should be able to do based on a condition right when im in the procedure itself. so, please bear with me.
i have a table with these 5 fields.
"AltNumb" "AltName" "TimeLastRun" "TimeNextRun" "skdDuration"
ALT001 FIRST ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:35:00 PM 5
ALT002 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:43:00 PM 10
ALT003 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:48:00 PM 15
i have a service that will call a procedure that takes the current_timestamp and compares it (datediff) with the column "TimeLastRun" and see's if it's equal with the "SkdDuration" - some are set to 5, 10, 15 minutess etc.
heres's the short lil proc below:
CREATE PROCEDURE dbo.Check_Time_Duration
AS
declare @myDate smalldatetime
select @myDate = getdate()
SELECT AlertNumber from time_check2 where DATEDIFF(MINUTE,timeLastRun,@myDate) = Schedule_Duration
what i'd like to do is if anything matches, is Update the two columns "TimeLastRun" & "TimeNextRun" right on the spot based on the AlertNumber(if that row matches in the above query).
something like this:
DECLARE @myDate smalldatetime
select @myDate = getDate()
Update time_check2 set timelastrun=@myDate,timenextrun=dateadd(minute,5,@ myDate)where AlertNumber ='ALT001'--this would need to be a variable
BUT the AlertNumber of course matched in the first select
Hopefully someone can see what i'm trying to accomplish here and give me some help
Been on Oracle too long - i feel like a real newbie here
thanks again
rik
View 1 Replies
View Related
Aug 24, 2006
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 3 Replies
View Related
Jun 15, 2006
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 3 Replies
View Related
Apr 30, 2008
Hello ALl, can anyone please tell me how to search for NULLS.
like i know one method....
select * from table
where col = 'NULL'
View 1 Replies
View Related
Feb 23, 2007
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 1 Replies
View Related
Feb 20, 2003
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 1 Replies
View Related
Jan 13, 2006
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 5 Replies
View Related
Jan 20, 2004
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 14 Replies
View Related
Aug 30, 2007
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 2 Replies
View Related
Sep 26, 2006
According to MS, GetLocalTime() (in C++) is only accurate to approx asecond,even though it reports milliseconds, and calling it twice and computingtheinterval can on occasion lead to a negative interval.Is T-SQL's GetDate() more accurate than that, or at leastnon-decreasing?Thanks,Jim
View 4 Replies
View Related
Mar 20, 2006
Hello,
I'm just trying out this new try-catch stuff in sql server 2005....
Using the example from the help, "Using TRY... CATCH in Transact-SQL" , it shows how things are done in the adventure works database.. loggin the error to the errorlog table and all that... looks great.. but I notice that when I implement this code in my project.. and tested by putting in a line that causes a divide by zero error... that the line number reported by error_line is acutally not the line at which the divide by zero code resides....
Any suggestions as to what would put the error_line out of whack? I have comments and some string literals in the code..would they be throwing it?
Thanks
View 5 Replies
View Related
Apr 24, 2006
Hi, how can i get a accurate division in tsql - is there some way of casting?
Problem is simple, "SELECT 3 / 2" is "1" - what can i do to get 1.5 as result?
View 3 Replies
View Related
Aug 21, 2012
SELECT
ts.[EmployeeId]
,p.[FirstName]
,p.[LastName]
[Code].....
This query should show people that have worked under 4 days but it displays the full database including all the people with 4 or more entries.
It works if the [StartedAt] and [HoursWorked] Fields are not included but I need these fields included.
View 2 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
May 16, 2006
I am newish to databases and would appreciate some
advise. I think I have a solution to my
problem but it is going to take me a lot of time to get it running. If there is a better way of doing it I would
like to know.
I have a table :-
“eventDates� with
columns (id, date, eventID,
eventCount)
The id auto increments as a Primary Key.
date holds the date of the
event.
EventID references another
table with info about the events
Up to 9 eventIDs can be added
for each date and I want eventCount to hold an integer (1 to 9) to allow me to “pivot�
the data to the table below
“results� with columns (date,
eventCount1, eventCount2 …..eventCount9) so each row will hold a date and non
to nine eventIDs occurring on that date.
Is there an easy way to keep eventCount accurate or do I
just have to write a lot of code? I
will need to be able to remove events as well as add them. I will use a mixture of stored procedures
and VB.Net I guess?
Many thanks for any advice.
Mike
View 4 Replies
View Related
Jun 17, 1999
I do hourly transaction log backups at 9,10, 11 etc...
When I restore from a 9:00 backup I clearly see changes that I made after 9:00 am!!!
I then noticed when I go to my scheduled backups that a 10 am backup was indeed done
but in the "restore from device" tab it says the last backup was at 9 am.
Apparently it is not showing the actual latest backup that was done. This explains why
when restoring from a 9am backup I am seeing changes after 9, because in reality
I am restoring from a 10 am backup!
Is this a bug? I am running on 6.5 sp 5a.
thanks in advance....Mary
View 1 Replies
View Related
Aug 27, 2007
Does anyone lese get the same results that I do?
tblTest has an id field and a testName field....table has 2 records:
1 kevin test 1
2 kevin test 3
SELECT *
FROM tblTest t
INNER JOIN CONTAINSTABLE(tblTest,*,'"kevin test 1"') as A ON t.id = A.[KEY]
Result is both records returned, but since the search is in quotes as an exact phrase, should not just the first record be returned?
View 3 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Dec 8, 2003
I am trying to write my first app using SQL Server. I am not able to even open a connection. I get the following error message:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I am not sure first of all why the user is listed as null. I hard coded a user name in for test purposes. Second, how DO I establish a trusted connection with a SQL Server. The SQL Server is located on an internal domain controller.
Also, is it necessary to have anything special installed on my remote machine? As I said, I am VERY new to Microsoft SQL Server
View 3 Replies
View Related
Nov 2, 2015
How do I get an accurate count of workstations connected to a database?
View 7 Replies
View Related
Dec 18, 2007
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 3 Replies
View Related
Sep 17, 2007
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
View 6 Replies
View Related
Jun 6, 2007
Hi,
Im trying to build a solution that Selects a couple of rows, checks their timestamp and sends them as a file with FTP if they´re up to date.
However, those old rows that haven´t been updated yet I need to check every hour to see if the they have been updated and send them with FTP when thei´re up to date.
Is is possable to solve this by using SSIS?
Many thanks.
View 1 Replies
View Related
Apr 27, 2006
I'm using conversation timers successfully to fire events at a predetermined time in the future, but I'm running into issues when using an interval of considerable size. I set the conversation timeout like so:
set @Timeout = DATEDIFF(SECOND, GETDATE(), DATEADD(MINUTE, -(@TimeOffset), @FollowUpDateTime));
if (@Timeout < 0)
set @Timeout = 1;
// begin dialog
begin conversation timer (@FollowUpConversation)
timeout = @Timeout;
In this case @Timeout was 94693494.
In the SQL error log I see the following error: "Invalid subcommand value 94693494. Legal range from 1 to 2147483."
I thought I may check the @Timeout value and simply set it to 2147483 if it is larger than 2147483, but I was wondering if there was a reason the upper limit was so small.
Thanks,
Chris
View 10 Replies
View Related
Apr 24, 2006
I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:
"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."
I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?
Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?
Thanks,
Chris
View 5 Replies
View Related
Nov 17, 2006
I need to follow up on a message and check on its status. I am planning on using Conversation Timers (self addressed). I've tried it and they do work well. I am wondering if the LIFETIME parameter can be used for the same purpose. If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue? It does not seem that this is the case, but it is worth checking, as it could be a much desired feature.
Thanks,
View 5 Replies
View Related
