Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hans
No, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans
|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
>
>

Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hansNo, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> > Hello,
> >
> > I'm trying to do this:
> > alter table maquinas add marca varchar(50) after modelo
> >
> > and I get the following error:
> > Incorrect syntax near 'after'.
> >
> > What is going on?
> >
> > hans
>
>

Does ASP 3.0 Support Varchar(Max) Data Type

Does ASP 3.0 support Varchar(Max) Data Type?

I am having problems displaying asp 3 recordset with varchar(max) data type...

ADO.NET 2.0 supports varchar(max) field. Older versions will support it as well since SQL will send back as text to older clients.

Does Alter Column do work if column already the same as desired?

I have a table with a column defined thus: LOCNumber Varchar(100) in a
table called Bookdata.

If I execute ALTER TABLE BookData ALTER COLUMN LOCNumber varchar(100)
on the table, does any work get done on the table and column?
Or does SqlServer know that the column is already varchar(100) and
nothing
needs to be done?
If it does do some work, how do you tell it not to run this as it is
already a varchar(100) column. Right now my program just blindly does
this ALTER TABLE
statement regardless of any conditions. Is this a potential problem?
Thanks for any help.SQL 2000 only needs to update meta data when the new varchar column length
is >= the specified length. Consequently, you can skip the length check.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<sdowney717@.msn.com> wrote in message
news:1107370345.836079.312700@.z14g2000cwz.googlegr oups.com...
>I have a table with a column defined thus: LOCNumber Varchar(100) in a
> table called Bookdata.
> If I execute ALTER TABLE BookData ALTER COLUMN LOCNumber varchar(100)
> on the table, does any work get done on the table and column?
> Or does SqlServer know that the column is already varchar(100) and
> nothing
> needs to be done?
> If it does do some work, how do you tell it not to run this as it is
> already a varchar(100) column. Right now my program just blindly does
> this ALTER TABLE
> statement regardless of any conditions. Is this a potential problem?
> Thanks for any help.|||Thanks
It is 2000, so everything is fine.

Does a role exist?

In my proc I have to add a user to a role in another database. I do it
in the following manner:
declare @.IIDdatabase varchar(255)
set @.IIDdatabase = 'IID'
SELECT @.cmd = N'USE ' + CAST(@.IIDdatabase as nvarchar) + @.crlf + N'exec
sp_addrolemember @.rolename = ''Role_MyCustomRole'', @.membername = ''' +
CAST(@.user as nvarchar) + '''' + @.crlf
EXEC @.STATUS = sp_executesql @.cmd
And this seems to work fine. However, how do I find out whether the
role (in the other database) exists to begin with? The difficulty here
is that the name of the database is passed in as a variable.
Thanks.Hi Frank,
I understand that you would like to judge wheter the database role is
existed before your execute your SQL statement.
If I have misunderstood, please let me know.
You may refer to the following stored procedure:
---
/* judge if a database role is existed in a database */
CREATE PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
You can call it like this:
----
--
declare @.isexisted int
exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
if @.isexisted=0
begin
..
end
----
--
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I am sorry, I should have mentioned. The code must work on SQL 2000 as
well as SQL 2005.
Charles Wang[MSFT] wrote:
> Hi Frank,
> I understand that you would like to judge wheter the database role is
> existed before your execute your SQL statement.
> If I have misunderstood, please let me know.
> You may refer to the following stored procedure:
> ---
> /* judge if a database role is existed in a database */
> CREATE PROCEDURE proc_existrole
> (
> @.dbname nvarchar(50)=null,
> @.rolename nvarchar(50)=null,
> @.isexisted int output
> )
> as
> declare @.strSQL nvarchar(200)
> declare @.strCount nvarchar(50)
> begin
> if @.dbname is null or @.rolename is null
> begin
> set @.isexisted = -1
> return
> end
> set @.strCount = N'@.nCount int output'
> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
> if @.isexisted>0
> set @.isexisted = 1
> else
> set @.isexisted = 0
> end
> ----
> --
> You can call it like this:
> ----
> --
> declare @.isexisted int
> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
> if @.isexisted=0
> begin
> ..
> end
> ----
> --
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Frank,
This will still work on both 2000 and 2005, although it is certainly
deprecated.
SELECT name
FROM sysusers
WHERE issqlrole = 1
RLF
"Frank Rizzo" <none@.none.com> wrote in message
news:uEsuyFB5HHA.2108@.TK2MSFTNGP02.phx.gbl...
>I am sorry, I should have mentioned. The code must work on SQL 2000 as
>well as SQL 2005.
> Charles Wang[MSFT] wrote:
>> Hi Frank,
>> I understand that you would like to judge wheter the database role is
>> existed before your execute your SQL statement.
>> If I have misunderstood, please let me know.
>> You may refer to the following stored procedure:
>> ---
>> /* judge if a database role is existed in a database */
>> CREATE PROCEDURE proc_existrole
>> (
>> @.dbname nvarchar(50)=null,
>> @.rolename nvarchar(50)=null,
>> @.isexisted int output
>> )
>> as
>> declare @.strSQL nvarchar(200)
>> declare @.strCount nvarchar(50)
>> begin
>> if @.dbname is null or @.rolename is null
>> begin
>> set @.isexisted = -1
>> return
>> end
>> set @.strCount = N'@.nCount int output'
>> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
>> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
>> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
>> if @.isexisted>0
>> set @.isexisted = 1
>> else
>> set @.isexisted = 0
>> end
>> ----
>> --
>> You can call it like this:
>> ----
>> --
>> declare @.isexisted int
>> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
>> if @.isexisted=0
>> begin
>> ..
>> end
>> ----
>> --
>> Hope this helps. If you have any other questions or concerns, please feel
>> free to let me know.
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> ====================================================== When responding to
>> posts, please "Reply to Group" via your newsreader so that others may
>> learn and benefit from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights. ======================================================|||Hi Frank,
In this case, you can combine Russell's suggestion and mine into one
procedure like this:
----
--
/* judge if a database role is existed in a database */
ALTER PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
declare @.productVersion nchar(1)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
SELECT @.productVersion = SUBSTRING(CAST(SERVERPROPERTY ( 'PRODUCTVERSION' )
AS NVARCHAR(10)),1,1)
IF @.productVersion = '9'
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
END
ELSE
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.dbo.sysusers where issqlrole=1 and name='''+@.rolename + ''''
END
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
Please feel free to let us know if you need further assistance on this
issue. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Frank,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles Wang[MSFT] wrote:
> Hi Frank,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
Thanks, your suggestion worked great.