fannonland2 (3/2/2011)
I appreciate you taking the time to talk me through this. I am not a DBA by any stretch but I am trying to convince the DBA's that scom can do this and obviously they aren't interested in helping me. So, I do this on my own. So I have gotten to the point where I created a new database called, "Analysis" on the scom server. This sits right alongside the OperationsmangerDW and ReportServer databases. I ran the scripts, it created tables, I copied and pasted the PRC files and ran those. Had a little problem because I hadn't created a database role called "ROLE_UID". So I did and made it a dbowner. Now, I am going back to change the scripts in SCOM.I get this...
sAnalysisInstance = "SCOMP001\SCOMSQL"
sAnalysisDB = "Analysis"
But I don't get this....
sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"
What do I need to change in this line???
sAnalysisInstance and sAnalysisDB take their values from the variable definitions so you don't have to worry about changing those.
UID: this is a server login and database user on Analysis db that has execute permissions on stored procedure SCOM_InsertBlockingInfo.
This procedure is installed on Analysis db.
See below code excerpt to get an idea of what I am talking about:
sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"
cnAnalysisInstance.Open sConnString
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnAnalysisInstance
cmd.CommandText = "[dbo].[SCOM_InsertBlockingInfo]"
cmd.CommandType = 4 'adCmdStoredProc
In my case I have a login, ssrs, mapped to a database role, ROLE_ssrs, in the Analysis db.
ROLE_ssrs has EXECUTE permissions on procedures SCOM_InsertBlockingInfo and SCOM_InsertBlockingInfo_2000.
Then the line you were asking about could read like this:
sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=ssrs;PWD=insert-complex-passwd"
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]