strange error in If exists block.....

[DLu]

Hi:
I have a nested 2 cursors to pass serverName and then databaseName to run insert query to a temp table.
To simplify, just use following if exists statement to determine if an insert should be skipped when there is no table 'table_5' exists to avoid failure.

the strange error occurs whenever the insert and select block appers but the table_5 does not exist. if I comment out the insert and select statment, the query execution will print 'not exist _5' to skip this db insert and move on to next db.

if exists (select * from [server1].db2.dbo.sysobjects where name='table_5'
and type = 'U')
begin
print 'exists _5 '
-- insert #tempHold
-- select 'server1','db2', count(*) from [server1].db2.dbo.table_5 where custID=40
end
else
begin
print 'Not exists _5 '
end

Server: Msg 7314, Level 16, State 1, Line 7
OLE DB provider 'server1' does not contain table '"db2"."dbo"."table_5"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='server1', TableName='"db2"."dbo"."table_5"'].

The if ... else... and begin ...end does not work properly when the insert and select uncommented in if exists block.

thanks
-D

[rmiao]

What's your sql sp level? How do you map user to linked server? Have proper permission on target table? Tried with openquery?

[DLu]

Here is the original code within the serverCursor:

select @tableName = 'table_5'
OPEN cur_databaseFetch
FETCH NEXT FROM cur_databaseFetch INTO @databaseName
WHILE @@FETCH_STATUS = 0
BEGIN
select @sqlStringFrom =
' if exists (select * from ['+@serverName+'].'+@databaseName+'.dbo.sysobjects '+ ' where name = ' +''''+@tableName+''''+' and type =''U'')'+' begin '+
' insert #tempHoldCountingFDMS ' +
' select ' + '''' + @serverName + '''' + ',' + '''' + @databaseName + ''''+ ', count(*) ' +' from [' + @serverName + '].' + @databaseName + '.dbo.' + @tableName_StyleTypes_5 +
' where customerID in (40, 45)' + ' end '
print @sqlStringFrom
exec (@sqlStringFrom)

select @countDatabase = @countDatabase + 1
FETCH NEXT FROM cur_databaseFetch INTO @databaseName
END
CLOSE cur_databaseFetch
DEALLOCATE cur_databaseFetch

compatible level 80.
linking from my pc via sp_addlinkedserver and sp_addlinkedsrvlogin with sa.

The puzzle: when 'table_5' does not exist in db2 of the 20 databases of serverA, the if exists(select * from serverA.db2.dbo.sysobjects where name = 'table_5' and type='U') should be responded false to go to else block, but it still executes the insert/select statement, which caused fail since the table_5 does not exist in db2. When db2 is skipped from the database cursor, all codes run fine. But I do need to include the if exists to skip process on the db which does not have table_5, such as db2.

I have tried to comment out the insert statement, just left the select in if exists block, the result is the same error. Feeling the if exists block not work as properly as when in a linked server level.

thanks
-D