We're trying to put a view of data maintained in desktop Access databases online and into SQL Server.
The desktop Access system uses separate databases instead of tables within one database, It's a strange design, but it can't be changed.
We have been importing all of the separate databases into a single, new Access database, then upsizing the new databse to SQL Server, then uploading it.
This is not going to work long term, because we are stuck with a 250 mB Access database to upsize and upload, when we never need to update more than 2 or 3 of the tables and upload more than 2 mB.
We'd like to be able to upload only the tables -- preferably the Access *.mdb's -- that have changed, and then replace the SQL Server tables with the new information. And we'd like to automate it as much as possible, without upsizing Wizardy.
I don't know where to even begin looking for information about how this might be done.
Any suggestions would be deeply appreciated.
- Tinker
This sounds possibly like a job for DTS. You could set up a DTSpackage on the SQL Server that will loop through the Access databasesand perform the updates as needed.
This site is a good resources for DTS work:http://www.sqldts.com.
|||Hi Terri, and thank you.
When I first ran across information about DTS, I skipped right past it. I'm curently out in the MS Tech Net learning more and it appears DTS may be much closer to what we want to do than I first thought.
Neither the FAQs athttp://www.sqldts.com/ nor the information I have trolled-through so far on the Tech Net discuss automating DTS; is that possible?
While we don't want to ride herd on these data updates any more than is absolutely necessary, it would be super great if I don't have to try to program my way through deleting all the records in an SQL table, then refilling the table with the new records...
Appreciate your suggestion,
- Tinker|||Sure, it's possible to automate DTS. You can use SQL Agent toschedule a DTS package to run on a schedule of your choosing.|||Thank you. I finally found where I get to do all this.
Sometimes I feel dumber than usual... 8-)
- Tinker
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Tuesday, March 27, 2012
Automating a time-sensitive query
My company uses MS Retail Management System, which is SQL-based. Without
going into a lot of detail about RMS, here's what I need to do: schedule a
query that will insert a new row into an existing table. Part of the data
that needs to be inserted is date/time. For example, here's the query I
need to run:
INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title, FromDate)
VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
What I don't know how to do:
Generate the date fields "Today 6:30PM" and "NOW"
Schedule the query.
I've got SQL 2000 (as part of SBS Premium), and I am certain that I have the
capability to do this. I think I have figured out the scheduling part, but
I'm hesitant to try it out on a live database. In fact, I think I'll try a
simpler query on a sample database before I ever go live with it.
Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
than an SQL expert.
Thanks for any advice you might offer,
Tom
--
Stop Fishing For e-MailThe function getdate() returns the current date/time, and jobs are the
standard method of scheduling a query, stored procedure, or package to
execute at a specific time or at intervals.
"Terrible Tom" <tomg@.gofish.robysfurniture.com> wrote in message
news:eHO$g55QGHA.1772@.TK2MSFTNGP14.phx.gbl...
> My company uses MS Retail Management System, which is SQL-based. Without
> going into a lot of detail about RMS, here's what I need to do: schedule
> a query that will insert a new row into an existing table. Part of the
> data that needs to be inserted is date/time. For example, here's the
> query I need to run:
> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
> I've got SQL 2000 (as part of SBS Premium), and I am certain that I have
> the capability to do this. I think I have figured out the scheduling
> part, but I'm hesitant to try it out on a live database. In fact, I think
> I'll try a simpler query on a sample database before I ever go live with
> it.
> Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
> than an SQL expert.
> Thanks for any advice you might offer,
> Tom
> --
> Stop Fishing For e-Mail
>|||> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
DECLARE @.eff SMALLDATETIME, @.from SMALLDATETIME;
SET @.from = CURRENT_TIMESTAMP;
SET @.eff = DATEADD(MINUTE, 30, DATEADD(HOUR, 18, DATEADD(DAY, 0,
DATEDIFF(DAY, 0, @.from))));
INSERT WorkSheet
(
Style,
EffectiveDate,
Status,
Notes,
Title,
FromDate
)
SELECT
250,
@.eff,
2,
'Auto 250',
'Automated Daily Item Update',
@.from;
> I'm hesitant to try it out on a live database. In fact, I think I'll try
> a simpler query on a sample database before I ever go live with it.
Never a bad idea. We test our code on three non-essential environments
before live clients ever hear about it.
A|||Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
proprietary getdate().
.|||Why is it called a time "stamp" ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141927581.261444.223900@.v46g2000cwv.googlegroups.com...
> Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
> proprietary getdate().
> .
>
going into a lot of detail about RMS, here's what I need to do: schedule a
query that will insert a new row into an existing table. Part of the data
that needs to be inserted is date/time. For example, here's the query I
need to run:
INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title, FromDate)
VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
What I don't know how to do:
Generate the date fields "Today 6:30PM" and "NOW"
Schedule the query.
I've got SQL 2000 (as part of SBS Premium), and I am certain that I have the
capability to do this. I think I have figured out the scheduling part, but
I'm hesitant to try it out on a live database. In fact, I think I'll try a
simpler query on a sample database before I ever go live with it.
Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
than an SQL expert.
Thanks for any advice you might offer,
Tom
--
Stop Fishing For e-MailThe function getdate() returns the current date/time, and jobs are the
standard method of scheduling a query, stored procedure, or package to
execute at a specific time or at intervals.
"Terrible Tom" <tomg@.gofish.robysfurniture.com> wrote in message
news:eHO$g55QGHA.1772@.TK2MSFTNGP14.phx.gbl...
> My company uses MS Retail Management System, which is SQL-based. Without
> going into a lot of detail about RMS, here's what I need to do: schedule
> a query that will insert a new row into an existing table. Part of the
> data that needs to be inserted is date/time. For example, here's the
> query I need to run:
> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
> I've got SQL 2000 (as part of SBS Premium), and I am certain that I have
> the capability to do this. I think I have figured out the scheduling
> part, but I'm hesitant to try it out on a live database. In fact, I think
> I'll try a simpler query on a sample database before I ever go live with
> it.
> Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
> than an SQL expert.
> Thanks for any advice you might offer,
> Tom
> --
> Stop Fishing For e-Mail
>|||> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
DECLARE @.eff SMALLDATETIME, @.from SMALLDATETIME;
SET @.from = CURRENT_TIMESTAMP;
SET @.eff = DATEADD(MINUTE, 30, DATEADD(HOUR, 18, DATEADD(DAY, 0,
DATEDIFF(DAY, 0, @.from))));
INSERT WorkSheet
(
Style,
EffectiveDate,
Status,
Notes,
Title,
FromDate
)
SELECT
250,
@.eff,
2,
'Auto 250',
'Automated Daily Item Update',
@.from;
> I'm hesitant to try it out on a live database. In fact, I think I'll try
> a simpler query on a sample database before I ever go live with it.
Never a bad idea. We test our code on three non-essential environments
before live clients ever hear about it.
A|||Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
proprietary getdate().
.|||Why is it called a time "stamp" ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141927581.261444.223900@.v46g2000cwv.googlegroups.com...
> Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
> proprietary getdate().
> .
>
Sunday, March 25, 2012
Automatically re-indexing
Is there a way to tell the system to re-index a table after a change - any
change is made to the table?
Is there a way to tell the system to re-index using a stored procedure?
Thanks,
Tom.
Tom,
Yes. That is assuming you are using SQL Server 2000, you can use "Change
Tracking" and "Update Index in Background" and get near real-time updates of
the FT Catalogs when a table's FT-enabled column changes. SQL Server 2000
BOL titles "Full-Text Search Recommendations" and "Maintaining Full-Text
Indexes" have more info on these options. If you're using SQL Server 7.0,
then you must schedule and run an Incremental Population (substitute
start_incremental for start_full in the below sql code).
As for a stored proc that can do this, try:
use pubs
go
if object_id('sp_WrapFT_SProcs','P') IS NOT NULL
drop procedure sp_WrapFT_SProcs
GO
CREATE PROCEDURE sp_WrapFT_SProcs @.tablename varchar(100), @.activity
varchar(100)
AS
DECLARE @.SQLCMD varchar(255)
SELECT @.SQLCMD = "sp_fulltext_catalog @.tablename, @.activity"
EXEC (@.SQLCMD)
GO
-- Execute above:
EXEC sp_WrapFT_SProcs 'PubInfo', 'start_full'
GO
Regards,
John
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:10h0diu7prv0h39@.corp.supernews.com...
> Is there a way to tell the system to re-index a table after a change - any
> change is made to the table?
> Is there a way to tell the system to re-index using a stored procedure?
> Thanks,
> Tom.
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:#F671tceEHA.724@.TK2MSFTNGP10.phx.gbl...
> Tom,
> Yes. That is assuming you are using SQL Server 2000, you can use "Change
> Tracking" and "Update Index in Background" and get near real-time updates
of
> the FT Catalogs when a table's FT-enabled column changes. SQL Server 2000
> BOL titles "Full-Text Search Recommendations" and "Maintaining Full-Text
> Indexes" have more info on these options. If you're using SQL Server 7.0,
> then you must schedule and run an Incremental Population (substitute
> start_incremental for start_full in the below sql code).
Yes, I am using Sql Server 2000.
What I am doing is setting up a table with all my QA Docs (about 150 of
them).
I have an ASP.NET page that just goes through my Document folder and creates
the records as well as copies the documents into my image field.
What I am doing is trying to set up an easy way to update the records when
some of the documents have changed. Instead of trying to determine which
have been changed, I plan to just delete all the records and create all the
records again (only takes about 3 minutes). I just want to make sure that
index is also updated.
Thanks,
Tom.[vbcol=seagreen]
> As for a stored proc that can do this, try:
> use pubs
> go
> if object_id('sp_WrapFT_SProcs','P') IS NOT NULL
> drop procedure sp_WrapFT_SProcs
> GO
> CREATE PROCEDURE sp_WrapFT_SProcs @.tablename varchar(100), @.activity
> varchar(100)
> AS
> DECLARE @.SQLCMD varchar(255)
> SELECT @.SQLCMD = "sp_fulltext_catalog @.tablename, @.activity"
> EXEC (@.SQLCMD)
> GO
> -- Execute above:
> EXEC sp_WrapFT_SProcs 'PubInfo', 'start_full'
> GO
> Regards,
> John
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:10h0diu7prv0h39@.corp.supernews.com...
any
>
|||Tom,
There should be an easy way to determine when & what document has changed,
at a mim. save the doc length &/or mod date/time in your SQL table and then
only upload the changed documents. With CT & UIiB enabled, each time you
upload (or really update/insert) the document into your FT-enable image
column, CT & UIiB will automatically update the FT Catalog with the
new/modified documents.
Even if it takes only 3 minutes, deleting all the records and then
re-creating all the records again, seems a bit time-consuming (not very
scalable as you get more & more documents) and wasteful as all documents
would then need to be re-FT Indexed and that can take more time as you get
more & more documents, IMHO.
Regards,
John
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:10h0rrtl23mhi76@.corp.supernews.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:#F671tceEHA.724@.TK2MSFTNGP10.phx.gbl...
updates[vbcol=seagreen]
> of
2000[vbcol=seagreen]
7.0,
> Yes, I am using Sql Server 2000.
> What I am doing is setting up a table with all my QA Docs (about 150 of
> them).
> I have an ASP.NET page that just goes through my Document folder and
creates
> the records as well as copies the documents into my image field.
> What I am doing is trying to set up an easy way to update the records when
> some of the documents have changed. Instead of trying to determine which
> have been changed, I plan to just delete all the records and create all
the[vbcol=seagreen]
> records again (only takes about 3 minutes). I just want to make sure that
> index is also updated.
> Thanks,
> Tom.
> any
procedure?
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:OnizJ8eeEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Tom,
> There should be an easy way to determine when & what document has changed,
> at a mim. save the doc length &/or mod date/time in your SQL table and
then
> only upload the changed documents. With CT & UIiB enabled, each time you
> upload (or really update/insert) the document into your FT-enable image
> column, CT & UIiB will automatically update the FT Catalog with the
> new/modified documents.
> Even if it takes only 3 minutes, deleting all the records and then
> re-creating all the records again, seems a bit time-consuming (not very
> scalable as you get more & more documents) and wasteful as all documents
> would then need to be re-FT Indexed and that can take more time as you get
> more & more documents, IMHO.
You're right.
And if I set CT & UIiB, I shouldn't have to check the length or time,
anyway.
Thanks,
Tom[vbcol=seagreen]
> Regards,
> John
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:10h0rrtl23mhi76@.corp.supernews.com...
"Change[vbcol=seagreen]
> updates
> 2000
Full-Text[vbcol=seagreen]
> 7.0,
> creates
when[vbcol=seagreen]
which[vbcol=seagreen]
> the
that[vbcol=seagreen]
change -
> procedure?
>
change is made to the table?
Is there a way to tell the system to re-index using a stored procedure?
Thanks,
Tom.
Tom,
Yes. That is assuming you are using SQL Server 2000, you can use "Change
Tracking" and "Update Index in Background" and get near real-time updates of
the FT Catalogs when a table's FT-enabled column changes. SQL Server 2000
BOL titles "Full-Text Search Recommendations" and "Maintaining Full-Text
Indexes" have more info on these options. If you're using SQL Server 7.0,
then you must schedule and run an Incremental Population (substitute
start_incremental for start_full in the below sql code).
As for a stored proc that can do this, try:
use pubs
go
if object_id('sp_WrapFT_SProcs','P') IS NOT NULL
drop procedure sp_WrapFT_SProcs
GO
CREATE PROCEDURE sp_WrapFT_SProcs @.tablename varchar(100), @.activity
varchar(100)
AS
DECLARE @.SQLCMD varchar(255)
SELECT @.SQLCMD = "sp_fulltext_catalog @.tablename, @.activity"
EXEC (@.SQLCMD)
GO
-- Execute above:
EXEC sp_WrapFT_SProcs 'PubInfo', 'start_full'
GO
Regards,
John
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:10h0diu7prv0h39@.corp.supernews.com...
> Is there a way to tell the system to re-index a table after a change - any
> change is made to the table?
> Is there a way to tell the system to re-index using a stored procedure?
> Thanks,
> Tom.
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:#F671tceEHA.724@.TK2MSFTNGP10.phx.gbl...
> Tom,
> Yes. That is assuming you are using SQL Server 2000, you can use "Change
> Tracking" and "Update Index in Background" and get near real-time updates
of
> the FT Catalogs when a table's FT-enabled column changes. SQL Server 2000
> BOL titles "Full-Text Search Recommendations" and "Maintaining Full-Text
> Indexes" have more info on these options. If you're using SQL Server 7.0,
> then you must schedule and run an Incremental Population (substitute
> start_incremental for start_full in the below sql code).
Yes, I am using Sql Server 2000.
What I am doing is setting up a table with all my QA Docs (about 150 of
them).
I have an ASP.NET page that just goes through my Document folder and creates
the records as well as copies the documents into my image field.
What I am doing is trying to set up an easy way to update the records when
some of the documents have changed. Instead of trying to determine which
have been changed, I plan to just delete all the records and create all the
records again (only takes about 3 minutes). I just want to make sure that
index is also updated.
Thanks,
Tom.[vbcol=seagreen]
> As for a stored proc that can do this, try:
> use pubs
> go
> if object_id('sp_WrapFT_SProcs','P') IS NOT NULL
> drop procedure sp_WrapFT_SProcs
> GO
> CREATE PROCEDURE sp_WrapFT_SProcs @.tablename varchar(100), @.activity
> varchar(100)
> AS
> DECLARE @.SQLCMD varchar(255)
> SELECT @.SQLCMD = "sp_fulltext_catalog @.tablename, @.activity"
> EXEC (@.SQLCMD)
> GO
> -- Execute above:
> EXEC sp_WrapFT_SProcs 'PubInfo', 'start_full'
> GO
> Regards,
> John
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:10h0diu7prv0h39@.corp.supernews.com...
any
>
|||Tom,
There should be an easy way to determine when & what document has changed,
at a mim. save the doc length &/or mod date/time in your SQL table and then
only upload the changed documents. With CT & UIiB enabled, each time you
upload (or really update/insert) the document into your FT-enable image
column, CT & UIiB will automatically update the FT Catalog with the
new/modified documents.
Even if it takes only 3 minutes, deleting all the records and then
re-creating all the records again, seems a bit time-consuming (not very
scalable as you get more & more documents) and wasteful as all documents
would then need to be re-FT Indexed and that can take more time as you get
more & more documents, IMHO.
Regards,
John
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:10h0rrtl23mhi76@.corp.supernews.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:#F671tceEHA.724@.TK2MSFTNGP10.phx.gbl...
updates[vbcol=seagreen]
> of
2000[vbcol=seagreen]
7.0,
> Yes, I am using Sql Server 2000.
> What I am doing is setting up a table with all my QA Docs (about 150 of
> them).
> I have an ASP.NET page that just goes through my Document folder and
creates
> the records as well as copies the documents into my image field.
> What I am doing is trying to set up an easy way to update the records when
> some of the documents have changed. Instead of trying to determine which
> have been changed, I plan to just delete all the records and create all
the[vbcol=seagreen]
> records again (only takes about 3 minutes). I just want to make sure that
> index is also updated.
> Thanks,
> Tom.
> any
procedure?
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:OnizJ8eeEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Tom,
> There should be an easy way to determine when & what document has changed,
> at a mim. save the doc length &/or mod date/time in your SQL table and
then
> only upload the changed documents. With CT & UIiB enabled, each time you
> upload (or really update/insert) the document into your FT-enable image
> column, CT & UIiB will automatically update the FT Catalog with the
> new/modified documents.
> Even if it takes only 3 minutes, deleting all the records and then
> re-creating all the records again, seems a bit time-consuming (not very
> scalable as you get more & more documents) and wasteful as all documents
> would then need to be re-FT Indexed and that can take more time as you get
> more & more documents, IMHO.
You're right.
And if I set CT & UIiB, I shouldn't have to check the length or time,
anyway.
Thanks,
Tom[vbcol=seagreen]
> Regards,
> John
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:10h0rrtl23mhi76@.corp.supernews.com...
"Change[vbcol=seagreen]
> updates
> 2000
Full-Text[vbcol=seagreen]
> 7.0,
> creates
when[vbcol=seagreen]
which[vbcol=seagreen]
> the
that[vbcol=seagreen]
change -
> procedure?
>
Sunday, March 11, 2012
Automatic Defragmentation?
Hi!
I have sql server system and in the diary backup i have
seen that the database size has been reduced a lot, from
one day to another.
I have any deframentation actived, Is the defragmentation
activated by defect in sql server'
Thanks.
Best RegardsThere is no automatic method for controlling fragmentation. See the
whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
for more details.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"fran" <gimmacks@.telepolis.com> wrote in message
news:001901c37938$47abd980$a101280a@.phx.gbl...
> Hi!
>
> I have sql server system and in the diary backup i have
> seen that the database size has been reduced a lot, from
> one day to another.
> I have any deframentation actived, Is the defragmentation
> activated by defect in sql server'
>
> Thanks.
> Best Regards|||Hi!
Thanks for your response.
If there is any automatic method for controlling
defragmentation, Do you have any idea why this has
happend'
Thanks
>--Original Message--
>There is no automatic method for controlling
fragmentation. See the
>whitepaper at
>http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
>for more details.
>--
>Paul Randal
>DBCC Technical Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"fran" <gimmacks@.telepolis.com> wrote in message
>news:001901c37938$47abd980$a101280a@.phx.gbl...
>> Hi!
>>
>> I have sql server system and in the diary backup i have
>> seen that the database size has been reduced a lot, from
>> one day to another.
>> I have any deframentation actived, Is the
defragmentation
>> activated by defect in sql server'
>>
>> Thanks.
>> Best Regards
>
>.
>|||No there isn't. Please read the whitepaper which should explain everything
you've asked.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"fran" <pnoia@.iespana.es> wrote in message
news:06ee01c37b6c$d3589c30$a101280a@.phx.gbl...
> Hi!
> Thanks for your response.
> If there is any automatic method for controlling
> defragmentation, Do you have any idea why this has
> happend'
>
> Thanks
>
> >--Original Message--
> >There is no automatic method for controlling
> fragmentation. See the
> >whitepaper at
> >http://www.microsoft.com/technet/treeview/default.asp?
> url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >for more details.
> >
> >--
> >Paul Randal
> >DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"fran" <gimmacks@.telepolis.com> wrote in message
> >news:001901c37938$47abd980$a101280a@.phx.gbl...
> >> Hi!
> >>
> >>
> >> I have sql server system and in the diary backup i have
> >> seen that the database size has been reduced a lot, from
> >> one day to another.
> >> I have any deframentation actived, Is the
> defragmentation
> >> activated by defect in sql server'
> >>
> >>
> >> Thanks.
> >>
> >> Best Regards
> >
> >
> >.
> >
I have sql server system and in the diary backup i have
seen that the database size has been reduced a lot, from
one day to another.
I have any deframentation actived, Is the defragmentation
activated by defect in sql server'
Thanks.
Best RegardsThere is no automatic method for controlling fragmentation. See the
whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
for more details.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"fran" <gimmacks@.telepolis.com> wrote in message
news:001901c37938$47abd980$a101280a@.phx.gbl...
> Hi!
>
> I have sql server system and in the diary backup i have
> seen that the database size has been reduced a lot, from
> one day to another.
> I have any deframentation actived, Is the defragmentation
> activated by defect in sql server'
>
> Thanks.
> Best Regards|||Hi!
Thanks for your response.
If there is any automatic method for controlling
defragmentation, Do you have any idea why this has
happend'
Thanks
>--Original Message--
>There is no automatic method for controlling
fragmentation. See the
>whitepaper at
>http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
>for more details.
>--
>Paul Randal
>DBCC Technical Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"fran" <gimmacks@.telepolis.com> wrote in message
>news:001901c37938$47abd980$a101280a@.phx.gbl...
>> Hi!
>>
>> I have sql server system and in the diary backup i have
>> seen that the database size has been reduced a lot, from
>> one day to another.
>> I have any deframentation actived, Is the
defragmentation
>> activated by defect in sql server'
>>
>> Thanks.
>> Best Regards
>
>.
>|||No there isn't. Please read the whitepaper which should explain everything
you've asked.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"fran" <pnoia@.iespana.es> wrote in message
news:06ee01c37b6c$d3589c30$a101280a@.phx.gbl...
> Hi!
> Thanks for your response.
> If there is any automatic method for controlling
> defragmentation, Do you have any idea why this has
> happend'
>
> Thanks
>
> >--Original Message--
> >There is no automatic method for controlling
> fragmentation. See the
> >whitepaper at
> >http://www.microsoft.com/technet/treeview/default.asp?
> url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >for more details.
> >
> >--
> >Paul Randal
> >DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"fran" <gimmacks@.telepolis.com> wrote in message
> >news:001901c37938$47abd980$a101280a@.phx.gbl...
> >> Hi!
> >>
> >>
> >> I have sql server system and in the diary backup i have
> >> seen that the database size has been reduced a lot, from
> >> one day to another.
> >> I have any deframentation actived, Is the
> defragmentation
> >> activated by defect in sql server'
> >>
> >>
> >> Thanks.
> >>
> >> Best Regards
> >
> >
> >.
> >
Automatic content expiry
Hi All,
I have a job coming up which involves me creating a vacancy system for our website.
I (think) I'll only need one table:
ID (PK)
JobTitle
JobLocation
Salary
Hours
JobDescription
ValidFrom (Date/Time)
ValidTo (Date/Time)
I'll display this data using a C#.Net repeater but I was wondering how I can automatically strip the page of the jobs that have gone past their expiry date? Is is straightforward to achieve?
Thanks in advance,
Brett
Add a where condition to your select statement to bring only the valid Jobs e.g.:
select * from Jobs where getDate() < ValidTo
Hope this was helpful
Eyad Salamin
|||Thanks Eyad,
I didn't think it would be that simple!
Brett
Saturday, February 25, 2012
Autoincrement
Hello
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,
> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,
> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>
Monday, February 13, 2012
Auto reminder emails?
I don't know much about broker service so I have question. We have a content management system the we developed locally, and what we have are catagories and subcatagries. When people choose a topic we send them an email about the topic they are interested in. We are now selling webinars and white papers and articles on demand. We would like to be able to send a reminder email to anyone who signed up for a webinar or special event. Is this possible with broker service?While you could use SSB to build your fan-out and scheduling for the mails, it will not be capable of actually delivering the mails to the users, since it can only send messages between two SQL Server instances. The Database mail functionality in SQL can achieve that though (see http://msdn2.microsoft.com/en-us/library/ms175887.aspx)|||So I can use service broker to send the email to the dbmail engine and than to the customers right?|||Yes, but db mail engine already does that (sp_senddbmail uses Service Broker to communicate with the mailing engine)
Auto Number mysql
I have just moved over to mysql as the back end and keeping access as
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
SimonUm, this isn't a MySQL newsgroup. It's a SQL Server newsgroup. They are
different products.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
<S.Dickson@.shos.co.uk> wrote in message
news:1162900496.357948.305300@.b28g2000cwb.googlegroups.com...
I have just moved over to mysql as the back end and keeping access as
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
Simon
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
SimonUm, this isn't a MySQL newsgroup. It's a SQL Server newsgroup. They are
different products.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
<S.Dickson@.shos.co.uk> wrote in message
news:1162900496.357948.305300@.b28g2000cwb.googlegroups.com...
I have just moved over to mysql as the back end and keeping access as
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
Simon
Auto Number mysql
I had an access database that i use as an ordering system. I have a
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.
I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.
When i go into the form and add a new customer it does not generate the
customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .
Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Any help would be great
SimonThis question is most likely better positioned at one of the mysql
newsgroups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<S.Dickson@.shos.co.uk> wrote in message
news:1162848855.370242.88140@.h54g2000cwb.googlegroups.com...
>I had an access database that i use as an ordering system. I have a
> form for entering customer details. When i add a new customer on the
> form the customer number is an auto number that appears when i type in
> the details.
>
> I have just moved over to mysql server with access as the front end. I
> have setup the sql tables with the customer number as autonumber.
> When i go into the form and add a new customer it does not generate the
> customer Number automaticaly on the form like it did before. once i
> have entered all the data and saved it i went into the customer table
> to see if the data was saved the auto number was in there .
> Is there any way for on the form for the autonumber to be displayed
> when i start entering the data. Like how it was when i used access as
> the back end before i moved to mysql as the back end
>
> Any help would be great
> Simon
>
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.
I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.
When i go into the form and add a new customer it does not generate the
customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .
Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Any help would be great
SimonThis question is most likely better positioned at one of the mysql
newsgroups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<S.Dickson@.shos.co.uk> wrote in message
news:1162848855.370242.88140@.h54g2000cwb.googlegroups.com...
>I had an access database that i use as an ordering system. I have a
> form for entering customer details. When i add a new customer on the
> form the customer number is an auto number that appears when i type in
> the details.
>
> I have just moved over to mysql server with access as the front end. I
> have setup the sql tables with the customer number as autonumber.
> When i go into the form and add a new customer it does not generate the
> customer Number automaticaly on the form like it did before. once i
> have entered all the data and saved it i went into the customer table
> to see if the data was saved the auto number was in there .
> Is there any way for on the form for the autonumber to be displayed
> when i start entering the data. Like how it was when i used access as
> the back end before i moved to mysql as the back end
>
> Any help would be great
> Simon
>
Auto Number mysql
I had an access database that i use as an ordering system. I have a
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.
I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.
When i go into the form and add a new customer it does not generate the
customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .
Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Any help would be great
SimonThis question is most likely better positioned at one of the mysql
newsgroups.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<S.Dickson@.shos.co.uk> wrote in message
news:1162848855.370242.88140@.h54g2000cwb.googlegroups.com...
>I had an access database that i use as an ordering system. I have a
> form for entering customer details. When i add a new customer on the
> form the customer number is an auto number that appears when i type in
> the details.
>
> I have just moved over to mysql server with access as the front end. I
> have setup the sql tables with the customer number as autonumber.
> When i go into the form and add a new customer it does not generate the
> customer Number automaticaly on the form like it did before. once i
> have entered all the data and saved it i went into the customer table
> to see if the data was saved the auto number was in there .
> Is there any way for on the form for the autonumber to be displayed
> when i start entering the data. Like how it was when i used access as
> the back end before i moved to mysql as the back end
>
> Any help would be great
> Simon
>
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.
I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.
When i go into the form and add a new customer it does not generate the
customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .
Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Any help would be great
SimonThis question is most likely better positioned at one of the mysql
newsgroups.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<S.Dickson@.shos.co.uk> wrote in message
news:1162848855.370242.88140@.h54g2000cwb.googlegroups.com...
>I had an access database that i use as an ordering system. I have a
> form for entering customer details. When i add a new customer on the
> form the customer number is an auto number that appears when i type in
> the details.
>
> I have just moved over to mysql server with access as the front end. I
> have setup the sql tables with the customer number as autonumber.
> When i go into the form and add a new customer it does not generate the
> customer Number automaticaly on the form like it did before. once i
> have entered all the data and saved it i went into the customer table
> to see if the data was saved the auto number was in there .
> Is there any way for on the form for the autonumber to be displayed
> when i start entering the data. Like how it was when i used access as
> the back end before i moved to mysql as the back end
>
> Any help would be great
> Simon
>
Auto Number mysql
I have just moved over to mysql as the back end and keeping access as
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
SimonUm, this isn't a mysql newsgroup. It's a SQL Server newsgroup. They are
different products.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
<S.Dickson@.shos.co.uk> wrote in message
news:1162900496.357948.305300@.b28g2000cwb.googlegroups.com...
I have just moved over to mysql as the back end and keeping access as
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
Simon
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
SimonUm, this isn't a mysql newsgroup. It's a SQL Server newsgroup. They are
different products.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
<S.Dickson@.shos.co.uk> wrote in message
news:1162900496.357948.305300@.b28g2000cwb.googlegroups.com...
I have just moved over to mysql as the back end and keeping access as
front end. My database is for a ordering system. I use autonumber as
the Order Number. as mysql does not display the order number
(autonumber) until the form has been saved.
Is there a way on the order form that i can have a button that will
save the data then reopen the form so the order number ( autonumber is
displayed)
I am currently only learning about access so some nice easy instruction
would be very helpful
Thanks
Simon
Friday, February 10, 2012
auto identity for each Type
Hello,
I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, ....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,...)
When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2...PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.
The question is :
What is the best solution to generate a counter for each type?(With code sample)
Thanks.do you really need to have the 'PV' and 'JV' before each value? if you could use ints, then you could use identity columns. That's the standard way of doing this.
You can always tack on a JV or PV in the front end if that's the way your boss wants it to look in a report or something.
from BOL:
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||if you were using mysql, this functionality (starting a new auto_increment within each type group) is built in
it's impossible to do this with an IDENTITY column
you will have to generate your own numbers, and i would recommend very strongly against it|||the counter in the question is the ReferenceCode in the Voucher table
Voucher: ID (primary), Date,TypeID, ReferenceCode.
so for each added voucher and according to the TypeID a the reference code will be generated. let say the last counter for the PV type is 230 so the referenceCode will be PV231. if the Type is JV and the last counter is 566 then the ReferenceCode will be JV567 and so on.
We don't have to forget that we are working in a multi user enviroment, and the Reference Code should be unique .|||put the JV or PV in another field and concatenate it in the front end. smart numbers are stupid and loved by the accounting types. this kind of things slow down joins and causes other kinds of pain. i have not seen smart numbers in a project for five years and that was a legacy foxpro app.
I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, ....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,...)
When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2...PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.
The question is :
What is the best solution to generate a counter for each type?(With code sample)
Thanks.do you really need to have the 'PV' and 'JV' before each value? if you could use ints, then you could use identity columns. That's the standard way of doing this.
You can always tack on a JV or PV in the front end if that's the way your boss wants it to look in a report or something.
from BOL:
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||if you were using mysql, this functionality (starting a new auto_increment within each type group) is built in
it's impossible to do this with an IDENTITY column
you will have to generate your own numbers, and i would recommend very strongly against it|||the counter in the question is the ReferenceCode in the Voucher table
Voucher: ID (primary), Date,TypeID, ReferenceCode.
so for each added voucher and according to the TypeID a the reference code will be generated. let say the last counter for the PV type is 230 so the referenceCode will be PV231. if the Type is JV and the last counter is 566 then the ReferenceCode will be JV567 and so on.
We don't have to forget that we are working in a multi user enviroment, and the Reference Code should be unique .|||put the JV or PV in another field and concatenate it in the front end. smart numbers are stupid and loved by the accounting types. this kind of things slow down joins and causes other kinds of pain. i have not seen smart numbers in a project for five years and that was a legacy foxpro app.
Subscribe to:
Posts (Atom)