| Author |
SMSS - script to new window uses sp_executesql?!
|
|
|
| Is there any way to get the old "script to new window as" instead of
the new style that uses:
EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE
[dbo].[usp_GetSeri...
I can't believe this is the only way to script a proc to a query
window...
| |
| Aaron Bertrand [SQL Server MVP] 2006-12-01, 7:15 pm |
| This is not what happens for me. Does this also happen when you use
right-click, Modify?
"KJ" <n_o_s_p_a__m@mail.com> wrote in message
news:1164984736.958544.17310@j72g2000cwa.googlegroups.com...
> Is there any way to get the old "script to new window as" instead of
> the new style that uses:
>
> EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE
> [dbo].[usp_GetSeri...
>
> I can't believe this is the only way to script a proc to a query
> window...
>
| |
|
| Yes, it does. Could it be because I am using the SP2 CTP?
Aaron Bertrand [SQL Server MVP] wrote:[color=darkred
]
> This is not what happens for me. Does this also happen when you use
> right-click, Modify?
>
>
>
> "KJ" <n_o_s_p_a__m@mail.com> wrote in message
> news:1164984736.958544.17310@j72g2000cwa.googlegroups.com...
| |
| Aaron Bertrand [SQL Server MVP] 2006-12-01, 7:15 pm |
| > Yes, it does. Could it be because I am using the SP2 CTP?
No, because that is also what I am using.
What happens when you run
EXEC sp_helptext 'procedure_name';
SELECT OBJECT_DEFINITION(OB
JECT_ID('procedure_n
ame'));
?
| |
|
| Hi Aaron,
Both of these statements correctly display the full text of the sp.
Aaron Bertrand [SQL Server MVP] wrote:
>
> No, because that is also what I am using.
>
> What happens when you run
> EXEC sp_helptext 'procedure_name';
> SELECT OBJECT_DEFINITION(OB
JECT_ID('procedure_n
ame'));
> ?
| |
|
| I figured out what does this: If I set the "Include IF NOT EXISTS"
clause to True in the Scripting options, then it uses the sp_executesql
style, if that settings is false, it doesn't.
Aaron Bertrand [SQL Server MVP] wrote:
>
> No, because that is also what I am using.
>
> What happens when you run
> EXEC sp_helptext 'procedure_name';
> SELECT OBJECT_DEFINITION(OB
JECT_ID('procedure_n
ame'));
> ?
| |
| Arnie Rowland 2006-12-01, 7:15 pm |
| What you are describing is not the normal behavior.
Please tell us exactly what you do, step by step, in order to this kind of a
script.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KJ" <n_o_s_p_a__m@mail.com> wrote in message
news:1164984736.958544.17310@j72g2000cwa.googlegroups.com...
> Is there any way to get the old "script to new window as" instead of
> the new style that uses:
>
> EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE
> [dbo].[usp_GetSeri...
>
> I can't believe this is the only way to script a proc to a query
> window...
>
| |
| Arnie Rowland 2006-12-01, 7:15 pm |
| What you are describing is not the normal behavior.
Please tell us exactly what you do, step by step, in order to this kind of a
script.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KJ" <n_o_s_p_a__m@mail.com> wrote in message
news:1164984736.958544.17310@j72g2000cwa.googlegroups.com...
> Is there any way to get the old "script to new window as" instead of
> the new style that uses:
>
> EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE
> [dbo].[usp_GetSeri...
>
> I can't believe this is the only way to script a proc to a query
> window...
>
| |
|
| Set the IF NOT EXISTS scripting option to true, then right-click any sp
and choose:
Script Stored Procedure as->Create to->New query editor window.
Arnie Rowland wrote:[color=darkred
]
> What you are describing is not the normal behavior.
>
> Please tell us exactly what you do, step by step, in order to this kind of a
> script.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
>
> "KJ" <n_o_s_p_a__m@mail.com> wrote in message
> news:1164984736.958544.17310@j72g2000cwa.googlegroups.com...
| |
| Arnie Rowland 2006-12-01, 7:15 pm |
| Thanks, I have not used the Wizard to script individual procedures, so I had
not seen that behavior.
If you just right click on the procedure, and select script to new window as
CREATE, it does not happen there as you describe -only on the wizard.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KJ" <n_o_s_p_a__m@mail.com> wrote in message
news:1164993819.213218.103340@79g2000cws.googlegroups.com...
> Set the IF NOT EXISTS scripting option to true, then right-click any sp
> and choose:
>
> Script Stored Procedure as->Create to->New query editor window.
>
>
> Arnie Rowland wrote:
>
| |
|
| I am not using the Wizard, only right-clicking the proc name under
Programmability->Stored Procedures
Arnie Rowland wrote:[color=darkred
]
> Thanks, I have not used the Wizard to script individual procedures, so I had
> not seen that behavior.
>
> If you just right click on the procedure, and select script to new window as
> CREATE, it does not happen there as you describe -only on the wizard.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
>
> "KJ" <n_o_s_p_a__m@mail.com> wrote in message
> news:1164993819.213218.103340@79g2000cws.googlegroups.com...
| |
| Arnie Rowland 2006-12-01, 7:15 pm |
| That's odd, I can only replicate the behavior by using the 'Generate
Scripts' wizard.
Even if the setting it [True] in the Generate Scripts wizard, right-clicking
on the object and scripting to a new windows as CREATE does NOT replicate.
Hummm...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KJ" <n_o_s_p_a__m@mail.com> wrote in message
news:1164995357.600708.144770@j72g2000cwa.googlegroups.com...
>I am not using the Wizard, only right-clicking the proc name under
> Programmability->Stored Procedures
>
> Arnie Rowland wrote:
>
| |
| Paul A. Mestemaker II [MSFT] 2006-12-02, 12:14 am |
| Arnie,
There is a new set of options for generating scripts in SQL Server 2005 SP2.
Tools | Options | Scripting. I've reproduced this behavior and my first
guess is that it is not something that we will be able to fix before
releasing SP2.
Could somebody please file this on Microsoft Connect and vote on it?
http://connect.microsoft.com/SQLServer/
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"Arnie Rowland" <arnie@1568.com> wrote in message
news:%232nl%23VXFHHA
.3616@TK2MSFTNGP06.phx.gbl...
> That's odd, I can only replicate the behavior by using the 'Generate
> Scripts' wizard.
>
> Even if the setting it [True] in the Generate Scripts wizard,
> right-clicking on the object and scripting to a new windows as CREATE does
> NOT replicate.
>
> Hummm...
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
>
> "KJ" <n_o_s_p_a__m@mail.com> wrote in message
> news:1164995357.600708.144770@j72g2000cwa.googlegroups.com...
>
>
| |
|
| I went to the Connect site, but it says "You need permission to submit
feedback in this area. To apply for permission, browse through your
choices on the Available Connections page." I can't find how to do
that.
Paul A. Mestemaker II [MSFT] wrote:[color=darkred
]
> Arnie,
>
> There is a new set of options for generating scripts in SQL Server 2005 SP2.
> Tools | Options | Scripting. I've reproduced this behavior and my first
> guess is that it is not something that we will be able to fix before
> releasing SP2.
>
> Could somebody please file this on Microsoft Connect and vote on it?
> http://connect.microsoft.com/SQLServer/
>
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
>
>
> "Arnie Rowland" <arnie@1568.com> wrote in message
> news:%232nl%23VXFHHA
.3616@TK2MSFTNGP06.phx.gbl...
| |
|
| I found the link and I applied. Hopefully, I will be accepted in time
to submit this, unless someone else wants to...
KJ wrote:[color=darkred
]
> I went to the Connect site, but it says "You need permission to submit
> feedback in this area. To apply for permission, browse through your
> choices on the Available Connections page." I can't find how to do
> that.
>
> Paul A. Mestemaker II [MSFT] wrote:
| |
| Aaron Bertrand [SQL Server MVP] 2006-12-02, 7:17 pm |
| > There is a new set of options for generating scripts in SQL Server 2005
> SP2. Tools | Options | Scripting. I've reproduced this behavior and my
> first guess is that it is not something that we will be able to fix before
> releasing SP2.
There is a bigger problem with this "feature" than just having dynamic SQL
that makes the output harder to read and modify. If I set this option to
TRUE and then try to modify a stored procedure or script as alter, the end
result is (pseudo):
IF NOT EXISTS (object)
EXEC('ALTER PROCEDURE...')
How will I ever be able to alter this object, without changing the logic
that SSMS so graciously provided for me? This will always end in a no-op!
So, I filed two issues.
https://connect.microsoft.com/SQLSe...edbackID=242795
https://connect.microsoft.com/SQLSe...edbackID=242799
| |
|
| Thank you for reporting these.
Aaron Bertrand [SQL Server MVP] wrote:
>
> There is a bigger problem with this "feature" than just having dynamic SQL
> that makes the output harder to read and modify. If I set this option to
> TRUE and then try to modify a stored procedure or script as alter, the end
> result is (pseudo):
>
> IF NOT EXISTS (object)
> EXEC('ALTER PROCEDURE...')
>
> How will I ever be able to alter this object, without changing the logic
> that SSMS so graciously provided for me? This will always end in a no-op!
> So, I filed two issues.
>
> https://connect.microsoft.com/SQLSe...edbackID=242795
>
> https://connect.microsoft.com/SQLSe...edbackID=242799
| |
| Erland Sommarskog 2006-12-03, 7:21 pm |
| Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
> There is a bigger problem with this "feature" than just having dynamic SQL
> that makes the output harder to read and modify.
Originally, you could only get that when you use the Generate Scripts
wizard. Then you probably only generated the script to run it somewhere.
In that case the dynamic SQL would not be an issue.
But when you script a single object is probably because you want to
look at the code and maybe also modify it. In that case the dyanmic SQL
is certainly not very practical.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
|
|
|
|