Hi all,
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer
.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:[vbcol=seagreen]
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you ca
n
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyz
er.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
>|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:[vbcol=seagreen]
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the tab
le
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
>
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 29, 2012
database import question
Hi all,
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you can
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyzer.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
> > Hi all,
> >
> > I have a database I maintain that has grown quite large. The part of it
> > I am interested in, is one paticular table that logs the history of
> > certain high volume "events". I have events back to 2002, and the
> > database is almost 3 Gigs. I rarely need to query more than 6 months
> > worth. Here is what I would like to do.
> >
> > I'd like to split the database, and create an archive database. I want
> > to move everything before 2006 to this new database. Now, I know I can
> > do a complete backup, and then delete the stuff I don't need, but I
> > wanted to know if there is a way to import just the dates I need from
> > the current database. Of course, there is a timedate field to key off
> > of.
> >
> > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > 2003 Server.
> >
> > Thanks in advance.
> >
> >|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
> > Hi there
> >
> > There are several ways to do this. You can try to use DTS to move the data
> > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > actually use a query something like:
> >
> > SELECT *
> > INTO [ArchiveDBName].dbo.[TableName]
> > FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > And to delete the records from the original table:
> >
> > DELETE FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > However, it was not clear from you post if you have access to Query Analyzer.
> >
> > Lucas
> >
> >
> > "mattdaddym@.gmail.com" wrote:
> >
> > > Hi all,
> > >
> > > I have a database I maintain that has grown quite large. The part of it
> > > I am interested in, is one paticular table that logs the history of
> > > certain high volume "events". I have events back to 2002, and the
> > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > worth. Here is what I would like to do.
> > >
> > > I'd like to split the database, and create an archive database. I want
> > > to move everything before 2006 to this new database. Now, I know I can
> > > do a complete backup, and then delete the stuff I don't need, but I
> > > wanted to know if there is a way to import just the dates I need from
> > > the current database. Of course, there is a timedate field to key off
> > > of.
> > >
> > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > 2003 Server.
> > >
> > > Thanks in advance.
> > >
> > >
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the table
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
> > I do have access to query analyzer. I was hoping for a way to do it in
> > the GUI since it creats the tables automatically when you do an import.
> > I don't know for sure, but I will assume if I use your command that I
> > will need to set the table up first. Not a big deal, though. I guess
> > I'm just being lazy. Thank you!
> > Lucas Kartawidjaja wrote:
> > > Hi there
> > >
> > > There are several ways to do this. You can try to use DTS to move the data
> > > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > > actually use a query something like:
> > >
> > > SELECT *
> > > INTO [ArchiveDBName].dbo.[TableName]
> > > FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > And to delete the records from the original table:
> > >
> > > DELETE FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > However, it was not clear from you post if you have access to Query Analyzer.
> > >
> > > Lucas
> > >
> > >
> > > "mattdaddym@.gmail.com" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a database I maintain that has grown quite large. The part of it
> > > > I am interested in, is one paticular table that logs the history of
> > > > certain high volume "events". I have events back to 2002, and the
> > > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > > worth. Here is what I would like to do.
> > > >
> > > > I'd like to split the database, and create an archive database. I want
> > > > to move everything before 2006 to this new database. Now, I know I can
> > > > do a complete backup, and then delete the stuff I don't need, but I
> > > > wanted to know if there is a way to import just the dates I need from
> > > > the current database. Of course, there is a timedate field to key off
> > > > of.
> > > >
> > > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > > 2003 Server.
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you can
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyzer.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
> > Hi all,
> >
> > I have a database I maintain that has grown quite large. The part of it
> > I am interested in, is one paticular table that logs the history of
> > certain high volume "events". I have events back to 2002, and the
> > database is almost 3 Gigs. I rarely need to query more than 6 months
> > worth. Here is what I would like to do.
> >
> > I'd like to split the database, and create an archive database. I want
> > to move everything before 2006 to this new database. Now, I know I can
> > do a complete backup, and then delete the stuff I don't need, but I
> > wanted to know if there is a way to import just the dates I need from
> > the current database. Of course, there is a timedate field to key off
> > of.
> >
> > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > 2003 Server.
> >
> > Thanks in advance.
> >
> >|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
> > Hi there
> >
> > There are several ways to do this. You can try to use DTS to move the data
> > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > actually use a query something like:
> >
> > SELECT *
> > INTO [ArchiveDBName].dbo.[TableName]
> > FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > And to delete the records from the original table:
> >
> > DELETE FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > However, it was not clear from you post if you have access to Query Analyzer.
> >
> > Lucas
> >
> >
> > "mattdaddym@.gmail.com" wrote:
> >
> > > Hi all,
> > >
> > > I have a database I maintain that has grown quite large. The part of it
> > > I am interested in, is one paticular table that logs the history of
> > > certain high volume "events". I have events back to 2002, and the
> > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > worth. Here is what I would like to do.
> > >
> > > I'd like to split the database, and create an archive database. I want
> > > to move everything before 2006 to this new database. Now, I know I can
> > > do a complete backup, and then delete the stuff I don't need, but I
> > > wanted to know if there is a way to import just the dates I need from
> > > the current database. Of course, there is a timedate field to key off
> > > of.
> > >
> > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > 2003 Server.
> > >
> > > Thanks in advance.
> > >
> > >
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the table
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
> > I do have access to query analyzer. I was hoping for a way to do it in
> > the GUI since it creats the tables automatically when you do an import.
> > I don't know for sure, but I will assume if I use your command that I
> > will need to set the table up first. Not a big deal, though. I guess
> > I'm just being lazy. Thank you!
> > Lucas Kartawidjaja wrote:
> > > Hi there
> > >
> > > There are several ways to do this. You can try to use DTS to move the data
> > > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > > actually use a query something like:
> > >
> > > SELECT *
> > > INTO [ArchiveDBName].dbo.[TableName]
> > > FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > And to delete the records from the original table:
> > >
> > > DELETE FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > However, it was not clear from you post if you have access to Query Analyzer.
> > >
> > > Lucas
> > >
> > >
> > > "mattdaddym@.gmail.com" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a database I maintain that has grown quite large. The part of it
> > > > I am interested in, is one paticular table that logs the history of
> > > > certain high volume "events". I have events back to 2002, and the
> > > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > > worth. Here is what I would like to do.
> > > >
> > > > I'd like to split the database, and create an archive database. I want
> > > > to move everything before 2006 to this new database. Now, I know I can
> > > > do a complete backup, and then delete the stuff I don't need, but I
> > > > wanted to know if there is a way to import just the dates I need from
> > > > the current database. Of course, there is a timedate field to key off
> > > > of.
> > > >
> > > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > > 2003 Server.
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
Database images not showing
How should I store an image into my SQL database in order to be able to read
it using Reporting Services Image control? I have a table with an image
field (datatype of the field is 'Image').
I have tried several ways and the report is always rendered with the image
showing a red cross inside it (image broken). However if I read the table
using MS Access ADP and double click on the image field, MS Paint opens and
shows up the image (this is a bmp image). I have also set the MIMEType of
the rs image field to 'image/bmp' (filling this field this is compulsory
with database images).
It seems that, even though the database field contents ('Image' field) are
properly stored, Reporting Services does not know how to handle it. May I
need to do a CONVERT(Binary, MyImageField) or the database field be of
another type? How should I store the image inside the database so that RS
could read it?
Regards.
PS: This is RS SP2.This sounds like the images are stored as OLE images in the database (e.g.
Access would convert images into OLE images). You can try the following
expression to get rid of the OLE chunk:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
> How should I store an image into my SQL database in order to be able to
> read it using Reporting Services Image control? I have a table with an
> image field (datatype of the field is 'Image').
> I have tried several ways and the report is always rendered with the image
> showing a red cross inside it (image broken). However if I read the table
> using MS Access ADP and double click on the image field, MS Paint opens
> and shows up the image (this is a bmp image). I have also set the MIMEType
> of the rs image field to 'image/bmp' (filling this field this is
> compulsory with database images).
> It seems that, even though the database field contents ('Image' field) are
> properly stored, Reporting Services does not know how to handle it. May I
> need to do a CONVERT(Binary, MyImageField) or the database field be of
> another type? How should I store the image inside the database so that RS
> could read it?
> Regards.
> PS: This is RS SP2.
>|||Thanks for your reply but it seems that there should be another reason. Your
explanation is on the good road, but
I have tried your expression, with that 105 varying from 100 to 110 with the
same results. The red cross is still there.
Any other suggestion? Regards.
PS: Just for further debugging, I have an output of the first 950 bytes
generated by the expression:
=System.Convert.ToBase64String(Fields!Picture.Value)
Here they follow:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
> This sounds like the images are stored as OLE images in the database (e.g.
> Access would convert images into OLE images). You can try the following
> expression to get rid of the OLE chunk:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
> 105))
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read the
>> table using MS Access ADP and double click on the image field, MS Paint
>> opens and shows up the image (this is a bmp image). I have also set the
>> MIMEType of the rs image field to 'image/bmp' (filling this field this is
>> compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field be
>> of another type? How should I store the image inside the database so that
>> RS could read it?
>> Regards.
>> PS: This is RS SP2.
>|||More information on the subject:
I have been doing more tests with this sample bitmap. I have sent it to
myself via email just to read the source code of the message and extract the
base64 of the bitmap. The header of attachment and some hundreds the
begining bytes are here:
--_=_NextPart_001_01C55532.6E34D77F
Content-Type: image/bmp;
name="PHOTO.BMP"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="PHOTO.BMP"
Qk1gSAAAAAAAAHYAAAAoAAAAzwAAALEAAAABAAQAAAAAAAAAAAAQFwAAEBcAAAAAAAAAAAAAAAAA
AP///wD6+voA8/PzAOfn5wDb29sAzMzMALm5uQCjo6MAi4uLAHNzcwBZWVkAQkJCAC8vLwAfHx8A
Dw8PABERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERARERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
etc...
If I copy this base64 chunk of data and set the Reporting Services image
field to:
=System.Convert.FromBase64String("the chunk")
The image is shown perfecly on the report.
However, if I compare some bytes of the begining of this chunk (which is
shown to be correct) and try to find it somewhere on the string returned by:
=System.Convert.ToBase64String(Fields!Picture.Value)
which is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
I can't find it anywhere. There is no 'Qk1gS' substring in it. Now my
question are: Are there multiple ways of converting a file into a base64
string? How can I remove the OLE header of the database if I cannot find the
matching of the image REAL data using this 'comparing' approach?
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>|||Instead of doing tests with the final image, I have created a 5x5 pix bitmap
to work/test with.
The Base64 encoding of it (grabbed from an email sourcecode is):
Qk2GAAAAAAAAADYAAAAoAAAABQAAAAUAAAABABgAAAAAAFAAAAAAAAAAAAAAAAAAAAAAAAAA////////////////////AP///wAA/////////wAA/wD///////////////////8A////////AAD/////AAD/AAAA/////////////////wA=
It works perfectly if I set the value property of the rs image to:
=System.Convert.FromBase64String("the former base64 string")
Now, the database version of the same file: I set a texbox in the RS report
and set its value to =System.Convert.ToBase64String(Fields!Picture.Value) so
that I could retrieve the complete base64 of the image stored in the
database. Here it is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAoAAAAEJNhgAAAAAAAAA2AAAAKAAAAAUAAAAFAAAAAQAYAAAAAABQAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////////wD///8AAP////////8AAP8A////////////////////AP///////wAA/////wAA/wAAAP////////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBQAAAAAAAIqtBf4=
By other means, I have been able to revert this base64 string back to binary
and here are the results:
9 + ÿÿÿÿImagen de mapa de bits Paint.Picture PBrush
BM? 6 ( P ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿ
ÿÿÿÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿ ÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿ
S þ
Of course, I have some rubbish here. But not everything is lost: Now I can
see that a localized language string is included in the OLE chunk before the
'BM' indicating the begining of the bitmap file. And now the final question:
Should I use other string than
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
when the locale of the systems/servers/software is not english? In my case
I'm using spanish and since the string 'Imagen de mapa de bits' is not as
long as 'Bitmap image file', I think that 105 is not valid/correct when
other languages are used.
After some calculations I think that the OLE chunk size for spanish (which
is my case) is 121 instead of 105. However the red cross is still there!!!!!
:(((
Another aproach, instead of letting RS cropping the image ole data, is to
retrieve the image from the database without it. Is it feasible to retrieve
just a 'substring' of the image?
Please help. I really need it. Thaks.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>
it using Reporting Services Image control? I have a table with an image
field (datatype of the field is 'Image').
I have tried several ways and the report is always rendered with the image
showing a red cross inside it (image broken). However if I read the table
using MS Access ADP and double click on the image field, MS Paint opens and
shows up the image (this is a bmp image). I have also set the MIMEType of
the rs image field to 'image/bmp' (filling this field this is compulsory
with database images).
It seems that, even though the database field contents ('Image' field) are
properly stored, Reporting Services does not know how to handle it. May I
need to do a CONVERT(Binary, MyImageField) or the database field be of
another type? How should I store the image inside the database so that RS
could read it?
Regards.
PS: This is RS SP2.This sounds like the images are stored as OLE images in the database (e.g.
Access would convert images into OLE images). You can try the following
expression to get rid of the OLE chunk:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
> How should I store an image into my SQL database in order to be able to
> read it using Reporting Services Image control? I have a table with an
> image field (datatype of the field is 'Image').
> I have tried several ways and the report is always rendered with the image
> showing a red cross inside it (image broken). However if I read the table
> using MS Access ADP and double click on the image field, MS Paint opens
> and shows up the image (this is a bmp image). I have also set the MIMEType
> of the rs image field to 'image/bmp' (filling this field this is
> compulsory with database images).
> It seems that, even though the database field contents ('Image' field) are
> properly stored, Reporting Services does not know how to handle it. May I
> need to do a CONVERT(Binary, MyImageField) or the database field be of
> another type? How should I store the image inside the database so that RS
> could read it?
> Regards.
> PS: This is RS SP2.
>|||Thanks for your reply but it seems that there should be another reason. Your
explanation is on the good road, but
I have tried your expression, with that 105 varying from 100 to 110 with the
same results. The red cross is still there.
Any other suggestion? Regards.
PS: Just for further debugging, I have an output of the first 950 bytes
generated by the expression:
=System.Convert.ToBase64String(Fields!Picture.Value)
Here they follow:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
> This sounds like the images are stored as OLE images in the database (e.g.
> Access would convert images into OLE images). You can try the following
> expression to get rid of the OLE chunk:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
> 105))
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read the
>> table using MS Access ADP and double click on the image field, MS Paint
>> opens and shows up the image (this is a bmp image). I have also set the
>> MIMEType of the rs image field to 'image/bmp' (filling this field this is
>> compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field be
>> of another type? How should I store the image inside the database so that
>> RS could read it?
>> Regards.
>> PS: This is RS SP2.
>|||More information on the subject:
I have been doing more tests with this sample bitmap. I have sent it to
myself via email just to read the source code of the message and extract the
base64 of the bitmap. The header of attachment and some hundreds the
begining bytes are here:
--_=_NextPart_001_01C55532.6E34D77F
Content-Type: image/bmp;
name="PHOTO.BMP"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="PHOTO.BMP"
Qk1gSAAAAAAAAHYAAAAoAAAAzwAAALEAAAABAAQAAAAAAAAAAAAQFwAAEBcAAAAAAAAAAAAAAAAA
AP///wD6+voA8/PzAOfn5wDb29sAzMzMALm5uQCjo6MAi4uLAHNzcwBZWVkAQkJCAC8vLwAfHx8A
Dw8PABERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERARERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
etc...
If I copy this base64 chunk of data and set the Reporting Services image
field to:
=System.Convert.FromBase64String("the chunk")
The image is shown perfecly on the report.
However, if I compare some bytes of the begining of this chunk (which is
shown to be correct) and try to find it somewhere on the string returned by:
=System.Convert.ToBase64String(Fields!Picture.Value)
which is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
I can't find it anywhere. There is no 'Qk1gS' substring in it. Now my
question are: Are there multiple ways of converting a file into a base64
string? How can I remove the OLE header of the database if I cannot find the
matching of the image REAL data using this 'comparing' approach?
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>|||Instead of doing tests with the final image, I have created a 5x5 pix bitmap
to work/test with.
The Base64 encoding of it (grabbed from an email sourcecode is):
Qk2GAAAAAAAAADYAAAAoAAAABQAAAAUAAAABABgAAAAAAFAAAAAAAAAAAAAAAAAAAAAAAAAA////////////////////AP///wAA/////////wAA/wD///////////////////8A////////AAD/////AAD/AAAA/////////////////wA=
It works perfectly if I set the value property of the rs image to:
=System.Convert.FromBase64String("the former base64 string")
Now, the database version of the same file: I set a texbox in the RS report
and set its value to =System.Convert.ToBase64String(Fields!Picture.Value) so
that I could retrieve the complete base64 of the image stored in the
database. Here it is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAoAAAAEJNhgAAAAAAAAA2AAAAKAAAAAUAAAAFAAAAAQAYAAAAAABQAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////////wD///8AAP////////8AAP8A////////////////////AP///////wAA/////wAA/wAAAP////////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBQAAAAAAAIqtBf4=
By other means, I have been able to revert this base64 string back to binary
and here are the results:
9 + ÿÿÿÿImagen de mapa de bits Paint.Picture PBrush
BM? 6 ( P ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿ
ÿÿÿÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿ ÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿ
S þ
Of course, I have some rubbish here. But not everything is lost: Now I can
see that a localized language string is included in the OLE chunk before the
'BM' indicating the begining of the bitmap file. And now the final question:
Should I use other string than
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
when the locale of the systems/servers/software is not english? In my case
I'm using spanish and since the string 'Imagen de mapa de bits' is not as
long as 'Bitmap image file', I think that 105 is not valid/correct when
other languages are used.
After some calculations I think that the OLE chunk size for spanish (which
is my case) is 121 instead of 105. However the red cross is still there!!!!!
:(((
Another aproach, instead of letting RS cropping the image ole data, is to
retrieve the image from the database without it. Is it feasible to retrieve
just a 'substring' of the image?
Please help. I really need it. Thaks.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>
Database Idea
Good morning
In messages system i have table to store messages and another table to contain the links which the message is posted through
Now i want to delete a link but i don't want to delete the message sent through this link
The problem:
When displaying the message sent what will be fount in the link field (i.e for the deleted link)
I tried to move the deleted link data to separate table (EX: deletedLink) but if the user added new link with the same name as the deleted link?The problem mainly in when displaying the message sent i will have the same link twice one for the deleted and the other for the added one.
If any one has a good idea for doing that please reply to me
I don’t want the sql code
I want just the idea
Thanks
kind regards
Mohammed Al Maghraby
Hey,
I posted in your other forum post.
Database I/O error
I am using SQL 2000 Dev edition. I create a DB on box
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.
Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>
sql
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.
Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>
sql
Database I/O error
I am using SQL 2000 Dev edition. I create a DB on box
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>
Tuesday, March 27, 2012
Database I/O error
I am using SQL 2000 Dev edition. I create a DB on box
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>
Database Help
I have am MSSQL Server.
I have a Database that has a table in it that I have been trying to access
with 3rd Party software. Well do to the way that the DB is setup I cannot
get to the data in the table how I want. so my thoughts were to have only
the parts I want go into another (local) MSSQL Database. Can I do this? If
so how do I set it up? I have the new Database created but I don't know how
to do a live copy of the data from the existing database.
Any help you could provide would really be appreciated.> I cannot get to the data in the table how I want.
Can you be more specific? What does the data look like, and how do you want
it to look?|||Sure
The data I am Looking to access is in a table called dbo.reading
The data looks like this
Device ID Sequence# Time
reg_seq tier data_desc sensor data_register
time_stamp status
052461368 0 4/14/2008 7:50:40 PM 0 0 3 NULL 2 NULL
8
052461368 0 4/14/2008 7:50:40 PM 1 0 1000001 036497.900543861091 NULL
8
052461368 0 4/14/2008 7:50:40 PM 2 0 4000001 128.900000430643559 NULL
8
052461368 0 4/14/2008 7:50:40 PM 3 0 6000001 1 614.4500091560185NULL8052461368 0 4/14/2008 7:50:40 PM 4 0 2000001 1
585.54998779296875 NULL 8
Each Device ID is a meter. However there are 4 sets of data (reg_seq)
associated with each reading per meter and I want to get the data that
corresponds with reg_seq being 1 for each data point
I created a new table called dbo.kw in the same global database
The problem I am having is I am not sure how to link the reading table to
the kW table to get what I want or set anything up so that I can do a live
output of dbo.reading.
Does this help?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:8535B201-2D67-4EF1-B81F-A7833CD03E1E@.microsoft.com...
>> I cannot get to the data in the table how I want.
> Can you be more specific? What does the data look like, and how do you
> want it to look?|||> Does this help?
Not really. Instead of:
> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
> associated with each reading per meter and I want to get the data that
> corresponds with reg_seq being 1 for each data point
Can you SHOW what you want the actual output to look like, based on the
sample rows you provided?|||Sorry about that when I pasted it all the tables lined up.
What I am looking to do is take the table dbo.reading and
export/link/synch/output the table dbo.kW
In dbo.reading I have several columns of information what I want is
Device_ID
Time
data_register (where data_desc. = 1)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
> Can you SHOW what you want the actual output to look like, based on the
> sample rows you provided?|||Anyone help?
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
> Sorry about that when I pasted it all the tables lined up.
> What I am looking to do is take the table dbo.reading and
> export/link/synch/output the table dbo.kW
> In dbo.reading I have several columns of information what I want is
> Device_ID
> Time
> data_register (where data_desc. = 1)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>|||I was trying to help. I asked for specific information and got more vague
information. <shrug>
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
> Anyone help?
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?|||I apologize Aaon. Let me phrase it this way. What specific information are
you asking for? I am sorry for not being clearer.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#IBvj6ipIHA.2256@.TK2MSFTNGP05.phx.gbl...
> I was trying to help. I asked for specific information and got more vague
> information. <shrug>
>
>
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
>> Anyone help?
>> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
>> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>
I have a Database that has a table in it that I have been trying to access
with 3rd Party software. Well do to the way that the DB is setup I cannot
get to the data in the table how I want. so my thoughts were to have only
the parts I want go into another (local) MSSQL Database. Can I do this? If
so how do I set it up? I have the new Database created but I don't know how
to do a live copy of the data from the existing database.
Any help you could provide would really be appreciated.> I cannot get to the data in the table how I want.
Can you be more specific? What does the data look like, and how do you want
it to look?|||Sure
The data I am Looking to access is in a table called dbo.reading
The data looks like this
Device ID Sequence# Time
reg_seq tier data_desc sensor data_register
time_stamp status
052461368 0 4/14/2008 7:50:40 PM 0 0 3 NULL 2 NULL
8
052461368 0 4/14/2008 7:50:40 PM 1 0 1000001 036497.900543861091 NULL
8
052461368 0 4/14/2008 7:50:40 PM 2 0 4000001 128.900000430643559 NULL
8
052461368 0 4/14/2008 7:50:40 PM 3 0 6000001 1 614.4500091560185NULL8052461368 0 4/14/2008 7:50:40 PM 4 0 2000001 1
585.54998779296875 NULL 8
Each Device ID is a meter. However there are 4 sets of data (reg_seq)
associated with each reading per meter and I want to get the data that
corresponds with reg_seq being 1 for each data point
I created a new table called dbo.kw in the same global database
The problem I am having is I am not sure how to link the reading table to
the kW table to get what I want or set anything up so that I can do a live
output of dbo.reading.
Does this help?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:8535B201-2D67-4EF1-B81F-A7833CD03E1E@.microsoft.com...
>> I cannot get to the data in the table how I want.
> Can you be more specific? What does the data look like, and how do you
> want it to look?|||> Does this help?
Not really. Instead of:
> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
> associated with each reading per meter and I want to get the data that
> corresponds with reg_seq being 1 for each data point
Can you SHOW what you want the actual output to look like, based on the
sample rows you provided?|||Sorry about that when I pasted it all the tables lined up.
What I am looking to do is take the table dbo.reading and
export/link/synch/output the table dbo.kW
In dbo.reading I have several columns of information what I want is
Device_ID
Time
data_register (where data_desc. = 1)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
> Can you SHOW what you want the actual output to look like, based on the
> sample rows you provided?|||Anyone help?
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
> Sorry about that when I pasted it all the tables lined up.
> What I am looking to do is take the table dbo.reading and
> export/link/synch/output the table dbo.kW
> In dbo.reading I have several columns of information what I want is
> Device_ID
> Time
> data_register (where data_desc. = 1)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>|||I was trying to help. I asked for specific information and got more vague
information. <shrug>
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
> Anyone help?
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?|||I apologize Aaon. Let me phrase it this way. What specific information are
you asking for? I am sorry for not being clearer.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#IBvj6ipIHA.2256@.TK2MSFTNGP05.phx.gbl...
> I was trying to help. I asked for specific information and got more vague
> information. <shrug>
>
>
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
>> Anyone help?
>> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
>> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>
Database Growth & Space Recovery Problem
I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Database Growth & Space Recovery Problem
I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Check for responses to your duplicate post in the Transact-SQL forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Check for responses to your duplicate post in the Transact-SQL forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
I have never receive solution.
Thanks
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Check for responses to your duplicate post in the Transact-SQL forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
I have never receive solution.
Thanks
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Hi Faiz,
You could use the following. Used in SQL 2000 and I use it for SQL 2005.
Code Snippet
use database_name
go
dbcc showfilestats
go
For log file sizes:
Code Snippet
dbcc SQLperf(logspace)
go
more over sysfiles, only shows the file size allocated, not being used.
regards
Jag
database grows abnormally
there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.sqlmonster.com
sepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
sql
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.sqlmonster.com
sepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
sql
database grows abnormally
there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
--
Message posted via http://www.sqlmonster.comsepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
--
David Gugick
Imceda Software
www.imceda.com
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
--
Message posted via http://www.sqlmonster.comsepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
--
David Gugick
Imceda Software
www.imceda.com
database grows abnormally
there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.droptable.comsepideh iranpour via droptable.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.droptable.comsepideh iranpour via droptable.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
Sunday, March 25, 2012
Database grew 50% in a week ??
What happened ?
normal volume of transaction.. db recovery is set to simple
Any Idea
Is there any way I can know the size of each table ?
Thanks
Sam
Probably did a reindex. That requires lots of free space other wise it will
grow the db.
Andrew J. Kelly SQL MVP
"Sam" <samnadeau@.sympatico.ca> wrote in message
news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
> What happened ?
> normal volume of transaction.. db recovery is set to simple
> Any Idea
> Is there any way I can know the size of each table ?
> Thanks
> Sam
>
|||Can the system do this by itself ? Cause I know for sure I didn't do it.
What else ?
How can we get more info the the space usage of each tables ?
Thanks
Sam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Probably did a reindex. That requires lots of free space other wise it
> will grow the db.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||It's also possible that that you had many inserts on a table that was
clustered in such a way that the inserts occurred throughout the table and
caused a lot of page splits. Run DBCC SHOWCONTIG to see the extent of
fragmentation. Pay close attention to page density.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Sam" <samnadeau@.sympatico.ca> wrote in message
news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
Can the system do this by itself ? Cause I know for sure I didn't do it.
What else ?
How can we get more info the the space usage of each tables ?
Thanks
Sam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Probably did a reindex. That requires lots of free space other wise it
> will grow the db.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||SQL Server doesn't do this by itself, but perhaps you have a join, possibly created by Maintenance
Plan Wizard, that does this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <samnadeau@.sympatico.ca> wrote in message news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Can the system do this by itself ? Cause I know for sure I didn't do it.
> What else ?
> How can we get more info the the space usage of each tables ?
> Thanks
> Sam
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
>
|||> SQL Server doesn't do this by itself, but perhaps you have a join,
> possibly created by Maintenance
I think Tibor really means "Job" not "Join"<g>.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uRtaG1f3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> SQL Server doesn't do this by itself, but perhaps you have a join,
> possibly created by Maintenance
> Plan Wizard, that does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
>
|||True. Thanks Andrew.
I've just talked about joins in class, and probably typed a bit too fast... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O7e919f3EHA.1152@.TK2MSFTNGP14.phx.gbl...
> I think Tibor really means "Job" not "Join"<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uRtaG1f3EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
normal volume of transaction.. db recovery is set to simple
Any Idea
Is there any way I can know the size of each table ?
Thanks
Sam
Probably did a reindex. That requires lots of free space other wise it will
grow the db.
Andrew J. Kelly SQL MVP
"Sam" <samnadeau@.sympatico.ca> wrote in message
news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
> What happened ?
> normal volume of transaction.. db recovery is set to simple
> Any Idea
> Is there any way I can know the size of each table ?
> Thanks
> Sam
>
|||Can the system do this by itself ? Cause I know for sure I didn't do it.
What else ?
How can we get more info the the space usage of each tables ?
Thanks
Sam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Probably did a reindex. That requires lots of free space other wise it
> will grow the db.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||It's also possible that that you had many inserts on a table that was
clustered in such a way that the inserts occurred throughout the table and
caused a lot of page splits. Run DBCC SHOWCONTIG to see the extent of
fragmentation. Pay close attention to page density.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Sam" <samnadeau@.sympatico.ca> wrote in message
news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
Can the system do this by itself ? Cause I know for sure I didn't do it.
What else ?
How can we get more info the the space usage of each tables ?
Thanks
Sam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Probably did a reindex. That requires lots of free space other wise it
> will grow the db.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:elb7gTX3EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||SQL Server doesn't do this by itself, but perhaps you have a join, possibly created by Maintenance
Plan Wizard, that does this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <samnadeau@.sympatico.ca> wrote in message news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Can the system do this by itself ? Cause I know for sure I didn't do it.
> What else ?
> How can we get more info the the space usage of each tables ?
> Thanks
> Sam
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uS9Pmaa3EHA.1564@.TK2MSFTNGP09.phx.gbl...
>
|||> SQL Server doesn't do this by itself, but perhaps you have a join,
> possibly created by Maintenance
I think Tibor really means "Job" not "Join"<g>.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uRtaG1f3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> SQL Server doesn't do this by itself, but perhaps you have a join,
> possibly created by Maintenance
> Plan Wizard, that does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sam" <samnadeau@.sympatico.ca> wrote in message
> news:ePgqTXf3EHA.3416@.TK2MSFTNGP09.phx.gbl...
>
|||True. Thanks Andrew.
I've just talked about joins in class, and probably typed a bit too fast... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O7e919f3EHA.1152@.TK2MSFTNGP14.phx.gbl...
> I think Tibor really means "Job" not "Join"<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uRtaG1f3EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
Database getting slower for every BLOB object
Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
>
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
>
Database getting slower for every BLOB object
Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
Subscribe to:
Posts (Atom)