DX Unified Infrastructure Management

Expand all | Collapse all

sqlserver probe - an object named '#temptab' in the database

  • 1.  sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 18, 2018 01:28 PM

    I am getting the following error during the sql server monitoring. It appears like the temporary table used in query already exist in the database. I reviewed other communication posts which is referenceing to permission issues. Also Case number 00731668 has full query to collect the required data for this metric. 

     

    Profile: da1w-agsql-t1_test_profile/fg_freeSpace_with_avail_disk/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named '#temptab' in the database.

     

     

    Can anyone help me with corrective steps and / or some workaround here?

     

    Thanks

    Balkar



  • 2.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 18, 2018 01:43 PM

    I would disable the probe and drop the temptab then activate the probe.

    make sure you do not have two probes pointing to the same database.



  • 3.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 18, 2018 04:57 PM

    Gene,

     

    I tried the sqlserver probe on two different servers and both instances of the sqlserver probe are generating the similar alarms. I am afraid if this fix needs to be applied on 100s of servers when we go production.

     

    I am not sure if this is something related to the permissions per the following post.

     

    Permissions for SQL Server Probe 



  • 4.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 19, 2018 09:19 AM

    have you reviewed the permissions for the user you are connecting with per the documentation with your DBA.

    sqlserver (SQL Server Monitoring) Release Notes - CA Unified Infrastructure Management Probes - CA Technologies Document… 

     

    If you test with SA and it works then it is definitely a permissions issue and you will need to review the user setup to correct.



  • 5.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 19, 2018 09:46 AM

    The same article was share with customer to setup the permissions for the user account and customer has configured the user and confirmed. 

     

    Any other suggestions for permission which might need to try. Testing with SA may not be an option.



  • 6.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 19, 2018 10:10 AM

    The DBA will need to find the root cause at this point.

    Testing with SA or SA equivalent will be the fastest way to find the root cause.

    I am 99% sure this is a rights issue.



  • 7.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 19, 2018 10:45 AM

    Here is the another reference from other metric. it appears like permission issue here as well.

    Profile: _test_profile/logfile_usage_with_avail_disk/Run Query - DB Provider: Code=0x0x80004005 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][ODBC SQL Server Driver][SQL Server]The server principal "customer\Monitor-xx-LSA-SVC" is not able to access the database "Test_AG" under the current security context.



  • 8.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 19, 2018 10:45 AM

    I have requested customer for a working session with dba.



  • 9.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 19, 2018 04:07 PM

    We tested with SA access and both alarms are not appearing at this time. It appears like a permission issue. The follow-on question would be what permissions are missing in the documentation and how to identify these permission at this time.

     

    Thanks

    Balkar 



  • 10.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 19, 2018 10:31 PM

    https://comm.support.ca.com/kb/granting-sqlserver-probe-permissions/kb000057266

     

    https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes#sqlserver(SQLServerMonitoring)ReleaseNotes-AccessPrerequisites

     

    For Windows authentication, perform the following:

    ensure that the user has access to the system hosting the monitored SQL Server.
    provide access rights to Niscache folder of the file system on which the CA UIM robot is installed.
    ensure that the user has local logon rights on the system where the sqlserver probe is installed.
    (From version 5.00) Grant the following permission to a non-SA user to access AlwaysOn tables in the database:

    USE master;
    GRANT VIEW DEFINITION ON AVAILABILITY GROUP::<group name> TO <username>;

    GO



  • 11.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 20, 2018 02:05 PM

    Customer has confirmed all the permissions, but after removing the SA level access we have started seeing the same errors.

     

    still not sure what permissions are missing in the documentation.

     

    Please help.



  • 12.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 21, 2018 09:11 AM

    Gene,

     

    Any further thoughts about the missing permissions or should I open a support case?

     

    Thanks

    Balkar 



  • 13.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jun 21, 2018 09:19 AM

    I would suggest you open a support case to look into this further.

    this could be a version issue or setup issue for the user...

    This will probably require a webex with the client to find out what is not set correctly or what needs to be changed.

     

    The DBA should be able to look at the requests coming in and adjust the rights as  needed.



  • 14.  Re: sqlserver probe - an object named '#temptab' in the database

    Broadcom Employee
    Posted Jul 16, 2018 06:06 PM

    Customer is trying to collect data from user databases which doesn't provide enough access to the monitoring user. They are looking into this issue either provide required access or remove those metrics.