Having trouble getting SP from sql Server 2005 to work in SQL Server 2000

I am getting an error saying incorrect syntax near f

It works in SQL Server 2005, but I cannot get it to work in SQL Server 2000

The error appears to be in the section that I marked in Bold.

CREATE PROCEDURE [dbo].[pe_getReport]
-- Add the parameters for the stored procedure here
@.BranchID INT,
@.InvestorID INT,
@.Status INT,
@.QCAssigned INT,
@.LoanOfficer nvarChar(40),
@.FromCloseDate DateTime,
@.ToCloseDate DateTime,
@.OrderBy nvarChar(50)
AS
DECLARE
@.l_Sql NVarChar(4000),
@.l_OrderBy NVarChar(500),
@.l_OrderCol NVarChar(150),
@.l_CountSql NVarChar(4000),
@.l_Where NVarChar(4000),
@.l_SortDir nvarChar(4)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SET @.l_Where = N' Where 1=1'

IF (@.BranchID IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.BranchID=' + CAST(@.BranchID As NVarChar)

IF (@.Status IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.Status=' + CAST(@.Status As NVarChar)

IF (@.InvestorID IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.InvestorID=' + CAST(@.InvestorID As NVarChar)

IF (@.QCAssigned IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.QCAssigned=' + CAST(@.QCAssigned As NVarChar)

IF (@.LoanOfficer IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.LoanOfficer LIKE ''' + @.LoanOfficer + '%'''

IF (@.FromCloseDate IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.ClosingDate>=''' + CAST(@.FromCloseDate AS NVarChar) + ''''

IF (@.ToCloseDate IS NOT NULL)
SET @.l_Where = @.l_Where + N' AND f.ClosingDate<=''' + CAST(@.ToCloseDate AS NVarChar) + ''''

IF @.OrderBy IS NULL
SET @.OrderBy = 'DateEntered DESC'

SET @.l_SortDir = SUBSTRING(@.OrderBy, CHARINDEX(' ', @.OrderBy) + 1, LEN(@.OrderBy))
SET @.l_OrderCol = SUBSTRING(@.OrderBy, 1, NULLIF(CHARINDEX(' ', @.OrderBy) - 1, -1))
IF @.l_OrderCol = 'InvestorName'
SET @.l_OrderBy = 'i.InvestorName ' + @.l_SortDir
ELSE IF @.l_OrderCol = 'BName'
SET @.l_OrderBy = 'b.BName ' + @.l_SortDir
ELSE IF @.l_OrderCol = 'StatusDesc'
SET @.l_OrderBy = 's.StatusDesc ' + @.l_SortDir
ELSE IF @.l_OrderCol = 'QCAssigned'
SET @.l_OrderBy = 'q.LoginName ' + @.l_SortDir
ELSE SET @.l_OrderBy = 'f.' + @.l_OrderCol + ' ' + @.l_SortDir

SET @.l_CountSql = 'SELECT f.FundedID As FundedID FROM FundedInfo AS f LEFT OUTER JOIN
Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN
Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN
Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN
QCLogins AS q f.QCAssigned = q.LoginID '
+ @.l_Where + ' ORDER BY ' + @.l_OrderBy

CREATE TABLE #RsltTable (ID int IDENTITY PRIMARY KEY, FundedID int)
INSERT INTO #RsltTable(FundedID)
EXECUTE (@.l_CountSql)

SELECT f.DateEntered As DateEntered, f.LastName As LastName, f.LoanNumber As LoanNumber,
f.LoanOfficer As LoanOfficer, f.ClosingDate As ClosingDate,
i.InvestorName As InvestorName, b.BName As BName, s.StatusDesc As StatusDesc,
q.LoginName As LoginName
FROM
FundedInfo AS f LEFT OUTER JOIN
Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN
Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN
Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN
QCLogins As q ON f.QCAssigned = q.LoginID
WHERE FundedID IN(SELECT FundedID FROM #rsltTable)
ORDER BY
CASE @.OrderBy WHEN 'DateEntered ASC' THEN f.DateEntered END ASC,
CASE @.OrderBy WHEN 'DateEntered DESC' THEN f.DateEntered END DESC,
CASE @.OrderBy WHEN 'LastName ASC' THEN f.LastName END ASC,
CASE @.OrderBy WHEN 'LastName DESC' THEN f.LastName END DESC,
CASE @.OrderBy WHEN 'LoanNumber ASC' THEN f.LoanNumber END ASC,
CASE @.OrderBy WHEN 'LoanNumber DESC' THEN f.LoanNumber END DESC,
CASE @.OrderBy WHEN 'LoanOfficer ASC' THEN f.LoanOfficer END ASC,
CASE @.OrderBy WHEN 'LoanOfficer DESC' THEN f.LoanOfficer END DESC,
CASE @.OrderBy WHEN 'ClosingDate ASC' THEN f.ClosingDate END ASC,
CASE @.OrderBy WHEN 'ClosingDate DESC' THEN f.ClosingDate END DESC,
CASE @.OrderBy WHEN 'InvestorName ASC' THEN i.InvestorName END ASC,
CASE @.OrderBy WHEN 'InvestorName DESC' THEN i.InvestorName END DESC,
CASE @.OrderBy WHEN 'BName ASC' THEN b.BName END ASC,
CASE @.OrderBy WHEN 'BName DESC' THEN b.BName END DESC,
CASE @.OrderBy WHEN 'StatusDesc ASC' THEN s.StatusDesc END ASC,
CASE @.OrderBy WHEN 'StatusDesc DESC' THEN s.StatusDesc END DESC,
CASE @.OrderBy WHEN 'LoginName ASC' THEN q.LoginName END ASC,
CASE @.OrderBy WHEN 'LoginName DESC' THEN q.LoginName END DESC
END
GO

Do a PRINT @.l_CountSql before you EXEC the SQL. That can help you debug the SQL Statement that is being formed.|||Thanks, I am blind... I was missing the 'ON' in the last LEFT OUTER JOIN

Has anyone ever seen this error before?

A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> >A google search turned up nothing when I searched for this.
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documented
> in the SQL Server Books Online.
> > Is it saying that if my total VARCHARs for all columns in a row add up
> > to more than 8060, then it can't do it?
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If you
> later try to insert a row that requires more than 8060 bytes of actual data,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>

Has anyone ever seen this error before?

A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.

> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documente
d
> in the SQL Server Books Online.
>
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If yo
u
> later try to insert a row that requires more than 8060 bytes of actual dat
a,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>

Handling database connection errors

If my database goes down, i get a popup error with the red x saying can't
connect to database, can't find the cubes, or the cubes are busy, etc...
Is there a way to change the error message to say:
"Report Cubes are being refreshed right now, please try again" in the
popup.
Or point to another page?No this is not possible, unless you implement your own custom data extension
which wraps the original data provider and throws specific exceptions with
your custom error message (which will then show up as inner error message).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:OfpbodJ3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> If my database goes down, i get a popup error with the red x saying can't
> connect to database, can't find the cubes, or the cubes are busy, etc...
> Is there a way to change the error message to say:
> "Report Cubes are being refreshed right now, please try again" in the
> popup.
> Or point to another page?
>|||Is this an IIS thing?
Easy to do?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OxvRcpP3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> No this is not possible, unless you implement your own custom data
extension
> which wraps the original data provider and throws specific exceptions with
> your custom error message (which will then show up as inner error
message).
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:OfpbodJ3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > If my database goes down, i get a popup error with the red x saying
can't
> > connect to database, can't find the cubes, or the cubes are busy,
etc...
> >
> > Is there a way to change the error message to say:
> >
> > "Report Cubes are being refreshed right now, please try again" in the
> > popup.
> >
> > Or point to another page?
> >
> >
>