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?

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.
--
Lolt Proegler


"Melissa Ragsdale" wrote:

> 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?

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" wrote:

> 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.
> --
> Lolt Proegler
>
>
> "Melissa Ragsdale" wrote:
>
> > 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?

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" wrote:

> 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" wrote:
>
> > 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.
> > --
> > Lolt Proegler
> >
> >
> > "Melissa Ragsdale" wrote:
> >
> > > 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?

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.

"LProegler" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > > --
> > > Lolt Proegler
> > >
> > >
> > > "Melissa Ragsdale" wrote:
> > >
> > > > 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?