background preloader


Facebook Twitter

Gunnar Peipman's ASP.NET blog - Using Visual Studio database projects in real life. Visual Studio database projects are good to support software development.

Gunnar Peipman's ASP.NET blog - Using Visual Studio database projects in real life

I have successfully used database projects for years and I think it’s time to share my experiences also to global developers audience. In this posting I will introduce you how to effectively use database projects so developers are working with up-to-date schema and test data all the time. Standardizing development environments To make things easier I will always use standardized development environments. It makes also life easier for developers because all problems that appear in environments are similar and after some time there are known solutions for all environment specific problems. SQL Code Snippets. Scenario: Creating an automatic report or collecting data about existing jobs.

SQL Code Snippets

In trying to translate the field freq_interval for freq_type 8 (weekly) to determine what days a job runs. SQL uses a code of Sunday=1, Monday=2, Tuesday=4, Wednesday=8, Thursday=16, Friday=32, Saturday=64. So for those days alone it’s an easy translation but what about Monday/Wednesday/Friday well that’s 42. They get that by adding 2+8+32. SQL Server Multi-Subnet Clustering (SQL Server) A SQL Server multi-subnet failover cluster is a configuration where each failover cluster node is connected to a different subnet or different set of subnets.

SQL Server Multi-Subnet Clustering (SQL Server)

These subnets can be in the same location or in geographically dispersed sites. Clustering across geographically dispersed sites is sometimes referred to as stretch clusters. As there is no shared storage that all the nodes can access, data should be replicated between the data storage on the multiple subnets. With data replication, there is more than one copy of the data available. Client Requests Not Redirecting to Failover Partner. Hi Jimmy, In this case, I am initiating the failover by clicking the failover button on the database properties, mirroring form from within Management Studio.

Client Requests Not Redirecting to Failover Partner

It behaves no differently if I stop the SQL Server service and force a failover that way. I am killing the connection at the database. I don't have the rights to cycle the web service. If killing the session at the database level does not accomplish the desired effect and I need to cycle the web service, let me know. This message returned to the browser is the same whether the database connection is present or not. Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "WebDB" requested by the login.

/search/searchResults.asp, line 55 I'll look into the provider version. Is this non-transparent failover specific to IIS connections, or does that apply across the board? Using the SQL WHERE clause - learn how to filter rows in your database queries at In our introduction to the SQL SELECT statement we saw how to select specific columns from a table.

Using the SQL WHERE clause - learn how to filter rows in your database queries at

In this article we will see how the WHERE clause can be used to select specific rows from the table that match certain specified criteria or conditions. The WHERE clause is an optional clause in the following SQL statements: SELECT statmentsDELETE statementsUPDATE statements. Ten Common SQL Programming Mistakes. Database delivery patterns & practices STAGE 2 Continuous Integration: Fundamentals It is not always easy to spot "antipatterns" in your SQL, especially in more complex queries.

Ten Common SQL Programming Mistakes

In this article, Plamen demonstrates some of the most common SQL coding errors that he encounters, explains their root cause, and illustrates potential solutions. Programming in SQL can be both a fun and a challenging task. Those with a background in traditional programming languages such as Java, C, C++, and VB often find it difficult to adapt to the "set-based" mindset. My intention with this article was to highlight some of the more common mistakes that I've seen people make when coding SQL. SELECT TOP(10) mistake.

Fixing the Entity Framework designer “Generate Database from Model” T4 template. The Entity Framework Designer include a reverse engineer feature called “Generate Database from Model” that enables a “Model first” workflow, and also enable you to persist any model modification in a new database.

Fixing the Entity Framework designer “Generate Database from Model” T4 template

The T4 template used for this feature supports both SQL Server and SQL Server Compact, but unfortunately lacks consistency in it’s use of the GO keyword. GO is used after each statement, except FOREIGN KEY constraint creation statements. Apart for being inconsistent, this also prevents SQL Server Compact Toolbox from executing the script, without a number of manual edits. I have proposed a fix on Codeplex for the next version of the designer, but it will not happen until version 7 (if ever). So in the meantime, I have updated the template to fix this, you can start using it today as replacement for the current one as follows: 1: Download my updated T4 file from here. 4: Generate the script.

How to share data between stored procedures. An SQL text by Erland Sommarskog, SQL Server MVP.

How to share data between stored procedures

Most recent update 2013-11-02. Introduction. Slow in the Application, Fast in SSMS? An SQL text by Erland Sommarskog, SQL Server MVP.

Slow in the Application, Fast in SSMS?

Last revision: 2013-08-30. A Visual Explanation of SQL Joins. I love the concept, though, so let's see if we can make it work.

A Visual Explanation of SQL Joins

Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each. id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams. There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram: