"Owner Name" cannot be omitted in sql 2005 querries?

Dear All,
Our project was done in SQL 2000, now we are migrating to 2005.
however, there are lots of command such as:
a) SELECT * FROM myTableName
which, shall be
b) SELECT * FROM myLoginId.myTableName
both (a) and (b) are ok in sql 2000, as long as you login with
myLoginId, but, only (b) works in sql 2005.
seems in sql 2005, you can not omit the "Owner Name" when speifying a
table, unless the table's owner is 'DBO'.
which means, even if I login with myLoginId, I have to use the detailed
format (b),not (a).
this causes trouble in our project -- developed in sql 2000, hundreds
of command omitted the owner name prefix, troublesome to modify one by
one.
Any workaround or suggestions, please? Thanks.
Hi, athos
In SQL Server 2005, schemas and users are different things (in SQL
Server 2000, they were about the same thing). You need to specify the
default schema for each user and then it will work without the prefix.
Use something like this:
ALTER USER myLoginId WITH DEFAULT_SCHEMA = myLoginId
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms190387.aspx
Razvan
|||Hi Athos
In addition to Razvan's comments, please keep in mind that a login ID and a
database user are two different things. Only a database user can own objects
in a database; a login never does. Although the user name and login id might
be the same name, they are still two different things. And now in SQL 2005
we also have schema names and default schemas. If an object is in your
default schema, no matter who owns the object or the schema, you don't need
to specify the schema.
But it is good practice, and it has ALWAYS been good practice even in SQL
2000, to specify the owner name to avoid ambiguity. There is also a
performance benefit to fully qualifying the object as there is more chance
an existing query plan can be reused the object is fully qualified.
Please read about users and logins in the SQL 2000 BOL.
Please read about user/schema separation in the SQL 2005 BOL.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"athos" <athos.liu@.gmail.com> wrote in message
news:1136380087.559222.4320@.o13g2000cwo.googlegrou ps.com...
> Dear All,
> Our project was done in SQL 2000, now we are migrating to 2005.
> however, there are lots of command such as:
> a) SELECT * FROM myTableName
> which, shall be
> b) SELECT * FROM myLoginId.myTableName
> both (a) and (b) are ok in sql 2000, as long as you login with
> myLoginId, but, only (b) works in sql 2005.
> seems in sql 2005, you can not omit the "Owner Name" when speifying a
> table, unless the table's owner is 'DBO'.
> which means, even if I login with myLoginId, I have to use the detailed
> format (b),not (a).
> this causes trouble in our project -- developed in sql 2000, hundreds
> of command omitted the owner name prefix, troublesome to modify one by
> one.
> Any workaround or suggestions, please? Thanks.
>
|||Hi guys,
thanks for your clarificatoin of my misunderstanding of some concepts.
yeah, it's good to fully specify the owner or schema name to avoid
ambiguity, however, it's me that suggest to upgrade our project from
SQL Server 2000 to 2005, and in our developing team I'm trying to avoid
other members' criticizing such as "why shall I search every line to
add some code due to your suggestion?" , or "why something runs fine in
SQL 2000 but not this modern 2005?"
back to my issue, let's say
--On my server "myServer" there is a login named "myLogin", and a
database "myDatabase".
--In "myDatabase" i have a user named "myUserFromMyLogin" which mapped
to the login "myLogin", and a table named "myTable" whose owner (user)
is "myUserFromMyLogin", and a schema named "mySchema" which has one
user "myUserFromMyLogin"
now, "If an object is in your default schema, no matter who owns the
object or the schema, you don't need
to specify the schema. " thanks for you help Kalen, however still a
little bit uncertain here, pls help...
first, when i loggin as the user "myUserFromMyLogin", I shall be using
its default schema "mySchema", right?
second, how to put the object, the table named "myTable", into the
default schema "mySchema"? I can see in the object explorer that the
table's full name is "myUserFromMyLogin.myTable", which means its owner
is "myUserFromMyLogin", which command shall I use to link the object
and the schema?
third, I can directly use commands like "SELECT * FROM myTable" now,
right?
thanks for your valuable time, pls help. Thanks.
yours,
athos.
|||Hi, Athos
If the table's full name is "myUserFromMyLogin.myTable", this means
that it's in the "myUserFromMyLogin" schema. You only need to execute
the following (once), to make the "myUserFromMyLogin" schema to be the
default schema for the "myUserFromMyLogin" user:
ALTER USER myUserFromMyLogin WITH DEFAULT_SCHEMA = myUserFromMyLogin
Razvan
|||Dear Razvan,
Thanks for your reply. Still cannot fix it.
First I made a mistake again. that is, the prefix of the table's
fullname is the schma, not the user.
OK, now what i did is:
Step1. create a Database named [Test], it's default owner is [sa]
Step2. create a User in Database [Test] named [kimliuTest], which maps
to the Login [kkhad\kimliu] on this Database Server, it's a windows
domain user.
Step3. create a Schema in Database [Test] named [schTest], whose Schema
Owner is User [kimliuTest]
Step4. created two tables: [dbo].[t1] and [schTest].[t2]
Step5. run the script
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
to set the default schema.
now my problem is:
Problem 1.
when I try to run
exec sp_changeobjectowner 'dbo.t1', 'schTest'
exec sp_changeobjectowner 'dbo.t1', 'kimliuTest'
, it returns
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'schTest' does not exist in
this database.
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'kimliuTest' does not exist in
this database.
why?
Problem 2:
when I try to run
select * from schTest.t2
, everything is OK, but if I run
select * from t2
the error message says:
Msg 208, Level 16, State 1, Line 1
Invalid object name 't2'.
why? I'm loggin in with my [kkhad\kimliu] windows domain id , Active
Monitor also shows that. but the command does not work?
Please help, thank you!
yours,
Athos.
|||Hello, Athos

> Problem 1:
> when I try to run: exec sp_changeobjectowner [...]
> it returns:
> Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107
> Database principal or schema 'schTest' does not exist in this database.
Books Online 2005 says the following about sp_changeobjectowner:
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER SCHEMA or
ALTER AUTHORIZATION instead. sp_changeobjectowner changes both
the schema and the owner. To preserve compatibility with earlier
versions of SQL Server, this stored procedure will only change
object owners when both the current owner and the new owner
own schemas that have the same name as their database user names.
So that's why it didn't work: the schema name and the owner name were
different. We can use one of the following:
ALTER SCHEMA schTest TRANSFER dbo.t1
or:
ALTER AUTHORIZATION ON dbo.t1 to kimliuTest
Note that the two statements written above have different effects:
the first changes the schema, the second changes the owner.
You probably want to run:
ALTER SCHEMA schTest TRANSFER dbo.t1
ALTER AUTHORIZATION ON schTest.t1 TO SCHEMA OWNER
This will make the table to appear as if it was created as
"schTest.t1". Of course, it's better to create the table in the correct
schema from the start (instead of creating it in the dbo schema and
moving it to some other schema after that).

> Problem 2:
> when I try to run [...] select * from t2
> the error message says: "Invalid object name 't2'."
As I've said, you need to change the default schema for the user:
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
After running the above, if you run "select * from t2" it should work.
You need to keep in mind that if the schema "x" is owned by the user
"y", this does not imply that the user "y" has the schema "x" as his
default schema.
Razvan
|||anybody could help please? thanks!
|||anybody could help, please?
|||anybody could help, please?

"Owner Name" cannot be omitted in sql 2005 querries?

Dear All,
Our project was done in SQL 2000, now we are migrating to 2005.
however, there are lots of command such as:
a) SELECT * FROM myTableName
which, shall be
b) SELECT * FROM myLoginId.myTableName
both (a) and (b) are ok in sql 2000, as long as you login with
myLoginId, but, only (b) works in sql 2005.
seems in sql 2005, you can not omit the "Owner Name" when speifying a
table, unless the table's owner is 'DBO'.
which means, even if I login with myLoginId, I have to use the detailed
format (b),not (a).
this causes trouble in our project -- developed in sql 2000, hundreds
of command omitted the owner name prefix, troublesome to modify one by
one.
Any workaround or suggestions, please? Thanks.Hi, athos
In SQL Server 2005, schemas and users are different things (in SQL
Server 2000, they were about the same thing). You need to specify the
default schema for each user and then it will work without the prefix.
Use something like this:
ALTER USER myLoginId WITH DEFAULT_SCHEMA = myLoginId
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms190387.aspx
Razvan|||Hi Athos
In addition to Razvan's comments, please keep in mind that a login ID and a
database user are two different things. Only a database user can own objects
in a database; a login never does. Although the user name and login id might
be the same name, they are still two different things. And now in SQL 2005
we also have schema names and default schemas. If an object is in your
default schema, no matter who owns the object or the schema, you don't need
to specify the schema.
But it is good practice, and it has ALWAYS been good practice even in SQL
2000, to specify the owner name to avoid ambiguity. There is also a
performance benefit to fully qualifying the object as there is more chance
an existing query plan can be reused the object is fully qualified.
Please read about users and logins in the SQL 2000 BOL.
Please read about user/schema separation in the SQL 2005 BOL.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"athos" <athos.liu@.gmail.com> wrote in message
news:1136380087.559222.4320@.o13g2000cwo.googlegroups.com...
> Dear All,
> Our project was done in SQL 2000, now we are migrating to 2005.
> however, there are lots of command such as:
> a) SELECT * FROM myTableName
> which, shall be
> b) SELECT * FROM myLoginId.myTableName
> both (a) and (b) are ok in sql 2000, as long as you login with
> myLoginId, but, only (b) works in sql 2005.
> seems in sql 2005, you can not omit the "Owner Name" when speifying a
> table, unless the table's owner is 'DBO'.
> which means, even if I login with myLoginId, I have to use the detailed
> format (b),not (a).
> this causes trouble in our project -- developed in sql 2000, hundreds
> of command omitted the owner name prefix, troublesome to modify one by
> one.
> Any workaround or suggestions, please? Thanks.
>|||Hi guys,
thanks for your clarificatoin of my misunderstanding of some concepts.
yeah, it's good to fully specify the owner or schema name to avoid
ambiguity, however, it's me that suggest to upgrade our project from
SQL Server 2000 to 2005, and in our developing team I'm trying to avoid
other members' criticizing such as "why shall I search every line to
add some code due to your suggestion?" , or "why something runs fine in
SQL 2000 but not this modern 2005?"
back to my issue, let's say
--On my server "myServer" there is a login named "myLogin", and a
database "myDatabase".
--In "myDatabase" i have a user named "myUserFromMyLogin" which mapped
to the login "myLogin", and a table named "myTable" whose owner (user)
is "myUserFromMyLogin", and a schema named "mySchema" which has one
user "myUserFromMyLogin"
now, "If an object is in your default schema, no matter who owns the
object or the schema, you don't need
to specify the schema. " thanks for you help Kalen, however still a
little bit uncertain here, pls help...
first, when i loggin as the user "myUserFromMyLogin", I shall be using
its default schema "mySchema", right?
second, how to put the object, the table named "myTable", into the
default schema "mySchema"? I can see in the object explorer that the
table's full name is "myUserFromMyLogin.myTable", which means its owner
is "myUserFromMyLogin", which command shall I use to link the object
and the schema?
third, I can directly use commands like "SELECT * FROM myTable" now,
right?
thanks for your valuable time, pls help. Thanks.
yours,
athos.|||Hi, Athos
If the table's full name is "myUserFromMyLogin.myTable", this means
that it's in the "myUserFromMyLogin" schema. You only need to execute
the following (once), to make the "myUserFromMyLogin" schema to be the
default schema for the "myUserFromMyLogin" user:
ALTER USER myUserFromMyLogin WITH DEFAULT_SCHEMA = myUserFromMyLogin
Razvan|||Dear Razvan,
Thanks for your reply. Still cannot fix it.
First I made a mistake again. that is, the prefix of the table's
fullname is the schma, not the user.
OK, now what i did is:
Step1. create a Database named [Test], it's default owner is [sa]
Step2. create a User in Database [Test] named [kimliuTest], which ma
ps
to the Login [kkhad\kimliu] on this Database Server, it's a windows
domain user.
Step3. create a Schema in Database [Test] named [schTest], whose Sch
ema
Owner is User [kimliuTest]
Step4. created two tables: [dbo].[t1] and [schTest].[t2]
Step5. run the script
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
to set the default schema.
now my problem is:
Problem 1.
when I try to run
exec sp_changeobjectowner 'dbo.t1', 'schTest'
exec sp_changeobjectowner 'dbo.t1', 'kimliuTest'
, it returns
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'schTest' does not exist in
this database.
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'kimliuTest' does not exist in
this database.
why?
Problem 2:
when I try to run
select * from schTest.t2
, everything is OK, but if I run
select * from t2
the error message says:
Msg 208, Level 16, State 1, Line 1
Invalid object name 't2'.
why? I'm loggin in with my [kkhad\kimliu] windows domain id , Active
Monitor also shows that. but the command does not work?
Please help, thank you!
yours,
Athos.|||Hello, Athos

> Problem 1:
> when I try to run: exec sp_changeobjectowner [...]
> it returns:
> Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107
> Database principal or schema 'schTest' does not exist in this database.
Books Online 2005 says the following about sp_changeobjectowner:
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER SCHEMA or
ALTER AUTHORIZATION instead. sp_changeobjectowner changes both
the schema and the owner. To preserve compatibility with earlier
versions of SQL Server, this stored procedure will only change
object owners when both the current owner and the new owner
own schemas that have the same name as their database user names.
So that's why it didn't work: the schema name and the owner name were
different. We can use one of the following:
ALTER SCHEMA schTest TRANSFER dbo.t1
or:
ALTER AUTHORIZATION ON dbo.t1 to kimliuTest
Note that the two statements written above have different effects:
the first changes the schema, the second changes the owner.
You probably want to run:
ALTER SCHEMA schTest TRANSFER dbo.t1
ALTER AUTHORIZATION ON schTest.t1 TO SCHEMA OWNER
This will make the table to appear as if it was created as
"schTest.t1". Of course, it's better to create the table in the correct
schema from the start (instead of creating it in the dbo schema and
moving it to some other schema after that).

> Problem 2:
> when I try to run [...] select * from t2
> the error message says: "Invalid object name 't2'."
As I've said, you need to change the default schema for the user:
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
After running the above, if you run "select * from t2" it should work.
You need to keep in mind that if the schema "x" is owned by the user
"y", this does not imply that the user "y" has the schema "x" as his
default schema.
Razvan|||anybody could help please? thanks!|||anybody could help, please?|||anybody could help, please?

"owner name" cannot be omitted in sql 2005 querries?

Dear All,

Our project was done in SQL 2000, now we are migrating to 2005.
however, there are lots of command such as:

a) SELECT * FROM myTableName

which, shall be

b) SELECT * FROM myLoginId.myTableName

both (a) and (b) are ok in sql 2000, as long as you login with
myLoginId, but, only (b) works in sql 2005.

seems in sql 2005, you can not omit the "Owner Name" when speifying a
table, unless the table's owner is 'DBO'.

which means, even if I login with myLoginId, I have to use the detailed
format (b),not (a).

this causes trouble in our project -- developed in sql 2000, hundreds
of command omitted the owner name prefix, troublesome to modify one by
one.

Any workaround or suggestions, please? Thanks.Hi, athos

Please do not "multi-post". If you really want to send a message to
more than one newsgroup, post a single message with all the newsgroups
in the "To:" field (this is called "cross-posting". See:
http://www.aspfaq.com/etiquette.asp?id=5003
http://www.aspfaq.com/etiquette.asp?id=5004

See my answer in the microsoft.public.sqlserver.server newsgroup:
http://groups.google.com/group/micr...ede4803725ce795

Razvan|||Dear Razvan,

thanks for your information. Sorry I never noticed the line saying
"(Separate multiple groups with commas)". thanks.

yours
Athos.

"Owner Name" cannot be omitted in sql 2005 querries?

Dear All,
Our project was done in SQL 2000, now we are migrating to 2005.
however, there are lots of command such as:
a) SELECT * FROM myTableName
which, shall be
b) SELECT * FROM myLoginId.myTableName
both (a) and (b) are ok in sql 2000, as long as you login with
myLoginId, but, only (b) works in sql 2005.
seems in sql 2005, you can not omit the "Owner Name" when speifying a
table, unless the table's owner is 'DBO'.
which means, even if I login with myLoginId, I have to use the detailed
format (b),not (a).
this causes trouble in our project -- developed in sql 2000, hundreds
of command omitted the owner name prefix, troublesome to modify one by
one.
Any workaround or suggestions, please? Thanks.Hi, athos
In SQL Server 2005, schemas and users are different things (in SQL
Server 2000, they were about the same thing). You need to specify the
default schema for each user and then it will work without the prefix.
Use something like this:
ALTER USER myLoginId WITH DEFAULT_SCHEMA = myLoginId
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms190387.aspx
Razvan|||Hi Athos
In addition to Razvan's comments, please keep in mind that a login ID and a
database user are two different things. Only a database user can own objects
in a database; a login never does. Although the user name and login id might
be the same name, they are still two different things. And now in SQL 2005
we also have schema names and default schemas. If an object is in your
default schema, no matter who owns the object or the schema, you don't need
to specify the schema.
But it is good practice, and it has ALWAYS been good practice even in SQL
2000, to specify the owner name to avoid ambiguity. There is also a
performance benefit to fully qualifying the object as there is more chance
an existing query plan can be reused the object is fully qualified.
Please read about users and logins in the SQL 2000 BOL.
Please read about user/schema separation in the SQL 2005 BOL.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"athos" <athos.liu@.gmail.com> wrote in message
news:1136380087.559222.4320@.o13g2000cwo.googlegroups.com...
> Dear All,
> Our project was done in SQL 2000, now we are migrating to 2005.
> however, there are lots of command such as:
> a) SELECT * FROM myTableName
> which, shall be
> b) SELECT * FROM myLoginId.myTableName
> both (a) and (b) are ok in sql 2000, as long as you login with
> myLoginId, but, only (b) works in sql 2005.
> seems in sql 2005, you can not omit the "Owner Name" when speifying a
> table, unless the table's owner is 'DBO'.
> which means, even if I login with myLoginId, I have to use the detailed
> format (b),not (a).
> this causes trouble in our project -- developed in sql 2000, hundreds
> of command omitted the owner name prefix, troublesome to modify one by
> one.
> Any workaround or suggestions, please? Thanks.
>|||Hi guys,
thanks for your clarificatoin of my misunderstanding of some concepts.
yeah, it's good to fully specify the owner or schema name to avoid
ambiguity, however, it's me that suggest to upgrade our project from
SQL Server 2000 to 2005, and in our developing team I'm trying to avoid
other members' criticizing such as "why shall I search every line to
add some code due to your suggestion?" , or "why something runs fine in
SQL 2000 but not this modern 2005?"
back to my issue, let's say
--On my server "myServer" there is a login named "myLogin", and a
database "myDatabase".
--In "myDatabase" i have a user named "myUserFromMyLogin" which mapped
to the login "myLogin", and a table named "myTable" whose owner (user)
is "myUserFromMyLogin", and a schema named "mySchema" which has one
user "myUserFromMyLogin"
now, "If an object is in your default schema, no matter who owns the
object or the schema, you don't need
to specify the schema. " thanks for you help Kalen, however still a
little bit uncertain here, pls help...
first, when i loggin as the user "myUserFromMyLogin", I shall be using
its default schema "mySchema", right?
second, how to put the object, the table named "myTable", into the
default schema "mySchema"? I can see in the object explorer that the
table's full name is "myUserFromMyLogin.myTable", which means its owner
is "myUserFromMyLogin", which command shall I use to link the object
and the schema?
third, I can directly use commands like "SELECT * FROM myTable" now,
right?
thanks for your valuable time, pls help. Thanks.
yours,
athos.|||Hi, Athos
If the table's full name is "myUserFromMyLogin.myTable", this means
that it's in the "myUserFromMyLogin" schema. You only need to execute
the following (once), to make the "myUserFromMyLogin" schema to be the
default schema for the "myUserFromMyLogin" user:
ALTER USER myUserFromMyLogin WITH DEFAULT_SCHEMA = myUserFromMyLogin
Razvan|||Dear Razvan,
Thanks for your reply. Still cannot fix it.
First I made a mistake again. that is, the prefix of the table's
fullname is the schma, not the user.
OK, now what i did is:
Step1. create a Database named [Test], it's default owner is [sa]
Step2. create a User in Database [Test] named [kimliuTest], which maps
to the Login [kkhad\kimliu] on this Database Server, it's a windows
domain user.
Step3. create a Schema in Database [Test] named [schTest], whose Schema
Owner is User [kimliuTest]
Step4. created two tables: [dbo].[t1] and [schTest].[t2]
Step5. run the script
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
to set the default schema.
now my problem is:
Problem 1.
when I try to run
exec sp_changeobjectowner 'dbo.t1', 'schTest'
exec sp_changeobjectowner 'dbo.t1', 'kimliuTest'
, it returns
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'schTest' does not exist in
this database.
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'kimliuTest' does not exist in
this database.
why?
Problem 2:
when I try to run
select * from schTest.t2
, everything is OK, but if I run
select * from t2
the error message says:
Msg 208, Level 16, State 1, Line 1
Invalid object name 't2'.
why? I'm loggin in with my [kkhad\kimliu] windows domain id , Active
Monitor also shows that. but the command does not work?
Please help, thank you!
yours,
Athos.|||Hello, Athos
> Problem 1:
> when I try to run: exec sp_changeobjectowner [...]
> it returns:
> Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107
> Database principal or schema 'schTest' does not exist in this database.
Books Online 2005 says the following about sp_changeobjectowner:
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER SCHEMA or
ALTER AUTHORIZATION instead. sp_changeobjectowner changes both
the schema and the owner. To preserve compatibility with earlier
versions of SQL Server, this stored procedure will only change
object owners when both the current owner and the new owner
own schemas that have the same name as their database user names.
So that's why it didn't work: the schema name and the owner name were
different. We can use one of the following:
ALTER SCHEMA schTest TRANSFER dbo.t1
or:
ALTER AUTHORIZATION ON dbo.t1 to kimliuTest
Note that the two statements written above have different effects:
the first changes the schema, the second changes the owner.
You probably want to run:
ALTER SCHEMA schTest TRANSFER dbo.t1
ALTER AUTHORIZATION ON schTest.t1 TO SCHEMA OWNER
This will make the table to appear as if it was created as
"schTest.t1". Of course, it's better to create the table in the correct
schema from the start (instead of creating it in the dbo schema and
moving it to some other schema after that).
> Problem 2:
> when I try to run [...] select * from t2
> the error message says: "Invalid object name 't2'."
As I've said, you need to change the default schema for the user:
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
After running the above, if you run "select * from t2" it should work.
You need to keep in mind that if the schema "x" is owned by the user
"y", this does not imply that the user "y" has the schema "x" as his
default schema.
Razvan|||anybody could help please? thanks!|||anybody could help, please?|||anybody could help, please?|||anybody could help please?|||Dear Razvan,
Thanks for you explanation about [sp_changeobjectowner], [ALTER
SCHEMA..] and [ALTER AUTHORIZATION ..], however, the main issue is
still not solved.
Maybe there is some misunderstanding, what I did is:
Step1. create a Database named [Test] with default owner [sa]
Step2. in Database [Test], create a User [kimliuTest] mapping to Login
[kimliu] on this Database Server
Step3. in Database [Test], create a Schema [schTest] owned by
[kimliuTest]
Step4. created two tables: [dbo].[t1] and [schTest].[t2]
Step5. run the script
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
to set the default schema.
Repeat, I HAVE gone through Step 5, run the script "ALTER USER
kimliuTest WITH DEFAULT_SCHEMA = schTest " to set the default schema.
Then, after all of these steps --
Case 1: Of course, everything is OK when run "SELECT * FROM
schTest.t2", either by running in a SQL Query Editor Window or by .net
code.
Case 2: Cannot use "SELECT * FROM t2", the error message says: "Msg
208, Level 16, State 1, Line 1, Invalid object name 't2'. ".
Case 3: Strangely enough, everything is OK when run "EXECUTE AS
USER='kimliuTest'; SELECT * FROM schTest.t2".
Case 4: Everything is also OK when only run "EXECUTE AS LOGIN='kimliu'
".
Case 5: But, cannot use "EXECUTE AS LOGIN='kimliu'; SELECT * FROM t2",
the error message is the same: "Msg 208, Level 16, State 1, Line 1,
Invalid object name 't2'. ".
I am LOST! Anybody could help PLEASE~~ Thank you very much~
yours
Athos|||Dear Razvan,
Thanks for you explanation about [sp_changeobjectowner], [ALTER
SCHEMA..] and [ALTER AUTHORIZATION ..], however, the main issue is
still not solved.
Maybe there is some misunderstanding, what I did is:
Step1. create a Database named [Test] with default owner [sa]
Step2. in Database [Test], create a User [kimliuTest] mapping to Login
[kimliu] on this Database Server
Step3. in Database [Test], create a Schema [schTest] owned by
[kimliuTest]
Step4. created two tables: [dbo].[t1] and [schTest].[t2]
Step5. run the script
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
to set the default schema.
Repeat, I HAVE gone through Step 5, run the script "ALTER USER
kimliuTest WITH DEFAULT_SCHEMA = schTest " to set the default schema.
Then, after all of these steps --
Case 1: Of course, everything is OK when run "SELECT * FROM
schTest.t2", either by running in a SQL Query Editor Window or by .net
code.
Case 2: Cannot use "SELECT * FROM t2", the error message says: "Msg
208, Level 16, State 1, Line 1, Invalid object name 't2'. ".
Case 3: Strangely enough, everything is OK when run "EXECUTE AS
USER='kimliuTest'; SELECT * FROM t2".
Case 4: Everything is also OK when only run "EXECUTE AS LOGIN='kimliu'
".
Case 5: But, cannot use "EXECUTE AS LOGIN='kimliu'; SELECT * FROM t2",
the error message is the same: "Msg 208, Level 16, State 1, Line 1,
Invalid object name 't2'. ".
I am LOST! Anybody could help PLEASE~~ Thank you very much~
yours
Athos|||Hi, Athos
> Repeat, I HAVE gone through Step 5, run the script "ALTER USER
> kimliuTest WITH DEFAULT_SCHEMA = schTest " to set the default schema.
Sorry, I overlook that part when I first read your previous message.
I was able to reproduce the behaviour you mentioned, but not
consistently.
Please try the following:
USE test
EXECUTE AS LOGIN='kkhad\kimliu'
SELECT SYSTEM_USER AS SystemUser, DB_NAME() as DatabaseName,
USER as UserName, default_schema_name
FROM sys.database_principals WHERE name=USER
REVERT
What result do you get ?
Razvan

"Object is required" (DMO) ?

Dear all,
are you seeing something weird here?
Dim oServer As SQLDMO.SQLServer2
'Dim oServer As SQLDMO.SQLServer2
For Each oServer In oApplication.ListAvailableSQLServers
Debug.Print oServer.Name
Next
This snippet fails.
Let me know where failing is any clue...
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)sorry for bothering
For i = 1 To oApplication.ListAvailableSQLServers.Count
Debug.Print oApplication.ListAvailableSQLServers.Item(i)
Next
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Enric" wrote:

> Dear all,
> are you seeing something weird here?
> Dim oServer As SQLDMO.SQLServer2
> 'Dim oServer As SQLDMO.SQLServer2
> For Each oServer In oApplication.ListAvailableSQLServers
> Debug.Print oServer.Name
> Next
> This snippet fails.
> Let me know where failing is any clue...
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||ListAvailableSQLServers returns a NaneList object, not a SQLServer
collection. Try something like:
Dim Name As String
For Each name in oApplication.ListAvailableSQLServers
Debug.Print name
Next name
Hope this helps.
Dan Guzman
SQL Server MVP
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:FE7AF93D-0E90-4C6F-AF11-FC5DDD846BDB@.microsoft.com...
> Dear all,
> are you seeing something weird here?
> Dim oServer As SQLDMO.SQLServer2
> 'Dim oServer As SQLDMO.SQLServer2
> For Each oServer In oApplication.ListAvailableSQLServers
> Debug.Print oServer.Name
> Next
> This snippet fails.
> Let me know where failing is any clue...
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide
> information
> without seeing the code. location: Alicante (ES)

"Missing Parameter Values" Error shown when subReport includes in Main Report

Dear all,

There is an error,"Missing Parameter Values" shown when sub-report includes in the Main report.
(And there is no parameter setup in sub-report)
What i do is to setup the datasource into sub-report by coding..

I find that they runs fine when the main report, sub-report are separated.

CAn anyone give me some ideas about the solution?

P.S. The platform i am using is VS 2005.. Thanks much

michaelDid you link your subreport to your main?
GJ|||there is no link between the subreport and the main report. Maybe say, main report and subreport are totally 2 different kinds of report.

P.S. the subreport is located in report footer

Thx

"Invalid Cursor Error"

Dear,
Does anyone have a clue when Microsoft is going to release it's next service
pack for SQL-Server? I'm running build 859 at this moment and I can't alter
any table anymore in my enterprise manager. ALTER TABLE is fine as long as
you don't wish to change a column into an identity column. I do not intend
to spend money on this by calling the support line, since I know what the
problem is and spending money on this seems absurd. There is no kb article
for that error as well(although it's is fixed in higher builds than 859) so
there is a big chance they don't want to give me a higher build because of
that (at least that is what appears from other articles). Anyone who can
help me? My development time reduces drastically and reinstalling isn't an
option!
yours sincerly
Michael
"Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Dear,
> Does anyone have a clue when Microsoft is going to release it's next
service
> pack for SQL-Server? I'm running build 859 at this moment and I can't
alter
> any table anymore in my enterprise manager. ALTER TABLE is fine as long as
> you don't wish to change a column into an identity column. I do not intend
> to spend money on this by calling the support line, since I know what the
> problem is and spending money on this seems absurd. There is no kb article
1) If they can't fix that problem, you dont have to spend money, they'll pay
that back.
2) What's your code? Do you use ADO or something like that?
I can tell you that *no* alter table /alter column statement supports that
natively..
--does *not* work
ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
Therefore, I think that the 'cursor error' is raised because some
object/component that you use, did not test this possibility and now it bugs
out...
3) Having the latest ODBC / oledb drivers might help.
Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so on...
4)
this below works...
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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.Tmp_Table1
(
nID int NOT NULL IDENTITY (1, 1),
adfs char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
nID
) ON [PRIMARY]
GO
COMMIT

> for that error as well(although it's is fixed in higher builds than 859)
so
> there is a big chance they don't want to give me a higher build because of
> that (at least that is what appears from other articles). Anyone who can
> help me? My development time reduces drastically and reinstalling isn't an
> option!
> yours sincerly
> Michael
>
|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> wrote in
message news:#BisWlsNEHA.1276@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
> news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> service
> alter
as[vbcol=seagreen]
intend[vbcol=seagreen]
the[vbcol=seagreen]
article
> 1) If they can't fix that problem, you dont have to spend money, they'll
pay
> that back.
>
Well they were nice and are going to send me the patch. I was a bit
frustrated but if they send me the fix, my problems should be over . But a
few nightly hours searching can drive you mad every now and then; :-D

> 2) What's your code? Do you use ADO or something like that?
>
Just plain simple Enterprise Manager. I tried to work around it with the web
data administrator but that didn't work either.

> I can tell you that *no* alter table /alter column statement supports that
> natively..
> --does *not* work
> ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
> Therefore, I think that the 'cursor error' is raised because some
> object/component that you use, did not test this possibility and now it
bugs
> out...
>
Yep that's what I figured out as well. Enterprise Manager generates some
extra statements to support that I've read somewhere.

> 3) Having the latest ODBC / oledb drivers might help.
> Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so
on...
>
I will check that...

> 4)
> this below works...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> 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.Tmp_Table1
> (
> nID int NOT NULL IDENTITY (1, 1),
> adfs char(10) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 ON
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
> SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> ALTER TABLE dbo.Table1 ADD CONSTRAINT
> PK_Table1 PRIMARY KEY CLUSTERED
> (
> nID
> ) ON [PRIMARY]
> GO
> COMMIT
>
I will try that as well. It's always usefull to have such workarounds.
Tnx a lot 4 your time.
yours sincerly
Michael

"Invalid Cursor Error"

Dear,
Does anyone have a clue when Microsoft is going to release it's next service
pack for SQL-Server? I'm running build 859 at this moment and I can't alter
any table anymore in my enterprise manager. ALTER TABLE is fine as long as
you don't wish to change a column into an identity column. I do not intend
to spend money on this by calling the support line, since I know what the
problem is and spending money on this seems absurd. There is no kb article
for that error as well(although it's is fixed in higher builds than 859) so
there is a big chance they don't want to give me a higher build because of
that (at least that is what appears from other articles). Anyone who can
help me? My development time reduces drastically and reinstalling isn't an
option!
yours sincerly
Michael"Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Dear,
> Does anyone have a clue when Microsoft is going to release it's next
service
> pack for SQL-Server? I'm running build 859 at this moment and I can't
alter
> any table anymore in my enterprise manager. ALTER TABLE is fine as long as
> you don't wish to change a column into an identity column. I do not intend
> to spend money on this by calling the support line, since I know what the
> problem is and spending money on this seems absurd. There is no kb article
1) If they can't fix that problem, you dont have to spend money, they'll pay
that back.
2) What's your code? Do you use ADO or something like that?
I can tell you that *no* alter table /alter column statement supports that
natively..
--does *not* work
ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
Therefore, I think that the 'cursor error' is raised because some
object/component that you use, did not test this possibility and now it bugs
out...
3) Having the latest ODBC / oledb drivers might help.
Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so on...
4)
this below works...
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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.Tmp_Table1
(
nID int NOT NULL IDENTITY (1, 1),
adfs char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
nID
) ON [PRIMARY]
GO
COMMIT

> for that error as well(although it's is fixed in higher builds than 859)
so
> there is a big chance they don't want to give me a higher build because of
> that (at least that is what appears from other articles). Anyone who can
> help me? My development time reduces drastically and reinstalling isn't an
> option!
> yours sincerly
> Michael
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> wrote in
message news:#BisWlsNEHA.1276@.TK2MSFTNGP11.phx.gbl...
> "Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
> news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> service
> alter
as[vbcol=seagreen]
intend[vbcol=seagreen]
the[vbcol=seagreen]
article[vbcol=seagreen]
> 1) If they can't fix that problem, you dont have to spend money, they'll
pay
> that back.
>
Well they were nice and are going to send me the patch. I was a bit
frustrated but if they send me the fix, my problems should be over . But a
few nightly hours searching can drive you mad every now and then; :-D

> 2) What's your code? Do you use ADO or something like that?
>
Just plain simple Enterprise Manager. I tried to work around it with the web
data administrator but that didn't work either.

> I can tell you that *no* alter table /alter column statement supports that
> natively..
> --does *not* work
> ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
> Therefore, I think that the 'cursor error' is raised because some
> object/component that you use, did not test this possibility and now it
bugs
> out...
>
Yep that's what I figured out as well. Enterprise Manager generates some
extra statements to support that I've read somewhere.

> 3) Having the latest ODBC / oledb drivers might help.
> Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so
on...
>
I will check that...

> 4)
> this below works...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> 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.Tmp_Table1
> (
> nID int NOT NULL IDENTITY (1, 1),
> adfs char(10) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 ON
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
> SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> ALTER TABLE dbo.Table1 ADD CONSTRAINT
> PK_Table1 PRIMARY KEY CLUSTERED
> (
> nID
> ) ON [PRIMARY]
> GO
> COMMIT
>
I will try that as well. It's always usefull to have such workarounds.
Tnx a lot 4 your time.
yours sincerly
Michael

"Invalid Cursor Error"

Dear,
Does anyone have a clue when Microsoft is going to release it's next service
pack for SQL-Server? I'm running build 859 at this moment and I can't alter
any table anymore in my enterprise manager. ALTER TABLE is fine as long as
you don't wish to change a column into an identity column. I do not intend
to spend money on this by calling the support line, since I know what the
problem is and spending money on this seems absurd. There is no kb article
for that error as well(although it's is fixed in higher builds than 859) so
there is a big chance they don't want to give me a higher build because of
that (at least that is what appears from other articles). Anyone who can
help me? My development time reduces drastically and reinstalling isn't an
option!
yours sincerly
Michael"Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Dear,
> Does anyone have a clue when Microsoft is going to release it's next
service
> pack for SQL-Server? I'm running build 859 at this moment and I can't
alter
> any table anymore in my enterprise manager. ALTER TABLE is fine as long as
> you don't wish to change a column into an identity column. I do not intend
> to spend money on this by calling the support line, since I know what the
> problem is and spending money on this seems absurd. There is no kb article
1) If they can't fix that problem, you dont have to spend money, they'll pay
that back.
2) What's your code? Do you use ADO or something like that?
I can tell you that *no* alter table /alter column statement supports that
natively..
--does *not* work
ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
Therefore, I think that the 'cursor error' is raised because some
object/component that you use, did not test this possibility and now it bugs
out...
3) Having the latest ODBC / oledb drivers might help.
Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so on...
4)
this below works...
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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.Tmp_Table1
(
nID int NOT NULL IDENTITY (1, 1),
adfs char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
nID
) ON [PRIMARY]
GO
COMMIT
> for that error as well(although it's is fixed in higher builds than 859)
so
> there is a big chance they don't want to give me a higher build because of
> that (at least that is what appears from other articles). Anyone who can
> help me? My development time reduces drastically and reinstalling isn't an
> option!
> yours sincerly
> Michael
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> wrote in
message news:#BisWlsNEHA.1276@.TK2MSFTNGP11.phx.gbl...
> "Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
> news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Dear,
> >
> > Does anyone have a clue when Microsoft is going to release it's next
> service
> > pack for SQL-Server? I'm running build 859 at this moment and I can't
> alter
> > any table anymore in my enterprise manager. ALTER TABLE is fine as long
as
> > you don't wish to change a column into an identity column. I do not
intend
> > to spend money on this by calling the support line, since I know what
the
> > problem is and spending money on this seems absurd. There is no kb
article
> 1) If they can't fix that problem, you dont have to spend money, they'll
pay
> that back.
>
Well they were nice and are going to send me the patch. I was a bit
frustrated but if they send me the fix, my problems should be over :). But a
few nightly hours searching can drive you mad every now and then; :-D
> 2) What's your code? Do you use ADO or something like that?
>
Just plain simple Enterprise Manager. I tried to work around it with the web
data administrator but that didn't work either.
> I can tell you that *no* alter table /alter column statement supports that
> natively..
> --does *not* work
> ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
> Therefore, I think that the 'cursor error' is raised because some
> object/component that you use, did not test this possibility and now it
bugs
> out...
>
Yep that's what I figured out as well. Enterprise Manager generates some
extra statements to support that I've read somewhere.
> 3) Having the latest ODBC / oledb drivers might help.
> Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so
on...
>
I will check that...
> 4)
> this below works...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> 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.Tmp_Table1
> (
> nID int NOT NULL IDENTITY (1, 1),
> adfs char(10) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 ON
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
> SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> ALTER TABLE dbo.Table1 ADD CONSTRAINT
> PK_Table1 PRIMARY KEY CLUSTERED
> (
> nID
> ) ON [PRIMARY]
> GO
> COMMIT
>
I will try that as well. It's always usefull to have such workarounds.
Tnx a lot 4 your time.
yours sincerly
Michael

"Cursor-Fetch" problem:Oracle2SQL Server Migration

Dear all,

I have a procedure in Oracle that contains the following cursor:

CURSOR SCHED_TRIPS IS
SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID
FROM Dbo.SCHEDTRIPS_VIEW
WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE)
AND DISPOSITION <> 'V';
BEGIN
FOR S IN SCH_TRIPS LOOP
UPDATE dbo.SCHEDULES T
SET T.DIRTYBIT = 1
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);

UPDATE dbo.SCHEDULES T
SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE;
END LOOP;
COMMIT ;
END;

My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.

Anxiously waiting for help!Most common loop structure is:

while @.@.fetch_status = 0 begin
...
end

But you'll have to change your UPDATE statement to reference the variables that you're going to be FETCHing the values into, rather than referencing the fields from the cursor. Also, by looking at your JOINs you'll have to implement conditional UPDATE because values from the cursor will correspont to only 1 row at a time, while your current syntax suggests that the cursor now is used as a subquery which will not be possible in SQL. In other words it'll look something like this:declare @.stop_num int, @.tripid int, @.stop_type char(1), @.promised_time datetime, @.eta datetime, @.etd datetime
declare s cursor local for
select STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, ETD
from Dbo.SCHEDTRIPS_VIEW
where UNQ_ID = SESSION_ID AND convert(char(8), TRAVELDATE, 112) = convert(char(8), TDATE, 112)
open s
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
while @.@.fetch_status = 0 begin
update t
set t.STOP_NUM = @.stop_num,
t.PROMISED_TIME = @.promised_time,
t.ETA = @.eta,
t.ETD = @.etd,
t.LAST_CHANGED = current_timestamp,
t.DIRTYBIT = case when (t.STOP_NUM <> @.stop_num OR t.ETA <> @.eta) then 1 else t.DIRTYBIT end
from dbo.SCHEDULES t
where t.TRIPID = @.tripid AND t.STOP_TYPE = @.stop_type
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
end
deallocate s
close s|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all. I would code:

UPDATE T
SET T.DIRTYBIT = 1
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID
AND T.STOP_TYPE = S.STOP_TYPE
AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA)
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

UPDATE T
SET T.STOP_NUM = S.STOP_NUM,
T.PROMISED_TIME = S.PROMISED_TIME,
T.ETA = S.ETA,
T.ETD = S.ETD,
T.LAST_CHANGED = SYSDATE
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID AND
T.STOP_TYPE = S.STOP_TYPE;
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

Bill|||Thx rdjabarov for going into the intricacies of my proc and giving a detailed reply.
But this was something which I was trying to avoid. Isn't there something similar to Oracle in SQL Server. Else I will have to declare hundreds of vars bcoz this is not the only proc with this style of code.
Moreover, shouldn't Close cursor statement come before deallocation?

Plz do suggest something to overcome my dilemma.

Thx again|||If you want to mimic the PL/SQL cursor style of updates in TSQL, I'm afraid there are no shortcuts.

As you'll be aware, the widespread use of cursors in ORACLE is unavoidable - that's just how you do things like updating one table from another. The particular syntax of the cursor loop in your example is neat PL/SQL shorthand to make cursor loops easier and quicker to code.

There is no equivalent to this shorthand in TSQL. You just have to do it the long way :(

In TSQL (in both MSSQL and Sybase) the use of cursors is widely discouraged, where avoidable. There is a significant overhead in using them that simply isn't there in ORACLE.

I don't know if this might be of some use to you...

http://www.swissql.com/products/oracle-to-sqlserver/index.html

Bill|||Actually the overhead associated with cursors also exists in Horacle. It's just the latter is usually run on monsterous hardware that can handle sloppy coding and poor design. SQL Server is running in prod environment on machines that are several times (sometimes a dozen or more) cheaper, and every intelligent attempt to optimize a process brings a reward in improved performance.|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all...TSQL programmers would also rewrite it into 1 update and convert the Horacle style into ANSI ;)

I just tried to retain the structure as it was presented in the post, that also included the use of cursor.|||Thx guys for the tips,

rdjabarov, why "Horacle"?

thompbil, I have already used the link that u kindly pointed out. Didn't find the results satisfactory. Thx all the same. Another thing, besides the marginal loss in performance by using Cursors, what other overheads can I expect? Moreover, what cud be a substitute for cursors, if the overheads are significant?

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)|||Thx guys for the tips,

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)

They are just different. SQL Server does some things better than ORACLE. ORACLE does some things better than SQL Server.
You could say that the "UPDATE...FROM..." construct (as in my original reply) is even neater than the PL/SQL cursor update example you originally cited. I think so...but that's just an opinion.|||Man, just wait till Yukon comes out, - talking about Horacle...|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?Yukon is the project name for the next version of SQL Server (either 9.0 or SQL 2005, depending on your point of view).

Horacle is an often used rdjabarovism for Oracle.

-PatP

!

Dear all,

I was given a project to transfer our database into sql server database.

In our previous database we used the datatype int4 for some columns to create some views and in some queries that we used to build our datawindows. In SQLServer 2000 i created a user defined function named int4. I can execute it with the line select dbo.int4(poso) from employee .

Unfortrunately this way make me to rebuild all my datawindows and replace int4( with dbo.int4( . Is there any way to execute queries using user defined function but omitting the first part name dbo. I mean to manage execute the command select int4(poso) from employee \\let int4 be a user definded function.

If i create an ODBC function i must use select fn int4(poso) from employee

I thing i could solve this if i could create a system function int4() but i have no idea how it can be done.

If i can’t solve this, i thing it will decided than is impossible to move to sqlserver Database. Has anyone any suggestions?

Thanks in advance,

Best regards,

In SQL Server, you are not allowed to create a system functions. There is no other choice. You have to use the dbo.int4().

But I am not sure what you are doing inside this function. How important it is. If it is permanent for all the query call, better update the values on the main table itself (using update statement) & take care while inserting these values.

|||

No, you will have to use the owner /schema prefix for the new defined function.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hellen, please do not multipost.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Firstly many thanks for your reply!!!

As i said, i want to create this function so as not to change at all the code of our programs. Inside the code we use select statements and we use in a lot of points int4(). It is necessary as i have functions that i use to my database and i want to have the optional to use sqlserver database without changing at all my code.

As you said it is not possible in Sql Server as i am not allowed to create system functions. Is it possible inSql Server 2005 or 2008 to create system functions?

Any ideas of how i could create an odbc function but include the function in a select statement without the fn prefix? Or should i abandon the idea of moving to a strong database?

|||

No, this is not possible as far as I know. You should consider trying to change the code logic to support this (strong9 syntax :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de