Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pkolf
Starting Member
4 Posts |
 Posted - 2004-06-04 : 13:28:36
|
| I seem to be having trouble with temp tables when accessing by means of an approle. If I connect to the database, use sp_setapprole, and run two identical stored procedures except that one contains the statement "select 'abc' as abc into #t", the one containing that statement fails while the other functions properly.Any ideas?Thanks, -Paul |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 13:31:20
|
| What is the error that it fails with?Tara |
 |
|
|
pkolf
Starting Member
4 Posts |
Posted - 2004-06-04 : 14:12:39
|
| The error I get is***Load\ADODB.Recordset (0x800A0E78)Operation is not allowed when the object is closed.***Actually I seem to get away with select into #t sometimes but I cannot select from #t-Paul |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 14:21:48
|
| Ok, since that is not a SQL Server error, what happens when you execute the queries inside Query Analyzer?Tara |
|
|
|
pkolf
Starting Member
4 Posts |
Posted - 2004-06-04 : 14:25:10
|
| It works fine. Even when you apply the approle.-P |
|
|
|
cra
Starting Member
2 Posts |
Posted - 2004-06-24 : 08:32:21
|
| I have the same error.If a make a temp table # in my store procedure then i have an error. |
|
|
|
pkolf
Starting Member
4 Posts |
Posted - 2004-06-24 : 09:17:25
|
| I finally resolved the problem by adding "SET NOCOUNT ON" to the SPHope that works for you,-Paul |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-24 : 12:11:06
|
| SET NOCOUNT ON should be the first statement after the AS in a stored procedure.Tara |
|
|
|
|
|
|
approles and temp tables