executing isql using xp_cmdshell

• mkey

  Say Hey Kid

  Points: 682

  More actions

  January 17, 2002 at 11:32 am

  #78377

  HI All,

  What I want to do is create a script to disable FK constraints.I have a procedure something like this:

  CREATE PROCEDURE create_drop_statement

  AS

  SET NOCOUNT ON

  EXEC master..xp_cmdshell 'isql -c -t -d database_name -S server_name -Usa -i c:\nocheck.sql -o c:\output_nocheck_sp.sql -P'

  GO

  The nocheck.sql contain the script to create alter statements. nocheck.sql script looks something like this:

  SELECT 'ALTER TABLE ' +

  QUOTENAME( c.TABLE_NAME ) +

  ' NOCHECK CONSTRAINT ' +

  QUOTENAME( c.CONSTRAINT_NAME ) AS ALTER_SCRIPT

  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c

  WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

  When I execute this procedure it's returning 0 records. Can you please tell me what am I doing wrong? The other question is that the isql.exe utility on my machine located upder d:\mssql7\binn path. When I incorporate in to the isql statement I am getting an error. Something like the path cannot be found. I have used the exact path to test my isql utility in the dos prompt and it works just fine. Can you help me on this issue please?

  Can someone help on this please!

  Thank you!

• jG

  SSCrazy

  Points: 2505

  More actions

  January 17, 2002 at 1:53 pm

  #422285

  Brian has a nice script that disables constraints it you want to look it over. It is on this site. here is the exact URL.

  http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=25

  
  -JG