aiDeeva.com

Author: Jugal Shah

  • How to Check When SQL Server was installed?

    NT AUTHORITY\SYSTEM login gets created when we install SQL Server. By querying sys.syslogins, we would get an idea of the SQL Server Installation date time as per the creation date time of NT AUTHORITY\SYSTEM.

    SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000

    SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000

    SELECT createdate AS sql_server_install_date FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000

SELECT * FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000

  • Dos Command to Check System Properties and Server Reboot time

    Many times we would like to check OS information and boot time. We can check the  operating system properties and reboot time by executing systeminfo command from dos prompt.

    C:\>systeminfo

  • Script to Update Statistics by passing database name

    You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

    You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

  • Resolving could not open a connection to SQL Server errors

    Problem

    Sometimes you may have issues connecting to SQL Server and you may get messages such as the following:

    ERROR: (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

    Or

    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

    Or

    A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No such host is known.) (Microsoft SQL Server, Error: 11001)

    http://www.mssqltips.com/tip.asp?tip=2340

    Click Me for Solution

  • Msg 15009, Level 16, State 1, Procedure sp_help, sp_helptext or object_definition()

    Whenever user has in-sufficient permission and he is executing sp_help, sp_helptext or the object_definition function, user will get the below error message. This error occurs because user doesn’t have have permissions to the see the object metadata.

    EXEC sp_helptext sp_indexdefrag
    Msg 15009, Level 16, State 1, Procedure sp_helptext

    To fix the issue we have to grant the VIEW DEFINITION permission to user.
    We can turn on the View Definition permission on all databases for all the users having public role using below code.

    USE master
    GO
    GRANT VIEW ANY DEFINITION TO PUBLIC

    We can turn on the View Definition permission on all databases for the user Jugal using below query.

    USE master
    GO
    GRANT VIEW ANY DEFINITION TO Jugal

    We can turn on the View Definition permission on SQLDBPOOL for the user Jugal using below query.

    USE SQLDBPOOL
    GO
    GRANT VIEW DEFINITION TO Jugal