| Author |
 Topic  |
|
|
bitwise2000
Starting Member
38 Posts |
 Posted - 13 March 2006 : 00:01:49
|
Migrating Access to MS SQL, down to my last issue or two.
I use the query below to generate an RSS feed which contains the last 10 postings, be they topics or replies, newest first. In the feed I use the Topic or Reply ID (to make a trackback link), Author, Timestamp, Subject, Forum, and the Message.
It works on Access, not on SQL.
sql = "SELECT TOP " & intResults & " * FROM [" &_
"SELECT TOP " & intResults & " * FROM " &_
"(SELECT R.Topic_ID AS Topic, R.Reply_ID AS Reply, M.M_Name AS Author, R.R_Date AS Post_Date,T.T_Subject AS Subject, F.F_Subject AS Forum, R.R_Message as Message " &_
"FROM (((Forum_Reply R INNER JOIN Forum_Topics T ON R.Topic_ID=T.Topic_ID) INNER JOIN Forum_Members M ON R.R_Author=M.Member_ID) INNER JOIN Forum_Forum F ON R.Forum_ID=F.Forum_ID) " &_
"WHERE R.R_STATUS < 2 AND R.Forum_ID IN (SELECT Forum_Forum.Forum_ID FROM Forum_Forum WHERE F_PrivateForums=0) ORDER BY R.R_Date DESC) AS Replies " &_
"UNION " &_
"SELECT TOP " & intResults & " * FROM " &_
"(SELECT T.Topic_ID AS Topic, Null AS Reply, M.M_Name AS Author, T.T_Date AS Post_Date, T.T_Subject AS Subject, F.F_Subject AS Forum, T.T_Message as Message " &_
"FROM ((Forum_Topics T INNER JOIN Forum_Members M ON T.T_Author=M.Member_ID) INNER JOIN Forum_Forum F ON T.Forum_ID=F.Forum_ID) " &_
"WHERE T.T_STATUS < 2 AND T.Forum_ID IN (SELECT Forum_Forum.Forum_ID FROM Forum_Forum WHERE F_PrivateForums=0) ORDER BY T.T_Date DESC) AS Topics " &_
"ORDER BY Post_Date DESC]. AS Q1;
The error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The identifier that starts with 'SELECT TOP 10 * FROM (SELECT R.Topic_ID AS Topic, R.Reply_ID AS Reply, M.M_Name AS Author, R.R_Date AS Post_Date,T.T_Subject AS ' is too long. Maximum length is 128.
I've deconstructed that long query into just the arguments of the union, and it works. I can pull from either the topics or replies table. I know that I have to add TOP to the Select because of the order by.
A) Is there a glaring MSSQL-specific error in that query?
B) Is there a better way with MSSQL to do what I'm trying to do? That long query was the only way I could get what I wanted on Access with one query. Not too efficient, I know.
Thanks - Jeff |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 March 2006 : 01:01:07
|
A simple way to reduce query length is to remove the TOP clauses from each of the UNION subqueries. They are not needed:
SELECT TOP " & intResults & " * FROM (" &_
"SELECT R.Topic_ID AS Topic, R.Reply_ID AS Reply, M.M_Name AS Author, R.R_Date AS Post_Date,T.T_Subject AS Subject, F.F_Subject AS Forum, R.R_Message as Message " &_
"FROM (((Forum_Reply R INNER JOIN Forum_Topics T ON R.Topic_ID=T.Topic_ID) INNER JOIN Forum_Members M ON R.R_Author=M.Member_ID) INNER JOIN Forum_Forum F ON R.Forum_ID=F.Forum_ID) " &_
"WHERE R.R_STATUS < 2 AND R.Forum_ID IN (SELECT Forum_Forum.Forum_ID FROM Forum_Forum WHERE F_PrivateForums=0) ORDER BY R.R_Date DESC " &_
"UNION " &_
"SELECT T.Topic_ID AS Topic, Null AS Reply, M.M_Name AS Author, T.T_Date AS Post_Date, T.T_Subject AS Subject, F.F_Subject AS Forum, T.T_Message as Message " &_
"FROM ((Forum_Topics T INNER JOIN Forum_Members M ON T.T_Author=M.Member_ID) INNER JOIN Forum_Forum F ON T.Forum_ID=F.Forum_ID) " &_
"WHERE T.T_STATUS < 2 AND T.Forum_ID IN (SELECT Forum_Forum.Forum_ID FROM Forum_Forum WHERE F_PrivateForums=0) ORDER BY T.T_Date DESC) As Q1" &_
"ORDER BY Post_Date DESC;
Can't be completely sure about the syntax, since I haven't tested it. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
hsalim
Starting Member
USA
1 Posts |
Posted - 22 March 2006 : 19:18:51
|
if you remove the square brackets "[]" I expect that it will run fine on SQL Server. SQL server is looking for a table name when it sees the square bracket, hence the limitation of 128 chars.
the limit on Query size is 32K and I think you are well within that
so no need to limit the size of the query.
Having said that, what you PROBABLY NEED is the query posted below
(I dont have the database to test the query but I think you'll be OK)
It returns the newest 10 topics and every reply they received.
Regards
HS
--------------------
select * from
( SELECT TOP 10
T.Topic_ID Topic,
Null Reply,
M.M_Name Author,
T.T_Date Post_Date,
T.T_Subject Subject,
F.F_Subject Forum,
T.T_Message Message
FROM (
(Forum_Topics T
INNER JOIN Forum_Members M
ON T.T_Author=M.Member_ID)
INNER JOIN Forum_Forum F
ON T.Forum_ID=F.Forum_ID)
WHERE T.T_STATUS < 2
AND F_PrivateForums=0
ORDER BY T.T_Date DESC) as Topics
union all
( SELECT
R.Topic_ID Topic,
R.Reply_ID Reply,
M.M_Name Author,
R.R_Date Post_Date,
T.T_Subject Subject,
F.F_Subject Forum,
R.R_Message Message
FROM (
(
(Forum_Reply R
INNER JOIN (Select Top 10 Topic_ID Forum_Topics order by T_Date desc) T
ON R.Topic_ID=T.Topic_ID)
INNER JOIN Forum_Members M
ON R.R_Author=M.Member_ID)
INNER JOIN Forum_Forum F
ON R.Forum_ID=F.Forum_ID)
WHERE R.R_STATUS < 2
AND F_PrivateForums=0
ORDER BY R.R_Date DESC) AS Replies
|
|
|
|
bitwise2000
Starting Member
38 Posts |
Posted - 28 March 2006 : 23:37:29
|
Here's how it ended up. I put it in a stored proc and it works fine.
Thanks for the help and the ideas.
SELECT TOP 10 * FROM
(SELECT R.Topic_ID AS Topic, R.Reply_ID AS Reply,
M.M_Name AS Author, R.R_Date AS Post_Date,
T.T_Subject AS Subject, F.F_Subject AS Forum,
R.R_Message as Message
FROM (((Forum_Reply R
INNER JOIN Forum_Topics T ON R.Topic_ID=T.Topic_ID)
INNER JOIN Forum_Members M ON R.R_Author=M.Member_ID)
INNER JOIN Forum_Forum F ON R.Forum_ID=F.Forum_ID)
WHERE R.R_STATUS < 2 AND R.Forum_ID
IN (SELECT Forum_Forum.Forum_ID FROM Forum_Forum
WHERE F_PrivateForums=0)
UNION ALL
SELECT T.Topic_ID AS Topic, Null AS Reply,
M.M_Name AS Author, T.T_Date AS Post_Date,
T.T_Subject AS Subject, F.F_Subject AS Forum,
T.T_Message as Message
FROM ((Forum_Topics T INNER JOIN Forum_Members M ON T.T_Author=M.Member_ID) INNER JOIN Forum_Forum F ON T.Forum_ID=F.Forum_ID)
WHERE T.T_STATUS < 2 AND T.Forum_ID
IN (SELECT Forum_Forum.Forum_ID FROM Forum_Forum
WHERE F_PrivateForums=0))
AS Q1
ORDER BY Post_Date DESC |
|
|
| |
Topic |
|
|
|
Topic Locked
