SQL tool for MSDE: call

Showing posts with label call. Show all posts

Sunday, March 11, 2012

Aggregation against multiple date fields

I have a single table which records calls made to a helpdesk.
Each call has a single row in the table, and there are three relevant date fields recorded: occurrence date, reported date, and resolution date.

So it looks a little like this:

OccDate | RepDate| ResDate | CallRef
21/3/05 |22/3/05 | 01/4/05 | PMR001
22/3/05 | 1/4/05 | 26/5/05 | PMR002
01/4/05 | 2/4/05 | 3/6/05 | PMR003
01/5/05 | 2/5/05 | <Null> | PMR004

Now what I want to do is to create a single SQL statement which will allow me to summarise in each month how many calls occurred, were reported, and resolved, so for this data it looks like this:

Month | Occ. | Rep. | Res.
Mar-05 | 2 | 1 | 0
Apr-05 | 1 | 2 | 1
May-05 | 1 | 1 | 1
Jun-05 | 0 | 0 | 1

I can get each column individually very easily by simply doing:
COUNT(CallRef) GROUP BY (OccDate)
COUNT(CallRef) GROUP BY (RepDate)
COUNT(CallRef) GROUP BY (ResDate)
..but I'm getting in a right pickle when trying to merge these into a single statement.

To start with, I'm guessing that I need to do 2 self-joins so that all three dates are linked to each other.
Then I tried using CASE and COALESCE statements to do conditional aggregations, but that got me nowhere...

I'd be grateful for any help!I tested it on MS SQL Server:

create table t (OccDate datetime, RepDate datetime, ResDate datetime, callRef varchar(10))

insert into t values( cast('3/21/05' AS DATETIME) , cast('3/22/05' as DATETIME), cast('4/1/05' as datetime), 'PMR001')
insert into t values( cast('3/22/05' AS DATETIME) , cast('4/1/05' as DATETIME), cast('5/26/05' as datetime), 'PMR002')
insert into t values( cast('4/1/05' AS DATETIME) , cast('4/2/05' as DATETIME), cast('6/3/05' as datetime), 'PMR003')
insert into t values( cast('5/1/05' AS DATETIME) , cast('5/2/05' as DATETIME), NULL, 'PMR004')

select
mont,
(select count(*) from t t3 where cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) = t2.mont) as Occ,
(select count(*) from t t3 where cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) = t2.mont) as Rep,
(select count(*) from t t3 where cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) = t2.mont) as Res
from
(
select distinct mon as Mont from
(
select cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) as mon from t where OccDate is not null
union all
select cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) as mon from t where RepDate is not null
union all
select cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) as mon from t where ResDate is not null
) t1
) t2

Month Occ Rep Res
-------------
Apr-2005 1 2 1
Jun-2005 0 0 1
Mar-2005 2 1 0
May-2005 1 1 1

to run this on different DB server all you need is replace
cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4))
with other functions which returns
'Month-Year'|||Thankyou so much for the helpful reply - you're a star!|||An other solution is the following:create table t (OccDate date, RepDate date, ResDate date, callRef varchar(10)) ;
insert into t values( '03/21/2005', '03/22/2005', '04/01/2005', 'PMR001') ;
insert into t values( '03/22/2005', '04/01/2005', '05/26/2005', 'PMR002') ;
insert into t values( '04/01/2005', '04/02/2005', '06/03/2005', 'PMR003') ;
insert into t values( '05/01/2005', '05/02/2005', NULL, 'PMR004') ;
SELECT coalesce(m1,m2,m3) AS "month",
coalesce(Occ,0), coalesce(Rep,0), coalesce(Res,0)
FROM ( SELECT m1, COUNT(*) AS Occ
FROM ( SELECT substr(char(OccDate,ISO),1,7) AS m1
FROM t ) AS x1
GROUP BY m1 ) AS y1
FULL OUTER JOIN
( SELECT m2, COUNT(*) AS Rep
FROM ( SELECT substr(char(RepDate,ISO),1,7) AS m2
FROM t ) AS x2
GROUP BY m2 ) AS y2
ON m1 = m2
FULL OUTER JOIN
( SELECT m3, COUNT(*) AS Res
FROM ( SELECT substr(char(ResDate,ISO),1,7) AS m3
FROM t ) AS x3
GROUP BY m3 ) AS y3
ON m2 = m3
ORDER BY 1It has the slight advantage that it works with DB2 V7, and that it might be a bit faster (but I did not verify that ;) )

aggregates in stored procedure

I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is on
e
to many with [Call Counts].
I need to update [Versions].[Number Months Reported] with the highest value
in [Call Counts].[Month Number] (technically, the highest value – 3), but
when I write a stored procedure to do this I get an error saying the
procedure is not updateable because the underlying query contains aggregates
.
Is there a way around this?
Here's the relevant table structure:
################################
CREATE TABLE [Versions] (
[Version ID] [int] IDENTITY (1, 1) NOT NULL ,
[Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
[Version Type] [smallint] NULL ,
[Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
Reported] DEFAULT (0),
(
[Version ID]
) ON [PRIMARY] ,
CONSTRAINT [Versions_FK00] FOREIGN KEY
(
[Test ID]
) REFERENCES [Tests] (
[Test ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
################################
CREATE TABLE [RCF Numbers] (
[RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
[Version ID] [int] NULL ,
[RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
(
[RCF Number ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
(
[Version ID]
) REFERENCES [Versions] (
[Version ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
################################
CREATE TABLE [Call Counts] (
[Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
[RCF Number ID] [int] NULL ,
[Month Number] [smallint] NULL ,
CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
(
[Call Count ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
(
[RCF Number ID]
) REFERENCES [RCF Numbers] (
[RCF Number ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
################################
Here's a view to query the correct value:
CREATE VIEW dbo.vwCalcMonthsReported
AS
SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
FROM dbo.Versions INNER JOIN
dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
dbo.[RCF Numbers].[Version ID] INNER JOIN
dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number ID]
= dbo.[Call Counts].[RCF Number ID]
GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version I
D]
HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
GO
################################
Here's my stored procedure:
CREATE PROCEDURE dbo.sp_Update_Months_Reported
AS UPDATE dbo.vwCalcMonthsReported
SET [Number Months Reported] = [Months of Data] - 3
GO
#####################Hi Mike,
You could consider an Instead Of trigger on the view for your updates.
http://msdn.microsoft.com/library/d...>
nsteadof.asp
Cheers,
Steve Goodyear
Vancouver, BC
"mike" wrote:

> I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
> is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is
one
> to many with [Call Counts].
> I need to update [Versions].[Number Months Reported] with the highest value
> in [Call Counts].[Month Number] (technically, the highest value – 3), bu
t
> when I write a stored procedure to do this I get an error saying the
> procedure is not updateable because the underlying query contains aggregat
es.
> Is there a way around this?
> Here's the relevant table structure:
> ################################
> CREATE TABLE [Versions] (
> [Version ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
> [Version Type] [smallint] NULL ,
> [Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
> Reported] DEFAULT (0),
> (
> [Version ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [Versions_FK00] FOREIGN KEY
> (
> [Test ID]
> ) REFERENCES [Tests] (
> [Test ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [RCF Numbers] (
> [RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Version ID] [int] NULL ,
> [RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
> (
> [RCF Number ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
> (
> [Version ID]
> ) REFERENCES [Versions] (
> [Version ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [Call Counts] (
> [Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
> [RCF Number ID] [int] NULL ,
> [Month Number] [smallint] NULL ,
> CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
> (
> [Call Count ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
> (
> [RCF Number ID]
> ) REFERENCES [RCF Numbers] (
> [RCF Number ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> Here's a view to query the correct value:
> CREATE VIEW dbo.vwCalcMonthsReported
> AS
> SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
> dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
> FROM dbo.Versions INNER JOIN
> dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
> dbo.[RCF Numbers].[Version ID] INNER JOIN
> dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number I
D]
> = dbo.[Call Counts].[RCF Number ID]
> GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version
ID]
> HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
> GO
> ################################
> Here's my stored procedure:
> CREATE PROCEDURE dbo.sp_Update_Months_Reported
> AS UPDATE dbo.vwCalcMonthsReported
> SET [Number Months Reported] = [Months of Data] - 3
> GO
> #####################
>|||Your view has aggregate funtions in select list. So you can not update it
with stored procedure. You can create INSTEAD OF triggers.
"mike" wrote:

> I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
> is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is
one
> to many with [Call Counts].
> I need to update [Versions].[Number Months Reported] with the highest value
> in [Call Counts].[Month Number] (technically, the highest value – 3), bu
t
> when I write a stored procedure to do this I get an error saying the
> procedure is not updateable because the underlying query contains aggregat
es.
> Is there a way around this?
> Here's the relevant table structure:
> ################################
> CREATE TABLE [Versions] (
> [Version ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
> [Version Type] [smallint] NULL ,
> [Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
> Reported] DEFAULT (0),
> (
> [Version ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [Versions_FK00] FOREIGN KEY
> (
> [Test ID]
> ) REFERENCES [Tests] (
> [Test ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [RCF Numbers] (
> [RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Version ID] [int] NULL ,
> [RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
> (
> [RCF Number ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
> (
> [Version ID]
> ) REFERENCES [Versions] (
> [Version ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [Call Counts] (
> [Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
> [RCF Number ID] [int] NULL ,
> [Month Number] [smallint] NULL ,
> CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
> (
> [Call Count ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
> (
> [RCF Number ID]
> ) REFERENCES [RCF Numbers] (
> [RCF Number ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> Here's a view to query the correct value:
> CREATE VIEW dbo.vwCalcMonthsReported
> AS
> SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
> dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
> FROM dbo.Versions INNER JOIN
> dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
> dbo.[RCF Numbers].[Version ID] INNER JOIN
> dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number I
D]
> = dbo.[Call Counts].[RCF Number ID]
> GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version
ID]
> HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
> GO
> ################################
> Here's my stored procedure:
> CREATE PROCEDURE dbo.sp_Update_Months_Reported
> AS UPDATE dbo.vwCalcMonthsReported
> SET [Number Months Reported] = [Months of Data] - 3
> GO
> #####################
>|||On Thu, 7 Apr 2005 08:37:03 -0700, mike wrote:

>I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
>is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is o
ne
>to many with [Call Counts].
>I need to update [Versions].[Number Months Reported] with the highest value
>in [Call Counts].[Month Number] (technically, the highest value 3), but
>when I write a stored procedure to do this I get an error saying the
>procedure is not updateable because the underlying query contains aggregate
s.
>Is there a way around this?
Hi Mike,
It's hard to say if this will work without having any sample data to
test it on (see www.aspfaq.com/5006), but you might try if this update
statement does the trick:
UPDATE dbo.Versions
SET [Number Months Reported] =
(SELECT MAX(dbo.[Call Counts].[Month Number])
FROM dbo.[RCF Numbers] AS rn
INNER JOIN dbo.[Call Counts] AS cc
ON cc.[RCF Number ID] = rn.[RCF Number ID]
WHERE rn.[Version ID] = dbo.Versions.[Version ID]) - 3
WHERE (SELECT MAX(dbo.[Call Counts].[Month Number])
FROM dbo.[RCF Numbers] AS rn
INNER JOIN dbo.[Call Counts] AS cc
ON cc.[RCF Number ID] = rn.[RCF Number ID]
WHERE rn.[Version ID] = dbo.Versions.[Version ID]) > 3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo. I needed to tweak it a bit, but your sp works great!
"Hugo Kornelis" wrote:

> On Thu, 7 Apr 2005 08:37:03 -0700, mike wrote:
>
> Hi Mike,
> It's hard to say if this will work without having any sample data to
> test it on (see www.aspfaq.com/5006), but you might try if this update
> statement does the trick:
> UPDATE dbo.Versions
> SET [Number Months Reported] =
> (SELECT MAX(dbo.[Call Counts].[Month Number])
> FROM dbo.[RCF Numbers] AS rn
> INNER JOIN dbo.[Call Counts] AS cc
> ON cc.[RCF Number ID] = rn.[RCF Number ID]
> WHERE rn.[Version ID] = dbo.Versions.[Version ID]) - 3
> WHERE (SELECT MAX(dbo.[Call Counts].[Month Number])
> FROM dbo.[RCF Numbers] AS rn
> INNER JOIN dbo.[Call Counts] AS cc
> ON cc.[RCF Number ID] = rn.[RCF Number ID]
> WHERE rn.[Version ID] = dbo.Versions.[Version ID]) > 3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Friday, February 24, 2012

AFTER TRIGGER

I would like to create a trigger that after a DML event, let's say an
INSERT, will call an external exe file using xp_cmdshell and pass to the exe
the @.@.identity which resulted by the INSERT.
Is there a way to achieve this?Yan,
Doing this will compromise the transaction (i.e. if the external program
hang up, then the transaction will remain open for a long time). Also take i
n
mind that the statement that activated the trigger could have affected
multiple rows. May be if you tell the group what are you trying to
accomplish, somebody can come with a better approach.
AMB
"Yan" wrote:

> I would like to create a trigger that after a DML event, let's say an
> INSERT, will call an external exe file using xp_cmdshell and pass to the e
xe
> the @.@.identity which resulted by the INSERT.
> Is there a way to achieve this?
>
>|||Hi Yan
This trigger runs for me. "Kuku" is a table with an IDENTITY column. I hope
it helps.
CREATE TRIGGER TRG_KUKU_ON_INSERT ON [dbo].[Kuku]
FOR INSERT
AS
DECLARE @.idd int
DECLARE @.cmd NVARCHAR(100)
SET @.idd = @.@.identity
SET @.cmd = 'CommandToExecute ' + CAST(@.idd AS NVARCHAR)
exec master..xp_cmdshell @.cmd
Boaz Ben-Porat
Milestone Systems
Denmark
"Yan" <yanive@.rediffmail.com> wrote in message
news:OSQOgBTmGHA.2372@.TK2MSFTNGP04.phx.gbl...
>I would like to create a trigger that after a DML event, let's say an
>INSERT, will call an external exe file using xp_cmdshell and pass to the
>exe the @.@.identity which resulted by the INSERT.
> Is there a way to achieve this?
>|||Yan wrote:
> I would like to create a trigger that after a DML event, let's say an
> INSERT, will call an external exe file using xp_cmdshell and pass to the e
xe
> the @.@.identity which resulted by the INSERT.
> Is there a way to achieve this?
>
I would take a different approach, for a couple of reasons:
1. An insert doesn't always involve a single record, multiple records
can be inserted at once, thus you won't have a single ID to work with.
2. Involving an external process in a transaction is introducing a
point of failure. If the external process fails, or performs poorly,
your transaction will as will, causing the insert to fail or perform poorly.
I would instead write the trigger to insert all new ID's into a
"staging" table, and create a scheduled job that monitors that staging
table for new records. When new records are found, then run your
external process.|||Thank you Tracy for this post and I thank all other answeres as well.
I have some logic in my trigger to ensure we work just on a single record as
we expect.
I adopt your approach to have the trigger insert the Ids to another table on
which we can make what we need and not in the context of a transaction
(inside the trigger).
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uV6ShYTmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Yan wrote:
> I would take a different approach, for a couple of reasons:
> 1. An insert doesn't always involve a single record, multiple records can
> be inserted at once, thus you won't have a single ID to work with.
> 2. Involving an external process in a transaction is introducing a point
> of failure. If the external process fails, or performs poorly, your
> transaction will as will, causing the insert to fail or perform poorly.
> I would instead write the trigger to insert all new ID's into a "staging"
> table, and create a scheduled job that monitors that staging table for new
> records. When new records are found, then run your external process.

Sunday, February 19, 2012

After server rename, xp_cmdshell problem

I have a job which runs a SP containing a xp_cmdshell call to
wzunzip.exe to unzip a file. This has worked for over a year without
a glitch. I recently renamed the server, following the proper steps I
think, and now this SP hangs at the unzip command. I can't even
cancel the job, when I try I see that the server is doing a rollback,
but it never finishes, and I have no idea what it would be rolling
back. I end up stopping the agent and restarting. I am probably
missing something easy but I can't figure it out. This same script
has always worked and continues to work on other servers, the only
change being the server rename. Any ideas on what I am missing?
Thanks,
CharlesAre you using the command-line versions of winzip (separate download)?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Sands" <z666z@.yahoo.com> wrote in message news:1f9df651.0408140754.3047b6a@.posting.google.com...
> I have a job which runs a SP containing a xp_cmdshell call to
> wzunzip.exe to unzip a file. This has worked for over a year without
> a glitch. I recently renamed the server, following the proper steps I
> think, and now this SP hangs at the unzip command. I can't even
> cancel the job, when I try I see that the server is doing a rollback,
> but it never finishes, and I have no idea what it would be rolling
> back. I end up stopping the agent and restarting. I am probably
> missing something easy but I can't figure it out. This same script
> has always worked and continues to work on other servers, the only
> change being the server rename. Any ideas on what I am missing?
> Thanks,
> Charles|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<O2t6qnigEHA.2928@.TK2MSFTNGP10.phx.gbl>...
> Are you using the command-line versions of winzip (separate download)?
Yes I am using the command-line version. I just uninstalled and
reinstalled winzip and the command-line add-on. It is working fine
from the command line. But when I try to run it from QA it just
hangs, no indication of anything. I can see from taskmanager that
wzunzip.exe is running, but nothing happens.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Charles Sands" <z666z@.yahoo.com> wrote in message news:1f9df651.0408140754.3047b6a@.posting.google.com...
> > I have a job which runs a SP containing a xp_cmdshell call to
> > wzunzip.exe to unzip a file. This has worked for over a year without
> > a glitch. I recently renamed the server, following the proper steps I
> > think, and now this SP hangs at the unzip command. I can't even
> > cancel the job, when I try I see that the server is doing a rollback,
> > but it never finishes, and I have no idea what it would be rolling
> > back. I end up stopping the agent and restarting. I am probably
> > missing something easy but I can't figure it out. This same script
> > has always worked and continues to work on other servers, the only
> > change being the server rename. Any ideas on what I am missing?
> >
> > Thanks,
> > Charles|||Perhaps it is waiting form some type of input? I believe that there are silent switches and stuff for the
command-line versions. Might be worth trying out? I haven't unzipped from xp_cmdshell, but I have zipped...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Sands" <z666z@.yahoo.com> wrote in message news:1f9df651.0408151256.29d1aded@.posting.google.com...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:<O2t6qnigEHA.2928@.TK2MSFTNGP10.phx.gbl>...
> > Are you using the command-line versions of winzip (separate download)?
> Yes I am using the command-line version. I just uninstalled and
> reinstalled winzip and the command-line add-on. It is working fine
> from the command line. But when I try to run it from QA it just
> hangs, no indication of anything. I can see from taskmanager that
> wzunzip.exe is running, but nothing happens.
>
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Charles Sands" <z666z@.yahoo.com> wrote in message news:1f9df651.0408140754.3047b6a@.posting.google.com...
> > > I have a job which runs a SP containing a xp_cmdshell call to
> > > wzunzip.exe to unzip a file. This has worked for over a year without
> > > a glitch. I recently renamed the server, following the proper steps I
> > > think, and now this SP hangs at the unzip command. I can't even
> > > cancel the job, when I try I see that the server is doing a rollback,
> > > but it never finishes, and I have no idea what it would be rolling
> > > back. I end up stopping the agent and restarting. I am probably
> > > missing something easy but I can't figure it out. This same script
> > > has always worked and continues to work on other servers, the only
> > > change being the server rename. Any ideas on what I am missing?
> > >
> > > Thanks,
> > > Charles

Sunday, March 11, 2012

Aggregation against multiple date fields

aggregates in stored procedure

Friday, February 24, 2012

AFTER TRIGGER

Sunday, February 19, 2012

After server rename, xp_cmdshell problem

After server rename, xp_cmdshell problem

After server rename, xp_cmdshell problem

SQL tool for MSDE

Blog Archive

About Me