osmansays,
Combine a trigger and a stored procedure to send a email on updates
--------------------------------
Procedure like :
Create Procedure sp_SMTPMail
@.SenderName varchar(100),
@.SenderAddress varchar(100),
@.RecipientName varchar(100),
@.RecipientAddress varchar(100),
@.Subject varchar(200),
@.Body varchar(8000),
@.MailServer varchar(100) = 'localhost'
AS
SET nocount on
declare @.oMail int
declare @.resultcode int
EXEC @.resultcode = sp_OACreate 'SMTPsvg.Mailer', @.oMail OUT
if @.resultcode = 0
BEGIN
EXEC @.resultcode = sp_OASetProperty @.oMail, 'RemoteHost', @.mailserver
EXEC @.resultcode = sp_OASetProperty @.oMail, 'FromName', @.SenderName
EXEC @.resultcode = sp_OASetProperty @.oMail, 'FromAddress', @.SenderAddress
EXEC @.resultcode = sp_OAMethod @.oMail, 'AddRecipient', NULL, @.RecipientName, @.RecipientAddress
EXEC @.resultcode = sp_OASetProperty @.oMail, 'Subject', @.Subject
EXEC @.resultcode = sp_OASetProperty @.oMail, 'BodyText', @.Body
EXEC @.resultcode = sp_OAMethod @.oMail, 'SendMail', NULL
EXEC sp_OADestroy @.oMail
END
SET nocount off
--------------------------------
Trigger like :
CREATE TRIGGER trgDataChanged on tblData
AFTER UPDATE
AS
BEGIN
exec sp_SMTPMail @.SenderName='me', @.SenderAddress='me@.somewhere.com', @.RecipientName = 'Someone', @.RecipientAddress = 'someone@.someplace.com', @.Subject='SQL Data Change', @.body='data in table tblData has been changed'
END
--------------------------------
If you also want to track the changes you can either translate the query to simple data or show the data that is changed but to view that you need to walk through the recordset with for instance a cursor.
Peter
Showing posts with label record. Show all posts
Showing posts with label record. Show all posts
Friday, March 23, 2012
How can I be notified when record is updated
I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..
How can I automate the column titled as ID NUmber in my database, in VS 2005?
I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type
*i am using VS 2005 and VB language
Go into design view for the table
toward the bottom, you'll see Identity Specification - open that up and set IS IDentity to True
|||Thank you, i have found the 'IS identity' but mine is set to 'No' and is blocked (i can't click on it). should i be using an specific data type; currenly i am using 'Real' is that right? I had to use 'Real' as with other data types the option for 'primary key' was not available.|||
Generally (though it might be possible some other way that I'm not familiar with), the way to do this is with an INT datatype, so it can automatically increment.
Maybe someone else can chime in here, how to do it some other way
|||To set a column as an Identity column the datatype has to be one of the INT types - tinyint, smallint, int, bigint etc.
|||i have used the tinyint to set the IS identity as 'yes', however when i run the database and add a new row to the database the row id is for example 9 instead of 2, even though i have deleted all the previous records and doesn't realise that i have now only 1 row and that the next id should be 2. is there any way to correct this?
|||You need to read up books online about Identity columns. Once a number is assigned to a row, its gone. Even if the row is deleted the number is gone. Any new rows will get the next number. If you were doing this as a test and want to empty the table and reset the identity you can TRUNCATE the table instead of delete. Again, before doing the truncate, read up books online about the command. You can also use DBCC CHEKCIDENT to reset the seed value.
|||Remember, also - tinyint is defined like this:tinyint
0 to 255
So - I'd recommend at least using INT
One last thing also - you said that your column is titled "ID Number"
My (and others) recommend that you remove the space (IDNumber) and that you never put spaces in your field or table names. Believe me, this will cause you less grief in the future.
Wednesday, March 21, 2012
How can i assign customize data to sql report
hi folks,
i want to assign customize data to sql report. i have one record related to a customer. i have its current balance. i have to show in report that after specific months how much its amount will be.
i have to show each years amount that user will get. i am showing report in my aspx page. how can i assign this customize dataset to report
i want to assign customize data to sql report. i have one record related to a customer. i have its current balance. i have to show in report that after specific months how much its amount will be.
i have to show each years amount that user will get. i am showing report in my aspx page. how can i assign this customize dataset to report
Good Morning or Good Evening!
I could be mising something based upon your post - but -
When you develop a report in REporting Services - you have to define a DataSet
The dataset can "grab" data from Sql and you can define a text string (Select whatever, count(1) from whatevertableyouwant)
The dataset could also execute a stored procedure and if you have this information available within your database a T-SQL stored proc ) can be executed via the dataset definition of reporting services - then -
you should have no problems in defining the report display elements
still not sure - but I think not so hard!
best regards.
how can I add a time stamp on a table
How can I know when a record on a table has been modified ?
I want to add a field and fill it with a date/time when the recors is modified
ThanksThe only way I know is to use a trigger (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp) to update the column.
-PatP|||Take a look at Lumigent Log Explorer. It allows you to peep into transaction logs to find out who did what when.|||Date and Time Functions
These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.
This table lists the date and time functions and their determinism property. For more information about function determinism, see Deterministic and Nondeterministic Functions.
Function Determinism
DATEADD Deterministic
DATEDIFF Deterministic
DATENAME Nondeterministic
DATEPART Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week.
DAY Deterministic
GETDATE Nondeterministic
GETUTCDATE Nondeterministic
MONTH Deterministic
YEAR Deterministic
See Also
Functions
1988-2000 Microsoft Corporation. All Rights Reserved.|||USE Northwind
GO
CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY
, Col2 char(1)
, ADD_TS datetime DEFAULT GetDate()
, ADD_BY varchar(255) DEFAULT System_User)
GO
-- OK We don't know who did what when, except when it was added
INSERT INTO myTable99(Col2)
SELECT 'A'
SELECT * FROM myTable99
UPDATE myTable99
SET Col2 = 'B'
WHERE Col1 = 1
SELECT * FROM myTable99
OK
-- OK Lets see what we can do
-- Alter the table to track the updates
ALTER TABLE myTable99 ADD UPDATE_TS datetime
GO
ALTER TABLE myTable99 ADD UPDATE_BY varchar(255)
GO
-- Set up a trigger to do the work
CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE
AS
BEGIN
UPDATE m
SET UPDATE_BY = System_User
, UPDATE_TS = GetDate()
FROM myTable99 m
INNER JOIN inserted i
ON i.Col1 = m.Col1
END
GO
-- viola
INSERT INTO myTable99(Col2)
SELECT 'C'
SELECT * FROM myTable99
UPDATE myTable99
SET Col2 = 'D'
WHERE Col1 = 2
SELECT * FROM myTable99
OK
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO|||If you don't like triggers, then you could re-write the application to use only stored procedures to update the tables, then remove update permissions from the tables, to make sure no one sneaks in the back way.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.
Huh?
And as for using a sproc...it's no guarentee...
No reason not to use a trigger like this...
Anyone?|||SQL Server Books Online
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
...
A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated...
...
A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.
I'm just saying, if he's looking for a field that will automatically update without having to do any coding, a timestamp field will do that.
He never said he needed to know the date/time the record was updated, he said he wanted to know when a record is modified. You'd know the record has been modified when the timestamp field changes.|||Going with the stored procedure requires that the DBAs ensure that the programmers don't try to back-end him/her. This would take a (politically) strong DBA group, that can enforce such a rule. Or being able to revoke that all important update permission, which forces the application to use the stored procedure.|||Stored procedures are not sufficient to guarantee relational or data integrity. Somebody can and will eventually hook directly into the table and bypass your logic.
Yeah, the timestamp updates. But how do you KNOW it updated unless you retain the previous value?
The thing you have to worry about is when a record thinks it has been updated, but actually the new data is the same as the previous data. If you have a value in your database such as gender that is "Male" and run:
Update mytable set gender = 'Male'
... the update trigger will run even though the data has not changed. In cases where this distinction is important, I've solved the problem by running a binarychecksum comparison between the new record and the old record.|||Right. But we don't know how he's using the date/time field, so any specific recommendation is moot without additional details on his requirements.|||... Somebody can and will eventually hook directly into the table and bypass your logic...?
Can you give us an example on how you'd go about doing it?
:rolleyes:|||Doing what?
Hooking into the table?
update table set thecolumn = somebaddatavalue
Implementing better integrity?
Use a trigger.
Checking to see whether the data had changed?
Use something like where binarychecksum(inserted.*) <> binarychecksum(currentdata.*), but I'd have to look up my old code to see exactly what syntax I used. I seem to recall using having to use subqueries to get around some of the limitations of the binarychecksum input parameters.
If nanou9999 is interested, I look it up when I have time.|||That's why I keep saying you have to be able to remove the permission to update the table.|||That's why I keep saying you have to be able to remove the permission to update the table.EXACTLY!!!
So my question to blindman was how he'd go about "hooking" (what a term!) into a table, if ALL permissions are denied, and the only way to affect the data is through stored procedures.
I guess I need to be more elaborate in stating my questions, huh?! ;)
So, blindman, how would you "hook" into a table (...hmmmmm...your update will fail, you know)?|||Perhaps you trust your Database Administrators never to directly change data in a table, but I do not. Or perhaps I just build my database applications to be more robust than you do.
If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense.
Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta... :cool:|||... Or perhaps I just build my database applications to be more robust than you do.
I doubt it, but...Is this a challenge?
...If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense. That's a front-end coder's answer, not an application architect's one, but then I never suspected you to be of that caliber either ;)
...Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta...
And as you see I do, but only to demonstrate that you are not the one to decide whether the thread should be closed or not. BTW, the rest of us don't think of ourselves that high-up-in-the-sky either ;) Get off of your cloud of self-praising and adoration of your superiority, be simpler, and people will love you :p|||EDIT: Nevermind...sql
I want to add a field and fill it with a date/time when the recors is modified
ThanksThe only way I know is to use a trigger (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp) to update the column.
-PatP|||Take a look at Lumigent Log Explorer. It allows you to peep into transaction logs to find out who did what when.|||Date and Time Functions
These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.
This table lists the date and time functions and their determinism property. For more information about function determinism, see Deterministic and Nondeterministic Functions.
Function Determinism
DATEADD Deterministic
DATEDIFF Deterministic
DATENAME Nondeterministic
DATEPART Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week.
DAY Deterministic
GETDATE Nondeterministic
GETUTCDATE Nondeterministic
MONTH Deterministic
YEAR Deterministic
See Also
Functions
1988-2000 Microsoft Corporation. All Rights Reserved.|||USE Northwind
GO
CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY
, Col2 char(1)
, ADD_TS datetime DEFAULT GetDate()
, ADD_BY varchar(255) DEFAULT System_User)
GO
-- OK We don't know who did what when, except when it was added
INSERT INTO myTable99(Col2)
SELECT 'A'
SELECT * FROM myTable99
UPDATE myTable99
SET Col2 = 'B'
WHERE Col1 = 1
SELECT * FROM myTable99
OK
-- OK Lets see what we can do
-- Alter the table to track the updates
ALTER TABLE myTable99 ADD UPDATE_TS datetime
GO
ALTER TABLE myTable99 ADD UPDATE_BY varchar(255)
GO
-- Set up a trigger to do the work
CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE
AS
BEGIN
UPDATE m
SET UPDATE_BY = System_User
, UPDATE_TS = GetDate()
FROM myTable99 m
INNER JOIN inserted i
ON i.Col1 = m.Col1
END
GO
-- viola
INSERT INTO myTable99(Col2)
SELECT 'C'
SELECT * FROM myTable99
UPDATE myTable99
SET Col2 = 'D'
WHERE Col1 = 2
SELECT * FROM myTable99
OK
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO|||If you don't like triggers, then you could re-write the application to use only stored procedures to update the tables, then remove update permissions from the tables, to make sure no one sneaks in the back way.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.
Huh?
And as for using a sproc...it's no guarentee...
No reason not to use a trigger like this...
Anyone?|||SQL Server Books Online
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
...
A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated...
...
A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.
I'm just saying, if he's looking for a field that will automatically update without having to do any coding, a timestamp field will do that.
He never said he needed to know the date/time the record was updated, he said he wanted to know when a record is modified. You'd know the record has been modified when the timestamp field changes.|||Going with the stored procedure requires that the DBAs ensure that the programmers don't try to back-end him/her. This would take a (politically) strong DBA group, that can enforce such a rule. Or being able to revoke that all important update permission, which forces the application to use the stored procedure.|||Stored procedures are not sufficient to guarantee relational or data integrity. Somebody can and will eventually hook directly into the table and bypass your logic.
Yeah, the timestamp updates. But how do you KNOW it updated unless you retain the previous value?
The thing you have to worry about is when a record thinks it has been updated, but actually the new data is the same as the previous data. If you have a value in your database such as gender that is "Male" and run:
Update mytable set gender = 'Male'
... the update trigger will run even though the data has not changed. In cases where this distinction is important, I've solved the problem by running a binarychecksum comparison between the new record and the old record.|||Right. But we don't know how he's using the date/time field, so any specific recommendation is moot without additional details on his requirements.|||... Somebody can and will eventually hook directly into the table and bypass your logic...?
Can you give us an example on how you'd go about doing it?
:rolleyes:|||Doing what?
Hooking into the table?
update table set thecolumn = somebaddatavalue
Implementing better integrity?
Use a trigger.
Checking to see whether the data had changed?
Use something like where binarychecksum(inserted.*) <> binarychecksum(currentdata.*), but I'd have to look up my old code to see exactly what syntax I used. I seem to recall using having to use subqueries to get around some of the limitations of the binarychecksum input parameters.
If nanou9999 is interested, I look it up when I have time.|||That's why I keep saying you have to be able to remove the permission to update the table.|||That's why I keep saying you have to be able to remove the permission to update the table.EXACTLY!!!
So my question to blindman was how he'd go about "hooking" (what a term!) into a table, if ALL permissions are denied, and the only way to affect the data is through stored procedures.
I guess I need to be more elaborate in stating my questions, huh?! ;)
So, blindman, how would you "hook" into a table (...hmmmmm...your update will fail, you know)?|||Perhaps you trust your Database Administrators never to directly change data in a table, but I do not. Or perhaps I just build my database applications to be more robust than you do.
If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense.
Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta... :cool:|||... Or perhaps I just build my database applications to be more robust than you do.
I doubt it, but...Is this a challenge?
...If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense. That's a front-end coder's answer, not an application architect's one, but then I never suspected you to be of that caliber either ;)
...Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta...
And as you see I do, but only to demonstrate that you are not the one to decide whether the thread should be closed or not. BTW, the rest of us don't think of ourselves that high-up-in-the-sky either ;) Get off of your cloud of self-praising and adoration of your superiority, be simpler, and people will love you :p|||EDIT: Nevermind...sql
Monday, March 12, 2012
how call stored procedure when user click on report line?
have a way to add ability to report, to do somthing, like delete
record, by calling Stored procedure when user click on some line within
the report?Hey I feel reports should be meant for reporting/Viewing. You should not
provide something like delete or modify options from reports. Then it becomes
an entry screen. I hope you will agree on this point.
Amarnath
"mtczx232@.yahoo.com" wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
>|||mtczx232@.yahoo.com wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
RS can call a stored procedure, or any arbitrary SQL with a side-effect
such as deleting a record.
Your SQL should return a result set as well as deleting the record. You
could navigate to another report, passing a parameter containing the id
of the parameter to delete. This "deleting" report would display
instead of the main report, which could look a bit funny.
Alternatively you could navigate to a URL of an ASP or ASPX page that
does the job. Ideally you would set the target so the web page
displayed as a pop-up with a message like "record xx deleted" instead
of replacing the whole report.
record, by calling Stored procedure when user click on some line within
the report?Hey I feel reports should be meant for reporting/Viewing. You should not
provide something like delete or modify options from reports. Then it becomes
an entry screen. I hope you will agree on this point.
Amarnath
"mtczx232@.yahoo.com" wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
>|||mtczx232@.yahoo.com wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
RS can call a stored procedure, or any arbitrary SQL with a side-effect
such as deleting a record.
Your SQL should return a result set as well as deleting the record. You
could navigate to another report, passing a parameter containing the id
of the parameter to delete. This "deleting" report would display
instead of the main report, which could look a bit funny.
Alternatively you could navigate to a URL of an ASP or ASPX page that
does the job. Ideally you would set the target so the web page
displayed as a pop-up with a message like "record xx deleted" instead
of replacing the whole report.
How big should a table be
I'm absolutely a greenhorn in SQL server. I need to record all the data from
my company. The datas are 24 floating points being logged into a SQL table
every one hour. Datas will be presenting on a website which may have at the
most 10 simultaneously access at one time mainly for company staff.
My opnion is try to keep them in one table so that it's easier for me to
write code to do the search, to create the trend etc because I don't have to
reference to multiple table. But I'm concerning the size of the table.
What I am not sure is: Should I keep them all in one table or into different
table divided by year for example. Given 10 years lifespan of it, will on
table be too big? Anybody can give me some consultancy will be greatly
apprecitated.JL (ljmagzine@.hotmail.com) writes:
> I'm absolutely a greenhorn in SQL server. I need to record all the data
> from my company. The datas are 24 floating points being logged into a
> SQL table every one hour. Datas will be presenting on a website which
> may have at the most 10 simultaneously access at one time mainly for
> company staff.
> My opnion is try to keep them in one table so that it's easier for me to
> write code to do the search, to create the trend etc because I don't
> have to reference to multiple table. But I'm concerning the size of the
> table.
> What I am not sure is: Should I keep them all in one table or into
> different table divided by year for example. Given 10 years lifespan of
> it, will on table be too big? Anybody can give me some consultancy will
> be greatly apprecitated.
If I understand this right, each time you save 25 * 8 bytes of data.
(24 floats + the datetime as key). That's 200 bytes. You do this 24
times a day. There are 365 days a year. That is 1.75 megabytes of data.
That is not much for any commercial DB engine.
Had you had 1.75 gigabytes of data per year, maybe it would have been
worth considering different tables, and then use paritioned views to
access them as one table. But in your case, just go along with one
table.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks a lot!|||"JL" <ljmagzine@.hotmail.com> wrote in message
news:uJyFpMx0DHA.2324@.TK2MSFTNGP09.phx.gbl...
> I'm absolutely a greenhorn in SQL server. I need to record all the data
from
> my company. The datas are 24 floating points being logged into a SQL table
> every one hour. Datas will be presenting on a website which may have at
the
> most 10 simultaneously access at one time mainly for company staff.
> My opnion is try to keep them in one table so that it's easier for me to
> write code to do the search, to create the trend etc because I don't have
to
> reference to multiple table. But I'm concerning the size of the table.
> What I am not sure is: Should I keep them all in one table or into
different
> table divided by year for example. Given 10 years lifespan of it, will on
> table be too big? Anybody can give me some consultancy will be greatly
> apprecitated.
Sounds like your needs are quite small ... you might even look at Access to
hold this information as I think SQL Server would be a bit of overkill.
Also, you might want to get a copy of a book that explains relational
databases so that you can better understand how to group your information
into
appropriate tables. [1]
Irrespective of size, it's better to have a good database design that
requires you to learn new code, then a poor design that will be impossible
to maintain in the future but means you don't need to learn new code.
[1] Something like :
http://www.amazon.co.uk/exec/obidos/ASIN/0764539884/qid=1073329045/sr=1-3/ref=sr_1_2_3/202-1193909-5035865
or
http://www.amazon.co.uk/exec/obidos/ASIN/0764507753/qid=1073329104/sr=1-2/ref=sr_1_3_2/202-1193909-5035865
my company. The datas are 24 floating points being logged into a SQL table
every one hour. Datas will be presenting on a website which may have at the
most 10 simultaneously access at one time mainly for company staff.
My opnion is try to keep them in one table so that it's easier for me to
write code to do the search, to create the trend etc because I don't have to
reference to multiple table. But I'm concerning the size of the table.
What I am not sure is: Should I keep them all in one table or into different
table divided by year for example. Given 10 years lifespan of it, will on
table be too big? Anybody can give me some consultancy will be greatly
apprecitated.JL (ljmagzine@.hotmail.com) writes:
> I'm absolutely a greenhorn in SQL server. I need to record all the data
> from my company. The datas are 24 floating points being logged into a
> SQL table every one hour. Datas will be presenting on a website which
> may have at the most 10 simultaneously access at one time mainly for
> company staff.
> My opnion is try to keep them in one table so that it's easier for me to
> write code to do the search, to create the trend etc because I don't
> have to reference to multiple table. But I'm concerning the size of the
> table.
> What I am not sure is: Should I keep them all in one table or into
> different table divided by year for example. Given 10 years lifespan of
> it, will on table be too big? Anybody can give me some consultancy will
> be greatly apprecitated.
If I understand this right, each time you save 25 * 8 bytes of data.
(24 floats + the datetime as key). That's 200 bytes. You do this 24
times a day. There are 365 days a year. That is 1.75 megabytes of data.
That is not much for any commercial DB engine.
Had you had 1.75 gigabytes of data per year, maybe it would have been
worth considering different tables, and then use paritioned views to
access them as one table. But in your case, just go along with one
table.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks a lot!|||"JL" <ljmagzine@.hotmail.com> wrote in message
news:uJyFpMx0DHA.2324@.TK2MSFTNGP09.phx.gbl...
> I'm absolutely a greenhorn in SQL server. I need to record all the data
from
> my company. The datas are 24 floating points being logged into a SQL table
> every one hour. Datas will be presenting on a website which may have at
the
> most 10 simultaneously access at one time mainly for company staff.
> My opnion is try to keep them in one table so that it's easier for me to
> write code to do the search, to create the trend etc because I don't have
to
> reference to multiple table. But I'm concerning the size of the table.
> What I am not sure is: Should I keep them all in one table or into
different
> table divided by year for example. Given 10 years lifespan of it, will on
> table be too big? Anybody can give me some consultancy will be greatly
> apprecitated.
Sounds like your needs are quite small ... you might even look at Access to
hold this information as I think SQL Server would be a bit of overkill.
Also, you might want to get a copy of a book that explains relational
databases so that you can better understand how to group your information
into
appropriate tables. [1]
Irrespective of size, it's better to have a good database design that
requires you to learn new code, then a poor design that will be impossible
to maintain in the future but means you don't need to learn new code.
[1] Something like :
http://www.amazon.co.uk/exec/obidos/ASIN/0764539884/qid=1073329045/sr=1-3/ref=sr_1_2_3/202-1193909-5035865
or
http://www.amazon.co.uk/exec/obidos/ASIN/0764507753/qid=1073329104/sr=1-2/ref=sr_1_3_2/202-1193909-5035865
How best to update a record from another record in the same table?
(SQL Server 2000, SP3)
Hello all!
I have a seemingly easy question that seems to stump me whenever I come across it, and was
hoping to get some clarification.
Consider the following:
create table Test (Id int, Value1 sysname, Value2 sysname)
go
insert into Test values (1, 'Test1', 'Another1')
insert into Test values (2, 'Test2', 'Another2')
go
I would like to update the first record with some of the fields in the second record.
Part of my confusion lies in the fact that you can't alias the table to be UPDATEd. My
first attempt looks something like this:
update Test
set Test.Value2 = Value2
from Test as t2
where Test.Id = 1
and t2.Id = 2
Which gives me the following error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Test' does not match with a table name or alias name used in the query.
So I tried to remove the Test reference in the WHERE clause:
update Test
set Test.Value2 = Value2
from Test as t2
where Id = 1
and t2.Id = 2
And this gives me 0 records affected -- perhaps like it's using the t2.Id implicitly.
I could do something like this:
update Test
set Test.Value2 = (select Value2 from Test where Id = 2)
where Id = 1
And that works, but I'd hate to reference Test for as many times as I might have fields to
update in the SET clause.
I'd appreciate any pointers that anyone might be able to provide! :-)
John PetersonEither one would do...
update t1
set Value2=t2.Value2
from Test t1, Test t2
where t1.Id=1 and t2.Id=2
update t1
set Value2=t2.Value2
from Test t1 join Test t2
on t1.Id=1 and t2.Id=2
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I have a seemingly easy question that seems to stump me whenever I come
across it, and was
> hoping to get some clarification.
> Consider the following:
> create table Test (Id int, Value1 sysname, Value2 sysname)
> go
> insert into Test values (1, 'Test1', 'Another1')
> insert into Test values (2, 'Test2', 'Another2')
> go
> I would like to update the first record with some of the fields in the
second record.
> Part of my confusion lies in the fact that you can't alias the table to be
UPDATEd. My
> first attempt looks something like this:
> update Test
> set Test.Value2 = Value2
> from Test as t2
> where Test.Id = 1
> and t2.Id = 2
> Which gives me the following error:
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'Test' does not match with a table name or alias name
used in the query.
> So I tried to remove the Test reference in the WHERE clause:
> update Test
> set Test.Value2 = Value2
> from Test as t2
> where Id = 1
> and t2.Id = 2
> And this gives me 0 records affected -- perhaps like it's using the t2.Id
implicitly.
> I could do something like this:
> update Test
> set Test.Value2 = (select Value2 from Test where Id = 2)
> where Id = 1
> And that works, but I'd hate to reference Test for as many times as I
might have fields to
> update in the SET clause.
> I'd appreciate any pointers that anyone might be able to provide! :-)
> John Peterson
>|||Thanks, oj!
I didn't realize that you could do an alias after the UPDATE, and then specify the actual
table name in the FROM clause. Very interesting!
Thanks again!
John Peterson
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23Ya0TdsXDHA.652@.TK2MSFTNGP10.phx.gbl...
> Either one would do...
> update t1
> set Value2=t2.Value2
> from Test t1, Test t2
> where t1.Id=1 and t2.Id=2
> update t1
> set Value2=t2.Value2
> from Test t1 join Test t2
> on t1.Id=1 and t2.Id=2
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I have a seemingly easy question that seems to stump me whenever I come
> across it, and was
> > hoping to get some clarification.
> >
> > Consider the following:
> >
> > create table Test (Id int, Value1 sysname, Value2 sysname)
> > go
> >
> > insert into Test values (1, 'Test1', 'Another1')
> > insert into Test values (2, 'Test2', 'Another2')
> > go
> >
> > I would like to update the first record with some of the fields in the
> second record.
> >
> > Part of my confusion lies in the fact that you can't alias the table to be
> UPDATEd. My
> > first attempt looks something like this:
> >
> > update Test
> > set Test.Value2 = Value2
> > from Test as t2
> > where Test.Id = 1
> > and t2.Id = 2
> >
> > Which gives me the following error:
> >
> > Server: Msg 107, Level 16, State 3, Line 1
> > The column prefix 'Test' does not match with a table name or alias name
> used in the query.
> >
> > So I tried to remove the Test reference in the WHERE clause:
> >
> > update Test
> > set Test.Value2 = Value2
> > from Test as t2
> > where Id = 1
> > and t2.Id = 2
> >
> > And this gives me 0 records affected -- perhaps like it's using the t2.Id
> implicitly.
> >
> > I could do something like this:
> >
> > update Test
> > set Test.Value2 = (select Value2 from Test where Id = 2)
> > where Id = 1
> >
> > And that works, but I'd hate to reference Test for as many times as I
> might have fields to
> > update in the SET clause.
> >
> > I'd appreciate any pointers that anyone might be able to provide! :-)
> >
> > John Peterson
> >
> >
>|||You're welcome, John.
I believe you can find the example and some info regarding this syntax in
sql book online under "update".
--
-oj
Rac v2.2 & QALite
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O9fn6fsXDHA.2204@.TK2MSFTNGP12.phx.gbl...
> Thanks, oj!
> I didn't realize that you could do an alias after the UPDATE, and then
specify the actual
> table name in the FROM clause. Very interesting!
> Thanks again!
> John Peterson
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23Ya0TdsXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > Either one would do...
> >
> > update t1
> > set Value2=t2.Value2
> > from Test t1, Test t2
> > where t1.Id=1 and t2.Id=2
> >
> > update t1
> > set Value2=t2.Value2
> > from Test t1 join Test t2
> > on t1.Id=1 and t2.Id=2
> >
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > I have a seemingly easy question that seems to stump me whenever I
come
> > across it, and was
> > > hoping to get some clarification.
> > >
> > > Consider the following:
> > >
> > > create table Test (Id int, Value1 sysname, Value2 sysname)
> > > go
> > >
> > > insert into Test values (1, 'Test1', 'Another1')
> > > insert into Test values (2, 'Test2', 'Another2')
> > > go
> > >
> > > I would like to update the first record with some of the fields in the
> > second record.
> > >
> > > Part of my confusion lies in the fact that you can't alias the table
to be
> > UPDATEd. My
> > > first attempt looks something like this:
> > >
> > > update Test
> > > set Test.Value2 = Value2
> > > from Test as t2
> > > where Test.Id = 1
> > > and t2.Id = 2
> > >
> > > Which gives me the following error:
> > >
> > > Server: Msg 107, Level 16, State 3, Line 1
> > > The column prefix 'Test' does not match with a table name or alias
name
> > used in the query.
> > >
> > > So I tried to remove the Test reference in the WHERE clause:
> > >
> > > update Test
> > > set Test.Value2 = Value2
> > > from Test as t2
> > > where Id = 1
> > > and t2.Id = 2
> > >
> > > And this gives me 0 records affected -- perhaps like it's using the
t2.Id
> > implicitly.
> > >
> > > I could do something like this:
> > >
> > > update Test
> > > set Test.Value2 = (select Value2 from Test where Id = 2)
> > > where Id = 1
> > >
> > > And that works, but I'd hate to reference Test for as many times as I
> > might have fields to
> > > update in the SET clause.
> > >
> > > I'd appreciate any pointers that anyone might be able to provide! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
Hello all!
I have a seemingly easy question that seems to stump me whenever I come across it, and was
hoping to get some clarification.
Consider the following:
create table Test (Id int, Value1 sysname, Value2 sysname)
go
insert into Test values (1, 'Test1', 'Another1')
insert into Test values (2, 'Test2', 'Another2')
go
I would like to update the first record with some of the fields in the second record.
Part of my confusion lies in the fact that you can't alias the table to be UPDATEd. My
first attempt looks something like this:
update Test
set Test.Value2 = Value2
from Test as t2
where Test.Id = 1
and t2.Id = 2
Which gives me the following error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Test' does not match with a table name or alias name used in the query.
So I tried to remove the Test reference in the WHERE clause:
update Test
set Test.Value2 = Value2
from Test as t2
where Id = 1
and t2.Id = 2
And this gives me 0 records affected -- perhaps like it's using the t2.Id implicitly.
I could do something like this:
update Test
set Test.Value2 = (select Value2 from Test where Id = 2)
where Id = 1
And that works, but I'd hate to reference Test for as many times as I might have fields to
update in the SET clause.
I'd appreciate any pointers that anyone might be able to provide! :-)
John PetersonEither one would do...
update t1
set Value2=t2.Value2
from Test t1, Test t2
where t1.Id=1 and t2.Id=2
update t1
set Value2=t2.Value2
from Test t1 join Test t2
on t1.Id=1 and t2.Id=2
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I have a seemingly easy question that seems to stump me whenever I come
across it, and was
> hoping to get some clarification.
> Consider the following:
> create table Test (Id int, Value1 sysname, Value2 sysname)
> go
> insert into Test values (1, 'Test1', 'Another1')
> insert into Test values (2, 'Test2', 'Another2')
> go
> I would like to update the first record with some of the fields in the
second record.
> Part of my confusion lies in the fact that you can't alias the table to be
UPDATEd. My
> first attempt looks something like this:
> update Test
> set Test.Value2 = Value2
> from Test as t2
> where Test.Id = 1
> and t2.Id = 2
> Which gives me the following error:
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'Test' does not match with a table name or alias name
used in the query.
> So I tried to remove the Test reference in the WHERE clause:
> update Test
> set Test.Value2 = Value2
> from Test as t2
> where Id = 1
> and t2.Id = 2
> And this gives me 0 records affected -- perhaps like it's using the t2.Id
implicitly.
> I could do something like this:
> update Test
> set Test.Value2 = (select Value2 from Test where Id = 2)
> where Id = 1
> And that works, but I'd hate to reference Test for as many times as I
might have fields to
> update in the SET clause.
> I'd appreciate any pointers that anyone might be able to provide! :-)
> John Peterson
>|||Thanks, oj!
I didn't realize that you could do an alias after the UPDATE, and then specify the actual
table name in the FROM clause. Very interesting!
Thanks again!
John Peterson
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23Ya0TdsXDHA.652@.TK2MSFTNGP10.phx.gbl...
> Either one would do...
> update t1
> set Value2=t2.Value2
> from Test t1, Test t2
> where t1.Id=1 and t2.Id=2
> update t1
> set Value2=t2.Value2
> from Test t1 join Test t2
> on t1.Id=1 and t2.Id=2
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I have a seemingly easy question that seems to stump me whenever I come
> across it, and was
> > hoping to get some clarification.
> >
> > Consider the following:
> >
> > create table Test (Id int, Value1 sysname, Value2 sysname)
> > go
> >
> > insert into Test values (1, 'Test1', 'Another1')
> > insert into Test values (2, 'Test2', 'Another2')
> > go
> >
> > I would like to update the first record with some of the fields in the
> second record.
> >
> > Part of my confusion lies in the fact that you can't alias the table to be
> UPDATEd. My
> > first attempt looks something like this:
> >
> > update Test
> > set Test.Value2 = Value2
> > from Test as t2
> > where Test.Id = 1
> > and t2.Id = 2
> >
> > Which gives me the following error:
> >
> > Server: Msg 107, Level 16, State 3, Line 1
> > The column prefix 'Test' does not match with a table name or alias name
> used in the query.
> >
> > So I tried to remove the Test reference in the WHERE clause:
> >
> > update Test
> > set Test.Value2 = Value2
> > from Test as t2
> > where Id = 1
> > and t2.Id = 2
> >
> > And this gives me 0 records affected -- perhaps like it's using the t2.Id
> implicitly.
> >
> > I could do something like this:
> >
> > update Test
> > set Test.Value2 = (select Value2 from Test where Id = 2)
> > where Id = 1
> >
> > And that works, but I'd hate to reference Test for as many times as I
> might have fields to
> > update in the SET clause.
> >
> > I'd appreciate any pointers that anyone might be able to provide! :-)
> >
> > John Peterson
> >
> >
>|||You're welcome, John.
I believe you can find the example and some info regarding this syntax in
sql book online under "update".
--
-oj
Rac v2.2 & QALite
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O9fn6fsXDHA.2204@.TK2MSFTNGP12.phx.gbl...
> Thanks, oj!
> I didn't realize that you could do an alias after the UPDATE, and then
specify the actual
> table name in the FROM clause. Very interesting!
> Thanks again!
> John Peterson
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23Ya0TdsXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > Either one would do...
> >
> > update t1
> > set Value2=t2.Value2
> > from Test t1, Test t2
> > where t1.Id=1 and t2.Id=2
> >
> > update t1
> > set Value2=t2.Value2
> > from Test t1 join Test t2
> > on t1.Id=1 and t2.Id=2
> >
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > I have a seemingly easy question that seems to stump me whenever I
come
> > across it, and was
> > > hoping to get some clarification.
> > >
> > > Consider the following:
> > >
> > > create table Test (Id int, Value1 sysname, Value2 sysname)
> > > go
> > >
> > > insert into Test values (1, 'Test1', 'Another1')
> > > insert into Test values (2, 'Test2', 'Another2')
> > > go
> > >
> > > I would like to update the first record with some of the fields in the
> > second record.
> > >
> > > Part of my confusion lies in the fact that you can't alias the table
to be
> > UPDATEd. My
> > > first attempt looks something like this:
> > >
> > > update Test
> > > set Test.Value2 = Value2
> > > from Test as t2
> > > where Test.Id = 1
> > > and t2.Id = 2
> > >
> > > Which gives me the following error:
> > >
> > > Server: Msg 107, Level 16, State 3, Line 1
> > > The column prefix 'Test' does not match with a table name or alias
name
> > used in the query.
> > >
> > > So I tried to remove the Test reference in the WHERE clause:
> > >
> > > update Test
> > > set Test.Value2 = Value2
> > > from Test as t2
> > > where Id = 1
> > > and t2.Id = 2
> > >
> > > And this gives me 0 records affected -- perhaps like it's using the
t2.Id
> > implicitly.
> > >
> > > I could do something like this:
> > >
> > > update Test
> > > set Test.Value2 = (select Value2 from Test where Id = 2)
> > > where Id = 1
> > >
> > > And that works, but I'd hate to reference Test for as many times as I
> > might have fields to
> > > update in the SET clause.
> > >
> > > I'd appreciate any pointers that anyone might be able to provide! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
Friday, March 9, 2012
How are pictures stored in a SQL database ??
Are they actually broken down into a binary record entry, or is there a jpg
file sitting out there somewhere that I haven't yet found ?I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?|||What needs to be done to that binary blob to display a picture on a web
site?
================================================<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegroups.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>> Are they actually broken down into a binary record entry, or is there a
>> jpg
>> file sitting out there somewhere that I haven't yet found ?
>|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the suggestion. This database is a production canned database
application, and I am assuming that there is no way to modify their product
to store pictures in a different way. So, with the understanding that I
know less then I don't know, Is this product only used in the development
realm, or can it be implemented into a database system which is already
storing pictures in blobs? :-) lol thanks..
=============================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE450B.7030409@.realsqlguy.com...
> WANNABE wrote:
>> Are they actually broken down into a binary record entry, or is there a
>> jpg file sitting out there somewhere that I haven't yet found ?
> http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
file sitting out there somewhere that I haven't yet found ?I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?|||What needs to be done to that binary blob to display a picture on a web
site?
================================================<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegroups.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>> Are they actually broken down into a binary record entry, or is there a
>> jpg
>> file sitting out there somewhere that I haven't yet found ?
>|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the suggestion. This database is a production canned database
application, and I am assuming that there is no way to modify their product
to store pictures in a different way. So, with the understanding that I
know less then I don't know, Is this product only used in the development
realm, or can it be implemented into a database system which is already
storing pictures in blobs? :-) lol thanks..
=============================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE450B.7030409@.realsqlguy.com...
> WANNABE wrote:
>> Are they actually broken down into a binary record entry, or is there a
>> jpg file sitting out there somewhere that I haven't yet found ?
> http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
How are pictures stored in a SQL database ??
Are they actually broken down into a binary record entry, or is there a jpg
file sitting out there somewhere that I haven't yet found ?
I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
|||What needs to be done to that binary blob to display a picture on a web
site?
================================================
<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegrou ps.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>
|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
file sitting out there somewhere that I haven't yet found ?
I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
|||What needs to be done to that binary blob to display a picture on a web
site?
================================================
<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegrou ps.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>
|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jpg
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
How are pictures stored in a SQL database ??
Are they actually broken down into a binary record entry, or is there a jpg
file sitting out there somewhere that I haven't yet found ?I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jp
g
> file sitting out there somewhere that I haven't yet found ?|||What needs to be done to that binary blob to display a picture on a web
site?
========================================
========
<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegroups.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>
>|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jp
g
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/databas...filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the suggestion. This database is a production canned database
application, and I am assuming that there is no way to modify their product
to store pictures in a different way. So, with the understanding that I
know less then I don't know, Is this product only used in the development
realm, or can it be implemented into a database system which is already
storing pictures in blobs? :-) lol thanks..
========================================
======
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE450B.7030409@.realsqlguy.com...
> WANNABE wrote:
> http://databases.aspfaq.com/databas...filesystem.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
file sitting out there somewhere that I haven't yet found ?I believe that they are stored as a binary blob in the record's entry,
not as a file elsewhere.
R.
WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jp
g
> file sitting out there somewhere that I haven't yet found ?|||What needs to be done to that binary blob to display a picture on a web
site?
========================================
========
<slaught@.vault13.co.uk> wrote in message
news:1169046978.563378.93060@.11g2000cwr.googlegroups.com...
>I believe that they are stored as a binary blob in the record's entry,
> not as a file elsewhere.
> R.
> WANNABE wrote:
>
>|||WANNABE wrote:
> Are they actually broken down into a binary record entry, or is there a jp
g
> file sitting out there somewhere that I haven't yet found ?
>
http://databases.aspfaq.com/databas...filesystem.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the suggestion. This database is a production canned database
application, and I am assuming that there is no way to modify their product
to store pictures in a different way. So, with the understanding that I
know less then I don't know, Is this product only used in the development
realm, or can it be implemented into a database system which is already
storing pictures in blobs? :-) lol thanks..
========================================
======
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE450B.7030409@.realsqlguy.com...
> WANNABE wrote:
> http://databases.aspfaq.com/databas...filesystem.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Please take a look at this article : "HOW TO: Read and Write BLOB Data by
Using ADO.NET Through ASP.NET": http://support.microsoft.com/kb/326502
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Sunday, February 19, 2012
HostName Function with Access2K front end
In an Insert Into statement I have used the Host_Name() function to
identify which user has suppied a record to a table that holds
temporary data.
I'm using an Access2K front end.
identify which user has suppied a record to a table that holds
temporary data.
I'm using an Access2K front end.
Code:
Alter procedure SPName
@.parameter1 int
AS
Set nocount On
Set xact_abort off
Declare @.myHost nvarchar(50)
Set @.myHost = Host_Name()
Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,
myMachineName)
SELECT tblMyName.UniqueID, AnyField1, AnyField2, @.myHost
FROM tblMyName
WHERE tblMyName.UniqueID = @.parameter1
I have two problems.
In some cases (and only on one or two of maybe about 250 client
workstations) Host_Name() returns the name of MY machine. I'm thinking
this is because I developed the app and distributed it and for some
reason it's retaining the info contained in my original connection
setup set in the MS-Access Connection window?
Also, on some occasions, I get blocking messages in my trace log
following this operation.
Any help on these two issues is appreciated.
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404100634.3ab98872@.posting.google.c om...
> In an Insert Into statement I have used the Host_Name() function to
> identify which user has suppied a record to a table that holds
> temporary data.
> I'm using an Access2K front end.
> Code:
> Alter procedure SPName
> @.parameter1 int
> AS
> Set nocount On
> Set xact_abort off
> Declare @.myHost nvarchar(50)
> Set @.myHost = Host_Name()
> Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,
> myMachineName)
> SELECT tblMyName.UniqueID, AnyField1, AnyField2, @.myHost
> FROM tblMyName
> WHERE tblMyName.UniqueID = @.parameter1
> I have two problems.
> In some cases (and only on one or two of maybe about 250 client
> workstations) Host_Name() returns the name of MY machine. I'm thinking
> this is because I developed the app and distributed it and for some
> reason it's retaining the info contained in my original connection
> setup set in the MS-Access Connection window?
> Also, on some occasions, I get blocking messages in my trace log
> following this operation.
> Any help on these two issues is appreciated.
> lq
As a complete guess, there may be some network name resolution issue which
means that the server sometimes resolves workstation names incorrectly. You
should be able to investigate this with your network admin if you can pin it
down to just a couple of machines.
As for the blocking issue, it's hard to say without more information, such
as the text of the errors.
Simon
Subscribe to:
Posts (Atom)