background preloader


Facebook Twitter

Sweet SQL Lass: "I can't take this database offline!" An anguished cry, which I have heard twice today.

Sweet SQL Lass: "I can't take this database offline!"

The problem is, if you try to take a database offline, it will not actually complete until the current transaction has ended (or been forcibly killed), as there is a lock on the database. Killing a transaction is a bit untidy. As we find here "The ALTER DATABASE statement waits indefinitely if there is any lock on the database. " Simple Example of Cursor. UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2 This is the simplest example of the SQL Server Cursor.

Simple Example of Cursor

I have used this all the time for any use of Cursor in my T-SQL.DECLARE @AccountID INT DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR SELECT Account_IDFROM AccountsOPEN @getAccountIDFETCH NEXTFROM @getAccountID INTO @AccountIDWHILE @@FETCH_STATUS = 0BEGIN PRINT @AccountIDFETCH NEXTFROM @getAccountID INTO @AccountIDEND CLOSE @getAccountIDDEALLOCATE @getAccountID Reference: Pinal Dave ( BOL Like this: Like Loading... Using Extended Properties on Database Objects. In using extended properties, you can add text, such as descriptive or instructional content, add input masks, and add formatting rules as properties of objects in a database or of the database itself.

Using Extended Properties on Database Objects

For example, you can add an extended property to a schema, a schema's view, or to a column in the view. Because extended properties are stored in the database, all applications reading the properties can evaluate the object in the same way. This helps enforce consistency in the way data is treated by all the programs in the system.


New Installation (Reporting Services) Reporting Services is a collection of server and client applications used for business reporting.

New Installation (Reporting Services)

You can install server components on a single computer or on multiple computers. You can install the client components alongside server components or on separate workstations. To install server components, the computer must satisfy the hardware and software requirements. If the computer is not configured as an application server or if the operating system does not support HTTP.SYS, Reporting Services will not be available in the Components to Install page in the Installation Wizard. Reporting Services is installed through the SQL Server installation media. Parsing - How to strip all non-alphabetic characters from string in SQL Server. Current community your communities Sign up or log in to customize your list. more stack exchange communities Stack Exchange sign up log in tour help careers 2.0.

parsing - How to strip all non-alphabetic characters from string in SQL Server

Migrate MySQL to Microsoft SQL Server. Introduction This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005.

Migrate MySQL to Microsoft SQL Server

The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment. Background Initially, I started my search for an article on CodeProject regarding MySQL->MS SQL migration without any success. 6 Reference Manual. - Forgot that connection string? Get it here! How to Insert Values into an Identity Column in SQL Server. By Bill Graziano on 6 August 2007 | 6 Comments | Tags: Identity, INSERT Identity columns are commonly used as primary keys in database tables.

How to Insert Values into an Identity Column in SQL Server

These columns automatically assign a value for each new row inserted. Best approach to remove time part of datetime in SQL Server. I, personally, almost always use User Defined functions for this if dealing with SQL Server 2005 (or lower version), however, it should be noted that there are specific drawbacks to using UDF's, especially if applying them to WHERE clauses (see below and the comments on this answer for further details).

Best approach to remove time part of datetime in SQL Server

If using SQL Server 2008 (or higher) - see below. In fact, for most databases that I create, I add these UDF's in right near the start since I know there's a 99% chance I'm going to need them sooner or later. I create one for "date only" & "time only" (although the "date only" one is by far the most used of the two). Here's some links to a variety of date-related UDF's: Essential SQL Server Date, Time and DateTime FunctionsGet Date Only Function That last link shows no less than 3 different ways to getting the date only part of a datetime field and mentions some pros and cons of each approach.

ADO Connection Strings. Overview Generally, one of the first steps when you are trying to work with databases is open it.

ADO Connection Strings

You can find several types of those, and each have a different mode of connection. When you try to connect with your database sometimes, you don't know the correct connection string that you must use. It is for that I wrote this article. I wanted to compile the connection strings to the majority of known databases... Caché - World's fastest high performance object database. - Entity Framework v2 doesn't support sql 2000. Current community your communities Sign up or log in to customize your list. - Entity Framework v2 doesn't support sql 2000

AppendText Method. Public static StreamWriter AppendText( string path ) Parameters path. Database Design - Introduction. Also available on color tutorial, demo application, and video; for Web accessibility resources and consulting, please see The Enabled Web. This third edition of dbDesign is a general update, both to meet legal requirements for U.S. “Section 508” accessibility and to bring the code into compliance with the latest World Wide Web Consortium standards. In the process, I've tried to make the SQL examples as generic as possible, although you will still have to consult the documentation for your own database system. Graphics no longer require the SVG plugin; large-image and text-only views of each graphic are provided for all readers; the menu is now arranged by topic areas; and the print version (minus left-side navigation) is done automatically by a style sheet.

The second edition was largely motivated by the very helpful comments of Prof. The original site was the outgrowth of a previous book project, Practical Relational Database Design, by Wayne Dick and Tom Jewett. Uniqueidentifier vs. IDENTITY. By Sean Baird on 12 September 2000 | 21 Comments | Tags: Identity Kim writes: "with an identity column you can SELECT @@identity after inserting a row. How do you get the value of a uniqueidentifier column after an insert? Thanks! " Uniqueidentifiers are new to SQL 7.0, and behave a little differently than our friend the IDENTITY column. Read on for the answer to Kim's question along with the SQLGuru's opinions on which is better as a key value.

First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:Uniqueidentifiers are also referred to as GUIDs. Understand Lambda Expressions in 3 minutes. What is a Lambda Expression? A lambda expression is an anonymous function and it is mostly used to create delegates in LINQ. Simply put, it's a method without a declaration, i.e., access modifier, return value declaration, and name. Why do we need lambda expressions? (Why would we need to write a method without a name?) Using Entity Framework 4 with SQL Server 2000 Databases. The Visual Studio 2010 IDE does not support connecting to SQL Server 2000, including not allowing connections from the ADO.NET Entity Framework designer.

However, the ADO.NET Entity Framework 4 runtime continues to offer support for running with SQL Server 2000 databases. A number of people have asked what to do with existing ADO.NET Entity Framework models built against SQL Server 2000 when moving to Visual Studio 2010. Following are a few development options and items of note: - The ADO .NET Entity Framework 4 runtime fully supports SQL Server 2000. - Apps you previously built on top of SQL Server 2000 will continue to function as-is when you move those apps to the ADO.NET Entity Framework 4 runtime. - EdmGen.exe is a command line utility that can be used to create ADO.NET Entity Framework models. Thanks, Tim Laverty PM, Entity Framework. Sql - Is there common street addresses database design for all addresses of the world. Internationalization - What is the "best" way to store international addresses in a database. Plain freeform text. Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database « Journey to SQLAuthority.

Foreign Keys between databases - Microsoft SQL Server answers. Filtering SQL Server Columns Using Column Level Permissions. Best practices for storing postal addresses in a database (RDBMS) Alter Schema – Move object to another schema.

Time and Date