I'm using sql server 7.0 - on "SQL Server Enterprise Manager" screen,
when I right click a table and select "Design Table" on the menu list,
I get error message box "Unable to load MDT2G.DLL".
I never had this before and don't know what happend.
What caused this and how to fix it ?
THANKS !
>
> I'm using sql server 7.0 - on "SQL Server Enterprise Manager"
> screen, when I right click a table and select "Design Table" on
> the menu list, I get error message box "Unable to load MDT2G.DLL".
> I never had this before and don't know what happened.
> What caused this and how to fix it ?
> THANKS !
>
The mdt2g.dll file could be corrupt. Try copying over it from your SQL CD
and see if it works for you.
Regards,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Monday, March 19, 2012
"unable to load MDT2G.DLL" (sql 7.0)?
I'm using sql server 7.0 - on "SQL Server Enterprise Manager" screen,
when I right click a table and select "Design Table" on the menu list,
I get error message box "Unable to load MDT2G.DLL".
I never had this before and don't know what happend.
What caused this and how to fix it ?
THANKS !>
> I'm using sql server 7.0 - on "SQL Server Enterprise Manager"
> screen, when I right click a table and select "Design Table" on
> the menu list, I get error message box "Unable to load MDT2G.DLL".
> I never had this before and don't know what happened.
> What caused this and how to fix it ?
> THANKS !
>
--
The mdt2g.dll file could be corrupt. Try copying over it from your SQL CD
and see if it works for you.
Regards,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
when I right click a table and select "Design Table" on the menu list,
I get error message box "Unable to load MDT2G.DLL".
I never had this before and don't know what happend.
What caused this and how to fix it ?
THANKS !>
> I'm using sql server 7.0 - on "SQL Server Enterprise Manager"
> screen, when I right click a table and select "Design Table" on
> the menu list, I get error message box "Unable to load MDT2G.DLL".
> I never had this before and don't know what happened.
> What caused this and how to fix it ?
> THANKS !
>
--
The mdt2g.dll file could be corrupt. Try copying over it from your SQL CD
and see if it works for you.
Regards,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Sunday, March 11, 2012
"select" statement in a function
CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN
End
ELSE
if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName
and strMiddleName=@.MiddleName and strLastName=@.LastName
and strNameSuffix=@.NameSuffix and lngSSN=@.SSN
End
ELSE
if @.LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@.LastName
and lngSSN = @.SSN
END
END
return (@.personID)
END
I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)
Cann I use "select" statement in the function? If not what is the alternative?
I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.
ThanksWhy isn't this a stored procedure?|||Brett
It can be, but I guess you answered many of my questions
I still have this question left in me.
Can a stored procedure return a value??
As far as my knowledge goes it returns with a
"select" statement. Is it true?? Is there any way to check the returned value.
I am very much confused with this concept.
Can I make all functions in a sybase database as stored procedures in SQL??
If it doesnot return then I have to make it a function for sure.
Can you please answer these questions?
Thanks|||USE Northwind
GO
CREATE PROC MySproc99 @.HoursToMargarittaville Decimal(15,4) OUTPUT
AS
BEGIN
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 22 2004 17:00:00'
SELECT @.HoursToMargarittaville = DATEDIFF(mi, GetDate(), @.Weekend)/60.00
Return 0
END
GO
DECLARE @.x decimal(15,4), @.rc int
EXEC @.rc = MySproc99 @.x OUTPUT
SELECT @.x, @.rc
GO
DROP PROC mySproc99
GO|||You can use a select function for assignment, but you cannot return the results of a select statement.
Here is your function rewritten. I also cleaned up some of the unnecessary BEGIN/END pairs and used a COALESCE function rather than mutiple IS NULL checks.
CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF COALESCE(@.LastName, @.FirstName, @.MiddleName, @.NamePrefix, @.NameSuffix) is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END
Be aware that I think your logic will fail when comparing NULL values, because NULL values cannot be compared to eachother. For example:
-----------
declare @.NULL1 varchar
declare @.NULL2 varchar
if @.NULL1 = @.NULL2 print 'Success' else print 'Failure'
-----------|||Oops. Looked at your code again and I see that you don't allow nulls for the comparison, so COALESCE is not appropriate. Use this instead:
CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END|||Yo, blind dude...
You don't think s/he would be better of with a sproc?
especially in light of a (sounds like massive) sybase to sql server conversion?|||Honestly, (MOO of course) I would use a function here, because I imagine this logic could be called by several different procedures. I've never liked using stored procedures that return scalar OUTPUT. I think the implementation is clunky, so since UDFs were introduced they have been my preferred method.|||MOO indeed...
Want to show me what the udf call would look like?|||see if you can not re-code your function to something like
Create function name (@.param...)
Returns Table
AS
Return Seturn Select ... Your SQL Goes here...
See also BOL, In-Line Functions
The only limitation is that you can have a single select sql phrase inside your function.|||I would call the UDF like this: MOO! MOOOOO! MOO MOO MOO MOO MOO!
Whether it would come when I call, I don't know...
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN
End
ELSE
if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName
and strMiddleName=@.MiddleName and strLastName=@.LastName
and strNameSuffix=@.NameSuffix and lngSSN=@.SSN
End
ELSE
if @.LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@.LastName
and lngSSN = @.SSN
END
END
return (@.personID)
END
I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)
Cann I use "select" statement in the function? If not what is the alternative?
I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.
ThanksWhy isn't this a stored procedure?|||Brett
It can be, but I guess you answered many of my questions
I still have this question left in me.
Can a stored procedure return a value??
As far as my knowledge goes it returns with a
"select" statement. Is it true?? Is there any way to check the returned value.
I am very much confused with this concept.
Can I make all functions in a sybase database as stored procedures in SQL??
If it doesnot return then I have to make it a function for sure.
Can you please answer these questions?
Thanks|||USE Northwind
GO
CREATE PROC MySproc99 @.HoursToMargarittaville Decimal(15,4) OUTPUT
AS
BEGIN
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 22 2004 17:00:00'
SELECT @.HoursToMargarittaville = DATEDIFF(mi, GetDate(), @.Weekend)/60.00
Return 0
END
GO
DECLARE @.x decimal(15,4), @.rc int
EXEC @.rc = MySproc99 @.x OUTPUT
SELECT @.x, @.rc
GO
DROP PROC mySproc99
GO|||You can use a select function for assignment, but you cannot return the results of a select statement.
Here is your function rewritten. I also cleaned up some of the unnecessary BEGIN/END pairs and used a COALESCE function rather than mutiple IS NULL checks.
CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF COALESCE(@.LastName, @.FirstName, @.MiddleName, @.NamePrefix, @.NameSuffix) is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END
Be aware that I think your logic will fail when comparing NULL values, because NULL values cannot be compared to eachother. For example:
-----------
declare @.NULL1 varchar
declare @.NULL2 varchar
if @.NULL1 = @.NULL2 print 'Success' else print 'Failure'
-----------|||Oops. Looked at your code again and I see that you don't allow nulls for the comparison, so COALESCE is not appropriate. Use this instead:
CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END|||Yo, blind dude...
You don't think s/he would be better of with a sproc?
especially in light of a (sounds like massive) sybase to sql server conversion?|||Honestly, (MOO of course) I would use a function here, because I imagine this logic could be called by several different procedures. I've never liked using stored procedures that return scalar OUTPUT. I think the implementation is clunky, so since UDFs were introduced they have been my preferred method.|||MOO indeed...
Want to show me what the udf call would look like?|||see if you can not re-code your function to something like
Create function name (@.param...)
Returns Table
AS
Return Seturn Select ... Your SQL Goes here...
See also BOL, In-Line Functions
The only limitation is that you can have a single select sql phrase inside your function.|||I would call the UDF like this: MOO! MOOOOO! MOO MOO MOO MOO MOO!
Whether it would come when I call, I don't know...
"select" only if theres a same record in the table
i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.
Substitute A_PK for whatever A's PK is and try the following:SELECT *
FROM A
WHERE EXISTS
(SELECT B.A_PK
FROM B
WHERE B.A_PK = A.A_PK
GROUP BY B.A_PK
HAVING COUNT(*) >= 2)
"select TOP" with parameter...
Hi there.
Does anyone know of a way to get the following statement to work without
using dynamic SQL?.....
I'm trying to get a "select TOP" to work while passing in a parameter as 'N'
for 'select the first N rows...'
declare @.NumberofRecords integer
select @.NumberofRecords = 5
select top @.NumberofRecords
from MyTable
Any info would be appreciated!len
Lookup SET ROWCOUNT in the BOL
"len" <len@.discussions.microsoft.com> wrote in message
news:3643D907-3483-4B3D-9F9A-B7D45EABF6AB@.microsoft.com...
> Hi there.
> Does anyone know of a way to get the following statement to work without
> using dynamic SQL?.....
> I'm trying to get a "select TOP" to work while passing in a parameter as
> 'N'
> for 'select the first N rows...'
> declare @.NumberofRecords integer
> select @.NumberofRecords = 5
> select top @.NumberofRecords
> from MyTable
> Any info would be appreciated!|||You are not selecting anything so maybe this will work
select top @.NumberofRecords myField1, myField2, etc.
from MyTable
if that still fails try
EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
HTH,
Gerard|||Len,
you can't use a parameter with TOP in SQL 2000. In 2005 it is possible
see this article for more details:
http://support.microsoft.com/defaul...kb;en-us;891605
In 2000 and 7.0 you can use the SET ROWCOUNT option to achieve what you
want.
Try something like
Declare @.num int
SET @.num = 8
SET ROWCOUNT @.num
select * from myTable
Order by columnname
Markus|||I just did a quick check in Qry Analyzer and
EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
works, provided offcourse that @.NumberofRecords is a string
Gerard|||"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1130325402.186088.177800@.g14g2000cwa.googlegroups.com...
>I just did a quick check in Qry Analyzer and
> EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
> works, provided offcourse that @.NumberofRecords is a string
It does work, but that's not a parameterised procedure, it's dynamic SQL.
Plenty of reasons to try and avoid using that if possible.
Dan|||> select top @.NumberofRecords myField1, myField2, etc.
> from MyTable
This will fail because TOP does not take parameters in SQL Server 2000!
> EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
This will fail because (a) @.NumberOfRecords can't implicitly be concatenated
into a string and (b) you didn't leave a space after top, so it will come
out with a syntax error like this:
select top5 myColumn, myColumn2, ...
Please see http://www.sommarskog.se/dynamic_sql.html to understand why
dynamic SQL is not always the best knee-jerk reaction to a problem...|||Yes yes yes,
I know about the limitations/dangers/restrictions with/of dynamic sql,
I was a bit quick of the gun maybe.
And thanks for checking on the punctuation, e.g. the space after the
top.
On your point b)
> works, provided offcourse that @.NumberofRecords is a string
which would, I forgot to mention, require an additional variable and a
CONVERT if the initial variable is an int.
Does anyone know of a way to get the following statement to work without
using dynamic SQL?.....
I'm trying to get a "select TOP" to work while passing in a parameter as 'N'
for 'select the first N rows...'
declare @.NumberofRecords integer
select @.NumberofRecords = 5
select top @.NumberofRecords
from MyTable
Any info would be appreciated!len
Lookup SET ROWCOUNT in the BOL
"len" <len@.discussions.microsoft.com> wrote in message
news:3643D907-3483-4B3D-9F9A-B7D45EABF6AB@.microsoft.com...
> Hi there.
> Does anyone know of a way to get the following statement to work without
> using dynamic SQL?.....
> I'm trying to get a "select TOP" to work while passing in a parameter as
> 'N'
> for 'select the first N rows...'
> declare @.NumberofRecords integer
> select @.NumberofRecords = 5
> select top @.NumberofRecords
> from MyTable
> Any info would be appreciated!|||You are not selecting anything so maybe this will work
select top @.NumberofRecords myField1, myField2, etc.
from MyTable
if that still fails try
EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
HTH,
Gerard|||Len,
you can't use a parameter with TOP in SQL 2000. In 2005 it is possible
see this article for more details:
http://support.microsoft.com/defaul...kb;en-us;891605
In 2000 and 7.0 you can use the SET ROWCOUNT option to achieve what you
want.
Try something like
Declare @.num int
SET @.num = 8
SET ROWCOUNT @.num
select * from myTable
Order by columnname
Markus|||I just did a quick check in Qry Analyzer and
EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
works, provided offcourse that @.NumberofRecords is a string
Gerard|||"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1130325402.186088.177800@.g14g2000cwa.googlegroups.com...
>I just did a quick check in Qry Analyzer and
> EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
> works, provided offcourse that @.NumberofRecords is a string
It does work, but that's not a parameterised procedure, it's dynamic SQL.
Plenty of reasons to try and avoid using that if possible.
Dan|||> select top @.NumberofRecords myField1, myField2, etc.
> from MyTable
This will fail because TOP does not take parameters in SQL Server 2000!
> EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
This will fail because (a) @.NumberOfRecords can't implicitly be concatenated
into a string and (b) you didn't leave a space after top, so it will come
out with a syntax error like this:
select top5 myColumn, myColumn2, ...
Please see http://www.sommarskog.se/dynamic_sql.html to understand why
dynamic SQL is not always the best knee-jerk reaction to a problem...|||Yes yes yes,
I know about the limitations/dangers/restrictions with/of dynamic sql,
I was a bit quick of the gun maybe.
And thanks for checking on the punctuation, e.g. the space after the
top.
On your point b)
> works, provided offcourse that @.NumberofRecords is a string
which would, I forgot to mention, require an additional variable and a
CONVERT if the initial variable is an int.
"Select TOP " question
I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Thursday, March 8, 2012
"Select TOP " question
I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
"select rows @start through @end" how to do it?
Thought I'd post the whole problem I'm trying to solve -- seems like it's
common enough that there must be a "normal" way to do this.
I want to say "select the top 2000 rows, starting at row 15000, from myTable
order by foo". I know I could use SELECT TOP 17000... to limit the number of
rows returned, but I'm still pulling a ton more data than I need.
So I thought I'd do it with a UDF, but I thought I'd see if there was a good
one already written for this.
Thanks!Jesse wrote:
> Thought I'd post the whole problem I'm trying to solve -- seems like
> it's common enough that there must be a "normal" way to do this.
> I want to say "select the top 2000 rows, starting at row 15000, from
> myTable order by foo". I know I could use SELECT TOP 17000... to
> limit the number of rows returned, but I'm still pulling a ton more
> data than I need.
> So I thought I'd do it with a UDF, but I thought I'd see if there was
> a good one already written for this.
> Thanks!
Select Top 2000
From dbo.MyTable
Where MyCol >= 15000
Order By Foo
Unless you have some column in the table that is numbered, there is no such
thing as "row 15000". Rows are not positional. In SQL 2005, you can assign
row numbers to the columns in the query using hte ROW_NUMBER() function.
David Gugick
Quest Software|||Thanks, David --
You're right, if the query isn't sorted. But if you specify an ORDER BY
clause, then there is a definite Nth row. (If the data changes, the Nth row
may change as well, but for my purposes, that's OK.)
In your example, if you can stop at the 2,000th row returned from an ordered
query (SELECT TOP 2000), it follows that you could skip over the first N of
them.
Jesse
> Select Top 2000
> From dbo.MyTable
> Where MyCol >= 15000
> Order By Foo
> Unless you have some column in the table that is numbered, there is no
> such thing as "row 15000". Rows are not positional. In SQL 2005, you can
> assign row numbers to the columns in the query using hte ROW_NUMBER()
> function.
>
> --
> David Gugick
> Quest Software
>
common enough that there must be a "normal" way to do this.
I want to say "select the top 2000 rows, starting at row 15000, from myTable
order by foo". I know I could use SELECT TOP 17000... to limit the number of
rows returned, but I'm still pulling a ton more data than I need.
So I thought I'd do it with a UDF, but I thought I'd see if there was a good
one already written for this.
Thanks!Jesse wrote:
> Thought I'd post the whole problem I'm trying to solve -- seems like
> it's common enough that there must be a "normal" way to do this.
> I want to say "select the top 2000 rows, starting at row 15000, from
> myTable order by foo". I know I could use SELECT TOP 17000... to
> limit the number of rows returned, but I'm still pulling a ton more
> data than I need.
> So I thought I'd do it with a UDF, but I thought I'd see if there was
> a good one already written for this.
> Thanks!
Select Top 2000
From dbo.MyTable
Where MyCol >= 15000
Order By Foo
Unless you have some column in the table that is numbered, there is no such
thing as "row 15000". Rows are not positional. In SQL 2005, you can assign
row numbers to the columns in the query using hte ROW_NUMBER() function.
David Gugick
Quest Software|||Thanks, David --
You're right, if the query isn't sorted. But if you specify an ORDER BY
clause, then there is a definite Nth row. (If the data changes, the Nth row
may change as well, but for my purposes, that's OK.)
In your example, if you can stop at the 2,000th row returned from an ordered
query (SELECT TOP 2000), it follows that you could skip over the first N of
them.
Jesse
> Select Top 2000
> From dbo.MyTable
> Where MyCol >= 15000
> Order By Foo
> Unless you have some column in the table that is numbered, there is no
> such thing as "row 15000". Rows are not positional. In SQL 2005, you can
> assign row numbers to the columns in the query using hte ROW_NUMBER()
> function.
>
> --
> David Gugick
> Quest Software
>
"select into" vs "insert into" question
Hello,
I have read in a number of forums that when using temporary tables, it is best to create the tables first before inserting the records rather than simply executing a "select into" to ensure that locking issues are alleviated.
I have to say that I am one of those developers who has relied on using the "select into" statement - the main reason being that I have almost always found the "select into" statement to be much faster than an "insert into" statement.
Obviously, the number of users running queries simultaneously when scripts that use "select into" statements are executed is a big deciding factor on whether "select into" or "insert into" should be used.
I'd appreciate it if someone can shed more light on other factors that should be taken into consideration when deciding to use "select into" vs "insert into".
Thanks in advance for your time and reply.It used to be that SELECT INTO was a no-no because it was an unlogged transaction, after which database recoverability was questionable. With SQL Server 2000 I do not believe this is still an issue, although I have never found documentation clarifying the matter.
INSERT INTO is slower partly because it verifies unique keys and other data restraints as the data is entered. SELECT INTO does not do this, but if you are selecting from a table that already has these restrictions inforced then it is not an issue.
Also, in many cases a table variable is the fastest method to use. Check into them and see if they might be more appropriate for your application.
I have read in a number of forums that when using temporary tables, it is best to create the tables first before inserting the records rather than simply executing a "select into" to ensure that locking issues are alleviated.
I have to say that I am one of those developers who has relied on using the "select into" statement - the main reason being that I have almost always found the "select into" statement to be much faster than an "insert into" statement.
Obviously, the number of users running queries simultaneously when scripts that use "select into" statements are executed is a big deciding factor on whether "select into" or "insert into" should be used.
I'd appreciate it if someone can shed more light on other factors that should be taken into consideration when deciding to use "select into" vs "insert into".
Thanks in advance for your time and reply.It used to be that SELECT INTO was a no-no because it was an unlogged transaction, after which database recoverability was questionable. With SQL Server 2000 I do not believe this is still an issue, although I have never found documentation clarifying the matter.
INSERT INTO is slower partly because it verifies unique keys and other data restraints as the data is entered. SELECT INTO does not do this, but if you are selecting from a table that already has these restrictions inforced then it is not an issue.
Also, in many cases a table variable is the fastest method to use. Check into them and see if they might be more appropriate for your application.
"SELECT DISTINCT and ORDER BY"-problem
Hi!
I can't seem to get my query to work. It returns (translated from Swedish):
Microsoft JET Database Engine (0x80004005)
The ORDER BY-instruction (myDate)..."doesn't work with"...DISTINCT.
This is my query (somewhat simplified):
set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT DISTINCT DATEPART(year, myDate) AS 'myColumn_alias' FROM myDB"
sql=sql & " WHERE myName='" & myName & "'"
sql=sql & " ORDER BY myColumn_alias"
rs.Open sql,connIs it because I have a WHERE-clause? If so, how should I design my query (I need only the years to populate a "select from" dropdownbox)? Or is it something else?
ThanksI don't think you can refer to a column alias the samw way you refer to a column.
Try:
ORDER BY DATEPART(year, myDate)|||I've tried that, but then I get the message that too few parameters are given and that 1 is expected.
:(|||All I had to do was to use YEAR(myDate) instead of DATEPART(year, myDate). Now it works!
"Select *" in Replication Tables
Hi,
I used "select * from replicate_table_name" in my stored procedures and my
application.Now i want to replicate that tables.
Will it affect my application and Replication.
Please Advice me.
Regds
Soura
It has zero effect on replication, since replication is not going to look at
your code.
It will have no effect on your application as long as you are using
transactional replication. If you are using any other method of
replication, an additional column will be added to your tables which can
then be picked up by that select * and returned to your application which
might not be expecting it, thereby causing it to blow up.
It is VERY STRONGLY recommended that you do NOT use select * in any code
that you ever write in an application. It is bad programming practice and
can lead to some very nasty errors after your application is deployed.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:56C4D4EC-2E19-4648-81E0-621E8EF821E7@.microsoft.com...
> Hi,
> I used "select * from replicate_table_name" in my stored procedures and my
> application.Now i want to replicate that tables.
> Will it affect my application and Replication.
>
> Please Advice me.
> Regds
> Soura
>
>
>
|||It depends
. The table schema is modified if you have updatable
subscribers or merge replication - an extra column is added. However, in the
case of merge if you already have a guid column with the rowguid attribute
there won't be another column added.
So, in those cases which cause a schema modification, will the extra column
cause an issue? This depends on your code. In TSQL, if you use:
Insert into x
select * from Y
and Y has an extra column added, it'll fail.
If your code simply does
select * from Y
then it depends on how the recordset/dataset is used in the client app.
There may be some code that iterates through the columns collection which'll
fail if it encounters a new column. Actually this is unlikely as most
columns are referenced directly by name, and the new column will simply
exist but never be referred to.
Anyway - hopefully this gives you some ideas of what to look for to see if
these things will be an issue in your code...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I used "select * from replicate_table_name" in my stored procedures and my
application.Now i want to replicate that tables.
Will it affect my application and Replication.
Please Advice me.
Regds
Soura
It has zero effect on replication, since replication is not going to look at
your code.
It will have no effect on your application as long as you are using
transactional replication. If you are using any other method of
replication, an additional column will be added to your tables which can
then be picked up by that select * and returned to your application which
might not be expecting it, thereby causing it to blow up.
It is VERY STRONGLY recommended that you do NOT use select * in any code
that you ever write in an application. It is bad programming practice and
can lead to some very nasty errors after your application is deployed.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:56C4D4EC-2E19-4648-81E0-621E8EF821E7@.microsoft.com...
> Hi,
> I used "select * from replicate_table_name" in my stored procedures and my
> application.Now i want to replicate that tables.
> Will it affect my application and Replication.
>
> Please Advice me.
> Regds
> Soura
>
>
>
|||It depends
. The table schema is modified if you have updatablesubscribers or merge replication - an extra column is added. However, in the
case of merge if you already have a guid column with the rowguid attribute
there won't be another column added.
So, in those cases which cause a schema modification, will the extra column
cause an issue? This depends on your code. In TSQL, if you use:
Insert into x
select * from Y
and Y has an extra column added, it'll fail.
If your code simply does
select * from Y
then it depends on how the recordset/dataset is used in the client app.
There may be some code that iterates through the columns collection which'll
fail if it encounters a new column. Actually this is unlikely as most
columns are referenced directly by name, and the new column will simply
exist but never be referred to.
Anyway - hopefully this gives you some ideas of what to look for to see if
these things will be an issue in your code...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
"Resource is low, some results are dropped"
I've been getting this message in a dialog box intermittently. Most recently
when trying to execute 'select @.@.trancount' after a statement attempted to
insert 100 rows into a table with only 10 rather small, varchar(256) columns
Everything that turned up on a Google search basically said throw more
hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
1039304, available memory = 333208(K) and System Cache = 507972 along with
2% CPU so I don't believe that it's a memory issue or more hardware will
solve the underlying issue.
Does anyone have an answer for this other than throw more hardware at it?
Where do you see this error? Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256) columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
> 1039304, available memory = 333208(K) and System Cache = 507972 along with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Hi,
Looks like you are executing the query in GRID result pane in query
analyzer. Could you change the mode to Text and try.
How to change:-
1. In query analyzer
2. Go to Query menu
3. Select "Result in Text"
4. Execute the query.
Thanks
Hari
SQL Server MVP
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most
> recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256)
> columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a
> 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K)
> of
> 1039304, available memory = 333208(K) and System Cache = 507972 along
> with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Or use the shortcuts CTRL + T for text mode, CTRL + D for grid
http://sqlservercode.blogspot.com/
"Hari Prasad" wrote:
> Hi,
> Looks like you are executing the query in GRID result pane in query
> analyzer. Could you change the mode to Text and try.
>
> How to change:-
> 1. In query analyzer
> 2. Go to Query menu
> 3. Select "Result in Text"
> 4. Execute the query.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
>
>
|||re: Resource is low, some results are dropped
There are a few different reasons this could happen.
1) Not patched up! Make sure you have the latest patches from Microsoft.
Service Packs
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Security Patches
You can check Technet for the latest patches...
http://www.microsoft.com/technet/security/current.aspx
2) How, and how many, results are returned.
Query Analyzer returns results in one of two ways when you execute SQL Statements (Text or Grid) It can execute and return the most complicated queries from huge databases with large result sets.
However, it has a problem returning multiple results to grid. Each grid requires a certain amount of resources, and if you execute a large number of queries, it eventually will run out memory and drop some results.
I was looping throogh sysobjects and syscolumns, executing a select statement on every column for every table. (Code GEnerator) It was trying to open a grid for every column in my database. Not that Pubs or Northwind would cause it, but my production da
tabase had a lot more objects. My guess is that this requires too many resources to complete.
If I changed the output for executing the queries to text mode, or I printed the information to the screen instead of executing t-SQL I did not get the error.
Try running it in text mode. Menu Bar - Query - Results in grid or Results in Text.
The keyboard shortcuts are Ctrl+D for Grid and Ctrl+T for Text.
Mike Pittser
Database Architect
sql2k5dba@.yahoo.com
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
when trying to execute 'select @.@.trancount' after a statement attempted to
insert 100 rows into a table with only 10 rather small, varchar(256) columns
Everything that turned up on a Google search basically said throw more
hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
1039304, available memory = 333208(K) and System Cache = 507972 along with
2% CPU so I don't believe that it's a memory issue or more hardware will
solve the underlying issue.
Does anyone have an answer for this other than throw more hardware at it?
Where do you see this error? Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256) columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
> 1039304, available memory = 333208(K) and System Cache = 507972 along with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Hi,
Looks like you are executing the query in GRID result pane in query
analyzer. Could you change the mode to Text and try.
How to change:-
1. In query analyzer
2. Go to Query menu
3. Select "Result in Text"
4. Execute the query.
Thanks
Hari
SQL Server MVP
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most
> recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256)
> columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a
> 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K)
> of
> 1039304, available memory = 333208(K) and System Cache = 507972 along
> with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Or use the shortcuts CTRL + T for text mode, CTRL + D for grid
http://sqlservercode.blogspot.com/
"Hari Prasad" wrote:
> Hi,
> Looks like you are executing the query in GRID result pane in query
> analyzer. Could you change the mode to Text and try.
>
> How to change:-
> 1. In query analyzer
> 2. Go to Query menu
> 3. Select "Result in Text"
> 4. Execute the query.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
>
>
|||re: Resource is low, some results are dropped
There are a few different reasons this could happen.
1) Not patched up! Make sure you have the latest patches from Microsoft.
Service Packs
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Security Patches
You can check Technet for the latest patches...
http://www.microsoft.com/technet/security/current.aspx
2) How, and how many, results are returned.
Query Analyzer returns results in one of two ways when you execute SQL Statements (Text or Grid) It can execute and return the most complicated queries from huge databases with large result sets.
However, it has a problem returning multiple results to grid. Each grid requires a certain amount of resources, and if you execute a large number of queries, it eventually will run out memory and drop some results.
I was looping throogh sysobjects and syscolumns, executing a select statement on every column for every table. (Code GEnerator) It was trying to open a grid for every column in my database. Not that Pubs or Northwind would cause it, but my production da
tabase had a lot more objects. My guess is that this requires too many resources to complete.
If I changed the output for executing the queries to text mode, or I printed the information to the screen instead of executing t-SQL I did not get the error.
Try running it in text mode. Menu Bar - Query - Results in grid or Results in Text.
The keyboard shortcuts are Ctrl+D for Grid and Ctrl+T for Text.
Mike Pittser
Database Architect
sql2k5dba@.yahoo.com
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Tuesday, March 6, 2012
"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?
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?
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
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
"Open table" doesn't show all records (sql server 2005).
Using sql managment studio in Sql Server 2005, I right-click on a (large)
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.
Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).
|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
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
|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set of
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegrou ps.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> 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
> --
>
|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.
|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the size
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same sql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
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
|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegr oups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.
Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).
|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
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
|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set of
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegrou ps.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> 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
> --
>
|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.
|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the size
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same sql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
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
|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegr oups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>
"Open table" doesn't show all records (sql server 2005).
Using sql managment studio in Sql Server 2005, I right-click on a (large)
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
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
--|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set o
f
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegroups.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> 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
> --
>|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the si
ze
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same s
ql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
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
--|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegroups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
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
--|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set o
f
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegroups.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> 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
> --
>|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the si
ze
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same s
ql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
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
--|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegroups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>
"Open table" doesn't show all records (sql server 2005).
Using sql managment studio in Sql Server 2005, I right-click on a (large)
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
--
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
--|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set of
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegroups.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
>> Using sql managment studio in Sql Server 2005, I right-click on a (large)
>> table and select "open table," and then press the "move last" (go to last
>> record) button. After a minute or two, it claims all records (455,903 of
>> 455,903) have been accessed (what it shows on the status bar, also the
>> "pause" button becomes greyed out), but in fact it has stopped at record
>> 446,739, and browsing doesn't show the missing last records.
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> 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
> --
>|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the size
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same sql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
--
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
--|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegroups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>
table and select "open table," and then press the "move last" (go to last
record) button. After a minute or two, it claims all records (455,903 of
455,903) have been accessed (what it shows on the status bar, also the
"pause" button becomes greyed out), but in fact it has stopped at record
446,739, and browsing doesn't show the missing last records.
Running a query in Management Studio on that same table shows that all the
records really are there. Opening the same table from Access as a linked
table also shows all the records are there (Access only takes a few seconds
to execute a move to last record command on the table and go to record
455,903).
Is this a bug? It's hard to see how it's by design, at least the false
indication that I'm at the last (455,903th) record seems to be a bug.
Thanks.Okay, I see that what "open table" has done is actually put the last set of
records somewhere in the middle of the table, in an order different from
SELECT * (and access). Setting a clustered index solved that problem (but
not the speed issue...).|||On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Using sql managment studio in Sql Server 2005, I right-click on a (large)
> table and select "open table," and then press the "move last" (go to last
> record) button. After a minute or two, it claims all records (455,903 of
> 455,903) have been accessed (what it shows on the status bar, also the
> "pause" button becomes greyed out), but in fact it has stopped at record
> 446,739, and browsing doesn't show the missing last records.
>
What exactly do you mean by saying "it has stopped at record 446,739"?
There is no concept of a first or last record because tables are
unordered sets by definition. There is no such thing as a record
number.
Returning 400K rows for display in a single query is a rather absurd
thing to do. It's certainly the case that the Open Table feature
leaves a lot to be desired in many respects. On the other hand it
isn't so surprising that it struggles with such an unreasonable
request. Why would you really want to do that?
I suggest you write queries to extract the data that's of interest to
you. Otherwise you might as well be using a spreadsheet instead of a
database.
--
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
--|||On 4 Mar, 10:33, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Okay, I see that what "open table" has done is actually put the last set of
> records somewhere in the middle of the table, in an order different from
> SELECT * (and access). Setting a clustered index solved that problem (but
> not the speed issue...).
No. A table is unordered by definition. This has nothing to do with
clustered indexes and unless you specify ORDER BY in a query the
resulting order is undefined - i.e. the ordering could even be
different each time you run the query. You simply cannot use a SQL
database as if it were a spreadsheet.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. I meant the indexed primary key field, which is actually a record
count field in my table. However, as I said in the revised post, the
records were there after all (the upsizing wizard had not created a
clustered index as I expected).
Of course, I follow the standard practice of using queries to limit the size
of result sets. But it's by no means absurd to occasionally need to browse
an entire table, even a large one--as you can guess from the numerous
complaints when MS briefly eliminated "open table" from studio during the
beta. At any rate, given how fast the linked table feature to that same sql
server 2005 table works from Access, I would think some speed improvement
can be made here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173004572.639515.95050@.64g2000cwx.googlegroups.com...
> On 4 Mar, 09:13, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
>> Using sql managment studio in Sql Server 2005, I right-click on a (large)
>> table and select "open table," and then press the "move last" (go to last
>> record) button. After a minute or two, it claims all records (455,903 of
>> 455,903) have been accessed (what it shows on the status bar, also the
>> "pause" button becomes greyed out), but in fact it has stopped at record
>> 446,739, and browsing doesn't show the missing last records.
> What exactly do you mean by saying "it has stopped at record 446,739"?
> There is no concept of a first or last record because tables are
> unordered sets by definition. There is no such thing as a record
> number.
> Returning 400K rows for display in a single query is a rather absurd
> thing to do. It's certainly the case that the Open Table feature
> leaves a lot to be desired in many respects. On the other hand it
> isn't so surprising that it struggles with such an unreasonable
> request. Why would you really want to do that?
> I suggest you write queries to extract the data that's of interest to
> you. Otherwise you might as well be using a spreadsheet instead of a
> database.
> --
> 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
> --
>|||Of course it's undefined by definition and one can't rely on the order
without an order by etc. However, some dbs do present the clustered order
on a simple table open (or equivalent) operation, and I checked for a
difference only after the post. I may have but didn't mean to imply that a
clustered index would lead to rows being in any given order on retrieval
(absent an order by), only that it in this case it did in fact cause a
difference between the select all and the open table operation.|||On 4 Mar, 10:50, "Tech_vs_Life" <limi...@.newsgroups.nospam> wrote:
> Thanks. I meant the indexed primary key field, which is actually a record
> count field in my table. However, as I said in the revised post, the
> records were there after all (the upsizing wizard had not created a
> clustered index as I expected).
> Of course, I follow the standard practice of using queries to limit the size
> of result sets. But it's by no means absurd to occasionally need to browse
> an entire table, even a large one--as you can guess from the numerous
> complaints when MS briefly eliminated "open table" from studio during the
> beta. At any rate, given how fast the linked table feature to that same sql
> server 2005 table works from Access, I would think some speed improvement
> can be made here.
>
The key point is that "Open Table" is mainly a shortcut for "SELECT *
FROM tbl", which is not a smart thing to do in many environments.
Now you might have expected that MS would provide a more sophisticated
page-and-requery style of interface. Unfortunately, designing a single
generic UI to do that for every possible case isn't really feasible.
There are too many potential variables around indexing, requery,
locking and sorting strategies prefererred for different situations.
Even if it were achievable, some people who liked the "traditional"
open table feature would complain that a requery interface was not
what they wanted. So instead the lobby who favoured an Access legacy
UI got the one-size-fits-all solution they asked for. Unfortunately
that really means one-size-fits... very little.
For most practical purposes the solution for browsing 1000 rows will
likely be different to the solution required for browsing 1 billion
rows. That's why the only real answer is to build it yourself. The
compasison to Access ADP is irrelevant. It would be insane even to
attempt that in an enterprise-scale environment with thousands of
concurrent users.
HTH.
--
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
--|||Yes, one might well have expected an interface much better, and not much
worse, than a good but cheaper product. What you said makes a lot of sense,
yet I'm not persuaded it's necessarily decisive. First, a minor point, this
is not being compared with an access adp, but an access mdb linked to sql
server via the native client. It may or not be insane in an enterprise
environment with thousands of concurrent users for only *one* user, the
developer, to browse the entire table with a linked mdb. However, it
doesn't seem insane to allow it *as an option* ("open table via ODBC" or
"open table in workgroup mode"?), nor impossible to implement, since already
used in the Access linked table scheme (you could also have an option to
"open table after table lock" or "try to acquire table lock on open
table"--there are several different possibilities here). Instead of "build
it myself," I prefer to have MS build it, and I'll "check the option box
myself." And why does it have to be one size fits all, when practically
everything else in sql server changes as environment and parameters change.
It would be an extremely useful feature for those of us who need it. Not
everyone is running sql server on the high end; increasingly people are
migrating in from the low end, and this is a very useful feature that I know
works well with sql server data in the small workgroup environment, but it's
only available as an option in Access. Of course, I can always open Access
and link to the same data, to avoid the minutes of delay, but then I need to
have Access installed on the machine and I need to set up a database there
and link.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1173008179.376755.126190@.s48g2000cws.googlegroups.com...
> The key point is that "Open Table" is mainly a shortcut for "SELECT *
> FROM tbl", which is not a smart thing to do in many environments.
> Now you might have expected that MS would provide a more sophisticated
> page-and-requery style of interface. Unfortunately, designing a single
> generic UI to do that for every possible case isn't really feasible.
> There are too many potential variables around indexing, requery,
> locking and sorting strategies prefererred for different situations.
> Even if it were achievable, some people who liked the "traditional"
> open table feature would complain that a requery interface was not
> what they wanted. So instead the lobby who favoured an Access legacy
> UI got the one-size-fits-all solution they asked for. Unfortunately
> that really means one-size-fits... very little.
> For most practical purposes the solution for browsing 1000 rows will
> likely be different to the solution required for browsing 1 billion
> rows. That's why the only real answer is to build it yourself. The
> compasison to Access ADP is irrelevant. It would be insane even to
> attempt that in an enterprise-scale environment with thousands of
> concurrent users.
> HTH.
> --
> David Portas, SQL Server MVP
>
Subscribe to:
Posts (Atom)
