可以使用osql将执行结果存放到一个文件中:
如:
declare @sql varchar(200)
set @sql ='osql -S '+@@servername + ' -d master -E -Q" select * from sysdatabases" -o c:\result.txt'
print @sql
exec master.dbo.xp_cmdshell @sql
如:
declare @sql varchar(200)
set @sql ='osql -S '+@@servername + ' -d master -E -Q" select * from sysdatabases" -o c:\result.txt'
print @sql
exec master.dbo.xp_cmdshell @sql
SQL SERVER可以通过使用sp_makewebtask存储过程把查询出的结果生成为网页并保存到服务器上。因此,我把本地文件进行二进制编码后存到SQL SERVER的临时表中,然后再调用sp_makewebtask存储过程把“上传”的文件保存到服务器上。用到时再进行“下载”,即通过BULK INSERT把服务器上的文件导入到临时表中,然后再进行分析解码并保存到客户端。
注:本类在直接“下载”服务器上固有的文件时,如果文件较大,会丢失一部分数据。我正在查找原因。而对于我们“上传”到服务器上的文件,则不存在此种问题。DEFINE CLASS ServerFileAccess AS Custom PROTECTED ConnectString
PROTECTED ConnectHandle
PROTECTED Connected
PROTECTED FLAG_START
PROTECTED FLAG_END
PROCEDURE Init
THIS.ConnectString = ""
THIS.ConnectHandle = -1
THIS.Connected = .F.
THIS.FLAG_START = "<<START>>"
THIS.FLAG_END = "<<END>>"
ENDPROC
PROCEDURE Destroy
IF THIS.Connected = .T.
THIS.Disconnect()
ENDIF
ENDPROC
PROCEDURE SetConnectString
LPARAMETERS cServer, cDatabase, cUserId, cPassword
THIS.ConnectString = "Driver=SQL Server;" + ;
"Server=" + cServer + ";" + ;
"Database=" + cDatabase + ";" + ;
"Uid=" + cUserId + ";" + ;
"Pwd=" + cPassword
ENDPROC
PROCEDURE SetConnectHandle
LPARAMETERS nConnectHandle
THIS.ConnectHandle = nConnectHandle
ENDPROC
PROCEDURE Connect
PRIVATE nDispLogin
nDispLogin = SQLGETPROP(0, "DispLogin")
= SQLSETPROP(0, "DispLogin", 3)
IF THIS.ConnectHandle > 0
RETURN .T.
ENDIF
THIS.ConnectHandle = SQLSTRINGCONNECT(THIS.ConnectString)
= SQLSETPROP(0, "DispLogin", nDispLogin)
IF THIS.ConnectHandle > 0
THIS.Connected = .T.
RETURN .T.
ENDIF
RETURN .F.
ENDPROC
PROCEDURE Disconnect
PRIVATE lReturnValue
lReturnValue = .T.
THIS.ConnectString = ""
TRY
= SQLDISCONNECT(THIS.ConnectHandle)
CATCH
lReturnValue = .F.
ENDTRY
THIS.ConnectHandle = -1
Connected = .F.
RETURN lReturnValue
ENDPROC
PROCEDURE UploadFile
LPARAMETERS cClientFile, cServerFile
PRIVATE lReturnValue, nReturnValue, cTableName, cSQL, cDrop, cFileData, cRandKey
lReturnValue = .F.
nReturnValue = -1
cTableName = "##" + SYS(2015)
cSQL = [CREATE TABLE ] + ;
cTableName + ;
[ (RandKey char(8) NULL, FileData text NULL)]
cDrop = [DROP TABLE ] + cTableName
cFileData = THIS.FLAG_START + ;
STRCONV(FILETOSTR(cClientFile), 15) + ;
THIS.FLAG_END
cRandKey = SYS(3)
= SQLEXEC(THIS.ConnectHandle, cSQL)
cSQL = [INSERT INTO ] + cTableName + ;
[ (RandKey, FileData)] + ;
[ VALUES] + ;
[ ('] + cRandKey + [', N'] + cFileData + [')]
nReturnValue = SQLEXEC(THIS.ConnectHandle, cSQL)
IF nReturnValue < 0
= SQLEXEC(THIS.ConnectHandle, cDrop)
RETURN lReturnValue
ENDIF
cSQL = [EXECUTE master..sp_makewebtask ] + ;
["] + cServerFile + [", ] + ;
["SELECT FileData FROM ] + cTableName + ;
[ WHERE RandKey = '] + cRandKey + ['"]
nReturnValue = SQLEXEC(THIS.ConnectHandle, cSQL)
IF nReturnValue > 0
lReturnValue = .T.
ENDIF
= SQLEXEC(THIS.ConnectHandle, cDrop)
RETURN lReturnValue
ENDPROC
PROCEDURE GetFileFromServer
LPARAMETERS cServerFile
PRIVATE cFileData, nReturnValue, cTableName, cSQL, cDrop, ;
cCurrentAlias, cAlias, i
cFileData = ""
nReturnValue = -1
cTableName = "##" + SYS(2015)
cSQL = [CREATE TABLE ] + ;
cTableName + ;
[ (FileData text NULL)]
cDrop = [DROP TABLE ] + cTableName
nReturnValue = SQLEXEC(THIS.ConnectHandle, cSQL)
IF nReturnValue < 0
RETURN cFileData
ENDIF
cSQL = [BULK INSERT ] + cTableName + ;
[ FROM '] + cServerFile + ['] + ;
[ WITH (ROWTERMINATOR = '\r')]
nReturnValue = SQLEXEC(THIS.ConnectHandle, cSQL)
IF nReturnValue < 0
= SQLEXEC(THIS.ConnectHandle, cDrop)
RETURN cFileData
ENDIF
cCurrentAlias = ALIAS()
cAlias = SYS(2015)
cSQL = [SELECT FileData FROM ] + cTableName
nReturnValue = SQLEXEC(THIS.ConnectHandle, cSQL, cAlias)
= SQLEXEC(THIS.ConnectHandle, cDrop)
IF nReturnValue < 0
RETURN cFileData
ENDIF
SELECT (cAlias)
GO TOP
SCAN
cFileData = cFileData + ;
IIF(ISNULL(FileData) = .T., "", ALLTRIM(FileData)) + ;
IIF(RECNO() = RECCOUNT(), "", CHR(13))
ENDSCAN
USE IN (cAlias)
IF EMPTY(cCurrentAlias) = .F.
SELECT (cCurrentAlias)
ENDIF
RETURN cFileData
ENDPROC
PROCEDURE GetUploadFileFromServer
LPARAMETERS cServerFile
PRIVATE cFileData, nStart, nLength
cFileData = ""
cFileData = THIS.GetFileFromServer(cServerFile)
IF EMPTY(cFileData) = .T.
RETURN cFileData
ENDIF
nStart = AT(THIS.FLAG_START, cFileData) + LEN(THIS.FLAG_START)
nLength = AT(THIS.FLAG_END, cFileData) - nStart
cFileData = SUBSTR(cFileData, nStart, nLength)
cFileData = STRCONV(cFileData, 16)
RETURN cFileData
ENDPROC
PROCEDURE DownloadFile
LPARAMETERS cServerFile, cClientFile
PRIVATE cFileData
cFileData = THIS.GetFileFromServer(cServerFile)
= STRTOFILE(cFileData, cClientFile)
RETURN .T.
ENDPROC
PROCEDURE DownloadUploadFile
LPARAMETERS cServerFile, cClientFile
PRIVATE cFileData
cFileData = THIS.GetUploadFileFromServer(cServerFile)
= STRTOFILE(cFileData, cClientFile)
RETURN .T.
ENDPROCENDDEFINE