|
|
Posted by natzol on 10/10/53 11:52
We do use SQL 2000 + VB.NET.
There is a way to create a file via O-SQL and drop it to the server
folder. But how to upload(save) this file content into the table via
stored proc? Is that possible at all?
--======================================================
--1 step: run the query (Northwind database sample query)
select * from dbo.employees
--======================================================
--2nd step: make a file stored on the server:
DECLARE @trenutniRed varchar(30),
@tableRow1 varchar(3000),
@sql varchar(300),
@firstColumnName varchar(30),
@fileURL varchar(1000),
@columnNumber varchar(30),
@fs int,
@ole int,
@file int,
@TmpStr1 varchar(200)
-- clean up old
SELECT @sql = 'del '+ @fileURL
EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT
EXEC master..xp_cmdshell @sql, NO_OUTPUT
EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @fileURL,
8, 1
-- make a local file
DECLARE SysKursor INSENSITIVE SCROLL CURSOR
FOR select * from dbo.employees
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @tableRow1
WHILE @@Fetch_Status = 0
BEGIN
EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @tableRow1
FETCH NEXT FROM SysKursor INTO @tableRow1
END
CLOSE SysKursor
DEALLOCATE SysKursor
EXECUTE @ole = sp_OADestroy @file
EXECUTE @ole = sp_OADestroy @fs
--======================================================
3rd step: load file content into the table column text/image
............... ??
Navigation:
[Reply to this message]
|