Killer Union

I have two queries joined with a union, one query by itself takes 34ms to
complete and the other runs by itself in 340ms but when they are joined by
the union (or a union all) the combined query takes an incredible one minute
and 15 seconds. Why does the union com with such an incredible cost?
The query is:
select
U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
SQL_Latin1_General_CP1_CI_AS + ')' as UserName
from Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0 and
Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
IsNumeric(Substring(U.Name,2,5))=1
union all
select
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name] collate database_default
where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
Len(E.EmplID) in (5,6) and T.[ID] is not null
Order by UserNameWe are not really going to be able to tell why, unless we can see the view
statements, structure of base tables, query plans, etc. I do have a couple
of questions though... why all the collate clauses? Why not let the front
end deal with parentheses, concatenation, etc.? Why left join with
vwTeamPartners and then make it an inner join by including it in the where
clause? Why left join with vwTeamUsers and then make it an inner join by
including it in the where clause?
"Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>I have two queries joined with a union, one query by itself takes 34ms to
> complete and the other runs by itself in 340ms but when they are joined
> by
> the union (or a union all) the combined query takes an incredible one
> minute
> and 15 seconds. Why does the union com with such an incredible cost?
> The query is:
> select
> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
> from Team..Users U with (nolock)
> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
> and
> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
> IsNumeric(Substring(U.Name,2,5))=1
> union all
> select
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.Emplid
> else null
> end as UserID
> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
> from vwPS_Employees E with (nolock)
> left join vwTeamUsers T with (nolock) on
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.EmplID
> end = T.[Name] collate database_default
> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
> Len(E.EmplID) in (5,6) and T.[ID] is not null
> Order by UserName
>|||UNIONS and anything but Inner joins are always expensive.
There is alwasy a better way to do it, as long as you are using stored
procedures as the method of access.
If you are not, then you have bigger problems
The biggest issue is that both selects have to complete in entirity before
the union can begin.
Things I noticed about your Query:
Your Collates are in series in the same column of the select.
Only the last one would count, and it is the default for SQL.
They should be omitted.
The only time Collate is normally seen is when you have different
collations in the return from multiple linked servers.
Performance Hit 2 )
always specify the schema, Database..Table Only works if the only schema
is dbo.
It forces QA to check the sys.objects table for table ownership and
access
WAIT WAIT WAIT
Your using a case statment in a join '
Your Joining to Views, I bet they are well written as this one.
Did you put indexes on your views.
If we are Left joining P but P.userid can't be null, THAT's AN Inner
I understand now this is an example of how to get a 3 minute execution on
2 tables with 2 rows of data each.
Hire A DBA
SELECT
U.[Name] as UserID
, U.LastName + ', ' + U.FirstName + ' ' + U.MiddleName + ' (' +
P.PartnerName + ')' as UserName
FROM
Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
WHERE
P.UserID is not null
and Len(U.Name) = 6
and Len(U.FirstName) = 0
and Len(U.LastName)=0
and Lower(Substring(U.Name,1,1)) ='v'
and IsNumeric(Substring(U.Name,2,5))=1
UNION ALL
SELECT
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from
vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name]
where
E.Empl_Status in('A','P','L','S')
and E.DeptID < '000'
and Len(E.EmplID) in (5,6)
and T.[ID] is not null
Order by
UserName
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:5E67B352-323C-4433-8126-F7C3B4A5FC17@.microsoft.com...
> We are not really going to be able to tell why, unless we can see the view
> statements, structure of base tables, query plans, etc. I do have a
> couple of questions though... why all the collate clauses? Why not let
> the front end deal with parentheses, concatenation, etc.? Why left join
> with vwTeamPartners and then make it an inner join by including it in the
> where clause? Why left join with vwTeamUsers and then make it an inner
> join by including it in the where clause?
>
> "Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
> news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>>I have two queries joined with a union, one query by itself takes 34ms to
>> complete and the other runs by itself in 340ms but when they are joined
>> by
>> the union (or a union all) the combined query takes an incredible one
>> minute
>> and 15 seconds. Why does the union com with such an incredible cost?
>> The query is:
>> select
>> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
>> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
>> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
>> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
>> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
>> from Team..Users U with (nolock)
>> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
>> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
>> and
>> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
>> IsNumeric(Substring(U.Name,2,5))=1
>> union all
>> select
>> Case Len(E.EmplID)
>> When 6 then E.EmplID
>> When 5 then 'C' + E.Emplid
>> else null
>> end as UserID
>> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
>> from vwPS_Employees E with (nolock)
>> left join vwTeamUsers T with (nolock) on
>> Case Len(E.EmplID)
>> When 6 then E.EmplID
>> When 5 then 'C' + E.EmplID
>> end = T.[Name] collate database_default
>> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
>> Len(E.EmplID) in (5,6) and T.[ID] is not null
>> Order by UserName
>sql

Killer Union

I have two queries joined with a union, one query by itself takes 34ms to
complete and the other runs by itself in 340ms but when they are joined by
the union (or a union all) the combined query takes an incredible one minute
and 15 seconds. Why does the union com with such an incredible cost?
The query is:
select
U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
SQL_Latin1_General_CP1_CI_AS + ')' as UserName
from Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0 and
Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
IsNumeric(Substring(U.Name,2,5))=1
union all
select
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name] collate database_default
where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
Len(E.EmplID) in (5,6) and T.[ID] is not null
Order by UserName
We are not really going to be able to tell why, unless we can see the view
statements, structure of base tables, query plans, etc. I do have a couple
of questions though... why all the collate clauses? Why not let the front
end deal with parentheses, concatenation, etc.? Why left join with
vwTeamPartners and then make it an inner join by including it in the where
clause? Why left join with vwTeamUsers and then make it an inner join by
including it in the where clause?
"Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>I have two queries joined with a union, one query by itself takes 34ms to
> complete and the other runs by itself in 340ms but when they are joined
> by
> the union (or a union all) the combined query takes an incredible one
> minute
> and 15 seconds. Why does the union com with such an incredible cost?
> The query is:
> select
> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
> from Team..Users U with (nolock)
> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
> and
> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
> IsNumeric(Substring(U.Name,2,5))=1
> union all
> select
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.Emplid
> else null
> end as UserID
> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
> from vwPS_Employees E with (nolock)
> left join vwTeamUsers T with (nolock) on
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.EmplID
> end = T.[Name] collate database_default
> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
> Len(E.EmplID) in (5,6) and T.[ID] is not null
> Order by UserName
>
|||UNIONS and anything but Inner joins are always expensive.
There is alwasy a better way to do it, as long as you are using stored
procedures as the method of access.
If you are not, then you have bigger problems
The biggest issue is that both selects have to complete in entirity before
the union can begin.
Things I noticed about your Query:
Your Collates are in series in the same column of the select.
Only the last one would count, and it is the default for SQL.
They should be omitted.
The only time Collate is normally seen is when you have different
collations in the return from multiple linked servers.
Performance Hit 2 )
always specify the schema, Database..Table Only works if the only schema
is dbo.
It forces QA to check the sys.objects table for table ownership and
access
WAIT WAIT WAIT
Your using a case statment in a join ?
Your Joining to Views, I bet they are well written as this one.
Did you put indexes on your views.
If we are Left joining P but P.userid can't be null, THAT's AN Inner
I understand now this is an example of how to get a 3 minute execution on
2 tables with 2 rows of data each.
Hire A DBA
SELECT
U.[Name] as UserID
, U.LastName + ', ' + U.FirstName + ' ' + U.MiddleName + ' (' +
P.PartnerName + ')' as UserName
FROM
Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
WHERE
P.UserID is not null
and Len(U.Name) = 6
and Len(U.FirstName) = 0
and Len(U.LastName)=0
and Lower(Substring(U.Name,1,1)) ='v'
and IsNumeric(Substring(U.Name,2,5))=1
UNION ALL
SELECT
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from
vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name]
where
E.Empl_Status in('A','P','L','S')
and E.DeptID < '000'
and Len(E.EmplID) in (5,6)
and T.[ID] is not null
Order by
UserName
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:5E67B352-323C-4433-8126-F7C3B4A5FC17@.microsoft.com...
> We are not really going to be able to tell why, unless we can see the view
> statements, structure of base tables, query plans, etc. I do have a
> couple of questions though... why all the collate clauses? Why not let
> the front end deal with parentheses, concatenation, etc.? Why left join
> with vwTeamPartners and then make it an inner join by including it in the
> where clause? Why left join with vwTeamUsers and then make it an inner
> join by including it in the where clause?
>
> "Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
> news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>

killed/rollback stuck on object_name(99)

Hello:
I have a process that's been stuck for two days.. It's a stored procedure
that runs as part of a scheduled SqlAgent job. I tried to kill the process
which put it into a rollback. Kill with statusonly returns:
SPID 52: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
I ran dbcc page for the resource that is listed in the wait type
(PAGEIOLATCH_UP)
and it points to Obj_id 99. Running "select object_name(99)" returns the
object name "Allocation".
Does anyone know what this means and how to allow the rollback to complete?
This spid blocks other processess that try to run in the affected database.
Even Enterprise Manager is blocked. Can't refresh table list or procedure
list in EM. Current activity times out. I can use sp_who2 to see active
processes.hi,
Right, try again using this:
KILL <your_process> WITH STATUSONLY
Because of your process has been running a long time the rollback will take
a lot of time (not the same, of course, but a lof anyway)
Rollback is undoing changes and transactions commited
Current location: Alicante (ES)
"tthrone" wrote:

> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the proces
s
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to complete
?
> This spid blocks other processess that try to run in the affected database
.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Hi Enric,
I did that. It returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
>
It's been returning the same thing for two days. It doesn't appear to be
making any progress on the rollback. The original process should have done
123,000 row inserts on a previously empty table. I can't imagine 123k rows
should take 2 days to rollback. I think it's totally stuck and idle.
"Enric" wrote:
> hi,
> Right, try again using this:
> KILL <your_process> WITH STATUSONLY
> Because of your process has been running a long time the rollback will tak
e
> a lot of time (not the same, of course, but a lof anyway)
> Rollback is undoing changes and transactions commited
> --
> Current location: Alicante (ES)
>
> "tthrone" wrote:
>|||First, try to find out what application and T-SQL statement caused this
situation so perhaps it won't repeat:
DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
client application owning this SPID.
SP_LOCK (spid) will list information about what specific objects the SPID
currently has locked and what type of lock (table, page, etc.).
Next, try to diagnose what is going on with your server hard disks, memory,
etc. that may have caused this unusual cirsumstance. If the server is
running critically low on disk space, this can cause problems when
attempting rollback a large transaction. Also, go into the windows
management console and review the event logs for possible evidence.
This article describes how get more detailed information about the current
status of the SPID:
http://support.microsoft.com/defaul...kb;en-us;171224
For example, the Process Status Structure (PSS) has the following values:
0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
0x2000 -- Process is being killed
0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
0x400 -- Process has received an ATTENTION signal, and has responded by
raising an internal exception
0x100 -- Process in the middle of a single statement transaction
0x80 -- Process is involved in multi-database transaction
0x8 -- Process is currently executing a trigger
0x2 -- Process has received KILL command
0x1 -- Process has received an ATTENTION signal
This article describes how to identify and troubleshoot an orphaned
connection:
http://support.microsoft.com/kb/137983/EN-US/
If the SPID can't be killed, then:
1. stop the SQL Server service (no need to reboot)
2. using Windows Explorer, move the data and transaction log file(s) to
another location
3. re-start the service
4. restore the database from the most recent backup
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the
> process
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to
> complete?
> This spid blocks other processess that try to run in the affected
> database.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Thanks JT. I know some answers to questions/issues you listed. I know the
transation that was in-flight, but I don't know why it stuck. Still can't
figure out why it remains stuck, but I found something interesting in the
process of doing some of what you suggested.
For one, I see this spid blocks some of my attempts to use sysobjects. I
mentioned that I can't refresh procedures or tables in EM on this database.
I think that's why. I have it narrowed down to one (maybe a few) affected
tables. I can query sysobjects so long as I don't try to read certain rows.
Not sure how that happened!
I think I'm going to have to try your suggestion about stopping the service
and restoring.
Thanks for the help.
"JT" wrote:

> First, try to find out what application and T-SQL statement caused this
> situation so perhaps it won't repeat:
> DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
> client application owning this SPID.
> SP_LOCK (spid) will list information about what specific objects the SPID
> currently has locked and what type of lock (table, page, etc.).
> Next, try to diagnose what is going on with your server hard disks, memory
,
> etc. that may have caused this unusual cirsumstance. If the server is
> running critically low on disk space, this can cause problems when
> attempting rollback a large transaction. Also, go into the windows
> management console and review the event logs for possible evidence.
> This article describes how get more detailed information about the current
> status of the SPID:
> http://support.microsoft.com/defaul...kb;en-us;171224
> For example, the Process Status Structure (PSS) has the following values:
> 0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
> 0x2000 -- Process is being killed
> 0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
> 0x400 -- Process has received an ATTENTION signal, and has responded by
> raising an internal exception
> 0x100 -- Process in the middle of a single statement transaction
> 0x80 -- Process is involved in multi-database transaction
> 0x8 -- Process is currently executing a trigger
> 0x2 -- Process has received KILL command
> 0x1 -- Process has received an ATTENTION signal
> This article describes how to identify and troubleshoot an orphaned
> connection:
> http://support.microsoft.com/kb/137983/EN-US/
> If the SPID can't be killed, then:
> 1. stop the SQL Server service (no need to reboot)
> 2. using Windows Explorer, move the data and transaction log file(s) to
> another location
> 3. re-start the service
> 4. restore the database from the most recent backup
>
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
>
>|||When querying sysobjects (or any other blocked table), you can get around
the locks by changing the isolation level to read uncommitted data. However,
this should not be used in a production system except perhaps in some
reporting situations.
set transaction isolation level read uncommitted
select * from sysobjects
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
> Thanks JT. I know some answers to questions/issues you listed. I know
> the
> transation that was in-flight, but I don't know why it stuck. Still can't
> figure out why it remains stuck, but I found something interesting in the
> process of doing some of what you suggested.
> For one, I see this spid blocks some of my attempts to use sysobjects. I
> mentioned that I can't refresh procedures or tables in EM on this
> database.
> I think that's why. I have it narrowed down to one (maybe a few) affected
> tables. I can query sysobjects so long as I don't try to read certain
> rows.
> Not sure how that happened!
> I think I'm going to have to try your suggestion about stopping the
> service
> and restoring.
> Thanks for the help.
> "JT" wrote:
>|||I normally do set the transaction isolation level to read uncommitted. I di
d
that in this case as well.
I even tried using the hint "with(readuncommitted)" but it was still blocked
by the stuck spid when I tried to return the sysobject rows of tables that
were affected.
Our DBA is going to bounce the service later today. I'm hoping it will
clear up after the restart.
"JT" wrote:

> When querying sysobjects (or any other blocked table), you can get around
> the locks by changing the isolation level to read uncommitted data. Howeve
r,
> this should not be used in a production system except perhaps in some
> reporting situations.
> set transaction isolation level read uncommitted
> select * from sysobjects
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
>
>

Kill cmd process started from SQL Server Agent

Hi!
I have a small problem , but it's still a problem.
I have a SQL Server Agent job that runs a .cmd file. This CMD is logged to a textfile.
This process is locked, waiting for me to type a password, but I have nowhere to type that pass.

What I want to do is kill the process that i locking the logfile, because since the logfile is locked, the job cannot be started again (and it's a scheduled job).
The jobs status is 'Not Running'.
I have solved the problem by making the cmd write to another logfile, so the schedule will work, but the file is still locked, and I don't want to restart the server since it's a productionserver.

How to I find the process that is initialized from SQL Agent, and kill it?

Thanks!

BixCould be you'll have a big problem...

what does the cmd file execute?

If it's ANY type of GUI you could hang the box...

What's in the cmd file?|||Nope, no GUI is executed.
It's juat a matter of reading textfiles, formatting the data and inserting it into the db.

The part that hangs is a "Net Use"-command for accessing a networkshare.
I have added the user and pass so that this does not happen again...|||I'm not sure about this, but I believe you will find cmdexec in your system processes. You can kill the PID and it should take care of you.|||killing the parent process without terminating the child may lead to system instability. cmdexec does not take care of anything that had been invoked from it.

Kicking off a job on another SQL server

I have a job that runs daily that creates a backup and copies that backup
off to a second server. I currently have another job running on the second
server that restores that database locally on that server. I gave the job
on server 2 enough time where job 1 on server one will have enough time to
finish before job 2 starts. That being said, sometimes, job 1 runs kind of
close. I am not sure if this is possible, but I would like to create a step
in job 1 that would kick off job 2 on server 2. Any ideas if this can be
done?
AaronHi,
There is a command in Windows Resource Kit "RCMD" , which is used to execute
jobs / executables / batch files remotely.
Thanks
Hari
MCDBA
"Aaron" <amhigley@.hotmail.com> wrote in message
news:#05tLnRwDHA.1908@.TK2MSFTNGP10.phx.gbl...
> I have a job that runs daily that creates a backup and copies that backup
> off to a second server. I currently have another job running on the
second
> server that restores that database locally on that server. I gave the job
> on server 2 enough time where job 1 on server one will have enough time to
> finish before job 2 starts. That being said, sometimes, job 1 runs kind
of
> close. I am not sure if this is possible, but I would like to create a
step
> in job 1 that would kick off job 2 on server 2. Any ideas if this can be
> done?
> Aaron
>|||If you create a linked server, you could
EXEC linkedServerName.msdb..sp_start_job 'job_name'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Aaron" <amhigley@.hotmail.com> wrote in message
news:#05tLnRwDHA.1908@.TK2MSFTNGP10.phx.gbl...
> I have a job that runs daily that creates a backup and copies that backup
> off to a second server. I currently have another job running on the
second
> server that restores that database locally on that server. I gave the job
> on server 2 enough time where job 1 on server one will have enough time to
> finish before job 2 starts. That being said, sometimes, job 1 runs kind
of
> close. I am not sure if this is possible, but I would like to create a
step
> in job 1 that would kick off job 2 on server 2. Any ideas if this can be
> done?
> Aaron
>

Kerberos

Hi
Want to know how to get kerberos to work
3 workstations involved
User computer with Windows 2000 runs I.E 6.0 and accesses a web page on
Reporting services server (Web Portion-IIS) running windows 2003.
USer is running a report that runs an mdx statement on a data server which
is also windows 2003
We included in the connection string sspi=kerberos
We also when to the domain controller which is windows 2003 and enabled
kerberos delegation on the web and data server.
We still get the below error
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
a.. Cannot create a connection to data source 'OLAP'.
(rsErrorOpeningConnection) Get Online Help
a.. The operation requested failed due to security problems - the user
could not be authenticated
b.. Thanks in advanceYou may want to try posting this to the
microsoft.public.windows.server.security newsgroup as well.
Jay Nathan
http://www.jaynathan.com/blog
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:u45ZipE5EHA.2124@.TK2MSFTNGP15.phx.gbl...
> Hi
> Want to know how to get kerberos to work
> 3 workstations involved
> User computer with Windows 2000 runs I.E 6.0 and accesses a web page on
> Reporting services server (Web Portion-IIS) running windows 2003.
> USer is running a report that runs an mdx statement on a data server which
> is also windows 2003
> We included in the connection string sspi=kerberos
> We also when to the domain controller which is windows 2003 and enabled
> kerberos delegation on the web and data server.
> We still get the below error
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> a.. Cannot create a connection to data source 'OLAP'.
> (rsErrorOpeningConnection) Get Online Help
> a.. The operation requested failed due to security problems - the user
> could not be authenticated
> b.. Thanks in advance
>
>|||I got Kerberos to work by using the artricle at
http://support.microsoft.com/default.aspx?kbid=828280
however what I ultimately got tied up with is the olap service on the
analysis service machine was using a domain account which I assumed it could
since the above article talks about setting up an SPN if the analysis
service service runs using a domain account.
I ran into another article
http://www.mosha.com/msolap/articles/enablingdelegation.htm
which stated
The MSSQLServerOLAPService must be running under the LocalSystem account in
order for delegation to be enabled.
Once I changed to run under local system the kerberos feature worked.
"Jay Nathan" <jay@.jaynathan.com> wrote in message
news:%23WurCfU5EHA.2124@.TK2MSFTNGP15.phx.gbl...
> You may want to try posting this to the
> microsoft.public.windows.server.security newsgroup as well.
> Jay Nathan
> http://www.jaynathan.com/blog
>
> "Will Byron" <will.byron@.maxqtech.com> wrote in message
> news:u45ZipE5EHA.2124@.TK2MSFTNGP15.phx.gbl...
> > Hi
> > Want to know how to get kerberos to work
> > 3 workstations involved
> > User computer with Windows 2000 runs I.E 6.0 and accesses a web page on
> > Reporting services server (Web Portion-IIS) running windows 2003.
> > USer is running a report that runs an mdx statement on a data server
which
> > is also windows 2003
> > We included in the connection string sspi=kerberos
> > We also when to the domain controller which is windows 2003 and enabled
> > kerberos delegation on the web and data server.
> >
> > We still get the below error
> >
> > An error has occurred during report processing. (rsProcessingAborted)
Get
> > Online Help
> > a.. Cannot create a connection to data source 'OLAP'.
> > (rsErrorOpeningConnection) Get Online Help
> > a.. The operation requested failed due to security problems - the
user
> > could not be authenticated
> > b.. Thanks in advance
> >
> >
> >
>

kerberos

Hi
Want to know how to get kerberos to work
3 workstations involved
User computer with Windows 2000 runs I.E 6.0 and accesses a web page on
Reporting services server (Web Portion-IIS) running windows 2003.
USer is running a report that runs an mdx statement on a data server which
is also windows 2003
We included in the connection string sspi=kerberos
We also when to the domain controller which is windows 2003 and enabled
kerberos delegation on the web and data server.
We still get the below error
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
a.. Cannot create a connection to data source 'OLAP'.
(rsErrorOpeningConnection) Get Online Help
a.. The operation requested failed due to security problems - the user
could not be authenticated
b.. Thanks in advanceHi Will:
There are some Kerberos troubleshooting tips in the following doc:
HOW TO: Troubleshoot Kerberos-Related Issues in IIS
http://support.microsoft.com/default.aspx?kbid=326985
HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 29 Oct 2004 15:52:43 -0400, "Will Byron"
<will.byron@.maxqtech.com> wrote:
>Hi
>Want to know how to get kerberos to work
>3 workstations involved
>User computer with Windows 2000 runs I.E 6.0 and accesses a web page on
>Reporting services server (Web Portion-IIS) running windows 2003.
>USer is running a report that runs an mdx statement on a data server which
>is also windows 2003
>We included in the connection string sspi=kerberos
>We also when to the domain controller which is windows 2003 and enabled
>kerberos delegation on the web and data server.
>We still get the below error
>An error has occurred during report processing. (rsProcessingAborted) Get
>Online Help
> a.. Cannot create a connection to data source 'OLAP'.
>(rsErrorOpeningConnection) Get Online Help
> a.. The operation requested failed due to security problems - the user
>could not be authenticated
> b.. Thanks in advance
>