hi friends,
Here is the stored procedures that I used.
--------------------------
create procedure globalCursor
AS
DECLARE abc CURSOR GLOBAL FOR
select * from sales
OPEN abc
create procedure globalCursorTest
AS
DECLARE @.sdate datetime
DECLARE @.sperson varchar(15)
DECLARE @.sregion varchar(15)
DECLARE @.sales int
EXECUTE globalCursor
FETCH NEXT FROM abc INTO @.sdate, @.sperson, @.sregion, @.sales
print @.sdate
print @.sperson
print @.sregion
print @.sales
--------------------------
When I execute globalCursorTest using SQL Query Analyser, it says
--------------------------
Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4
A cursor with the name 'abc' already exists.
Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5
The cursor is already open.
--------------------------
how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem.
JakeLooks like the abc cursor is not closed/deallocated. Does either one of the procedures perform these actions?|||Ummm...
Do you have an Oracle background?
To my knowledge it doesn't work that way, though I'll go test it out...
And yes, as Kaiowas points out you need to
CLOSE ABC
DEALLOCATE ABC
But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||hi brett,
I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.
The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.
I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.
Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.
Appreciate your he
Jake
Originally posted by Brett Kaiser
Ummm...
Do you have an Oracle background?
To my knowledge it doesn't work that way, though I'll go test it out...
And yes, as Kaiowas points out you need to
CLOSE ABC
DEALLOCATE ABC
But still, it looks like you're trying to mimic reference CURSORs like Oracle has...|||anybody know about this....
Originally posted by Jake K
hi brett,
I'm new to database and doing DB2 to SQL server migration tool project.
In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.
The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.
I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.
Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
but that will not solve my problem.
I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.
Appreciate your he
Jake|||"In DB2, one procedure can access the cursors opened by another procedure, after calling it."
Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||I guess my best suggestion would be to rewrite your cursor procedure as a table function.|||hi,
it's definitely not scope disaster!!! By default, the cursors opened in a procedure could not be accessed from another procedure. If one wants this kind of feature, the cursor has to be specially declared with the option "WITH RETURN TO CALLER/CLIENT". It's like Sequel's local & global cursor concept. In global cursor, the cursor can be accessed from outside where it is declared.
Jake
Originally posted by blindman
"In DB2, one procedure can access the cursors opened by another procedure, after calling it."
Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.|||thanks for your suggestion. as of now, i don't know about table function. I will try it out...
but i have another way of achieving this. the procedure that i attached in the starting mail is working fine, of course after including close & disallocate stmts at the end of the second procedure, globalCursorTest.
previously i used SQL Query Analyser GUI which will compile & execute the procedure at one shot. Thus the globalCursor procedure executed twice, which caused the 'cursor already opened' error.
As I mentioned in my earlier mails, i search for a mechanism which will only compile & create the procedure into the db without executing it. I find isql command line tool creates the procedure into the db without executing it.
After creating both the procedures, i executed second procedure, globalCursorTest. It works fine.
friends, Thanks for your time.
Jake
Originally posted by blindman
I guess my best suggestion would be to rewrite your cursor procedure as a table function.
Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts
Tuesday, March 27, 2012
Tuesday, March 20, 2012
Accessing .NET dlls from Stored procedures
Can anyone tell me is it possible to register and access .NET dlls from Stored procedures
and how to call those methods in those dlls from Stored proceduresI know that it is possible but I haven't done so myself. You would use an extended stored procedure and a type library conversion to get this to work with SQL Server 2000.
and how to call those methods in those dlls from Stored proceduresI know that it is possible but I haven't done so myself. You would use an extended stored procedure and a type library conversion to get this to work with SQL Server 2000.
Terri
Access.adp & Stored Procedures
Here is a bug - maybe.
I have an access.adp that executes a sproc on SQL Server 2000. The sproc
contains a correlated subquery that updates the table ExcelData. Works fine
until someone other than the dbo uses the project. The sproc will not update
a table owned by any non-dbo user. The sproc does not use any owner name so
it should work for any user and update the table they own. However the sproc
will only update the table dbo.ExcelData and that only when it is executed b
y
a dbo - which is right.
Curiously, I copied the SQL statement from the sproc into an Access VBA
module in the project. Changed it into a string and executed it using a
command object. Works perfectly. Will correctly update the table owned y the
user whether the user is the dbo or not.
I'd rather have one sproc on the server than the sql statement in the VBA
module because, actually, the sproc involves about 30 updating queries, not
just one and I'd like to minimize network trafic.
And I do not want a sproc for each potential user even though that would
allow each sproc to identify the table by the owner's name.
Question: How can a sproc be tweaked to recognize the current user when the
user is not the dbo and update the correct table when the sproc is executed
from an ADP?
--
malcolmIt's hard to say from your description what's going on, but I suspect
it's a naming issue rather than a permissions one since you can run
VBA code to achieve the desired result. One way to verify this is to
put a Profiler trace on the app and see what the exact calls are.
Another is to always use the two-part name, either dbo.Tablename or
ownername.Tablename, depending. Unlike Access, SQL Server considers
the name of the object to be the two part owner.object name, so if the
object was created by a sy
min it will be owned by dbo. If it was
created by a non-symin, it will be owned by that person. So you can
have a dbo.Table1, a Fred.Table1, a Cindy.Table1, (etc etc etc) all in
the same database, and each one will need its own sets of permissions
granted to other users. If Access sends a query across the wire for
"Table1", which one would you expect SQLS to use? So in order to avoid
future anguish, have everything owned by dbo, and specify the two-part
name everywhere, in the UI and in your code. And use Profiler to see
what's actually going across the wire. See SQL Books Online, "Using
Ownership Chains" for more information.
--Mary
On Sat, 27 Aug 2005 15:58:02 -0700, "malcolm"
<ramartower@.access312.com> wrote:
>Here is a bug - maybe.
>I have an access.adp that executes a sproc on SQL Server 2000. The sproc
>contains a correlated subquery that updates the table ExcelData. Works fine
>until someone other than the dbo uses the project. The sproc will not updat
e
>a table owned by any non-dbo user. The sproc does not use any owner name so
>it should work for any user and update the table they own. However the spro
c
>will only update the table dbo.ExcelData and that only when it is executed
by
>a dbo - which is right.
>Curiously, I copied the SQL statement from the sproc into an Access VBA
>module in the project. Changed it into a string and executed it using a
>command object. Works perfectly. Will correctly update the table owned y th
e
>user whether the user is the dbo or not.
>I'd rather have one sproc on the server than the sql statement in the VBA
>module because, actually, the sproc involves about 30 updating queries, not
>just one and I'd like to minimize network trafic.
>And I do not want a sproc for each potential user even though that would
>allow each sproc to identify the table by the owner's name.
>Question: How can a sproc be tweaked to recognize the current user when the
>user is not the dbo and update the correct table when the sproc is executed
>from an ADP?|||Hello, Malcolm
>From your description, I understand that:
1. You have some tables with the same name and same structure, owned by
different users (including dbo);
2. You wrote a stored procedure (owned by dbo), that accesses that
table(s), specifying only the name of the table (not the owner)
3. You want the procedure to access each user's table, when invoked by
non-dbo users.
4. Currently, the procedure always accesses the tables owned by dbo
(regardless of which user invoked it).
First, let me say that what you are seeing it's not a bug, it's the
expected behaviour. Normally, when an object is accessed without
specifying the owner name, SQL Server looks first for an object with
that name owned by the current user, and then (if it doesn't exist) it
looks for an object owned by dbo. However, in stored procedures, when
an object is accessed without specifying the owner name, SQL Server
only looks for an object owned by the owner of the stored procedure.
For more informations, see:
http://msdn.microsoft.com/library/e...curity_2sz6.asp
http://msdn.microsoft.com/library/e...des_07_786r.asp
If you want a procedure (owned by dbo) to access each user's table
(when invoked by non-dbo users), then you can use EXEC('...') in the
stored procedure, so the queries are considered in another batch,
outside the scope of the procedure.
For example (considering that you already have user "a" and user "b" in
your database):
CREATE TABLE a.TheTable (a int PRIMARY KEY)
CREATE TABLE b.TheTable (b int PRIMARY KEY)
CREATE TABLE dbo.TheTable (x int PRIMARY KEY)
GO
CREATE PROCEDURE dbo.Test
AS
EXEC ('SELECT * FROM TheTable')
SELECT * FROM TheTable
GO
GRANT EXEC ON dbo.Test TO public
The first statement in the procedure will select from the table owned
by the user that invoked the procedure, but the second statement will
select from the table owned by the user that owns this procedure (dbo).
Razvan
I have an access.adp that executes a sproc on SQL Server 2000. The sproc
contains a correlated subquery that updates the table ExcelData. Works fine
until someone other than the dbo uses the project. The sproc will not update
a table owned by any non-dbo user. The sproc does not use any owner name so
it should work for any user and update the table they own. However the sproc
will only update the table dbo.ExcelData and that only when it is executed b
y
a dbo - which is right.
Curiously, I copied the SQL statement from the sproc into an Access VBA
module in the project. Changed it into a string and executed it using a
command object. Works perfectly. Will correctly update the table owned y the
user whether the user is the dbo or not.
I'd rather have one sproc on the server than the sql statement in the VBA
module because, actually, the sproc involves about 30 updating queries, not
just one and I'd like to minimize network trafic.
And I do not want a sproc for each potential user even though that would
allow each sproc to identify the table by the owner's name.
Question: How can a sproc be tweaked to recognize the current user when the
user is not the dbo and update the correct table when the sproc is executed
from an ADP?
--
malcolmIt's hard to say from your description what's going on, but I suspect
it's a naming issue rather than a permissions one since you can run
VBA code to achieve the desired result. One way to verify this is to
put a Profiler trace on the app and see what the exact calls are.
Another is to always use the two-part name, either dbo.Tablename or
ownername.Tablename, depending. Unlike Access, SQL Server considers
the name of the object to be the two part owner.object name, so if the
object was created by a sy
min it will be owned by dbo. If it wascreated by a non-sy
min, it will be owned by that person. So you canhave a dbo.Table1, a Fred.Table1, a Cindy.Table1, (etc etc etc) all in
the same database, and each one will need its own sets of permissions
granted to other users. If Access sends a query across the wire for
"Table1", which one would you expect SQLS to use? So in order to avoid
future anguish, have everything owned by dbo, and specify the two-part
name everywhere, in the UI and in your code. And use Profiler to see
what's actually going across the wire. See SQL Books Online, "Using
Ownership Chains" for more information.
--Mary
On Sat, 27 Aug 2005 15:58:02 -0700, "malcolm"
<ramartower@.access312.com> wrote:
>Here is a bug - maybe.
>I have an access.adp that executes a sproc on SQL Server 2000. The sproc
>contains a correlated subquery that updates the table ExcelData. Works fine
>until someone other than the dbo uses the project. The sproc will not updat
e
>a table owned by any non-dbo user. The sproc does not use any owner name so
>it should work for any user and update the table they own. However the spro
c
>will only update the table dbo.ExcelData and that only when it is executed
by
>a dbo - which is right.
>Curiously, I copied the SQL statement from the sproc into an Access VBA
>module in the project. Changed it into a string and executed it using a
>command object. Works perfectly. Will correctly update the table owned y th
e
>user whether the user is the dbo or not.
>I'd rather have one sproc on the server than the sql statement in the VBA
>module because, actually, the sproc involves about 30 updating queries, not
>just one and I'd like to minimize network trafic.
>And I do not want a sproc for each potential user even though that would
>allow each sproc to identify the table by the owner's name.
>Question: How can a sproc be tweaked to recognize the current user when the
>user is not the dbo and update the correct table when the sproc is executed
>from an ADP?|||Hello, Malcolm
>From your description, I understand that:
1. You have some tables with the same name and same structure, owned by
different users (including dbo);
2. You wrote a stored procedure (owned by dbo), that accesses that
table(s), specifying only the name of the table (not the owner)
3. You want the procedure to access each user's table, when invoked by
non-dbo users.
4. Currently, the procedure always accesses the tables owned by dbo
(regardless of which user invoked it).
First, let me say that what you are seeing it's not a bug, it's the
expected behaviour. Normally, when an object is accessed without
specifying the owner name, SQL Server looks first for an object with
that name owned by the current user, and then (if it doesn't exist) it
looks for an object owned by dbo. However, in stored procedures, when
an object is accessed without specifying the owner name, SQL Server
only looks for an object owned by the owner of the stored procedure.
For more informations, see:
http://msdn.microsoft.com/library/e...curity_2sz6.asp
http://msdn.microsoft.com/library/e...des_07_786r.asp
If you want a procedure (owned by dbo) to access each user's table
(when invoked by non-dbo users), then you can use EXEC('...') in the
stored procedure, so the queries are considered in another batch,
outside the scope of the procedure.
For example (considering that you already have user "a" and user "b" in
your database):
CREATE TABLE a.TheTable (a int PRIMARY KEY)
CREATE TABLE b.TheTable (b int PRIMARY KEY)
CREATE TABLE dbo.TheTable (x int PRIMARY KEY)
GO
CREATE PROCEDURE dbo.Test
AS
EXEC ('SELECT * FROM TheTable')
SELECT * FROM TheTable
GO
GRANT EXEC ON dbo.Test TO public
The first statement in the procedure will select from the table owned
by the user that invoked the procedure, but the second statement will
select from the table owned by the user that owns this procedure (dbo).
Razvan
Tuesday, March 6, 2012
Access to Extended Stored Procedures
Hi,
How can I remove access to extended (xp_) stored procedures?
Is there any revoke on <stored_procedure_name> ... command? How can I generate a script of all users who have execute privileges for these procedures? Also, is there any way of restricting (instead of removing) access to those procedures?
Any help will be greatly appreciated!!!
Thanks,
Allarevoke execute on xp_fixeddrives from public|||Thanks! Also, how can I review which stored procedures public has execute privileges for?
Thanks in advance!
How can I remove access to extended (xp_) stored procedures?
Is there any revoke on <stored_procedure_name> ... command? How can I generate a script of all users who have execute privileges for these procedures? Also, is there any way of restricting (instead of removing) access to those procedures?
Any help will be greatly appreciated!!!
Thanks,
Allarevoke execute on xp_fixeddrives from public|||Thanks! Also, how can I review which stored procedures public has execute privileges for?
Thanks in advance!
Access to execute Store procedures
Hello there
I would like to give my users permission to insert/update/delete data
from/to tables and views
and i also need to give them full access to execute Store Procedures.
In order to do so i gave them on Dababase mode db_datareader and
db_datawriter roles.
This roles are giving them full access to insert/update and delete data, but
it doesn't give them access to execute Store procedures.
Which permission i sould give them wituout give them db_owner?Roy
GRANT EXECUTE ON storeprocedure TO username
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I would like to give my users permission to insert/update/delete data
> from/to tables and views
> and i also need to give them full access to execute Store Procedures.
> In order to do so i gave them on Dababase mode db_datareader and
> db_datawriter roles.
> This roles are giving them full access to insert/update and delete data,
> but it doesn't give them access to execute Store procedures.
> Which permission i sould give them wituout give them db_owner?
>|||Shalom Uri
This i've already know
I'm looking for role for this and not go spesific on each store procedure.
on the business i'm working on they are afraid to give the users db_owner's
permission which i know that it is not a problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
> Roy
> GRANT EXECUTE ON storeprocedure TO username
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
>|||Roy
Ok, so create a new role, add to the role users that you want to execute
sp. Go to the permission tab and click/check EXEC column for stored
procedure
Also , you can write script to grant an execute permission for all stored
procedure tospecific user. If you are interested I will post it out
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Shalom Uri
> This i've already know
> I'm looking for role for this and not go spesific on each store procedure.
> on the business i'm working on they are afraid to give the users
> db_owner's permission which i know that it is not a problem.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
>|||Roy
Be aware that if you have dymanic sql within a stored procedure you will
have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
as well
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Roy
> Ok, so create a new role, add to the role users that you want to execute
> sp. Go to the permission tab and click/check EXEC column for stored
> procedure
> Also , you can write script to grant an execute permission for all stored
> procedure tospecific user. If you are interested I will post it out
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Whell Uri
For this i have already gave db_datareader and db_datawriter role which
supply this need.
In fact before i deal there, it was as you said.
Now after i add db_datareader and db_datawriter this problem has gone and
there is full permission
it is seems to be some stupide of microsoft not to have role to execute
store procedures. it looks like the is reason for it. i'm wondering why
thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ed005aLHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Roy
> Be aware that if you have dymanic sql within a stored procedure you will
> have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
> as well
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> For this i have already gave db_datareader and db_datawriter role which
> supply this need.
> In fact before i deal there, it was as you said.
> Now after i add db_datareader and db_datawriter this problem has gone and
> there is full permission
> it is seems to be some stupide of microsoft not to have role to execute
> store procedures. it looks like the is reason for it. i'm wondering why
You can't do this in SQL 2000, as far as I know. But in SQL 2005,
permissions cascade, and you can say things like:
grant execute on database::yourdb to somerole
grant execute on schema::dbo to someotherrole
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I would like to give my users permission to insert/update/delete data
from/to tables and views
and i also need to give them full access to execute Store Procedures.
In order to do so i gave them on Dababase mode db_datareader and
db_datawriter roles.
This roles are giving them full access to insert/update and delete data, but
it doesn't give them access to execute Store procedures.
Which permission i sould give them wituout give them db_owner?Roy
GRANT EXECUTE ON storeprocedure TO username
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I would like to give my users permission to insert/update/delete data
> from/to tables and views
> and i also need to give them full access to execute Store Procedures.
> In order to do so i gave them on Dababase mode db_datareader and
> db_datawriter roles.
> This roles are giving them full access to insert/update and delete data,
> but it doesn't give them access to execute Store procedures.
> Which permission i sould give them wituout give them db_owner?
>|||Shalom Uri
This i've already know
I'm looking for role for this and not go spesific on each store procedure.
on the business i'm working on they are afraid to give the users db_owner's
permission which i know that it is not a problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
> Roy
> GRANT EXECUTE ON storeprocedure TO username
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
>|||Roy
Ok, so create a new role, add to the role users that you want to execute
sp. Go to the permission tab and click/check EXEC column for stored
procedure
Also , you can write script to grant an execute permission for all stored
procedure tospecific user. If you are interested I will post it out
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Shalom Uri
> This i've already know
> I'm looking for role for this and not go spesific on each store procedure.
> on the business i'm working on they are afraid to give the users
> db_owner's permission which i know that it is not a problem.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
>|||Roy
Be aware that if you have dymanic sql within a stored procedure you will
have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
as well
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Roy
> Ok, so create a new role, add to the role users that you want to execute
> sp. Go to the permission tab and click/check EXEC column for stored
> procedure
> Also , you can write script to grant an execute permission for all stored
> procedure tospecific user. If you are interested I will post it out
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Whell Uri
For this i have already gave db_datareader and db_datawriter role which
supply this need.
In fact before i deal there, it was as you said.
Now after i add db_datareader and db_datawriter this problem has gone and
there is full permission
it is seems to be some stupide of microsoft not to have role to execute
store procedures. it looks like the is reason for it. i'm wondering why
thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ed005aLHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Roy
> Be aware that if you have dymanic sql within a stored procedure you will
> have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
> as well
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> For this i have already gave db_datareader and db_datawriter role which
> supply this need.
> In fact before i deal there, it was as you said.
> Now after i add db_datareader and db_datawriter this problem has gone and
> there is full permission
> it is seems to be some stupide of microsoft not to have role to execute
> store procedures. it looks like the is reason for it. i'm wondering why
You can't do this in SQL 2000, as far as I know. But in SQL 2005,
permissions cascade, and you can say things like:
grant execute on database::yourdb to somerole
grant execute on schema::dbo to someotherrole
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Saturday, February 25, 2012
Access SQL functions through .net?
I usually access stored procedures using SQL data source. But now I need a string returned from the database. If I write a function in SQL how do I access it from an aspx.vb file?
Put it in a proc and run an EXEC query, same way as any other procedure.
Jeff
|||If the SQL function returns a string, why not use SqlCommand.ExecuteScalar method:
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
{
conn.Open();
string qstring = "SELECT dbo.fn_test('IORI')";
SqlCommand cmd = new SqlCommand(qstring, conn);
string s=cmd.ExecuteScalar().ToString();
Response.Write("The new string is:" + s);
}
Friday, February 24, 2012
Access permissions on stored procedures.
Hi All
Is there a way to find out what access permissions have been granted to all
stored procedures in my database? Thank you in advance.sp_helprotect returns all permissions in the current database. To see only
stored procedures, you could put the results into a temporary table and
delete all but EXECUTE grants, which will leave stored procedures and
scalar-valued functions. Try this:
CREATE TABLE #p (Owner sysname,
Object sysname,
Grantee sysname,
grantor sysname,
ProtectType varchar(10),
Action varchar(20),
[Column] sysname);
INSERT INTO #p EXEC sp_helprotect @.permissionarea = o;
DELETE #p WHERE Action <> 'Execute';
SELECT * FROM #p;
DROP TABLE #p;
HTH
Vern
"MittyKom" wrote:
> Hi All
> Is there a way to find out what access permissions have been granted to al
l
> stored procedures in my database? Thank you in advance.
>
Is there a way to find out what access permissions have been granted to all
stored procedures in my database? Thank you in advance.sp_helprotect returns all permissions in the current database. To see only
stored procedures, you could put the results into a temporary table and
delete all but EXECUTE grants, which will leave stored procedures and
scalar-valued functions. Try this:
CREATE TABLE #p (Owner sysname,
Object sysname,
Grantee sysname,
grantor sysname,
ProtectType varchar(10),
Action varchar(20),
[Column] sysname);
INSERT INTO #p EXEC sp_helprotect @.permissionarea = o;
DELETE #p WHERE Action <> 'Execute';
SELECT * FROM #p;
DROP TABLE #p;
HTH
Vern
"MittyKom" wrote:
> Hi All
> Is there a way to find out what access permissions have been granted to al
l
> stored procedures in my database? Thank you in advance.
>
Monday, February 13, 2012
Access front end - stored procedure result
Hello,
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil
Pass-through queries *are* the most efficient way of working with
stored procedures. You can manipulate them in code via the DAO
QueryDef object, supplying a string for the .SQL property that
contains the execute statement plus any parameter values:
qdf.SQL = "EXEC mysproc 'paramvalue1'", etc.
If the stored procedure returns a result set it will be read only,
which is ideal for reports, which can be based on the pass-through
query.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 13 Apr 2004 04:54:33 -0700, hey_its_neil@.yahoo.co.uk (Neil) wrote:
>Hello,
>I have a SQL Server 2000 database with an Access 97 front end.
>I want to run stored procedures, (not nessessarily ones which return
>records either - action type queries for adding new records etc), from
>access and retreive some result from the procedure which would be used
>programatically in VBA so that users don't get nasty looking ODBC
>errors. Could anyone suggest what I need to do? I'm aware I could use
>a pass through query to run my stored procedure all the time if i need
>a result returned, but this seems overkill for the sake of getting
>some form of return value from the procedure, surely there must be a
>more sensible way? Or is there a good website which tutors this kind
>of stuff? I've trawelled the net and I can't see anything which will
>help me so far and feel sure that this is such a normal thing to need
>to do...
>Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
>so far and feel nervy and unsure about how (or why) I should be using
>ADO, so DAO stuff would be nice! (or some nice pointers or examples on
>ADO to calm my nerves!)
>Cheers,
>Neil
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil
Pass-through queries *are* the most efficient way of working with
stored procedures. You can manipulate them in code via the DAO
QueryDef object, supplying a string for the .SQL property that
contains the execute statement plus any parameter values:
qdf.SQL = "EXEC mysproc 'paramvalue1'", etc.
If the stored procedure returns a result set it will be read only,
which is ideal for reports, which can be based on the pass-through
query.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 13 Apr 2004 04:54:33 -0700, hey_its_neil@.yahoo.co.uk (Neil) wrote:
>Hello,
>I have a SQL Server 2000 database with an Access 97 front end.
>I want to run stored procedures, (not nessessarily ones which return
>records either - action type queries for adding new records etc), from
>access and retreive some result from the procedure which would be used
>programatically in VBA so that users don't get nasty looking ODBC
>errors. Could anyone suggest what I need to do? I'm aware I could use
>a pass through query to run my stored procedure all the time if i need
>a result returned, but this seems overkill for the sake of getting
>some form of return value from the procedure, surely there must be a
>more sensible way? Or is there a good website which tutors this kind
>of stuff? I've trawelled the net and I can't see anything which will
>help me so far and feel sure that this is such a normal thing to need
>to do...
>Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
>so far and feel nervy and unsure about how (or why) I should be using
>ADO, so DAO stuff would be nice! (or some nice pointers or examples on
>ADO to calm my nerves!)
>Cheers,
>Neil
Access front end - stored procedure result
Hello,
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil
I was in your situation a couple years back when I first migrated my systems from Acces
s97 to Access2000 + SQL Server. There's actually a moderate amount of (somewhat obscur
e) reference material at http://support.microsoft.com, but I'll give you a s
hort synopsis.
Using ADO requires a few things:
First, in any database that uses ADO code, you're going to need to set a Ref
erence to a Microsoft ActiveX Data Objects Library.
In the VBA editor window, select Tool ->
References
Select an appropriate version of Microsoft ActiveX Data Objects Library. I
believe Access97 ships with at least version 2.1, but I believe the followin
g code will work just fine with pretty much any version.
Once you've done that, you need to do two things.
- Set up a Connection to your data source (in your case, SQL Server)
- Retrieve the data through the connection
You could also do this via DAO, although I honestly don't recommend doing so
, since DAO is terribly inefficient at handling communications between Acces
s and SQL Server (speaking from personal experience). While researching thi
s, I happened to find some old DAO code that I had commented out of my datab
ase, so I can provide the syntax for the DAO call here.
Hope this helps.
Jim
Edit: Sorry for the poor formatting, this forum software doesn't seem to wan
t to format my posts correctly.
stored procedures. You can manipulate them in code via the DAO
QueryDef object, supplying a string for the .SQL property that
contains the execute statement plus any parameter values:
qdf.SQL = "EXEC mysproc 'paramvalue1'", etc.
If the stored procedure returns a result set it will be read only,
which is ideal for reports, which can be based on the pass-through
query.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 13 Apr 2004 04:54:33 -0700, hey_its_neil@.yahoo.co.uk (Neil) wrote:
>Hello,
>I have a SQL Server 2000 database with an Access 97 front end.
>I want to run stored procedures, (not nessessarily ones which return
>records either - action type queries for adding new records etc), from
>access and retreive some result from the procedure which would be used
>programatically in VBA so that users don't get nasty looking ODBC
>errors. Could anyone suggest what I need to do? I'm aware I could use
>a pass through query to run my stored procedure all the time if i need
>a result returned, but this seems overkill for the sake of getting
>some form of return value from the procedure, surely there must be a
>more sensible way? Or is there a good website which tutors this kind
>of stuff? I've trawelled the net and I can't see anything which will
>help me so far and feel sure that this is such a normal thing to need
>to do...
>Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
>so far and feel nervy and unsure about how (or why) I should be using
>ADO, so DAO stuff would be nice! (or some nice pointers or examples on
>ADO to calm my nerves!)
>Cheers,
>Neil
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil
quote:
Originally posted by Neil
Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil
I was in your situation a couple years back when I first migrated my systems from Acces
s97 to Access2000 + SQL Server. There's actually a moderate amount of (somewhat obscur
e) reference material at http://support.microsoft.com, but I'll give you a s
hort synopsis.
Using ADO requires a few things:
First, in any database that uses ADO code, you're going to need to set a Ref
erence to a Microsoft ActiveX Data Objects Library.
In the VBA editor window, select Tool ->
References
Select an appropriate version of Microsoft ActiveX Data Objects Library. I
believe Access97 ships with at least version 2.1, but I believe the followin
g code will work just fine with pretty much any version.
Once you've done that, you need to do two things.
- Set up a Connection to your data source (in your case, SQL Server)
- Retrieve the data through the connection
You could also do this via DAO, although I honestly don't recommend doing so
, since DAO is terribly inefficient at handling communications between Acces
s and SQL Server (speaking from personal experience). While researching thi
s, I happened to find some old DAO code that I had commented out of my datab
ase, so I can provide the syntax for the DAO call here.
Hope this helps.
Jim
Edit: Sorry for the poor formatting, this forum software doesn't seem to wan
t to format my posts correctly.
code:
Public Sub VBA_ADO_Example()
' Example code to demonstrate how to make an ADO
' call to retrieve data from SQL Server
' Declare variables
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
' Open a connection to the server
' Depending on what sort of login authentication
' you are using on your server...
' If you are using SQL Server authentication...
cnn.ConnectionString = "Provider=SQLOLEDB.1;
Data Source=MY_SERVER_NAME;
Initi
al Catalog = MY_DB_NAME;
User ID = MY_LOGIN_NAME;
Password = MY_PASSWORD"
' If you are using Windows authentication...
cnn.ConnectionString = "Provider=SQLOLEDB.1;
Data Source=MY_SERVER_NAME;
Initi
al Catalog = MY_DB_NAME;
"
cnn.ConnectionTimeout = 0
cnn.Open
' Get the data
rst.Open "SELECT * FROM My_Table WHERE MyCondition = 1", cnn, adOpenDynamic,
adLockPessimistic
' At this point, you should be able to treat the rst object
' pretty much exactly like you would a normal DAO recordset
With rst
.AddNew
!MyDataField = MyValue
.Update
End With
rst.MoveFirst
MsgBox "My First Value Is " & rst!MyDataField
' Clean up
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = nothing
End Sub
|||Pass-through queries *are* the most efficient way of working withcode:
Public Sub VBA_DAO_Example()
' Example code to demonstrate how to make an DAO
' call to retrieve data from SQL Server
' Declare variables
Dim ws as Workspace
Dim cnn as Connection
Dim rst as Recordset
Set ws = CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseODBC)
' Open a connection to the server
' Depending on what sort of login authentication
' you are using on your server...
' If you are using SQL Server authentication...
Set cnn = ws.OpenConnection("MyConnection", dbDriverNoPrompt, False, "ODBC;
D
ATABASE=MY_DATABASE;
UID=MY_USERNAME;
PWD=
MY_PASSWORD;
DSN=MY_ODBC_DATA_SOURCE_
NAME")
' If you are using Windows authentication...
Set cnn = ws.OpenConnection("MyConnection", dbDriverNoPrompt, False, "ODBC;
D
ATABASE=MY_DATABASE;
DSN=MY_ODBC_DATA_SOU
RCE_NAME")
Set rst = cnn.OpenRecordset("SELECT * FROM MyTable WHERE MyCondition = 1", d
bOpenDynamic, 0, dbPessimistic)
' Manipulate or retrieve data
With rst
.AddNew
!MyDataField = MyValue
.Update
End With
rst.MoveFirst
MsgBox "My First Value Is " & rst!MyDataField
' Clean up
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
ws.Close
Set ws = nothing
End Sub
stored procedures. You can manipulate them in code via the DAO
QueryDef object, supplying a string for the .SQL property that
contains the execute statement plus any parameter values:
qdf.SQL = "EXEC mysproc 'paramvalue1'", etc.
If the stored procedure returns a result set it will be read only,
which is ideal for reports, which can be based on the pass-through
query.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 13 Apr 2004 04:54:33 -0700, hey_its_neil@.yahoo.co.uk (Neil) wrote:
>Hello,
>I have a SQL Server 2000 database with an Access 97 front end.
>I want to run stored procedures, (not nessessarily ones which return
>records either - action type queries for adding new records etc), from
>access and retreive some result from the procedure which would be used
>programatically in VBA so that users don't get nasty looking ODBC
>errors. Could anyone suggest what I need to do? I'm aware I could use
>a pass through query to run my stored procedure all the time if i need
>a result returned, but this seems overkill for the sake of getting
>some form of return value from the procedure, surely there must be a
>more sensible way? Or is there a good website which tutors this kind
>of stuff? I've trawelled the net and I can't see anything which will
>help me so far and feel sure that this is such a normal thing to need
>to do...
>Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
>so far and feel nervy and unsure about how (or why) I should be using
>ADO, so DAO stuff would be nice! (or some nice pointers or examples on
>ADO to calm my nerves!)
>Cheers,
>Neil
Subscribe to:
Posts (Atom)