I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
I encountered a werid bug that I can't figure it out in my stored procedure.Here's some sample code the can represent the scenario
Create Proc sp_test @DeptID Int as Begin Declare @i int=0 Declare @Count int=(Select count(*) from Total) while(@i<@Count)
[code]....
In the above code Total is a table that has employee name and its department ID and row_number info.The above code should list all employee info that belongs to one DEpt.but after I placed a try catch block the select statement returns no records.If I removed the try catch block it behaves correct.For example If three records reside in the Total table for a certain DeptID.
I expect the outPut will be Name age salary Mike 35 $60006 Tom 50 $75000 Frank 55 $120000
I have a Stored Proc which populates a table and then uses BCP to output the table into a flat file and lastly ftp the file out to a remote site.
I'm trying to update the error handling as I first wrote this script on SQL2000 and it has now moved to SQL2008r2. The stored proc looks something like this:
BEGIN TRY BEGIN TRANSACTION <A whole bunch of inserts and updates> COMMIT TRANSACTION END TRY BEGIN CATCH <Error handling> ROLLBACK TRANSACTION END CATCH BEGIN <xp_cmdshell, BCP, FTP stuff> END
What I need to do is jump to the end of the script if an error invokes the CATCH block, so the xp_cmdshell stuff is not exicuted. Can I simply put a GOTO statement to take it to the end in the CATCH block, or do I have to set a variable in the CATCH block then test the variable outside the CATCH block or indeed is there a better way to simply terminate the script following the ROLLBACK?
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this: IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL BEGIN
I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.
I've written a couple blocks but I have no idea when it comes to this one. Create a PL/SQL block to retrieve the last name and department ID if each employee from the EMPLOYEES table for those employees whose EMPLOYEE_ID is less than 114. From the values retreived from the employees table, populate two PL/SQL tables, one to store the records of the employee last names and the other to store the records of their department IDs. Using a loop, retreive the employee name information and salary infromation from the PL/SQL tables and doisplay it in the window, using DBMS_OUTPUT.PUT_LINE. Display these details for the first 15 employees in the PL/SQL tables. Any help helps
If I've got a 64-bit OS such as Windows XP Professional x64 Edition running and I have the SQL Server 2005 Express Edition, is AES permittable or does TRIPLEDES still need to be used?
I have been experiencing a lot of blocking activity in my database. My trace results show that the following extended stored procedures sp_prepexec and sp_cursorfetch are executed by both the user causing the block and the user tha is blocked. Any ideas as to how to rectify this situation will be appreciated.
HI, i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin begin try insert into dbo.Test values (1,'aaa') -- here we are inserting int value in identity field... END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
Whereas the following block works fine and the Catch block executes.
Begin begin try Select 1/0 --This causes an error. END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
Hi there! I need such thing: I have a data in Table A. When I get this data, I also increment this data by one, what I want is block Table A in order to other people will not get access to data.. I think about transactions, but are transactions reaaly blocks a table's content? P.S. For example in MySQL there are Lock table command for blocking table for reading/writing. I need such thing/ Thanks
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
hi there, to retrieve more then one record with a cursor we can use block cursor, so we have to set the cursor attribute SQL_ATTR_ROWSET_SIZE to some number...i am not getting any code example on this. do anyone know this???
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated. I am unable to understand as to why the CATCH block is not executed when an INSERT is made On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2 Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions: Warnings or informational messages with a severity of 10 or lower. Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb go
create table t1 (a int) create table t2 (b int) create table t3 (c int)
Begin tran
Insert into t1 values (1) Insert into t2 values (2) Insert into t3 values (3)
IF @@error <> 0 Rollback tran else commit tran -------------------------------------------------------------
Select * from t1 Select * from t2 Select * from t3 -------------------------------------------------------------
Insert into t1 values (1) Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3) commit tran insertNow End try
Begin Catch
IF (XACT_STATE()) = -1 BEGIN PRINT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.' ROLLBACK TRANSACTION insertNow END;
-- Test if the transaction is active and valid. IF (XACT_STATE()) = 1 BEGIN PRINT 'The transaction is committable.' + ' Committing transaction.' COMMIT TRANSACTION insertNow END
connection 1. one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files. After having did this, we add 1 at grp field. we insert into orv the record max(grp) + 1
connection 2. an other application could insert at the same time record in this table orv with same parameters.
my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2. I believe I need to use HOLDLOCK, UPDLOCK. but I have not the habitude to use them.
Can I do this ? connection 1 select max(grp) from orv WITH HOLDLOCK connection 2 what should I use to avoid lock when I need to insert into orv. ?
There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks.
i've performed a test on 2 machines based on the topic above, but it doesn't seem like blocking each other... This is the context...
I've created a table call TEST_DEL i.e. quote:CREATE TABLE test_del (v_id INT, desc CHAR(3) )
In machine 1, i'm login as USER 1 and try to insert a set of records into the table quote: Machine 1 ~~~~ BEGIN DECLARE @li_num int, @li_start int
SET @li_num = 100000 SET @li_start = 1
WHILE @li_start < @li_num BEGIN INSERT INTO test_del VALUES (@li_start, 'zzz', 'xxx')
IF (@li_start > @li_num) BREAK ELSE SET @li_start = @li_start + 1 CONTINUE
END END
In machine 2, i login in as USER 2 to retrieve the records at the same time... quote: SELECT * FROM test_del
but, the system still allow me to retrieve the records at the same time... May i know when will "Writer block reader, reader block writer" occur and in what situation
I am writing some code generation stuff and I am trying to get a scriptlike this to work:IF (something)BEGINCREATE PROCEDURE WhateverASSELECT 1 as oneENDBut it complains about this, so I am guessing that I can't put thecreate prodcedure in an IF block.Does anyone know of a work around for this?
I have a job that has several steps. One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action. Else exit from the job reporting failure.
I would like to know what sql stmts should i use to exit from the job (in the ELSE block) I tried to use EXIT in the else stmt but it is not exiting from the job step.
I'm working on a stored procedure which includes an IF statement at the end of the procedure which appends my sql call with two lines. But I'm getting an error that probably comes from the fact that I have a nested BEGIN END block in my overall procedure. Can anyone tell me if my assumption is correct and help polish of the syntax?
The error I'm getting is:
Msg 156, Level 15, State 1, Procedure payments_sp, Line 55
Incorrect syntax near the keyword 'AND'.
and the sql code looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[payments_sp] -- Add the parameters for the stored procedure here @payment_type varchar(15), @mydate smalldatetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @myBool1 tinyint, @myBool12 tinyint SET @myBool1 = 1 IF (@payment_type = 'CODE1' OR @payment_type = 'CODE4') SET @myBool1 = 1 ELSE -- the payment type must be 'CODE1' or 'CODE2' -- set the value to false SET @myBool1 = 0 IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4') SET @myBool12 = 1 ELSE SET @myBool12 = 0
SELECT SUM(Mydatabase.dbo.[PAYMENTS].[AMT_RECD]) FROM Mydatabase.dbo.[PAYMENTS] INNER JOIN Mydatabase.dbo.[ACCT] ON Mydatabase.dbo.[PAYMENTS].[SOME_UID] = Mydatabase.dbo.[ACCT].[SOME_UID] WHERE Mydatabase.dbo.[ACCT].[CLIENT] = 'MY CLIENT' AND Mydatabase.dbo.[PAYMENTS].[DATE_RECD] = @mydate AND Mydatabase.dbo.[PAYMENTS].[BOOL] = @myBool1 AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val1' AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val2' AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val3'
IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4') BEGIN AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val4' AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val5' END END GO
SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
FROM src_terrier INNER JOIN src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no
WHERE (src_terrier.datadate = @dt_src_date) AND (@chr_div is null or src_terrier.Areacode = @chr_div) AND (@vch_portfolio_no is null or src_centre_list.Portfolio_no = @vch_portfolio_no) AND (@vch_prop_cat is null or src_centre_list.propcat = @vch_prop_cat)
How can I modify this function in two ways
1. I want the value of src_terrier.siteref to be matched to a different table src_tbl_budget.siteref. I want all rows in src_terrier.siteref and only the one row from the new table where the siteref is matched.
2. Also part of the new query, not only do I want it to match the siteref, I also want it to only match the mm/yyyy of the parameter entered (src_terrier.datadate = @dt_src_date) in the WHERE statement.
Help! On my desktop some security warning came up and my friend clicked "ok" when it said "block". Apparently it was warning her that internet explorer was trying to contact the internet. So...now internet explorer cannot display anything and it is the only browser I have on my computer. I am so frustrated I can't figure out what on earth I can change to fix this. Please...someone help me figure out why I can't contact the internet? My internet connection is fine, and the troubleshooting feature in explorer keeps telling me so.
I guess this is a common problem because I ran into a lot of threads concerning the matter. Unfortunately, none of them helped my situation.I am throw a RAISERROR() in my sql and my vb.net try catch block is not detecting an error. SELECT '3' RAISERROR('testerror',10,5) Dim con As New SqlConnection Dim _sqlcommand As New SqlCommand con = New SqlConnection(Spiritopedia.Web.Globals.Settings.General.ConnectionString) _sqlcommand.CommandType = Data.CommandType.StoredProcedure _sqlcommand.CommandText = "TestFunction" _sqlcommand.Connection = con
'The value to be returned Dim value As New Object 'Execute the command making sure the connection gets closed in the end
Try
'Open the connection of the command _sqlcommand.Connection.Open() 'Execute the command and get the number of affected rows 'value = _sqlcommand.ExecuteScalar().ToString()
value = _sqlcommand.ExecuteScalar()
Catch ex As SqlException Throw ex Finally
'Close the connection _sqlcommand.Connection.Close() End Try
Is there any way to clear out the exception from a previous Try/Catch block if I am nesting another Try/Catch block within it. I am executing a SQL Command and based on the Error number coming back decide whether or not to execute various other Sql commands. Now the Outer exception seems to be taking precedence over the Inner try block, and even though the code is stepped through for the inner try block it is never executed due to the Parent Exception. Is there any way to clear the exception received from the outer Try block? Here is a snippet of code:
Hi, Is there anyway I can impliment this in my application without installing it as a COM. My Web Hoster says they won't install it. The Data Access Application Block
is there a way to process a file x records at a time?
We have a table that I need to append to an existing table. The date columns are currently in char but must be converted to datetime for the existing table. The problem is I have bad data. There are 3 million rows where the date field isn't valid for SQL's datetime format. Since this is the data I have, I have to work with it. I would like for SQL to just insert a null if it comes upon a bad date. Currently when it encounters a field that isn't valid, it stops the process with an error.
I have tried to go around it below, but there is still something "hanging" I would like to be able just to insert one million rows at a time and if it errors, then I can look at the next million, find the error, fix it and continue on.
Any suggestions? Or if you have a better idea all together I would love to see it.
SQL Server 7.0, SP2
,CASE when (substring(check_date,1,4) not between '1997' and '2000' or substring(check_date,5,2) not between '01' and '12' or substring(check_date,7,2) not between '01' and '31') THEN null ELSE cast(check_date as datetime) END AS check_date
(0 row(s) affected) Msg 208, Level 16, State 1, Line 41 Invalid object name 'X_SET_PREOP'.
FOR THE FOLLOWING CODE SEGMENT.. I am trying to do 2 updates with just one WITH BLOCk.Create table #temp( MPOG_CASE_ID uniqueidentifier, lab_name varchar(100), lab_date datetime, lab_value decimal(19,2) );
with X_SET_PREOP as ( SELECT xx= ROW_NUMBER() OVER ( PARTITION BY lab.MPOG_Case_ID, lab.lab_name ORDER BY lab.lab_date DESC ), lab.MPOG_Case_ID, lab.lab_name, lab.lab_value,lab.lab_date FROM MPOG_Research..ACRC_427_lab_data lab
I am using SQL server 2000 with Uniface (4GL).I am running a process which commits every 30 seconds or so on onemachine and another user is trying to perform a small update at thesame time on the one of the tables whose data is modified by the firstprocess.I accept that the two second process may get blocked at times by thefirst but expect that to only happen rarely due to the likelihood ofboth processes trying to update the very same row in a large table.Other users are also on line at the same time performing updatesacross the database.What I have witnessed though was the second process being blocked bythe first (confirmed by looking in enterprise manager) and REMAININGBLOCKED EVEN AFTER A COMMIT in the first process!!My big problem is that I have only seen this happen once on a customersite and have not been able to reproduce it myself to provide anyfurther information.The application is set to use row level locking and is also in readuncommited mode.Any help or suggestions would be greatly appreciated.Bob.
