Determine SPID is using TempDB disk space

I have an application that processes approximately 300 transactions/sec on a
SQL Server 2000 database.
Periodically I have a process that uses a large amount of TempDB database
used disk space.
I have an alert set if the TempDB database used disk space reaches 80% then
send an alert.
Please help me with a script that I can use after the alert is set off to
determine which process is using the most TempDB database used disk space.
Thank You,You can run sp_who2 on tempdb, and see which [SPID] has been consuming
massive unusually high amounts of [DiskIO]. Also, is you know the object_id
that is consuming the disk space, then sp_lock will reveal who is currently
holding it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B9B5531E-B69F-4406-8951-8A3C3B009819@.microsoft.com...
> I have an application that processes approximately 300 transactions/sec on
> a
> SQL Server 2000 database.
> Periodically I have a process that uses a large amount of TempDB database
> used disk space.
> I have an alert set if the TempDB database used disk space reaches 80%
> then
> send an alert.
> Please help me with a script that I can use after the alert is set off to
> determine which process is using the most TempDB database used disk space.
> Thank You,
>
>
>

Details Section Question?

My Questions Is
How to let Detaisls Section fill empty space of the report
For example SalesInvoice Report

ReportHeader-->Sales Invoice Headers
ReprtFooter -->Sales Invoice Totals,etc
Now Details Section-->
i want it to fill the remain space with Salesinvoice Items even if it only contain few items

How do i do that?
Thanks in advancenot a issue man just put the item fields it will display the item it contain and will leave the remaining space blank automatically we don't have to do anything in that . This is the only benefits of using details section it will calculate paper size and then minus header and footer and remaining space will be used by details section

ok.

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
Percy
Sounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file i
n
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf i
n
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted
.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the se
cond log file to the
first log files name and location? If so, you probably need to go the restor
e route, open a case or
try the last resort posted by Andrew. If not, well, your options are the sam
e...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the D
B
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original locati
on
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a prima
ry
> database file. Could not open new darabase 'crn'. CREATE DATABASE is abort
ed.
> Device activation errror, The physical name 'c:\program files\microsoft sq
l
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

detach and attach database with two logfiles.

Hello fellow SQL Server users :-)
Some background. Have a database (quite large) that has
diskspace problems. Need to acquire more space so that the
database wont experiance some future problems. So to
remedy this problem temporarily i created a new logfile on
another disk. I also shrunk and restricted the filegrowth
on the first logfile. Ok, the problem was solved for the
time being.
Now we have scheduled a stop in the SQL Server (it's
version 7.0 and latest SP) so now i am doing a detach of
the database to move it all to new disks.
I am attaching the mdf file on one disk and the logfiles
on another disk.
The thing is. I just want one logfile and not two logfiles
on the new disk. How can i get rid of the new logfile (or
old one, doesnt matter which) so that i get the sweet
feeling of one MDF and one LDF file on the disks?
I know that when only having one ldf file you can use
sp_attach_single_file_db to attach the database and it
creates a new logfile for you. But that doesnt work when
having 2 logfiles from the beginning.
It isn't absolutly necessary but it looks a lot better...
So if you have a way to solve this please post it here
Thanks in advance
Risunsee dbcc shrinkfile emptyfile.
You can then use alter database to drop te file.

Destination runs out of space => job never finishes

Hi,

I have developed a SSIS package that performs data cleansing before data is loaded into a DW. I'm using a Multicast transformation to load the cleansed data to both a production and a test environment.

A couple of time now, I have exprienced that the test environment runs out of disk space and can not grow the database file (I know - this should never happen, but tell that to the DBA :o).

For some reason this causes the package to hang - no error is returned and the job executing the package remains in "Executing job" status, meaning that the cube processing is never started. Shouldn't I get an error back when the disk runs out of space?

Regards,
SuneYes, you should. However, we rely on the provider returning the error from the SQL Server. If it doesn't then we have no way of knowing an error occurred.

Thanks,
Matt

Desparately need help parsing field

Hello,

I have to split data that is currently in a single field delimited by a space.

Problem is the field could be between 7 and 72 bytes long.
I have to parse the field for a character set and its corresponding number.
For instance: MyColumn = Index1 50 Index2 40 Index3 10

I need to parse this out to:

INDEX PERCENT
-------
Index1 50
Index2 40
Index3 10

Again, there may exist 1-4 sets of this data within the field.

Anyone have a stored proc or better T-SQL ?

Thanks so much in advance!

mzimmIs this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?|||Originally posted by rnealejr
Is this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?

Hello,

This will repeat many times.
Upwards of 1000 records exis t in this table.
The datasets will be in combination of
(Example) Index1 5 Index2 10 ... Indexn ##
where Indexn can be any number of characters long and ## is between 1 and 100. The characters and numbers are separated by a space.

Actual example:

Column1 = Lehman 100
Column2 = lehIUSGV 5 LehGVcr 10 MSCIEAFE 5 R2000Gro 5 RMCAPGro 10
Column3 = LehCredt 10 Lehmktgov 15 DnB 35 S&P 40

Thanks much for your help!

mzimm|||These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?|||Originally posted by rnealejr
These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?

I GREATLY aplogize, it's late.
Those are RECORDS, only 1 column.|||This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.|||Originally posted by rnealejr
This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.

Yes, Please, can you post the VBscript?
It would be thee answer to my problem.

Thanks again.|||Here you go (Let me know if you need additional help):

Function Main()
strString = DTSSource("b")
intTest = 1
intBegin = 1
while intTest <> 0
intTest = instr(intTest , strString, " ")
strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & " "
intTest = intTest + 1
intBegin = intTest
intTest = instr(intTest, strString, " ")
if intTest = 0 then
strFinal = strFinal & right(strString, len(strString) - intBegin + 1) & " "
else
strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & vbCrLf
intTest = intTest + 1
intBegin = intTest
end if
wend
DTSDestination("b") = strFinal
Main = DTSTransformStat_OK
End Function

Design with the best space allocation.

We are getting ready to setup the gov's death file on a sql server. I would
like thoughts on the table design to optimize space. This is the data spec's
https://dmf.ntis.gov/recordlayout.pdf
The master file doesn't have the first column. So here is where I was headed
for the main table. The column's with asterisk are possibly empty. The
file updates will be done based off SSN column.
CREATE TABLE [dbo].[tblDMFile] (
[SSN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[NameSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[StatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeathDate] [smalldatetime] NULL ,
[BirthDate] [smalldatetime] NULL ,
*[ResidentCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[ZipLastResident] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
*[ZipLumpSumPaymt] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
(
[SSN]
) ON [PRIMARY]
GO
CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([LastName], [FirstName])
ON [PRIMARY]
GO
TIA
CDMy understanding (could be wrong) is that SSNs of deceased persons are
reused, (or can be resused) so, if that is in fact true, then your use of SS
N
as single column Primary Key might be an issue. If true, you could add
DeathDate as well, (make it non null) I would assume since this is Death
File, every record will have to have a Death Date?
CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
([DeathDate],[SSN]) ON [PRIMARY]
That will handle the issue...
Also, suggest you put DeathDate first in the Index, and make this the
Clustered Index (on DeatHDate, SSN), since this will ensure that new Inserts
will generally be added to new Pages on disk, not randomly distributed
throughout the physical storage... Putting SSN as first column in PK index,
means that records would be physically ordered on SSN, and new inserts will
be distributed among all the physical pages on disk, causing many page split
s
and rapid table and index fragmentation.
"CD" wrote:

> We are getting ready to setup the gov's death file on a sql server. I wou
ld
> like thoughts on the table design to optimize space. This is the data spec
's
> https://dmf.ntis.gov/recordlayout.pdf
> The master file doesn't have the first column. So here is where I was head
ed
> for the main table. The column's with asterisk are possibly empty. The
> file updates will be done based off SSN column.
> CREATE TABLE [dbo].[tblDMFile] (
> [SSN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[NameSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FirstName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[StatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DeathDate] [smalldatetime] NULL ,
> [BirthDate] [smalldatetime] NULL ,
> *[ResidentCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[ZipLastResident] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> *[ZipLumpSumPaymt] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
> (
> [SSN]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([LastName], [FirstName])
> ON [PRIMARY]
> GO
> TIA
> CD
>
>|||Thanks for the reply. That is a good point about SSN resuage(possibly)
1) To clarify the table schema is good for the best Least space usage
2) ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
([DeathDate], [SSN] ) ON [PRIMARY]
3) CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([DeathDate],
[LastName], [FirstName])
ON [PRIMARY]
I am guessing most of the searches will be off the SSN then probably least
likely LastName...
Thanks again.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:A19A88F8-A183-4116-98F2-24BF3666352E@.microsoft.com...
> My understanding (could be wrong) is that SSNs of deceased persons are
> reused, (or can be resused) so, if that is in fact true, then your use of
> SSN
> as single column Primary Key might be an issue. If true, you could add
> DeathDate as well, (make it non null) I would assume since this is Death
> File, every record will have to have a Death Date?
> CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
> ([DeathDate],[SSN]) ON [PRIMARY]
> That will handle the issue...
> Also, suggest you put DeathDate first in the Index, and make this the
> Clustered Index (on DeatHDate, SSN), since this will ensure that new
> Inserts
> will generally be added to new Pages on disk, not randomly distributed
> throughout the physical storage... Putting SSN as first column in PK
> index,
> means that records would be physically ordered on SSN, and new inserts
> will
> be distributed among all the physical pages on disk, causing many page
> splits
> and rapid table and index fragmentation.
>
> "CD" wrote:
>|||CD,
The only other suggestion I can make, related to minimizing disk storage,
which will only save you 8bytes per record, is based on my assumption that
you are not storing time data in either Birthdate or death date... Therefore
,
by using SmallDateTime, which uses 2bytes for date, and 2 bytes for time, yo
u
are wasting the 2 time bytes. One way avoid this is to use your own
propietary "Date" data type, based on SmallInt, where the value of the
smallInt
(which is from -2^15 (-32,768) through 2^15 - 1 (32,767) represents the
Date as number of days since some arbitrary Date. If you Cast a
smalldateTime to a SmallInt, you will get the same value based on 1 Jan 1900
,
If you want to use the same values as the 2 bytes in a SmallDateTime Value,
you must remember that the 2 date bytes in a smalldatetime represent integer
s
from 0 to 65535, (UNSIGNED 2-byte Integer), not from -32,768 to 32,767.
So, if you want to take this approach, first you need to decide what range
of dates you want to be able to represent... And to do this in 2 bytes, you
need to limit it to 65536 dates.. If you want to use the same range of dates
as SmallDateTime
(1 Jan 1900 - 6 Jun 2079)
Then the conversion from a SmalldateTime value (say it's in Variable @.SDT)
to the smallint you need to store in your table will be
Cast (Cast (@.SDT as Integer) - 32768 As SmallInt)
1) Cast SmallDateTime as Integer (it's in range 0 - 65535)
2) Subtract 32,768 (now it's in range - 32,768 to 32,7687)
3) Cast it as SmallInt (This not absoutely necessay as Insert into SmallInt
Column will auto convert an integer)
And the conversion from the smallint in the table (say Colname is
"DeathDt"), to the SmalldateTime value will be
Cast(DeathDt + 32768 As SmallDateTime)
Add 32,768 (to make it positive (in range from 0 - 65535) and then cast it
to smalldateTime
By the way, you can add Calculated columns to the table definition, based
exactly on that latter formula [ Cast(DeathDt + 32768 As SmallDateTime) ]
that will not increase on disk data storage, yet output the exact
SmallDateTime value exactly as you would if you stored it in the the table a
s
a SmallDateTime...
"CD" wrote:

> Thanks for the reply. That is a good point about SSN resuage(possibly)
> 1) To clarify the table schema is good for the best Least space usage
> 2) ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
> ([DeathDate], [SSN] ) ON [PRIMARY]
> 3) CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([DeathDate],
> [LastName], [FirstName])
> ON [PRIMARY]
> I am guessing most of the searches will be off the SSN then probably least
> likely LastName...
> Thanks again.
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:A19A88F8-A183-4116-98F2-24BF3666352E@.microsoft.com...
>
>|||Never actually researched this one, Thanx Michael... Another urban legend
bites the dust...
"Michael C#" wrote:

> According to the Social Security Administration (www.ssa.gov), SSN's are N
OT
> re-assigned after someone dies. However, some people are not assigned SSN
's
> (Pennsylvania Amish spring immediately to mind), so SSN might be NULL for
> some folks, which could cause issues. I'm facing a similar issue with
> trying to identify people uniquely in a database right now myself.
> --Quote From SSA.GOV Website:
> Question:
> Are Social Security Numbers re-assigned after a person dies?
> Answer:
> No. We do not reassign a Social Security number (SSN) after the
> number holder's death. Even though we have issued over 415 million SSNs so
> far, and we assign about 5 and one-half million new numbers a year, the
> current numbering system will provide us with enough new numbers for sever
al
> generations into the future with no changes in the numbering system.
>
> --End Quote
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:A19A88F8-A183-4116-98F2-24BF3666352E@.microsoft.com...
>
>|||Sorry, not 8bytes per record, only 2 byes/record, total only 4 bytes saved..
.
"CBretana" wrote:
> CD,
> The only other suggestion I can make, related to minimizing disk storage,
> which will only save you 8bytes per record, is based on my assumption that
> you are not storing time data in either Birthdate or death date... Therefo
re,
> by using SmallDateTime, which uses 2bytes for date, and 2 bytes for time,
you
> are wasting the 2 time bytes. One way avoid this is to use your own
> propietary "Date" data type, based on SmallInt, where the value of the
> smallInt
> (which is from -2^15 (-32,768) through 2^15 - 1 (32,767) represents the
> Date as number of days since some arbitrary Date. If you Cast a
> smalldateTime to a SmallInt, you will get the same value based on 1 Jan 19
00,
> If you want to use the same values as the 2 bytes in a SmallDateTime Value
,
> you must remember that the 2 date bytes in a smalldatetime represent integ
ers
> from 0 to 65535, (UNSIGNED 2-byte Integer), not from -32,768 to 32,767.
> So, if you want to take this approach, first you need to decide what range
> of dates you want to be able to represent... And to do this in 2 bytes, yo
u
> need to limit it to 65536 dates.. If you want to use the same range of dat
es
> as SmallDateTime
> (1 Jan 1900 - 6 Jun 2079)
> Then the conversion from a SmalldateTime value (say it's in Variable @.SDT)
> to the smallint you need to store in your table will be
> Cast (Cast (@.SDT as Integer) - 32768 As SmallInt)
> 1) Cast SmallDateTime as Integer (it's in range 0 - 65535)
> 2) Subtract 32,768 (now it's in range - 32,768 to 32,7687)
> 3) Cast it as SmallInt (This not absoutely necessay as Insert into SmallIn
t
> Column will auto convert an integer)
>
> And the conversion from the smallint in the table (say Colname is
> "DeathDt"), to the SmalldateTime value will be
> Cast(DeathDt + 32768 As SmallDateTime)
> Add 32,768 (to make it positive (in range from 0 - 65535) and then cast i
t
> to smalldateTime
> By the way, you can add Calculated columns to the table definition, based
> exactly on that latter formula [ Cast(DeathDt + 32768 As SmallDateTime) ]
> that will not increase on disk data storage, yet output the exact
> SmallDateTime value exactly as you would if you stored it in the the table
as
> a SmallDateTime...
>
> "CD" wrote:
>|||Thanks for the advice,
The date is currently a problem in its current format (MM,DD,CC,YY)
01301980. I have the data loaded (dates are in varchar) and plan to run a
update to move the year to front the change the column type.
I have not test but hope this will work.
set Deathdate = Right(Deathdate, 4) + Left(Deathdate, 4)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:17A3FFDD-9F3F-4266-9B28-9F57F729BC07@.microsoft.com...
> Sorry, not 8bytes per record, only 2 byes/record, total only 4 bytes
> saved...
> "CBretana" wrote:
>|||Yes that should work...
but cast or convert the result to smalldateTime as well...
Cast(Right(Deathdate, 4) + Left(Deathdate, 4) As SmallDateTime)
"CD" wrote:

> Thanks for the advice,
> The date is currently a problem in its current format (MM,DD,CC,YY)
> 01301980. I have the data loaded (dates are in varchar) and plan to run a
> update to move the year to front the change the column type.
> I have not test but hope this will work.
> set Deathdate = Right(Deathdate, 4) + Left(Deathdate, 4)
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:17A3FFDD-9F3F-4266-9B28-9F57F729BC07@.microsoft.com...
>
>|||On Wed, 9 Mar 2005 12:14:46 -0600, CD wrote:
(snip)
> The column's with asterisk are possibly empty.
(snip)
Hi CD,
Then why are the columns without asterisk not declared as NOT NULL?
* Prevents data corruption
* Since you want the best space allocation: would save you one byte per
row (up to 8 nullable columns take a one-byte bitmap to hold the
NULL/NOT NULL control bits; 9-16 nullable columns add a second byte to
the NULL/NOT NULL bitmap, etc.)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)