Sweet SQL Lass: "I can't take this database offline!" Simple Example of Cursor. 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.
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. Extended properties can be used for the following: Specifying a caption for a table, view, or column. Applications can then use the same caption in a user interface that displays information from that table, view, or column.Specifying an input mask for a column so that applications can validate data before running a Transact-SQL statement.
Note Extended properties should not be used to hide sensitive information about an object. Defining Extended Properties Table.
New Installation (Reporting Services) 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. 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. I had an old PHPbb forum running, that needed to be upgraded to a Microsoft environment entirely. I could have just kept MySQL and Apache server, but instead I decided to migrate the entire concept of PHPbb to a YAF-forum. Setup ODBC Connection to MySQL Database This article will not go through how to setup a MySQL or Microsoft SQL server, but make sure you have downloaded at least the MySQL ODBC Connector from here.
For this article, I downloaded the MySQL ODBC Connector 5.1. The setup of this connector is pretty simple: Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. History. 6 Reference Manual :: 13.7.1.4 GRANT Syntax. ConnectionStrings.com - 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. These columns automatically assign a value for each new row inserted. But what if you want to insert your own value into the column? First we'll need a table to work with.
Use tempdb GO IF OBJECT_ID('IdentityTable') IS NOT NULL DROP TABLE IdentityTable GO CREATE TABLE IdentityTable ( TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY, TheValue NVARCHAR(20) NOT NULL ) GO Simply trying to INSERT a value into the identity column generates an error: INSERT IdentityTable(TheIdentity, TheValue) VALUES (1, 'First Row') GO Msg 544, Level 16, State 1, Line 3 Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF.
The trick is to enable IDENTITY_INSERT for the table. SET IDENTITY_INSERT IdentityTable ON INSERT IdentityTable(TheIdentity, TheValue) VALUES (3, 'First Row') SET IDENTITY_INSERT IdentityTable OFF. Best approach to remove time part of datetime in SQL Server. ADO Connection Strings. Overview Generally, one of the first steps when you are trying to work with databases is open it. 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...
ODBC DSN Less Connection ODBC Driver for dBASE strConnection = _T("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;" "Dbq=c:\\DatabasePath;"); Note: You must specify the filename in the SQL statement... CString strQuery = _T("Select Name, Address From Clients.dbf"); ODBC Driver for Excel strConnection = _T("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;" bq=C:\\DatabasePath\\DBSpreadSheet.xls;DefaultDir=c:\\databasepath;"); ODBC Driver for Text strConnection = _T("Driver={Microsoft Text Driver (*.txt; *.csv)};" "Dbq=C:\\DatabasePath\\;Extensions=asc,csv,tab,txt;"); Using SSL. Caché - World's fastest high performance object database. Unified Multi-Model Architecture Within Caché, data can be modeled and stored as tables, objects, or multidimensional arrays (hierarchies).
Different models can seamlessly access data – without the need for performance-killing mapping between models. Built-in support for dynamic data objects (such as XML and JSON) enables easy interoperability and rapid development of Web applications. High-Performance Queries SQL is the lingua franca for querying Caché data across all data models. Distributed Systems InterSystems Enterprise Cache Protocol allows a network of many servers to behave as a single data store, dramatically enhancing the scalability and performance of distributed applications. High Availability through Database Mirroring Caché Database Mirrors do not require large investments in hardware, support, operating system licenses, or storage.
Security Model Secure data-at-rest and data-in-motion while minimizing the burden on application performance. Application Development. Asp.net - Entity Framework v2 doesn't support sql 2000. AppendText Method. Public static StreamWriter AppendText( string path ) Parameters path Type: System.String The path to the file to append to. Return Value Type: System.IO.StreamWriterA stream writer that appends UTF-8 encoded text to the specified file or to a new file. The path parameter is not case-sensitive. The following example appends text to a file. .NET Framework Supported in: 4.5.1, 4.5, 4, 3.5, 3.0, 2.0, 1.1, 1.0 .NET Framework Client Profile Supported in: 4, 3.5 SP1 .NET for Windows Phone apps Supported in: Windows Phone 8.1, Windows Phone 8, Silverlight 8.1 Windows Phone 8.1, Windows Phone 8, Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)
Database Design - Introduction. Also available on tomjewett.com: 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. First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:Uniqueidentifiers are also referred to as GUIDs. DECLARE @GUID uniqueidentifierSET @GUID = NEWID()INSERT Item VALUES (@GUID,'Yak Hoof') So, to answer Kim's question: Sorry, there isn't a way to get the value of a uniqueidentifier column after an insert. The major advantage of using GUIDs is that they are unique across all space and time. The main disadvantage to using GUIDs as key values is that they are BIG.
Not only that, but they're just plain hard to read. -SQLGuru. 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?) Convenience. It's a shorthand that allows you to write a method in the same place you are going to use it. Benefits: Reduced typing. Lambda expressions should be short. How do we define a lambda expression? Lambda basic definition: Parameters => Executed code. Simple example n => n % 2 == 1 n is the input parameter n % 2 == 1 is the expression You can read n => n % 2 == 1 like: "input parameter named n goes to anonymous function which returns true if the input is odd". Same example (now execute the lambda): 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. It is still possible to use EdmGen.exe to generate ADO.NET Entity framework models for SQL Server 2000 databases. 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. 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. Problem I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately so that not everyone can select the data? In a previous tip, Filtering Columns in SQL Server Using Views we looked at using Views. In this tip we cover how this can be done with column-level permissions.
Solution One of the easiest ways to do this is through the use of views. First, let's set up a table to use: CREATE TABLE dbo.Employee ( EmployeeID INT IDENTITY(1,1), FirstName VARCHAR(20) NOT NULL, MiddleName VARCHAR(20) NULL, SurName VARCHAR(20) NOT NULL, SSN CHAR(9) NOT NULL, Salary INT NOT NULL, CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID) ); And we'll go ahead and load it up with a couple of entries for a proof of concept: Let's go ahead and set up two users and two roles for this demonstration: This sets up two levels of users: HR Employees (role HR_Employee, of which SalaryPerson is one) and HR Interns (role HR_Intern, played by SummerIntern).
(Figure 1) Best practices for storing postal addresses in a database (RDBMS) Alter Schema – Move object to another schema.