SQLEXECDirect is returning an error

Question

I have a C++ program that should return the showplan of a store procedure.    In the program, I am using the SQLEXECDirect statment to put the showplan_text on and then calling my store procedure.  My store procedure has a temp table in it.   when I execute the SQLExecDirect statement, I get this error - Invalid object name #temptable.   It seems that it does not like the temptabe.  My temp table in my store procedure is created by select * into #temptable...   If I replace this with the statement Create #temptable...   everythign works.  If I run up the 2012 Management studio and set the showplan on in the new query and run the showplan on the store procedure, I do get the showplan back in the result set, but in the message set, I get the error above.   All the research I did says that you either have to replace the select into with the create table, which we can not do, or actually execute the store procedure.  We do not want to do that either since we just want the showplan.   Is there anyway thru C++ that I can skip that message set informational error and get to the result set which has the showplan.

Answer 1

Hi SAR88,

Thanks for your post!

I would like suggest you to post this thread to Visual C++ forum for more support since this thread is a C++ issue more than a SQL Server Data Access issue and please notice this forum is for the questions and discussion about data access to SQL Server using SQL Native Client, OLEDB, ODBC, ADO, WDAC/MDAC, JDBC, or SOAP/HTTP.

Here is the C++ forum link: http://social.msdn.microsoft.com/Forums/en-US/vclanguage/threads

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

I have a C++ program that should return the showplan of a store procedure.    In the program, I am using the SQLEXECDirect statment to put the showplan_text on and then calling my store procedure.  My store procedure has a temp table in it.   when I execute the SQLExecDirect statement, I get this error - Invalid object name #temptable.   It seems that it does not like the temptabe.  My temp table in my store procedure is created by select * into #temptable...   If I replace this with the statement Create #temptable...   everythign works.  If I run up the 2012 Management studio and set the showplan on in the new query and run the showplan on the store procedure, I do get the showplan back in the result set, but in the message set, I get the error above.   All the research I did says that you either have to replace the select into with the create table, which we can not do, or actually execute the store procedure.  We do not want to do that either since we just want the showplan.   Is there anyway thru C++ that I can skip that message set informational error and get to the result set which has the showplan. 

Basically the mdv_udr_monitorSCOTT is the store procedure.  that is the line that gives me the error. 

sample code

retcode = SQLExecDirect(hstmt, (SQLCHAR*)"SET SHOWPLAN_XML ON", SQL_NTS);

retcode = SQLExecDirect(hstmt,(SQLCHAR *)"mdv_udr_monitorSCOTT",SQL_NTS);

 int i=1;   

while ( SQLGetDiagRec( SQL_HANDLE_STMT,hstmt, i, SqlState,  &NativeError, SqlMsg,  sizeof(SqlMsg) + 1,         &MsgLen                         ) == SQL_SUCCESS )

{      

cout<< "SQLSTATE"<< SqlState<<endl  ;      

cout<< "Native Error Code "<< NativeError <<endl ;      

cout<< "m"<< SqlMsg<<endl ;     

  i++ ;

}

Answer 3

Hello,

I will follow the Iric's advice : i will move your thread to the vclanguage forum ASAP.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Hello,

Move done. I hope you to find a quick and full answer to your problem on this forum.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

Hi SAR88,

According to my research, I found you have posted another thread in MSDN Forum, which is almost same as this one. And this is its link: http://social.msdn.microsoft.com/Forums/en-US/vclanguage/thread/4ecc6255-3611-4a96-9375-74fb101184e2. I'd like to merge them to keep in the same topic.

By the way, please do not post the same question more than once in further.

Thanks for your understanding.
Best regards,

---

Helen Zhao [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Hello Helen,

I believed to have checked whether the OP has already created another similar thread on vclanguage forum before doing the move. I am sorry that you had to do the merge ( as moderator on the SQL Server Data Access Forum i may do a merge between forums ). As i should have to do a merge, i hope you will accept my most sincere excuses for my error.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.