 07-03-2008, 2:15 AM
|
nigtman
Joined on 10-28-2005
Philippines
Posts 5
|
sql stored procedure execution (Export to excel)
|
|
|
|
|
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
|
|
|
|
|
Report
|
|
|
|