What exactly are the errors you get?
I don't do a whole lot with MS SQL (or DBI:ODBC for that matter), as my work
with DBI is confined mostly to MySQL, so I could be way off on this, but you
may find that you have better luck if you break up that statement you're
preparing into multiple queries. In any case (and this is just a general
statement for anyone getting errors), post up those errors, it'll help us to
more easily give you accurate debugging information.
Brian McCain
PageMasters Internet Group
----- Original Message -----
From: "Adam Peterson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 31, 2002 12:18 PM
Subject: DBI MS SQL Server Question
| hi,
|
| i can execute my query in MS sql query analyzer fine. when i run it in my
| script, i get errors.
|
| script:
| # connect to database
| $dbh = DBI->connect("dbi:ODBC:$dbname", $user, $passwd) or die
| $dbh->errstr;
|
| # set data size and ok to truncate
| $dbh->{LongReadLen} = 10000;
| $dbh->{LongTruncOk} = 1;
|
| $sth = $dbh->prepare("
| use ecmaster
| declare @type varchar(50)
| declare @type1 varchar(50)
| declare @from datetime
| declare @to datetime
|
| set @type1 = 'rpt'
| set @type = 'c0.asc'
| SET @from = getdate()-1
| SET @to = getdate()+1
|
| SELECT distinct filename, status
| FROM MASTERFILE MF JOIN MASTERFILEHISTORY MFH ON MF.masterfileid
| =mfh.masterfileid
| WHERE
| ( message like '%Error running TranslationScript%' or message like
| '%Error running InitialRoutingScript:%' or message like 'Error importing
| report data%')
| and FILENAME NOT LIKE '%TST-%' and createdtime >= @from and createdtime
| <= @to
| and (filename like '%' + @type + '' or filename like '%' + @type1 + '')
| and ascii(substring(filename,Len(filename),1)) = 116
| ");
|
| $sth->execute;
|
| while(@now = $sth->fetchrow_array)
| {
| $file_name = $now[0];
| $status = $now[1];
| print "$file_name\t$status\n";
| }
|
|
| ######### disconnect
|
| $sth->finish;
| $dbh->disconnect;
|
|
| error:
| C:\>perl\scripts\collab_crash.pl
| DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
| Server]Lin
| e 3: Incorrect syntax near '('. (SQL-37000)
| [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
| keyword
| 'and'. (SQL-37000)
| [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
| prepare
| d. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at
| C:\perl\scripts\collab_cras
| h.pl line 39.
| DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
| executi
| ng err=-1) at C:\perl\scripts\collab_crash.pl line 41.
|
|
| can anyone tell me why this is happening? and how it can be fixed?
|
| thanks!
| -adam
|
|
| __________________________________________________
| Do you Yahoo!?
| HotJobs - Search new jobs daily now
| http://hotjobs.yahoo.com/
|
