my $script = <<EOF;
declare \@intpos int
declare \@intuser int
declare \@dtlastpost varchar(30)
set \@intuser = (select userid from $dbname.dbo.users where name = '$u
+ser')
set \@intpos = (select top 1 permissionid from $dbname.dbo.security wh
+ere userid = \@intuser order by permissionid desc)
set \@dtlastpost = (SELECT top 1 PostDate FROM $dbname.dbo.Messages wh
+ere userid = @intuser order by postdate desc )
EOF
[download]

You don't show even a single line of Perl code, and yet your post's title blames the failure on one or two Perl modules.

Please read Before asking a database related question ..., then read the docs for DBD::ODBC, especially the bit about handling stored procedures.

I am sure that after that you could ask a better question.

that's because the problem script is only one of several in the PERL code overall, and the others work without any fuss. I've read up on DBI:ODBC and know there is a matter of how the module inteprets the sql script before it passes it over to the db.

However I thank you for the pointers about DBI (some pages I hadn't found before!) and will have a check through before I come back.

Do you mind showing us how the statement is wrapped in Perl and how you tried to execute it?

use DBI;
    use DBD::ODBC;
    $dbh->trace(2);
    my $dbh2 = DBI->connect('DBI:ODBC:'.$dbname, $dblogin, $dbpass)
        or die "[$dbname]  " . DBI->errstr ;
    $sql = qq~
    declare @intpos int
    declare @intuser int
    declare  @dtlastpost varchar(30)
    set @intuser = (select userid from [$dbname].[dbo].[users] where [
+name] = '$user')    
    set @intpos = (select top 1 permissionid from [$dbname].[dbo].[sec
+urity] where [userid] = @intuser order by permissionid desc)
    set @dtlastpost = (SELECT top 1  [PostDate] FROM [$dbname].[dbo].[
+Messages] where userid = @intuser order by postdate desc    )
    SELECT rtrim([Password])/*$settings[0]*/, rtrim([FirstName]) + ' '
+ + rtrim([LastName])/*$settings[1]*/,
     [Email]/*$settings[2]*/,''/*$settings[3]*/, [HomePage]/*$settings
+[4]*/, [Signature]/*$settings[5]*/, 
     [MsgsPosted]/*$settings[6]*/,  
case
    when @intpos = 1 then 'Administrator'
    else ''
end  /*$settings[7]*/,    '' /*$settings[8]*/,'' /*$settings[9]*/, '' 
+/*$settings[10]*/,
    ''    /*##'gender' => "$settings[11]"*/,
    '' /*##'usertext' => "$settings[12]"*/,
    '' /*##'userpic' => "$settings[13]"*/,
    
case  
    when datepart(mm,[FirstLogin]) <  10 then '0'+cast(datepart(mm,[Fi
+rstLogin]) as char(1))
    else  cast(datepart(mm,[FirstLogin]) as char(2))
end
+'/'+

case  
    when datepart(dd,[FirstLogin]) <  10 then '0'+cast(datepart(dd,[Fi
+rstLogin]) as char(1))
    else  cast(datepart(dd,[FirstLogin]) as char(2))
end
 +'/'+

right(cast(datepart(yy,[FirstLogin]) as char(4)), 2)
+ ' at ' +
case
    when datepart(hh,[FirstLogin]) <  10 then '0'+cast(datepart(hh,[Fi
+rstLogin]) as char(1))
    else  cast(datepart(hh,[FirstLogin]) as char(2))
end
+':'+
case
    when datepart(mi,[FirstLogin]) <  10 then '0'+cast(datepart(mi,[Fi
+rstLogin]) as char(1))
    else  cast(datepart(mi,[FirstLogin]) as char(2))
end
+':'+
case
    when datepart(ss,[FirstLogin]) <  10 then '0'+cast(datepart(ss,[Fi
+rstLogin]) as char(1))
    else  cast(datepart(ss,[FirstLogin]) as char(2))
end    /*        #'regdate' => "$settings[14]",*/
    , rtrim([City]) + ',' + rtrim([State]) + ',' + rtrim([Country])/*#
+'location' => "$settings[15]"*/
    ,
    '' /*        ##'bday' => "$settings[16]"*/,
    ''/*    ##'timeselect' => "$settings[17]"*/,
    ''/*    ##'timeoffset' => "$settings[18]"*/,
    [HideEmail]    /*#'hidemail' => "$settings[19]"*/,
    ''/*    ##'msn' => "$msnaddress"*/,
    ''    /*##'gtalk' => "$settings[32]"*/,
    ''    /*##'template' => "$new_template"*/,
    ''    /*##'language' => "$language"*/,
    
case  
    when datepart(mm,[LastLogin]) <  10 then '0'+cast(datepart(mm,[Las
+tLogin]) as char(1))
    else  cast(datepart(mm,[LastLogin]) as char(2))
end
+'/'+

case  
    when datepart(dd,[LastLogin]) <  10 then '0'+cast(datepart(dd,[Las
+tLogin]) as char(1))
    else  cast(datepart(dd,[LastLogin]) as char(2))
end
 +'/'+

right(cast(datepart(yy,[LastLogin]) as char(4)), 2)
+ ' at ' +
case
    when datepart(hh,[LastLogin]) <  10 then '0'+cast(datepart(hh,[Las
+tLogin]) as char(1))
    else  cast(datepart(hh,[LastLogin]) as char(2))
end
+':'+
case
    when datepart(mi,[LastLogin]) <  10 then '0'+cast(datepart(mi,[Las
+tLogin]) as char(1))
    else  cast(datepart(mi,[LastLogin]) as char(2))
end
+':'+
case
    when datepart(ss,[LastLogin]) <  10 then '0'+cast(datepart(ss,[Las
+tLogin]) as char(1))
    else  cast(datepart(ss,[LastLogin]) as char(2))
end          /*    #'lastonline' => "$settings[23]"*/, 
@dtlastpost
/*    #    'lastpost' => "$settings[24]" */,
    ''/*    ##'lastim' => "$settings[25]" */,
    ''/*    ##'im_ignorelist' => "$settings[26]"*/,
    ''/*    ##'im_notify' => "$settings[27]"*/,
    ''/*    ##'im_popup' => "$settings[28]"*/,
    ''    /*##'im_imspop' => "$settings[29]"*/,
    ''    /*##'cathide' => "$settings[30]"*/,
    ''    /*##'postlayout' => "$settings[31]"*/
     FROM [$dbname].[dbo].[Users] u 
      where [name] = '$user' ~; 
    my $sth2 = $dbh2->prepare($sql)
        or die "Couldn't prepare statement: " . $dbh2->errstr;
    $sth2->execute()             # Execute the query
        or die "Couldn't execute statement: " . $sth2->errstr;  
    $dbh->trace(0);
    @settings = $sth2->fetchrow_array();
[download]

Edit: g0n - code tags

$sql = qq~

try changing that to a single q like
$sql = q~

I doubt that it will help much, but try it anyway. You don't need to worry about perl misunderstanding @-strings as arrays inside your sql.

As it won't interpolate the variables you wanted it to interpolate anyomore, also change all the $settings[...] to a simple ? in all occurrences and add the Array to the excute statement like this:
$sth2->execute( @settings );

Cheers, Sören