Tools I want to use...
Sql2k/MSDE
ADO 2.x
VB6
Before I waste time experimenting, thought I'd ask the veterans:
I want to store Jpgs into a table, using the image datatype.
Will ADO auto-magically pick up that I'm streaming an image in when I try to do this...
Dim Pic as StdPicutre, RS as ADODB.Recordset
RS.open "select * from...."
set pic = Rs!MyImage
Or do I have to go through bizarre motions like this...
Dim Pic as StdPicutre, RS as ADODB.Recordset
Dim Blob() as byte
Blob = rs!MyImage
Open MyFilePath for Binary as #FileNum
Put #FileNum,,Blob
Close #FileNum
Set Pic = LoadPicture(MyFilePath)
Any thoughts?
(Please don't recommended linked controls, I hate those damn things with a passion.)
Thanks for reading!Agree about linked controls.
If you search you can find code related to inserting images.
Do you need to hold them in the database?
I usually hold them in files on the ado application server with the filename in the database - this saves the network traffic in saving and retrieving and gets round the transaction/trigger issues.|||I found some code, yes. All ways to acomplish this are easy, don't get me wrong...just looking for the simplest solution.
You seam to be implying that you do not recomend storing the actually image bits in the database itself...at all. The system you talk off is similar to what I'm doing now.
If it's not broken, don't fit it?
Is there anything to gain by placing these images into a table?
Thanks!|||Don't see what you would gain - you can't do a lot with them anyway.
If you do want to save them in a table then I would make this a separate database so that it doesn't compromise the rest of the data.
Would still prefer to leave them in files though.
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Image data type. Tricks and tips anyone?
Tools I want to use...
Sql2k/MSDE
ADO 2.x
VB6
Before I waste time experimenting, thought I'd ask the veterans:
I want to store Jpgs into a table, using the image datatype.
Will ADO auto-magically pick up that I'm streaming an image in when I try to do this...
Dim Pic as StdPicutre, RS as ADODB.Recordset
RS.open "select * from...."
set pic = Rs!MyImage
Or do I have to go through bizarre motions like this...
Dim Pic as StdPicutre, RS as ADODB.Recordset
Dim Blob() as byte
Blob = rs!MyImage
Open MyFilePath for Binary as #FileNum
Put #FileNum,,Blob
Close #FileNum
Set Pic = LoadPicture(MyFilePath)
Any thoughts?
(Please don't recommended linked controls, I hate those damn things with a passion.)
Thanks for reading!Agree about linked controls.
If you search you can find code related to inserting images.
Do you need to hold them in the database?
I usually hold them in files on the ado application server with the filename in the database - this saves the network traffic in saving and retrieving and gets round the transaction/trigger issues.|||I found some code, yes. All ways to acomplish this are easy, don't get me wrong...just looking for the simplest solution.
You seam to be implying that you do not recomend storing the actually image bits in the database itself...at all. The system you talk off is similar to what I'm doing now.
If it's not broken, don't fit it?
Is there anything to gain by placing these images into a table?
Thanks!|||Don't see what you would gain - you can't do a lot with them anyway.
If you do want to save them in a table then I would make this a separate database so that it doesn't compromise the rest of the data.
Would still prefer to leave them in files though.
Sql2k/MSDE
ADO 2.x
VB6
Before I waste time experimenting, thought I'd ask the veterans:
I want to store Jpgs into a table, using the image datatype.
Will ADO auto-magically pick up that I'm streaming an image in when I try to do this...
Dim Pic as StdPicutre, RS as ADODB.Recordset
RS.open "select * from...."
set pic = Rs!MyImage
Or do I have to go through bizarre motions like this...
Dim Pic as StdPicutre, RS as ADODB.Recordset
Dim Blob() as byte
Blob = rs!MyImage
Open MyFilePath for Binary as #FileNum
Put #FileNum,,Blob
Close #FileNum
Set Pic = LoadPicture(MyFilePath)
Any thoughts?
(Please don't recommended linked controls, I hate those damn things with a passion.)
Thanks for reading!Agree about linked controls.
If you search you can find code related to inserting images.
Do you need to hold them in the database?
I usually hold them in files on the ado application server with the filename in the database - this saves the network traffic in saving and retrieving and gets round the transaction/trigger issues.|||I found some code, yes. All ways to acomplish this are easy, don't get me wrong...just looking for the simplest solution.
You seam to be implying that you do not recomend storing the actually image bits in the database itself...at all. The system you talk off is similar to what I'm doing now.
If it's not broken, don't fit it?
Is there anything to gain by placing these images into a table?
Thanks!|||Don't see what you would gain - you can't do a lot with them anyway.
If you do want to save them in a table then I would make this a separate database so that it doesn't compromise the rest of the data.
Would still prefer to leave them in files though.
Monday, March 12, 2012
i'm new to dw been to oltp for some time
I'm new to dw i'm just confuse with the terms
1. what is a warehouse?
Is it just a copy of an OLTP database that has been cleased and transformed
is this a SQL server DB and not an AS DB
or is it the AS DB
thanks
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
Before explaining a data warehouse you need to think of the
opposite, a transaction system. If your company sells things
then you will likely have a transaction system which stores
details of all your customers, what they have bought, how
much they owe, what products you are selling etc.
But this transaction system is ever changing, and it is almost
impossible to find out if, for example, you are selling more this
month that you did last month, or if you are owed more money
this month than last month, or if the sales of a certain product
has increased because of the marketing campaign you ran in
the north for the last three months.
So with a data warehouse you take a 'snapshot' of the data at
a particular time:- hourly, daily, weekly, monthly etc. Gradually
you build up a history of what was in your transaction database
and you can then perform 'business intelligence' on that data to
try to help you run your company better.
This business intelligence is done with 'query and reporting' tools
of which there are many, but products such as Excel, Access, Brio,
Business Objects, Crystal Reports, Cognos etc can all be used.
You can also aggregate and summarize the data as you go along,
so that users do not see all the detail, but just a summary of all
the products sold in the north, or the west and so on.
Of course you store this data warehouse on a different machine,
and in a different relational database server. This is because you
do not want to impact the performance of your transaction system
when people are doing end-user queries on the data warehouse.
Now the hard part is deciding what data to put in your data
warehouse, how often to put it there, even finding out what all
your data means (many companies do not actually know what
all the data in their database means).
Of cours you can also build an OLAP cube, which is a form of
a data warehouse. Here all the data is pre-aggregated to produce
very fast query results, but of course you can only query what is
in the 'cube'.
In SQL Server an OLAP cube is held in relational tables (fact tables
and dimensions tables) but you do not query the tables directly
but via the 'cube'. Some products, such as Hyperion Essbase, do not
hold their cube in relational tables.
Building a data warehouse or OLAP cube, can be a long and
complex job (just finding out what data a company has, where it
is, and how to access it, can be a major job).
There are plenty of books about data warehouse if you look
for them
Alan
|||Hi Jose,
I have published a lot of materials for 'newbies' on my personal
website www.peternolan.com. If you have anything else you would like
to see on my website please let me know as I am always interested in
hearing feedback on what is there.
By the way, I notice you are in Manila. I used to work with Remax
International and they are great people. You might want to call them
and see if they can come and present to your company about BI/DW...I
could not find their web site...I did find
this..http://www.celerant.com/Internationa...ssPartners.cfm
Remax was in Makati district..I'm sure you will be able to find them in
your local web sites or phone books...
Best Regards
Peter Nolan...
1. what is a warehouse?
Is it just a copy of an OLTP database that has been cleased and transformed
is this a SQL server DB and not an AS DB
or is it the AS DB
thanks
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
Before explaining a data warehouse you need to think of the
opposite, a transaction system. If your company sells things
then you will likely have a transaction system which stores
details of all your customers, what they have bought, how
much they owe, what products you are selling etc.
But this transaction system is ever changing, and it is almost
impossible to find out if, for example, you are selling more this
month that you did last month, or if you are owed more money
this month than last month, or if the sales of a certain product
has increased because of the marketing campaign you ran in
the north for the last three months.
So with a data warehouse you take a 'snapshot' of the data at
a particular time:- hourly, daily, weekly, monthly etc. Gradually
you build up a history of what was in your transaction database
and you can then perform 'business intelligence' on that data to
try to help you run your company better.
This business intelligence is done with 'query and reporting' tools
of which there are many, but products such as Excel, Access, Brio,
Business Objects, Crystal Reports, Cognos etc can all be used.
You can also aggregate and summarize the data as you go along,
so that users do not see all the detail, but just a summary of all
the products sold in the north, or the west and so on.
Of course you store this data warehouse on a different machine,
and in a different relational database server. This is because you
do not want to impact the performance of your transaction system
when people are doing end-user queries on the data warehouse.
Now the hard part is deciding what data to put in your data
warehouse, how often to put it there, even finding out what all
your data means (many companies do not actually know what
all the data in their database means).
Of cours you can also build an OLAP cube, which is a form of
a data warehouse. Here all the data is pre-aggregated to produce
very fast query results, but of course you can only query what is
in the 'cube'.
In SQL Server an OLAP cube is held in relational tables (fact tables
and dimensions tables) but you do not query the tables directly
but via the 'cube'. Some products, such as Hyperion Essbase, do not
hold their cube in relational tables.
Building a data warehouse or OLAP cube, can be a long and
complex job (just finding out what data a company has, where it
is, and how to access it, can be a major job).
There are plenty of books about data warehouse if you look
for them
Alan
|||Hi Jose,
I have published a lot of materials for 'newbies' on my personal
website www.peternolan.com. If you have anything else you would like
to see on my website please let me know as I am always interested in
hearing feedback on what is there.
By the way, I notice you are in Manila. I used to work with Remax
International and they are great people. You might want to call them
and see if they can come and present to your company about BI/DW...I
could not find their web site...I did find
this..http://www.celerant.com/Internationa...ssPartners.cfm
Remax was in Makati district..I'm sure you will be able to find them in
your local web sites or phone books...
Best Regards
Peter Nolan...
i'm new to dw been to oltp for some time
I'm new to dw i'm just confuse with the terms
1. what is a warehouse?
Is it just a copy of an OLTP database that has been cleased and transformed
is this a SQL server DB and not an AS DB
or is it the AS DB
thanks
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787Before explaining a data warehouse you need to think of the
opposite, a transaction system. If your company sells things
then you will likely have a transaction system which stores
details of all your customers, what they have bought, how
much they owe, what products you are selling etc.
But this transaction system is ever changing, and it is almost
impossible to find out if, for example, you are selling more this
month that you did last month, or if you are owed more money
this month than last month, or if the sales of a certain product
has increased because of the marketing campaign you ran in
the north for the last three months.
So with a data warehouse you take a 'snapshot' of the data at
a particular time:- hourly, daily, weekly, monthly etc. Gradually
you build up a history of what was in your transaction database
and you can then perform 'business intelligence' on that data to
try to help you run your company better.
This business intelligence is done with 'query and reporting' tools
of which there are many, but products such as Excel, Access, Brio,
Business Objects, Crystal Reports, Cognos etc can all be used.
You can also aggregate and summarize the data as you go along,
so that users do not see all the detail, but just a summary of all
the products sold in the north, or the west and so on.
Of course you store this data warehouse on a different machine,
and in a different relational database server. This is because you
do not want to impact the performance of your transaction system
when people are doing end-user queries on the data warehouse.
Now the hard part is deciding what data to put in your data
warehouse, how often to put it there, even finding out what all
your data means (many companies do not actually know what
all the data in their database means).
Of cours you can also build an OLAP cube, which is a form of
a data warehouse. Here all the data is pre-aggregated to produce
very fast query results, but of course you can only query what is
in the 'cube'.
In SQL Server an OLAP cube is held in relational tables (fact tables
and dimensions tables) but you do not query the tables directly
but via the 'cube'. Some products, such as Hyperion Essbase, do not
hold their cube in relational tables.
Building a data warehouse or OLAP cube, can be a long and
complex job (just finding out what data a company has, where it
is, and how to access it, can be a major job).
There are plenty of books about data warehouse if you look
for them
Alan|||Hi Jose,
I have published a lot of materials for 'newbies' on my personal
website www.peternolan.com. If you have anything else you would like
to see on my website please let me know as I am always interested in
hearing feedback on what is there.
By the way, I notice you are in Manila. I used to work with Remax
International and they are great people. You might want to call them
and see if they can come and present to your company about BI/DW...I
could not find their web site...I did find
this..http://www.celerant.com/Internation...essPartners.cfm
Remax was in Makati district..I'm sure you will be able to find them in
your local web sites or phone books...
Best Regards
Peter Nolan...
1. what is a warehouse?
Is it just a copy of an OLTP database that has been cleased and transformed
is this a SQL server DB and not an AS DB
or is it the AS DB
thanks
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787Before explaining a data warehouse you need to think of the
opposite, a transaction system. If your company sells things
then you will likely have a transaction system which stores
details of all your customers, what they have bought, how
much they owe, what products you are selling etc.
But this transaction system is ever changing, and it is almost
impossible to find out if, for example, you are selling more this
month that you did last month, or if you are owed more money
this month than last month, or if the sales of a certain product
has increased because of the marketing campaign you ran in
the north for the last three months.
So with a data warehouse you take a 'snapshot' of the data at
a particular time:- hourly, daily, weekly, monthly etc. Gradually
you build up a history of what was in your transaction database
and you can then perform 'business intelligence' on that data to
try to help you run your company better.
This business intelligence is done with 'query and reporting' tools
of which there are many, but products such as Excel, Access, Brio,
Business Objects, Crystal Reports, Cognos etc can all be used.
You can also aggregate and summarize the data as you go along,
so that users do not see all the detail, but just a summary of all
the products sold in the north, or the west and so on.
Of course you store this data warehouse on a different machine,
and in a different relational database server. This is because you
do not want to impact the performance of your transaction system
when people are doing end-user queries on the data warehouse.
Now the hard part is deciding what data to put in your data
warehouse, how often to put it there, even finding out what all
your data means (many companies do not actually know what
all the data in their database means).
Of cours you can also build an OLAP cube, which is a form of
a data warehouse. Here all the data is pre-aggregated to produce
very fast query results, but of course you can only query what is
in the 'cube'.
In SQL Server an OLAP cube is held in relational tables (fact tables
and dimensions tables) but you do not query the tables directly
but via the 'cube'. Some products, such as Hyperion Essbase, do not
hold their cube in relational tables.
Building a data warehouse or OLAP cube, can be a long and
complex job (just finding out what data a company has, where it
is, and how to access it, can be a major job).
There are plenty of books about data warehouse if you look
for them
Alan|||Hi Jose,
I have published a lot of materials for 'newbies' on my personal
website www.peternolan.com. If you have anything else you would like
to see on my website please let me know as I am always interested in
hearing feedback on what is there.
By the way, I notice you are in Manila. I used to work with Remax
International and they are great people. You might want to call them
and see if they can come and present to your company about BI/DW...I
could not find their web site...I did find
this..http://www.celerant.com/Internation...essPartners.cfm
Remax was in Makati district..I'm sure you will be able to find them in
your local web sites or phone books...
Best Regards
Peter Nolan...
I'm having a web cast - and you're all invited
Place: Your desktop - browse to
http://support.microsoft.com/default.aspx?kbid=893437
Date: Thursday, February 24, 2005: 10:00 AM Pacific time (Greenwich mean
time - 8 hours)
Details:
This Microsoft Support WebCast discusses replication internals and provides
various tips and tricks that are useful to help design strong
high-performance replication solutions. The presentation focuses on
transactional replication and merge replication in Microsoft SQL Server 2000
and SQL Server 2005.
This is a Level 300 session that will be presented by Hilary Cotter.
Hilary Cotter has been a Most Valued Professional (MVP) for Microsoft SQL
Server since 2001 and has been involved in information technology for 20
years. Hilary is a Web and database specialist who has just completed a book
on transactional replication in SQL Server 2000. He is currently working on
a book about Merge Replication in SQL Server 2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
In article <O9bANBvDFHA.1260@.TK2MSFTNGP12.phx.gbl>,
hilary.cotter@.gmail.com says...
> Place: Your desktop - browse to
> http://support.microsoft.com/default.aspx?kbid=893437
> Date: Thursday, February 24, 2005: 10:00 AM Pacific time (Greenwich mean
> time - 8 hours)
> Details:
> This Microsoft Support WebCast discusses replication internals and provides
> various tips and tricks that are useful to help design strong
> high-performance replication solutions. The presentation focuses on
> transactional replication and merge replication in Microsoft SQL Server 2000
Sweet.. thanks for the heads up
-comb
http://support.microsoft.com/default.aspx?kbid=893437
Date: Thursday, February 24, 2005: 10:00 AM Pacific time (Greenwich mean
time - 8 hours)
Details:
This Microsoft Support WebCast discusses replication internals and provides
various tips and tricks that are useful to help design strong
high-performance replication solutions. The presentation focuses on
transactional replication and merge replication in Microsoft SQL Server 2000
and SQL Server 2005.
This is a Level 300 session that will be presented by Hilary Cotter.
Hilary Cotter has been a Most Valued Professional (MVP) for Microsoft SQL
Server since 2001 and has been involved in information technology for 20
years. Hilary is a Web and database specialist who has just completed a book
on transactional replication in SQL Server 2000. He is currently working on
a book about Merge Replication in SQL Server 2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
In article <O9bANBvDFHA.1260@.TK2MSFTNGP12.phx.gbl>,
hilary.cotter@.gmail.com says...
> Place: Your desktop - browse to
> http://support.microsoft.com/default.aspx?kbid=893437
> Date: Thursday, February 24, 2005: 10:00 AM Pacific time (Greenwich mean
> time - 8 hours)
> Details:
> This Microsoft Support WebCast discusses replication internals and provides
> various tips and tricks that are useful to help design strong
> high-performance replication solutions. The presentation focuses on
> transactional replication and merge replication in Microsoft SQL Server 2000
Sweet.. thanks for the heads up
-comb
Friday, March 9, 2012
IIS Server
I have a SQL Database that is used for storage for an application located on a IIS server.
The users are having a hard time running reports, and automatically assume that it is the SQL Database. I have look in all of the logs, I have done a dbcc checkdb and everything looks fine.
However when I go to the IIS server itself in the event logs there are all sorts of error like the one below:
Error occurred in file:
'D:\teamtrack\Source\Servers\NotificationServer\NS ServerEvents.cpp', line 611.
Resource Msg Id = 474
An error occurred while connecting to
the server.
Message id 38340 could not be sent to (email_address.com.
Does this seems like a sql problem?
Please advise.
LystraSQL Server and IIS do not play well on the same server. You may have to throttle SQL Server's use of memory on the box so that IIS is not memory starved. Search the KnowledgeBase site or Google the microsoft.public.* groups with IIS , SQL Server, and memory keywords.
This sounds reminiscent of a question in the microsoft admin test that I took years ago.
HTH
Tom|||Thanks Tom,
It is not on the same server. I am getting error like below:
Exception occurred in file:
'D:\teamtrack\Source\Db\AppRecord.cpp', line 2827.
Resource Msg Id = 451
The '' record could not be updated in the 'Support Services' database table.
No rows were affected by the update or delete operation.
It looks like a update issue. How should I go about troubleshooting this problem???
Lystra|||In order to narrow down if this is a SQL server problem, or an IIS/Application problem, you coudl get profiler running on the SQL Server to trace all error events. The output will be somewhat confusing, as Profiler will not give you the text of the error message, and some of the messages are actually "normal". I never knew how many "Object not found" errors Enterprise manager could throw.
With this trace, you will find any overt SQL error that the database may be having. Things like the application having timeout issues would be missed by this, as Profiler will only trace server side events.
In order to catch possible timeouts, you could trace for any statement that takes more than 30,000 ms. If you see a lot of these, then you could have either poor database performance, or lock contention.|||I will try using the profiler.
I ran across an article form MS Knowledge base entitled:
There many not be enought virtual memory with large number of database.
Currently the database size is 1.40 GB, and log size is 208MB.
For the server properties on the memory tab it have User a fixed memory size 1494 and the minimun query memory is fixed at 1024.
Is there a way to check to see if memory is okay for the server itself?
Thanks
Lystra|||HELPPPP!!!
Does anyone have any ideas?
The users are having a hard time running reports, and automatically assume that it is the SQL Database. I have look in all of the logs, I have done a dbcc checkdb and everything looks fine.
However when I go to the IIS server itself in the event logs there are all sorts of error like the one below:
Error occurred in file:
'D:\teamtrack\Source\Servers\NotificationServer\NS ServerEvents.cpp', line 611.
Resource Msg Id = 474
An error occurred while connecting to
the server.
Message id 38340 could not be sent to (email_address.com.
Does this seems like a sql problem?
Please advise.
LystraSQL Server and IIS do not play well on the same server. You may have to throttle SQL Server's use of memory on the box so that IIS is not memory starved. Search the KnowledgeBase site or Google the microsoft.public.* groups with IIS , SQL Server, and memory keywords.
This sounds reminiscent of a question in the microsoft admin test that I took years ago.
HTH
Tom|||Thanks Tom,
It is not on the same server. I am getting error like below:
Exception occurred in file:
'D:\teamtrack\Source\Db\AppRecord.cpp', line 2827.
Resource Msg Id = 451
The '' record could not be updated in the 'Support Services' database table.
No rows were affected by the update or delete operation.
It looks like a update issue. How should I go about troubleshooting this problem???
Lystra|||In order to narrow down if this is a SQL server problem, or an IIS/Application problem, you coudl get profiler running on the SQL Server to trace all error events. The output will be somewhat confusing, as Profiler will not give you the text of the error message, and some of the messages are actually "normal". I never knew how many "Object not found" errors Enterprise manager could throw.
With this trace, you will find any overt SQL error that the database may be having. Things like the application having timeout issues would be missed by this, as Profiler will only trace server side events.
In order to catch possible timeouts, you could trace for any statement that takes more than 30,000 ms. If you see a lot of these, then you could have either poor database performance, or lock contention.|||I will try using the profiler.
I ran across an article form MS Knowledge base entitled:
There many not be enought virtual memory with large number of database.
Currently the database size is 1.40 GB, and log size is 208MB.
For the server properties on the memory tab it have User a fixed memory size 1494 and the minimun query memory is fixed at 1024.
Is there a way to check to see if memory is okay for the server itself?
Thanks
Lystra|||HELPPPP!!!
Does anyone have any ideas?
Sunday, February 19, 2012
Ignoring time stamp in my date parameter
Hi,
I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @.Startdate is fine because the timestamps is at 12:00:00AM but my @.EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.
If someone can help me out with, I would greatly appreciate it.
Thanks,
Hello,
If I understand correctly, you want to include the date that your user selects in your results. The problem is that when a parameter is used, it assumes midnight, so any values that are on that day but have a time other than midnight will not be included. In order to fix this, just add one day to your EndDate parameter.
In your SQL query, add this:
... where DateField >= @.StartDate and DateField < dateadd(d, 1, @.EndDate)
Hope this helps.
Jarret
|||You got it! I've been adding one day when I enter the dates when I test the report. I don't know why I did not think of just adding one day in the code. I guess I was thinking too hard trying to completely ignore the timestamp.
Anyways, I think this will work with the users. THey will definitely be happy that they do not have to type in the timestamp when they run their reports.
Thank you so much!!
|||Glad I could help!
Jarret
Subscribe to:
Posts (Atom)