SQL 2012

Finding SQL Column Dependencies

 

I’m working on a project to rewrite an older SSIS package. As part of that rewrite, I noticed that there were some duplicate columns (with different names) being sent from vendor. They corrected their end, and now I want to drop the extra columns in the database.

Problem is, how can you make sure that those columns weren’t used in some other procedure or view?

I found this article on Different Ways to Find SQL Server Object Dependencies and started with Svetlana’s second example.

Unfortunately it didn’t quite give me what I wanted. Since we heavily use schemas in our database and we don’t use Hungarian notation, I wanted to make sure that I could more easily locate the objects I needed.

I revised the code to include the schema name and object type, as well as pulling the items out into variables for easy reading.

DECLARE @TableName AS VARCHAR(50) = 'Customers' -- Table that holds column you are dropping or renaming
DECLARE @ColumnName AS VARCHAR(50) = 'Cstomer'	-- Column you are looking for

SELECT
	OBJECT_SCHEMA_NAME(referencing_id) + '.' + OBJECT_NAME(referencing_id)	AS referencing_name
,   o.type
,	referenced_database_name
,	referenced_schema_name
,	referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referencing_id = o.object_id
WHERE OBJECT_NAME(d.referenced_id) = @TableName 
    AND OBJECT_DEFINITION (referencing_id)  LIKE '%' + @ColumnName + '%'
ORDER by OBJECT_SCHEMA_NAME(referencing_id), OBJECT_NAME(referencing_id)

Is Excel Missing from Import and Export Data?

 

Recently, a user was trying to import some data from a Microsoft Excel file using the Import and Export Data wizard.  The dropdown choices did not Excel as an option, but they know they had used it in the past.

64bit

The reason for this is that they were running the 64-bit wizard. Since there is no 64-bit version of the Excel driver, it wasn’t on the list.

Running the 32-bit version provided the data sources they were expecting.

32-bit

In addition to Excel, the 32-bit version provides drivers for:

  1. Microsoft Access (Microsoft Access Database Edition)
  2. Microsoft Access (Microsoft Jet Database Engine)
  3. Microsoft Office 15.0 Access Database Engine OLE DB Provider
  4. Microsoft OLE DB Provider for Analysis Servers 11.0
  5. Microsoft OLE DB Provider for Oracle

In our case, we are running SQL 2012, but we’ve installed the SQL 2014 tools in order to use the latest SSMS.

By default the 64-bit SQL Server 2014 Import and Export Data is located at C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTSWizard.exe.

The 32-bit version can be found at C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTSWizard.exe

Lync Monitoring Time Zone

 

When attempting to utilize Lync 2013 Monitoring Reports, we were presented with an error which began with:
An error has occurred because the time zone of SQL Server Reporting Services (SSRS) is inconsistent with the time zone of the…

image

We run our SQL servers in GMT because of our International presence, but our Lync servers are running in EST. We recently rebuild our Lync environment and hadn’t verified the monitoring reports on the new installation because they were so rarely used.

The error message provides the proper steps for resolution. These steps needed to be run on both the LCSCDR and QoEMetrics databases, and the above error message will reference either database as they both need to be fixed.

1. Run the dbo.RTCClearTimeZoneInfo stored procedure on the [LcsCDR/QoeMetrics] database
2. Open the reports home page in your web browser using the SSRS that has the time zone that you want to use
3. Run the dbo.RtcGenerateSummaryTables stored procdure on [LcsCDR/QoeMetrics] database to improve your reporting experience.

However, after running the procedure in step 1, we were presented with the following error:

Msg 547, Level 16, State 0, Procedure RtcClearTimeZoneInfo, Line 6

The DELETE statement conflicted with the REFERENCE constraint “FK_DaylightSavingYears_1”. The conflict occurred in database “QoEMetrics”, table “dbo.DaylightSavingYears”, column ‘TimeZone’.

The statement has been terminated.

(1 row(s) affected)

I found this thread on technet which properly identified the problem with Lync 2013 that the end user had resolved with a Microsoft support call.

There are two issues which prevent the successful execution of the stored procedure in Lync 2013.

  1. There is now a foreign key constraint between the DaylightSavingYears table and the TimeZones table which prevents the TimeZones table from being cleared first.
  2. There is now a trigger associated with the TimeZoneConfiguration table which requires there always be exactly one row in the table.

Resolution

To work around this issue the following SQL query can be run to perform the same action as the stored procedure.  It will execute the operation on both the LcsCDR and QoEMetrics tables.

USE [LcsCDR]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @Status int
set @Status = 0
BEGIN TRANSACTION
    DELETE from DaylightSavingYears WITH (TABLOCKX)
    if (@@error <> 0) begin
        ROLLBACK TRANSACTION
    end
 DELETE from TimeZones WITH (TABLOCKX)
    if (@@error <> 0) begin
        ROLLBACK TRANSACTION
    end
 ALTER TABLE TimeZoneConfiguration DISABLE TRIGGER "TimeZoneConfigurationTrigger";
    DELETE from TimeZoneConfiguration WITH (TABLOCKX)
    if (@@error <> 0) begin
        ROLLBACK TRANSACTION
    end
 ALTER TABLE TimeZoneConfiguration ENABLE TRIGGER "TimeZoneConfigurationTrigger";
COMMIT TRANSACTION
exec @Status = RtcTruncateSummaryTables
SELECT @Status

USE [QoEMetrics]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @Status int
set @Status = 0
BEGIN TRANSACTION
    DELETE from DaylightSavingYears WITH (TABLOCKX)
    if (@@error <> 0) begin
        ROLLBACK TRANSACTION
    end
 DELETE from TimeZones WITH (TABLOCKX)
    if (@@error <> 0) begin
        ROLLBACK TRANSACTION
    end
 ALTER TABLE TimeZoneConfiguration DISABLE TRIGGER "TimeZoneConfigurationTrigger";
    DELETE from TimeZoneConfiguration WITH (TABLOCKX)
    if (@@error <> 0) begin
        ROLLBACK TRANSACTION
    end
 ALTER TABLE TimeZoneConfiguration ENABLE TRIGGER "TimeZoneConfigurationTrigger";
COMMIT TRANSACTION
exec @Status = RtcTruncateSummaryTables
SELECT @Status

The nice thing about the script is that it combines the procs from both the LcsCDR and QoEMetrics in a single procedure. After running the script, I still needed to run generate the summary tables with dbo.RtcGenerateSummaryTables in each of the databases.

Defaulting to last week in SSRS 2012

I had a recent request to change the default date range for a report to be the previous week Mon-Sun.

With SQL, you can use the current week and DATEADD into the previous week, like this:

IF (@StartDate IS NULL)
    SET @StartDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0)
IF (@EndDate IS NULL)
   SET @EndDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)

SSRS uses VB.Net as it’s expression language, and as a result I couldn’t directly translate the functions as the behavior with the DATEADD using a week interval returns the wrong date.

Instead I wound up using these functions instead. Note the use of the multiplication by –1. WIthout this, the calculation would only be correct on Mondays, and we need it to work as the week progresses further away from Monday each day.

=today.AddDays(((today.DayOfWeek - DayOfWeek.Monday)*-1)-7)
=today.AddDays(((today.DayOfWeek - DayOfWeek.Monday)*-1)-1)