RETURN @@ERROR?

If I execute one SP within a parent SP and want to trap and return an error
code from the child SP, can I just use RETURN @.@.ERROR in the child SP rather
than capturing @.@.ERROR in a local variable first? Or does the successful
completion of the RETURN statement set @.@.ERROR back to 0 by the time it gets
back to the parent SP?
The reason I'm asking is that all the examples I've found about error
trapping don't seem to do it this way, and this way seems the most
straightforward. Thanks for any insight.
CREATE PROCEDURE parentSP
DECLARE @.returncode int
EXEC @.returncode = childSP @.value
IF @.returncode <> 0 Do something like rollback transaction
CREATE PROCEDURE childSP
@.value int
INSERT INTO someTable (column) VALUES (@.value)
RETURN @.@.ERRORYour method will work for the simplest procedure but in the following 0
will be returned if the first insert fails:
CREATE PROCEDURE childSP
@.value int
as
INSERT INTO someTable (column) VALUES (@.value)
IF @.@.ERROR <> 0
RETURN @.@.ERROR
INSERT INTO someOtherTable (column) VALUES (@.value)
RETURN @.@.ERROR
go
It would have to be, a minium the following but this introduces
different methods of handling errors and provides multiple exit points
for the procedured:
CREATE PROCEDURE childSP
@.value int
as
declare @.err
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
RETURN @.err
INSERT INTO someOtherTable (column) VALUES (@.value)
RETURN @.@.ERROR
go
Best practices of coding state that each object should only have 1 exit
point so the following is the standard that I use:
CREATE PROCEDURE childSP
@.value int
as
declare @.err int
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
goto ErrH
INSERT INTO someOtherTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
goto ErrH
ErrH:
return @.ErrorSave|||Thanks. Yes, most of my "child" INSERT, UPDATE SPs are single statements and
I didn't have them trap errors or explicitly RETURN codes and just thought
RETURN @.ERROR was the most expedient.
I'm going over my SPs now to make sure I have one exit point instead of
exits all over.
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1147291591.811293.125770@.y43g2000cwc.googlegroups.com...
> Your method will work for the simplest procedure but in the following 0
> will be returned if the first insert fails:
> CREATE PROCEDURE childSP
> @.value int
> as
> INSERT INTO someTable (column) VALUES (@.value)
> IF @.@.ERROR <> 0
> RETURN @.@.ERROR
> INSERT INTO someOtherTable (column) VALUES (@.value)
> RETURN @.@.ERROR
> go
> It would have to be, a minium the following but this introduces
> different methods of handling errors and provides multiple exit points
> for the procedured:
> CREATE PROCEDURE childSP
> @.value int
> as
> declare @.err
> INSERT INTO someTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> RETURN @.err
> INSERT INTO someOtherTable (column) VALUES (@.value)
> RETURN @.@.ERROR
> go
> Best practices of coding state that each object should only have 1 exit
> point so the following is the standard that I use:
> CREATE PROCEDURE childSP
> @.value int
> as
> declare @.err int
> INSERT INTO someTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> goto ErrH
> INSERT INTO someOtherTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> goto ErrH
> ErrH:
> return @.ErrorSave
>|||If you don't like GOTO then the following also works:
CREATE PROCEDURE childSP
@.value int
as
declare @.err int
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err = 0 BEGIN
INSERT INTO someOtherTable (column) VALUES (@.value)
select @.err = @.@.ERROR
END
return @.Err

retrieving user's permissions for each table

Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via SQLMonster.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>> Hi ,
>[quoted text clipped - 10 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1

retrieving user's permissions for each table

Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1

Retrieving user defined Role name

Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.

Greetings,
GodofredoIs it not clear? I just want to retrieve a user defined Role

Greets,
geoff|||I'd use sp_helpuser (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_help_45o2.asp).

-PatP

Retrieving native progress events for a 2000 package from SSIS. Is it possible?

Dear all,

AFAIK I don't think so. Let me know if possible and how.

I'm talking about a SSIS package which owns a Execute DTS 2000 Package Task and from there it calls a DTS 2000. Programatically, from our VB .Net front-end app we're seeing only SSIS events (when validate and execute methods are called) and any kind of information is provided from RCW proxy (I suppose that internally it gonna be created in order to send messages between managed and unmanaged code).

When a task inside our DTS 2000 fails we're awaring by means a "generic message" such this:

Error: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

Thanks a lot for your time and comments,

hi guys any idea?

Retrieving from SQLServer to Word

Please,
how can I execute a SQL command into SQLServer from Word/VBA code using ADO? I want to execute a comand and work with resulting recordset.
Best regards.Please see the following link:

dbforums link (http://dbforums.com/t390641.html)

If this does not help, please respond.

Good luck !|||Thanks a lot. It works fine.

Retrieve GUID from SQL 2005 Stored Procedure

I have a stored procedure that returns GUID and BIT datatypes (see
below). I am using the VS 2005 TableAdapter.GetData to execute and
return the values. BUT, I can't pass a null value for the GUID output
parameter.
- Why is it required to pass a value for an output value?
- How do I retrieve the GUID value? If I remove the GUID field from
the SP, I can retrieve the BIT fields...so I am confident the SP works
fine.
Thanks for any ideas.
-KB
STORED PROCEDURE:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_UserLogin]
(
@.UserName nvarchar(50),
@.Password nvarchar(50),
@.Authorized bit output,
@.UserID uniqueidentifier output,
@.Security_Expeditor bit output,
@.Security_Tech bit output,
@.Security_WS bit output,
@.Security_RN bit output,
@.Security_MD bit output,
@.Security_SuperUser bit output,
@.Security_ReportAccess bit output,
@.Security_Admin bit output
)
AS
SET NOCOUNT ON;
BEGIN
Select @.Authorized = 'False'
Select @.Security_Expeditor = 'False';
Select @.Security_Tech = 'False';
Select @.Security_WS = 'False';
Select @.Security_RN = 'False';
Select @.Security_MD = 'False';
Select @.Security_SuperUser = 'False';
Select @.Security_ReportAccess = 'False';
Select @.Security_Admin = 'False';
END
BEGIN
select @.UserID = (select top 1 UserID from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and
IsApproved = 'True');
if @.@.rowcount > 0
set @.Authorized = 'True'
END
Begin
select * from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and IsApproved =
'False'
end
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'expeditor' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Expeditor = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'tech' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Tech = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'ws' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_WS = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'rn' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_RN = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'md' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_MD = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'superuser' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_SuperUser = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'reportaccess' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_ReportAccess = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'adminsecurity' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Admin = 'True'
END(corsspost to unofficial group removed)
On 19 Apr 2006 10:09:21 -0700, kb wrote:

>I have a stored procedure that returns GUID and BIT datatypes (see
>below). I am using the VS 2005 TableAdapter.GetData to execute and
>return the values. BUT, I can't pass a null value for the GUID output
>parameter.
Hi kb,
How do you attempt to pass NULL? For an OUTPUT variable, you have to
pass in a variable, never a constant - but that variable can be NULL
(see code example below).

>- Why is it required to pass a value for an output value?
Not a value, but a variable - because an output variable can be changed
from the stored proc. You can't change a constant!

>- How do I retrieve the GUID value?
See this example:
CREATE PROC Test @.guid uniqueidentifier OUTPUT
AS
SET @.guid = NEWID()
go
DECLARE @.x uniqueidentifier
SET @.x = NULL
SELECT @.x
EXEC Test @.guid = @.x OUTPUT
SELECT @.x
go
DROP PROC Test
go
Hugo Kornelis, SQL Server MVP

Retrieve date from filename

Hye all,

I need help on this.

Let say my source file in excel format. Currently i'm using foreach loop to execute those source files. The problem is that , i want to retrieve a date from the source(filename) itself. i've tried using derived column. but it doesnt work.

eg: Source file name Email_Jan_05.xls, i want to grab the string JAN and 05 which later i'm going to put it in my database.

any suggestion? Thanks in advance.

Inside foreach loop, insert a script task before the data flow task, create a presedence constraint between them. Inside the script task, obtain the file date and store it in some variable.|||

Thanks Michael.

Anyway, do u have any source code / syntax or reference for this?

|||I don't have any code at hand, but look at System.IO.File.GetCreationTime and System.IO.File.GetLastWriteTime (depending on what time you need).

Retrieve Count from stored procedure and display in datagrid.

Hi Guys,

I have a sql procedure that returns the following result when I execute it in query builder:

CountE ProjStatus

6 In Progress

3 Complete

4 On Hold

The stored procedure is as follow:

SELECT COUNT(*) AS countE, ProjStatus
FROM PROJ_Projects
GROUP BY ProjStatus

This is the result I want but when I try to output the result on my asp.net page I get the following error:

DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.

Source Error:

Line 271: </asp:TemplateColumn>Line 272: <asp:TemplateColumn>Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>Line 274: </asp:TemplateColumn>Line 275: </columns>

My asp.net page is as follows:

<script runat="server">

Dim myCommandPSAsNew SqlCommand("PROJ_GetProjStatus")

' Mark the Command as a SPROC

myCommandPS.CommandType = CommandType.StoredProcedure

Dim numasinteger

num =CInt(myCommand.ExecuteScalar)

'Set the datagrid's datasource to the DataSet and databind

Dim myAdapterPSAsNew SqlDataAdapter(myCommandPS)

Dim dsPSAsNew DataSet()

myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS

dgProjSumm.DataBind()

myConnection.Close()

</script>

<asp:datagridid="dgProjSumm"runat="server"

BorderWidth="0"

Cellpadding="4"

Cellspacing="0"

Width="100%"

Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"

Font-Size="xx-small"

AutoGenerateColumns="false">

<columns>

<asp:TemplateColumnHeaderText="Project Summary"HeaderStyle-Font-Bold="true">

<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem,"ProjStatus" ))%></itemtemplate>

</asp:TemplateColumn>

<asp:TemplateColumn>

<itemtemplate> <%# DataBinder.Eval(Container.DataItem,"Count" )%></itemtemplate>

</asp:TemplateColumn>

</columns>

</asp:DataGrid>

Please help if you can Im havin real trouble here.

Cheers

Since you have the count aliased as countE, it should be:

<%# DataBinder.Eval(Container.DataItem, "countE" )%>

|||

Thanks for pointing that out, I changed it and get the same error, any other ideas, do I have to create an output parameter or something.

Cheers

|||

Why are you using ExecuteScalar and a DataSet? Also, where is the SQL Connection as I didn't see that in your code?

Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
myCommandPS.CommandType = CommandType.StoredProcedure

Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()