network error

Hi,
Running a stored procedure in query analyser returns data but when running the asp.net page which uses that stored procedure returns an exception which is:
General network error. Check your network documentation

Do you know what the problem could be please?
ThanksNetwork errors are your SQL Server is installed with the local systems account which leaves SQL Server Agent without Network permissions. The solution you have to install SQL Server with a service account. Hope this helps.

Kind regards,
Gift Peddie|||Hi,
Are you sure this is the solution?
It's just that the query works for some and not for all of the queries that are passed through the network.
Thanks|||Installing SQL Server with a service account is good practice in all but free standing developer boxes because after SQL Server Service, SQL Server Agent is the next most important service in SQL Server. Some distributed queries will fail and you cannot run Replication because the Local Systems account leaves SQL Server Agent without Network permissions. Hope this helps.

Kind regards,
Gift Peddie

Nesting stored procedure, how to ?

Hello,

I'm trying to use nested stored procedure but can't get it to work.

I've created a stored procedure named 'sousmenu1_parents' which does a simple select, no parameter.

here's what I'm trying to achieve now :

CREATE PROCEDURE SelectAllDroitSpe

-- Add the parameters for the stored procedure here

@.UserId int

AS

EXEC sousmenu1_parents;

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT sousmenu1_parents.menu_id1 AS sousmenu1_id

FROM authorisations_speciales LEFT JOIN sousmenu1_parents ON authorisations_speciales.page_id = sousmenu1_parents.menu_id1

WHERE authorisations_speciales.utilisateur_id=@.UserId

END

I get 'sousmenu1_parents isn't a valid object name'

Where am I going wrong ?

Thanks a lot.

Flip:

If your stored procedure does a simple select then transform the procdure into an function.

Dave

|||

Hi,

you can′t directly select from a strored procedure although the procedure return a table with the results, you will have to EXEC the procedure and redirect the output into a table (like a temp table)

-Create a Table first (can be also a temp table)
-

INSERT INTO YourTable
EXEC procedurename

-Use the table in your query.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is a good alternative; the problem with the INSERT INTO ... EXEC PROC method is that you can only use this construct at a single level in a nested series; after that you get execution errors. Don't get me wrong, I use this construct frequently but it is definitely not my first choice when I am dealing with nesting a procedure that "is a simple select". You might be able to make a case for avoiding the function for some other reason but this post is titled "Nesting stored procedure, how to?"

|||

Thanks very much.

I'm now looking up on Functions.

I'm new to Sql Server and I'm migrating an Access database.

I used many nested queries in Access and it looks like converting them is going to be a nightmare.

One thing I don't understand : if I can't "directly select from a stored procedure" what is the point in nesting stored procedure ?

|||

Flip:

Stored procedures will nest; however, it sometims requires judicious use of your alternatives. There are several options to return data from a called stored procedure to a calling stored procedure:

Use of output parameter(s)|||

ignition, I was a big user of access in my past and used many nested access queries to produce result sets. Think of Access queries as Views in SQL.

If in Access you had two queries

select col1, col2 from tablea where some where clause.... (query name is Query1)

select count(*) from Query1 (query name is Query2)

In SQL you could create a View:

Create View vw_Query1 as

select col1, col2 from tableA where....some where clause

Select count(*) from vw_Query 1

Views do not allow you to pass parameters (this is where stored procedures or functions come into play)

Reason you might want to nest stored procedures is to utilize other stored procedures to get a resultset or parameter back to the calling procedure and/or to encapsulate standard code that you cannot execute using functions. Functions have specific limitations (can't do data manipulation (insert, create, drop, can only call extended stored procedures) that you can do in stored procedures.

Create Proc usp_Test1 @.tablename varchar(100)

as

exec usp_AddMissingColumns @.tablename (does an ALTER which you can't do in functions)

exec usp_SetBlanksToNull @.tablename

return

|||

Great, it works a treat using a View !

This all make sense now ...

So for all the simple SELECT queries I had in Access (no parameters) should I use views in Sql Server ? is it in any way faster than a stored procedure, or any other advantage of this ?

Thanks again everyone, and in case I don't come back here before next year : have a lot of fun to end 2006 !

|||

Ignition, I wouldn't automatically change every simple query you have in Access to a view. If you're migrating to SQL Server, it's a good time to analyze the MS Access queries you currently have to determine if there is a continued need for them. I know that I would create queries (in Access) to do some ad hoc things, and then leave them there (laziness on my part!). When I started looking at the stored queries (for migration), I realized that many of them could be combined into a handful of views (some did basically the same thing but with different columns).

Think of a view as a subset (either just certain columns, an aggregation, and/or with specific where clauses) of a table. If you are querying the same data with the same where clause continually, this is probably a good candidate for a view... If you are continually looking at only certain columns within a table, probably a candidate for a view...

Views, stored procedures, and functions all have their place to do certain types of activities. If you lookup these items in Books Online, they will give you some scenarios on when/where and how to use them...

Good luck and happy New Year...

|||Convert your SPs to table-valued functions or even views if there is no parameterization so that you can reuse them easily.

nesting stored procedure

hi I have another question to ever so helpful forum
I am trying to nest stored procedure but I guess it is not the way to do it as it does not work:

CREATE PROCEDURE dbo.GetSharesTransactionsbyDates

(
@.Startdate as char(10),
@.Enddate as char(10)

)

AS

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

I get complain that dbo is incorrect syntax

ST.Proc. which I try to call call is basicly a select statement with no parameters:

ALTER PROCEDURE dbo.GetSharesTransactionsData
AS
SELECT TRANS_DATE, TYPE_DESCRIPTION, SHARE_SYMBOL, SHARES_QUANTITY,
ROUND(PRICE_PER_SHARE,2) AS PRICE_PER_SHARE, COMMISSION_VALUE,
STAMP_DUTY,
dbo.GetShareTransactionTotalValue(PRICE_PER_SHARE,STAMP_DUTY,COMMISSION_VALUE,
SHARES_QUANTITY,CASH_AMOUNT, TYPE_DESCRIPTION) AS TOTAL_VALUE,/*calling function*/
ACCOUNT_NAME
FROM SHARES_TRANSACTIONS
WHERE (TYPE_DESCRIPTION = 'Sell') OR
(TYPE_DESCRIPTION = 'Buy') OR
(TYPE_DESCRIPTION = 'Cash Divident')
ORDER BY TRANS_DATEYou cannot really do what you are trying to do:

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

You cannot add a WHERE clause like that, and you would need to add EXEC to execute the SP. Can you instead create a SP that accepts two dates as parameters?|||Well I can. The idea is that I first bring all transactions up and then only transactions between the chosen dates so I was trying to avoid repeating the same sql select statement but I can rewrite the original SPROC by adding parameters and additional where clause
Thanks for advice.|||alter the sproc, have it accept paramaters with default values:

create procedure spFoo
@.startDate datetime = '1900-01-01',
@.enddate datetime = '2199-12-31'
AS
--whatever

this way, you have a single sp. existng calls continue to work.|||Aaa, thanks.sql

Nesting a SP within another SP?

I have a stored procedure that calls some UDF User Defined Functions,
the purpose of which is to create row strings out of numerous column
strings for matching uniqueIDs.

The problem is I need to join that SP with some other tables.

The SP I have reads something like:

mySPName
@.myUserID int
SELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningString
FROM myTEMPTableName
GROUP BY myUniqueID
WHERE myTEMPTableName.UserID = @.myUserID

I need to join that result with myTableName on myUniqueID such as:
Select myTableName.myField1, myTableName.myField2,
mySPName.myRunningString
From ...
-- joining myTableName.myUniqueID = mySPName.myUniqueID

Can this be done?
The reason I don't just do it with a View instead of an SP is that I
have that parameter that must be passed to filter the records in
myTEMPTableName.

Any help is appreciated.
lq

oh...
the UDF looks like:

Create Function dbo.fn_myFunctionName(@.myUniqueID as int) returns
nvarchar(500)
AS
BEGIN
DECLARE @.ret_value nvarchar(500)
SET @.ret_value=''
SELECT @.ret_value=@.ret_value + ';' + myString
FROM myTEMPTableName
WHERE
myUniqueID =@.myUniqueID
RETURN RIGHT(@.ret_value,Len(@.ret_value)-2)
ENDlaurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401272245.449a0756@.posting.google.com>...
> I have a stored procedure that calls some UDF User Defined Functions,
> the purpose of which is to create row strings out of numerous column
> strings for matching uniqueIDs.
> The problem is I need to join that SP with some other tables.
> The SP I have reads something like:
> mySPName
> @.myUserID int
> SELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningString
> FROM myTEMPTableName
> GROUP BY myUniqueID
> WHERE myTEMPTableName.UserID = @.myUserID
> I need to join that result with myTableName on myUniqueID such as:
> Select myTableName.myField1, myTableName.myField2,
> mySPName.myRunningString
> From ...
> -- joining myTableName.myUniqueID = mySPName.myUniqueID
> Can this be done?
> The reason I don't just do it with a View instead of an SP is that I
> have that parameter that must be passed to filter the records in
> myTEMPTableName.
> Any help is appreciated.
> lq
> oh...
> the UDF looks like:
> Create Function dbo.fn_myFunctionName(@.myUniqueID as int) returns
> nvarchar(500)
> AS
> BEGIN
> DECLARE @.ret_value nvarchar(500)
> SET @.ret_value=''
> SELECT @.ret_value=@.ret_value + ';' + myString
> FROM myTEMPTableName
> WHERE
> myUniqueID =@.myUniqueID
> RETURN RIGHT(@.ret_value,Len(@.ret_value)-2)
> END

There are some options described here:

http://www.sommarskog.se/share_data.html

From your description, rewriting the stored procedure as a
table-valued UDF sounds like it should be possible.

Simon

Nested Stored Procs w/Transactions

I have some nested stored procedures where one sp calls another, etc. I nee
d
this wrapped in a transaction so that if an error occurs on any one sp
(either the calling sp or the one that is called) it will fail.
I'm continuall getting this error: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 2, current count = 3." So I've been playing around with
where to put the Begin Tran, Committ,
Rollback, etc.
Here's some pseudo code:
CREATE PROCEDURE [dbo].[spFILE_PROCESS]
AS
--Perform some queries, etc, then:
Exec spInsert_Customer
Exec spInsert_Trans
----
Where do I place Begin Tran/ committ, etc? I want both spInsert_Customer
and spInsert_Trans to be their own transaction as I call these sps by
themselves
elsewhere in my application.Hi,
have you tried this:
create procedure dbo.spFile_Process
as
set implicit_transactions off
--do something here without transaction
begin tran OuterTran
exec spInsert_Customer
exec spInsert_Trans
commit OuterTran
both nested procedures should have the same construction:
create procedure spInsert_Customer
as
set implicit_transactions off
begin tran TranA
...
commit TranA
create procedure spInsert_Trans
as
set implicit_transactions off
begin tran TranB
...
commit TranB
in case of error you should rollback the transation you are within
(decreasing @.@.trancount) and possible outer transactions. you have to
specify name of the transaction when rolling back, otherwise you will roll
back all transactions you're in.
HTH
Peter|||Alternately you could check for the existance of a transaction at the
begining of each procedure and only open a new transaction within the
procedure if one does not exist:
IF @.@.Trancount != 0
set @.Dotran = 0
...
If @.Dotran = 1
begin tran
...
-- on error
if @.dotran = 1
rollback transaction
return @.error
-- on success
if @.dotran = 1
comit tran
return 0
"Rogas69" wrote:

> Hi,
> have you tried this:
> create procedure dbo.spFile_Process
> as
> set implicit_transactions off
> --do something here without transaction
> begin tran OuterTran
> exec spInsert_Customer
> exec spInsert_Trans
> commit OuterTran
> both nested procedures should have the same construction:
> create procedure spInsert_Customer
> as
> set implicit_transactions off
> begin tran TranA
> ...
> commit TranA
> create procedure spInsert_Trans
> as
> set implicit_transactions off
> begin tran TranB
> ...
> commit TranB
> in case of error you should rollback the transation you are within
> (decreasing @.@.trancount) and possible outer transactions. you have to
> specify name of the transaction when rolling back, otherwise you will roll
> back all transactions you're in.
> HTH
> Peter
>
>|||Aren't you forgetting :
if @.@.Trancount = 0
set@.DoTran = 1
Anyway...
I've gone ahead and added the following whenever the committ tran, begin
tran, or rollback tran appear in my stored procs:
IF @.@.TRANCOUNT > 0 AND @.@.ERROR <> 0 BEGIN
ROLLBACK TRAN
RETURN
END
IF @.@.TRANCOUNT > 0 AND @.@.ERROR = 0 BEGIN
COMMIT TRAN
END
Now I'm longer getting the error message, but the Transaction is not being
rolled back either.
"Tony Sellars" wrote:
> Alternately you could check for the existance of a transaction at the
> begining of each procedure and only open a new transaction within the
> procedure if one does not exist:
> IF @.@.Trancount != 0
> set @.Dotran = 0
> ...
> If @.Dotran = 1
> begin tran
> ...
> -- on error
> if @.dotran = 1
> rollback transaction
> return @.error
> -- on success
> if @.dotran = 1
> comit tran
> return 0
>
> "Rogas69" wrote:
>

nested stored procs

Hi Guys, is there a way of nesting a stored procedure within another...i dont mean calling one within another...more like

creating one within another e.g

Create proc TEST1

as

Create proc TEST2

as

select 'this is inner proc'

where by running test one creates test2

i tried this and got an error:

Msg 156, Level 15, State 1, Procedure TEST1, Line 3

Incorrect syntax near the keyword 'proc'.

is this possible if so how, cos i am trying to create a master Proc that creates a DB, and Tables and store procs, hence i want to use it like a Batch file or script.

To nest stored procedures, you must create them separately and have one proc call the other -- maybe something like this:

create procedure A

as

print 'This is procedure a.'

go

create procedure B

as

exec A

go

exec B

-- - Output: -
-- This is procedure a.

If you are trying to create a procedure that creates other procedures, you will need to take a different approach. Stored procedures cannot directly create other database objects.

|||

You cannot create a stored procedure from within a stored procedure.

You can create a T-SQL script file, and then execute that script file from the command line using OSQL.exe or SQLCommand.exe

Refer to Books Online for OSQL utility, or SQLCommand.

|||Thanks Kent yeah i do now this form of nesting but its not suitable as i am not calling a proc from a proc|||

thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

ta.

|||

One thing to remember is that when using scripts, each use of 'GO' will clear all variables. You may have to re-establish them for the next object.

Sometimes, from the stored procedure, I have loaded a [Name-Value] table with input parameter values that will be used as variables in the script file. The values can be pulled whenever needed.

|||

>> thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

You would have to create the SPs in the newly created database so your SP create statements couldn't be in-line anyway.

Usually I do this via osql or just concatenated scripts.

|||

One other option would be to use dynamic sql to create the procedure. The code below in option 1 creates a permanent procedure that is then executed. The code in option 2 creates a temporary stored procedure that is then executed. Temporary procedures are nice when the task you are executing doesn't need to be permanent. HTH.

-Chris

--Option 1

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE myProc2 AS select getdate()')

exec myProc2

END;

GO

EXEC myProc1

--Option 2

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE #myProc2 AS select getdate()')

exec #myProc2

END;

GO

EXEC myProc1

|||

Why Not..! Yes you can create... But it is bit different....

You have to use the procedure numbers...

The main storedproc will be visible to world rest will be hidden.

You can call them from your main proc / externally..

Example:

Code Snippet

Create proc MyGroupedProc

(

@.Param as int

)

as

Begin

Exec MyGroupedProc;2 @.Param

End

Go

Create Proc MyGroupedProc;2

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [2]'

Select @.Param as [@. 2]

Exec MyGroupedProc;3 @.param

End

Go

Create Proc MyGroupedProc;3

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [3]'

Select @.Param as [@. 3]

End

Go

ExecMyGroupedProc 1

Select * from Sysobjects Where Name Like 'MyGroupedProc%' --It only list the Main Proc not other 2

ExecMyGroupedProc;2 1 --You can execute the hidden stored proc directly

Nested Stored Proceudre Error

Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
AnsariIs procedure A returning some resultset? If so, you have to process that,
and move on to the next recordset, using the Recordset.NextRecordset method,
to get the error. Also make sure you have SET NOCOUNT ON at the beginning of
your stored procedure code.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
Ansari|||MM
There are some errors that just terminate the bacth , you are not be able to
captute them ( like Primary Key Violation)
CREATE PROC Parent
@.ord INT
AS
SELECT OrderId FROM Orders WHERE OrderId =@.ord
IF @.@.ERROR >0
RETURN 99 --Failure
ELSE
RETURN 0 --Success
GO
CREATE PROC Child
@.ord INT
AS
DECLARE @.err INT
EXEC @.err=Parent @.ord
IF @.err =99
RAISERROR ('We have a problem',16,1)
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi Gurus;
> I m calling a stroed procedure B inside the stored procedure A from VB. If
> error occured in stored procedure B VB code does detect that error nor it
> raise any error why? So I m unable to do error handling if Procedure B
> failed.
> Any suggestion to solve this problem
> Ansari
>
>sql

Nested Stored Proceudre Error

Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
AnsariIs procedure A returning some resultset? If so, you have to process that,
and move on to the next recordset, using the Recordset.NextRecordset method,
to get the error. Also make sure you have SET NOCOUNT ON at the beginning of
your stored procedure code.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
Ansari|||MM
There are some errors that just terminate the bacth , you are not be able to
captute them ( like Primary Key Violation)
CREATE PROC Parent
@.ord INT
AS
SELECT OrderId FROM Orders WHERE OrderId =@.ord
IF @.@.ERROR >0
RETURN 99 --Failure
ELSE
RETURN 0 --Success
GO
CREATE PROC Child
@.ord INT
AS
DECLARE @.err INT
EXEC @.err=Parent @.ord
IF @.err =99
RAISERROR ('We have a problem',16,1)
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi Gurus;
> I m calling a stroed procedure B inside the stored procedure A from VB. If
> error occured in stored procedure B VB code does detect that error nor it
> raise any error why? So I m unable to do error handling if Procedure B
> failed.
> Any suggestion to solve this problem
> Ansari
>
>

Nested Stored Proceudre Error

Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
Ansari
Is procedure A returning some resultset? If so, you have to process that,
and move on to the next recordset, using the Recordset.NextRecordset method,
to get the error. Also make sure you have SET NOCOUNT ON at the beginning of
your stored procedure code.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
Ansari
|||MM
There are some errors that just terminate the bacth , you are not be able to
captute them ( like Primary Key Violation)
CREATE PROC Parent
@.ord INT
AS
SELECT OrderId FROM Orders WHERE OrderId =@.ord
IF @.@.ERROR >0
RETURN 99 --Failure
ELSE
RETURN 0 --Success
GO
CREATE PROC Child
@.ord INT
AS
DECLARE @.err INT
EXEC @.err=Parent @.ord
IF @.err =99
RAISERROR ('We have a problem',16,1)
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi Gurus;
> I m calling a stroed procedure B inside the stored procedure A from VB. If
> error occured in stored procedure B VB code does detect that error nor it
> raise any error why? So I m unable to do error handling if Procedure B
> failed.
> Any suggestion to solve this problem
> Ansari
>
>

Nested Stored Procedure?

I have a stored procedure that returns a list of userIds that are available to the logged in user. I need to be able to use this list of userIds in another stored procedure whose purpose is to simply query a table for all results containing any of those userIds.

So if my first Stored Procedure returns this:
2
3
5
6

I need my select statement to do something like this:

select UserId, Column1, Column2
from Table1
where UserId = 2 or UserId = 3 or UserId = 5 or UserId = 6

I'm very new to stored procedures, can anyone help me with the syntax for doing this. I'm being pressured to get this done very quickly.

Thanks for any help!Huh? User ids like SQL Server User ids, or something application specific?

-PatP|||Eric1776,

There are 2 options to choose from:

Option 1 is to use a subselect, your select statement will look like this.

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM UserID_Table)

Option 2 is to create a function instead of a stored procedure to return the valid User ID's, your script would look like this.

CREATE FUNCTION dbo.func_Return_UserID ()
RETURNS TABLE AS
RETURN SELECT UserID FROM UserID_Table
GO

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM dbo.func_Return_UserID ())

Regards,
K3n|||Eric1776,

There are 2 options to choose from:

Option 1 is to use a subselect, your select statement will look like this.

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM UserID_Table)

Option 2 is to create a function instead of a stored procedure to return the valid User ID's, your script would look like this.

CREATE FUNCTION dbo.func_Return_UserID ()
RETURNS TABLE AS
RETURN SELECT UserID FROM UserID_Table
GO

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM dbo.func_Return_UserID ())

Regards,
K3n
Is it possible to use Option 1 with a stored procedure instead of a select statement?|||CREATE TABLE #tmp_user_list ( UserID INT)

INSERT INTO #tmp_user_list(UserID)
EXEC proc_name

SELECT * FROM #tmp_user_list -- Test only

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM #tmp_user_list)

DROP TABLE #tmp_user_list

The above is how I solved this type of problem in SQL 7.0

Tim S|||CREATE TABLE #tmp_user_list ( UserID INT)

INSERT INTO #tmp_user_list(UserID)
EXEC proc_name

SELECT * FROM #tmp_user_list -- Test only

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM #tmp_user_list)

DROP TABLE #tmp_user_list

The above is how I solved this type of problem in SQL 7.0

Tim S

Thanks! I should have thought of that. :) Its working great now.

Nested Stored procedure question!

Hi,
i'm calling proc2, proc3 from proc1, as soon as proc1 initiates proc2 kicks in and after completion of proc2 only it will jump to proc3 and so on..right
Please clarify on this.
or it would be great if you refer to any manual which gives details aabout nested stored procedure execution criteria..
any help is greately appreciated..
thanksI'm not sure that I understand your question, but a given spid (connection to SQL Server) is single threaded by default. Unless you go to considerable lengths to work around it, everything done by that connection will be done in the sequence given.

Does that answer your question, or am I way off?

-PatP|||Thanks for your immediate response Pat,
Here is my question in detailed way...
Let's say proc1 is calling two procs say proc2 and then proc3.. first proc2 executes and then proc3 executes right..i mean it is sequential...
for some reason if proc2 fails, will proc3 continues execution or the whole process of executing proc1 fails?
hope my explanation is ok..
thanks|||It depends on why the procedure fails. Some errors are statement_fatal, meaning that they only stop the current statement, others are batch_fatal, meaning that the whole batch stops immediately. There are a few errors that sit between these two degrees of impact, and they are harder to diagnose.

-PatP

Nested Stored Procedure

Hi, I have two tables:
TABLE 1 Table 2
========== ==========
UID TeamName
TeamID <------> TeamID

and I am trying to obtain the value of TeamName via a nested stored procedure.
However, I am only allowed to obtain the @.UID as input parameter.
I tried writing the following:
Select table2.TeamName
From Table1, Table2
Where (table1.TeamID=table2.TeamID)and (UID = @. UID)
It seems to not work as TeamID was not given.
Thus, my other idea of doing this is to:
1) Execute a sql to first obtain the TeamID value with the @.UID parameter given.
then 2)Execute another sql to obtain the TeamName with the result from the first sql as the input parameter.

However, I have never worked with nested sql, so it would be great if someone can link me a site that they know. Better yet, write me an example code.
Thanks you very much!
You do not need to first obtain the TeamID value in the manner you areproposing. What you have should work OK. I'd rewrite it assuch, but it should function in the same manner:

Select Table2.TeamName
From Table1
Inner Join Table2 ON Table1.TeamID = Table2.TeamID
Where Table1.UID = @. UID

Let us know if you are still experiencing problems.|||let me post the code and see if you can shine some light in it:

Function GetSchoolName(ByVal UIDAsInteger)AsString

Dim mycmdAsNew SqlCommand("GetSchoolName", myconn)

mycmd.CommandType = CommandType.StoredProcedure

mycmd.Parameters.Add("@.UID", SqlDbType.Char, 3)

mycmd.Parameters("@.UID").Value = UID

myconn.Open()

Dim SchoolNameAsString = mycmd.ExecuteScalar

myconn.Close()

Return SchoolName

EndFunction
=============================

ALTER PROCEDUREdbo.GetSchoolName

(

@.UIDchar

)

AS

SELECTCOLLEGE_DIV1A.schoolNameASSchoolName

FROMPOWER_RANKINGS, COLLEGE_DIV1A

WHERE(POWER_RANKINGS.UID = @.UID)and(POWER_RANKINGS.TeamCode = COLLEGE_DIV1A.teamCode)

RETURN

================================

Dim dtUpdateTableAsNew DataTable

dtUpdateTable.Clear()

Dim DcAs DataColumn

Dim intTypeAs System.Type = System.Type.GetType("System.Int32")

'create column

Dc =New DataColumn("UID", intType)

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("Points",GetType(Decimal))

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("Week", intType)

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("SchoolName",GetType(String))

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("OldPoints",GetType(Decimal))

dtUpdateTable.Columns.Add(Dc)

dtUpdateTable.Rows.Add(dtUpdateTable.NewRow())

With dtUpdateTable.Rows(dtUpdateTable.Rows.Count - 1)

.Item("UID") = drPowerRankings.UID

.Item("Points") = drPowerRankings.Points

.Item("OldPoints") = Session("OLD_Points")

.Item("Week") = Session("WeekId")

.Item("SchoolName") = GetSchoolName(drPowerRankings.UID)

EndWith
============================
Problem: The value I got returned from the function, GetSchoolName, is always the first row's first value of the table. Regardless of the UID inserted.
I ran the sql with query analyzer, and the Stored Procedure works fine. But the actual coding doesn't work for some reason. kinda weird...
Thanks again for looking into my code.

|||couple of things.
(1)

mycmd.Parameters("@.UID").Value = UID

should be
mycmd.Parameters("@.UID").Value = "UID" --The value should be inquotes if it is a string.
(2) In your stored proc I'd recommend setting the SIZE for the parameter.

@.UIDchar

would be

@.UIDchar(3)

Other recommendations:
(1) Use SET NOCOUNT option. check out Books on line for more info.
(2) Use OUTPUT Parameter. Since you are only returning one parameter that would be faster with ExecuteScalar. SELECT statement returns a recordset versus OUTPUT parameter returning a single record. Prbly not very noticeable but would be more efficient. check out books on line about using OUTPUT parameter.

sql

Nested stored procedure

I have a report, say for example "Dummy report" which has its dataset to be a
stored procedure SP1. This stored procedure SP1 gets its data from another
stored procedure (SP2) that is nested in it.
When I try to generate "Dummy report" from report designer for the first
time, it fails. What I need to do now is run SP2 individually from SSMS, and
then run the "Dummy report" from designer to see the report output. Every
time I make a change to SP2, I have to do the same thing - run SP2
individually and then generate the report. I am not able to understand why
this is happening. Can anyone help me with this problem? Thanks in advance.
SQL Server 2005 SP1/Windows Server 2003On Mar 9, 12:23 pm, KMP <K...@.discussions.microsoft.com> wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003
If I'm understanding you correctly, it has something to do w/why a
stored procedure cannot be initially used when creating a report.
Basically, the report needs to know the dataset format and what data
to expect, etc up front. Hopefully in a future version of SSRS, this
will be corrected.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Have you checked, that your sp1 is working from SSMS ? Just check whether
you have given hints in the query or recompile etc... and check whether it
returns single set of data.
Amarnath, MCTS
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||If your first stored procedure is creating a temp table that is then filled
by the second stored procedure then you need to add a line to your stored
procedure.
SET FMTONLY OFF
The issue is that when RS is trying to create the field list it calls the
stored procedure with the set fmtonly on (i.e. it doesn't really execute
it). This means it does not create the temp table either (although I have
found I only have this issue with nested stored procedures).
Anyway, add the statement at the top of you calling stored procedure.
The only other issue, sometimes you have to click on the refresh fields
button, it is one of the buttons to the right of the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21160AC2-8078-4CA8-A70D-494ACB4F3168@.microsoft.com...
>I have a report, say for example "Dummy report" which has its dataset to be
>a
> stored procedure SP1. This stored procedure SP1 gets its data from
> another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS,
> and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in
> advance.
> SQL Server 2005 SP1/Windows Server 2003|||SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either. Thanks for all the
ideas. Still need to find a solution though...
"Amarnath" wrote:
> Have you checked, that your sp1 is working from SSMS ? Just check whether
> you have given hints in the query or recompile etc... and check whether it
> returns single set of data.
> Amarnath, MCTS
>
> "KMP" wrote:
> > I have a report, say for example "Dummy report" which has its dataset to be a
> > stored procedure SP1. This stored procedure SP1 gets its data from another
> > stored procedure (SP2) that is nested in it.
> >
> > When I try to generate "Dummy report" from report designer for the first
> > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > then run the "Dummy report" from designer to see the report output. Every
> > time I make a change to SP2, I have to do the same thing - run SP2
> > individually and then generate the report. I am not able to understand why
> > this is happening. Can anyone help me with this problem? Thanks in advance.
> >
> > SQL Server 2005 SP1/Windows Server 2003|||Please help!!!
"KMP" wrote:
> SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either. Thanks for all the
> ideas. Still need to find a solution though...
> "Amarnath" wrote:
> > Have you checked, that your sp1 is working from SSMS ? Just check whether
> > you have given hints in the query or recompile etc... and check whether it
> > returns single set of data.
> >
> > Amarnath, MCTS
> >
> >
> > "KMP" wrote:
> >
> > > I have a report, say for example "Dummy report" which has its dataset to be a
> > > stored procedure SP1. This stored procedure SP1 gets its data from another
> > > stored procedure (SP2) that is nested in it.
> > >
> > > When I try to generate "Dummy report" from report designer for the first
> > > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > > then run the "Dummy report" from designer to see the report output. Every
> > > time I make a change to SP2, I have to do the same thing - run SP2
> > > individually and then generate the report. I am not able to understand why
> > > this is happening. Can anyone help me with this problem? Thanks in advance.
> > >
> > > SQL Server 2005 SP1/Windows Server 2003|||Any help will be greatly appreciated. Thank you!
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||I thought I had answered this one.
Anyway, is the issue that fields are not showing up in RS?
If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
issue is that you are not seeing the new fields then try the below:
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP is
defined by calling the SP with SET FMTONLY ON because Temp tables don't get
created if you select from the temp table the metadata from the rowset can't
be returned. This can be worked around by turning FMTONLY OFF in the SP."
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> Any help will be greatly appreciated. Thank you!
> "KMP" wrote:
>> I have a report, say for example "Dummy report" which has its dataset to
>> be a
>> stored procedure SP1. This stored procedure SP1 gets its data from
>> another
>> stored procedure (SP2) that is nested in it.
>> When I try to generate "Dummy report" from report designer for the first
>> time, it fails. What I need to do now is run SP2 individually from SSMS,
>> and
>> then run the "Dummy report" from designer to see the report output. Every
>> time I make a change to SP2, I have to do the same thing - run SP2
>> individually and then generate the report. I am not able to understand
>> why
>> this is happening. Can anyone help me with this problem? Thanks in
>> advance.
>> SQL Server 2005 SP1/Windows Server 2003|||Sorry if I am posting this over and over again. Seems to be more a SQL Server
issue, not RS. But below is my previous post and what happened when I tried
the different ideas:
"SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either."
"Bruce L-C [MVP]" wrote:
> I thought I had answered this one.
> Anyway, is the issue that fields are not showing up in RS?
> If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
> issue is that you are not seeing the new fields then try the below:
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...)
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP is
> defined by calling the SP with SET FMTONLY ON because Temp tables don't get
> created if you select from the temp table the metadata from the rowset can't
> be returned. This can be worked around by turning FMTONLY OFF in the SP."
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> > Any help will be greatly appreciated. Thank you!
> >
> > "KMP" wrote:
> >
> >> I have a report, say for example "Dummy report" which has its dataset to
> >> be a
> >> stored procedure SP1. This stored procedure SP1 gets its data from
> >> another
> >> stored procedure (SP2) that is nested in it.
> >>
> >> When I try to generate "Dummy report" from report designer for the first
> >> time, it fails. What I need to do now is run SP2 individually from SSMS,
> >> and
> >> then run the "Dummy report" from designer to see the report output. Every
> >> time I make a change to SP2, I have to do the same thing - run SP2
> >> individually and then generate the report. I am not able to understand
> >> why
> >> this is happening. Can anyone help me with this problem? Thanks in
> >> advance.
> >>
> >> SQL Server 2005 SP1/Windows Server 2003
>
>|||Ahh, this is not a Reporting Services issue. If you cannot run this from
outside of Reporting Services then all the advice I gave does not help you.
I suggest posting on the SQL Server newsgroups. They will be able to help
you,. First make sure everything works from outside of RS before trying to
create a report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:07013913-3721-4A9C-9B54-C6B2259ED23D@.microsoft.com...
> Sorry if I am posting this over and over again. Seems to be more a SQL
> Server
> issue, not RS. But below is my previous post and what happened when I
> tried
> the different ideas:
> "SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either."
>
> "Bruce L-C [MVP]" wrote:
>> I thought I had answered this one.
>> Anyway, is the issue that fields are not showing up in RS?
>> If SP1 calls SP2 then when you run SP1 from RS both should execute. If
>> the
>> issue is that you are not seeing the new fields then try the below:
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...)
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP
>> is
>> defined by calling the SP with SET FMTONLY ON because Temp tables don't
>> get
>> created if you select from the temp table the metadata from the rowset
>> can't
>> be returned. This can be worked around by turning FMTONLY OFF in the SP."
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
>> > Any help will be greatly appreciated. Thank you!
>> >
>> > "KMP" wrote:
>> >
>> >> I have a report, say for example "Dummy report" which has its dataset
>> >> to
>> >> be a
>> >> stored procedure SP1. This stored procedure SP1 gets its data from
>> >> another
>> >> stored procedure (SP2) that is nested in it.
>> >>
>> >> When I try to generate "Dummy report" from report designer for the
>> >> first
>> >> time, it fails. What I need to do now is run SP2 individually from
>> >> SSMS,
>> >> and
>> >> then run the "Dummy report" from designer to see the report output.
>> >> Every
>> >> time I make a change to SP2, I have to do the same thing - run SP2
>> >> individually and then generate the report. I am not able to understand
>> >> why
>> >> this is happening. Can anyone help me with this problem? Thanks in
>> >> advance.
>> >>
>> >> SQL Server 2005 SP1/Windows Server 2003
>>

Nested Stored Proc

Pleaseet me know the Advantages and Divantages of nested Stored porc in
sql server 2000.Mustafa
You have to understand why you need to use nested stored procedures
In my opinion one of "diventages" is a maintenance, that if one of the
DML is failed to rollback whole batch , so obviously it depends in your
business reqirements
Also read up this article
http://www.sql-server-performance.c..._procedures.asp
"Mustafa" <Mustafa@.discussions.microsoft.com> wrote in message
news:834A3DFF-CBA5-45B9-B798-DE41EC54E871@.microsoft.com...
> Pleaseet me know the Advantages and Divantages of nested Stored porc in
> sql server 2000.|||if by "nesting" you mean calling one SP from another, this is busy subject..
.
See Code Complete, or Code Complete II, by Steve McConnell, for basic ideas.
1. In general, Nesting allows you to better organize, resuse and maintain
the code you write.
2. Portions of code that are used by many SPs within adatabase system can
be written in a separate SP, and maintained in only one spot insteadof havin
g
to be copied and pasted in multiple places.
3. When you write a long SP that executes a different portion of it's code
each time it runs, depending on the values of parameters, the query optimize
r
and compiler will only optimize it for the portion of the code that it ran
the first time it ran - when it was compiled.. Subsequent runs, (unless it i
s
recompiled each time) it may not be running the best possible query plan.
4. There will many more SPs within you system to maintain if you split them
up into Main SPs and Subroutines.
5. Error handling in"Called" Subroutine SPs is a bit trickier. If you're
not familiar, make sure you read up on this topic...
etc.
"Mustafa" wrote:

> Pleaseet me know the Advantages and Divantages of nested Stored porc in
> sql server 2000.

Nested Rollback/Commits Question

Hello all
I have a question regarding Rollbacks and Committs when
you are nesting stored procedures. My problem is that i
continue to get the following error.
Transaction count after EXECUTE indicates that a COMMIT
or ROLLBACK TRANSACTION statement is missing. Previous
count = 0, current count = 1
I am not sure what i am doing wrong. Any help would be
appriciated. Here is a sample SP that i may be nesting
in a larger SP.
---
CREATE PROCEDURE usp_Update_Client_Biography
@.Client_id as integer,
@.Biography as varchar(1000)
AS
SET NOCOUNT ON
DECLARE @.intErrorCode integer
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode = 0
BEGIN TRANSACTION
IF @.intErrorCode = 0
BEGIN
UPDATE Client_Biography
SET biography = @.Biography
WHERE biz_association_id = @.Client_id
SELECT @.intErrorCode = @.@.Error
END
IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @.intErrorCode
THANKS
BAPerhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||When you rollback a transaction it will rollback all the transactions that
it is nested in. So you won't have any open transaction by the time you get
to your errorhandler. You can catch this by changing your code (at the end)
to:
IF @.@.TRANCOUNT > 0
IF @.intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
You might want to have a look at the SET XACT_ABORT option. If you have it
on any error will cause the transaction to roll back and prevent any further
code in the batch to execute. The disadvantage of this of course is that you
can't do any custom error handling, the advantage is that you don't have to
do any custom error handling.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||This is a multi-part message in MIME format.
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
declare
@.trancount int
set @.trancount =3D @.@.trancount
if @.trancount =3D 0
begin tran MyTran
else
save tran MyTran
-- do the work, check errors
if @.@.ERROR =3D 0
begin
if @.trancount =3D 0
commit tran
end
else
rollback tran MyTran
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"BA Baracus" <JCoxEUP@.hotmail.com> wrote in message =news:06ea01c3810f$02b36670$a001280a@.phx.gbl...
Thanks for the help!
Is there any way you can show me some T-SQL that will show me your suggestion? If i understand you correctly, i should check the transaction count before beginning the transaction? This has been an ongoing issue with me and i am very appriciative of your help!
thanks BA
>--Original Message--
>Perhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
>trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=3Ddjq&as=20
ugroup=3Dmicrosoft.public.sqlserver
>
>"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
>> Hello all
>> I have a question regarding Rollbacks and Committs when
>> you are nesting stored procedures. My problem is that i
>> continue to get the following error.
>> Transaction count after EXECUTE indicates that a COMMIT
>> or ROLLBACK TRANSACTION statement is missing. Previous
>> count =3D 0, current count =3D 1
>> I am not sure what i am doing wrong. Any help would be
>> appriciated. Here is a sample SP that i may be nesting
>> in a larger SP.
>> ---
>> CREATE PROCEDURE usp_Update_Client_Biography
>> @.Client_id as integer,
>> @.Biography as varchar(1000)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.intErrorCode integer
>> SELECT @.intErrorCode =3D @.@.Error
>> IF @.intErrorCode =3D 0
>> BEGIN TRANSACTION
>> IF @.intErrorCode =3D 0
>> BEGIN
>> UPDATE Client_Biography
>> SET biography =3D @.Biography
>> WHERE biz_association_id =3D @.Client_id
>> SELECT @.intErrorCode =3D @.@.Error
>> END
>>
>> IF @.intErrorCode =3D 0 AND @.@.TRANCOUNT > 0
>> COMMIT TRANSACTION
>> ELSE
>> ROLLBACK TRANSACTION
>>
>> RETURN @.intErrorCode
>>
>> THANKS
>> BA
>
>.
>
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
declare
@.trancount int
set @.trancount =3D =@.@.trancount
if @.trancount =3D =0
begin tran MyTran
else
save tran MyTran
-- do the work, check =errors
if @.@.ERROR =3D 0
begin
if =@.trancount =3D 0
= commit tran
end
else
rollback =tran MyTran
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"BA Baracus" wrote in =message news:06ea01c3810f$02=b36670$a001280a@.phx.gbl...Thanks for the help!Is there any way you can show me some T-SQL that =will show me your suggestion? If i understand you correctly, i =should check the transaction count before beginning the transaction? =This has been an ongoing issue with me and i am very appriciative of your help!thanks BA >--Original Message-->Perhaps @.@.TRANCOUNT is 1 when you enter the =procedure? If you exit the proc with some other>trancount then when =entering, you get such error. And remember that ROLLBACK exits the transaction>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.>>-- >Tibor Karaszi, SQL Server MVP>Archive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver>>>"B.A. =Baracus" =wrote in message news:065201c38109$f1=f0e150$a001280a@.phx.gbl...> Hello all>> I have a question regarding Rollbacks =and Committs when> you are nesting stored procedures. My =problem is that i> continue to get the following error.>> Transaction count after EXECUTE indicates =that a COMMIT> or ROLLBACK TRANSACTION statement is missing. Previous> count =3D 0, current count =3D =1>> I am not sure what i am doing wrong. Any help would be> appriciated. Here is a sample SP that i may be nesting> =in a larger SP.>> --->>= CREATE PROCEDURE usp_Update_Client_Biography> @.Client_id as integer,> @.Biography as varchar(1000)>> =AS>> SET NOCOUNT ON>> =DECLARE @.intErrorCode integer> SELECT @.intErrorCode =3D @.@.Error>> IF @.intErrorCode =3D 0> BEGIN =TRANSACTION>> IF @.intErrorCode =3D 0> BEGIN> UPDATE Client_Biography> SET biography =3D @.Biography> WHERE biz_association_id =3D @.Client_id>> SELECT @.intErrorCode =3D =@.@.Error> END>>> IF @.intErrorCode =3D 0 AND =@.@.TRANCOUNT > 0> COMMIT TRANSACTION> ELSE> =ROLLBACK TRANSACTION>>> RETURN @.intErrorCode>>> THANKS> BA>>>.>

---

--=_NextPart_000_009C_01C380EE.56F75F50--|||Hi.
I'm currently reading SQL Server 2000 Stored Proc 7 XML Programing,
2nd ed. By Dejan Sunderic - ISBN 0-07-222896-2
So far it has been an excellent book IMHO though I'm only on chapter 9
I have just read the chapter dealing with errorcodes & transactions,
using the information in the book I've got a template that I've
started to use for all my stored procs which might be of some use to
you.
What I would like is if any Guru out there can comment on the template
and let me know before I go to far with it if its as good as what the
book seems to be saying it is.
Thanks & HTH.
Al
/*
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Function : usp__Template
** Language : T-SQL
** Description : This is a template for all stored procs
** :
** Input : None
** Returns : None
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Ver Date Description of modification
**
---
** 1.0 date here text here
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
*/
CREATE PROCEDURE [dbo].[usp__Template]
-- @.intInValuesHere INT ,
-- @.intInValuesMoreHere INT ,
-- @.intOutputParam INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.intErrorCode INT , -- to store the errorcodes
@.intTranCountOnEntry INT , -- Store transaction count
@.intAnotherVariable INT
SELECT @.intErrorCode = @.@.Error ,
@.intTranCountOnEntry = @.@.TranCount ,
@.intAnotherVariable = 0
if @.@.TranCount = 0
BEGIN TRANSACTION
-- T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
-- more T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
IF @.@.TranCount > @.intTranCountOnEntry
COMMIT TRANSACTION
RETURN 0
/****************************************
******* ERROR HANDLER CODE BELOW ********
*****************************************/
ERROR_HANDLER:
IF @.@.TranCount > @.intTranCountOnEntry
ROLLBACK TRANSACTION
RAISERROR ('Some Error Message here...',16,1)
RETURN @.intErrorCode
GO
On Mon, 22 Sep 2003 06:03:41 -0700, "B.A. Baracus"
<JCoxEUP@.hotmail.com> wrote:
>Hello all
>I have a question regarding Rollbacks and Committs when
>you are nesting stored procedures. My problem is that i
>continue to get the following error.
>Transaction count after EXECUTE indicates that a COMMIT
>or ROLLBACK TRANSACTION statement is missing. Previous
>count = 0, current count = 1
>I am not sure what i am doing wrong. Any help would be
>appriciated. Here is a sample SP that i may be nesting
>in a larger SP.
>---
>CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
>AS
>SET NOCOUNT ON
>DECLARE @.intErrorCode integer
>SELECT @.intErrorCode = @.@.Error
>IF @.intErrorCode = 0
> BEGIN TRANSACTION
>IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
>IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
>ELSE
> ROLLBACK TRANSACTION
>
>RETURN @.intErrorCode
>
>THANKS
>BA

Nested Query Problem

I am writing a stored procedure that has to insert several rows from one table to another. The problem is that the table into which the rows will be inserted, has more columns than the table that the rows come from. When the stored procedure is called, the extra columns in each new row is supposed to be populated by the stored procedure's arguments. Example:

TableA:
Columns: ID, Group, Name, Email, NewletterSubscriber

TableB:
Columns: ID, Name, Email

The arguments provided are Group, and NewsletterSubscriber.

I need to insert into Table A all records from Table B where ID > 1000 and I need to insert the Group and NewsLetterSubscriber arguments at the same time because these columns do not allow nulls.

I think it might be something like

insert into TableA (ID, Group, Name, Email, NewletterSubscriber)
values (
select * from TableB where ID > 1000)

But how do I insert the stored procedure arguments into the correct columns of the rows?

Your insert statement should be something like

insert into TableA(group, newsletterSubscriber)
values (@.arg_group, @.arg_newsletterSubscriber)

|||

As per your example:

You can do the following :

insert TableA (ID, Group, Name, Email, NewletterSubscriber)

select ID,@.Group,Name,Email,@.NewsletterSubscriber from TableB where ID > 1000

Here @.Group and @.NewsletterSubscriber are a arguments from a Stored procedure.

Thanks

Naras.

|||

I think the procedure definition you want is:

Code Snippet

CREATE PROCEDURE TransRecords_AtoB

@.DefGroup AS int,

@.DefNewsLetterSub AS bit

AS

BEGIN

-- Do you need to clear existing records if so uncomment statement below

-- DELETE FROM TableA

-- WHERE [ID] IN (

-- SELECT ID

-- FROM TableB

-- WHERE (ID > 1000)

-- )

-- Insert the required rows

INSERT INTO TableA ([ID], [Group], [Name],

[Email], [NewsLetterSubscriber])

SELECT [ID], @.DefGroup, [Name], [Email], @.DefNewsLetterSub

FROM TableB

WHERE ([ID] > 1000)

END

If you need to remove records already existing for IDs in TableB then use the commented out delete. Change the types of the arguments to match your fields.

I would recomment that you consider changing some of your column names. It is a bad idea to use identifiers that are reserved words (or might become ones). Name and Group fall into that camp and ID is also suspect. These have to be delimited as shown (and this can cause problem with autogenerated SQL in some tools). Use RecID, RecName, RecGroup etc. or something more descriptive.

nested insert?

Can this be done? a nested instert in a trigger or stored proc?Explain|||Originally posted by ispaleny
Explain
I need to insert several checkbox responses into a table, but the only thing that I can think of doing is using a trigger that will will do an insert ofr the first record and then trigger another insert for the next record. But I cannot find anyone on the web that seems to know if that can be done... please let me know or give a little example of how this coul dbe done. thanks

greg|||??
You have several pieces of data in the front end that you want to get into a database - one piece of data per record?

If you insert the first and use a trigger for the rest how is the database going to know what data to insert - it only knows about the datat for the record inserted.

Easiest is to call the insert SP several times from the front end - once for each record - maybe in a transaction.

You could also send a csv lst to the sp in one call and have the SP then do all the inserts.

Nested execution of strored procedures

How i can capture one nested stored procedure's ouput parameter and using the out param in calling stored procedure...Help me..declare @.i int
exec sptest @.i output

create procedure sptest
@.i int output
as
...

For a dynamic sp call see
www.nigelrivett.com
setting variables from dynamic sql

nested cursors in stored procedure

I have a stored procedure that attempts to process some stored data files
located in one or more directories. All the procedure tries to do is walk
through each directory and process (in this case, bulk insert) each file.
I've coded this to use 2 cursors, one to walk the list of directories, the
other to walk the list of files matching some specification. The problem is,
even though the number of files is identified correctly, the same filename
ends up in the cursor value for the file name each time through the loop.
It's as if FETCH NEXT has no affect. The value of @.@.CURSOR_ROWS is also
correct when the second (inner) cursor is opened. The result is the loop
executing the correct number of times, but always on the same filename. It's
as if the cursor is "stuck" on the first value fetched into it.
I realize that using cursors may not be optimal as far as performance, but I
am working with generally a small number of directories (1 - 4) with maybe a
handful of files in each to be processed.
I placed the guts of this procedure into query analyzer as a script and
removed some of the processing and it appears to work perfectly (it cycles
through all the filenames in the directory and then exits). As far as I can
tell the logic matches the examples provided in BOL.
The directory and file specification is retrieved from a table which already
exists in the database. Any advice as to why this isn't working would be
greatly appreciated.
Thanks.
-Gary
IF OBJECT_ID('idw_import_mbs_files') IS NOT NULL
BEGIN
PRINT 'idw_import_mbs_files...'
DROP PROCEDURE idw_import_mbs_files
END
PRINT 'Creating procedure idw_import_mbs_files...'
GO
CREATE PROCEDURE idw_import_mbs_files
@.arg_wideChar bit = 1, -- set to 0 to process ASCII files
@.arg_rows_per_batch int = 10000,
@.arg_max_errors int = 10,
@.debug_output bit = 1
AS
BEGIN
CREATE TABLE #tmpMBSExtractFiles (MBSFileName NVARCHAR(200))
CREATE TABLE #tmpMBSDirectories(mbsDirname NVARCHAR(400))
CREATE TABLE #tmpMBSExtractFileDetails
(
alternate_name CHAR(20),
[size] CHAR(20),
creation_date CHAR(20),
creation_time CHAR(20),
last_written_date CHAR(20),
last_written_time CHAR(20),
last_accessed_date CHAR(20),
last_accessed_time CHAR(20),
attributes CHAR(20)
)
CREATE TABLE #tmpMBSInsert
(
Identifier NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
AssetId INT NULL ,
Billable BIT NULL ,
ContentProvider NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
Genre NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
FF INT NULL ,
Pause INT NULL ,
[Rewind] INT NULL ,
PlayTime INT NULL ,
Price real NULL ,
PurchaseTime NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
RentalTime int NULL ,
ShortTitle NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
Title NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
BillingID INT NULL ,
HomeId INT NULL ,
PurchaseId INT NULL ,
SmartCardId INT NULL ,
EventId NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
PackageId INT NULL ,
Provider NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
)
CREATE TABLE #tmpProcessedFile([FileName] NVARCHAR(255), FileSize INT)
DECLARE @.currentDir NVARCHAR(255)
DECLARE @.currentFile NVARCHAR(255)
DECLARE @.tmpFile NVARCHAR(255)
DECLARE @.SQL NVARCHAR(2000)
DECLARE @.Path NVARCHAR(400)
DECLARE @.FileSpec NVARCHAR(400)
DECLARE @.filecount INT
DECLARE @.filesProcessed INT
DECLARE @.rowsProcessed INT
DECLARE @.status INT
DECLARE @.SQLInsert NVARCHAR(4000)
DECLARE @.useWideChar NVARCHAR(16)
DECLARE @.currentFileSize INT
DECLARE @.fileDateTime NVARCHAR(20)
DECLARE @.fileCreateTime NVARCHAR(8)
DECLARE @.lastAccessed NVARCHAR(20)
DECLARE @.lastAccessedTime NVARCHAR(8)
DECLARE @.processedFileName NVARCHAR(255)
DECLARE @.processedFileSize INT
-- initialize
--
SET @.filesProcessed = 0
SET @.status = 0
IF @.arg_wideChar <> 0
SET @.useWideChar = 'widechar'
ELSE
SET @.useWideChar = 'char'
-- get the export file path
--
SELECT @.Path = StrValue FROM WHA_Settings WHERE SettingName =
'MBSExportDirectory'
-- The directory may be a semi-colon separated list.
--
INSERT INTO #tmpMBSDirectories select * from dbo.idw_str_split(@.Path, ';')
-- get the filespec
--
SELECT @.FileSpec = StrValue FROM WHA_Settings WHERE SettingName =
'MBSExportFilespec'
PRINT 'Importing MBSExtract files from ' + @.currentDir + ' matching
filespec ' + @.FileSpec
-- replace * wildcard to use %
--
SET @.FileSpec = REPLACE(@.FileSpec,'*','%')
DECLARE fileDirCursor CURSOR STATIC FORWARD_ONLY
FOR SELECT * FROM #tmpMBSDirectories
OPEN fileDirCursor
FETCH NEXT FROM fileDirCursor into @.currentDir
WHILE @.@.FETCH_STATUS = 0
BEGIN
OPEN fileNameCursor
FETCH NEXT FROM fileNameCursor INTO @.currentFile
SELECT @.@.CURSOR_ROWS as CursorRows
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.rowsProcessed = 0
PRINT 'Processing file ' + @.currentFile
-- use undocumented extended stored procedure to get the file details
--
INSERT #tmpMBSExtractFileDetails EXEC master..xp_getfiledetails
@.currentFile
SELECT @.currentFileSize = size FROM #tmpMBSExtractFileDetails
SELECT @.fileDateTime = creation_date FROM #tmpMBSExtractFileDetails
SELECT @.fileCreateTime = creation_time FROM #tmpMBSExtractFileDetails
IF @.debug_output <> 0
BEGIN
PRINT 'FileSize: ' + convert(varchar(12), @.currentFileSize)
PRINT 'CreateDate: ' + @.fileDateTime
PRINT 'LastAccessed ' + @.lastAccessed
END
.
.
.
MORE PROCESSING HERE (REMOVED)
.
.
.
next_file:
FETCH NEXT FROM fileNameCursor INTO @.currentFile
IF @.debug_output <> 0
PRINT 'CurrentFile after FETCH NEXT: ' + @.currentFile
END
CLOSE fileNameCursor
DEALLOCATE fileNameCursor
next_dir:
FETCH NEXT FROM fileDirCursor INTO @.currentDir
END
PRINT convert(varchar(12), @.filesProcessed) + ' files processed'
PRINT '***** Process completed ' + CONVERT(VARCHAR(32),GETDATE()) + '
*****'
CLOSE fileDirCursor
DEALLOCATE fileDirCursor
RETURN @.status
END
GOOn Wed, 9 Nov 2005 15:34:43 -0500, Gary wrote:

>I've coded this to use 2 cursors, one to walk the list of directories, the
>other to walk the list of files matching some specification. The problem is
,
>even though the number of files is identified correctly, the same filename
>ends up in the cursor value for the file name each time through the loop.
>It's as if FETCH NEXT has no affect. The value of @.@.CURSOR_ROWS is also
>correct when the second (inner) cursor is opened. The result is the loop
>executing the correct number of times, but always on the same filename. It'
s
>as if the cursor is "stuck" on the first value fetched into it.
Hi Gary,
Your code didn't include the DECLARE CURSOR statement for the inner
cursor (fileNameCursor). If the SELECT statement for that cursor uses a
variable that's read from the outer cursor (fileDirCursor), then you
MUST re-define the inner cursor after each FETCH. The variables used in
a SELECT statement are replaced with their values when you DECLARE the
cursor; they are not re-evaluated when you OPEN the cursor.

>I realize that using cursors may not be optimal as far as performance, but
I
>am working with generally a small number of directories (1 - 4) with maybe
a
>handful of files in each to be processed.
Since file handling is done by the OS at the individual file level,
you'd have to use a cursor anyway. You might be able to rewrite this
with one cursor that joins the tables used as input, but I expect the
speed of accessing the physical files by the OS to be the limiting
factor in a procedure like this, not the speed of the cursor.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,
I actually do have it in my original source file but when trying to simplify
the code for the post I ended up deleting it.
Just before the inner while loop I have:
DECLARE fileNameCursor CURSOR FORWARD_ONLY STATIC READ_ONLY
FOR SELECT * FROM #tmpMBSExtractFiles
OPEN fileNameCursor
FETCH NEXT FROM fileNameCursor INTO @.currentFile
WHILE @.@.FETCH_STATUS = 0
.
..
.
The fact that it works in a script but not in the stored procedure says to
me that it should work, but I must be doing something to break it...I tried
commented out all the processing in the inner while loop, and the behavior
is consistent...it finds the correct number of files (4), but then prints
out the same name four times, then exists.
Are there any limitations for using cursors in a stored procedure? I'm not
aware of any.
Thanks again,
-Gary
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cjp4n110l26oha0eigft8nh2oss539rbn9@.
4ax.com...
> On Wed, 9 Nov 2005 15:34:43 -0500, Gary wrote:
>
> Hi Gary,
> Your code didn't include the DECLARE CURSOR statement for the inner
> cursor (fileNameCursor). If the SELECT statement for that cursor uses a
> variable that's read from the outer cursor (fileDirCursor), then you
> MUST re-define the inner cursor after each FETCH. The variables used in
> a SELECT statement are replaced with their values when you DECLARE the
> cursor; they are not re-evaluated when you OPEN the cursor.
>
> Since file handling is done by the OS at the individual file level,
> you'd have to use a cursor anyway. You might be able to rewrite this
> with one cursor that joins the tables used as input, but I expect the
> speed of accessing the physical files by the OS to be the limiting
> factor in a procedure like this, not the speed of the cursor.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 9 Nov 2005 16:45:54 -0500, Gary wrote:

>Thanks Hugo,
>I actually do have it in my original source file but when trying to simplif
y
>the code for the post I ended up deleting it.
>Just before the inner while loop I have:
> DECLARE fileNameCursor CURSOR FORWARD_ONLY STATIC READ_ONLY
> FOR SELECT * FROM #tmpMBSExtractFiles
> OPEN fileNameCursor
> FETCH NEXT FROM fileNameCursor INTO @.currentFile
> WHILE @.@.FETCH_STATUS = 0
Hi Gary,
LOL! Now I knopw how the cursor is defined, yet it doesn't bring me any
further. The script you posted does include a CREATE TABLE statement for
#tmpMBSExtractFiles, but nothing else. I'm sure there must be something,
otherwise you'd get no results at all.

>The fact that it works in a script but not in the stored procedure says to
>me that it should work, but I must be doing something to break it...I tried
>commented out all the processing in the inner while loop, and the behavior
>is consistent...it finds the correct number of files (4), but then prints
>out the same name four times, then exists.
What _exactly_ did you do to verify that it works in a script but not in
a stored procedure? Did you copy all the code and paste from the sp into
a script, then execute that without further modifications? Did you take
a working script, put a "CREATE PROCEDURE xxx AS" in front of it and run
it to create the procedure? Or were there other changes - changes that
might seem irrelevant to you, but that might have caused the stored
procedure to break?

>Are there any limitations for using cursors in a stored procedure? I'm not
>aware of any.
Neither am I.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> What _exactly_ did you do to verify that it works in a script but not in
> a stored procedure? Did you copy all the code and paste from the sp into
> a script, then execute that without further modifications? Did you take
> a working script, put a "CREATE PROCEDURE xxx AS" in front of it and run
> it to create the procedure? Or were there other changes - changes that
> might seem irrelevant to you, but that might have caused the stored
> procedure to break?
YES! One potential would be using a GO command somewhere in the standalone
script, and not realizing that when you put a CREATE PROCEDURE at the
beginning, that the procedure will end at the first GO (assuming the rest of
the code passes the syntax checker).
This can be avoided if you train yourself to use the following as a
template:
CREATE PROCEDURE dbo.name
AS
BEGIN
SET NOCOUNT ON;
-- code here
END
GO
Now if you mistakenly enter a batch separator or some other code that is not
valid inside a procedure, the create procedure will fail, because the BEGIN
and END will not match.|||I found the problem. The loop processing was wacking one of my temp tables
to include all the same values. Cursors work perfectly.
"Gary" <spam@.mail.com> wrote in message
news:evcyzbX5FHA.3312@.TK2MSFTNGP15.phx.gbl...
> Thanks Hugo,
> I actually do have it in my original source file but when trying to
> simplify the code for the post I ended up deleting it.
> Just before the inner while loop I have:
> DECLARE fileNameCursor CURSOR FORWARD_ONLY STATIC READ_ONLY
> FOR SELECT * FROM #tmpMBSExtractFiles
> OPEN fileNameCursor
> FETCH NEXT FROM fileNameCursor INTO @.currentFile
> WHILE @.@.FETCH_STATUS = 0
> .
> ..
> .
> The fact that it works in a script but not in the stored procedure says to
> me that it should work, but I must be doing something to break it...I
> tried commented out all the processing in the inner while loop, and the
> behavior is consistent...it finds the correct number of files (4), but
> then prints out the same name four times, then exists.
> Are there any limitations for using cursors in a stored procedure? I'm not
> aware of any.
> Thanks again,
> -Gary
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:cjp4n110l26oha0eigft8nh2oss539rbn9@.
4ax.com...
>

Needs Help With Modifying Stored Procedure

I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID.

I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure?

Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @.typeid int AS delete from jobTypeGallery where typeID = @.typeid
GO

(1) You should be setting up an FK reference to the Remodel table from JobTypeGallery table.

(2) Modify your proc to check for existence of any records in the Remodel table before you delete here. You might also want to RaiseError appropriately. Also you can modify the proc to delete from the Remodel table first if any records exist there before deleting from jobTypeGallery.

Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @.typeidint AS BEGINIFNOT EXISTS (SELECT *FROM RemodelWHERE TypeId = @.TypeID)delete from jobTypeGallerywhere typeID = @.typeidENDGO

|||

Hope this will help you!!!

delete from JobTypeGallery, Remodel where JobTypeGallery.typeid!=Remodel.typeid

/

delete from JobTypeGallery, Remodel where JobTypeGallery.typeid<>Remodel.typeid

|||

The below stored procedure:

Create PROCEDURE [dbo].[spDeleteJobTypeGallery]
@.typeidint
AS
BEGIN

IFNOT EXISTS (SELECT *FROM RemodelWHERE TypeId = @.TypeID)
delete from jobTypeGallerywhere typeID = @.typeid
END
GO

worked well. I need help with creating a message that would display if there are no pictures associated with a jobtypegallery in the Remodel table. Can someone please help me with this?

|||

Read up books online for OUTPUT parameters. You can return an appropriate value via OUTPUT parameter to front end and display an appropriate message to the user.