How do I move a web assistant job?

Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>

How do I move a web assistant job?

Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>sql

How do I move a project between machines?

Hi,

I'm an SQL Server novice.

After developing some websites (VS 2005 VWD), my machine started to crumble. (e.g, SQL Server won't uninstall and lots of other software problems)

So I bought a new machine and set up a clean installation of SQL Server 2005 Standard and Visual Studio 2005 standard.

I copied my old "projects" folder to the new machine but I cannot use it.

1: When I try to import I get a "cannot import external files" error.

2: When I click on a project, it launches VS 2005 and I can edit etc.
However, when I try to run the website in VS 2005, I get the following error.

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

Please can someone let me know how I can get my "old" projects running in my new environment?

Thanks

Ken

Has your sql server name or instance changed if so you will have to change your dataset to represent the new server.|||

Hi Ray,

Thanks for the reply.
Yes - I'm using a completely new setup. New server name and new instance name.

But I'm still struggling to get to grips with the management console. I bought several books on SQL Server 2005 and spent lots of time ploughing through the zillions of help files but I have not yet found a "How to" that meets my level of need to understand what I'm doing with SQL Serverr Management Studio.

So, when you say "change the dataset" I don't know what steps to take to do that. If you have a minute, I would very much appreciate some basic guide that shows me how to "change the dataset to represent the new server".

Its hard being a novice!

Ken

|||In your visual studios project you should have set up your project to connect to the database this is a dataset. It is a setup for a connection to the database from visual studios. You should look in your project, and should be somewhere in your solution depending on which type you are creating.|||

Hi Ray,

Thanks for your comment.

One of the problems I'm experienceing is that most of the well intended advice is couched in metaphorical terms rather than in "Press this then click that" terms.

Let's use the sample SQL Express personal website starter kit as an example.

Which DB?: In the App_Data folder thare are two .mdf files: ASPNETDB.MDF and Personal.mdf I know that the application needs both of these DB's. If I change the connection string, do I have to do it for both of these DB's ?

Connection String?: I have been floundering about in the help files without finding anything that I have found to be helpful. Where do I find the "connection string" to these DB's? what does a connection string look like? , How do I change it? What effect does it have on the rest of the project? Do I have to do anything else to make the app run under SQL Server Standard?

If I can get answers to these questions it will help me to get moving forward.

Thanks for your help.

Ken

How do I move a project between machines?

Hi,

I'm an SQL Server novice.

After developing some websites (VS 2005 VWD), my machine started to crumble. (e.g, SQL Server won't uninstall and lots of other software problems)

So I bought a new machine and set up a clean installation of SQL Server 2005 Standard and Visual Studio 2005 standard.

I copied my old "projects" folder to the new machine but I cannot use it.

1: When I try to import I get a "cannot import external files" error.

2: When I click on a project, it launches VS 2005 and I can edit etc.
However, when I try to run the website in VS 2005, I get the following error.

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

Please can someone let me know how I can get my "old" projects running in my new environment?

Thanks

Ken

Has your sql server name or instance changed if so you will have to change your dataset to represent the new server.|||

Hi Ray,

Thanks for the reply.
Yes - I'm using a completely new setup. New server name and new instance name.

But I'm still struggling to get to grips with the management console. I bought several books on SQL Server 2005 and spent lots of time ploughing through the zillions of help files but I have not yet found a "How to" that meets my level of need to understand what I'm doing with SQL Serverr Management Studio.

So, when you say "change the dataset" I don't know what steps to take to do that. If you have a minute, I would very much appreciate some basic guide that shows me how to "change the dataset to represent the new server".

Its hard being a novice!

Ken

|||In your visual studios project you should have set up your project to connect to the database this is a dataset. It is a setup for a connection to the database from visual studios. You should look in your project, and should be somewhere in your solution depending on which type you are creating.|||

Hi Ray,

Thanks for your comment.

One of the problems I'm experienceing is that most of the well intended advice is couched in metaphorical terms rather than in "Press this then click that" terms.

Let's use the sample SQL Express personal website starter kit as an example.

Which DB?: In the App_Data folder thare are two .mdf files: ASPNETDB.MDF and Personal.mdf I know that the application needs both of these DB's. If I change the connection string, do I have to do it for both of these DB's ?

Connection String?: I have been floundering about in the help files without finding anything that I have found to be helpful. Where do I find the "connection string" to these DB's? what does a connection string look like? , How do I change it? What effect does it have on the rest of the project? Do I have to do anything else to make the app run under SQL Server Standard?

If I can get answers to these questions it will help me to get moving forward.

Thanks for your help.

Ken

How do I move a database I have developed on my local server (2005) to my client?

Hi all. Probably a simple thing to do but I need to know how to do this. I
have to move the database I created in 2005 Enterprise to 2005 Express on my
client site. Do I just do a back up and burn to a CD and after installing
2005 Express restore the backup? Don't I need the .mdf and the .ldf files
or does the backup procedure in the Studio backup both? I set it to backup
the Database.
Regards,
RobertYou can A) drop the database, copy the mdf and ldf files to CD or
somewhere that you can move them and at the client site attach them or
b) do a backup of the database and restore at the client site. Did you
use any Enterprise features that Express may not support?
Robert Johnson wrote:
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on my
> client site. Do I just do a back up and burn to a CD and after installing
> 2005 Express restore the backup? Don't I need the .mdf and the .ldf files
> or does the backup procedure in the Studio backup both? I set it to backup
> the Database.
> Regards,
> Robert
>|||I don't believe so.. Just added tables and one view. No triggers, functions
or stuff like that. Relationships between tables and constraints are in
place. The client has less than 5 concurent users so Express should work
fine for him. It's a very small client and small database needs.
Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
think that would work. I'll give that a shot.
Regards and Thanks
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
> You can A) drop the database, copy the mdf and ldf files to CD or
> somewhere that you can move them and at the client site attach them or b)
> do a backup of the database and restore at the client site. Did you use
> any Enterprise features that Express may not support?
>
> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert|||I had to ask <G>. I use the attach/detach all the time. After I've
copied the ldf and mdf I reattach to the original db. That's how I move
from our test to production server.
Robert Johnson wrote:
> I don't believe so.. Just added tables and one view. No triggers, functions
> or stuff like that. Relationships between tables and constraints are in
> place. The client has less than 5 concurent users so Express should work
> fine for him. It's a very small client and small database needs.
> Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
> think that would work. I'll give that a shot.
> Regards and Thanks
> Robert
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or b)
>> do a backup of the database and restore at the client site. Did you use
>> any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>|||Thanks again Brett. I'll test it out tomorrow on site. If your ears are
burning...lol.
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:eKtpB4UBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>I had to ask <G>. I use the attach/detach all the time. After I've copied
>the ldf and mdf I reattach to the original db. That's how I move from our
>test to production server.
> Robert Johnson wrote:
>> I don't believe so.. Just added tables and one view. No triggers,
>> functions or stuff like that. Relationships between tables and
>> constraints are in place. The client has less than 5 concurent users so
>> Express should work fine for him. It's a very small client and small
>> database needs. Interesting idea about detaching and copy the mdf and ldf
>> to CD. Didn't think that would work. I'll give that a shot.
>> Regards and Thanks
>> Robert
>> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
>> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or
>> b) do a backup of the database and restore at the client site. Did you
>> use any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do
>> this. I have to move the database I created in 2005 Enterprise to 2005
>> Express on my client site. Do I just do a back up and burn to a CD and
>> after installing 2005 Express restore the backup? Don't I need the
>> .mdf and the .ldf files or does the backup procedure in the Studio
>> backup both? I set it to backup the Database.
>> Regards,
>> Robert
>>|||Hello Robert,
When you take a backup of a database, you do not need mdf or ldf files of
your database. *.BAK files (which is the backup file) contains all that
files.
This is a comman way used to copy databases or as Brett mentioned, you can
attach\detach your database files.
For additional info: Attaching and Detaching a database is the fastest
method to copy or move a database. However, it takes your database offline
and this would not be good for production environments. So, backing up a
database does not cause a problem like this.
Ekrem Önsoy
"Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on
> my client site. Do I just do a back up and burn to a CD and after
> installing 2005 Express restore the backup? Don't I need the .mdf and the
> .ldf files or does the backup procedure in the Studio backup both? I set
> it to backup the Database.
> Regards,
> Robert
>|||Thanks Ekrem. I wasn't sure if it had all that I needed.
Regards,
Robert
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:F98D9090-F8AE-4DF2-AEFC-30898DE47197@.microsoft.com...
> Hello Robert,
>
> When you take a backup of a database, you do not need mdf or ldf files of
> your database. *.BAK files (which is the backup file) contains all that
> files.
> This is a comman way used to copy databases or as Brett mentioned, you can
> attach\detach your database files.
> For additional info: Attaching and Detaching a database is the fastest
> method to copy or move a database. However, it takes your database offline
> and this would not be good for production environments. So, backing up a
> database does not cause a problem like this.
>
> --
> Ekrem Önsoy
>
> "Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
> news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>>
>

How do I monitor my SQL JOB?

Hullo. I have a SQL SCHEDULER Job that keeps hanging.

1) How do I monitor this? I would like to have another Sql Job monitor the first one hourly, and send me an email if it is hanging. Isn't there a system table that tells me a job status?

1a) Once I find the job, how can I "Stop it" automatically?

2) Is there a way to have the Job stop itself it it runs for more than 20 minutes?

Thanks.

~LeNo one Knows...?

~Le

"No one knows? No one Knows if the Hot Tea is Hot or Cold...?"
"Is it Iced Tea?"
"NO!"
"Well then, I have no idea."|||Well, I have a suspision that most dba's would probably want to spend their time investigating the cause of a 'hanging' job rather than developing other jobs to manage such a situation. I think it's more correct to say that a job step stays 'in-progress'. The only time I have seen this is in relation to replication jobs that seem to have a tendency to report being continiously 'in-progress'. Apart from that 'feature', I can't remember a situation where a job would routinely misbehave without a good reason.

Have you looked at the job history to see which step the job is failing on? I know it's not much use if it's a one step job but I thought I would mention it anyway. Either way, look at the code in the step that is staying 'in-progress' and see if you can isolate it further. Perhaps add a few audit steps to write out to a table (like print statements if you were running code via QA). The objective should be to keep drilling down until you've found the cause.

Perhaps post more detail about the code that is staying in-progress and more useful feedback will come your way.

Clive|||Well, I have a suspision that most dba's would probably want to spend their time investigating the cause of a 'hanging' job rather than developing other jobs to manage such a situation. I think it's more correct to say that a job step stays 'in-progress'. The only time I have seen this is in relation to replication jobs that seem to have a tendency to report being continiously 'in-progress'. Apart from that 'feature', I can't remember a situation where a job would routinely misbehave without a good reason.

Have you looked at the job history to see which step the job is failing on? I know it's not much use if it's a one step job but I thought I would mention it anyway. Either way, look at the code in the step that is staying 'in-progress' and see if you can isolate it further. Perhaps add a few audit steps to write out to a table (like print statements if you were running code via QA). The objective should be to keep drilling down until you've found the cause.

Perhaps post more detail about the code that is staying in-progress and more useful feedback will come your way.

Clive

LOL. You are right, the better thing to do would be to stop it from hanging in the first place! The SQL Agent fires off a VB App that I wrote. The VB App is hanging on the the custom PGP command. But it does not happen often. The PGP 8 Service itself seems to be hanging about every 13-14 days, which in turn causes my VB App to hang, which causes the SQL Agent to hang.

Since there is a code freeze, I cannot edit the VB app to trap the error at the moment. So I just want to monitor the SQL Agent jobs, and if possible stop it if it is hanging.

`Le|||You can find what jobs are running by looking in the sysprocesses table for jobs running:

select *
from master..sysprocesses
where program_name like 'SQLAgent%Job%'

The problem is that the job_id you get: 0xC778DE2759DD354B9C219B301886EA43
equates to the binary version of the job_id stored in the sysjobs table.

Still if you know you shouldn't have any jobs running at all except the one you are checking on to see if it hung, you should be able to use this select to get the SPID of the hanging job and kill it if need be until you can figure out what's causing it to fail.

How do I monitor MSDE performance

Hi there,
How can I monitor the MSDE performance. I read about it
on the MSDN site but was not clear what tool to use.
Can u help?
Thanks,
FP
hi FP,
FP wrote:
> Hi there,
> How can I monitor the MSDE performance. I read about it
> on the MSDN site but was not clear what tool to use.
> Can u help?
depending on what you want to monitor, you can even use the System Monitor,
adding your preferred counters, like
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

How do I monitor mirroring?

SQL2K5 SP1.
I set up mirroring and the target DB now says (Mirror, Synchronized /
Restoring) but it would seem as though there should be a way for monitor as
well? To make sure its staying in synch, doesnt fail, etc?
TIA, ChrisRChris,
There is an entire section named 'Monitoring Database Mirroring' on BOL
April 2006. Take a look at it.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:
> SQL2K5 SP1.
> I set up mirroring and the target DB now says (Mirror, Synchronized /
> Restoring) but it would seem as though there should be a way for monitor as
> well? To make sure its staying in synch, doesnt fail, etc?
> TIA, ChrisR
>
>

How do I monitor mirroring?

Chris,
There is an entire section named 'Monitoring Database Mirroring' on BOL
April 2006. Take a look at it.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:

> SQL2K5 SP1.
> I set up mirroring and the target DB now says (Mirror, Synchronized /
> Restoring) but it would seem as though there should be a way for monitor a
s
> well? To make sure its staying in synch, doesnt fail, etc?
> TIA, ChrisR
>
>SQL2K5 SP1.
I set up mirroring and the target DB now says (Mirror, Synchronized /
Restoring) but it would seem as though there should be a way for monitor as
well? To make sure its staying in synch, doesnt fail, etc?
TIA, ChrisR|||Chris,
There is an entire section named 'Monitoring Database Mirroring' on BOL
April 2006. Take a look at it.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ChrisR" wrote:

> SQL2K5 SP1.
> I set up mirroring and the target DB now says (Mirror, Synchronized /
> Restoring) but it would seem as though there should be a way for monitor a
s
> well? To make sure its staying in synch, doesnt fail, etc?
> TIA, ChrisR
>
>

How do I Modify a Stored Procedure?

I am using the sql server express manager and when I right click on
stored procedure I have a modify option. I use this, make my changes,
and hit save. But is always saves a separate .sql file and never
changes the original stored procedure. What am I doing wrong? How can
I modify the stored procedure in my database? Thank you for any help.
You have to execute the changing script, NOT saving it, which always
saves the script to a file.
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||Jens wrote:
> You have to execute the changing script, NOT saving it, which always
> saves the script to a file.
> HTH; Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
So you are saying that when I hit modify, change the code, and then hit
Execute (!) and that automatically saves it?
|||needin4mation@.gmail.com wrote:
> Jens wrote:
> So you are saying that when I hit modify, change the code, and then hit
> Execute (!) and that automatically saves it?
>
It's not correct wording to say it's being saved. As Jens says, you'll
have to execute the new stored procedure to get it added to the
database, but that's not the same as saving it...:-). When you save it,
it's being saved as a regular sql file but that will not do anything to
the actual SP in the database.
Regards
Steen
|||right.

How do I Modify a Stored Procedure?

I am using the sql server express manager and when I right click on
stored procedure I have a modify option. I use this, make my changes,
and hit save. But is always saves a separate .sql file and never
changes the original stored procedure. What am I doing wrong? How can
I modify the stored procedure in my database? Thank you for any help.You have to execute the changing script, NOT saving it, which always
saves the script to a file.
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens wrote:
> You have to execute the changing script, NOT saving it, which always
> saves the script to a file.
> HTH; Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
So you are saying that when I hit modify, change the code, and then hit
Execute (!) and that automatically saves it?|||needin4mation@.gmail.com wrote:
> Jens wrote:
> So you are saying that when I hit modify, change the code, and then hit
> Execute (!) and that automatically saves it?
>
It's not correct wording to say it's being saved. As Jens says, you'll
have to execute the new stored procedure to get it added to the
database, but that's not the same as saving it...:-). When you save it,
it's being saved as a regular sql file but that will not do anything to
the actual SP in the database.
Regards
Steen|||right.

How do I Modify a Stored Procedure?

I am using the sql server express manager and when I right click on
stored procedure I have a modify option. I use this, make my changes,
and hit save. But is always saves a separate .sql file and never
changes the original stored procedure. What am I doing wrong? How can
I modify the stored procedure in my database? Thank you for any help.You have to execute the changing script, NOT saving it, which always
saves the script to a file.
HTH; Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Jens wrote:
> You have to execute the changing script, NOT saving it, which always
> saves the script to a file.
> HTH; Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
So you are saying that when I hit modify, change the code, and then hit
Execute (!) and that automatically saves it?|||needin4mation@.gmail.com wrote:
> Jens wrote:
>> You have to execute the changing script, NOT saving it, which always
>> saves the script to a file.
>> HTH; Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
> So you are saying that when I hit modify, change the code, and then hit
> Execute (!) and that automatically saves it?
>
It's not correct wording to say it's being saved. As Jens says, you'll
have to execute the new stored procedure to get it added to the
database, but that's not the same as saving it...:-). When you save it,
it's being saved as a regular sql file but that will not do anything to
the actual SP in the database.
Regards
Steen|||right.sql

How do I model Slowly Changing Hierarchy?

We have an anomaly where over time the hierarchy of a dimension
changes. With that, we are only allowed to have "1" hierarchy. It is
basically an organization hierarchy where people get promoted, demoted
and change ranks over time. The business requirement is to be able to
see that 1 appropriate hierarchy over time and the hierarchy changes.
I know all about slowly changing dimensions and know how to manage
those. But what about hierarchys. We would like to use a Type 2 but
really need some pointers in how to model this.
Thanks,
Rico
Slowly Changing Dimensions Type 2
Hello Rico,
Type 2 is relatively easy to implement, but it can be a pain for users
to understand and use.
Implementing Type 2
Generally I use a set of standard flags to track the changes in the
dimension. Each flag is used to determine the state of the record. I am
assuming that you are using Surrogate keys in your dimensions and Fact
tables.
Date From -The date the record arrived in the dimension
Date To - The date the record is deemed to be changed
Current Flag -The State of the record Y or N
With these flags you can track changes of the dimension at the lowest
level such as employee changes.
e.g.
Employee Dimension
EmployeeKeyEmployeeID EmployeeNameEmployeeJobTitleManager
DateFrom DateTo CurrentFlag
1001SD00301Billy Bob Sales RepKate
Hawkins01/01/200412/05/2005N
1200SD00301Billy Bob Sales RepJohn
Simon12/05/200512/08/2005N
1250SD00301Billy Bob Sales ManagerJohn
Simon12/08/200512/08/2005Y
You will have to come up with at change capture process. If you have
type 1 deployed already it would be the same logic for identifying the
changes, with the exception of updating old records and a creating new
records in the dimension. Remember you will have to update your
surrogate key lookup in you fact table build to load with the current
dimension record. I.E. "WHERE Current Flag ='Y' "
Implementing Type 2 without Flags
To be honest I have not tried this method but it worth considering if
your Business users have problems reporting using the Type 2 Flags.
Rather than having one Employee Dimension create a series of mini
dimensions against the fact table. This would allow you to track
changes against the fact record instead of the Dimension.
E.G.
Employee Dim is broken into three new dimensions
Dim Manager
Dim Employee
Dim Job Title
There is a HUGE draw back to this you end up a large amount of
dimensions and a really wide fact table if you have to include a lot of
them.
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hello Rico,
The table in this post has come out a bit crap. I have reposted it on
Blog.
see:
http://bi-on-sql-server.blogspot.com...-changing.html
hope this helps
Myles
|||Hi Rico,
the particular question is one of the most important yet poorly
understood questions in all of BI. I have written extensively on this
topic on the DWLIST (www.datawarehousing.com).
The 'publicly available answer' is build a type 2 dimension that can be
linked to facts where you need it. This shows you the organisation
hierarchy at the point in time.
However, this is useless when answering the 'BIG QUESTION' from the
CEO/CFO/Director of marketing.
The 'BIG QUESTION' is.....now we have re-organised how are we doing
this year vs last year?
And these guys do not like the answer..."Well, our DW only shows us
the organisation hierarchy at the current point in time so we cannot
really compare this year vs last year."
So, to be able to do this you need to maintain an archive of the
organisation structure so that you can build what are called 'hot
swappable dimensions' to be able to compare last years results against
this years structure and this years results against last years
structure etc.
The 'Sybase wants you to pay' answer is that in the Sybase models
(sorry MSFT folk) there is a technique that allows you to compare the
value of anything across any point in time...so you could compare
transactions against the organisational hierarchy across any point in
time with the exception that people who were not there at that point in
time might show up as 'unknown'. (I implement Sybase IWS for a
living...as far as I am aware MSFT does not have a similar data model
offering today.)
So, the best you can go with from public infomation is type 2 hot
swappable dimensions...you can search Ralph Kimballs web page and tips
for details on hot swappable dimensions and I think they are still in
his data modeling book...
Best Regards
Peter Nolan
www.peternolan.com

How do I model Slowly Changing Hierarchy?

We have an anomaly where over time the hierarchy of a dimension
changes. With that, we are only allowed to have "1" hierarchy. It is
basically an organization hierarchy where people get promoted, demoted
and change ranks over time. The business requirement is to be able to
see that 1 appropriate hierarchy over time and the hierarchy changes.
I know all about slowly changing dimensions and know how to manage
those. But what about hierarchys. We would like to use a Type 2 but
really need some pointers in how to model this.
Thanks,
RicoSlowly Changing Dimensions Type 2
Hello Rico,
Type 2 is relatively easy to implement, but it can be a pain for users
to understand and use.
Implementing Type 2
Generally I use a set of standard flags to track the changes in the
dimension. Each flag is used to determine the state of the record. I am
assuming that you are using Surrogate keys in your dimensions and Fact
tables.
Date From - The date the record arrived in the dimension
Date To - The date the record is deemed to be changed
Current Flag - The State of the record Y or N
With these flags you can track changes of the dimension at the lowest
level such as employee changes.
e.g.
Employee Dimension
EmployeeKey EmployeeID EmployeeName EmployeeJobTitle Manager
DateFrom DateTo CurrentFlag
1001 SD00301 Billy Bob Sales Rep Kate
Hawkins 01/01/2004 12/05/2005 N
1200 SD00301 Billy Bob Sales Rep John
Simon 12/05/2005 12/08/2005 N
1250 SD00301 Billy Bob Sales Manager John
Simon 12/08/2005 12/08/2005 Y
You will have to come up with at change capture process. If you have
type 1 deployed already it would be the same logic for identifying the
changes, with the exception of updating old records and a creating new
records in the dimension. Remember you will have to update your
surrogate key lookup in you fact table build to load with the current
dimension record. I.E. "WHERE Current Flag ='Y' "
Implementing Type 2 without Flags
To be honest I have not tried this method but it worth considering if
your Business users have problems reporting using the Type 2 Flags.
Rather than having one Employee Dimension create a series of mini
dimensions against the fact table. This would allow you to track
changes against the fact record instead of the Dimension.
E.G.
Employee Dim is broken into three new dimensions
Dim Manager
Dim Employee
Dim Job Title
There is a HUGE draw back to this you end up a large amount of
dimensions and a really wide fact table if you have to include a lot of
them.
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||Hello Rico,
The table in this post has come out a bit crap. I have reposted it on
Blog.
see:
[url]http://bi-on-sql-server.blogspot.com/2005/07/news-group-post-slowly-changing.html[
/url]
hope this helps
Myles|||Hi Rico,
the particular question is one of the most important yet poorly
understood questions in all of BI. I have written extensively on this
topic on the DWLIST (www.datawarehousing.com).
The 'publicly available answer' is build a type 2 dimension that can be
linked to facts where you need it. This shows you the organisation
hierarchy at the point in time.
However, this is useless when answering the 'BIG QUESTION' from the
CEO/CFO/Director of marketing.
The 'BIG QUESTION' is.....now we have re-organised how are we doing
this year vs last year?
And these guys do not like the answer..."Well, our DW only shows us
the organisation hierarchy at the current point in time so we cannot
really compare this year vs last year."
So, to be able to do this you need to maintain an archive of the
organisation structure so that you can build what are called 'hot
swappable dimensions' to be able to compare last years results against
this years structure and this years results against last years
structure etc.
The 'Sybase wants you to pay' answer is that in the Sybase models
(sorry MSFT folk) there is a technique that allows you to compare the
value of anything across any point in time...so you could compare
transactions against the organisational hierarchy across any point in
time with the exception that people who were not there at that point in
time might show up as 'unknown'. (I implement Sybase IWS for a
living...as far as I am aware MSFT does not have a similar data model
offering today.)
So, the best you can go with from public infomation is type 2 hot
swappable dimensions...you can search Ralph Kimballs web page and tips
for details on hot swappable dimensions and I think they are still in
his data modeling book...
Best Regards
Peter Nolan
www.peternolan.com

How do I mimic autoNumber for non-identity columns?

Assume I have an inventory system used by several customers. Each customer
wants each item in their inventory to have a number, and they don't want any
gaps.
So, there's an Item table that has information about each item, as well as
the customer the item belongs to. When an item is inserted, I cannot use an
Identity column to autonumber the ItemId because if a customer inserts an
item, then a second customer inserts a hundred items, then the original
customer inserts another item, there is a gap of one hundred items from the
perspective of the original customer. This isn't desired behavior.
I need a way to do a per-customer autonumber, but I don't know how to do
this without running into concurrency problems.Greg, If I understood you properly you need
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
WHERE custid=.....
INSERT INTO AnothetTable VALUES (@.max_item)
COMMIT TRAN
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
> Assume I have an inventory system used by several customers. Each
> customer
> wants each item in their inventory to have a number, and they don't want
> any
> gaps.
> So, there's an Item table that has information about each item, as well as
> the customer the item belongs to. When an item is inserted, I cannot use
> an
> Identity column to autonumber the ItemId because if a customer inserts an
> item, then a second customer inserts a hundred items, then the original
> customer inserts another item, there is a gap of one hundred items from
> the
> perspective of the original customer. This isn't desired behavior.
> I need a way to do a per-customer autonumber, but I don't know how to do
> this without running into concurrency problems.|||If Table can be the same as AnotherTable, I think this could work. So,
assuming ItemNumber is the column I want to mimic autonumber on, we'd have:
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(ItenNumber),0) FROM Inventory WITH
(UPDLOCK,HOLDLOCK)
WHERE custid=4
INSERT INTO Inventory VALUES (@.max_item + 1)
COMMIT TRAN
Would that work? Are UPDLOCK and HOLDLOCK merely hints? What if the hints
get ignored?
Thanks.
"Uri Dimant" wrote:

> Greg, If I understood you properly you need
> DECLARE @.max_item
> BEGIN TRAN
> SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
> WHERE custid=.....
> INSERT INTO AnothetTable VALUES (@.max_item)
> COMMIT TRAN
>
>
> "Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
> news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
>
>

How do I migrate reports to new server

Hi all,
I just installed reporting services on a new server and need to migrate
existing reports from an old server to the new one. Being new to Reporting
Services, I am not sure of how to do this. Any help you gurus can provide
will be greatly appreciated.
Thanks,
--
LynnYou might want to use Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Any questions just drop me a mail using the link in the readme file
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:7BEBF34B-420F-42D0-BEB0-33BF5E01AD46@.microsoft.com...
> Hi all,
> I just installed reporting services on a new server and need to migrate
> existing reports from an old server to the new one. Being new to
> Reporting
> Services, I am not sure of how to do this. Any help you gurus can provide
> will be greatly appreciated.
> Thanks,
> --
> Lynn|||> I just installed reporting services on a new server and need to migrate
> existing reports from an old server to the new one. Being new to
> Reporting
> Services, I am not sure of how to do this. Any help you gurus can provide
> will be greatly appreciated.
Hi Lynn,
I think this tool [1] can help you.
Look in the generated script to find out how it is working.
[1] http://www.sqldbatips.com/showarticle.asp?ID=62
Best regards,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD

How do I Merge two databases?

I have two databases with each one on a different server. I can copy
both databases to the same server with no problems. But how do I merge
them?

This is what I came up with so far but how do I do the same process for
multiple rows and tables?

IF NOT EXISTS (SELECT tmp_DB1.dbo.tb1.key1 FROM tmpDB1.dbo.tb1 WHERE
tmp_DB1.dbo.tb1.key1 = tmp_DB2.dbo.tb1.key1)
INSERT INTO tmp_DB1.dbo.tb1 (<all fields>)
VALUES (<all fields>)

Thanks in advance.You probably want to do something like this (insert into a new table only
the rows that don't already exist):

INSERT INTO DB1.dbo.TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM DB2.dbo.SourceTable AS S
LEFT JOIN DB1.dbo.TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

Obviously this won't be useful if Key_col is an IDENTITY column. Use the
natural key of your table(s).

--
David Portas
SQL Server MVP
--|||Thanks, it works great. Now, I just have to do this for about 20 more
tables. :)sql

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx
.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some oth
er filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by cre
ate a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you ne
ed to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the
DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear
. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports
Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
H
Hi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H
|||Thanks Hari
I'll give it a go!
Regards
H
|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H
|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
--
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file is not empty. and suggestion?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.