Limit of 8000

Question

Limit of 8000

Usman Tanveer

SSC Journeyman

Points: 94
More actions
April 6, 2005 at 6:04 am

#362320

I have to make a dynamic query using the record sets of the cursor.
The problem is size of the Dynamic Query goes beyond 8000.
Can some one please tell me a solution.
Here is the sample code
DECLARE PointName_Cur CURSOR FOR
SELECT B.PointIndexId, B.PointName, B.ConfigTableName, B.ServerId
FROM IPM_Historian_EqpItmM2MPoints A, IPM_Historian_PointIndex B
WHERE A.EquipmentItemId = @EquipmentItemId
AND A.PointIndexId = B.PointIndexId
AND B.DigitalFlag = 0
AND B.DeletedFlag = 0
OPEN PointName_Cur
FETCH NEXT FROM PointName_Cur INTO
@PointIndexId, @PointName, @ConfigTableName, @ServerId

SET @Query = ''
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Query <> ''
   SET @Query = @Query + ' UNION '
  SET @Query = @Query + 'SELECT ISNULL(PointAlias,'''') AS PointAlias, ISNULL(Unit,'''') AS Unit,'+CAST(@PointIndexId AS VARCHAR)+' AS PointIndexId,'''+@PointName+''' AS PointName,'+CAST(@EquipmentItemId AS VARCHAR)+' AS EquipmentItemId FROM '+@ConfigTableName
  SET @Query = @Query + ' WHERE PointName = '''+@PointName+''' AND ServerId = '+CAST(@ServerId AS VARCHAR)
  FETCH NEXT FROM PointName_Cur INTO
  @PointIndexId, @PointName, @ConfigTableName, @ServerId
  print @Query
  print len (@Query)
END
CLOSE PointName_Cur
DEALLOCATE PointName_Cur
IF @Query IS NOT NULL
  EXECUTE (@Query)
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What exactly r u trying to accomplish in this stored proc?... I didn't see any reason to validate the use of a cursor in this case.

Can you post the whole script and the task at hand.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

http://www.sommarskog.se/dynamic_sql.html

Erland also discusses there the use of both dynamic sql and cursors. Worth reading.

Now, if you don't mind something undocumented, have a look here:

http://www.rac4sql.net/xp_execresultset.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Usman Tanveer SSC Journeyman Points: 94 More actions · Answer 3

There are different configuration tables, which contains points.

There are different Equipments and points are associated with Equipments.

There is an Index Table which contains the Point Name with corresponding Configtable Name.

Now the Cursor Gets the Information of the Points of an Equipment from the Index Table.

And the Dynamic Query is build to get the information of the Point from its corresponding ConfigTable

Now the problem is size of the Dynamic Query gets more than 8000

noeld SSC Guru Points: 96590 More actions · Answer 4

I still don't think you need a cursor and I would need DDL to write a set based solution. But to answer your question you can get around that limitation by using several variables like:

exec (@var1 + @var2 + @var3)

Remember: the size may be very large but not infinite

hth

* Noel

dongadoy SSC Eights! Points: 880 More actions · Answer 5

Your dynamic sql is a series of UNIONs. An alternative is to use a temp table. For each row of the cursor, execute sql to insert to the temp table. At the end of the procedure, use SELECT DISTINCT from the temp table.

Of course the best option is to design the database so you don't need dynamic sql. Storing table names (ConfigTableName) in the data is the problem.