Sunday, March 25, 2012
Best Practise For Updates between Access 2000 and SQL Server
Let me paint a picture...Access 2000 frontend, linking to a SQL Server
backend, inherited database, me - fairly new at this! :o)
The developer has, for better or for worse, (I'm not sure), designed a
frontend that grabs a set of order records from SQL and pulls them into
a form. While the records are there, the user can allocate the order to
a person by selecting their name, we would only be talking about 300
records at a time. When the user closes the relevant form, the orders
that have been updated in the frontend are sent to the SQL-Server.
Now, from what I can make of the code that Updates back to SQL, the
developer initially grabs a recordset of those orders in the Frontend
requiring an update and then loops through them one by one and updates
the SQL-Server record using a field call OrderID.
My questions are: Is this the only method of doing this? Meaning -
surely there is a way to do a bulk Update between the two applications
that doesn't require cycling through records.
And what is considered best practise for doing bulk record updates
between Access and SQL-Server? I could probaly re-invent the wheel
myself, but if anyone can point me in the right direction it would be
appreciated.
Kind RegardsThis is not the right forum.
--
Regards
R.D
--Knowledge gets doubled when shared
"PeteP" wrote:
> Hi,
>
> Let me paint a picture...Access 2000 frontend, linking to a SQL Server
> backend, inherited database, me - fairly new at this! :o)
>
> The developer has, for better or for worse, (I'm not sure), designed a
> frontend that grabs a set of order records from SQL and pulls them into
> a form. While the records are there, the user can allocate the order to
> a person by selecting their name, we would only be talking about 300
> records at a time. When the user closes the relevant form, the orders
> that have been updated in the frontend are sent to the SQL-Server.
>
> Now, from what I can make of the code that Updates back to SQL, the
> developer initially grabs a recordset of those orders in the Frontend
> requiring an update and then loops through them one by one and updates
> the SQL-Server record using a field call OrderID.
>
> My questions are: Is this the only method of doing this? Meaning -
> surely there is a way to do a bulk Update between the two applications
> that doesn't require cycling through records.
>
> And what is considered best practise for doing bulk record updates
> between Access and SQL-Server? I could probaly re-invent the wheel
> myself, but if anyone can point me in the right direction it would be
> appreciated.
>
> Kind Regards
>|||Petep
This is the right forum for you. There is a seperate forum for acceess/sql
integration post there.
What you described is not the right method. probably they are using mdb not
adp.
SQL SERVER integrates well with access in adp mode. you can do set based
operations as you do in sql. In this mode data will completely reside in sql
.
so you can write spocs/functions what not, everything in sql.
Regards
R.D
--Knowledge gets doubled when shared
"PeteP" wrote:
> Hi,
>
> Let me paint a picture...Access 2000 frontend, linking to a SQL Server
> backend, inherited database, me - fairly new at this! :o)
>
> The developer has, for better or for worse, (I'm not sure), designed a
> frontend that grabs a set of order records from SQL and pulls them into
> a form. While the records are there, the user can allocate the order to
> a person by selecting their name, we would only be talking about 300
> records at a time. When the user closes the relevant form, the orders
> that have been updated in the frontend are sent to the SQL-Server.
>
> Now, from what I can make of the code that Updates back to SQL, the
> developer initially grabs a recordset of those orders in the Frontend
> requiring an update and then loops through them one by one and updates
> the SQL-Server record using a field call OrderID.
>
> My questions are: Is this the only method of doing this? Meaning -
> surely there is a way to do a bulk Update between the two applications
> that doesn't require cycling through records.
>
> And what is considered best practise for doing bulk record updates
> between Access and SQL-Server? I could probaly re-invent the wheel
> myself, but if anyone can point me in the right direction it would be
> appreciated.
>
> Kind Regards
>
Monday, March 19, 2012
Best Practice for SQL Server Null Values / Empty Strings
For example, suppose I have a 'Phone' field that is often, but not always, filled in. If the user blanks out a phone number, the .NET DataAdapter .Update method will save the field as an empty string instead of a NULL. This of course makes every query more complex having to check for both nulls and empty strings
Is there any practical way to prevent, at the database level, the empty strings from getting into the database? (Perhaps triggers or some global setting?) Or should the string fields be empty strings and never nulls...? I could re-write the data adapter, but I don't know if I can trust that every program that touches the database will have handled the issue correctly
Any opionions
Thanks
Denis
using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by default it sometimes saves them as an empty stringYou are totally right in your reseaech of null fields, did
you know that if you perform a string concatination with a
null it will always result in a null i.e
@.Forename = 'Denise'
@.Middlename = null
@.Surname = 'Smith'
Set @.Fullname = @.Forename + ' ' + @.Middlename + ' ' +
@.Surname
Will mean @.Fullname will be null.
However Nulls can also be useful i.e looking for NOT NULL,
and see the COALESCE statement, it really up to you.
If you want to get rid of null then you can use defaults.
The default will change a null to anything you want it to
be i.e '' or empty string.
To create a default
1. In EA go to the database
2. Select Defualts
3. Right click - select new defaults
4. Give it a name such as 'EmptyString'
Then when you create a column you can assign it the
default. This however will only work for new records and
not for existing ones.
J
>--Original Message--
>I'm fairly new to SQL Server. Coming from Acces, I see
that Null values are handled differently. I've read many
of the posts on querying Null values, but I want to know
what is the best practice for designing a new system (SQL
Server 2000) that could contain empty fields.
>For example, suppose I have a 'Phone' field that is
often, but not always, filled in. If the user blanks out
a phone number, the .NET DataAdapter .Update method will
save the field as an empty string instead of a NULL. This
of course makes every query more complex having to check
for both nulls and empty strings.
>Is there any practical way to prevent, at the database
level, the empty strings from getting into the database?
(Perhaps triggers or some global setting?) Or should the
string fields be empty strings and never nulls...? I
could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will
have handled the issue correctly.
>Any opionions?
>Thanks,
>Denise
>
>using VB.Net and ADO.Net code and if the user blanks out
a field, ADO.Net by default it sometimes saves them as an
empty string
>.
>|||Denise,
I'll give you my opinion, for what it's worth.
A NULL means an unknown state. Therefore if you do not know the person's
phone number then it is unknown, therefore NULL.
An empty string is a positive entry into the database. It could be
interpreted as, "I KNOW that this value is empty" and could therefore be
interpreted as "this person doesn't have a phone".
This is a subtle difference to NULL. NULL just means "I don't know", or
unknown state. I doubt there is any performance difference between the
two, however I haven't tested it.
I'm not sure if I've actually answered your question because you need to
ensure you are passing NULLs to the database and not empty strings in
your data access layer code. If you want to prevent empty strings from
entering the database, then you could use a constraint like this:
--
create table a (i int not null, c varchar(30) null check (c <> ''))
insert a (i,c) values (1,null) -- succeeds
insert a (i,c) values (2,'') -- fails
select * from a
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Denise wrote:
> I'm fairly new to SQL Server. Coming from Acces, I see that Null
values are handled differently. I've read many of the posts on querying
Null values, but I want to know what is the best practice for designing
a new system (SQL Server 2000) that could contain empty fields.
> For example, suppose I have a 'Phone' field that is often, but not
always, filled in. If the user blanks out a phone number, the .NET
DataAdapter .Update method will save the field as an empty string
instead of a NULL. This of course makes every query more complex having
to check for both nulls and empty strings.
> Is there any practical way to prevent, at the database level, the
empty strings from getting into the database? (Perhaps triggers or some
global setting?) Or should the string fields be empty strings and never
nulls...? I could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will have handled the
issue correctly.
> Any opionions?
> Thanks, Denise
>
> using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by default it sometimes saves them as an empty string|||Julie,
(just to point out that the first part of your response is not always
necessarily the case:)
SET CONCAT_NULL_YIELDS_NULL ON
select null + 'hello'
SET CONCAT_NULL_YIELDS_NULL OFF
select null + 'hello'
Regards,
Paul Ibison|||There are strong debates regarding whether or not nulls should ever be
allowed in data columns. I agree with Mark, if you do not know the value
allow null ( even though it may require programming on the front end.)
Others ( Kalen Delaney for instance) make strong arguments for never
allowing nulls in the database.
This is an area where reasonable people differ in their opinions, so do
whatever works for you, with a clear conscience..
--
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
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O07qtuHSEHA.2000@.TK2MSFTNGP11.phx.gbl...
> Denise,
> I'll give you my opinion, for what it's worth.
> A NULL means an unknown state. Therefore if you do not know the person's
> phone number then it is unknown, therefore NULL.
> An empty string is a positive entry into the database. It could be
> interpreted as, "I KNOW that this value is empty" and could therefore be
> interpreted as "this person doesn't have a phone".
> This is a subtle difference to NULL. NULL just means "I don't know", or
> unknown state. I doubt there is any performance difference between the
> two, however I haven't tested it.
> I'm not sure if I've actually answered your question because you need to
> ensure you are passing NULLs to the database and not empty strings in
> your data access layer code. If you want to prevent empty strings from
> entering the database, then you could use a constraint like this:
> --
> create table a (i int not null, c varchar(30) null check (c <> ''))
> insert a (i,c) values (1,null) -- succeeds
> insert a (i,c) values (2,'') -- fails
> select * from a
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Denise wrote:
> > I'm fairly new to SQL Server. Coming from Acces, I see that Null
> values are handled differently. I've read many of the posts on querying
> Null values, but I want to know what is the best practice for designing
> a new system (SQL Server 2000) that could contain empty fields.
> >
> > For example, suppose I have a 'Phone' field that is often, but not
> always, filled in. If the user blanks out a phone number, the .NET
> DataAdapter .Update method will save the field as an empty string
> instead of a NULL. This of course makes every query more complex having
> to check for both nulls and empty strings.
> >
> > Is there any practical way to prevent, at the database level, the
> empty strings from getting into the database? (Perhaps triggers or some
> global setting?) Or should the string fields be empty strings and never
> nulls...? I could re-write the data adapter, but I don't know if I can
> trust that every program that touches the database will have handled the
> issue correctly.
> >
> > Any opionions?
> >
> > Thanks, Denise
> >
> >
> > using VB.Net and ADO.Net code and if the user blanks out a field,
ADO.Net by default it sometimes saves them as an empty string|||you've definately opened a can of worms. (Religious topic)
Many argue that nulls suggest a normalization problem.
Nulls will cause performance issues
Nulls will force you to add handling into your sprocs etc.
it's your call of course whether or not you wish to use them.
I lean towards not using them except in rare situations, but that's just me.
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for all your insight.
Sunday, March 11, 2012
Best Practice for SQL Server Null Values / Empty Strings
handled differently. I've read many of the posts on querying Null values,
but I want to know what is the best practice for designing a new system (SQL
Server 2000) that could co
ntain empty fields.
For example, suppose I have a 'Phone' field that is often, but not always, f
illed in. If the user blanks out a phone number, the .NET DataAdapter .Upda
te method will save the field as an empty string instead of a NULL. This of
course makes every query m
ore complex having to check for both nulls and empty strings.
Is there any practical way to prevent, at the database level, the empty stri
ngs from getting into the database? (Perhaps triggers or some global settin
g?) Or should the string fields be empty strings and never nulls...? I cou
ld re-write the data adapte
r, but I don't know if I can trust that every program that touches the datab
ase will have handled the issue correctly.
Any opionions?
Thanks,
Denise
using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by
default it sometimes saves them as an empty stringDenise,
I'll give you my opinion, for what it's worth.
A NULL means an unknown state. Therefore if you do not know the person's
phone number then it is unknown, therefore NULL.
An empty string is a positive entry into the database. It could be
interpreted as, "I KNOW that this value is empty" and could therefore be
interpreted as "this person doesn't have a phone".
This is a subtle difference to NULL. NULL just means "I don't know", or
unknown state. I doubt there is any performance difference between the
two, however I haven't tested it.
I'm not sure if I've actually answered your question because you need to
ensure you are passing NULLs to the database and not empty strings in
your data access layer code. If you want to prevent empty strings from
entering the database, then you could use a constraint like this:
create table a (i int not null, c varchar(30) null check (c <> ''))
insert a (i,c) values (1,null) -- succeeds
insert a (i,c) values (2,'') -- fails
select * from a
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Denise wrote:
> I'm fairly new to SQL Server. Coming from Acces, I see that Null
values are handled differently. I've read many of the posts on querying
Null values, but I want to know what is the best practice for designing
a new system (SQL Server 2000) that could contain empty fields.
> For example, suppose I have a 'Phone' field that is often, but not
always, filled in. If the user blanks out a phone number, the .NET
DataAdapter .Update method will save the field as an empty string
instead of a NULL. This of course makes every query more complex having
to check for both nulls and empty strings.
> Is there any practical way to prevent, at the database level, the
empty strings from getting into the database? (Perhaps triggers or some
global setting?) Or should the string fields be empty strings and never
nulls...? I could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will have handled the
issue correctly.
> Any opionions?
> Thanks, Denise
>
> using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by defau
lt it sometimes saves them as an empty string|||There are strong debates regarding whether or not nulls should ever be
allowed in data columns. I agree with Mark, if you do not know the value
allow null ( even though it may require programming on the front end.)
Others ( Kalen Delaney for instance) make strong arguments for never
allowing nulls in the database.
This is an area where reasonable people differ in their opinions, so do
whatever works for you, with a clear conscience..
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
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O07qtuHSEHA.2000@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Denise,
> I'll give you my opinion, for what it's worth.
> A NULL means an unknown state. Therefore if you do not know the person's
> phone number then it is unknown, therefore NULL.
> An empty string is a positive entry into the database. It could be
> interpreted as, "I KNOW that this value is empty" and could therefore be
> interpreted as "this person doesn't have a phone".
> This is a subtle difference to NULL. NULL just means "I don't know", or
> unknown state. I doubt there is any performance difference between the
> two, however I haven't tested it.
> I'm not sure if I've actually answered your question because you need to
> ensure you are passing NULLs to the database and not empty strings in
> your data access layer code. If you want to prevent empty strings from
> entering the database, then you could use a constraint like this:
> --
> create table a (i int not null, c varchar(30) null check (c <> ''))
> insert a (i,c) values (1,null) -- succeeds
> insert a (i,c) values (2,'') -- fails
> select * from a
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Denise wrote:
> values are handled differently. I've read many of the posts on querying
> Null values, but I want to know what is the best practice for designing
> a new system (SQL Server 2000) that could contain empty fields.
> always, filled in. If the user blanks out a phone number, the .NET
> DataAdapter .Update method will save the field as an empty string
> instead of a NULL. This of course makes every query more complex having
> to check for both nulls and empty strings.
> empty strings from getting into the database? (Perhaps triggers or some
> global setting?) Or should the string fields be empty strings and never
> nulls...? I could re-write the data adapter, but I don't know if I can
> trust that every program that touches the database will have handled the
> issue correctly.
ADO.Net by default it sometimes saves them as an empty string|||you've definately opened a can of worms. (Religious topic)
Many argue that nulls suggest a normalization problem.
Nulls will cause performance issues
Nulls will force you to add handling into your sprocs etc.
it's your call of course whether or not you wish to use them.
I lean towards not using them except in rare situations, but that's just me.
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for all your insight.
Best Practice for SQL Server Null Values / Empty Strings
ntain empty fields.
For example, suppose I have a 'Phone' field that is often, but not always, filled in. If the user blanks out a phone number, the .NET DataAdapter .Update method will save the field as an empty string instead of a NULL. This of course makes every query m
ore complex having to check for both nulls and empty strings.
Is there any practical way to prevent, at the database level, the empty strings from getting into the database? (Perhaps triggers or some global setting?) Or should the string fields be empty strings and never nulls...? I could re-write the data adapte
r, but I don't know if I can trust that every program that touches the database will have handled the issue correctly.
Any opionions?
Thanks,
Denise
using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by default it sometimes saves them as an empty string
Denise,
I'll give you my opinion, for what it's worth.
A NULL means an unknown state. Therefore if you do not know the person's
phone number then it is unknown, therefore NULL.
An empty string is a positive entry into the database. It could be
interpreted as, "I KNOW that this value is empty" and could therefore be
interpreted as "this person doesn't have a phone".
This is a subtle difference to NULL. NULL just means "I don't know", or
unknown state. I doubt there is any performance difference between the
two, however I haven't tested it.
I'm not sure if I've actually answered your question because you need to
ensure you are passing NULLs to the database and not empty strings in
your data access layer code. If you want to prevent empty strings from
entering the database, then you could use a constraint like this:
create table a (i int not null, c varchar(30) null check (c <> ''))
insert a (i,c) values (1,null) -- succeeds
insert a (i,c) values (2,'') -- fails
select * from a
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Denise wrote:
> I'm fairly new to SQL Server. Coming from Acces, I see that Null
values are handled differently. I've read many of the posts on querying
Null values, but I want to know what is the best practice for designing
a new system (SQL Server 2000) that could contain empty fields.
> For example, suppose I have a 'Phone' field that is often, but not
always, filled in. If the user blanks out a phone number, the .NET
DataAdapter .Update method will save the field as an empty string
instead of a NULL. This of course makes every query more complex having
to check for both nulls and empty strings.
> Is there any practical way to prevent, at the database level, the
empty strings from getting into the database? (Perhaps triggers or some
global setting?) Or should the string fields be empty strings and never
nulls...? I could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will have handled the
issue correctly.
> Any opionions?
> Thanks, Denise
>
> using VB.Net and ADO.Net code and if the user blanks out a field, ADO.Net by default it sometimes saves them as an empty string
|||There are strong debates regarding whether or not nulls should ever be
allowed in data columns. I agree with Mark, if you do not know the value
allow null ( even though it may require programming on the front end.)
Others ( Kalen Delaney for instance) make strong arguments for never
allowing nulls in the database.
This is an area where reasonable people differ in their opinions, so do
whatever works for you, with a clear conscience..
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
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O07qtuHSEHA.2000@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Denise,
> I'll give you my opinion, for what it's worth.
> A NULL means an unknown state. Therefore if you do not know the person's
> phone number then it is unknown, therefore NULL.
> An empty string is a positive entry into the database. It could be
> interpreted as, "I KNOW that this value is empty" and could therefore be
> interpreted as "this person doesn't have a phone".
> This is a subtle difference to NULL. NULL just means "I don't know", or
> unknown state. I doubt there is any performance difference between the
> two, however I haven't tested it.
> I'm not sure if I've actually answered your question because you need to
> ensure you are passing NULLs to the database and not empty strings in
> your data access layer code. If you want to prevent empty strings from
> entering the database, then you could use a constraint like this:
> --
> create table a (i int not null, c varchar(30) null check (c <> ''))
> insert a (i,c) values (1,null) -- succeeds
> insert a (i,c) values (2,'') -- fails
> select * from a
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Denise wrote:
> values are handled differently. I've read many of the posts on querying
> Null values, but I want to know what is the best practice for designing
> a new system (SQL Server 2000) that could contain empty fields.
> always, filled in. If the user blanks out a phone number, the .NET
> DataAdapter .Update method will save the field as an empty string
> instead of a NULL. This of course makes every query more complex having
> to check for both nulls and empty strings.
> empty strings from getting into the database? (Perhaps triggers or some
> global setting?) Or should the string fields be empty strings and never
> nulls...? I could re-write the data adapter, but I don't know if I can
> trust that every program that touches the database will have handled the
> issue correctly.
ADO.Net by default it sometimes saves them as an empty string
|||you've definately opened a can of worms. (Religious topic)
Many argue that nulls suggest a normalization problem.
Nulls will cause performance issues
Nulls will force you to add handling into your sprocs etc.
it's your call of course whether or not you wish to use them.
I lean towards not using them except in rare situations, but that's just me.
Cheers,
Greg Jackson
PDX, Oregon
|||Thanks for all your insight.
Best practice for backups
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backu
p
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:
> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backu
p
> give me everything I need to restore a database? How would I get it to bac
kup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse
3
> months ago I will be able to. What qould be the best way to do this?|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though
.
"Tom Mongold" wrote:
[vbcol=seagreen]
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are th
en
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to kee
p
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
>|||Either use the maintenance wizard for this, or you have to do some coding to
create a new backup
files each time you do the backup. Search the archives and you will find suc
h sample code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backu
p
> give me everything I need to restore a database? How would I get it to bac
kup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse
3
> months ago I will be able to. What qould be the best way to do this?
Best practice for backups
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backup
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:
> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though.
"Tom Mongold" wrote:
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are then
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to keep
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
> > I am wanting to know some ways to backup my databases. I have 3 I need to
> > backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> > growing. I want to have a backup every night. Will doing just a full backup
> > give me everything I need to restore a database? How would I get it to backup
> > every day but NOT overwrite the previous file, I want to create a new file
> > every night so if for some reason I need to get information off a databse 3
> > months ago I will be able to. What qould be the best way to do this?|||Either use the maintenance wizard for this, or you have to do some coding to create a new backup
files each time you do the backup. Search the archives and you will find such sample code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?
Best practice for backups
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backup
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?
what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:
> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?
|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though.
"Tom Mongold" wrote:
[vbcol=seagreen]
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are then
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to keep
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
|||Either use the maintenance wizard for this, or you have to do some coding to create a new backup
files each time you do the backup. Search the archives and you will find such sample code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?
Thursday, March 8, 2012
best place to start
I am fairly versed with SQL 2005 as I have been using it since it came out. Now I have need to learn about Cubes.
Does anybody have any suggestions of where I need to begin to learn about what cubes are, how to create them and use them?
Ultimately we will be incorporating them into Reporting Services.
Thanks for the information.
Using Books Online, look up the Topic: SQL Server 2005 Analysis Services Tutorial
There are some excellent sections on Cubes.
|||Thanks for the information.Monday, February 13, 2012
Beginner to SQL server 2005 Express Edition
Hi,
I am fairly new to Visual Studio 2005 (using C#) and am writing an application for the company I work in. I have got a really nice functioning form working and am now planning what to do about the massive database I need to create (I don't think it will ever get to the 4GB limitation of SQL Server 2005 Express Edition).
Can someone please help with some simple questions please?
1: I am decoding a specific file format and extracting the information for the main database. Fortunately, each record in the data file I am decoding has unique identity codes (so this will be great for the ID of each entry). Because the software has to do this by itself (the encoded file contains more than 1,120,000 records which will shrink to about 30,000 records, all different sizes once decoded). Can each record in a SQL server database contain a different number of elements? If so, what is this known as (I have read a little about jagged arrays - is there a similar thing in SQL server databases)?
2: As the program will decode the file and assemble the database, can I make my program create the database from scratch and manipulate the database rather than me having to define a database for my program to fill? If so, where can I find some info on doing this?
3: The database file will be stored on our office network and has to be accessible to more than one person at a time (in this case, a maximum of 6 users - well within the 25 users my book says is possible with SSE2005). Is it more complex to develop multi-user access to the database?
Just in case you wonder what books I have got, they are "Introducing Microsoft SQL Server 2005 for Developers" and "Getting Started with SQL Server 2005 Express Edition".
Thank you!
1. Yes and No. You can store the information for the missing columns as NULL, but your code will have to handle that, and the columns will need to be nullable. If the file is in a standard format (eg a spreadsheet, or a csv file), you can use DTS (or SSIS in 2005) to load the file. That would be easier than writing a custom application to load the file.
2. You can build a dynamic sql statement based on the columns and column types of the tables you want to build. However, it would be easier and safer to define the database beforehand. Building tables is easy enough, just make sure you define the relationships in the correct order, and then load the data in that order aswell. You may want to leave defining relationships until after the data is loaded, just make sure the data is concurrent.
3. I am not sure, I don't know too much about this. The database schema itself would not have to be changed, you would just have to create SQL server user accounts for each user, or use windows integrated security which is much easier to set up. Then ensure they can connect to the server which the SQL server instance is based upon.
As this is a new product, you may learn more useful and up-to-date information from the books online. Microsoft are still offering a free e-learning course on 2005:
https://www.microsoftelearning.com/sqlserver2005/default.aspx
I have used parts of it and found it useful. Books tend to be overly wordy, whereas the e-learning courses get to the point quicker.
|||Thank you,
Your reply will help me decide where to go next.
The file I am importing is not a straight-forward data file. It is a custom file format used by the British railway industry and some code is necessary to read in the data, join some elements to form a list, then the list will be placed into the database.
The database will have some fixed elements to start with, but will then be followed by my decoded list. The decoded list will contain a different number of entries. Some lists may contain around 10 entries. Some may contain as many as 200 entries.
Sean.
Sunday, February 12, 2012
Beginner help stored procedures, also .net
I'm not qualified enough to be called a beginner -- I could use some help.
I've written a few fairly simple queries and run them through the query
analyzer. I'd like to know 2 things:
How can I turn them into stored procedures?
How would I then run those stored procedures through vb.net?
Any help would be appreciated.
Thanks,
ArtArt
CREATE PROCEDURE myFirstSp
@.par INT
AS
SELECT OrderDate FROM Orders WHERE OrderId=@.par
--usage
EXEC myFirstSp @.par=10248
You will find more info about stored procedures in the BOL.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art|||You can find samples and tutorials on MSDN.com and ASP.net
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art