I want to export a stored procedures query results, that will be run on a daily basis to a Microsoft excel file.
I can do this through ISQL with the 32 bit version using the following code: SELECT * FROM SalesOrders; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c:\test\sales.xls; READONLY=0' INTO "newSalesData";
From the example in DocCommentXchange.
But on a 64 bit Sybase database, can I compile the stored procedure to use 32 bit?
I am aware that I can do this as a csv file, but was wondering whether it could be done to Excel with a Microsoft 32-bit driver on a 64 Bit Server.
asked 11 Aug '15, 21:59
Ah, the pitfalls of quoting:
The following works for me when run against the SA16 demo database (note, I'm using the SQLANY16 environment variable and the 64-bit binary here, and are using the "2>" stderr redirection):
The call creates a CSV file and an empty MyTest.log file.
In case your command contains an error (say, you scripted the table name in the SQL statement wrong), the via "2>" generated log file will contain an error message:
I still would think running the UNLOAD statement without xp_cmdshell would be easier. When using xp_cmdshell, it might also be easier to put the command in a batch file and make xp_cmdshell run that.
Here's a short code snippet of how to check for errors (and you would usually delete the log file afterwards):
begin declare strLog long varchar; declare bSuccess bit = 0; call xp_cmdshell('""%SQLANY16%\\Bin64\\dbisql" -q -c "UID=DBA;PWD=sql;Server=demo16;Database=demo" UNLOAD select * from Employees TO ''c:\\employee_data.csv'' 2> C:\\MyTest.log"', 'no_output'); set strLog = cast(xp_read_file('C:\\MyTest.log') as long varchar); if strLog = '' then set bSuccess = 1; end if; select bSuccess, strLog; end;
This returns "1, ''" in the above case but might return the following if you spelt the table wrong: