How do I create database with script

Question

0.00/5 (No votes)

See more:

Hello,
I've written a small application in VS 2010 with an .mdf shopperDB.mdf. I created the setup file in Advanced Installer and Included all pre-requisites, including the sql server express.
The Installation runs okay in a Windows XP virtual machine right up to the SQL server. However Upon Querying the database the error message:

[SQLExecDirect]{SQL_ERROR}:{SQL_STATE:42000}[Microsoft][ODBC SQL Server Driver][SQL Server]Directory lookup for the file "C:\Program Files\SHOPPER\shopperDB.mdf" failed with the operating system error 2(The file specified cannot be found)

how can I resolve this issue please

What I have tried:

SQL

The Database generation script:

    -- Inline SQL script
    USE [master]
    GO
    /****** Object:  Database [G:\DEPLOY PRACTICE\SHOPPER\SHOPPER\SHOPPERDB.MDF]        Script Date: 16/12/2017 20:18:17 ******/
    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'C:\Program Files\SHOPPER\SHOPPERDB.MDF')
    BEGIN
    CREATE DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] ON  PRIMARY 
    ( NAME = N'shopperDB', FILENAME = N'C:\Program Files\SHOPPER\shopperDB.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'shopperDB_log', FILENAME = N'C:\Program Files\SHOPPER\shopperDB_log.ldf' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET COMPATIBILITY_LEVEL =     100
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [C:\Program Files\SHOPPER\SHOPPERDB.MDF].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET ANSI_WARNINGS OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET ARITHABORT OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET AUTO_CLOSE ON 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET AUTO_SHRINK ON 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET AUTO_UPDATE_STATISTICS     ON 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET CURSOR_DEFAULT  GLOBAL 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET   CONCAT_NULL_YIELDS_NULL OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET NUMERIC_ROUNDABORT OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET QUOTED_IDENTIFIER OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET RECURSIVE_TRIGGERS OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET  DISABLE_BROKER 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET TRUSTWORTHY OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET PARAMETERIZATION SIMPLE 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET READ_COMMITTED_SNAPSHOT OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET HONOR_BROKER_PRIORITY OFF 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET RECOVERY SIMPLE 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET  MULTI_USER 
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET PAGE_VERIFY CHECKSUM  
    GO
    ALTER DATABASE [C:\Program Files\SHOPPER\SHOPPERDB.MDF] SET DB_CHAINING OFF 
    GO
    USE [C:\"Program Files"\"FUBUH GROUP"\SHOPPER\SHOPPERDB.MDF]
    GO
    /****** Object:  Table [dbo].[bits_available]    Script Date: 16/12/2017 20:18:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bits_available]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[bits_available](
............
............
............

Posted 17-Dec-17 8:45am

Zen'o_179

Updated 17-Dec-17 9:22am

PIEBALDconsult

v2

Add a Solution

Comments

[no name] 17-Dec-17 15:14pm

I did not read through carefully, but one Thing which draws my Attention is your database path "C:\Program Files\SHOPPER\". Since about XP (if I remember correctly) it is not longer allowed (without tricks) to have such files in the "Program Files" Folder.

PIEBALDconsult 17-Dec-17 15:23pm

Yeah, that certainly doesn't seem like the best place; "ProgramData" might be better.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-12-17T09:22:00

There are a couple of things you need to change here. SQL Server should be free to specify a file name - it's a poor idea to try and specify it yourself, particularly when you try to put a DB file under Program files.
This is because Program files (and folders under it) are generally read only - as a security measure - so Admin access is required to write to it. Allowing Sql to store it in it's own data area gets round that.

In addition, it only normally in development that an application is running in the same computer as the SQL Server instance - and if you are always expecting that then you have destroyed the point of using a server based system and installed a huge and complicated package for no good reason. So your fixed path is also unlikely to work anyway.

Think about what you are doing: if you need multiuser access (and thus a server based system) then let Sql handle where the db is stored. If you don't, then don't use SQL Server - use SqLite or even Access, and store your db in a sensible location: Where should I store my data?[^]