Error generatin alter procs/functins script

• 
  Complementing
  
  Are my procs/functions source code stored in memory? What happens if:
  I open my QA -> SPv3 reads my database schema.
  Anoter user change one proc souce
  I use alter proc -> SPv3 get the source from memory, or it go back in database to get the fresh source?
  
  Thanks
  
  Ésio Nunes

  EsioN December 14, 2006 08:44

  - 0 +
• 
  Hi Ésio,
  

  A tested SPv3 with some procs and functions, but, in a specifc database, when I try to alter some functions (ex. dbo.fuQ_Banco), when I start typing the clause, SPv3 completes the function name, but concatenet the function name again, and of couse, can generate the script:
  
  alter function dbo.fuQ_BancofuQ_Banco] cannot be scripted. The object may be encrypted.
  
  It's happing sometime wih procedures too.

  I've added an issue to our list. Part of the problem is that Prompt can't read the SQL definition of your function. This happens with system objects in SQL Server 2005, due to encryption. It can also happen when you don't have sufficient permissions.
  

  Are my procs/functions source code stored in memory? What happens if:
  I open my QA -> SPv3 reads my database schema.
  Anoter user change one proc souce
  I use alter proc -> SPv3 get the source from memory, or it go back in database to get the fresh source?

  
  The first time Prompt sees a database it reads the schema and stores it in a file. All subsequent times it reads the schema from that file to speed things up. You can refresh this file manually by using the Cache Management dialog (via the SQL Prompt menu). For example, if you add a new table to your database, you would want to update this file.
  
  In the final version we will provide some mechanism to automatically refresh the schema cache, aswell as a keyboard shortcut to refresh the current database.
  
  Regards,
  
  Tilman

  Tilman December 18, 2006 10:08

  - 0 +
• 

  Tilman wrote:

  Hi Ésio,
  

  A tested SPv3 with some procs and functions, but, in a specifc database, when I try to alter some functions (ex. dbo.fuQ_Banco), when I start typing the clause, SPv3 completes the function name, but concatenet the function name again, and of couse, can generate the script:
  
  alter function dbo.fuQ_BancofuQ_Banco] cannot be scripted. The object may be encrypted.
  
  It's happing sometime wih procedures too.

  I've added an issue to our list. Part of the problem is that Prompt can't read the SQL definition of your function. This happens with system objects in SQL Server 2005, due to encryption. It can also happen when you don't have sufficient permissions.
  
  

  Esio wrote:

  Tilman, I'm using SQL 2000 and not SQL 2005 (just to precise your response).
  My user objects aren't encrypted. And my problem are occurring with user objects, not system objects.
  And, I have all previleges in databases. So I think this problem is really a bug.

  Are my procs/functions source code stored in memory? What happens if:
  I open my QA -> SPv3 reads my database schema.
  Anoter user change one proc souce
  I use alter proc -> SPv3 get the source from memory, or it go back in database to get the fresh source?

  
  The first time Prompt sees a database it reads the schema and stores it in a file. All subsequent times it reads the schema from that file to speed things up. You can refresh this file manually by using the Cache Management dialog (via the SQL Prompt menu). For example, if you add a new table to your database, you would want to update this file.
  
  In the final version we will provide some mechanism to automatically refresh the schema cache, aswell as a keyboard shortcut to refresh the current database.

  Esio wrote:

  Perfect!! I was worried about too much code in memroy

  Regards,
  
  Tilman

  EsioN December 19, 2006 06:46

  - 0 +
• 
  Hi again,
  

  Tilman, I'm using SQL 2000 and not SQL 2005 (just to precise your response).
  My user objects aren't encrypted. And my problem are occurring with user objects, not system objects.
  And, I have all previleges in databases. So I think this problem is really a bug.

  That's a bit worrying. Can you tell me if you see schema information for your objects in the schema panel? In particular, for the functions and stored procedures that have the problem?
  
  Regards,
  
  Tilman

  Tilman December 20, 2006 04:48

  - 0 +
• 
  Hi Tilman.
  
  I'm sorry, I didn't undestand "schema panel". Is this QA Object Browser?
  If it is; yes I can my schema information there.
  
  Regards,
  Ésio

  EsioN December 20, 2006 12:31

  - 0 +
• 
  More details.
  
  Today morning I started QA, and all worked fine. I one of my initial windows I type some alter procs statements.
  During the day I opened other windows (not too much, only 3 or for, and always closing unused windows, please see this topic http://www.red-gate.com/messageboard/viewtopic.php?t=3884&highlight=).
  
  In some moment, in one of my older windows (that I used typing different statments and deleting these, to reuse the connection), I type again alter proc and ... SPv3 brings me the message "...cannot be scripted. The object may be encrypted.". But I used this window to edit these procs earlier!!!! And now it isn't working.
  
  No problem, I opened a new window, for brief moments I watched the message about SPv3 get the snapshots (from the memory I think) because I'm using the same database I used in oldier window.
  I typed the alter proc, and this time, it's worked, SPv3 brings me the proc code, BUT, it brings me the original code, not the code I changed in the morning. I confirmed with one sp_help and the code is changed in database. There is no other person editing my procs. So, SPv3 bring the code from another place other than database? Is from memory or cache? In the topic I mentioned, it was explained the procs text aren't in cache.
  
  Any ideia?
  
  Thanks
  
  Ésio Nunes

  EsioN January 03, 2007 13:49

  - 0 +
• 
  Hi Ésio,
  

  Today morning I started QA, and all worked fine. I one of my initial windows I type some alter procs statements.
  During the day I opened other windows (not too much, only 3 or for, and always closing unused windows, please see this topic http://www.red-gate.com/messageboard/vi ... highlight=).
  
  In some moment, in one of my older windows (that I used typing different statments and deleting these, to reuse the connection), I type again alter proc and ... SPv3 brings me the message "...cannot be scripted. The object may be encrypted.". But I used this window to edit these procs earlier!!!! And now it isn't working.

  
  I've added an issue to our list about this.

  No problem, I opened a new window, for brief moments I watched the message about SPv3 get the snapshots (from the memory I think) because I'm using the same database I used in oldier window.
  I typed the alter proc, and this time, it's worked, SPv3 brings me the proc code, BUT, it brings me the original code, not the code I changed in the morning. I confirmed with one sp_help and the code is changed in database. There is no other person editing my procs. So, SPv3 bring the code from another place other than database? Is from memory or cache? In the topic I mentioned, it was explained the procs text aren't in cache.

  Prompt keeps it's own copy of your database schema. When you change your database schema, Prompt knows nothing about it and happily keeps on using the old (out of date) schema. To make Prompt use the correct schema you need to go to SQL Prompt > Cache Management and refresh your database.
  
  Regards,
  
  Tilman

  Tilman January 08, 2007 07:35

  - 0 +

Add comment

Please sign in to leave a comment.