How do I launch an agent job from command line?

What is the easiest way to execute a SQL Agent (2005) job from the command line? I need to make a batch file to execute a reporting service shared schedule job.

Thanks in advance.

Refer to Books Online, Topic: 'DTExec utility'

DTExec.exe is the replacement for SQL 2000's DTSRun.exe.

|||Correct me if i'm wrong, but I think DTExec is just used for SSIS packages? I need to run the entire job. The job was generated by Reporting Services and I need to call it manually. Thanks a lot.|||

You're correct. I mis-interpreted your question.

You can use SQLCmd.exe, combined with a call to sp_startJob.

(If you are using SQL 2000, then use OSQL.exe instead of SQLCmd.)

Refer to Books Online for the complete syntax for either.

|||

Thanks a lot, I couldn't get the stored procedure to work but i just used the code that it mentioned in the Job properties window. The exact code for sqlcmd was: sqlcmd -Q "exec ReportServer.dbo.AddEvent @.EventType ='SharedSchedule', @.EventData='0dd48b88-6c35-4f3c-913e-b5299de948db'"

Thanks much.

How Do I give EXECUTE Permissions on Stored Procedures?

Hey guys,

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!alvinstraight38@.hotmail.com wrote:

Quote:

Originally Posted by

Hey guys,
>
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
>

if you right click on the SP and click properties, that should bring up
the options.

--sharif|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

Then again, in the long run you are better of using GRANT commands.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>
If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.
>
Then again, in the long run you are better of using GRANT commands.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:

Microsoft SQL Server Management Studio Express Version 9.00.2047.00|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:

Quote:

Originally Posted by

Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00

That's SP1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sep 20, 10:26 pm, "alvinstraigh...@.hotmail.com"
<alvinstraigh...@.hotmail.comwrote:

Quote:

Originally Posted by

On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>
>
>

Quote:

Originally Posted by

alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:

Quote:

Originally Posted by

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

>

Quote:

Originally Posted by

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

>

Quote:

Originally Posted by

Then again, in the long run you are better of using GRANT commands.

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00- Hide quoted text -
>
- Show quoted text -

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')

/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')|||abu hisham wrote:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

To clarify, this will not directly grant the permissions, but will
output SQL code that can be copy+pasted into Query Analyzer and
executed to grant the permissions.|||abu hisham (yjogee@.hotmail.co.uk) writes:

Quote:

Originally Posted by

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

In SQL 2005 this can be achieved with a single statement:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::schema_name TO user

Access granted on schema level are inherited by objects in the schema, which
means that it also applies to future objects.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How do I get DESC order?

Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.

How do I keep it in DESC order for viewing? Here's the statement:

SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC

By the way, I'm using SQL Server Express, if it makes any difference.

|||

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

|||

Andrea Montanari wrote:

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

Thank you for the very informative response as well as the links, Andrea. I was starting to go insane wondering what I was missing! Another day another lesson, I guess!

Once again, thanks for the info.

How do I execute a stored Procedure using SSIS

Please anyone help me with my question

How do I execute a stored Procedure using SSIS ?

I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in Visual studio 2005

Thanks,

George

It's been awhile, but don't you just type in this into the sql statement slot:

exec my_procedure_name_here

|||

Thanks for your help

How do I do thisexec my_procedure_name_here

What I have to do in SSIS the steps..please

Thanks again

George

How do I execute a .sql file from command promt

How do I execute a .sql file from command promt?
I googled and couldnt find a clue about it...
Please help

Quote:

Originally Posted by Bangaru

How do I execute a .sql file from command promt?
I googled and couldnt find a clue about it...
Please help

im not 100% sure, but i think you just type in the location of the file|||

Quote:

Originally Posted by Bangaru

How do I execute a .sql file from command promt?
I googled and couldnt find a clue about it...
Please help

you can use isql and osql command lines for accomplishing this .. you can also pass the server name and other parameters ..

for more info pls check sql books online ..|||see books online for isql utility|||

Quote:

Originally Posted by siva538

you can use isql and osql command lines for accomplishing this .. you can also pass the server name and other parameters ..

for more info pls check sql books online ..

Note, you will need to have the Client Tools installed (I'm a real idiot sometimes).

How do i Edit the Package Source file name Dynamically

Hi

I have created a package using DTS Designer to import the data from flat file to the SQL server.
i have sheduled the package to execute at every 1st day of the month and saved the the package to the local server.

i want to do the following steps dynamically from the Visual Basic

1. i am loading all the packages from the local server and also from the metadata services into a combo box. select one package from the combo box and i want to change the package shedule time from vb and i want to save the package back to the sql server.

2. when the package is executing at the schedule time at that time i want to change the source of the package that means flat filename(to different filename) and i need to execute the package and save the package.

how do i do this dynamically.

Thanks in advance

Regards
GandhiThe package schedule time is really a job schedule time - so you would use the sp_update_jobschedule stored procedure. For the 2nd part, when would the filename change and what would change it ?