Can anyone say whether "EXEC xp_cmdshell (sql script)" is supposed to work from within ssmse2k8?

Question

0.00/5 (No votes)

See more:

This setup of a variable for use as a filename doesn't hold any water if I try to execute it from the script editor as a line item.

SQL

DECLARE @scriptfile varchar(4000)
SET @scriptfile = 'c:\users\fith.sql'
EXECUTE xp_cmdshell @scriptfile

[EXEC sp_configure 'xp_cmdshell', 1 ... has been done]

The interface enters either a long execute wait phase out of which it never returns or it completes successfully there at the message bar but doesn't actually change anything in the database per the code.

Another switch?

WSServer2K8 R2
SSMSE 64 bit

Posted 21-Dec-11 9:29am

RedDk

Updated 21-Dec-11 9:32am

Wendelius

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-12-21T09:37:00

Solution 1

The xp_cmdshell executes an OS command so basically if you execute a SQL script using xp_cmdshell, it tries to execute the SQL statements in command prompt.

If you want to run a SQL script, try executing SQLCMD[^] using xp_cmdshell and give the sql script to sqlcmd as a parameter.

So something like:

SQL

SET @scriptfile = 'sqlcmd -i c:\users\fith.sql' -S... -U...

Posted 21-Dec-11 9:37am

Wendelius

Comments

RedDk 21-Dec-11 20:26pm

Yes,

Thanks Mika. I've done this before. The form "EXEC xp_cmdshell sqlcmd -S machine\instance -i c:\users\fith.sql". The thing I was trying to recall was actually WHY I was avoiding that :out (.bat) outside of the shell.

It just seemed like there should have been a shell but not in that batch run sense.