using xp_cmdshell

[varshan09]

Hi,
We have a batch file which needs to be executed from query analyzer. We are trying to do this using xp_cmdshell. It does not seem to be working. When I run the .bat file through command prompt, it transfers the data and logs the trace in the file, as per expectation.

With xp_cmdshell, it is just printing the command in the output, it is not launching the command.

Can anybody help in this regard?
Thanks
Varsha

 

[hmckillop]

If it prints the output, it has executed the batch file.
Are you sure its not working, have you tried creating a simple batch file to copy a file or something similiar and then execute xp_cmdshell.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[varshan09]

Actually as part of the bat file, we are writing in a log file, since it is not writing in the log file, also it is not changing the data as per expectation.

Thanks
Varsha

 

[JamesLean]

Can you post the SQL code you are using to call the batch file? Also, can you tell us what the batch file is doing? In most cases like this the problem is that the SQL Service account does not have the necessary permissions.

--James

 

[varshan09]

Just to you an example, we have many steps as part of data migration -
1. Dropping all constraints
2. Running Create and Alter scripts to change the schema
3. Creating stored procedures
4. Creating all constraints
We have created different .sql files to perform these operation, and our batch file invokes these files using oSql utility.

The code for .bat file -
====================================
SET LOG=runsql.log
SET SRVNAME=BLRKEC28881D\SQLSERVER
SET DB=test_migratjon
SET USER=admin
SET PASS=admin
SET ERR_PATH=C:\ASDOS\Database\Install\Install_errors\
ECHO. >> %LOG%


osql -S%SRVNAME% -n -d%DB% -U%USER% -P%PASS% -i DropConstraints.sql -o %ERR_PATH%test_DataBase.err >> %LOG%

=====================================

code for DropConstraint.sql
=====================================
BEGIN TRANSACTION

DECLARE @strSQL varchar(300)



-- ==================================



PRINT '== Dropping FOREIGN KEY constraints'

DECLARE c_stmt CURSOR FOR

SELECT 'ALTER TABLE [' + OBJECT_NAME(id)+ '] DROP CONSTRAINT ' + OBJECT_NAME(constid) AS stmt

FROM sysconstraints

WHERE OBJECT_NAME(id) NOT LIKE 'dt%' AND (OBJECT_NAME(constid) like 'FK%' OR OBJECT_NAME(constid) like '%_FK')

OPEN c_stmt

FETCH NEXT FROM c_stmt INTO @strSQL

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXEC(@strSQL)

FETCH NEXT FROM c_stmt INTO @strSQL

END

CLOSE c_stmt

DEALLOCATE c_stmt

--Aborting the transaction in case of any error

IF @@Error<>0

BEGIN

ROLLBACK TRANSACTION

END

-- ==================================



PRINT '== Dropping PRIMARY KEY constraints'

DECLARE c_stmt CURSOR FOR

SELECT 'ALTER TABLE [dbo].[' + OBJECT_NAME(id)+ '] DROP CONSTRAINT ' + OBJECT_NAME(constid) AS stmt

FROM sysconstraints

WHERE OBJECT_NAME(id) NOT LIKE 'dt%' AND (OBJECT_NAME(constid) like 'PK%' or OBJECT_NAME(constid) like '%_PK')

OPEN c_stmt

FETCH NEXT FROM c_stmt INTO @strSQL

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXEC(@strSQL)

FETCH NEXT FROM c_stmt INTO @strSQL

END

CLOSE c_stmt

DEALLOCATE c_stmt

--Aborting the transaction in case of any error

IF @@Error<>0

BEGIN

ROLLBACK TRANSACTION

END

-- ==================================



PRINT '== Dropping DEFAULT constraints'

DECLARE c_stmt CURSOR FOR

SELECT 'ALTER TABLE [' + OBJECT_NAME(id) + '] DROP CONSTRAINT ' + OBJECT_NAME(constid) AS stmt

FROM sysconstraints

WHERE OBJECT_NAME(id) NOT LIKE 'dt%' AND OBJECT_NAME(constid) like 'DF%'

OPEN c_stmt

FETCH NEXT FROM c_stmt INTO @strSQL

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXEC(@strSQL)

FETCH NEXT FROM c_stmt INTO @strSQL

END

CLOSE c_stmt

DEALLOCATE c_stmt

--Aborting the transaction in case of any error

IF @@Error<>0

BEGIN

ROLLBACK TRANSACTION

END
-- ==================================



PRINT '== Dropping CHECK constraints'

DECLARE c_stmt CURSOR FOR

SELECT 'ALTER TABLE [' + OBJECT_NAME(id) + '] DROP CONSTRAINT ' + OBJECT_NAME(constid) AS stmt

FROM sysconstraints

WHERE OBJECT_NAME(id) NOT LIKE 'dt%' AND OBJECT_NAME(constid) like 'CK%'

OPEN c_stmt

FETCH NEXT FROM c_stmt INTO @strSQL

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXEC(@strSQL)

FETCH NEXT FROM c_stmt INTO @strSQL

END

CLOSE c_stmt

DEALLOCATE c_stmt

--Aborting the transaction in case of any error

IF @@Error<>0

BEGIN

ROLLBACK TRANSACTION

END
-- ==================================



PRINT '== Dropping UNIQUE constraints'

DECLARE c_stmt CURSOR FOR

SELECT 'ALTER TABLE [' + OBJECT_NAME(id) + '] DROP CONSTRAINT ' + OBJECT_NAME(constid) AS stmt

FROM sysconstraints

WHERE OBJECT_NAME(id) NOT LIKE 'dt%' AND OBJECT_NAME(constid) like 'UK%'

OPEN c_stmt

FETCH NEXT FROM c_stmt INTO @strSQL

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXEC(@strSQL)

FETCH NEXT FROM c_stmt INTO @strSQL

END

CLOSE c_stmt

DEALLOCATE c_stmt

--Aborting the transaction in case of any error

IF @@Error<>0

BEGIN

ROLLBACK TRANSACTION

END
-- ==================================

COMMIT

PRINT 'Finished dropping constraints.'


====================================

When we run .bat file from the command prompt, it is working properly. But with this stored procedure, it does not do anything.

Let me know, if I am missing anything.

Thanks
Varsha

 

[mrdenny]

We need the SQL Code that is running the xp_cmdshell not the code the xp_cmdshell is running.

Why not just put this code into a job, and have the job save the output to a text file.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[varshan09]

EXEC master..xp_cmdshell 'C:\MSSQL7\Exec\SQL_Install_Script_Migration.bat'


This will be one time activity during the installation, so we didn't go ahead with the idea of putting the entire thing in a SQL job.

Thanks
Varsha

 

[mrdenny]

That should be working fine. When this code is called does the user account have sysadmin rights to the SQL Server?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)