Skip to content
+1-888-319-3663

COMMUNITY FORUM

Cannot Find Table 0

Sherry asked 5 years ago
I have a map that is using a stored proc as its source.  The stored proc pulls data from a GP database and a linked server.   The map was created in older versions of GP and SC and upgraded.  Prior to the upgrade, I didn’t get any errors with this map, and I could edit the mapping as needed.  Since the upgrade, when I open this map I get “Cannot find table 0” error.  The event viewer for SC shows “Connection could not be validated.Cannot find table 0.”
I can preview the data (it correctly returns no rows), but I cannot open any of the nodes to review the mapping.  The map runs just fine (no errors) and integrates into GP if there is data to integrate.
I started a new map from scratch.  I’ve gotten as far as specifying the data source (OLEDB Connection) and adding the query to execute the stored proc and provide the two required parameter values.
I cannot set a key field or open any of the nodes for mapping; I get the ‘cannot find table 0’ error.
I can run the stored proc execute statement that I am using as the Source Query just fine in Management Studio.  I can run the select statements in the stored proc just fine. 
I’m at a loss…..  Any ideas?  Thanks!
Patrick Roth Staff replied 5 years ago

I would guess your query is failing to execute and throwing a SQL error. Most likely due to the linked servers (even if it worked previously).

You get the “Cannot find table 0” error because your script didn’t return a resultset (or even a resultset with no records).
SmartConnect is looking for the first table in the resultset (DataTable[0]) and it can’t find that particular one (since not returned) and so blows up with a nasty error.

You’d have to run a SQL Profiler Trace to see the actual issue – but the main reason is “the proc isn’t returning a recordset”.

Sherry Whitten replied 5 years ago

I took the where clause off the stored proc so it returns all records, not just ones that need to be integrated. When I preview it in SC or run the stored proc in SQL, data is there. It is definitely returning data. I just can’t validate the connection or set key fields.

Sherry Whitten replied 5 years ago

I ran a SQL trace just for the one blip of a second it takes me to click the validate button and get the error message. It returned several rows in the trace. None of them seemed informative; any advice on what specifically to look for in the trace? Thanks.

Sherry Whitten replied 5 years ago

I ran a trace for a stored proc that gives me ‘connection could not be validated’ error, and then I ran one for a stored proc that validates successfully and compared the two. The execute stored proc statement seems to be the same between the two. the only place I see the error message is where the ‘connection could not be validated’ is inserted into the event log. The trace for the stored proc that successfully validates has two statements that I don’t see in the one that failed. exec [ADN]..sp_primary_keys_rowset N’@Deposits’,NULL
exec [ADN]..sp_indexes_rowset N’@Deposits’,NULL,NULL

What causes those two statements to run? Are they missing from my ‘bad’ trace because I haven’t been able to select key fields for that stored proc yet?

Anything else I can look for in the trace?

As a review – here’s my testing methodology.
1. Start a brand new map and give it a name.
2. Select Bulk Data Load data source type and OLEDB Data Source Connection. Selection the connection type.
3. In the Query, enter the exec stored proc statement for the stored proc that will validate successfully.
4. Click Validate. Successful.
5. Change the Query to the exec stored proc statement that will not validate.
6. Click Validate. It fails.

So clearly this has nothing to do with the destination type, mapping, tasks, translations, anything. It is strictly the call for the stored proc. Both use exactly the same Connection String.

I’m at a loss….

Patrick Roth Staff replied 5 years ago

“I ran a SQL trace just for the one blip of a second it takes me to click the validate button and get the error message. It returned several rows in the trace. None of them seemed informative; any advice on what specifically to look for in the trace?”

if you data previews fine – why are you bothering to validate? Just look up the key fields and then run the map.

not sure about the queries on the sp_primary_keys_rowset stuff – might be SQL doing what it is doing for whatever reason.

Sherry Whitten replied 5 years ago

My data previews fine, but that is all I can do. If I try to select key fields, I get the ‘connection cannot be validated – cannot find table 0’ error. If I select GP general ledger as a destination and attempt to open a node to map the fields, I get the error. I cannot do anything with this map.

Heather Maneiro replied 3 years ago

We are running into the same error here after moving servers. Proc executes fine in management studio – but not through Smart Connect. The proc is using a temp table. If the proc is rewritten to take out the temp table it works. Any ideas?

Patrick Roth Staff replied 3 years ago

The solution to this last reply fro Heather was to switch from SQL OLEDB source to SQL ODBC Source.


If you would like to submit an answer or comment, please sign in to the eOne portal.