Make and save a blob on the fly in stored proc

[natzol]

Is there a way to run query, make file out of query (txt,csv or xls -
for example) and save it as a blob into the table column type
Image/Text?

Any thoughts would be greatly appreciated.

Thanks!

[Erland Sommarskog]

Re: Make and save a blob on the fly in stored proc

natzol (nataliaz@thehu manequation.com ) writes:

Is there a way to run query, make file out of query (txt,csv or xls -
for example) and save it as a blob into the table column type
Image/Text?

If you are on SQL 2005 you can create an XML document and save it a
column of the xml datatype or possibly nvarchar(MAX). (Don't use
image/text on SQL 2005 in new code, as these types have been deprecated.)

On SQL 2000, the answer is no.

If you describe the real business problem, we might be able to provide
alternate solutions.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[natzol]

Re: Make and save a blob on the fly in stored proc

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),
@firstColumnNam e 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.File SystemObject', @fs OUT
EXEC master..xp_cmds hell @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
............... ??

[Erland Sommarskog]

Re: Make and save a blob on the fly in stored proc

natzol (nataliaz@thehu manequation.com ) writes:

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?

I need to ask: what is the purpose of this? It sounds to me like a very
funny thing to do. Thus, I suspect that there is a better solution, if I
only knew what business problem you are trying to solve.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[natzol]

Re: Make and save a blob on the fly in stored proc

ok, the last try:

".NET application must provide the functionality to save output files
from stored procs (reports), that are running either inside the jobs or
either as a single call of the web-user, into the database as blobs."

[Erland Sommarskog]

Re: Make and save a blob on the fly in stored proc

natzol (nataliaz@thehu manequation.com ) writes:

ok, the last try:
>
".NET application must provide the functionality to save output files
from stored procs (reports), that are running either inside the jobs or
either as a single call of the web-user, into the database as blobs."

You keep repeating yourself. The only clue I get when I read this is
that it appears to be take from a requirement specification. What I
wanted to know was the business reason for doing this.

But my reading of the above, does not match well what you are trying
to do. Having a stored procedure that writes data to a file, is just
plain silly. You need to run a cursor over the result set, and performance
will be ugly.

The reasonable interpretation of the above is that the client should
call the stored procedure, produce a report on file, and then save that
file somewhere, for instance in the database.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx