ROBUST PLAN error

Hi all
I'm trying to INSERT rows from one table to the other in a Oracle Linked server to SQL server
My query is something like this
INSERT INTO ORACLE_LINK..User.Table
SELECT *
FROM ORACLE_LINK..User.Table
I'm getting the following error
Server: Msg 510, Level 16, State 1, Line
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint
(Both are oracle tables only)
Can anybody tell the reason for this error
Thanks
Siva.It looks like the rows in the Oracle tables are longer than the maximum
rowlength in SQL Server, which is 8060 bytes.
You are probably best off to issue this query as a pass-through query using
OPENQUERY(ORACLE_LINK, 'INSERT INTO User.Table1SELECT * FROM User.Table2')
--
Jacco Schalkwijk
SQL Server MVP
"Siva" <siva116@.yahoo.com> wrote in message
news:0CD3DD0E-9B5A-441F-A040-030C9E108750@.microsoft.com...
> Hi all,
> I'm trying to INSERT rows from one table to the other in a Oracle Linked
server to SQL server.
> My query is something like this:
> INSERT INTO ORACLE_LINK..User.Table1
> SELECT *
> FROM ORACLE_LINK..User.Table2
> I'm getting the following error:
> Server: Msg 510, Level 16, State 1, Line 1
> Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint.
> (Both are oracle tables only).
> Can anybody tell the reason for this error?
> Thanks,
> Siva.

ROBUST PLAN error

Hi all,
I'm trying to INSERT rows from one table to the other in a Oracle Linked se
rver to SQL server.
My query is something like this:
INSERT INTO ORACLE_LINK..User.Table1
SELECT *
FROM ORACLE_LINK..User.Table2
I'm getting the following error:
Server: Msg 510, Level 16, State 1, Line 1
Cannot create a worktable row larger than allowable maximum. Resubmit your q
uery with the ROBUST PLAN hint.
(Both are oracle tables only).
Can anybody tell the reason for this error?
Thanks,
Siva.It looks like the rows in the Oracle tables are longer than the maximum
rowlength in SQL Server, which is 8060 bytes.
You are probably best off to issue this query as a pass-through query using
OPENQUERY(ORACLE_LINK, 'INSERT INTO User.Table1SELECT * FROM User.Table2')
Jacco Schalkwijk
SQL Server MVP
"Siva" <siva116@.yahoo.com> wrote in message
news:0CD3DD0E-9B5A-441F-A040-030C9E108750@.microsoft.com...
> Hi all,
> I'm trying to INSERT rows from one table to the other in a Oracle Linked
server to SQL server.
> My query is something like this:
> INSERT INTO ORACLE_LINK..User.Table1
> SELECT *
> FROM ORACLE_LINK..User.Table2
> I'm getting the following error:
> Server: Msg 510, Level 16, State 1, Line 1
> Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint.
> (Both are oracle tables only).
> Can anybody tell the reason for this error?
> Thanks,
> Siva.

rights issues with linked server

I have a web server and database server which are not on a domain. The
web server has an app that is running on under the IUSR account and
there is a corresponding IUSR account on the database server. The web
app connects to the SQL server with the windows authentication and
everything works fine.

I also have a stored proc which does a file search using a linked
server to an index server on the very same web server. For now assume
that cannot change, the files must be on the web server (TRA is the
machine name). When attempting to execute the following query from the
database server, doing a RunAs on the SQL 2005 Management studio as the
IUSR account from the database server, everything works fine:

SELECT
Q.*
FROM
OPENQUERY(FTIndexPM, 'SELECT path, characterization, rank, hitcount
FROM TRA.CatalogTest..SCOPE(''DEEP TRAVERSAL OF "C:\Program
Files\dir1\Files\Project757\DocMgmt"'') WHERE CONTAINS(''"test"'') > 0
OR FileName LIKE ''%test%'' ORDER BY rank DESC') AS Q

The IUSR_TRA account is an administrator on both the database and web
server for debugging reasons until we figure this out, but clearly it
shouldn't be a rights issue if both users are administrators on both
machines.

Now for the tricky, part. When the same query as above is executed from
the web server, it fails with the the below error:

OLE DB provider "MSIDXS" for linked server "FTIndexPM" returned message
"Invalid catalog name 'CatalogTest'. SQLSTATE=42000 ".

Using SQL Profiler, I have verified that both queries are actually
running under the IUSR_TRA account. Any ideas why the exact same query
running under the exact same NT login would behave differently when
executed from a web app as opposed to through a clinet request from a
web app?As for the last question, I meant through the SQL 2005 query tool as
opposed to the web app. Same command, same executing windows user,
different results.|||Anyone know where this should be posted where it might get a response?|||pb648174 (google@.webpaul.net) writes:
> Anyone know where this should be posted where it might get a response?

Maybe an IIS or ASP group. It's completely beyond my domain, so I don't
really know.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Could you perhaps comment on the last question, i.e. if in SQL Profiler
they are both running under the same login, could anything be different
about their access rights? In SQL 2005, when I try to change the
authentication for a linked server, it seems to have no effect as well.
I can change the user/password to sdfsdf/sdfsdf and it still works...
Seems like there are issues with linked server authentication in SQL
2005.|||pb648174 (google@.webpaul.net) writes:
> Could you perhaps comment on the last question, i.e. if in SQL Profiler
> they are both running under the same login, could anything be different
> about their access rights? In SQL 2005, when I try to change the
> authentication for a linked server, it seems to have no effect as well.
> I can change the user/password to sdfsdf/sdfsdf and it still works...
> Seems like there are issues with linked server authentication in SQL
> 2005.

There is still a web server involved. And probably some Windows networking.
Both fall outside my field of expertise.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I fixed it by doing the following:

Instead of trying to use Index server on web server, just used index
server on database server and mapped drive for database server index
server to get at files on web server. Works like a champ.

Erland Sommarskog wrote:
> pb648174 (google@.webpaul.net) writes:
> > Could you perhaps comment on the last question, i.e. if in SQL Profiler
> > they are both running under the same login, could anything be different
> > about their access rights? In SQL 2005, when I try to change the
> > authentication for a linked server, it seems to have no effect as well.
> > I can change the user/password to sdfsdf/sdfsdf and it still works...
> > Seems like there are issues with linked server authentication in SQL
> > 2005.
> There is still a web server involved. And probably some Windows networking.
> Both fall outside my field of expertise.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspxsql

rights for each login

Hi ,
1 - How do i know the rights for each logins ? is there
any command to show it ?
2 - There's a client application linked to the SQL
server , how will i be able to check if it's using the
application role or the role specified inside the SQL
server ?
Appreciate if there's anyone that could help me out
thks & rdgs
Hi
1) Look at sp_helprotect system procedure.
2)sp_helprole
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:9ec501c4791f$01827350$a401280a@.phx.gbl...
> Hi ,
> 1 - How do i know the rights for each logins ? is there
> any command to show it ?
> 2 - There's a client application linked to the SQL
> server , how will i be able to check if it's using the
> application role or the role specified inside the SQL
> server ?
> Appreciate if there's anyone that could help me out
> thks & rdgs
>
|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there
>
>.
>
|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there
>
>.
>
|||BOL says
B. List the permissions for a user
This example lists all permissions that user Judy has in the current
database.
EXEC sp_helprotect NULL, 'Judy'
If you get the error (in your case) so the user abc does not exist in the
system"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:a21f01c4793b$87054f40$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Uri ,
> thks but can i find out further
> i have used exec sp_helprotect null, 'abc' which abc is a
> user. however the system returns 'there are no matching
> roles on which to report'
> in ur opinon , wat could be the cause ?
> thks & rdgs
> message
> there
|||Hi
I have actually looked under the users of that database
before i execute the command. it's ok for another server
but not this one
rdgs
>--Original Message--
>BOL says
>B. List the permissions for a user
>This example lists all permissions that user Judy has in
the current
>database.
>EXEC sp_helprotect NULL, 'Judy'
>If you get the error (in your case) so the user abc does
not exist in the
>system"maxzsim" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:a21f01c4793b$87054f40$a501280a@.phx.gbl...
a[vbcol=seagreen]
in[vbcol=seagreen]
the
>
>.
>

rights for each login

Hi ,
1 - How do i know the rights for each logins ? is there
any command to show it ?
2 - There's a client application linked to the SQL
server , how will i be able to check if it's using the
application role or the role specified inside the SQL
server ?
Appreciate if there's anyone that could help me out
thks & rdgsHi
1) Look at sp_helprotect system procedure.
2)sp_helprole
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:9ec501c4791f$01827350$a401280a@.phx.gbl...
> Hi ,
> 1 - How do i know the rights for each logins ? is there
> any command to show it ?
> 2 - There's a client application linked to the SQL
> server , how will i be able to check if it's using the
> application role or the role specified inside the SQL
> server ?
> Appreciate if there's anyone that could help me out
> thks & rdgs
>|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there[vbcol=seagreen]
>
>.
>|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
there[vbcol=seagreen]
>
>.
>|||BOL says
B. List the permissions for a user
This example lists all permissions that user Judy has in the current
database.
EXEC sp_helprotect NULL, 'Judy'
If you get the error (in your case) so the user abc does not exist in the
system"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:a21f01c4793b$87054f40$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Uri ,
> thks but can i find out further
> i have used exec sp_helprotect null, 'abc' which abc is a
> user. however the system returns 'there are no matching
> roles on which to report'
> in ur opinon , wat could be the cause ?
> thks & rdgs
> message
> there|||Hi
I have actually looked under the users of that database
before i execute the command. it's ok for another server
but not this one
rdgs
>--Original Message--
>BOL says
>B. List the permissions for a user
>This example lists all permissions that user Judy has in
the current
>database.
>EXEC sp_helprotect NULL, 'Judy'
>If you get the error (in your case) so the user abc does
not exist in the
>system"maxzsim" <anonymous@.discussions.microsoft.com>
wrote in message
>news:a21f01c4793b$87054f40$a501280a@.phx.gbl...
a[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>

rights for each login

Hi ,
1 - How do i know the rights for each logins ? is there
any command to show it ?
2 - There's a client application linked to the SQL
server , how will i be able to check if it's using the
application role or the role specified inside the SQL
server ?
Appreciate if there's anyone that could help me out
thks & rdgsHi
1) Look at sp_helprotect system procedure.
2)sp_helprole
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:9ec501c4791f$01827350$a401280a@.phx.gbl...
> Hi ,
> 1 - How do i know the rights for each logins ? is there
> any command to show it ?
> 2 - There's a client application linked to the SQL
> server , how will i be able to check if it's using the
> application role or the role specified inside the SQL
> server ?
> Appreciate if there's anyone that could help me out
> thks & rdgs
>|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
>> Hi ,
>> 1 - How do i know the rights for each logins ? is
there
>> any command to show it ?
>> 2 - There's a client application linked to the SQL
>> server , how will i be able to check if it's using the
>> application role or the role specified inside the SQL
>> server ?
>> Appreciate if there's anyone that could help me out
>> thks & rdgs
>
>.
>|||Hi Uri ,
thks but can i find out further
i have used exec sp_helprotect null, 'abc' which abc is a
user. however the system returns 'there are no matching
roles on which to report'
in ur opinon , wat could be the cause ?
thks & rdgs
>--Original Message--
>Hi
>1) Look at sp_helprotect system procedure.
>2)sp_helprole
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9ec501c4791f$01827350$a401280a@.phx.gbl...
>> Hi ,
>> 1 - How do i know the rights for each logins ? is
there
>> any command to show it ?
>> 2 - There's a client application linked to the SQL
>> server , how will i be able to check if it's using the
>> application role or the role specified inside the SQL
>> server ?
>> Appreciate if there's anyone that could help me out
>> thks & rdgs
>
>.
>|||BOL says
B. List the permissions for a user
This example lists all permissions that user Judy has in the current
database.
EXEC sp_helprotect NULL, 'Judy'
If you get the error (in your case) so the user abc does not exist in the
system"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:a21f01c4793b$87054f40$a501280a@.phx.gbl...
> Hi Uri ,
> thks but can i find out further
> i have used exec sp_helprotect null, 'abc' which abc is a
> user. however the system returns 'there are no matching
> roles on which to report'
> in ur opinon , wat could be the cause ?
> thks & rdgs
> >--Original Message--
> >Hi
> >1) Look at sp_helprotect system procedure.
> >
> >2)sp_helprole
> >
> >
> >
> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:9ec501c4791f$01827350$a401280a@.phx.gbl...
> >> Hi ,
> >>
> >> 1 - How do i know the rights for each logins ? is
> there
> >> any command to show it ?
> >>
> >> 2 - There's a client application linked to the SQL
> >> server , how will i be able to check if it's using the
> >> application role or the role specified inside the SQL
> >> server ?
> >>
> >> Appreciate if there's anyone that could help me out
> >>
> >> thks & rdgs
> >>
> >
> >
> >.
> >|||Hi
I have actually looked under the users of that database
before i execute the command. it's ok for another server
but not this one
rdgs
>--Original Message--
>BOL says
>B. List the permissions for a user
>This example lists all permissions that user Judy has in
the current
>database.
>EXEC sp_helprotect NULL, 'Judy'
>If you get the error (in your case) so the user abc does
not exist in the
>system"maxzsim" <anonymous@.discussions.microsoft.com>
wrote in message
>news:a21f01c4793b$87054f40$a501280a@.phx.gbl...
>> Hi Uri ,
>> thks but can i find out further
>> i have used exec sp_helprotect null, 'abc' which abc is
a
>> user. however the system returns 'there are no matching
>> roles on which to report'
>> in ur opinon , wat could be the cause ?
>> thks & rdgs
>> >--Original Message--
>> >Hi
>> >1) Look at sp_helprotect system procedure.
>> >
>> >2)sp_helprole
>> >
>> >
>> >
>> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:9ec501c4791f$01827350$a401280a@.phx.gbl...
>> >> Hi ,
>> >>
>> >> 1 - How do i know the rights for each logins ? is
>> there
>> >> any command to show it ?
>> >>
>> >> 2 - There's a client application linked to the SQL
>> >> server , how will i be able to check if it's using
the
>> >> application role or the role specified inside the SQL
>> >> server ?
>> >>
>> >> Appreciate if there's anyone that could help me out
>> >>
>> >> thks & rdgs
>> >>
>> >
>> >
>> >.
>> >
>
>.
>sql