Toad World® Forums

Little question about TOAD views

I have a DB in production on SQL Server 2005. I backup it and restore on my PC to works.
I made changes and test it on my PC, before apply to DB of production.
The DB's are identical.
These DB's are on different server, with different connection in TOAD SS.

3 weeks ago I wrote views in my TOAD 6.0 beta on DB of production. All is OK.
I can use these views without problems in Edit SQL, but yesterday I try to use these views in SQL Query Builder and I have one problem.
See my screensoht TOAD_VIEW.JPG in attach.

In details, I try to drag and drop a view in SQL Query Builder, but view was created on DB of production on another server and your message advise me of the problem.

Attention, it's a false problem, because I can select columns of view and press F9 to see results.
For details see my screenshots TOAD_EDIT.JPG and TOAD_RESULTS.JPG.

Why don't you think to add possibility in TOAD VIEWS window to change the parameter -original connection - manually ?
I try to edit view with menu - EDIT TOAD VIEW - to change orignal connection but I obtain an error message.
See my screenshot TOAD_ERROR.JPG in attach for details. TOAD can find original server of the connection. Correctly because the server of DB is on another network.

TOAD works OK, but I want change original connection parameter of my views to works correctly with the same views in all case, production and development on two different servers/PC's.

I stay tune for your consideration.

Best regards.
Sergio Bertolotti

Toad Views should work the way you describe. What version of Toad for SQL Server are you using. We made many changes for the 6.0 version. You might want to try the Beta to see if this resolved.

One thing to check. Did you fully qualify your objects? Perhaps the full qualification is different from server to server. IE: database/Owener. To use toad Views on multiple connections it is best to no fully quality but the connetion does need to be able to resolve the objecst in the query.

Debbie

Dear Debbie,

many thank's for your help and support.

Today I re-test my problem with TOAD SS BETA 6.0.0.229, the last available. And the view for me doesn't work like you tell me in your message.

See my screenshot in attach for details.
As you can see I try to drag and drop a view from TOAD VIEWS - on the right side - but when I drop the view in query builder I obtain the error message that you can see.

I have a DB with name AZService on a SQL Server of production.
I have the same DB named AZService on my notebook in development environment. This DB is a restore of the original DB in production.
View was created on a server SERVERDB\SQLEXPRESS of production.
Tables are identical.
Text of sql statement of the view is a simple - select * from tickets - where tickets is a table in this DB, and for me objects are not fully qualified.

It's a vey simple simple statement.

Have you some other idea ?

Best regards.

Sergio Bertolotti

It looks like your table [tickets] in database that is not default for this connection.
That’s why SQL Server cannot resolve the name and your get this message.
For SQL Server I’d suggest to use fully qualify names in Toad View. In this case the server will be able to resolve the names.

Hi Aleksey,

many thank’s for your support.

You are right. Database is not default for this connection.

Your colleague Debbie wrote :

To use toad Views on multiple connections it is best to no fully quality

You wrote :

For SQL Server I’d suggest to use fully qualify names in Toad View.

At this point what’s the right solution ?

One consideration…
I have two DB’s. One for test and one for production.
Generally I wrote views on test DB, and sometime I use these views on production DB.
If every time I must to full qualify table with DB name, what’s the power of views ?

From my point of view, views are some mechanism to use for don’t rewrite statement SQL for every DB that I use.
At this point, I prefer to use funtionality SQL Recall. I use/select a DB and recall statement on it.

What’s do you think of this my consideration ?

I stay tune for your answer…

Best regards.

Sergio Bertolotti

You can define a connection that will always point to that database when connecting. To do this set the database attribute in the connection.
Debbie

Hi Debbie,

many thank’s for the clarification.

I know how define default attach DB when connecting to a SQL server.

Repeat my previous consideration.

If to use views I must define a default DB to works I don’t understand the utility of views.

I can’t reuse my sql statement on another DB with same metadatas.

At this point I prefer change my DB connection via combobox of DB or via use ; in the script and use SQL Recall.

From my point of view it’s better.

Probably in Toad Bunsiness Intelligence views are more useful, but for one wich develop or administer DB’s views it’s feature not so important.

I stay tune for your consideration.

Best regards.

Sergio Bertolotti

Hi Sergio,

Every tool/approach has some limitation. With the Toad views this limitation is - the system has to be able to resolve the names. How we achieve it depends on us.
If dev environment is the same as production then fully qualify names should not be an issue. Or we can set default schema/database for the connection. But I can imagine a situation where none of these works. In this case Toad Views do not useful at all.
But at the same time I can give an example where the views can be used and SQL Recalls cannot – Query Builder. Toad Views can be added and used in QB easily.

I hope it helps you to understand better how to use Toad Views.
Thank you for all these questions because it helps us to make Toad better

Aleksey

Alex, I am not sure I agree with that statement. The purpose of a Toad View is to not fully qualify the objects so it can be used on different connections. I do think we have an issue here. I have entered CR109048 to give us some time to look into this issue.

Debbie

Hi Aleksey, Debbie,

Aleksey wrote :

But at the same time I can give an example where the views can be used and SQL Recalls cannot – Query Builder. Toad Views can be added and used in QB easily.

Aleksey a DB Developer or admin don’t use Query Builder. I prefer open SQL Editor and write manually my statement SQL’s to admin/manipulate metadata and datas.

Sometime I can use QB to test rapidly an SQL statement on different DB like my situation for example. A DB of production and another in test environment.

But this situation it’s not the standard when I work with DB’s…

I think this situation is standard to every DB admin or developer…

Debbie,

many thank’s for your support . I believe you understand my problem and I hope to see it solved in the future.

At the end of the story I see a bit of confusion on the concept of views and how to use it.

This is not a good signal for me and for all TOAD SS users.

I conitnue to think that views it’s more useful for Business analisys, like TOAD Business Intellignece, instead a DB admin or developer.

For now I return to use SQL Recall. Probably in the future if the situation changes, maybe, but for now I prefer SQL Recall…

Best regards.

Sergio Bertolotti

I removed the message. It has to be in a different thread.

Message was edited by: Aleksey Bazhenov

Hi Aleksey,

you wrote :

I removed the message. It has to be in a different thread.

Where is the new thread ?

I make some test and finally I have reproduce the problem.

If you send me the link of the new thread I post the steps to reproduce it ?

If you prefer send me and e-mail…

I stay tune for your answer.

Best regards.

Sergio Bertolotti

I did find a bug in the Query Builder on this issue. So we will get that fixed.

You make an interesting point in regards to Toad Views and Analsyts vs DBA/Dev. The feature was originally created for Toad Data Point but we felt is was a common use case so shared it with all Toad.net products.

Debbie

Hi Debbie,Aleksey,

Yesterday I found a bug about this problem.
I describe it below...
I connect to DB.
With DB selected I open QB a drag and drop views on it.
As you can see the error appear.
See error_view.jpg for details.

To don't have a problem. I connect to DB and select it.
I expand node of DB AZService and select subnode tables.
At this point I open QB and drag and drop view on it.
In this case all works well.
See view_ok.jpg for detail.

At the end of the story, to don't obtain error I must to connect a DB, expand nodes and select TABLES. If I don't follow this step the problem appear.

In this mode al works OK.

Debbie, do you know if ths problem is the same to yours ?

Debbie you wrote :

You make an interesting point in regards to Toad Views and Analsyts vs DBA/Dev. The feature was originally created for Toad Data Point but we felt is was a common use case so shared it with all Toad.net products.

Business Analyst and system DBA or Developer are role completely different.
One use data and metadatas to crete graph,query,analisys,etc,etc,...
DBA create a foundation to building the future...

Best regards.

Sergio Bertolotti

Hi Debbie,Aleksey,

Yesterday I found a bug about this problem.
I describe it below...
I connect to DB.
With DB selected I open QB a drag and drop views on it.
As you can see the error appear.
See error_view.jpg for details.

To don't have a problem. I connect to DB and select it.
I expand node of DB AZService and select subnode tables.
At this point I open QB and drag and drop view on it.
In this case all works well.
See view_ok.jpg for detail.

At the end of the story, to don't obtain error I must to connect a DB, expand nodes and select TABLES. If I don't follow this step the problem appear.

In this mode al works OK.

Debbie, do you know if ths problem is the same to yours ?

Debbie you wrote :

You make an interesting point in regards to Toad Views and Analsyts vs DBA/Dev. The feature was originally created for Toad Data Point but we felt is was a common use case so shared it with all Toad.net products.

Business Analyst and system DBA or Developer are role completely different.
One use data and metadatas to crete graph,query,analisys,etc,etc,...
DBA create a foundation to building the future...

Best regards.

Sergio Bertolotti

We found the bug and fixed it. It will be in the next Beta posting. Thanks for finding this.

Debbie

Debbie,

many thank’s to you for your help and support.

When the new beta will be available I test it and inform you about the result.

Best regards.

Sergio Bertolotti

Hi Debbie,

last beta version of TOAD SS 6.0.0.299.

The problem is the same.

See my screenshot in attach.
The problem isn't been solved.

I stay tune for your answer.

Best regards.

Sergio Bertolotti

Dear Debbie, Aleksey,

last beta version of TOAD SS 6.0.0.365cand now finally for me the problem has been solved.

Best regards.

Sergio Bertolotti