Offhand, I'm thinking the problem is the use of the @Counter variable in the recursive part of the CTE query. Maybe something like this instead:
with cte as (
SELECT @Counter as c,ASSET_NUM,[SHAPE].STPointN(@Counter).STAsText() as Points
FROM [dbo].[GEOMETRY_TABLE]
where ASSET_NUM = 9782654
union all
SELECT cte.C+1,t.ASSET_NUM,t.[SHAPE].STPointN(cte.C+1).STAsText()
FROM cte inner join [dbo].[GEOMETRY_TABLE] t
on cte.ASSET_NUM = t.ASSET_NUM and t.ASSET_NUM= 9782654
where cte.C < 5
)
Or avoid recursion altogether and use a tally table to pass into STPointN. If you don't have a tally table, you could improvise a small one on the fly here like:
WITH Tally AS
(SELECT N FROM (VALUES (1),(2),(3),(4),(5)) AS list (N))
SELECT t.N AS c, g.ASSET_NUM, g.[SHAPE].STPointN(t.N).STAsText() AS Points
FROM [dbo].[GEOMETRY_TABLE] g
CROSS JOIN Tally t
WHERE g.ASSET_NUM = 9782654