Manage remote instances using SQL Server 2005 tools

I'm trying to manage remote instances using the following SQL Server 2005 tools:

SQL Server Configuration Manager (SSCM)

SQL Server Surface Area Configuration (SSSAC)

It seems that SSSAC is designed to manage remote instances since it has the "change computer" link. On the other hand, SSCM does not have such link or something similar. I read this MS article http://msdn2.microsoft.com/en-US/library/ms190622.aspx so it seems that this is a way to run SSCM to manage remote instance. I was having problem to show SQL Server 2005 Services by following that article. However, the problem is now gone (don't know why yet).

What are the reasons that the SSCM is not designed to manage remote instances but SSSAC is? If SSCM is really designed not to manage remote instances, what is the purpose of having the word (Local) next to the SQL Server Configuration Manager?

If I understand correctly, the only main difference between SSCM and SSSAC is that SSCM does not have the functionality to turn on and off features according to http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

So, if I don't need to turn on and off features, SSCM will be the only tool needed if it can manage remote instance.

With SQL Server Management Studio (SSMS) open and Registered Servers visible, right-click a remote reristered server and click SQL Server Configuration Manager. You will see SQL Server Configuration Manager(remote server name).|||

Hi lkh,

Thanks for the information. I wonder why MS does not implement the change computer in SSCM. Is there a way to run SSSAC in SSMS?

|||

it is a command prompt utility see it can be run in Query Editor in sqlcmd mode

See SQL Server 2005 Books Online topic:

sac Utility

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

you can add in in SSMS under Tools\External Tools. Its located at C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe

making changes to templates. Need help urgent

I created templates in the folder C:\Program Files\Microsoft SQL
Server\80\Tools\Report Designer\ProjectItems\ReportProject\. My question is
:Will any changes to the template automatically reflect changes to reports
that have already been created using this template.If that is possible how do
i do it.
I am unable to find answers to this issue. Appreciate if somebody can
provide a solution.Changes to a template will not reflect in reports created by that template as
they could be published anywhere.
"Kiran" wrote:
> I created templates in the folder C:\Program Files\Microsoft SQL
> Server\80\Tools\Report Designer\ProjectItems\ReportProject\. My question is
> :Will any changes to the template automatically reflect changes to reports
> that have already been created using this template.If that is possible how do
> i do it.
> I am unable to find answers to this issue. Appreciate if somebody can
> provide a solution.

Maintenence plan backup schedule doesnt work

I am running the latest MSDE with the SQL Server 2000 client tools onWindows 2K Server on my production server. I have a simliar setup on mydevelopment machine except it is running Windows 2K (non-server). Ihave the same issue on both machines (I am wondering if it is relatedto the structure of the database). When I open Enterprise Manager,connect to the server, navigate to the Management folder, and add a newmanagement plan, I experience the following error.
I create a management plan that includes nothing but a completedatabase backup (i.e. no reorganization of index pages, logging, etc.).I set the backup schedule to occur every Sun at 12:00AM and hit OK.Sometimes I get the following error :"Error 8114: Error converting datatype int to tinyint" sometimes not. Either way, though, the same thinghappens, the schedule for the backup is not saved. If I reopen themanagement plan and go to the "Complete Backup" tab, there is noschedule in the schedule box.
I have found this link:
http://www.technologyone.org/new-4581847-3733.html
Which appears to be the same issue that I am having, however, there isno resolution there. I was wondering if anyone had any ideas.
Jason
just a quick dirty question: can you perform a normal backup? Is there a job for the backup? If so will this force run?|||Yes, I can perform manual backups without a problem.And, in fact, I can successfully create a maintenence plan withschedules for things like "Index pages" etc. And these schedules worksuccessfully. However, I am only interested in making a maintenenceplan for complete backups on a schedule and that does not work. Oneworkaround that would probably work is to just use the SQL ServerBackup dialog and set a schedule in there. However, this does not allowme the flexibility I require to manage the backups, such as saving thefiles with their dates appended and triaging the backups into seperatefolders for each database. It also does not allow any extensibility fordown the line when I would like to automate the cleaning andoptimization tasks as well as logging.Finally, I think I should alsomention, that I do have SQL Server Agent running, because this isrequired for the schedules to work correctly.
Jason
|||Have you thought of writing back-up scripts for these processes. Thesecould then be controlled by DTS or if you really have to xp_cmdshell?
|||I have thought about it, but have decided that my efforts are betterspent getting the maintenence plan working for several reasons. Firstoff, all I need is a basic backup and I do not require the flexibilitythat custom scripts would provide (nor do I need the overhead ofmaintaining said scripts). Lastly, the maintenance plan SHOULD work, Ijust have to figure out why it is not working rather than determining aworkaround.
Jason
|||

ok, just is case you do want a script here's one you could use.
--Declare variables

declare @.BACKUP varchar (100)
declare @.day varchar(50) -- date dd/mm/yyyy
declare @.hour varchar (4) -- hour hh
declare @.copy varchar (200) -- copy statement to where ever you want

--set day This is used for the day FULL backup
set @.day = convert(varchar(50), getdate(), 103)
set @.day =replace (@.day, '/', '_')

--set hour This is used for the LOG transaction backups Not needed for full backup
--set @.hour = datepart(hour, getdate())
--set the name of the backup
set @.BACKUP = location of backup i.e. 'E:\data\MSSQL\BACKUP\Northwind\Northwind_'+@.day+'.bak'
--backup database SQL
backup database Northwind todisk=@.BACKUP with stats
--copy the file to where
set @.copy = 'copy'+@.BACKUP+ location'\\servername\drive$\folder\