osql results - 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

SQL Server 2000 Forums

SQL Server Development (2000)

osql results

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-15 : 21:07:36

I am having a proliferation of SQL Server instances... A topic I am addressing with management

I am currently running xp_cmdshell 'osql -L' to get a list of all of the servers running in the domain, Great!

Now how can I get those results stored in a table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 13:46:23

For DOS, you can use the greater than sign to put the output into a file. Or you could use -o switch of osql.exe. Remember to run osql /? to find out what is available.

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-16 : 16:58:59

Thank you Tara
So what you are implying is that once the file is created I do a bulk import to a temp table that I create, Destroy the file, and work with the imported data?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 17:04:01

I would not use osql.exe to generate a file that will be used to import from. You should be exporting the data using bcp.exe, then importing with it as well. osql.exe is to run queries. bcp.exe is for imports and exports.

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-16 : 19:51:13

Yes I understand the purpose of the bcp and osql my orriginal question was

quote:
currently running xp_cmdshell 'osql -L' to get a list of all of the servers running in the domain, Great!
Now how can I get those results stored in a table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 19:52:51

Then why do you want to put results of osql.exe into a table? Regardless of the answer, you could do an INSERT INTO with an EXEC master.dbo.xp_cmdshell 'type FileName.txt' or something like that.

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-16 : 20:42:29

I was trying all different combinations of this and could'nt get it to work.

quote:
or something like that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 20:45:29

Works fine for me:

CREATE TABLE #Temp(Column1 varchar(7000))

INSERT INTO #Temp
EXEC master.dbo.xp_cmdshell 'type C:\SomeFile.txt'

SELECT * FROM #Temp

DROP TABLE #Temp

But keep in mind that xp_cmdshell runs from the database server so the path and filename must be valid there and not on your local client machine.

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-16 : 22:18:00

Your definately the Queen... But a princes to me! Thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources