external data doesn't show all data...

[Melissa Ragsdale]

I am trying to access a SQL database (MS CRM 4.0) using the Connections
feature in Excel 2007. I am able to connect to various tables and views,
however, some key columns of data do not appear. I am wondering if they are
hidden or I have a security issue. If I try to access the data using SQL
Server BI 2005, all data is available. However using Excel 2007, the columns
I need to connect the data to other tables is missing. Any thoughts?

 

[LProegler]

I've encountered a similar problem in the past when SQL columns were 'raw'
data. Once cast to appropriate data type things worked fine. In CRM user
defined variables may be stored as raw, untyped data.

 

[Melissa Ragsdale]

Once cast to appropriate data type things worked fine... what does that mean?
I don't understand why something looks like a column of data in SQL but it
doesn't in Excel. I'm stupid but willing to learn here Thanks!

 

[LProegler]

You have to create a query in SQL Server to force the data to the type you
want. Basically, execute the following script in SQL Server Management
Studio (or Query Analyzer if using SQL Server 2000)

"CREATE VIEW vw_Test1
AS
SELECT CAST(missingfieldname AS INTEGER) as NewFieldName FROM
TableOrViewInvolved".

Replace INTEGER with approppriate SQL datatype. Then try
connecting/importing vw_test1 into Excel. If the column shows up you've
found the problem. You'll have to learn enough SQL in order to create views
with the data you need

It's also possible to use the Excel Query Editor to use CAST on one of the
offending columns to see if it changes anything.

Lolt Proegler

 

[Melissa Ragsdale]

THANKS! I will see what I can do with this. MS is pretty strict on any
changes/edits or additional views to CRM database. I have books though and I
can read up on CAST. Thanks again.