Tech blog

So, you have Microsoft SQL Server 2005 Management Studio (SSMS) and when trying to connect “Server name” drop down list is filled with sensitive data:

To clear this list, all you have to do is delete this file:
C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat.

Just remember, once you delete that file all of your previous connections will be lost. So make sure that you have any necessary IP addresses, usernames, and passwords that you’ll need to reconnect to your favorite/most used SQL Servers.

Linq allows to handle data easily. Linq to SQL tools are integrated in Visual Studio but is limited to can generate code only for  certain databases. To create ORM from .sdf database file read on…

What to do?
Copy next script to a file with .bat extension.

Change DATABASE_NAME, DATABASE_PASSWORD and NAMESPACE according to your needs.

@echo off
rem Input parameters
rem DATABASE_NAME should NOT contain spaces
set DATABASE_NAME=MyDatabase
set DATABASE_PASSWORD=mypass
set NAMESPACE=MyNamespace.Database

rem Set full path to SqlMetal.exe
rem For 64-bit Windows
set SQLMETAL="%ProgramW6432%\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe"
rem For 32-bit Windows
if "%ProgramW6432%"=="" set SQLMETAL="%ProgramFiles%\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe"

rem Other parameters
set DATABASE_PATH="%CD%\%DATABASE_NAME%.sdf"
set OUTPUT_CS_PATH="%CD%\%DATABASE_NAME%.cs"
set OUTPUT_DBML_PATH="%CD%\%DATABASE_NAME%.dbml"
set CONNECTION_STRING="Data Source=\"%DATABASE_PATH%\"; Password=\"%DATABASE_PASSWORD%\";"

rem Check and run sqlmetal
if not exist %SQLMETAL% goto error-missing-sqlmetal
%SQLMETAL% /conn:%CONNECTION_STRING% /dbml:%OUTPUT_DBML_PATH% /namespace:%NAMESPACE% /context:%DATABASE_NAME%DataContext /pluralize
%SQLMETAL% /conn:%CONNECTION_STRING% /code:%OUTPUT_CS_PATH% /namespace:%NAMESPACE% /context:%DATABASE_NAME%DataContext /pluralize
goto end

:error-missing-sqlmetal
echo Error: cannot find sqlmetal.exe
echo Search location: %SQLMETAL%
goto end

:end

Example

If the database is stored in C:\db\Website.sdf then copy script into C:\db\make.bat. Change DATABASE_NAME to Website and run the script. Script should generate C:\db\Website.cs and C:\db\Website.dbml files. Include the generated .cs file into your project.

Using the generated code

class Program
{
  static void Main()
  {
    //Suppose that Website.sdf has Page table with CreationDate column of type DateTime
    WebsiteDataContext db = new WebsiteDataContext(@"C:\db\Website.sdf");
    var list = from p in db.Pages
                 where p.CreationDate > DateTime.Today
                 selet p;

    //Show number of pages that match Linq query
    Console.WriteLine(list.Count() + " pages created today.");
  }
}

We tried to add new ntext nullable column in MS SQL table.  We were quite surprised to get the following error message:

Saving changes is not permitted.  The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

It wouldn’t let us add an “Allow Nulls” column?  That just seemed absurd.

Apparently, this is now the default behavior for any of the following changes to a table:

• Adding a new column to the middle of the table
• Dropping a column
• Changing column nullability
• Changing the order of the columns
• Changing the data type of a column

In order to prevent this default behavior, you simply need to uncheck a box in the table designer options using the Tools -> Options menu item

Expand the Designers section to display the Table and Database Designers options.

To change this behavior, just uncheck the “Prevent saving changes that require table re-creation” checkbox.

Original article was published at http://codeslammer.wordpress.com/2008/10/19/saving-changes-is-not-permitted/

Standard Security
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Standard Security alternative syntax
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;

Trusted Connection
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Trusted Connection alternative syntax
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connecting to an SQL Server instance
Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;

Trusted Connection from a CE device
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

Very often you need to migrate your MySQL database from one server to another, from Windows to Linux, Linux to Linux….

So, first you need to create dump of your existing database, to export it to a file. Here is the command:

mysqldump -P 3306 -u database_username -p database_name > /home/somedirectory/your_database.sql

-P - means port, if you don’y mention it default port is used (usually 3306)
-u – means database username
-p – means password but we didn’t specified it so after this command is executed we will be asked to type a password
database_name – is the name of your database you want to export
> location – at the end you specify directory where you want to create this dump

You probably want to zip this and you can use TAR (install Cygwin if working on Windows):
Here is the command:

tar cvf database.tar /home/somedirectory/your_database.sql

Transfer this file on other server using FTP… And unzip it if you want using command:

tar -xvf database.tar

Then import the database:

mysql -P 3306 -u  database_username -p new_existing_database_name < /home/somedirectory/public_ftp/your_database.sql

-P - means port, if you don’y mention it default port is used (usually 3306)
-u – means database username
-p – means password but we didn’t specified it so after this command is executed we will be asked to type a password
new_existing_database_name – is the name of your existing database on new server where you want to import data
< location – at the end you specify from which directory you want to import database

And thats it…