Putting A Stored Proc To Sleep.
Jun 2, 2004is there a way to pause a stored proc for x amount of time and then continue. I rather not go through a loop x number of times if I had to.
thanks,
DMW
ADVERTISEMENT
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
Putting Breakpoint For Stored Procedures
Mar 31, 2008Hi All,
How we breakpoint to the vb.net code to analyse the code. Is it possible to put breakpoint to stored proceudres so that analysis can be done.
Thanks
Abdul
Putting @Parameters In Stored Procedures
Nov 18, 2005I am creating a stored Procedure and I am getting an error which relates to DATENAME.
SELECT COUNT(*) AS calls, DATENAME(@varDate, CALLSTARTTIME) AS 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS='1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <=@EndDatesql doesn't like: DATENAME( @varDate, CallStartTime)sql works fine if I change @varDate into 'yy', 'mm', 'dd', or 'wk'Since I do not want to make 5 unique Stored Proc just because of @varDate.....Is there any way to work around this problem?
Stored Proc - Calling A Remote Stored Proc
Aug 24, 2006I 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.
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 RelatedCalling A Stored Proc From Within Another Stored Proc
Feb 20, 2003I 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
Stored Proc Calls Another Stored Proc
Jan 13, 2006Hi 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
Calling Stored Proc B From Stored Proc A
Jan 20, 2004Hi 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
Calling T SQL Stored Proc From CLR Stored Proc
Aug 30, 2007I 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.
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I 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 RelatedHow Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello 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.
Does Sql Server Has BIF Like Sleep()?
Apr 1, 2004Dear all:
Does sql server has BIF like vb sleep()?
I want to use this kind of function to balance the sql server loading,
or do you have better way to implement this task?
thanks for your kindly assistance
regards,
Stanley Huang
VBscript Sleep
Apr 28, 2004im writing a DTS package and i have am using vbscript to check if a file exists on an ftp server.
I can't figure out how to make my process sleep b/t checks on if the file exists.
in vb u can just
Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
but i cant seem to find a counterpart in vbscript.
thanks in advance,
pete
Sleep For N Seconds
Jul 20, 2005HiI am trying to use the WAITFOR function to make each loop in a cursoroccur every 4 seconds until the curdb is emptythe prtocedure is as follows---------------- startcreate procedure q_additionalrabatt@additionalrabatt float,@ordernr intASdeclare @ordradnr intdeclare curdb cursor for select ordernr, ordradnr from orp whereordernr = @ordernrfor read onlyopen curdbfetch curdb into @ordernr, @ordradnrwhile @@fetch_status = 0beginupdateorpsetorp.rabatt1 = (orp.rabatt1 + @additionalrabatt)whereorp.ordernr = @ordernr andorp.ordradnr = @ordradnrfetch curdb into @ordernr, @ordradnrendclose curdbdeallocate curdb------------------- endI need to make sure, that before it fetches the next row it waits 4seconds before executing the next loop.Matt
View 1 Replies View RelatedCalling A Stored Procedure From Within A Stored Proc
Dec 18, 2007Hi 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
Website Seems To 'sleep' When No One Accesses It....
Jan 24, 2007Hi,
Im currently running a website in conjuction with MSSQL Express. However at the moment the website/database seems to 'sleep' when no-one is using it, then there are huge dealays accessing the data while the database/website kicks in again. I tried to google this but truthfully i don't know what i am looking for. Does anyone have any ideas? Or whether this problem is Database or website driven? I thought there might be a control in the web.config but i haven't found a solution as yet!
Thanks
Daniel
Can You Use 'Pause' Or 'Sleep' In SQL Query?
Jan 4, 2008Can you use 'Pause' or 'Sleep' in SQL query?
View 3 Replies View RelatedReporting Service Seems To Go To Sleep After A While...
Sep 11, 2007
My reporting server seems to go to sleep after some time of non-use. This is affecting the users who create reports at the start of the day. A report that takes 1 second to generate takes 11 seconds on first hit in the morning. After the initial "wake up" call, the report server generates the reports normally without any delay. Can I keep it awake the whole time? Is there a setting somewhere?
Need Help Lost A Lot Of Sleep--Percentage??????
Oct 17, 2007
I created a report using the wizard. Every thing works fine. but in one section the report returns the value of a subtotal just fine. Now I'm trying to get it to do a %(percent) using the subtotal and it keeps returning a percentage using the grand total value of what I'm adding.
i have a
group total in the group <this fine> this is want i need look down |
V
catalog %
-------------------------------------------
person total to the group<want a % based on the group>
activity total based on the total of the person activity
can any help
SSRS 2005 Goes To Sleep!
Jul 17, 2006Has anyone figured out why it takes about 30-60 seconds to load a report or even get the login webpage in SSRS2005?? This seems to happen in 2 situations. One, the server has just started up and its the first request for a report or the SSRS webpage. Two, no one has requested a report or a SSRS webpage for a while (say 30min, maybe less). Its like part of SSRS has gone to sleep. Is there anyway to keep this from happening??
View 4 Replies View RelatedWscript.sleep Doesn't Work In SQL?
Aug 22, 2007I setup a maintenance plan in SQL and added some vbscript codes. One line is just wscript.sleep 10000. When I tried to run the job, I received error description: variable is undefined: 'wscript'
What's up with SQL?
Problem With Sleep Function In SSIS
Jun 22, 2006Hi,
I have a script task in SSIS which uses a sleep command. sleep(600000) which waits for 10 minutes. This works fine when i execute the package through GUI. But when i execute the same with Commandline, it does not work. Is there some thing which I have not set. I have also tried the Windows API call for sleep still it behaves the same way.
Any help??
Thanks in advance
Srividya
Report Server Goes To Sleep....zzzzzzz
Jul 21, 2006The known issue about the report server going to sleep after being idle for a while is causing me a problem. I have set up a dummy sibscription to create and email a report every hour and also have set the IIS setting on the application pool the RS uses (shut down worker process after being idle for x mins, I have set this to 860!).
But the report server still goes to sleep and it can take up to 60 seconds for a report to start up.
What else can I set to reduce this problem?
SPID Goes To SLEEP Every Time I Run A DBCC Command
May 14, 2008So every time i run a DBCC command like DBCC SHOW_STATISTICS and DBCC INDEXFRAG, the query executes and goes on for ever. When i do a sp_who2 i notice that my DBCC SPID goes to sleep mode with high CPUTime and High DiskIO's. i also noticed that my server has no activity, no inserts, no updates and no select statements running by other users. Why cant i run a DBCC command?
You thoughts and suggestions are highly appreciated!
Thanks in advance
Lana
THE LADDERS (The Most $100k+ Jobs.)
www.TheLadders.com
How To Repeat Delete Records And Sleep Awhile In T-SQL
Feb 14, 2008Hi:
I have one table in which there are almost 800,000 records, and I tried to delete them by SQL stored-procedure in which executing SQL like this: "delete from tablename where date='YYYYMMDD'", once I started this procedure, I noticed SQL server will take most of CPU resources in a short period, but that's not acceptable. and there are some other useful records in this table, we can't use Drop Table.
So my question is "can we delete 1000 records and then sleep awhile, and repeat until all these records are deleted !"
Any refernece will be appreciated, thanks!
Under Which Filegroup Are Stored Proc. Stored?
Aug 23, 2007When you create a Stored procedure, is it automatically stored under the default Filegoup?
How can I see under which Filegroup my Stored Procedures and Triggers are stored?
Multiple Table Joins Makes Query Go To Sleep
Jul 23, 2005Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed
View 9 Replies View RelatedFoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure
Mar 10, 2005I 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
Sql And Stored Proc
Jul 19, 2007Im 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.
?
Help With SQL Stored Proc
Feb 22, 2008I 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 5 Replies View RelatedSQL Stored Proc
Jan 15, 2004Hi,
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?