how to change the actual data filename

i'm hoping someone can help me out with this problem.

i have a database called Tracking.mdf and its associated logfile Tracking_log.ldf

i need to build another database based on the above with more tables and keep the data separate hence the requirement to make a copy of the tracking data file i've tried changing its name but it will not attach. i can change its database name to whatever i want but somehow can't find out how i can say change its actual filename to Tracker.mdf and Tracker_log.ldf

would someone be able to help me out. thanks.

hi,

please have a look at this article on how to move user's databases..

ALTER DATABASE syntax support both logical and physical rename of database and database's files..

regards

How to capture Error Messages in script

I know how to capture the error number using @.@.ERROR, but I don't know how
to capture the actual text. It is easy to do so via an application that uses
a database driver (BDE, ADO .NET etc...), but I just want to capture the
error text in SQL script and log it to a table.
Please Note:
'select description from master.dbo.sysmessages' alone is not sufficientThe current version of SQL Server does not provide a method to get error
message text in Transact-SQL. This functionality is planned for SQL 2005,
though.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <nospam@.nospam.com> wrote in message
news:eRXqGcYjFHA.3448@.TK2MSFTNGP10.phx.gbl...
>I know how to capture the error number using @.@.ERROR, but I don't know how
> to capture the actual text. It is easy to do so via an application that
> uses
> a database driver (BDE, ADO .NET etc...), but I just want to capture the
> error text in SQL script and log it to a table.
> Please Note:
> 'select description from master.dbo.sysmessages' alone is not sufficient
>

How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/i...on/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
> give
>
>
> See http://msdn.microsoft.com/msdnmag/i...ledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,

> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.

> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>

How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
>>Unfortunatly you cannot capture such kind of errors in current version.I
>>know with Yukon will introduce error handle like try--catch but cannot
> give
>>more details at this time.
>
> See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
> for new Yukon enhancements in error handling with the TRY/CATCH construct
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>

How to calculate actual size of column?

Hi,
I am wondering if any equivalent function available which
gives the actual length of column in bytes as VSIZE in
Oracle?
If not how to calcuate actual column size?
Thanks in advance.
RajIs the DATALENGTH function what you're looking for? For example:
SELECT DATALENGTH(au_lname)
FROM pubs..authors
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Raj" <vmresumes@.yahoo.com> wrote in message
news:8fdb01c345a2$356250c0$a401280a@.phx.gbl...
> Hi,
> I am wondering if any equivalent function available which
> gives the actual length of column in bytes as VSIZE in
> Oracle?
> If not how to calcuate actual column size?
> Thanks in advance.
> Raj