Change Logical File Name Containing a Hyphen

I'm running SQL Server 2000. I have a DB with a logical file name containing
a hyphen. When trying to change the size of the db and log files by running
the following SQL statement it throws an error stating the file name is not
listed in sysfiles:
-- Modify db file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_DATA],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
-- Modify log file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_Log],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
GO
I've verified the logical file names using SP_HELPDB. When the DB was
created I did not specify any logical file names. SQL Server assigned them
automatically and put the hyphens in. What is causing the problem with the
SQL command and how can I get around this problem.
Thanks for any help!Please post result of following:
sp_helpfile
"Skippy, DOT DBA" wrote:
> I'm running SQL Server 2000. I have a DB with a logical file name containing
> a hyphen. When trying to change the size of the db and log files by running
> the following SQL statement it throws an error stating the file name is not
> listed in sysfiles:
> -- Modify db file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_DATA],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> -- Modify log file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_Log],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> GO
> I've verified the logical file names using SP_HELPDB. When the DB was
> created I did not specify any logical file names. SQL Server assigned them
> automatically and put the hyphens in. What is causing the problem with the
> SQL command and how can I get around this problem.
> Thanks for any help!|||SP_HELPFILE results:
DB_NAME-DS_Data
1 <drive
letter>:\data\DB_NAME-DS_Data.MDF
PRIMARY 32896 KB Unlimited 10% data only
DB_NAME-DS_Log
2 <drive
letter>:\Logs\DB_NAME-DS_Log.LDF
NULL 102400 KB Unlimited 25600 KB log only
"Absar Ahmad" wrote:
> Please post result of following:
> sp_helpfile
> "Skippy, DOT DBA" wrote:
> > I'm running SQL Server 2000. I have a DB with a logical file name containing
> > a hyphen. When trying to change the size of the db and log files by running
> > the following SQL statement it throws an error stating the file name is not
> > listed in sysfiles:
> >
> > -- Modify db file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_DATA],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > -- Modify log file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_Log],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > GO
> >
> > I've verified the logical file names using SP_HELPDB. When the DB was
> > created I did not specify any logical file names. SQL Server assigned them
> > automatically and put the hyphens in. What is causing the problem with the
> > SQL command and how can I get around this problem.
> >
> > Thanks for any help!sql

Change location of RS log files?

Hi,
How can I change the location where the RS log files are
stored/created? The .config file seems to have options to change the
filename, but will this allow me to specify a different folder as
well?
We would like to put these files on a seperate hard drive from our
main application drive, in case the log files ever happen to grow
exponentially and fill the entire DB again.
Matt BrownEach of the following files for MS Reporting Services has a section called
RStrace that contains the settings for the log files.
...\ReportServer\bin\ReportingServicesService.exe.config
â?¦\ReportServer\web.config
â?¦\ReportManager\web.config
A default installation writes the log files to the directory where Reporting
Services is installed under the LogFiles folder. In order to change the
default location of the log files you need to add the following line under
the RStrace section for each of the above files. Once you have added the
directory line you will need to restart the ReportServer service.
<add name=â'Directoryâ' value=â'your dirâ' />
Example:
<RStrace>
<add name="Directory" value="d:\RSLogs" />
<add name="FileName" value="ReportServerService_" />
<add name="FileSizeLimitMb" value="32" />
<add name="KeepFilesForDays" value="14" />
<add name="Prefix" value="tid, time" />
<add name="TraceListeners" value="debugwindow, file"
/>
<add name="TraceFileMode" value="unique" />
<add name="Components" value="all" />
</RStrace>

Change Drive Location for Log Files.

Hello,
I am attempting to change the location of the log files for one of the
default SQL instance, every time I click on the ... button under Properties
of the instance/Database settings/default log directory, I only receive the
single drive that the SQL Server instance was installed on, and not any of
the other active drives. Anyone have thoughts or instructions on this for me?
Thanks,
Jeremey
I take you have an additional drive which has been added to the MSCS
configuration as Physical Disk
If so then you must also take the SQLServer resource off-line and add a
dependency to the logs physical disk resource and bring sql server back
on-line. Then you'll be able to see the new disk on the Database Setting
property page.
Nik Marshall-Blank MCSD/MCDBA
"Jeremey" <Jeremey@.discussions.microsoft.com> wrote in message
news:B9C72029-3D58-4486-B485-AE649DCC6E81@.microsoft.com...
> Hello,
> I am attempting to change the location of the log files for one of the
> default SQL instance, every time I click on the ... button under
> Properties
> of the instance/Database settings/default log directory, I only receive
> the
> single drive that the SQL Server instance was installed on, and not any of
> the other active drives. Anyone have thoughts or instructions on this for
> me?
> Thanks,
> Jeremey
|||That was the problem.
Thanks,
Jeremey
"Nik Marshall-Blank" wrote:

> I take you have an additional drive which has been added to the MSCS
> configuration as Physical Disk
> If so then you must also take the SQLServer resource off-line and add a
> dependency to the logs physical disk resource and bring sql server back
> on-line. Then you'll be able to see the new disk on the Database Setting
> property page.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Jeremey" <Jeremey@.discussions.microsoft.com> wrote in message
> news:B9C72029-3D58-4486-B485-AE649DCC6E81@.microsoft.com...
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default...b;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.
|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default...b;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> > There's a setting in properties for SQL server in Enterprise Manager where
> > you can point out new default location for databases and logs. I want to
> > do
> > just that, but have two questions:
> >
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> >
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> >
> > [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> >
> > Looking forward to any help.
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/defaul...kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFil
es
> or F:\DBLogFiles). These don't need to match the default SQL Server folde
r
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/defaul...kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...
Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...
|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nva
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql

change data files name and transaction log name after restore

Hi All,
I have SQL Server database. Server name is DBServer. I have database A
in this server. If i click properties, the data files name show
"A_Data" and the transaction log name show "A_Log". I backup database A
to C:\ABackup.
After that, i create database B. I want to restore database A to
database B. I choose "from device" and select the location to
C:\ABackup. But when i tried to restore, in the Original File Name
(tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
data files name and transaction log name will change into A_Data and
A_Log.
What i want is that in my database B the name of data files and
transaction log not change after i do restore from database A.
Does anybody know how to do that?
Thanks before.On the Restore Database window select your file using 'From device'.
Go to Options. On 'Restore As' update the directory path and file name. You
can not change the logical name here, only the physical name.
Finish the Restore
If you are using SQL Server 2005
Select Database Properties, select Files tab
You can change the Logical Name here.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"afang" wrote:

> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Hi
You can restore a database with a different name ,so there is no need to
create a new database prior to restore operation
"afang" <khokimfang@.gmail.com> wrote in message
news:1147053596.925983.99420@.j33g2000cwa.googlegroups.com...
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Thanks Ben, for your information

change data files name and transaction log name after restore

Hi All,
I have SQL Server database. Server name is DBServer. I have database A
in this server. If i click properties, the data files name show
"A_Data" and the transaction log name show "A_Log". I backup database A
to C:\ABackup.
After that, i create database B. I want to restore database A to
database B. I choose "from device" and select the location to
C:\ABackup. But when i tried to restore, in the Original File Name
(tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
data files name and transaction log name will change into A_Data and
A_Log.
What i want is that in my database B the name of data files and
transaction log not change after i do restore from database A.
Does anybody know how to do that?
Thanks before.On the Restore Database window select your file using 'From device'.
Go to Options. On 'Restore As' update the directory path and file name. You
can not change the logical name here, only the physical name.
Finish the Restore
If you are using SQL Server 2005
Select Database Properties, select Files tab
You can change the Logical Name here.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"afang" wrote:
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Hi
You can restore a database with a different name ,so there is no need to
create a new database prior to restore operation
"afang" <khokimfang@.gmail.com> wrote in message
news:1147053596.925983.99420@.j33g2000cwa.googlegroups.com...
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Thanks Ben, for your information

Change CSV file to XLS

Hi,

I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.

thanksOriginally posted by krishna
Hi,

I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.

thanks

Why can you not import the csv file? What advantages does importing a .XLS file provide?|||because, it is easy to import excel file than csv format.|||DTS can handle .CSV files just as easy as it can handle .XLS files that is why I was askng for advantages. Sorry I couldn't help.

Originally posted by krishna
because, it is easy to import excel file than csv format.|||I don't see any issues in handling .CSV or .XLS by Excel and SQL server DTS.|||what datasouce i should use ? Microsoft Text-Treiber(*.txt,*.csv)?
If i use this one as datasource for to import CSV file into table it is not converting datetime column. I was thinking of using Microsoft Excel for the datasource thats why i asked about converting csv to xls

Cause of table truncated

We have a few tables is SQL 2000 database being truncated and don't know wha
t
happen to them. The database log file look-up is the way I am trying. But
dbcc does not bring too much useful information. I appreciate it if you can
point out a few ways, which can identify the cause of this incident. Thanks
.
--
DLUnderstanding the DBCC LOG output is a bit difficult, especially as there
isn't much documentation available.
You might want to use a thid party tool like Lumigent Log Explorer, which
can read transaction logs and answer the questions like the one you've just
asked.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dliu" <Dliu@.discussions.microsoft.com> wrote in message
news:9E7A4BF4-6974-46DB-A489-BBFEBD28DF59@.microsoft.com...
> We have a few tables is SQL 2000 database being truncated and don't know
> what
> happen to them. The database log file look-up is the way I am trying.
> But
> dbcc does not bring too much useful information. I appreciate it if you
> can
> point out a few ways, which can identify the cause of this incident.
> Thanks.
> --
> DL|||I'm wondering if a trigger could be created to store at least a
time/date and possibly login used to truncate? Or you could lock the
table down and then see what process receives an error while trying to
truncate. Probably some user in query analyzer|||On 5 Aug 2005 07:34:46 -0700, MICHAEL_SUNLIN@.COUNTRYWIDE.COM wrote:

>I'm wondering if a trigger could be created to store at least a
>time/date and possibly login used to truncate? Or you could lock the
>table down and then see what process receives an error while trying to
>truncate. Probably some user in query analyzer
Hi Michael,
Since TRUNCATE TABLE bypasses all trigger execution, your first
suggestion won't work.
The second suggestion will work, but it will also block all legitimate
inserts, updates and deletes. Not an option in most environments.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.

Have a data flow which inserts 1000 rows into a table. One of the rows cannot

go because of a constraint on the column. The following is thrown by SQL /

SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code:

0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL

Native Client" Hresult: 0x80040E2F Description: "The

statement has been terminated.". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F

Description: "The INSERT statement conflicted with the CHECK constraint

"CK_cpxx". The conflict occurred in database "POS_ETL",

table "dbo.cpxx", column 'Col1'.".

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE

DB Destination Input" (32)" failed because error code 0xC020907B

occurred, and the error row disposition on "input "OLE DB Destination

Input" (32)" specifies failure on error. An error occurred on the

specified object of the specified component.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component

"OLE DB Destination" (19) failed with error code 0xC0209029. The

identified component returned an error from the ProcessInput method. The error

is specific to the component, but the error is fatal and will cause the Data

Flow task to stop running.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0"

has exited with error code 0xC0209029.

>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)

This causes the onerror event to fire 4 times.

This type of behavior is useless for any error handling of this type as

anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work

than it's worth.

OnFail constraint is the simplest and totally ignoring the onerror event

handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.
Have a data flow which inserts 1000 rows into a table. One of the rows cannot go because of a constraint on the column. The following is thrown by SQL / SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the CHECK constraint "CK_cpxx". The conflict occurred in database "POS_ETL", table "dbo.cpxx", column 'Col1'.".
>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE DB Destination Input" (32)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (32)" specifies failure on error. An error occurred on the specified object of the specified component.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (19) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)
This causes the onerror event to fire 4 times.
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.
I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?

CATCH BLOCK ERRORS - what are the datatypes?

Hi,
I am writing my catch block to log error messages to a table. For this
purpose I am creating a log table, but I need to know what are the
datatypes? I can't seem to find this info.
I need the datatype of the following:
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
ThanksHi
These are documented in Books online see erturn type in
http://msdn2.microsoft.com/en-us/library/ms175069.aspx etc..
Numbers are int, procedure is nvarchar(126) and message is nvarchar(4000).
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am writing my catch block to log error messages to a table. For this
> purpose I am creating a log table, but I need to know what are the
> datatypes? I can't seem to find this info.
> I need the datatype of the following:
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> Thanks
>

CATCH BLOCK ERRORS - what are the datatypes?

Hi,
I am writing my catch block to log error messages to a table. For this
purpose I am creating a log table, but I need to know what are the
datatypes? I can't seem to find this info.
I need the datatype of the following:
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
Thanks
Hi
These are documented in Books online see erturn type in
http://msdn2.microsoft.com/en-us/library/ms175069.aspx etc..
Numbers are int, procedure is nvarchar(126) and message is nvarchar(4000).
John
"tootsuite@.gmail.com" wrote:

> Hi,
> I am writing my catch block to log error messages to a table. For this
> purpose I am creating a log table, but I need to know what are the
> datatypes? I can't seem to find this info.
> I need the datatype of the following:
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> Thanks
>

CATCH BLOCK ERRORS - what are the datatypes?

Hi,
I am writing my catch block to log error messages to a table. For this
purpose I am creating a log table, but I need to know what are the
datatypes? I can't seem to find this info.
I need the datatype of the following:
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
ThanksHi
These are documented in Books online see erturn type in
http://msdn2.microsoft.com/en-us/library/ms175069.aspx etc..
Numbers are int, procedure is nvarchar(126) and message is nvarchar(4000).
John
"tootsuite@.gmail.com" wrote:

> Hi,
> I am writing my catch block to log error messages to a table. For this
> purpose I am creating a log table, but I need to know what are the
> datatypes? I can't seem to find this info.
> I need the datatype of the following:
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> Thanks
>