Invisible rows after failed syncronization

Publisher: SQL Server 2000 SP4
Subscriber: SQL Server 2005 Mobile Edition

Sometimes, after a failed merge replication(due to communication error) some rows on the subscriber became invisible for the publisher.

I've reproduced a case:

[START Short Version]
All rows inserted between a comm failed sync and a fine sync became invisible.
This seems to be caused because the subscriber keeps generating rows with the same generation number and the publisher doesn't look for this generation anymore.
[END Short Version]

[START Detailed case]
STEP 0: INITIAL STATE
Publisher
Orders Table
EMPTY

Subscriber
NO DATABASE

STEP 1: After FIRST SYNC and INSERTING 2 Orders

Publisher

Orders Table

EMPTY

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 81
000002 4 4 81

STEP 2: After SECOND SYNC

Publisher

Orders Table

OrderId

000001

000002

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

STEP 3: INSERT ANOTHER ORDER

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

000003 6 6 81

STEP 4: After THIRD SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table

OrderId

000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81
Note: Publisher has the row but subscriber keeps the state in 81.

STEP 5: INSERT ANOTHER ORDER (BEFORE A GOOD SYNC)
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81
Note: Orders 000003 and 000004 have the same system info

STEP 6: After FOURTH SYNC
Publisher
Orders Table

OrderId
000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81

Note: Even with a fine merge process Publisher didn't get the 000004 row and the Subscriber didn't update the __sysMC

STEP 7: INSERT ANOTHER ORDER

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 81

000004 6 6 81

000005 8 8 81

STEP 8: After FIFTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81

000004 6 6 81

000002 8 8 80

Note: Data from generation 8 merge correctly, but data from generation 6 keeps invisible to publisher.
STEP 9: DUMMY UPDATE ON GEN 6 ROWS
Action: I made a non relevant update to force the Subscriber update the generation.
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 10 81
000004 6 10 81
000005 8 8 80

Note: sysCG get its value updated correctly.

STEP 10: After SIXTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000004

000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 80

000004 6 6 80

000002 8 8 80

Note: Data merges correctly.
[END Detailed case]

I could code a system that checks all the publication tables and updates all the last generation rows in case of communication error but i would really like to avoid doing it.

I don't know if I'm missing something or if this is a bug or a known issue.

This is a big problem for me because communication errors on cellular phone based connection are quite common and users keep working even if there's an error.

Any comment will be appreciated.Finally, I've modified my replication process solve this manually.

On a normal case (no previous error):
1. I get the current generation (__sysMergeSubscriptions.LastUploadedGen+1)
2. Launch a normal sync.
3. If it fails I save the failed generation for using it on next sync.

On the previous error case:
1. Do a normal sync (to get a valid __sysMergeSubscriptions.LastUploadedGen)
2. Do a dummy update on all rows (from all published tables (from __sysMergeArticles)) that __sysIG or __sysCG equals the wrong generation number.
3. Do a normal sync to send all the invisible rows to the publisher.

I don't like my own solution, but that's the only way I have to keep the system working as I haven't find any reference to this problem anywhere.|||

Hey MeZKaL,

I just have few questions to narrow downt he problem point in the whole path of sync components.

In the same above scenario, can you please also let me know the contents of __sysMergeSubscription and
__sysMergeSubscriptionProperties table for that subscription in each of the above STEPs (esp. Steps 4 to 6)?

Basically, I just want to know which of the following is true (after a failed sync)

1) Client did not enumerate the changes and hence did not send the changes

2) Client has enumerated the changes, sent the changes but metadata does not say its new change

3) Client has enumerated, sent the changes, metadata also says its a new change, but publisher ignores it for some internal
reasons like publisher metadata says its a false change ...etc

Thanks,

Laxmi

|||I repeated the case saving a copy of the mobile db for each step.

There are no changes on __sysMergeSubscriptionProperties so I will put it just one time.

__sysMergeSubscriptionProperties
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
Distributor
DistributorAddress
DistributorNetwork: 0
DistributorSecurityMode: 1
DistributorLogin
DistributorPassword
ExchangeType: 3
InternetLogin: domain\user
InternetURL: URL
InternetPassword: *******
LoginTimeout: 15
ProfileName: DEFAULT
Publisher: SERVER\INSTANCE
PublisherAddress
PublisherNetwork: 0
PublisherDatabase: DBName
PublisherSecurityMode: 1
PublisherLogin: domain\user
PublisherPassword: *******
Publication: PubName
QueryTimeout: 300
Validate: 0
HostName
Subscriber: domain\user
InternetProxyServer
InternetProxyLogin
InternetProxyPassword
ConnectionRetryTimeout: 120
CompressionLevel: 1
ConnectionManager: 0
SnapshotTransferType: 99

Many of the field value from __sysMergeSubscriptions didn't change during the process.

__sysMergeSubscriptions
Publisher: server\instance
PublisherDatabase: DBName
Publication: PubName
PublicationId: 03e497f6-83b6-4de3-87e3-e97835880c94
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
SyncPartnerId: 751a4dfe-2eba-7a4a-b11a-35beec9a8bf3
SyncAnchorId: 00000000-0000-0000-0000-000000000000
SchemaVersion: 481
SchemaVersionId: 00000000-0000-0000-0000-000000000000
ReplNick (casted to int): -312916250
Retention: 0
MachineId: 374677b5-b002-d9f5-d4e4-4628538f2ccd
PathName: Path\DB.sdf
ReinitRequested: 0
ReplProp: NULL
HostName:
ValidationHostName: 0
ReplicaState: 00000000-0000-0000-0000-000000000000
LastCleanupDate: 15/11/2006 16:30
ReconcilerVersion: 8
Subscriber: domain\user

[START Detailed case]

STEP 1: After FIRST SYNC
Publisher
Orders Table
EMPTY

Subscriber
Orders Table
EMPTY

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 2: After INSERTING 2 Orders
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 3: After SECOND SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 4: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 5: After THIRD SYNC
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 6: DUMMY UPDATE
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 81
57001 4 6 81
57002 4 6 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 7: After FOURTH SYNC
Publisher
Orders Table
OrderId
57000
57001
57002

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 80
57001 4 6 80
57002 4 6 80

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5213 7 15/11/2006 17:35 72
SentGen80Id
SentGen80Id: 4171a971-b963-a541-300a-0b1602902e39

[__sysMergeSubscriptions DETAIL]
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
step1 4977 0 15/11/2006 16:30 70
step2 4977 0 15/11/2006 16:30 70
step3 4977 0 15/11/2006 16:30 70
step4 4977 0 15/11/2006 16:30 70
step5 5095 5 15/11/2006 17:22 71
step6 5095 5 15/11/2006 17:22 71
step7 5213 7 15/11/2006 17:35 72

SentGen80Id
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
e5ff3df6-195c-3145-273e-86784ace7e16
e5ff3df6-195c-3145-273e-86784ace7e16
4171a971-b963-a541-300a-0b1602902e39|||Can you open a case with CSS please for this? That way we can get a better handle on this in a timely manner.|||My workaround runs pretty well and I don't have time now for spending hours and hours crossing mails and test results with a support service.

If anyone can help with the previous data, it'll be welcomed but I won't spent more time testing and logging for this issue. I've lots of work and not many time.

Invert Rows and Columns in Tables

I have many fields but only a few rows to display. How do I invert the table with Report Services Designer so that Field (Columns) go down the page and the row items go across the page?

The best way to do this is to use Matrix instead of table for more help and examples check those links

http://msdn2.microsoft.com/en-us/library/ms251712(VS.80).aspx
http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx

Invert rows and columns (PIVOT)

Hi everybody.

I have this table that contains how many items were sold (and their value) into two departments:

SellerID(PK)	Year(PK)	ItemsSoldDPT1	ItemsSoldDPT2	ValueSoldDPT1	ValueSoldDPT2
1	2002	10	20	300.00	400.00
1	2003	13	71	450.00	320.00
1	2004	8	4	350.00	640.00
1	2005	2	15	110.00	680.00
2	2001	3	1	130.00	100.00
2	2005	1	7	190.00	200.00
2	2006	6	9	170.00	500.00
...	...	...	...	...	...

I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..

I want to obtain this:

SellerID	2001 Items	2001 Values	2002 Items	2002 Values	2003 Items	2003 Values	2004 Items	2004 Values	2005 Items	2005 Values	2006 Items	2006 Values	...
1	(NULL)	(NULL)	30	700.00	84	770.00	12	990.00	17	790.00	(NULL)	(NULL)	...
2	4	230.00	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	8	390.00	15	670.00	...
...	...	..	...	...	...	...	...	...	...	...	...	...	...

Any ideas? I think I should use the PIVOT keyword to write the sql but I can't figure how it works and how can I do that sums.

Please note: I don't know how many distinct values of "Year" exists and the min and max year can be specified by the user.

Thank you for your help.

It is best to use the standard SQL approach of using GROUP BY and CASE expressions. Pivot can only be used to pivot one set of values. You have to write more complex query if you want to just use PIVOT operator and performance will be bad for those approaches.

select t.SellerID

, sum(case t.Year when 2001 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2001 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2002 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2002_Values

, sum(case t.Year when 2002 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2002_Values

...

from tbl as t

group by t.SellerID

|||Thank you for your interest and your time.
I already thought at this solution (it was the first approach I used) but this means that I must build a query that contains a couple of "sum(...) as ..." for each year I want to consider.. and I know neither how many different years are contained in the table nor how big is the date interval..
Moreover I discarted this approach because I can only replace the initial and final year value in the SQL query (something like a "tag replace"). We use a particular reporting tecnology that accepts queries at design time and replaces variables at runtime (with typed parameters).

So I have to write something like
SELECT.... xxx...xxx..xxx
WHERE...
and Year>=[%shortInitialYear] and Year<=[%shortFinalYear]

Even if I could use the PIVOT keyword I have a similar problem because the pivot keyword assumes that the user has to specify each value to pivot "IN ([value1], [value2], [value3])" and not a range (something like "BETWEEN [%shortInitialYear] and [%shortFinalYear]" )|||

hi

i understand exactly what you mean.

here is a short description on what i did to solve the problem, i apologize for not having the time to go into more detail but you'll figure this out quickly

first do a query where you would get the distinct date information with the amounts (sum it if you want), in a view would be best

then when you have got the distinct dates create a new temporary table, dynamically of course. i created the tabel and in a cursor added the columns (which was a result from the view)

then insert into your dynamic table the values into columns corresponding to the view you have created.

this whole approach works if your dynamic sql logic is sound, best is that you don't have to specify any thing other than what you want in the original view.

hope this helps

|||

It is a known annoyance :) You can use pivot by flattening out the set: http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!758.entry but like Umachandar said, the "classic" style is easier/faster. If you want to help change this, vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127071

The easiest thing to do is to just make your SUM clauses dynamic:

create table year
(
year char(4) primary key
)
insert into year
select 2001
union all
select 2002
go
declare @.query varchar(8000)
select @.query = 'select t.SellerID ' + char(10) + (
SELECT distinct
', sum(case t.Year when ''' + year + ''' then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as ' + year + '_Values' + char(10) +
', sum(case t.Year when ''' + year + ''' then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as ' + year + '_Values' + char(10)
AS [text()]
FROM
year y
FOR XML PATH('') ) + char(10) + ' from tbl as t group by t.SellerID'

select @.query

I used 2005 syntax to build the sum columns, since I think you have 2005, in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=488720&SiteID=1 there is another full example.

sql

invalid object name

In my database I have created a table called "com01" owned
by "lisa" and if I try to select the rows with the
statement "select * from lisa.com01" it works fine !
But if I submit the statement "select * from com01" it
return the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'com01'.
Why '
In both cases I'm connected to the database with
user "lisa" who is the owner of the table. There isn't
other table called "com01" in the database.
Help me please !!!Is 'lisa' a member of the sysadmin role? In this case, the default owner
will be 'dbo' instead of 'lisa' when resolving object names. You can
determine the name used for object name resolution with SELECT USER.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>|||HI
Read Books Online: object names -> Object Visibility and Qualification Rules
Andras Jakus MCDBA
"paolo" wrote:
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
>|||Thank you for your help Andras !
I've just read the documentation as you suggest me, but
unfortunately the problem is not solved:
"lisa" is the owner of the table "com01" and is the user
connected to the database, but if I want to select from
that table I've to specified the owner_name dot table_name
(lisa.com01) and not only the table_name (com01). Is the
same also for other tables owned by "lisa" !
If I try to select from table owned by "dbo" only with
table_name (sysobjects) it works fine !
"lisa" is db_owner of the database.
Any other suggestions '
Thank you!
Bye Paolo.
>--Original Message--
>HI
>Read Books Online: object names -> Object Visibility and
Qualification Rules
>Andras Jakus MCDBA
>"paolo" wrote:
>> In my database I have created a table called "com01"
owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>.
>|||Bingo Dan ! "lisa" is a member of sysadmin role and the
statement "select user" return "dbo".
Is it possible to select from tables owned by "lisa"
without specified the owner ? I can't disable sysadmin
role for lisa !
Thank you !!!
Bye Paolo.
>--Original Message--
>Is 'lisa' a member of the sysadmin role? In this case,
the default owner
>will be 'dbo' instead of 'lisa' when resolving object
names. You can
>determine the name used for object name resolution with
SELECT USER.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"paolo" <paolo.ricci@.gidi.it> wrote in message
>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
>> In my database I have created a table called "com01"
owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>
>.
>|||You could create a view
create view dbo.com01 as select * from lisa.com01
then
select * from com01
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>|||Hopefully, Wayne's view suggestion will address your issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<paolo.ricci@.gidi.it> wrote in message
news:75b501c494df$edd69f20$a301280a@.phx.gbl...
> Bingo Dan ! "lisa" is a member of sysadmin role and the
> statement "select user" return "dbo".
> Is it possible to select from tables owned by "lisa"
> without specified the owner ? I can't disable sysadmin
> role for lisa !
> Thank you !!!
> Bye Paolo.
>>--Original Message--
>>Is 'lisa' a member of the sysadmin role? In this case,
> the default owner
>>will be 'dbo' instead of 'lisa' when resolving object
> names. You can
>>determine the name used for object name resolution with
> SELECT USER.
>>--
>>Hope this helps.
>>Dan Guzman
>>SQL Server MVP
>>"paolo" <paolo.ricci@.gidi.it> wrote in message
>>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
>> In my database I have created a table called "com01"
> owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>>
>>.

invalid object name

In my database I have created a table called "com01" owned
by "lisa" and if I try to select the rows with the
statement "select * from lisa.com01" it works fine !
But if I submit the statement "select * from com01" it
return the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'com01'.
Why ?
In both cases I'm connected to the database with
user "lisa" who is the owner of the table. There isn't
other table called "com01" in the database.
Help me please !!!
Is 'lisa' a member of the sysadmin role? In this case, the default owner
will be 'dbo' instead of 'lisa' when resolving object names. You can
determine the name used for object name resolution with SELECT USER.
Hope this helps.
Dan Guzman
SQL Server MVP
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why ?
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
|||HI
Read Books Online: object names -> Object Visibility and Qualification Rules
Andras Jakus MCDBA
"paolo" wrote:

> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why ?
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
>
|||Thank you for your help Andras !
I've just read the documentation as you suggest me, but
unfortunately the problem is not solved:
"lisa" is the owner of the table "com01" and is the user
connected to the database, but if I want to select from
that table I've to specified the owner_name dot table_name
(lisa.com01) and not only the table_name (com01). Is the
same also for other tables owned by "lisa" !
If I try to select from table owned by "dbo" only with
table_name (sysobjects) it works fine !
"lisa" is db_owner of the database.
Any other suggestions ?
Thank you!
Bye Paolo.

>--Original Message--
>HI
>Read Books Online: object names -> Object Visibility and
Qualification Rules[vbcol=seagreen]
>Andras Jakus MCDBA
>"paolo" wrote:
owned
>.
>
|||Bingo Dan ! "lisa" is a member of sysadmin role and the
statement "select user" return "dbo".
Is it possible to select from tables owned by "lisa"
without specified the owner ? I can't disable sysadmin
role for lisa !
Thank you !!!
Bye Paolo.

>--Original Message--
>Is 'lisa' a member of the sysadmin role? In this case,
the default owner
>will be 'dbo' instead of 'lisa' when resolving object
names. You can
>determine the name used for object name resolution with
SELECT USER.[vbcol=seagreen]
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"paolo" <paolo.ricci@.gidi.it> wrote in message
>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
owned
>
>.
>
|||You could create a view
create view dbo.com01 as select * from lisa.com01
then
select * from com01
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why ?
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
|||Hopefully, Wayne's view suggestion will address your issue.
Hope this helps.
Dan Guzman
SQL Server MVP
<paolo.ricci@.gidi.it> wrote in message
news:75b501c494df$edd69f20$a301280a@.phx.gbl...[vbcol=seagreen]
> Bingo Dan ! "lisa" is a member of sysadmin role and the
> statement "select user" return "dbo".
> Is it possible to select from tables owned by "lisa"
> without specified the owner ? I can't disable sysadmin
> role for lisa !
> Thank you !!!
> Bye Paolo.
> the default owner
> names. You can
> SELECT USER.
> owned

Interview question

I was asked in an interview what was the fastest way to count the rows in a
table?
I assume that: Select count(*) from Tablename - is or is not the fastest way
.
Is there another way that is faster or did I miss something.
thanksYour query would result in a full table scan. You can
also do this:
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.masterado.net
"brian" <brian@.discussions.microsoft.com> wrote in message
news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>I was asked in an interview what was the fastest way to count the rows in a
> table?
> I assume that: Select count(*) from Tablename - is or is not the fastest
> way.
> Is there another way that is faster or did I miss something.
> thanks|||The count returned by this method can be used as an approximation but might
not be accurate. The SELECT COUNT(*) method should be used in normal
production code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
> 2
>
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://www.masterado.net
>
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>|||> Your query would result in a full table scan.
Not necessarily. SELECT COUNT(*) FROM tblname can use (scan) any index on th
e table. Preferably
there is an index on a narrow column, and SQL Server can scan that index (le
ss pages to read).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
2
>
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://www.masterado.net
>
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uT63vfT2FHA.892@.TK2MSFTNGP12.phx.gbl

> Not necessarily. SELECT COUNT(*) FROM tblname can use (scan) any
> index on the table. Preferably there is an index on a narrow column,
> and SQL Server can scan that index (less pages to read).
Wouldn't a clustered index be the fastest possible, so that the rows are lin
ed
up for a quickie?
I doubt, therefore I might be.|||> Wouldn't a clustered index be the fastest possible, so that the rows are
> lined up for a quickie?
Not sure what you mean by 'lined up' but the issue with the clustered index
is that the clustered index leaf nodes are the data rows. Consequently, the
scan of a 10GB table with 100M rows will require scanning 10GB of data. A
scan of a non-clustered index on an int column of that table will only scan
about 400MB data (plus overhead) in the non-clustered leaf nodes. This is
why the optimizer chooses the narrowest available index for the COUNT(*)
operation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:uDKVnIW2FHA.1148@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:uT63vfT2FHA.892@.TK2MSFTNGP12.phx.gbl
>
> Wouldn't a clustered index be the fastest possible, so that the rows are
> lined up for a quickie?
> --
> I doubt, therefore I might be.|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Ooe7gxW2FHA.2216@.TK2MSFTNGP15.phx.gbl

> in the non-clustered leaf nodes. This is why the optimizer chooses
> the narrowest available index for the COUNT(*) operation.
Thanks for the clarification!
I doubt, therefore I might be.|||"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
> 2
>
You don't have to do a table scan. A scan of a secondary index will work
too. Moreover the value in sysindexes is not guaranteed to be correct. So
"Select count(*)" is correct, although the "right" answer probably touches
on all these issues.
David|||No, this will not always need to do a full table scan. Since every
nonclustered index has a pointer to every row, a leaf scan of any nc index
will also give an accurate result.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:e1WlYqQ2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Your query would result in a full table scan. You can
> also do this:
> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid <
> 2
>
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> http://www.masterado.net
>
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:585DEB83-4414-4FD6-BDEA-2CF49C47FAB3@.microsoft.com...
>
>