Query Access & MS SQL

Are syntax of queries same in MS Access and MS SQL 2005?
I am asking because I have lot of queries in Access database. Do I have to
change every query when I move to MS SQL 2005?
Thanks!!!Hi
No. Most will work the same, but certain Access specific implementations of
queries will not.
If a query does not work with SQL Server 2000, it won't in SQL Server 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <john713@.hotmail.com> wrote in message
news:ddkllt$f2g$1@.ss405.t-com.hr...
> Are syntax of queries same in MS Access and MS SQL 2005?
> I am asking because I have lot of queries in Access database. Do I have to
> change every query when I move to MS SQL 2005?
> Thanks!!!
>|||Some basic queries might be portable between MS Access and SQL Server but if
you've used stuff like most of the VBA functions or some of the non-standard
SQL elements of Access then they'll need some work. If you are porting an
application to SQL Server then to get the most out of the platform you
should aim to convert your queries and other data-access code into SQL
stored procedures. That'll almost certainly involve a significant re-write
of your app. How far you need to go will depend partly on what you want to
gain from switching platforms.
Also, if your data model is of more than trivial complexity you should
certainly review it when upsizing. Many people use data models in Access
that are poorly normalized. These are OK in Access because Access often lets
you do non-relational stuff in the database to cope with problems like
missing keys or redundant data. In SQL Server you are more likely to have
serious problems with a weak data model. Of course, you may already be
totally confident that your data is strictly Third Normal Form, which will
give you a head start.
Hope this helps.
David Portas
SQL Server MVP
--|||Thanks David,
there are no VBA elements, because I am using VB 6.0 and Access as database,
but some queries are not in VB code, but in Access database, like cross tab
queries.|||John:
Cross-tab queries in Access have no equivalent in SQL Server. Also, as was
mentioned before by others in this thread, VB functions in Access won't work
in SQL.
You may want to check out the following: "Microsoft Access Developer's Guide
to SQL Server" by Mary Chipman and Andy Baron (ISBN 0-672-31944-6), availabl
e
at Amazon.com for under $10 used.
Going through an upgrade from MDB to ADP/SQL I learned to watch for the
"Now()" function in many Access queries, which were replaced with
"GETDATE()". You will need to see what functions are in use.
Good luck.
Toddsql

Query - pop-up menu for user data entry

Using the following syntax:
Select fname, lastn
From list
Where fname like 'jones'
I need the syntax that the user can enter a name to perform a query (the
user will enter a name on a pop-up menu before the query is performed).Roy,
You didn't say in what context you would be using this, so the below-code
assumes you are using a Stored Procedure:
CREATE STORED PROCEDURE [dbo].[usp_FindName]
@.LName varchar(100)=''
AS
IF @.LName<>''
BEGIN
Select fname, lastn
From list
Where fname like @.LName
END
GO
Hope thi assists,
Tony
"ROY A. DAY" wrote:

> Using the following syntax:
> Select fname, lastn
> From list
> Where fname like 'jones'
> I need the syntax that the user can enter a name to perform a query (the
> user will enter a name on a pop-up menu before the query is performed).

Query

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '.'.
/verslag/MIncSum4.asp, line 9
This is the error i get when running a query.shown below:
line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID = V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit = V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
(V_Transaksie.Maand =" & request.form("Maand") & ") AND
(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
line8:set rstMain = CreateObject("ADODB.Recordset")
9: rstMain.Open sql, _
10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
Source=172.16.4.180",1,4
Any idea what might be cuasing the error?One thing you could try is : extract the sql statement , hard code in some
some values and try to execute in SQL Query Analyzer
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"amatuer" <njoosub@.gmail.com> wrote in message
news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Line 1: Incorrect syntax near '.'.
> /verslag/MIncSum4.asp, line 9
> This is the error i get when running a query.shown below:
> line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> line8:set rstMain = CreateObject("ADODB.Recordset")
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> Source=172.16.4.180",1,4
> Any idea what might be cuasing the error?
>|||I tried that,it gives the same error in sql server as well.
any other ideas?
Jack Vamvas wrote:
> One thing you could try is : extract the sql statement , hard code in some
> some values and try to execute in SQL Query Analyzer
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> > Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> > Line 1: Incorrect syntax near '.'.
> >
> > /verslag/MIncSum4.asp, line 9
> >
> > This is the error i get when running a query.shown below:
> >
> > line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> > V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> > V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> > V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => > V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => > V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> > (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> > (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> > (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> > (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> > V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> >
> > line8:set rstMain = CreateObject("ADODB.Recordset")
> > 9: rstMain.Open sql, _
> > 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> > ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> > Source=172.16.4.180",1,4
> >
> > Any idea what might be cuasing the error?
> >|||On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
>Microsoft OLE DB Provider for SQL Server error '80040e14'
>Line 1: Incorrect syntax near '.'.
>/verslag/MIncSum4.asp, line 9
>This is the error i get when running a query.shown below:
>line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
>V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
>V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =>V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =>V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
>(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
>(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
>(V_Transaksie.Maand =" & request.form("Maand") & ") AND
>(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
>line8:set rstMain = CreateObject("ADODB.Recordset")
>9: rstMain.Open sql, _
>10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
>ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
>Source=172.16.4.180",1,4
>Any idea what might be cuasing the error?
Hi amatuer,
The SUM function needs parentheses. So for instance, change
> SELECT Sum V_Transaksie.Aantal As Total,
to
SELECT Sum(V_Transaksie.Aantal) As Total,
And so on for the other totals in the select list.
--
Hugo Kornelis, SQL Server MVP|||that helped. thanx Hugo.
Although my query still nt working the way i want it to...lol
Hugo Kornelis wrote:
> On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
> >Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> >Line 1: Incorrect syntax near '.'.
> >
> >/verslag/MIncSum4.asp, line 9
> >
> >This is the error i get when running a query.shown below:
> >
> >line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> >V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> >V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> >V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => >V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => >V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> >(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> >(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> >(V_Transaksie.Maand =" & request.form("Maand") & ") AND
> >(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> >V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> >
> >line8:set rstMain = CreateObject("ADODB.Recordset")
> >9: rstMain.Open sql, _
> >10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> >ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> >Source=172.16.4.180",1,4
> >
> >Any idea what might be cuasing the error?
> Hi amatuer,
> The SUM function needs parentheses. So for instance, change
> > SELECT Sum V_Transaksie.Aantal As Total,
> to
> SELECT Sum(V_Transaksie.Aantal) As Total,
> And so on for the other totals in the select list.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
>that helped. thanx Hugo.
>Although my query still nt working the way i want it to...lol
Hi amatuer,
I'll be happy to help you further (as I'm sure several others are as
well). But to do that, we do need some more information:
- how do your tables look (please post CREATE TABLE statements,
including all constraints, properties, and indexes. You may omit
irrelevant columns);
- what does your data look like (please post INSERT statements with a
few well-chosen rows of sample data the nicely illustrate your problem);
- how should the results look like (please post expected results, for
example in tabular format - for readability, I suggest using a
fixed-size font and using spaces rather than tabs to layout the table).
Also see www.aspfaq.com/5006 for more tips on information to provide
when asking for help.
--
Hugo Kornelis, SQL Server MVP|||Hugo, i appreciate your willingness to help,but unfortunately i am nt
permitted to give out valuable info.so it seems i will hav to figure it
out by myself.
If i hav any othr questions i will let u know.
Thank you for ur time,it is highly appreciated
Hugo Kornelis wrote:
> On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
> >that helped. thanx Hugo.
> >Although my query still nt working the way i want it to...lol
> Hi amatuer,
> I'll be happy to help you further (as I'm sure several others are as
> well). But to do that, we do need some more information:
> - how do your tables look (please post CREATE TABLE statements,
> including all constraints, properties, and indexes. You may omit
> irrelevant columns);
> - what does your data look like (please post INSERT statements with a
> few well-chosen rows of sample data the nicely illustrate your problem);
> - how should the results look like (please post expected results, for
> example in tabular format - for readability, I suggest using a
> fixed-size font and using spaces rather than tabs to layout the table).
> Also see www.aspfaq.com/5006 for more tips on information to provide
> when asking for help.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
>Hugo, i appreciate your willingness to help,but unfortunately i am nt
>permitted to give out valuable info.so it seems i will hav to figure it
>out by myself.
>If i hav any othr questions i will let u know.
>Thank you for ur time,it is highly appreciated
Hi amatuer,
That's a common requirement. One way to work aroound that is to change
the data (and possibly even the table and column names), but keep the
structure intact. That is a bit more work, of course, but it can be
worth it if you're really stuck.
On the other hand, you learn more by figuring it out for yourself :-))
--
Hugo Kornelis, SQL Server MVP|||thanx Hugo, but my prob sorted.took me a while bt i gt it to do wat i
wanted it to do.
Hugo Kornelis wrote:
> On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
> >Hugo, i appreciate your willingness to help,but unfortunately i am nt
> >permitted to give out valuable info.so it seems i will hav to figure it
> >out by myself.
> >If i hav any othr questions i will let u know.
> >Thank you for ur time,it is highly appreciated
> Hi amatuer,
> That's a common requirement. One way to work aroound that is to change
> the data (and possibly even the table and column names), but keep the
> structure intact. That is a bit more work, of course, but it can be
> worth it if you're really stuck.
> On the other hand, you learn more by figuring it out for yourself :-))
> --
> Hugo Kornelis, SQL Server MVP

Query

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '.'.
/verslag/MIncSum4.asp, line 9
This is the error i get when running a query.shown below:
line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =
V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =
V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
(V_Transaksie.Maand =" & request.form("Maand") & ") AND
(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
line8:set rstMain = CreateObject("ADODB.Recordset")
9: rstMain.Open sql, _
10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
Source=172.16.4.180",1,4
Any idea what might be cuasing the error?One thing you could try is : extract the sql statement , hard code in some
some values and try to execute in SQL Query Analyzer
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"amatuer" <njoosub@.gmail.com> wrote in message
news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Line 1: Incorrect syntax near '.'.
> /verslag/MIncSum4.asp, line 9
> This is the error i get when running a query.shown below:
> line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =
> V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =
> V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> line8:set rstMain = CreateObject("ADODB.Recordset")
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> Source=172.16.4.180",1,4
> Any idea what might be cuasing the error?
>|||I tried that,it gives the same error in sql server as well.
any other ideas?
Jack Vamvas wrote:[vbcol=seagreen]
> One thing you could try is : extract the sql statement , hard code in some
> some values and try to execute in SQL Query Analyzer
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:436868
On 7 Jun 2006 01:28:19 -0700, amatuer wrote:

>Microsoft OLE DB Provider for SQL Server error '80040e14'
>Line 1: Incorrect syntax near '.'.
>/verslag/MIncSum4.asp, line 9
>This is the error i get when running a query.shown below:
>line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
>V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
>V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =
>V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =
>V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
>(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
>(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
>(V_Transaksie.Maand =" & request.form("Maand") & ") AND
>(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
>line8:set rstMain = CreateObject("ADODB.Recordset")
>9: rstMain.Open sql, _
>10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
>ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
>Source=172.16.4.180",1,4
>Any idea what might be cuasing the error?
Hi amatuer,
The SUM function needs parentheses. So for instance, change

> SELECT Sum V_Transaksie.Aantal As Total,
to
SELECT Sum(V_Transaksie.Aantal) As Total,
And so on for the other totals in the select list.
Hugo Kornelis, SQL Server MVP|||that helped. thanx Hugo.
Although my query still nt working the way i want it to...lol
Hugo Kornelis wrote:
> On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
>
> Hi amatuer,
> The SUM function needs parentheses. So for instance, change
>
> to
> SELECT Sum(V_Transaksie.Aantal) As Total,
> And so on for the other totals in the select list.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 02:36:57 -0700, amatuer wrote:

>that helped. thanx Hugo.
>Although my query still nt working the way i want it to...lol
Hi amatuer,
I'll be happy to help you further (as I'm sure several others are as
well). But to do that, we do need some more information:
- how do your tables look (please post CREATE TABLE statements,
including all constraints, properties, and indexes. You may omit
irrelevant columns);
- what does your data look like (please post INSERT statements with a
few well-chosen rows of sample data the nicely illustrate your problem);
- how should the results look like (please post expected results, for
example in tabular format - for readability, I suggest using a
fixed-size font and using spaces rather than tabs to layout the table).
Also see www.aspfaq.com/5006 for more tips on information to provide
when asking for help.
Hugo Kornelis, SQL Server MVP|||Hugo, i appreciate your willingness to help,but unfortunately i am nt
permitted to give out valuable info.so it seems i will hav to figure it
out by myself.
If i hav any othr questions i will let u know.
Thank you for ur time,it is highly appreciated
Hugo Kornelis wrote:
> On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
>
> Hi amatuer,
> I'll be happy to help you further (as I'm sure several others are as
> well). But to do that, we do need some more information:
> - how do your tables look (please post CREATE TABLE statements,
> including all constraints, properties, and indexes. You may omit
> irrelevant columns);
> - what does your data look like (please post INSERT statements with a
> few well-chosen rows of sample data the nicely illustrate your problem);
> - how should the results look like (please post expected results, for
> example in tabular format - for readability, I suggest using a
> fixed-size font and using spaces rather than tabs to layout the table).
> Also see www.aspfaq.com/5006 for more tips on information to provide
> when asking for help.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 04:42:01 -0700, amatuer wrote:

>Hugo, i appreciate your willingness to help,but unfortunately i am nt
>permitted to give out valuable info.so it seems i will hav to figure it
>out by myself.
>If i hav any othr questions i will let u know.
>Thank you for ur time,it is highly appreciated
Hi amatuer,
That's a common requirement. One way to work aroound that is to change
the data (and possibly even the table and column names), but keep the
structure intact. That is a bit more work, of course, but it can be
worth it if you're really stuck.
On the other hand, you learn more by figuring it out for yourself :-))
Hugo Kornelis, SQL Server MVP|||thanx Hugo, but my prob sorted.took me a while bt i gt it to do wat i
wanted it to do.
Hugo Kornelis wrote:
> On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
>
> Hi amatuer,
> That's a common requirement. One way to work aroound that is to change
> the data (and possibly even the table and column names), but keep the
> structure intact. That is a bit more work, of course, but it can be
> worth it if you're really stuck.
> On the other hand, you learn more by figuring it out for yourself :-))
> --
> Hugo Kornelis, SQL Server MVP

Qualified table name syntax

Im trying to write a generic data access layer that supports SQL CE and Im wondering if any type of schema qualifier can be placed in front of a table name when executing a sql statement.

I've tried soemthing like this

select * from dbo.Account

I get this error,

The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 19,Table name = account ]

It doesnt make really make sense to include a qualifier for sql ce but I just wanted to make sure that there wasnt some other syntax that I wasnt aware of.

Thanks.

Nope, as I describe in the book, the SQL engine for "SQL Server" Compact Edition is not SQL Server--it uses a subset so the concept of "ownership" or "schemas" does not exist--it simply confuses the little engine. See www.hitchhikerguides.net FMI.|||thanks for confirming my thoughts. I'll check out the guide.

QA tells me my table is ambiguous

Can someone help with this syntax? I have a non-sensicle example
below, but it illustrates the problem if you copy/paste into QA.

**********************************

use pubs
go

update authors set address = 'some address'
from authors a
inner join authors a2 on a.zip = a2.zip

--------------

Server: Msg 8154, Level 16, State 1, Line 2
The table 'authors' is ambiguous.

**********************************It needs to know which alias to update a or a2.

update a set address = 'some address'
from authors a
inner join authors a2 on a.zip = a2.zip

Jackie

<john.livermore@.inginix.com> wrote in message
news:1114025028.974990.86780@.o13g2000cwo.googlegro ups.com...
> Can someone help with this syntax? I have a non-sensicle example
> below, but it illustrates the problem if you copy/paste into QA.
> **********************************
> use pubs
> go
> update authors set address = 'some address'
> from authors a
> inner join authors a2 on a.zip = a2.zip
> --------------
> Server: Msg 8154, Level 16, State 1, Line 2
> The table 'authors' is ambiguous.
>
> **********************************|||thx!|||Of course, instead of the Microsoft proprietary syntax, you could also
write this statement with the ANSI SQL compliant syntax, as follows:

-- Note: The update is still non-sensicle...
UPDATE Authors
SET Address = (
SELECT 'some address'
FROM Authors A2
WHERE A2.zip = Authors.zip
)

HTH,
Gert-Jan

john.livermore@.inginix.com wrote:
> Can someone help with this syntax? I have a non-sensicle example
> below, but it illustrates the problem if you copy/paste into QA.
> **********************************
> use pubs
> go
> update authors set address = 'some address'
> from authors a
> inner join authors a2 on a.zip = a2.zip
> --------------
> Server: Msg 8154, Level 16, State 1, Line 2
> The table 'authors' is ambiguous.
> **********************************