December 14, 2006 at 4:08 pm
Hi,
I am gettinng following error when I ran this sp:
The number name 'mwltmw.MWL.DBO.RM20201' contains more than the maximum number of prefixes. The maximum is 3.
For your information, my stroe proc in one server and table 'DM20201' and 'PM20501' in another server.
Here is my store proc:
CREATE PROCEDURE debug_test @startdate nvarchar(30), @enddate NVARCHAR(30), @ordercompanyid NVARCHAR(10), @orderbilltoid NVARCHAR(100), @ordernumber NVARCHAR(21) AS
DECLARE @mytable VARCHAR(30)
DECLARE @pos INT
SET @mytable = CONVERT(CHAR(12), GETDATE(), 14)
SET @pos = CHARINDEX(':', @mytable)
WHILE @pos > 0
BEGIN
SET @mytable = SUBSTRING(@mytable, 1, @pos - 1) + SUBSTRING(@mytable, @pos + 1, 30 - @pos)
SET @pos = CHARINDEX(':', @mytable)
END
IF @startdate IS NOT NULL AND @enddate IS NOT NULL
BEGIN
SET @startdate = dbo.fn_FindDateTime(@startdate, 'S', 'D', 0)
SET @enddate = dbo.fn_FindDateTime(@enddate, 'E', 'D', 0)
END
ELSE IF @startdate IS NOT NULL AND @enddate IS NULL
BEGIN
SET @enddate = dbo.fn_FindDateTime(@startdate, 'E', 'D', 0)
END
IF @startdate IS NULL AND @enddate IS NOT NULL
BEGIN
SET @enddate = NULL
END
DECLARE @sql VARCHAR(8000)
DECLARE @sql2 VARCHAR(8000)
DECLARE @writeoff INT
DECLARE @switch INT
SET @writeoff = 10
SET @switch = 1
DECLARE @gpdb NVARCHAR(20)
IF @ordercompanyid = 'MWLT'
SET @gpdb = 'mwltmw.MWL.DBO.'
ELSE IF @ordercompanyid = 'MWIR'
SET @gpdb = 'mwltmw.MWI.DBO.'
ELSE IF @ordercompanyid = 'MWBL'
SET @gpdb = 'mwltmw.MWB.DBO.'
---------------------------------------------------------------------------------
--
--
--THIS IS THE BEGINNING OF GATHERING INVOICES WITH PAYMENTS APPLIED
--
--
---------------------------------------------------------------------------------
--GET INVOICE DETAILS
SET @sql = 'CREATE TABLE ##rmf' + @mytable + ' ( '
SET @sql = @sql + '[APTODCNM] char(21), '
SET @sql = @sql + '[APFRDCNM] char(21) '
EXEC(@sql)
SET @switch = 1
SET @sql = 'INSERT INTO ##rmf' + @mytable + ' SELECT '
--SET @sql = @sql + '' + @gpdb + 'DM20201.APTODCNM, '
SET @sql = @sql + 'APTODCNM, '
--SET @sql = @sql + '' + @gpdb + 'DM20201.APFRDCNM '
SET @sql = @sql + 'from ' + @gpdb + 'DM20201 '
SET @sql = @sql + 'WHERE (APFRDCNM NOT LIKE ''CREDT%'') '
SET @sql = @sql + 'AND (APFRDCNM NOT LIKE ''PYMNT%'') '
SET @sql = @sql + 'AND (APFRDCNM NOT LIKE ''WCREDT%'') '
SET @sql = @sql + 'AND ((SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'RM20201.APFRDCNM) > 0 OR (SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'DM20201.APTODCNM) > 0) '
EXEC(@sql)
SET @switch = 1
GO
Please help.
Thanks,
Monir
December 14, 2006 at 8:49 pm
Change the EXEC(@SQL) to PRINT @sql and debug that... that will lead you back to the problem in your dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2006 at 7:43 am
Hi Jeff,
Thanks for your reply. I believe the problem is in the following line of code:
SET @sql = @sql + 'AND ((SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'RM20201.APFRDCNM) > 0 OR (SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'DM20201.APTODCNM) > 0) '
If I comment out this line, sp executes fine. If I modify the code to the following line,
SET @sql = @sql + 'AND ((SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = APFRDCNM) > 0 OR (SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = APTODCNM) > 0) '
then I get the error below:
Statement(s) could not be prepared.
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
December 15, 2006 at 8:54 am
what is the value of @gpdb...
SELECT count(a.DOCNUMBR) from gpdb PM20501 a
here you see that this variable should have no meaning for the query.. can you post the extact value for this field or print the query rather then Dyanamic String. It would realy give you as well us the clear picture of problme
cheers
December 15, 2006 at 9:11 am
the value of gpdb is mwltmw.mwl.dbo.RM20201(server name.databasename.dbo.tablename). The problem may arise while I am trying to insert into a table. For example, if I execute the following query, it executes fine.
SELECT APTODCNM, APFRDCNM
from mwltmw.MWI.DBO.RM20201
WHERE (APFRDCNM NOT LIKE 'CREDT%')
AND (APFRDCNM NOT LIKE 'PYMNT%')
AND (APFRDCNM NOT LIKE 'WCREDT%')
and ((select count(a.DOCNUMBR) from mwltmw.MWI.DBO.RM20101 a where a.DOCNUMBR = APFRDCNM) > 0
or
(select count(a.DOCNUMBR) from mwltmw.MWI.DBO.RM20101 a where a.DOCNUMBR = APTODCNM) > 0)
But when I add,
CREATE TABLE ##rmf(
[APTODCNM] char(21),
[APFRDCNM] char(21)
)
INSERT INTO ##rmf
SELECT APTODCNM, APFRDCNM
from mwltmw.MWI.DBO.RM20201
WHERE (APFRDCNM NOT LIKE 'CREDT%')
AND (APFRDCNM NOT LIKE 'PYMNT%')
AND (APFRDCNM NOT LIKE 'WCREDT%')
and ((select count(a.DOCNUMBR) from mwltmw.MWI.DBO.RM20101 a where a.DOCNUMBR = APFRDCNM) > 0
or
(select count(a.DOCNUMBR) from mwltmw.MWI.DBO.RM20101 a where a.DOCNUMBR = APTODCNM) > 0)
I get the following error:
Statement(s) could not be prepared. The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Hope it helps. Any suggestions.
Thanks,
Monir
December 15, 2006 at 9:26 am
I think you should passed only mwltmw.mwl.dbo.
If the value of this is mwltmw.mwl.dbo.RM20201, then why you are using in your query the table name once again.
SET @sql = @sql + 'AND ((SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'RM20201.APFRDCNM) > 0 OR (SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'DM20201.APTODCNM) > 0) '
Please follow the bellow step and give the output of your query.
1. After the above statement write : PRINT @sql
2. RETURN 0
3. Run the procedure in query analyser with your parameters.
4. Copy the Printed result in the post and let check the exact problem
Cheers
cheers
December 15, 2006 at 9:52 am
I followed your your steps and I get the following printed result:
INSERT INTO ##rmf103650513 SELECT APTODCNM, APFRDCNM
from mwltmw.MWI.DBO.RM20201
WHERE (APFRDCNM NOT LIKE 'CREDT%') AND (APFRDCNM NOT LIKE 'PYMNT%') AND (APFRDCNM NOT LIKE 'WCREDT%')
AND ((SELECT count(a.DOCNUMBR) from mwltmw.MWI.DBO.RM20101 a where a.DOCNUMBR = mwltmw.MWI.DBO.RM20201.APFRDCNM) > 0
OR
(SELECT count(a.DOCNUMBR) from mwltmw.MWI.DBO.RM20101 a where a.DOCNUMBR = mwltmw.MWI.DBO.RM20201.APTODCNM) > 0)
Please be aware that table name should be RM20101 instead of PM20501.
Also, I wanted to know why I am getting, 'Statement(s) could not be prepared. The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.' when I tried to insert the records into the temp table. Because, when I do not insert into the table, it executes fine. May be this is the part of the problem.
Thanks,
Monir
December 15, 2006 at 10:21 am
Replacing:
SET @sql = @sql + 'AND ((SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'RM20201.APFRDCNM) > 0 OR (SELECT count(a.DOCNUMBR) from ' + @gpdb + 'PM20501 a where a.DOCNUMBR = ' + @gpdb + 'DM20201.APTODCNM) > 0) '
with:
SET @sql = @sql + 'AND ((SELECT count(a.DOCNUMBR) from ' + @gpdb + 'RM20201 a where a.DOCNUMBR = ' a.APFRDCNM) > 0 OR (SELECT count(a.DOCNUMBR) from ' + @gpdb + 'RM20201 a where a.DOCNUMBR = ' a.APTODCNM) > 0) '
should do the trick, if I'm understanding what you're trying to accomplish.
December 15, 2006 at 10:46 am
Thanks for your suggestion. After making few adjustments, it works but only when I don't insert into a table. For example, it gives me error when I add the following line to my original query.
SET @sql = 'INSERT INTO ##rmf SELECT '
Error msg is:
Server: Msg 8180, Level 16, State 1, Line 4
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 4
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 4
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
But if I use only, SET @sql = ' SELECT ' it works. Would you tell me why it's behaving like this.
Thanks,
Monir
December 15, 2006 at 12:10 pm
The problem is that you are using a five part name to reference a column in a table that you've already aliased, specifically "mwltmw.MWI.DBO.RM20201.APFRDCNM" and "mwltmw.MWI.DBO.RM20201.APTODCNM".
In fact, after looking at this more closely, even my suggestion won't work, as I assumed that the difference between the 20201 and 20101 was a typo as was the 20501 in the table name. If RM20201 and RM20101 are indeed different tables, then the where clauses need to be completely revisited. It almost looks like that five part reference is supposed to return that column for all rows in a different table. It won't work with that syntax at all.
Let us know specifically what you are trying to do and we can probably get through this, but my hunch is that we'll need to replace those five part names with a subquery (and an IN or EXISTS instead of =) or a joined table.
December 15, 2006 at 1:52 pm
Yes, you are correct. RM20201 and RM20101 are two different tables. I also agree with you that we need to replace the five part names with a subquery (IN or EXIST instead of = or join table).
I am trying to get APTODCNM and APFRDCNM column data from RM20201 table only if APTODCNM column value in RM20201 table matches with DOCNUMBR column value in RM20101 table and APFRCDNM column in RM20201 table matches with DOCNUMBR column values in RM20101 table.
Please keep in mind that I am executing the stor proc from MWLDB02 server but tables RM20201 and RM20101 resides in mwltmw server.
Would you also send me the subquery that replaces existing 5 part names with IN or EXISTS or join table instead of =.
I really appreciate you hwlp.
Thanks,
Monir
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply