OSQL Question

Question

OSQL Question

Foram Gandhi

SSC Enthusiast

Points: 126
More actions
May 18, 2006 at 4:06 pm

#93918

Hello Guys,
        I am not an expert at SQL but tring to learn it. I have the following scnerio.
I want to connect to a SQL Server not on the DOMAIN. Execute a query and store the results of the query in a txt file.
I hear OSQL is the way to go.
1) How do I install OSQL on the server.
2) How do I connect to a server that is not in the DOMAIN. Its on the Network and I can remote into the server.
Need to create a Link server to lets say NY030(Workgroup) from NY010 i.e on the domain.
Then,
3) Setup a job that runs DAILY WITH the following query
    SELECT A.MATTER, A.DESCRIPTION FROM NY010.DATABASE.CUSTOM2 A, NY030.CCOMM32.CCINFO B
     WHERE
A.MATTER != B.MATTER >>> STORE TO ABC.TXT
Any input on this would be much appriciated.
Thank you for your time.
Foram

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Richard Gardner-291039 Hall of Fame Points: 3603 More actions · Answer 1

Hi Foram,

This is more of a security issue than an SQL issue, the technicalities of doing this are pretty trivial once you can see the server.

Question : From within Enterprise manager, when you are logged into your domain, can you see the other SQL server advertised? i.e Run the SQL server registration wizard (right click SQL server group). Can you see the server name you need to attach to? If you can try and attach to it using the local SQL security settings.

Can you do that?

If you can then you just need to add it as a linked server, I think the settings are "Using this Security Context", you type an SQL login into the box and I think you need the "impersonate" set to yes (I just messed around with this until it worked, doesn't take long to work it out).

Once you have a linked server your problem becomes working out the right syntax for querying linked servers (I have no idea what OSQL is, you just use standard SQL with an extra qualifier in your table names for referring to the SQL server)

If you can't then you could try adding the server by IP address, but more than likely you'll need to open up some traffic on your firewall.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

OSQL is via the command line. I suggest looking it up in the BOL and you will find the syntax and all the commands there.

BOL=Books OnLine=Microsoft SQL Server's HELP

Installed as part of the Client Tools

Found at Start>Programs>Microsoft SQL Server>Books OnLine

or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

-SQLBill

Richard Gardner-291039 Hall of Fame Points: 3603 More actions · Answer 3

Oh yeah, thanks Bill, completely irrelevant to you Faram, just use linked servers and normal queries.

Foram Gandhi SSC Enthusiast Points: 126 More actions · Answer 4

Thank you guys for your valuable response.

1) yes I can see the other Server in the ENT Mgr. I just cannot login b'us I don't know the Sa password for that server or any other login credentials. Should I just create a new login on the that SQL Box and use that to connect or add linked server.

I can add new login as I can remote in that Server via Terminal Services as Admin.

2) Writing the correct query is a the easy part redirecting it to a text file is the hard one.

Please advise how can I redirect the results of a query to a text file. (This is why I was refering to OSQL unless there are other ways and I don't know about it.)

Please advise on the topic. Your comments are very valuable to me.

Thank You,

Foram Gandhi

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Foram

(1) If the remote server has SQL and Windows authentication then yes, you can create a user for yourself and use that to connect. If it has Windows authentication only then there is nothing you can do if it is not on the domain.

(2) If you are using Query Analyzer then choose Results to File from the Query menu. If your query runs regularly as a job then you can choose to send the results of that job to a text file.

Hope that helps

John

Richard Gardner-291039 Hall of Fame Points: 3603 More actions · Answer 6

Of course you could also connect Excel to the db to extract the results that way....

If you're going to use OSQL you don't need a linked server, just create an ODBC connection using the username / password you create and run it straight down that link - you could also use the ODBC connection to connect to Excel, really depends on what you're trying to achieve, how permanent you need the connection to be, how often the query needs to run, etc....

If you just need the data once use John's method.

Foram Gandhi SSC Enthusiast Points: 126 More actions · Answer 7

Thank you all guys for your excelent comments and suggestions.

I have finally accomplished this task and it feels good.

Here is what I ended up with.

1) Created a new login to the remote server.

2) Created Link Server on the host SQL box

3) Created a DTS Package to query and export the query results to a network destination.

a) On the DTS Package first create connection to Host Server

b) User Data Transformation Task to write the query and setup appropriate delimetrs etc.

c) Create "Text File Destination" to give the network path where the results will be exported to. ( you must have an empty file e.g. export.txt at this location)

Again, this was my first experiance at SQL central and a very pleasent one. Hope to talk to you all soon.

Thank You,

Foram Gandhi