MS SQL Server 2008 connection problem 1

[PDAnalyst]

Hi,

I am having a very unique problem that our dba's can't figure out.

I am using CR XI R2 SP6 to connect to a new SQL Server 2008 R2 database that had been upgraded from SQL Server 2000. I have been using ODBC Native SQL connection ver2005 until now with no problems on the old database. Ever since they have upgarded to the new SQL Server 2008, when I connect with ODBC to the same table that had 145 columns, I am only able to see 35 columns (with different data types). In the SQL Server Management, with my id and password, I can see all of the 145 columns.

If I use the ADO connection instead of ODBC, with same user name and password, I am able to see all 145 columns. The problem is that I have close to 65 reports that I have created using the ODBC connection and don't want to convert all to ADO connection.

Does anyone have any idea why this might be happening?

Thanks,

Safa

 

[kray4660]

Are you using the lastest ODBC driver for SQL Server 2008 (Microsoft Native Client 10.0). It is backwards compatiable (I think to SQL Server 2000). The ODBC Native SQL driver for 2005 will not connect to an 2008 DB.

 

[PDAnalyst]

Yes,

we have upgraded to SQL Server Native Client 10.0 ver 2009.100.1600.01 thinking that it might be the problem but it did not help.

Our dba just gave me full dba rights to the Server 2008 database (instead of read only) and now I am able to see 127 columns (still missing some columns). But he is telling me that he can't keep it the rights that way since I can change the database.

We have a replicated database on the same server that is a mirror image backup and with read only access I am able to see all of the columns. It is very strange that production database is restricting the columns but the backup is open to see all columns.

 

[kray4660]

Security in 2008 can be hard to understand (yes there is column level security available). I rely on our DBA's to set up the security so I can access what I need. So far it has worked.

 

[PDAnalyst]

So it is possible that there is column level security that had been implemented on each table that prevents me from seeing the columns?

Do you know if there is any documentation regarding this from MS that I can show to my dba?

Thanks

Safa

 

[kray4660]

There are volumes of information from Microsoft. The best bet probably would be to go to

http://msdn.microsoft.com/en-us/default.aspx

and search from there.

Lots of luck.

 

[PDAnalyst]

Thanks for all of the help.

My dba told me that he will be creating a special account that has higher priviledges to see the data and for me to stop using my account log-in.

Hopefully that will be fixing the issue.

 

[davisprogrammer]

What OS are you running Crystal on? If it's XP, make sure you have the latest MDAC 2.8 drivers. I have experienced trouble with the sql native client 10.0 passing partial data in sql server 2008 that I have not experienced using ODBC.

 

[PDAnalyst]

Thanks for reviving the thread,

Running the CR 11 R2 on Windows XP SP3.

I have MDAC ver 2.81.1132.0