Issue with renaming the objects using Management Studio 2005

Whenever we rename an object like stored procedure, function, view or trigger using the management studio in SQL server 2005, the syscomments table will not be updated. Hence when we try to get the text/code of the renamed object using the sp_helptext or OBJECT_DEFINITION () we will get the text/code of the object with the old name itself.

Try it yourself:

Use AdventureWorks
Go
CREATE PROC TestRename AS
Print ‘XYZ’
GO

Now, rename this SP using the management studio object list to “RenameTest”

Now Execute the below Query on AdventureWorks Database.

sp_helptext RenameTest

Now if you observer the result pane, the result will be the same statement with the procedure name still not changed even after renaming the SP using the management studio. The result will be as shown below.

CREATE PROC TestRename AS
Print 'XYZ'

When was my database last used???

Use <DatabaseName>

GO

SELECT DB_NAME() as DatabaseName,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

SQL 2005 SSIS and Excel 2007

Most of us know how to use an Office Excel file in an SSIS package. As long as we use an Excel file with the .xls extension it would be fine. But, when it comes to Office Excel 2007 it would be a bit different. The Connection manager for Excel files option that we usually choose will not work.

Since Office Excel 2007 and its .xlsx extension came after a long time after SQL Server 2005 release and since this new format is entirely different from the previous formats we have a little problem in using Connection manager for Excel files option with Excel 2007.This was sorted out with the Service Pack 2. This Service Pack gave us a new driver which could be used for Office Excel 2007 files.

Here are the brief steps on how to create a connection manager for Excel 2007.

In a new or existing package,

1. Add a New Connection and choose the connection manager type either ADO.NET or OLEDB by right-clicking on the Connection Managers tab.

2. Click on New and under the Provider drop-down list, select Microsoft Office 12.0 Access Database Engine OLE DB Provider

3. Click OK

4. Click on “All” which is located on the left side of the connection manager window, and type “Excel 12.0” against the Extended Properties.

5. Now go back to the Connections tab and type in the file path of the Excel 2007 file along with the file name there.

6. Click OK and you are done.
7. The same can be used for Access database 2007.