Data Sources

Is there anyway to use a Class File (VB.Net or C# class that generates a
DataSet) as the data source for a SQL Server 2005 Reporting Services report?
If there is, where are some examples or how-to's?
The only obvious way I see to generate a Data Source for a SQL report is a
sproc or dynamic query.
Thank You.Alex,
You want to use a Custom Data Processing Extension. You can even tie
the extension to an existing data layer.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
Andy Potter

Data Source Provider Error: The event log file is full

I'm trying to process one of my cubes and am getting this error, see subject
line. Mt configuration is this: Development Web server Dul09 holds the cube
s. The data lives on a SQL server DryHou10. I have had my sysadmin check the
event logs on both serv
ers, neither of which is full. This setup has been working just fine for the
last few years. Other cubes on Dul09, using different SQL servers for data,
process just fine.
All servers are running SQL 2000TJSully wrote:
> *I'm trying to process one of my cubes and am getting this error, see
> subject line. Mt configuration is this: Development Web server Dul09
> holds the cubes. The data lives on a SQL server DryHou10. I have had
> my sysadmin check the event logs on both serv
> ers, neither of which is full. This setup has been working just fine
> for the last few years. Other cubes on Dul09, using different SQL
> servers for data, process just fine.
> All servers are running SQL 2000 *
annieckl
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message441896.html|||Are you sure that they are not full...I got the same error once and emptied
all my logs (you can put them into files if
you want to keep them) - it worked again after that.
--Michael
"annieckl" <annieckl.1dtcei@.mail.mcse.ms> skrev i en meddelelse
news:annieckl.1dtcei@.mail.mcse.ms...
> TJSully wrote:
>
> --
> annieckl
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message441896.html
>

Data Source file on Reporting Services server question

In the Reporting Services Manager, when the user selects the folder where the
reports reside there is a file that contains the connection string
information along with a user name and password (Data Source).
Is there a way to keep the file from being selected by the user ?
Thanks.There is a setting in the properties of the data source to "Hide in List
View". Unfortunately the users can just click on the "Show Details"
option and see the datasource. Depending on the permissions you gave
them they will only be able to view the information.
I sent an question out some time ago seeing if there was away to remove
the "Show Details" for some users but no one replied.
Mike wrote:
> In the Reporting Services Manager, when the user selects the folder where the
> reports reside there is a file that contains the connection string
> information along with a user name and password (Data Source).
> Is there a way to keep the file from being selected by the user ?
>
> Thanks.|||You can use a datasource in another location. It's a faff because you
have to change it manually for each deployed report affected. Also
everytime you deploy the report it will create the datasources in the
deployment folder if it can, so you have to remove it each time.
Having said that, this would essentially cure your problem.
Chris
Bon733 wrote:
> There is a setting in the properties of the data source to "Hide in
> List View". Unfortunately the users can just click on the "Show
> Details" option and see the datasource. Depending on the permissions
> you gave them they will only be able to view the information.
> I sent an question out some time ago seeing if there was away to
> remove the "Show Details" for some users but no one replied.
> Mike wrote:
> > In the Reporting Services Manager, when the user selects the folder
> > where the reports reside there is a file that contains the
> > connection string information along with a user name and password
> > (Data Source).
> >
> > Is there a way to keep the file from being selected by the user ?
> >
> >
> > Thanks.

Data source file name has a timestamp

I have 2 years worth of data that are stored in individual .dbf files for each day. Is there a way to 1 quickly import all of these tables into one and 2. move the timestamp from the file name to a date column?

Any help would be greatly appreciated

The For Each Loop has a file enumertor. You end up with the filename in a variable. You can then use this variable in two ways -

1 Use it in an expression for a connection, so that the connection string is changed to pick up the new file. You would use this connection for your source component, in a Data Flow within the loop itself.

2 Add a derived column transform in your data flow, and use the variable again, grab the date part and pass out in a new column.

Data Source

I have a problem connecting Visual Studio 2005 to my DataBase file. I've downloaded northwind.mdf . Three weeks ago I installed SQL express and I used Visual Studio in the following manner: From server Explorer tab I choose Add connection.. , Change data source and I selected Microsoft SQL Server Database File (SqlClient), next I've selected my NORTHWND.MDF and when I pressed Test connection , it succeded. Recently, I've purchsed SQL server developer edition. The problem is when I follow the steps above I reveive:

An error has occurred while establishing a connection to the server, When connecting to SQL Server 2005, this failture may be caused by the fact that under the default SQL Server does not allow remote connections. ( provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So , I've checked and changed remote connections to allow TCP/IP , but I receive the same error.

I've set up Visual Studio to SQL Server Instance Name : NAME , where NAME is the name assigned to my SQL Server. Worthless

The thing is it is working in another way : From server Explorer tab I choose Add connection.. , Change data source and I selected Microsoft SQL Server (SqlClient) , next I've selected server name to NAME , then Attach a database file . Test connection : succeded . This is because I do not have the northwind database attached directly in my SQL server ( and I do not want to ) .

Why with Developer edition I do not receive the same results ? Where is the mistake?

Thank you

Connecting to a database is a feature available only in SQL Server Express edition, but not in any other SQL Server version. This feature intention is to allow database applications development without the need to have a DBA who attaches the database files. For more information I would recommend to visit SQL Server 2005 Express Edition User Instances (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp).

If you want to use SQL Server developer edition, I would recommend attaching the database to SQL Server. For more information on this topic you can visit the following link: How to: Attach a Database File to SQL Server Express (http://msdn2.microsoft.com/en-us/library/ms165673.aspx).

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?
Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>
|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon
|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>

data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>sql

data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>

Data Pump Failing

I did not see a forum for the SQL Server 2000 DTS.

I have a flat file feeding a table via a data pump. The table is only used by this process. It will run for about 30minutes and then fail. The message in the history does not give any detail on why it is failing. Below is the message I get and if I rerun the job it works fine. Anyone help me please.

Date 07/23/2007 6:00:02 AM
Log Job History (Daily: Load EOL from MVS1 (First Run))

Step ID 1
Server PIT-CS-M608
Job Name Daily: Load EOL from MVS1 (First Run)
Step Name Daily: Load tblCaseMasterSched
Duration 00:28:05
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: PIT-CS-M608\SYSTEM. ...rt: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSStep_DTSDataP... Process Exit Code 1. The step failed.

DTS questions can be asked in the DTS newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Data problem when publishing website

I published my website directly to its production folder then opened Sql Express Management Studio and attached the ASPNETDB file to it in that folder. However, the location information displayed in the Databases window shows the file is actually mapped back to my development folder. ?

Has anyone else encountered this problem?

Yes, I did verify that I selected the correct location.

Okay, my mistake, it isn't actually mapped back to that file, it just names the database with the original path. Gotta wonder what genius thought that one up.

data not flowing out of flat file source

my package has a flat file source that should be extracting data from a text file passing the data to the next component in the data flow. the package validates fine, but the data isn't flowing. however, i see the data in the source component. i added a data viewer between the source and the next component to see if any data flowed and saw no data. can someone suggest how i should go about trying to debug this? thanks.Look at the output window. Anything of interest there?|||

Could you give more details about the flat file format and how you configured the flat file connection manager?

Thanks.

|||

DarrenSQLIS wrote:

Look at the output window. Anything of interest there?

below are some output lines that interest me:

Information: 0x40043007 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has started.
Information: 0x400490F4 at Data Flow Lockbox Validate File and Header Info, Lookup BankBatchID [373]: component "Lookup BankBatchID" (373) has cached 0 rows.

"Flat File Lockbox [1]" is the flat file source component. the flat file connection manager is connected to file "c:\casestudy\lockbox\samplelockbox.txt"|||

Bob Bojanic wrote:

Could you give more details about the flat file format and how you configured the flat file connection manager?

Thanks.

below is the entire contents of the text file:

H080105 B1239-99Z-99 0058730760
I4001010003 181INTERNAT
C4001010004 01844400
I4002020005 151METROSPOOO1
C4002020006 02331800
I4003030009 MAGIC CYCLES
C4003030010 02697000
I4004040013 LINDELL
C4004040014 02131800
I4005040017 151GMASKI0001
C4005040019 01938800

the general tab of the flat file connection manager is configured as follows:

file name: c:\casestudy\lockbox\samplelockbox.txt
locale: english (united states)
unicode: unchecked
code page: 1252 (ANSI - Latin I)
format: ragged right
text qualifier: <none>
header row delimiter: {CR}{LF}
header rows to skip: 0
column names in first data row: unchecked|||

Can you try this?

In a copy of your package, delete everything after the flat file source.

Add a row count component after the Flat File Source (see http://msdn2.microsoft.com/en-us/library/ms141136(SQL.90).aspx for the row count component)

Now you can run the data flow with nothing but the flat file - if data flows to the row count, then your source is ok.

The reason I ask is becuase I see that your lookup component cached 0 rows - I wonder if that is actually the issue. Lookup row caching occurs on pre-execute so if there is a problem there, your flat file source will never even get started.

Donald Farmer

|||donald,

i took your suggestion and copied my package. then, i deleted everything from the data flow task except a flat file source and a rowcount. i then ran this package and the data still wasn't flowing out of the flat file source. next, i deleted my data flow task and created a new data flow task. then, i added a flat file source and a rowcount to this new data flow. low and behold, that resolved the issue. it seems that my original data flow somehow became corrupted and that this prevented the data from flowing out of the flat file source. to me, this seems to be a bug. is there a way to repair my original data flow so that it won't be necessary for me to duplicate all of my previous work?|||

Well that's odd, for sure. You can select, copy and paste components from data flow to data flow, so you could try copying and pasting the rest of your original data flow into your new one and hooking it up. There will be messages about metadata needing fixed up, but it all is effectively identical it should be relatively easy to do so.

(Copy and back up that new data flow first of course.)

I don't really have any suggestions about what could have gone wrong. I do wonder if the new data flow is identical in all ways to the old one, but difficult to tell without examining them in detail.

Donald

|||

Donald Farmer wrote:

Well that's odd, for sure. You can select, copy and paste components from data flow to data flow, so you could try copying and pasting the rest of your original data flow into your new one and hooking it up. There will be messages about metadata needing fixed up, but it all is effectively identical it should be relatively easy to do so.

(Copy and back up that new data flow first of course.)

I don't really have any suggestions about what could have gone wrong. I do wonder if the new data flow is identical in all ways to the old one, but difficult to tell without examining them in detail.

Donald

donald,

you were correct about there being messages about metadata needing fixed up. below are the messages:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [DTS.Pipeline]: input column "line" (158) has lineage ID 28 that was not previously used in the Data Flow task.

Error at Data Flow Task [DTS.Pipeline]: "component "Derived Column Checks 1" (156)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

you previously stated that fixing this should be relatively easy to do so. so, how should i go about fixing this?|||

There should be a warning triangle in the components that need to be fixed up. Double click on those components to open the UI and the metadata may be fixed automatically, or you will be prompted with a mapping dialog to fix up the changes.

Donald

|||

Donald Farmer wrote:

There should be a warning triangle in the components that need to be fixed up. Double click on those components to open the UI and the metadata may be fixed automatically, or you will be prompted with a mapping dialog to fix up the changes.

Donald

ok, that worked. thanks for your assistance.

data mining viwer

i want to ask how i can deal with data mining viewr to viwe result

on excel file or on application (get just the chart dynimaclly )

( export or import it )

thanks

Can you please clarify your question?

When browsing existing model using DM Excel add-in you can copy data from viewer to Excel spreadsheet by clicking "Copy to Excel" button. Is this what you are looking for?

(Without add-ins, you can copy viewer data to the clipboard and paste it to other applications such as Excel, Word, notepad etc).

Tatyana

|||first thanks in know after i put the Q about add ins
but without add-ins how i can copy viewer data to the clipboard in dynamic way
not manual

my regards
|||

I will be able to help you better if you tell me more about your scenario and what you are trying to achieve.

Do you have viewer hosted inside your application and you want to get information from the viewer to present it differently?

May be you can call the same store procedures viewers are calling to get the information?

Tatyana

data mining viwer

i want to ask how i can deal with data mining viewr to viwe result

on excel file or on application (get just the chart dynimaclly )

( export or import it )

thanks

Can you please clarify your question?

When browsing existing model using DM Excel add-in you can copy data from viewer to Excel spreadsheet by clicking "Copy to Excel" button. Is this what you are looking for?

(Without add-ins, you can copy viewer data to the clipboard and paste it to other applications such as Excel, Word, notepad etc).

Tatyana

|||first thanks in know after i put the Q about add ins
but without add-ins how i can copy viewer data to the clipboard in dynamic way
not manual

my regards
|||

I will be able to help you better if you tell me more about your scenario and what you are trying to achieve.

Do you have viewer hosted inside your application and you want to get information from the viewer to present it differently?

May be you can call the same store procedures viewers are calling to get the information?

Tatyana

Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Because of update issue we need to merge data from an already installed database into the new compact edition. Are there some tools we could use in our installation routine (InstallShield) or are there some recommendations how we could do this data merging?

Thanks @. all
Daniel

Hello Daniel,

I am going to begin investigating this issue myself very soon and I'll be more than happy to provide you with any info. I'm amazed that nobody has gotten to you, especially since you made this post well over a month ago. Have you received any information from others that might be relevant here? If so, I'd really like to hear your input.

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Because of update issue we need to merge data from an already installed database into the new compact edition. Are there some tools we could use in our installation routine (InstallShield) or are there some recommendations how we could do this data merging?

Thanks @. all
Daniel

Hello Daniel,

I am going to begin investigating this issue myself very soon and I'll be more than happy to provide you with any info. I'm amazed that nobody has gotten to you, especially since you made this post well over a month ago. Have you received any information from others that might be relevant here? If so, I'd really like to hear your input.

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

Data migration

Dear all
I want to know if sql server 2000 supports 2-bytes language, say Chinese? I
have an Excel file with Chinese characters in it, after imported into sql
server table using dts, all Chinese become question mark(?).
How should I do?
--
Best regards,
RichardSee my reply in .dts
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"lurc" <lurchot@.hotmail.com> wrote in message
news:uSsMTkazDHA.540@.tk2msftngp13.phx.gbl...
> Dear all
> I want to know if sql server 2000 supports 2-bytes language, say Chinese?
I
> have an Excel file with Chinese characters in it, after imported into sql
> server table using dts, all Chinese become question mark(?).
> How should I do?
> --
> Best regards,
> Richard
>

Data Mart Maintenance

The database maintenance on our data mart SQL server is causing problems.The re-index process causes too much file growth and the processes are unable to complete due to disk space constraints.I want to write a process to re-index the database, but handle this a single file group at a time and then shrink the file group before going to the next one. I'm looking to write a maintenance routine (stored procedure) that will get all of the tables from a file group and re-index them. Any general ideas would be greatly appreciated.

I think I've found a reindexing solution to this problem I posted. I'm posting an answer here and if anyone wants to comment thats fine but I am doing it to help out other developers out there. If you scroll down farther you can see that I only reindexed clustered indexes in this Database and that is because when you reindex clustered indexes, tables with non-clustered indexes are automatically reindexed as well I believe.

-- cursor to loop through File Groups

DECLARE FG CURSOR FOR

SELECT DISTINCT groupid

FROM sysfilegroups

OPEN FG

FETCH NEXT FROM FG INTO @.fgroupid

WHILE @.@.Fetch_status=0

BEGIN

-- Cursor to loop through filenames

DECLARE FILENAME CURSOR FOR

SELECT DISTINCT f.name

FROM sysfiles f

INNER JOIN sysfilegroups fg ON f.groupid = @.fgroupid

OPEN FILENAME

FETCH NEXT FROM FILENAME INTO @.fname

WHILE @.@.Fetch_status=0

BEGIN

PRINT 'Current Filename = ' + @.fname

--Table Name Cursor

DECLARE TBL CURSOR FOR

--This query selects tables with clustered indexes only

SELECT DISTINCT 'DBCC DBREINDEX (' + i.TABLE_NAME + ')'

FROM INFORMATION_SCHEMA.TABLES i

INNER JOIN sysindexes si ON i.TABLE_NAME = object_name(si.id)

INNER JOIN sysfiles sf ON sf.groupid = si.groupid

WHERE objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1

AND objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex')=1

AND sf.filename=@.fname

OPEN TBL

FETCH NEXT FROM TBL INTO @.tname

WHILE @.@.Fetch_status=0

BEGIN

PRINT @.SQLA

EXEC (@.SQLA)

FETCH NEXT FROM TBL INTO @.SQLA

END

CLOSE TBL

DEALLOCATE TBL

FETCH NEXT FROM FILENAME INTO @.fname

END

SELECT @.SQLB = 'DBCC SHRINKFILE ('+ @.fname+ ')'

EXEC (@.SQLB)

CLOSE FILENAME

DEALLOCATE FILENAME

FETCH NEXT FROM FG INTO @.fgroupid

Data Mart Maintenance

The database maintenance on our data mart SQL server is causing problems.The re-index process causes too much file growth and the processes are unable to complete due to disk space constraints.I want to write a process to re-index the database, but handle this a single file group at a time and then shrink the file group before going to the next one. I'm looking to write a maintenance routine (stored procedure) that will get all of the tables from a file group and re-index them. Any general ideas would be greatly appreciated.

I think I've found a reindexing solution to this problem I posted. I'm posting an answer here and if anyone wants to comment thats fine but I am doing it to help out other developers out there. If you scroll down farther you can see that I only reindexed clustered indexes in this Database and that is because when you reindex clustered indexes, tables with non-clustered indexes are automatically reindexed as well I believe.

-- cursor to loop through File Groups

DECLARE FG CURSOR FOR

SELECT DISTINCT groupid

FROM sysfilegroups

OPEN FG

FETCH NEXT FROM FG INTO @.fgroupid

WHILE @.@.Fetch_status=0

BEGIN

-- Cursor to loop through filenames

DECLARE FILENAME CURSOR FOR

SELECT DISTINCT f.name

FROM sysfiles f

INNER JOIN sysfilegroups fg ON f.groupid = @.fgroupid

OPEN FILENAME

FETCH NEXT FROM FILENAME INTO @.fname

WHILE @.@.Fetch_status=0

BEGIN

PRINT 'Current Filename = ' + @.fname

--Table Name Cursor

DECLARE TBL CURSOR FOR

--This query selects tables with clustered indexes only

SELECT DISTINCT 'DBCC DBREINDEX (' + i.TABLE_NAME + ')'

FROM INFORMATION_SCHEMA.TABLES i

INNER JOIN sysindexes si ON i.TABLE_NAME = object_name(si.id)

INNER JOIN sysfiles sf ON sf.groupid = si.groupid

WHERE objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1

AND objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex')=1

AND sf.filename=@.fname

OPEN TBL

FETCH NEXT FROM TBL INTO @.tname

WHILE @.@.Fetch_status=0

BEGIN

PRINT @.SQLA

EXEC (@.SQLA)

FETCH NEXT FROM TBL INTO @.SQLA

END

CLOSE TBL

DEALLOCATE TBL

FETCH NEXT FROM FILENAME INTO @.fname

END

SELECT @.SQLB = 'DBCC SHRINKFILE ('+ @.fname+ ')'

EXEC (@.SQLB)

CLOSE FILENAME

DEALLOCATE FILENAME

FETCH NEXT FROM FG INTO @.fgroupid

Data Lost , Help !!!

i make an application to insert data to sdf file and save it

when i colse the application and open it again i find my stored data on the sdf file well

and if i closed the application 1000 times i'll find my data ok

that's ok and that's not the problem

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

Any Ideas Here ?

I had a similar problem when I first used SQL CE

1. Copied the demo Northwind.sdf into its own directory
2. Added it to my VS2005 project
3. Made changes during testing
4. Data not there when viewed from SQL Server Management tool

After that initial moment of panic I realised that VS2005 takes a COPY of the sdf file and puts it into the project directory. Tra la - that is where the updates went.

In VS2005 project put mouse over the .sdf file to get the address and then look there for the data.

Good luck|||

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

do u c what i wrote above in my case

ur answer isn't related to the problem i'm facing

but thx alot for help

but i know that vs2005 take a copy to the local folder and use it and it's optional cause vs2005 asks u if u wanna do this or not before copying the file to the local folder

thx alot

hey there any ideas

i'm gonna crazy

Data Lost , Help !!!

i make an application to insert data to sdf file and save it

when i colse the application and open it again i find my stored data on the sdf file well

and if i closed the application 1000 times i'll find my data ok

that's ok and that's not the problem

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

Any Ideas Here ?

I had a similar problem when I first used SQL CE

1. Copied the demo Northwind.sdf into its own directory
2. Added it to my VS2005 project
3. Made changes during testing
4. Data not there when viewed from SQL Server Management tool

After that initial moment of panic I realised that VS2005 takes a COPY of the sdf file and puts it into the project directory. Tra la - that is where the updates went.

In VS2005 project put mouse over the .sdf file to get the address and then look there for the data.

Good luck|||

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

do u c what i wrote above in my case

ur answer isn't related to the problem i'm facing

but thx alot for help

but i know that vs2005 take a copy to the local folder and use it and it's optional cause vs2005 asks u if u wanna do this or not before copying the file to the local folder

thx alot

hey there any ideas

i'm gonna crazy