Passwords are not saved in a package unless in an encrypted format. Check the ProtectionLevel property of your package to set this up.
-Jamie
|||Thanks, that was it.sql
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Friday, March 30, 2012
Losing Oracle user name and password
I have written a simple SQL Server 2005 package to pull some data from Oracle (using ODBC) and pumping it into SQL Server. When I run it from the server in debug mode in VS it works fine. When I schedule the job it errors out with "ora-01005: null password given; logon denied." The password is there. Has anyone experienced this? Is there a security setting somewhere preventing me from saving passwords? Is there a work around? Thanks.
Wednesday, March 28, 2012
Loose ODBC conection
I am very new to all this
That seems like an Access problem to me. Maybe you should ask in an
Access newsgroup.
I have a access databse wich uses a ODBC conection to mysql server. I
can open the and run the database find but if i leave the dadabase
loaded but do not use it when i go back to it is has lost the
conection and i need to close the databse and reopen it
If there a way to make sure i alwasy have a coenction
Thanks(S.Dickson@.shos.co.uk) writes:
Quote:
Originally Posted by
I am very new to all this
>
I have a access databse wich uses a ODBC conection to mysql server. I
can open the and run the database find but if i leave the dadabase
loaded but do not use it when i go back to it is has lost the
conection and i need to close the databse and reopen it
>
If there a way to make sure i alwasy have a coenction
That seems like an Access problem to me. Maybe you should ask in an
Access newsgroup.
Do you use MySQL or is that just a typo? This news group is for
MS SQL Server, and not MySQL.
--
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
Monday, March 26, 2012
Looping through databases in stored proc
I am trying to loop through the databases on a server (SQL 2000) and
dynamically run sp_helpfile against each database on the server. Of course
that means I need to store the name of the database as a variable or
parameter.
When I use the following code I am told "a USE database statement is not
allowed in a procedure or trigger.":
use @.dbname
go
exec sp_helpfile
When I use the following code I am told "Incorrect syntax near '.'"
exec @.dbname..sp_helpfile
Any suggestions?
Message posted via http://www.webservertalk.comLook at the "Undocumented" stored procedure sp_MSforeachdb in the Master
database.
Don't forget, use at your own risk. Since it is undocumented, it may change
or disappear in the next version or service pack. If this is a one-time
thing, go ahead, but don't use in production code.
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com|||You'll need to use dynamic SQL. Its complaining about the variable database
name in your EXEC statement. Check out this excellent article regarding
dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Just curious... What are you using this info for? Some sort of SQL admin.
application?
Paul
"Robert Richards via webservertalk.com" wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Try,
use northwind
go
create table #t (
dbn sysname,
fileid int,
filen sysname,
fileg sysname null,
size_ varchar(15),
maxsize_ varchar(15),
growth varchar(15),
usage varchar(128)
)
declare @.sql nvarchar(4000)
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'use [' + @.db + N'] execute sp_helpfile'
insert into #t
execute sp_executesql @.sql
end
close databases_cursor
deallocate databases_cursor
select
*
from
#t
order by
dbn, fileid
drop table #t
go
AMB
"Robert Richards via webservertalk.com" wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Robert Richards via webservertalk.com wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of
> course that means I need to store the name of the database as a
> variable or parameter.
> When I use the following code I am told "a USE database statement is
> not allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
>
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Yuu could try declaring and executing a string within your stored procedure
(I assume that you are using a cursor):
declare @.str varchar(255)
set @.str = 'exec ' + @.dbname + '..sp_helpfile'
--optional
select @.str AS TheStringToExecute
exec (@.str)
or you can use the undocumented stored procedure that is shown below:
EXEC sp_Msforeachdb 'PRINT (''?''); EXEC sp_helpfile'
Keith
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
dynamically run sp_helpfile against each database on the server. Of course
that means I need to store the name of the database as a variable or
parameter.
When I use the following code I am told "a USE database statement is not
allowed in a procedure or trigger.":
use @.dbname
go
exec sp_helpfile
When I use the following code I am told "Incorrect syntax near '.'"
exec @.dbname..sp_helpfile
Any suggestions?
Message posted via http://www.webservertalk.comLook at the "Undocumented" stored procedure sp_MSforeachdb in the Master
database.
Don't forget, use at your own risk. Since it is undocumented, it may change
or disappear in the next version or service pack. If this is a one-time
thing, go ahead, but don't use in production code.
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com|||You'll need to use dynamic SQL. Its complaining about the variable database
name in your EXEC statement. Check out this excellent article regarding
dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Just curious... What are you using this info for? Some sort of SQL admin.
application?
Paul
"Robert Richards via webservertalk.com" wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Try,
use northwind
go
create table #t (
dbn sysname,
fileid int,
filen sysname,
fileg sysname null,
size_ varchar(15),
maxsize_ varchar(15),
growth varchar(15),
usage varchar(128)
)
declare @.sql nvarchar(4000)
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'use [' + @.db + N'] execute sp_helpfile'
insert into #t
execute sp_executesql @.sql
end
close databases_cursor
deallocate databases_cursor
select
*
from
#t
order by
dbn, fileid
drop table #t
go
AMB
"Robert Richards via webservertalk.com" wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Robert Richards via webservertalk.com wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of
> course that means I need to store the name of the database as a
> variable or parameter.
> When I use the following code I am told "a USE database statement is
> not allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
>
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Yuu could try declaring and executing a string within your stored procedure
(I assume that you are using a cursor):
declare @.str varchar(255)
set @.str = 'exec ' + @.dbname + '..sp_helpfile'
--optional
select @.str AS TheStringToExecute
exec (@.str)
or you can use the undocumented stored procedure that is shown below:
EXEC sp_Msforeachdb 'PRINT (''?''); EXEC sp_helpfile'
Keith
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
Looping through Command Line Results...
I want to run a command line utility and return the results. I'm pretty sure
I
know how to do that. What's the best practice for looping through the results
to find specific information from the results of the command line?
Say I want to search for a string "computername" in each returned row.
Thanks.
On Fri, 20 Jul 2007 10:32:05 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:
>I want to run a command line utility and return the results. I'm pretty sure
>I
>know how to do that. What's the best practice for looping through the results
>to find specific information from the results of the command line?
>Say I want to search for a string "computername" in each returned row.
>Thanks.
There are diverse and contradictory way to interpret your question.
One would be that you want to run SQL Server commands from the command
prompt (sometimes called the DOS prompt). OSQL would let you run the
commands, and it is common to save the results to a .txt file. It is
possible to search a .txt file using the command line utility FIND.
Another interpretation of your question is that you intend to run
something from within SQL Server using xp_cmdshell and wish to search
the results. The approach in that case is to create a table with one
column (varchar(255) and then:
INSERT ThatTable (ThatColumn)
EXEC master..xp_cmdshell 'some command here'
After which you can search the table using SELECT and LIKE. If the
sequence of the rows is significant, include an IDENTITY column in the
table definition so that you can tell which order the rows came in.
A third interpreation could be that your question has nothing to do
with SQL Server and belongs somewhere else.
Perhaps you would like to clarify?
Roy Harvey
Beacon Falls, CT
|||Roy,
Thank you, and sorry for being unclear. The second answer is what I was
planning
and how I will do it. I just wasnt sure if I ran xp_cmdshell if I could
just loop
through the results without having to populate a table.
Thanks again.
"Roy Harvey" wrote:
> On Fri, 20 Jul 2007 10:32:05 -0700, Joe
> <Joe@.discussions.microsoft.com> wrote:
>
> There are diverse and contradictory way to interpret your question.
> One would be that you want to run SQL Server commands from the command
> prompt (sometimes called the DOS prompt). OSQL would let you run the
> commands, and it is common to save the results to a .txt file. It is
> possible to search a .txt file using the command line utility FIND.
> Another interpretation of your question is that you intend to run
> something from within SQL Server using xp_cmdshell and wish to search
> the results. The approach in that case is to create a table with one
> column (varchar(255) and then:
> INSERT ThatTable (ThatColumn)
> EXEC master..xp_cmdshell 'some command here'
> After which you can search the table using SELECT and LIKE. If the
> sequence of the rows is significant, include an IDENTITY column in the
> table definition so that you can tell which order the rows came in.
> A third interpreation could be that your question has nothing to do
> with SQL Server and belongs somewhere else.
> Perhaps you would like to clarify?
> Roy Harvey
> Beacon Falls, CT
>
I
know how to do that. What's the best practice for looping through the results
to find specific information from the results of the command line?
Say I want to search for a string "computername" in each returned row.
Thanks.
On Fri, 20 Jul 2007 10:32:05 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:
>I want to run a command line utility and return the results. I'm pretty sure
>I
>know how to do that. What's the best practice for looping through the results
>to find specific information from the results of the command line?
>Say I want to search for a string "computername" in each returned row.
>Thanks.
There are diverse and contradictory way to interpret your question.
One would be that you want to run SQL Server commands from the command
prompt (sometimes called the DOS prompt). OSQL would let you run the
commands, and it is common to save the results to a .txt file. It is
possible to search a .txt file using the command line utility FIND.
Another interpretation of your question is that you intend to run
something from within SQL Server using xp_cmdshell and wish to search
the results. The approach in that case is to create a table with one
column (varchar(255) and then:
INSERT ThatTable (ThatColumn)
EXEC master..xp_cmdshell 'some command here'
After which you can search the table using SELECT and LIKE. If the
sequence of the rows is significant, include an IDENTITY column in the
table definition so that you can tell which order the rows came in.
A third interpreation could be that your question has nothing to do
with SQL Server and belongs somewhere else.
Perhaps you would like to clarify?
Roy Harvey
Beacon Falls, CT
|||Roy,
Thank you, and sorry for being unclear. The second answer is what I was
planning
and how I will do it. I just wasnt sure if I ran xp_cmdshell if I could
just loop
through the results without having to populate a table.
Thanks again.
"Roy Harvey" wrote:
> On Fri, 20 Jul 2007 10:32:05 -0700, Joe
> <Joe@.discussions.microsoft.com> wrote:
>
> There are diverse and contradictory way to interpret your question.
> One would be that you want to run SQL Server commands from the command
> prompt (sometimes called the DOS prompt). OSQL would let you run the
> commands, and it is common to save the results to a .txt file. It is
> possible to search a .txt file using the command line utility FIND.
> Another interpretation of your question is that you intend to run
> something from within SQL Server using xp_cmdshell and wish to search
> the results. The approach in that case is to create a table with one
> column (varchar(255) and then:
> INSERT ThatTable (ThatColumn)
> EXEC master..xp_cmdshell 'some command here'
> After which you can search the table using SELECT and LIKE. If the
> sequence of the rows is significant, include an IDENTITY column in the
> table definition so that you can tell which order the rows came in.
> A third interpreation could be that your question has nothing to do
> with SQL Server and belongs somewhere else.
> Perhaps you would like to clarify?
> Roy Harvey
> Beacon Falls, CT
>
Looping through Command Line Results...
I want to run a command line utility and return the results. I'm pretty sur
e
I
know how to do that. What's the best practice for looping through the resul
ts
to find specific information from the results of the command line?
Say I want to search for a string "computername" in each returned row.
Thanks.On Fri, 20 Jul 2007 10:32:05 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:
>I want to run a command line utility and return the results. I'm pretty su
re
>I
>know how to do that. What's the best practice for looping through the resu
lts
>to find specific information from the results of the command line?
>Say I want to search for a string "computername" in each returned row.
>Thanks.
There are diverse and contradictory way to interpret your question.
One would be that you want to run SQL Server commands from the command
prompt (sometimes called the DOS prompt). OSQL would let you run the
commands, and it is common to save the results to a .txt file. It is
possible to search a .txt file using the command line utility FIND.
Another interpretation of your question is that you intend to run
something from within SQL Server using xp_cmdshell and wish to search
the results. The approach in that case is to create a table with one
column (varchar(255) and then:
INSERT ThatTable (ThatColumn)
EXEC master..xp_cmdshell 'some command here'
After which you can search the table using SELECT and LIKE. If the
sequence of the rows is significant, include an IDENTITY column in the
table definition so that you can tell which order the rows came in.
A third interpreation could be that your question has nothing to do
with SQL Server and belongs somewhere else.
Perhaps you would like to clarify?
Roy Harvey
Beacon Falls, CT|||Roy,
Thank you, and sorry for being unclear. The second answer is what I was
planning
and how I will do it. I just wasnt sure if I ran xp_cmdshell if I could
just loop
through the results without having to populate a table.
Thanks again.
"Roy Harvey" wrote:
> On Fri, 20 Jul 2007 10:32:05 -0700, Joe
> <Joe@.discussions.microsoft.com> wrote:
>
> There are diverse and contradictory way to interpret your question.
> One would be that you want to run SQL Server commands from the command
> prompt (sometimes called the DOS prompt). OSQL would let you run the
> commands, and it is common to save the results to a .txt file. It is
> possible to search a .txt file using the command line utility FIND.
> Another interpretation of your question is that you intend to run
> something from within SQL Server using xp_cmdshell and wish to search
> the results. The approach in that case is to create a table with one
> column (varchar(255) and then:
> INSERT ThatTable (ThatColumn)
> EXEC master..xp_cmdshell 'some command here'
> After which you can search the table using SELECT and LIKE. If the
> sequence of the rows is significant, include an IDENTITY column in the
> table definition so that you can tell which order the rows came in.
> A third interpreation could be that your question has nothing to do
> with SQL Server and belongs somewhere else.
> Perhaps you would like to clarify?
> Roy Harvey
> Beacon Falls, CT
>
e
I
know how to do that. What's the best practice for looping through the resul
ts
to find specific information from the results of the command line?
Say I want to search for a string "computername" in each returned row.
Thanks.On Fri, 20 Jul 2007 10:32:05 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:
>I want to run a command line utility and return the results. I'm pretty su
re
>I
>know how to do that. What's the best practice for looping through the resu
lts
>to find specific information from the results of the command line?
>Say I want to search for a string "computername" in each returned row.
>Thanks.
There are diverse and contradictory way to interpret your question.
One would be that you want to run SQL Server commands from the command
prompt (sometimes called the DOS prompt). OSQL would let you run the
commands, and it is common to save the results to a .txt file. It is
possible to search a .txt file using the command line utility FIND.
Another interpretation of your question is that you intend to run
something from within SQL Server using xp_cmdshell and wish to search
the results. The approach in that case is to create a table with one
column (varchar(255) and then:
INSERT ThatTable (ThatColumn)
EXEC master..xp_cmdshell 'some command here'
After which you can search the table using SELECT and LIKE. If the
sequence of the rows is significant, include an IDENTITY column in the
table definition so that you can tell which order the rows came in.
A third interpreation could be that your question has nothing to do
with SQL Server and belongs somewhere else.
Perhaps you would like to clarify?
Roy Harvey
Beacon Falls, CT|||Roy,
Thank you, and sorry for being unclear. The second answer is what I was
planning
and how I will do it. I just wasnt sure if I ran xp_cmdshell if I could
just loop
through the results without having to populate a table.
Thanks again.
"Roy Harvey" wrote:
> On Fri, 20 Jul 2007 10:32:05 -0700, Joe
> <Joe@.discussions.microsoft.com> wrote:
>
> There are diverse and contradictory way to interpret your question.
> One would be that you want to run SQL Server commands from the command
> prompt (sometimes called the DOS prompt). OSQL would let you run the
> commands, and it is common to save the results to a .txt file. It is
> possible to search a .txt file using the command line utility FIND.
> Another interpretation of your question is that you intend to run
> something from within SQL Server using xp_cmdshell and wish to search
> the results. The approach in that case is to create a table with one
> column (varchar(255) and then:
> INSERT ThatTable (ThatColumn)
> EXEC master..xp_cmdshell 'some command here'
> After which you can search the table using SELECT and LIKE. If the
> sequence of the rows is significant, include an IDENTITY column in the
> table definition so that you can tell which order the rows came in.
> A third interpreation could be that your question has nothing to do
> with SQL Server and belongs somewhere else.
> Perhaps you would like to clarify?
> Roy Harvey
> Beacon Falls, CT
>
Looping through Command Line Results...
I want to run a command line utility and return the results. I'm pretty sure
I
know how to do that. What's the best practice for looping through the results
to find specific information from the results of the command line?
Say I want to search for a string "computername" in each returned row.
Thanks.On Fri, 20 Jul 2007 10:32:05 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:
>I want to run a command line utility and return the results. I'm pretty sure
>I
>know how to do that. What's the best practice for looping through the results
>to find specific information from the results of the command line?
>Say I want to search for a string "computername" in each returned row.
>Thanks.
There are diverse and contradictory way to interpret your question.
One would be that you want to run SQL Server commands from the command
prompt (sometimes called the DOS prompt). OSQL would let you run the
commands, and it is common to save the results to a .txt file. It is
possible to search a .txt file using the command line utility FIND.
Another interpretation of your question is that you intend to run
something from within SQL Server using xp_cmdshell and wish to search
the results. The approach in that case is to create a table with one
column (varchar(255) and then:
INSERT ThatTable (ThatColumn)
EXEC master..xp_cmdshell 'some command here'
After which you can search the table using SELECT and LIKE. If the
sequence of the rows is significant, include an IDENTITY column in the
table definition so that you can tell which order the rows came in.
A third interpreation could be that your question has nothing to do
with SQL Server and belongs somewhere else.
Perhaps you would like to clarify?
Roy Harvey
Beacon Falls, CT|||Roy,
Thank you, and sorry for being unclear. The second answer is what I was
planning
and how I will do it. I just wasnt sure if I ran xp_cmdshell if I could
just loop
through the results without having to populate a table.
Thanks again.
"Roy Harvey" wrote:
> On Fri, 20 Jul 2007 10:32:05 -0700, Joe
> <Joe@.discussions.microsoft.com> wrote:
> >I want to run a command line utility and return the results. I'm pretty sure
> >I
> >know how to do that. What's the best practice for looping through the results
> >to find specific information from the results of the command line?
> >
> >Say I want to search for a string "computername" in each returned row.
> >
> >Thanks.
> There are diverse and contradictory way to interpret your question.
> One would be that you want to run SQL Server commands from the command
> prompt (sometimes called the DOS prompt). OSQL would let you run the
> commands, and it is common to save the results to a .txt file. It is
> possible to search a .txt file using the command line utility FIND.
> Another interpretation of your question is that you intend to run
> something from within SQL Server using xp_cmdshell and wish to search
> the results. The approach in that case is to create a table with one
> column (varchar(255) and then:
> INSERT ThatTable (ThatColumn)
> EXEC master..xp_cmdshell 'some command here'
> After which you can search the table using SELECT and LIKE. If the
> sequence of the rows is significant, include an IDENTITY column in the
> table definition so that you can tell which order the rows came in.
> A third interpreation could be that your question has nothing to do
> with SQL Server and belongs somewhere else.
> Perhaps you would like to clarify?
> Roy Harvey
> Beacon Falls, CT
>sql
I
know how to do that. What's the best practice for looping through the results
to find specific information from the results of the command line?
Say I want to search for a string "computername" in each returned row.
Thanks.On Fri, 20 Jul 2007 10:32:05 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:
>I want to run a command line utility and return the results. I'm pretty sure
>I
>know how to do that. What's the best practice for looping through the results
>to find specific information from the results of the command line?
>Say I want to search for a string "computername" in each returned row.
>Thanks.
There are diverse and contradictory way to interpret your question.
One would be that you want to run SQL Server commands from the command
prompt (sometimes called the DOS prompt). OSQL would let you run the
commands, and it is common to save the results to a .txt file. It is
possible to search a .txt file using the command line utility FIND.
Another interpretation of your question is that you intend to run
something from within SQL Server using xp_cmdshell and wish to search
the results. The approach in that case is to create a table with one
column (varchar(255) and then:
INSERT ThatTable (ThatColumn)
EXEC master..xp_cmdshell 'some command here'
After which you can search the table using SELECT and LIKE. If the
sequence of the rows is significant, include an IDENTITY column in the
table definition so that you can tell which order the rows came in.
A third interpreation could be that your question has nothing to do
with SQL Server and belongs somewhere else.
Perhaps you would like to clarify?
Roy Harvey
Beacon Falls, CT|||Roy,
Thank you, and sorry for being unclear. The second answer is what I was
planning
and how I will do it. I just wasnt sure if I ran xp_cmdshell if I could
just loop
through the results without having to populate a table.
Thanks again.
"Roy Harvey" wrote:
> On Fri, 20 Jul 2007 10:32:05 -0700, Joe
> <Joe@.discussions.microsoft.com> wrote:
> >I want to run a command line utility and return the results. I'm pretty sure
> >I
> >know how to do that. What's the best practice for looping through the results
> >to find specific information from the results of the command line?
> >
> >Say I want to search for a string "computername" in each returned row.
> >
> >Thanks.
> There are diverse and contradictory way to interpret your question.
> One would be that you want to run SQL Server commands from the command
> prompt (sometimes called the DOS prompt). OSQL would let you run the
> commands, and it is common to save the results to a .txt file. It is
> possible to search a .txt file using the command line utility FIND.
> Another interpretation of your question is that you intend to run
> something from within SQL Server using xp_cmdshell and wish to search
> the results. The approach in that case is to create a table with one
> column (varchar(255) and then:
> INSERT ThatTable (ThatColumn)
> EXEC master..xp_cmdshell 'some command here'
> After which you can search the table using SELECT and LIKE. If the
> sequence of the rows is significant, include an IDENTITY column in the
> table definition so that you can tell which order the rows came in.
> A third interpreation could be that your question has nothing to do
> with SQL Server and belongs somewhere else.
> Perhaps you would like to clarify?
> Roy Harvey
> Beacon Falls, CT
>sql
Looping Query
I know I have done this before but for the life of me I can remember how'
When I run the query below it loops and loops, how do I make it appear just
once - missing something but brain freeze has taken over aaaggghhh...
select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
[transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '27 September,2004' and '28 September, 2004'
your help much appreciatedWhat do you mean by "The Query loops" ? A query doesn=B4t loop around on
its own, actually it doesn=B4t loop at all.|||same information in rows 1-84 shows up in query analyzer then row 85 shows
row 1 again then so on until row 829227262525......or until system stops
"Jens" wrote:
> What do you mean by "The Query loops" ? A query doesn′t loop around on
> its own, actually it doesn′t loop at all.
>|||examnotes (Ivo@.discussions.microsoft.com) writes:
> I know I have done this before but for the life of me I can remember how'
> When I run the query below it loops and loops, how do I make it appear
> just once - missing something but brain freeze has taken over
> aaaggghhh...
> select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
> [transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '27 September,2004' and '28 September, 2004'
You have two tables in your WHERE clause, but there is no condition for
joining them, which means that you get call possible combination of
rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
rows in transactions for the given time range, the query will produce
one milliard rows.
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|||Erland Sommarskog skrev:
> You have two tables in your WHERE clause, but there is no condition for
> joining them, which means that you get call possible combination of
> rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
> rows in transactions for the given time range, the query will produce
> one milliard rows.
>
That would be one 'billion' in english, or are there even differences
between british and american english? (Not to mention Erland's swedish
english ;) )
/impslayer, aka Birger Johansson|||impslayer (impslayer@.hotmail.com) writes:
> That would be one 'billion' in english, or are there even differences
> between british and american english?
See http://www.m-w.com/dictionary/milliard.
However, both in British and American English, they capitalise "British",
"American" and "English". :-)
But maybe we should table this discussion before it goes too far. (Now,
let's see how many how get *that* one!)
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|||Erland Sommarskog skrev:
> impslayer (impslayer@.hotmail.com) writes:
> See http://www.m-w.com/dictionary/milliard.
>
When will I ever (EVER!) learn to check stuff out before I make a
post?
Had no idea 'milliard' existed in English (sic!), and was hoping I'd
clarify that you meant 'billion'... Definitely no cheap shot, I promise
:)
/impslayer, aka NoBrainPoster|||impslayer (impslayer@.hotmail.com) writes:
> When will I ever (EVER!) learn to check stuff out before I make a
> post?
> Had no idea 'milliard' existed in English (sic!), and was hoping I'd
> clarify that you meant 'billion'... Definitely no cheap shot, I promise
>:)
Rest assured that if I ever say "billion", I mean a real big billion, and
not the wimpy billions they have over there!
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
When I run the query below it loops and loops, how do I make it appear just
once - missing something but brain freeze has taken over aaaggghhh...
select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
[transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '27 September,2004' and '28 September, 2004'
your help much appreciatedWhat do you mean by "The Query loops" ? A query doesn=B4t loop around on
its own, actually it doesn=B4t loop at all.|||same information in rows 1-84 shows up in query analyzer then row 85 shows
row 1 again then so on until row 829227262525......or until system stops
"Jens" wrote:
> What do you mean by "The Query loops" ? A query doesn′t loop around on
> its own, actually it doesn′t loop at all.
>|||examnotes (Ivo@.discussions.microsoft.com) writes:
> I know I have done this before but for the life of me I can remember how'
> When I run the query below it loops and loops, how do I make it appear
> just once - missing something but brain freeze has taken over
> aaaggghhh...
> select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
> [transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '27 September,2004' and '28 September, 2004'
You have two tables in your WHERE clause, but there is no condition for
joining them, which means that you get call possible combination of
rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
rows in transactions for the given time range, the query will produce
one milliard rows.
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|||Erland Sommarskog skrev:
> You have two tables in your WHERE clause, but there is no condition for
> joining them, which means that you get call possible combination of
> rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
> rows in transactions for the given time range, the query will produce
> one milliard rows.
>
That would be one 'billion' in english, or are there even differences
between british and american english? (Not to mention Erland's swedish
english ;) )
/impslayer, aka Birger Johansson|||impslayer (impslayer@.hotmail.com) writes:
> That would be one 'billion' in english, or are there even differences
> between british and american english?
See http://www.m-w.com/dictionary/milliard.
However, both in British and American English, they capitalise "British",
"American" and "English". :-)
But maybe we should table this discussion before it goes too far. (Now,
let's see how many how get *that* one!)
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|||Erland Sommarskog skrev:
> impslayer (impslayer@.hotmail.com) writes:
> See http://www.m-w.com/dictionary/milliard.
>
When will I ever (EVER!) learn to check stuff out before I make a
post?
Had no idea 'milliard' existed in English (sic!), and was hoping I'd
clarify that you meant 'billion'... Definitely no cheap shot, I promise
:)
/impslayer, aka NoBrainPoster|||impslayer (impslayer@.hotmail.com) writes:
> When will I ever (EVER!) learn to check stuff out before I make a
> post?
> Had no idea 'milliard' existed in English (sic!), and was hoping I'd
> clarify that you meant 'billion'... Definitely no cheap shot, I promise
>:)
Rest assured that if I ever say "billion", I mean a real big billion, and
not the wimpy billions they have over there!
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
Friday, March 23, 2012
loop to run 'Create Trigger' script?
I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@.TriggerName' error.
if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go
CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = tblUsers
..................from here the code is the same for all
You can add to this script.
set nocount on
declare @.cursor cursor,
@.triggerName sysname
set @.cursor = cursor for (select name
from sys.triggers)
open @.cursor
while (1=1)
begin
fetch next from @.cursor into @.triggerName
if @.@.fetch_status <> 0
break
select @.triggername
end
If you are using 2000, use:
select name
from sysobjects
where xtype = 'tr'
for the select statement...
|||The triggers don't yet exist, so they won't be in sysobjects.
I tried putting the 'create trigger' code in a sp, with table name and trigger name as parameters but get a syntax error when using a variable.
|||Just to clarify based on a comment from a related thread-
"On a related note, this is really not a very good approach. You should create a different trigger for every table (I like the approach of creating the triggers automatically using the loop, but not like this, as it will be problematic and slow)."
I am creating different triggers for each table, but they are created by a common script that dynamically determines the column names, etc. I would like a loop to run the create script for each table, as opposed to editing the table name and trigger name and running the script manually for each table.
|||Sorry, I misunderstood :) Will this work for you:
set nocount on
declare @.cursor cursor,
@.tableName sysname
set @.cursor = cursor for (select name
from sys.tables)
open @.cursor
while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break
select 'if exists (select * from sysobjects where id =
object_id (N''dbo.tg' + @.tableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)
drop trigger dbo.tg' + @.tableName + 'Audit
go
CREATE TRIGGER tg' + @.tableName + 'Audit on ' + @.tableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.tableName + '''
<more code here>
'
end
|||Yes, this is along the lines of what I want to do- however... my code is just too ugly and I'm struggling with getting the string all to concatenate correctly (can't get the quotes right and don't have time to play with it right now). I guess for now I'll just have to run it separately for each table- boo hoo!|||Though you have said you want to do it one table by one table, I am still put this code here incase it can save you some energy.
select 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + name + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + name + 'Audit
go'
from sys.tables
SELECT '
CREATE TRIGGER tg' + name + 'Audit on ' + name + ' FOR insert, update, delete
AS
DECLARE name varchar(128)
SET name = ''' + name + '''
' + char(13) +
'<more code here>
' + char(13) +
char(13)
from sys.tables
This will generate two batch. First one clean existing trigger. The scaond one has the head part of each trigger. Replace "<more code here>" with your common code. That will give you the excutable batch to do the job.
|||I really want to get it to work, but am short of time and just can't seem to get it right.
I would generate the table names from a populated cursor vs from sysobjects because not all of the tables in the db should be audited by this trigger, so I was thinking something like the following.
set nocount on
declare @.cursor cursor
declare @.tblTableNames table (TableName varchar(30))
insert into @.tblTableNames (TableName) values ('tblCompanies')
insert into @.tblTableNames (TableName) values ('tblDepartments')
insert into @.tblTableNames (TableName) values ('tblManagementLevels').......
set @.cursor = cursor for (select TableName
from @.tblTableNames)
open @.cursor
while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break
<create script code here.....>
end
close cursor
deallocate cursor
Also, something I am having trouble with is that my <create script code here.....> is full of somewhat complex code, embedded strings, etc. Posted below for your reading enjoyment...
if exists (select * from sysobjects where id =
object_id (N'dbo.tgDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgDepartmentsAudit
go
CREATE TRIGGER tgDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'
-
DECLARE @.fieldname varchar(128),
@.pkJoinClause varchar(1000),
@.sql nvarchar(2000),
@.UpdateDate varchar(21),
@.UserName varchar(128),
@.TriggerType nchar(1),
@.rowId int,
@.maxRowId int,
@.str1 varchar(100),
@.str2 varchar(100)
SET @.UserName = SYSTEM_USER
SET @.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
--detemine type of trigger
IF exists(select * FROM inserted) AND exists(select * from deleted)
SET @.TriggerType = 'U'
else
IF exists(select * FROM inserted)
SET @.TriggerType = 'I'
ELSE
SET @.TriggerType = 'D'
--get all Column names for table
SELECT c1.COLUMN_NAME as colName, c1.ORDINAL_POSITION as RowId into #tblFieldNames
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c1 ON t1.TABLE_NAME = c1.TABLE_NAME
WHERE t1.TABLE_NAME = @.tableName
-- Get PRIMARY KEY columns
select c.COLUMN_NAME as colName INTO #primaryKeyFields
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on (c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
WHERE pk.TABLE_NAME = @.tableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
--Create Join clause for primary key field(s)
SELECT @.pkJoinClause = coalesce(@.pkJoinClause + ' and', ' on') + ' i.' + PKF.colName + ' = d.' + PKF.colName
FROM #primaryKeyFields PKF
--Throw error if no primary key
IF @.pkJoinClause IS NULL
BEGIN
raiserror('no PK ON TABLE %s', 16, -1, @.TableName)
RETURN
END
-
-- the 'inserted' and 'deleted' tables have limitations, dump to temp tables for greater control
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
--get number of columns
select
@.rowId = min(RowId),
@.MaxRowId = max(RowId)
from #tblFieldNames
-- Loop through fields and build Sql string
while @.RowId <= @.MaxRowId
BEGIN
SELECT @.fieldname = colName FROM #tblFieldNames WHERE RowId = @.RowId
SELECT @.sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @.sql = @.sql + ' select ''' + @.TableName + ''''
SELECT @.sql = @.sql + ',''' + @.TriggerType + ''''
SELECT @.sql = @.sql + ',''' + @.fieldname + ''''
SELECT @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',''' + @.UpdateDate + ''''
SELECT @.sql = @.sql + ', 1'
SELECT @.sql = @.sql + ',''' + @.UserName + ''''
SELECT @.sql = @.sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @.sql = @.sql + @.pkJoinClause
SELECT @.sql = @.sql + ' WHERE (''' + @.TriggerType + ''' = ''I'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''D'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''U'' AND '
SELECT @.sql = @.sql + '((i.' + @.fieldname + ' <> d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ' OR (''' + @.fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NULL AND d.' + @.fieldname + ' is NOT null)'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NOT NULL AND d.' + @.fieldname + ' is null)))'
EXEC (@.sql)
set @.RowId = @.RowId + 1
END
Drop Table #ins
Drop Table #del
Drop Table #tblFieldNames
Drop Table #primaryKeyFields
go
Incidentally, if you're interested in what the actual audit table looks like, run this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAuditAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAuditAdmin]
GO
CREATE TABLE [dbo].[tblAuditAdmin] (
[UpdateDate] [datetime] NOT NULL ,
[TableAltered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateNumber] [int] NULL ,
[Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblAuditAdmin] ADD
CONSTRAINT [PK_tblAuditAdmin] PRIMARY KEY CLUSTERED
(
[UpdateDate],
[TableAltered],
[FieldName]
) ON [PRIMARY]
GO
Sorry. Forgot to tell you how to use the code I posted yesterday.
Change the output of Query Analyzer to 'text'. excute the code. Copy the output to edit portion of Query Analyzer. Then replace "<more code here>" with the common part of your code. Now you will have the script.
This makeshift works well, If you don't want to spend too much time.
|||Oh, I get it! This worked great, thanks to Aego!
For what it's worth, the following is what I ended up with.
-- To create this trigger for each table, insert the table name into the 2nd line of code (set @.TableName = 'TblCompanies'),
-- then highlight and execute the "first block of code" (inside dashed lines). Then paste the output from this into the
-- second block. Select that code all the way to the bottom and execute to create the trigger for that table. Do this
-- for each table name that you want the trigger created for. Alternatively, you can just set the table name and trigger
-- name manually for each table in the second block of code.
--First block of code...
declare @.TableName varchar(50)
set @.TableName = 'tblDepartments'
declare @.string varchar(5000)
set @.string = 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + @.TableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + @.TableName + 'Audit ' + + char(13) +
'go ' + char(13) +
'CREATE TRIGGER tg' + @.TableName + 'Audit on ' + @.TableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.TableName + '''
' + char(13)
print @.string
--Second block of code...
--replace code in this block with the output from above block and execute from here down
if exists (select * from sysobjects where id = object_id (N'dbo.tgtblDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgtblDepartmentsAudit
go
CREATE TRIGGER tgtblDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'
...... the rest of my code.......
loop to run 'Create Trigger' script?
I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@.TriggerName' error.
if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go
CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = tblUsers
..................from here the code is the same for all
You can add to this script.
set nocount on
declare @.cursor cursor,
@.triggerName sysname
set @.cursor = cursor for (select name
from sys.triggers)
open @.cursor
while (1=1)
begin
fetch next from @.cursor into @.triggerName
if @.@.fetch_status <> 0
break
select @.triggername
end
If you are using 2000, use:
select name
from sysobjects
where xtype = 'tr'
for the select statement...
|||The triggers don't yet exist, so they won't be in sysobjects.
I tried putting the 'create trigger' code in a sp, with table name and trigger name as parameters but get a syntax error when using a variable.
|||Just to clarify based on a comment from a related thread-
"On a related note, this is really not a very good approach. You should create a different trigger for every table (I like the approach of creating the triggers automatically using the loop, but not like this, as it will be problematic and slow)."
I am creating different triggers for each table, but they are created by a common script that dynamically determines the column names, etc. I would like a loop to run the create script for each table, as opposed to editing the table name and trigger name and running the script manually for each table.
|||Sorry, I misunderstood :) Will this work for you:
set nocount on
declare @.cursor cursor,
@.tableName sysname
set @.cursor = cursor for (select name
from sys.tables)
open @.cursor
while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break
select 'if exists (select * from sysobjects where id =
object_id (N''dbo.tg' + @.tableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)
drop trigger dbo.tg' + @.tableName + 'Audit
go
CREATE TRIGGER tg' + @.tableName + 'Audit on ' + @.tableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.tableName + '''
<more code here>
'
end
|||Yes, this is along the lines of what I want to do- however... my code is just too ugly and I'm struggling with getting the string all to concatenate correctly (can't get the quotes right and don't have time to play with it right now). I guess for now I'll just have to run it separately for each table- boo hoo!|||Though you have said you want to do it one table by one table, I am still put this code here incase it can save you some energy.
select 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + name + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + name + 'Audit
go'
from sys.tables
SELECT '
CREATE TRIGGER tg' + name + 'Audit on ' + name + ' FOR insert, update, delete
AS
DECLARE name varchar(128)
SET name = ''' + name + '''
' + char(13) +
'<more code here>
' + char(13) +
char(13)
from sys.tables
This will generate two batch. First one clean existing trigger. The scaond one has the head part of each trigger. Replace "<more code here>" with your common code. That will give you the excutable batch to do the job.
|||I really want to get it to work, but am short of time and just can't seem to get it right.
I would generate the table names from a populated cursor vs from sysobjects because not all of the tables in the db should be audited by this trigger, so I was thinking something like the following.
set nocount on
declare @.cursor cursor
declare @.tblTableNames table (TableName varchar(30))
insert into @.tblTableNames (TableName) values ('tblCompanies')
insert into @.tblTableNames (TableName) values ('tblDepartments')
insert into @.tblTableNames (TableName) values ('tblManagementLevels').......
set @.cursor = cursor for (select TableName
from @.tblTableNames)
open @.cursor
while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break
<create script code here.....>
end
close cursor
deallocate cursor
Also, something I am having trouble with is that my <create script code here.....> is full of somewhat complex code, embedded strings, etc. Posted below for your reading enjoyment...
if exists (select * from sysobjects where id =
object_id (N'dbo.tgDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgDepartmentsAudit
go
CREATE TRIGGER tgDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'
-
DECLARE @.fieldname varchar(128),
@.pkJoinClause varchar(1000),
@.sql nvarchar(2000),
@.UpdateDate varchar(21),
@.UserName varchar(128),
@.TriggerType nchar(1),
@.rowId int,
@.maxRowId int,
@.str1 varchar(100),
@.str2 varchar(100)
SET @.UserName = SYSTEM_USER
SET @.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
--detemine type of trigger
IF exists(select * FROM inserted) AND exists(select * from deleted)
SET @.TriggerType = 'U'
else
IF exists(select * FROM inserted)
SET @.TriggerType = 'I'
ELSE
SET @.TriggerType = 'D'
--get all Column names for table
SELECT c1.COLUMN_NAME as colName, c1.ORDINAL_POSITION as RowId into #tblFieldNames
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c1 ON t1.TABLE_NAME = c1.TABLE_NAME
WHERE t1.TABLE_NAME = @.tableName
-- Get PRIMARY KEY columns
select c.COLUMN_NAME as colName INTO #primaryKeyFields
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on (c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
WHERE pk.TABLE_NAME = @.tableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
--Create Join clause for primary key field(s)
SELECT @.pkJoinClause = coalesce(@.pkJoinClause + ' and', ' on') + ' i.' + PKF.colName + ' = d.' + PKF.colName
FROM #primaryKeyFields PKF
--Throw error if no primary key
IF @.pkJoinClause IS NULL
BEGIN
raiserror('no PK ON TABLE %s', 16, -1, @.TableName)
RETURN
END
-
-- the 'inserted' and 'deleted' tables have limitations, dump to temp tables for greater control
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
--get number of columns
select
@.rowId = min(RowId),
@.MaxRowId = max(RowId)
from #tblFieldNames
-- Loop through fields and build Sql string
while @.RowId <= @.MaxRowId
BEGIN
SELECT @.fieldname = colName FROM #tblFieldNames WHERE RowId = @.RowId
SELECT @.sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @.sql = @.sql + ' select ''' + @.TableName + ''''
SELECT @.sql = @.sql + ',''' + @.TriggerType + ''''
SELECT @.sql = @.sql + ',''' + @.fieldname + ''''
SELECT @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',''' + @.UpdateDate + ''''
SELECT @.sql = @.sql + ', 1'
SELECT @.sql = @.sql + ',''' + @.UserName + ''''
SELECT @.sql = @.sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @.sql = @.sql + @.pkJoinClause
SELECT @.sql = @.sql + ' WHERE (''' + @.TriggerType + ''' = ''I'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''D'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''U'' AND '
SELECT @.sql = @.sql + '((i.' + @.fieldname + ' <> d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ' OR (''' + @.fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NULL AND d.' + @.fieldname + ' is NOT null)'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NOT NULL AND d.' + @.fieldname + ' is null)))'
EXEC (@.sql)
set @.RowId = @.RowId + 1
END
Drop Table #ins
Drop Table #del
Drop Table #tblFieldNames
Drop Table #primaryKeyFields
go
Incidentally, if you're interested in what the actual audit table looks like, run this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAuditAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAuditAdmin]
GO
CREATE TABLE [dbo].[tblAuditAdmin] (
[UpdateDate] [datetime] NOT NULL ,
[TableAltered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateNumber] [int] NULL ,
[Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblAuditAdmin] ADD
CONSTRAINT [PK_tblAuditAdmin] PRIMARY KEY CLUSTERED
(
[UpdateDate],
[TableAltered],
[FieldName]
) ON [PRIMARY]
GO
Sorry. Forgot to tell you how to use the code I posted yesterday.
Change the output of Query Analyzer to 'text'. excute the code. Copy the output to edit portion of Query Analyzer. Then replace "<more code here>" with the common part of your code. Now you will have the script.
This makeshift works well, If you don't want to spend too much time.
|||Oh, I get it! This worked great, thanks to Aego!
For what it's worth, the following is what I ended up with.
-- To create this trigger for each table, insert the table name into the 2nd line of code (set @.TableName = 'TblCompanies'),
-- then highlight and execute the "first block of code" (inside dashed lines). Then paste the output from this into the
-- second block. Select that code all the way to the bottom and execute to create the trigger for that table. Do this
-- for each table name that you want the trigger created for. Alternatively, you can just set the table name and trigger
-- name manually for each table in the second block of code.
--First block of code...
declare @.TableName varchar(50)
set @.TableName = 'tblDepartments'
declare @.string varchar(5000)
set @.string = 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + @.TableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + @.TableName + 'Audit ' + + char(13) +
'go ' + char(13) +
'CREATE TRIGGER tg' + @.TableName + 'Audit on ' + @.TableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.TableName + '''
' + char(13)
print @.string
--Second block of code...
--replace code in this block with the output from above block and execute from here down
if exists (select * from sysobjects where id = object_id (N'dbo.tgtblDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgtblDepartmentsAudit
go
CREATE TRIGGER tgtblDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'
...... the rest of my code.......
Wednesday, March 21, 2012
looooong backup differential
I am making backup of several data bases to tape, Sunday run backup full and the particular days backup differential, the problem is that backup differential delays more than backup full, I must mention that I am using the option nounload.:confused:Originally posted by lvigo
I am making backup of several data bases to tape, Sunday run backup full and the particular days backup differential, the problem is that backup differential delays more than backup full, I must mention that I am using the option nounload.:confused:
Can't you dump to disk, then copy the file to tape?|||No, i don't have disk for thaht, but what is the technical solution ??|||Originally posted by lvigo
No, i don't have disk for thaht, but what is the technical solution ??
You have to have a hard drive...is it raid? How big is the hard drive?
Are you using the gui to set up backups or using t-sql?
Also what version of sql server?|||I don't have disk space for make a backup. The version is 7. with sp 3|||Originally posted by lvigo
I don't have disk space for make a backup. The version is 7. with sp 3
you can use UNC filenames to make the backup...but going across the network mught be slower...
But if you're running out of room on tape...then you might have no choice.
Are you overwriting the nightly dump, or appending?|||I need make the backup to tape, full backup is executed the weekend and on the same tape we append the differential backup of the other days.|||Originally posted by lvigo
I need make the backup to tape, full backup is executed the weekend and on the same tape we append the differential backup of the other days.
Are you using 1 tape? How full is it? Can you swap out the Sunday tape with the full back ups and put a new 1 in for differential...if the get to big can you swap them out every night...
just guessing...need to know what you're doing...
I am making backup of several data bases to tape, Sunday run backup full and the particular days backup differential, the problem is that backup differential delays more than backup full, I must mention that I am using the option nounload.:confused:
Can't you dump to disk, then copy the file to tape?|||No, i don't have disk for thaht, but what is the technical solution ??|||Originally posted by lvigo
No, i don't have disk for thaht, but what is the technical solution ??
You have to have a hard drive...is it raid? How big is the hard drive?
Are you using the gui to set up backups or using t-sql?
Also what version of sql server?|||I don't have disk space for make a backup. The version is 7. with sp 3|||Originally posted by lvigo
I don't have disk space for make a backup. The version is 7. with sp 3
you can use UNC filenames to make the backup...but going across the network mught be slower...
But if you're running out of room on tape...then you might have no choice.
Are you overwriting the nightly dump, or appending?|||I need make the backup to tape, full backup is executed the weekend and on the same tape we append the differential backup of the other days.|||Originally posted by lvigo
I need make the backup to tape, full backup is executed the weekend and on the same tape we append the differential backup of the other days.
Are you using 1 tape? How full is it? Can you swap out the Sunday tape with the full back ups and put a new 1 in for differential...if the get to big can you swap them out every night...
just guessing...need to know what you're doing...
Friday, March 9, 2012
looking to write my own simple SQL Client via TCP/IP
I'm looking to write my own simple SQL Client via TCP/IP. We have some hardware that does not run an OS of sorts, but I can create TCP Connectons. We's like to be able to Query our SQL Server with this hardware.
The SQL Servers we are talking to are MS SQL2000 and MS SQL 2005 both via TCP port 1433. We can telnet to the port of the SQL Server. Any resources to the SQL Protocol you could pass along would be great.
Thank you,
Scott<-
Hi Scott,
In order to create an application driver that can send requests and receive responses from a SQL Server instance, your application must be able to communicate using the Tablular Data Stream (TDS) protocol. The MS TDS protocol is a proprietary protocol and must be licensed from Microsoft. If this interests you, then please let me know and I'll send you information on getting started.
Thanks,
Il-Sung.
Its not too proprietary since there are Linux and Java versions of the TDS Protocol out there with the Source? If its not too much trouble wont hurt to see whats involved in geting the License for the Protocol.
I've found the Following on TDS since I did the original Post:
http://en.wikipedia.org/wiki/Tabular_Data_Stream
http://www.freetds.org/
http://jtds.sourceforge.net/
I'm sure the MS version of it is more full featured when communicating to MS SQL 2005, though I jsut need to return a few Select statements.
I was thinking It might be easier to send a HTTP Request to IIS on the SQL Server and send the Select statement as a parameter and return the Data. I'm looking for something pretty simple as the hardware playform I'm working with is pretty limited.
Thank you,
Scott<-
|||Hi Scott,
Yes, there are free implimentations available although none are endorsed by Microsoft and they are typically based on reverse-engineering efforts. If you'd like to license TDS start with the MS IP Licensing website (http://www.microsoft.com/about/legal/intellectualproperty) and search for TDS.
Alternatively, if your can send HTTP requests from your hardware platform, you can take advantage of the fact that SQL Server 2005 can active as a native web service for SOAP applications. Take a look at the following documentation for more info:
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
http://msdn2.microsoft.com/en-us/library/ms345123.aspx
Il-Sung.
Looking for thoughts on my upgrade strategy
Hello,
We've run upgrade advisor and fixed everything and we're ready to upgrade from sql 2k to sql 2k5. We did find a problem though. Our current sql2k has an instance name of "dbserver". The problem is that we wanted to put CRM 3.0 data on this instance as well, but I guess CRM 3.0 will only install based upon the default instance only. So here is my thoughts and was wondering if someone see any problems on this process.
1. Install another sql2k instance, using the default instance name this time, on the current w2k3 box which holds our current sql2k instance named "dbserver".
2. Once default instance is up and running, transfer over logins, operators.
3. Stop current live instance "dbserver"
4. Use new default sql2k instance and attach all databases from the stopped "dbserver" instance other than master, tempdb, model, msdb.
5. Run the upgrade on default instance, which now has all current databases associated to it, for sql2k5.
6. Uninstall "dbserver" instance.
Anyone see any problems on suggestions? Thanks in advance.
You approach will work but there is a quicker option you could consider. You could install a default instance of SQL Server 2005 and migrate your databases and logins directly over to that new instance instead of going through the intermediate SQL 2000 installation step.
Michelle
Wednesday, March 7, 2012
looking for some hints on SP performance
I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JD
does it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon
|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>
|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.ST ART_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8) ,@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX (',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USER S))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resou rces.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+ 1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>
|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JD
does it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon
|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>
|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.ST ART_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8) ,@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX (',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USER S))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resou rces.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+ 1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>
|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>
looking for some hints on SP performance
I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||I suggest you first read up on the difference between constants, parameters
and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is crea
ted and estimates
selectivity. Plan is created based on that and re-used (even if not optimal
for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average
of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equa
ls returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...reen">
> I'm trying to figure out why a SQL statement will run faster in a query wi
ndow then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5
minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting
it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I c
heck it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless index
es have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with thi
s.
> Thanks,
> JD
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARC
HAR(8),@.START_DATE),101),CONVERT(DAT
ETIME,CONVERT(VARCHAR(8),@.END_DATE),101)
)
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1
,CHARINDEX(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1
,LEN(@.USERS))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('',Resources.LogonName)+1),LEN(Res
ources.LogonName)-CHARINDEX('',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('',U.LogonName)+1),LEN(U.LogonName)-CHARIN
DEX('',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||I suggest you first read up on the difference between constants, parameters
and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is crea
ted and estimates
selectivity. Plan is created based on that and re-used (even if not optimal
for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average
of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equa
ls returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...reen">
> I'm trying to figure out why a SQL statement will run faster in a query wi
ndow then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5
minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting
it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I c
heck it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless index
es have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with thi
s.
> Thanks,
> JD
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARC
HAR(8),@.START_DATE),101),CONVERT(DAT
ETIME,CONVERT(VARCHAR(8),@.END_DATE),101)
)
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1
,CHARINDEX(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1
,LEN(@.USERS))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('',Resources.LogonName)+1),LEN(Res
ources.LogonName)-CHARINDEX('',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('',U.LogonName)+1),LEN(U.LogonName)-CHARIN
DEX('',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>
looking for some hints on SP performance
I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.START_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8),@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USERS))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resources.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.LogonName)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.LogonName)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>> I'm trying to figure out why a SQL statement will run faster in a query
>> window then as a stored procedure. In a query window the SQL runs in 2
>> seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
>> cross a database select, so I'm not sure of posting it here in the group.
>> I've looked at Procedure cache seems to be more then enough but how do I
>> check it to be sure?
>> I've updates statistics but, that hasn't made any difference.
>> There are indexes that are being used, so I think that is ok. Unless
>> indexes have different affects on a interactive query vs. a SP?
>> I'm open to any other options that I can look at that may help me with
>> this.
>> Thanks,
>> JD
>
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.START_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8),@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USERS))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resources.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.LogonName)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.LogonName)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>> I'm trying to figure out why a SQL statement will run faster in a query
>> window then as a stored procedure. In a query window the SQL runs in 2
>> seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
>> cross a database select, so I'm not sure of posting it here in the group.
>> I've looked at Procedure cache seems to be more then enough but how do I
>> check it to be sure?
>> I've updates statistics but, that hasn't made any difference.
>> There are indexes that are being used, so I think that is ok. Unless
>> indexes have different affects on a interactive query vs. a SP?
>> I'm open to any other options that I can look at that may help me with
>> this.
>> Thanks,
>> JD
>
Subscribe to:
Posts (Atom)