Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Scheduled SQL statement not running

Posted on 2010-11-25
3
Medium Priority
?
275 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

885 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