bcp isql osql

Question

bcp isql osql

oracle_coorgi

SSC-Addicted

Points: 483
More actions
January 8, 2007 at 5:55 am

#170909

hi firstly i dont know where to post the topic
i have posted in two place's
hi
i need to run a big query say 50line query which gives me 5 output
2943 Records, Jan 5 2007 1:23AM
197 Records, Feb 28 2006 1:00PM
No Data
No Data
1 Records, Jan 6 2007 8:01PM
i need to export the query out to a c:\data\test.txt file
is it possible to
use dcp,isql,osql
C:\>bcp "SELECT GETDATE()" queryout testfinal.txt -c -t ","
-Uxyz -Pabc -Sserverab
C:\>isql -Sserverab -Uxyz -Pabc -d northwind -q"select getdate()" -o.\erappa.txt
how can i do this i dont want to use DTS or query analyzer
i need to this by command prompt(windows)
thanxs

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

First, don't use ISQL... it's quite a bit deprecated when compared to OSQL which you should use instead.

Second, yes, both your BCP and OSQL should be very close... probably the best thing to do would be to turn your 50 line query into a stored procedure and then make your BCP or OSQL call that procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

if you are using bcp and can't create a sproc, you can put the whole thing in a .bat file and build up your query in an environment variable, so it's a bit more readable:

set SQL= select *

set SQL=%SQL% from sysobjects

set SQL=%SQL% where xtype='U'

bcp "%SQL%" queryout testfinal.txt -c -t, -Uxyz -Pabc -Sserverab

---------------------------------------
elsasoft.org

oracle_coorgi SSC-Addicted Points: 483 More actions · Answer 3

hi

i have limitation on this

iam working or monitoring on remote production database i cannot create sp on the database and i dont want to do that can run this query without creating any object .i want to run from local command prompt by given the reqiured info

c:>isqlw -S BOSPRODOL401\C -d MBI401 -U abc -P axxy -i C:\Public\macros_test\ZBA_Agent.sql -o C:\Public\macros_test\editest.txt

the sample query is ...........

select getdate() as "Query Time"

select count(*) as "ZBA Agent" from dbo.vw_email_complete_queue(nolock) where insert_dte > '01/01/2005'

and email_type_cde in (4,5)

select getdate() as "Missing Transactions"

SELECT daily_proc_status_cde,*

FROM setl_day_totals

WHERE setl_dte =dbo.udf_FormatShortDate(GETDATE())

can i use bcp , isql ,osql and others

after this i need to shedule this through

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

why don't you do what I suggested then. did you even read my post?

---------------------------------------
elsasoft.org

Sergiy SSC Guru Points: 110208 More actions · Answer 5

I wanna see a requirement to monitor car engine temperature without putting a sensor in it.

Why IT projects are ruled by such idiots?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Heh... THAT can be done! But I absolutely agree... The requirements that some of these folks have to put up with is insane.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 7

I understand, they don't want newbie to affect their production system.

So, instead of letting him to create SP which could be checked by a professional, tested properly and provide required report in safest way they prefer to open the whole database including system tables to the same guy whose skills they don't trust to run whatever query he could invent, without testing, putting up with locking uncontrollable number of resourses.

And worst part - they open database for ad-hoc queries from command prompt!!!

Their business should stay low. Otherwise it will be destroyed by competitors in no time.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Agreed on all points... I have the same problems with a batch scheduling program at work because they don't know how to and/or won't use the SQL Job scheduler. Then they pitch and moan about deadlocks... go figure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)