Deploy SQL File from VSS to SQL Server using Batch File

Question

I want to know if there is any way for me to deploy a stored procedure (sql file) that is checked-in at VSS? I have a couple of procedures that I want to deploy to SQL Server. I'm trying to create a batch file to deploy them from VSS to SQL Server.

I want to achieve this since we would like to remove direct access to SQL Server. So that everything step we do on the procedures could be monitored.

Thanks!

EDIT:

I have also read that it's possible in Powershell. If anyone can point me to a good way to do it, that would be appreciated so much! I'm new to the VSS, Batch Files, and Powershells. So I'm a little bit confused where to start. Thanks!

This is what I have so far. But it doesn't work.

@echo off
cls

set path=C:\Program Files\Microsoft Visual SourceSafe
set ssdir=\\MySampel_VSS\VSS\SampleDB
set Recursive = Yes

set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p DbName=Database Name :

set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?

if '%choice%'=='y' goto begin
goto end

:begin
if exist C:\Scripts\error.txt del C:\Scripts\error.txt

@echo on

sqlcmd -S %SName% -U %UName% -P %Pwd% -d %DbName% -I -i $/Database/SampleDB/Procedures/MySample.sql >> error.txt 2>&1

@notepad error.txt

:end

Answer 1

You need to use Visual Source Safe command line in a batch file to deploy a project that is checked in. Take a look at the Checkout and Deploy commands in particular.