Run SQL from File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Run SQL from File

Author

Topic

vishal_7
Posting Yak Master

127 Posts

Posted - 2005-12-07 : 13:33:54

Hi,

I need to run a SQL which is stored inside a file. The SQL looks like this:

Select col1, col2 from t

I am running this from analyzer:

EXEC master..xp_cmdshell 'osql -E -dCS -Slocalhost -ic:\test.sql -n'

Now the problem is that it returns everything in one column called 'output'. How can I make this to return the date the same way as I were running the sql inside stored procedure (which would return the data in two colums).

Please help.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-07 : 15:20:40

Why? If you are in QA why not just run the query

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 23:50:22

You need to make use of osql and run it from command promt. Look for more information on osql in Books On Line, SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-08 : 00:33:56

Can you modify the TEST.SQL file? (or is something else generating it?)

If so you could modify it to store the results into a table, instead of outputting them. Then process the table.

Alternatively, read the file into a variable, and execute that instead

INSERT INTO MyTable
EXEC master..xp_cmdshell 'TYPE c:\test.sql'

Now concatenate the rows from MyTable into a variable, and execute - probably something like:

DECLARE @strSQL varchar(8000)
SELECT @strSQL = COALESCE(@strSQL + ' ', '') + MyColumn
FROM MyTable
ORDER BY MyID

INSERT INTO MyOtherTable
EXEC (@strSQL)

Kristen

vishal_7
Posting Yak Master

127 Posts

Posted - 2005-12-08 : 12:02:18

Thanks to all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources