sql report manager: heck

Showing posts with label heck. Show all posts

Sunday, March 11, 2012

can't return zero when no rows exist using aggregate function sum

Hi,
I'm having a heck of a time trying to get SQL Server 2000 to return a
zero (0) if no rows are returned in a sum() statement. I know in
Oracle you wrap an NVL around the whole shebang, and in Googling it
seems as if SQL Server should work this way, too. But I cannot get it
to work, I just keep getting null.
I have tried:
what I thought would work:
SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
a case statement testing for null:
SELECT CASE
WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
ELSE SUM(ISNULL(PointValue, 0))
END
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
wrapping the whole select statement in ISNULL as a subquery:
SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
and then to make sure what I was getting back was null and not empty
...something:
declare
@.n_sum numeric(6, 2)
SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
sum is: 0.00
What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
etc...but no go. I guess I could get around by using the variable and
then returning that to the VB (since this is a stored proc used to find
one value to return to the VB6 program), but that seems kind of messy.
I would appreciate any help!
TIA
PatCould you provide actual specs and desired results?
http://www.aspfaq.com/5006

> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement.|||I need to say this - I inherited this code and database structure!!!!
CREATE TABLE [tbl_Attendance_Event] (
[EventCode] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[File #] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Event Type] [numeric](9, 0) NULL ,
[EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Excused] [bit] NULL ,
[EventDate] [smalldatetime] NULL ,
[EventDuration] [money] NULL ,
[PointValue] [money] NULL ,
[ConsecDayCount] [numeric](9, 0) NULL ,
[Consecutive_Days] [bit] NULL ,
[EventRemove] [bit] NULL ,
[EventRemoveDate] [datetime] NULL ,
CONSTRAINT [PK_tbl_Attendance_Event] PRIMARY KEY NONCLUSTERED
(
[EventCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Attendance_Event_tbl_Event_Types
] FOREIGN KEY
(
[Event Type]
) REFERENCES [tbl_Event_Types] (
[EventID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tbl_Event_Types] (
[EventID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[EventDescription] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EventPoints] [smallmoney] NULL ,
[ExcepID] [numeric](9, 0) NULL ,
[TardyLogic] [bit] NULL ,
[Minimum Minutes] [numeric](9, 0) NULL ,
[Maximum Minutes] [numeric](9, 0) NULL ,
[ExtendsYearWindow] [bit] NULL CONSTRAINT
[DF_tbl_Event_Types_ExtendsYearWindow] DEFAULT (0),
[RuinsPerfectMonthlyAttendace] [bit] NOT NULL CONSTRAINT
[DF_tbl_Event_Types_RuinsPerfectMonthlyA
ttendace] DEFAULT (0),
[LogEvent] [bit] NULL ,
[PayCodeEvent] [bit] NULL ,
[Paycode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Consecutive_Days] [bit] NULL ,
[AddToDropOffPeriod] [bit] NULL ,
CONSTRAINT [PK_tbl_Event_Types] PRIMARY KEY CLUSTERED
(
[EventID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes. This query is in a stored procedure that returns the number
of points an employee has, so if there are no points I'd like a zero,
to do further processing with that zero. I could mess with the VB code
to make it zero there if there's no rows in the recordset, but this is
something that I've done with Oracle and is very useful in lots of
cases. I've Googled and seen responses that the
isnull(sum(isnull(expression, value)), value) should work, so I'm
missing something here!
Thanks
Pat|||If you want to see the groups where no matching rows exist by the where
clause, then you have to use [ALL] in the group by clause.
-- this is to reproduce what is happening to you
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by c1
-- this is what you want
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by all c1
go
AMB
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||You are dealing with two separate questions.
1. What is the sum on each row when there are records
2. What are the number of records (only important if 0)
One possible solution:
SELECT into a temp table. If the temp table has no rows, insert a row with
0. SELECT back off the temp table for your return value.
Since you have a filter on [File #], another option is to select into a
value and then test @.@.RowCount and set the value to 0 if @.@.RowCount = 0.
SOmething like:
DECLARE @.ReturnValue decimal
SELECT @.ReturnValue = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
IF (@.@.ROWCOUNT = 0)
SET @.ReturnValue = 0
SELECT @.ReturnValue
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||> There are currently no rows that match the query, but I want to get
> back a zero if that is the case.
Assuming that PointValue will always be positive if it *has* a value, you
can do this ugly stuff...
SELECT MAX(foo) FROM
(SELECT foo = COALESCE(SUM(PointValue),0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '20050224' -- m/dd/yyyy is a terrible format
AND getdate()
UNION SELECT foo = 0) x|||>> ..get SQL Server 2000 to return a zero (0) if no rows are returned in a s
um() statement <<
SELECT COALESCE (SUM(i), 0) FROM EmptyTable;
However, what you are actually getting back is an empty set (ab nulo,
ex nulo) and the empty set is converted into a NULL. This is important
when you use EXISTS() predicates.|||>> There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes.
<<
then why don't you add a dummy row to every group:
select a, sum(b) from(
select a,b from some_table
union all
select distinct a, 0 from some_table) t|||Aha!
when I have the where clause limiting the sum, I don't need the group
by (right?). The original code also had the [file #] (again, not my
code, I don't make column names with spaces!!) in the select statement
even though it wasn't ever used - I don't know, I'm trying to make this
whole application work with bad code and bad underlying database
structure. In any case, I was trying to shoehorn in code to existing
code and not seeing the group by wasn't needed.
Thanks!!!!!

Tuesday, February 14, 2012

can't install sql developer on vista

i am having a heck of a time -- i know SP2 makes sql server 2005
incompatiable... however, i need an instance INSTALLED first and that is
where the problem lies.
i of course built my system and put office on before visual studio - so
office put sql express on the system (which worked by the way) -- so i had to
uninstall sql express, because i wanted to use sql developer.
when i try to install sql developer it doesn't install owc , sql server or
the workstation tools.
how do I get sql installed on vista so i can continue working?
thanks
SandyHi Sandy
"Sandy Ryan" wrote:
> i am having a heck of a time -- i know SP2 makes sql server 2005
> incompatiable... however, i need an instance INSTALLED first and that is
> where the problem lies.
> i of course built my system and put office on before visual studio - so
> office put sql express on the system (which worked by the way) -- so i had to
> uninstall sql express, because i wanted to use sql developer.
> when i try to install sql developer it doesn't install owc , sql server or
> the workstation tools.
> how do I get sql installed on vista so i can continue working?
> thanks
> Sandy
Did you check what the readme says about Vista issues?
http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm#_windows_vista or the log files such as SQLSTP.log
John|||i did read this, but this relates to SP2 - not the base installation WHICH
has to be there before apply SP2 -- and that's what i'm having trouble with.
any clues?
"John Bell" wrote:
> Hi Sandy
> "Sandy Ryan" wrote:
> > i am having a heck of a time -- i know SP2 makes sql server 2005
> > incompatiable... however, i need an instance INSTALLED first and that is
> > where the problem lies.
> > i of course built my system and put office on before visual studio - so
> > office put sql express on the system (which worked by the way) -- so i had to
> > uninstall sql express, because i wanted to use sql developer.
> > when i try to install sql developer it doesn't install owc , sql server or
> > the workstation tools.
> > how do I get sql installed on vista so i can continue working?
> > thanks
> > Sandy
> Did you check what the readme says about Vista issues?
> http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm#_windows_vista or the log files such as SQLSTP.log
> John
>|||Hi Sandy
So what messages are you getting when it fails? What does SQLStp.log say?
John
"Sandy Ryan" wrote:
> i did read this, but this relates to SP2 - not the base installation WHICH
> has to be there before apply SP2 -- and that's what i'm having trouble with.
> any clues?
> "John Bell" wrote:
> > Hi Sandy
> >
> > "Sandy Ryan" wrote:
> >
> > > i am having a heck of a time -- i know SP2 makes sql server 2005
> > > incompatiable... however, i need an instance INSTALLED first and that is
> > > where the problem lies.
> > > i of course built my system and put office on before visual studio - so
> > > office put sql express on the system (which worked by the way) -- so i had to
> > > uninstall sql express, because i wanted to use sql developer.
> > > when i try to install sql developer it doesn't install owc , sql server or
> > > the workstation tools.
> > > how do I get sql installed on vista so i can continue working?
> > > thanks
> > > Sandy
> >
> > Did you check what the readme says about Vista issues?
> > http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm#_windows_vista or the log files such as SQLSTP.log
> >
> > John
> >
> >|||John
I searched my system and couldn't find the ssqlstp.log -
but the summary log says:
Microsoft SQL Server 2005 9.00.1399.06
==============================OS Version : Professional (Build 6000)
Time : Fri Feb 23 10:12:20 2007
Machine : SWTAB2
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_1.log
----
Machine : SWTAB2
Product : OWC11
Error : Error 1706. Setup cannot find the required files. Check
your connection to the network, or CD-ROM drive. For other potential
solutions to this problem, see C:\Program Files\Microsoft
Office\OFFICE11\1033\SETUP.CHM.
----
Machine : SWTAB2
Product : Microsoft Office 2003 Web Components
Product Version : 11.0.8003.0
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_OWC11_1.log
Last Action : InstallExecute
Error String : Setup cannot find the required files. Check your
connection to the network, or CD-ROM drive. For other potential solutions to
this problem, see C:\Program Files\Microsoft Office\OFFICE11\1033\SETUP.CHM.
Error Number : 1706
----
Machine : SWTAB2
Product : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_1.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_2.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_1.log
----
Machine : SWTAB2
Product : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_2.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_3.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_2.log
----
Machine : SWTAB2
Product : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_3.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_4.log
----
Machine : SWTAB2
Product : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_4.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_5.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_3.log
----
Machine : SWTAB2
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SqlWriter_1.log
----
Machine : SWTAB2
Product : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_5.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_6.log
----
Machine : SWTAB2
Product : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_4.log
----
Machine : SWTAB2
Product : Microsoft SQL Server 2005 Books Online (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BOL_1.log
----
Machine : SWTAB2
Product : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_6.log
----
Machine : SWTAB2
Product : SQLXML4
Error : An installation package for the product SQLXML4 cannot be
found. Try the installation again using a valid copy of the installation
package 'sqlxml4.msi'.
----
Machine : SWTAB2
Product : SQLXML4
Product Version : 9.00.3042.00
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLXML4_1.log
Last Action : InstallFinalize
Error String : An installation package for the product SQLXML4 cannot be
found. Try the installation again using a valid copy of the installation
package 'sqlxml4.msi'.
Error Number : 1706
---
"John Bell" wrote:
> Hi Sandy
> So what messages are you getting when it fails? What does SQLStp.log say?
> John
> "Sandy Ryan" wrote:
> >
> > i did read this, but this relates to SP2 - not the base installation WHICH
> > has to be there before apply SP2 -- and that's what i'm having trouble with.
> >
> > any clues?
> >
> > "John Bell" wrote:
> >
> > > Hi Sandy
> > >
> > > "Sandy Ryan" wrote:
> > >
> > > > i am having a heck of a time -- i know SP2 makes sql server 2005
> > > > incompatiable... however, i need an instance INSTALLED first and that is
> > > > where the problem lies.
> > > > i of course built my system and put office on before visual studio - so
> > > > office put sql express on the system (which worked by the way) -- so i had to
> > > > uninstall sql express, because i wanted to use sql developer.
> > > > when i try to install sql developer it doesn't install owc , sql server or
> > > > the workstation tools.
> > > > how do I get sql installed on vista so i can continue working?
> > > > thanks
> > > > Sandy
> > >
> > > Did you check what the readme says about Vista issues?
> > > http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm#_windows_vista or the log files such as SQLSTP.log
> > >
> > > John
> > >
> > >|||also
all the path error messages - the files ARE There
"Sandy Ryan" wrote:
> John
> I searched my system and couldn't find the ssqlstp.log -
> but the summary log says:
> Microsoft SQL Server 2005 9.00.1399.06
> ==============================> OS Version : Professional (Build 6000)
> Time : Fri Feb 23 10:12:20 2007
> Machine : SWTAB2
> Product : Microsoft SQL Server Setup Support Files (English)
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_1.log
> ----
> Machine : SWTAB2
> Product : OWC11
> Error : Error 1706. Setup cannot find the required files. Check
> your connection to the network, or CD-ROM drive. For other potential
> solutions to this problem, see C:\Program Files\Microsoft
> Office\OFFICE11\1033\SETUP.CHM.
> ----
> Machine : SWTAB2
> Product : Microsoft Office 2003 Web Components
> Product Version : 11.0.8003.0
> Install : Failed
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_OWC11_1.log
> Last Action : InstallExecute
> Error String : Setup cannot find the required files. Check your
> connection to the network, or CD-ROM drive. For other potential solutions to
> this problem, see C:\Program Files\Microsoft Office\OFFICE11\1033\SETUP.CHM.
> Error Number : 1706
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server 2005 Backward compatibility
> Product Version : 8.05.1054
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_1.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Setup Support Files (English)
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_2.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Native Client
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_1.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server 2005 Backward compatibility
> Product Version : 8.05.1054
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_2.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Setup Support Files (English)
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_3.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Native Client
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_2.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server 2005 Backward compatibility
> Product Version : 8.05.1054
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_3.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Setup Support Files (English)
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_4.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server 2005 Backward compatibility
> Product Version : 8.05.1054
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_4.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Setup Support Files (English)
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_5.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Native Client
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_3.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server VSS Writer
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SqlWriter_1.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server 2005 Backward compatibility
> Product Version : 8.05.1054
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_5.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Setup Support Files (English)
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLSupport_6.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server Native Client
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLNCLI_4.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server 2005 Books Online (English)
> Product Version : 9.00.1399.06
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BOL_1.log
> ----
> Machine : SWTAB2
> Product : Microsoft SQL Server 2005 Backward compatibility
> Product Version : 8.05.1054
> Install : Successful
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_BackwardsCompat_6.log
> ----
> Machine : SWTAB2
> Product : SQLXML4
> Error : An installation package for the product SQLXML4 cannot be
> found. Try the installation again using a valid copy of the installation
> package 'sqlxml4.msi'.
> ----
> Machine : SWTAB2
> Product : SQLXML4
> Product Version : 9.00.3042.00
> Install : Failed
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0001_SWTAB2_SQLXML4_1.log
> Last Action : InstallFinalize
> Error String : An installation package for the product SQLXML4 cannot be
> found. Try the installation again using a valid copy of the installation
> package 'sqlxml4.msi'.
> Error Number : 1706
> ---
> "John Bell" wrote:
> > Hi Sandy
> >
> > So what messages are you getting when it fails? What does SQLStp.log say?
> >
> > John
> >
> > "Sandy Ryan" wrote:
> >
> > >
> > > i did read this, but this relates to SP2 - not the base installation WHICH
> > > has to be there before apply SP2 -- and that's what i'm having trouble with.
> > >
> > > any clues?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Sandy
> > > >
> > > > "Sandy Ryan" wrote:
> > > >
> > > > > i am having a heck of a time -- i know SP2 makes sql server 2005
> > > > > incompatiable... however, i need an instance INSTALLED first and that is
> > > > > where the problem lies.
> > > > > i of course built my system and put office on before visual studio - so
> > > > > office put sql express on the system (which worked by the way) -- so i had to
> > > > > uninstall sql express, because i wanted to use sql developer.
> > > > > when i try to install sql developer it doesn't install owc , sql server or
> > > > > the workstation tools.
> > > > > how do I get sql installed on vista so i can continue working?
> > > > > thanks
> > > > > Sandy
> > > >
> > > > Did you check what the readme says about Vista issues?
> > > > http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm#_windows_vista or the log files such as SQLSTP.log
> > > >
> > > > John
> > > >
> > > >|||Hi
"Sandy Ryan" wrote:
> also
> all the path error messages - the files ARE There
>
Have you tried copying the CD to the local hard disc? Where did you get the
installation from? Is it corrupted?
John|||msdn subscription
i have it on the local machines cd - i'll try copying it over
"John Bell" wrote:
> Hi
> "Sandy Ryan" wrote:
> > also
> > all the path error messages - the files ARE There
> >
> Have you tried copying the CD to the local hard disc? Where did you get the
> installation from? Is it corrupted?
> John|||"Sandy Ryan" wrote:
> i am having a heck of a time -- i know SP2 makes sql server 2005
> incompatiable... however, i need an instance INSTALLED first and that is
> where the problem lies.
> i of course built my system and put office on before visual studio - so
> office put sql express on the system (which worked by the way) -- so i had to
> uninstall sql express, because i wanted to use sql developer.
> when i try to install sql developer it doesn't install owc , sql server or
> the workstation tools.
> how do I get sql installed on vista so i can continue working?
> thanks
> Sandy
I found the following similar problem and resolution. I am not sure if this
is exactly what you experienced, but it sounds close.
I installed:
-Vista Ultimate x64
-Sql Server 2005 Express Edition (via Visual Studio 2005 Pro)
At this point, Sql Express is installed, but NOT Management Studio. I then
tried to Install Sql Dev edition. I discovered that it was considered an
Upgrade from Sql Express. But the kicker: it would NOT install the
Workstation Tools because it thought it didnâ't qualify for an Upgrade! I
think this is a bug personally. Even though Sql Express was installed AND is
a valid upgrade path to Sql Dev, the problem is that it didnâ't see Management
Studio, and therefore assumed you donâ't have rights to install Dev edition
Workstation Tools (completely stupid).
Resolution:
-Go to Programs and Features
-Right Click Microsoft Sql Server 2005 and select Uninstall
-Check ONLY the box that says Workstation Tools (donâ't uninstall Sql Express
unless you donâ't want it)
-Click Next and finish the Uninstall of the Workstation Components
-Run setup for Sql Dev edition
Worked like a charm. I now have Sql 2005 Express and Sql Dev 2005 editions
running, and the Dev edition tools wherewith to manage them (and BTW, there
are reasons you want to keep Sql Express depending on what you are
developing). Apparently, Sql Dev edition has now been tricked into thinking
itâ's a clean install since the Express edition of the workstation tools were
removed (no longer an â'unsupported upgradeâ' scenario). It will therefore go
ahead with Dev edition Workstation tools, and Management Studio also gets
installed.
Tip: Remember to run SP2 for both Sql Express and Sql Dev at this point.
Also, I believe an alternative resolution is to simply install Sql Express
Management Studio after Sql Express (Visual Studio), and THEN run the Sql Dev
edition install, and it will in fact upgrade everything properly. I cannot
confirm this, but I recall having a conversation with a friend at Microsoft
who said that worked for him.
I hope this post helps some poor souls out there!
-David P.|||You helped out this poor soul.
Thanks David.|||David
Thanks for the information
Unfortuntately it didn't work, I'm still getting SQLXMK4; OWC11 and
Workstation errors so they don't install
any other thoughts?
thanks
Sandy
"David Pugmire" wrote:
>
> "Sandy Ryan" wrote:
> > i am having a heck of a time -- i know SP2 makes sql server 2005
> > incompatiable... however, i need an instance INSTALLED first and that is
> > where the problem lies.
> > i of course built my system and put office on before visual studio - so
> > office put sql express on the system (which worked by the way) -- so i had to
> > uninstall sql express, because i wanted to use sql developer.
> > when i try to install sql developer it doesn't install owc , sql server or
> > the workstation tools.
> > how do I get sql installed on vista so i can continue working?
> > thanks
> > Sandy
> I found the following similar problem and resolution. I am not sure if this
> is exactly what you experienced, but it sounds close.
> I installed:
> -Vista Ultimate x64
> -Sql Server 2005 Express Edition (via Visual Studio 2005 Pro)
> At this point, Sql Express is installed, but NOT Management Studio. I then
> tried to Install Sql Dev edition. I discovered that it was considered an
> Upgrade from Sql Express. But the kicker: it would NOT install the
> Workstation Tools because it thought it didnâ't qualify for an Upgrade! I
> think this is a bug personally. Even though Sql Express was installed AND is
> a valid upgrade path to Sql Dev, the problem is that it didnâ't see Management
> Studio, and therefore assumed you donâ't have rights to install Dev edition
> Workstation Tools (completely stupid).
> Resolution:
> -Go to Programs and Features
> -Right Click Microsoft Sql Server 2005 and select Uninstall
> -Check ONLY the box that says Workstation Tools (donâ't uninstall Sql Express
> unless you donâ't want it)
> -Click Next and finish the Uninstall of the Workstation Components
> -Run setup for Sql Dev edition
> Worked like a charm. I now have Sql 2005 Express and Sql Dev 2005 editions
> running, and the Dev edition tools wherewith to manage them (and BTW, there
> are reasons you want to keep Sql Express depending on what you are
> developing). Apparently, Sql Dev edition has now been tricked into thinking
> itâ's a clean install since the Express edition of the workstation tools were
> removed (no longer an â'unsupported upgradeâ' scenario). It will therefore go
> ahead with Dev edition Workstation tools, and Management Studio also gets
> installed.
> Tip: Remember to run SP2 for both Sql Express and Sql Dev at this point.
> Also, I believe an alternative resolution is to simply install Sql Express
> Management Studio after Sql Express (Visual Studio), and THEN run the Sql Dev
> edition install, and it will in fact upgrade everything properly. I cannot
> confirm this, but I recall having a conversation with a friend at Microsoft
> who said that worked for him.
> I hope this post helps some poor souls out there!
> -David P.
>|||David
Thanks for the help - unfortunately I'm still getting errors on SQLS=XML4;
OWC11 and Workstation tools when i attempt to load (eventhough SQLXML4 is
already installed)
Any other suggestions?
"David Pugmire" wrote:
>
> "Sandy Ryan" wrote:
> > i am having a heck of a time -- i know SP2 makes sql server 2005
> > incompatiable... however, i need an instance INSTALLED first and that is
> > where the problem lies.
> > i of course built my system and put office on before visual studio - so
> > office put sql express on the system (which worked by the way) -- so i had to
> > uninstall sql express, because i wanted to use sql developer.
> > when i try to install sql developer it doesn't install owc , sql server or
> > the workstation tools.
> > how do I get sql installed on vista so i can continue working?
> > thanks
> > Sandy
> I found the following similar problem and resolution. I am not sure if this
> is exactly what you experienced, but it sounds close.
> I installed:
> -Vista Ultimate x64
> -Sql Server 2005 Express Edition (via Visual Studio 2005 Pro)
> At this point, Sql Express is installed, but NOT Management Studio. I then
> tried to Install Sql Dev edition. I discovered that it was considered an
> Upgrade from Sql Express. But the kicker: it would NOT install the
> Workstation Tools because it thought it didnâ't qualify for an Upgrade! I
> think this is a bug personally. Even though Sql Express was installed AND is
> a valid upgrade path to Sql Dev, the problem is that it didnâ't see Management
> Studio, and therefore assumed you donâ't have rights to install Dev edition
> Workstation Tools (completely stupid).
> Resolution:
> -Go to Programs and Features
> -Right Click Microsoft Sql Server 2005 and select Uninstall
> -Check ONLY the box that says Workstation Tools (donâ't uninstall Sql Express
> unless you donâ't want it)
> -Click Next and finish the Uninstall of the Workstation Components
> -Run setup for Sql Dev edition
> Worked like a charm. I now have Sql 2005 Express and Sql Dev 2005 editions
> running, and the Dev edition tools wherewith to manage them (and BTW, there
> are reasons you want to keep Sql Express depending on what you are
> developing). Apparently, Sql Dev edition has now been tricked into thinking
> itâ's a clean install since the Express edition of the workstation tools were
> removed (no longer an â'unsupported upgradeâ' scenario). It will therefore go
> ahead with Dev edition Workstation tools, and Management Studio also gets
> installed.
> Tip: Remember to run SP2 for both Sql Express and Sql Dev at this point.
> Also, I believe an alternative resolution is to simply install Sql Express
> Management Studio after Sql Express (Visual Studio), and THEN run the Sql Dev
> edition install, and it will in fact upgrade everything properly. I cannot
> confirm this, but I recall having a conversation with a friend at Microsoft
> who said that worked for him.
> I hope this post helps some poor souls out there!
> -David P.
>|||I also was having a problem installing SQL 2005 Developer edition on Vista. I
ended up copying the install to my hard drive and then right clicking on the
setup.exe and choose properties. I then went to the compatability tab and
checked the box that said run in compatability mode for Windows XP Service
Pack2 and also checked the box that said run this prgram as administrator. I
then ran the setup.exe by double clicking on it and I no longer got any
errors.
"Sandy Ryan" wrote:
> David
> Thanks for the help - unfortunately I'm still getting errors on SQLS=XML4;
> OWC11 and Workstation tools when i attempt to load (eventhough SQLXML4 is
> already installed)
> Any other suggestions?
> "David Pugmire" wrote:
> >
> >
> > "Sandy Ryan" wrote:
> >
> > > i am having a heck of a time -- i know SP2 makes sql server 2005
> > > incompatiable... however, i need an instance INSTALLED first and that is
> > > where the problem lies.
> > > i of course built my system and put office on before visual studio - so
> > > office put sql express on the system (which worked by the way) -- so i had to
> > > uninstall sql express, because i wanted to use sql developer.
> > > when i try to install sql developer it doesn't install owc , sql server or
> > > the workstation tools.
> > > how do I get sql installed on vista so i can continue working?
> > > thanks
> > > Sandy
> >
> > I found the following similar problem and resolution. I am not sure if this
> > is exactly what you experienced, but it sounds close.
> >
> > I installed:
> > -Vista Ultimate x64
> > -Sql Server 2005 Express Edition (via Visual Studio 2005 Pro)
> >
> > At this point, Sql Express is installed, but NOT Management Studio. I then
> > tried to Install Sql Dev edition. I discovered that it was considered an
> > Upgrade from Sql Express. But the kicker: it would NOT install the
> > Workstation Tools because it thought it didnâ't qualify for an Upgrade! I
> > think this is a bug personally. Even though Sql Express was installed AND is
> > a valid upgrade path to Sql Dev, the problem is that it didnâ't see Management
> > Studio, and therefore assumed you donâ't have rights to install Dev edition
> > Workstation Tools (completely stupid).
> >
> > Resolution:
> > -Go to Programs and Features
> > -Right Click Microsoft Sql Server 2005 and select Uninstall
> > -Check ONLY the box that says Workstation Tools (donâ't uninstall Sql Express
> > unless you donâ't want it)
> > -Click Next and finish the Uninstall of the Workstation Components
> > -Run setup for Sql Dev edition
> >
> > Worked like a charm. I now have Sql 2005 Express and Sql Dev 2005 editions
> > running, and the Dev edition tools wherewith to manage them (and BTW, there
> > are reasons you want to keep Sql Express depending on what you are
> > developing). Apparently, Sql Dev edition has now been tricked into thinking
> > itâ's a clean install since the Express edition of the workstation tools were
> > removed (no longer an â'unsupported upgradeâ' scenario). It will therefore go
> > ahead with Dev edition Workstation tools, and Management Studio also gets
> > installed.
> >
> > Tip: Remember to run SP2 for both Sql Express and Sql Dev at this point.
> > Also, I believe an alternative resolution is to simply install Sql Express
> > Management Studio after Sql Express (Visual Studio), and THEN run the Sql Dev
> > edition install, and it will in fact upgrade everything properly. I cannot
> > confirm this, but I recall having a conversation with a friend at Microsoft
> > who said that worked for him.
> >
> > I hope this post helps some poor souls out there!
> >
> > -David P.
> >

Sunday, March 11, 2012

can't return zero when no rows exist using aggregate function sum

Tuesday, February 14, 2012

can't install sql developer on vista

can't install sql developer on vista

can't install sql developer on vista

sql report manager

Blog Archive

About Me