Executing query/SPs thru Command line

Question

Executing query/SPs thru Command line

Tac11

SSCertifiable

Points: 6980
More actions
September 23, 2015 at 3:24 pm

#320858

Hi experts,
How do I execute below scripts using CMD:
Please Note: SQL server 2008r2, default instance with window Authentication.
1. Select * from Adventurewoks2008.Person.Person where Id = 5
2. Exec usp_getemployee (with parameter Manager_id = 100)
3. I have a folder named 'MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C). How do I execute all 3 using CMD against AdventureWorks database.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

Tac11 (9/23/2015)
Hi experts,
How do I execute below scripts using CMD:
Please Note: SQL server 2008r2, default instance with window Authentication.
1. Select * from Adventurewoks2008.Person.Person where Id = 5
2. Exec usp_getemployee (with parameter Manager_id = 100)
3. I have a folder named 'MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C). How do I execute all 3 using CMD against AdventureWorks database.

I have to first ask why you want to do these things from the Command Line.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tac11 SSCertifiable Points: 6980 More actions · Answer 2

Just some questions, with different scenario. Would appreciate if you post answer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Smells like a homework question to me. Have you looked at sqlcmd? If so, what have you tried? By the way, a folder contains subfolders and/or files. A database contains stored procedures. Please will you be more specific about what is in your folder in item 3?

John

Lowell SSC Guru Points: 323546 More actions · Answer 4

sqlcmd or bcp are the two command line options.

bcp is designed for importing or exporting data, whereas sqlcmd can do both, but also execute commands;

i've used use sqlcmd to run backup commands, for example, on express instances that don't have SQL Agent and job capabilities.

if you are just executing the procedures, with no output, you'd use sqlcmd only.

it sounds more complex though, since your procs are files and not something already on the server, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

You can simply use the SQL Server command object from Powershell to make these calls. It's easy. Here are some examples.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Tac11 SSCertifiable Points: 6980 More actions · Answer 6

Wow. Just asked simple questions, no answer but too much noise!!!!

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 7

Tac11 (9/24/2015)
Wow. Just asked simple questions, no answer but too much noise!!!!

Not sure what you're looking for? The link I posted shows how to call stored procedures from the command line using Powershell. Done.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323546 More actions · Answer 8

and here's a basic example for the command line:

sqlcmd -S . -d master -E -Q "exec sp_who"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tac11 SSCertifiable Points: 6980 More actions · Answer 9

Thanks Lowell.

@Grant. I had posted my questions, I am not looking for Powershell script.

Server 'Myserver', 'default instance' with 'window Authentication'. I want below queries to execute on CMD.

1. Select * from Adventurewoks2008.Person.Person where Id = 5

2. Exec 'usp_getemployee' with parameter Manager_id = 100

3. I have a folder named in 'C:\MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C). How do I execute all 3 using CMD against AdventureWorks database. just need to execute don't need results.

Hope you understand my question.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 10

Tac11 (9/24/2015)
Wow. Just asked simple questions, no answer but too much noise!!!!

No problem. Moving on. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 11

Tac11 (9/24/2015)
@Grant. I had posted my questions, I am not looking for Powershell script.

Powershell isn't run from the command line?

OK.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323546 More actions · Answer 12

there was a recent article that demonstrated how to run all *.sql files in a given folder.

that means the scripts contain commands that already have to have the parameters to call.

look at this article from not too long ago:

http://www.sqlservercentral.com/articles/sqlcmd+mode/129764/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!