Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana MHi Diana,
I do not think that someone here can give a specific solution to that problem.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your sp
from vb. also take a look at sysprocesses able and try and see if you have an
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:
> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it does
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running from
> query analyzer
> I can assure you that it is not permission problem, it is not query problem,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored procedues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>sql

pl/sql package will not compile

hi,

trying to concat 1:m relationsships data to a string fails :

what did I do wrong?

PACKAGE denorm_PKG IS

FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2 ;

END denorm_PKG;
/
PACKAGE BODY denorm_PKG AS

/*

*/
FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2

IS
-- Cursor fr die Datenbankabfrage
CURSOR netzcrs (lv_netzwerkdienst n.netzwerkdienst%TYPE) IS

select n.netzwerkdienst

from netzwerkdienst n, knoten_netzwerkdienst kn

where n.id_netzwerkdienst = kn.id_netzwerkdienst

and kn.id_plan = ix_plan
and kn.id_knoten = ix_knoten;

tmpVar varchar2;
netz_rec netzcrs%ROWTYPE;
BEGIN
tmpVar := NULL;

IF NOT netzcrs%ISOPEN
THEN
OPEN netzcrs(lv_netzwerkdienst);
END IF;

FETCH netzcrs INTO netz_rec;
-- Schleife ber alle Resultdatenstze, konkateniert alle
netzwerkdienste
WHILE (netzcrs%FOUND)
LOOP
IF tmpVar is NULL
THEN
tmpVar := netz_rec.lv_netzwerkdienst;
ELSE
tmpVar := tmpVar ||','|| netz_rec.lv_netzwerkdienst;

END IF;
FETCH netzcrs INTO netz_rec;
END LOOP;

CLOSE netzcrs;

RETURN tmpVar;
END GetNetz;

END denorm_PKG;
/

Oracle Version 8.1.7.4

error msges are the following

SP2-0734: Unbekannter Befehl ab "PACKAGE de..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "FUNCTION G..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "ix_plan IN..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "END denorm..." - restliche Zeile
ignoriert.
SP2-0044: Zum Auflisten bekannter Befehle HELP
und zum Verlassen EXIT eingeben.
END denorm_PKG;
*
FEHLER in Zeile 32:
ORA-06550: line 32, column 1:
PLS-00103: Encountered the symbol "END"

SP2-0734: Unbekannter Befehl ab "PACKAGE BO..." - restliche Zeile
ignoriert.
DOC>
DOC>*/
SP2-0734: Unbekannter Befehl ab "FUNCTION G..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "ix_plan IN..." - restliche Zeile
ignoriert.
SP2-0042: Unbekannter Befehl "IS" - restliche Zeile wurde ignoriert.
SP2-0734: Unbekannter Befehl ab "CURSOR net..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "from netzw..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "where n.id..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "and kn.id_..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "and kn.id_..." - restliche Zeile
ignoriert.
SP2-0044: Zum Auflisten bekannter Befehle HELP
und zum Verlassen EXIT eingeben.
SP2-0734: Unbekannter Befehl ab "tmpVar var..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "netz_rec n..." - restliche Zeile
ignoriert.
END denorm_PKG;
*
FEHLER in Zeile 32:
ORA-06550: line 32, column 1:
PLS-00103: Encountered the symbol "END"

SQL> PACKAGE denorm_PKG IS

SP2-0734: Unbekannter Befehl ab "PACKAGE de..." - restliche Zeile
ignoriert.
SQL>Hello,

which program do you use ... I never read such error statements !!!
As I was testing your posted package with AlligatorSQL, it compiles
with any error - except that I do not have your table "knoten".

Perhaps you have to place a "CREATE OR REPLACE" before your "PACKAGE BODY" and "PACKAGE" statement ?!!?

Hope that helps

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hello,

which program do you use ... I never read such error statements !!!
As I was testing your posted package with AlligatorSQL, it compiles
with any error - except that I do not have your table "knoten".

Perhaps you have to place a "CREATE OR REPLACE" before your "PACKAGE BODY" and "PACKAGE" statement ?!!?

Hope that helps

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

I 've used sqlplus and the stmts as a script.sql|||Hello,

do you have tested the "CREATE OR REPLACE PACKAGE"
command ?

Just insert "CREATE OR REPLACE" before your package and package specification.

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

Pkg Execution Works in VS but not in SQL

I'm able to execute a package in VS Pro on my machine. However when I upload it to the sql server and try to execute the package directly it fails with:

Error: The product level is insuficient for component "...." (3129)
Error: The product level is insuficient for component "...." (5411)

The first component is a DataReader Source which is consuming an ODBC (Noble Systems ATOMIX Driver) connection and the second component is a DataConversion object. Anyone know why this would work running it on my pc in VS but not when I execute it from SQL on the same machine?

This is a silly question but are SSIS components installed on your sql server?

The error message looks similar to an error message I had once. I was trying to execute maintenance plans (which uses SSIS) and I had not installed the components.

|||

Jason's suggeston is a good one. You only need to install client components off the CD in order to build packages. You need to install SSIS to execute them outside of BIDS/VS.

-Jamie

|||

Yes, SSIS is installed. I can log into the service and can successfully run and schedule 'hello world' packages. As a developer I'm locked out of this system but I am trying to get access to the app logs to see if they reveal something.

I'm really at a loss. Since I'm executing the package it should be using the odbc driver from my pc as well as the dsn config right? Based on my description of the objects that threw the error(s) am I trying to use a feature that is only available in the enterprise edition?

|||

How are you executing the package?

Donald

|||

from within SMS-->Stored Packages-->MSDB--><Subfolder>--><MyPackage>

I right click on package and select Run Package.

thanks,
Scott

|||All components work in VS reguardless of the version required to run those components. The version checks come in only when you run the package outside the VS environment. What version of SSIS is installed on the server?|||

v.9.0.1399 (pre SP1)

|||Sorry, what product level. Also, list all the types of components in the entire package.|||

Ah, sorry about that.

All of the following components are installed on a single Win3k server (flavor and sp level n/a but I can find out if necessary) Sql standard edition:

Database Engine
SSIS
SSRS

|||

One - I got this message when running the package on my local machine with Enterprise Manager connected to the server.To fix this, you need to install the SSIS Service on your local machine. When you installed SQL 2005, you only installed the workstation components and the workstation product level is missing some components. Installing the SSIS Service will include the components needed to run SSIS on your local machine.

Two - If you are remote in to your server and you are getting this message, then you might have a Fuzzy lookup task that only works with the Enterprise Edition.

Hope that help.

|||Does anyone know if this will be a licensing issue?|||

Installing the SSIS component locally on my machine resolved this issue. However, I'm concerned about this behavior (i.e. not being able to execute a package that is on a server from a remote machine via Mgt Studio).

Maybe someone at Microsoft can answer this:

If I have a full install of the client tools and even a full install of VS05 Pro, why do I need to explicity installl the SSIS server component on my local machine to run packages located on a remote server (sql 2005 standard w/out sp1)? I spent a lot of time troubleshooting this error (as did a number of members of this forum) and I can't help but think that this should have worked out of the box.