Is there anyway possible to grab a snapshot via some vb.net or c# code and
then export it to pdf, txt, etc...?Yes,
If you've created a snapshot then you can get to the snapshot via the REnder
Method on the Reporting Services' SOAP interface.
Chapter 9¾ (Our Harry Potter Themed Chapter with Voldemort Basic.NET quips)
deals with SOAP and pages 585 - 592 - should show you all the details you
need to know about how to create a snapshot via code and also how to render
it.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Mike H" <Mike H@.discussions.microsoft.com> wrote in message
news:62F37D51-6862-415C-9C87-FE82685FC6D3@.microsoft.com...
> Is there anyway possible to grab a snapshot via some vb.net or c# code and
> then export it to pdf, txt, etc...?sql
Showing posts with label txt. Show all posts
Showing posts with label txt. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
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 Condition],
[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
See 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 Condition],
> [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 Condition],
[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 ITEM
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 Condition],
> [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 Condition],
> [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 ITEM
> 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:
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 Condition],
[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
See 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 Condition],
> [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 Condition],
[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 ITEM
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 Condition],
> [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 Condition],
> [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 ITEM
> 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:
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 Condition],
[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 Condition],
> [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 Condition],
[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 ITEM
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 Condition],
> [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 Condition],
> [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 ITEM
> 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 Condition],
>> [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
>>
>|||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...
>>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 Condition],
>>[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 ITEM
>>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 Condition],
>>[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
>>
>>
>|||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...
> 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...
>>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 Condition],
>>[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 ITEM
>>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 Condition],
>>[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
>>
>>
>>
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 Condition],
[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 Condition],
> [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 Condition],
[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 ITEM
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 Condition],
> [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 Condition],
> [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 ITEM
> 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 Condition],
>> [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
>>
>|||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...
>>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 Condition],
>>[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 ITEM
>>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 Condition],
>>[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
>>
>>
>|||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...
> 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...
>>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 Condition],
>>[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 ITEM
>>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 Condition],
>>[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
>>
>>
>>
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:
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:
Export to Tab Delimited TXT - SQL Reporting Services
I am new to Reporting tools. How can I export the output to Tab Delimited TXT. I found some solutions, however, I could not understand how to proceed with them... anybody can explain the solution step by step...
Hi Pradeep,
Try some of these links
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924133&SiteID=1
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74882
|||Thanks Sudhir for the reply, however, I need to export the SQL Reporting Services REPORT output to TAB Delimited TXT file.
|||Hi,
From your description, it seems that you want to change the CSV extension to a Tab delimited output in your reporting service, right?
If so, you should add a new extension line to the RSReportServer.config file to enable the Tab delimited output.
In the config file, you should change the following settings:
1. For tab-delimited report, use
<FieldDelimiter> </FieldDelimiter>
2. For name override, you must specify language attribute:
<Name Language="en-US">TXT (Tab Delimited Text File)</Name>
For more information, see:http://msdn2.microsoft.com/en-us/library/ms156281.aspx
Thanks.
Export to plain text
All I want to do is export a report to plain text in a .txt file. Is
this possible? If it is... how do you do it? Thanks!I dont think it is possible. Just check the config file for a list of
supported rendering format. Infact by default some of the rendering options
are disabled. Depending on your requirements you can enable it. If .txt is
not there then you need to write custom rendering.
Amarnath
"schuhtl@.gmail.com" wrote:
> All I want to do is export a report to plain text in a .txt file. Is
> this possible? If it is... how do you do it? Thanks!
>|||Amarnath,
Thanks for your response. I don't see text as a rendering format in
the config file.
I am sure I am not the only person who has asked for this. Is there a
create your own rendering options for dummy's guide anywhere? I
normally would not export a report to a txt file but in this case it is
a requirement.
Amarnath wrote:
> I dont think it is possible. Just check the config file for a list of
> supported rendering format. Infact by default some of the rendering options
> are disabled. Depending on your requirements you can enable it. If .txt is
> not there then you need to write custom rendering.
> Amarnath
> "schuhtl@.gmail.com" wrote:
> > All I want to do is export a report to plain text in a .txt file. Is
> > this possible? If it is... how do you do it? Thanks!
> >
> >|||nah sorry we actually ended up buying a 3rd party tool called 'Office
Writer' to be able to export to WORD in some situations.
have you considered PRINTING to text?
install a printer; call it a text printer or something and you print to
it.. presto-chango you can export to TXT
-Aaron
schuhtl@.gmail.com wrote:
> Amarnath,
> Thanks for your response. I don't see text as a rendering format in
> the config file.
> I am sure I am not the only person who has asked for this. Is there a
> create your own rendering options for dummy's guide anywhere? I
> normally would not export a report to a txt file but in this case it is
> a requirement.
>
> Amarnath wrote:
> > I dont think it is possible. Just check the config file for a list of
> > supported rendering format. Infact by default some of the rendering options
> > are disabled. Depending on your requirements you can enable it. If .txt is
> > not there then you need to write custom rendering.
> >
> > Amarnath
> >
> > "schuhtl@.gmail.com" wrote:
> >
> > > All I want to do is export a report to plain text in a .txt file. Is
> > > this possible? If it is... how do you do it? Thanks!
> > >
> > >|||If it is a special requirement (and one timer) to take it to txt, then export
to CSV/excel option and then convert to txt. I have seen an article with
examples on MSDN please search for "custom rendering". You should get some
examples.
Amarnath
"schuhtl@.gmail.com" wrote:
> Amarnath,
> Thanks for your response. I don't see text as a rendering format in
> the config file.
> I am sure I am not the only person who has asked for this. Is there a
> create your own rendering options for dummy's guide anywhere? I
> normally would not export a report to a txt file but in this case it is
> a requirement.
>
> Amarnath wrote:
> > I dont think it is possible. Just check the config file for a list of
> > supported rendering format. Infact by default some of the rendering options
> > are disabled. Depending on your requirements you can enable it. If .txt is
> > not there then you need to write custom rendering.
> >
> > Amarnath
> >
> > "schuhtl@.gmail.com" wrote:
> >
> > > All I want to do is export a report to plain text in a .txt file. Is
> > > this possible? If it is... how do you do it? Thanks!
> > >
> > >
>
this possible? If it is... how do you do it? Thanks!I dont think it is possible. Just check the config file for a list of
supported rendering format. Infact by default some of the rendering options
are disabled. Depending on your requirements you can enable it. If .txt is
not there then you need to write custom rendering.
Amarnath
"schuhtl@.gmail.com" wrote:
> All I want to do is export a report to plain text in a .txt file. Is
> this possible? If it is... how do you do it? Thanks!
>|||Amarnath,
Thanks for your response. I don't see text as a rendering format in
the config file.
I am sure I am not the only person who has asked for this. Is there a
create your own rendering options for dummy's guide anywhere? I
normally would not export a report to a txt file but in this case it is
a requirement.
Amarnath wrote:
> I dont think it is possible. Just check the config file for a list of
> supported rendering format. Infact by default some of the rendering options
> are disabled. Depending on your requirements you can enable it. If .txt is
> not there then you need to write custom rendering.
> Amarnath
> "schuhtl@.gmail.com" wrote:
> > All I want to do is export a report to plain text in a .txt file. Is
> > this possible? If it is... how do you do it? Thanks!
> >
> >|||nah sorry we actually ended up buying a 3rd party tool called 'Office
Writer' to be able to export to WORD in some situations.
have you considered PRINTING to text?
install a printer; call it a text printer or something and you print to
it.. presto-chango you can export to TXT
-Aaron
schuhtl@.gmail.com wrote:
> Amarnath,
> Thanks for your response. I don't see text as a rendering format in
> the config file.
> I am sure I am not the only person who has asked for this. Is there a
> create your own rendering options for dummy's guide anywhere? I
> normally would not export a report to a txt file but in this case it is
> a requirement.
>
> Amarnath wrote:
> > I dont think it is possible. Just check the config file for a list of
> > supported rendering format. Infact by default some of the rendering options
> > are disabled. Depending on your requirements you can enable it. If .txt is
> > not there then you need to write custom rendering.
> >
> > Amarnath
> >
> > "schuhtl@.gmail.com" wrote:
> >
> > > All I want to do is export a report to plain text in a .txt file. Is
> > > this possible? If it is... how do you do it? Thanks!
> > >
> > >|||If it is a special requirement (and one timer) to take it to txt, then export
to CSV/excel option and then convert to txt. I have seen an article with
examples on MSDN please search for "custom rendering". You should get some
examples.
Amarnath
"schuhtl@.gmail.com" wrote:
> Amarnath,
> Thanks for your response. I don't see text as a rendering format in
> the config file.
> I am sure I am not the only person who has asked for this. Is there a
> create your own rendering options for dummy's guide anywhere? I
> normally would not export a report to a txt file but in this case it is
> a requirement.
>
> Amarnath wrote:
> > I dont think it is possible. Just check the config file for a list of
> > supported rendering format. Infact by default some of the rendering options
> > are disabled. Depending on your requirements you can enable it. If .txt is
> > not there then you need to write custom rendering.
> >
> > Amarnath
> >
> > "schuhtl@.gmail.com" wrote:
> >
> > > All I want to do is export a report to plain text in a .txt file. Is
> > > this possible? If it is... how do you do it? Thanks!
> > >
> > >
>
Friday, March 9, 2012
export to flat file - text qualifier problem
I'm exporting using a query to a flat .txt file. The problem I'm encountering is when I export the data and then open the .txt file into excel some columns cause line breaks to the next row. The columns that are breaking to a new row are varchar fields where the user has entered text into the field with double quotes ".
When I export, I'm using row delimiter {CR}{LF} column delimiter Comma and text qualifier Double Quote (")
Is there a way to prevent this from happening when I export and open the flat file into Excel?
I tried using replace, but I was getting a syntax error in my query. Here is the query without using replace:
SELECT e.session_date, l.lab_no, i.first_name + ' ' + i.last_name AS Teacher,
tt.name, d.district_name, s.school_name, t.title, a.q1 AS Question1, a.q2 AS Question2,
a.q3 AS Question3, a.q4 AS Question4, a.q5 AS Question5, a.q6 AS Question6, a.q7 AS Question7,
a.q8 AS Question8, a.q9 AS Question9, a.q10 AS Question10
FROM evaluation e
LEFT OUTER JOIN training t ON t.id = e.training
LEFT OUTER JOIN lab l ON l.id = e.lab_no
LEFT OUTER JOIN instructor i ON i.id = e.instructor
LEFT OUTER JOIN trainee tt ON tt.id = e.trainee
LEFT OUTER JOIN district d ON d.id = e.district
LEFT OUTER JOIN school s ON s.id = e.school
LEFT OUTER JOIN answers a ON a.id = e.answers
WHERE session_date >= '20070401' AND session_date < '20070501'
I would need to use the replace on columns a.q7, a.q8, a.q9, and a.q10
I tried using another delimiter...pipes (|) and that didn't work? Maybe I was attempting it incorrectly?
Thanks in advance for any help.I got Your problem
suppose your column is
a7 = AA"DS
a8 = SD"AD
a9 = WR"TY
a10 = DGHR
now you have given text qualifier as double quote -> "
so while preparing data for export your fields will be treated as
a7 = "AA"DS"
a8 = "SD"AD"
a9 = "WR"TY"
a10 = "DGHR"
since they are text
did you get my point now... AA will be treated as one piece of text instead of AA"DS.....since there is a start " and an end " which defines a text i.e AA
try using a different symbol for text qualifier, a symbol which does not exist in a7,a8,a9,a10 and that should do your job....
i know I have not explained it that clearly but i hope you are getting what i am trying to convey to you......|||Hi Nick,
thanks for replying to my post. yes, I think that is exactly the problem and I understand what you are saying. How do I use a different text qualifier though? The only ones available are a comma, double quote or none?
Thanks for your help.|||the thing is why do you need a text qualifier......if eventually you are going to import the data into another table or the same table you don't need to specify the text qualifier..... SQL server will directly import the data without a hitch.....has worked for me...try it out if that is the case....
Also if you import directly without the text qualifier to excel it should work fine.....try it out and let us know what happens
When I export, I'm using row delimiter {CR}{LF} column delimiter Comma and text qualifier Double Quote (")
Is there a way to prevent this from happening when I export and open the flat file into Excel?
I tried using replace, but I was getting a syntax error in my query. Here is the query without using replace:
SELECT e.session_date, l.lab_no, i.first_name + ' ' + i.last_name AS Teacher,
tt.name, d.district_name, s.school_name, t.title, a.q1 AS Question1, a.q2 AS Question2,
a.q3 AS Question3, a.q4 AS Question4, a.q5 AS Question5, a.q6 AS Question6, a.q7 AS Question7,
a.q8 AS Question8, a.q9 AS Question9, a.q10 AS Question10
FROM evaluation e
LEFT OUTER JOIN training t ON t.id = e.training
LEFT OUTER JOIN lab l ON l.id = e.lab_no
LEFT OUTER JOIN instructor i ON i.id = e.instructor
LEFT OUTER JOIN trainee tt ON tt.id = e.trainee
LEFT OUTER JOIN district d ON d.id = e.district
LEFT OUTER JOIN school s ON s.id = e.school
LEFT OUTER JOIN answers a ON a.id = e.answers
WHERE session_date >= '20070401' AND session_date < '20070501'
I would need to use the replace on columns a.q7, a.q8, a.q9, and a.q10
I tried using another delimiter...pipes (|) and that didn't work? Maybe I was attempting it incorrectly?
Thanks in advance for any help.I got Your problem
suppose your column is
a7 = AA"DS
a8 = SD"AD
a9 = WR"TY
a10 = DGHR
now you have given text qualifier as double quote -> "
so while preparing data for export your fields will be treated as
a7 = "AA"DS"
a8 = "SD"AD"
a9 = "WR"TY"
a10 = "DGHR"
since they are text
did you get my point now... AA will be treated as one piece of text instead of AA"DS.....since there is a start " and an end " which defines a text i.e AA
try using a different symbol for text qualifier, a symbol which does not exist in a7,a8,a9,a10 and that should do your job....
i know I have not explained it that clearly but i hope you are getting what i am trying to convey to you......|||Hi Nick,
thanks for replying to my post. yes, I think that is exactly the problem and I understand what you are saying. How do I use a different text qualifier though? The only ones available are a comma, double quote or none?
Thanks for your help.|||the thing is why do you need a text qualifier......if eventually you are going to import the data into another table or the same table you don't need to specify the text qualifier..... SQL server will directly import the data without a hitch.....has worked for me...try it out if that is the case....
Also if you import directly without the text qualifier to excel it should work fine.....try it out and let us know what happens
Sunday, February 19, 2012
Export to CSV without header line
Hi,
Is it possible to export to CSV without header line?
I create report for my user where they can retrieve data in csv(txt) file
for their further use.
I would like to skip generation of header line in CVS rendering if it is
possible.
Thanks
DonThanks for the tip.
But where do you set the device info?
Sissel
"Gastón PÃrez" wrote:
> You can do this setting a parameter in the device info
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
> Gaston.-
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:ECC0D669-97A0-49D4-9914-50ABCD4C3223@.microsoft.com...
> > Hi,
> >
> > Is it possible to export to CSV without header line?
> >
> > I create report for my user where they can retrieve data in csv(txt) file
> > for their further use.
> > I would like to skip generation of header line in CVS rendering if it is
> > possible.
> >
> > Thanks
> > Don
> >
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"Don" wrote:
> Hi,
> Is it possible to export to CSV without header line?
> I create report for my user where they can retrieve data in csv(txt) file
> for their further use.
> I would like to skip generation of header line in CVS rendering if it is
> possible.
> Thanks
> Don
>|||in sql server 2000 reporting services, can i export csv without header line?
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> Try www.sqlscripter.com to export data to text/csv.
> It's free.
>
> "Don" wrote:
>> Hi,
>> Is it possible to export to CSV without header line?
>> I create report for my user where they can retrieve data in csv(txt) file
>> for their further use.
>> I would like to skip generation of header line in CVS rendering if it is
>> possible.
>> Thanks
>> Don|||Good question. I'd like to know how to export csv without header lines in
RS2005. AND can someone tell me how to customer csv render in a data-drive
subscription? Can we do that automatically so that users who retrieve the
report from a designed folder will be able to open the csv file automatically
w/o headers? thanks
"Peter Fuller" wrote:
> in sql server 2000 reporting services, can i export csv without header line?
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> > Try www.sqlscripter.com to export data to text/csv.
> > It's free.
> >
> >
> > "Don" wrote:
> >
> >> Hi,
> >>
> >> Is it possible to export to CSV without header line?
> >>
> >> I create report for my user where they can retrieve data in csv(txt) file
> >> for their further use.
> >> I would like to skip generation of header line in CVS rendering if it is
> >> possible.
> >>
> >> Thanks
> >> Don
> >>
>
>
Is it possible to export to CSV without header line?
I create report for my user where they can retrieve data in csv(txt) file
for their further use.
I would like to skip generation of header line in CVS rendering if it is
possible.
Thanks
DonThanks for the tip.
But where do you set the device info?
Sissel
"Gastón PÃrez" wrote:
> You can do this setting a parameter in the device info
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_34fa.asp
> Gaston.-
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:ECC0D669-97A0-49D4-9914-50ABCD4C3223@.microsoft.com...
> > Hi,
> >
> > Is it possible to export to CSV without header line?
> >
> > I create report for my user where they can retrieve data in csv(txt) file
> > for their further use.
> > I would like to skip generation of header line in CVS rendering if it is
> > possible.
> >
> > Thanks
> > Don
> >
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"Don" wrote:
> Hi,
> Is it possible to export to CSV without header line?
> I create report for my user where they can retrieve data in csv(txt) file
> for their further use.
> I would like to skip generation of header line in CVS rendering if it is
> possible.
> Thanks
> Don
>|||in sql server 2000 reporting services, can i export csv without header line?
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> Try www.sqlscripter.com to export data to text/csv.
> It's free.
>
> "Don" wrote:
>> Hi,
>> Is it possible to export to CSV without header line?
>> I create report for my user where they can retrieve data in csv(txt) file
>> for their further use.
>> I would like to skip generation of header line in CVS rendering if it is
>> possible.
>> Thanks
>> Don|||Good question. I'd like to know how to export csv without header lines in
RS2005. AND can someone tell me how to customer csv render in a data-drive
subscription? Can we do that automatically so that users who retrieve the
report from a designed folder will be able to open the csv file automatically
w/o headers? thanks
"Peter Fuller" wrote:
> in sql server 2000 reporting services, can i export csv without header line?
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:CCE0FED9-1AA5-4C0A-8041-A5DE99D66C42@.microsoft.com...
> > Try www.sqlscripter.com to export data to text/csv.
> > It's free.
> >
> >
> > "Don" wrote:
> >
> >> Hi,
> >>
> >> Is it possible to export to CSV without header line?
> >>
> >> I create report for my user where they can retrieve data in csv(txt) file
> >> for their further use.
> >> I would like to skip generation of header line in CVS rendering if it is
> >> possible.
> >>
> >> Thanks
> >> Don
> >>
>
>
Export to .txt file using SQL Server DTS
I am exporting a SQL Server 2000 table to an "errors.txt" file. I can get this to work just fine. I need to be able to plug the date and record count into my file name...(ex. MMYYYYCCCCerrors.txt).
The first 6 characters are the month and year of the file. CCCC represents a record count with leading zeroes.
Current Process - An ASP page has an "Export" button on it. After the "Export" button is clicked, a Stored Proc is called and the SP executes the DTS package. The DTS package just copies the data from a table to a .txt file.
Thanks.Hello Len,
I would propose that you add an ActiveXTask to your DTS-package, which manipulates the properties of your export task.
This ActiveXskript defines a variable with your desired file name (e.g. MMYYYYCCCCerrors.txt) and checks if this files exists in OS level. If it does exist, increase CCCC by 1 and check again.
For better help on this topic look at this article (http://www.sqldts.com/default.aspx?235). Generally, this site offers me quite a lot of help.
Hope this helps you! Otherwise post a reply.
Greetings,
Carsten
The first 6 characters are the month and year of the file. CCCC represents a record count with leading zeroes.
Current Process - An ASP page has an "Export" button on it. After the "Export" button is clicked, a Stored Proc is called and the SP executes the DTS package. The DTS package just copies the data from a table to a .txt file.
Thanks.Hello Len,
I would propose that you add an ActiveXTask to your DTS-package, which manipulates the properties of your export task.
This ActiveXskript defines a variable with your desired file name (e.g. MMYYYYCCCCerrors.txt) and checks if this files exists in OS level. If it does exist, increase CCCC by 1 and check again.
For better help on this topic look at this article (http://www.sqldts.com/default.aspx?235). Generally, this site offers me quite a lot of help.
Hope this helps you! Otherwise post a reply.
Greetings,
Carsten
Subscribe to:
Posts (Atom)