Re: support requests

Re: support requests

 

  

That error does not have anything to do with the %DayRange% code. It
would appear that one of the following is returning two or more rows.

set @second = (select snapshot2 from #t where snapshot1 = @first)
set @Guid = (select Guid from #t where snapshot1 = @first)
set @InventoryDate = (select distinct inventorydate from



Englund Louis wrote:
> 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
>
>
>
>
> ---------------------------------------------------------------------
> 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