Product:
Microsoft SQL server
Issue:
Can i list when all databases where backed up?
Solution:
Enter a Query;
SELECT bs.database_name, bs.backup_size, bs.backup_start_date, bs.backup_finish_date, bs.server_name, bs.user_name, bs.type, bm.physical_device_name FROM msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id
More information:
https://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/
Product:
Microsoft SQL Server
Issue:
How list users in a database?
Suggestion:
To get all users in one database;
use [database_name]
select name as username,
create_date,
modify_date,
type_desc as type,
authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'
order by username;To get all users on the SQL server;
declare @db varchar(100)
declare @user varchar(100)
declare c cursor for select name from sys.sysdatabases
open c
fetch next from c into @db
while @@fetch_status = 0
begin
print @db
exec ('use ' + @db)
declare u cursor for select name from sys.sysusers
where issqlrole <> 1 and hasdbaccess <> 0 and isntname <> 1
open u
fetch next from u into @user
while @@fetch_status = 0
begin
print @user
fetch next from u into @user
end
print '--------------------------------------------------'
close u
deallocate u
fetch next from c into @db
end
close c
deallocate c
To list the DB_OWNERS in database, use this code:
SELECT user_name(member_principal_id) FROM sys.database_role_members WHERE user_name(role_principal_id) = 'db_owner'
To get more information, you can query like this:
SELECT members.name MemberName, roles.name,roles.type_desc,members.type_desc FROM sys.database_role_members rolemem INNER JOIN sys.database_principals roles ON rolemem.role_principal_id = roles.principal_id INNER JOIN sys.database_principals members ON rolemem.member_principal_id = members.principal_id where roles.name like 'db_owner'
To find out who is sysadmin on the SQL database:
SELECT createdate,accdate, name FROM sys.syslogins WHERE sysadmin = 1
More Information:
https://stackoverflow.com/questions/2445444/how-to-get-a-list-of-users-for-all-instances-databases
https://dev.to/jsgurugit/list-users-in-sql-server-database-1k20
https://www.sqlserver-dba.com/2015/05/find-database-users-mapped-to-db_owner-role.html
https://www.mssqltips.com/sqlservertip/6828/sql-server-login-user-permissions-fn-my-permissions/
Product:
Microsoft SQL server
Issue:
How to simple move database file to other hard disk?
Suggested solution:
There are many ways to do this task, this is one of them:
Detach the database with a SQL query:
Use MASTER GO -- Set database to single user mode ALTER DATABASE adventure SET SINGLE_USER GO -- Detach the database sp_detach_db 'Adventure' GO
Copy the files with robocopy from a command prompt:
Syntax are : ROBOCOPY source destination [file [file]…] [options]
The /MOV parameter will move the file, if you leave it out – there will be a copy instead.
This will move file adventure.mdf from C to D drive.
robocopy /MOV "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" D:\Data adventure.mdf
This will move file adventure.ldf from C to L drive.
robocopy /MOV "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" L:\Log adventure.ldf
You need to put file path in ” if they contain spaces.
Attach the database with a SQL query:
USE master GO -- Attach the database sp_attach_DB 'Adventure', 'D:\data\Adventure.mdf', 'L:\log\Adventure.ldf' GO
More Information:
Robocopy does NOT like trailing \
https://dba.stackexchange.com/questions/52007/how-do-i-move-sql-server-database-files
How to Move Log File or MDF File in SQL Server? – Interview Question of the Week #208
Product:
Cognos Analytics 11.1.7
Microsoft Windows 2019 server
Issue:
I have installed all of cognos on the windows server, but only want it to act as Content Manager, in a multi server setup. What service should be running?
Solution:
Go into Cognos Configuration on your CM server.
Set all to False, except this services:
Content Manager service enabled?
Event management service enabled?
Job service enabled?
Metadata service enabled?
Monitor service enabled?
Save the configuration and restart all your CA11 servers.
More information:
https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=architecture-cognos-analytics-services
Schedule jobs in CA11 is handled by the Monitor service.
Product:
Cognos Controller 10.4.2
Cognos Analytics 11.1.7
Microsoft Windows 2016 server
Issue:
After a windows patch, no one can login to cognos controller.
Error:
An error occurred while trying to access server…
System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.IndexOutOfRangeException: Index was outside the bounds of the array….
Solution:
Login to Windows server where CA11 and Cognos Controller IIS are.
Check if you can login to Cognos Controller, after above restart of www service.
More information:
Product:
VirtualBox https://www.virtualbox.org/wiki/Downloads
Microsoft Windows 10
Issue:
How stop virtual box vms from command line before computer shutdown?
Suggestion: (this may not work)
Create text files in a c:\scripts folder with the command you need to start and stop vms.
Important: that case of the vms name matched the name you have given them inside Virtualbox.
Create a CMD file with following command:
rem stop one virtualbox image "C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" list runningvms "C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" controlvm "win2012big" acpipowerbutton "C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" controlvm "Win2016 CAPA" acpipowerbutton
Add the file to group policy on the computer:
Add the start script (c:/scripts/vboxstart.cmd) to the Start properties.
Create a CMD file with following command to start:
rem start one virtualbox image "C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" startvm "Win2016 CAPA" "C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" startvm "Win2019"
You may need to use the Task Scheduler instead, to make it work.
Or try placing a shortcut of the script as a startup program in %APPDATA%\Microsoft\Windows\Start Menu\Programs\Startup.
Or place a shortcut to your start script in folder C:\ProgramData\Microsoft\Windows\Start Menu\Programs\StartUp – that make the script run when user login.
More Information:
https://simplecodesoftware.com/articles/how-to-set-up-group-policy-scripts-programmatically
https://www.windowscentral.com/how-create-automated-task-using-task-scheduler-windows-10
https://4sysops.com/archives/managing-virtualbox-vms-with-powershell/
http://desertpenguin.org/blog/nnvirtualboxpowershellmodul.html
https://github.com/SmithersTheOracle/VirtualBoxPS
Product:
Cognos Controller 10.4.2
Microsoft Windows 2016 server
Issue:
Working in a excel file with fgetval formulas, but even do – i is logged into Cognos Controller, the cell show “You have not logged in”.
Solution:
Check if it will update the values if you press F9.
Otherwise check your formulas carefully in your spreadsheet, as if they are wrong you will get this error.
If it is only one spreadsheet that does not work, it is probably a misspelled formula in that sheet.
More information:
Product:
Cognos Controller 10.4.2
Cognos Analytics 11.1.7
Microsoft Windows 2019 server
Issue:
When login to Cognos Controller, you get a error, after you select the database. This is a multi-server setup with load balanced Controller and CA11 servers. SSO with Active Directory is used.
Error message:
or
System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.NullReferenceException: Object reference not set to an instance of an object.
at Cognos.Controller.Common.CRNBridge.CRNBridge.ValidateUser(String passportId, String[]& membership, String sGuid, String sUser, String& sErrorMessage)….
System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()
— End of inner exception stack trace —
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at CCRRemoteServerProxy.Proxy.Light.WSLight.UserManager_GetCurrentUsers(String sGuid, String sUser)
at FrUserManagerB.RemoteUserManager.GetCurrentUsers…..
or
System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.NullReferenceException: Object reference not set to an instance of an object.
at Cognos.Controller.Proxy.ControllerReportNetService.RepNet.GetUserInfo(String sGuid, String sUser, String passportId)
at Cognos.Controller.Proxy.CCRWS.GetUserInfo(String sGuid, String sUser, String passportId)
— End of inner exception stack trace —
at Cognos.Controller.Forms.Common.Main.DoLoginCognos8(Form& frm)
at CCR.AppContext.DoLogin()
at CCR.AppContext.Login()
Suggested Solution:
Check that the Cognos Gateway point to the correct Cognos Dispatcher or correct port in the IIS setup.
Start Internet Information Services (IIS) manager on the Cognos Gateway server.
Click on the Server name and double-click the Configuration Editor icon.
Expand the drop-down list and select webFarms.
Mark Collection and click on the ellipses.
Click on the webFarm element and then the ellipses.
Select the server you want to check, expand the applicationRequestRouting, and see the port number.
Edit the number, if it is wrong. Check the other servers in the list.
Close the dialog.
Close the collection editor dialog to.
Click Apply in the Configuration Editor.
Close IIS manager.
You can also check the values in applicationHost.config file, under C:\Windows\System32\inetsrv\Config folder.
Open the file in Notepad++
The lines for webfarms is at the end of the file.
How the communication probably works;
Check that the ccr.exe.config files WSSURL value point to a Cognos Controller server that is up;
<add key=”WSSUrl” value=”http://controllerservername.domain.com/ibmcognos/controllerserver”/>
On that server; Ensure that Cognos Controller Configuration points to a report server gateway that is up;
http://caservername.domain.com/ibmcognos/bi/v1/disp
On that server; ensure that Cognos Analytics Configuration Dispatchers URI for gateway point to a CA dispatcher (report) server that is up;
http://caservername.domain.com:9300/bi/v1/disp
But also check the IIS server farm, that they point to CA servers that is up and that they use the correct port:9300
The CA11 server cognos configuration, will use the Content Manager URI, to validate that you can login;
http://caservername.domain.com:9300/p2pd/servlet
But also check that the IIS site /ibmcognos/sso have Windows Authentication enabled.
If the IBM Cognos service is not running, then the IIS Server Farm will classify it as unhealthy.
If the dispatcher server is unhealthy, then the CA gateway will not send the request there, and it will time out.
Check that the Windows firewall will allow port 8173 for the IIS communication and port 9300 for Cognos communication.
More information:
https://serverfault.com/questions/132081/load-balancing-with-arr-iis-on-ports-other-than-80
https://www.ibm.com/support/pages/invalid-login-response-error-when-accessing-cognos-analytics-sso
https://www.ibm.com/support/pages/enabling-single-sign-cognos-secured-against-active-directory
https://docuri.com/download/the-active-directory-story_59c1d487f581710b2865bb0f_pdf
In the cognosserver.log file you can see error like this:
ERROR com.ibm.bi.platform.commons.web.BIResponseWrapper [Default Executor-thread-72] NA bi HttpServletResponseCode for /bi/v1/ping is in error: 503
Browse to http://caservername.domain.com:9300/p2pd/servlet to check if the content manager is running.
Product:
Cognos Controller 10.4.2
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2000.1063-0
Microsoft Windows 2019 server
Issue:
You try to unlock a company in Cognos Controller. You get a error message:
“Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression”
Workaround:
Delete the entire contents of the temporary table ‘sactrlinfo’.
Delete FROM [CCR_UKFPM].[fastnet].[sactrlinfo]
or
Disable the system audit log functionality entirely.
Go to Maintain – System Audit Log – Configuration and deselect:
“Enable tracking of structure change details for System Audit Log > Structures”
“Enable tracking of data change details for Audit Trail”
More information:
https://www.ibm.com/support/pages/node/883764
https://www.ibm.com/support/pages/node/375041
https://www.ibm.com/support/pages/node/960392
Product:
Cognos Controller 10.4.2
Microsoft Windows 10
Issue:
How install Cognos Controller client on my laptop?
Solution:
Find your CCRLocalClient64.msi file from a file share on the server. Recommended to copy the msi file first to your hard-disk and place it in a folder e.g. c:\temp.
Right click on msi file and select Install.
Click Next
Enter the name of your controller server, instead of localhost and press Next.
You should only update the WSS URL line, to something like this:
http://servername.domain.com/ibmcognos/controllerserver
Press next
Press install
If you get a question about allow Cognos Controller to make changes to your computer, click Yes.
Click Finish.
Click on the IBM Cognos Controller icon on the Windows start menu, to start the program.
Select your database to login.
Click on the Excel icon to start your excel session. Can be good, to do this before you go to Data Entry.
Under Help – System Info – menu, can you see the version you have installed.
https://www.ibm.com/support/pages/cognos-controller-builds-ccr-name-and-database-version
If you go to Control Panel – Programs and Features, you will see the program installed.
From the control panel you can uninstall Cognos Controller, this you need to do before upgrade to a new version.
You need to have NET Framework 4.7.2 on your laptop for best performance;
More Information:
https://www.ibm.com/support/pages/how-install-controller-local-client
Inside Microsoft Excel, go to File – Options – Add-ins and check that Controller add ins are active.
https://www.ibm.com/support/pages/controller-excel-link-disappears-after-excel-crashes