SQL SERVER – Spatial shapes data

Shapes are the geometry objects that are used to draw an outline or it can be used as real geometry shape for calculation on top of maps (geography). There are 11 usable or instantiable shape objects available in SQL Server. But, we can use only 11 of them and the image shown here is used to quickly identify the instantiable objects mentioned in blue color. In general, child object type properties are inherited from the parent objects.

Spatial Data Hierarchy – Microsoft Image

These data types are splitting into two categories simple and collections.

Simple Types

Point
LineString
CircularString
CompoundCurve
Polygon
CurvePolygon

Collection Types

MultiPoint
MultiLineString
MultiPolygon
GeometryCollection

Geography Type

FullGlobe

It is important to know that some of the methods are capable of handling only simple objects. SQL Server follows the standards of OGC (Open Geospatial Consortium). The following links will give you detailed information about OGC standards and other GIS architecture details.

SQL SERVER – Obtain ERROR_STATE code for troubleshooting

ERROR_STATE:

This function returns the error state number of an error occurred inside the try..catch block. This state number is different from @@ERROR number. Error State will differ for a same error but in different situation. So, to pin point the particular cause, ERROR_STATE() is very useful when referring Microsoft KB article for troubleshooting.

it returns an integer value. Error_State must be used within the try…catch block otherwise it will return a null value. If we use nested try…catch block it returns the error_state value for the current scope.

Syntax

ERROR_STATE()

Example

1. A simple example to show how to use of ERROR_STATE() function.

DECLARE @int AS INTEGER
BEGIN TRY
    SET @int = 1/0.0
END TRY

BEGIN CATCH

    PRINT 'Error : ' + CAST(@@ERROR AS VARCHAR(20))
    PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR(20))
    PRINT 'Error Message : ' + ERROR_MESSAGE()    
END CATCH

Output

SQL SERVER – Get the Failed Stored Procedure name Using ERROR_PROCEDURE

ERROR_PROCEDURE () : This function is used to get the stored procedure name or trigger name that failed during execution. This function must be used with in the try..catch block otherwise it returns NULL value.

Syntax

ERROR_PROCEDURE ( )

It returns the data type nvarchar of size 128 bytes.

Example

1. This is a simple example to show what will happen when we execute this function outside the try…catch scope

select ERROR_PROCEDURE ()

Output

2. In this example, I am going to create a sample stored procedure and test the function error_procedure()

Creating Procedure

create procedure sp_Test
 as
 begin
    declare @int as int
    set @int = 1/0
 End
 GO

If we execute this procedure using the below given script then it returns an error.

exec sp_Test
GO

But, we need the name of the procedure which has got error during execution. So, I am going to alter the previous stored procedure to add try..catch block.

Altered stored procedure

alter procedure sp_Test
 as
 begin
    declare @int as int
 begin try
    set @int = 1/0
 end try
 begin catch
    print 'Failed Procedure Name : ' + ERROR_PROCEDURE()
 end catch
 End
 GO

Now, execute the stored procedure

exec sp_Test
GO

Output

So, we have learned to capture the stored procedure names that are failed during execution. We can use this function in the application and audit or handle the errors gracefully.

Additional Reading

Try…Catch

@@Error

SQL SERVER – Working with the session context information – CONTEXT_INFO

Context Information is a binary value of size up to 128 bytes that can be set for a session and referenced in multiple batches, stored procedures, triggers, or user-defined functions executing on the same session.

There are two commands that are used to set and get the context information.

SET CONTEXT_INFO – It helps to assign session context value

CONTEXT_INFO () – It helps to retrieve session context value.

We can also use DMV to see the context binary value in the current session. These are the DMVs that can use to view the context information.

sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

SET CONTEXT_INFO

It sets the binary information to the session context of the current session or connection.

Syntax

SET CONTEXT_INFO binary_string_constant

SET CONTEXT_INFO @binary_variable

Example

In this example I am going to generate a binary value for a string constant and set that value to the session context.

To Generate binary value, we can use cast or convert functions. In this example, I used this statement to create the binary value of a string value.

SELECT CAST('SQL Server Rider' AS VARBINARY(50))
SET CONTEXT_INFO 0x53514C20536572766572205269646572

CONTEXT_INFO ()

This function is used to retrieve the session context value that is already set by user. Otherwise, it will return a null value. It returns 128 bytes binary value. We can use data type conversion function to convert the binary value to other type.

Syntax

CONTEXT_INFO ()

Example

In this example I am going to use the context_info() function to read the context data and convert into string value.

SET CONTEXT_INFO 0x53514C20536572766572205269646572
SELECT CAST(CONTEXT_INFO() AS varchar(100))

Output Section

This section contains the result of all the SQL query used in the examples.

Using DMV to get the context information

SQL Query

SET CONTEXT_INFO 0x53514C20536572766572205269646572

SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID

Output

SQL SERVER – Get Fetch status of a cursor using @@FETCH_STATUS function

@@FETCH_STATUS: This function is used to get the current fetch status of a latest opened cursor.This function is global function for all cursors in the application and it is non-deterministic. Because, the result is unpredictable.

For instance, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes the results from another cursor. When control is returned from the called stored procedure, @@FETCH_STATUS reflects the last FETCH executed in the stored procedure, not the FETCH statement executed before the stored procedure is called.

Function Syntax

@@FETCH_STATUS

It returns an integer value as given below.

0 = The FETCH statement was successful.

-1 = The FETCH statement failed or the row was beyond the result set or end of record.

-2 = The row fetched is missing.

Example

This is a simple example read the all rows in the cursor variable.

SQL

--Create a cursor.
DECLARE person CURSOR  FOR 
    SELECT Top 5 [FirstName] FROM [AdventureWorks2012].[Person].[Person]

-- opening the cursor
OPEN person
SELECT CURSOR_STATUS('global','person') AS 'isReady?'

fetch next from person;
while @@FETCH_STATUS = 0
begin
    fetch next from person;
end

--closing the cursor.
CLOSE person
SELECT abs(CURSOR_STATUS('global','person')) AS 'isCursorClosed?'

--Remove the cursor from memory.
DEALLOCATE person

OutputThe latest fetch status of all cursors can be obtained from the DMV sys.dm_exec_cursors.

Example DMV query

select * from sys.dm_exec_cursors (@@SPID)

Share this:

Share this:

Share this:

Share this:

Share this: