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
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
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.
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.
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.
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.
Easy T-SQL, brain lapse on Friday
Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
--
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> > Is there a view or something that lets you see what recovery mode your
> > db is set to?
> >
> > I'm trying to tidy up a stored procedure that does a backup of all the
> > trans logs, but I get an error when it hits a db set to Simple.
> >
> > Thx.
> >
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
--
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> > Is there a view or something that lets you see what recovery mode your
> > db is set to?
> >
> > I'm trying to tidy up a stored procedure that does a backup of all the
> > trans logs, but I get an error when it hits a db set to Simple.
> >
> > Thx.
> >
Easy T-SQL, brain lapse on Friday
Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:[vbcol=seagreen]
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:[vbcol=seagreen]
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
Easy stored procedure
Hi,
I have two tables...
Employees Departments
| EmployeeID | DeptID
| Name | Name
| Department |
|______________ | _____________
I would like to know how could i make an insert procedure on employees, inse
rting in the employeeid the next numeric value (in the table), and take in c
ount that the name of the employee must dont exists ...
Thanks and Regards.
Any Help will be grateful, urls, articles, anything...
Josema.See my reply in .programming
Vishal Parkar
vgparkar@.yahoo.co.in
I have two tables...
Employees Departments
| EmployeeID | DeptID
| Name | Name
| Department |
|______________ | _____________
I would like to know how could i make an insert procedure on employees, inse
rting in the employeeid the next numeric value (in the table), and take in c
ount that the name of the employee must dont exists ...
Thanks and Regards.
Any Help will be grateful, urls, articles, anything...
Josema.See my reply in .programming
Vishal Parkar
vgparkar@.yahoo.co.in
Wednesday, March 21, 2012
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
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
Dynamicly load tables for DataSources/DataDestinations
Hi everybody,
The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.
I hope somebody can help me. Thanks.
Is the metadata of these tables the same? If so you could loop over the list of tables using a ForEach loop and execute the same data-flow for each one of them. Reply here, search this forum or read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx if you need help in doing that.
If the metadata is NOT the same then you'll need a seperate data-flow for each in which case there isn't much point in storing the name of the source tables somewhere.
If you are intent on dynamically setting the name of the table to extract from then this should help also: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
-Jamie
sqlDynamicly create report
Hi All,
I would like to create report like below:
For example: I have a stored procedure spDeptEmp, which has a Dept ID
as input parameter, Once the Dept ID has been passed in, I will get
all the employees on that dept. The question is: I would like to
generate the report, which will display each employee's detail
information, one person per page.
Could you let me know how can I do that using reporting services?
Thanks in advance!
--BillWhat do you mean by dynamic? I don't see the report being dynamic (i.e. that
you show different columns at different times or some such thing). It seems
to me that you are just returning different data depending on the parameter
but the format/layout etc of the report is unchanged. Everything you
describe here is very vanilla report generation for RS. You can easily add
page breaks, you can easily have a query based on a parameter.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408250950.723ae518@.posting.google.com...
> Hi All,
> I would like to create report like below:
> For example: I have a stored procedure spDeptEmp, which has a Dept ID
> as input parameter, Once the Dept ID has been passed in, I will get
> all the employees on that dept. The question is: I would like to
> generate the report, which will display each employee's detail
> information, one person per page.
> Could you let me know how can I do that using reporting services?
> Thanks in advance!
> --Bill|||"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> What do you mean by dynamic? I don't see the report being dynamic (i.e. that
> you show different columns at different times or some such thing). It seems
> to me that you are just returning different data depending on the parameter
> but the format/layout etc of the report is unchanged. Everything you
> describe here is very vanilla report generation for RS. You can easily add
> page breaks, you can easily have a query based on a parameter.
> Bruce L-C
> "bill" <bli2001@.hotmail.com> wrote in message
> news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > Hi All,
> >
> > I would like to create report like below:
> > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > as input parameter, Once the Dept ID has been passed in, I will get
> > all the employees on that dept. The question is: I would like to
> > generate the report, which will display each employee's detail
> > information, one person per page.
> >
> > Could you let me know how can I do that using reporting services?
> >
> > Thanks in advance!
> >
> > --Bill
The dynamic means you don't know how many employees inside one dept.
until you get the input parameter(dept ID). Different dept. will have
different number of employees. i.e. the report will be different.
Also, for one employee's information, it will come from different
dataset.
Thanks,
--Bill|||What you are wanting to do is exactly what RS is designed to do quite
easily. If a simple matter of here is a dept, list all employee's
information with page breaks between them. That would be a single
parameterized query with appropriate grouping and page breaks. If it is more
a master detail type report then subreports will do what you want.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408251442.232899ab@.posting.google.com...
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> > What do you mean by dynamic? I don't see the report being dynamic (i.e.
that
> > you show different columns at different times or some such thing). It
seems
> > to me that you are just returning different data depending on the
parameter
> > but the format/layout etc of the report is unchanged. Everything you
> > describe here is very vanilla report generation for RS. You can easily
add
> > page breaks, you can easily have a query based on a parameter.
> >
> > Bruce L-C
> >
> > "bill" <bli2001@.hotmail.com> wrote in message
> > news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > > Hi All,
> > >
> > > I would like to create report like below:
> > > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > > as input parameter, Once the Dept ID has been passed in, I will get
> > > all the employees on that dept. The question is: I would like to
> > > generate the report, which will display each employee's detail
> > > information, one person per page.
> > >
> > > Could you let me know how can I do that using reporting services?
> > >
> > > Thanks in advance!
> > >
> > > --Bill
> The dynamic means you don't know how many employees inside one dept.
> until you get the input parameter(dept ID). Different dept. will have
> different number of employees. i.e. the report will be different.
> Also, for one employee's information, it will come from different
> dataset.
> Thanks,
> --Bill
I would like to create report like below:
For example: I have a stored procedure spDeptEmp, which has a Dept ID
as input parameter, Once the Dept ID has been passed in, I will get
all the employees on that dept. The question is: I would like to
generate the report, which will display each employee's detail
information, one person per page.
Could you let me know how can I do that using reporting services?
Thanks in advance!
--BillWhat do you mean by dynamic? I don't see the report being dynamic (i.e. that
you show different columns at different times or some such thing). It seems
to me that you are just returning different data depending on the parameter
but the format/layout etc of the report is unchanged. Everything you
describe here is very vanilla report generation for RS. You can easily add
page breaks, you can easily have a query based on a parameter.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408250950.723ae518@.posting.google.com...
> Hi All,
> I would like to create report like below:
> For example: I have a stored procedure spDeptEmp, which has a Dept ID
> as input parameter, Once the Dept ID has been passed in, I will get
> all the employees on that dept. The question is: I would like to
> generate the report, which will display each employee's detail
> information, one person per page.
> Could you let me know how can I do that using reporting services?
> Thanks in advance!
> --Bill|||"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> What do you mean by dynamic? I don't see the report being dynamic (i.e. that
> you show different columns at different times or some such thing). It seems
> to me that you are just returning different data depending on the parameter
> but the format/layout etc of the report is unchanged. Everything you
> describe here is very vanilla report generation for RS. You can easily add
> page breaks, you can easily have a query based on a parameter.
> Bruce L-C
> "bill" <bli2001@.hotmail.com> wrote in message
> news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > Hi All,
> >
> > I would like to create report like below:
> > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > as input parameter, Once the Dept ID has been passed in, I will get
> > all the employees on that dept. The question is: I would like to
> > generate the report, which will display each employee's detail
> > information, one person per page.
> >
> > Could you let me know how can I do that using reporting services?
> >
> > Thanks in advance!
> >
> > --Bill
The dynamic means you don't know how many employees inside one dept.
until you get the input parameter(dept ID). Different dept. will have
different number of employees. i.e. the report will be different.
Also, for one employee's information, it will come from different
dataset.
Thanks,
--Bill|||What you are wanting to do is exactly what RS is designed to do quite
easily. If a simple matter of here is a dept, list all employee's
information with page breaks between them. That would be a single
parameterized query with appropriate grouping and page breaks. If it is more
a master detail type report then subreports will do what you want.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408251442.232899ab@.posting.google.com...
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> > What do you mean by dynamic? I don't see the report being dynamic (i.e.
that
> > you show different columns at different times or some such thing). It
seems
> > to me that you are just returning different data depending on the
parameter
> > but the format/layout etc of the report is unchanged. Everything you
> > describe here is very vanilla report generation for RS. You can easily
add
> > page breaks, you can easily have a query based on a parameter.
> >
> > Bruce L-C
> >
> > "bill" <bli2001@.hotmail.com> wrote in message
> > news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > > Hi All,
> > >
> > > I would like to create report like below:
> > > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > > as input parameter, Once the Dept ID has been passed in, I will get
> > > all the employees on that dept. The question is: I would like to
> > > generate the report, which will display each employee's detail
> > > information, one person per page.
> > >
> > > Could you let me know how can I do that using reporting services?
> > >
> > > Thanks in advance!
> > >
> > > --Bill
> The dynamic means you don't know how many employees inside one dept.
> until you get the input parameter(dept ID). Different dept. will have
> different number of employees. i.e. the report will be different.
> Also, for one employee's information, it will come from different
> dataset.
> Thanks,
> --Bill
Monday, March 19, 2012
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
that is not (yet) parameterizable. You would have to use dynamic sql here.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Dynamically select tables
Hi,
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
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|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:
> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
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|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:
> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet
Dynamically select column
Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:
CREATE PROCEDURE VoteStoredProc
(
@.PlayerID int,
@.VoteID int,
@.BootNumber nvarchar(50)
)
ASDECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+'='+ @.VoteID + ' WHERE (PlayerID = '+ @.PlayerID +')'EXEC(@.SQLStatement)
GO
I get the following error:
Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int
The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.
CREATE PROCEDURE VoteStoredProc
(
@.PlayerID int,
@.VoteID int,
@.BootNumber nvarchar(50)
)
ASUPDATE
myTable
SET
Boot3 = @.VoteID
WHERE
PlayerID = @.PlayerID
GO
Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!Try this:
CREATE PROCEDURE VoteStoredProc(
@.PlayerID int,
@.VoteID int,
@.BootNumber varchar(50))
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+ ' = ' + CAST(@.VoteID as VARCHAR(10)) + ' WHERE (PlayerID = '+ CAST(@.PlayerID as VARCHAR(10)) +')'
EXEC(@.SQLStatement)
GO
Casting the integers to varchars so they can be concatenated into the larger string.
Hope this helps,
John|||John:
Brilliant! Thanks; works beautifully.
JP
Sunday, March 11, 2012
Dynamically formatting data using XSD
Hi,
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
Sameer
Hello Sameer,
I think you might be getting confused as to what a XSD Schema is, what an
XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent
|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting confused as to what a XSD Schema is, what an
> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concept
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directly
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
Sameer
Hello Sameer,
I think you might be getting confused as to what a XSD Schema is, what an
XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent
|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting confused as to what a XSD Schema is, what an
> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concept
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directly
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent
Dynamically formatting data using XSD
Hi,
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
SameerHello Sameer,
I think you might be getting
as to what a XSD Schema is, what an
XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting as to what a XSD Schema is, what an
> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concep
t
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directl
y
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
SameerHello Sameer,
I think you might be getting
as to what a XSD Schema is, what anXML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting
as to what a XSD Schema is, what an> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concep
t
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directl
y
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent
Dynamically execute stored procedure
Hello,
I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:
insert into Table1
(
id, name
)
select id, name
from Table2
Can you do something like:
exec spProc @.id = id, @.name = name
from Table1
Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.
Thanks.
I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:
insert into Table1
(
id, name
)
select id, name
from Table2
Can you do something like:
exec spProc @.id = id, @.name = name
from Table1
Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.
Thanks.
You can create a SQL cursor base on Table 1. Then iterate over the cursor and build your SQL statements dynamically using that syntax of the Execute statement. You can find syntax in SQL Books Online.
HTH.
dynamically creating a select statement
I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:
ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END
Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.
There's two approaches that I can think of that might help you.
Firstly, it's entirely valid to include criteria such as
WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...
in the stored procedure.
However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:
declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)
set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''
if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end
if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end
... and so on for the other parameters ...
set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end
exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than
Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).
Let me know if you need any further assistance with this.
Iain
|||You can try this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchCurrency]
@.currencycode nchar(5) = null,
@.currencyname nchar(25) = null
as
begin
declare @.stmt nvarchar(max)
set @.stmt = 'SELECT currencycode, currencyname
FROM currencies where 1=1 '
IF @.currencycode IS NOT NULL
set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''
IF @.currencyname IS NOT NULL
set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''
exec(@.stmt)
end
|||I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.
See:
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Dynamically Created Stored Procs??
Okay. Scenario:
We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.
From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.
This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.
So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?
Or if not, is there some round-about yet effective way of doing this?
Thank you in advance for any help.
-ZanderBI guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.|||The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).
When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.
If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?
The structure of the where statement can cause the execution plan to be changed.
So my question again is why you wouldn't want a dynamically created stored proc for this.
Are dynamic queries going to have a cached execution plan if the same ones are called enough?|||When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.
Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.
n both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.|||Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.|||Originally posted by ZanderB
When you have web enabled reports that already take several minutes
Every new request becomes a log of the report in a table.
The get a list and select what they want... the table stores the proc name which gets executed
You'll come to a point when you've created almost all variations.
And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long
You need to build a process to denormalize the data so they pop...
I would imagine an in/experienced user would think the damn thing is broken...|||Originally posted by ostrande
anonymous block.
Don't look now...but your Oracle is showing...
:D|||No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.
And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.|||Yo, Blind dude...
ever wait MINUTES for a web based report?
We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.
From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.
This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.
So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?
Or if not, is there some round-about yet effective way of doing this?
Thank you in advance for any help.
-ZanderBI guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.|||The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).
When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.
If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?
The structure of the where statement can cause the execution plan to be changed.
So my question again is why you wouldn't want a dynamically created stored proc for this.
Are dynamic queries going to have a cached execution plan if the same ones are called enough?|||When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.
Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.
n both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.|||Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.|||Originally posted by ZanderB
When you have web enabled reports that already take several minutes
Every new request becomes a log of the report in a table.
The get a list and select what they want... the table stores the proc name which gets executed
You'll come to a point when you've created almost all variations.
And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long
You need to build a process to denormalize the data so they pop...
I would imagine an in/experienced user would think the damn thing is broken...|||Originally posted by ostrande
anonymous block.
Don't look now...but your Oracle is showing...
:D|||No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.
And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.|||Yo, Blind dude...
ever wait MINUTES for a web based report?
Dynamically create SQL Scripts
I would like to be able to create SQL Scripts with a stored procedure (or some other means). I have looked all through the help files and even tried watching the profiler to see how EM does it. Any help would be appreciated.I've written a set of procedures that will generate INSERT/SELECT/UPDATE statements for a given table, plus more. Is that what you are looking for?|||Thanks for your reply.
I went back and read my post and I'm sorry, I should have been more clear. I am looking for a way to dynamically generate the scripts to create the objects themselves (Stored Procedures, Tables, Logins, etc.). I would like to have a job that would fire every week and write out the individual scripts for each object into a directory named the same as the database. Basically, I want to automate the "Generate SQL Scripts" menu option in EM.|||Don't know an easy way to do this, I don't think you can do it using DTS
at least not pre-2000.
The hard way is to write an SP which generates all the DDL using
sysobjects, syscolumns, systypes, sysindexes, syslogins, sp_helptext etc.
You can use sp_help code as a preliminary guide on how to extract the DDL.|||You can use SQLDMO to generate the script. What version of SQL Server are you using, I may have a VB program that does this and a Perl program.|||Thanks for the reply plus any help given, I am using 2000.|||achorozy, you mentioned I could use DMO (something I have zero experience with) or you might have something I can use. Can you help me?
Thanks|||I've attached a VB project that was written using SQLDMO to generate SQL script for a given database.
This script was originally for 6.5 but was converted to 7.0 and 2000 by changing the SQLDMO libray from SQLOLE to SQLDMO. I believe this code original came from an example on DEVX a few years ago. This is not my code but I did modify it to work with 7.0 and 2000.|||Attachment|||Thanks!!
That worked great.
I went back and read my post and I'm sorry, I should have been more clear. I am looking for a way to dynamically generate the scripts to create the objects themselves (Stored Procedures, Tables, Logins, etc.). I would like to have a job that would fire every week and write out the individual scripts for each object into a directory named the same as the database. Basically, I want to automate the "Generate SQL Scripts" menu option in EM.|||Don't know an easy way to do this, I don't think you can do it using DTS
at least not pre-2000.
The hard way is to write an SP which generates all the DDL using
sysobjects, syscolumns, systypes, sysindexes, syslogins, sp_helptext etc.
You can use sp_help code as a preliminary guide on how to extract the DDL.|||You can use SQLDMO to generate the script. What version of SQL Server are you using, I may have a VB program that does this and a Perl program.|||Thanks for the reply plus any help given, I am using 2000.|||achorozy, you mentioned I could use DMO (something I have zero experience with) or you might have something I can use. Can you help me?
Thanks|||I've attached a VB project that was written using SQLDMO to generate SQL script for a given database.
This script was originally for 6.5 but was converted to 7.0 and 2000 by changing the SQLDMO libray from SQLOLE to SQLDMO. I believe this code original came from an example on DEVX a few years ago. This is not my code but I did modify it to work with 7.0 and 2000.|||Attachment|||Thanks!!
That worked great.
Friday, March 9, 2012
Dynamically change report datasource based upon parameter.
I currently have one report that I would like to be able to use to report
off of stored procs on multiple servers. And am hoping someone can point me
in the right direction.
Example:
Report A will run off of storedProc1 which exists in every database.
However, the specific server and database to use will depend upon the user
currently logged in.
Currently I am trying to make use of the custom dataset extension (by Teo
Lachev) to report off of an XML string. Unfortunately, I am having fits
trying to get it to work and don't even know if this is the best way.
Any help would be appreciated.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* Use a custom data processing extension (as you currently do)
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
Native support (expression-based connection strings) is available in RS
2005.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:O4%23lPKA9EHA.960@.TK2MSFTNGP11.phx.gbl...
> I currently have one report that I would like to be able to use to report
> off of stored procs on multiple servers. And am hoping someone can point
me
> in the right direction.
> Example:
> Report A will run off of storedProc1 which exists in every database.
> However, the specific server and database to use will depend upon the user
> currently logged in.
> Currently I am trying to make use of the custom dataset extension (by Teo
> Lachev) to report off of an XML string. Unfortunately, I am having fits
> trying to get it to work and don't even know if this is the best way.
> Any help would be appreciated.
>
off of stored procs on multiple servers. And am hoping someone can point me
in the right direction.
Example:
Report A will run off of storedProc1 which exists in every database.
However, the specific server and database to use will depend upon the user
currently logged in.
Currently I am trying to make use of the custom dataset extension (by Teo
Lachev) to report off of an XML string. Unfortunately, I am having fits
trying to get it to work and don't even know if this is the best way.
Any help would be appreciated.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* Use a custom data processing extension (as you currently do)
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
Native support (expression-based connection strings) is available in RS
2005.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:O4%23lPKA9EHA.960@.TK2MSFTNGP11.phx.gbl...
> I currently have one report that I would like to be able to use to report
> off of stored procs on multiple servers. And am hoping someone can point
me
> in the right direction.
> Example:
> Report A will run off of storedProc1 which exists in every database.
> However, the specific server and database to use will depend upon the user
> currently logged in.
> Currently I am trying to make use of the custom dataset extension (by Teo
> Lachev) to report off of an XML string. Unfortunately, I am having fits
> trying to get it to work and don't even know if this is the best way.
> Any help would be appreciated.
>
Subscribe to:
Posts (Atom)