I have some code that I need to execute from withing SQL Server and it is written in TSQL. The short version is that there are several sql statements and are all being executed at one time, back-to-back, with one query and this works fine with Microsoft Query Analyzer but not with DBD.

For instance, if I have the following query (which is valid in Microsoft Query Analyzer), how would I run it through DBD:

use Northwind

declare @d datetime
declare @c int

select @d = getdate()
print @d
print @d - 365*8

select @c = count(*) from Orders where ShippedDate > @d - 365*6
print @c
[download]

And my Perl code:

print "=== processing $server server ===\n";

my $DSN = "driver={SQL Server};Server=$server;database=master;uid=;pwd
+=;";

my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

$tsql = <<'END_QUERY';

use Northwind

declare @d datetime
declare @c int

select @d = getdate()
print @d
print @d - 365*8

select @c = count(*) from Orders where ShippedDate > @d - 365*6
print @c

END_QUERY

# create a modified sp_helprotect that will output results in table ve
+rsion
$sth = $dbh->prepare ($tsql);
$sth->execute();
$out = "";

while (@row = $sth->fetchrow_array()) {         # this is line 44
    print $server,':',join(':',@row),"\n";
}

$sth->finish();

$dbh->disconnect();
[download]

Here is the result if I run it through Perl DBD:

C:\Data\Scripts\sql>tsql_test.pl
=== processing new-borg server ===
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement current
+ly executing err=-1) at C:\Data\Scripts\sql\tsql_test.pl line 44.

C:\Data\Scripts\sql>
[download]

Ed

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.