SQL Select statement with a space in the table name

Business Central/NAV Developers Forum
1

Hello,

I can’t seem to find the answer to my problem on the forums so I thought I would post it here. I’m trying to select a range of records from another Navision Database from within Navision. I can get it to work great if the table I am querying doesn’t have a space in the name.

This select statement works perfectly: ADORst.Open(‘Select * from $Item’,ADOConn,2,3);

This does not: ADORst.Open(‘Select * from $My Table’,ADOConn,2,3);

I have tried:

ADORst.Open(‘Select * from [$My Table]’,ADOConn,2,3);

ADORst.Open(‘Select * from “$My Table”’,ADOConn,2,3);

ADORst.Open('Select * from <company>$My Table',ADOConn,2,3);

Nothing works. The error I receive is: “Invalid Object Name”

Here is all the code:

ConnStr := ‘Provider=SQLOLEDB.1;Password=mypassword;User ID=sa;database=’+MYDATABASE+’;Data Source=’+MYDATASOURCE;
CREATE(ADOConn);
ADOConn.Open(ConnStr);
CREATE(ADORst);
ADORst.Open(‘Select * from $My Table’,ADOConn,2,3);

Both databases are running Navision 4.0 SP3.

Thanks!!

2

Try SELECT * FROM [Cronus$Item Ledger Entry]. If company name has special chars you might want to quote with [].

3

Thanks for the reply Nuno! I have tried the braces [ ] and I still got the same error “Invalid Object Name”. The company name does not have any unusual characters and it’s only one word. I have to be missing something simple. [:)]

4

Haha! Success! So this table is a custom made table. I needed to prefix the table name with “dbo”.

Example: dbo.[$My table]

It works now! If you use the braces you must provide that prefix for a custom table. I’m guessing a system table would be “sys”. At any rate it’s solved now! Thanks![8-|]

5

Try with table name enclosed with double quotes (") eg select * from “my table name”