Newbie here,
I have been tasked with troubleshooting the following script. When it
runs I get this error:
"Sql error in query. Error: System.Data.Sqlclient.SqlError: Subquery
returned more than 1 value. This is not permitted when the subquery
follows =, !=, <=, >, >= or when the subquery is used as an expression."
Here is the complete script. I was thinking that maybe something is
wrong with the "DAYRANGE" value near the end :(
create table
#t
(
Snapshot1 int,
Guid nvarchar(40),
Snapshot2 int,
InventoryDate datetime
)
create table
#info
(
Snapshot int,
InventoryDate datetime,
Guid nvarchar(40),
Change nvarchar(50),
Name nvarchar(100)
)
insert into #t (Snapshot1,Guid,Snapshot2,InventoryDate)
Select distinct a.snapshotid, '{' + CONVERT (nvarchar(40),
a._resourceguid) + '}' , min(b.snapshotid)as ss2,a.InventoryDate
from dbo.InvHist_AeX_OS_Add_Remove_Programs a,
dbo.InvHist_AeX_OS_Add_Remove_Programs b
where a._resourceguid = b._resourceguid
and b.snapshotid > a.snapshotid
group by a.snapshotid,a._resourceguid,a.inventorydate
declare @First varchar(255)
declare @Second varchar(255)
declare @Guid varchar(255)
declare @InventoryDate Datetime
declare tmpCur cursor for
select snapshot1 from #t order by snapshot1
open
tmpCur
while 1 = 1 begin
fetch next from tmpCur into @First
if @@fetch_status <> 0
break
set @second = (select snapshot2 from #t where snapshot1 = @first)
set @Guid = (select Guid from #t where snapshot1 = @first)
set @InventoryDate = (select distinct inventorydate from
dbo.InvHist_AeX_OS_Add_Remove_Programs where snapshotid = @second)
insert into #info (Snapshot, InventoryDate, Guid, Change, Name)
Select @second as snapshot, @InventoryDate,'{' + CONVERT (nvarchar(40),
_resourceguid) + '}','Installed',Name
from dbo.InvHist_AeX_OS_Add_Remove_Programs
where _id not in (Select a._id
from dbo.InvHist_AeX_OS_Add_Remove_Programs a,
dbo.InvHist_AeX_OS_Add_Remove_Programs b
where a._resourceguid = @guid
and a._resourceguid = b._resourceguid
and a.snapshotID = @second
and b.snapshotid = @first
and a.name = b.name)
and snapshotID = @second
insert into #info (Snapshot, InventoryDate, Guid, Change, Name)
Select @second as snapshot,@InventoryDate,'{' + CONVERT (nvarchar(40),
_resourceguid) + '}','Un-Installed',name
from dbo.InvHist_AeX_OS_Add_Remove_Programs
where _id not in (Select a._id
from dbo.InvHist_AeX_OS_Add_Remove_Programs a,
dbo.InvHist_AeX_OS_Add_Remove_Programs b
where a._resourceguid = @Guid
and a._resourceguid = b._resourceguid
and a.snapshotID = @first
and b.snapshotid = @second
and a.name = b.name)
and snapshotID = @first
end
close tmpCur
deallocate tmpCur
select c.domain as Region,c.location,b.name as Computer,
a.InventoryDate, a.Guid, a.Change, a.Name as Software, isnull(b.[Last
Logon User],d.[user]) as [Last Logon User]
from #info a, wrksta b, altiris3_aux.dbo.locations c,
dbo.Inv_AeX_AC_Primary_User d
where b.guid = a.guid
and (b.guid = d._Resourceguid and d.[month]= DATENAME(month, getdate())
)
and left(b.name,5) = c.sitecode
and c.domain like '%Region%'
and c.location like '%Location%'
and datediff(day,a.InventoryDate,getdate())< %DayRange%
order by a.InventoryDate
drop table #t
drop table #info
Any advice would be greatly appreciated.
Sincerely,
Lou
-----Original Message-----
From: Robert Davis
[mailto:mssqldba-ezmlmshield-x54928922.[Email address protected]
Sent: Friday, January 27, 2006 12:28 PM
To: LazyDBA Discussion
Subject: RE: support requests
Actually, there was a database language called Sequel. It was the
predecessor to SQL. It stood for Structured English Query Language and
was defined by researchers at IBM in 1974. It's the reason why SQL
Server is pronounced "sequel server" and not "ess queue ell server".
Robert Davis
-----Original Message-----
From: LHale
[mailto:mssqldba-ezmlmshield-x53617557.[Email address protected]
Sent: Friday, January 27, 2006 7:48 AM
To: LazyDBA Discussion
Subject: Re: support requests
The scary part is that I actually had to explain to my boss that SQL
stood for Structured Query Language! In our help desk application, SQL
support is listed as Sequel. I printed this out for his enjoyment.
Thanks
Lisa A. Hale
Database Administrator
"Michael_Beadles" <mssqldba-ezmlmshield-x66170865.[Email address
protected]
01/26/2006 08:52 PM
To
"LazyDBA Discussion" <[Email address protected] cc
Subject
Re: support requests
Non-clunky forms will have check boxes, so the user will be able to fill
them in quickly...
Name ______________
Rqst Date ______________
Rqst ECD:
[ ] 3 AM Tomorrow.
[ ] 8 PM Today.
[ ] 3 PM Today.
[ ] You are authorized to use company-owned Time Travel Machine.
[ ] Whatever the next company holiday is, that's when it needs to
be done.
Request Type:
A. Software Maintenance
[ ] Install SQL Server
[ ] Uninstall SQL Server; replace with MS Access
[ ] Uninstall SQL Server; replace with MS Excel
B. User Account Maintenance
[ ] Give me the sa password.
[ ] Make my password blank, the one I have is too hard to
remember / type / pronounce
[ ] Give me sys admin rights:
Reason:
[ ] I am good friends with someone in your appraisal /
management chain.
[ ] I am a developer (manager or above).
[ ] I work in Sales (manager or above).
[ ] Give me dbo rights in db _____________
Reason:
[ ] I am a developer (team leader or below).
[ ] I work in Sales (team leader or below).
C. DB Performance Maintenance
[ ] Backup / DBCC jobs slowing down user access; please delete
jobs.
D. Developer Assistance
[ ] Urgent: troubleshoot non-functioning production query.
Production is down!
(Query text to be supplied in 3 spanned zip files due to email
attachment limitations)
[ ] Urgent: table was accidently dropped 3 months ago (ref
Section
B request submitted 3 months ago).
Tape rotation policy _________________________________.
E. Knowlege Transfer Assistance
[ ] How does SQL Server work?
[ ] How do you pronounce / spell "SQL"?
"Zelli Brian "
<mssqldba-ezmlmsh
ield-x45742975.x1
To
[Email Address Removed] "LazyDBA Discussion"
.com> <[Email address protected]
cc
01/19/2006 12:04
PM
Subject
support requests
Hello all,
Quick question. If you do support, do you utilize a support request
form? I need samples to revise ours. People bypass it because its'
clunky
to work with and I need a simpler form. Anybody have one they are
willing
to share????
Brian
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page