remotely start/stop sql server service in a workgroup

hi, i have an administrator account on a box, there is a sql server pc
in the same workgroup w/ a different admin pass (which i know ^_^, and
the sql sa pass too), how can i restart the sql server service from my box ?You can use xp_cmdshell with NET START/STOP MSSQLSERVER command. On you
command prompt type in NET START /? for details on this DOS command. For
details on xp_cmdshell, refer to SQL Server Books Online.
Anith|||Anith Sen wrote:
> You can use xp_cmdshell with NET START/STOP MSSQLSERVER command. On you
> command prompt type in NET START /? for details on this DOS command. For
> details on xp_cmdshell, refer to SQL Server Books Online.
>
xp_cmdshell is deleted(as recomended by every sql server security
quide), i can kill the process using pskill, but could that lead to data
loss?|||You can stop using the TSQL SHUTDOWN command.
At the OS level, you can both start and stop using NETSVC.EXE (Google for it
).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Fred" <fred@.ilovespam.com> wrote in message news:exFBLGgHGHA.2036@.TK2MSFTNGP14.phx.gbl...[
color=darkred]
> hi, i have an administrator account on a box, there is a sql server pc
> in the same workgroup w/ a different admin pass (which i know ^_^, and
> the sql sa pass too), how can i restart the sql server service from my box ?[/colo
r]

Remotely Connect to MSDE

Hi
I just installed MSDE over my Windows 2003 box and facing an small problem.
I installed it with SQL security mode and also set a password for user "sa".
Everything looks just fine using "osql" as it uses local address.
But I can't connect to my server from another computer using VS.NET Server Explorer.
It seems as MSDE is set to reject connections from outside by default.
My question is how to enable it so I can connect using my "sa" account.
Appreciate your help in advance.OK. are you trying to connect by IP address or server name? do you have a firewall installed?|||Since I am connecting from outside of the server, I am using IP address.
And also I don't have any firewall installed.|||at a guess I'd say you have only windows authentication enabled. not 100% sure how to change that in MSDE, but I'll scout around and see.

Remote Stored Proc Call

I'm calling this from another sql server...
I created a linked server... and want to restore database backups on the other box...
The restore script runs fine when ran locally but fails with the message below when calling it remotely

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.

CREATE PROCEDURE usp_restore_database_backups AS

RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

WAITFOR DELAY '00:00:05'

EXEC sp_dboption 'BesMgmt', 'single user', true
GO

I have set it to read only dbo only ... single user... still get the same message...
does anyone have any suggestions...try executing...

ALTER DATABASE <MyDB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

or

ALTER DATABASE <MyDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

before the restore statement and drop the sp_dboption.|||thanks Thrasymachus
I'm still getting the same error:
Executed as user: NFCU\sqlsvc. RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101). The step failed.|||post the script you are currently using and try running sp_who when it fails to see what connections are in use. Also when you execute this script in the QA are you connected to the database you are trying to restore? The database selected from the dropdown should not be the database you are trying to restore. If everything is OK with your script I suspect this is the case.|||The error does not tally with this but just belt and braces.

If you run
EXEC sp_helpserver

do you see RPC & RPC out in the status field?|||When I set the database manually into single user mode I get a different error when trying to restore the database... this is the error that comes from when the database is in single user mode....
"Executed as user: NFCU\sqlsvc. Cannot open database requested in login 'BESMgmt'. Login fails. [SQLSTATE 42000] (Error 4060). The step failed."

CREATE PROCEDURE usp_restore_database_backups AS

/*
declare @.x varchar(255)
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('BesMgmt')
exec (@.x)
*/

ALTER DATABASE BesMgmt SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'

WAITFOR DELAY '00:00:05'

--EXEC sp_dboption 'BesMgmt', 'single user', true
GO|||The command is within a job...
the sql agent service is ran under a domain user acount...

Remote Manage SQL Server Agent via SSMS

When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
Server Agent jobs. When I open a specific job I can see the owner and steps
etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
Server 2005 instance I can see the jobs, but when I go to look at properties
it is acting like it is a new job with none of the actual owner/config
information. The account I am using to connect in both cases is the same
account and is in the sysadmin role. Any advice is appreciated.
Thanks
--
Adam SYou need to install SP2 or later on your client machine. I believe you have
a mismatch between client and server (a later edition has probably been
upgraded on the server). You should always try to maintain consistency
between the version of tools installed on the server and all clients that
will access it...
A
"Adam S" <Adam S@.community.nospam> wrote in message
news:7B3CEA63-4090-48D4-AFEA-70487F537E5E@.microsoft.com...
> When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
> Server Agent jobs. When I open a specific job I can see the owner and
> steps
> etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
> Server 2005 instance I can see the jobs, but when I go to look at
> properties
> it is acting like it is a new job with none of the actual owner/config
> information. The account I am using to connect in both cases is the same
> account and is in the sysadmin role. Any advice is appreciated.
> Thanks
> --
> Adam S

Remote Manage SQL Server Agent via SSMS

When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
Server Agent jobs. When I open a specific job I can see the owner and steps
etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
Server 2005 instance I can see the jobs, but when I go to look at properties
it is acting like it is a new job with none of the actual owner/config
information. The account I am using to connect in both cases is the same
account and is in the sysadmin role. Any advice is appreciated.
Thanks
Adam SYou need to install SP2 or later on your client machine. I believe you have
a mismatch between client and server (a later edition has probably been
upgraded on the server). You should always try to maintain consistency
between the version of tools installed on the server and all clients that
will access it...
A
"Adam S" <Adam S@.community.nospam> wrote in message
news:7B3CEA63-4090-48D4-AFEA-70487F537E5E@.microsoft.com...
> When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
> Server Agent jobs. When I open a specific job I can see the owner and
> steps
> etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
> Server 2005 instance I can see the jobs, but when I go to look at
> properties
> it is acting like it is a new job with none of the actual owner/config
> information. The account I am using to connect in both cases is the same
> account and is in the sysadmin role. Any advice is appreciated.
> Thanks
> --
> Adam S

Remote Manage SQL Server Agent via SSMS

When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
Server Agent jobs. When I open a specific job I can see the owner and steps
etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
Server 2005 instance I can see the jobs, but when I go to look at properties
it is acting like it is a new job with none of the actual owner/config
information. The account I am using to connect in both cases is the same
account and is in the sysadmin role. Any advice is appreciated.
Thanks
Adam S
You need to install SP2 or later on your client machine. I believe you have
a mismatch between client and server (a later edition has probably been
upgraded on the server). You should always try to maintain consistency
between the version of tools installed on the server and all clients that
will access it...
A
"Adam S" <Adam S@.community.nospam> wrote in message
news:7B3CEA63-4090-48D4-AFEA-70487F537E5E@.microsoft.com...
> When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
> Server Agent jobs. When I open a specific job I can see the owner and
> steps
> etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
> Server 2005 instance I can see the jobs, but when I go to look at
> properties
> it is acting like it is a new job with none of the actual owner/config
> information. The account I am using to connect in both cases is the same
> account and is in the sysadmin role. Any advice is appreciated.
> Thanks
> --
> Adam S

Remote Executiong - SSIS

All,

Is it possible to run the ssis package from a remote box?

The SSIS package will be stored in a sql server (open for suggestions on this) . We have a seperate box which has a 3rd party scheduler application. All our current scheduled jobs are ran from this box. we want the ssis packages also to be called from this box, as it will be easier to maintain and keep track of the jobs running.

So, we want the SSIS package to be called from this scheduler box. Any ideas?

Thanks

You can create a job without schedule on SQL box, then invoke this job at the time defined by your own scheduler (e.g. start osql.exe to run sp_start_job).

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

|||

If I install the client tools for Sql Server 2005 in the scheduler box, will i be able to run the package from that box itself using DTEXEC?

The problem I see with sp_start_job is that, it looks it will just start the job and report a success as long as the job gets started. If the job fails during execution we will still not know anything about it and I cannot have a dependency of jobs based on that.

|||

Karunakaran wrote:

If I install the client tools for Sql Server 2005 in the scheduler box, will i be able to run the package from that box itself using DTEXEC?

Yes, but that box will require a SQL Server license.|||

Another question on the same lines, let me know if I need to post this in a seperate thread.

I write a console application referencing dts runtime classes, and I invoke the package from the console app. Now If I deploy this console application in a box where ssis is not installed, but .NET framework is installed will this work? or is it against the licensing terms?

Thanks
Karunakaran

|||

Karunakaran wrote:

Another question on the same lines, let me know if I need to post this in a seperate thread.

I write a console application referencing dts runtime classes, and I invoke the package from the console app. Now If I deploy this console application in a box where ssis is not installed, but .NET framework is installed will this work? or is it against the licensing terms?

Thanks
Karunakaran

No. SSIS runtime components are not redistributable. You'll need to install SSIS on that box, and that will require a license.|||Thanks for the clarifications, Phil.

Remote Connections Enabled, but I still get error that Remote is not configured - Sql 2005

Fellow Devs,

I have an instance of SQL Server Express 2005 running on another box and I have Remote Connections enabled over both TCP/IP and Named Pipes, but on my other box I keep getting the error that the server does not accept Remote Connections.

Any ideas why this might be happening? Is there some other configuration?

start server configuration manager

click on protocols > TCP/IP and properties

go to IP addresses and under IPALL remove everythink from TCP dynamic port , under TCP Port enter port you want to use like 1433

save all changes restart service and should work

|||and if you have windows firewall on you have to allow to accept connection on this port|||

I don't see that option in my server configuration manager. I just see "File Server", "Application Server" and "Remote Access/VPN Server". Where do I modify thse settings?

Thanks!

|||

1) open SQL Server configuration manager
2) on the left under "Sql server 2005 network configuration" click on protocols for SQL
3) on right side right click TCP/IP > properties and tab IP addresses

|||

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

|||

Swackhammer1:

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

If VS is not detecting the SQL Server it may mean your SQL Server service maybe off. You may want to download the advanced version from the link below if not first get the eval and then spend $33 to get the developer edition. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

|||

looool what kind of solution is that ? doesn't work pay $33.

here is an article explaining step by step what you can do to enable remote connections - pretty much same idea i gave you , but when you will follow it must work

|||You have given the original poster very low level usually not adviced connection to SQL Server and it is not working, Express is best used for small company intranet hosted application nothing more. I have used SQL Server since 1998 and I have not got the need to connect to SQL Server on those layers. So mine will cost after 180 days but it is pain free.|||

i saw many of your posts and i KNOW that you work with SQL for long time and you know about it much more than i do.
I agree that SQL express is perfect for small project, intranet & for development and i believe that is what this person needs - when someone ask how to enable remote connections in SQL express- my guess would be that he/she doesn't work for BIG corporation that has billions of transaction / day

My point is that there is no reason to buy anything cos EXPRESS edition can work perfectly all you need is to spend few minutes with it and make the setup + maybe change firewall settings.

|||Yes but connecting to SQL Server through TCP port is not good advice because there is also the known UDP port and two others Microsoft admit to have reserved but only give to customers as needed which opens you up to known security issues. Microsoft was like Oracle selling the developer edition for hundreds we asked for the lower price for access and got it. Fighting with Express eats into development time.|||Ok. Here's the thing. I'm using SQL EXPRESS and VS 2005 on my machine, but the company I'm developing for has the full SQL SERVER 2005. Before I changed the tcp/ip setting, I could see my server in VS. After changing the settings though, it no longer shows up. I do have the service running. I double checked.|||OK. I decided to get the trial version of SQL SERVER 2005 for now. Let's see how that works out.|||

The links below from Microsoft covers most of what you need and some of what I have been trying to explain in details. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Caddre - very good links :)

BTWSwackhammer1 where in VS you can not see your SQL server ? in databse explorer when you click "add connection" ? if that is the problem just enter ".\SQLEXPRESS" in server name field or IP address of you server or "YOUR_COMPUTER_NEME\SQLEXPRESS"