sql stored procedure execution (Export to excel)

Databases and Drivers

sql stored procedure execution (Export to excel)

---

nigtman 07-03-2008, 2:15 AM
Hi all,

I was trying to export sql query result to excel using stored procedure

SQL procedure code :

Create Proc [dbo].[ESO_ExpPayAdv] @PayAdvHead Int as
EXEC sp_addlinkedserver 'PayAdv', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'D:\Wintas\Export\LoanAdvice1.xls',
        NULL, 'Excel 5.0'
declare @tsqlstmt varchar(1000), @QryStmt varchar(1000)
set @QryStmt = 'insert PayAdv...[Sheet1$] (SerialEmpNo,LastName,FirstName,MI,Amount,LoanID,LoanCode,LoanDesc)
SELECT cd.SERIALNO_EMPLOYEENO, cd.LastName, cd.FirstName, cd.Middlename, a.PAYMENTS, a.LOANID, lt.LOANCODE, lt.DESCRIPTION
FROM         PCGLive..CustomerDetails AS cd RIGHT OUTER JOIN
                      PCGLive..LOAN AS l ON cd.CustomerID = l.MEMBERCODE LEFT OUTER JOIN
                      PCGLive..LoanTypes AS lt ON l.LOANTYPE = lt.LTID RIGHT OUTER JOIN
                      PCGLive..PayAdvDetail AS a ON l.LOANID = a.LOANID
WHERE     (a.PAYADVHEAD = ''' + cast(@PayAdvHead as varchar(10)) +''')'

exec (@QryStmt)
EXEC sp_dropserver 'PayAdv'

but when I try to execute it on clarion 6.3 using the code below I'm getting some error :

MySqlFile{prop:sql} = 'NORESULTCALL ESO_ExpPayAdv (' & PMA:TranID &')'
Next(MySqlFile)

fileerror() returns : "Executing SQL directlly; no cursor"
error() returns : "Record not available"


Is there any problem with my sp? or the clarion command to call the sp.

Need help please