Atomic Transaction

Hi all,

If I have 2 database operations
1. Update ...
2. Delete ...

I want these 2 operations to execute atomically, thus I make use of

BEGIN Transaction ...
Update ...
Delete ...
COMMIT Transaction ...

But if one of these operation fails, it locks up the database. Does anyone have a resolution for this? How do I detect sql Exceptions to release the transaction so that it doesn't lock up the database?

Thanx

James :)I assume this is written in a stored procedure.

In that case

After your first UPDATE you need to chek

@.@.ERROR

If its Zero continue otherwise you need to ROLLBACK the transaction as the update failed. The same is true for your Delete statement. You should only COMMIT when both succeed. You should report or log the error somehow of course.

Have a look in Books Online under the topic

Using @.@.ERROR

its under Accessing and Changing relational Data :: Advanced Query Concepts :: Error Handling

regards

Steve|||Thanx, I will give that a try.
I love dbforums, people here's always got the answer.

James :D

Asynchronous ADO procedure invocation?

Is there any way to send several ADO Command.Execute asynchronously at once?
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeIn the following example, an event handler has been implemented to print to
the Debug window when the command has completed:
Dim WithEvents conn As ADODB.Connection
Sub Form_Load()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql70server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
Set cmd.ActiveConnection = conn
cmd.Execute "select * from authors", , adAsyncExecute
Debug.Print "Command Execution Started."
End Sub
Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)
Debug.Print "Completed Executing the Command."
End Sub
Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
> Is there any way to send several ADO Command.Execute asynchronously at
> once?
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||Thanks for your answer.
However, I found that the secondary asynchronous Execute fails while the
first asynchronous Execute is not completed yet. Is this phenomenon
ordinary? Or is there any way to enable more overlapped Execute be allowed?
Thanks.
Regards,
Hyun-jik Bae
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In the following example, an event handler has been implemented to print
> to
> the Debug window when the command has completed:
> Dim WithEvents conn As ADODB.Connection
> Sub Form_Load()
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
> Set conn = New ADODB.Connection
> conn.ConnectionString = _
> "Provider=SQLOLEDB;Data Source=sql70server;" _
> & "User ID=sa;Password='';Initial Catalog=pubs"
> conn.Open
> Set cmd.ActiveConnection = conn
> cmd.Execute "select * from authors", , adAsyncExecute
> Debug.Print "Command Execution Started."
> End Sub
> Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
> pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
> pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
> ByVal pConnection As ADODB.Connection)
> Debug.Print "Completed Executing the Command."
> End Sub
> Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
> news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
>|||There should be the possibility to do this unless you will not use the same
connection for it.
Jens Suessmeyer.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:uCgnSElRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Thanks for your answer.
> However, I found that the secondary asynchronous Execute fails while the
> first asynchronous Execute is not completed yet. Is this phenomenon
> ordinary? Or is there any way to enable more overlapped Execute be
> allowed?
> Thanks.
> Regards,
> Hyun-jik Bae
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>

Asynchronous ADO Command execute

I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,
ThanksIt also happens inconsistently. Sometimes, it just runs fine and sometimes, it just gives a Program Error and everything blows out.|||Originally posted by archnam
I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,

Thanks

Have you looked in the SQL Server error log in enterprise manager?|||Where exactly are error logs in the SQL Server under Enterprise Manager?
Also, if I put a breakpoint in my program, it seems to run fine and when I just run it, it bombs, do you think may be I should put some delay in it. The Command.Execute might be taking some time to run.

AsyncExecute problem

Hi there!
I've got a problem using ADO to execute a stored procedure (sp) on sql
server, which gets two dates and fills a table with some data on that
interval. Typical execution time is about a minute for a 30days
interval. Since the sp takes potentialy a long time to execute, I
executes it async., and give the user the option to cancel it. The C++
code below does not include that, but it doesn't work anyway:

mdbConnection->BeginTrans();
_CommandPtr cmd;
cmd.CreateInstance( __uuidof( Command ) );
cmd->ActiveConnection = mdbConnection;
cmd->CommandType = adCmdStoredProc;
cmd->CommandText = _bstr_t( SP_DDPOM );
cmd->Parameters->Append( cmd->CreateParameter( "@.0", adVarChar,
adParamInput, 10, _bstr_t( dateod ) ) );
cmd->Parameters->Append( cmd->CreateParameter( "@.1", adVarChar,
adParamInput, 10, _bstr_t( datedo ) ) );
cmd->Execute( &vtEmpty, &vtEmpty2, adAsyncExecute );
int state;
while((state=cmd->State) & adStateExecuting) {
TRACE("State = %ld\n", state );
// if cancel then mdb->RollbackTrans(); return;
}
mdbConnection->CommitTrans();

The thing is that the state is adStateExecuting for about 16 seconds
(30days interval), and afterwards drops to 0 (adStateClosed), while the
sp is still executing (which usually takes about a minute). The process
waits at the CommitTrans() until the sp is done. There aren't any errors
reported or exceptions thrown.

BUT: NO DATA IS FILLED IN THE TABLE.

I've tested that begin-commit works by adding a call to another sp, and
it works fine.

The weird thing is that the sp works if the execution takes less then
this 16 or so second limit. E.g. for 3days interval, it works fine.

The weirdest thing is that the whole thing worked at first, i.e. did not
produce erroneous state and filled the table, and that for 5 minutes
execution and more. Then I changed something in the sp (commented out a
single line!!), and ever since then it doesn't work anymore. I've got
the database backup from when it worked, but doesn't anymore.

The sp works fine when executed from Query Analyzer.

Has anyone got any ideas? Am I missing something?
Thanks,
Juhu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!juhu (juhu@.bruhu.com) writes:
> The thing is that the state is adStateExecuting for about 16 seconds
> (30days interval), and afterwards drops to 0 (adStateClosed), while the
> sp is still executing (which usually takes about a minute). The process
> waits at the CommitTrans() until the sp is done. There aren't any errors
> reported or exceptions thrown.

I don't have any experience of asynchronous queries. But it could be
that the command timeout strikes. This timeout is by default 30 seconds.
Set it to 0 to get rid of it:

cmd->CommandTimeout = 0;

My first thought was that you have a pseudo-recordset in form of a
rowcount from an INSERT into a temp table. But since it works with
smaller amount of data it may not be that. Nevertheless, add a SET
NOCOUNT ON in the stored procedure if you don't have one.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

ASYNC_NETWORK_IO

Hello, When I execute a SQL SERVER 2005 stored procedure from Visual Basic 6 Application, I get my process locked with ASYNC_NETWORK_IO wait type.

When I Execute the same procedure from sql server console, It doesn't occur...

This is the visual basic code:

With objcommand

.ActiveConnection = CnnConexionPpalDb
.CommandTimeout = 0
.CommandText = "EJECUTIN"
.CommandType = adCmdStoredProc

'- Parametros del Command

'.Parameters("@.StrOrdenEstablecido").Value = StrOrdenEstablecido

End With
Set gTbl.VisOrdenamientoDatosFinalCiudades = objcommand.Execute

With gTbl.VisOrdenamientoDatosFinalCiudades

This is SQL SERVER CODE:

ALTER PROCEDURE [dbo].PCN_CONSULTAR_VISORDENAMIENTODATOSFINALCIUDADES_ORDENADO] @.StrOrdenEstablecido varchar(200)
as
BEGIN TRAN
DECLARE @.lvaSentencia NVARCHAR(4000)

SELECT dbo.Tbl_DatosSubidos.Direccion, dbo.Tbl_DatosSubidos.Ciudad, dbo.Tbl_DatosSubidos.Departamento,
dbo.Tbl_DatosSubidos.Inserto, dbo.Tbl_DatosSubidos.IndiceOLlaveUnica, dbo.Tbl_DatosSubidos.Identificacion,
dbo.Tbl_DetalleCiudadesBuenas.IdArchivo, dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes,
dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytesFinalDato, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Dep_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dep_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.DatoIngresado, dbo.Tbl_DetalleCiudadesBuenas.Dis_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dis_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.Doc_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Doc_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Cli_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Cli_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.DCB_Novedad,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Georreferenciada, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Prioridad,
dbo.Tbl_DetalleCiudadesBuenas.Geo_CodigoZona, dbo.Tbl_DetalleCiudadesBuenas.DatoCiudadCompleta,
dbo.Tbl_DetalleCiudadesBuenas.Tipo_Distribucion, dbo.Tbl_DatosSubidos.Factura, dbo.Tbl_DatosSubidos.Nombre,
dbo.Tbl_ArchivosSubidos.Ars_NombreArchivoSubido AS NombreArchivo
FROM dbo.Tbl_DatosSubidos WITH (NOLOCK) INNER JOIN
dbo.Tbl_ArchivosSubidos WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_ArchivosSubidos.Ars_Codigo INNER JOIN
dbo.Tbl_DetalleCiudadesBuenas WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_DetalleCiudadesBuenas.IdArchivo AND
dbo.Tbl_DatosSubidos.PosicionEnBytes = dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes

ORDER BY 1
COMMIT

This wait type means the server is busy sending rows to the client fo processing. The best way to fix it is to reduce the number of rows returned. Alternatively, client-side processing should be accelerated. Or both.

-Ryan / Kardax

Asymmetry in "Execute SQL Task"?

Anyone know why the Execute SQL Task is only available under Control flow and not Data flow? (At times it's a useful data manipuation task and I wish was available as a Data flow task as well.)

Barkingdog

It could lead to some extremely inefficient data flows.

Imagine executing a SQL statement for every record in a 1,000,000 row transformation?

Or what if you decided to execute an update against the source data in your transformation?

The posiibilities for abuse are staggering...

If you need to run a SQL task doing it before or after a transformation allows you to execute SQL on the entire set... Not just a single record.

|||ssis provides the ole db command data flow transformation component for sql-based data manipulation: http://msdn2.microsoft.com/en-us/library/ms141138.aspx

Association Rules: discretization

Hi there,

it has been a long i'm trying to execute Microsoft Association Rules on my database.

I solved memory leak problem now, but i still can't understand output rules.

Database contain all the italian student who took a degree last year. Here in Italy, they have to compile a summary where they speak about universitary experience. ie: they talk about experience with teachers (pointage from 1 to 5); they says if they want to continue in the universitary field or not, and so on.

Most of the rules, says:

Int_Stud=1-2, RapDoc>4

Int_Stud is the column where i store student intention to continue university. 1 means they want to go on, 2 means they do not want to continue to study. So, this rules has no sense, because it relates all the student (in my mind): the one who wants to continue university and the one who do not want to.

I think problem is that visual studio 2005 and analysis service has no understanding of Int_Stud world, they've no idea that Int_Stud can have just 2 values and that they're opposite each other. Is there a solution to this problem? Can i discretize this column?

Even if I know not to have perfect english, I hope to be understandable

I think you have to modify your data source in sense of replacing 1 with "Continue" and 2 with "NotContinue" or something like that. I think the system consider 1 and 2 as a number. This replacing conduct to understandability too.|||

do u mean i have to modify value in database? I mean, i've something similar to 90 column in the same situation of Int_Stud ... does exist another solution? Moreover, most of fact table - i worked on - had only numeric values in (no text) ...|||

In this discretized situations I think u have to make necessary transformations. Let's think you have to present this project to your big boss. You have to explain in a legend what you mean with Int_stud that have value 1 or 2 etc. I don't have to be in your position ...

|||

ggciubuc wrote:

In this discretized situations I think u have to make necessary transformations. Let's think you have to present this project to your big boss. You have to explain in a legend what you mean with Int_stud that have value 1 or 2 etc. I don't have to be in your position ...

I continue not understand what you mean when you speak about transformations .. why can't i make a discretization procedure on my numeric value? Can't i divided them into bucket?

|||

think you have to modify your data source in sense of replacing 1 with "Continue" and 2 with "NotContinue" or something like that.

|||

ggciubuc wrote:

think you have to modify your data source in sense of replacing 1 with "Continue" and 2 with "NotContinue" or something like that.

so, you mean that i've to modify my source data by changing numeric value with text one? Why? This will be text analysis, won't it?

I used weka one time, and it has a tool which allows to discretize numeric value by dividing it into bin (and you can set your preference too)

|||

You don't have to replace int values with text to make association rules model work correctly. All you need to do is change the column from Discretized to Discrete. (Open Mining Structure tab and look at the Content property of the column)

|||

My ideea is to make things more readable and understandable. I should do in this way.

|||

Tatyana Yakushev wrote:

You don't have to replace int values with text to make association rules model work correctly. All you need to do is change the column from Discretized to Discrete. (Open Mining Structure tab and look at the Content property of the column)

thanks so much .. it works exactly as i wanted to

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
Simon
This might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select@.uid = uid
from sysusers
where[name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David
|||Thanks guys!
|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh
|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegr oups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
SimonThis might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select @.uid = uid
from sysusers
where [name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David|||Thanks guys!|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
>> Hi All,
>> I have a database with about 250 stored procedures. I need to give some
>> users execute permissions on all of these procedures.
>> Can anyone tell me if there is an easy way to select a group and grant
>> execute permissions to it for all 250? I basically need the group to be
>> able to execute every SProc that I've created
>> Surely I don't have to go through every SProc one by one?
>> Please, please say it aint so!
>> :-(
>> Thanks
>> Simon
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
>>From sysobjects o
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
SimonThis might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select @.uid = uid
from sysusers
where [name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David|||Thanks guys!|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

assign variable within SP

I use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?
The name of the column I want to retreive the value from is "UserID"
Here's my SP so far:

ALTERPROCEDURE [dbo].[spUnregisterUser]

@.UserCodeint

AS

BEGIN

SETNOCOUNTON;declare @.uiduniqueidentifier--get useridSELECT @.uid=UserIDFROM tblUserDataWHERE UserCode=@.UserCode-- Delete userUPDATE tblUserDataSET IsDeleted='True'WHERE UserCode=@.UserCode

END

That's pretty much how you'd do it. What's not working?

ASP.NET and IS

Hi,

I am new to IS.

How do I use ASp.NET to execute an IS package by invoking a SQL Server stored procedure? Is this a good idea in terms of system performance?

Thanks for the help!!

anyone?|||Hi IS Dude,

This is a good link to get you started. I've used the method involving the web service and it seems to work well.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

Hope this helps,

Grant