Best Practices question:

Hi,
When I run BPA I get this message:
Database Name ANSI_NULLS ANSI_PADDING_ENABLED ANSI_WARNINGS ARITHABORT
CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIERS NUMERIC_ROUNDABORT Result
[Recommended Settings] ON ON ON ON ON ON OFF -
Axapta OFF OFF OFF OFF OFF OFF OFF Failed
Even though I have turn them ON in the server properties, and in the
database properties as well
(2 total qualifying rows in repository table
[bpa_rule_tbl!DBSQLOPTIONS!1.0] )
Copy Close
begin 666 copy.gif
M1TE&.#EA$ `0`.8``/___U&#T35)8Y.BM[+"V(JN_/S]_J[$\%R,VIFLQW.A
MWH&NY:Z_U)BKQI*P[=G=XBE4JBE4K&Z<VF".Y[_+W=+>\,?2X 25,FW^G\R-(
MEOO]_IRUYV:"I=OD]Z[0__7V^Y:FNZFYST=NNJO"[Z.RQ];>[*"Y[^OO]H6G
MZZFXSIRKP;K:_]+;\62%R2=0HMGD^9_%^I>IQ#5EQ=[BZ<_9YB!!AO?X^]?
MZBI7LW.2T9RPSK[.\/GZ_-/>]?;Y_;+)\*^^U"5-G<73\I>FN_#T^_#S^&B7
MU9NOS)JNS")%CIJMRIJNREMRCZ*QQRM7L2=0I(*?V.KO^U5YP <_;\9"JX*:]
M[*.RR+K%U/#R^&Z7YW*:ZJK![^#F\8ZX\*_#[2-'DB%#B25*F")&CR%#BLW7
M[>;J\____P``````````````````````````````````````` ```````````
M````````````````````````````````````````````````` "'Y! $``&8`
M+ `````0`! ```>F@.&:"@.X2%9@.0$# PI5C.&@.P0`D@.!-' ^/9D"3!B!73)>&
M(0`K'A4#!R-;)@.X.+X,DDCQ890>3`"@.R4X(J`#!="PI5MEH_3F1F0P8?)UPE
M&P`%!2P3.!%/9@.,VP!)&5)-$'4)(0=A%-S06%% `&%D(`0%+8=@."]?4Y`!I1
M/3M>"5^/6@.!X!R] @.R2/I/C0<41)@.@.8QP#P2`0&"BPL9Q(RI@.:FC1T.!```[
`
end
begin 666 arrow.gif
M1TE&.#EA'@.`9`/<``,# P !P``!O``&1`0)\`@.)Y`@.2?! 6?!05^!0BC" JM
M"@.=\!PZQ#@.VD#0I^"@.N "P^C#PU_#1.R$Q2O%!6D%1BV&!&#$0]O#Q*"$AJS
M&AFL&1BB&!-]$QRQ'!RC'!F-&2&U(1B!&!=Y%Q9T%A^>'QAX&"2O)!EW&1^,
M'QIV&B2>)!U_'2*1(B61YZ'B:9)B.,(R%^(2F:*2F7*2J7* B-^(R-](RN8
M*RJ1*BR6+"V5+2Z4+C"9,#"8,"^4+R^3+S&8,3"5,#BJ.#.9, S:<-C6:-3FA
M.3><-SB<.#J=.CN>.T"J0#R?/#V?/4&H03^@./T*F0C^?/T&A04.B0T*@.0D*?
M0D:G1D6E14:C1D6B14FG24BD2$FD24BB2$JD2DRG3$NE2TRE3 $ZH3DZG3DRC
M3$^G3U*H4E&F45.I4U*F4E2I5%:K5E2H5%JL6EFK65NM6UJK6 EVN75ZO7EZM
M7F&O86"N8&*P8F.Q8V.P8V6R96BT:&>S9V>R9VJT:FRV;&NU: W"X<&^W;W:[
M=G>[=WF\>7J]>H# @.(S&C(W&C7^P?X6WA8J[BJ/6HYW/G;+8LK39M/___^;F
MY@.``````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````"'Y! $`````+ `````>`!D`0 C_``$('$BPH,&#
M!B/)*00(#YPTD"(.X='#AXU(DA 6C/0AQY$F3(PP@.F2B@.P0%!@.I@.U,BRI4N!
MD6+*G#GS)<PH:"+JW F)1(B5-B4)'4ITJ,VC2",-:G2($* ^>>S$63-&BH\3
M0%M&*G.'CILS70T?F"1HRL+"/EH *FS!<K2W1FF #A`5J-D11=&)$B10D1
M'"(X6$ @.0**[2!,K7LP8821'@.O:\45/&"Q@.L3Y0`@.;$HDLU(?Q 9"L2GSB-(
M8;A(21+$!>*#D=#XT3.'#9DJ.F?<T%'C=<(L;7CR;+'"]\8B8,R@.$:/%B1"=
M&C9@.,$XP$@.LD4[9<@.31 4&"1!0'Q\8R0(*'#]VY)"AP@..%!@.<&"!A/GJ;]
*F!D;Z]\_," `.P``
`
end
Is there some client connection that's chaging tehse settings? You could
run a profiler for some time to check.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Best Practices Question - how do you execute multiple packages?

I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.

Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.

Any comments or suggestions?

Sounds like you have a great solution that works for you. If you decided to use a parent package to execute the child packages, it would be easy enough to drive with a ForEach loop and a simple file input or even a script task. A lot about whether this is the right choice for you depends on some things you haven't told us. For example, what is the long term plan for your current solution, do you plan on enhancing the current solution etc. Also, if you use a parent package, you're not forced to use parent package configurations. You can still use the same configuration scheme you're using currently.

From the information you've given, I'd say that it sounds like a good solution.

|||

Thanks for the response, Kirk. My 200+ packages use the config file in an indirect manner. When I design my packages, I don't step thru the Configuration Wizard and create a direct configuration, I just make sure to always name my objects the same and then I just apply my global config file with the /CONFIGFILE "c:\wherever\conf.dtsConfig". However, the Execute Package Task doesn't have any properties for specifying configurations. Ideally, I'd like a master package that read a queue table and that table would have the path to a package and a path to a config file and feed that to the Execute Package Task. Also, it would be nice if the Execute Package Task had a property like /SET from dtexec.exe so you wouldn't have to have child packages "pulling" variables/data from a master package because you have to design child packages with an awareness that they are executing in a larger context. Being able to apply a change from a master package to a child package would be preferrabe.

Best Practices Analyzer contradicts BOL for SQLSERVERAGENT

The 2005 Best Practices Analyzer (July 2007) contradicts Books Online about what account to run SQLSERVERAGENT under.

BPA says use Network Service Account, BOL explicitly says not to use Network Service Account:

BPA gives a warning saying "SQL Server Service [MY-PC\SQLSERVERAGENT] In Unrecommended Account On Host [MY-PC]. We recommend that the service [SQLSERVERAGENT] on host [MY-PC] be run under Network Service Account. Currently it is designated to run under the account [SOMEDOMAIN\someaccount]."

BooksOnline says "Security Note: Because multiple services can use the Network Service account, it is difficult to control which services have access to network resources, including SQL Server databases. We do not recommend using the Network Service account for the SQL Server Agent service."

So who's right ? BPA or BOL ?

Books online is correct - a non-system account provides more control over access, security.

Just curious...which version of BPA are you running?

-Sue

sql

Best Practices Analyzer Access Denied Message

I am receiving an Access is Denied messsage when trying to run the Best
Practices Analyzer against a Named Instance of SQL using the AV and Severe
Errors,Unexpected Shutdowns,Datbase File Compression, NO_LOG backups,Error
Log File Size, and Failed Backups rules. I am receiving expected results for
all other rule sets. Any direction or help is appreciated.
If you can post the BPA logs from %PROGRAMFILES%\Microsoft SQL Server Best
Practices Analyzer\log, we could analyze why you are getting this message
Thanks
Sethu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"sskeen" <sskeen@.discussions.microsoft.com> wrote in message
news:D5ED9CBC-67AE-487B-840A-16DFAF3F8131@.microsoft.com...
>I am receiving an Access is Denied messsage when trying to run the Best
> Practices Analyzer against a Named Instance of SQL using the AV and
> Severe
> Errors,Unexpected Shutdowns,Datbase File Compression, NO_LOG backups,Error
> Log File Size, and Failed Backups rules. I am receiving expected results
> for
> all other rule sets. Any direction or help is appreciated.
|||Here is the log file information for analysis:
//--
Best Practice log file created at 3/25/2005 10:16:12 AM
Job name: test
Log file: c:\program files\microsoft sql server best practices
analyzer\log\test_clmi-vm-sqltools_0001_(local)_sqlbpa.log
//--
Preparing rule... Microsoft.SqlBpa.Rules.BpAffinityMask.BPAffinityMa sk
Getting affinity mask setting... BPAffinityMask.Execute()
Preparing rule... Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression
Getting machine name and list of databases ... BpFileCompression.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpDbFileCompression.Bp FileCompression
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule... Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup
Getting current date ... BPNoLogBackup.Execute()
Getting machine name and server instance ... BPNoLogBackup.Execute()
Getting info from NT Event Log ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogB kup
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule... Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og
Getting machine and instance name... BPNoLogBackup.Execute()
Getting error log path... BPNoLogBackup.Execute()
Getting log size ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycl eErrLog
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt
Getting current date ... BPFailedBackupEvent.Execute()
Getting machine name and server instance ... BPFailedBackupEvent.Execute()
Getting info from NT Event Log ... BPFailedBackupEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFailedBackupEvents.B PFailedBackupEvt
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized
Getting machine name ... BPFTBackgroundServicesOptimized.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
-- End of inner exception stack trace --
Server stack trace:
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFTBackgroundServices Optimized.BPFTBgServicesOptimized
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem
Getting machine name ... BPFTMSSearchLocalSystem.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
-- End of inner exception stack trace --
Server stack trace:
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSyste m.BPMSSearchLocalSystem
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore
Getting machine name... BPFTPropertyStore.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize. BPFTPropertyStore
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule... Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt
Getting current date, machine name, and instance name ...
BPSevereErrorEvent.Execute()
Getting info from NT Event Log ... BPSevereErrorEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BP SevereErrorEvt
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn
Getting current date, machine name, and instance name ...
BPUnexpectedShutdown.Execute()
Getting startup and shutdown info from NT Event Log ...
BPUnexpectedShutdown.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
-- End of inner exception stack trace --
Server stack trace:
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns. BPUnexpectedShutdwn
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
"Sethu Srinivasan [MSFT]" wrote:

> If you can post the BPA logs from %PROGRAMFILES%\Microsoft SQL Server Best
> Practices Analyzer\log, we could analyze why you are getting this message
> Thanks
> Sethu
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "sskeen" <sskeen@.discussions.microsoft.com> wrote in message
> news:D5ED9CBC-67AE-487B-840A-16DFAF3F8131@.microsoft.com...
>
>
|||Preparing rule... Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression
Getting machine name and list of databases ... BpFileCompression.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
Preparing rule... Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup
Getting current date ... BPNoLogBackup.Execute()
Getting machine name and server instance ... BPNoLogBackup.Execute()
Getting info from NT Event Log ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
Preparing rule... Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og
Getting machine and instance name... BPNoLogBackup.Execute()
Getting error log path... BPNoLogBackup.Execute()
Getting log size ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt
Getting current date ... BPFailedBackupEvent.Execute()
Getting machine name and server instance ... BPFailedBackupEvent.Execute()
Getting info from NT Event Log ... BPFailedBackupEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized
Getting machine name ... BPFTBackgroundServicesOptimized.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem
Getting machine name ... BPFTMSSearchLocalSystem.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore
Getting machine name... BPFTPropertyStore.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
Preparing rule... Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt
Getting current date, machine name, and instance name ...
BPSevereErrorEvent.Execute()
Getting info from NT Event Log ... BPSevereErrorEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn
Getting current date, machine name, and instance name ...
BPUnexpectedShutdown.Execute()
Getting startup and shutdown info from NT Event Log ...
BPUnexpectedShutdown.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
"Sethu Srinivasan [MSFT]" wrote:

> If you can post the BPA logs from %PROGRAMFILES%\Microsoft SQL Server Best
> Practices Analyzer\log, we could analyze why you are getting this message
> Thanks
> Sethu
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "sskeen" <sskeen@.discussions.microsoft.com> wrote in message
> news:D5ED9CBC-67AE-487B-840A-16DFAF3F8131@.microsoft.com...
>
>

Best Practice Question - "Lowest possible privileges"

One of the MS Checklist Best Practices states to "Run SQL Server services
with the lowest possible privileges." Is there any definitive guide or
statement as to what these lowest possible privileges are or should be? Doe
s
this refer to SQL privileges or Windows privileges?Windows Previleges. Se BOL topic "Setting up Windows Services Accounts"
(topic updated July 2003). Let us know how you do.
dlr
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message news:04A12ED4-9606-487D-8232-383BC175C498@.microsoft.com...
> One of the MS Checklist Best Practices states to "Run SQL Server services
> with the lowest possible privileges." Is there any definitive guide or
> statement as to what these lowest possible privileges are or should be?
Does
> this refer to SQL privileges or Windows privileges?|||I could not find this topic in BOL - there was a "Windows Services Accounts"
topic, but it was greyed out. I can't find it on the MS web site either.
Would it be possible for you to post a link to the article or e-mail it to
me? The e-mail address is apioinc.com instead of newsgroups.nospam.
"Dennis Redfield" wrote:

> Windows Previleges. Se BOL topic "Setting up Windows Services Accounts"
> (topic updated July 2003). Let us know how you do.
> dlr
> "smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com
> wrote in message news:04A12ED4-9606-487D-8232-383BC175C498@.microsoft.com..
.
> Does
>
>|||The minimum permissions, rights are listed in the following:
HOW TO: Change the SQL Server or SQL Server Agent Service
Account Without Using SQL Enterprise Manager in SQL Server
2000
http://support.microsoft.com/?id=283811
-Sue
On Fri, 12 Nov 2004 15:56:02 -0800,
"smaas@.newsgroups.nospam"
<smaasnewsgroupsnospam@.discussions.microsoft.com> wrote:

>One of the MS Checklist Best Practices states to "Run SQL Server services
>with the lowest possible privileges." Is there any definitive guide or
>statement as to what these lowest possible privileges are or should be? Do
es
>this refer to SQL privileges or Windows privileges?

Best practice for remote queries

Hi
Would it still be currently regarded as best practice to use OPENQUERY
to run remote queries?
Regards
RichardIf you have a linked server defined you can use
SELECT <> FROM Server.DataBase.dbo.Table
<richhollis@.gmail.com> wrote in message
news:1133219788.491783.204870@.g49g2000cwa.googlegroups.com...
> Hi
> Would it still be currently regarded as best practice to use OPENQUERY
> to run remote queries?
> Regards
> Richard
>

Best Practice for Domain Account for SQL Services?

Hello,

I've done some searching, but have found no definite answer yet. Our SQL 2005 servers are members of Active Directory Services. We want to run SQL services using an ADS account.

I see 7 SQL services in the SQL Server Configuration Manager: Integration Services, FullText Search, SQL Server, Analysis Services, Reporting Services, Browser, and Agent.

Question: Is it a bad move to run them all using the same domain account? I mean, wouldn't this give, say the Browser service, more privileges than it needs by allowing its account access to the same resources as, for example, the Agent service? What I'm concerned about is a vulnerability in one service compromising another service.

I would like to be able to use one domain account for all 7 services on two SQL servers, but I have a feeling this is a poor choice.

What is the best method for running SQL services using a domain account?

Thanks,

-Tony

Perhaps these articles will help you in your decison process:

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx

Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557

Often, SQL Agent needs a higher level of access to network resources than the SQL Server Service. It is a good practice to provide the minimum level of access to local and network resources as is required for the task. It would not be unusual to have multiple domain accounts for the various SQL Services. In fact, for those services that require network resources, some folks will have separate domain accounts for each server. I have seen every SQL Agent service running under a unique account -the theory is if one server is compomised, unique accounts protect the remaining servers from compromise.

It is a balance between amount of security desired vs. amount of effort required to maintain that security. (As always...)

best practice analyzer help

Hi,
I have downloaded and run hte analyzer, but the result I get have lots of
exeption errors that contain the message:
Error while executing the Best Practice Group. See the log file
loadtraced_antodxp_0003_mshfsql_sqlbpa.log. Error message:
the log file cannot be found anywhere on the server or in my pc that I run
it from...
regards,
Dimitris
Based on your other post I'm guessing it is related to the trace files
you're feeding to BPA. Trace files should be files generated by SQL Server
profiler, not .sql files.
Let's try two things to isolate the errors you're seeing:
1) clear any specified Trace Files from the servers registered. Try to run
BPA. I'm guessing things should succeed.
2) Try with a .trc file generated by Profiler.
If this doesn't help, search for LOG folder under BPA installation folder
and post the log files.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dimitris" <dimitris@.microsoft.com> wrote in message
news:ubtp0bSTEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have downloaded and run hte analyzer, but the result I get have lots of
> exeption errors that contain the message:
> Error while executing the Best Practice Group. See the log file
> loadtraced_antodxp_0003_mshfsql_sqlbpa.log. Error message:
> the log file cannot be found anywhere on the server or in my pc that I run
> it from...
> regards,
>
|||Thanks, it worked!
As you have already noticed I have posted a trace help request, you might be
able to help:
I have created a trace template using Profiler, called locks and have
included only the locks from the events menu (deadlocks, escalation and lock
timeout). I have saved a trace file called traceLocks, but I dont know what
to do with it. Can you help?
Best regards,
Dimitris
"Christian Kleinerman [MS]" <ckleiner@.online.microsoft.com> wrote in message
news:OUaZpwXTEHA.2336@.TK2MSFTNGP10.phx.gbl...
> Dimitris
> Based on your other post I'm guessing it is related to the trace files
> you're feeding to BPA. Trace files should be files generated by SQL Server
> profiler, not .sql files.
> Let's try two things to isolate the errors you're seeing:
> 1) clear any specified Trace Files from the servers registered. Try to run
> BPA. I'm guessing things should succeed.
> 2) Try with a .trc file generated by Profiler.
> If this doesn't help, search for LOG folder under BPA installation folder
> and post the log files.
> - Christian
>
> --
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "dimitris" <dimitris@.microsoft.com> wrote in message
> news:ubtp0bSTEHA.1732@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
run
>
|||Dimitris
BPA will scan statement start and completion as well as batch start and
completion events - for those events, it will lookup the SQL text being
exeuted and will scan it for best practices.
If you only have locking events, I'm not sure you'll be able to do much with
BPA.
If you create a trace file that captures batch/statement events, you can
include the path in PBA (e.g. c:\yourpath\yourtracefile.trc) for a given
server and BPA will perform the scan.
Let me know if you have additional questions.
- Christian
"dimitris" <dimitris@.microsoft.com> wrote in message
news:OUGWzLcTEHA.904@.TK2MSFTNGP12.phx.gbl...
> Thanks, it worked!
> As you have already noticed I have posted a trace help request, you might
be
> able to help:
> I have created a trace template using Profiler, called locks and have
> included only the locks from the events menu (deadlocks, escalation and
lock
> timeout). I have saved a trace file called traceLocks, but I dont know
what
> to do with it. Can you help?
> Best regards,
> Dimitris
> "Christian Kleinerman [MS]" <ckleiner@.online.microsoft.com> wrote in
message[vbcol=seagreen]
> news:OUaZpwXTEHA.2336@.TK2MSFTNGP10.phx.gbl...
Server[vbcol=seagreen]
run[vbcol=seagreen]
folder
> rights.
> of
> run
>

Best practice analyer

what login permissions should I have to run best practice
analyser tool? DO I have to be a sql server sysadmin?
I use mixed mode.
Some rules (e.g. syntax analysis, queries on the system catalog) will
succeed without sysadmin privileges, but some others (e.g. those that use
sp_configure) will require sysadmin privileges.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ann" <anonymous@.discussions.microsoft.com> wrote in message
news:796e01c4950f$0b640990$a301280a@.phx.gbl...
> what login permissions should I have to run best practice
> analyser tool? DO I have to be a sql server sysadmin?
> I use mixed mode.

best practice connecting to run database creation script

I want to run a database creation sql script. The problem (I believe) is
that I have to specify the database I wish to connect to when connecting to
the server (ADO). Should I specify the Main database? Or is there a way
around this?
You can use master for the initial catalog in your
connection string.
-Sue
On Mon, 7 Nov 2005 13:01:03 -0800, "William Sullivan"
<WilliamSullivan@.discussions.microsoft.com> wrote:

>I want to run a database creation sql script. The problem (I believe) is
>that I have to specify the database I wish to connect to when connecting to
>the server (ADO). Should I specify the Main database? Or is there a way
>around this?

best practice connecting to run database creation script

I want to run a database creation sql script. The problem (I believe) is
that I have to specify the database I wish to connect to when connecting to
the server (ADO). Should I specify the Main database? Or is there a way
around this?You can use master for the initial catalog in your
connection string.
-Sue
On Mon, 7 Nov 2005 13:01:03 -0800, "William Sullivan"
<WilliamSullivan@.discussions.microsoft.com> wrote:

>I want to run a database creation sql script. The problem (I believe) is
>that I have to specify the database I wish to connect to when connecting to
>the server (ADO). Should I specify the Main database? Or is there a way
>around this?

Best practice

i have two databases on two different servers, one which is a live server.
What i want to do is run a query to update a table in the second server wit
h
records from the first database. would this be a select if not exists ?It can be an insert followed by a subquery which is based on a NOT exists, a
ssuming that you want to
add the rows that doesn't exist (based on some key column).
Or, it can be an update based on a JOIN (or an update with a number of corre
lated subqueries in SET,
as well as an EXISTS), if you want to update rows that already exists in the
other table, picking
column values from that other table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:B13DDBFC-4697-4DA6-B9C0-5F7A575BC8DF@.microsoft.com...
>i have two databases on two different servers, one which is a live server.
> What i want to do is run a query to update a table in the second server w
ith
> records from the first database. would this be a select if not exists ?
>

Best Practice

We have a dedicated web server that runs SQL it is not hosted on our network
it is hosted by an ISP. We also run an inhouse SQL server. We need to get
info back and forth between the two DB's. What would be the best way to do
this securely.
Thank youA Scheduled DTS task. But, your table design needs to be able to handle such
a model.
You need a way to identify changed rows and a way to resolve them is they
were both changed between synchronization.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:4D58A22F-4269-4365-861C-DA7E202EFA04@.microsoft.com...
> We have a dedicated web server that runs SQL it is not hosted on our
> network
> it is hosted by an ISP. We also run an inhouse SQL server. We need to get
> info back and forth between the two DB's. What would be the best way to do
> this securely.
> Thank you|||Thank you for your reply one other question. Would the DTS package be secure
?
"Mike Epprecht (SQL MVP)" wrote:

> A Scheduled DTS task. But, your table design needs to be able to handle su
ch
> a model.
> You need a way to identify changed rows and a way to resolve them is they
> were both changed between synchronization.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:4D58A22F-4269-4365-861C-DA7E202EFA04@.microsoft.com...
>
>

Best Enterprise Datawarehouse

We currently run a MASSIVE enterprise data warehouse on SQL Server 2000 and it is struggling a bit with both user queries and daily loads.
Need opinions on
1. Is SQL Server the best product for the job
2. What are the alternatives
CheersWhat is massive ? Also, what is your hardware specs ?|||And what are you doing with it? Are you creating cubes off of it?|||Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software|||What type of cubes ? What version of sql are you using ?|||What window do you have available to perform the daily loads? Approx how long does each load take?|||At that size I am sure MS would love to help you (and use you in their marketing :) )|||You can easily deploy SQL Analaysis Services in this case, check ver 2000 for more enhancements and information on MS SQL homepage.

HTH|||No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!|||Originally posted by aldo_2003
Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software

Gotta ask...what's the subject of the data?

3rd party software?

I don't think (damn it, again) that ANY 3rd party software plans for anything that massive...

And EVERYONE is writting adhoc queries...right?

"This sounds like a job for "Super-Silver bullet""

And yes, MS would love to talk to you....|||Originally posted by aldo_2003
No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!

Be happy using Sybase IQ (http://www.sybase.com/products/bi/sybaseiq) !
=> T-SQL compliance
=> DSS database
=> performance++|||You have given very few information regarding your environment and the reasons you are struggling, other blaming SQL. We would be glad to help you if you could tell us more about your data warehouse. So we can see where your bottleneck is. But if you are looking for the answer of 'Yes, you need to switch to O.... and S.. platform', please go to O.... forum. Thanks.

Best approach for creating SQL Server 2005 reports to run against SQL 2000 database engine

We have a client who is running SQL Server 2000. They have Business Objects reports. We want to create those same reports using SQL Reporting Services and get rid of Business Objects. The trick is, we'd prefer to create them in SQL Server 2005 with VS 2005, so that we have the reports created with the newest version rather than having to use the older, less functional version to create them. What are our options here? Can we create the reports in 2005 and somehow run the reports against a 2000 database without making our client have to install a SQL Server 2005 instance?
Or will we need to create the reports in SQL Server 2000? Are there other combinations that would work where we can still create them in 2005 but run against 2000?
I'm sure they will upgrade eventually, but I'd rather not rush them at this time.
Thanks much!I think I didn′t get you right, so here are two explanations to your problem:

You can run reports against every supported datasource, this does not have to be SQL Server, it can either be Oracle, a text file, a Webservice or any other source. You can use the out-of-the-box functionality and sources or you can create your own custom provider which leaves with unlimited variety for sources. The Report Server has to be installed on a SQL Server 2005 Server. Therefore you need a SQL Server 2005 licence. If you have one of these you can also install a SQL Server 2005 database. If the clients don′t have a SQL Server 2005 licences you will be stuck to SQL Server Express Advanced Edition, which is capable to distribute it as you want but will only have access to local SQL Server database sources.

So I hope I hit on of your problems withthat answer :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks - I think that does answer my question. My customer only has SQL Server 2000 right now, so it seems I will need to build the reports in SQL Server 2000 using Visual Studio 2003 as the IDE. In our development shop we have moved on to VS 2005, so I am assuming that for new clients we can upgrade our reports to 2005 without too much effort. If you know of any issues about upgrading reports from SQL Server 2000 reporting services to SQL Server 2005, please let me know.

Thanks much!

Beginner looking for help

Hi All,

I'm not very good at SQL but I want to finish my ASP project and have run into a little problem. I have a variable that I formatted represent a nice comma delimited character list with all "words" in single quotes ('A','B','C'). I was going to pass this variable to a SELECT statement -

SELECT col FROM table WHERE item IN (<my variable>)

Am I doing this right?

Thanks!

BI don't think this will work. The IN clause will try to match all values of column ITEM with the complete string that your variable represents. There are a lot of ways to handle this, one would be to use dynamic SQL. What database are you working with ?

Another would be to use specific functions that allow to search for a character (pattern) within another string. Again, the solution will depend on your database.|||Originally posted by cvandemaele
I don't think this will work. The IN clause will try to match all values of column ITEM with the complete string that your variable represents. There are a lot of ways to handle this, one would be to use dynamic SQL. What database are you working with ?

Another would be to use specific functions that allow to search for a character (pattern) within another string. Again, the solution will depend on your database.

Thanks. I learned the dynamic SQL way last night shortly after posing this.

I appreciate your resonse!

Beginner help stored procedures, also .net

Hi,
I'm not qualified enough to be called a beginner -- I could use some help.
I've written a few fairly simple queries and run them through the query
analyzer. I'd like to know 2 things:
How can I turn them into stored procedures?
How would I then run those stored procedures through vb.net?
Any help would be appreciated.
Thanks,
ArtArt
CREATE PROCEDURE myFirstSp
@.par INT
AS
SELECT OrderDate FROM Orders WHERE OrderId=@.par
--usage
EXEC myFirstSp @.par=10248
You will find more info about stored procedures in the BOL.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art|||You can find samples and tutorials on MSDN.com and ASP.net
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art

Begining Queries

I am a novice SQL guy so you all take it easy on me.
I am trying to run a query and one of the columns of my has values that is
referenced in another table, i.e. for EmployedStatus I have a bunch a
numbers from aother table. I go to that table to see what the numbers mean
and there you go. What I would like to do is, say I am working with person
profile table and instead of showing the column "EmployedStatus" as a 63
(for example) for a record, I would like it to show what that 63 means from
the "desription" column of the table that the 63 is referenced to. I hope
that make sense and I would appreciate any help anyone can stand to stomach!
Thanks,
Aaron
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers mean
> and there you go. What I would like to do is, say I am working with person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to stomach!
> Thanks,
> Aaron
>
|||Thanks Arnie for the info, worked great.
If I could ask one more; How would I modify this code to to be able to
reference more than one field that uses the same table? For example to
continue with our example, I have a an EmployedStatusID that is referenced
by a number in an ID Table and it corresponding Description of Full-Time,
Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
in the same ID table with it's corresponding Description as Full-Time,
Half-Time, etc.. I tried to use the AND along with the Join and when I
added more than one, no data was displayed. If I run separate queries on
both, they show up correctly. Any suggestions?
Thanks again,
Aaron
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex
set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this
code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message
news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers
> mean
> and there you go. What I would like to do is, say I am working with
> person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means
> from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to
> stomach!
> Thanks,
> Aaron
>
|||Aaron,
As you noticed, trying to retrieve fields from a JOIN table where the JOIN criteria is different doesn't work.
So there are a couple of different ways to make this work. One involves using a JOIN with a second virtual copy of the table, and the other involves using a sub-SELECT.
First, the JOIN using a second copy of the same table, and then the sub-select.
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int,
StudentStatus int
)
CREATE TABLE Status
( StatusID int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63, 23 )
INSERT INTO Status VALUES ( 63, 'On Leave' )
INSERT INTO Status VALUES ( 21, 'Full Time' )
INSERT INTO Status VALUES ( 21, 'Half Time' )
INSERT INTO Status VALUES ( 23, 'Part Time' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = s1.Description,
StudentStatus = s2.Description
FROM PersonProfile p
JOIN Status s1
ON p.EmployedStatus = s1.StatusID
JOIN Status s2
ON p.StudentStatus = s2.StatusID
--Use a Sub-SELECT
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.EmployedStatus
),
StudentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.StudentStatus
)
FROM PersonProfile p
--Clean up
DROP TABLE PersonProfile
DROP TABLE Status
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:129A1BD8-33A4-40D8-8B41-BC3A33BE3AEC@.microsoft.com...[vbcol=seagreen]
> Thanks Arnie for the info, worked great.
> If I could ask one more; How would I modify this code to to be able to
> reference more than one field that uses the same table? For example to
> continue with our example, I have a an EmployedStatusID that is referenced
> by a number in an ID Table and it corresponding Description of Full-Time,
> Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
> in the same ID table with it's corresponding Description as Full-Time,
> Half-Time, etc.. I tried to use the AND along with the Join and when I
> added more than one, no data was displayed. If I run separate queries on
> both, they show up correctly. Any suggestions?
> Thanks again,
> Aaron
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
> It's ok Aaron, we all started out somewhere.
> For reference, look in Books Online for the topic JOIN TABLES -its a complex
> set of topics and takes most of us a lot of practice to get it right.
> Now about your problem. I'll create an example. (You can copy and paste this
> code into Query Analyzer and run it.)
> SET NOCOUNT ON
> CREATE TABLE PersonProfile
> ( EmployeeID int IDENTITY,
> LastName varchar(20),
> FirstName varchar(20),
> EmployedStatus int
> )
> CREATE TABLE TableB
> ( Status int,
> Description varchar(50)
> )
> INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
> INSERT INTO TableB VALUES ( 63, 'On Leave' )
> --Join the two tables
> SELECT
> p.EmployeeID,
> p.LastName,
> p.FirstName,
> CurrentStatus = b.Description
> FROM PersonProfile p
> JOIN TableB b
> ON p.EmployedStatus = b.Status
> --Clean up
> DROP TABLE PersonProfile
> DROP TABLE TableB
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Aaron" <support@.pinetreeit.com> wrote in message
> news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...

Beginer Problem: Is there any way to create the destination file at runtime?

I have to generate flat files from SQL Server tables. Its a backup package for few tables which will be run daily. I want the names of the destination flat files to be automatically generated at run time.

I created an expression for the Flat file destination connection and appended the date and the time to the flat file name. For today my file name is 'table02-12-2007.txt'. for tomorrow it should be 'table02-13-2007'. When I evaluate the expression it shows the correct value.

But when i run the package, I receive an error indicating - Destination file is not found. Error is because the destination file is not present at runtime and I was hoping that the package will be create the file on the fly.

Is there any way to create the destination file at runtime? Or Am I missing a step?

Any help will be greatly appreciated.

Thanks

Hi Don,

You probably need to set the DelayValidation property for the package to True. Do this by clicking on the Control Flow and hitting F4 for properties.

One issue here is the destination file for the Flat File Connection Manager does not exist - by design. You have to configure the package so it will stop looking for it - which is what occurs during validation.

Hope this helps,
Andy

|||

Thanks Andy for your quick response.

I changed the delay validation but that didnt work. There were spaces in destination path "C:\My Folder\My Files" which was not allowed.

Is there any way we can have spaces in the path or connection string for flat file?

Thanks

|||

Hi Don,

I believe you can enclose the file name in quotes.

Hope this helps,
Andy

Beginer Problem

I have to generate flat files from SQL Server tables. Its a backup package for few tables which will be run daily. I want the names of the destination flat files to be automatically generated at run time.

I created an expression for the Flat file destination connection and appended the date and the time to the flat file name. For today my file name is 'table02-12-2007.txt'. for tomorrow it should be 'table02-13-2007'. When I evaluate the expression it shows the correct value.

But when i run the package, I receive an error indicating - Destination file is not found. Error is because the destination file is not present at runtime and I was hoping that the package will be create the file on the fly.

Is there any way to create the destination file at runtime? Or Am I missing a step?

Any help will be greatly appreciated.

Thanks

Hi Don,

You probably need to set the DelayValidation property for the package to True. Do this by clicking on the Control Flow and hitting F4 for properties.

One issue here is the destination file for the Flat File Connection Manager does not exist - by design. You have to configure the package so it will stop looking for it - which is what occurs during validation.

Hope this helps,
Andy

|||

Thanks Andy for your quick response.

I changed the delay validation but that didnt work. There were spaces in destination path "C:\My Folder\My Files" which was not allowed.

Is there any way we can have spaces in the path or connection string for flat file?

Thanks

|||

Hi Don,

I believe you can enclose the file name in quotes.

Hope this helps,
Andy