Blobs in SQL Server

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...Connie (csawyer@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

I have a very quick sketch for this on
http://www.sommarskog.se/blobload.txt.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 15, 4:26 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Connie (csaw...@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

>
I have a very quick sketch for this onhttp://www.sommarskog.se/blobload.txt.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Here is some good information that I found in my search to complete
the above task, I am posting as this may help others working with
blobs:
In this article I want to show, how you can copy a single text or
image value into or out of SQL Server with textcopy.exe utility. You
can find this utility in the directory containing the standard SQL
Server EXE files (C:\Mssql\Binn by default for SQL Server 6.5 and C:
\Mssql7\Binn by default for SQL Server 7.0).

The Textcopy utility is not described in SQL Server Books Online, but
you can get its description by typing textcopy /? from the command
prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The
value
is a specified text or image 'column' of a single row (specified by
the
"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If
the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is
not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not
specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the
text or
image data. If 'database' is not specified, the
default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE
keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from
'file'.
/O Copy text or image value out of SQL Server into
'file'.
/K chunksize Size of the data transfer buffer in bytes.
Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.

You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of
textcopy utility:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (30),
@.whereclause varchar (40),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'textcopy /S ' + @.srvname +
' /U ' + @.login +
' /P ' + @.password +
' /D ' + @.dbname +
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause +
'" /F ' + @.filename +
' /' + @.direction
EXEC master..xp_cmdshell @.exec_str

This is the example to copy image into SQL Server database pubs, table
pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @.srvname = 'ServerName',
@.login = 'Login',
@.password = 'Password',
@.dbname = 'pubs',
@.tbname = 'pub_info',
@.colname = 'logo',
@.filename = 'c:\picture.bmp',
@.whereclause = " WHERE pub_id='0736' ",
@.direction = 'I'

BLOB fields - questions

Hi,

Ok, I am pretty new to BLOB's on SQL SERVER 200 so I need some answeres.

1. Can BLOB Fields hold .doc and/or .pdf documents?
2. If yes, can the contents of the documents be searchable.
3. If documents are updated do BLOB Fields lengtht change/grow dynamically?

I would apreciate any answers in this matter and if possible examples were I can get a better idea on how to configure the BLOB fieds on my DB and/or documentation regarding BLOB's.

Thank you, Pepe.I am far from an expert in BLOB fields but here is my understanding of them. If there are any other gurus out there I will follow with interest as it is not something I have had cause to use/do yet.

1. Being Binary Large Objects I believe a BLOB field should be able to hold a pointer to a .doc or .pdf file.

2. I don't think you can search on the BLOB field as it is only a pointer to the actuall file. I seem to remember form somewhere though that you may be able to search them if it is full text indexed. (Don't quote me on that though)

3. As the table only stores a pointer to the BLOB the length of the field doesn't change when the file changes.

Sorry can't supply any examples for using or configuring BLOB fields|||If you want to learn more on this topic check the following link:

Brief tutorial on using text, ntext, and image data types (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)|||Hi,

I need to upload .doc files to a BLOB field.
Does anyone know a way to do this? I guess I could use plain Insert commands from SQL Query Analyzer. Alternately, I want to use VB.NET to create an interface for the user to upload this .docs and to retrieve them.

Thank you and I would appreciate some examples if possible.

Pepe.|||ok, since you're saying whatever the website shows is not enough, here is what i came up with when i needed something similar to store various types of documents.

blank pages inserted on exported pdf and tif format

Hi,
After I export the report to PDF or TIF format, the pages became double. It
inserts a blank sheet on every page of the report. Does anyone know how to
fix this issue? Any help is appreciate it.
Thanks,
LianneLianne,
I had that problem too. Some of your report items are wider than the page.
Look on the ruler above your report page and make sure that you do not have
any report items that go to the right more than the width of your page
(usually it's 8.5 inches).
Good luck.
"Lianne Kwock" wrote:
> Hi,
> After I export the report to PDF or TIF format, the pages became double. It
> inserts a blank sheet on every page of the report. Does anyone know how to
> fix this issue? Any help is appreciate it.
>
> Thanks,
> Lianne|||Not greater than the width of the page when considering your margins plus
the ruler.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:862C6173-C1F9-40B2-8C4E-1B1333BEA0ED@.microsoft.com...
> Lianne,
> I had that problem too. Some of your report items are wider than the page.
> Look on the ruler above your report page and make sure that you do not
> have
> any report items that go to the right more than the width of your page
> (usually it's 8.5 inches).
> Good luck.
> "Lianne Kwock" wrote:
>> Hi,
>> After I export the report to PDF or TIF format, the pages became double.
>> It
>> inserts a blank sheet on every page of the report. Does anyone know how
>> to
>> fix this issue? Any help is appreciate it.
>>
>> Thanks,
>> Lianne|||You will also need to make sure that your report respect the following:
Body:Width > (Report:PageWidth - Report:LeftMargin -
Report:RightMargin).
A similar restriction exists for Body:Height and Report:PageHeight.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lianne Kwock" <LianneKwock@.discussions.microsoft.com> wrote in message
news:509E186D-CBBD-4725-B46C-47F175AEAA6F@.microsoft.com...
> Hi,
> After I export the report to PDF or TIF format, the pages became double.
> It
> inserts a blank sheet on every page of the report. Does anyone know how
> to
> fix this issue? Any help is appreciate it.
>
> Thanks,
> Lianne

blank pages

I have two tables in my report. The second table spans two pages, because of
this, in the pdf output, the first table has blank pages after each page.
Anyone could help me on this?
Thanks
AngeloI solved this myself by putting the second table into a list
"Mathi" wrote:
> I have two tables in my report. The second table spans two pages, because of
> this, in the pdf output, the first table has blank pages after each page.
> Anyone could help me on this?
> Thanks
> Angelo

Blank Page while exporting output to pdf

Hi,
I am exporting the output of Reporting Services to a pdf file. There is
always a blank page after every page. How can I remove it. Please help me
to resolve this.
Thanks.
AnujHi,
Check the properties of the objects on the report. In the layout section you
will find PageBreakAtEnd and PageBreakAtStart properties, make sure these are
set correctly or you may end up with blank pages where you don't want them.
Although this is unlikely to be the problem if the report displays fine in
HTML or other formats.
Hope it helps.
Ben Lee
"Anuj" wrote:
> Hi,
> I am exporting the output of Reporting Services to a pdf file. There is
> always a blank page after every page. How can I remove it. Please help me
> to resolve this.
> Thanks.
> Anuj|||I reckon that you're using too much space (across) on your report when
building it in the designer.
Say your report is regular portrait with width of 21cm and height of 29.7,
and 2.5cm left and right margin (you'll find these settings in the menu
option Report -> Report Properties -> Layout), check that the width of the
report area you're working with in your designer is not greater than the Page
Width - (Left Margin + Right Margin). If it is then you'll get those blank
pages.
"Anuj" wrote:
> Hi,
> I am exporting the output of Reporting Services to a pdf file. There is
> always a blank page after every page. How can I remove it. Please help me
> to resolve this.
> Thanks.
> Anuj

Blank page when exported to PDF

Hi all,

I have this report which has one table and 2 matixes one below the other. The 2 matrixes are very big matrixes (i mean they are wide enough to spill over to the next page). I have "PageBreakatStart" true for the 2 matixes.

Here is my problem. when i export this report to PDF, i get a blank page after the table. If i get rid of "PageBreakatStart", there is no blank page but matrix1 start right after the table which i don't want. can somebody tell me how to eliminate that blank page? Hope my explanation makes sense.

Hey rsri... what did you do to get around this problem?|||I am still looking for the answer. I did not get around this problem yet.

blank page in pdf report

When I export my report to PDF, I get a blank page that shows up. The
header and footer are on the second page, but the body is blank. There
isn't enough data for a second page. Why is one being created? Any ideas?Most likely, something in your footer is touching one or more of your
margins. Make sure everything is inside the margins and you should be
good.

blank page between the main report and a subreport when exporting to PDF.

When I create a report that has imbedded subreports there are occasions
when I get a blank page between the main report and a subreport, this
occurs when exporting to PDF. From what I have observed; if a
subreport contains more rows of data than can fit on the same page as
the main report then a page break occurs a blank page is inserted and
then the subreport comes out on the subsequent page. This behavior will
not be repeated until the same condition occurs again.When the result
sets from the main report and subreport can fit on one page there is no
problem in exporting to PDF. In HTML format I get the report as
expected with no blank pages..PDF rendering is picky. You have to make sure that the body width does not
exceed, not even by 1 pixel, the (page width - (left margin + right
margin)).
--
Adrian M.
MCP
"Tom" <tom_barnard@.aotx.uscourts.gov> wrote in message
news:1112137446.997763.245800@.l41g2000cwc.googlegroups.com...
> When I create a report that has imbedded subreports there are occasions
> when I get a blank page between the main report and a subreport, this
> occurs when exporting to PDF. From what I have observed; if a
> subreport contains more rows of data than can fit on the same page as
> the main report then a page break occurs a blank page is inserted and
> then the subreport comes out on the subsequent page. This behavior will
> not be repeated until the same condition occurs again.When the result
> sets from the main report and subreport can fit on one page there is no
> problem in exporting to PDF. In HTML format I get the report as
> expected with no blank pages..
>|||Adrian,
We have checked the body width and found no problem there. Could
something else be causing this?
Thanks,
Tom|||This same situation happened to me. I found that two of the subreports had
matrices in them with extra white space to the right of the matrix. The extra
pages were eliminated when I removed this white space.
I hope this helps!
-Jason
"Tom" wrote:
> When I create a report that has imbedded subreports there are occasions
> when I get a blank page between the main report and a subreport, this
> occurs when exporting to PDF. From what I have observed; if a
> subreport contains more rows of data than can fit on the same page as
> the main report then a page break occurs a blank page is inserted and
> then the subreport comes out on the subsequent page. This behavior will
> not be repeated until the same condition occurs again.When the result
> sets from the main report and subreport can fit on one page there is no
> problem in exporting to PDF. In HTML format I get the report as
> expected with no blank pages..
>

Blank browser window when exporting to PDF

Hi:
Is there any way to avoid the blank browser window we see when exporting a
report to pdf?
I'll prefer to download the pdf on the same window to save it to the disk,
or to open it on another browser window using the acrobat reader plugin, but
what reporting services is doing is sending a request to download the report
on a new window, so after finishing the download, the user has to close this
window.
Any solutions?
Thanks,
Daniel Bello UrizarriI do not know what happens to you, here is what happend with my browser
(IE7.0, but it behaved the same when I used IE6):
1. In report viewer, choose export to PDF (or any other exporting format)
and click "Export";
2. A blank Browser window pops up followed by "File Download" dialog box
with button "Open", "Save", and "Cancel";
3.
a. If you choose "Open", the report is opened inside the new browser
window;
b. If you choose "Save" or "Cancel", the newly opened browser CLOSES
automatically. Of course, when you choose "Save", "Save File" dialog box
pops up while the blank browser window closes.
It must be something wrong with your browser/or setting being changes, such
as certain type of client-side script were disabled..., if it does not close
the blank window automatically.
"Daniel Bello" <dburizarri@.yahoo.es> wrote in message
news:eZ0bdYioHHA.5052@.TK2MSFTNGP04.phx.gbl...
> Hi:
> Is there any way to avoid the blank browser window we see when exporting a
> report to pdf?
> I'll prefer to download the pdf on the same window to save it to the disk,
> or to open it on another browser window using the acrobat reader plugin,
> but what reporting services is doing is sending a request to download the
> report on a new window, so after finishing the download, the user has to
> close this window.
> Any solutions?
> Thanks,
> Daniel Bello Urizarri
>|||Norman:
In my PC (and most of the others in my company) the browser window never
gets closed. If I click "Open", an instance of the Acrobat Reader standalone
application is started. If I click on "Save" or "Cancel", after the file
download dialog closes, the browser window remains there. I have IE 6 and
Acrobat Reader 7.
Do you have any ideas about where to look on the browser configuration to
change this behavior?
Thanks,
Daniel Bello Urizarri.
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:%23WJPd6ioHHA.3880@.TK2MSFTNGP04.phx.gbl...
>I do not know what happens to you, here is what happend with my browser
>(IE7.0, but it behaved the same when I used IE6):
> 1. In report viewer, choose export to PDF (or any other exporting format)
> and click "Export";
> 2. A blank Browser window pops up followed by "File Download" dialog box
> with button "Open", "Save", and "Cancel";
> 3.
> a. If you choose "Open", the report is opened inside the new browser
> window;
> b. If you choose "Save" or "Cancel", the newly opened browser CLOSES
> automatically. Of course, when you choose "Save", "Save File" dialog box
> pops up while the blank browser window closes.
> It must be something wrong with your browser/or setting being changes,
> such as certain type of client-side script were disabled..., if it does
> not close the blank window automatically.
> "Daniel Bello" <dburizarri@.yahoo.es> wrote in message
> news:eZ0bdYioHHA.5052@.TK2MSFTNGP04.phx.gbl...
>> Hi:
>> Is there any way to avoid the blank browser window we see when exporting
>> a report to pdf?
>> I'll prefer to download the pdf on the same window to save it to the
>> disk, or to open it on another browser window using the acrobat reader
>> plugin, but what reporting services is doing is sending a request to
>> download the report on a new window, so after finishing the download, the
>> user has to close this window.
>> Any solutions?
>> Thanks,
>> Daniel Bello Urizarri
>

black rectangles appearing in PDF

I have suddenly started getting black rectangles appearing on the right hand
side of PDF output.
There are no rectangles or other objects in this area of the report.
Is this a known bug?By any chance, have you included column 2 as well, and a border, then you
will get this rectangles. check in the properties for column and the border
line for left,right etc... Just to make sure you have not done these things.
Amarnath
"adolf garlic" wrote:
> I have suddenly started getting black rectangles appearing on the right hand
> side of PDF output.
> There are no rectangles or other objects in this area of the report.
> Is this a known bug?|||Just to add, the rectangle is solid black and is the same height as the table
appearing in the report.
I took a copy of the report, deleted the table and the problem went away,
but now I don't have a table...
If I move the table up and down and redeploy, recreate PDF the black
rectangle moves up and down, always in line with the table.
If I move the table left and right, the size of the rectangle changes, when
the table is pushed right up against the right hand side of the body the
rectangle disappears.
The table has dynamically hidden columns based on whether or not there is
data to show. The black rectangle has not previously been a problem.
I'm not sure what you mean by "have you included column 2". The report has
more than 2 columns but there is only one table present. There are no other
hidden or other objects to the right of the table.
"Amarnath" wrote:
> By any chance, have you included column 2 as well, and a border, then you
> will get this rectangles. check in the properties for column and the border
> line for left,right etc... Just to make sure you have not done these things.
> Amarnath
> "adolf garlic" wrote:
> > I have suddenly started getting black rectangles appearing on the right hand
> > side of PDF output.
> >
> > There are no rectangles or other objects in this area of the report.
> >
> > Is this a known bug?|||The size/position of the black rectangle is
top of the rectangle is level with the top of the table
bottom of the rectangle is level with the bottom of the table
right hand side of the rectangle is level with the right hand margin
left hand side starts where the table ends in the designer, where all
columns appear and none are hidden)
e.g.
M M
MTTTTTTTTTTTHHHBBBM
MTTTTTTTTTTTHHHBBBM
MTTTTTTTTTTTHHHBBBM
M M
where M is margin, T is table, H is hidden table, B is bloody annoying black
rectangle
I am using vs2005 sp1
"adolf garlic" wrote:
> Just to add, the rectangle is solid black and is the same height as the table
> appearing in the report.
> I took a copy of the report, deleted the table and the problem went away,
> but now I don't have a table...
> If I move the table up and down and redeploy, recreate PDF the black
> rectangle moves up and down, always in line with the table.
> If I move the table left and right, the size of the rectangle changes, when
> the table is pushed right up against the right hand side of the body the
> rectangle disappears.
> The table has dynamically hidden columns based on whether or not there is
> data to show. The black rectangle has not previously been a problem.
> I'm not sure what you mean by "have you included column 2". The report has
> more than 2 columns but there is only one table present. There are no other
> hidden or other objects to the right of the table.
> "Amarnath" wrote:
> > By any chance, have you included column 2 as well, and a border, then you
> > will get this rectangles. check in the properties for column and the border
> > line for left,right etc... Just to make sure you have not done these things.
> >
> > Amarnath
> >
> > "adolf garlic" wrote:
> >
> > > I have suddenly started getting black rectangles appearing on the right hand
> > > side of PDF output.
> > >
> > > There are no rectangles or other objects in this area of the report.
> > >
> > > Is this a known bug?

Black area where column is hidden in a table when exporting to PDF

I have a very strange problem. I'm using a table and I've
conditionally hidden columns before without any problems, but all of a
sudden, this one report I'm working on has a conditionally hidden
column in the table, but when the column is hidden and I export the
report as PDF, there is a solid black rectangle. It's not directly
where the column is hidden (the hidden column is the next to last
column to the right), but it's one column further to the
right...outside the table. So for instance, if all the columns were
shown in the table, the black rectangle would be to the right of the
table...where another column would go if I were to add one more on the
end. So with the hidden column, it basically goes...end of the table,
white space column, then black rectangle.
Anyone have any ideas what's up with this? I'm using reporting
services 2000 with SP2. Thanks.
RyanHi Ryan
[apologies if you have already received a mail through google groups from me]
did you ever find a solution to this issue, i am having exactly the same
problem of a black square being tacked on to the end of a table?
I'd be grateful if you do have a solution
thanks
"Ryan" wrote:
> I have a very strange problem. I'm using a table and I've
> conditionally hidden columns before without any problems, but all of a
> sudden, this one report I'm working on has a conditionally hidden
> column in the table, but when the column is hidden and I export the
> report as PDF, there is a solid black rectangle. It's not directly
> where the column is hidden (the hidden column is the next to last
> column to the right), but it's one column further to the
> right...outside the table. So for instance, if all the columns were
> shown in the table, the black rectangle would be to the right of the
> table...where another column would go if I were to add one more on the
> end. So with the hidden column, it basically goes...end of the table,
> white space column, then black rectangle.
> Anyone have any ideas what's up with this? I'm using reporting
> services 2000 with SP2. Thanks.
> Ryan
>