ERwin Data Modeler Error: Unable to locate client connectivity software

When first trying to connect to SQL Server from an installation of ERwin (regardless if it’s 32 or 64 bit), you may receive the message:  Unable to locate client connectivity software 

There is a very simple fix to this seemingly common problem.

Locate the file “ntwdblib.DLL” , and copy the dll file to “AllFusion ERwin Data Modeler” folder.

Grant EXECUTE permissions to all stored procedures and functions

Often times we need to grant EXECUTE rights to all stored procedures and functions in a database to a specific user.  Replace ‘YourUserName’ with the name of your login, and run on the database in question.  It will produce a line of SQL for each securable that requires a GRANT.

declare @username varchar(255)
set @username = 'YourLoginName'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')

In SQL Server 2008, you receive error 605 and error 824 when you run a query that inserts data into a temporary table in SQL Server

If your application makes frequent use of #temp tables in stored procedures, you may see error 605 and 824 in your Event Viewer with the following:

Msg 605, Level 21, State 3, Line 1 
Attempt to fetch logical page (1:225) in database 2 failed. It belongs to allocation unit 281474980315136 not to 504403158513025024.

My immediate suspicion was database corruption, however all DBCC scans came back clean.  Looking at the database ID specified in the error message (2) we know it’s tempDB, and not our actual production database.

Turns out, in SQL Server 2008, we can enable a trace flag to get around this issue:   T4135

Set it, restart the SQL Server service, and your problems (at least those relevant to errors 608 and 804) should be solved.  I am running the latest build of R2, so no additional updates were necessary.

Saving Changes is Not Permitted

While making a database change that requires a table to be recreated, by default SQL Server Management Studio will not allow you to make the change.  Seems a bit strange that this is out-of-the-box behavior, but it’s easy to change:

Row and Page Level Compression in SQL Server 2008

A new type of data compression was introduced in SQL Server 2008, available in Enterprise and Datacenter editions.  Compression yields performance gains, as both storage and memory requirements are reduced due to the data being compressed both on disk on in the data cache.    I/O is reduced by there being less data to actually have to read; however, this benefit comes at extra CPU overhead, but the net gain should be an overall performance boost.

What types of objects can be compressed?  Tables, nonclustered indexes, and indexed views.

There are two basic types of compression in SQL Server 2008:

Row-Level Compression:  not exactly true data compression.  Savings are achieved here by employing a more efficient storage format for fixed-length datatypes.   For example, the int datatype requires 4 bytes of storage, regardless of how much data is actually stored in the field, NULLs included.  Row-level compression allows the usage of only the actual amount of storage required.  Less compression is achieved by row-level compression then by page-level, but less CPU overhead is incurred as well.

To enable row-level compression on a particular table:

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

Page-Level Compression: True data compression.  Data is compresses by storing repeating values or common prefixes only once.  When page-level compression is employed, row-level compression is switched on as well.  The nature of the data stored in the object you are compressing will define how much the data is actually compressed.  If there is a good deal of repeating data, compression will be highly effective.

To enable page-level compression on a particular table:

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=PAGE)

Page compression is applied only after a data page is full, while row-level compression is applied immediately.

Is data compression right for you?

To determine how a table will look post-compression, SQL Server 2008 provides a stored procedure to calculate the potential effects of compression by sampling 5,000 data pages of the object in question.  (This works by copying the candidate pages to tempDB, compressing them, and reporting the results back to the user).

[ @schema_name = ] ‘schema_name’

, [ @object_name = ] ‘object_name’

, [@index_id = ] index_id

, [@partition_number = ] partition_number

 

, [@data_compression = ] ‘data_compression’

Either ‘ROW’ or ‘PAGE’ may be specified for the @data_compression parameter.

A sample call:

use myDatabase

go

exec sp_estimate_data_compression_savings ‘dbo’, ‘myTable’, null, null, ‘PAGE’

 

go

Implementing via Management Studio:

Right click on the table you would like to compress, go to Storage–>Manage Compression

The next page will allow you to select the compression type, and the partition you would like affected (if the table in question has multiple partitions).

The next screen simply affects your changes to the database, either by generating a script (my preferred method) or running it straight into your database.

TFS 2008 Database Backup and Restore scripts

These scripts can come handy when performing a migration based upgrade.

Backup Script

BACKUP DATABASE [ReportServer] TO  DISK = N’E:\Backups\ReportServer.bak’ WITH NOFORMAT, NOINIT,  NAME = N’ReportServer-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [ReportServerTempDB] TO  DISK = N’E:\Backups\ReportServerTempDB.bak’ WITH NOFORMAT, NOINIT,  NAME = N’ReportServerTempDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsActivityLogging] TO  DISK = N’E:\Backups\TfsActivityLogging.bak’ WITH NOFORMAT, NOINIT,  NAME = N’TfsActivityLogging-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsBuild] TO  DISK = N’E:\Backups\TfsBuild.bak’ WITH NOFORMAT, NOINIT,  NAME = N’TfsBuild-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsIntegration] TO  DISK = N’E:\Backups\TfsIntegration.bak’ WITH NOFORMAT, NOINIT,  NAME = N’TfsIntegration-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsVersionControl] TO  DISK = N’E:\Backups\TfsVersionControl.bak’ WITH NOFORMAT, NOINIT,  NAME = N’TfsVersionControl-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsWarehouse] TO  DISK = N’E:\Backups\TfsWarehouse.bak’ WITH NOFORMAT, NOINIT,  NAME = N’TfsWarehouse-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsWorkItemTracking] TO  DISK = N’E:\Backups\TfsWorkItemTracking.bak’ WITH NOFORMAT, NOINIT,  NAME = N’TfsWorkItemTracking-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsWorkItemTrackingAttachments] TO  DISK = N’E:\Backups\TfsWorkItemTrackingAttachments.bak’ WITH NOFORMAT, NOINIT,  NAME = N’TfsWorkItemTrackingAttachments-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [WSS_AdminContent] TO  DISK = N’E:\Backups\WSS_AdminContent.bak’ WITH NOFORMAT, NOINIT,  NAME = N’WSS_AdminContent-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [WSS_Config] TO  DISK = N’E:\Backups\WSS_Config.bak’ WITH NOFORMAT, NOINIT,  NAME = N’WSS_Config-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [WSS_Content] TO  DISK = N’E:\Backups\WSS_Content.bak’ WITH NOFORMAT, NOINIT,  NAME = N’WSS_Content-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore Script

RESTORE DATABASE [ReportServer] FROM  DISK = N’E:\Backups\ReportServer.bak’ WITH  FILE = 1,  MOVE N’ReportServer’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf’,  MOVE N’ReportServer_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServer.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [ReportServerTempDB] FROM  DISK = N’E:\Backups\ReportServerTempDB.bak’ WITH  FILE = 1,  MOVE N’ReportServerTempDB’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf’,  MOVE N’ReportServerTempDB_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsActivityLogging] FROM  DISK = N’E:\Backups\TfsActivityLogging.bak’ WITH  FILE = 1,  MOVE N’TfsActivityLogging’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsActivityLogging.mdf’,  MOVE N’TfsActivityLogging_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsActivityLogging_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsBuild] FROM  DISK = N’E:\Backups\TfsBuild.bak’ WITH  FILE = 1,  MOVE N’TfsBuild’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsBuild.mdf’,  MOVE N’TfsBuild_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsBuild_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsIntegration] FROM  DISK = N’E:\Backups\TfsIntegration.bak’ WITH  FILE = 1,  MOVE N’TfsIntegration’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsIntegration.mdf’,  MOVE N’TfsIntegration_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsIntegration_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsVersionControl] FROM  DISK = N’E:\Backups\TfsVersionControl.bak’ WITH  FILE = 1,  MOVE N’TfsVersionControl’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsVersionControl.mdf’,  MOVE N’TfsVersionControl_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsVersionControl_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsWarehouse] FROM  DISK = N’E:\Backups\TfsWarehouse.bak’ WITH  FILE = 1,  MOVE N’TfsWarehouse’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWarehouse.mdf’,  MOVE N’TfsWarehouse_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWarehouse.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsWorkItemTracking] FROM  DISK = N’E:\Backups\TfsWorkItemTracking.bak’ WITH  FILE = 1,  MOVE N’TfsWorkItemTracking’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTracking.mdf’,  MOVE N’TfsWorkItemTracking_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTracking.LDF’,  MOVE N’sysft_TeamFoundationServer10FullTextCatalog’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTracking.TeamFoundationServer10FullTextCatalog’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsWorkItemTrackingAttachments] FROM  DISK = N’E:\Backups\TfsWorkItemTrackingAttachments.bak’ WITH  FILE = 1,  MOVE N’TfsWorkItemTrackingAttachments’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTrackingAttachments.mdf’,  MOVE N’TfsWorkItemTrackingAttachments_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTrackingAttachments_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [WSS_AdminContent] FROM  DISK = N’E:\Backups\WSS_AdminContent.bak’ WITH  FILE = 1,  MOVE N’WSS_AdminContent’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_AdminContent.mdf’,  MOVE N’WSS_AdminContent_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_AdminContent_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [WSS_Config] FROM  DISK = N’E:\Backups\WSS_Config.bak’ WITH  FILE = 1,  MOVE N’WSS_Config’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Config.mdf’,  MOVE N’WSS_Config_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Config_1.LDF’,  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [WSS_Content] FROM  DISK = N’E:\Backups\WSS_Content.bak’ WITH  FILE = 1,  MOVE N’WSS_Content’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Content.mdf’,  MOVE N’WSS_Content_log’ TO N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Content_1.LDF’,  NOUNLOAD,  STATS = 10
GO

 

Database Mirroring – Login failure after role switch

After configuring database mirroring and testing a failover, you may be unable to log into the new principal database. This occurs because the database user is not associated with a SQL server login (mismatched SIDs). Running sp_change_users_login ‘auto_fix’, ‘userName’ will correct this, but we need to avoid the situation altogether so a seamless failover is possible.

During the mirroring setup, we can run the following query on the principal to generate the SQL necessary to correctly create the logins on the mirror server.

SELECT
‘create login [' + p.name + '] ‘ +
case when p.type in(‘U’,'G’) then ‘from windows ‘ else ” end +
‘with ‘ +
case when p.type = ‘S’ then ‘password = ‘ + master.sys.fn_varbintohexstr(l.password_hash) + ‘ hashed, ‘ +
‘sid = ‘ + master.sys.fn_varbintohexstr(l.sid) + ‘, check_expiration = ‘ +
case when l.is_policy_checked > 0 then ‘ON, ‘ else ‘OFF, ‘ end + ‘check_policy = ‘ + case when l.is_expiration_checked > 0 then ‘ON, ‘ else ‘OFF, ‘ end +
case when l.credential_id > 0 then ‘credential = ‘ + c.name + ‘, ‘ else ” end
else ” end +
‘default_database = ‘ + p.default_database_name +
case when len(p.default_language_name) > 0 then ‘, default_language = ‘ + p.default_language_name else ” end

FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON l.credential_id = c.credential_id
WHERE p.type in(‘S’,'U’,'G’)
AND p.name ‘sa’

Generating SQL from Visio (non-Enterprise Architect versions)

Creating an ERD without the ability to generate SQL from it is of questionable value.  If you’re not using a CASE tool such as ERwin (and why not?), you may need to generate a DDL from your Visio layout.  If typing out SQL statements proves too laborious, here is a great workaround to generate workable SQL from your Visio diagram.

A company called Orthogonal Software has a Visio plug-in called Orthogonal Toolbox that will allow you to export the data from the Visio document to an XML File.  Additionally, Orthogonal has provided a couple XSLT’s that you can apply (look for XSLT on that page). One of which has been altered to export SQL.

Once you have the plug-in installed and the XSLT’s downloaded, it’s pretty simple.  Use the plug-in to export to XML, and apply the stylesheet when exporting.  Presto- DDL statements for your structures.

 

Row many columns and rows are in my tables…and which tables are largest?

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable ‘sp_spaceused ”?”’
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ‘ KB’, ”) AS integer) DESC
DROP TABLE #temp

SQL Profiler: Audit Logout

Q:
A developer reported very high duration times for the SQL Profiler “Audit Logout” event in his development environment, and inquired how to decrease this time.

A:
When a connection disconnects from SQL Server, “Audit Logout” reports the duration the entire connection existed. If the connection was open for a long time, this number can be quite large. It’s not indicative of any real performance issue, but is often times confused with one due to the large duration time.