Add some code to your question. Are we supposed to guess what your program does and where it fails just from one error message?

--shmem

_($_=" "x(1<<5)."?\n".q·/)Oo.  G°\        /
                              /\_¯/(q    /
----------------------------  \__(m.====·.(_("always off the crowd"))."·
");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}

"Are we supposed to guess what your program does and where it fails just from one error message?"

It's quite clear that there is a syntax error on Line 17

*grin*

http://support.microsoft.com/kb/831997/

The problem is with the stored procedure, it's difficult to know for sure without seeing the code but it is likely that you have PRINT statements in the procedure or are attempting to return multiple datasets.

/J\

It is difficult to correctly diagnose problems when we do not know the context. Please provide the following:

1. What operating system are you using
2. What database driver are you using (e.g. DBD::Sybase with freetds, DBD::ODBC with iodbc)
3. The smallest working implementation of the perl code that demonstrates the problem
4. The smallest working implementation of the SQL being executed that demonstrates the problem

sorry for inconvinience

Im using operating system: Windows XP

database driver: DBD::ODBC

i want give customer name as input to the procedure (Ex-procedure name) and it checks whether the name is already present in the table and insert the customer name if it is not in the table.

here is sample code

#!/usr/local/bin/perl
use DBI;
use DBD::ODBC;
my $dbh = DBI->connect("dbi:ODBC:dsnentry", '', '', {PrintError => 0})
+;
die "Unable for connect to server" unless $dbh;
$dbh->{odbc_SQL_ROWSET_SIZE} = 2;
$query="Exec ex 'habibrahman'";
my $result=$dbh->prepare($query) or print "cannot prepare" ;
$result->{LongReadLen}=512*1024;
$result->{LongTruncOk}=1;
$result->execute() or print $result->errstr();
[download]

and here is my procedure

create procedure ex (@name varchar(200))
as
declare @cust_id int
where cust_name=@name
if(@cust_id='')
begin
insert into x(cust_name) values(@name)
end
GO
[download]

Edited by planetscape - added code tags

Read more... view votes (25 Bytes)

The stored procedure isn't valid in ms sql 2000, because of this line:

where cust_name=@name
[download]

Perhaps you intended this?

select @cust_id = cust_id from x where cust_name=@name
[download]

I tested the perl and SQL you posted with the above modification and experienced the same problem when run from Windows XP using perl 5.8.8, dbd::odbc 1.13. I do not experience this problem on openbsd using perl 5.8.4, dbd::odbc 1.13, and iodbc.

Looking into it further the issue is this line:

$dbh->{odbc_SQL_ROWSET_SIZE} = 2;
[download]

With that line I get the error, without it I don't. Please review DBD::ODBC and check if this option is what you want.

By the way - when testing for the non-existence of a record in sql server you should check 'IS NULL', not compare to an empty string. Like this:

if(@cust_id IS NULL)
[download]