'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

Database
Enthusiast

'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

Hi

I've wrote small program in C# .net using the  Teradata.Client.Provider 

which uses a statement for fetching Stored procedure body, for instance: SHOW PROCEDURE tdwm.TDWMRuleEnableSP;

the statement above return result when calling it from the program + TD Sql Assitent on dev. Environment.

but on Customer site (with other DB and User of course)  the program return nothing for this.

Note that when asking other some system DB's like SYSLIB i do receive a response: Connection Error: [Teradata Database] [5535] No SPL source text available for stored procedure '....'.

when using the same statement on TD Sql Assitent  with the same user,  I do get the procedure body....

 

Can anyone suggest Why I don't  get response on the Customer  site for his operational database and his Procedures?

is it permission\Priviligaes issue? something else..?

maybe related to the Teradata.Client.Provider ?

 

 

thanks!

Arik

 

8 REPLIES
Teradata Employee

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

I believe SHOW generally returns multiple result sets. Are you invoking NextResult method to be certain you have retrieved all the results?

Highlighted
Enthusiast

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

 

I'm using DataAdapter and filling DataTable, not using DataReader.

and then i'm concating the rows on code.

 using (TdConnection cn = new TdConnection(ConnectionString))
                {

                    TdCommand cmd = cn.CreateCommand();
                    cmd.CommandTimeout = CommandTimeoutWindow;
                    cmd.CommandText = query;
                    
                    using (TdDataAdapter da = new TdDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                }

 

Teradata Employee

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

Then the fill method should be populating multiple Data Table objects. See the Microsoft documentation on how the common Data Adapter class handles multiple result sets.

Enthusiast

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

thank you man,

what i'm still not understand, why pulling the rows with DataAdapter and filling DataTable , works fine on various other customers.

and why is the problem with this specific customer.

can you please  ellaborate why do you think using DataReader can give better result and why is the problem  with DataAdapter occur on the first time ?

thanks again

 

 

Teradata Employee

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

Typo in prior reply (edited now). It's the Data Adapter that should fill multiple data tables; Data Reader isn't "better" for this than Data Adapter, just different in how it handles multiple result sets.

 

No idea why it would work for some sites and not others, unless it's somehow tied to versions of software. (And that could even be Windows software, not just TD client and database.)

Enthusiast

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

Hi @Fred
maybe the with code it get more ideas...
i have tried both ways:  
1. DataSet and 
DataAdapter 

2. DataSet and DataReader
this is the code  DataSet and DataAdapter:

 DataSet ds = new DataSet();
            try
            {
                using (TdConnection cn = new TdConnection(ConnectionString))
                {
                    cn.Open();
                    TdCommand cmd = cn.CreateCommand();
                    cmd.CommandTimeout = CommandTimeoutWindow;
                    cmd.CommandText = query;

                    using (TdDataAdapter da = new TdDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }
                }
            }

and this is ths code i have tried for DataReader

DataSet ds = new DataSet();

            try
            {
                using (TdConnection cn = new TdConnection(ConnectionString))
                {
                    cn.Open();
                    TdCommand cmd = cn.CreateCommand();
                    cmd.CommandText = query;
                    cmd.CommandTimeout = CommandTimeoutWindow;

                    using (TdDataReader reader = cmd.ExecuteReader())
                    {

                        // Read every result set in the data reader.
                        while (!reader.IsClosed)
                        {
                            DataTable dt1 = new DataTable();
                            // DataTable.Load automatically advances the reader to the next result set
                            dt1.Load(reader);
                            ds.Tables.Add(dt1);
                        }
                    }
                }
            }

 thanks

Teradata Employee

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

Either one looks OK. In either case, do you then check whether the DataSet Tables collection contains multiple DataTable objects, e.g. "Table" and "Table1"?

 

Back to the original statement: Does SQL Assistant return a result with both ODBC and .NET connections?

Enthusiast

Re: 'SHOW PROCEDURE' return empty on code but return result on the TD SQL Assistent

yes. i do check all the tables in the DataSet collection.

regarding the SQL Assistant - i will look into it, thank you