Extracting Sql: transaction

Showing posts with label transaction. Show all posts

Tuesday, March 27, 2012

Failed to get distributed transaction startet

Hi
I'm trying to get distributed transactions working between 2 machines within
2 different windows domains.
I can't get around following error:
Server: Msg 7391, Level 16, State 1, Line 1
OLE DB provider "SQLNCLI" for linked server <our server> returned message
"No transaction is active.".
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server <our server> was unable to begin a distributed transaction.
That the environment:
- server A is Win2k3 + SP 1
- server B is Win2k3 + SP 1 (within other windows domain; connection through
VPN)
- both servers running SQL 2005 + SP1
- Distributed Transaction Coordinator service is enabled on both servers
(security set to: 'No authentication required')
- The services are running with 'Log On As' Network Service
- windows firewall is disabled on both servers
- all ports are opened (bidirectional) between both domains
- @.@.servername is set correctly on both servers
That's what I try from server A:
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
but
begin tran
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
commit tran
When running this query from another ServerC within the same domain of
ServerB everything works fine. So, I guess there's some limitations across
the domains ?!
Do you have any further suggestions ?
Many thanks in advance
MichaelHi Michael
Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
John
"micpauls" wrote:
> Hi
> I'm trying to get distributed transactions working between 2 machines within
> 2 different windows domains.
> I can't get around following error:
>
> Server: Msg 7391, Level 16, State 1, Line 1
> OLE DB provider "SQLNCLI" for linked server <our server> returned message
> "No transaction is active.".
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server <our server> was unable to begin a distributed transaction.
> That the environment:
> - server A is Win2k3 + SP 1
> - server B is Win2k3 + SP 1 (within other windows domain; connection through
> VPN)
> - both servers running SQL 2005 + SP1
> - Distributed Transaction Coordinator service is enabled on both servers
> (security set to: 'No authentication required')
> - The services are running with 'Log On As' Network Service
> - windows firewall is disabled on both servers
> - all ports are opened (bidirectional) between both domains
> - @.@.servername is set correctly on both servers
>
> That's what I try from server A:
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> but
> begin tran
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> commit tran
> When running this query from another ServerC within the same domain of
> ServerB everything works fine. So, I guess there's some limitations across
> the domains ?!
> Do you have any further suggestions ?
>
> Many thanks in advance
> Michael|||Yes, I already tried, but without success. I still get the same error message.
"John Bell" wrote:
> Hi Michael
> Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> John
> "micpauls" wrote:
> > Hi
> >
> > I'm trying to get distributed transactions working between 2 machines within
> > 2 different windows domains.
> >
> > I can't get around following error:
> >
> >
> > Server: Msg 7391, Level 16, State 1, Line 1
> > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > "No transaction is active.".
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server <our server> was unable to begin a distributed transaction.
> >
> > That the environment:
> > - server A is Win2k3 + SP 1
> > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > VPN)
> > - both servers running SQL 2005 + SP1
> > - Distributed Transaction Coordinator service is enabled on both servers
> > (security set to: 'No authentication required')
> > - The services are running with 'Log On As' Network Service
> > - windows firewall is disabled on both servers
> > - all ports are opened (bidirectional) between both domains
> > - @.@.servername is set correctly on both servers
> >
> >
> > That's what I try from server A:
> >
> > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> >
> > but
> >
> > begin tran
> > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > commit tran
> >
> > When running this query from another ServerC within the same domain of
> > ServerB everything works fine. So, I guess there's some limitations across
> > the domains ?!
> >
> > Do you have any further suggestions ?
> >
> >
> > Many thanks in advance
> > Michael|||Hi
Have you checked out http://support.microsoft.com/kb/839279? I would also
try changing the service to be a domain account.
John
"micpauls" wrote:
> Yes, I already tried, but without success. I still get the same error message.
> "John Bell" wrote:
> > Hi Michael
> >
> > Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> >
> > John
> >
> > "micpauls" wrote:
> >
> > > Hi
> > >
> > > I'm trying to get distributed transactions working between 2 machines within
> > > 2 different windows domains.
> > >
> > > I can't get around following error:
> > >
> > >
> > > Server: Msg 7391, Level 16, State 1, Line 1
> > > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > > "No transaction is active.".
> > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > linked server <our server> was unable to begin a distributed transaction.
> > >
> > > That the environment:
> > > - server A is Win2k3 + SP 1
> > > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > > VPN)
> > > - both servers running SQL 2005 + SP1
> > > - Distributed Transaction Coordinator service is enabled on both servers
> > > (security set to: 'No authentication required')
> > > - The services are running with 'Log On As' Network Service
> > > - windows firewall is disabled on both servers
> > > - all ports are opened (bidirectional) between both domains
> > > - @.@.servername is set correctly on both servers
> > >
> > >
> > > That's what I try from server A:
> > >
> > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> > >
> > > but
> > >
> > > begin tran
> > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > > commit tran
> > >
> > > When running this query from another ServerC within the same domain of
> > > ServerB everything works fine. So, I guess there's some limitations across
> > > the domains ?!
> > >
> > > Do you have any further suggestions ?
> > >
> > >
> > > Many thanks in advance
> > > Michael|||Hi
windows firewall is completely disabled on both servers.
Security settings for MSDTC were already set apropriate to
"http://support.microsoft.com/kb/839279?" on both servers.
I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
find this account on serverA because this is a domain controller. How can I
do this ? Should I try changing the account for "Distributed Transaction
Coordinator" service to some other domain admin account ?
Michael
"John Bell" wrote:
> Hi
> Have you checked out http://support.microsoft.com/kb/839279? I would also
> try changing the service to be a domain account.
> John
> "micpauls" wrote:
> > Yes, I already tried, but without success. I still get the same error message.
> >
> > "John Bell" wrote:
> >
> > > Hi Michael
> > >
> > > Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> > >
> > > John
> > >
> > > "micpauls" wrote:
> > >
> > > > Hi
> > > >
> > > > I'm trying to get distributed transactions working between 2 machines within
> > > > 2 different windows domains.
> > > >
> > > > I can't get around following error:
> > > >
> > > >
> > > > Server: Msg 7391, Level 16, State 1, Line 1
> > > > OLE DB provider "SQLNCLI" for linked server <our server> returned message
> > > > "No transaction is active.".
> > > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > > linked server <our server> was unable to begin a distributed transaction.
> > > >
> > > > That the environment:
> > > > - server A is Win2k3 + SP 1
> > > > - server B is Win2k3 + SP 1 (within other windows domain; connection through
> > > > VPN)
> > > > - both servers running SQL 2005 + SP1
> > > > - Distributed Transaction Coordinator service is enabled on both servers
> > > > (security set to: 'No authentication required')
> > > > - The services are running with 'Log On As' Network Service
> > > > - windows firewall is disabled on both servers
> > > > - all ports are opened (bidirectional) between both domains
> > > > - @.@.servername is set correctly on both servers
> > > >
> > > >
> > > > That's what I try from server A:
> > > >
> > > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> > > >
> > > > but
> > > >
> > > > begin tran
> > > > UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message above
> > > > commit tran
> > > >
> > > > When running this query from another ServerC within the same domain of
> > > > ServerB everything works fine. So, I guess there's some limitations across
> > > > the domains ?!
> > > >
> > > > Do you have any further suggestions ?
> > > >
> > > >
> > > > Many thanks in advance
> > > > Michael|||micpauls wrote:
> Hi
> windows firewall is completely disabled on both servers.
> Security settings for MSDTC were already set apropriate to
> "http://support.microsoft.com/kb/839279?" on both servers.
> I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
> find this account on serverA because this is a domain controller. How can I
> do this ? Should I try changing the account for "Distributed Transaction
> Coordinator" service to some other domain admin account ?
>
You shouldn't run SQL under the NetworkService context. Create a real
domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
use that domain user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, for your help. I already run SQL services using a separate domain
(admin) account. Yesterday the problem has been resolved by adding
appropriate hostname/IP address entries to the 'hosts' file and the 'lmhosts'
file, to enable network transport services to resolve the 2 server hostnames
accross the 2 different domains. Adding linked servers by IP address wasn't
sufficient. RPC in/out options within the linked servers properties must by
enabled.
"Tracy McKibben" wrote:
> micpauls wrote:
> > Hi
> >
> > windows firewall is completely disabled on both servers.
> > Security settings for MSDTC were already set apropriate to
> > "http://support.microsoft.com/kb/839279?" on both servers.
> >
> > I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
> > find this account on serverA because this is a domain controller. How can I
> > do this ? Should I try changing the account for "Distributed Transaction
> > Coordinator" service to some other domain admin account ?
> >
> You shouldn't run SQL under the NetworkService context. Create a real
> domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
> use that domain user.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Failed to get distributed transaction startet

Friday, March 9, 2012

Fail over scenario under Transaction replication

I get 2 questions which need quick answer:
Setup replication between Srv1 DB A (Distributor and publisher) with push
publication to the subscriber Srv2 DB AB
First qestion: many table get identity value set to YES on publisher, these
values are lost in subscriber, but i need to get identical Design of table in
both DB, as if Srv 1 fail, we will use DB AB from Srv 2 to replace the one in
publisher side.
How get automatically the identity setup to YES on the subscriber site?
i see many post but no one with concrete explanation, also about sp4?
Second question: as Publisher fail, we will restore the Database from
Subscriber to Publisher.
But if i do so, the replication between the Publisher and subscriber fails
(with some counter msg). Only way is to close publication and create it again.
We want to avoid any manual intervention possible as the customer will just
restore the DB and replication should continue as normal with subscriber.
Any recommandation on the second questions, i see some stored procedure? to
reset the counter. Please more precise solutions.
Mostly if the DB is restore and if you make any modification on the
Publisher side, they are not forwarded to the subscriber.
Waiting your feedback with a major welcome as our customer will used Dual
Sql server 2k. A reply direct to my email will be appreciate.
1) Your best bet is to script out your schema and then push this to the
subscriber using the pre snapshot command. Then for your articles
defaults/table articles select the snapshot tab, and select delete all the
data in the existing table.
2) when you restore your database, recreate your publication and do a
no-sync subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martial" <Martial@.discussions.microsoft.com> wrote in message
news:62916144-CA72-49F1-AC09-4003E0D70ABC@.microsoft.com...
> I get 2 questions which need quick answer:
> Setup replication between Srv1 DB A (Distributor and publisher) with push
> publication to the subscriber Srv2 DB AB
> First qestion: many table get identity value set to YES on publisher,
these
> values are lost in subscriber, but i need to get identical Design of table
in
> both DB, as if Srv 1 fail, we will use DB AB from Srv 2 to replace the one
in
> publisher side.
> How get automatically the identity setup to YES on the subscriber site?
> i see many post but no one with concrete explanation, also about sp4?
> Second question: as Publisher fail, we will restore the Database from
> Subscriber to Publisher.
> But if i do so, the replication between the Publisher and subscriber fails
> (with some counter msg). Only way is to close publication and create it
again.
> We want to avoid any manual intervention possible as the customer will
just
> restore the DB and replication should continue as normal with subscriber.
> Any recommandation on the second questions, i see some stored procedure?
to
> reset the counter. Please more precise solutions.
> Mostly if the DB is restore and if you make any modification on the
> Publisher side, they are not forwarded to the subscriber.
> Waiting your feedback with a major welcome as our customer will used Dual
> Sql server 2k. A reply direct to my email will be appreciate.
|||Hi Paul,
I try this method, now i get my structure table kept in place, but i can not
edit on the table of the Subscriber.
Example.
DB1
"Paul Ibison" wrote:

> Have a look at queued updating subscribers. This will
> solve the identity problem and will allow failback
> without having to set up everything again.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Paul.
I try that, the structure table is identical to original one.
But it's a little worse, cause in case of fail of primary server, can not
modify or
delete or add new item to table.
I used our application and directly to SQL server, i get the msg login
failed for sa, check my Sa password on Subscriber it's fine.
I believe maybe related to MSDTC startup.
Please Help!!!
If i can modifiy on subscriber and add, i solved all my pb.
"Paul Ibison" wrote:

> Have a look at queued updating subscribers. This will
> solve the identity problem and will allow failback
> without having to set up everything again.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It looks like you have set it up as immediate updating
with queued failover. What I was suggesting is just a
queue. Have a look at this article for the source of the
error: http://support.microsoft.com/default.aspx?scid=%
2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D32 0773
However, I think that you'll have to reinitialize because
on failover if the publisher is not available, your
triggers will prevent any changes being made as they'll
error.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thank you for your feedback, i foudn this article earlier and fix my problem.
But u are right, as tested with my customer if publisher server fail, no
changes are possible on the subscriber which not answer the customer need of
failover system.
So i hope Transaction replication will do or i need to change to Merge?
Or as u mention should i will try the queue updating (subcription) and at
the publication level should i choose both (queue and immediate)?
I get an additional question about the fail over process: the subscriber SQL
DB should time to time be used to restore older database for application
purpose.
My idea is follow:
1. stop all agents on publisher sql server, which i believe should stop
replication to occurs between both SQL.
2. Backup database on subscriber (full one of course)
3. restore old DB for application purpose.
4. restore backup of point 2, once needed.
5. restart agent.
I hope this procedure will continue the replication where it stop, can you
confirm my idea?
I really appreciate your prompt feedback on my question, you are Master of
Replication in point of view.
My last question is the growing size of DB on both SQL, how to ensure i will
not get XXGb of DB.
"Paul Ibison" wrote:

> It looks like you have set it up as immediate updating
> with queued failover. What I was suggesting is just a
> queue. Have a look at this article for the source of the
> error: http://support.microsoft.com/default.aspx?scid=%
> 2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D32 0773
> However, I think that you'll have to reinitialize because
> on failover if the publisher is not available, your
> triggers will prevent any changes being made as they'll
> error.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Just queued on its own would meet your requirements.
Your backup plan looks sound. Also consider the timeframe
involved. You might need to change the transaction
retention period and history retention period and the
subscription expiration time as appropriate if
the 'downtime' lasts any length of time. In the worst
case if you don't do this, commands will be removed from
the distribution database before being propagated to the
subscriber and you'll be left having to reinitialize.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
It works like a dream, thanks for all.
Now i will make some more testing in our office to check HOW to setup
retention period, clean up transaction period, etc...
You have been my 'seifu' like we said in Asia. for master.
"Paul Ibison" wrote:

> Just queued on its own would meet your requirements.
> Your backup plan looks sound. Also consider the timeframe
> involved. You might need to change the transaction
> retention period and history retention period and the
> subscription expiration time as appropriate if
> the 'downtime' lasts any length of time. In the worst
> case if you don't do this, commands will be removed from
> the distribution database before being propagated to the
> subscriber and you'll be left having to reinitialize.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Friday, February 24, 2012

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?
Check out www.lumigent.com.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?Check out www.lumigent.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

extraction of data from transaction log of SQL Server 2000

I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?Check out www.lumigent.com.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"sandyinfy" <sandyinfy@.discussions.microsoft.com> wrote in message
news:4A28AAAF-D988-4ED1-9BB3-A70076CDCE79@.microsoft.com...
I would like to know if there is any method for extracting data from
transaction log of SQL Server 2000 into a flat file. Is it possible? If so
how?

Friday, February 17, 2012

Extracting

Dear experts,

I need to spool the results of the following extraction from PL/SQL

For each invoice no in TRANSACTION table, loop:
Txn no, customer code, date etc etc, tab-delimited
Then for each invoice no, get all invoice lines in TPRODLINE table, loop:
Txn line, product, qty, sale price etc etc, tab-delimited
end invoice lines loop
end invoice no loop

TRANSACTION table is joined to the TPRODLINE table by the transaction_id in both tables.

My questions:

1) should I do 2 cursors in the loops. 1 cursor to read the required invoice lines and pass the transaction no to the 2nd cursor to extract the transaction header infor. Then write/append this to the text file. then go back to the 2 loops? Will that work?

2) I also need help to spool this to a text file. Will UTL_FILE be able to help me? If so, anything I need to be careful about?

Any advice greatly appreciated!

Cheers,

Pete

Cheers,

Pete1) That depends on the size of table and you indexes.
I would create on SQL statement that joins all table and give me the result that I need. Look into the explain plan before executing.

2) Here is an example for using UTL_FILE

DECLARE
fHandle UTL_FILE.FILE_TYPE;
vTextIn varchar2(25);
vTextOut varchar2(25);
BEGIN
-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','w');

IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File write open');
ELSE
DBMS_OUTPUT.PUT_LINE('File write not open');
END IF;

vTextIn := 'Hello World';
vTextOut := 'World Hello';

-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR
UTL_FILE.PUT_LINE(fHandle,vTextIn);

DBMS_OUTPUT.PUT_LINE('Value write: '||vTextIn);

-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);

-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','r');

IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;

-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or READ_ERROR
-- NO_DATA_FOUND or VALUE_ERROR
UTL_FILE.GET_LINE(fHandle,vTextOut);

DBMS_OUTPUT.PUT_LINE('Value read: '||vTextOut);

-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);

DBMS_OUTPUT.PUT_LINE('Successful Completion');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');

WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');

WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20104,'Read Error');

WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20105,'Write Error');

WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');

WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20107,'No Data Found');

WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR(-20108,'Value Error');

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20109,'Unknown UTL_FILE Error');
END;
/

3) Set the option UTL_FILE_DIR to access other files on your OS
If you want to use more directories, separate them with a ;

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,

Thank you so much for your help! An addition to the Oracle manual!

I am still a bit stuck with the SQL though!

Cheers,

Pete|||Hello,

when I understand your description you have two tables
One TRANSACTION table and one TPRODLINE table.
The reference is transaction_id ...

So just join with that transaction id

SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id

Here is the cursor process (a little bit old style :) )

DECLARE
CURSOR cuProcess IS
SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id

rProcess cuProcess%ROWTYPE; -- record structure
cOut VARCHAR2(2000);

BEGIN
OPEN cuProcess;
FETCH cuProcess INTO rProcess;

-- Open your file !!!

<<BeginLoop>>
WHILE cuProcess%FOUND LOOP

FETCH cuProcess INTO rProcess;

-- spool your datas
cOut := rProcess.txnno || CHR(9) || .............;

END LOOP BeginLoop;

CLOSE cuProcess;

-- close your file

EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;
END;

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,

The table join is simple.

But the required file consists of at least 3 lines for each invoice which makes it difficult.

Line 1:txn no, customer, address, date etc ...
Line 2 to nth:txn lineno, product code, unit price, qty, linetot
line nth+1:invoice totamt, standard greeting message
for invoice no 12345, customer = Burgerking with 2 invoice lines for products Whopper and fries, I need to product a text file of

Header,12345,burgerking,123 high street, 14-Apr-03(carriage return)
Lines,1,whopper,2.3,1,2.3(carriage return)
lines,2,fries,1.4,2,2.8(carriage return)
footer,5.1,thank you for buying from us(carriage return)
... (next invoice header)
header,12346
....

Once all these have been extracted successfully, I will need to update a date field in the transaction table to mark the invoice as "printed/extracted" ...

That's why I was thinking of using 2 cursors!

Any advice greatly appreciated (coz not many hair left on my head)!

Cheers,

Pete|||Hello,

that is something we call in germany "Gruppenwechsel" or group changing :)

You can use two cursor but you dont have to do that.

Do it in this way

DECLARE

cCompareField VARCHAR2(200);

BEGIN
cCompareField := "";

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

-- Open your file !!!

<<BeginLoop>>
WHILE cuProcess%FOUND LOOP

IF cCompareField <> rProcess.taxno THEN
... do your header thing
cCompareField := rProcess.taxno;
END IF;

.. do your normal invoice stuff

FETCH cuProcess INTO rProcess;

-- spool your datas
cOut := rProcess.txnno || CHR(9) || .............;

END LOOP BeginLoop;

CLOSE cuProcess;

Hope this is clear enough otherwise please let me know ?!?!

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi

Thanks again for your help. Just to make sure I understand your coding:

1) the 1 cursor that you have used will select rows (including txn_id) from the transaction lines table.

2) first loop select records from transaction table based on txn_id in the cursor. then assign it to variable cOut and set cCompare to the txn_id.

3) then select transaction line data from transaction line table and append it to cOUT.

4) and repeat from (2) until cursor NOT FOUND.

5) then use cOUT with UTL_FILE ...

Is that correct? Thanks!

Happy easter!

Pete|||Hello,

thats completly correct ... if you need more groups like the id,
you can define more CompareField variable like it is shown in the example.
This is the classical way to program "group changing" ...

Best regards

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,

You are a life saver!!

Thank you so much!!!

cheers,

Pete|||Hi

This time round I have got some question on UTL_FILE.

I used your eg. and added a loop for generating a count and writing it to a text file on my local drive using UTL.

Run the block and it just say completed successfully. But no file is generated.

I do not have Oracle client installed on my local machine. I also know the UTL_FILE_DIR parameter is set to null.

Is there anything I have not done correctly?

Thanks for any advice!

Cheers,

Pete|||Hello ...

can you please post the code ?

Best regards

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi,

here is the code:

~~~~~~~~~~~~~~
DECLARE
fHandle UTL_FILE.FILE_TYPE;
cOut varchar2(25);
cCount NUMBER :=1;
BEGIN
--
-- Open file, Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION here
--
fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');
dbms_output.put_line('File opened');
--
-- Append/write cOut into file. Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR here
--
WHILE cCount < 11 LOOP
cOut := 'The Count is ' || cCount;
UTL_FILE.PUT_LINE(fHandle,cOut);
dbms_output.put_line('The Count is ' || cCount);
cCount := cCount + 1;
END LOOP;
--
-- Close file. Might get INVALID_FILEHANDLE or WRITE_ERROR
--
UTL_FILE.FCLOSE(fHandle);
dbms_output.put_line('File opened');
--
-- Update PO_ERR to indicate successful extraction
--
-- INSERT INTO PO_ERR VALUES(sysdate,'COmpleted');
-- COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20100 Invalid path');
-- COMMIT;
WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20101 Invalid Mode');
-- COMMIT;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20102 Invalid Filehandle');
-- COMMIT;
WHEN UTL_FILE.INVALID_OPERATION THEN
--
-- May signal a file locked by the OS'
--
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20103 Invalid Operation');
-- COMMIT;
WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20104 Read Error');
-- COMMIT;
WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20105 Write Error');
-- COMMIT;
WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20106 Internal Error');
-- COMMIT;
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20107 No Data Found');
-- COMMIT;
WHEN VALUE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20108 Value Error');
-- COMMIT;
WHEN OTHERS THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20109 Unknown UTL_FILE Error');
-- COMMIT;
END;
/|||Hello,

fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');

1) Remeber, that the path v:\Sweden\Post must be accessable on your server (not on your client).

2) Enter the path in your init.ora file under UTL_FILE_DIR

3) I did not test this ... but could it be that you must use the open mode
'w' for write not 'a' for append ??? Cause the file did not exist ?

4) Check that Post blank Office is a valid path ... sometimes you must reference v:\Sweden\Post~1 or something like that ...

5) For the first time ... I would use only filenames and directories that are not longer 8 characters. If this work, I try the orginal names ...

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,

I am getting some resistance from our DBA to set the parameter in the init.ora bec they will have to reboot the server and that has to go thru a lot of bureacracy red tape. i.e. I cant really test my invoice extraction scripts.

Thus, I was wondering if you know of any other tools that I can use easily.

Cheers,

Pete|||Hello,

they have to boot the server ? I though, that is is enough to start the instance and perhaps the listener ? anyway ...

Anyway, what you can do is to leave the directory blank. Oracle will put this into the oracle_home directory ...

What do you mean by ... any other tool that you can use ?
UTL_FILE is the right way to do this and I do not know any other tecniques to write in a file on the server.
You can create your own DLL or SO to program it by yourself, but your DBA has to do the same thing than when chaning the init.ora

My suggestion is ... try your example with leaving the dir blank and
Change the open mode into "w"
search for the file on the server

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,

I have changed the directory to blank and the mode to 'W'. There isnt any file created on the server.

Is there something wrong with my block? I have put some dbms_output.Put_line in the block but all I got was "PL/SQL completed successfully" even thought serveroutput is on. Thus, I think the block was not entirely executed at all.

I am running this on the SQL*Plus (via Citrix) on the server's Bin directory. I do not have oracle installed on my location machine or my session of citrix. Does this have any impact?

Greatly appreciate any advice!

Cheers,

Pei Siong|||Hello,

I dont thing that this is a problem of your client configuration or your citrix.

Can you please insert a put_line in every exception block and
insert a WHEN OTHERS exception block ?

The step through the code with a debugger (f.e. AlligatorSQL) or
insert a step variable for every line and print this out in the exception block

and

insert in every exception block
dbms_output.put_line('Error=' || SQLERRM || ' - Errocode = ' || SQLCODE);

example

DECLARE
nStep PLS_INTEGER := 0;
cVar VARCHAR2(200);
BEGIN
<<Start>>
nStep := 10;
cVar := 'Hello';

.
.
.
nStep := 100;
cVar := 'Hello back';

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Last executed Step = ' || nStep);
END

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,

I got the invalid path error when I leave the location blank and have a filename shorter than 8 char and open_mode = 'W'.

Looks like I really do need to get the UTL_FILE_DIR parameter set to a specific location?

Cheers,

Pei Siong|||Hello,

try to define the path with "../" for unix and "..\" for windows
or only ".."

my last idea :(

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,

I have got the parameter changed and my script works brilliantly! however, I have to abandon the UTL_FILE method as my DBA team says it is not right to spool any text file to the Oracle server. so they have turned me down.

what I have done is to insert cOut (the variable that stores the text) into a temp table and then do a normal spool from the temp table.

This will have to do for the time being. But still, I have learnt many things from you... Thank you!!

Cheers,

Pei Siong|||Hello,

you are welcome.
By the way ... I have never heard, that you do not have to spool files to an oracle server. We do this in ever project ... but anyway :)

I would like to mention one thing ...

If you spool text in a temp table directly there are two inportant
things to know

1) Your application performance will go down.
2) You only see your spool text, if the application commits the transaction ... means ... if you have an exception and your normal work
will rollbacked by the application, you do not see your spool files.

To avoid this problems do the following

1) write a procedure that spools the text in an Oracle pipe.
2) write a procedure that pools against the pipe and if the pipe have datas, insert the datas in your temp table.

Your performance will not go down and you see every text that you want to spool.

But for the first step your solution will work ...
I only want to give you this information for further project.

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Tuesday, March 27, 2012

Failed to get distributed transaction startet

Failed to get distributed transaction startet

Friday, March 9, 2012

Fail over scenario under Transaction replication

Friday, February 24, 2012

extraction of data from transaction log of SQL Server 2000

extraction of data from transaction log of SQL Server 2000

extraction of data from transaction log of SQL Server 2000

Friday, February 17, 2012

Extracting

Extracting Sql

Blog Archive

About Me