How to Check When SQL Server was installed?

Leave a reply

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

Script to Update Statistics by passing database name

Leave a reply

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

4 Replies

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()

Leave a reply

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