April 23, 2004 at 12:17 pm
Here's the problem. I wrote a stored procedure that will backup all of my databases on my SQL Server. When I execute the stored procedure from Query Analyzer, it works fine. When I run it from a SQL Server Agent job, it fails. I have tried running the job as 'sa' and as the service account.
The error I get is the following:
Executed as user: SWN\sql_houpas. Invalid column name 'DATABASE_NAME'. [SQLSTATE 42S22] (Error 207) Invalid column name 'size'. [SQLSTATE 42S22] (Error 207) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed.
I am pasting my code below. If someone can see where the problem is, I would appreciate your help.
*** CODE ***
DECLARE @backup_device varchar(500),
@database_name varchar(128),
@dbname varchar(128),
@description varchar(255),
@expire_date datetime
-- Gather all databases on the server
CREATE TABLE #databases(
dbname varchar(128),
dbsize int,
remarks varchar(254))
INSERT INTO #databases
EXEC dbo.sp_databases
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT dbname
FROM #databases
WHERE dbname <> 'tempdb'
ORDER BY dbname
-- Backup each database
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @database_name = @dbname
SET @backup_device = 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\'
SET @backup_device = @backup_device + @database_name + '.BAK'
SET @description = 'Backup Date: ' + CONVERT(varchar(30), getdate(), 121)
SET @expire_date = DATEADD(hh, 23, getdate())
BACKUP DATABASE @database_name
TO DISK = @backup_device
WITH DESCRIPTION = @description,
EXPIREDATE = @expire_date,
INIT
FETCH NEXT FROM db_cursor INTO @dbname
END
DROP TABLE #databases
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
April 23, 2004 at 12:42 pm
How are you calling the dts from the job? Could you paste the execution line?
April 23, 2004 at 12:50 pm
The job step is a Transact-SQL Script type and the command is:
EXEC dbo.swn_sp_regular_backups
(This SP is made of the code you see above.)
I also noticed something else after I posted this. I went back to run the SP from QA again then I got this error. When I went back to run the sp_databases SP from query analyzer, I got this error also.
After I disconnected then re-ran, the sp_databases SP runs fine and the SP runs fine from QA but the job still doesn't run.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
April 23, 2004 at 1:05 pm
The problem seems to be with running the sp_databases SP in the stored procedure. I changed my SP to populate the temp table with just some of the databases and now it works fine.
BTW, I usually use a Database Maintenance Plan but I have some reasons for building the backups this way.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
April 23, 2004 at 1:15 pm
Try this...same script, but I have changed the name of #databases to #thedatabases and it seems to work. I wonder if it has something to do with the sp_databases proc using a temp table called #databases as well...so that the creation of the temp table has already happened and you are attempting to create a table of the same name in the other sp and it's already there...then it's trying to insert data into columns that don't exist as they are named as in your proc....
DECLARE @backup_device varchar(500),
@database_name varchar(128),
@dbname varchar(128),
@description varchar(255),
@expire_date datetime
-- Gather all databases on the server
CREATE TABLE #thedatabases(
dbname varchar(128),
dbsize int,
remarks varchar(254))
INSERT INTO #thedatabases
EXEC dbo.sp_databases
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT dbname
FROM #thedatabases
WHERE dbname <> 'tempdb'
ORDER BY dbname
-- Backup each database
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @database_name = @dbname
SET @backup_device = 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\'
SET @backup_device = @backup_device + @database_name + '.BAK'
SET @description = 'Backup Date: ' + CONVERT(varchar(30), getdate(), 121)
SET @expire_date = DATEADD(hh, 23, getdate())
BACKUP DATABASE @database_name
TO DISK = @backup_device
WITH DESCRIPTION = @description,
EXPIREDATE = @expire_date,
INIT
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
DROP TABLE #thedatabases
April 23, 2004 at 1:53 pm
Thanks, that seemed to do it. I just never thought about that possibility.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
April 23, 2004 at 2:01 pm
I didn't either, I just happened to notice that the temp table names matched, more luck than judgement believe me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply