Re: sql statement for a DataView by John
John
Mon Aug 16 12:05:29 CDT 2004
For some reason I can't read the SQL statement that is giving you trouble,
but I can read the one that is not.
--
Thanks!
John Jansen
Microsoft Office FrontPage
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pax" <?andrei###?###popovici?@yahoo.com> wrote in message
news:OV3uLs3gEHA.1048@tk2msftngp13.phx.gbl...
>
> Hi, John!
> I guess the things you are talking about are quite all right. They were
> already set, as follows:
> Enabled data retrieval services:
> -Windows SharePoint Services
> -OLEDB
> -SOAP Passthrough
> -XML-URL
> Enabled update query support for:
> -OLEDB
>
>
> OK now, regarding the complexity:
> 1) the following statement works just fine:
> SELECT * FROM STS_MyServer_1605625003.dbo.Docs WHERE CONTAINS(content,
> '"ss*"')
> 2) the large statement at the end of this post also works fine in Query
> Analyzer. You can try it by replacing STS_MyServer_1605625003 with the
> name
> of a content database on any WSS server, it's just a select, nothing
> dangerous. But if I am to paste this statement in Data Source
> Properties ->
> Edit Custom Query, then bang!
>
> The strange thing is that it doesn't reach the SQL Server. The message
> ("The
> data source does not contain valid XML data...") could indicate that the
> string itself, in the context of an XML document (as the data source info
> is
> stored), is invalid. I don't know... I've checked it also by navigating in
> the site structure (_fpdatasources/fpdatasources/MyConnection.xml) and the
> browser said the document is OK; I can see the statement,
> copy/paste/execute
> it in QA, the authentication info is correctly stored in the
> connectionString element.
>
> Thanks for your time and hope to hear some more from you.
>
>
>
>
> Pax
>
>
>
>
>
>
>
> "John Jansen (MSFT)" <johnjan@online.microsoft.com> wrote in message
> news:eElWdUJgEHA.1188@TK2MSFTNGP11.phx.gbl...
>> Nope, FrontPage doesn't do anything of consequence to your SQL statement,
> so
>> the complexity shouldn't matter.
>>
>> What does matter is that your server allows passthrough queries (have you
>> tried using a simple SQL statement, like "Select * from foo"?).
>> Obviously
>> there is some risk in allowing sql queries, so this needs to be done from
>> the Server Admin Pages.
>>
>> 1.. Browse to the Windows SharePoint Services Central Administration
> page.
>> 2.. Under Component Configuration click on Configure data retrieval
>> service settings.
>> 3.. Check the Enable Data Retrieval Services checkbox.
>> 4.. Check the Enable update query support checkbox.
>> 5.. Click OK.
>>
>> --
>> Thanks!
>> John Jansen
>> Microsoft Office FrontPage
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Pax" <?andrei###?###popovici?@yahoo.com> wrote in message
>> news:%23VO27%23tfEHA.2896@TK2MSFTNGP11.phx.gbl...
>> > Hi, everybody!
>> > Environment: Windows Server 2003, Windows Sharepoint Services, SQL
> Server
>> > 2000, FP2003
>> > The question I have regards the complexity of the SQL statement the
>> > DataView
>> > webpart understands. Are there any additional requirements on this
>> > statement? Take for example quite a complex statement like the one
> above.
>> > I've got it by modifying the main part of the
>> > proc_FetchDocSearchResults
>> > stored procedure in one of my content databases. (I'm trying to build a
>> > search engine for the portal and I thought I could start the
> investigation
>> > the easy way :-) The server has no problem in returning the set, but
>> > FP2003
>> > chokes ("The data source does not contain valid XML data. The server
>> > for
>> > the
>> > data source returned a non-specific error when..." ). Should the
> statement
>> > be XML returning (for xml or something)? By launching a trace I can see
>> > that
>> > it doesn't even reach the SQL Server.
>> >
>> > Anybody... suggestions, hints, anything?
>> > Thanks in advance.
>> >
>> >
>> > Pax
>> > ******************************************
>> > SELECT
>> > NULL as c1,
>> > NULL as c2,
>> > NULL as c3,
>> > Docs.DirName,
>> > Docs.LeafName,
>> > Docs.DocLibRowId,
>> > Docs.ListId,
>> > Lists.tp_Title as Lists_tp_Title,
>> > UserData.tp_Author,
>> > UserData.tp_Editor,
>> > UserInfo.tp_Title as UserInfo_tp_Title,
>> > Docs.TimeLastModified AS LastModified,
>> > Docs.[Size],
>> > Docs.Id AS ItemId,
>> > Docs#CT.Rank AS CT#Rank,
>> > CASE WHEN (Docs.Type = 1)
>> > THEN 3
>> > ELSE 2
>> > END as c4
>> > FROM
>> > STS_MyServer_1605625003..Docs
>> > INNER JOIN
>> > CONTAINSTABLE (STS_MyServer_1605625003..Docs, *, '"ss*"') AS Docs#CT
>> > ON
>> > Docs#CT.[Key] = Docs.ID
>> > LEFT OUTER JOIN
>> > STS_MyServer_1605625003..Lists
>> > ON
>> > Docs.ListId = Lists.tp_ID
>> > LEFT OUTER JOIN
>> > STS_MyServer_1605625003..UserData
>> > ON
>> > (Docs.ListId = UserData.tp_ListId) AND
>> > (Docs.DocLibRowId = UserData.tp_ID)
>> > LEFT OUTER JOIN
>> > STS_MyServer_1605625003..UserInfo
>> > ON
>> > (UserData.tp_Editor = UserInfo.tp_ID) AND
>> > (UserData.tp_SiteId = UserInfo.tp_SiteID)
>> > WHERE
>> > ((Lists.tp_Flags IS NULL) OR
>> > ((Lists.tp_Flags & 0x400) = 0) OR
>> > (UserData.tp_ModerationStatus IS NULL) OR
>> > (UserData.tp_ModerationStatus = 0)) AND
>> > ((Docs.Type = 0) OR
>> > ((Docs.Type = 1) AND
>> > (Docs.DoclibRowId IS NOT NULL)))
>> > ORDER BY
>> > CT#Rank DESC,
>> > LastModified DESC,
>> > ItemId ASC
>> > ******************************************
>> >
>> >
>> >
>> >
>>
>>
>
>