I am trying to develop a script(s) that can retrieve a list of .sql files
names to be executed and then call sqlcmd for each file name. The filename
retrieval part works fine but I am at a loss for how to loop and call sqlcmd
for the current file. All the examples I see are for a known set of
filenames to be executed. Currenttly I executing a SQLCMD file that does the
foloowing

DECLARE c_Scripts cursor FAST_FORWARD for SELECT ScriptName from @TestTable
OPEN c_Scripts
FETCH NEXT FROM c_Scripts INTO @ScriptName
WHILE @@FETCH_STATUS = 0
Begin

Select @ScriptName = quotename(@ScriptName,'"')
Print @ScriptName
:setvar SetVariablesScript @ScriptName
:r $(SetVariablesScript)

FETCH NEXT From c_Scripts INTO @ScriptName
End
CLOSE c_Scripts
DEALLOCATE c_Scripts

Doesn't work! A While loop doesn't work either.

I could do this in a windows for loop but if one sql file fails I would like
NOT to execute any others.

for %%X in (*.SQL) do SQLCMD -S ServerName -d DatabasesName -U UserName -P
UserPassword -I -i "%%X" >> ResultScript.txt


There must be someway to iterate through a list and execute the .sql file.
While Perl is an option to me I would rather keep this out of Perl. Is there
a way in TSQL/sqlcmd to do this?


Any help in that direction would be greatly appreciated!
RJ

RJ (RJ (AT) discussions (DOT) microsoft.com) writes:
That's right, it doesn't work, and it can't work. SQLCMD is a client,
and cannot be controlled from SQL Server. The only way do could do
the loop with T-SQL is to kick off the SQLCMD script through xp_cmdshell.

I'm not too good at BAT files (for reasons that soon will prevail), but
does it not give you the option to check %ERRORLEVEL% and exit the loop?

Hrpmf! Perl is a great tool for this sort of stuff. I would definitely
use Perl. And Perl is definitely a better choice than runing a
T-SQL scripts that calls SQLCMD through xp_cmdshell.

If you want to stick with things that comes in the box, Powershell
could be an option. It may not be in your box, but it ships with SQL 2008,
and I guess with Windows 2008 as well. And as far as I down, it's
available for download.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx