Executing a stored proc inside a script file using the sqlcmd -i

Need some help with script files. I have an SQL script file in the following format:

Begin tran

insert..

select..

updat开发者_如何学Pythone..

Commit
exec linked_server.db1.dbo.storedproc1

I am calling the above script file from within a .js file in the following manner:

var sCommand = "sqlcmd -i C:\\scriptfile1"

var WshShell = new ActiveXObject("WScript.Shell");

var oExec = WshShell.Exec(sCommand);

When I run the .js file, the code between tran-commit gets executed but the storeproc1 is never called. I know for sure that the storedproc1 is not called because it has a list of insert statements that never shows up in the table.

---

Have you tried running the exec storedproc1 alone? Maybe it throws an error.

Also you can try adding go like this:

commit
go
exec storedproc1

You can try this in the management studio first. After you are sure it works in the management studio, you can go on running it through sqlcmd.

Edit: next you can check the permission of the user running the script, whether it is allowed to run stored procedure.