Where did I screw this up? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where did I screw this up?

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-12 : 04:47:32

the below code gives this error:

Msg 207, Level 16, State 1, Line 60
Invalid column name 'media_set_id'.

I can leave everything else the same and comment out "AND a.[media_set_id] = b.[media_set_id]" under "left join" and it works fine...

CREATE TABLE #temptable
(
rowID INT IDENTITY(1, 1) PRIMARY KEY,
[Server] nvarchar(128),
[Database] nvarchar(128),
[media_set_id] int
--, [sequence_number] int
)

INSERT into #temptable
(
[Server],
[Database],
[media_set_id]
--, [sequence_number]
)
SELECT [Server],
[Database],
[media_set_id]
--, [sequence_number]
FROM dbo.BackupHistory
WHERE DATEDIFF(DAY, backup_finish, GETDATE()) = 1
ORDER BY [size] DESC

DECLARE @newserver nvarchar(64),
@newdbname nvarchar(64),
@filesequence int,
@mediasetid int

SELECT top 1 @newserver = a.[Server], @newdbname = a.[Database], @mediasetid = a.[media_set_id]--, @filesequence = a.[sequence_number]
from #temptable a
LEFT JOIN Restores b
ON a.[Server] = b.[Server]
AND a.[Database] = b.[Database]
AND a.[media_set_id] = b.[media_set_id]
--AND a.[sequence_number] = b.[sequence_number]
WHERE b.[Server] is NULL and b.[Database] is NULL --and b.[media_set_id] is NULL and b.[sequence_number] is NULL

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-12 : 05:11:21

you probably don't have a column called "media_set_id" in Restores table.

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-12 : 05:28:49

DAMN IT!!! I knew it had to be something simple!

Thanks

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources