RE: support requests

RE: support requests

 

  

As a last resort, there's always "Select Top 1" .... but I would avoid it if you can.

Robert Davis
<mailto:[Email address protected]

________________________________

From: Jay Butler [mailto:mssqldba-ezmlmshield-x26202293.[Email address protected]
Sent: Fri 1/27/2006 5:24 PM
To: LazyDBA Discussion
Subject: Re: support requests



This syntax will get rid of the error, but it may not produce the
desired results.

select @var1 = snapshot2 from #t where snapshot1 = ...
select @var2 = Guid from #t where snapshot1 = ...
select @var3 = distinct inventorydate from ...


Nelson Merrill wrote:
> I would agree with Jay. Try running those sub-selects individually for
> all scenarios and I'm almost sure you'll find one of them returning more
> than 1 row.
>
> -----Original Message-----
> From: Jay Butler
> [Email address protected]
> Sent: Friday, January 27, 2006 1:57 PM
> To: LazyDBA Discussion
> Subject: 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 [Email address protected] = (select snapshot2 from #t where snapshot1 = [Email address protected]
> set [Email address protected] = (select Guid from #t where snapshot1 = [Email address protected]
> set [Email address protected] = (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 [Email address protected] varchar(255)
>> declare [Email address protected] varchar(255)
>> declare [Email address protected] varchar(255)
>> declare [Email address protected] Datetime
>>
>> declare tmpCur cursor for
>> select snapshot1 from #t order by snapshot1
>> open
>>
>> tmpCur
>>
>> while 1 = 1 begin
>> fetch next from tmpCur into [Email address protected]
>> if [Email address protected] <> 0
>> break
>>
>> set [Email address protected] = (select snapshot2 from #t where snapshot1 = [Email address protected]
>>
>
>
>> set [Email address protected] = (select Guid from #t where snapshot1 = [Email address protected]
>> set [Email address protected] = (select distinct inventorydate from
>> dbo.InvHist_AeX_OS_Add_Remove_Programs where snapshotid = [Email address protected]
>>
>
>
>> insert into #info (Snapshot, InventoryDate, Guid, Change, Name)
>> Select [Email address protected] as snapshot, [Email address protected] + 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 = [Email address protected]
>> and a._resourceguid = b._resourceguid
>> and a.snapshotID = [Email address protected]
>> and b.snapshotid = [Email address protected]
>> and a.name = b.name)
>> and snapshotID = [Email address protected]
>>
>> insert into #info (Snapshot, InventoryDate, Guid, Change, Name)
>> Select [Email address protected] as [Email address protected] + 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 = [Email address protected]
>> and a._resourceguid = b._resourceguid
>> and a.snapshotID = [Email address protected]
>> and b.snapshotid = [Email address protected]
>> and a.name = b.name)
>> and snapshotID = [Email address protected]
>>
>> 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 protected] "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
>>
>>
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> 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