>>A way around :) Not a clear one but might be creating a service that runs with a trusted account (sysadmin,bulkadmin) and checks for txt files ready to be uploaded. No sample as I didn't even done or tested, off the top of my head.
>
>I thought about that. Let users simply post a file with the keys up to a collection area and have a trusted app do the importing. Need to master the SQL stored procedures before I consider that route.
>
>>
>>OpenRowset is the one that looks promising to me. Combined with insert into/select or insert into/exec it might work fast. Unfortunately couldn't locate my own samples right now :(
>>
>>There is also SQLDMO Bulkcopy object and I don't know if that needs sysadmin/bulkadmin role (it's good to be a sysadmin:)
>>
>I shall look into that one. Haven't come across it before.
>
>>Thousands of users ? and each with thousands of recs upload or few per user ? If the latter than conventional ways might be enough. A very busy server you'll have I think :)
>
>Not really - the key upload is a one off exercise for each user when they initialise their software. Thereafter they will use one of the keys each time they send an audited or encrypted email. On average, over the course of a year or so, each user will probably only use the server a few times a day at most.
>
>Thanks for all your help. Most valuable.
>
>Harry
Harry,
I found my sample code :)
Local lnHandle, lcDatabaseName
lcDatabaseName = "myBulkTest"
lnHandle=SQLStringConnect('DRIVER=SQL Server;SERVER=server;Trusted_connection=Yes')
If SQLExec(lnHandle, "create database "+lcDatabaseName) < 0
Do errHand
Return
Endif
mySQLExec(lnHandle, "use "+lcDatabaseName)
mySQLExec(lnHandle, ;
" create table myTable"+;
" (line_no int not null,"+;
" order_id int not null,"+;
" product_id int not null,"+;
" unit_price smallmoney not null,"+;
" quantity int not null)" )
lnSeconds = Seconds()
mySQLExec(lnHandle, ;
"insert into myTable"+;
" SELECT * "+;
" FROM OPENROWSET('VFPOLEDB.1', "+;
" '"+_samples+"data\testdata.dbc';'';'',"+;
" 'select * from orditems') " )
? Seconds() - lnSeconds
mySQLExec(lnHandle, "select * from myTable", "myBulk")
SQLDisconnect(lnHandle)
Select myBulk
Browse
Function mySQLExec
Lparameters tnHandle, tcSQL, tcCursorName
tcCursorName = iif(empty(tcCursorName),'',tcCursorName)
If SQLExec(tnHandle,tcSQL,tcCursorName) < 0
Do errHand with tcSQL
Endif
Function errHand
Lparameters tcSQL
lcError=tcSQL+chr(13)
Aerror(arrCheck)
For ix=1 to 7
lcError = lcError+trans( arrCheck [ix])+ chr(13)
Endfor
Messagebox(lcError,0,'Error def.')
I checked it in SQL profiler. The insert part is a single batch. Duration was 7510 ms on Athlon 650, 192Mb RAM.
Cetin