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:

Problem using an AXIS Webservice

Helo:

I am trying to use an axis webservice as a datasource to a report.

I have an error : Failed to prepare web request for the specified URL

When I run the query in query designer.

I test the webservice in a windows aplication and works fine. I think that the problem I have is with the target namespace/url

I am using the folowing data in the report:

datasource conection string: http://191.158.134.200:8080/CatalogWS/services/CatalogWebService

dataset query:

<Query>

<Method Namespace="urn:CatalogWebService" Name="findArticulos"/>

<SoapAction>urn:CatalogWebService/findArticulos</SoapAction>

</Query>

And here is a fragment (the header) of the wsdl of the webservice:

http://191.158.134.200:8080/CatalogWS/services/CatalogWebService?wsdl

<wsdl:definitions targetNamespace="urn:CatalogWebService" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:apachesoap="http://xml.apache.org/xml-soap" xmlns:impl="urn:CatalogWebService" xmlns:intf="urn:CatalogWebService" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns1="http://core.catalog.cepre.opp.gub.uy" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:wsdlsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

- <wsdl:types>...

Someone can help me, to find the right way ?

Tanks!!

I had resolved the conection problem, the problem was the parameters of the method.

The problem now is that the dataset result is not the actual data, there are 3 columns and one row:

columns: href ns1 encodingStyle

values: #0 urn:CatalogWebService http://schemas.xmlsoap.org/soap/encoding/

The data type returned by the ws method is a complex object with an array inside, is this the problem ?

Tanks for any help!

|||

Yes the complex object may be the issue.

The following article may give you some ideas on how to use the element path and apply type casting in your xml data extension query: http://msdn.microsoft.com/library/en-us/dnsql90/html/RepServXMLDS.asp

-- Robert

|||

Robert

the link you send me is about Charts and have no information related to xml. Please review that.

Tanks.

|||

Sorry for the incorrect link. I fixed it.

-- Robert

Problem using an AXIS Webservice

Helo:

I am trying to use an axis webservice as a datasource to a report.

I have an error : Failed to prepare web request for the specified URL

When I run the query in query designer.

I test the webservice in a windows aplication and works fine. I think that the problem I have is with the target namespace/url

I am using the folowing data in the report:

datasource conection string: http://191.158.134.200:8080/CatalogWS/services/CatalogWebService

dataset query:

<Query>

<Method Namespace="urn:CatalogWebService" Name="findArticulos"/>

<SoapAction>urn:CatalogWebService/findArticulos</SoapAction>

</Query>

And here is a fragment (the header) of the wsdl of the webservice:

http://191.158.134.200:8080/CatalogWS/services/CatalogWebService?wsdl

<wsdl:definitions targetNamespace="urn:CatalogWebService" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:apachesoap="http://xml.apache.org/xml-soap" xmlns:impl="urn:CatalogWebService" xmlns:intf="urn:CatalogWebService" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns1="http://core.catalog.cepre.opp.gub.uy" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:wsdlsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

- <wsdl:types>...

Someone can help me, to find the right way ?

Tanks!!

I had resolved the conection problem, the problem was the parameters of the method.

The problem now is that the dataset result is not the actual data, there are 3 columns and one row:

columns: href ns1 encodingStyle

values: #0 urn:CatalogWebService http://schemas.xmlsoap.org/soap/encoding/

The data type returned by the ws method is a complex object with an array inside, is this the problem ?

Tanks for any help!

|||

Yes the complex object may be the issue.

The following article may give you some ideas on how to use the element path and apply type casting in your xml data extension query: http://msdn.microsoft.com/library/en-us/dnsql90/html/RepServXMLDS.asp

-- Robert

|||

Robert

the link you send me is about Charts and have no information related to xml. Please review that.

Tanks.

|||

Sorry for the incorrect link. I fixed it.

-- Robert

problem using "com.microsoft.jdbc.sqlserver.SQLServerDriver" driver

hi
I am using "com.microsoft.jdbc.sqlserver.SQLServerDriver" driver to connect
the SQL Server 2000.
Everything is ok if there is one request to the SQL server.
When there are more than one requests to the SQL server, exception was
thrown
I want to ask if that driver has some problems to handle multi-request.
Thanks
The expcetions are:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Object has
been closed.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseResultSet.validateClos edState(Unknown
Source)
at
com.microsoft.jdbc.base.BaseResultSet.commonFetchI nitialize(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.next(Unknown Source)
at GetData.GetData.getColumn(GetData.java:192)
at GetData.GetData.insert_fileInstance_table(GetData. java:1156)
at RadicaPocketServer2.handleRequest(RadicaPocketServ er2.java:1056)
at RadicaPocketServer2.doPost(RadicaPocketServer2.jav a:55)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
at
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(Application
FilterChain.java:247)
at
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterCh
ain.java:193)
at
org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.ja
va:256)
at
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
at
org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.ja
va:191)
at
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
at
org.apache.catalina.core.StandardContext.invoke(St andardContext.java:2415)
at
org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:180
)
at
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.valves.ErrorDispatcherValve.in voke(ErrorDispatcherValve.
java:171)
at
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:172
)
at
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.AccessLogValve.invoke(A ccessLogValve.java:509)
at
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
at
org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java
:174)
at
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
at
org.apache.ajp.tomcat4.Ajp13Processor.process(Ajp1 3Processor.java:466)
at
org.apache.ajp.tomcat4.Ajp13Processor.run(Ajp13Pro cessor.java:585)
at java.lang.Thread.run(Thread.java:534)
Hello Joe
Thanks for you suggestion, but I still have questions about it.
My servlet is run in tomcat 5.0 and I know that tomcat help us to handle
multiple threading problem
Could you explain your idea to me more. Thank you
Also, I have written a class called GetData.java which represent to connect
and execute SQL statement.
GetData is used by my servlet program to handle database connections
Do you mean that I need to handle mutli-threading in GetData.java's
functions
Thank you very much
Simon Lee
"Joe Weinstein" <joeNOSPAM@.bea.com> ? news:407FFE48.30900@.bea.com ?...[vbcol=seagreen]
> Hi.
> the problem is probably just that your servlet code is not thread-safe.
> Multiple threads are probably trying to use the same jdbc objects.
> Define all your jdbc objects as method-level variables and you should
> be OK...
> Joe Weinstein at BEA
> Simon Lee wrote:
connect[vbcol=seagreen]
has[vbcol=seagreen]
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown[vbcol=seagreen]
com.microsoft.jdbc.base.BaseResultSet.validateClos edState(Unknown[vbcol=seagreen]
Source)[vbcol=seagreen]
RadicaPocketServer2.handleRequest(RadicaPocketServ er2.java:1056)[vbcol=seagreen]
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(Application[vbcol=seagreen]
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterCh[vbcol=seagreen]
org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.ja[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)[vbcol=seagreen]
org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.ja[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)[vbcol=seagreen]
org.apache.catalina.core.StandardContext.invoke(St andardContext.java:2415)[vbcol=seagreen]
org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:180[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok[vbcol=seagreen]
org.apache.catalina.valves.ErrorDispatcherValve.in voke(ErrorDispatcherValve.[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok[vbcol=seagreen]
org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:172[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok[vbcol=seagreen]
org.apache.catalina.valves.AccessLogValve.invoke(A ccessLogValve.java:509)[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)[vbcol=seagreen]
org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok[vbcol=seagreen]
org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
>