Troubleshootinga DataTools Link to an SQL Server database

[austin.dumas]

Hello...I have seen several posts explaining links to excel files and access databases, but not to an SQL Server.

I have established DataLinks to Excel files and Access databases just fine, But I can't seem to access the same data stored in an SQL database.  Below is a screenshot of the source view in question, a screenshot of a sample element (to show Element ID) and the set up I have done in DataTools.

Note that I am trying to read data from a View as opposed to a table.  This is because the table the data resides in is a massive table repository from a bunch of sources and a view is required to get the specific data into the desired 'table' format. By all accounts my setup is correct, but it just won't read the data (applicable tab is not showing). Being relatively green in the scripting department, I am wondering where that is where the issue lies.  Any help is greatly appreciated.

Austin

[Anonymous]

Everything looks straight forward but try adding a semi colon (;) at the end of the sql string.

[austin.dumas]

Hi jgarza,

Good catch on the semi-colon (syntax...gah!), but it doesn't look to have done anything. After editing the query, turning Mnage off/on, checking/unchecking, etc.  The info from the db still isn't appearing.  I know it's connecting to the database in that the Setup button gives me a list of the dbs in the SQL server.  And I triple checked the Element ID and it's correct. Not sure what else it could be.

Cheers,

Austin.

[Anonymous]

Try removing the underscore from "Element_ID" they are not matching your tabs name (Element ID).

Jgarza0422

[austin.dumas]

Hi jgarza,

Gah! Syntax strikes again! That looks to have done the trick, although now when you select items in the model it takes forever to 'register' the selection and view the associated data. I'll play with this a little more, but for my immediate needs, you have answered the question. Thanks.
Cheers,
Austin.

[Anonymous]

Glad, to hear its working.

JGarza0422

[austin.dumas]

I spoke to soon.  It turns out I had another Data link to the same data in another excel file turned on without realizing it.  I turned it off and it still isn't pulling the data.  However it is doing something.  You click on an element and the 'wheel spins for about 2 minutes but then finally the properties window shows data, but just the regular stuff, not the tab with the data I'm looking for.

Could it be a permissions thing? I'm going to play with that more myself.

Cheers,

Austin.