Archive

Posts Tagged ‘SQLServer 2005’

SQL Server – Different Ways to Check Object Definition

14.04.2014 2 comments

sp_helptext is widely used for checking object definition in SQL Server. sp_helptext can be used to check definition of various database objects like Views, Stored Procedures and User Defined Functions.

There are two other options which can be used to retrieve object definition:

OBJECT_DEFINITION( object_id ) – is a built-in function. It can also retrieve definitions of CHECK/DEFAULT constraints

sys.sql_modules – is a catalog view which returns definitions of all modules in current database

Each of these can be used as follows:


USE [SqlAndMe]
GO

sp_helptext 'MyProcedure'
GO

-- Use OBJECT_ID() function to get object id
SELECT    OBJECT_DEFINITION(OBJECT_ID('MyProcedure'))
GO

-- Use OBJECT_ID() function to get object id
SELECT    [definition]
FROM    sys.sql_modules
WHERE    object_id = OBJECT_ID('MyProcedure')
GO

OBJECT_DEFINITION(object_id) and sys.sql_modules returns results as a single-line when in “Results to Grid” (Ctrl + D) mode. Switch to “Results to Text” (Ctrl + T) for formatted output which will include line breaks.

Hope This Helps!

Vishal

SQL Server – How to get last access/update time for a table

11.03.2014 2 comments

Modify date and create date for a table can be retrieved from sys.tables catalog view. When any structural changes are made the modify date is updated. It can be queried as follows:


USE [SqlAndMe]
GO

SELECT    [TableName] = name,
create_date,
modify_date
FROM    sys.tables
WHERE    name = 'TransactionHistoryArchive'
GO

 

 

sys.tables only shows modify date for structural changes. If we need to check when was the tables last updated or accessed, we can use dynamic management view sys.dm_db_index_usage_stats. This DMV returns counts of different types of index operations and last time the operation was performed.

It can be used as follows:


USE [SqlAndMe]
GO

SELECT    [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM    sys.dm_db_index_usage_stats
WHERE    database_id = DB_ID('SqlAndMe')
AND        OBJECT_NAME(object_id) = 'TransactionHistoryArchive'
GO

 

 

last_user_update – provides time of last user update

last_user_* – provides time of last scan/seek/lookup

It is important to note that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.

 

Hope This Helps!

Vishal

SQL Server – How to Move Table to Another Schema

13.05.2013 1 comment

Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.

For example, if I create a table using below script it will be created under my default schema which is dbo:

USE [SqlAndMe]

GO

 

CREATE TABLE Employee

(

       ID     INT,

       Name VARCHAR(20)

)

GO

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      dbo

 

(1 row(s) affected)

As you can see from the output the table is currently in dbo schema.

Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.

USE [SqlAndMe]

GO

 

CREATE SCHEMA HumanResources

GO

 

ALTER SCHEMA HumanResources

TRANSFER dbo.Employee

GO

 

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      HumanResources

 

(1 row(s) affected)

As you can see from the output the Employee table is now moved to HumanResources schema.

 

Hope This Helps!

Vishal

SQL Server – How to find Default data and log path for SQL Server 2012

06.05.2013 No comments

To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.

It can be used as below:

SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')

SELECT [Default Log Path]  = SERVERPROPERTY('InstanceDefaultLogPath')

GO

Result Set:

Default Data Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

 

Default Log Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

You can also get this information from Server Properties dialog box in SQL Server Management Studio.

To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.

 

Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.

Hope This Helps!

Vishal

SQL Server – Finding TCP Port Number SQL Instance is Listening on

01.05.2013 4 comments

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.

Here are a few methods which we can use to get this information.

Method 1: SQL Server Configuration Manager

Method 2: Windows Event Viewer

Method 3: SQL Server Error Logs

Method 4: sys.dm_exec_connections DMV

Method 5: Reading registry using xp_instance_regread

 


Let's see how you can use each of these methods in detail:

Method 1: SQL Server Configuration Manager:

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <Instance Name>

Step 3. Right Click on TCP/IP and select Properties

image

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

image

If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.

 


Method 2: Windows Event Viewer:

When SQL Server is started it logs an event message as 'Server is listening on [ 'any' <ipv4> <port number>' in windows event logs. Here <port number> will be actual port number on which SQL Server is listening.

To view this using Event Viewer:

Step 1. Click Start > Administrative Tools > Event Viewer.

Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs

Step 2. Navigate to Event Viewer > Windows Logs > Application

Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log…

image

Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it’s source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.

image

Step 5. Once the filter is applied, Locate message 'Server is listening on [ 'any' <ipv4> …'. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.

image

 


Method 3: SQL Server Error Logs:

When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'

GO

Result Set:

LogDate                  ProcessInfo Text

2013-03-21 13:34:40.610  spid18s     Server is listening on [ ‘any’ <ipv4> 61499].

2013-03-21 13:34:40.610  spid18s     Server is listening on [ ‘any’ <ipv6> 61499].

 

(2 row(s) affected)

As we can see from the output that SQL Server Instance is listening on 61499.

Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.

 


Method 4: sys.dm_exec_connections DMV:

DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port

FROM   sys.dm_exec_connections

WHERE  session_id = @@SPID

GO

Result Set:

local_tcp_port

61499

 

(1 row(s) affected)

As we can see from the output… same as above Smile

 


Method 5: Reading registry using xp_instance_regread:

Port number can also be retrieved from Windows Registry database.

We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:

DECLARE       @portNumber   NVARCHAR(10)

 

EXEC   xp_instance_regread

@rootkey    = 'HKEY_LOCAL_MACHINE',

@key        =

'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',

@value_name = 'TcpDynamicPorts',

@value      = @portNumber OUTPUT

 

SELECT [Port Number] = @portNumber

GO

Result Set:

Port Number

61499

 

(1 row(s) affected)

As we can see … same as above Smile Smile

Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = 'TcpPort' as opposed to @value_name = 'TcpDynamicPorts'.

Hope This Helps!

Vishal

Categories: Management Views and Functions, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions Tags: 2008 R2, application event logs, check sql port, check sql server port no, configuration manager, Database, dmv, dm_exec_connextions, error logs, errorlos, event id, event id 26022, event viewer, find sql server port, finding the port number for a particular sql server instance, how to check the port number in sql server, how to determine whar sql server tcp ip port is being used, how to find port for sql, how to find sql runnig port, how to find sql server runnig port, how to find the port for ms sql server 2005, how to find the port for ms sql server 2008, how to find the port for ms sql server 2008 r2, how to find the port for ms sql server 2012, how to know sql server port number, identify sql server port, identify sql server port being used, identify tcp ip port, identify tcp ip port sql server, instance specific registry, ipall, key, local tcp port @@spid, local_tcp_port, logdate, Management Studio, processinfo, protocols for sql sevrer, Query, read registry tsql, rootkey, scripts, server is listening on, session_id, sp cycle error log, spid, sp_cycle_errorlog, SQL, Sql & Me, SQL 2005, sql 2005 port, SQL 2008, sql 2008 port, SQL 2008 R2, sql 2008 r2 port, sql 2012, sql 2012 port, Sql And Me, sql events, sql registry read reading registry using tsql reading registry using sql server, SQL Server, SQL Server 2005, sql server 2005 how to check port, SQL Server 2008, sql server 2008 how to check port, SQL Server 2008 R2, sql server 2008 r2 how to check port, sql server 2012, sql server 2012 how to check port, sql server change port, sql server configuration manager, sql server default port, sql server determine port, sql server error logs, sql server instance port, sql server listening port, sql server named instance port, sql server where can i find the tcp port configured, Sql&Me, SqlAndMe, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, SSMS, supersocketnetlib, sys.dm_exec_connections, tcp, tcp dynamic ports, tcp port, tcp/ip port number, tcpip port, TSQL, value, value_name, Vishal Gajjar, what tcp port is sql server running under, windows logs application, xp instance regread, xp read error log, xp regread, xp_instance_regread, xp_readerrorlog, xp_regread

SQL Server – How to check if you are running 32-bit or 64-bit version

22.04.2013 No comments

There are two different command you can use to check if you are running 32-bit or 64-bit version of SQL Server.

Using @@VERSION:

You can user @@VERSION system variable to determine edition and architecture of SQL Server as below:

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (Intel X86)

       Oct 19 2012 13:43:21

       Copyright (c) Microsoft Corporation

       Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

 

 

(1 row(s) affected)

This command returns both SQL Server and Operating system information. In the first line you get information about SQL Server, it's version and architecture. Here, x86 = 32-bit architecture and x64 = 64-bit architecture. Same it true for operating system.

 

Using SERVERPROPERTY():

We can also user SERVERPROPERTY() function to get information about SQL Server Edition and architecture.

SELECT SERVERPROPERTY('Edition')

Result Set:

Developer Edition

 

(1 row(s) affected)

From the output we can see that it's a Developer Edition instance and architecture is 32-bit. If it is 64-bit then '(64-bit)' will be appended to edition. i.e. For 64-bit the output will be 'Developer Edition (64-bit)'

Hope This Helps!

Vishal

SQL Server – "Denali" – Analytic Functions – LAG() and LEAD()

28.07.2011 No comments

LAG() function can be used to access data from a previous row in the result set without using a self-join. And it’s counterpart LEAD() can be used to access data from a subsequent row in the same result set. These functions are introduced in “Denali” as T-SQL functions, these has been available as MDX functions since SQL Server 2005.

LAG() and LEAD() functions are also available in Oracle since Oracle 8i.

Syntax for LAG()/LEAD():

LAG/LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

offset provides the numbers of rows back or forward the current row. default provides a default value when the scalar_expression returned by offset is NULL.

Below example uses LEG() and LEAD() to identify previous or next date for a particular order.

CREATE TABLE #Orders

(

       OrderDate     DATE,

       ProductID     INT,

       Quantity      INT

)

INSERT INTO #Orders VALUES

('2011-07-28',11,12), ('2011-03-18',12,74), ('2011-04-12',13,95),

('2011-07-25',14,57), ('2011-05-30',11,28), ('2011-05-21',10,12),

('2011-04-12',11,38)

 

SELECT OrderDate, ProductID, Quantity

FROM   #Orders

Result Set:

OrderDate  ProductID   Quantity

———- ———– ———–

2011-07-28 11          12

2011-03-18 12          74

2011-04-12 13          95

2011-07-25 14          57

2011-05-30 11          28

2011-05-21 10          12

2011-04-12 11          38

 

(7 row(s) affected)

Last order date using LAG():

SELECT OrderDate, ProductID, Quantity,

       LAG(OrderDate, 1, OrderDate)

       OVER (PARTITION BY ProductID ORDER BY OrderDate)

       AS Last_OrderDate

FROM   #Orders

Result Set:

OrderDate  ProductID   Quantity    Last_OrderDate

———- ———– ———– ————–

2011-05-21 10          12          2011-05-21

2011-04-12 11          38          2011-04-12

2011-05-30 11          28          2011-04-12

2011-07-28 11          12          2011-05-30

2011-03-18 12          74          2011-03-18

2011-04-12 13          95          2011-04-12

2011-07-25 14          57          2011-07-25

 

(7 row(s) affected)

Next order date using LEAD():

SELECT OrderDate, ProductID, Quantity,

       LEAD(OrderDate, 1, OrderDate)

       OVER (PARTITION BY ProductID ORDER BY OrderDate)

       AS Next_OrderDate

FROM   #Orders

Result Set:

OrderDate  ProductID   Quantity    Next_OrderDate

———- ———– ———– ————–

2011-05-21 10          12          2011-05-21

2011-04-12 11          38          2011-05-30

2011-05-30 11          28          2011-07-28

2011-07-28 11          12          2011-07-28

2011-03-18 12          74          2011-03-18

2011-04-12 13          95          2011-04-12

2011-07-25 14          57          2011-07-25

 

(7 row(s) affected)

Hope This Helps!

Vishal

SQL Server – "Denali" – Conversion Function – PARSE() and TRY_PARSE()

27.07.2011 No comments

PARSE() and TRY_PARSE() are new function introduced in SQL Server Denali.

PARSE() returns the result of an expression, translated to specified data type if the translation is possible, otherwise, it raises an error.

TRY_PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns NULL.

For example, let’s try to parse a numeric values as integer data type:

SELECTPARSE('10.20' AS INT) AS Result

SELECTTRY_PARSE('10.20' AS INT) AS Result

Result Set:

Result

———–

Msg 9819, Level 16, State 1, Line 1

Error converting string value '10.20' into data type int using culture ”.

 

Result

———–

NULL

 

(1 row(s) affected)

Since 10.20 is not an integer value PARSE() fails, and TRY_PARSE() returns NULL.

An instance of usefulness of TRY_PARSE() is when using PARSE() without error handling using TRY … CATCH.

DECLARE @NumList TABLE

(

       Num1 VARCHAR(5)

)

INSERT INTO @NumList VALUES ('10'),('10.20'),('11')

 

SELECTCASE WHEN TRY_PARSE(Num1 AS INT) IS NULL THEN 0

       ELSE PARSE(Num1 AS INT)

       END AS Result

FROM   @NumList

Result Set:

Result

———–

10

0

11

 

(3 row(s) affected)

Both these functions also support using different culture. For example, when parsing a string as DATETIME:

SELECTTRY_PARSE('27 juillet, 2011' AS DATETIME)

       AS 'English',

       TRY_PARSE('27 juillet, 2011' AS DATETIME USING 'fr-FR')

       AS 'French'

Result Set:

English                 French

———————– ———————–

NULL                    2011-07-27 00:00:00.000

 

(1 row(s) affected)

First result returns NULL, since 'juillet' is not a valid month according to us_english.

Hope This Helps!

Vishal

SQL Server – "Denali" – End of Month using EOMONTH()

26.07.2011 3 comments

SQL Server Denali CTP3 introduces a new date/time function EOMONTH(). EOMONTH() returns the last day of the month for the given date. It also accepts an optional argument which is an integer expression specifying number of months to add/remove from specified date.

Syntax:

       EOMONTH( start_date [, months_to_add] )

It’s similar to Excel’s EOMONTH() function to calculate last day of the month.

It can be used as below:

SELECT DATEADD(s, 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

       AS 'Last day of Month', — Earlier Versions

       EOMONTH(GETDATE())

       AS 'Last day of Month',

       DATEADD(s, 1, DATEADD(day, 1, EOMONTH(GETDATE())))

       AS 'Last day of Month'

       — Time part 23:59:59

Result Set:

Last day of Month       Last day of Month       Last day of Month

———————– ———————– ———————–

2011-07-31 23:59:59.000 2011-07-31 00:00:00.000 2011-07-31 23:59:59.000

 

(1 row(s) affected)

You can use DATEADD() and EOMONTH() to calculate first day of the month as follows:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

       AS 'First day of Month', — Earlier Versions

       DATEADD(day, 1, EOMONTH(GETDATE(), 1))

       AS 'First day of Month'

Result Set:

First day of Month      First day of Month

———————– ———————–

2011-07-01 00:00:00.000 2011-07-01 00:00:00.000

 

(1 row(s) affected)

Hope This Helps!

Vishal