Tim,

I am still having problems passing parameters to stored procs

BEGIN
 DECLARE Result CURSOR WITH RETURN FOR Stmt;
 BEGIN
   PREPARE Stmt FROM 'BACKUP DATABASE TTWNN AS ? TO ? TABLES ImpExModel';
   OPEN Result USING DBName, DirName;
 END;
END

Throws a db error 700 'Expected backup name but instead found ?' irrespective of whether I paramterise with "TestBackup" / 'C:\backups' or TestBackup / C:\backups

I have also tried

BEGIN
 DECLARE Query STATEMENT;
 PREPARE Query FROM 'BACKUP DATABASE TTWNN AS ? TO ? TABLES ImpExModel';
 EXECUTE USING DBName, DirName;
END

with the same result.

Peter

Peter,

<< Throws a db error 700 'Expected backup name but instead found ?'
irrespective of whether I paramterise with "TestBackup" / 'C:\backups' or
TestBackup / C:\backups >>

You can't use parameters in a BACKUP DATABASE statement.  You need to build
the statement dynamically:

BEGIN
   EXECUTE IMMEDIATE 'BACKUP DATABASE TTWNN AS "'+DBName+'" TO
'''+DirName+''' TABLES ImpExModel';
END

--
Tim Young
Elevate Software
www.elevatesoft.com

OK. Thanks Tim. I presume that in this instance DBName and DirName are delared variables in the proc?

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

<< You can't use parameters in a BACKUP DATABASE statement.  You need to build
the statement dynamically:

BEGIN
   EXECUTE IMMEDIATE 'BACKUP DATABASE TTWNN AS "'+DBName+'" TO
'''+DirName+''' TABLES ImpExModel';
END >>

Peter


NOt having written any SPs yet I would have thought IN parameters
Roy Lambert

Peter,

<< OK. Thanks Tim. I presume that in this instance DBName and DirName are
delared variables in the proc? >>

It's up to you - you can declare them as variables and assign them values
inside of the SP, or you can declare them as input parameters like Roy
suggests.

--
Tim Young
Elevate Software
www.elevatesoft.com