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

Netbios and SQL Server Startup Procedure

Is there any way to configure MS SQL Server 2000 standard/developer to choose
a different name other than the netbios machine name as the "server" identity
during startup.
We are currently haveing issues with the assignment of a netbios alias that
SQL Server 2000 reads on it's startup that is different than the name of the
default instance.
TIA,
Hi
Have you checked out
http://msdn.microsoft.com/library/de...stall_5r8f.asp
John
"Dax Lawless" wrote:

> Is there any way to configure MS SQL Server 2000 standard/developer to choose
> a different name other than the netbios machine name as the "server" identity
> during startup.
> We are currently haveing issues with the assignment of a netbios alias that
> SQL Server 2000 reads on it's startup that is different than the name of the
> default instance.
> TIA,
|||That is interesting, however, that is not actually what is happening. The
machine name is actually staying the same as far as the TCP/IP properties are
concerned. We are simply assigning an alias during the startup sequence of
our failover maintenance software (Legato AAM) for a piece of 3rd party
software to use.
Unfortunately, it seems that SQL Server 2000 is reading this netbios alias
and thinks that the servername and when it compares that name with the name
of the default instance (which is not the alias), it treats the default
instance as a named instance and refuses to start listening at port 1433 as
it should with the default instance (we are not using any named instances in
our SQL Server 2000 configuration).
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you checked out
> http://msdn.microsoft.com/library/de...stall_5r8f.asp
> John
> "Dax Lawless" wrote:
|||Hi
Have you looked at creating an alias in the client networking utility,
or you may possibly be able to overcome it by adding the name to the
the hosts file?
John

Netbios and SQL Server Startup Procedure

Is there any way to configure MS SQL Server 2000 standard/developer to choose
a different name other than the netbios machine name as the "server" identity
during startup.
We are currently haveing issues with the assignment of a netbios alias that
SQL Server 2000 reads on it's startup that is different than the name of the
default instance.
TIA,Hi
Have you checked ou
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
John
"Dax Lawless" wrote:
> Is there any way to configure MS SQL Server 2000 standard/developer to choose
> a different name other than the netbios machine name as the "server" identity
> during startup.
> We are currently haveing issues with the assignment of a netbios alias that
> SQL Server 2000 reads on it's startup that is different than the name of the
> default instance.
> TIA,|||That is interesting, however, that is not actually what is happening. The
machine name is actually staying the same as far as the TCP/IP properties are
concerned. We are simply assigning an alias during the startup sequence of
our failover maintenance software (Legato AAM) for a piece of 3rd party
software to use.
Unfortunately, it seems that SQL Server 2000 is reading this netbios alias
and thinks that the servername and when it compares that name with the name
of the default instance (which is not the alias), it treats the default
instance as a named instance and refuses to start listening at port 1433 as
it should with the default instance (we are not using any named instances in
our SQL Server 2000 configuration).
"John Bell" wrote:
> Hi
> Have you checked out
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
> John
> "Dax Lawless" wrote:
> > Is there any way to configure MS SQL Server 2000 standard/developer to choose
> > a different name other than the netbios machine name as the "server" identity
> > during startup.
> >
> > We are currently haveing issues with the assignment of a netbios alias that
> > SQL Server 2000 reads on it's startup that is different than the name of the
> > default instance.
> >
> > TIA,|||Hi
Have you looked at creating an alias in the client networking utility,
or you may possibly be able to overcome it by adding the name to the
the hosts file?
John

Netbios and SQL Server Startup Procedure

Is there any way to configure MS SQL Server 2000 standard/developer to choos
e
a different name other than the netbios machine name as the "server" identit
y
during startup.
We are currently haveing issues with the assignment of a netbios alias that
SQL Server 2000 reads on it's startup that is different than the name of the
default instance.
TIA,Hi
Have you checked out
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
John
"Dax Lawless" wrote:

> Is there any way to configure MS SQL Server 2000 standard/developer to cho
ose
> a different name other than the netbios machine name as the "server" ident
ity
> during startup.
> We are currently haveing issues with the assignment of a netbios alias tha
t
> SQL Server 2000 reads on it's startup that is different than the name of t
he
> default instance.
> TIA,|||That is interesting, however, that is not actually what is happening. The
machine name is actually staying the same as far as the TCP/IP properties ar
e
concerned. We are simply assigning an alias during the startup sequence of
our failover maintenance software (Legato AAM) for a piece of 3rd party
software to use.
Unfortunately, it seems that SQL Server 2000 is reading this netbios alias
and thinks that the servername and when it compares that name with the name
of the default instance (which is not the alias), it treats the default
instance as a named instance and refuses to start listening at port 1433 as
it should with the default instance (we are not using any named instances in
our SQL Server 2000 configuration).
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you checked out
> http://msdn.microsoft.com/library/d...nstall_5r8f.asp
> John
> "Dax Lawless" wrote:
>|||Hi
Have you looked at creating an alias in the client networking utility,
or you may possibly be able to overcome it by adding the name to the
the hosts file?
John

Net Send

I got this script from Database journal and I found that very useful to DBA:
Create procedure netsend @.dbname varchar(50)
As
--Script Language and Platform: MS SQL 7.0 and MS SQL 2000
--Objecttive: Before restoreing,upgrading database,database administrator is
responsible to
--inform all the users in that database that they are going to be
disconnected and remind
--them to save their works.
--exec netsend 'xyz'--xyz is the database name
--Created by :Claire Hsu 2003/4/19
--Email:messageclaire@.yahoo.com
create table table1000(msg varchar(500))
insert into table1000
select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 min
,please save your work"' from master.dbo.sysprocesses
y,master.dbo.sysdatabases x
where x.dbid = y.dbid and x.name = @.dbname
declare @.msgs varchar(500)
declare cur1 cursor for select msg from table1000
open cur1
fetch next from cur1 into @.msgs
while @.@.fetch_status = 0
begin
exec master.dbo.xp_cmdshell @.msgs
fetch next from cur1 into @.msgs
end
close cur1
deallocate cur1
drop table table1000
--Usage
--exec netsend 'xyz'
However when I ran this query, I got the error message as below:
Server: Msg 207, Level 16, State 3, Procedure netsend, Line 14
Invalid column name 'net send '.
I think the something wrong with the " net send", anyone can advise on this?
Thanks so muchDo not use colon, use apostrophes instead.

> select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 min[/colo
r]
select 'net send ' + ltrim(rtrim(y.hostname)) + ' "we will log you out in 2
min
AMB
"Sql Fren" wrote:
> I got this script from Database journal and I found that very useful to DB
A:
> Create procedure netsend @.dbname varchar(50)
> As
> --Script Language and Platform: MS SQL 7.0 and MS SQL 2000
> --Objecttive: Before restoreing,upgrading database,database administrator
is
> responsible to
> --inform all the users in that database that they are going to be
> disconnected and remind
> --them to save their works.
> --exec netsend 'xyz'--xyz is the database name
> --Created by :Claire Hsu 2003/4/19
> --Email:messageclaire@.yahoo.com
>
> create table table1000(msg varchar(500))
> insert into table1000
> select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 m
in
> ,please save your work"' from master.dbo.sysprocesses
> y,master.dbo.sysdatabases x
> where x.dbid = y.dbid and x.name = @.dbname
> declare @.msgs varchar(500)
> declare cur1 cursor for select msg from table1000
> open cur1
> fetch next from cur1 into @.msgs
> while @.@.fetch_status = 0
> begin
> exec master.dbo.xp_cmdshell @.msgs
> fetch next from cur1 into @.msgs
> end
> close cur1
> deallocate cur1
> drop table table1000
>
> --Usage
> --exec netsend 'xyz'
>
> However when I ran this query, I got the error message as below:
> Server: Msg 207, Level 16, State 3, Procedure netsend, Line 14
> Invalid column name 'net send '.
>
> I think the something wrong with the " net send", anyone can advise on thi
s?
> Thanks so much
>|||Correction,
Do not use quotation mark, use apostrophes instead.
> select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 min[/colo
r]
select 'net send ' + ltrim(rtrim(y.hostname)) + ' "we will log you out in 2
min
AMB
"Alejandro Mesa" wrote:
> Do not use colon, use apostrophes instead.
>
> select 'net send ' + ltrim(rtrim(y.hostname)) + ' "we will log you out in
2
> min
>
> AMB
> "Sql Fren" wrote:
>

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 cursors

Hi, i have to nest 2 cursors together and frankly i'm lost.

Here's what I have so far

CREATE PROCEDURE SeeAllColumns
AS
SET NOCOUNT ON
DECLARE @.strMessage VARCHAR(100)
DECLARE @.strColumn VARCHAR(100)
DECLARE @.strTable VARCHAR(100)
DECLARE @.strCommand VARCHAR(250)

SELECT @.strMessage = 'SELECT ALL TABLES'
PRINT @.strMessage
DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'

OPEN crsTables
FETCH NEXT FROM crsTables INTO @.strTable
WHILE (@.@.FETCH_STATUS = 0) BEGIN

SELECT @.strMessage = 'SELECT ALL COLUMNS'
PRINT @.strMessage

SELECT @.strCommand = ' SELECT ' + @.strColumn + ' FROM ' + @.strTable

EXECUTE (@.strCommand)

FETCH NEXT FROM crsTables INTO @.strTable

END

DEALLOCATE crsTables

PRINT 'DONE'

What i need to do is select all columns from all tables, but none of the actual data within the columns, just the names. I just don't know where or how to insert the 2nd cursor.
Can someone help me?

Thanks!Frankly ... you lost me somewhere in the middle of the proc ... could you plz explain what you are trying to do here ...
as far as it seems to me .. this proc will generate as many resultsets as the total no of columns in the database

DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
name LIKE 'T%'

should probably be

DECLARE crsTables CURSOR FOR
SELECT
name AS strTable
FROM
sysobjects
WHERE
xtype = 'U'

if you are trying to select all user tables|||declare @.tbl sysname
declare @.clm sysname

declare tbls cursor local fast_forward for
select [TABLE_NAME]
from [INFORMATION_SCHEMA].[TABLES]
where [TABLE_TYPE] = 'BASE TABLE'

open tbls

fetch next from tbls into @.tbl
while @.@.fetch_status = 0
begin
declare cols cursor local fast_forward for
select [COLUMN_NAME]
from [INFORMATION_SCHEMA].[COLUMNS]
where [TABLE_NAME] = @.tbl

open cols

fetch next from cols into @.clm
while @.@.fetch_status = 0
begin
print @.tbl + ':' + @.clm

fetch next from cols into @.clm
end

close cols
deallocate cols

fetch next from tbls into @.tbl
end

close tbls
deallocate tbls|||you can just get by with 1 cursor if you declare it against this statement (alternatively you can use information_schema.columns view):

select cast(object_name(id) as char(128)), colid, [name] from syscolumns
where objectproperty(id, 'istable')=1 and objectproperty(id, 'ismsshipped')=0
order by id, colid

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 Transaction!

The following example is given in BOL under the topic 'Nested
Transaction':
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction,this will roll back TransProc's
nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit of the inner
transaction from the first EXECUTE statement of TransProc was
overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
---
The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
Apart from this, there is another transaction named 'OutOfProc' (which,
if I am not mistaken, isn't a part & parcel of the stored procedure
'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
where or which is the nested transaction? Shouldn't a nested
transaction have 1 transaction under another transaction something like
this (similar to nested If...Else conditions):
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
--Do Something
BEGIN TRANSACTION InProc1
--Do Something More
BEGIN TRANSACTION InProc2
--Do Something More
COMMIT TRANSACTION InProc
---
Or does the presence of more than 1 SQL statement (the 2 INSERT
queries) within the transaction 'InProc' (in the stored procedure
'TransProc') make it a nested transaction?
Thanks,
ArpanA begin tran must have either a commit/rollback tran. Thus, the TransProc's
Inproc transaction is participating in the OutProc transaction. If
commit/rollback tran OutProc is not explicitly called and the connection to
the server is dropped, the transaction will be forced to rollback.
As stated in bol, if outer most commit/rollback is what really important. It
decides the final commit all or rollback all.
-oj
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Thanks, OJ, for your help. How stupid of me....actually I had
misinterpreted the example given in BOL :-)
Thanks once again,
Regards,
Arpan|||Transactions are not really nested. There can only be one outstanding
transaction context for a connection. The global variable @.@.TRANCOUNT
reports whether or not a transaction context is outstanding, and its value
immediately before a Transact-SQL statement begins executing determines
whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
0, no transaction context is outstanding for the connection. Each time a
BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
@.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
transaction started by that BEGIN TRANSACTION statement. Each sucessive
BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
COMMIT TRANSACTION statement are changes made within the transaction made
permanent by that COMMIT TRANSACTION statement. All Transact-SQL statements
that alter the state or schema of the database operate within the context of
a transaction. If a transaction context is not already outstanding, then a
new transaction context is initiated before executing the statement. If
IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
committed immediately after the statement completes. This is called
"autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit COMMIT
WORK must be issued to commit the transaction. A transaction can span
multiple statements, multiple stored procedure calls, even multiple batches.
If a transaction is outstanding, dynamic SQL executed via either EXEC() or
sp_executesql executes within that transaction context. Since changes made
while a transaction context is outstanding are not made permanent until
they're committed, ROLLBACK backs all of the changes for the entire
transaction context. The only exception is when a savepoint is specified on
a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
log that identifies a reference point which can be specified in a ROLLBACK
statement to partially backout changes made while a transaction context is
outstanding. When a ROLLBACK savepoint statement is executed, all changes
made after the save point are backed out, and the transaction context
remains outstanding.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Dude, paragraph breaks :)

> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection.
I don't know that this particularly means that transactions aren't nested.
By your definition IF..THEN statements are not really nested. I don't think
that nested transactions implies any technical innerworkings more than it
just simply implies that you can syntactically do:
BEGIN TRANSACTION
BEGIN TRANSACTION
BEGIN TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
It is more or less meaningless to us whether a stack is used or a counter
and whether or not ROLLBACK kills the whole stack or just goes back to the
original point. Either way I still consider them nested because of syntax.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection. The global variable @.@.TRANCOUNT
> reports whether or not a transaction context is outstanding, and its value
> immediately before a Transact-SQL statement begins executing determines
> whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
> 0, no transaction context is outstanding for the connection. Each time a
> BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
> @.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
> transaction started by that BEGIN TRANSACTION statement. Each sucessive
> BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
> decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
> COMMIT TRANSACTION statement are changes made within the transaction made
> permanent by that COMMIT TRANSACTION statement. All Transact-SQL
> statements
> that alter the state or schema of the database operate within the context
> of
> a transaction. If a transaction context is not already outstanding, then
> a
> new transaction context is initiated before executing the statement. If
> IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
> committed immediately after the statement completes. This is called
> "autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit
> COMMIT
> WORK must be issued to commit the transaction. A transaction can span
> multiple statements, multiple stored procedure calls, even multiple
> batches.
> If a transaction is outstanding, dynamic SQL executed via either EXEC() or
> sp_executesql executes within that transaction context. Since changes
> made
> while a transaction context is outstanding are not made permanent until
> they're committed, ROLLBACK backs all of the changes for the entire
> transaction context. The only exception is when a savepoint is specified
> on
> a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
> log that identifies a reference point which can be specified in a ROLLBACK
> statement to partially backout changes made while a transaction context is
> outstanding. When a ROLLBACK savepoint statement is executed, all changes
> made after the save point are backed out, and the transaction context
> remains outstanding.
>
> "Arpan" <arpan_de@.hotmail.com> wrote in message
> news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
>|||There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there m
ust always be a matching commit transaction for every begin transaction, and
that transactions can be "nested," many newbies erroneously assume that a r
ollback only backs out the innermost block of statements--that is, to the po
int of the innermost BEGIN TRANSACTION. The assumption follows from the pat
tern etched in their brain by the second example. A break exits the innermo
st block; therefore, it is logical to assume (erroneously, of course) that a
rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the b
lock of code bounded by the outermost begin/commit pair, the transaction con
text is initiated by the first BEGIN TRANSACTION and is terminated either by
any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:#7aRUwomFHA.2484@.TK2
MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't thi
nk
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
>
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
>
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of syntax
.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>
>|||I like to think of it as:
"Nested transactions are allowed in syntax but not semantics."
I don't know what "real" nested transactions mean, or if there is a formal d
efinition of what nested
transaction semantics means? :
Perhaps a rollback of an inner transaction would allow commit of an outer tr
ansaction? We can do
that with savepoints.
Or the other way: A commit of an inner transaction will still be committed i
f the outer transaction
does a rollback? True, we don't have this in SQL server (which, I believe, B
rian wished for in an
earlier post in some other thread). To some extent, we can work around it wi
th table variables or
opening a new connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
By your definition
> IF..THEN statements are not really nested. I don't think that nested tran
sactions implies any
> technical innerworkings more than it just simply implies that you can synt
actically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
and whether or not
> ROLLBACK kills the whole stack or just goes back to the original point. E
ither way I still
> consider them nested because of syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||But the one break can get us out of several scope operators. I see your
point, but as long as you have to commit them one at a time, I think the
common term nested for transactions is probably going to stick...
And you code still looks pretty good in plain text :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:u9My2XpmFHA.4028@.TK2MSFTNGP10.phx.gbl...
There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there
must always be a matching commit transaction for every begin transaction,
and that transactions can be "nested," many newbies erroneously assume that
a rollback only backs out the innermost block of statements--that is, to the
point of the innermost BEGIN TRANSACTION. The assumption follows from the
pattern etched in their brain by the second example. A break exits the
innermost block; therefore, it is logical to assume (erroneously, of course)
that a rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the
block of code bounded by the outermost begin/commit pair, the transaction
context is initiated by the first BEGIN TRANSACTION and is terminated either
by any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:#7aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't
> think
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of
> syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||No doubt we could use a more convienient model for how transactions work,
but your statement is great.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDRWzWqmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I like to think of it as:
> "Nested transactions are allowed in syntax but not semantics."
>
> I don't know what "real" nested transactions mean, or if there is a formal
> definition of what nested transaction semantics means? :
> Perhaps a rollback of an inner transaction would allow commit of an outer
> transaction? We can do that with savepoints.
> Or the other way: A commit of an inner transaction will still be committed
> if the outer transaction does a rollback? True, we don't have this in SQL
> server (which, I believe, Brian wished for in an earlier post in some
> other thread). To some extent, we can work around it with table variables
> or opening a new connection.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
>

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 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 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