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 |
|
afterburn
Starting Member
28 Posts |
 Posted - 2004-04-27 : 01:14:32
|
| I have am executing sp_stored_procedures into a temp table with set nocount on.to get a list sp's, narrowing it down to what I need but however some procedures do not get returned.... Its like its random or something SQL Analyzer sees them but ADO says its EOF/BOF. Which is very odd....The oddest part is that it can see some of them but not all of them. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-27 : 01:34:58
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34551Does that help ?Damian |
 |
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-04-27 : 01:37:40
|
| SET NOCOUNT ONcreate table #tmpSpInfo(PROCEDURE_QUALIFIER varchar(255),PROCEDURE_OWNER varchar(255),PROCEDURE_NAME nvarchar(134) ,NUM_INPUT_PARAMS int ,NUM_OUTPUT_PARAMS int ,NUM_RESULT_SETS int ,REMARKS varchar(254) ,PROCEDURE_TYPE smallint)insert into #tmpSpInfoexec originsUnknown..sp_stored_procedures @sp_owner = 'dbo'select left(PROCEDURE_NAME,Len(PROCEDURE_NAME)-2) as PROCEDURE_NAME,PROCEDURE_TYPE from #tmpSpInfo where PROCEDURE_NAME not like 'dt_%' order by PROCEDURE_NAMEdrop table #tmpSpInfo SET NOCOUNT Offnope as you can see its already done. Its not giving me that error its just not returning the same results as Analyzer |
|
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-04-27 : 23:15:07
|
| Well stupid thing finally works.The issue is that you can not do an Parameter.Refresh if you do not have permissions on the procedure to execute. Seems there a bit more going on in ADO than is suggested. Though you would think if it doesnt give me the parameters using refresh it should show me the procedure using "sp_stored_procedures"Thanks for the reply. Singularly..... LOL |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-27 : 23:21:49
|
Wow, with the info you provided in your posts there is no way in hell I coulda figured that was your problem.  If you want to see what ADO is doing, run profiler when you call the proc. It will show you exactly what is going on.Damian |
|
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-04-27 : 23:25:52
|
| Well I am login as administrator on my local box running SQL with NT Auth. But the webpage is executing using NT Auth of IUSR_, which couldn't see it howeverseems that cause Analyzer was using my creditials it was showing me the result however IUSR_ could not execute the procedure and the resut was not returned. Which was creating some confusion LMAO ... |
|
|
|
|
|
|
|
|
ADO issue