Code is vb6.
myRSATT.Open mySQL2Text, myConnATT, adOpenStatic, adLockOptimistic mySQL2Text = "unload SELECT NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, EMAIL,CUCD, INVOICE, INVDATE, INVTOTAL FROM EMAIL_LIST " & _ "Where ((INVTOTAL > '" & LowDollars & "') and (INVTOTAL < '" & HighDollars & "')) GROUP BY NAME, CUCD, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, EMAIL, INVDATE, INVOICE, INVTOTAL " & _ " TO '" & SaveFileName & "' FORMAT ascii delimited by ';'"
SavedFileName is from popup savefile.showsave, it gets string :
"c:\documents and settings\username\my documents\email.txt"
it unloads the data on the server machine not the client.
When I use ISQL thru ODBC, I get the same result, the data file is created on the server. I have never encountered this before. I have built a lot in MS SQL (not good or bad, I know it does work) with expected results, data file on machine program is run on.
Any suggestions? If you are in SOCal I will buy dinner.
That's exactly how UNLOAD works without CLIENT FILE: the file name is relative to the server:
http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-unload-statement.html "Because it is the database server that executes the statements, file-name specifies a file on the database server computer."
If you can't upgrade to use CLIENT FILE, you need to fetch the data in your ODBC app and then write it out to a local file in your app. If dbisql is a valid option, use
SELECT NAME, ADDRESS1, ... ; OUTPUT TO foo.txt
SELECT NAME, ADDRESS1, ... ># foo.txt
Or, with statistics:
SELECT NAME, ADDRESS1, ... >& foo.txt
Another option might be to unload to a file on a server then use xp_readfile to fetch the file contents. That's not a great approach though.
answered 25 May '12, 20:00
(Revised: See additional example at bottom.)
Here is what you used to run on MS SQL, with line breaks for readability:
"BCP.EXE ElectroPro.dbo.BuildFileAllOthers out " & """C:Documents and Settings" & AppLoginName & "My DocumentsItem Listing NAME.TXT""" & " -c -U" & """" & AppLoginName & """ -P" & """" & strpwd & """" & " -S" & "" & strsvr & ""
BCP.EXE is an MS SQL executable utility program ("Bulk Copy") that you launched from inside your application. It ran on the client workstation, and it connected over the network to the MS SQL database. According to the bcp docs at http://msdn.microsoft.com/en-us/library/ms162802.aspx you told it to dump the table ElectroPro.dbo.BuildFileAllOthers to the file C:Documents and Settings...NAME.TXT.
The -c says to use tab delimited character output with cr/lf line breaks.
The -U -P -S give the user id, password and server name to connect to.
You can do exactly the same thing by executing dbisql.exe, and having it run a combination of SELECT and OUTPUT statements. The SELECT statement is passed over the network connection to the SQL Anywhere, which returns the result set to dbisql. The OUTPUT statement is a special statement executed by dbisql, on the client workstation, rather than by the SQL Anywhere server. OUTPUT can be coded to do pretty much exactly the same thing as UNLOAD, with the difference that it is executed by dbisql on the client computer and thus writes its output to a local file.
Here is a sample table:
CREATE TABLE t1 ( pkey INTEGER, data VARCHAR ( 10 ) ); INSERT t1 VALUES ( 1, 'Hello' ); INSERT t1 VALUES ( 2, 'World' ); COMMIT;
Here is a dbisql.exe command line that runs OUTPUT with tab-delimited output:
"C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'select_output.txt' DELIMITED BY '\X09';
Here's what the output file looks like:
1 'Hello' 2 'World'
For more about OUTPUT see the V10 Help: http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-output-statement.html
For more about dbisql see http://dcx.sybase.com/index.html#1001/en/dbdaen10/da-dbisql-interactive-dbutilities.html
Note that dbisql accepts multiple SQL statements separated by semicolons on the command line, but the V10 Help doesn't admit to that :)
If the SQL commands get too long and funky to code on the dbisql command line, you can put them inside a *.SQL text file, and have dbisql READ that file and execute it:
"C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" READ ENCODING Cp1252 "C:\projects\$SA_templates\run\dbisql\select_output.sql"
The "ENCODING Cp1252" is optional, but like waving a dead chicken over the keyboard it solves some possible problems with READ.
Note that in this example, the full path for the select_output.sql file is provided on the command line.
Here is what select_output.sql looks like; note that the OUTPUT command also specifies the full path for the select_output.txt file:
SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\projects\$SA_templates\run\dbisql\select_output.txt' DELIMITED BY '\X09';
Again, here is what select_output.txt looks like, same as before:
1 'Hello' 2 'World'
It might not help (because the filespec is coming from user input) but UNC filespecs are often used with LOAD and UNLOAD statements to point to the client computer:
where server-name is the computer name of the client computer, share-name is the name of a share on the client computer, and directory-filename is the filespec beneath the share; e.g.,
Another reason it might not help is if the SQL Anywhere server is running as a service and it doesn't have file access rights to the network and/or client file.