alias

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@.payment_or_bill nvarchar(2),

@.spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.fieldname varchar(10) DECLARE @.stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @.stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @.fieldname

WHILE @.@.Fetch_Status = 0

BEGIN

SET @.stmt = @.stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @.fieldname + ''''

SET @.stmt = @.stmt + ') As ' + @.fieldname

FETCH Next

FROM Fields

INTO @.fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @.stmt = @.stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @.stmt = @.stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @.stmt = @.stmt + ' From bill_ as bill1'

exec(@.stmt)

select (@.stmt)

END

Thats right, you won′t see them at the same query level, by referencing an alias you will probably have to do something like this here:

Select SomeAlias
FROM
(
Select 'Something' + 'To' + 'SomethingElse' AS SomeAlias
) Subquery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sorry i did not get the point|||You can′t select from an alias from the same level of Select. You will either have to repeat the expression of use a Subquery where the Expression is "materialized" as a column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Will the next sql version allow referencing an alias in the same select level ?

alias

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@.payment_or_bill nvarchar(2),

@.spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.fieldname varchar(10) DECLARE @.stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @.stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @.fieldname

WHILE @.@.Fetch_Status = 0

BEGIN

SET @.stmt = @.stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @.fieldname + ''''

SET @.stmt = @.stmt + ') As ' + @.fieldname

FETCH Next

FROM Fields

INTO @.fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @.stmt = @.stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @.stmt = @.stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @.stmt = @.stmt + ' From bill_ as bill1'

exec(@.stmt)

select (@.stmt)

END

Thats right, you won′t see them at the same query level, by referencing an alias you will probably have to do something like this here:

Select SomeAlias
FROM
(
Select 'Something' + 'To' + 'SomethingElse' AS SomeAlias
) Subquery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sorry i did not get the point|||You can′t select from an alias from the same level of Select. You will either have to repeat the expression of use a Subquery where the Expression is "materialized" as a column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Will the next sql version allow referencing an alias in the same select level ?

alias

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@.payment_or_bill nvarchar(2),

@.spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.fieldname varchar(10) DECLARE @.stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @.stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @.fieldname

WHILE @.@.Fetch_Status = 0

BEGIN

SET @.stmt = @.stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @.fieldname + ''''

SET @.stmt = @.stmt + ') As ' + @.fieldname

FETCH Next

FROM Fields

INTO @.fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @.stmt = @.stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @.stmt = @.stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @.stmt = @.stmt + ' From bill_ as bill1'

exec(@.stmt)

select (@.stmt)

END

Thats right, you won′t see them at the same query level, by referencing an alias you will probably have to do something like this here:

Select SomeAlias
FROM
(
Select 'Something' + 'To' + 'SomethingElse' AS SomeAlias
) Subquery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sorry i did not get the point|||You can′t select from an alias from the same level of Select. You will either have to repeat the expression of use a Subquery where the Expression is "materialized" as a column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Will the next sql version allow referencing an alias in the same select level ?

sql

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running on
a new server and have the AGENT running under a local server username - not a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps?
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do?
The SQLServerAgent runs under a domain account for us, that has writes to all
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:

> I'm asking this question for a client of mine. They have SQL 2005 running on
> a new server and have the AGENT running under a local server username - not a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not have
> access to the share. How do you get more un's listed for the agent steps?
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do?

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running on
a new server and have the AGENT running under a local server username - not a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps'
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do'The SQLServerAgent runs under a domain account for us, that has writes to all
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:
> I'm asking this question for a client of mine. They have SQL 2005 running on
> a new server and have the AGENT running under a local server username - not a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not have
> access to the share. How do you get more un's listed for the agent steps'
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do'

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running o
n
a new server and have the AGENT running under a local server username - not
a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps'
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do'The SQLServerAgent runs under a domain account for us, that has writes to al
l
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:

> I'm asking this question for a client of mine. They have SQL 2005 running
on
> a new server and have the AGENT running under a local server username - no
t a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not ha
ve
> access to the share. How do you get more un's listed for the agent steps?
?
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do'

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0, 0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSER VER2005_DEV01_0_Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
Olav
Specify an output file for the job step and see if you get any root error messages there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job fails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0, 0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSER VER2005_DEV01_0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job failed?
> Olav
>
|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed because
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
The ProcessInput method on component "OLE DB Destination" (2) 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.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>
|||I'm no DTS person, but it looks line you try to execute some code that tries to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you post to the DTS group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I have created a new SSIS package using the Import / Export Wizard and theese are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: The ProcessInput method
> on component "OLE DB Destination" (2) 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.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs are not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,0,DEV01,CDW_LION_SQLSERVER2005
_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,1,DEV01,CDW_LION_SQLSERVER2005
_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_
Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
OlavSpecify an output file for the job step and see if you get any root error me
ssages there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...[vb
col=seagreen]
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job f
ails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Messa
ge,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,
Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,0,DEV01,CDW_LION_SQLSERVER20
05_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,1,DEV01,CDW_LION_SQLSERVER20
05_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_
0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step fai
led.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job f
ailed?
> Olav
>[/vbcol]|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed beca
use
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeli
ne:
The ProcessInput method on component "OLE DB Destination" (2) 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.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeli
ne:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE
DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE
DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>|||I'm no DTS person, but it looks line you try to execute some code that tries
to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you
post to the DTS group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...[vbc
ol=seagreen]
>I have created a new SSIS package using the Import / Export Wizard and thee
se are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hres
ult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occu
rred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure o
n error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipe
line: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. T
he identified component
> returned an error from the ProcessInput method. The error is specific to t
he component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipe
line: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs ar
e not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
OlavSpecify an output file for the job step and see if you get any root error messages there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job fails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job failed?
> Olav
>|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed because
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
The ProcessInput method on component "OLE DB Destination" (2) 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.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job
>> fails when I try to run it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step
>> Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
>> Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
>> last step to run was step 1
>> (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step
>> failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job
>> failed?
>> Olav
>|||I'm no DTS person, but it looks line you try to execute some code that tries to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you post to the DTS group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I have created a new SSIS package using the Import / Export Wizard and theese are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: The ProcessInput method
> on component "OLE DB Destination" (2) 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.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs are not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>> Specify an output file for the job step and see if you get any root error messages there.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job fails when I try to run
>> it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
>> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
>> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job failed?
>> Olav
>>
>

After ftp transfer, database allways corrupted

Hi,
we use ftp to transfer database from one server to another
(detach, ftp copy, attach). Database is pretty large
(~300GB), and when we reattach database files to
destination server, database comes up with errors
(detected by dbcc checkalloc, there is nothing in error
log). We're unable to repair the database, and even if we
use dts to recreate corrupted tables, other tables come up
corrupted...
We use ftp because of the speed... Does anybody know why
is this happening? Any other idea for data transfer?
Thanks,
OJ
How often are you doing this? Have you considered doing a backup at one
site, restoring at the other, and then using log shipping/replication to
keep them in sync perpetually?
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ
|||Have you run CHECKDB on the original db to make sure it isn't corrupted to
begin with?
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ
|||No, because we build this database on the staging server
monthly and after transfer to destination server set it to
read only mode...
Thanks
>--Original Message--
>How often are you doing this? Have you considered doing
a backup at one
>site, restoring at the other, and then using log
shipping/replication to
>keep them in sync perpetually?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
another[vbcol=seagreen]
we[vbcol=seagreen]
up
>
>.
>
|||Yes we have.We have every day daily maintenance job which
checks database integrity...
>--Original Message--
>Have you run CHECKDB on the original db to make sure it
isn't corrupted to
>begin with?
>--
>Andrew J. Kelly SQL MVP
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
another[vbcol=seagreen]
we[vbcol=seagreen]
up
>
>.
>
|||Is there any chance you are transferring the files using ASCII instead of
binary?
"OJ" wrote:

> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ
>
|||I would suspect the FTP process it self then. I have had issues in the past
with large FTP files getting corrupted especially if it is not sent in
binary format. You might want to look at using a product such as SQL
LiteSpeed to provide you with a compressed backup that you can then FTP.
Your likely to get a file that is up to 70 or 80% smaller than the detached
one you are using now.
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:030201c495e6$517ab730$a401280a@.phx.gbl...[vbcol=seagreen]
> Yes we have.We have every day daily maintenance job which
> checks database integrity...
> isn't corrupted to
> message
> another
> we
> up

After ftp transfer, database allways corrupted

Hi,
we use ftp to transfer database from one server to another
(detach, ftp copy, attach). Database is pretty large
(~300GB), and when we reattach database files to
destination server, database comes up with errors
(detected by dbcc checkalloc, there is nothing in error
log). We're unable to repair the database, and even if we
use dts to recreate corrupted tables, other tables come up
corrupted...
We use ftp because of the speed... Does anybody know why
is this happening? Any other idea for data transfer?
Thanks,
OJHow often are you doing this? Have you considered doing a backup at one
site, restoring at the other, and then using log shipping/replication to
keep them in sync perpetually?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ|||Have you run CHECKDB on the original db to make sure it isn't corrupted to
begin with?
--
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ|||No, because we build this database on the staging server
monthly and after transfer to destination server set it to
read only mode...
Thanks
>--Original Message--
>How often are you doing this? Have you considered doing
a backup at one
>site, restoring at the other, and then using log
shipping/replication to
>keep them in sync perpetually?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
>> Hi,
>> we use ftp to transfer database from one server to
another
>> (detach, ftp copy, attach). Database is pretty large
>> (~300GB), and when we reattach database files to
>> destination server, database comes up with errors
>> (detected by dbcc checkalloc, there is nothing in error
>> log). We're unable to repair the database, and even if
we
>> use dts to recreate corrupted tables, other tables come
up
>> corrupted...
>> We use ftp because of the speed... Does anybody know why
>> is this happening? Any other idea for data transfer?
>> Thanks,
>> OJ
>
>.
>|||Yes we have.We have every day daily maintenance job which
checks database integrity...
>--Original Message--
>Have you run CHECKDB on the original db to make sure it
isn't corrupted to
>begin with?
>--
>Andrew J. Kelly SQL MVP
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
>> Hi,
>> we use ftp to transfer database from one server to
another
>> (detach, ftp copy, attach). Database is pretty large
>> (~300GB), and when we reattach database files to
>> destination server, database comes up with errors
>> (detected by dbcc checkalloc, there is nothing in error
>> log). We're unable to repair the database, and even if
we
>> use dts to recreate corrupted tables, other tables come
up
>> corrupted...
>> We use ftp because of the speed... Does anybody know why
>> is this happening? Any other idea for data transfer?
>> Thanks,
>> OJ
>
>.
>|||I would suspect the FTP process it self then. I have had issues in the past
with large FTP files getting corrupted especially if it is not sent in
binary format. You might want to look at using a product such as SQL
LiteSpeed to provide you with a compressed backup that you can then FTP.
Your likely to get a file that is up to 70 or 80% smaller than the detached
one you are using now.
--
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:030201c495e6$517ab730$a401280a@.phx.gbl...
> Yes we have.We have every day daily maintenance job which
> checks database integrity...
> >--Original Message--
> >Have you run CHECKDB on the original db to make sure it
> isn't corrupted to
> >begin with?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"OJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> >> Hi,
> >> we use ftp to transfer database from one server to
> another
> >> (detach, ftp copy, attach). Database is pretty large
> >> (~300GB), and when we reattach database files to
> >> destination server, database comes up with errors
> >> (detected by dbcc checkalloc, there is nothing in error
> >> log). We're unable to repair the database, and even if
> we
> >> use dts to recreate corrupted tables, other tables come
> up
> >> corrupted...
> >> We use ftp because of the speed... Does anybody know why
> >> is this happening? Any other idea for data transfer?
> >> Thanks,
> >> OJ
> >
> >
> >.
> >

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
Mike
Set the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>
|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:

>I have a database that i make a copy of everynight. I delete all the table
s
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:

> I have a database that i make a copy of everynight. I delete all the tabl
es
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore m
y
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreat
e
> the whole database by running the dts package (this only takes 10 minutes)
.
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>

advice on transaction log

I have a database that i make a copy of everynight. I delete all the tables
in the destination database and recreate it, ensuring that the destination
database reflects all the new tables and columns from the original. This
destination database is used for reporting only, so the last step i do in
the copy is set the destination database to readonly. The copy is done
using a dts package that is scheduled to run at a given time everynight.
Since i delete all the tables everynight, i don't do backups. Therefore my
transaction log continues to grow. I don't ever need any information from
the transaction log, since if the database fails then i would just recreate
the whole database by running the dts package (this only takes 10 minutes).
What is the best way to keep the transction log to a minimum size.
Thank you
MikeSet the recovery mode to Simple on the readonly database. That will
cause any transactions completed to be automatically truncated from
the log.
Allen White
On Wed, 26 Jan 2005 10:34:28 -0500, "Mike Read"
<mike.read.spam.no@.allwaresolutions.com> wrote:
>I have a database that i make a copy of everynight. I delete all the tables
>in the destination database and recreate it, ensuring that the destination
>database reflects all the new tables and columns from the original. This
>destination database is used for reporting only, so the last step i do in
>the copy is set the destination database to readonly. The copy is done
>using a dts package that is scheduled to run at a given time everynight.
>Since i delete all the tables everynight, i don't do backups. Therefore my
>transaction log continues to grow. I don't ever need any information from
>the transaction log, since if the database fails then i would just recreate
>the whole database by running the dts package (this only takes 10 minutes).
>What is the best way to keep the transction log to a minimum size.
>Thank you
>Mike
>|||Mike
The answer to your question is that you need to set your database to simple
recovery mode.
I would however suggest you look into using log shipping or snapshot
replication to maintain your read only database. These are much better
methods to do what you want.
Hope this helps
John
"Mike Read" wrote:
> I have a database that i make a copy of everynight. I delete all the tables
> in the destination database and recreate it, ensuring that the destination
> database reflects all the new tables and columns from the original. This
> destination database is used for reporting only, so the last step i do in
> the copy is set the destination database to readonly. The copy is done
> using a dts package that is scheduled to run at a given time everynight.
> Since i delete all the tables everynight, i don't do backups. Therefore my
> transaction log continues to grow. I don't ever need any information from
> the transaction log, since if the database fails then i would just recreate
> the whole database by running the dts package (this only takes 10 minutes).
> What is the best way to keep the transction log to a minimum size.
> Thank you
> Mike
>
>