Altering linked tables

I have a table (in Access) that is linked to the SQL server, and I
need to add a column to it. I wrote the following:

ALTER TABLE Census
ADD COLUMN 'ActiveFacility' BIT;

and I get a syntax error that I do not know how to solve. The column
needs to contain yes/no data for each record.

Any help for a struggling newbie?
Thanks,

ChristineChristine (cbrandel@.rehabmanagement.com) writes:
> I have a table (in Access) that is linked to the SQL server, and I
> need to add a column to it. I wrote the following:
> ALTER TABLE Census
> ADD COLUMN 'ActiveFacility' BIT;
> and I get a syntax error that I do not know how to solve. The column
> needs to contain yes/no data for each record.
> Any help for a struggling newbie?

It is always wise to include the error message you get. In this case it
was very easy for me to reproduce the error, but sometimes this is
may be impossible with access to the database.

The best place to find out the syntax for a command, is to look it up
in Books Online, which comes with SQL Server. In the book T-SQL Syntax
Reference you find all T-SQL commands described with syntax diagrams.
Sure, the diagram for ALTER TABLE may seem unwieldy, since you can
alter a table in many ways. On the flip side, there are also examples
for the most common uses of the command.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Altering linked tables

Greetings,
I am using an Access mdb with all linked tables in MSDE. I know that you
can't change linked tables from the MDB, so I created an ADP that imports
all the tables, so that I can alter them in the ADP. The ADP let's me add a
column to a table, but when I open the mdb up again that column isn't there.
What do I do?
Thanks in advance.
Hi, Yair.
Drop the link, then recreate the link to the table. An external table's
structure and connection properties are only recorded at the time of
linking, so any later changes that you make to a linked table's structure
(i.e., add/change/delete/rename fields) or connection properties (i.e.,
add/change/delete database password) are unknown. Dropping and recreating
the link will re-establish the correct properties needed to access the data
in the external table.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I am using an Access mdb with all linked tables in MSDE. I know that you
> can't change linked tables from the MDB, so I created an ADP that imports
> all the tables, so that I can alter them in the ADP. The ADP let's me add
a
> column to a table, but when I open the mdb up again that column isn't
there.
> What do I do?
> Thanks in advance.
>
|||Thanks Gunny.
How do I drop the link and relink?
Will it affect all my forms and reports?
I'll check the help too but if it's an easy answer...
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Thanks. I used the linked table manager to refresh the tables an it worked
perfectly.
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Check out http://www.mvps.org/access/tables/tbl0010.htm at "The Access Web"
for one approach to relinking ODBC tables, or see
http://members.rogers.com/douglas.j...LessLinks.html for how to do
it without requiring a DSN.
Assuming you do it when you first start up the application, it won't affect
your forms or reports unless table changes have occurred, and your forms or
reports reference fields or tables that are no longer present.
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss#HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>
|||Hi, Yair.

> How do I drop the link and relink?
Select the linked table in the database window with your mouse and hit the
<DELETE> key. Then use the menu "File -> Get External Data -> Link Tables"
and browse for the file that contains the table that you want to link to,
then follow the prompts in the dialog window just like you did when you
originally linked the table.

> Will it affect all my forms and reports?
Sort of. It will allow you to add this new field to all of the forms and
reports bound to this table, and any queries and Recordsets that utilize the
table, but won't automatically make these changes for you.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss%23HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all databases.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all database
s.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all databases.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Alter a SQL table

I used the MS Access 2000 Upsize wizard to create a Client/server
application. After the wizard ran, the original table names had become
the linked tables and then there were duplicates of the the tables
named xxx_local.

I realized later that I needed to add some fields to a table and
couldn't do that in Access (couldn't edit the linked tables). So I used
the Query Analyzer and created a script to add the fields to my table.
It looked fine in Query Analyzer, but back in Access the fields were
added to xxx_local and not xxx, the linked table. My forms and reports
are built on xxx, not xxx_local.

How do I do this correctly?

Janejfrasier wrote:

Quote:

Originally Posted by

I used the MS Access 2000 Upsize wizard to create a Client/server
application. After the wizard ran, the original table names had become
the linked tables and then there were duplicates of the the tables
named xxx_local.
>
I realized later that I needed to add some fields to a table and
couldn't do that in Access (couldn't edit the linked tables). So I used
the Query Analyzer and created a script to add the fields to my table.
It looked fine in Query Analyzer, but back in Access the fields were
added to xxx_local and not xxx, the linked table. My forms and reports
are built on xxx, not xxx_local.
>
How do I do this correctly?
>
Jane

Anytime I've added fields to a linked table in Access, I've always had
to refresh the links (using the Linked Table Manager) in order to see
them on the Access side. I'm not sure if the fact that I seldom use
Query Anazlyer to add fields to a table or not has anything to do with
it (I just go into Enterprise Manager and make the changes there). Are
you sure you're referencing the desired table in your script?

Molly J. Fagan|||I re-linked the table in Access and that did the trick. Thanks.

Jane

mollyf@.hotmail.com wrote:

Quote:

Originally Posted by

jfrasier wrote:

Quote:

Originally Posted by

I used the MS Access 2000 Upsize wizard to create a Client/server
application. After the wizard ran, the original table names had become
the linked tables and then there were duplicates of the the tables
named xxx_local.

I realized later that I needed to add some fields to a table and
couldn't do that in Access (couldn't edit the linked tables). So I used
the Query Analyzer and created a script to add the fields to my table.
It looked fine in Query Analyzer, but back in Access the fields were
added to xxx_local and not xxx, the linked table. My forms and reports
are built on xxx, not xxx_local.

How do I do this correctly?

Jane

>
Anytime I've added fields to a linked table in Access, I've always had
to refresh the links (using the Linked Table Manager) in order to see
them on the Access side. I'm not sure if the fact that I seldom use
Query Anazlyer to add fields to a table or not has anything to do with
it (I just go into Enterprise Manager and make the changes there). Are
you sure you're referencing the desired table in your script?
>
Molly J. Fagan