May 16, 2008 at 12:08 am
I need to run a set of scripts on a database. How do i create a batch file to execute them.
May 16, 2008 at 12:51 am
isql.exe (SS2000) or sqlcmd.exe (SS2005) can be used to do this;
type them in with /? as a parameter which will show you all the options;
May 16, 2008 at 4:03 am
1) First Open one new notepad
then
write the sql commands that you want to excute
like this for example
USE MASTER
go
SELECT * FROM SYSDATABASES
go
now save the file with .sql suppose say (example.sql)
2) now open another notepad in the same folder
write the following code
osql -S servername -U username -P password <example.sql
pause
save this as .bat extension (this will create the batch file) file
3) double click on the batch file it will excute the commands
thats all
May 27, 2008 at 5:42 am
How do I raise an exception/error if one of the files doesn't execute properly.
Rosh
August 23, 2012 at 12:07 am
Thank u it is nice work..
August 24, 2012 at 7:11 am
Here is an example of a SQLCMD call from a batch file. It uses a separate .sql file (after the -i), and sends all output to a log file (the file after -o).
sqlcmd -S MyServerName -E -i C:\MyScriptName.sql -o C:\SQLLogs\MyLogFile.txt
The -E means to use Windows authentication for the user that is running the batch file. The -i is the name of the .sql file to use.
Using Windows Authentication means that you don't have to put the username and password in the batch file. You give it the username and password when you set up the scheduled task to run the batch file. This handles it.
August 24, 2012 at 7:18 am
Also, here is some code that I use to do error trapping in scripts. It allows the printing of the message, the RAISEERROR, and the sending of the information to a AppErrorLog table. Use any or all methods.
DECLARE
@ErrorMessageNVARCHAR(4000),
@ErrorNumberINT,
@ErrorSeverityINT,
@ErrorStateINT,
@ErrorLineINT,
@ErrorProcedureNVARCHAR(200)
IF OBJECT_ID(N'ApplicationErrors.dbo.AppErrorLog', N'U') IS NULL
BEGIN
CREATE TABLE ApplicationErrors.dbo.AppErrorLog
(EID int IDENTITY NOT NULL
CONSTRAINT PK_ApplicationErrors_On_EID PRIMARY KEY CLUSTERED,
EDatedatetimeNOT NULL,
EUservarchar(50)NULL,
EMessagevarchar(1000)NULL,
ESource varchar(150)NOT NULL,
ENointNOT NULLDEFAULT 0,
ESeverityintNOT NULLDEFAULT 0,
ELineNo intNOT NULLDEFAULT 0,
EHost varchar(50)NULL
)
END
BEGIN TRY
--do something here
END TRY
BEGIN CATCH
SELECT
@ErrorMessage= ERROR_MESSAGE(),
@ErrorNumber= ERROR_NUMBER(),
@ErrorSeverity= ERROR_SEVERITY(),
@ErrorState= ERROR_STATE(),
@ErrorLine= ERROR_LINE(),
@ErrorProcedure= ISNULL(ERROR_PROCEDURE(), '-');
--RAISERROR
--(
--@ErrorMessage,
--@ErrorSeverity,
--1,
--@ErrorNumber, -- parameter: original error number.
--@ErrorSeverity, -- parameter: original error severity.
--@ErrorState, -- parameter: original error state.
--@ErrorProcedure, -- parameter: original error procedure name.
--@ErrorLine -- parameter: original error line number.
--);
-- Put a record in AppErrorLog to show the error message for review later
INSERT INTO ApplicationErrors.dbo.AppErrorLog
VALUES (GETDATE(), USER, @ErrorMessage, '<description of where this is at and other values>', @ErrorNumber, @ErrorSeverity, @ErrorLine, HOST_NAME())
PRINT 'ERROR - Found error when attempting to ???. Error=' + @ErrorMessage
END CATCH
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply