Odd issues - alter procedure not changing procedure

Question

Odd issues - alter procedure not changing procedure

Jeff Shurak

SSCrazy

Points: 2361
More actions
July 9, 2014 at 9:20 am

#309052

I'm trying to change references to a fully qualified object to a semi qualified in a stored procedure. I execute the alter procedure statement, it runs fine. When I open the source code for the stored procedure, the fully qualified object reference is still there.
SELECT columns
FROM [DB1].[dbo].[table1] t1
JOIN [DB1].[dbo].[table2] t2
ON t1.id = t2.id

to
SELECT columns
FROM [dbo].[table1] t1
JOIN [dbo].[table2] t2
ON t1.id = t2.id

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Only reason I can think of for this would be you're either pointing at the wrong db or the wrong schema. I've never seen an alter successfully run but not update the proc.

And then again, I might be wrong ...
David Webb

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 2

Totally agree. I'm right-clicking 'Modify' - > making changes in query window -> execute -> Modify again to verify. This is strange.

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 3

I just tried it in a different database and it worked fine...

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

It's unlikely to be the reason in this case, but a server-level DDL trigger could probably make this happen.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Maybe caching at the client, try a refresh... grasping at straws here. I've only ever seen it work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

Just for fun, you could also try DROP/CREATE.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 7

I tried drop/create for laughs, still the same. I have a few databases on a local instance that I use for dev purposes, different branches if you will and have been keeping them in sync using openDBDiff. I wonder if applying changes via this software had anything to do with it. I'm looking through their open issues now.