ClickOnce path names are too long for SQL Server Express identifiers

I'm posting this in the ClickOnce forums as well...

My application doesn't include the .mdf and .ldf files, rather it creates the database the first time the application is run using a script that is included in the build. I want to create the database in the ClickOnce data directory. The problem I'm having is that SQL Server complains when I execute the CREATE DATABASE command as follows:

CREATE DATABASE [C:\Documents and Settings\xxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\DataiFieldMobile.mdf] ON PRIMARY
( NAME = N'myDatabase', FILENAME = N'C:\Documents and Settings\xxxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\Data\myDatabase.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'myDatabase_log', FILENAME = N'C:\Documents and Settings\xxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\Data\myDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

Here's the error:

System.Data.SqlClient.SqlException "The identifier that starts with 'C:\Documents and Settings\xxxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.00' is too long. Maximum length is 128."

The problem lies in the name of the database. This is restricted to 128 characters only since it is an identifier like table name or index name or column name. So you need to change the database name by not specifying the full path of the MDF file there.|||

Umachandar Jayachandran - MS wrote:

The problem lies in the name of the database. This is restricted to 128 characters only since it is an identifier like table name or index name or column name. So you need to change the database name by not specifying the full path of the MDF file there.

Thanks for the prompt reply... however, I am not a T-SQL expert... how exactly do I change my script? Do I create a variable for the database name and then use it in the CREATE DATABASE command?

|||

I'm using the script generated by SQL Server. The first thing I tried doing was replacing the pathnames with a token. The application replaces the tokens with the ClickOnce data path, etc. Here's the first part of the script... How exactly do I need to modify the script?

USE [master]

GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF')

BEGIN

CREATE DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] ON PRIMARY

( NAME = N'iFieldMobile', FILENAME = N'D:\Projects\DotNet\TabletPC\Release\iField 1.2.0.20\App\Data\iFieldMobile.mdf' , SIZE = 2112KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'iFieldMobile_log', FILENAME = N'D:\Projects\DotNet\TabletPC\Release\iField 1.2.0.20\App\Data\iFieldMobile_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

END

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF', @.new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF].[dbo].[sp_fulltext_database] @.action = 'enable'

end

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_NULLS OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_PADDING OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_WARNINGS OFF

GO

|||The issue is that the MDF file name cannot exceed 128 characters if you are using it as database name also. So you have to either specify a different name for the database or specify a shorter MDF file name. I am not sure what generates this script. Maybe you will have to post this question in the SQL Server Express forum here.