Results 1 to 4 of 4

Thread: SQL temp table woos

  1. #1
    Roman P.. Guest

    SQL temp table woos


    Hello,

    We have a 6.5 SQL DB, when I run a query from ISQL/w the error appears...

    Msg 1105, Level 17, State 1
    Can't allocate space for object '-386' in database 'tempdb' because the 'system' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
    Msg 21, Level 20, State 1
    WARNING - Fatal Error 1510 occurred at Aug 1 2001 7:18PM. Please note the error and time, and contact your System Administrator.

    I am at my wits end to figure this out.

    All help would be appreciated.

  2. #2
    IVO Guest

    SQL temp table woos (reply)

    Reason for this error is that your query creates too large temporary table, or cursor. Solution is to expand size of tempdb, or to rewrite query so that you will not use large temporary objects (for example you'd use permanent table in your database instead of temporary.)
    Fatal Error 1510 is clearly descripted in SQL Books Online and is about creating an index - do you create index on a temporary table ?
    For better advice you should insert here your query, or its important parts ...

    ------------
    Roman P.. at 8/1/01 7:46:43 PM


    Hello,

    We have a 6.5 SQL DB, when I run a query from ISQL/w the error appears...

    Msg 1105, Level 17, State 1
    Can't allocate space for object '-386' in database 'tempdb' because the 'system' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
    Msg 21, Level 20, State 1
    WARNING - Fatal Error 1510 occurred at Aug 1 2001 7:18PM. Please note the error and time, and contact your System Administrator.

    I am at my wits end to figure this out.

    All help would be appreciated.

  3. #3
    Roman P. Guest

    SQL temp table woes (reply)

    Ivo,

    Good news is that the expansion wooked!!!

    Here is the query from a custom/modified app that we are running inhouse.

    1.select listbox from lookups where lookupid='cmpaids'

    (value returned is as follows..
    [CMSList]
    Info=8,7,583,185,860
    Name=LIST
    Prompt=List Box
    Font=MS Sans Serif,11,0,0
    Border=8
    SQLTable=DOCSADM.CMPAIDS
    TitleFont=MS Sans Serif,11,1,0
    Column0=Case/Memb/Ed ID,117,88,0,0,,
    SQLCol0=DOCSADM.CMPAIDS,ID
    Column1=Name,100,88,0,0,,
    SQLCol
    2. execute sp_scsgetindid 'cmpaids','id'

    Below is the stored procedure......

    if exists (select * from sysobjects where id =
    object_id 'DOCSADM.SP_CMSGETINDID&#39
    and sysstat & 0xf = 4)
    drop procedure DOCSADM.SP_CMSGETINDID
    GO

    create procedure SP_CMSGETINDID @TableName varchar(30),
    @ColumnName varchar(30) as BEGIN DECLARE @ThisKey varchar(30)
    DECLARE @x int select @x = 0 WHILE @x <= 254
    BEGIN SELECT @ThisKey = INDEX_COL(@TableName, @x, 1)
    if @ThisKey IS NULL and @x > 1 goto keysdone
    if @ThisKey = @ColumnName begin select @x return end
    SELECT @x = @x + 1 END return keysdone: END
    GO
    GRANT EXECUTE ON DOCSADM.SP_CMSGETINDID TO public
    GO
    GRANT EXECUTE ON DOCSADM.SP_CMSGETINDID TO DOCS_USERS
    GO

    3. After the stored procedure is run this is executed....

    Select cmpaids.id,...,cmpanames.username
    from cmpaids(2),cmpanames where
    cmpaaids.link= cmpanmaes.system_id
    order by cmpaids.id

    Thats it!!!
    Thank you again.


    ------------
    IVO at 8/2/01 1:29:00 AM

    Reason for this error is that your query creates too large temporary table, or cursor. Solution is to expand size of tempdb, or to rewrite query so that you will not use large temporary objects (for example you&#39;d use permanent table in your database instead of temporary.)
    Fatal Error 1510 is clearly descripted in SQL Books Online and is about creating an index - do you create index on a temporary table ?
    For better advice you should insert here your query, or its important parts ...

    ------------
    Roman P.. at 8/1/01 7:46:43 PM


    Hello,

    We have a 6.5 SQL DB, when I run a query from ISQL/w the error appears...

    Msg 1105, Level 17, State 1
    Can&#39;t allocate space for object &#39;-386&#39; in database &#39;tempdb&#39; because the &#39;system&#39; segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
    Msg 21, Level 20, State 1
    WARNING - Fatal Error 1510 occurred at Aug 1 2001 7:18PM. Please note the error and time, and contact your System Administrator.

    I am at my wits end to figure this out.

    All help would be appreciated.

  4. #4
    IVO Guest

    SQL temp table woes (reply)

    RP,
    I don&#39;t know why this query should cause your problems, it is trivial.
    maybe problem could be if your tables cmpaids and cmpanames were huge, or your tempdb very very small.
    Maybe problem is somwhere out there, Mulder ... )
    Bye


    ------------
    Roman P. at 8/2/01 11:44:11 AM

    Ivo,

    Good news is that the expansion wooked!!!

    Here is the query from a custom/modified app that we are running inhouse.

    1.select listbox from lookups where lookupid=&#39;cmpaids&#39;

    (value returned is as follows..
    [CMSList]
    Info=8,7,583,185,860
    Name=LIST
    Prompt=List Box
    Font=MS Sans Serif,11,0,0
    Border=8
    SQLTable=DOCSADM.CMPAIDS
    TitleFont=MS Sans Serif,11,1,0
    Column0=Case/Memb/Ed ID,117,88,0,0,,
    SQLCol0=DOCSADM.CMPAIDS,ID
    Column1=Name,100,88,0,0,,
    SQLCol
    2. execute sp_scsgetindid &#39;cmpaids&#39;,&#39;id&#39;

    Below is the stored procedure......

    if exists (select * from sysobjects where id =
    object_id &#39;DOCSADM.SP_CMSGETINDID&#39
    and sysstat & 0xf = 4)
    drop procedure DOCSADM.SP_CMSGETINDID
    GO

    create procedure SP_CMSGETINDID @TableName varchar(30),
    @ColumnName varchar(30) as BEGIN DECLARE @ThisKey varchar(30)
    DECLARE @x int select @x = 0 WHILE @x <= 254
    BEGIN SELECT @ThisKey = INDEX_COL(@TableName, @x, 1)
    if @ThisKey IS NULL and @x > 1 goto keysdone
    if @ThisKey = @ColumnName begin select @x return end
    SELECT @x = @x + 1 END return keysdone: END
    GO
    GRANT EXECUTE ON DOCSADM.SP_CMSGETINDID TO public
    GO
    GRANT EXECUTE ON DOCSADM.SP_CMSGETINDID TO DOCS_USERS
    GO

    3. After the stored procedure is run this is executed....

    Select cmpaids.id,...,cmpanames.username
    from cmpaids(2),cmpanames where
    cmpaaids.link= cmpanmaes.system_id
    order by cmpaids.id

    Thats it!!!
    Thank you again.


    ------------
    IVO at 8/2/01 1:29:00 AM

    Reason for this error is that your query creates too large temporary table, or cursor. Solution is to expand size of tempdb, or to rewrite query so that you will not use large temporary objects (for example you&#39;d use permanent table in your database instead of temporary.)
    Fatal Error 1510 is clearly descripted in SQL Books Online and is about creating an index - do you create index on a temporary table ?
    For better advice you should insert here your query, or its important parts ...

    ------------
    Roman P.. at 8/1/01 7:46:43 PM


    Hello,

    We have a 6.5 SQL DB, when I run a query from ISQL/w the error appears...

    Msg 1105, Level 17, State 1
    Can&#39;t allocate space for object &#39;-386&#39; in database &#39;tempdb&#39; because the &#39;system&#39; segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
    Msg 21, Level 20, State 1
    WARNING - Fatal Error 1510 occurred at Aug 1 2001 7:18PM. Please note the error and time, and contact your System Administrator.

    I am at my wits end to figure this out.

    All help would be appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •