Daily Archives: October 8, 2010

How to find out top version store generating objects

Posted on by

I recently found the need to write a query to find out which tables are contributing to version store usage on the server, then you can use the following piece of T-SQL:

EXEC sp_MSforeachdb 'IF ((select DB_ID(''?'')) in (select distinct database_id from sys.dm_tran_top_version_generators)) begin print ''Database Name :'' + ''?'' + char(13) select a.*,object_name(b.object_id) as TableName from sys.dm_tran_top_version_generators a inner join ?.sys.partitions b on a.rowset_id = b.partition_id end'

The reason this got so convoluted is because the Object_Name function doesn’t accept a partition ID or a allocation hobt ID. So, I have to get the object ID from sys.partitions for the associated database and then use the object_name function.

If anyone has an alternative quicker way, then please feel to leave a comment.

Advertisement

Data Type Mapping for OLEDB Providers

Posted on by

I recently had a question on the #sqlhelp hashtag on Twitter regarding how OLE DB Providers map the data columns to SQL Server data types.

The Data Type Mappings for SQL Server for distributed queries are mentioned here. The DBType values for each data that you are retrieving from a non-SQL data source like Oracle, Excel, Access can be found out using RowSet Viewer. The Microsoft® Developer Network (MSDN®) Platform SDK contains an OLE DB RowsetViewer sample application written in Microsoft Visual C++®. This application enables you to connect to either the Microsoft OLE DB Provider for AS/400 and VSAM or the Microsoft OLE DB Provider for DB2, open a table window, type the host file name or DB2 database, return a rowset, and browse the contents.

Using Rowset Viewer you can get the DBType of each column returned from the remote data source. The function used is IColumnsRowset::GetColumnsRowset. Using this you can create the necessary schema on the SQL Server database which will act as the destination for the data received from the remote data source.

Happy DB Scheming! Smile