SQL SERVER – CURSOR_STATUS Cursor System Function

CURSOR_STATUS() is the cursor system functions in SQL Server that is used to check the status of current cursor. This is useful in an application to check the cursor status before invoking it. Especially, when we are working with one or more cursors in the application. This function requires two parameters that is given in the syntax section. We have to remember that this function is a non-deterministic. So, result may vary in different situations.

Status can be obtained for both local and global cursor.

Syntax

CURSOR_STATUS(cursor scope, cursor name)

CURSOR_STATUS(Scope variable name, cursor variable name)

Reference

CURSOR_STATUS
(
{ ‘local’ , ‘cursor_name’ }
| { ‘global’ , ‘cursor_name’ }
| { ‘variable’ , ‘cursor_variable’ }
)

Function Results

Example

In this example I am going to declare a simple cursor and get its status in different state.
–Create a cursor.
DECLARE emp CURSOR FOR SELECT EmpName FROM dbo.tblEmployee

–Display the status of the cursor before and after opening
SELECT CURSOR_STATUS(‘global’,’emp’) AS ‘After declare’

— opening the cursor
OPEN emp
SELECT CURSOR_STATUS(‘global’,’emp’) AS ‘After Open’

–closing the cursor.
CLOSE emp
SELECT CURSOR_STATUS(‘global’,’emp’) AS ‘After Close’

— invalid cursor name
SELECT CURSOR_STATUS(‘global’,’dept’) AS ‘isValid?’

–Remove the cursor from memory.
DEALLOCATE emp

Output

This cursor function is really a useful function. I hope you enjoyed reading this blog post.

FTP Task Connection Error – Unable to service PORT commands 500 LPRT

This blog post is about an issue I faced while implement SSIS package communication with FTP server. For simplicity, I am going to assume a scenario as a web hosting server with FTP access.

Scenario

My task is to design a package to upload a file to FTP server. I can login to FTP server using FTP tools and perform file system operations such as create, copy, paste and etc. But, my requirement is to automate the process. It is simple that a file need to be copied from a local machine to remote server every hour.

I did design a package that is not very difficult to do and configured the FTP connection and connection tested successfully. But, when I execute the package it throws an error as given below.

Error description

SSIS FTP Task —————————— An error occurred in the requested FTP operation. Detailed error description: 200 Type set to A 500 Unable to service PORT commands 500 LPRT not understood

I just put the error description in google to find the solution and I found the following reference. It is useful to understand why this problem occurred and not helping me to resolve it.

Microsoft link : http://support.microsoft.com/kb/281193

I did not search further that it may take some more time to resolve and I continue my development. So, I just start looking for some settings to alter connection properties to make the connect successful. Surprisingly, I saw an option in the FTP connection manager to set the connection in passive mode.

I checked that option and executed the package. This time package executed succeeded.

Solution is

Setup FTP connection in passive mode solve this problem.

FTP connection manager screen shot:

Further Reading

Active and Passive FTP Connections

Send or Receive files from FTP server

Accessing FTP server using script

SQL SERVER 2012 – Convert an Expression to Specific Data Type using PARSE Conversion Function

Parse : This conversion function is used to compute the expression and return the output to the desired data type. But, this function is in SQL Server 2012 and onwards.

Parse function suitable for data and numeric data and it requires CLR because it is a .NET function. So, as usual there will be some performance impact if we use in large mission critical application. We still have cast and convert functions for type casting.

This function requires a valid data and appropriate convertible data type. Otherwise, parse function raise error.

I think the best feature is this function is language or culture option. We can specify the culture type in the conversion. So, the output reflects the given culture with converted data type.

Usage

PARSE ( string_value AS data_type [ USING culture ] )

string_value : It is nvarchar(4000) storage.

data_type : These are the valid data types

culture : Optional string that identifies the culture in which string_value is formatted. Please refer this blog post for languages in SQL Server.

Example

1. Parsing currency of different region.

SELECT PARSE(‘€345,98’ AS money USING ‘de-DE’) AS Result;

Output

2. Parsing date of the given language or locale

SELECT PARSE(’13 August 2013′ AS datetime2 USING ‘en-us’) AS Result;

Output

SQL SERVER – Performance Monitoring System Stored Procedures – T-SQL

Performance is the most critical part of any system to serve the request at its best. But, we need a well configured machine with lots of hard drive space for data storage, sufficient RAM and good processor with multiple core and etc. Even if we have everything in place our application and database design should be appropriate with best practices implemented for high performance.

Often, we face some performance issues with SQL Server as the data and user request grows. Now, we are in the situation to identify the performance problem and fix it with best possible solutions. To do so, we need performance monitoring tools. SQL Server has many performance monitor tools inbuilt, external utility software and system stored procedure.

In this blog post, I am going to introduce few performance monitoring system stored procedures that helps us to monitor the activities of current user activity, processes, blocking, locking and data store utilization.

Performance Monitoring System Stored Procedures.

sp_who (Transact-SQL)

Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.

sp_lock (Transact-SQL)

Reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies.

sp_spaceused (Transact-SQL)

Displays an estimate of the current amount of disk space used by a table (or a whole database).

sp_monitor (Transact-SQL)

Displays statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.

We can use the output of these stored procedure in our application to perform some task such as alerts, remove blocks and lock by releasing the resource and look for unusual user activities and etc.

These procedures need appropriate permission for execution. I believe you have understood the use of these performance monitoring procedure in SQL Server.

Share this:

Share this:

Share this:

Share this:

Share this: