Alternate to a not in query

-- tested schema below --
-- create tables --
create table tbl_test
(serialnumber char(12))
go
create table tbl_test2
(serialnumber char(12),
exportedflag int)
go
--insert data --
insert into tbl_test2 values ('123456789010',0)
insert into tbl_test2 values ('123456789011',0)
insert into tbl_test2 values ('123456789012',0)
insert into tbl_test2 values ('123456789013',0)
insert into tbl_test2 values ('123456789014',0)
insert into tbl_test2 values ('123456789015',0)
insert into tbl_test2 values ('123456789016',0)
insert into tbl_test2 values ('123456789017',0)
insert into tbl_test2 values ('123456789018',0)
insert into tbl_test2 values ('123456789019',0)

insert into tbl_test values ('123456789011')
insert into tbl_test values ('123456789012')
insert into tbl_test values ('123456789013')
insert into tbl_test values ('123456789014')
insert into tbl_test values ('123456789015')

-- query --
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0

This query runs quite fast with only the data above but when both
tables get million plus rows, the query simply bogs down. Is there a
better way to write this query?Select serialnumber
from tbl_test2 a
left joint tbl_test b on a. serialnumber = b.serialnumber
where (b.serialnumber IS NULL)
AND (a.exportedflag=0)|||There is another way to write the query, but it's not better (in fact,
I think it's worse):

Select tbl_test2.serialnumber from tbl_test2
left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
where exportedflag=0 and tbl_test.serialnumber is null

To improve the performance of this query, you should create primary
keys on the tables. Besides the conceptual benefits of a proper design,
this would accomplish (at least) the following things:
- create an index on the serialnumber column
- declare that the serialnumber column does not allow duplicates
- declare that the serialnumber column does not allow nulls
These things will help the Query Optimizer very much to create a better
execution plan.

Razvan|||
Razvan Socol wrote:
> There is another way to write the query, but it's not better (in fact,
> I think it's worse):
> Select tbl_test2.serialnumber from tbl_test2
> left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
> where exportedflag=0 and tbl_test.serialnumber is null

Razvan,

Why worse?

The common wisdom seems to be that it is always more efficient
eliminate nested subqueries, if possible.

My understanding is that the optimizer will internally eliminate the
subquery by doing a left join as above if it can.|||Ira Gladnick (IraGladnick@.yahoo.com) writes:
> Why worse?
> The common wisdom seems to be that it is always more efficient
> eliminate nested subqueries, if possible.

It's worse, becase it does not express the intent of the query equally
well, and therefore can contribute to higher maintenance costs.

> My understanding is that the optimizer will internally eliminate the
> subquery by doing a left join as above if it can.

I don't know if this is the case, but in such case there is even less
reason to rewrite the query in an obscure way.

I would write the query as:

Select serialnumber
from tbl_test2 t2
where not exists (select *
from tbl_test t
where t2.serialnuber = t.serialnumber)
and exportedflag=0

In SQL 6.5 this would typically perform better than NOT IN. But I believe
SQL 2000 will rewrite NOT IN to NOT EXISTS internally, so it is not that
much of an issue for performance. But NOT EXISTS is more general to use
than NOT IN, because you can handle multi-column conditions. Furthermore,
if there are NULL values involved, NOT IN can give you surpriese.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(kjaggi@.hotmail.com) writes:
> -- query --
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
> This query runs quite fast with only the data above but when both
> tables get million plus rows, the query simply bogs down. Is there a
> better way to write this query?

Beside the obvious point from Razvan about indexes, if you are on a multi-
CPU box, you can try this at the end of the query:

OPTION (MAXDOP 1)

this turns off parallelism. I've seen SQL Server use massive parallel
plans for this type of query, when a non-parallel plan have been much
faster.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Altering functions and CHECK constraints

Let's say I create a multi-statement function like this:

CREATE FUNCTION dbo.Test ()
RETURNS @.res TABLE (N int NOT NULL CHECK (N >= 0))
AS
BEGIN

INSERT INTO @.res
SELECT 1

RETURN
END

That works fine. Then I make a change in the function's body, replace the
CREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:

Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9
Cannot ALTER 'dbo.Test' because it is being referenced by object
'CK__Test__N__5D2E32EB'.

Indeed, if I look at the list of dependencies for the function in QA's
object tree, I can see the check constraint referenced in the error
message.

ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
definition of the @.res table.

So it seems that the only way to modify such a function is to drop and
recreate. Is that a known behavior? Is there any particular reason for it?

Thanks.

--
(remove a 9 to reply by email)Dimitri Furman (dfurman@.cloud99.net) writes:
> ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
> definition of the @.res table.
> So it seems that the only way to modify such a function is to drop and
> recreate. Is that a known behavior? Is there any particular reason for it?

I will have to admit that I was not aware of this. As for why, my guess
is that this is an artefact of the metadata structure in SQL Server, and
the SQL Server developers did not write the necessary code to avoid this.

Anyway, the restriction is not there in SQL 2005, so whatever the reason
for this in SQL 2000, it is not likely to be a compelling one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Altering a column which has an index defined on it

Hello,

I'm trying the following test (which works like a charm on Oracle)

create table x1(c1 numeric(10), c2 numeric(5,1))
create index x1_c2_idx on x1(c2)
alter table x1 alter column c2 numeric(9,1)

I get the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'x1_c2_idx' is dependent on column 'c2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.

Is there a way to alter the column WITHOUT dropping the index ?



Regards,

Tal Olier
otal@.mercury.co.ilNo, you must drop the index first.|||Originally posted by Paul Young
No, you must drop the index first.

Thanks.

Alteration date

THere's a create date stored in Sysobjects, but is it true that there's no track of alteration dates?I also have been unable to find one.
In my case I'm planning to use sp_table_validation to generate a checksum to determine whether a table has been altered.

- Andy Abel|||About sp_table_validation - is there anything similar for other objects than tables?

I thought about searching for a certain string in an SP's code, a string where the user that made the last change wrote his/her signature as a comment. Our developers are fairly disciplined...
But, the 'text' attribute of syscomments was hard to understand, since selecting it using left() and substr() functions gave very different results compared to doing just a select on the attribute. I need to use a string function to truncate the 'text' attribute because it's so wide.|||Rather than using substring() you could pattern matching if you know the form of the comment you're looking for. i.e. you could use:

where text like '%Version%'

or use patindex() with substr() to locate the beginning of your comment section and do a substring from that point.

substring(text,PATINDEX('%Version%', text),255)

- Andy Abel

Alter table weird bug?

I have created the following test SQL code to illustrate a real
problem I have with some SQL code.

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe'
, 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Bill
Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

When I run this SQL via the query analyser I get the errors:-

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.

It appears the SQL Server is trying to "pre-parse" the query and
hasn't picked up on the ALTER TABLE line that adds this column and so
complains that it doesn't exist. However it doesn't end there.

If I then run this query a line at a time by highlighting each line in
the query analyser and running it all works (not unexpected). However
then dropping the table and then running the full SQL code once more
(I.E. not highlighting each line), it then works as expected. I assume
it was somehow remembering "state" in my session so closed and
re-started the Query Analyser with the same result that the code does
now work.

However changing the table name to something new brings back the
errors once more. Can anyone explain what is going on here? I am
dropping the table before re-running the code each time.

I'm using SQL Server 2000 if that makes a difference.

Thanks.
Jon.Not a bug. As you rightly suspected, SQL Server tries to validate your
code first by attempting to resolve any column or object references to
existing objects and columns. If the table doesn't exist at compile time
then the resolution of that table's columns is deferred until the
statement executes. However, if the table exists then you will receive
an error if the referenced columns don't also exist. A workaround is to
put a GO batch separator after the ALTER TABLE statement so that the
second batch will be compiled independently.

For this reason among others it is good practice always to separate DDL
(Data Definition Language, such as CREATE and ALTER table statements)
and DML (Data Manipulation Language, such as SELECT, UPDATE, INSERT,
DELETE statements). I would create separate scripts for your DDL and DML
statements.

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||David Portas wrote:

> Not a bug. As you rightly suspected, SQL Server tries to validate your
> code first by attempting to resolve any column or object references to
> existing objects and columns. If the table doesn't exist at compile time
> then the resolution of that table's columns is deferred until the
> statement executes.

David,

Thanks, this does make sense however the table in my statement did not exist
at compile time (otherwise the create table line would fail), so that is
why I cannot understand why I get that message.

Thanks.
Jon.|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Thanks, this does make sense however the table in my statement did not
> exist at compile time (otherwise the create table line would fail), so
> that is why I cannot understand why I get that message.

Your batch gets compiled several times. First you have:

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Jon Combe' , 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Bill Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

On the first compile, all but the first statement is deferred. Once the
table has been created, SQL Server hits the ALTER TABLE, finds that the
statement is deferred, and recompiles the batch. This time, all statements
are scrutinized, since once all tables in a query exist, SQL Server per-
form full checks on the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> Your batch gets compiled several times. First you have:
> CREATE TABLE JCTable ( CustomerName varchar(50) )
> ALTER TABLE JCTable ADD CustomerNo int
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Jon Combe' , 1 )
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Bill Gates' , 1 )
> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
> SELECT * FROM JCTable
> On the first compile, all but the first statement is deferred. Once the
> table has been created, SQL Server hits the ALTER TABLE, finds that the
> statement is deferred, and recompiles the batch. This time, all statements
> are scrutinized, since once all tables in a query exist, SQL Server per-
> form full checks on the query.

Thanks Erland,

Why doesn't it spot that I've added a column and then either defer the last
three statements again, or recognise that the column I'm using does now
exist? I'd expect that sort of behaviour from it although I take the point
made earlier that it's best to split the statements with a GO between them.

Also given the way it is compiling the code it doesn't explain why if I run
these statements one line at a time, then drop the table, reload Query
Analyser and re-run this full batch of code it works yet running the full
batch before the table has ever existed generates an error. That just seems
weird, so if anyone can explain why I'd be interested to hear it! The
database should be in the same state in both cases, but as the behaviour is
different it can't be.

Jon.|||Jon Combe wrote:
> Erland Sommarskog wrote:
>
>>Your batch gets compiled several times. First you have:
>>
>> CREATE TABLE JCTable ( CustomerName varchar(50) )
>> ALTER TABLE JCTable ADD CustomerNo int
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Jon Combe' , 1 )
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Bill Gates' , 1 )
>> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
>> SELECT * FROM JCTable
>>
>>On the first compile, all but the first statement is deferred. Once the
>>table has been created, SQL Server hits the ALTER TABLE, finds that the
>>statement is deferred, and recompiles the batch. This time, all statements
>>are scrutinized, since once all tables in a query exist, SQL Server per-
>>form full checks on the query.
>
> Thanks Erland,
> Why doesn't it spot that I've added a column and then either defer the last
> three statements again, or recognise that the column I'm using does now
> exist? I'd expect that sort of behaviour from it although I take the point
> made earlier that it's best to split the statements with a GO between them.
> Also given the way it is compiling the code it doesn't explain why if I run
> these statements one line at a time, then drop the table, reload Query
> Analyser and re-run this full batch of code it works yet running the full
> batch before the table has ever existed generates an error. That just seems
> weird, so if anyone can explain why I'd be interested to hear it! The
> database should be in the same state in both cases, but as the behaviour is
> different it can't be.
> Jon.

I have to confess that this behaviour, if it is "normal", is surprising
to me. Is this the way the product is expected to behave?

Thanks.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Why doesn't it spot that I've added a column

No, you haven't added a column. You get the error the table has been
created, but the ALTER TABLE statement has not been executed. Since the
ALTER TABLE statement was deferred, SQL Server recompiles the batch,
and it recompiles the batch, because that the lowest granularity for
compilation in SQL 2000. And since at this point the columns does not
exist, the compilation fails.

> and then either defer the last three statements again,

SQL Server could defer compilation because of unknown columns too, but it
has quite some ramifications, and I am very happy that unknown columns is
reason for deferral. It is bad as it is. To wit, when you create a stored
procedure, you want to be alerted if you have misspelled a table name of a
column name. Due to deferred name resolution, you don't get alerts for
misspelled table names, but since SQL Server checks the query once all
tables are there, you do at least sometimes get alerts about misspelling
column names. (And in our in-house load tool, I scan the code for table
references to find the missing tables, and also perform some tricks to
get SQL Server check queries with temp tables too.

But, there is light at the end of the tunnel. Your script runs as you
expected in SQL 2005. This is because SQL 2005 is able to recompile a
single statement in a batch, so the entire batch is not recompiled at
once.

> Also given the way it is compiling the code it doesn't explain why if I
> run these statements one line at a time, then drop the table, reload
> Query Analyser and re-run this full batch of code it works yet running
> the full batch before the table has ever existed generates an error.
> That just seems weird, so if anyone can explain why I'd be interested to
> hear it! The database should be in the same state in both cases, but as
> the behaviour is different it can't be.

This has to do with cached plans. The plans are in the cache, even if
the table is dropped. (This makes sense with temp tables.) Yes, it's
certainly a bit confusing, but for the situations for which the behaviour
is designed, it gives the best result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

>> Also given the way it is compiling the code it doesn't explain why if I
>> run these statements one line at a time, then drop the table, reload
>> Query Analyser and re-run this full batch of code it works yet running
>> the full batch before the table has ever existed generates an error.
>> That just seems weird, so if anyone can explain why I'd be interested to
>> hear it! The database should be in the same state in both cases, but as
>> the behaviour is different it can't be.
> This has to do with cached plans. The plans are in the cache, even if
> the table is dropped. (This makes sense with temp tables.) Yes, it's
> certainly a bit confusing, but for the situations for which the behaviour
> is designed, it gives the best result.

So basically with the query I have, whether I get an error or not (when the
database is in the same state) depends entirely on what has been run
previously? That doesn't seem very satisfactory!

Jon.

ALTER TABLE to add NOT NULL fields

I'm using the following statement to create two new fields in a table:
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
When I run it in QA, I get this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a
DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
'tblActivity' because it does not allow nulls and does not specify a DEFAULT
definition.
How can I get those two fields to be created via the ALTER TABLE statement,
I can not use Enterprise Manager, since the statement that I'm using is bein
g
generated by another script to add those fields to all the tables in the
database...You need to specify a default value...
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
DEFAULT( 0 )
Make sure whatever default value you specify is meaningful to your
application; also, you could drop the DEFAULT constraint afterward...
ALTER TABLE ... DROP CONSTRAINT ...
Example...
create table t (
id int null )
insert t values( 1 )
insert t values( null )
go
alter table t add mycol int not null default( 0 )
go
sp_help t
go
alter table t drop constraint DF__t__mycol__781FBE44
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...|||To add on to Tony's response, you can specify an explicit constraint name to
make subsequent table maintenance easier:
ALTER TABLE tblActivity
ADD
LOGUserID [INT] NOT NULL
CONSTRAINT DF_tblActivity_LOGUserID DEFAULT( 0 ),
LOGDATE [DATETIME] NOT NULL
CONSTRAINT DF_tblActivity_LOGDATE DEFAULT( GETDATE() )
Hope this helps.
Dan Guzman
SQL Server MVP
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...sql

alter table problem?

in a stored procedure , i've the following coding
...
create table tbl (
a int, b int
)
insert into tbl values (1, 2)
alter table tbl drop column b
select * from tbl
...
sql server returns "Column name or number of supplied values does not match
table definition."
Could anyone help me please.the table is temp table
"Win" <aaa@.aaa.com> wrote in message
news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not
match
> table definition."
> Could anyone help me please.
>|||Win
create table #tbl (
a int, b int
)
insert into #tbl values (1, 2)
GO
alter table #tbl drop column b
GO
select * from #tbl
drop table #tbl
"Win" <aaa@.aaa.com> wrote in message
news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
> the table is temp table
> "Win" <aaa@.aaa.com> wrote in message
> news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> match
>|||Cannot alter table '#tbl' because this table does not exist in database
'abc'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
> Win
> create table #tbl (
> a int, b int
> )
> insert into #tbl values (1, 2)
> GO
> alter table #tbl drop column b
> GO
> select * from #tbl
> drop table #tbl
>
> "Win" <aaa@.aaa.com> wrote in message
> news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
>|||Win
On my machine it works file. What version are you using?
"Win" <aaa@.aaa.com> wrote in message
news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Cannot alter table '#tbl' because this table does not exist in database
> 'abc'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
>|||The procedure is parsed as a unit, before any execution has taken place. So,
when the SELECT is
parsed, the ALTER hasn't occurred yet, hence the error message. This is expe
cted. If you post the
logic behind doing this, someone might provide an alternative, or you can us
e dynamic SQL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Win" <aaa@.aaa.com> wrote in message news:%23XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...darkred">
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not matc
h
> table definition."
> Could anyone help me please.
>|||my coding is inside a stored procedure
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uToyZl2OGHA.428@.tk2msftngp13.phx.gbl...
> Win
> On my machine it works file. What version are you using?
>
> "Win" <aaa@.aaa.com> wrote in message
> news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
not
>

Alter table permission to dbo

I have the following requirement

I am creating a login and database user 'test' on a database with dbo
role .
I want to remove create table , alter table permisions to this user.
I am able to revoke create table permission but alter table goes
through.
I gave a command deny insert,delete,update on ssycolumns to test.
Still I am not able to prevent user altering schema . Alter table
successfully goes throgh.

I do not want to use datreader and datwriter role.
since I want user 'test' to create storred procedure with dbo owner

Is there a way to achieve this ?

Thanks

M A Srinivas"M A Srinivas" <masri@.vsnl.com> wrote in message
news:f7e90f78.0309260634.3791a935@.posting.google.c om...
> I have the following requirement
> I am creating a login and database user 'test' on a database with dbo
> role .
> I want to remove create table , alter table permisions to this user.
> I am able to revoke create table permission but alter table goes
> through.
> I gave a command deny insert,delete,update on ssycolumns to test.
> Still I am not able to prevent user altering schema . Alter table
> successfully goes throgh.
> I do not want to use datreader and datwriter role.
> since I want user 'test' to create storred procedure with dbo owner
> Is there a way to achieve this ?
> Thanks
> M A Srinivas

You can't prevent the user from modifying/dropping an existing object. If
you need to create objects with dbo owner, then the user must be in the
db_owner role, and that means he can modify/drop any dbo object. If you can
explain why you need the test user to create stored procedures, then perhaps
someone can suggest an alternative approach. Are you creating the procedures
dynamically, are you deploying new code to several server, etc.

Simon

Alter table name

Is there a way to alter a table name through query analyser.
I know how to do it through enteprise manager but would like to create a job
or package that runs daily and renames an existing table and creates a new
one.You can use sp_rename, see Books Online...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
job
> or package that runs daily and renames an existing table and creates a new
> one.
>|||check out sp_rename in BOL.
--
Andrew J. Kelly SQL MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>|||You can change object names with sp_rename. A simple example:
EXEC sp_rename 'Table1', 'Table1_Old'
GO
CREATE TABLE Table1
(
MyData int
)
GO
Note that renaming a table will not rename the associated constraints or
triggers. You'll need to rename those object too before recreating.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>

Alter table name

Is there a way to alter a table name through query analyser.
I know how to do it through enteprise manager but would like to create a job
or package that runs daily and renames an existing table and creates a new
one.
You can use sp_rename, see Books Online...
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
job
> or package that runs daily and renames an existing table and creates a new
> one.
>
|||check out sp_rename in BOL.
Andrew J. Kelly SQL MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>
|||You can change object names with sp_rename. A simple example:
EXEC sp_rename 'Table1', 'Table1_Old'
GO
CREATE TABLE Table1
(
MyData int
)
GO
Note that renaming a table will not rename the associated constraints or
triggers. You'll need to rename those object too before recreating.
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:OB1G5zR4EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Is there a way to alter a table name through query analyser.
> I know how to do it through enteprise manager but would like to create a
> job or package that runs daily and renames an existing table and creates a
> new one.
>

ALTER TABLE MyT ALTER COLUMN IdtyCol t_idty NOT NULL Identity

Hello World,
Is there a way not to drop and create the table with temp table to alter a
column as in the subject?
Thanks,
C TO
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
Ehm, as far as I know, the "subject" should work just fine.
Is there an error that you're getting? If so, what is it?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||YOu have to recreate the column on order to create a identity column:
http://www.windowsitpro.com/Article...2080/22080.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"C TO" <CTO@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2193C5FD-CC98-417A-90EC-751F71128B19@.microsoft.com...
> Hello World,
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
> Thanks,
> C TO|||
a
> Ehm, as far as I know, the "subject" should work just fine.
> Is there an error that you're getting? If so, what is it?
Woops, mixed this up with "not null".
Bugger.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

Alter Table

Hi, i think that my question is stupid, but i will ask anyway ..
I want to allow a user to create stored procedure, alter stored procedure,
and drop procedure, but the same user cant alter any table, can i do that '
?
Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200508/1You can
GRANT CREATE PROC TO username
The procedures that the user creates will that user also be able to alter an
d drop. But the user
will only be able to create procedures with that user as the owner, and the
user will only be able
to alter and drop the procedures that he/she is the owner of.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Plantador R via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:525A944123AA0@.webservertalk.com...
> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
?
> Thanks
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200508/1|||Sure you can do that.
AMB
"Plantador R via webservertalk.com" wrote:

> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
?
> Thanks
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200508/1
>|||Thanks . Ill try it ...
Message posted via http://www.webservertalk.com

Alter table

Need some help with the following
My goal is to create a unique field by Concatenating two columns.
I am getting the following error
"Warning: The table 'Copy_AFS' has been created but its maximum row size
(928267) exceeds the maximum number of bytes per row (8060). INSERT or UPDAT
E
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
Warning! The maximum key length is 900 bytes. The index 'ak1_some_key' has
maximum length of 16000 bytes. For some combination of large values, the
insert/update operation will fail."
Please let me know if I have the correct statement
ALTER TABLE Copy_AFS
ADD CONSTRAINT Key_TDM
UNIQUE ([CORP-NUM],[REFERENCE-NUMBER]);
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:56E90F9A-EF19-4879-9930-A5229C3F1BFF@.microsoft.com...
> Need some help with the following
> My goal is to create a unique field by Concatenating two columns.
> I am getting the following error
> "Warning: The table 'Copy_AFS' has been created but its maximum row size
> (928267) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> Warning! The maximum key length is 900 bytes. The index 'ak1_some_key' has
> maximum length of 16000 bytes. For some combination of large values, the
> insert/update operation will fail."
>
> Please let me know if I have the correct statement
>
> ALTER TABLE Copy_AFS
> ADD CONSTRAINT Key_TDM
> UNIQUE ([CORP-NUM],[REFERENCE-NUMBER]);
>
> Thanks
>
That's not an error, it's a warning. Your table and index allow data that is
larger than the supported maximum. That means you'll receive an error if you
try to populate those columns with data that is too large. You can ignore
the message and continue but the more prudent course of action would be to
change your table design.
There are some solutions but first it would help if you could state what
version and edition of SQL Server you are using.
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
--

alter table

I am using MS SQL server 2005 management studio. When I go to the "script
table as", the "alter to" is grey out but the other options such as "create
to", "select to" and etc ... are all available. I already login as sa. Can
anyone please help? Thanks.00KobeBrian wrote:
> I am using MS SQL server 2005 management studio. When I go to the "script
> table as", the "alter to" is grey out but the other options such as "create
> to", "select to" and etc ... are all available. I already login as sa. Can
> anyone please help? Thanks.
You can script procs, views and functions as ALTER scripts. You can't
do that with tables because there is no equivalent statement for
tables. For example the ALTER PROCEDURE statement really means
"recreate the entire procedure". ALTER TABLE on the other hand just
adds to the existing table structure.
The nearest equivalent would be to DROP and then CREATE the table - but
of course you lose your data if you do that.
--
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
--|||OK. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163055114.197968.327260@.i42g2000cwa.googlegroups.com...
> 00KobeBrian wrote:
>> I am using MS SQL server 2005 management studio. When I go to the "script
>> table as", the "alter to" is grey out but the other options such as
>> "create
>> to", "select to" and etc ... are all available. I already login as sa.
>> Can
>> anyone please help? Thanks.
>
> You can script procs, views and functions as ALTER scripts. You can't
> do that with tables because there is no equivalent statement for
> tables. For example the ALTER PROCEDURE statement really means
> "recreate the entire procedure". ALTER TABLE on the other hand just
> adds to the existing table structure.
> The nearest equivalent would be to DROP and then CREATE the table - but
> of course you lose your data if you do that.
> --
> 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
> --
>

alter table

Using the alter table command i create a new column on a existing table.
When I creater this new column I want to set the default value as 0. For
some reason the column name is null.
SQL statement:
ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
[ACTIVEFLAGS] [bit] DEFAULT ((0))
Any ideas or the issue with SQL statement?
Thanks,
Big DBig D,
The default value becomes 0 for new rows, but I'll guess what
you want is for the value of this column in existing rows to be 0. In
order to apply a DEFAULT to existing rows, add WITH VALUES to
the statement (and remove NOCHECK, because it doesn't make any
sense for a DEFAULT constraint).
ALTER TABLE [dbo].[DMINFORMATION]
ADD [ACTIVEFLAGS] [bit] DEFAULT 0 WITH VALUES
Steve Kass
Drew University
Big D wrote:

>Using the alter table command i create a new column on a existing table.
>When I creater this new column I want to set the default value as 0. For
>some reason the column name is null.
>SQL statement:
>ALTER TABLE [dbo].[DMINFORMATION] WITH NOCHECK ADD
>[ACTIVEFLAGS] [bit] DEFAULT ((0))
>Any ideas or the issue with SQL statement?
>Thanks,
>Big D
>
>

alter table

I am using MS SQL server 2005 management studio. When I go to the "script
table as", the "alter to" is grey out but the other options such as "create
to", "select to" and etc ... are all available. I already login as sa. Can
anyone please help? Thanks.00KobeBrian wrote:
> I am using MS SQL server 2005 management studio. When I go to the "script
> table as", the "alter to" is grey out but the other options such as "create
> to", "select to" and etc ... are all available. I already login as sa. Can
> anyone please help? Thanks.
You can script procs, views and functions as ALTER scripts. You can't
do that with tables because there is no equivalent statement for
tables. For example the ALTER PROCEDURE statement really means
"recreate the entire procedure". ALTER TABLE on the other hand just
adds to the existing table structure.
The nearest equivalent would be to DROP and then CREATE the table - but
of course you lose your data if you do that.
--
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
--|||OK. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163055114.197968.327260@.i42g2000cwa.googlegroups.com...
> 00KobeBrian wrote:
>> I am using MS SQL server 2005 management studio. When I go to the "script
>> table as", the "alter to" is grey out but the other options such as
>> "create
>> to", "select to" and etc ... are all available. I already login as sa.
>> Can
>> anyone please help? Thanks.
>
> You can script procs, views and functions as ALTER scripts. You can't
> do that with tables because there is no equivalent statement for
> tables. For example the ALTER PROCEDURE statement really means
> "recreate the entire procedure". ALTER TABLE on the other hand just
> adds to the existing table structure.
> The nearest equivalent would be to DROP and then CREATE the table - but
> of course you lose your data if you do that.
> --
> 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
> --
>

Alter Table

Hi, i think that my question is stupid, but i will ask anyway ..
I want to allow a user to create stored procedure, alter stored procedure,
and drop procedure, but the same user cant alter any table, can i do that '
?
Thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200508/1Hi,
ALTER PROCEDURE and DROP PROCEDURE commands are not grantable. Only solution
is to give db_ddladmin tole to
this login account. In this case that user will be able to ALTER and DROP
tables.
Thanks
Hari
SQL Server MVP
"Plantador R via droptable.com" <forum@.droptable.com> wrote in message
news:525A9612E2CA8@.droptable.com...
> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
> ?
> Thanks
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...curity/200508/1

Alter statement to create foreign key relationships

Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?

ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])

REFERENCES [Category] ([CategoryID])

ON UPDATE CASCADE

ON DELETE CASCADE

The syntax is ALTER TABLE [] ADD CONSTRAINT []

The SQL Mobile Books Online cover the syntax of the ALTER TABLE statement if you have further questions.

Darren

|||Thanks Darren, I looked in the books online but I must have overlooked that.

Alter service <SVC1> (Add contract <CONTRACT>) does not really work, need help

Hi,

Sure you can run

Create Service SVC1 ON QUEUE QUEUE1 (CONTRACT1);

alter service SVC1 (add contract CONTRACT2);

But You can only send message using contract1, when you try to send msg using contract2, it always say can not find CONTRACT "contract2".

The following query shows the contract is there.

select s.*,c.* from sys.service_contract_usages U

inner join sys.services S on S.service_id = U.service_id

inner join sys.service_contracts C on U.service_contract_id=C.service_contract_id

where S.name='SVC1'

declare @.lMsg xml

declare @.ConversationHandle uniqueidentifier

set @.lMsg = '<test>testing</test>'

Begin Transaction

Begin Dialog @.ConversationHandle

From Service SVC1

To Service 'SVC2'

On Contract contract1

WITH Encryption=off;

SEND

ON CONVERSATION @.ConversationHandle

Message Type [type1]

Commit

The above works, but the following will not work.

declare @.lMsg xml

declare @.ConversationHandle uniqueidentifier

set @.lMsg = '<test>testing</test>'

Begin Transaction

Begin Dialog @.ConversationHandle

From Service SVC1

To Service 'SVC2'

On Contract CONTRACT2

WITH Encryption=off;

SEND

ON CONVERSATION @.ConversationHandle

Message Type [type2]

(@.lMsg)

Commit

Any idea ?

Thanks!

The service contract bindings of the initiator service (SVC1) are irelevant. Is the target service (SVC2) that has to be bound to a specific contract. Try altering SVC2.|||

I mean everywhere SVC2. it is a typo when I posted the msg, my bad.

But still the alter service is still not working, any idea ?

|||

Works fine for me:

Code Snippet

use tempdb

go

create message type mt1 validation = none;

create message type mt2 validation = none;

create contract sc1 (mt1 sent by any);

create contract sc2 (mt2 sent by any);

create queue q1;

create queue q2;

create service svc1 on queue q1;

create service svc2 on queue q2 ([sc1]);

alter service svc2 (add contract [sc2]);

go

declare @.h uniqueidentifier;

begin dialog conversation @.h

from service svc1

to service 'svc2', 'current database'

on contract [sc2]

with encryption = off;

send on conversation @.h message type [mt2];

waitfor (receive message_type_name, service_contract_name, * from q2);

go

message_type_name service_contract_name status priority queuing_order conversation_group_id conversation_handle message_sequence_number service_name service_id service_contract_name service_contract_id message_type_name message_type_id validation message_body

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

mt2 sc2 1 0 0 DDCD3F76-9C50-DC11-B57C-00188B111155 DECD3F76-9C50-DC11-B57C-00188B111155 0 svc2 65537 sc2 65537 mt2 65537 N NULL

(1 row(s) affected)

|||

I am using distributed environment, It did not work when I tried.

I will re-test it, and update you.

|||It works, My bad, because I have different contract names, which caused the failure. Sorry for the late response.

Alter service <SVC1> (Add contract <CONTRACT>) does not really work, need help

Hi,

Sure you can run

Create Service SVC1 ON QUEUE QUEUE1 (CONTRACT1);

alter service SVC1 (add contract CONTRACT2);

But You can only send message using contract1, when you try to send msg using contract2, it always say can not find CONTRACT "contract2".

The following query shows the contract is there.

select s.*,c.* from sys.service_contract_usages U

inner join sys.services S on S.service_id = U.service_id

inner join sys.service_contracts C on U.service_contract_id=C.service_contract_id

where S.name='SVC1'

declare @.lMsg xml

declare @.ConversationHandle uniqueidentifier

set @.lMsg = '<test>testing</test>'

Begin Transaction

Begin Dialog @.ConversationHandle

From Service SVC1

To Service 'SVC2'

On Contract contract1

WITH Encryption=off;

SEND

ON CONVERSATION @.ConversationHandle

Message Type [type1]

Commit

The above works, but the following will not work.

declare @.lMsg xml

declare @.ConversationHandle uniqueidentifier

set @.lMsg = '<test>testing</test>'

Begin Transaction

Begin Dialog @.ConversationHandle

From Service SVC1

To Service 'SVC2'

On Contract CONTRACT2

WITH Encryption=off;

SEND

ON CONVERSATION @.ConversationHandle

Message Type [type2]

(@.lMsg)

Commit

Any idea ?

Thanks!

The service contract bindings of the initiator service (SVC1) are irelevant. Is the target service (SVC2) that has to be bound to a specific contract. Try altering SVC2.|||

I mean everywhere SVC2. it is a typo when I posted the msg, my bad.

But still the alter service is still not working, any idea ?

|||

Works fine for me:

Code Snippet

use tempdb

go

create message type mt1 validation = none;

create message type mt2 validation = none;

create contract sc1 (mt1 sent by any);

create contract sc2 (mt2 sent by any);

create queue q1;

create queue q2;

create service svc1 on queue q1;

create service svc2 on queue q2 ([sc1]);

alter service svc2 (add contract [sc2]);

go

declare @.h uniqueidentifier;

begin dialog conversation @.h

from service svc1

to service 'svc2', 'current database'

on contract [sc2]

with encryption = off;

send on conversation @.h message type [mt2];

waitfor (receive message_type_name, service_contract_name, * from q2);

go

message_type_name service_contract_name status priority queuing_order conversation_group_id conversation_handle message_sequence_number service_name service_id service_contract_name service_contract_id message_type_name message_type_id validation message_body

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

mt2 sc2 1 0 0 DDCD3F76-9C50-DC11-B57C-00188B111155 DECD3F76-9C50-DC11-B57C-00188B111155 0 svc2 65537 sc2 65537 mt2 65537 N NULL

(1 row(s) affected)

|||

I am using distributed environment, It did not work when I tried.

I will re-test it, and update you.

|||It works, My bad, because I have different contract names, which caused the failure. Sorry for the late response.