File in SQL

• Nita Reddy

  SSCarpal Tunnel

  Points: 4182

  More actions

  January 13, 2005 at 1:34 pm

  #102350

  Hi,

  I have written a simple SQL code on text file and is on C:\test.sql

  test.sql contains

  "select * from pubs.dbo.authors"

  NOW, I want to execute this file in one of the store procedure BUT I DON't KNOW HOW TO CALL or Write the syntax?

  Nita

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 13, 2005 at 1:59 pm

  #537377

  You might want to search here for xp_cmdshell and OSQL. Syntax is also explained in BOL. Sorry, I haven't got a working example at hand right now.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Martin Vrieze

  SSCrazy

  Points: 2760

  More actions

  January 14, 2005 at 8:15 am

  #537481

  Set up a text file called "Test.SQL".  Paste your SQL syntax into that file.

  Set up another text file called "Test.cmd"

  Place an osql call inside "Test.cmd"...here is what the OSQL might look like...

  osql /U{username} /P{password} /d{database name} /S{server name} /i {script file} /o {output text file}

  Comments...

  1.  leave out the "{" and "}"

  2.  The {script file} references "Test.SQL"

  3.  The {output text file} is a simple text file that will contain any messages, counts, etc that arise from the SQL script.  Your select * from yada-yada output will dump into this text file too.

  To call the "Test.cmd" dos executable, you need to set up the XP_cmdshell.  See BOL for doing this part...it's pretty simple.

   

  Marty Vrieze