Check if table exists

[ablackwe]

I have a little problem that hopefully someone can help with. I currently have a DTS package that is scheduled and creates a number of tables. What I need to be able to do is schedule a seperate DTS package that checks if a specific table exists. If not, it keeps checking until the table has been created.

Help would be much appreciated.
Adam

 

[JayKusch]

OK ... first off ...

Create a SQL Server Job. In the step of the job add the code to check to see if the table exists yet. Something like ...

SELECT  *
FROM    MyDatabase..SysObjects
WHERE   xtype = 'U'
AND     name = 'MyTableName'

IF  @@RowCount <> 0
  BEGIN
    EXEC Master..xp_CmdShell 'dtsrun /Sserver_name 
                                     /Uuser_nName 
                                     /Ppassword 
                                     /Npackage_name 
                                     /Mpackage_password
  END

Then schedule the job to run as needed. Maybe every 5 minutes or so.

Here is the catch though .... what do you want to do w/ the job once it HAS executed the DTS package against the table that now exists.

I quick fix would be to stop the job as part of the code above. Somewhat like ...

SELECT  *
FROM    MyDatabase..SysObjects
WHERE   xtype = 'U'
AND     name = 'MyTableName'

IF  @@RowCount <> 0
  BEGIN
    EXEC Master..xp_CmdShell 'dtsrun /Sserver_name 
                                     /Uuser_nName 
                                     /Ppassword 
                                     /Npackage_name 
                                     /Mpackage_password'

    EXEC MSDB..sp_Stop_Job 'MyJobName'

  END

Hope this helps!

Thanks

J. Kusch