469,148 Members | 1,257 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

Make and save a blob on the fly in stored proc

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!

Jul 7 '06 #1
5 1906
natzol (na******@thehumanequation.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 7 '06 #2
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
............... ??

Jul 10 '06 #3
natzol (na******@thehumanequation.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 10 '06 #4
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."

Jul 10 '06 #5
natzol (na******@thehumanequation.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 10 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Matt | last post: by
3 posts views Thread by Hrvoje Voda | last post: by
reply views Thread by Biztalk Migration | last post: by
9 posts views Thread by matt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.