#1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,003
    Rep Power
    32

    How do I automate extract of sql for table structures, stored proc and views?


    This may be easy and I may be searching for the wrong keywords but how do I automate extract of sql for table structures, stored proc and views? I've been searching for two days now and only found one way to do it and it's not working for some reason.

    Code:
    execute xp_cmdshell 'bcp "SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES" queryout "C:\Proc_Backup.txt" -T -c -t'
    unfortunately I can't figure out the error I get so if anyone knows of a way to fix this or if there's another way, I'd greatly appreciate it! Error is below:

    SQLState = 08001, NativeError = 2
    Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
    SQLState = 08001, NativeError = 2
    Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
    ured to allow remote connections. For more information see SQL Server Books Online.
    SQLState = S1T00, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
    NULL
  2. #2
  3. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,003
    Rep Power
    32
    Resolved!

    Had to add a -S server\instance at the end of the bcp statement to tell bcp where to run the query.

IMN logo majestic logo threadwatch logo seochat tools logo