Category: SQL Server

SQL Server: Clone a database with all its objects

Posted on by

A previous post covered the steps to copy table structure and data to a new or existing database.

To copy all the objects (tables, users, constraints, etc.) from one SQL Server database to another database, use the Generate Scripts function in SQL Server Management Studio.

  1. In the Object Explorer, right click the name of the database. Select Tasks > Generate Scripts…

  2. The Script Wizard will open. Click Next.

  3. Select the source database. If you want to copy ALL objects, make sure Script all objects in the selected database is checked. If the box is not checked, you will get a chance to choose the objects you want to include later.

    Click Next.

Continue reading

SQL Server or SQL Server Express: Connection Error: (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.

Posted on by

After a server containing a SQL Server database is restarted, you may get the error

Connection Error: (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.

Or you may get an error like the one below:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connection.s (provider: Shared Memory Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)”

Continue reading

SQL Server Express tutorial: Add a built-in user to a database

Posted on by
In IIS, several built-in Windows accounts are created automatically during installation. These include IUSR_MachineName, IWAM_MachineName, and ASPNET, each of which has specific privileges. Web applications will often require that the IUSR account have rights to connect to SQL Server Express.

Here’s how to give this account permissions in the SQL Server database, when the database is located on the same box as the application.

  1. Open  SQL Server Management Studio.
  2. Right click the folder “Users” in the path
    Databases > Database Name > Security

    2010-08-18_18.27.18.jpg

  3. Select New User. . . from the popup menu.

    2010-08-18_18.27.35.jpg

    4. Continue reading

SQL Server tutorial: Copy database tables to another database

Posted on by

To export the table structure and data from one SQL Server database to another existing or new database, use the Export Data… function in SQL Server Management Studio. This procedure will not copy any of the other database objects such as constraints, stored procedures, or users.

  1. In the Object Explorer, right click the name of the database. Select Tasks >Export Data…

    2. Continue reading

How to set up an ODBC Data Source for a SQL Server database on a Windows Server

Posted on by

Many web applications require that you set up an ODBC Data Source which serves as a sort of pointer to a particular database which specifies which driver and credentials to use to connect. Here’s how to set up one for SQL Server/SQL Server Express on a Windows 2003 server.

  1. Select Start > Administrative Tools > Data Sources(ODBC)

     odbc data sources

  2. Select the System DSN tab, and click the Add button.

    2010-08-18_17.58.49.jpg

Continue reading