"Oracle Not Available" error from SQL

We have a DTS package utilizing an ODBC connection to and Oracle database to run a few updates. I can TNSPING the Oracle instance but get the error Ora-01034 ORACLE not available when trying to run the package.
Any ideas?DTS packages can run as scheduled tasks, on the server itself, or from a workstation. They may or may not have the same NT Credentials. My guess would be that you're running the task from two different machines (a workstation and a server), where one has Oracle connectivity and the other does not.

-PatP|||Pat, Thanks for the reply.

This is all running from our SQL server and is scheduled from there. When i am looking at the DTS package, specifically the connection i can not even close it without getting the error.|||Have you rebooted NT since you installed the Oracle drivers on your server?

-PatP|||We have been utilizing the Microsoft ODBC for Oracle drivers.|||Have you tried running xp_cmdshell stored procedure from sql server with something like 'tnsping oracle_server'?

exec master..xp_cmdshell 'tnsping oracle_server_name'

what is the result? it could help to determine the source of problem

btw we have been using microsoft ole db provider for oracle for two years in dts packages and it's absolutely problemless. but only for selects. mojza

"GUID should contain 32 digits" when saving brand new Maintenance

Someone help me please. I'm confused.
I've got a brand new Maintenance Plan which reorganizes indexes, clears out
the MP history and updates DB stats. It refused to save with a schedule of
once a week on Sunday at 1:00 a.m., but did save without a schedule (on
demand).
I have reopened the Maintenance Plan and tried to create a schedule. Every
time I try to close out and save (X on the upper right), it gives me the
Error: "GUID should contain 32 digits with 4 dashes
(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)" with an OK button and no other
information.
What is going on? Does anyone know? Help would be appreciatedd since we're
right on top of our official 2000-to-2005 server switch over.
Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Check this link out, hopefully it should resolve your issue.
http://www.kbalertz.com/Feedback_922651.aspx
Bob
"Catadmin" wrote:

> Someone help me please. I'm confused.
> I've got a brand new Maintenance Plan which reorganizes indexes, clears ou
t
> the MP history and updates DB stats. It refused to save with a schedule o
f
> once a week on Sunday at 1:00 a.m., but did save without a schedule (on
> demand).
> I have reopened the Maintenance Plan and tried to create a schedule. Ever
y
> time I try to close out and save (X on the upper right), it gives me the
> Error: "GUID should contain 32 digits with 4 dashes
> (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)" with an OK button and no other
> information.
> What is going on? Does anyone know? Help would be appreciatedd since we'
re
> right on top of our official 2000-to-2005 server switch over.
> Thanks,
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
'
> @.=)|||Just an FYI. I tried creating a new job that contained the above 3 steps an
d
the Notify Operator (set with a fail precendence from the other three tasks)
for another user db. I hit save, then the X up in the right hand corner and
got the following error message:
"Apply to target server failed for job 'MyJobName' "
I did get this error message on the other job and wonder if maybe there's a
connection between this and the GUID message?
Thanks,
Catadmin
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Catadmin" wrote:

> Someone help me please. I'm confused.
> I've got a brand new Maintenance Plan which reorganizes indexes, clears ou
t
> the MP history and updates DB stats. It refused to save with a schedule o
f
> once a week on Sunday at 1:00 a.m., but did save without a schedule (on
> demand).
> I have reopened the Maintenance Plan and tried to create a schedule. Ever
y
> time I try to close out and save (X on the upper right), it gives me the
> Error: "GUID should contain 32 digits with 4 dashes
> (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)" with an OK button and no other
> information.
> What is going on? Does anyone know? Help would be appreciatedd since we'
re
> right on top of our official 2000-to-2005 server switch over.
> Thanks,
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
'
> @.=)

"expanding" updates

Hi,
i need to "expand" a raw datatable like

1, 'Harry', 'London'
2, 'Peter', null
3, null, 'New York'

into this format

1, 'Harry', 'London'
2, 'Peter', 'London'
3, 'Peter', 'New York'

so, for several columns, null values should be replaced with the values of the previous (non-null) row. the raw data is bulk-inserted and cannot be modified before.

is there anything other (faster!) than using a cursor ?
i like to have on single update command to do the job.does each row in the table have a unique sequential key?|||Originally posted by Paul Young
does each row in the table have a unique sequential key?

yes, the first column is an identity|||why not join the table to its self based on the identity column?

select *
from yourbcptable ybt1
join yourbcptable ybt2 on ybt1.IdentityColumn - 1 = ybt2.IdentityColumn
where ybt1.IdentityColumn > 1

This assumes your identity seed = 1|||yes, the identity seed is 1, so your select works,

but what about multiple null rows in order like

1, 'Peter', 'london'
2, 'Markus', null
3, 'Mary', null

for row 3 the row (id-1) will be null as well?|||either a row at a time approach or run the update till you no longer have nulls or wrap everything up in a stored procedure.

probably should have added more to the where caluse

where ybt1.IdentityColumn > 1
and ytb1.col1 is null or ybt1.col2 is null

BTW, I would NOT use a CURSOR as these tend to be resource intensive.|||i got closer. the following update will nearly do the job, but the update order needs to be by the identity column. is there a way to set the order for updates ?

update mytable set
@.Column1 = Column1 = isnull( Column1, @.Column1 ),
@.Column2 = Column2 = isnull( Column2, @.Column2 )

this will set column1 to column1 (keep the value) when it is not null or will set column1=@.column1, which holds then "not null"-value of the rows before. works fine - but only when processed in correct order !|||No guaranties on the order. SQL Server will process in the most efficient manner.

You might try setting a clustered index on the key, but I would check the results very closely.|||--create table T (a int identity, N varchar(10), C varchar(10))
/*
delete from T
insert into T (n,c) select 'Harry','London'
insert into T (n,c) select 'Peter',null
insert into T (n,c) select null,'New York'
*/
select * from T
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1
select * from T|||Originally posted by kukuk
--create table T (a int identity, N varchar(10), C varchar(10))
/*
delete from T
insert into T (n,c) select 'Harry','London'
insert into T (n,c) select 'Peter',null
insert into T (n,c) select null,'New York'
*/
select * from T
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1
select * from T

correct - that's what we discussed before,
but what about multiple null rows in order like

1, 'Peter', 'london'
2, 'Markus', null
3, 'Mary', null

for row 3 the row (id-1) will be null as well?
there might be many null-rows after one data-row.|||:)

while exists (select * from T where a>1 and (C is null or N is null) )
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1

(will work only if the first row does not contain nulls)

or

update x set
N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
from T as x

(expensive one)|||Originally posted by kukuk
:)

while exists (select * from T where a>1 and (C is null or N is null) )
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1

(will work only if the first row does not contain nulls)

or

update x set
N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
from T as x

(expensive one)

the first one seems to be the one ...
i'll give it a try and come back later|||hi kukuk,

i think i got it:

declare @.A int, @.B varchar(20), @.C varchar(20)

select top 1 @.A = A from Tbl order by ID
while @.@.RowCount>0 begin
update Tbl
set @.B= B= isnull( B, @.B ),
@.C= C= isnull( C, @.C ),
@.A=A+1
from Tbl
where A=@.A
end

its a mix of your first select and my variable-version. your select works fine too, but it is much more expensive than the one above.

thanks to all for your time
markus|||Yes but the number of iterations will be equal to nuimber of rows in the table.|||Originally posted by kukuk
Yes but the number of iterations will be equal to nuimber of rows in the table.

true, but i don't care as long as it runs much faster.