By: Alejandro Villarreal During database development, unique indexes (or constraints) are very important part of the design. There’s always data for which you do not want to have repeated values, like usernames or email addresses. Different applications might have their own uniqueness requirements, but they all usually have at least one. It’s great when we can identify those requirements from the start and create the appropriate indexes up front. But what if your application now needs to enforce uniqueness on the values of a million-row table which has tons of duplicates in the production environment? A naïve solution (and definitely not appropriate for a production environment) wou...

By: Alejandro Villarreal Debugging replication-related problems can be extremely hard if you do not have access to an environment with the exact same replication architecture where the problem occurred in the first place. As consultants working for several clients, it is not feasible to have an exact replica of each of their environments, so a feasible solution is to set up a copy of the desired environment “on demand”, on virtual machines. This is exactly what we did a couple of weeks back, and although we finally got replication to work, we had to deal with this error first: Error 18483: Could not connect to server '<Server>\<Instance>' because '<login>' is not define...

By Alejandro Villarreal A few months ago we developed a slightly complex service that made use of query notifications to keep an in-memory copy of some database tables. The service worked like a charm, it passed QA, UAT, and went into production, where it has been working ever since. Recently, however, our client had a vague suspicion that something weird was happening with it, but could not pinpoint exactly what, and they only felt like this after a couple of weeks of the service working just fine. Restarting the service dissipated whatever problem the service appeared to be having, so we all lived with that for a while… until they found actual evidence that the service was not respondi...

By Alejandro Villarreal Distributed transactions can be of great help when dealing with complex operations that must be atomic across servers, but then again for their very nature –distributed– they can be hard to debug when something fails. A good example of this is an error I encountered recently. Here’s a bit of background: We have a Web Application hosted in Server01 sending messages to a WCF Service through an MSMQ endpoint (we’re using transactional queues to leverage their reliability), and this Service saves the content of the messages it receives in a SQL Server database hosted in a remote server (Server02). The queue in Server01 plus the database in Server02 make this a distri...

By Carina C. Terms used in Reporting Services: ·         Data Source ·         Data Set: query related to a data source ·         Data Region: object that displays data Types of Data Regions: ·         Table: Tabular report ·         Matrix ·         List: free form enlisted data ·         Graphics Creating Reports in Reporting Services Reporting Services offers the following ways to create a Report: ·         Add New Report Wizard (Templates) ·         Add/Add New Item. Creates a blank report ·         Import Reports. It allows the importation of reports from an Access database Report using Tables Data View 1.       Select <New Dataset…> 2.       Assign a na...

By Otoniel D. Hi everyone, last week I had to make a quick research about Dynamic Management Views (DMV) so I could create a quick technical session to give to my colleagues; a quick overview about this particular subject. I really have to admit that I enjoyed it a lot. Mainly because I believe that DMVs are one of the greatest features that Microsoft SQL Server have. But now that I’m writing this post, I found out that DMV’s are actually categorized. This means basically two things. First, that the sys schema is so intuitive that it allows you to use it easily and second that I need to look at the MSDN SQL Server 2008 Books Online more closely. J Let’s take a look about how do I fin...

By Otoniel Diaz We are always looking our databases as a bunch of tables and columns designed to store the data according to an application requirements. But when you are in charge of talking care of these tables and rows you actually need to review you start to wonder how to do it, and how SQL Server does manage the so called Metadata. Microsoft SQL Server 2008 metadata is represented in system views which are divided in 16 main categories.   System views objects can belong to a different type of objects which can be: 1.       Dynamic Management View 2.       Dynamic Management Function () 3.       Catalog View 4.       System Administration The most famous view may be is the ...

By Gabriel Palma   The situation A customer asked recently for a quick daily report of about-to-expire items, he wanted something simple that could be sent via email to the people already registered in the database. Acquiring the information was very simple (as simple as writing a query). The problem was getting those results inside an email and sending it automatically every day. We came up with a scheduled SQL Server job running daily, getting and shipping the results without passing through .Net code.   The steps Step 1. Write the query to retrieve the raw data.   I used AdventureWorks database for the following example. Here I’ll get a report of work orders with due date ...

Have you ever faced the need to send a list of parameters to a stored procedure? A nice and simple solution is the user-defined table type, which is available in SQL Server 2008.   First, we need to create the user defined type:   CREATE TYPE [dbo].[SongsType] AS TABLE(        [Id] [int] NOT NULL,        [Name] [varchar](100) NOT NULL,        [Artist] [varchar](100) NULL, )   In our code, we will create and populate a DataTable:   var dtSongs = new DataTable("Songs");             dtSongs.Columns.Add(new DataColumn("Id", typeof(int))); dtSongs.Columns.Add(new DataColumn("Name", typeof(string))); dtSongs.Columns.Add(new DataColumn("Art...

“Command Timeout” is not equal to “Connection Timeout”.   A few days ago I had an application that was throwing the following error:   “Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding…”   It was an obvious error, a database timeout. I reviewed the Connection String and it didn’t have the property “Connection Timeout” (The default connection timeout is 30 seconds). So I added the property and set it to 600 seconds.   <connectionStrings> <add name="ConnectionString" connectionString="Data Source=localhost\SQLEXPRESS;Integrated Security=SSPI;Connection Timeout=600;" /> </connecti...