April 8, 2014 at 12:33 pm
I am connecting to a new SQL Server 2008 R2 database using SSMS from my ADMIN VM workstation. I bring up a Stored Procedure and make a change.... I execute the Stored Procedure... after it finishes.... I exit out without saving to a file.... I go back in and my change was not held.
I can do the exact same process with an old SQL Server 2005 database. Is there a permission I am missing to set to be able to do this on the 2008 database?
Thank you in advance for anyone that responds!
Charlie
April 8, 2014 at 2:13 pm
what do you mean when you say, code is not saving? If you have recently renamed the file using sp_rename and trying to pull the procedure using sp_helptext, I have seen similar issues before. Create a back up procedure and properly drop and recreate the stored procedure to see all changes.
Let us know if it works for you..
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 8, 2014 at 2:17 pm
Hi Apple.... what I meant was..... I open a Stored procedure.... add a comment statement with text.... not changing any functional code.... execute the Stored Procedure.... it runs successfully.... I exit out of Query Analyzer.... get back in Query Analyzer... and the Comment line is not saved.
I can do this with another database on a SQL Server 2005 instance on a different server. I am missing something here...
April 8, 2014 at 2:44 pm
rummings (4/8/2014)
Hi Apple.... what I meant was..... I open a Stored procedure.... add a comment statement with text.... not changing any functional code.... execute the Stored Procedure.... it runs successfully.... I exit out of Query Analyzer.... get back in Query Analyzer... and the Comment line is not saved.I can do this with another database on a SQL Server 2005 instance on a different server. I am missing something here...
If you are using 2008 you don't have QueryAnalyzer anymore. I assume mean SSMS instead.
You can't execute the procedure and expect it to save changes. You have to execute an alter procedure command. Can you post a portion of the code and what steps you took?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2014 at 3:21 pm
I'm with Sean. It sounds like you're not running the ALTER PROCEDURE script but instead you're executing the procedure itself. Those are two distinct steps.
"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
April 9, 2014 at 5:20 am
Good Morning Sean and Grant,
I am not a SQL Developer.... so I am going on what my Developer is showing me. I migrated a SQL Server database from a SQL Server 2005 instance to a new server with SQL Server 2008 R2 installed. The developer logs onto the new server with the SQL Server 2008 R2 instance. He right clicks on his Stored procedure and takes the "Script Stored Procedure as" -> "Create To" -> "New Query Editor Window" options. He adds a comment statement to the Stored Procedure. He then clicks the "Execute!" button. The Stored Procedure runs to successful completion. He exits the query window with the Stored Procedure and takes "no" to the option to save to file. He then follows the same process. When the Stored Procedure appears in the Query window... the Comment statement is present in the Stored Procedure.
Now if he goes to his "privileged" Virtual Machine and brings up SSMS and connects to the new SQL Server 2008 R2 instance..... and tries the exact same process.... the new Comment statement does not hold.
My confusion here is that if he connects to the SQL Server 2005 instance (the original location)... using his VM.... the Comment remains just as it did when he was logged onto the new server.
It seems like some permission is different. I checked the users permissions and they are identical on the SQL Server 2005 instance as the SQL Server 2008 R2 instance.
Thank you both for taking time to help with this issue.
Charlie
April 9, 2014 at 5:46 am
But if the permissions were different, you'd get an error, not just disappearing comments. Are the schema's different or missing? Are you putting the comments within the procedure definition or outside it? Something here is going off, but it doesn't sound like permissions.
"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
April 9, 2014 at 6:09 am
It was a straight migration.... I installed a new installation of SQL Server 2008 R2 on a new server... added SP2. Then backed up the database from the SQL Server 2005 instance and restored it to the SQL Server 2008 R2 instance. I checked and all schemas are the same.... all permissions are the same. It just does not make sense to me that everything holds if the developer is on the server... yet if he connects via SSMS... it does not... I am sure I am missing something... but I can not see what it is.
Thanks again for your time!
Charlie
April 9, 2014 at 6:17 am
Yeah, but even if they're "on the server" they're using SSMS, right? You could check the default connection settings for each. ANSI differences could be occurring. Not sure why that would cause this (I've never seen it) but I suppose it's possible.
"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
April 9, 2014 at 10:53 am
Grant,
I found the issue thanks to your input.... the Schema was pointing to the Service Account as the owner instead of the correct owner.... made the change and all is working. Thank you VERY much. Thanks to all that replied as well.
Charlie
April 9, 2014 at 11:17 am
Awesome. Glad you figured it out.
"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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply