Solved

Scheduled SQL statement not running

Posted on 2010-11-25
3
270 Views
Last Modified: 2012-05-10
Hi Experts,
The below statements run well when starting them manually. When I try to schedule the statements the SQL part does not run.

We use Windows Server 2008 / MS SQL 2008.

Thanks for the help in advance.

Regards,
MB
@echo off
:delfiles
del /q c:\temp\*.*

:runftp
ftp -s:"c:\scripts\HRC_Shipnet.ftp"
rem if errorlevel 0 goto runsql
rem echo Error while downloading HRC Shipnet files
rem net send admin3 "Error while downloading HRC Shipnet files"
rem goto end

:runsql
sqlcmd -i c:\scripts\HRC_Shipnet.sql -b -o C:\scripts\logs\HRC_Shipnet.log
if errorlevel 0 goto end
echo Error while importing data to db
net send admin3 "Error occured while importing HRC Shipnet db"
goto next1

:end

USE ShipNet
GO
TRUNCATE TABLE REP_MAIN
GO
TRUNCATE TABLE REP_PORTS
GO
TRUNCATE TABLE REP_PORTS_BUNKERS
GO
TRUNCATE TABLE REP_PORTS_BUNKERS_SUPP
GO
TRUNCATE TABLE T016_PORT
GO
TRUNCATE TABLE T065_SHIP
GO
TRUNCATE TABLE T580_POSITION
GO
TRUNCATE TABLE T581_POSACTIVITIES
GO

BULK
INSERT REP_MAIN
FROM 'c:\temp\REP_MAIN.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT REP_PORTS
FROM 'c:\temp\REP_PORTS.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT REP_PORTS_BUNKERS
FROM 'c:\temp\REP_PORTS_BUNKERS.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT REP_PORTS_BUNKERS_SUPP
FROM 'c:\temp\REP_PORTS_BUNKERS_SUPP.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT T016_PORT
FROM 'c:\temp\T016_PORT.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT T065_SHIP
FROM 'c:\temp\T065_SHIP.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT T580_POSITION
FROM 'c:\temp\T580_POSITION.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

BULK
INSERT T581_POSACTIVITIES
FROM 'c:\temp\T581_POSACTIVITIES.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

Open in new window

0
Comment
Question by:mark_norge
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34211243
you need to log the error, if any, when it runs scheduled, otherwise it will just be guesswork....
0
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34211605
You need to create an Credential for a windows login that have access in SQL Server. Then create a proxy on that credential. Then use that proxy in Agent job in Run As account.
0
 

Author Closing Comment

by:mark_norge
ID: 34212035
Changing the error log level showed a mistake I was able to fix. Thanks and apologies.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Set Field Values ito Zero Based on Related Table 4 44
SQL Error - Query 6 50
SQL DATEADD 10 81
Re-appearing SQL Server Agent jobs 7 43
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question