Toad World® Forums

"Run" script doesn't always work as generated?


#1

Hey all,

I’ve created a stored procedure to push data from one of our SQL Server 2005
tables to Oracle 10.1 via a MSDAORA Linked Server. The Linked Server works
as tested in the Editor, but INSERTs are silently failing when the sp is run
from the right-click menu on the Object Explorer.

First, here’s the sp (or something very close to it):

CREATE PROCEDURE [dbo].[apex_push] AS

PRINT ‘Truncating table…’;

EXEC (‘BEGIN ORASCHEMA.TRUNCATE_MERGE_TABLE; END;’) AT APEX;

PRINT ‘Inserting rows into MERGE table…’;

INSERT INTO OpenQuery(APEX,‘SELECT compname, login, domain, computermodel,
audit_date FROM ORASCHEMA.MERGE_TABLE’) SELECT compname, login, domain,
computermodel, audit_date FROM DifferentSSDB.dbo.Workstation;

PRINT ‘Done.’
GO

When I right-click on this sp from the Object Explorer and choose “Run”, it
generates this script:

DECLARE @return_value int;

EXEC @return_value = MYSSDB.dbo.apex_push;

SELECT @return_value as N’@Return Value’;

GO

It looks fine, but the sp does not work. The EXEC statement completes
successfully, but while the INSERT appears to run (given the execution
time), there are no rows INSERTed into the Oracle table. I checked this
using Toad for Oracle. However, if I alter the generated script to simply:

EXEC MYSSDB.dbo.apex_push;

…it works fine! Huh???

I suspect I’m missing some SQL Server trick here, but I’m then wondering how
to determine if I can use the “Run” option from the right-click menu or not.

Thoughts anyone?

Thanks!
Rich


#2

Hi Rich,

You’re saying that INSERTs don’t work. Does it mean you still see
PRINT’s messages? What I’m trying to ask is maybe the stored
procedure doesn’t work as a whole when attempting to run in a generated
batch?

Another question: What is your current AutoCommit setting (see lower left corner
of Toad window)? Also, can you please try to run any stored procedure (that
doesn’t have a link to any linked server) from the right click on the LHS
of Object Explorer and see if its behavior is the same as the one you’ve
reported about?

Thanks,

Igor.


#3

Hey Igor,

You’re saying that INSERTs don’t work. Does it mean you still see PRINT’s
messages? What I’m trying to ask is maybe the stored procedure doesn’t work
as a whole when attempting to run in a generated batch?

Everything else works, except the INSERT is acting like it’s not being
committed in Oracle. Here’s the output in the Messages window when the sp
is executed from the “Run” right-click menu:

Statement:02/25/2011 14:05:49 0:00:00.093: Executed Successfully
1: – Execute Stored Proc MYSSDB.dbo.apex_push

USE [MYSSDB];
Connection:02/25/2011 14:05:53: apex_push:29: Truncating table…
Connection:02/25/2011 14:05:53: apex_push:33: Inserting rows into MERGE
table…
Connection:02/25/2011 14:05:53: apex_push:42: Done.
Statement:02/25/2011 14:05:49 0:00:04.546: 601 rows affected
6: DECLARE @return_value int;

EXEC @return_value = MYSSDB.dbo.apex_push;

SELECT @return_value as N’@Return Value’;
@Return Value

0
Script Execution:02/25/2011 14:05:53: Execution finished in 0:00:04.687

Note the “601 rows affected” line? The Oracle table does get truncated via
the EXEC’d procedure, but the INSERTed rows never show up.

Another question: What is your current AutoCommit setting (see lower left
corner of Toad window)? Also, can you please try to run any stored procedure
(that doesn’t have a link to any linked server) from the right click on the
LHS of Object Explorer and see if its behavior is the same as the one you’ve
reported about?

AutoCommit is on (green) – for this at least.

I’ve also created a new stored procedure from this one. The only difference
is that I’ve replaced the INSERT into OpenQuery with a local one:

SELECT compname,login,domain,computermodel,audit_date INTO
MYSSDB.dbo.workstat_merge_tmp FROM DifferentSSDB.dbo.Workstation WHERE NAME
IS NOT NULL AND AUDIT_DATE IS NOT NULL;

This one works as designed when executed from “Run”. So perhaps it’s an
issue with the OpenQuery/Linked Server transaction?

Thanks!

Rich


#4

Hi Rich,

Seems like you are right and the problem is in linked server (Actually, it was my second idea when I asked you questions). Unfortunately, I cannot say if it’s a problem of ToadSS or MSDAORA because I’m not an Oracle expert. But after quick Google’ing I figured out that sometimes there are problems with this configuration.

Just curious, did you try the same in Management Studio? If it works there then it’s ToadSS problem for sure.

Thanks,

Igor.


#5

Hi Rich,

Seems like you are right and the problem is in linked server (Actually, it was my second idea when I asked you questions). Unfortunately, I cannot say if it’s a problem of ToadSS or MSDAORA because I’m not an Oracle expert. But after quick Google’ing I figured out that sometimes there are problems with this configuration.

Just curious, did you try the same in Management Studio? If it works there then it’s ToadSS problem for sure.

Thanks,

Igor.


#6

Hi Igor,

Hi Rich, Seems like you are right and the problem is in linked server
(Actually, it was my second idea when I asked you questions).Unfortunately,
I cannot say if it’s a problem of ToadSS or MSDAORA because I’m not an
Oracle expert. But after quick Google’ing I figured out that sometimes there
are
problems with this configuration. Just curious, did you try the same in
Management Studio? If it works there then it’s ToadSS problem for sure.

Hmmmm…I just tried the script as generated by the “Run” from Toad-SS in Mgmt
Studio and it worked. Here’s the messages output by Toad:

Statement:03/01/2011 10:20:38 0:00:00.109: Executed Successfully
1: -- Execute Stored Proc MYSSDB.dbo.apex_push

USE [MYSSDB];
Connection:03/01/2011 10:20:51: apex_push:29: Truncating table...
Connection:03/01/2011 10:20:51: apex_push:33: Inserting rows into MERGE
table...
Connection:03/01/2011 10:20:51: apex_push:38: Merging workstation data...
Connection:03/01/2011 10:20:51: apex_push:42: Done.
Statement:03/01/2011 10:20:38 0:00:13.406: 601 rows affected
6: DECLARE @return_value int;

EXEC @return_value = MYSSDB.dbo.apex_push;

SELECT @return_value as N'@Return Value';
@Return Value
=============
0
Script Execution:03/01/2011 10:20:51: Execution finished in 0:00:13.578

Note the “601 rows affected”. Now here’s the SSMS output from its “Messages”
tab:

Truncating table...

(1 row(s) affected)
Inserting rows into MERGE table...

(600 row(s) affected)
Merging workstation data...
Done.

(1 row(s) affected)

Only 600 rows here. The actual number of rows in the table being selected is
600, so I’m not sure how Toad-SS is getting 601.

Thoughts?

Thanks!
Rich