Upsize from mdb to SQL Express 2005

Hi,
i'm working on a project for convert from a mdb AccessXP(2002)
application backend to SQL Server Express 2005.

In the past i convertend a similar backend to SQL Server 2000.

My problem is: I have many fields on mdb backend with spaces in field
name (i know bad thing)

With SQL Server 2000 I had no problem because it recognize the field
correctly with syntax [field name]

but now with SQL Server Express 2005 i'm experiencing problems....in
fact it wants to write the field in this way 'field name'.

Now...what do u suggest me?

Go back to SQL Server 2000 or change all the field names? this is
because with the character ' apex i have many problem in SQL syntax.

Thank u in advance

Bye
Rob

Re: Upsize from mdb to SQL Express 2005

On Thu, 22 Nov 2007 05:29:16 -0800 (PST), Yoda
wrote:

SQL Server recognizes the [field name] syntax as well, so you could
rename those fields (see Alter Table).

-Tom.



>Hi,
>i'm working on a project for convert from a mdb AccessXP(2002)
>application backend to SQL Server Express 2005.
>
>In the past i convertend a similar backend to SQL Server 2000.
>
>My problem is: I have many fields on mdb backend with spaces in field
>name (i know bad thing)
>
>With SQL Server 2000 I had no problem because it recognize the field
>correctly with syntax [field name]
>
>but now with SQL Server Express 2005 i'm experiencing problems....in
>fact it wants to write the field in this way 'field name'.
>
>Now...what do u suggest me?
>
>Go back to SQL Server 2000 or change all the field names? this is
>because with the character ' apex i have many problem in SQL syntax.
>
>Thank u in advance
>
>Bye
>Rob

Re: Upsize from mdb to SQL Express 2005

Yoda (rmotto@teaminf.com) writes:
> i'm working on a project for convert from a mdb AccessXP(2002)
> application backend to SQL Server Express 2005.
>
> In the past i convertend a similar backend to SQL Server 2000.
>
> My problem is: I have many fields on mdb backend with spaces in field
> name (i know bad thing)
>
> With SQL Server 2000 I had no problem because it recognize the field
> correctly with syntax [field name]
>
> but now with SQL Server Express 2005 i'm experiencing problems....in
> fact it wants to write the field in this way 'field name'.

Where exactly is this happening?

According to the ANSI standard, the proper delimiter for identifiers with
special characters in them is double quote ("). However, SQL Server also
recognizes [], and in my experience, SQL Server strongly favours [] over
"".

Single quotes always delimit string literals.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Upsize from mdb to SQL Express 2005

On 22 Nov, 23:44, Erland Sommarskog wrote:
> Yoda (rmo...@teaminf.com) writes:
> > i'm working on a project for convert from a mdb AccessXP(2002)
> > application backend to SQL Server Express 2005.
>
> > In the past i convertend a similar backend to SQL Server 2000.
>
> > My problem is: I have many fields on mdb backend with spaces in field
> > name (i know bad thing)
>
> > With SQL Server 2000 I had no problem because it recognize the field
> > correctly with syntax [field name]
>
> > but now with SQL Server Express 2005 i'm experiencing problems....in
> > fact it wants to write the field in this way 'field name'.
>
> Where exactly is this happening?
>
> According to the ANSI standard, the proper delimiter for identifiers with
> special characters in them is double quote ("). However, SQL Server also
> recognizes [], and in my experience, SQL Server strongly favours [] over
> "".
>
> Single quotes always delimit string literals.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Hi,
my frontend is AccessXP(2002) my backend is SQL Express 2005. In the
rowsource of a combobox or even in view or sp design I can wirte SQL
Statement only in this way 'field name' ......it doesn't recognize
correctly [field name].
This is not valid for SQL 2000 in which i can use [field name].

Thank u

Re: Upsize from mdb to SQL Express 2005

Sorry...my mistake....the syntax i mean is

SELECT "Articoli"."ID ARTICOLO" FROM "Articoli" with double quotesnot
single quotes

I just want to know if SQL Express 2005 can work the same way SQL 2000
works....with [] instead of ""

I think the problem is related to Access Frontend....because if i use
New Query From Management Studio Express it works with []
Thank you
Rob

Re: Upsize from mdb to SQL Express 2005

Yoda (rmotto@teaminf.com) writes:
> Sorry...my mistake....the syntax i mean is
>
> SELECT "Articoli"."ID ARTICOLO" FROM "Articoli" with double quotesnot
> single quotes
>
> I just want to know if SQL Express 2005 can work the same way SQL 2000
> works....with [] instead of ""
>
> I think the problem is related to Access Frontend....because if i use
> New Query From Management Studio Express it works with []

Yes, SQL Express can use []. Why you get double quotes in Access I have
no idea - then again, Access is a product of which I have no knowledge.
You may get better help with Access-specific questions in an Accesss
newsgroup.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx