Export to txt

I tried to read all ther previous messages but they are unavailable.
In the unix world I do
Select field into 'path/filename';
I have tried both
select field into "c:\test.txt"
and
SELECT [INV ITEM Id], [INV ITEM Manufacturer], [INV ITEM Con
dition],
[INV ITEM Qty On Hand]
INTO [ C : text.txt]
FROM [INV Items]
I think my bottom line question is, is this possible with MSSQL? and if so,
can someone give me the syntax, where am I blowing it?
Thanks
GeorgeSee BCP, DTS in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"george collins" <george@.nospan.com> wrote in message
news:Oz%239O$EbEHA.3684@.TK2MSFTNGP09.phx.gbl...
> I tried to read all ther previous messages but they are unavailable.
> In the unix world I do
> Select field into 'path/filename';
> I have tried both
> select field into "c:\test.txt"
> and
> SELECT [INV ITEM Id], [INV ITEM Manufacturer], [INV ITEM C
ondition],
> [INV ITEM Qty On Hand]
> INTO [ C : text.txt]
> FROM [INV Items]
> I think my bottom line question is, is this possible with MSSQL? and if
so,
> can someone give me the syntax, where am I blowing it?
> Thanks
> George
>
>|||Hi,
There are 3 options
1. Execute OSQL utility from command prompt
OSQL -Usa -Ppassword -Sserver -Q"
SELECT [INV ITEM Id], [INV ITEM Manufacturer], [INV ITEM Con
dition],
[INV ITEM Qty On Hand] FROM [INV Items]" -oc:\text.txt -n
2. BCP with QUERYOUT option from command prompt
BCP "SELECT [INV ITEM Id], [INV ITEM Manufacturer], [INV ITE
M
Condition], [INV ITEM Qty On Hand] FROM [INV Items]" QUERYOUT
c:\text.txt -Usa -Ppassword -SServer_name -c
3. DTS (Graphical utility) , you can mention ur query
Thanks
Hari
MCDBA
"george collins" <george@.nospan.com> wrote in message
news:Oz#9O$EbEHA.3684@.TK2MSFTNGP09.phx.gbl...
> I tried to read all ther previous messages but they are unavailable.
> In the unix world I do
> Select field into 'path/filename';
> I have tried both
> select field into "c:\test.txt"
> and
> SELECT [INV ITEM Id], [INV ITEM Manufacturer], [INV ITEM C
ondition],
> [INV ITEM Qty On Hand]
> INTO [ C : text.txt]
> FROM [INV Items]
> I think my bottom line question is, is this possible with MSSQL? and if
so,
> can someone give me the syntax, where am I blowing it?
> Thanks
> George
>
>|||Here is what I did and so far it has worked great.
Private Sub cmdDispersals_Click(Index As Integer)
Dim ILSFile
Dim FileSysObject
Dim sql As String
Dim DataString As Variant
Dim rowcount As String
'sql = " Select [INV ITEM Id],[INV ITEM Qty On Hand],[INV ITEM
Condition],[INV ITEM Manufacturer] FROM [INV Items] "
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open sql, db, adOpenStatic
DataString = adoPrimaryRS.GetString()
Set FileSysObject = CreateObject("scripting.filesystemobject")
Set ILSFile = FileSysObject.createtextfile("c:\ILS.txt", True)
ILSFile.Write DataString
ILSFile.Close
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uXExIxMbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> Hi,
> There are 3 options
> 1. Execute OSQL utility from command prompt
> OSQL -Usa -Ppassword -Sserver -Q"
> SELECT [INV ITEM Id], [INV ITEM Manufacturer], [INV ITEM C
ondition],
> [INV ITEM Qty On Hand] FROM [INV Items]" -oc:\text.txt -n
> 2. BCP with QUERYOUT option from command prompt
> BCP "SELECT [INV ITEM Id], [INV ITEM Manufacturer], [INV I
TEM
> Condition], [INV ITEM Qty On Hand] FROM [INV Items]" QUERYOUT
> c:\text.txt -Usa -Ppassword -SServer_name -c
> 3. DTS (Graphical utility) , you can mention ur query
> Thanks
> Hari
> MCDBA
>
> "george collins" <george@.nospan.com> wrote in message
> news:Oz#9O$EbEHA.3684@.TK2MSFTNGP09.phx.gbl...
> so,
>|||george,
You may also wish to consider using the ExportData method of the Table
object with the BulkCopy object in SQL-DMO. You might find this faster
than the code you have there, depends on your data volumes. If you have
a very high volume of data I would recommend testing both methods. For
the ultimate in flexibility, use DTS.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
george collins wrote:
> Here is what I did and so far it has worked great.
> Private Sub cmdDispersals_Click(Index As Integer)
> Dim ILSFile
> Dim FileSysObject
> Dim sql As String
> Dim DataString As Variant
> Dim rowcount As String
> 'sql = " Select [INV ITEM Id],[INV ITEM Qty On Hand],[INV ITEM
> Condition],[INV ITEM Manufacturer] FROM [INV Items] "
> Set adoPrimaryRS = New Recordset
> adoPrimaryRS.Open sql, db, adOpenStatic
> DataString = adoPrimaryRS.GetString()
> Set FileSysObject = CreateObject("scripting.filesystemobject")
> Set ILSFile = FileSysObject.createtextfile("c:\ILS.txt", True)
> ILSFile.Write DataString
> ILSFile.Close
>
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uXExIxMbEHA.3480@.TK2MSFTNGP11.phx.gbl...
>
>|||I do see an issue as you speak. It takes about 5 minutes for 242000
records. I will see if the customer complains and then decide whats next.
I have some concern about memory running out, things like that.
Seems like keeping in memory should be faster than disk IO. I will probably
try both and report what I find.
Thanks everyone for your help.
George
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OJGO%23WWbEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> george,
> You may also wish to consider using the ExportData method of the Table
> object with the BulkCopy object in SQL-DMO. You might find this faster
> than the code you have there, depends on your data volumes. If you have a
> very high volume of data I would recommend testing both methods. For the
> ultimate in flexibility, use DTS.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> george collins wrote: