Simple Computed Column not working

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

SQL Server 2000 Forums

Transact-SQL (2000)

Simple Computed Column not working

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-10-03 : 06:25:34

Hi,

Can anyone tell me why I am receiving this error?

Invalid column name 'backup_start_d'.

The following is the proc.

The error is associated with the line BS.backup_size/1000000 MB.

Alter Proc usp_FailedJobs_GetLatestBaks 

@servername varchar (100)


As

Declare @sql varchar (500)

Select @sql = 'SELECT TOP 100

    Server_Name,
	Database_Name,
	Convert (char(25), BS.backup_start_date) [Start DateTime],
	Convert (char(25), BS.backup_finish_date)[Finish DateTime],


	CONVERT(CHAR(12),BS.backup_finish_date-BS.backup_start_date,114) Duraction, 
	BS.backup_size/1000000 MB


FROM ' + @servername + '.msdb.dbo.backupset AS BS
	LEFT OUTER JOIN ' + @servername + '.msdb.dbo.backupmediafamily AS BMF
		ON BMF.media_set_id = BS.media_set_id
WHERE	1=1

	AND BS.type=''D''

ORDER BY BS.backup_start_date DESC, BS.database_name'

Exec (@sql)

Thanks
Drew

---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 06:31:57

Declare @sql varchar (5000)

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 06:35:14

print @sql and see what it returns

Madhivanan

Failing to plan is Planning to fail

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-10-03 : 06:35:21

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 06:36:41

because

Declare @sql varchar (500)

is not going to be big enough to hold the SQL string.

To debug dynamic SQL review the generated SQL, try it stand-alone, and once working make any changes to the original code.

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 06:37:29

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 06:38:12

quote:
Originally posted by Peso

Declare @sql varchar (5000)

E 12°55'05.25"
N 56°04'39.16"

Thats it. I didnt see your reply when I posted

Madhivanan

Failing to plan is Planning to fail

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-10-03 : 06:40:07

What threw me off is that BS.backup_size/1000 worked and BS.backup_size/1000000 didn't. It was off by a few characters.

Thanks.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 06:47:35

No reason to DECLARE varchar variables for things like dynamic SQL strings any less than the 8,000 maximum, is there?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 06:51:14

quote:
Originally posted by Kristen

No reason to DECLARE varchar variables for things like dynamic SQL strings any less than the 8,000 maximum, is there?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources