problem with ALTER TABLE syntax

I finally found a way to "deploy" my local SqlServerExpress (SSE) database to the remote Sql2K server... In VisualWebDeveloper (VWD) I can create the table definition and then save the creation sql script and use that in SSE Express Manager while connected to my remote DB. I am describing this because I'm so surprised no one has had problems with this as thousands of developers, some very unexperienced (as me maybe!), are trying the same situation now that some are offering 2.0 hosting... Well I thought this was a great idea until the Sql2K server is returning error messages on the script VWD created. So please could you help since I'm not that good at complex sql scripting. It seems the error comes from the ALTER TABLE syntax:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Table2
(
prID int NOT NULL IDENTITY (1, 1),
DateInserted datetime NOT NULL,
Title nvarchar(100) NOT NULL,
Description nvarchar(MAX) NULL,
CategoryID smallint NOT NULL,
DateLastUpdated datetime NULL,
Price int NOT NULL,
SpecialPrice int NULL,
ImgSuffix nvarchar(5) NULL,
ImgCustomWidth smallint NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
prID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Exactly what error message are you getting?
Tryst
|||

Msg 170... Incorrect syntax near '('.
I've found out if I remove "WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)"
it works...

Problem with ALTER TABLE in stored procedure

Hello,
I am trying to drop a column from a table with a stored procedure that
chacks if the column exists before droping it. However, I am having trouble
with passing the Table Name and Column Name to the ALTER TABLE command. The
code is:
CREATE PROCEDURE usp_DeleteColumnEx
@.TableName varchar(200),
@.ColumnName varchar(200)
AS
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')= 1
AND O.Name = @.TableName
AND C.Name = @.ColumnName)
ALTER TABLE @.TableName DROP COLUMN @.ColumnName
When I try to execute this the server returns an error: "Incorrect syntax
near '@.TableName'". I am assuming that I cannot just pass the table name as
a
parameter. Is there a another way to do this?
Thank you for your help.
Daniel> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
You can't do this - SQL Server has to know what objects you're talking
about. The best you could do is dynamic SQL, please read:
http://www.sommarskog.se/dynamic_sql.html
On a side note, why on earth are you changing your table structure on the
fly like this? Sounds very dangerous and suspicious, but not in the Austin
Powers way.|||"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9A9DCE9F-191A-4E76-AD5A-FC6E7A316E61@.microsoft.com...
> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having
> trouble
> with passing the Table Name and Column Name to the ALTER TABLE command.
> The
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name
> as a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel
Why would you want a proc that drops columns? Help me understand what you
are trying to achieve.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Aaron and David,
Thank you for your replies. The reason I need to alter the table dynamically
is
that I have a table where each record is corresponds to one employee, each
column represents a different skill involved in the employees' everyday work
.
In each column every employee have assigned a number corresponding to their
skill level. According to their skills they are assigned to their work
stations, which is VERY important for the management.
This is a basically how the table looks like:
Create table SkillsCheck (
emp_id int,
skill1 int,
skill2 int,
skill3 int,
skill4 int)
The problem is that the company has the option to change what skills they
want to track, so when that happens I have to drop or add a column to the
table to reflect the current situation. For example, they might decide that
they do not want to track skill3 anymore, but want to track skill5, so my
program needs to drop skill3 column and add skill5 column. I am working with
VB.NET and I can execute the ALTER TABLE statement from my code, but I think
that it would be better to use stored procedure that check if the column
exists before adding or droping.
After considering the situation I decided that would be easier to alter the
table instead of keeping the information in multiple tables. If you have
encountered a similiar scenario maybe you can give me an advice for more
efficient approach to this problem.
Thank you for your help,
Daniel
"Daniel" wrote:

> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having troubl
e
> with passing the Table Name and Column Name to the ALTER TABLE command. Th
e
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name a
s a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel|||Daniel wrote:
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table dynamical
ly
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday wo
rk.
> In each column every employee have assigned a number corresponding to thei
r
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide tha
t
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working wi
th
> VB.NET and I can execute the ALTER TABLE statement from my code, but I thi
nk
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter th
e
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>
The type of relationship between employees and skills is called
many-to-many. The textbook solution looks like this:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
PRIMARY KEY (emp_id,skill_code));
This has huge advantages over the design that you proposed: It can
support any number of skills. No redundancy. No nulls required. Joins
and queries always reference just one skills column. The table
structure doesn't ever need to change (!).
I recommend you read up and study some relational design theory. Most
database architects would consider your suggestion as a serious design
flaw. To appreciate why you need to understand principles like
normalization and the normal forms, which are some of the tools we use
to design effective databases.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||As you stated, it would take a schema changed to accommodate future
requirements. Such as that, you're better off by creating a skills table to
store the skills. Any changes will just be a simple delete from the tables.
Also, this approach will not incur a table lock like you have when doing
schema update.
e.g.
create table skills(skillid int primary key, skillname sysname)
create table skillscheck(empid int primary key, skillid int foreign key
references skills(skillid))
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table
> dynamically
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday
> work.
> In each column every employee have assigned a number corresponding to
> their
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide
> that
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working
> with
> VB.NET and I can execute the ALTER TABLE statement from my code, but I
> think
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter
> the
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>|||Correction. Don't forget to add a column for the skill level:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
skill_level INTEGER NOT NULL
CHECK (skill_level BETWEEN 0 AND 10),
PRIMARY KEY (emp_id,skill_code));
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
I will follow your advice and will read about many-to-many relationships.
After reading more on Dynamic SQL, looks like it is not the best choice.
Thanks again,
Daniel
"David Portas" wrote:

> Correction. Don't forget to add a column for the skill level:
> CREATE TABLE EmployeeSkills
> (emp_id INTEGER NOT NULL
> REFERENCES Employees (emp_id),
> skill_code INTEGER NOT NULL
> REFERENCES Skills (skill_code),
> skill_level INTEGER NOT NULL
> CHECK (skill_level BETWEEN 0 AND 10),
> PRIMARY KEY (emp_id,skill_code));
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||OJ,
Thanks you your reply. Looks like I need to revise the database design and
reorganize the tables differently.
Best Regards,
Daniel
"oj" wrote:

> As you stated, it would take a schema changed to accommodate future
> requirements. Such as that, you're better off by creating a skills table t
o
> store the skills. Any changes will just be a simple delete from the tables
.
> Also, this approach will not incur a table lock like you have when doing
> schema update.
> e.g.
> create table skills(skillid int primary key, skillname sysname)
> create table skillscheck(empid int primary key, skillid int foreign key
> references skills(skillid))
>
> --
> -oj
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
>
>

Problem with Alter table disable trigger all

Hi,

I am replicating few production databases and I am running into a problem with "Alter table tblname disable trigger all". I get the following error: Cannot alter the table 'tblname' because it is being published for replication. I am aware that when you want to add or drop a column, you need to use some system stored procedures to do so. But how can I disable the triggers before running some queries/statement on a table that is being published to a subscriber?

I really apreciate you help,

Regards,

SasIt seems you have to use sp_dropsubscription and then sp_droparticle to discontinue replication of the table, do you alter, then use sp_addarticle and sp_addsubscription to add continue replication. If there is an easier way, I'd be interested to know as well.

http://www.databasejournal.com/features/mssql/article.php/1477441|||UPDATE sysobjects SET replinfo=0 WHERE xtype='U' AND name='YOUR TABLE'
It would disable the table in the publication.
When u r done with ur work, Add the table back into Publication through Enterprise-Manager.
However sometimes it goes messed-up and SQL doesn't allow to add the table back into publication. In that case u may need to reinialize the subscriptions followed by the SNAPSHOT.

Regards!!

Problem using a parameter in a subquery

The following code works (assuming that there is an employee with the alias
'some alias', of course)
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS SELECT
ExpenseLocations.LocationId, ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees WHERE EmployeeAlias = 'some alias'))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
However, the following parameterized code does not work:
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
int output) AS SELECT ExpenseLocations.LocationId,
ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM Employees
WHERE EmployeeAlias = @.EmployeeAlias))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
I am obviously doing something wrong but am not sophisticated enough
(apparently) to figure out why. Thanks in advance for any suggestions.
DuncanYou do not define a length of the char param
@.EmployeeAlias char
Should be something like this
@.EmployeeAlias char(10),
my suggestion:
exec sp_help Employees
make the variable the same length as the EmployeeAlias column as it is
defined within the table.
Keith
"duncans" <duncans@.discussions.microsoft.com> wrote in message
news:59D22412-47F6-4ED6-A1D9-B31C53994CC8@.microsoft.com...
> The following code works (assuming that there is an employee with the
alias
> 'some alias', of course)
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS
SELECT
> ExpenseLocations.LocationId, ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
> Employees WHERE EmployeeAlias = 'some alias'))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> However, the following parameterized code does not work:
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
> int output) AS SELECT ExpenseLocations.LocationId,
> ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees
> WHERE EmployeeAlias = @.EmployeeAlias))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> I am obviously doing something wrong but am not sophisticated enough
> (apparently) to figure out why. Thanks in advance for any suggestions.
> Duncan
>

Problem using a parameter in a subquery

The following code works (assuming that there is an employee with the alias
'some alias', of course)
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS SELECT
ExpenseLocations.LocationId, ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees WHERE EmployeeAlias = 'some alias'))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
However, the following parameterized code does not work:
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
int output) AS SELECT ExpenseLocations.LocationId,
ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM Employees
WHERE EmployeeAlias = @.EmployeeAlias))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
I am obviously doing something wrong but am not sophisticated enough
(apparently) to figure out why. Thanks in advance for any suggestions.
Duncan
You do not define a length of the char param
@.EmployeeAlias char
Should be something like this
@.EmployeeAlias char(10),
my suggestion:
exec sp_help Employees
make the variable the same length as the EmployeeAlias column as it is
defined within the table.
Keith
"duncans" <duncans@.discussions.microsoft.com> wrote in message
news:59D22412-47F6-4ED6-A1D9-B31C53994CC8@.microsoft.com...
> The following code works (assuming that there is an employee with the
alias
> 'some alias', of course)
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS
SELECT
> ExpenseLocations.LocationId, ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
> Employees WHERE EmployeeAlias = 'some alias'))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> However, the following parameterized code does not work:
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
> int output) AS SELECT ExpenseLocations.LocationId,
> ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees
> WHERE EmployeeAlias = @.EmployeeAlias))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> I am obviously doing something wrong but am not sophisticated enough
> (apparently) to figure out why. Thanks in advance for any suggestions.
> Duncan
>