Monday, March 26, 2012

Problem with attached view

I write you because I have a big problem with request access : when
I use a sql server wiev in access (attached view) and i join it with
a local access table, the sql server trace say sql server send all
the data of the view to a access . This view have 15 000 000 rows
When i do the same with an sql server table (attached table) , i
have a very fast answer. every data of the local table is send to
sqlserver (exec sp_execute 1, N'205513214066535435'), why it doesn't
happen this with the view.
there is no difference between the table and the view.
my table is :dbo.table
my view is : create view vtable as select * from dbo.table
Hi
Show us the code you use to call the view.
Where is the where clause?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"toni" <toni@.discussions.microsoft.com> wrote in message
news:2282D883-62D2-4810-9442-418C36148C9C@.microsoft.com...
> I write you because I have a big problem with request access : when
> I use a sql server wiev in access (attached view) and i join it
> with
> a local access table, the sql server trace say sql server send all
> the data of the view to a access . This view have 15 000 000 rows
> When i do the same with an sql server table (attached table) , i
> have a very fast answer. every data of the local table is send to
> sqlserver (exec sp_execute 1, N'205513214066535435'), why it
> doesn't
> happen this with the view.
> there is no difference between the table and the view.
> my table is :dbo.table
> my view is : create view vtable as select * from dbo.table
|||Hello,
The local acces table have one field id
The linked view sqlserver_view
The linked table sqlserver_table
in access the request is:
SELECT *
FROM sqlserver_view.view INNER JOIN local_acces.table ON
local_acces.table.id = sqlserver_view.view.id
All the datat of the view are send (odbc error after many minutes)
the trace give :
sqlbatchcompleted
SELECT *FROM sqlserver_view.view
when i do the same with the linked table sqlserver_table.table
SELECT *
FROM sqlserver_table.table INNER JOIN local_acces.table ON
local_acces.table.id = sqlserver_table.table.id
the data are send immediatly
the trace give
rpc.completed
declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT
*FROMsqlserver_table.table WHERE ("Carte_SAM" = @.P1)', N'453335736479610200'
select @.P1
Sorry , I don't speak a very good english because I'm spanish
regards
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Show us the code you use to call the view.
> Where is the where clause?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "toni" <toni@.discussions.microsoft.com> wrote in message
> news:2282D883-62D2-4810-9442-418C36148C9C@.microsoft.com...
>
>
|||The linked view sqlserver_view
The linked table sqlserver_table
and
the sqlserver_view is
create view dbo. sqlserver_view
as
select
*
from
dbo. sqlserver_table
regards
"toni" wrote:
[vbcol=seagreen]
> Hello,
> The local acces table have one field id
> The linked view sqlserver_view
> The linked table sqlserver_table
> in access the request is:
> SELECT *
> FROM sqlserver_view.view INNER JOIN local_acces.table ON
> local_acces.table.id = sqlserver_view.view.id
> All the datat of the view are send (odbc error after many minutes)
> the trace give :
> sqlbatchcompleted
> SELECT *FROM sqlserver_view.view
> when i do the same with the linked table sqlserver_table.table
> SELECT *
> FROM sqlserver_table.table INNER JOIN local_acces.table ON
> local_acces.table.id = sqlserver_table.table.id
> the data are send immediatly
> the trace give
> rpc.completed
> declare @.P1 int
> set @.P1=1
>
> exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT
> *FROMsqlserver_table.table WHERE ("Carte_SAM" = @.P1)', N'453335736479610200'
> select @.P1
>
> Sorry , I don't speak a very good english because I'm spanish
>
>
>
> regards
> "Mike Epprecht (SQL MVP)" wrote:
|||The view haven't clause where, it's a simply select * from the table
"toni" wrote:
[vbcol=seagreen]
> The linked view sqlserver_view
> The linked table sqlserver_table
> and
> the sqlserver_view is
> create view dbo. sqlserver_view
> as
> select
> *
> from
> dbo. sqlserver_table
>
> regards
>
>
> "toni" wrote:

No comments:

Post a Comment