FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT childTable.childValue
FROM childTable
WHERE childTable.childId = @childId
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
END
CLOSE parent_cursor
DEALLOCATE parent_cursor
COMMIT TRAN T1
GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.
My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter()
da.SelectCommand = New SqlCommand("myStoredProc", conn)
Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int)
paramParentId.Value = 1
Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor")
paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?
Hello. I'm having a perfectly(!) normal stored procedure that returns a Resultset with one row (containing an ID I want). Not I need that ID in another stored procedure and I can't get it out from the stored procedure.
exec @blabla = MyProc -- works well if I use return exec MyProc @blabla -- works using OUTPUT keyword
But neither of these examples works with a CURSOR as the @blabla. Do I need to specificly pass a cursor as a return value, wich would give me bellyache, or can I do something like this:
DECLARE @MyCursor CURSOR SET @MyCursor = CURSOR FOR exec MyProc
I have a stored Procedure that is looping through multiple cursors.
It is never finding any records in any curosr that is using a local variable in the where clause...Help
Alter Procedure ProjectedIncome As SET ROWCOUNT 0 Declare -- Date types @startdate smalldatetime ,@enddate smalldatetime ,@ProjectedDate smalldatetime ,@termination smalldatetime ,@effectivedate smalldatetime -- Integer ,@Nums int ,@nums2 int ,@ClientId int ,@AssetId int ,@ProductID int ,@Policies int ,@product int ,@Per int ,@Projected int
-- String ,@debugtext varchar(150) ,@productid2 varchar(15) -- float ,@rate float ,@Cap float --bit ,@Override bit --Money ,@AnnualPremium Money ,@Value Money ,@Premium Money ,@PaymentAmount Money --Doubles ,@PremCalc int ,@HoldPrem int ,@HoldCom int ,@CumBal int ,@CumPrem int ,@MonthlyPrem int ,@XBal int ,@CapPrev int ,@PremTier int ,@Incriment int
--Declare cursor for System Variables DECLARE SystemVar_cur cursor for SELECT ProjectionStartDate,ProjectionEndDate from SystemVariables
--Declare the Cursor for Asset Definitions declare AssetDef_cur cursor for SELECT termination,effectivedate,ClientID,AnnualPremium,A ssetID,ProductID,Policies from AssetDefinitions
--Declare cursor for CommisionDefinitions declare CommisionDef_cur cursor for Select a.product,a.per,a.cap,a.rate,a.value from CommisionDefinitions a where a.product = @ProductId2;
--Declare cursor for projections declare projections_cur cursor for Select a.override,a.premium,a.paymentamount from projections a where a.date = @ProjectedDate and assetid = @AssetId;
-- Select from the SystemVariables Table OPEN SystemVar_cur FETCH SystemVar_cur INTO @startdate,@enddate CLOSE SystemVar_cur DEALLOCATE SystemVar_cur -- Open the AssetDefinition File and loop through -- INSERT INTO debug_table VALUES('Open the Asset Cursor')
Open AssetDef_cur
Fetch AssetDef_cur INTO @termination ,@effectivedate ,@clientId ,@AnnualPremium ,@assetId ,@ProductId ,@Policies
While @@fetch_status = 0 Begin-- begin AssetDefinitions Loop
--If Asset is not Terminated
If @termination IS NULL BEGIN-- begin @termination IS NULL SET @MonthlyPrem = (@AnnualPremium/12) SET @debugtext = 'MonthlyPrem = AnnualPrem' + CAST(@AnnualPremium as Char) + '/12' INSERT INTO debug_table VALUES(@debugtext)
If @effectivedate > @startdate SET @ProjectedDate = @effectivedate Else SET @ProjectedDate = @startdate -- end if SET @PremCalc = 0 SET @CumBal = 0 SET @XBal = 0 SET @HoldCom = 0
-- Fetch the Projection Record open projections_cur
fetch projections_cur INTO @override,@premium,@paymentamount If @@fetch_status = 0 BEGIN IF @override = 1 BEGIN-- begin @override = 1
SET @CumPrem = @premium SET @CumBal = @paymentamount SET @HoldPrem = @CumPrem SET @HoldCom = @CumBal END-- end @override = 1 Else SET @HoldPrem = @MonthlyPrem END CLOSE projections_cur While @ProjectedDate <= @enddate BEGIN-- begin While @ProjectedDate <= @enddate SET @CapPrev = 0 --reset cap balance SET @XBal = 0 SET @debugtext = 'Begin Get Commision Record For Product' + CAST(@productID as CHAR) INSERT INTO debug_table VALUES(@debugtext) SET @productid2 = @productid SET @PremTier = @HoldPrem
---NOW Open the CommisionDef table OPEN CommisionDef_cur
FETCH CommisionDef_cur INTO @product,@per,@cap,@rate,@value IF @@fetch_status <> 0 BEGIN SET @debugtext = 'ERROR? ' + CAST(@@error as Char) INSERT INTO debug_table VALUES(@debugtext) END WHILE @@fetch_status = 0 BEGIN-- begin While CommisionDef Fetch = 0 SET @debugtext = 'Found Commision Record' + CAST(@product as Char) INSERT INTO debug_table VALUES(@debugtext)
If @Per = 0 BEGIN-- begin If @Per = 0
SET @Incriment = @Cap - @CapPrev If @PremTier > @Incriment SET @XBal = @XBal + (@Incriment * @Rate) Else BEGIN-- begin @PremTier > @Incriment If @PremTier >= 0 SET @XBal = @XBal + (@PremTier * @Rate) END-- end @PremTier > @Incriment SET @debugtext = 'XBal ' + CAST(@XBal as CHAR(10)) INSERT INTO debug_table VALUES(@debugtext)
SET @CapPrev = @Cap SET @PremTier = @PremTier - @Incriment END-- end If @Per = 0
Else BEGIN-- begin If @Per <> 0
SET @XBal = @value * @Policies / 12 SET @HoldCom = 0 SET @PremCalc = 0 SET @CumBal = @XBal SET @debugtext = 'CumBal' + CAST(@CumBal as Char) INSERT INTO debug_table VALUES(@debugtext)
SET @HoldPrem = @Policies END-- end If @Per <> 0
FETCH CommisionDef_cur INTO @product,@per,@cap,@rate,@value END-- end While CommisionDef Fetch = 0
CLOSE commisionDef_cur -- Fetch the Projection Record open projections_cur
fetch projections_cur INTO @override,@premium,@paymentamount IF @@fetch_status = 0 BEGIN -- begin Projection Fetch = 0 IF @override = 1 SET @HoldCom = @CumBal ELSE -- If not overridden, set the fields to Update the projection File BEGIN-- begin @override <> 1 SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100 SET @Premium = @HoldPrem - @PremCalc UPDATE projections SET projected = @projected, premium = @Premium where assetid=@AssetID and date = @ProjectedDate SET @HoldCom = @XBal END-- end @override <> 1
END-- end Projection Fetch = 0 ELSE BEGIN -- Begin Projection Fetch else IF @@fetch_status = -1 BEGIN-- begin Projection Fetch = -1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100 SET @Premium = @HoldPrem - @PremCalc SET @debugtext = '((xbal - holdcom)*100 + 0.5)/100 ' + CAST(@Xbal as char) + ' , ' + CAST(@holdcom as CHAR) INSERT INTO debug_table VALUES(@debugtext)
SET @debugtext = 'Projection Record Not Found so Write it' INSERT INTO debug_table VALUES(@debugtext) --Projection record was not found so write it SET @override = 0 INSERT INTO Projections (AssetId,Date,Premium,Projected,Override,Payment,P aymentAmount) VALUES(@AssetId,@ProjectedDate,@Premium,@Projected ,@override,0,0) SET @HoldCom = @XBal END-- end Projection Fetch = -1 END -- end Projection Fetch else
CLOSE projections_cur
SET @ProjectedDate = DateAdd("m", 1, @ProjectedDate) SET @PremCalc = @HoldPrem -- Fetch the Projection Record OPEN projections_cur
FETCH projections_cur INTO @override,@premium,@paymentamount IF @override = 1 BEGIN-- begin @override = 1
SET @CumBal = @paymentamount SET @HoldPrem = @HoldPrem + @CumPrem END -- end @override = 1
ELSE SET @HoldPrem = @HoldPrem + @MonthlyPrem CLOSE projections_cur
END-- End the While ProjectedDate <=@enddate END --End the If Termination is NULL Fetch AssetDef_cur INTO @termination ,@effectivedate ,@clientId ,@AnnualPremium ,@assetId ,@ProductId ,@Policies END CLOSE AssetDef_cur DEALLOCATE AssetDef_cur DEALLOCATE projections_cur DEALLOCATE CommisionDef_cur
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for Select emp_no, emp_name, event_date, Test_no, Code, Test_result From test_table1 order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
How can I view the output of a stored procedure that is returning a OUTPUT variable? I've written a stored proc that uses OUTPUT but when I run it, all I see is "The command(s) completed successfully." I'm at a loss on how to debug/verify/view the output value.
The same thing happens using this example from MS.
CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT AS SELECT 'Title Name' = title FROM titles WHERE title LIKE @TITLE SELECT @SUM = SUM(price) FROM titles WHERE title LIKE @TITLE GO
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc ( @IDINT, @NameVARCHAR (200), @SessionIDINT ) AS BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.The problem is that cmd.Parameters["@UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.Here is my code:using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("mys_ExistsPersonWithUserName", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@UserExists", SqlDbType.Int); cmd.Parameters["@UserExists"].Direction = ParameterDirection.Output; cn.Open(); int x = (int)cmd.Parameters["@UserExists"].Value; cn.Close(); return (x>1); } And the corresponding stored procedure: ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName ( @UserName varchar(16), @UserExists int OUTPUT ) AS SET NOCOUNT ON SELECT @UserExists = count(*) FROM mys_Profiles WHERE UserName = @UserName
Hi Guys I am wondering if you could spare some time and help me out with this puzzle. I am new to this stuff so please take it easy on me.
I’m trying to create procedure which will take 2 input parameters and give me 1 back. Originally there will be more outputs but for this training exercise 1 should do. There are 2 tables as per diagram below and what I’m trying to do is Verify username & password and pull out user group_name.
For my proc. I am using some ideas from this and some other sites, but obviously i've done something wrong.
'==================================================== ALTER PROCEDURE dbo.try01 ( @UserName varchar(50), @Password varchar(50), @Group varchar Output ) AS SET NOCOUNT ON; SELECT TBL_USERS.USERNAME, TBL_USERS.PASSWORD,@Group = TBL_USER_GROUPS.GROUP_NAME, TBL_USERS.USER_ID, TBL_USER_GROUPS.GROUP_ID FROM TBL_USERS INNER JOIN TBL_USER_GROUPS ON TBL_USERS.GROUP_ID = TBL_USER_GROUPS.GROUP_ID WHERE (TBL_USERS.USERNAME = @UserName) AND (TBL_USERS.PASSWORD = @Password) '====================================================
and this is what i'm getting in VS.Net while trying to save.
'==================================================== ADO error: A select statement that assigns a value to variable must not be combined with data-retrieval operation. '====================================================
I did not see any samples on the net using ‘varchar’ as OUTPUT usually they where all ‘int’s. Could that be the problem?
I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.
Here is the entirety of the SQL in the SQLStatement property: EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT; I have also tried it like this: EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;
Here are my Parameter Mappings: Variable Name Direction Data Type Parameter Name User::ImportJobId Input LONG 0 User::FileType Input LONG 1 User::FileName Input LONG 2 User::FilePath Input LONG 3 User::ImportId Output LONG 4
When this task is run, I get the following error:
0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work. Any thoughts on what I might be doing wrong? Thanks.
How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below 1 int GetTheReturnValue=0;2//Code not shown// 9 mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0; // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();
I get this error: Server: Msg 119, Level 15, State 1, Line 10 Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
But when I run it this way : exec usp_List_Relationship_Users 1851 , 'Channel Member', @cust_idOUTPUT, @user_login_id OUTPUT, @username OUTPUT, @password OUTPUT, @status_id OUTPUT, @sdesc OUTPUT, @administrator OUTPUT
I get the expected results. Why should I omit the name of the name of the input parameters? I don't know why I am getting this error if don't run by ommitting the name of the input params.
Any help regarding this matter is greatlt appreciated. You can assume that all the variables are declared prior to executing the stored procedure.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON
GO -- ============================================= ALTER PROCEDURE [dbo].[spPK] -- Add the parameters for the stored procedure here @varNSC varchar(4), @varNC varchar(2), @varIIN varchar(7), @varIMCDMC varchar(8), @varOut as int output AS Declare @varPK int set @varPK = 0 BEGIN
--This checks Method 1 --NSC = @varNSC --NC = @varNC --IIN = @varIIN begin if exists (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varPK = (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varOut = @varPK if @varPK <> 0 Return end
[There are some more methods here]
Return
END
How do I get at the output value?
I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database.
I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver?
I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns.
First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement?
Second problem: What do I set the value of the parameter to?
in a asp .net application, I call a stored procedure which have a output parameter. the output parameter works find in sql session, but not in the asp .net application.
if I put select msg_out = "error message" in position A(see below for stored proc), it works fine if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session The stored proc was created like this:
Create procedure XXXXXXX (@msg_out varchar(80) OUTPUT ) as begin
while exists (*******) begin //position A if certain condition begin
select msg_out = "error message" return 1 end
end
end
end
It seems to me that anything inside if - the second begin...end - it wont get executed.
I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.
To continue with the set up, here is the stored procedure in question: CREATE PROCEDURE [dbo].[HE_GetStks] AS
select top 15 Rating, rank, coname, PriceClose, pricechg, DailyVol, symbol from
(selectf.rating, f.rank, s.coname, cast ( f.priceclose as decimal(10,2)) as PriceClose, cast ( f.pricechg as decimal(10,2)) as pricechg, f.DailyVol, f.symbol from dailydata f, snames s where f.tendcash = 0 and f.status = 1 and f.typ = 1 and f.osid = s.osid) tt order by rating desc, rank desc
GO
The code in the calling bat file is: REM ************************* REM BCP .WRK FILE REM ************************* bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.
I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
( NAME_COL varchar(64), ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
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
---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot. select id,fname,lname,sponsor from masterfile where id='TARZAN'
---from those 20 rows there is id that sponsored some one else ---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan) ---but Shaggy has sponsored 2 others select id,fname,lname,sponsor from masterfile where id='SHAGGY'
---will display 3 rows and if from one of those 3 others that belongs to shaggy ---I also want to get their information ID,fname,lname ---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches
---Explain: ---Let's assume that we have an OAK Tree that has 4 main branches ---and out of those 4 main branches 2 of them have other branches with leaves under it
--I would like to do this process in a cursor (Store Proc) is possible --the way I have it now taking way too long --because in within so many (do while loop)
TIA Please pardon me, I could not find better layout to explain this.
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
Existing Stored Procedure, has been running well on SQL since 7.0.(but needed some tweaking to migrate to 2000).Now all of a sudden after installing SP4 of SQL 2000,this process slows down, and SQL Spotlight shows the number of locksjust climbing throughout the processing run.According to the MS Knowledge Base Articles on KeyLocks .. this was aproblem that was *fixed* in the service pack ... where as for me it isnow broken.Article ID: 260652PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"May Hold Locks Longer http://support.microsoft.com/kb/260652/Article ID: 828096FIX: Key Locks Are Held Until the End of the Statement for Rows ThatDo Not Pass Filter Criteria http://support.microsoft.com/kb/828096/Anybody else have this issue, or have any "eazy" solutions?The proc cursors thru a list and runs a proc on each item in the "worklist".This is an existing systemwith no plans to turn the process into a set oriented one,as is going away shortly.
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':
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?
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
In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated.
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
--Here is the cursor script.--
Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255) DECLARE emp_skills CURSOR For select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924'
DECLARE emp_skills2 CURSOR For select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' OPEN emp_skills OPEN emp_skills2 FETCH NEXT FROM emp_skills into @empID, @Rank, @skills FETCH NEXT FROM emp_skills2 into @skills2 WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = @skills2 FETCH NEXT FROM emp_skills2 into @skills2 Print @empID + ' '+ @Rank + ' ' + @message
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
End CLOSE emp_skills DEALLOCATE emp_skills CLOSE emp_skills2 DEALLOCATE emp_skills2
--Previous Post--
Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.
--SQL SCRIPT__
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3')
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
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.
Hi, I have a situation where I need to sort the output of a cursor. But since the sort criteria are rather complex, I am NOT able to use the Order By clause directly with the cursor definition statement.
Hence, I need to have a solution where I will use a dummy (calculated) field within the CURSOR and I want the output of this cursor sorted by the dummy field that I calculated within the cursor itself.
Please let me know the different possibilities in this scenario.
INSERT INTO RCSAdvantage VALUES('123','Mike','Bhatt','12/12/2003','123','RCSA')
INSERT INTO RCSAdvantage VALUES('TM123','Mike','Bhatt','12/12/2003','456','TRIMICRO')
INSERT INTO RCSAdvantage VALUES('INR234','Mike','Bhatt','12/12/2003','890','INSIGHT')
INSERT INTO RCSAdvantage VALUES('INR234','John','Bhatt','12/12/2003','890','INSIGHT')
I needed to run following cursor and get the result exported to excel file. But Cursor retrives two resultset and while exporting to excel spreadsheet , it is the only first resultset without second resultset. How can it be exported to excel as a single resultset combined of first and second one.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
