Archive for October, 2010

Recently I was working on a stored procedure to accomplish a complex task in SQL Server 2008. So I spend some time working on the procedure. The procedure uses some temp tables created in the procedure itself.

After finishing this procedure it was time to use the procedure in a SSIS package. So I open the dataflow task is will be implemented in. Enter the Exec “<stored procedure name>” command and want to save…

HRESULT: 0xC020204A

Error: ” Error at Combined Job [Get Data[1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Invalid object name ‘<tablename>’.”.

Error at Combined Job [Get data[1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

Additional information:
Exception from HRESULT: 0xc020204A (Microsoft.SqlServer.DTSPipelineWrap)

But then this error occurs. Very annoying,  but after doing some research the problem is easily solved. SSIS actually does not handle temporary tables how it should. So with an easy workaround you will be able to use the stored procedure in no time.

The solution is to fake the columns it returns so it will pick up the names from the procedure. So we add the following code to our procedure:

IF 1=2
BEGIN
SELECT
CAST(NULL AS INT) AS ID,
CAST(NULL AS NVARCHAR(50) AS Surname,
CAST(NULL AS DECIMAL(18,2) AS Salary,

END”

The code will never become executed because of the IF 1=2 but the column names will get loaded and used as return columns. So you want to enter all the columns of the #tmp table in the code.

Instead of casting al the columns it is possible to select them from an existing table, and the properties will get loaded automatically. But if the table structure of the temp table is non-existent then the description above is the way to go.

Advertisements

T-SQL Cursor

Posted: 14 October 2010 in Uncategorized
Tags: , ,

Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.

The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:

  1. Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
  2. Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
  3. Use the OPEN statement to execute the SELECT statement and populate the cursor.
  4. Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
  5. When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor’s result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is deallocated, you must issue a DECLARE statement to rebuild the cursor.

Advantages of Cursors:

1. Row by Row operations can be successfully executed.

Disadvantages of Cursors:

1. SQL Server by default have a direct performance degradation on the usage of cursors. SQL Server Engine processes records or data in such a way it will have a performance impact when cursor is used.
2. As you add overhead to the Server / engine, it will eat up your memory and hence other parallel processes will suffer too.
3. Cursors are the SLOWEST way to access data inside SQL Server as it does row by row operations and cursors are over thirty times slower than set based alternatives.

The following information may vary depending on the specific database system.

Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE. Repeated network round trips can severely impact the speed of the operation using the cursor. Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.

Cursors allocate resources on the server, for instance locks, packages, processes, temporary storage, etc. For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query’s result-set. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed. This wasting of resources on the server can not only lead to performance degradations but also to failures.

Considerations before using a Cursor:

1. Think of all possible alternatives to solve your problem and if you didnt got any then create a cursor.
2. Consider Temp tables as alternatives to usage of cursors.

Example:

This is a simple example of a cursor witch prints to the message screen of SQL Server.

USE AdventureWorksLT2008
— Declare a variable to save the product id.
DECLARE @ProductID int

— Declare the Cursor
DECLARE cursornaam CURSOR FOR
— SELECT statement for getting a cursor record set
SELECT ProductID FROM SalesLT.Product WHERE Color = ‘Black’

— Open cursor
OPEN cursornaam

— fetch the first ID from the cursor
FETCH NEXT FROM cursornaam INTO @ProductID

— While there is something to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

— Sub querys & Sub Declares
DECLARE @nameVar varchar(250)
SET @nameVar = (SELECT Name FROM SalesLT.Product Where ProductID = @ProductID )
Print @nameVar

— try to fetch the next ID from the cursor
FETCH NEXT FROM cursornaam INTO @ProductID

— end the actions
END
— Close the cursor, this can be reopened.
CLOSE cursornaam
–Deallocate all the resources of the cursor to free up space
DEALLOCATE cursornaam

Sources:

WIKIPEDIA

MSDN Cursor