Help needed: Scheduling of package fails

Hi there,

I have followed the steps in BOL (not too complex really) to schedule a package in SQL Server
Agent.

My package reads out Data from the SQL Server and writes some log into a file. It is encrypted
by a password and is stored to the filesystem.
Executing the pkg via dtexec works fine.

Then I've created a new job with one step via SQL Server Management Studio, added
the package and password when prompted.

I took the command line params and used them with dtexec: It woked.
I started the job manually from SQL Server Management Studio by selecting "start job"
from the context menu.

But when I try to schedule the job nothing happens. The only trace I can find is a warning in
the SQL Server Agent Error Logs:
"[162] Internal request (from SetJobNextRunDate [reason: schedule will not run again]) to deactivate schedule 2"
This is inherent when you configure a schedule to go off once.

Thanks in advance
FridtjofI have found the error:

I created more than one Schedule with the same name from within the job step schedules New button. That seemed to confuse the Server Agent. After correcting this everything worked.

Fridtjof

help needed on XML

using the BOL and looking through some of the posts on here ive managed to
come up with the following code in T-SQl
DECLARE @.TESTXML varchar(8000)
SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
<BDocument>,
<Data>
<InputReport>
<Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
reportSequenceNumber="000121" userNumber="123456">
<ProducedOn time="19:21:22" date="2004-09-30"/>
<ProcessingDate date="2004-10-01"/>
</Header>
</InputReport>
</Data>
</BDocument>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
SELECT *
FROM
OPENXML(@.hDoc, '/BDocument')
EXEC sp_xml_removedocument @.hDoc
I need help on expanding this.
1. Would i be right in assuming that to load the contants of a XML file
into the variable @.TESTXML, i would need to use something like actixex in a
DTS.
2. how can i in this case just do a select on a specific field ie
'reportnumber'
3. some of the reports i will be recieving will have the same field names in
different sections, for example
- <AccountTotals>
- <DebitEntry>
<AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
</DebitEntry>
</AccountTotal>
- <CreditEntry>
<AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
<AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
</CreditEntry>
As you can see the field 'numberOf' is used several times, how can i
differanciate between each one in each section
See below.
Best regards
Michael
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3A396C18-0271-4DD4-B2B5-C74505CE1156@.microsoft.com...
> using the BOL and looking through some of the posts on here ive managed to
> come up with the following code in T-SQl
> DECLARE @.TESTXML varchar(8000)
> SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BDocument>,
> <Data>
> <InputReport>
> <Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
> reportSequenceNumber="000121" userNumber="123456">
> <ProducedOn time="19:21:22" date="2004-09-30"/>
> <ProcessingDate date="2004-10-01"/>
> </Header>
> </InputReport>
> </Data>
> </BDocument>'
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> SELECT *
> FROM
> OPENXML(@.hDoc, '/BDocument')
> EXEC sp_xml_removedocument @.hDoc
> I need help on expanding this.
> 1. Would i be right in assuming that to load the contants of a XML file
> into the variable @.TESTXML, i would need to use something like actixex in
> a
> DTS.
Not necessarily. Any client side API that allows you to pass a parameter to
a stored proc should work. Just copy the file content over as parameter
value.

> 2. how can i in this case just do a select on a specific field ie
> 'reportnumber'
The OpenXML above results in an edge table.
To get the reportNumber for every header, you would replace your select
with:
select *
from OpenXML(@.hDoc, '/BDocument/Data/InputReport/Header') WITH (rno int
'@.reportNumber')

> 3. some of the reports i will be recieving will have the same field names
> in
> different sections, for example
> - <AccountTotals>
> - <DebitEntry>
> <AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
> </DebitEntry>
> </AccountTotal>
> - <CreditEntry>
> <AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
> <AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
> </CreditEntry>
>
> As you can see the field 'numberOf' is used several times, how can i
> differanciate between each one in each section
The following will give you only AcceptedRecords:
select * from OpenXML(@.hDoc, '//AcceptedRecords') with
(numberOf int, valueOf real, currency nvarchar(5))
The following will give you numberOf and the name of its element:
select * from OpenXML(@.hDoc, '//DebitEntry/*') with
(recname nvarchar(40) '@.mp:localname', numberOf int)
HTH
Michael