How to query/report on data in NDF files

Question

How to query/report on data in NDF files

julie.tilley

SSC Veteran

Points: 280
More actions
March 15, 2009 at 8:38 am

#129089

Hello
I have an Avaya telephone sys database on SQL 2005. I have 1 .MDF file and 19 .NDF (Secondary datafiles).
I need to be able to produce reports on the history of a call centre but I am struggling to know how to do this. This is the first time I have ever come across a database with FileGroups and wonder how on earth I can query my data across the MDF and NDF files. (I can query MDF no problem).
I write a lot of reports with Crystal Reports (ODBC'd) to SQL Server Views etc, and I was hoping someone out there has done the same.
Alternatively is there a way with Analysis Services that would allow me to view data in an NDF file.
Please any help I would really appreciate.
Many Thanks
JT

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You query the data the exact same way that you query a database that has only one MDF file. When you issue any SQL Statement you don’t have to know on which data file or data group the table or index were created.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

julie.tilley SSC Veteran Points: 280 More actions · Answer 2

Hi

Thanks for the advice, however, I still keep getting an invalid object name on the NDF File, the NDF file is HistCMSCallHist, and the Table it relates to in the database is oa.cmsCallHistory.

USE Ztest

GO

Select * from

HistCMSCallHist as hc Join oa.cmsCallHistory as ch

On ch.CallID = hc.CallID

Msg 208, Level 16, State 1, Line 1

Invalid object name 'HistCMSCallHist'.

Any help would be great I am desperate..

Thanks

JT

ps. One Orange Chip Points: 29423 More actions · Answer 3

julie.tilley (3/16/2009)
USE Ztest
GO
Select * from
HistCMSCallHist as hc Join oa.cmsCallHistory as ch
On ch.CallID = hc.CallID
Msg 208, Level 16, State 1, Line 1
Invalid object name 'HistCMSCallHist'.
Any help would be great I am desperate..
Thanks
JT

try prefixing the table name with the table owner's name.

Select * from

owner.HistCMSCallHist as hc Join oa.cmsCallHistory as ch

On ch.CallID = hc.CallID

Pradeep Singh

S. Kusen SSChampion Points: 10936 More actions · Answer 4

To the query, the NDF file wouldn't even really matter. It is an administrative function to logically separate out tables and data.

In SQL 2005, you can do table partitioning which can effectively take all your data in 1 table and spread it across multiple filegroups. to you, to would still only query "select col1 from table 1".

So, if the table that the NDF file stores is for oa.cmsCallHistory, you may or may not be hitting one or many of the NDF files when you query against that table.

I found this query on http://www.mssqltips.com/tip.asp?tip=1112 which has some other useful filegroup information that might help you out.

This query should tell you which objects exist in a particular filegroup:

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = 2 --* New FileGroup*

GO

Within each filegroup, there are MDF and NDF files (only 1 MDF per database, all other data files are NDF).

Hope that information helps!

Steve