TroubleshootingSQL

Category: Data

Posts about data platforms, SQL Server, databases, Azure SQL, analytics, and data engineering.

  • Denali CTP1 is now available

    SQL Server Denali CTP1, the next major release of SQL Server, is now available for download on the Microsoft Download site.

    You can create your Hyper-V virtual machines and get your hands dirty with the installation bits. Post your questions or queries regarding Denali on the MSDN Forums. If you want to log a defect with the CTP1 release, then please do so on the Microsoft Connect Site.

    Technorati Tags: ,

  • How to fetch all Login Failed messages from default traces

    I was recently investigating a login failed issue on a SQL instance and required to quickly find out all the Login Failed events that had occurred on the server. I could have scoured the SQL Errorlogs but I didn’t want to do this due to the bloated size of the Errorlogs. An easier way would be to read the Default Traces (available from SQL Server 2005 and above) and look for all the Audit Login Failed events (event class = 20) using fn_trace_gettable function.

    The query that can be used to get this information is given below:

    
declare @path varchar(1000)

select @path = path from sys.traces where id = 1

select trc.Spid, TextData, State, Error, StartTime, EventSubClass, LoginName, NTDomainName, NTUserName, ClientProcessID, HostName, ApplicationName

from fn_trace_gettable(@path,DEFAULT) trc

where EventClass = 20

order by StartTime desc

  • Data Type Mapping for OLEDB Providers

    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

  • [Blog Update] SQLServerFAQ posts for September

    September has been a content month for me both on the SQLServerFAQ blog on MSDN as well on this blog. Here is a round up of my posts on SQLServerFAQ for September:

    I was intrigued by this when someone asked me what the Scan Count value meant w.r.t. SEEKs/SCANs:
    SCAN COUNT meaning in SET STATISTICS IO output

    I had worked on multiple issues for SQL Server 2005 regarding 511. Wanted to demonstrate how easy it is to troubleshoot this on SQL Server 2008 with XEvents:
    Troubleshooting Error 511 using XEvents

    Ran into a Compile Lock blocking issue because the Stored Procedure being used had an Open Symmetric Key command in it. Here is the post talking about the issue and how to workaround it:
    Open Symmetric Key command prevents plan caching

    I ended up doing a sequel for the Scan Count values w.r.t. Joins in the post below:
    Scan Count meaning in SET STATISTICS IO output: Part 2

    Technorati Tags: ,,