Strange ALIAS problems..

[JJayUK]

Has anyone else seen this?

MSSQL 7 with sp3

We tried to pull off a stored procedure from one database and reload it on another (the stored procedure uses aliases in some update/select statements).

The procedure compiles fine on the normal database but it will not compile on any other database without this error.

Server: Msg 1032, Level 15, State 1, Line 4
Cannot use the column prefix 'CH'. This must match the object in the UPDATE clause 'Table1'.

I've hunted through the different database options and they're all exactly the same but why won't it allow me to use aliases in one database but will in another?

Regards,
JJayUK

 

[AshleyRickards]

Hmm .. there are no option settings to use aliases. Make sure the table names are the same in your other db's. Ashley L Rickards
SQL DBA

 

[JJayUK]

They are the same....All the database objects have been transferred to this other database using DTS. The only difference is most of the tables have not been populated but I don't see why that should give the error! The databases have different owners, though, could it be something to do with that? Regards,
JJayUK

 

[AshleyRickards]

I think it is an owner problem. If the object is not owned by dbo. Then you'll need to reference it exactly as ownername.object. There was another issue with this I just replied to.

HTH Ashley L Rickards
SQL DBA

 

[tlbroadbent]

See thread183-225113. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.