Edit 1600+ stored procedures at once

I'm looking for a way to track usage of SPs. I haven't found any TSQL tool that is already tracking that information. Long ago another DBA added an exec to the end of some SPs to log the usage to a table.

If there is a function already tracking this I'd like to know.

If not, I want to tack an exec line to the end of all the SPs but I don't want to do it by adding it 1600 times manually. Is there any trick in the scripting process I can do to make this easier?

JBSelect
Replace(sc.Text +
Case
When sc.colid = s0.maxColid Then N'Exec your_proc' + NChar(10)
Else NChar(10)
End , N'Create Proc', 'Alter Proc') + NChar(10) + N'Go'
From syscomments sc
Join sysobjects so On sc.id = so.id
Join ( Select so.id, Max(colid) As 'maxColId'
From syscomments sc
Join sysobjects so On sc.id = so.id
Where so.type = 'P'
Group By so.id
) s0 On so.id = s0.id
Where so.type = 'P'
Order By so.name, sc.colid|||This is awesome! I did have to take it half at a time due to the limitation of row size, but it appears it did just what I wanted. Thank you very much. This will help for many other projects.

John

Easy way to encrypt

Is there a way to encrypt all of my custom stored procedures by issuing a
command from the query designer? Something like sp_ Encrypt
AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
with the "WITH ENCRYPTION" option? I have too many and this will take
forever.
Thanks.Rene,
To my knowledge, you will have to perform an ALTER PROC statement for each
stored procedure to add the WITH ENCRYPTION clause.
Prior to doing this understand that SQL Server does not provide a way to
unencrypt encrypted objects such as stored procedures. All DDL should be
retained in a secure location. Also, encrypted objects cannot be scripted
out which can cause issues with other functionalites like replication.
HTH
Jerry
"Rene" <nospam@.nospam.com> wrote in message
news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is there a way to encrypt all of my custom stored procedures by issuing a
> command from the query designer? Something like sp_ Encrypt
> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
> with the "WITH ENCRYPTION" option? I have too many and this will take
> forever.
> Thanks.
>
>|||What a pain in the butt!! To make things worst, it looks like the SQL Server
encryption can be easily decrypted!! What a bummer.
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OJ359GxuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To my knowledge, you will have to perform an ALTER PROC statement for each
> stored procedure to add the WITH ENCRYPTION clause.
> Prior to doing this understand that SQL Server does not provide a way to
> unencrypt encrypted objects such as stored procedures. All DDL should be
> retained in a secure location. Also, encrypted objects cannot be scripted
> out which can cause issues with other functionalites like replication.
> HTH
> Jerry
> "Rene" <nospam@.nospam.com> wrote in message
> news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> Is there a way to encrypt all of my custom stored procedures by issuing a
>> command from the query designer? Something like sp_ Encrypt
>> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
>> with the "WITH ENCRYPTION" option? I have too many and this will take
>> forever.
>> Thanks.
>>
>|||Hi Rene,
Yes, I understood it would not be an easy job ALTER all the stored
procedures one by one, however this is the only method available now.
Admittedly, encrypted stored procedures could be decrypted by some third
party applications and we do not have better solution on this side. I
believe we should use other method to ensure the security of SQL Server.
Here are some articles about SQL Server security for your reference.
Injection Protection
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/
html/InjectionProtection.asp
A Security Roadmap
http://www.sql-server-performance.com/sql_server_security_distilled_chap1_ex
cept.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.com/vk_sql_security.asp
Chapter 18 - Securing Your Database Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/THCMCh18.asp
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Easy way to encrypt

Is there a way to encrypt all of my custom stored procedures by issuing a
command from the query designer? Something like sp_ Encrypt
AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
with the "WITH ENCRYPTION" option? I have too many and this will take
forever.
Thanks.
Rene,
To my knowledge, you will have to perform an ALTER PROC statement for each
stored procedure to add the WITH ENCRYPTION clause.
Prior to doing this understand that SQL Server does not provide a way to
unencrypt encrypted objects such as stored procedures. All DDL should be
retained in a secure location. Also, encrypted objects cannot be scripted
out which can cause issues with other functionalites like replication.
HTH
Jerry
"Rene" <nospam@.nospam.com> wrote in message
news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is there a way to encrypt all of my custom stored procedures by issuing a
> command from the query designer? Something like sp_ Encrypt
> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
> with the "WITH ENCRYPTION" option? I have too many and this will take
> forever.
> Thanks.
>
>
|||What a pain in the butt!! To make things worst, it looks like the SQL Server
encryption can be easily decrypted!! What a bummer.
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OJ359GxuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To my knowledge, you will have to perform an ALTER PROC statement for each
> stored procedure to add the WITH ENCRYPTION clause.
> Prior to doing this understand that SQL Server does not provide a way to
> unencrypt encrypted objects such as stored procedures. All DDL should be
> retained in a secure location. Also, encrypted objects cannot be scripted
> out which can cause issues with other functionalites like replication.
> HTH
> Jerry
> "Rene" <nospam@.nospam.com> wrote in message
> news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Rene,
Yes, I understood it would not be an easy job ALTER all the stored
procedures one by one, however this is the only method available now.
Admittedly, encrypted stored procedures could be decrypted by some third
party applications and we do not have better solution on this side. I
believe we should use other method to ensure the security of SQL Server.
Here are some articles about SQL Server security for your reference.
Injection Protection
http://msdn.microsoft.com/library/de...us/dnsqlmag04/
html/InjectionProtection.asp
A Security Roadmap
http://www.sql-server-performance.co...illed_chap1_ex
cept.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.co...l_security.asp
Chapter 18 - Securing Your Database Server
http://msdn.microsoft.com/library/de...us/dnnetsec/ht
ml/THCMCh18.asp
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Easy way to encrypt

Is there a way to encrypt all of my custom stored procedures by issuing a
command from the query designer? Something like sp_ Encrypt
AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
with the "WITH ENCRYPTION" option? I have too many and this will take
forever.
Thanks.Rene,
To my knowledge, you will have to perform an ALTER PROC statement for each
stored procedure to add the WITH ENCRYPTION clause.
Prior to doing this understand that SQL Server does not provide a way to
unencrypt encrypted objects such as stored procedures. All DDL should be
retained in a secure location. Also, encrypted objects cannot be scripted
out which can cause issues with other functionalites like replication.
HTH
Jerry
"Rene" <nospam@.nospam.com> wrote in message
news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is there a way to encrypt all of my custom stored procedures by issuing a
> command from the query designer? Something like sp_ Encrypt
> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
> with the "WITH ENCRYPTION" option? I have too many and this will take
> forever.
> Thanks.
>
>|||What a pain in the butt!! To make things worst, it looks like the SQL Server
encryption can be easily decrypted!! What a bummer.
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OJ359GxuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To my knowledge, you will have to perform an ALTER PROC statement for each
> stored procedure to add the WITH ENCRYPTION clause.
> Prior to doing this understand that SQL Server does not provide a way to
> unencrypt encrypted objects such as stored procedures. All DDL should be
> retained in a secure location. Also, encrypted objects cannot be scripted
> out which can cause issues with other functionalites like replication.
> HTH
> Jerry
> "Rene" <nospam@.nospam.com> wrote in message
> news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
>|||Hi Rene,
Yes, I understood it would not be an easy job ALTER all the stored
procedures one by one, however this is the only method available now.
Admittedly, encrypted stored procedures could be decrypted by some third
party applications and we do not have better solution on this side. I
believe we should use other method to ensure the security of SQL Server.
Here are some articles about SQL Server security for your reference.
Injection Protection
http://msdn.microsoft.com/library/d...-us/dnsqlmag04/
html/InjectionProtection.asp
A Security Roadmap
http://www.sql-server-performance.c...tilled_chap1_ex
cept.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.c...ql_security.asp
Chapter 18 - Securing Your Database Server
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/THCMCh18.asp
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

DynamicSQL & the Index Tuning wizard.

Some facts first:
1. Large application running on SQL Server 2000.
2. Stored Procedures use a lot of dynamic SQL.
3. Performance is a major issue.
4. Focussing on the database:
4.1 Start up SQL Profiler.
4.2 Run the application for a period of time (normal customer usage) to
generate the workload file.
4.3 Run the ITW on this workload file.
Questions:
(a) What kind of output can I expect from the ITW since the t-sql code is
heavily using dynamic SQL?
(b) Will the ITW be able to properly analyze the workload file in this
scenario?
(c) Are there any other issues that I should be aware of when using the SQL
Profiler and ITW for dynamic SQL code analysis?
TIA
Cheers!
SQLCatZ
SQLCatz,
Yes, the ITW will work fine with dynamic SQL. You might also want to
look at the profiler trace yourself and run some queries against it. I
look for:
1) CPU intensive queries (CPU column)
2) IO intensive queries (reads, writes columns)
3) Long running queries (duration column)
If you get the top 10 culprits from each of those categories, you will
have eliminated 90% of your poor performing queries. You want to get the
biggest bangs for your buck, and not waste time on things that don't
really matter.
Use the ITW as a guide to making decisions about which indexes to apply,
in most cases you won't want to blindly implement what it suggests.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
SQLCatz wrote:
> Some facts first:
> 1. Large application running on SQL Server 2000.
> 2. Stored Procedures use a lot of dynamic SQL.
> 3. Performance is a major issue.
> 4. Focussing on the database:
> 4.1 Start up SQL Profiler.
> 4.2 Run the application for a period of time (normal customer usage) to
> generate the workload file.
> 4.3 Run the ITW on this workload file.
> Questions:
> (a) What kind of output can I expect from the ITW since the t-sql code is
> heavily using dynamic SQL?
> (b) Will the ITW be able to properly analyze the workload file in this
> scenario?
> (c) Are there any other issues that I should be aware of when using the SQL
> Profiler and ITW for dynamic SQL code analysis?
> TIA
> Cheers!
> SQLCatZ
>
sql

Dynamically specify server and database in Stored Procedure

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')?)

Thanks for any help

Hi,

that is not (yet) parameterizable. You would have to use dynamic sql here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

dynamic table names in stored procedure...

Hello all,

Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name?

Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...

Cheers,
Justinyou will have to create a dynamic query something on these lines

declare @.table nvarchar(20)
set @.table = 'Customers'
declare @.sql nvarchar(100)
set @.sql = 'select * from ' + @.table
exec sp_executesql @.sql

i used the northwind database as an example for this and this example works on the customers table...

so if your column names will not make a difference then you will need to create a dynamic query on these lines and execute it|||http://www.sommarskog.se/dynamic_sql.html|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.

Paranoia ... a man after my own heart.

Dynamic Stored Procedures uses vars only

Hi there,

I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.

And also how to Dynamicly create a sp_GetNameByID (for instance)

using vars only.

Thanks

It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.

Writing dynamic T-SQL doesn't strike me as being relevant to SSIS so I'm a little confused. Perhaps you could elaborate.

By the way, best practice stipulates that you shouldn't name your sprocs "sp_*".

-Jamie

Dynamic Stored Procedures

Hi everyone,

My question is how can i set a var with a table name to use it in a SELECT statement for example.

EX:

Declare @.table varchar
Set @.table = 'mytable'

Select * From @.table

I've got 1 stored procedure wich i want to use to get and update 2 tables. For that reason i want to know how to do this because having 2 stored procedures when the only difference are table names its not a good solution.

Thanks :)

Declare @.sql varchar
Declare @.table varchar
Set @.table = 'mytable'

set @.sql = 'Select * From '+ @.table

execute(@.sql)

visit this link for more info

http://www.sommarskog.se/dynamic_sql.html

|||

Tiago:

You can do what you ask with something like:

exec ( 'select * from ' + @.table )

A safer method is something that does not potentially incur problems from hacking is something like:

if @.table = 'A'
select * from A
else
select * from B

The other comment I have has to do with the "select *" syntax; is this what you are really planning on doing? Because this syntax leaves "land mines." What I mean is that if you include this syntax in a stored procedure and later make column changes to either table A or table B these "select *" statements are likely to surprise you.

The reason is that what "select *" means in a stored procedure has to do with what "select *" meant at COMPILE time and NOT what "select *" should mean at RUN time! If in fact what you are trying to do is to write a generic stored procedure that returns all columns for an unspecified table you need to realize that this is a potentially dangerous stored procedure in a number of different ways. Also, you should tend to explicitly list all columns instead of using "select *" syntax when dealing with a permanent table. This is not so bad with temp tables, but I am assuming here that your target tables are not temp tables.

If you are wanting a stored procedure to list all columns of permanent tables that are not isomorphic -- that is, the forms of the tables differernt -- in my opinion you are better off writing separate stored procedures.

Dave

|||

Thanks for quick answers.

I've already put my query correctly, but i've got another problem.

The "dynamic query" its used in a CURSOR and i've getting error with that because Exec(@.query)

My Code:

set @.query = 'SELECT CodigoConta,AnoOrcamento FROM ' + @.t +
' WHERE AnoOrcamento = ' + CAST(@.Ano as varchar) +
' AND CodigoConta like ''' + cast(@.Classe as varchar) +
'%'' ORDER BY CAST(CodigoConta as varchar)'

DECLARE Orcamento_Cursor CURSOR FOR
execute(@.query).

when i try to save the SP, i'm getting this error:

Incorrect syntax near the keyword 'execute'.

Can you help me with this problem ?

|||

Tiago:

Would you mind posting the rest of your process that is using this cursor? (I just marched my army right off a cliff; trying not to repeat it.)

Dave

|||

Mugambo,

I've clear the code because i don't have more time now to get arround with this, so i'm using something like this:

IF @.Classe = '6'
BEGIN
DECLARE Orcamento_Cursor CURSOR FOR
SELECT CodigoConta
FROM OrcamentoCustosPerdas
WHERE AnoOrcamento = @.Ano
AND CodigoConta like @.Classe + '%'
ORDER BY CAST(CodigoConta AS VARCHAR)
END

ELSE

BEGIN
DECLARE Orcamento_Cursor CURSOR FOR
SELECT CodigoConta
FROM OrcamentoProveitosGanhos
WHERE AnoOrcamento = @.Ano
AND CodigoConta like @.Classe + '%'
ORDER BY CAST(CodigoConta AS VARCHAR)
END

Maybe in future i change this code to a dynamic one. Btw, thanks a lot for u'r help. :)

|||

Tiago:

I think this adjustment will work; you are welcome.

Dave

|||create a temp table,...store the result of execute in that...then in cursor..use select * from #temp, ..though better options may exist...|||( HELP! I am really concerned that I have badly screwed this up. )

Dynamic SQL Stored Precedure problem

Hi,

I have a project that all its reports are based on Dynamic SQL Stored Procedures. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied."Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.

When I wrote in the data setquery"execx @.y=1"and chose "Text" instead of"Stored Procedure"I got thecorrect SP answer, but because all the reports gettheir parameters from the application (user)I can not leave the report like that.

Try this: As a data set write static query that returns all the fields of your dynamic query and uses all the parameters. Then refresh report so it will update parameter list and field names. After that you can change static query to dynamic.

I did it like this:

declare @.sql varchar(max); set @.sql = '';

set @.sql = '.....'

exec (@.sql)

Maciej

dynamic sql performance

i`m wondering how fast r stored procedures which define their insert,
select, where, from ... clauses as strings vs normal procedures?
at least i`d think dynamic sql gets compiled on every runPerformance here should not be at the top of your concern list, IMHO.
Please read:
http://www.sommarskog.se/dynamic_sql.html
"Fred" <fred@.ilovespam.com> wrote in message
news:euqdamdJGHA.3896@.TK2MSFTNGP15.phx.gbl...
> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run|||If you must use dynamic SQL, then build it at the application level and then
submit it via Execute or RowSet.Open. Building long queries or T-SQL within
a stored procedure is clunky.
"Fred" <fred@.ilovespam.com> wrote in message
news:euqdamdJGHA.3896@.TK2MSFTNGP15.phx.gbl...
> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run|||"JT" <someone@.microsoft.com> wrote in message
news:uvRhNWeJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> If you must use dynamic SQL, then build it at the application level and
> then submit it via Execute or RowSet.Open. Building long queries or T-SQL
> within a stored procedure is clunky.
>
No. All dynamic SQL is somewhat clunky. And TSQL is better for dynamic SQL
than most languages since it allows newlines in literal strings. EG
declare @.sql varchar(8000)
set @.sql = '
select * from
' + @.MyTable + '
where id = 124
and region in (' + @.RegionList + ')
'
Anyway you would just push the clunkyness out into the application. It's
generally simpler and more secure to keep the dynamic SQL close.
David|||Fred wrote:

> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run
Fast relative to what exactly? If you can do the same thing with static
SQL then you should. You use dynamic code when there is no viable
static alternative.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||For the sake of clarity, can you explain further just what
you mean by 'All dynamic SQL is somewhat clunky'.
Terms like 'clunky' can often hide very interesting points of view.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uqD3ZEfJGHA.1088@.tk2msftngp13.phx.gbl...
> "JT" <someone@.microsoft.com> wrote in message
> news:uvRhNWeJGHA.2696@.TK2MSFTNGP14.phx.gbl...
T-SQL
> No. All dynamic SQL is somewhat clunky. And TSQL is better for dynamic
SQL
> than most languages since it allows newlines in literal strings. EG
> declare @.sql varchar(8000)
> set @.sql = '
> select * from
> ' + @.MyTable + '
> where id = 124
> and region in (' + @.RegionList + ')
> '
>
> Anyway you would just push the clunkyness out into the application. It's
> generally simpler and more secure to keep the dynamic SQL close.
> David
>|||RE:
<< Terms like 'clunky' can often hide very interesting points of view.>>
And terms like that frequently hide often very UNinteresting points of view.
It's a form of name-calling. It's like referring to Microsoft as M$... or
that SQL Server is clunky... the CLR is bloat-ware. yadda yadda yadda. Often
these terms are used by luddites
(http://www.google.com/search?hl=en&...ite&btnG=Search).
"05ponyGT" <nospam@.nospam> wrote in message
news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
> For the sake of clarity, can you explain further just what
> you mean by 'All dynamic SQL is somewhat clunky'.
> Terms like 'clunky' can often hide very interesting points of view.
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uqD3ZEfJGHA.1088@.tk2msftngp13.phx.gbl...
> T-SQL
> SQL
>|||Thanks for speaking up for Mr. luddite...er Mr. Browne.
I had no idea he was so hostile.
"Smithers" <A@.B.COM> wrote in message
news:OSkddjgJGHA.2912@.tk2msftngp13.phx.gbl...
> RE:
> << Terms like 'clunky' can often hide very interesting points of view.>>
> And terms like that frequently hide often very UNinteresting points of
view.
> It's a form of name-calling. It's like referring to Microsoft as M$... or
> that SQL Server is clunky... the CLR is bloat-ware. yadda yadda yadda.
Often
> these terms are used by luddites
> (http://www.google.com/search?hl=en&...ite&btnG=Search).
>
>
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
and
dynamic
It's
>|||"05ponyGT" <nospam@.nospam> wrote in message
news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
> For the sake of clarity, can you explain further just what
> you mean by 'All dynamic SQL is somewhat clunky'.
Sure. 'Clunky' was introduced into the thread by JT complaining about
dynamic SQL in stored procedures. I took it to mean that TSQL stored
procedures that use dynamic SQL are not as simple and elegant as stored
procedures which use only static SQL. And dynamic SQL is clunky because you
have two different programs interlaced in one programming unit, where one
program is embedded as a literal string in the other program. That's
clunky.
But it's even more clunky when you embed a SQL program (query) in a 3GL
language like VB or C#. Not only are you building one program as a string
literal in another program, but the programming languages are different, and
the host language often doesn't allow literal strings to span multiple
lines. That's extra clunky.
David|||Please forgive my clunky analysis of the situation.

Dynamic SQL in Stored Procedures

A small percentage of my client's stored procedures use dynamic SQL
statements that are executed using sp_executesql. I am planning on
removing access to everything except stored procedure execution.
However, I have heard, that to be able to execute dynamic sql, a user
must have access to more than just stored procedure execution (i.e.,
SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
site that explains it in better detail? Thank you in advance, Jeremy.On SQL Server 2000, permissions would be needed on the
underlying tables. There are other options with execute as
and certificates in SQL Server 2005.
You can find a lot of details on dynamic sql in the
following article on Erland's site:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
-Sue
On 5 Jun 2006 06:51:31 -0700, "jbiros" <jbiros@.sppinc.net>
wrote:

>A small percentage of my client's stored procedures use dynamic SQL
>statements that are executed using sp_executesql. I am planning on
>removing access to everything except stored procedure execution.
>However, I have heard, that to be able to execute dynamic sql, a user
>must have access to more than just stored procedure execution (i.e.,
>SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
>site that explains it in better detail? Thank you in advance, Jeremy.|||Sue,
Thank you for the site reference. That is exactly the type of
information I was looking for.
Jeremy