No access at sql 2000 DB after restore on an other sql server

HI,
i've some trouble with a sql server 2000 db.
the db uses sql server auth
i make a backup from the DB and take the backup to an other sqlserver, i had
restored the database at ther new server.
i've create ^the db owner user on the new server and executed
sp_change_users_login 'Update_One', 'username', 'loginname'
now my asp .net application works fine. but i would like to modify some
tables from the DB if, i start query analyzer and login as the db owner and
start a query
select *
rom issues
at the restored db i recieved an error
"Server: Nachr.-Nr. 208, Schweregrad 16, Status 1, Zeile 1
Ungültiger Objektname 'issues'." means the Object issues is invalid, but th
e
table exists. the same query on the original db Server is ok i recieved the
right result
whats wrong?
Many Thanks for your helpflabs (flabs@.discussions.microsoft.com) writes:
> i've some trouble with a sql server 2000 db.
> the db uses sql server auth
> i make a backup from the DB and take the backup to an other sqlserver, i
> had restored the database at ther new server.
> i've create ^the db owner user on the new server and executed
> sp_change_users_login 'Update_One', 'username', 'loginname'
> now my asp .net application works fine. but i would like to modify some
> tables from the DB if, i start query analyzer and login as the db owner
> and start a query
> select *
> rom issues
> at the restored db i recieved an error
> "Server: Nachr.-Nr. 208, Schweregrad 16, Status 1, Zeile 1
> Ungltiger Objektname 'issues'." means the Object issues is invalid, but
> the table exists. the same query on the original db Server is ok i
> recieved the right result
Apparently your default schema on the server is not the schema where the
issues table is, nor is the table in the default schema of dbo. Note that
on SQL 2000 the default schema for a user is always the username.
It's not clear to me whether you did an sp_changedbowner of the database,
but you should probably have done that.
Run this query:
SELECT user_name(uid), *
FROM sysobjects
WHERE name= 'issues'
This should give you the owner/schema for the issues table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hej
no, i didn't run sp_changedbowner. why must i run the sp ?
the result of SELECT user_name(uid), *
geminiuser issues 341576255 U 5 26 1610620982 64 0 0 2006-08-11
16:02:18.570 0 64 0 U 1 8291 0 2006-08-11 16:02:18.570 0 0 0 0 0 18433 0
i find out, if i run select *
from geminiuser.issues i recieved the right result
run i only issues without geniniuser. before issue i recieved an error.
what happens if i run sp_changeDBowner, sure i think it change the owner of
the db? but some else?
must i change all tables, add prefix geminiuser. in my sql script, i don't
like this.
"Erland Sommarskog" wrote:

> flabs (flabs@.discussions.microsoft.com) writes:
> Apparently your default schema on the server is not the schema where the
> issues table is, nor is the table in the default schema of dbo. Note that
> on SQL 2000 the default schema for a user is always the username.
> It's not clear to me whether you did an sp_changedbowner of the database,
> but you should probably have done that.
> Run this query:
> SELECT user_name(uid), *
> FROM sysobjects
> WHERE name= 'issues'
> This should give you the owner/schema for the issues table.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||flabs (flabs@.discussions.microsoft.com) writes:
> no, i didn't run sp_changedbowner. why must i run the sp ?
> the result of SELECT user_name(uid), *
> geminiuser issues 341576255 U 5 26 1610620982
> 64 0 0 2006-08-11
> 16:02:18.570 0 64 0 U 1 8291 0 2006-08-11
> 16:02:18.570 0 0 0 0 0 18433 0
> i find out, if i run select *
> from geminiuser.issues i recieved the right result
> run i only issues without geniniuser. before issue i recieved an error.
> what happens if i run sp_changeDBowner, sure i think it change the owner
> of the db? but some else?
> must i change all tables, add prefix geminiuser. in my sql script, i don't
> like this.
The simplest is if you run with a user that has geminiuser as its default
schema. On SQL 2000, this means that you should run as geminiuser.
I don't know which login you are logged in as, but this login needs to
map to geminiuser. Note that if the login geminiuser owns the database,
the login geminiuser maps to the user dbo.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

next sp for sql server 2000

It has been a while since MS released service pack 3 for
sql server 2000. Can we expect a service pack for sql
server 2000 before the release of yukon ?
MS generally does not publish future SP information for SQL. Release of SPs
depends on the quantity, and nature(severity) of bug fixes that would be
part of the SP.
However ( IMHO) I would not be surprised to see one more SP before Yukon.
<anonymous@.discussions.microsoft.com> wrote in message
news:17e6001c42229$3785b6d0$a001280a@.phx.gbl...
> It has been a while since MS released service pack 3 for
> sql server 2000. Can we expect a service pack for sql
> server 2000 before the release of yukon ?

next sp for sql server 2000

It has been a while since MS released service pack 3 for
sql server 2000. Can we expect a service pack for sql
server 2000 before the release of yukon ?MS generally does not publish future SP information for SQL. Release of SPs
depends on the quantity, and nature(severity) of bug fixes that would be
part of the SP.
However ( IMHO) I would not be surprised to see one more SP before Yukon.
<anonymous@.discussions.microsoft.com> wrote in message
news:17e6001c42229$3785b6d0$a001280a@.phx
.gbl...
> It has been a while since MS released service pack 3 for
> sql server 2000. Can we expect a service pack for sql
> server 2000 before the release of yukon ?

newline character in sqlserver

what is the newline character in sqlserver.
My problem is ,I have to display text (say "Hi \n abcd\n hhh\n") where \n is the newline.
But when i print this in sqlserver storedproc,Its displaying as it is instead of newlines.

please help

thxyou can do it via asp.net when you pass in the string do a string.replace of newlines ..something like

txtstmtbdy.text.replace(environment.newline,"<br>")

and when you display it the html is automatically parsed by the browser..so you dont have to worry about messing with it in sql server.

hth|||I assume you mean displaying the results in Query Analyser? If so I believe its the good old fashioned ASCII 10+13

NEWID() Generation Code

hi,
i want the code for newid() in sqlserver 2000 , if anyone knows about it please send me the code.I need it for my customised application . It will be of great help.
Could you explain what exactly you're trying to achieve? I can guarantee you no one will send you the code.|||If your customized application is managed code, Guid.NewGuid() generates a new GUID.

If your app is native, HRESULT CoCreateGUID(GUID *pguid) does the same thing.

Newby: Administer a database remotely?

I'm working on a website remotely through a VPN and my client uses SQL
Server. There's a requirement for a database and my client uses SQL
Server 2000.

Is it possible to administer this database remotely, just like I do
with MySQL/PHP MyAdmin?

Please elaborate.

Thanks,

Marjorie"marjorie" <marjorie@.popkit.com> wrote in message
news:71f66b4d.0407271230.4c220eb1@.posting.google.c om...
> I'm working on a website remotely through a VPN and my client uses SQL
> Server. There's a requirement for a database and my client uses SQL
> Server 2000.
> Is it possible to administer this database remotely, just like I do
> with MySQL/PHP MyAdmin?
> Please elaborate.
> Thanks,
> Marjorie

I don't know how much administration you need, but if you're developing a
website and need to create / modify a database for it, you can install the
SQL Server client and use the osql command-line utility:

e.g.
c:\> osql -S<server address> -U<user name> -P<password
So I use
osql -Ssqlserver -Usa -Pmypassword.

The flags are case sensitive.
I don't know if this makes sense in the context of virtual private networks
though, as I've never played about with them...

Rowland.|||marjorie@.popkit.com (marjorie) wrote in message news:<71f66b4d.0407271230.4c220eb1@.posting.google.com>...
> I'm working on a website remotely through a VPN and my client uses SQL
> Server. There's a requirement for a database and my client uses SQL
> Server 2000.
> Is it possible to administer this database remotely, just like I do
> with MySQL/PHP MyAdmin?
> Please elaborate.
> Thanks,
> Marjorie

Yes, just install the client tools on your workstation, and make sure
the VPN is configured to allow you to connect to the server. This
article may help:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon

Newbie: UPDATE a Table

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:529989
Hi,
I want to insert some new data in a Table. There is a possibility that there
are duplicate rows .
An Insert is obviously not allowed due to PK violatons.
The new data reside on a temporary Table and there are NO duplicates!
Will an Update do?
I simply want to :
"If the row exists replace it, if not insert the new one"
TIA
-steveIf you know you are replacing the old rows when you have a match, then
why not just delete them and then insert this scrubbed data, like this?
BEGIN
DELETE FROM Foo
WHERE Foo.key_col
IN (SELECT key_col FROM Bar);
INSERT INTO Foo
SELECT * FROM Bar;
END;|||Ok, thanx!
But, for info, there is no command that will do this in one step ?
and in my case the PK consists of multiple columns. However i get an error:
DELETE FROM tblMeasQAHor
WHERE
(tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
tblMeasQAHor.Valide, tblMeasQAHor.val)
IN
(SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
why? i tried a few combinations and it doesn't work. If i only leave one
column then it's OK.
TIA
"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
> If you know you are replacing the old rows when you have a match, then
> why not just delete them and then insert this scrubbed data, like this?
>
> BEGIN
> DELETE FROM Foo
> WHERE Foo.key_col
> IN (SELECT key_col FROM Bar);
> INSERT INTO Foo
> SELECT * FROM Bar;
> END;
>|||You'll need to do this in 2 steps. Delete then Insert
For the delete, test this out before you execute it on production code.
Replace temp1
with whatever your temp table name is
DELETE FROM tblMeasQAHor
FROM tblMeasQAHor t1
JOIN temp1 t2 on t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val
"steve" <steve@.here.com> wrote in message
news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> Ok, thanx!
> But, for info, there is no command that will do this in one step ?
> and in my case the PK consists of multiple columns. However i get an
error:
> DELETE FROM tblMeasQAHor
> WHERE
> (tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
> tblMeasQAHor.Valide, tblMeasQAHor.val)
> IN
> (SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
>
> why? i tried a few combinations and it doesn't work. If i only leave one
> column then it's OK.
> TIA
> "--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
> 1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
>|||I just want to clarify. If you want to do it the way Joe recommends, you'll
need to do it in 2 steps. You could also do a correlated subquery
(untested)
INSERT INTO tblMeasQAHor
SELECT *
FROM temp1 t1
WHERE NOT EXISTS (
SELECT 'X' FROM tblMeasQAHor t2 WHERE t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val)
I'm not sure which you prefer. Also, I generally recommend using column
names in the insert and select. I don't know what the table looks like
structurally so I went with the select * approach.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:eY77X9vYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> You'll need to do this in 2 steps. Delete then Insert
> For the delete, test this out before you execute it on production code.
> Replace temp1
> with whatever your temp table name is
> DELETE FROM tblMeasQAHor
> FROM tblMeasQAHor t1
> JOIN temp1 t2 on t1.MENVid = t2.MENVid
> AND t1.date_time = t2.date_time
> AND t1.CodePol = t2.CodePol
> AND t1.Valide = t2.Valide
> AND t1.val = t2.val
>
> "steve" <steve@.here.com> wrote in message
> news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> error:
this?
>

NEWBIE: Not an Updatable Query

Hello NG,
SQLServer 2000
Background:
I have been working with an Access2K FE with Access2K BE Tables - I have
just moved all the BE tables to the SQLServer database - I have set up full
permissions to Add,Delete,Modify and Read all the records in all the tables.
All of my code is in the FE database.
2 Questions
1. I try to run the following query:
db.Execute ("UPDATE [Stats Daily Table - Temp] INNER JOIN [Production] ON
([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
Number]) AND ([Stats Daily Table - Temp].[Daily Date] = [Production].[Stats
Date]) " & _
"SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats
Daily Table - Temp]![LB Pulling Orders] + ([Production]![Orders] +
[Production]![WPL Orders]), [Stats Daily Table - Temp].[LB Pulling Lines] =
[Stats Daily Table - Temp]![LB Pulling Lines] + ([Production]![Lines] +
[Production]![WPL Lines]) " & _
"WHERE (([Production].[Department Number]=1) AND
([Production].[Area Number]=102) AND (([Production].[Portion]='LL Lower
Portion') Or ([Production].[Portion]='LU Upper Portion') Or
([Production].[Portion]='LE Leather Portion') Or ([Production].[Portion]='LC
Closeout Portion')));")
It gives me an error that this is not an Updatable Query - It did work
Access to Access.
How can I make this query work - do I have to put the query into the
SQLServer database as a function I need to call for? Do I just need to have
it worded differently for the BE to understand.
2. If this needs to be in the BE - How do I call the query from the FE?
Please Help - I am trying to upgrade to SQLServer - I See its benefits - but
if I cannot understand how to use it I guess I'll be stuck with Access2K's
limitations.
TIAFAH
RandyFor one thing get rid of those !'s in your query between the table and
column names. Try running the query in QA (you can copy n paste it from
here):
UPDATE [Stats Daily Table - Temp]
INNER JOIN [Production]
ON ([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
Number])
AND ([Stats Daily Table - Temp].[Daily Date] = [Production].[Stats Date])
SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats Daily Table -
Temp].[LB Pulling Orders] + ([Production].[Orders] + [Production].[WPL
Orders]),
[Stats Daily Table - Temp].[LB Pulling Lines] = [Stats Daily Table -
Temp].[LB Pulling Lines] + ([Production].[Lines] + [Production].[WPL Lines])
WHERE (([Production].[Department Number]=1)
AND ([Production].[Area Number]=102)
AND (([Production].[Portion]='LL Lower Portion')
OR ([Production].[Portion]='LU Upper Portion')
OR ([Production].[Portion]='LE Leather Portion')
OR ([Production].[Portion]='LC Closeout Portion')));
Also, are you running this using ASP.NET as a front end?
Thanks
"Randy Fritz" <Randyfritz@.sbcglobal.net> wrote in message
news:eD0BclsRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hello NG,
> SQLServer 2000
> Background:
> I have been working with an Access2K FE with Access2K BE Tables - I
> have
> just moved all the BE tables to the SQLServer database - I have set up
> full
> permissions to Add,Delete,Modify and Read all the records in all the
> tables.
> All of my code is in the FE database.
> 2 Questions
> 1. I try to run the following query:
> db.Execute ("UPDATE [Stats Daily Table - Temp] INNER JOIN [Production] ON
> ([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
> Number]) AND ([Stats Daily Table - Temp].[Daily Date] =
> [Production].[Stats
> Date]) " & _
> "SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats
> Daily Table - Temp]![LB Pulling Orders] + ([Production]![Orders] +
> [Production]![WPL Orders]), [Stats Daily Table - Temp].[LB Pulling Lines]
> =
> [Stats Daily Table - Temp]![LB Pulling Lines] + ([Production]![Lines] +
> [Production]![WPL Lines]) " & _
> "WHERE (([Production].[Department Number]=1) AND
> ([Production].[Area Number]=102) AND (([Production].[Portion]='LL Lower
> Portion') Or ([Production].[Portion]='LU Upper Portion') Or
> ([Production].[Portion]='LE Leather Portion') Or
> ([Production].[Portion]='LC
> Closeout Portion')));")
> It gives me an error that this is not an Updatable Query - It did work
> Access to Access.
> How can I make this query work - do I have to put the query into the
> SQLServer database as a function I need to call for? Do I just need to
> have
> it worded differently for the BE to understand.
> 2. If this needs to be in the BE - How do I call the query from the FE?
> Please Help - I am trying to upgrade to SQLServer - I See its benefits -
> but
> if I cannot understand how to use it I guess I'll be stuck with Access2K's
> limitations.
> TIAFAH
> Randy
>|||You might want to learn standard SQL syntax and good programming
practices. Never embed blanks in a data element name unless you want to
destroy portability. If you must use reserved words for a data element
name because you do not know ISO-11179 rules then at least do not use
square brackets. I think this is what you were trying to do:
UPDATE DailyStats
SET lb_pull_orders
= DailyStats.lb_pull_orders
+ (SELECT P1.orders + P1.wpl_orders
FROM Production AS P1
WHERE P1.dept_nbr = 1
AND P1.area_nbr = 102
AND DailyStats.emp_nbr = P1.emp_nbr
AND DailyStats.daily_date = P1.stat_date
AND P1.Portion
IN (LL Lower Portion', 'LU Upper Portion',
'LE Leather Portion''LC Closeout Portion'));|||The typo in the IN predicate notwithstanding, Joe is right about your table
naming convention. But you'll get used to that, Joe is always right :)
Even if porting your Database over to another DBMS isn't a priority, those
extra spaces and hyphens in the names could cause you problems down the road
when you try to access the database via other tools and methods. Consider
using underscore characters instead of spaces in identifiers, or just
eliminate spaces altogether and go with the AlternateCAPSLook, which happens
to be "in" this season thanks to a new reality TV show :)
Here's your IN Clause with the correct number of commas and single quotes:
IN ('LL Lower Portion', 'LU Upper Portion',
'LE Leather Portion','LC Closeout Portion'));
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114129809.103356.5010@.l41g2000cwc.googlegroups.com...
> You might want to learn standard SQL syntax and good programming
> practices. Never embed blanks in a data element name unless you want to
> destroy portability. If you must use reserved words for a data element
> name because you do not know ISO-11179 rules then at least do not use
> square brackets. I think this is what you were trying to do:
>
> UPDATE DailyStats
> SET lb_pull_orders
> = DailyStats.lb_pull_orders
> + (SELECT P1.orders + P1.wpl_orders
> FROM Production AS P1
> WHERE P1.dept_nbr = 1
> AND P1.area_nbr = 102
> AND DailyStats.emp_nbr = P1.emp_nbr
> AND DailyStats.daily_date = P1.stat_date
> AND P1.Portion
> IN (LL Lower Portion', 'LU Upper Portion',
> 'LE Leather Portion''LC Closeout Portion'));
>|||Hi Randy,
When moving over to SQL a load of those update/select queries stop
becomming updatable, the most probable reason is the joins, see if you
can send updates to single tables first. I totally agree with the other
guys, NEVER use blanks in table names/field names use the _ instead for
readablity. Take the time to rename your tables and fields as this will
make your coding easer and more readable.
Good luck
Regards
Alex|||TY Everyone involved,
I Still Have my same problem- First I will work on transferring
everything over to the naming conventions you guys have suggested - I more
than likely will use the underscores in place of spaces as sometimes when
things go wrong others are in here that just do not understand so my naming
of the tables and fields where to make it as simple to understand as
possible for those who do not know how to read code normally.
Second - your queries I am assuming are to be set in the user defined
functions of SQLServer - We use an Access2K FE - How do I call this function
from the front end - The front end is where I have run all of my queries and
the query I gave and the ones you have given still give me the error. what
do I have to do to run the query from an Access FE?.
Again
TIAFAH
Randy
"Michael C#" <xyz@.abcdef.com> wrote in message
news:e4_9e.134$eR1.12@.fe12.lga...
> The typo in the IN predicate notwithstanding, Joe is right about your
table
> naming convention. But you'll get used to that, Joe is always right :)
> Even if porting your Database over to another DBMS isn't a priority, those
> extra spaces and hyphens in the names could cause you problems down the
road
> when you try to access the database via other tools and methods. Consider
> using underscore characters instead of spaces in identifiers, or just
> eliminate spaces altogether and go with the AlternateCAPSLook, which
happens
> to be "in" this season thanks to a new reality TV show :)
> Here's your IN Clause with the correct number of commas and single quotes:
> IN ('LL Lower Portion', 'LU Upper Portion',
> 'LE Leather Portion','LC Closeout Portion'));
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1114129809.103356.5010@.l41g2000cwc.googlegroups.com...
>|||Try pass-thru queries. Tends to be more efficient than relying on Jet as
well. And as far as naming, name your tables for *your* ease of use, not
ours :) The main point about removing spaces is that not all tools deal
equally well with spaces in identifier names, and it could cause you
problems down the road. If you're willing to take that chance though, no
one can force you to rename anything.
"Randy Fritz" <Randyfritz@.sbcglobal.net> wrote in message
news:uSL0Bm1RFHA.1096@.tk2msftngp13.phx.gbl...
> TY Everyone involved,
> I Still Have my same problem- First I will work on transferring
> everything over to the naming conventions you guys have suggested - I more
> than likely will use the underscores in place of spaces as sometimes when
> things go wrong others are in here that just do not understand so my
> naming
> of the tables and fields where to make it as simple to understand as
> possible for those who do not know how to read code normally.
> Second - your queries I am assuming are to be set in the user defined
> functions of SQLServer - We use an Access2K FE - How do I call this
> function
> from the front end - The front end is where I have run all of my queries
> and
> the query I gave and the ones you have given still give me the error. what
> do I have to do to run the query from an Access FE?.
> Again
> TIAFAH
> Randy
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:e4_9e.134$eR1.12@.fe12.lga...
> table
> road
> happens
>|||On Thu, 21 Apr 2005 16:22:21 -0700, Randy Fritz wrote:

>Hello NG,
>SQLServer 2000
>Background:
> I have been working with an Access2K FE with Access2K BE Tables - I hav
e
>just moved all the BE tables to the SQLServer database - I have set up full
>permissions to Add,Delete,Modify and Read all the records in all the tables
.
>All of my code is in the FE database.
(snip)
Hi Randy,
A few points to keep in mind:
1. The UPDATE you posted uses a syntax that only Access understands. The
ANSI standard form of the UPDATE syntax is
UPDATE TableName
SET Column = expression
[ ,Column = expression ] ...
[ WHERE logical-expression ]
SQL Server also supports an alternative UPDATE syntax, much like (but
not the same as!) the special Access syntax you used - simplified:
UPDATE [ TableName | alias ]
SET Column = expression
[ ,Column = expression ] ...
FROM TableName [ [AS] alias ]
[ (jointype) JOIN TableName [ [AS] alias ] ON logical-expression ] ...
[ WHERE logical-expression ]
The SQL Server UPDATE ... FROM syntax has some gotchas that you should
be aware of. The most important is that the end result is inpredictable
when one row in the table to be updated can be joined to more than one
row from the tables in the FROM clause.
2. If you run regular queries on linked tables in Access, then the query
will be completely executed by Access. It will get the data it needs
from SQL Server, combine the data to get the end result, then (if it's
an update query) send the modifications back to SQL Server. This means
that regular queries in Access should still use Access' version of SQL
(Jet SQL), not SQL Server's (Transact-SQL). It also means that things
might get slow, especially if a lot of data has to be fetched from the
server before the end result is generated.
3. If you define a query as a pass-through query, Access will simply
pass the exact SQL code on to SQL Server and SQL Server will execute it.
For these queries to work, you'll have to write your code in Transact
SQL, and you can't use the nice visual drag and drop tools offered by
Access. In most cases, pass-through queries perform faster than "normal"
queries.
(snip)
> 2. If this needs to be in the BE - How do I call the query from the FE?
if you create a pass-thorugh query, then the code will be sent to the BE
for execution. But the recommended best practice is to start writing
stored procedures that perform the tasks that typically need to be done,
then call the stored procedures from the FE.
Check out CREATE PROCEDURE in Books Online for the syntax you need to
create stored procedures.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)