sql reports: existing

Showing posts with label existing. Show all posts

Sunday, March 25, 2012

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping
Pu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>
|||When you create the temporary table you can also specify the collation used in your user database on the textfields.
create table #abc
(description varchar(100) COLLATE latin1_general_cs_as)
-- Pu Gong wrote: --
same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping
|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
In
> a
> nvarchar
> that
> setting?
>

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helpingPu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>|||When you create the temporary table you can also specify the collation used in your user database on the textfields
create table #ab
(description varchar(100) COLLATE latin1_general_cs_as
-- Pu Gong wrote: --
same as the subject
I have a database have a different collation setting from the temp db. In
SP, I use a temp table, and later, I use the temp table to compare nvarcha
data with an physical table in the database, then I meet an error like tha
Msg 446, Level 16 ..
Cannot resolve collation conflict for equal to operatio
I think I should change the collation setting of the database to th
collation of tempdb and master, but how can I change the collation setting
I have read the BOL, but can't find a way to change the collatio
Thanks a lot for helpin|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > same as the subject.
> > I have a database have a different collation setting from the temp db.
In
> a
> > SP, I use a temp table, and later, I use the temp table to compare
> nvarchar
> > data with an physical table in the database, then I meet an error like
> that
> >
> > Msg 446, Level 16 ...
> > Cannot resolve collation conflict for equal to operation
> >
> > I think I should change the collation setting of the database to the
> > collation of tempdb and master, but how can I change the collation
> setting?
> > I have read the BOL, but can't find a way to change the collation
> >
> > Thanks a lot for helping
> >
> >
>

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helpingPu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>|||When you create the temporary table you can also specify the collation used
in your user database on the textfields.
create table #abc
(description varchar(100) COLLATE latin1_general_cs_as)
-- Pu Gong wrote: --
same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
In[vbcol=seagreen]
> a
> nvarchar
> that
> setting?
>

Thursday, March 22, 2012

Can torn page detection fire from existing corruption?

Tuesday, March 20, 2012

Can torn page detection fire from existing corruption?

Does torn page detection only show new errors or errors during restore or
can it error due to a torn page that may have been in the database for a
while.
Thanks
PaulAFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
doesn't matter when the page was torn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Does torn page detection only show new errors or errors during restore or can it error due to a
> torn page that may have been in the database for a while.
> Thanks
> Paul
>|||Thanks Tibor.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||I believe it is only when the page is written assuming you have torn page
detection turned on at the time of the write.
--
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all 1)
are at read time. At least that is how I read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for "torn")
and BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
option is set, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I believe it is only when the page is written assuming you have torn page detection turned on at
>the time of the write.
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to a
>> torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>|||The check is only performed when the dboption is set, and only for pages
that have been written out since the option was set.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Yes, the bits are flipped at write time (assuming db option is on). But
> detection (all 0 or all 1) are at read time. At least that is how I read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> (search for "torn") and BOL
> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
> I'm not 100% positive whether checking of inconsistent bits are always
> performed or only when db option is set, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page
>>detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore
>> or can it error due to a torn page that may have been in the database
>> for a while.
>> Thanks
>> Paul
>>
>>
>|||Ahh, thanks. I assume there must be some flag in the page header saying something like "torn pages
flagged/flipped" (i.e. the page was written while detection was on) by which the code can determine
whether to check for tp or not?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> The check is only performed when the dboption is set, and only for pages that have been written
> out since the option was set.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
>> option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on at
>>the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to
>> a torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>>
>>
>|||So torn page detection wil activate only for fresh corruption.
That helps ie our problems are current.
We are in one of those situations where the db is corrupting but our disks
and controllers say all is well. We have turned off caching (batt backed)
etc.
No luck. We have installed sp4 and -T818. No errors from that.
We have run the new sqliostress. No lost writes, stale reads etc but still
the corruptions continue.
System, dell pe 8450 raid 10, had been stable for over 2 years.
We've built another box today and will move tonight.
Wish us luck!
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Ahh, thanks. I assume there must be some flag in the page header saying
> something like "torn pages flagged/flipped" (i.e. the page was written
> while detection was on) by which the code can determine whether to check
> for tp or not?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages
>> that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But
>> detection (all 0 or all 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during
>> restore or can it error due to a torn page that may have been in the
>> database for a while.
>> Thanks
>> Paul
>>
>>
>>
>>
>|||> So torn page detection wil activate only for fresh corruption.
That is not how I read Paul's statement. Assuming you have had torn page detection on for the
lifetime of the database. Then, as I understand it, a torn page could have happened a year ago.
Assuming you haven't read that page since it happened until now, you wouldn't have discovered it
until now.
Above scenario (again, as I understand how it works) is not very likely, though.
First, torn pages should be detected by DBCC CHECKDB, which I assume you do regularly.
Also, a torn page is most likely to occur if the system is shut down unexpectedly, and during the
following startup and the recovery phase, the pages which are torn would be very likely to be read
as they are likely to be involved in the recovery work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
> So torn page detection wil activate only for fresh corruption.
> That helps ie our problems are current.
> We are in one of those situations where the db is corrupting but our disks and controllers say all
> is well. We have turned off caching (batt backed) etc.
> No luck. We have installed sp4 and -T818. No errors from that.
> We have run the new sqliostress. No lost writes, stale reads etc but still the corruptions
> continue.
> System, dell pe 8450 raid 10, had been stable for over 2 years.
> We've built another box today and will move tonight.
> Wish us luck!
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying something like "torn
>> pages flagged/flipped" (i.e. the page was written while detection was on) by which the code can
>> determine whether to check for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages that have been written
>> out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when
>> db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on
>>at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during restore or can it error due
>>> to a torn page that may have been in the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>
>>
>>
>|||I think I may have not been clear. We did not have torn page on but have
been doing a full checkdb/checkalloc using sqlmaint, once a week.
We did not have torn page on as our dell perc3/dc (rebadged megaraid 1600)
have battery backup and we have a hefty ups.
To try to resolve is we had a hardware problem we turned off write bac
cache. No luck we then installed sql sp4 and added -t818. This did not show
any errors.We then enabled torn page. We got a detection after a few hours.
I'm at a loss as to why the dell raid event log shows no errors. I've done a
raid consistency test and it came up clean. chkdsk is OK.
The new sqliostress looks like it an excellent simulation. I ran it with the
extra i/o checking flags. Again clean.
Well we should know by tomorrow night if it was hardware. If it is, then we
may have a very expensive door stop. We'll nuke the box and rebuild it but
I'd feel scared to go back to a box that shows no errors but corrupts data.
I guess we'll run all dells diags for an age, memory etc. This was my dream
machine. I wanted to spec out a great server. 24 small disks, raid 10, 6
channels, 128Mb cache per controller etc.
Perhaps the secret is keep it simple, two big mirrors and just throw a ton
of memory in.
What a fortnight.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FbYj6JjFHA.1044@.tk2msftngp13.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
> That is not how I read Paul's statement. Assuming you have had torn page
> detection on for the lifetime of the database. Then, as I understand it, a
> torn page could have happened a year ago. Assuming you haven't read that
> page since it happened until now, you wouldn't have discovered it until
> now.
> Above scenario (again, as I understand how it works) is not very likely,
> though.
> First, torn pages should be detected by DBCC CHECKDB, which I assume you
> do regularly.
> Also, a torn page is most likely to occur if the system is shut down
> unexpectedly, and during the following startup and the recovery phase, the
> pages which are torn would be very likely to be read as they are likely to
> be involved in the recovery work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
>> That helps ie our problems are current.
>> We are in one of those situations where the db is corrupting but our
>> disks and controllers say all is well. We have turned off caching (batt
>> backed) etc.
>> No luck. We have installed sp4 and -T818. No errors from that.
>> We have run the new sqliostress. No lost writes, stale reads etc but
>> still the corruptions continue.
>> System, dell pe 8450 raid 10, had been stable for over 2 years.
>> We've built another box today and will move tonight.
>> Wish us luck!
>> Paul
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying
>> something like "torn pages flagged/flipped" (i.e. the page was written
>> while detection was on) by which the code can determine whether to check
>> for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for
>> pages that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on).
>> But detection (all 0 or all 1) are at read time. At least that is how
>> I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>>> AFAIK, torn page detection is done every tome a page is accessed
>>> from disk. This mean that it doesn't matter when the page was torn.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during
>>> restore or can it error due to a torn page that may have been in
>>> the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>>
>>
>>
>>
>>
>sql

Monday, March 19, 2012

can this be done in SQL2000

Hi I need to create a stored procedure that can do the following but now sure
if it can be done with sql2000.
1. read in existing data from a table.
2. create the current Julian date.
3. make a comparison and add a sequence number onto this number if it is a
specific Julian date.
I think all of this could probably be done with 2005 since it allows using
C#,vb for TSQL.
Paul G
Software engineer.On Wed, 30 Aug 2006 08:18:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I need to create a stored procedure that can do the following but now sure
>if it can be done with sql2000.
>1. read in existing data from a table.
Stored procedures are very good at retrieving data from database
tables.
>2. create the current Julian date.
SQL Server has sufficient tools for date manipulation that calculating
Julian date from the current date (getdate()) should be no problem. It
does, however, require knowing which definition of Julian date is
intended.
>3. make a comparison and add a sequence number onto this number if it is a
>specific Julian date.
Comparison of what to what? By "this number" do you mean the Julian
data calculated in item 2? What specific Julian date? One passed as
a parameter to the stored procedure? One retrieved from the table in
item 1?
>I think all of this could probably be done with 2005 since it allows using
>C#,vb for TSQL.
I am sure it can be done in 2005. It is unclear from the information
give that it will require C# or VB, but if it turns out to be
complicated they are available. Perhaps if you provided a bit more
detail someone will be able to suggest an appropriate approach.
>Paul G
>Software engineer.
Roy Harvey
Beacon Falls, CT|||Hi thanks for the response. Here are more details on what I am trying to do.
There is a table (table1) that has hundreds of records that look like
JTB-ABC-MDTC-06200-0001
JCV-BCD-ABAM-06201-0001
JTB-ABC-MDAC-06200-0002
I need the stored procedure to take the value
JCV-BCD-RBSV
and build the rest of it based on the following conditions and then save it
to a table.
Get the current julian date, today would be 06242, two hundred forty two day
of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
and only the last section still to be built. This is done by doing the
following.
1. Compare the jul date for the day the stored procedure will run (06242)
and find all records in table 1 with the same date (call this subset a).
2. Next out of subset a find all that match the first 6 letters (create
subset b).
3. Next out of subset b find the greatest value of the last 4 numbers (say
it was 0002).
4. Finally increment this value by 1 and use it to finish the newly created
value, so we would have JVC-BCD-RBSV-06242-0003.
This is easy to do in vb or C#.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> On Wed, 30 Aug 2006 08:18:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I need to create a stored procedure that can do the following but now sure
> >if it can be done with sql2000.
> >
> >1. read in existing data from a table.
> Stored procedures are very good at retrieving data from database
> tables.
> >2. create the current Julian date.
> SQL Server has sufficient tools for date manipulation that calculating
> Julian date from the current date (getdate()) should be no problem. It
> does, however, require knowing which definition of Julian date is
> intended.
> >3. make a comparison and add a sequence number onto this number if it is a
> >specific Julian date.
> Comparison of what to what? By "this number" do you mean the Julian
> data calculated in item 2? What specific Julian date? One passed as
> a parameter to the stored procedure? One retrieved from the table in
> item 1?
> >I think all of this could probably be done with 2005 since it allows using
> >C#,vb for TSQL.
> I am sure it can be done in 2005. It is unclear from the information
> give that it will require C# or VB, but if it turns out to be
> complicated they are available. Perhaps if you provided a bit more
> detail someone will be able to suggest an appropriate approach.
> >Paul G
> >Software engineer.
> Roy Harvey
> Beacon Falls, CT
>|||Of course all the parts of that complex column should be individual
table columns, but I will not belabor that point.
There is nothing about that which requires going to C# or VB. This
should give you some ideas. Note that I used a view, but each of the
pieces could have been substringed out when references. However since
all the real work is one in one SQL command, all those references
would make it a good bit more opaque. Another advantage to the view
is that it is possible to make it an indexed view, which could make a
major difference in performance.
CREATE TABLE Table1
(StrungOut char(23) NOT NULL)
INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
GO
CREATE VIEW Table1_V
AS
SELECT StrungOut,
First6 = SUBSTRING(StrungOut,1,6),
JDate = SUBSTRING(StrungOut,14,5),
JYear = SUBSTRING(StrungOut,14,2),
JDay = SUBSTRING(StrungOut,16,3),
Last4 = SUBSTRING(Strungout,20,4)
FROM Table1
GO
CREATE TABLE Table2
(StrungOut char(23) NOT NULL)
GO
CREATE PROC Demonstration
@.front char(12)
AS
INSERT Table2
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
FROM Table1_V
WHERE JDate = right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
GO
EXEC Demonstration 'JCV-BCD-RBSV'
SELECT *
FROM Table2
StrungOut
--
JCV-BCD-RBSV-06242-0006
Also, in the spec you mentioned matching on the first six characters,
but I wondered if it might have actually been the first seven.
One final point worth making. It would be quite practical to modify
this so that rather than taking in a string as a parameter, it
produced a new row such as this for each Front6 in Table1 that matches
the current date.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 09:09:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi thanks for the response. Here are more details on what I am trying to do.
>There is a table (table1) that has hundreds of records that look like
>JTB-ABC-MDTC-06200-0001
>JCV-BCD-ABAM-06201-0001
>JTB-ABC-MDAC-06200-0002
>I need the stored procedure to take the value
>JCV-BCD-RBSV
>and build the rest of it based on the following conditions and then save it
>to a table.
>Get the current julian date, today would be 06242, two hundred forty two day
>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>and only the last section still to be built. This is done by doing the
>following.
>1. Compare the jul date for the day the stored procedure will run (06242)
>and find all records in table 1 with the same date (call this subset a).
>2. Next out of subset a find all that match the first 6 letters (create
>subset b).
>3. Next out of subset b find the greatest value of the last 4 numbers (say
>it was 0002).
>4. Finally increment this value by 1 and use it to finish the newly created
>value, so we would have JVC-BCD-RBSV-06242-0003.
>This is easy to do in vb or C#.|||An alternate coding of the WHERE clause:
WHERE JYear = right(convert(char(4),datepart(year,getdate())),2)
AND JDay = right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
Roy
On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
>Of course all the parts of that complex column should be individual
>table columns, but I will not belabor that point.
>There is nothing about that which requires going to C# or VB. This
>should give you some ideas. Note that I used a view, but each of the
>pieces could have been substringed out when references. However since
>all the real work is one in one SQL command, all those references
>would make it a good bit more opaque. Another advantage to the view
>is that it is possible to make it an indexed view, which could make a
>major difference in performance.
>CREATE TABLE Table1
>(StrungOut char(23) NOT NULL)
>INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
>INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
>INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
>INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
>GO
>CREATE VIEW Table1_V
>AS
>SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
>GO
>CREATE TABLE Table2
>(StrungOut char(23) NOT NULL)
>GO
>CREATE PROC Demonstration
>@.front char(12)
>AS
>INSERT Table2
>SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate => right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
>GO
>EXEC Demonstration 'JCV-BCD-RBSV'
>SELECT *
> FROM Table2
>StrungOut
>--
>JCV-BCD-RBSV-06242-0006
>Also, in the spec you mentioned matching on the first six characters,
>but I wondered if it might have actually been the first seven.
>One final point worth making. It would be quite practical to modify
>this so that rather than taking in a string as a parameter, it
>produced a new row such as this for each Front6 in Table1 that matches
>the current date.
>Roy Harvey
>Beacon Falls, CT
>On Wed, 30 Aug 2006 09:09:02 -0700, Paul
><Paul@.discussions.microsoft.com> wrote:
>>Hi thanks for the response. Here are more details on what I am trying to do.
>>There is a table (table1) that has hundreds of records that look like
>>JTB-ABC-MDTC-06200-0001
>>JCV-BCD-ABAM-06201-0001
>>JTB-ABC-MDAC-06200-0002
>>I need the stored procedure to take the value
>>JCV-BCD-RBSV
>>and build the rest of it based on the following conditions and then save it
>>to a table.
>>Get the current julian date, today would be 06242, two hundred forty two day
>>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>>and only the last section still to be built. This is done by doing the
>>following.
>>1. Compare the jul date for the day the stored procedure will run (06242)
>>and find all records in table 1 with the same date (call this subset a).
>>2. Next out of subset a find all that match the first 6 letters (create
>>subset b).
>>3. Next out of subset b find the greatest value of the last 4 numbers (say
>>it was 0002).
>>4. Finally increment this value by 1 and use it to finish the newly created
>>value, so we would have JVC-BCD-RBSV-06242-0003.
>>This is easy to do in vb or C#.|||Hi thanks for the response. Yes the table was already setup and there was
not time to restructure. Will take a look at what you have. Was not aware
that you can do a lot with SQL.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> Of course all the parts of that complex column should be individual
> table columns, but I will not belabor that point.
> There is nothing about that which requires going to C# or VB. This
> should give you some ideas. Note that I used a view, but each of the
> pieces could have been substringed out when references. However since
> all the real work is one in one SQL command, all those references
> would make it a good bit more opaque. Another advantage to the view
> is that it is possible to make it an indexed view, which could make a
> major difference in performance.
> CREATE TABLE Table1
> (StrungOut char(23) NOT NULL)
> INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> GO
> CREATE VIEW Table1_V
> AS
> SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
> GO
> CREATE TABLE Table2
> (StrungOut char(23) NOT NULL)
> GO
> CREATE PROC Demonstration
> @.front char(12)
> AS
> INSERT Table2
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate => right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> GO
> EXEC Demonstration 'JCV-BCD-RBSV'
> SELECT *
> FROM Table2
> StrungOut
> --
> JCV-BCD-RBSV-06242-0006
> Also, in the spec you mentioned matching on the first six characters,
> but I wondered if it might have actually been the first seven.
> One final point worth making. It would be quite practical to modify
> this so that rather than taking in a string as a parameter, it
> produced a new row such as this for each Front6 in Table1 that matches
> the current date.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi thanks for the response. Here are more details on what I am trying to do.
> >There is a table (table1) that has hundreds of records that look like
> >JTB-ABC-MDTC-06200-0001
> >JCV-BCD-ABAM-06201-0001
> >JTB-ABC-MDAC-06200-0002
> >
> >I need the stored procedure to take the value
> >JCV-BCD-RBSV
> >and build the rest of it based on the following conditions and then save it
> >to a table.
> >Get the current julian date, today would be 06242, two hundred forty two day
> >of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >and only the last section still to be built. This is done by doing the
> >following.
> >1. Compare the jul date for the day the stored procedure will run (06242)
> >and find all records in table 1 with the same date (call this subset a).
> >2. Next out of subset a find all that match the first 6 letters (create
> >subset b).
> >3. Next out of subset b find the greatest value of the last 4 numbers (say
> >it was 0002).
> >4. Finally increment this value by 1 and use it to finish the newly created
> >value, so we would have JVC-BCD-RBSV-06242-0003.
> >This is easy to do in vb or C#.
>|||I see were you get the current julian date, JYear and JDay but I did not see
if combine these to get the 06242 for example for today. thanks.
Paul G
Software engineer.
"Roy Harvey" wrote:
> An alternate coding of the WHERE clause:
> WHERE JYear => right(convert(char(4),datepart(year,getdate())),2)
> AND JDay => right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> Roy
> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> wrote:
> >Of course all the parts of that complex column should be individual
> >table columns, but I will not belabor that point.
> >
> >There is nothing about that which requires going to C# or VB. This
> >should give you some ideas. Note that I used a view, but each of the
> >pieces could have been substringed out when references. However since
> >all the real work is one in one SQL command, all those references
> >would make it a good bit more opaque. Another advantage to the view
> >is that it is possible to make it an indexed view, which could make a
> >major difference in performance.
> >
> >CREATE TABLE Table1
> >(StrungOut char(23) NOT NULL)
> >
> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >
> >GO
> >CREATE VIEW Table1_V
> >AS
> >SELECT StrungOut,
> > First6 = SUBSTRING(StrungOut,1,6),
> > JDate = SUBSTRING(StrungOut,14,5),
> > JYear = SUBSTRING(StrungOut,14,2),
> > JDay = SUBSTRING(StrungOut,16,3),
> > Last4 = SUBSTRING(Strungout,20,4)
> > FROM Table1
> >GO
> >
> >CREATE TABLE Table2
> >(StrungOut char(23) NOT NULL)
> >GO
> >
> >CREATE PROC Demonstration
> >@.front char(12)
> >AS
> >
> >INSERT Table2
> >SELECT @.front + '-' +
> > right(convert(char(4),datepart(year,getdate())),2) +
> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> > '-' +
> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> > FROM Table1_V
> > WHERE JDate => > right(convert(char(4),datepart(year,getdate())),2) +
> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> > AND First6 = SUBSTRING(@.front,1,6)
> >GO
> >
> >EXEC Demonstration 'JCV-BCD-RBSV'
> >
> >SELECT *
> > FROM Table2
> >
> >StrungOut
> >--
> >JCV-BCD-RBSV-06242-0006
> >
> >Also, in the spec you mentioned matching on the first six characters,
> >but I wondered if it might have actually been the first seven.
> >
> >One final point worth making. It would be quite practical to modify
> >this so that rather than taking in a string as a parameter, it
> >produced a new row such as this for each Front6 in Table1 that matches
> >the current date.
> >
> >Roy Harvey
> >Beacon Falls, CT
> >
> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> ><Paul@.discussions.microsoft.com> wrote:
> >
> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >>There is a table (table1) that has hundreds of records that look like
> >>JTB-ABC-MDTC-06200-0001
> >>JCV-BCD-ABAM-06201-0001
> >>JTB-ABC-MDAC-06200-0002
> >>
> >>I need the stored procedure to take the value
> >>JCV-BCD-RBSV
> >>and build the rest of it based on the following conditions and then save it
> >>to a table.
> >>Get the current julian date, today would be 06242, two hundred forty two day
> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >>and only the last section still to be built. This is done by doing the
> >>following.
> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >>and find all records in table 1 with the same date (call this subset a).
> >>2. Next out of subset a find all that match the first 6 letters (create
> >>subset b).
> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >>it was 0002).
> >>4. Finally increment this value by 1 and use it to finish the newly created
> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >>This is easy to do in vb or C#.
>|||JYear and JDay are the pieces of the Julian date in Table1, so they
are only used for the comparison.
In the assignment:
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
the Julian date part of the output string is the second and third
lines. BUT, I just thought of an alternate way to code the
assignment. We went to all that trouble to make sure Table1_V.JDate
matched the current day, so instead of using the current day we could
just use Table1_V.JDate.
SELECT @.front + '-' +
JDate +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
However, I'm guessing that IF there is no matching row with the
current date you might actually want to create the new row with 0000
or 0001 as the final part of the string. In that case I would keep to
using the derivation from getdate() as it will be easier to code in
the row-not-found branch.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 10:32:01 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>I see were you get the current julian date, JYear and JDay but I did not see
>if combine these to get the 06242 for example for today. thanks.
>Paul G
>Software engineer.
>
>"Roy Harvey" wrote:
>> An alternate coding of the WHERE clause:
>> WHERE JYear =>> right(convert(char(4),datepart(year,getdate())),2)
>> AND JDay =>> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
>> AND First6 = SUBSTRING(@.front,1,6)
>> Roy
>> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
>> wrote:
>> >Of course all the parts of that complex column should be individual
>> >table columns, but I will not belabor that point.
>> >
>> >There is nothing about that which requires going to C# or VB. This
>> >should give you some ideas. Note that I used a view, but each of the
>> >pieces could have been substringed out when references. However since
>> >all the real work is one in one SQL command, all those references
>> >would make it a good bit more opaque. Another advantage to the view
>> >is that it is possible to make it an indexed view, which could make a
>> >major difference in performance.
>> >
>> >CREATE TABLE Table1
>> >(StrungOut char(23) NOT NULL)
>> >
>> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
>> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
>> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
>> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
>> >
>> >GO
>> >CREATE VIEW Table1_V
>> >AS
>> >SELECT StrungOut,
>> > First6 = SUBSTRING(StrungOut,1,6),
>> > JDate = SUBSTRING(StrungOut,14,5),
>> > JYear = SUBSTRING(StrungOut,14,2),
>> > JDay = SUBSTRING(StrungOut,16,3),
>> > Last4 = SUBSTRING(Strungout,20,4)
>> > FROM Table1
>> >GO
>> >
>> >CREATE TABLE Table2
>> >(StrungOut char(23) NOT NULL)
>> >GO
>> >
>> >CREATE PROC Demonstration
>> >@.front char(12)
>> >AS
>> >
>> >INSERT Table2
>> >SELECT @.front + '-' +
>> > right(convert(char(4),datepart(year,getdate())),2) +
>> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
>> > '-' +
>> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
>> > FROM Table1_V
>> > WHERE JDate =>> > right(convert(char(4),datepart(year,getdate())),2) +
>> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
>> > AND First6 = SUBSTRING(@.front,1,6)
>> >GO
>> >
>> >EXEC Demonstration 'JCV-BCD-RBSV'
>> >
>> >SELECT *
>> > FROM Table2
>> >
>> >StrungOut
>> >--
>> >JCV-BCD-RBSV-06242-0006
>> >
>> >Also, in the spec you mentioned matching on the first six characters,
>> >but I wondered if it might have actually been the first seven.
>> >
>> >One final point worth making. It would be quite practical to modify
>> >this so that rather than taking in a string as a parameter, it
>> >produced a new row such as this for each Front6 in Table1 that matches
>> >the current date.
>> >
>> >Roy Harvey
>> >Beacon Falls, CT
>> >
>> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
>> ><Paul@.discussions.microsoft.com> wrote:
>> >
>> >>Hi thanks for the response. Here are more details on what I am trying to do.
>> >>There is a table (table1) that has hundreds of records that look like
>> >>JTB-ABC-MDTC-06200-0001
>> >>JCV-BCD-ABAM-06201-0001
>> >>JTB-ABC-MDAC-06200-0002
>> >>
>> >>I need the stored procedure to take the value
>> >>JCV-BCD-RBSV
>> >>and build the rest of it based on the following conditions and then save it
>> >>to a table.
>> >>Get the current julian date, today would be 06242, two hundred forty two day
>> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>> >>and only the last section still to be built. This is done by doing the
>> >>following.
>> >>1. Compare the jul date for the day the stored procedure will run (06242)
>> >>and find all records in table 1 with the same date (call this subset a).
>> >>2. Next out of subset a find all that match the first 6 letters (create
>> >>subset b).
>> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
>> >>it was 0002).
>> >>4. Finally increment this value by 1 and use it to finish the newly created
>> >>value, so we would have JVC-BCD-RBSV-06242-0003.
>> >>This is easy to do in vb or C#.|||ok thanks for the information.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I see were you get the current julian date, JYear and JDay but I did not see
> >if combine these to get the 06242 for example for today. thanks.
> >Paul G
> >Software engineer.
> >
> >
> >"Roy Harvey" wrote:
> >
> >> An alternate coding of the WHERE clause:
> >>
> >> WHERE JYear => >> right(convert(char(4),datepart(year,getdate())),2)
> >> AND JDay => >> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> AND First6 = SUBSTRING(@.front,1,6)
> >>
> >> Roy
> >>
> >> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> >> wrote:
> >>
> >> >Of course all the parts of that complex column should be individual
> >> >table columns, but I will not belabor that point.
> >> >
> >> >There is nothing about that which requires going to C# or VB. This
> >> >should give you some ideas. Note that I used a view, but each of the
> >> >pieces could have been substringed out when references. However since
> >> >all the real work is one in one SQL command, all those references
> >> >would make it a good bit more opaque. Another advantage to the view
> >> >is that it is possible to make it an indexed view, which could make a
> >> >major difference in performance.
> >> >
> >> >CREATE TABLE Table1
> >> >(StrungOut char(23) NOT NULL)
> >> >
> >> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >> >
> >> >GO
> >> >CREATE VIEW Table1_V
> >> >AS
> >> >SELECT StrungOut,
> >> > First6 = SUBSTRING(StrungOut,1,6),
> >> > JDate = SUBSTRING(StrungOut,14,5),
> >> > JYear = SUBSTRING(StrungOut,14,2),
> >> > JDay = SUBSTRING(StrungOut,16,3),
> >> > Last4 = SUBSTRING(Strungout,20,4)
> >> > FROM Table1
> >> >GO
> >> >
> >> >CREATE TABLE Table2
> >> >(StrungOut char(23) NOT NULL)
> >> >GO
> >> >
> >> >CREATE PROC Demonstration
> >> >@.front char(12)
> >> >AS
> >> >
> >> >INSERT Table2
> >> >SELECT @.front + '-' +
> >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> >> > '-' +
> >> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> >> > FROM Table1_V
> >> > WHERE JDate => >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> > AND First6 = SUBSTRING(@.front,1,6)
> >> >GO
> >> >
> >> >EXEC Demonstration 'JCV-BCD-RBSV'
> >> >
> >> >SELECT *
> >> > FROM Table2
> >> >
> >> >StrungOut
> >> >--
> >> >JCV-BCD-RBSV-06242-0006
> >> >
> >> >Also, in the spec you mentioned matching on the first six characters,
> >> >but I wondered if it might have actually been the first seven.
> >> >
> >> >One final point worth making. It would be quite practical to modify
> >> >this so that rather than taking in a string as a parameter, it
> >> >produced a new row such as this for each Front6 in Table1 that matches
> >> >the current date.
> >> >
> >> >Roy Harvey
> >> >Beacon Falls, CT
> >> >
> >> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> >> ><Paul@.discussions.microsoft.com> wrote:
> >> >
> >> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >> >>There is a table (table1) that has hundreds of records that look like
> >> >>JTB-ABC-MDTC-06200-0001
> >> >>JCV-BCD-ABAM-06201-0001
> >> >>JTB-ABC-MDAC-06200-0002
> >> >>
> >> >>I need the stored procedure to take the value
> >> >>JCV-BCD-RBSV
> >> >>and build the rest of it based on the following conditions and then save it
> >> >>to a table.
> >> >>Get the current julian date, today would be 06242, two hundred forty two day
> >> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >> >>and only the last section still to be built. This is done by doing the
> >> >>following.
> >> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >> >>and find all records in table 1 with the same date (call this subset a).
> >> >>2. Next out of subset a find all that match the first 6 letters (create
> >> >>subset b).
> >> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >> >>it was 0002).
> >> >>4. Finally increment this value by 1 and use it to finish the newly created
> >> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >> >>This is easy to do in vb or C#.
> >>
>|||I tried out the sample and it worked. Anyhow just had a quick last question.
Do you know if there is a way to conditionally run a job? I am thinking of
scheduling the stored procedure but in some cases if a manual data entry has
taken place (through an asp.net web application) I will not want to add a new
record with the scheduled job. I guess this could be properly handled in the
stored procedure(scheduled job), possibly perform some type of table check to
see when the last entry took place to know weather or not to add a record.
Thanks.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I see were you get the current julian date, JYear and JDay but I did not see
> >if combine these to get the 06242 for example for today. thanks.
> >Paul G
> >Software engineer.
> >
> >
> >"Roy Harvey" wrote:
> >
> >> An alternate coding of the WHERE clause:
> >>
> >> WHERE JYear => >> right(convert(char(4),datepart(year,getdate())),2)
> >> AND JDay => >> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> AND First6 = SUBSTRING(@.front,1,6)
> >>
> >> Roy
> >>
> >> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> >> wrote:
> >>
> >> >Of course all the parts of that complex column should be individual
> >> >table columns, but I will not belabor that point.
> >> >
> >> >There is nothing about that which requires going to C# or VB. This
> >> >should give you some ideas. Note that I used a view, but each of the
> >> >pieces could have been substringed out when references. However since
> >> >all the real work is one in one SQL command, all those references
> >> >would make it a good bit more opaque. Another advantage to the view
> >> >is that it is possible to make it an indexed view, which could make a
> >> >major difference in performance.
> >> >
> >> >CREATE TABLE Table1
> >> >(StrungOut char(23) NOT NULL)
> >> >
> >> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >> >
> >> >GO
> >> >CREATE VIEW Table1_V
> >> >AS
> >> >SELECT StrungOut,
> >> > First6 = SUBSTRING(StrungOut,1,6),
> >> > JDate = SUBSTRING(StrungOut,14,5),
> >> > JYear = SUBSTRING(StrungOut,14,2),
> >> > JDay = SUBSTRING(StrungOut,16,3),
> >> > Last4 = SUBSTRING(Strungout,20,4)
> >> > FROM Table1
> >> >GO
> >> >
> >> >CREATE TABLE Table2
> >> >(StrungOut char(23) NOT NULL)
> >> >GO
> >> >
> >> >CREATE PROC Demonstration
> >> >@.front char(12)
> >> >AS
> >> >
> >> >INSERT Table2
> >> >SELECT @.front + '-' +
> >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> >> > '-' +
> >> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> >> > FROM Table1_V
> >> > WHERE JDate => >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> > AND First6 = SUBSTRING(@.front,1,6)
> >> >GO
> >> >
> >> >EXEC Demonstration 'JCV-BCD-RBSV'
> >> >
> >> >SELECT *
> >> > FROM Table2
> >> >
> >> >StrungOut
> >> >--
> >> >JCV-BCD-RBSV-06242-0006
> >> >
> >> >Also, in the spec you mentioned matching on the first six characters,
> >> >but I wondered if it might have actually been the first seven.
> >> >
> >> >One final point worth making. It would be quite practical to modify
> >> >this so that rather than taking in a string as a parameter, it
> >> >produced a new row such as this for each Front6 in Table1 that matches
> >> >the current date.
> >> >
> >> >Roy Harvey
> >> >Beacon Falls, CT
> >> >
> >> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> >> ><Paul@.discussions.microsoft.com> wrote:
> >> >
> >> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >> >>There is a table (table1) that has hundreds of records that look like
> >> >>JTB-ABC-MDTC-06200-0001
> >> >>JCV-BCD-ABAM-06201-0001
> >> >>JTB-ABC-MDAC-06200-0002
> >> >>
> >> >>I need the stored procedure to take the value
> >> >>JCV-BCD-RBSV
> >> >>and build the rest of it based on the following conditions and then save it
> >> >>to a table.
> >> >>Get the current julian date, today would be 06242, two hundred forty two day
> >> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >> >>and only the last section still to be built. This is done by doing the
> >> >>following.
> >> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >> >>and find all records in table 1 with the same date (call this subset a).
> >> >>2. Next out of subset a find all that match the first 6 letters (create
> >> >>subset b).
> >> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >> >>it was 0002).
> >> >>4. Finally increment this value by 1 and use it to finish the newly created
> >> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >> >>This is easy to do in vb or C#.
> >>
>|||If the proc should NOT add an entry based on conditions that it can
test in the database, then by all means put those tests in the proc,
and just schedule it to run unconditionally. Coding to prevent bad
data in your database is what we all strive for. It also means you
can schedule it to simply execute and not worry about it.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 14:33:01 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>I tried out the sample and it worked. Anyhow just had a quick last question.
> Do you know if there is a way to conditionally run a job? I am thinking of
>scheduling the stored procedure but in some cases if a manual data entry has
>taken place (through an asp.net web application) I will not want to add a new
>record with the scheduled job. I guess this could be properly handled in the
>stored procedure(scheduled job), possibly perform some type of table check to
>see when the last entry took place to know weather or not to add a record.
>Thanks.|||ok sounds like a good idea!
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> If the proc should NOT add an entry based on conditions that it can
> test in the database, then by all means put those tests in the proc,
> and just schedule it to run unconditionally. Coding to prevent bad
> data in your database is what we all strive for. It also means you
> can schedule it to simply execute and not worry about it.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 14:33:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I tried out the sample and it worked. Anyhow just had a quick last question.
> > Do you know if there is a way to conditionally run a job? I am thinking of
> >scheduling the stored procedure but in some cases if a manual data entry has
> >taken place (through an asp.net web application) I will not want to add a new
> >record with the scheduled job. I guess this could be properly handled in the
> >stored procedure(scheduled job), possibly perform some type of table check to
> >see when the last entry took place to know weather or not to add a record.
> >Thanks.
>

Sunday, March 25, 2012

Can we change the Colloation setting for an existing database?

Can we change the Colloation setting for an existing database?

Can we change the Colloation setting for an existing database?

Thursday, March 22, 2012

Can torn page detection fire from existing corruption?

Tuesday, March 20, 2012

Can torn page detection fire from existing corruption?

Can torn page detection fire from existing corruption?

Monday, March 19, 2012

can this be done in SQL2000

can this be done in SQL2000

sql reports

Blog Archive

About Me