Running agent jobs from command line

Hi,
How can i run a SQL Server Agent Job from the command line on the same box? I am using SQL Server 2000.
ThanksCheck out the SQL Books Online for Command Line execution of SQL Agent

The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS package can be stored in the Microsoft SQL Server msdb database, a COM-structured storage file, or SQL Server Meta Data Services.

Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password|||So i have to create a DTS package to run my job and then use dtsrun to run that package?|||Sorry I sent you the wrong script. I'll be back with the correct one shortly. Mark|||You could create a stored procedure to run the job. Example:
(You may be able to pass the name through as a parameter ??)

CREATE PROCEDURE sp_RunJob
AS

BEGIN

EXEC msdb..sp_start_job @.job_name = 'The job name'

END

You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax.

Then from the command line, create a .bat file and put the following isql command synax into the .bat file:

isql /U username -n -E /d "database name" -Q "stored procedure name" /S servername -oC:\returncode.txt

This will actually produce some output into the returncode.txt file, which might be useful. I'm not sure whether you'll have to return a value back out of the stored procedure or not. I've not tried. My sample above it a cut down version. But anyhow there should be enough above to give you the idea in terms of syntax etc.

You would then just execute the .bat file on your server/pc etc, perhaps via a scheduler etc...

Anyhow a search on SQL Books Online will give you all the details. Search for isql , isqlw etc..|||Cheers Mark.

Running a SQL file from OSQL command line utility

Hi,

I have dumped a very large database from mysql (using mysqldump program)
as a raw sql file. The reason was, convert this database to a MSSQL
database. Since mysqldump creates the file as raw sql file with the
database-table structures and the data in it, I thought using OSQL command
line utilities should work to out this whole database in MSSQL server.

I have run this command from command line:

osql -u sa -i mysqldump.sql

It is going since yesterday. It has been almost 36 hours that it's
started. And in the mssql server, I see no database created yet. On the
screen of the command line, I see bunch of numbers are going in order. I
assume they are row numbers of the tables processed. But, if it is doing it,
then where is it saving all this data ? I have checked the tempdb, pub db,
other dbs, and I see no tables related to the database I am inserting. Will
it populate it at the and of the job ? Or, am I doing something wrong here
?

Regards.

Murtix.Hi ,

You cant use that file (generated using MYSQLDUMP) directly in MSSQL using
OSQL. This raw file can be used only in MYSQL incase you need a recovary.
OSQL program can take only TSQL commands.

The solution for you is:

1. Create a database in MSSQL server Manually
2. Use DTS to transfer Tables to MSSQL Server

If your table size is very huge , generate a text file in MYSQL for each
tables and use BCP IN to load inside MSSQL.

Thanks
Hari
MCDBA

"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:3fee42a7$1_3@.athenanews.com...
> Hi,
> I have dumped a very large database from mysql (using mysqldump
program)
> as a raw sql file. The reason was, convert this database to a MSSQL
> database. Since mysqldump creates the file as raw sql file with the
> database-table structures and the data in it, I thought using OSQL command
> line utilities should work to out this whole database in MSSQL server.
> I have run this command from command line:
> osql -u sa -i mysqldump.sql
> It is going since yesterday. It has been almost 36 hours that it's
> started. And in the mssql server, I see no database created yet. On the
> screen of the command line, I see bunch of numbers are going in order. I
> assume they are row numbers of the tables processed. But, if it is doing
it,
> then where is it saving all this data ? I have checked the tempdb, pub db,
> other dbs, and I see no tables related to the database I am inserting.
Will
> it populate it at the and of the job ? Or, am I doing something wrong
here
> ?
> Regards.
> Murtix.|||Hi Hari,

all the pain I suffered to do this, was because of not to use BCP method.
I guess I will have to use it after finding out osql methid would not work.

Thank you clearifying this issue for me.

Best Regards.

Murtix.

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Oaj1x2OzDHA.1412@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> You cant use that file (generated using MYSQLDUMP) directly in MSSQL using
> OSQL. This raw file can be used only in MYSQL incase you need a recovary.
> OSQL program can take only TSQL commands.
> The solution for you is:
> 1. Create a database in MSSQL server Manually
> 2. Use DTS to transfer Tables to MSSQL Server
> If your table size is very huge , generate a text file in MYSQL for each
> tables and use BCP IN to load inside MSSQL.
> Thanks
> Hari
> MCDBA
>
>
>
> "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> news:3fee42a7$1_3@.athenanews.com...
> > Hi,
> > I have dumped a very large database from mysql (using mysqldump
> program)
> > as a raw sql file. The reason was, convert this database to a MSSQL
> > database. Since mysqldump creates the file as raw sql file with the
> > database-table structures and the data in it, I thought using OSQL
command
> > line utilities should work to out this whole database in MSSQL server.
> > I have run this command from command line:
> > osql -u sa -i mysqldump.sql
> > It is going since yesterday. It has been almost 36 hours that it's
> > started. And in the mssql server, I see no database created yet. On the
> > screen of the command line, I see bunch of numbers are going in order. I
> > assume they are row numbers of the tables processed. But, if it is doing
> it,
> > then where is it saving all this data ? I have checked the tempdb, pub
db,
> > other dbs, and I see no tables related to the database I am inserting.
> Will
> > it populate it at the and of the job ? Or, am I doing something wrong
> here
> > ?
> > Regards.
> > Murtix.

Running a SQL file from OSQL command line utility

Hi,
I have dumped a very large database from mysql (using mysqldump program)
as a raw sql file. The reason was, convert this database to a MSSQL
database. Since mysqldump creates the file as raw sql file with the
database-table structures and the data in it, I thought using OSQL command
line utilities should work to out this whole database in MSSQL server.
I have run this command from command line:
osql -u sa -i mysqldump.sql
It is going since yesterday. It has been almost 36 hours that it's
started. And in the mssql server, I see no database created yet. On the
screen of the command line, I see bunch of numbers are going in order. I
assume they are row numbers of the tables processed. But, if it is doing it,
then where is it saving all this data ? I have checked the tempdb, pub db,
other dbs, and I see no tables related to the database I am inserting. Will
it populate it at the and of the job ? Or, am I doing something wrong here
?
Regards.
Murtix.Hi Murtix
You cannot load into SQL Server anything other than a SQL Server backup.
Is mysqldump the actual backup file? If so, then what you are seeing in the
line number is osql thinking it is receiving a command, and it keep
receiving them, waiting to receive a 'go' to tell it to execute what it has
received. It will not even try to process whatever bits are in the mysqldump
file until it finally encounters a 'go'.
The contents of an input file passed to osql (with -i) needs to be a SQL
script, containing valid Transact-SQL statements. Even if you had a valid
SQL Server backup file, the input file to osql would have to be script in
the TSQL language telling SQL Server to restore a specified backup file, you
could not just pass in the backup file itself.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:3fee42a7$1_3@.athenanews.com...
> Hi,
> I have dumped a very large database from mysql (using mysqldump
program)
> as a raw sql file. The reason was, convert this database to a MSSQL
> database. Since mysqldump creates the file as raw sql file with the
> database-table structures and the data in it, I thought using OSQL command
> line utilities should work to out this whole database in MSSQL server.
> I have run this command from command line:
> osql -u sa -i mysqldump.sql
> It is going since yesterday. It has been almost 36 hours that it's
> started. And in the mssql server, I see no database created yet. On the
> screen of the command line, I see bunch of numbers are going in order. I
> assume they are row numbers of the tables processed. But, if it is doing
it,
> then where is it saving all this data ? I have checked the tempdb, pub db,
> other dbs, and I see no tables related to the database I am inserting.
Will
> it populate it at the and of the job ? Or, am I doing something wrong
here
> ?
> Regards.
> Murtix.
>|||Hi ,
You cant use that file (generated using MYSQLDUMP) directly in MSSQL using
OSQL. This raw file can be used only in MYSQL incase you need a recovary.
OSQL program can take only TSQL commands.
The solution for you is:
1. Create a database in MSSQL server Manually
2. Use DTS to transfer Tables to MSSQL Server
If your table size is very huge , generate a text file in MYSQL for each
tables and use BCP IN to load inside MSSQL.
Thanks
Hari
MCDBA
"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:3fee42a7$1_3@.athenanews.com...
> Hi,
> I have dumped a very large database from mysql (using mysqldump
program)
> as a raw sql file. The reason was, convert this database to a MSSQL
> database. Since mysqldump creates the file as raw sql file with the
> database-table structures and the data in it, I thought using OSQL command
> line utilities should work to out this whole database in MSSQL server.
> I have run this command from command line:
> osql -u sa -i mysqldump.sql
> It is going since yesterday. It has been almost 36 hours that it's
> started. And in the mssql server, I see no database created yet. On the
> screen of the command line, I see bunch of numbers are going in order. I
> assume they are row numbers of the tables processed. But, if it is doing
it,
> then where is it saving all this data ? I have checked the tempdb, pub db,
> other dbs, and I see no tables related to the database I am inserting.
Will
> it populate it at the and of the job ? Or, am I doing something wrong
here
> ?
> Regards.
> Murtix.
>|||I see.
Thanks.
It is a mysqldump file so, there is not any t-sql scripts in it. (AFAIK
T-SQL is only being used by MSSQL, and I dont think mysql could create a
raw-sql file in that format) That means I have been doing nothing :-(.
But just of a curiosity. what is the numbers I am seeing on the screen
though ? I mean, could you tell me what is the computer processing now ?
Murtix.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eqe$00OzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> Hi Murtix
> You cannot load into SQL Server anything other than a SQL Server backup.
> Is mysqldump the actual backup file? If so, then what you are seeing in
the
> line number is osql thinking it is receiving a command, and it keep
> receiving them, waiting to receive a 'go' to tell it to execute what it
has
> received. It will not even try to process whatever bits are in the
mysqldump
> file until it finally encounters a 'go'.
> The contents of an input file passed to osql (with -i) needs to be a SQL
> script, containing valid Transact-SQL statements. Even if you had a valid
> SQL Server backup file, the input file to osql would have to be script in
> the TSQL language telling SQL Server to restore a specified backup file,
you
> could not just pass in the backup file itself.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> news:3fee42a7$1_3@.athenanews.com...
> > Hi,
> >
> > I have dumped a very large database from mysql (using mysqldump
> program)
> > as a raw sql file. The reason was, convert this database to a MSSQL
> > database. Since mysqldump creates the file as raw sql file with the
> > database-table structures and the data in it, I thought using OSQL
command
> > line utilities should work to out this whole database in MSSQL server.
> >
> > I have run this command from command line:
> >
> > osql -u sa -i mysqldump.sql
> >
> > It is going since yesterday. It has been almost 36 hours that it's
> > started. And in the mssql server, I see no database created yet. On the
> > screen of the command line, I see bunch of numbers are going in order. I
> > assume they are row numbers of the tables processed. But, if it is doing
> it,
> > then where is it saving all this data ? I have checked the tempdb, pub
db,
> > other dbs, and I see no tables related to the database I am inserting.
> Will
> > it populate it at the and of the job ? Or, am I doing something wrong
> here
> > ?
> >
> > Regards.
> >
> > Murtix.
> >
> >
>|||Hi Hari,
all the pain I suffered to do this, was because of not to use BCP method.
I guess I will have to use it after finding out osql methid would not work.
Thank you clearifying this issue for me.
Best Regards.
Murtix.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Oaj1x2OzDHA.1412@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> You cant use that file (generated using MYSQLDUMP) directly in MSSQL using
> OSQL. This raw file can be used only in MYSQL incase you need a recovary.
> OSQL program can take only TSQL commands.
> The solution for you is:
> 1. Create a database in MSSQL server Manually
> 2. Use DTS to transfer Tables to MSSQL Server
> If your table size is very huge , generate a text file in MYSQL for each
> tables and use BCP IN to load inside MSSQL.
> Thanks
> Hari
> MCDBA
>
>
>
> "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> news:3fee42a7$1_3@.athenanews.com...
> > Hi,
> >
> > I have dumped a very large database from mysql (using mysqldump
> program)
> > as a raw sql file. The reason was, convert this database to a MSSQL
> > database. Since mysqldump creates the file as raw sql file with the
> > database-table structures and the data in it, I thought using OSQL
command
> > line utilities should work to out this whole database in MSSQL server.
> >
> > I have run this command from command line:
> >
> > osql -u sa -i mysqldump.sql
> >
> > It is going since yesterday. It has been almost 36 hours that it's
> > started. And in the mssql server, I see no database created yet. On the
> > screen of the command line, I see bunch of numbers are going in order. I
> > assume they are row numbers of the tables processed. But, if it is doing
> it,
> > then where is it saving all this data ? I have checked the tempdb, pub
db,
> > other dbs, and I see no tables related to the database I am inserting.
> Will
> > it populate it at the and of the job ? Or, am I doing something wrong
> here
> > ?
> >
> > Regards.
> >
> > Murtix.
> >
> >
>|||Murtix,
> It is a mysqldump file so, there is not any t-sql scripts in it.
> (AFAIK T-SQL is only being used by MSSQL, and I dont think mysql
> could create a raw-sql file in that format) That means I have
> been doing nothing :-(.
Please take a look at this product. It is probably the best solution
for your problem. You can download the evaluation version.
http://www.ispirer.com/products/
> But just of a curiosity. what is the numbers I am seeing on the
> screen though ? I mean, could you tell me what is the computer
> processing now ?
As Kalen said, osql is trying to interpret the file as a series of
T-SQL statements. The numbers you see are line numbers of "lines"
that osql has parsed. They can be suppressed wih the -n command line
switch.
Linda|||Hi Murtix
What exactly do you mean by 'raw' sql file? SQL is a language, so the only
thing I can think of 'raw' SQL meaning is a file of ANSI standard SQL
commands.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:3fee4677$1_1@.athenanews.com...
> I see.
> Thanks.
> It is a mysqldump file so, there is not any t-sql scripts in it. (AFAIK
> T-SQL is only being used by MSSQL, and I dont think mysql could create a
> raw-sql file in that format) That means I have been doing nothing :-(.
> But just of a curiosity. what is the numbers I am seeing on the screen
> though ? I mean, could you tell me what is the computer processing now ?
> Murtix.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eqe$00OzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > Hi Murtix
> >
> > You cannot load into SQL Server anything other than a SQL Server backup.
> >
> > Is mysqldump the actual backup file? If so, then what you are seeing in
> the
> > line number is osql thinking it is receiving a command, and it keep
> > receiving them, waiting to receive a 'go' to tell it to execute what it
> has
> > received. It will not even try to process whatever bits are in the
> mysqldump
> > file until it finally encounters a 'go'.
> >
> > The contents of an input file passed to osql (with -i) needs to be a SQL
> > script, containing valid Transact-SQL statements. Even if you had a
valid
> > SQL Server backup file, the input file to osql would have to be script
in
> > the TSQL language telling SQL Server to restore a specified backup file,
> you
> > could not just pass in the backup file itself.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> > news:3fee42a7$1_3@.athenanews.com...
> > > Hi,
> > >
> > > I have dumped a very large database from mysql (using mysqldump
> > program)
> > > as a raw sql file. The reason was, convert this database to a MSSQL
> > > database. Since mysqldump creates the file as raw sql file with the
> > > database-table structures and the data in it, I thought using OSQL
> command
> > > line utilities should work to out this whole database in MSSQL server.
> > >
> > > I have run this command from command line:
> > >
> > > osql -u sa -i mysqldump.sql
> > >
> > > It is going since yesterday. It has been almost 36 hours that it's
> > > started. And in the mssql server, I see no database created yet. On
the
> > > screen of the command line, I see bunch of numbers are going in order.
I
> > > assume they are row numbers of the tables processed. But, if it is
doing
> > it,
> > > then where is it saving all this data ? I have checked the tempdb, pub
> db,
> > > other dbs, and I see no tables related to the database I am inserting.
> > Will
> > > it populate it at the and of the job ? Or, am I doing something wrong
> > here
> > > ?
> > >
> > > Regards.
> > >
> > > Murtix.
> > >
> > >
> >
> >
>|||Murtix Van Basten (nospam@.nospam.org) writes:
> It is a mysqldump file so, there is not any t-sql scripts in it. (AFAIK
> T-SQL is only being used by MSSQL, and I dont think mysql could create a
> raw-sql file in that format) That means I have been doing nothing :-(.
> But just of a curiosity. what is the numbers I am seeing on the screen
> though ? I mean, could you tell me what is the computer processing now ?
OSQL is also intended to be a interactive utility, so when you start
OSQL without the -i switch, you can enter commands. The 1>, 2> etc are
the command prompts, one per line. As Linda said, you can use -n to
suppress these. You can also use -o to redirect output to a file, so
you can look at the error messages.
However, since MySQL is not like to generate SQL suitable for MS SQL Server,
the whole operation appears to be a fruitless one. You could possibly use
the file, but you would have to edit it manually to address changes
between the SQL dialects and to insert GO statements to separate
batches.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Running a batch command using scheduler

Hi,

I have a batch file that runs an isql script. The sql accepts command line arguments as parameters. I want to invoke this batch file using windows scheduler. How do I pass parameters to my sql? Somebody please help.

Thanks.

I am not sure about the batch file idea, but SQL server has a job scheduler built in called sql agent. You would be much better using this to call a stored procedure and pass in the arguments. To get you started, try this:

http://msdn2.microsoft.com/en-us/library(d=robot)/ms189237.aspx

Otherwise, why not replace your batch file with a .NET console application that takes command line parameters and passes them to a stored procedure call. Batch files are old, and while they still have some uses, there are much better ways of achieving what you are trying to do in an easier way, which gives you more power and flexibility.

There is a way to run sql scripts from the commandline using an exe supplied with sql server. For information on this, check out:

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

HTH

For more T-SQL tips, check out my home page:|||Thanks for the reply :)

Running a .rpt file from dos

Can someone tell me if it's possible to run a CR .rpt file from the MS DOS command line? I'd like to run it (in a .bat file) and then save the output, to disc.I wonder if there is a way to do this
Why dont you use Export functionality?|||Hello Madhi: Can you elaborate on your idea? How would I invoke the Export functionality, from DOS?|||Open the report and from export option export the data|||What syntax do I use? I want to do this from the command line, not from within the IDE.

running .sql script from command line

how do I run a file with an .sql extension from the command window in windows 2k server?
Hi,
Use OSQL utility from command prompt
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql -o
output.log
Thanks
Hari
MCDBA
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:0FB9BC49-83D4-416D-889C-00A024A07079@.microsoft.com...
> how do I run a file with an .sql extension from the command window in
windows 2k server?

Runnable master awaiting command

Hi,
Database Server running slow. I used SP_WHO2 ACTIVE and the result
has many of

XXXX SA RUNNABLE MASTER AWAITING
COMMAND........
(XXXX is spid)

and they stay for couples seconds.

Last week , the server was running fine and during weekend ,none
of errors record in Windows
event log or SQL Server log.

Server is Windows 2003 server with 4 CPUs of xeon , 4 GB of memory
and
MSSQL SERVER 200 SP4.

I found out that every runnable master db tasks has shared lock on
key of
'master.dbo.sysxlogins.sysxlogins' object

Is this the problem of slow running database server?

Please advice.

Peeud

Quote:

Originally Posted by

Database Server running slow. I used SP_WHO2 ACTIVE and the result
has many of
>
XXXX SA RUNNABLE MASTER AWAITING
COMMAND........
(XXXX is spid)
>
and they stay for couples seconds.

As long as these entries only stay for a couple of seconds it means
that whatever is opening these connections is closing it again. I would
start to worry if these connections don't close and are increasing in
numbers.

Also as long as the BlkBy column doesn't have an entry then it means
that the these processes aren't being blocked, which is a good thing.

Quote:

Originally Posted by

I found out that every runnable master db tasks has shared lock on
key of
'master.dbo.sysxlogins.sysxlogins' object
>
Is this the problem of slow running database server?

When you mean slow running database server are you referring to
Windows, or SQL Server when you pull a query? If you are referring to
Windows, use Windows Task Manager to ensure that there are no processes
using large amounts of CPU or Memory resources. If you are referring to
SQL Server, use SQL Profiler to determine what queries are running and
see if some performance tuning can be done on those tables.

Regards,
Louis

Run Report via Command Line

Is it possible to run a .rdl report via command line? What would be
the syntax?
Thanks
BobOn Apr 10, 7:25 am, "Bob" <b...@.ebearings.com> wrote:
> Is it possible to run a .rdl report via command line? What would be
> the syntax?
> Thanks
> Bob
In SSRS 2000, it could be done via rs.exe at the command line;
however, in SSRS 2005 it will take a bit more work. This link might be
helpful:
http://msdn2.microsoft.com/ko-kr/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.aspx
Regards,
Enrique Martinez
Sr. Software Consultant

RUN NET START/STOP MSSQLSERVER FROM SSIS PACKAGE

HELLO,

I want to create a package which start and stop the SQL server's services... i know i can achive this via NET COMMAND.... but i coudnt find in which task (SSIS) I can place that command?..

I also came across that I can achieve this using Execute Process task but for this I have to define executable file.... actually i dont want ne thing outside from my SSIS package

CAN I ACHIEVE THIS WITH IN SSIS PACKAGE?

is there ne other alternative?

regards,

Anas

Execute "cmd.exe" and use the following arguments:

"/C net send ..."

Try running from the command line to see what I'm talking about.

cmd.exe /C net send ...|||

Phil Brammer wrote:

Execute "cmd.exe" and use the following arguments:

"/C net send ..."

Try running from the command line to see what I'm talking about.

cmd.exe /C net send ...

what do u mean by that? where shud i run that command? can u please explain it in more details...

regards,

Anas

|||In the execute process task. The executable is "cmd.exe" and the arguments are "/C net start..."

|||You can start and stop services using the WMI connection manager. The WMI connection manager's acquires a ManagementScope. Other connection manager's acquire SqlConnections, MessageQueues, and so on.

Add a WMI connection manager, named "WMI" with the following connection string, built dynamically as the case may be.

ServerName=\\localhost;Namespace=\root\cimv2:Win32_Service.Name="MSSQLSERVER";UseNtAuth=True;UserName=;

Use the connection manager in a task (custom or script) to invoke any management call (including service management). I left out handling of dependent services here (for example, SQL Agent is a dependent service), but this may suffice for the general concept.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Management ' Add reference to System.Management.dll

Public Class ScriptMain

Private wmiScope As ManagementScope

Private svc As ManagementObject

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

wmiScope = DirectCast(Dts.Connections("WMI").AcquireConnection(Nothing), _

ManagementScope)

wmiScope.Connect()

svc = New ManagementObject(wmiScope, wmiScope.Path, Nothing)

If CType(svc("State"), String) = "Running" Then

svc.InvokeMethod("StopService", Nothing)

End If

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

|||

jaegd wrote:

You can start and stop services using the WMI connection manager. The WMI connection manager's acquires a ManagementScope. Other connection manager's acquire SqlConnections, MessageQueues, and so on.

Add a WMI connection manager, named "WMI" with the following connection string, built dynamically as the case may be.

ServerName=\\localhost;Namespace=\root\cimv2:Win32_Service.Name="MSSQLSERVER";UseNtAuth=True;UserName=;

Use the connection manager in a task (custom or script) to invoke any management call (including service management). I left out handling of dependent services here (for example, SQL Agent is a dependent service), but this may suffice for the general concept.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Management ' Add reference to System.Management.dll

Public Class ScriptMain

Private wmiScope As ManagementScope

Private svc As ManagementObject

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

wmiScope = DirectCast(Dts.Connections("WMI").AcquireConnection(Nothing), _

ManagementScope)

wmiScope.Connect()

svc = New ManagementObject(wmiScope, wmiScope.Path, Nothing)

If CType(svc("State"), String) = "Running" Then

svc.InvokeMethod("StopService", Nothing)

End If

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

what do you means by "' Add reference to System.Management.dll"... actually i geting error on ManagementScope ((blue under line)

can u please tell me ?

thanx for ur support

regards,

Anas

|||

There are about 4 ways to add an assembly reference in a given script task. There may be others for all I know.

1. Project Menu/Add reference ... to System.Management.dll

2. View menu/Project explorer. Right click (get context menu) on the project explorer References node. Chose assembly...

3. View menu/Object Browser. Browse to System.Management assembly. Click on the "+" toolbar strip icon.

4. View menu/Class view. Right click (get context menu) on References node. Choose assembly...

RUN NET START/STOP MSSQLSERVER FROM SSIS PACKAGE

HELLO,

I want to create a package which start and stop the SQL server's services... i know i can achive this via NET COMMAND.... but i coudnt find in which task (SSIS) I can place that command?..

I also came across that I can achieve this using Execute Process task but for this I have to define executable file.... actually i dont want ne thing outside from my SSIS package

CAN I ACHIEVE THIS WITH IN SSIS PACKAGE?

is there ne other alternative?

regards,

Anas

Execute "cmd.exe" and use the following arguments:

"/C net send ..."

Try running from the command line to see what I'm talking about.

cmd.exe /C net send ...|||

Phil Brammer wrote:

Execute "cmd.exe" and use the following arguments:

"/C net send ..."

Try running from the command line to see what I'm talking about.

cmd.exe /C net send ...

what do u mean by that? where shud i run that command? can u please explain it in more details...

regards,

Anas

|||In the execute process task. The executable is "cmd.exe" and the arguments are "/C net start..."

|||You can start and stop services using the WMI connection manager. The WMI connection manager's acquires a ManagementScope. Other connection manager's acquire SqlConnections, MessageQueues, and so on.

Add a WMI connection manager, named "WMI" with the following connection string, built dynamically as the case may be.

ServerName=\\localhost;Namespace=\root\cimv2:Win32_Service.Name="MSSQLSERVER";UseNtAuth=True;UserName=;

Use the connection manager in a task (custom or script) to invoke any management call (including service management). I left out handling of dependent services here (for example, SQL Agent is a dependent service), but this may suffice for the general concept.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Management ' Add reference to System.Management.dll

Public Class ScriptMain

Private wmiScope As ManagementScope

Private svc As ManagementObject

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

wmiScope = DirectCast(Dts.Connections("WMI").AcquireConnection(Nothing), _

ManagementScope)

wmiScope.Connect()

svc = New ManagementObject(wmiScope, wmiScope.Path, Nothing)

If CType(svc("State"), String) = "Running" Then

svc.InvokeMethod("StopService", Nothing)

End If

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

|||

jaegd wrote:

You can start and stop services using the WMI connection manager. The WMI connection manager's acquires a ManagementScope. Other connection manager's acquire SqlConnections, MessageQueues, and so on.

Add a WMI connection manager, named "WMI" with the following connection string, built dynamically as the case may be.

ServerName=\\localhost;Namespace=\root\cimv2:Win32_Service.Name="MSSQLSERVER";UseNtAuth=True;UserName=;

Use the connection manager in a task (custom or script) to invoke any management call (including service management). I left out handling of dependent services here (for example, SQL Agent is a dependent service), but this may suffice for the general concept.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Management ' Add reference to System.Management.dll

Public Class ScriptMain

Private wmiScope As ManagementScope

Private svc As ManagementObject

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

wmiScope = DirectCast(Dts.Connections("WMI").AcquireConnection(Nothing), _

ManagementScope)

wmiScope.Connect()

svc = New ManagementObject(wmiScope, wmiScope.Path, Nothing)

If CType(svc("State"), String) = "Running" Then

svc.InvokeMethod("StopService", Nothing)

End If

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

what do you means by "' Add reference to System.Management.dll"... actually i geting error on ManagementScope ((blue under line)

can u please tell me ?

thanx for ur support

regards,

Anas

|||

There are about 4 ways to add an assembly reference in a given script task. There may be others for all I know.

1. Project Menu/Add reference ... to System.Management.dll

2. View menu/Project explorer. Right click (get context menu) on the project explorer References node. Chose assembly...

3. View menu/Object Browser. Browse to System.Management assembly. Click on the "+" toolbar strip icon.

4. View menu/Class view. Right click (get context menu) on References node. Choose assembly...

Run DTS package

Hello !

I have a DTS package which I can run it fine from command line using
one SQL account who is not a also a user in the windows 2003
machine.If I try as another user that is a user or sysadmin account in
the box then I get a login error from OLE. Any ideas? When I use the
Enterprise Manager to run the package it runs fine regardless of the
user.

Many Thanks,
Marios Koumides"Marios Koumides" <koumides@.gmail.com> wrote in message
news:f0e6dfb6.0504180147.2807f984@.posting.google.c om...
> Hello !
> I have a DTS package which I can run it fine from command line using
> one SQL account who is not a also a user in the windows 2003
> machine.If I try as another user that is a user or sysadmin account in
> the box then I get a login error from OLE. Any ideas? When I use the
> Enterprise Manager to run the package it runs fine regardless of the
> user.
>
> Many Thanks,
> Marios Koumides

It's not really clear (to me) from your description exactly who is running
the package, and where it's executing. Are you running it with dtsrun.exe?
Are you running it on the server, or on your workstation? Can the users who
cannot run the package log in to MSSQL, or only to Windows? Are you using
Windows authentication or SQL logins for MSSQL? What exactly is the error
message? Which version of MSSQL do you have?

As a complete guess, the package contains MSSQL connection objects which use
Windows authentication, so it fails when you run it with a Windows user
which cannot log in to those MSSQL servers. But without more information,
that may be totally wrong.

Simon|||Simon Hayes wrote:
> "Marios Koumides" <koumides@.gmail.com> wrote in message
> news:f0e6dfb6.0504180147.2807f984@.posting.google.c om...
> > Hello !
> > I have a DTS package which I can run it fine from command line
using
> > one SQL account who is not a also a user in the windows 2003
> > machine.If I try as another user that is a user or sysadmin account
in
> > the box then I get a login error from OLE. Any ideas? When I use
the
> > Enterprise Manager to run the package it runs fine regardless of
the
> > user.
> > Many Thanks,
> > Marios Koumides
> It's not really clear (to me) from your description exactly who is
running
> the package, and where it's executing. Are you running it with
dtsrun.exe?

I tried 2-3 users and they can run the package using dtsrun.exe but
providing a username and password different from theirs.(This username
and password is not a windows user but MS-SQL one)

> Are you running it on the server, or on your workstation?
Running it on the server.

> Can the users who cannot run the package log in to MSSQL, or only to
>Windows?
They can log to both SQL and Windows

Are you using Windows authentication or SQL logins for MSSQL? What
exactly is the error
> message? Which version of MSSQL do you have?
Yes I am using windows authentication in MS SQL 2000.
The error message is they can't login MS SQL 2000

Many Thanks,
Marios Koumides

> As a complete guess, the package contains MSSQL connection objects
which use
> Windows authentication, so it fails when you run it with a Windows
user
> which cannot log in to those MSSQL servers. But without more
information,
> that may be totally wrong.
> Simon

run backup (maintenance plan) from Batch (.bat) file

What is the method to execute backups from batch (.bat) files on the server running SQL Server. I have tried the sqlmaint command - doesn't seem to execute, looked into the xp_sqlmaint with no luck. I'm sure the problem lies in my lack of DOS batch programming skills. If anyone has an example of a batch file that executes a backup would you mind sharing. thanksHave you tried using OSQL from within a batch file?|||just stumbled onto that option this morning. I'm not much of a SQL guru, tring to set up some backups of DB contained in SQL from an COTS package. Do you know how this would all fit together? It appears I would create a file with (possiblely) the SQLMAINT (statement and parms) contained within. Then execute it from within the OSQL statement. Does this sound like I'm on the right track? Do you use the OSQL statement in any .BAT files? I would love to see an example of a (.BAT & sqlmaint) file that I could mimic the context of.|||personally I do not use maintenance plans. I script out all of my maintenance using BACKUP and DBCC t-sql statements. And yu can probably do this with osql in a batch file.

Why may I ask are you taking this approach? Do you need to schedule these to run at a specific time? If so, why not use a sql server agent job?|||I want to have it executed by the server backup software right before the backup system: tivoli, TSM, (not sure of the specific tool) runs. I also want to include some file ZIPs and versioning (file rolls - moves - to specific folders) to mimic the backup/DR process used for other products on the server (a comprehebnsive backup/recovery porocess used for our COTS tools)|||you can use jobs in the sql server agent to do all of that. I would go to SQL Server books Online-->Contents-->Administering SQL Server--> Automating Administrative Tasks-->Implimenting Jobs and do some reading.

you may also want to read xp_cmdshell for the file copies|||thanks I will...I just can't believe that there isn't a fairly easy way to do it extrnally so that I can leverage my existing BAT files and scheduling software (autosys)

Run As Command

I need to copy a backup file from server to server in seperate domains.
Unfortunately in my company they do not have trusted connections. So I need
to use a seperate logon for the destination server. I have tried a mapped
drive that is mapped as a valid logon in thatdomain with no luck, I get
"cannot find the drive specified." Is there a way thorugh sql server to run
a
job or a copy as something different than the SQL Agent account. FYI, the
logon that I use to logon to the other domain cannot be registered as a logo
n
to SQL server bacause SQL Server cannot see that domain. Any suggestions?Not sure of all the details on the mapped drive - you had no
luck where? Can you create a mapped drive and access it from
your domain with a particular login and password?
If you have that, you could execute net use to map a drive
and specify a domain\user and password. Then copy the file
to that mapped drive. Then delete the mapped drive when
done.
-Sue
On Mon, 21 May 2007 09:11:00 -0700, Rick
<Rick@.discussions.microsoft.com> wrote:

>I need to copy a backup file from server to server in seperate domains.
>Unfortunately in my company they do not have trusted connections. So I need
>to use a seperate logon for the destination server. I have tried a mapped
>drive that is mapped as a valid logon in thatdomain with no luck, I get
>"cannot find the drive specified." Is there a way thorugh sql server to run
a
>job or a copy as something different than the SQL Agent account. FYI, the
>logon that I use to logon to the other domain cannot be registered as a log
on
>to SQL server bacause SQL Server cannot see that domain. Any suggestions?|||Rick
here is how you can do it
SELECT @.VCHCOMMAND = 'net use v: \\10.1.97.150\sqlbackups$\SRDEV dbbackup
/user:XXXX\srdev_db_backup'
EXEC @.IRESULT = MASTER..XP_CMDSHELL @.VCHCOMMAND, NO_OUTPUT
where v: is the share name
dbbackup : password to connect \\10.1.97.150\sqlbackups$\SRDEV
XXXX\srdev_db_backup : is the user login on the destination server
if you are using BACKUP DATABASE command to backup the database then
issue the above command first and then backup up the database on to v:
hope this helps you
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:042AEE4F-6267-4EA8-8BB6-4609B32409DB@.microsoft.com...
>I need to copy a backup file from server to server in seperate domains.
> Unfortunately in my company they do not have trusted connections. So I
> need
> to use a seperate logon for the destination server. I have tried a mapped
> drive that is mapped as a valid logon in thatdomain with no luck, I get
> "cannot find the drive specified." Is there a way thorugh sql server to
> run a
> job or a copy as something different than the SQL Agent account. FYI, the
> logon that I use to logon to the other domain cannot be registered as a
> logon
> to SQL server bacause SQL Server cannot see that domain. Any suggestions?|||This is what I came up with after a lot of trial and error
Declare @.VCHCOMMAND Char (150),
@.CMD Char (50),
@.COPY Char (50)
Set @.VCHCOMMAND = 'net use L: *\\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples'
Set @.COPY = 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
Set @.CMD = @.VCHCOMMAND + ' Y ' + @.COPY
EXEC MASTER..XP_CMDSHELL @.CMD
, but I get the following error:
L: has a remembered connection to \\172.17.9.67\backup. Do you
want to overwrite the remembered connection? (Y/N) [Y]:
No valid response was provided.
NULL
Any Suggestions?
"vt" wrote:

> Rick
> here is how you can do it
> SELECT @.VCHCOMMAND = 'net use v: \\10.1.97.150\sqlbackups$\SRDEV dbbackup
> /user:XXXX\srdev_db_backup'
> EXEC @.IRESULT = MASTER..XP_CMDSHELL @.VCHCOMMAND, NO_OUTPUT
> where v: is the share name
> dbbackup : password to connect \\10.1.97.150\sqlbackups$\SRDEV
> XXXX\srdev_db_backup : is the user login on the destination server
> if you are using BACKUP DATABASE command to backup the database then
> issue the above command first and then backup up the database on to v:
> hope this helps you
>
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:042AEE4F-6267-4EA8-8BB6-4609B32409DB@.microsoft.com...
>
>|||> L: has a remembered connection to \\172.17.9.67\backup. Do you
> want to overwrite the remembered connection? (Y/N) [Y]:
> No valid response was provided.
> NULL
> Any Suggestions?
First, remove the persistent connection:
EXEC master..xp_cmdshell 'net use L: /delete'
Then try:
--map drive
EXEC master..xp_cmdshell 'net use L: \\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples /persistent:no'
--copy
EXEC master..xp_cmdshell 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
--unmap drive
EXEC master..xp_cmdshell 'net use L: /delete'
I might be wrong but I think you could also do without the drive letter:
--establish security credentials
EXEC master..xp_cmdshell 'net use \\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples /persistent:no'
--copy
EXEC master..xp_cmdshell 'copy \\server2\Backup\DB\SIRETGUE.Bak
\\172.9.17.99\Backup\DB'
--unmap
EXEC master..xp_cmdshell 'net use \\172.9.17.99\Backup /delete'
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:6D7669E9-390B-46D4-8EAB-F03196899C3B@.microsoft.com...[vbcol=seagreen]
> This is what I came up with after a lot of trial and error
> Declare @.VCHCOMMAND Char (150),
> @.CMD Char (50),
> @.COPY Char (50)
> Set @.VCHCOMMAND = 'net use L: *\\172.9.17.99\Backup peoples123
> /user:sigueqa\rpeoples'
> Set @.COPY = 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
> Set @.CMD = @.VCHCOMMAND + ' Y ' + @.COPY
> EXEC MASTER..XP_CMDSHELL @.CMD
>
> , but I get the following error:
> L: has a remembered connection to \\172.17.9.67\backup. Do you
> want to overwrite the remembered connection? (Y/N) [Y]:
> No valid response was provided.
> NULL
> Any Suggestions?
> "vt" wrote:
>

Run a scheduled job from the command promt

Hi
I was wondering if it is possible to run a scheduele from the command
promt.
I have a job that runs every night that updates my sales in my
datawarehouse. That jobs executes several DTS-packages. But sometimes
some of my users wish to update the sales in the middle of the day.
So now I wondered if I could create a .bat file that executed the job.
I know that I can use the DTSRUN command to execute a package, but
thats not flexible enough for me
Regards and thanks in advance
FlemmingHi
Take a look at OSQL utility in the BOL.
"Flemming Nielsen" <flemming.delph@.gmail.com> wrote in message
news:fccae6c9.0506080010.5b48d526@.posting.google.com...
> Hi
> I was wondering if it is possible to run a scheduele from the command
> promt.
> I have a job that runs every night that updates my sales in my
> datawarehouse. That jobs executes several DTS-packages. But sometimes
> some of my users wish to update the sales in the middle of the day.
> So now I wondered if I could create a .bat file that executed the job.
> I know that I can use the DTSRUN command to execute a package, but
> thats not flexible enough for me
> Regards and thanks in advance
> Flemming

run a dos command?

Hi all,
I am just wondering is there a way to run a dos command in the stored procedure? Please fill me in on how to go about doing if is there is a way. If this is not the correct forum to post this question, please let me know. Thanks in advance.
Daren

Hi faren,

you can open up a shell with XP_cmdshell and execute commands via passing the command as a paramter:

EXEC MASTER.XP_CMDSHELL 'dir C:\'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Run a DTS Win32 Process Task hidden

Anyone?
I have a picky client who does not want to see the command window open when a batch file is called from DTS.
Can anyone help me in running it hidden?If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.|||That's what I was afrain of.

Originally posted by Satya
If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.

RUN "Intergration Services Deployment Manifest" through Command Prompt

Hi, I want to run "Intergration Services Deployment Manifest" through the command prompt, passing the values .. which should basically go and install that Package either on File System or SQL Server. Does any one have any idea on how to do that ...

Thanks,

You can use the dtutil command prompt utility to copy a package to the Microsoft SQL Server database, the SSIS Package Store, or the file system.

However, the package must already exist in one of these three locations.

For more information, see "dtutil Utility" at http://msdn2.microsoft.com/en-us/library/ms162820.aspx.

|||Thanks alot for your reply ..

RUN "Intergration Services Deployment Manifest" through Command Prompt

Hi, I want to run "Intergration Services Deployment Manifest" through the command prompt, passing the values .. which should basically go and install that Package either on File System or SQL Server. Does any one have any idea on how to do that ...

Thanks,

You can use the dtutil command prompt utility to copy a package to the Microsoft SQL Server database, the SSIS Package Store, or the file system.

However, the package must already exist in one of these three locations.

For more information, see "dtutil Utility" at http://msdn2.microsoft.com/en-us/library/ms162820.aspx.

|||Thanks alot for your reply ..

RSWebParts.cab install and Command Line Error

Hi all,
I have SQL Server 2005 installed on one server and WSS 3.0 installed
on another server. I would like to install the reporting services wbe
parts (RSWebParts.cab) to my WSS Server. I have copied the file across
and am using the following command:
:: ***********PARAMS***********
:: stsadm tool
SET STSADMTOOL="C:\Program Files\Common Files\Microsoft Shared\web
server extensions\12\bin\stsadm.exe"
GOTO STOP
:STOP
%STSADMTOOL% -help addwppack
pause;
echo using STSADMTOOL at %STSADMTOOL%
%STSADMTOOL% -o addwppack -force -filename C:\ReportingServices
\RSWebParts.cab
When I run it I get a Command Line Error and it points me in the
direction of stsadm help. I can guarantee you the files are in the
correct location as per the parameters passed in and that I am a local
admin on the server.
This is driving me nuts, can anybody help asap?
Cheers
JimskiI have the same problem.  I am losing faith in Microsoft.  Have you ever found a solution?