'dbo' disappeared from table and stored procedure components

TrevorB · Post by **TrevorB** » Fri 24 Oct 2008 11:18

Hi,

This is kind of a strange one that I cannot work out why it has happned.

I have changed nothing (as far as I am aware) in the connection parameters. And I've checked with the person who looks after the server and he says nothing in the configuration has changed there either.

But.....

I have not had to go and edit a table or stored procedure definition for a while. But today I have to and when I have gone into (for example) a table component definition, the table name is set to dbo.customers but when I drop down the list of tables within the database to choose from, it doesn't default to any table because all the table names listed do not have the 'dbo.' prefix. I can reselect the name without the prefix and save it and go back in and then everything is fine.

On stored procedures, the problem is slightly different but presumably for the same reason. On SPs the original name has a ; and a number at the end e.g. getcustomer;1 - in the list now to select from there is no ';1' suffix.

But under what conditions would the 'dbo.' prefix and the ';1' suffix disappear?

If all these need changing, I have a lot of them to do. So I thought maybe I could do it from the text view. But this is unusual to. For example, before I changed it (and when it could not reconcile the name to the list of possibles in th drop down), the text for the a particular stored procedure looked like this....

object MSReadyForDespatch: TMSStoredProc
StoredProcName = 'GetReadyForDespatch;1'
Connection = MSDespatchConnection
SQL.Strings = (
'{:RETURN_VALUE = CALL GetReadyForDespatch;1}')
Left = 320
Top = 32
ParamData =
end

If I go into the StoredProcName property and reselect the SQL procedure, the text view is then changed to:

object MSReadyForDespatch: TMSStoredProc
StoredProcName = 'GetReadyForDespatch'
Connection = MSDespatchConnection
SQL.Strings = (
'{:RETURN_VALUE = CALL GetReadyForDespatch }')
Left = 320
Top = 32
ParamData =
CommandStoredProcName = 'GetReadyForDespatch'
end

Does this point to any kind of problem or answer as to what is happening?

The only remote possibility that I can think of is that I have upgraded from 4.35 to 4.5, but I assume this wouldn't make a difference or it would be in the install and upgrade notes?

Do I have to go through and change all these to point to something that exists in the lists that drop down or do you think this won't cause a problem for now?

Many thanks,

Trevor

Challenger · Post by **Challenger** » Fri 24 Oct 2008 13:24

We reproduced the situation with table names but could not reproduce the situation with stored procedure names. This change of behavior was made in SDAC 4.50 and conserns the SQL Server support in UniDAC. Anyway this should not affect the behavior of your application.

TrevorB · Post by **TrevorB** » Fri 24 Oct 2008 13:41

Hi Challenger,

Thanks for the explanation re tables and it's good to know I don't have to go through and change them all manually!

On the stored procedures, the problem was slightly different. They were suffixed with a ;1 or a ;0 (no .dbo prefix). It seemed that ;1 was for stored procedures and ;0 was appended to scalarvalued functions. Examples: we had a stored procedure GetCustomerHeader, this was stored as GetCustomerHeader;1 in the storedprocedure name property and now is simply GetCustomerHeader. And we had a function called GetCategory which previously was stored as GetCategory;0 and now we cannot pick this up at design time and store it in the storedprocedure property at all.

The ;1 and ;0 seemed to be dropped now.

Is this another change of behaviour? If it is, how can I now pick up and execute the functions? Or will I have to recreate them as stored procedures, which I guess is easy enough to do.

Thanks again for you prompt support, which is always appreciated.

Trevor

Dimon · Post by **Dimon** » Tue 28 Oct 2008 14:50

As we said in the previous post, these changes should not affect the behavior of your application. You should not change anything in your source code and your application should work.

TrevorB · Post by **TrevorB** » Mon 03 Nov 2008 08:51

Many thanks for your reply, and the reassurance that the code will work if not changed.

Can I just check...... Am I right in saying then that it isn't really a correct thing to do to call a SQL function directly but rather they are really only meant to be used in other SQL objects? Is this why you no longer allow direct access to these functions? In fact it isn't really to much of a problem as I simply encapsulate the functions within stored procedures and access them from there. I was just wondering if it wasn't a correct thing to do to access them directly from code.

Many thanks,

Trevor

Dimon · Post by **Dimon** » Fri 07 Nov 2008 13:41

If you use TMSStoredProc to access stored procedures, you need only to define the StoredProcName property, while not bothering about writing SQL statement manually.
A stored procedure can be used in many cases. You can called it from many contexts, such as other stored procedures, calculated measures, or client applications.
For more information please refer SQL Server Books Online.