RE: support requests

RE: support requests

 

  

Thanks to everyone who has offered assistance. I will try your
suggestions first thing monday. I will update you on the results.

TX< Lou


> 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
>
>
>
>
>
> ---------------------------------------------------------------------
> 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