problem with run PROCEDURE-table is empty

• midan1

  SSCertifiable

  Points: 5100

  More actions

  December 30, 2004 at 1:34 pm

  #60373

  i work with link server (pioledb)

  the problem is thet the table is empty sometimes an it not work OK

  how can i condition the delete  ???

  i wont thet the table is always be full

  ---

  CREATE PROCEDURE

  [select_pi2]

  as

  DELETE FROM

  [pitable].[dbo].[Table1]

  INSERT INTO

  [pitable].[dbo].[Table1]([val],[uu],[col])

  SELECT

  *

  FROM

  OPENQUERY(pcntn, 'SELECT value as val ,''UNIT1''as uu ,''FF9900'' AS col FROM picomp WHERE time = date(''*'') AND tag = ''a1mp340''

  union all

  SELECT value as val ,''UNIT2''as uu ,''00CC00'' AS col FROM picomp WHERE time = date(''*'') AND tag = ''a2mp340''

  union all

  SELECT value as val ,''UNIT3''as uu ,''FFFF00'' AS col FROM picomp WHERE time = date(''*'') AND tag = ''a3mp340''

  union all

  SELECT value as val ,''UNIT4''as uu ,''0099FF'' AS col FROM picomp WHERE time = date(''*'') AND tag = ''a4mp340''

  ')

  union all

  SELECT

  *

  FROM

  OPENQUERY(p2074,

  'SELECT value as val ,''UNIT5''as uu ,''0033FF'' AS col FROM picomp WHERE time = date(''*'') AND tag = ''a5mp113''

  union all

  SELECT value as val ,''UNIT6''as uu ,''FF33FF'' AS col FROM picomp WHERE time = date(''*'') AND tag = ''a6mp113''

  ')

  GO

  ---

   

  thnks ilan

• Phil Parkin

  SSC Guru

  Points: 246991

  More actions

  December 30, 2004 at 2:09 pm

  #535461

  Conditioning the DELETE is possible, but I'm not sure what condition you want. Can you be a bit more specific? Also, when you say that it doesn't work when the table is empty, what do you mean? DELETE works fine on empty tables.

  Also, if you just want to delete all records in a table without logging, you should investigate the TRUNCATE TABLE command, which is much faster than DELETE.

  Regards

  Phil

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• midan1

  SSCertifiable

  Points: 5100

  More actions

  December 30, 2004 at 2:24 pm

  #535464

  this PROCEDURE run from IIS asp page and the page refresh evry 20 second

  maybe ##temp table weel be more good ???

  the problem is the delay in the link server

  can you help me work with TRUNCATE and fix my PROCEDURE ?

  i need thet table weel bee always full !!!

  for all the users

  thnks ilan

• midan1

  SSCertifiable

  Points: 5100

  More actions

  December 30, 2004 at 3:02 pm

  #535469

  ops ................