Toad World® Forums

Multi Row Data results issue


#1

I’m using Toad as a trial product seeing if we’d like to purchase this software for query use. Currently we use Excel to query the data.

So far I LOVE the interface. it has a lot more bells and whistles than just Excel.

HOWEVER… My problem is that I’m getting a ton of rows back that are blank or with garbage data. I have compared the SQL syntax to that of excel and it’s exactly the same. WHY is this happening. I’m connecting to the DB with ODBC. And the “blank” results vary – sometimes I get like 50 per real item or just 25.

I’m sure I’m missing something simple – Please help!!!

Here is an example of the garbage I’m getting back:

INVOICE
CRUISE
555555
XXX100910


#2

What database are you connecting to?


#3

I’m connecting to a SQL server DB via ODBC.

I’m also getting this error at times:

System.FormatException
Input string was not in a correct format.
Stack Trace:
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)
at Quest.Toad.NativeOdbc.UnManagedColumnDescriptor.GetBufferDataAsDecimal(UInt16 rowOffset)
at Quest.Toad.NativeOdbc.OdbcDataReader.GetValue(Int32 i)
at Quest.Toad.Db.Connection.ReadRow(IDataReader reader, FastTable datatable, FastReadOptions options, PrePostProcessHandler prePostProcessHandler)
at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()


#4

Can you go into the connection properties and click on the advanced tab and change the block cursor size option to “1” and let me know if that helps?

Can I ask why you are using the ODBC connection to SQL Server over the ADO.NET based connection?

Let me know if this exception still occurs after changing the block cursor size.

Thanks.


#5

That worked!!! THANKS!!

um… this might be a dumb answer… but i’m using ODBC because that’s what my IT department set up for me to connect the DB. Is there a better way to connect???

FYI I have some DBs located onsite and other is another state and I have to use Terminal Server to connect – does this product work on TS?


#6

Another question…

Is there a way to make the tables auto link on keys and save any that I have to create because the field names that are different but that is how the tables link?


#7

Glad to hear it worked.

When creating a connection you should be able to select “SQL Server” (non Freeware edition) instead of “ODBC”. Using the SQL Server connection will generally give you much better performance over the equivalent ODBC connection.

I don’t normally run TDA in a Terminal Services environment but I suspect it will run without issue. Please give it a try and if you have any issues please feel free to post a comment or contact me directly.

Thanks.


#8

If you are using the Query Builder and you have foreign keys setup on the tables that you pull in, the QB should automatically create the join between those tables. If you create the relationships manually, saving the QB file will retain the relationships that you create for that query. Let me know if that answers your question.

Thanks.


#9

I’m using the QB and when I pull the tables in it isn’t automatically
joining on the primary key… is there a step I’m missing before I run
the query?

Example:

Table one:
Sales
Field Cruise
Field Agent

Table two:
Cruise
Field Cruise (pk)

Table three:
Agent
Field Agtcode (pk)

Shouldn’t it link the cruise fields automatically? Now the Agent and
Agtcode fields – I would expect to have to link separately as they
don’t have the same name.

Thanks…

:smiley:


#10

Does table one have foreign key relationships to the other two tables?


#11

No they do not. So I guess that answers the overall question. Now…
is this something that the developer would have to do or can I build
something in toad to keep these relationships without affecting the
actual DBs.

Also, is there a way to lock down toad so that it doesn’t allow the user
to “edit” the data just view it and so on…


#12

If you use the ER Diagram and manually draw the relationships these are retained in the Annotation Cache. This cache can be shared by a team(See Help File). The ER Diagram can then be sent to the Query Builder as a template to start a Query and automatically adds the tables and relationships.

Regarding “edit”: You can accomplish this is two ways. You can install the application as Read-Only. There is an option when installing and this can be done on the command line as well.

If you want to implement Read-Only differently for each user and server, we have Toad Security so you can set this up. Most users like to just use the Read-Only install. This prevents grids from updating as well as preventing execution of any insert, update, delete or Create statement.

If you truly only want a non-editable grid, there is a read-only option for just grids in the Options. Search for ‘Read-Only’ and you will find it.

Debbie


#13

It would be something that the developer would have to do.

Yes, when installing the application there is an option for “read only” mode. You can read more about it on this thread: http://tda.inside.quest.com/thread.jspa?threadID=9929&tstart=-2

Thanks.