Making a new ms access project

I'm having trouble making a new ms access project.
MSDE is running.
I run the wizard and it asks for a user name and password for the new
database. I put in sa and leave the password blank. Then I get an error
message.
What should I do?
Thanks.
Also, do the tables actually reside on in the sql file and the project
links to them? Where is there a tutorial on this?
Thanks again.
I found out the the Symantec Personal Firewall was blocking this.
Which program do I need to have it allow to have access? Ms Access?
Thanks.
"SAC" <someone@.microsoft.com> wrote in message
news:%23F2BGbG6EHA.3616@.TK2MSFTNGP11.phx.gbl...
> I'm having trouble making a new ms access project.
> MSDE is running.
> I run the wizard and it asks for a user name and password for the new
> database. I put in sa and leave the password blank. Then I get an error
> message.
> What should I do?
> Thanks.
> Also, do the tables actually reside on in the sql file and the project
> links to them? Where is there a tutorial on this?
> Thanks again.
>

Maintenance Wizard error...

Hi,

I'm just trying to use the Maintenance Wizard for the first time, but the SQL Server Management Studio shows me an error message about 'Agent XPs' is not running on my server; and I should activate it through 'sp_configure'?

I've tried to understand 'sp_configure' by searching it on MS TechNet, etc. But of course, because I'm not a SQL Server expert user; I don't even know how to use 'sp_configure' at all....

Anyone know how to work this thing around? Appreciate all the help...

PS: I almost forgot to mention, that I'm using MS Windows Small Business Server R2 2003 with SQL Server 2005 Workgroup Edition.

sp_configure is used to view/change global settings for the current server. To enable them, connect to Management Studio, start a new query, and view the current configuration. For more options with sp_configure, check out:

http://msdn2.microsoft.com/en-us/library/ms188787.aspx

To enable Agent XP's, you can try something like this:

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
go
RECONFIGURE
GO

Thanks,
Sam Lester (MSFT)

|||

Sam,

Thanks for your reply there...

But unfortunately, I am a very novice at SQL Server, is there a step-by-step documentation on how to start using sp_configure?

Thank you again.

|||

The link I supplied explains the entire syntax for sp_configure, one of many system stored procedures used in managing your server. I'd suggest taking a look at the tutorials included in books online (the documentation portion of SQL Server 2005). Here is a good one on getting familiar with Management Studio, including writing T-SQL statements (such as sp_configure):

http://msdn2.microsoft.com/en-us/library/ms167593.aspx

Thanks,
Sam

|||

Hello -

I'm not an expert on SQL Server Management Studio, but I just ran into the same issue and here's how I got around it:

I loaded up SQL Server Management Studio|||

Chris,

Is it really true, you did have the similar problem with Maintenance Wizard like I do? Then perhaps it's like I'm most affraid of, Microsoft actually de-activate it by default settings in SQL Server 2005 Workgroup Edition... The question is why?

I did try to look up in the Object Explorer in SQL Server Management Studio first before post a thread in here, because I didn't find anything that said about Agent XPs. But maybe it's just that missed it, I'll take a look it again when I got back to my office then (too bad right now I'm on vacation, gives me a jeepers to leave the server's database un-protected like that ).

Thanks for the feedback Chris! Hopefully others who has the same problem with Maintenance Wizard will post too, so Microsoft could give a good explanation why there's such issue in the first place.

Maintenance Wizard

Hi
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
ShayHi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:
> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>

Maintenance Wizard

Hi
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
ShayHi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:

> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>

Maintenance Wizard

Hi
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
Shay
Hi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:

> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>

Maintenance Wizard

I'm trying to backup several databases to UNC folders. I'm able to do it
manually for each database, but when I set up the maintenance plan it fails
because SQL tries to use the SQL administrator account to push the data.

The million dollar question is how does one change which account is running
the maintenance?"GTM" <gmckaigeATworkerstempDOTcom> wrote in message
news:gJScnQFltqR2TcmiXTWJkw@.fdn.com...
> I'm trying to backup several databases to UNC folders. I'm able to do it
> manually for each database, but when I set up the maintenance plan it
fails
> because SQL tries to use the SQL administrator account to push the data.
>
> The million dollar question is how does one change which account is
running
> the maintenance?

Change the account the SQL Server Agent runs under to a domain account.
|||Hi

Gregs advice is probably the root cause of your problem if the account is
set to local system.

If it is already a local/domain account the the permissions to the
shares/directories/trusts etc... need to be in place.

John

"GTM" <gmckaigeATworkerstempDOTcom> wrote in message
news:gJScnQFltqR2TcmiXTWJkw@.fdn.com...
> I'm trying to backup several databases to UNC folders. I'm able to do it
> manually for each database, but when I set up the maintenance plan it
fails
> because SQL tries to use the SQL administrator account to push the data.
>
> The million dollar question is how does one change which account is
running
> the maintenance?

Maintenance plan wizard missing

Under Management, all I have is SQL Server Logs and Activity monitor, no option to create a maintenance plan or run the wizard ?Under management you have Maintenance plans,Sql server logs,activity monitor,dataabse mail etc........your question sounds bizzare.....just check if you are logged in with sysadmin privilege.......|||

which edition of sql server is this. I thing Express edition u have . in that case MP not supported in this edtion

Madhu

|||I am running SQL 2005, not Express.|||SQL 2005

Maintenance Plan Wizard is Crashing

Hi,

I am having trouble with the Maintenance Plan Wizard in SQL Server 2005 Management Studio.

I have run the Maintenance Plan Wizard many times since I installed SQL Server 2005. However it looks like there is a problem with assembly 'Microsoft.SqlServer.Smo. I am getting this error:

- Creating maintenance plan "DNNSRC Backup" (Error)
Messages
* Create maintenance plan failed.

ADDITIONAL INFORMATION:
Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (Microsoft.SqlServer.MaintenancePlanTasks)

Note I was going to upgrade with service pack 2 but I am reluctant to do that until I fix this problem. I am not sure SP2 would have any effect.

Any help or suggestions would be very much appreciated.

Thanks.
G. M.

make sure that your system is upgraded to SP2 and also you have installed SSIS.

Refer these links for more info

http://support.microsoft.com/default.aspx/kb/933508

http://blogs.msdn.com/sqlrem/

Madhu

|||

Check the following thread...sp2 resolve the same issue...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404000&SiteID=1

|||Thanks, Your suggestions helped. I installed SP2 and that corrected the problem.

Maintenance Plan Wizard doesn't see my DB

Using SQL2005-Std, the Maintenance Plan Wizard doesn't see my database. My
database is not listed in the dialog box where you select specific databases
to backup. I see AdventureWorks, AdventureWorksSW, master, model and msdb.
Object Explorer doesn't show "msdb". Maybe it is my DB ? Strange because my
DB has another name and is configured as SQL7 compatible.
--
Francois PIETTE
http://www.overbyte.beMaint plan doesn't support database in 70 or lower compat mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francois PIETTE" <fpiette@.newsgroups.nospam> wrote in message
news:%23C5Jhp8aHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Using SQL2005-Std, the Maintenance Plan Wizard doesn't see my database. My
> database is not listed in the dialog box where you select specific databases
> to backup. I see AdventureWorks, AdventureWorksSW, master, model and msdb.
> Object Explorer doesn't show "msdb". Maybe it is my DB ? Strange because my
> DB has another name and is configured as SQL7 compatible.
> --
> Francois PIETTE
> http://www.overbyte.be
>|||Hi, Francois,
I understand that you could not see your database with compatibility level
70 in your Maintenance Plan.
If I have misunderstood, please let me know.
This is a known by design issue. Tibor has explained the reason here.
For now, the workaround is that you change the compatibility level to 80 or
90.
Please feel free to let us know if you have any other questions or concerns.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||> I understand that you could not see your database with
> compatibility level 70 in your Maintenance Plan.
Right.
> This is a known by design issue. Tibor has explained the reason here.
> For now, the workaround is that you change the compatibility
> level to 80 or 90.
> Please feel free to let us know if you have any other questions or
concerns.
I have an existing application running with SQL version 7 that I want to run
on SQL2005. That's why I selected compatibility level 70.
a) If I change the compatibility level to 80 or 90, what kind of problem
could I have ? I have to say that I have no control over the application and
will not be able to fix any request or stored procedure.
b) Is changing the compatibility level a revertible process ? If I change
the compatibility level from 70 to 90, then create the maintenance plan, can
I go back to level 70 and still use the maintenance plan and the DB ?
c) If none of the above strategy work, how am I supposed to automate backup,
integrity check, reindexation and all similar maintenance tasks wih the
"level 70" database ?
--
Francois PIETTE
http://www.overbyte.be|||Hi,
For your three questions, please see the following answers:
>a) If I change the compatibility level to 80 or 90, what kind of problem
> could I have ? I have to say that I have no control over the application
and
> will not be able to fix any request or stored procedure.
If there are some features or functionalities that SQL Server 2005 does not
support but still exist in your application, it may not work.
Please refer to:
SQL Server 2005 Database Engine Backward Compatibility
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Since it is critical to your application, I do not recommend that you
change the compatibility level. You may consider to write T-SQL script and
execute it in SQL jobs. Anyway this depends on your requirements. Could you
please let us know what your requirements are?
> b) Is changing the compatibility level a revertible process ? If I change
> the compatibility level from 70 to 90, then create the maintenance plan,
can
> I go back to level 70 and still use the maintenance plan and the DB ?
Yes, the database can change back to 70 if the SQL Server 2005 new features
were not used in your database; For the maintenance plan, I recommend that
you established a test environment to check if it still work since I have
not found any document talking about this issue.
>c) If none of the above strategy work, how am I supposed to automate
backup,
>integrity check, reindexation and all similar maintenance tasks wih the
>"level 70" database ?
As I replied in your first question, you can write T-SQL statement in one
or several SQL jobs and schedule them to run. For example:
BACK UP DATABASE
===================BACKUP DATABASE Test To DISK='D:\SQLDB_BACKUP\Test.bak'
CHECK INTEGRITY
=================USE [TEST]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
INDEX DEFRAGEMENT
=====================DBCC INDEXDEFRAG Test
GO
INDEX REBUILDING
======================DBCC DBREINDEX 'Test.dbo.tblTest'
GO
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks, this perfectly answer my question.
Very good support as usual.
--
Francois PIETTE
http://www.overbyte.be
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> a écrit dans le message
de news:x9I%234XGbHHA.928@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> For your three questions, please see the following answers:
> >a) If I change the compatibility level to 80 or 90, what kind of problem
> > could I have ? I have to say that I have no control over the application
> and
> > will not be able to fix any request or stored procedure.
> If there are some features or functionalities that SQL Server 2005 does
not
> support but still exist in your application, it may not work.
> Please refer to:
> SQL Server 2005 Database Engine Backward Compatibility
> http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> Since it is critical to your application, I do not recommend that you
> change the compatibility level. You may consider to write T-SQL script and
> execute it in SQL jobs. Anyway this depends on your requirements. Could
you
> please let us know what your requirements are?
> > b) Is changing the compatibility level a revertible process ? If I
change
> > the compatibility level from 70 to 90, then create the maintenance plan,
> can
> > I go back to level 70 and still use the maintenance plan and the DB ?
> Yes, the database can change back to 70 if the SQL Server 2005 new
features
> were not used in your database; For the maintenance plan, I recommend that
> you established a test environment to check if it still work since I have
> not found any document talking about this issue.
> >c) If none of the above strategy work, how am I supposed to automate
> backup,
> >integrity check, reindexation and all similar maintenance tasks wih the
> >"level 70" database ?
> As I replied in your first question, you can write T-SQL statement in one
> or several SQL jobs and schedule them to run. For example:
> BACK UP DATABASE
> ===================> BACKUP DATABASE Test To DISK='D:\SQLDB_BACKUP\Test.bak'
> CHECK INTEGRITY
> =================> USE [TEST]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> INDEX DEFRAGEMENT
> =====================> DBCC INDEXDEFRAG Test
> GO
> INDEX REBUILDING
> ======================> DBCC DBREINDEX 'Test.dbo.tblTest'
> GO
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
>
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
> ======================================================>
>
>|||Hi,
Appreciate your update and response. I am glad to hear that the suggestions
are helpful. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Maintenance Plan Wizard doesn't see my DB

Using SQL2005-Std, the Maintenance Plan Wizard doesn't see my database. My
database is not listed in the dialog box where you select specific databases
to backup. I see AdventureWorks, AdventureWorksSW, master, model and msdb.
Object Explorer doesn't show "msdb". Maybe it is my DB ? Strange because my
DB has another name and is configured as SQL7 compatible.
Francois PIETTE
http://www.overbyte.beMaint plan doesn't support database in 70 or lower compat mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francois PIETTE" <fpiette@.newsgroups.nospam> wrote in message
news:%23C5Jhp8aHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Using SQL2005-Std, the Maintenance Plan Wizard doesn't see my database. My
> database is not listed in the dialog box where you select specific databas
es
> to backup. I see AdventureWorks, AdventureWorksSW, master, model and msdb.
> Object Explorer doesn't show "msdb". Maybe it is my DB ? Strange because m
y
> DB has another name and is configured as SQL7 compatible.
> --
> Francois PIETTE
> http://www.overbyte.be
>|||Hi, Francois,
I understand that you could not see your database with compatibility level
70 in your Maintenance Plan.
If I have misunderstood, please let me know.
This is a known by design issue. Tibor has explained the reason here.
For now, the workaround is that you change the compatibility level to 80 or
90.
Please feel free to let us know if you have any other questions or concerns.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||> I understand that you could not see your database with
> compatibility level 70 in your Maintenance Plan.
Right.

> This is a known by design issue. Tibor has explained the reason here.
> For now, the workaround is that you change the compatibility
> level to 80 or 90.
> Please feel free to let us know if you have any other questions or
concerns.
I have an existing application running with SQL version 7 that I want to run
on SQL2005. That's why I selected compatibility level 70.
a) If I change the compatibility level to 80 or 90, what kind of problem
could I have ? I have to say that I have no control over the application and
will not be able to fix any request or stored procedure.
b) Is changing the compatibility level a revertible process ? If I change
the compatibility level from 70 to 90, then create the maintenance plan, can
I go back to level 70 and still use the maintenance plan and the DB ?
c) If none of the above strategy work, how am I supposed to automate backup,
integrity check, reindexation and all similar maintenance tasks wih the
"level 70" database ?
Francois PIETTE
http://www.overbyte.be|||Hi,
For your three questions, please see the following answers:
>a) If I change the compatibility level to 80 or 90, what kind of problem
> could I have ? I have to say that I have no control over the application
and
> will not be able to fix any request or stored procedure.
If there are some features or functionalities that SQL Server 2005 does not
support but still exist in your application, it may not work.
Please refer to:
SQL Server 2005 Database Engine Backward Compatibility
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Since it is critical to your application, I do not recommend that you
change the compatibility level. You may consider to write T-SQL script and
execute it in SQL jobs. Anyway this depends on your requirements. Could you
please let us know what your requirements are?

> b) Is changing the compatibility level a revertible process ? If I change
> the compatibility level from 70 to 90, then create the maintenance plan,
can
> I go back to level 70 and still use the maintenance plan and the DB ?
Yes, the database can change back to 70 if the SQL Server 2005 new features
were not used in your database; For the maintenance plan, I recommend that
you established a test environment to check if it still work since I have
not found any document talking about this issue.

>c) If none of the above strategy work, how am I supposed to automate
backup,
>integrity check, reindexation and all similar maintenance tasks wih the
>"level 70" database ?
As I replied in your first question, you can write T-SQL statement in one
or several SQL jobs and schedule them to run. For example:
BACK UP DATABASE
===================
BACKUP DATABASE Test To DISK='D:\SQLDB_BACKUP\Test.bak'
CHECK INTEGRITY
=================
USE [TEST]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
INDEX DEFRAGEMENT
=====================
DBCC INDEXDEFRAG Test
GO
INDEX REBUILDING
======================
DBCC DBREINDEX 'Test.dbo.tblTest'
GO
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks, this perfectly answer my question.
Very good support as usual.
Francois PIETTE
http://www.overbyte.be
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> a crit dans le mes
sage
de news:x9I%234XGbHHA.928@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> For your three questions, please see the following answers:
> and
> If there are some features or functionalities that SQL Server 2005 does
not
> support but still exist in your application, it may not work.
> Please refer to:
> SQL Server 2005 Database Engine Backward Compatibility
> http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> Since it is critical to your application, I do not recommend that you
> change the compatibility level. You may consider to write T-SQL script and
> execute it in SQL jobs. Anyway this depends on your requirements. Could
you
> please let us know what your requirements are?
>
change[vbcol=seagreen]
> can
> Yes, the database can change back to 70 if the SQL Server 2005 new
features
> were not used in your database; For the maintenance plan, I recommend that
> you established a test environment to check if it still work since I have
> not found any document talking about this issue.
>
> backup,
> As I replied in your first question, you can write T-SQL statement in one
> or several SQL jobs and schedule them to run. For example:
> BACK UP DATABASE
> ===================
> BACKUP DATABASE Test To DISK='D:\SQLDB_BACKUP\Test.bak'
> CHECK INTEGRITY
> =================
> USE [TEST]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> INDEX DEFRAGEMENT
> =====================
> DBCC INDEXDEFRAG Test
> GO
> INDEX REBUILDING
> ======================
> DBCC DBREINDEX 'Test.dbo.tblTest'
> GO
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
>
http://msdn.microsoft.com/subscript...ault.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> ========================================
==============
>
>
>|||Hi,
Appreciate your update and response. I am glad to hear that the suggestions
are helpful. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Maintenance Plan Wizard doesn't see my DB

Hi, Francois,
I understand that you could not see your database with compatibility level
70 in your Maintenance Plan.
If I have misunderstood, please let me know.
This is a known by design issue. Tibor has explained the reason here.
For now, the workaround is that you change the compatibility level to 80 or
90.
Please feel free to let us know if you have any other questions or concerns.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
Hi,
For your three questions, please see the following answers:
>a) If I change the compatibility level to 80 or 90, what kind of problem
> could I have ? I have to say that I have no control over the application
and
> will not be able to fix any request or stored procedure.
If there are some features or functionalities that SQL Server 2005 does not
support but still exist in your application, it may not work.
Please refer to:
SQL Server 2005 Database Engine Backward Compatibility
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Since it is critical to your application, I do not recommend that you
change the compatibility level. You may consider to write T-SQL script and
execute it in SQL jobs. Anyway this depends on your requirements. Could you
please let us know what your requirements are?

> b) Is changing the compatibility level a revertible process ? If I change
> the compatibility level from 70 to 90, then create the maintenance plan,
can
> I go back to level 70 and still use the maintenance plan and the DB ?
Yes, the database can change back to 70 if the SQL Server 2005 new features
were not used in your database; For the maintenance plan, I recommend that
you established a test environment to check if it still work since I have
not found any document talking about this issue.

>c) If none of the above strategy work, how am I supposed to automate
backup,
>integrity check, reindexation and all similar maintenance tasks wih the
>"level 70" database ?
As I replied in your first question, you can write T-SQL statement in one
or several SQL jobs and schedule them to run. For example:
BACK UP DATABASE
===================
BACKUP DATABASE Test To DISK='D:\SQLDB_BACKUP\Test.bak'
CHECK INTEGRITY
=================
USE [TEST]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
INDEX DEFRAGEMENT
=====================
DBCC INDEXDEFRAG Test
GO
INDEX REBUILDING
======================
DBCC DBREINDEX 'Test.dbo.tblTest'
GO
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
Appreciate your update and response. I am glad to hear that the suggestions
are helpful. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Maintenance Plan Wizard ... No User Databases

A friend is trying to use the Database Maintenance Plans to build maintenance
jobs for her databases. She is using SQL 2005 Ent Ed. Her account is in the
system administrators role. She can back up the databases by writing the SQL
scripts manually. She can also use the object browser to walk through to the
appropriate database base and right-click to back up the database
... but - when she opens the maintenance wizard, there are not any user
defined databases listed there, only the system databases. I am not local
and I have not actually seen her configuration. Has anyone seen anything
like this?
Thanks.
Ann
I just found out that when the databases were imported, they were set to be
SQL 7.0 compatible. The info in BOL updated July 2006 states that
maintenance plans only work with compat level of 80 or higher.
"alw" wrote:

> A friend is trying to use the Database Maintenance Plans to build maintenance
> jobs for her databases. She is using SQL 2005 Ent Ed. Her account is in the
> system administrators role. She can back up the databases by writing the SQL
> scripts manually. She can also use the object browser to walk through to the
> appropriate database base and right-click to back up the database
> ... but - when she opens the maintenance wizard, there are not any user
> defined databases listed there, only the system databases. I am not local
> and I have not actually seen her configuration. Has anyone seen anything
> like this?
> Thanks.
> Ann

maintenance plan wizard - security question, possible bug?

Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way to
specify that (concering that I get IS package, very hard to modify anything)?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000).
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed the
> password
> In other words , your SQL Agent runs under the same account as you logged ,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> > Hi All,
> > I am trying to create maintenance plan for updating statistics, checking
> > database integrity and database backup, using wizard. First of all, I
> > don't
> > see any option for how long do I want to keep backup files on the system
> > (like we had on 2000). I find it very inconvinient. Is there any other way
> > to
> > specify that (concering that I get IS package, very hard to modify
> > anything)?
> > More important question is regarding security issue with this wizard: I
> > have
> > only 'sa' access to the server (not member of local admin group, and I'm
> > not
> > able to use windows authentication). Each month we change 'sa' password on
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> > am I
> > missing something?
> > Thanks in advance,
> > Pedja
> >
> > P.S. The same thing would happen even if I created maintenance plan
> > manually, because I would have to create connection objects manually (and
> > specify 'sa' again)...
>
>

maintenance plan wizard - security question, possible bug?

Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way t
o
specify that (concering that I get IS package, very hard to modify anything)
?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am
I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:

> Pedja
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed t
he
> password
> In other words , your SQL Agent runs under the same account as you logged
,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
>
>