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 |
|
vitoco
Starting Member
22 Posts |
 Posted - 2008-02-29 : 10:29:48
|
| I´m wriing a small perl script that connects to SQL Server through ODBC, using DBI and DBD-ODBC modules.I can get data from a query, but I cannot get the execution plan for it... instead, I got "[Microsoft][ODBC Driver Manager] Function sequence error".Using Profiler, I can see both "set showplan_xml on" and my query as "SQL BatchCompleted" events on target database. The same happens when I use "set showplan_text on".Is it possible to get an execution plan through ODBC?Thanks!!!++Vitoco |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2008-02-29 : 21:56:50
|
| Is this 2005? If so, you should be able to resolve the plan xml using the batch handle and this dmv: http://technet.microsoft.com/en-us/library/ms189747.aspxLooking at BOL, it mentions that the "set showplan_xml on" must be the only statement in the batch or you will get error. Not sure if you read that as well.Please reply back to this thread, im curious the outcome.Nathan Skerl |
 |
|
|
vitoco
Starting Member
22 Posts |
Posted - 2008-03-03 : 10:49:10
|
Yes, version is SQL Server 2005 SP2.Both statements were sent one at a time, but failed on the "execute" method for the analyzed query, the "prepare" was OK.UPDATE: while I was writing this answer, changed some debug lines of my perl code using DBI, just to be sure of what I was about to say, and I got the execution plan I wanted  I think that the magic was done by setting the "odbc_exec_direct" attribute and using "do" for the showplan statements. I also removed an error handler callback function (I read somewhere that execution plans should be obtained as a system message, like the number of rows processed and the DBCC results).Just for the record, this is the cleaned test code:#!perluse strict;use DBI;my $dbh = DBI->connect( "DBI:ODBC:Driver={SQL Server};server={MYSERVER};database={MYDB};", "MYUSER", "MYPASS", { RaiseError => 1, AutoCommit => 0 });$dbh->{LongReadLen} = 1024000;$dbh->{odbc_exec_direct} = 1;my $sth;my @ary;$sth = $dbh->do("set showplan_xml on");$sth = $dbh->prepare("select MYFIELDS from MYTABLE");$sth->execute;while (@ary = $sth->fetchrow_array) { print "@ary\n"; # <- XML PLAN}$sth->finish;$sth = $dbh->do("set showplan_xml off");$dbh->disconnect;BTW, it's very interesting the info at the page you link. This opens another front to get info for my case study. Thanks!  ++Vitoco |
|
|
|
|
|
|
|
|
SHOWPLAN through ODBC