FP2003/Data View/Custom Query

[scmiles]

Hello, I am trying to figure out the custom query portion
of inserting a data view but can not for the life of me.
I am inserting the exact query as you would in SQL Query
Analyzer, and it works fine over there. However after I
insert the query and then try to view the data, FP2003
displays a message in the data view details window that
says "The Data Source does not contain valid XML data."

If I just use the wizard to pick the columns I want, it
all works fine, except what I am trying to do is only
diaplay rows that contain a column that equals the
current logged on user's username.

Any FP2003 users out there that can help?

 

[scmiles]

I found in the code, the xsl where statement it looks
like this:
<dsp:Where
xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp">

<dsp:Eq>

<dsp:FieldRef Name="GOBTPAC_EXTERNAL_USER"/>

<dsp:Value Type="x:string">scmiles</dsp:Value>

</dsp:Eq>

</dsp:Where>

This is when I hardcode a user in to test, what I need to
know is what to put in place of the hardcoded "scmiles"
that gets the current username.

 

[John Clark]

It sounds like you are having a proxy or permisions issue.

First, try it without using a custom query, can you just connect to the
database and see the list of available tables and what not? If so, then it
is probably a proxy issue.

This was posted in the beta newsgroups:

Add

<system.net>
<defaultProxy>
<proxy proxyaddress="http://proxyServer"/>
</defaultProxy>
</system.net>

to the c:\program files\common files\microsoft shared\web server
extensions\60\config\web.config file on the server. Just above the closing
</configuration> tag.

http://proxyServer (I think obviously) should be the same proxy server as in
your Internet Options > Connections tab.

-John

 

[scmiles]

Okay, I tried this on a server where the proxy is
configured corerctly.

I click on 'Data\Insert Data View
The Data Sourse Catalog Window Opens to the right
I expand 'Data Base Connections' and click on 'Add to
catalog...'
Click on 'Configure Database Connection...'
Type in Server name and account info and click next
Check box for 'Use Custom Query' and click 'Edit...'
button
I paste the query from Query Analyzer in the field and
click 'OK'
Click Finish
Back in the Data Sourse Catalog Window the Connection now
appears.
I click on the drop down arrow next to the connection and
choose show data.
The Data View Detals window shows the following message.
"The Data Source does not contain valid XML data."
"The administration settings for this Web site do not
allow access to databases by using custom command
strings. Use the Microsoft Windows SharePoint Services
Central Administration page to enable update query
support in data retrieval services, or contact your Web
site administrator for more information."

FYI the query I am pasting in the edit command string box
is a simple select query query not an update query.

So I make the change requested above and delete the
conection close out FP2003 and reset IIS.

Go back to FP2003 and repeat the above steps and this
time when I do 'Show Data' I get the following message:
"The Data Source does not contain valid XML data."
"The server for the data source returned a non-specific
error when trying to execute your query. Check the
format and content of your query and try again."

So I am thinking the box where I am pasting in the query
is not expecting a regular SQL expression, but rather
some XML or XSL code or something.

Any ideas, or a way you can try to re-create?

 

[scmiles]

I have aslo tested this on a server that is runnign SQL
on it, so it does not have to go through a firewall to
connect to the data source in which case you do not have
to make the proxy setting change in the web.config

-----Original Message-----
I found in the code, the xsl where statement it looks
like this:
<dsp:Where
xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp">


<dsp:FieldRef Name="GOBTPAC_EXTERNAL_USER"/>

 

[John Clark]

Like I said, try not using a custom query. Can you see a list of available
tables?

 

[scmiles]

Yes it works without using a custom query, that was in my
original post.

 

[John Clark]

In order to display rows that only contain the user's user name, you don't
need to use a custom query, but since you want to, you need to enable the
server to read update queries.

This is done from the Server admin pages (not the WSS admin pages, rather
the pages on the server itself -- from the admin section of the control
panel).

Check the box on this admin page:

http://localhost:XXXXX/DspSettings.aspx

-John

 

[scmiles]

That is the exact place I have already enabled it to read
update queries. I have even tried iisreset/restart Portal
Servies, and finally rebooted, and I can still not use
Update queries.

Also, how would you create a filter on the data
connection to only show records with the current username?

Not being ablt to do custom queries is the biggest
problem though.

 

[John Clark]

It is really odd that your custom query doesn't work, this same type of
query works for me.

The way I prefer to do it, though, is like this:

Create a data source pointing to the table you want to view
Insert a view of that table
Click on Data > Filter
Choose the column with your user names in it
Equals
[Current User]

That should do it.

-John

 

[scmiles]

I figured out why the custom queries were not working,
simply copying the query from Query analyzer, such as
(Select * from Sometable) would not work because it did
not have any relation to a database, so I did it like
this and it worked (Select * From dbname.dbo.tablename)

As for the username I have tried using the filter after
simply selecting the table and columns I want and then
clicking on filter, but when I click on the field that
has the username and then eqauls and then the drop down
for the value, the value drop down is empty it does not
contain [current user] as an option.

-----Original Message-----
It is really odd that your custom query doesn't work, this same type of
query works for me.

The way I prefer to do it, though, is like this:

Create a data source pointing to the table you want to view
Insert a view of that table
Click on Data > Filter
Choose the column with your user names in it
Equals
[Current User]

That should do it.

-John

That is the exact place I have already enabled it to read
update queries. I have even tried iisreset/restart Portal
Servies, and finally rebooted, and I can still not use
Update queries.

Also, how would you create a filter on the data
connection to only show records with the current username?

Not being ablt to do custom queries is the biggest
problem though. section
of the control in
message in
my wrote
in on 'Add
to field
and your
Web and
what

.

 

[scmiles]

Is the username value you have in your DB stored as
domain\username or just username?

-----Original Message-----
It is really odd that your custom query doesn't work, this same type of
query works for me.

The way I prefer to do it, though, is like this:

Create a data source pointing to the table you want to view
Insert a view of that table
Click on Data > Filter
Choose the column with your user names in it
Equals
[Current User]

That should do it.

-John

That is the exact place I have already enabled it to read
update queries. I have even tried iisreset/restart Portal
Servies, and finally rebooted, and I can still not use
Update queries.

Also, how would you create a filter on the data
connection to only show records with the current username?

Not being ablt to do custom queries is the biggest
problem though. section
of the control in
message in
my wrote
in on 'Add
to field
and your
Web and
what

.

 

[John Clark]

Yeah, I think that must be a bug or something. I don't think you can use
user name there, you have to use it on the view itself.

After you insert a view of the data, then click on Data > Filter. Then the
choice for user name should show up.

-John

I figured out why the custom queries were not working,
simply copying the query from Query analyzer, such as
(Select * from Sometable) would not work because it did
not have any relation to a database, so I did it like
this and it worked (Select * From dbname.dbo.tablename)

As for the username I have tried using the filter after
simply selecting the table and columns I want and then
clicking on filter, but when I click on the field that
has the username and then eqauls and then the drop down
for the value, the value drop down is empty it does not
contain [current user] as an option.

-----Original Message-----
It is really odd that your custom query doesn't work, this same type of
query works for me.

The way I prefer to do it, though, is like this:

Create a data source pointing to the table you want to view
Insert a view of that table
Click on Data > Filter
Choose the column with your user names in it
Equals
[Current User]

That should do it.

-John

That is the exact place I have already enabled it to read
update queries. I have even tried iisreset/restart Portal
Servies, and finally rebooted, and I can still not use
Update queries.

Also, how would you create a filter on the data
connection to only show records with the current username?

Not being ablt to do custom queries is the biggest
problem though.
-----Original Message-----
In order to display rows that only contain the user's
user name, you don't
need to use a custom query, but since you want to, you
need to enable the
server to read update queries.

This is done from the Server admin pages (not the WSS
admin pages, rather
the pages on the server itself -- from the admin section
of the control
panel).

Check the box on this admin page:

http://localhost:XXXXX/DspSettings.aspx

-John


message
Yes it works without using a custom query, that was in
my
original post.
-----Original Message-----
Like I said, try not using a custom query. Can you
see
a list of available
tables?


in
message
Okay, I tried this on a server where the proxy is
configured corerctly.

I click on 'Data\Insert Data View
The Data Sourse Catalog Window Opens to the right
I expand 'Data Base Connections' and click on 'Add
to
catalog...'
Click on 'Configure Database Connection...'
Type in Server name and account info and click next
Check box for 'Use Custom Query' and click 'Edit...'
button
I paste the query from Query Analyzer in the field
and
click 'OK'
Click Finish
Back in the Data Sourse Catalog Window the
Connection
now
appears.
I click on the drop down arrow next to the
connection
and
choose show data.
The Data View Detals window shows the following
message.
"The Data Source does not contain valid XML data."
"The administration settings for this Web site do
not
allow access to databases by using custom command
strings. Use the Microsoft Windows SharePoint
Services
Central Administration page to enable update query
support in data retrieval services, or contact your
Web
site administrator for more information."

FYI the query I am pasting in the edit command
string
box
is a simple select query query not an update query.

So I make the change requested above and delete the
conection close out FP2003 and reset IIS.

Go back to FP2003 and repeat the above steps and
this
time when I do 'Show Data' I get the following
message:
"The Data Source does not contain valid XML data."
"The server for the data source returned a non-
specific
error when trying to execute your query. Check the
format and content of your query and try again."

So I am thinking the box where I am pasting in the
query
is not expecting a regular SQL expression, but
rather
some XML or XSL code or something.

Any ideas, or a way you can try to re-create?


-----Original Message-----
It sounds like you are having a proxy or permisions
issue.

First, try it without using a custom query, can you
just
connect to the
database and see the list of available tables and
what
not? If so, then it
is probably a proxy issue.

This was posted in the beta newsgroups:

Add

<system.net>
<defaultProxy>
<proxy proxyaddress="http://proxyServer"/>
</defaultProxy>
</system.net>

to the c:\program files\common files\microsoft
shared\web server
extensions\60\config\web.config file on the server.
Just above the closing
</configuration> tag.

http://proxyServer (I think obviously) should be
the
same proxy server as in
your Internet Options > Connections tab.

-John


"scmiles" <[email protected]>
wrote
in
message
Hello, I am trying to figure out the custom query
portion
of inserting a data view but can not for the
life of
me.
I am inserting the exact query as you would in
SQL
Query
Analyzer, and it works fine over there. However
after
I
insert the query and then try to view the data,
FP2003
displays a message in the data view details
window
that
says "The Data Source does not contain valid XML
data."

If I just use the wizard to pick the columns I
want, it
all works fine, except what I am trying to do is
only
diaplay rows that contain a column that equals
the
current logged on user's username.

Any FP2003 users out there that can help?


.



.



.

.