Exporting report to multiple PDF's

Is it possile for a report to be exported to multiple PDF files, each pdf file would be for a group in the report and would ideally use field information for the group to construct the pdf filename?

The purpose of this is to provide a separate PDF for access over the web, so for example we have a scheduled report that generates a report grouped by sales region we would end up with SalesRegionA.PDF, SalesRegionB.PDF etc.

Hi Hugh,

What you can do is have a parameter in your report which will have the groups...ie you can have a parameter for Sales Region, then what you can do is run batch script, which has a command that calls a .rss file passing in some values -which will export your values to pdf...

you can create a batch script that calls the .rss file like below...

rs -i RunReports.rss -s http:..localhost/reportserver -1 0 -v directory="%1 -v reportname= "Name of report" -v filename="Name of file.pdf" -v format="pdf" -v params="this is were the parameter value goes" -v debug.

rs -i RunReports.rss -s http:..localhost/reportserver -1 0 -v directory="%1 -v reportname= "Name of report 2" -v filename="Name of file.pdf 2" -v format="pdf" -v params="this is were the parameter 2 value goes" -v debug.

(note you can have mutiple statements, allowing you to pass different values in the reportname, params section...

Your .rss file will use these values to render through the reports and export the files, i beleive there are examples on the Web of this.

Or what you can do is use a data driven subscription which is much easier (you will have to create the parameter in your report still). You have to create a table in your SQL Server DB, to hold the parameters ie all the Sales Region and use the Subscription to look at this tables..to do this go to subscriptions in report manager (when viewing the report), then go to data driven subscriptions, then follow the steps which are pretty simple...

Hope this helps.

|||Thans for your suggestions, I'd sort of moved in the direction of a a data driven subscription anyway but I think there's still quite a bit more to do, especially working out how to trigger the subscription from an application - I've started looking at the fireevent api.

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob Boles
This isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:
> I have a query that exports to an Excel spreadsheet. One of the columns in a
> long text field (defined as ntext (16)). When I run the query and export to
> Excel the text is truncated at 255 characters. How do I get all the text to
> export?
> Thanks,
> Bob Boles

Exporting long text fields to Excel

I have a query that exports to an Excel spreadsheet. One of the columns in a
long text field (defined as ntext (16)). When I run the query and export to
Excel the text is truncated at 255 characters. How do I get all the text to
export?
Thanks,
Bob BolesThis isn't an issue with SQL Server - it's that Excel has a 255
characters/cell limit.
There seem to be cases where Excel will allow a cell to 'contain' more
characters, but will not display any of them. Instead the cell displays
pound signs. You have to manually edit the cell contents, cutting out
characters that exceed the maximum and pasting them in to another cell.
You may want to export the text in multiple 255 character (or fewer) chunks
"Bob Boles" wrote:

> I have a query that exports to an Excel spreadsheet. One of the columns in
a
> long text field (defined as ntext (16)). When I run the query and export t
o
> Excel the text is truncated at 255 characters. How do I get all the text t
o
> export?
> Thanks,
> Bob Boles

Export varbinary Data Types to Local PC from SQL Database

Could someone help me with writing the code to export the contents of a varbinary field in my database to make the contents be written to the local harddrive? I can do this in Visual Foxpro but my company wants it in C# and I have no clue about C#.
Thank you:eek:What do you need help doing? Are you looking for someone to write the C# program for you?

-PatP|||You know.......this is the 6th forum I have posted this question to. I must not be asking my question correctly.

I need help writing code to retrieve all my files out of an SQL IMAGE (varbinary) field and make the contents become files back on my hard drive again.|||There are 192 records. I need to retrieve the contents of all the varbinary fields and put the contents back on my hard drive. Some are .xls files, some .doc, some .txt. But, they need to come back out of SQL Server and go back to being files on my hard drive again.

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:

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

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:

Export to Excel Spreadsheet

I am going to export a numeric(14,2) field that containing
the Balance to a CSV file. It will be opened by the
Finance Staff with Excel.
They would like to get the Balance Field opened as
Currency Field (With $ sign). Is it possible for us to
convert the Balance Field or do some manuipulation so that
it means her need ?
Thanks
You might try just prefixing it with a $ e.g. Instead of
SELECT numeric_column
Use
SELECT '$' + RTRIM(numeric_column)
Not sure if Excel will automatically recognize it as currency as a string,
but that's probably as close as you're going to get--SQL Server doesn't have
any clue what Excel is, never mind what Excel commands you would embed to
force the confusion. Besides, since you're exporting to CSV, there's
nothing you can embed anyway... It won't process any Excel commands you
include in a plain text CSV file...
On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0$a601280a@.phx.gbl,
"Paul" <anonymous@.discussions.microsoft.com> wrote:

> I am going to export a numeric(14,2) field that containing
> the Balance to a CSV file. It will be opened by the
> Finance Staff with Excel.
> They would like to get the Balance Field opened as
> Currency Field (With $ sign). Is it possible for us to
> convert the Balance Field or do some manuipulation so that
> it means her need ?
> Thanks
|||Thank you for your advice and it seems work properly.
However, I would like to know why we have to use RTRIM. I
have attempted not to use RTRIM, it gives me an error
message.

>--Original Message--
>You might try just prefixing it with a $ e.g. Instead of
>SELECT numeric_column
>Use
>SELECT '$' + RTRIM(numeric_column)
>Not sure if Excel will automatically recognize it as
currency as a string,
>but that's probably as close as you're going to get--SQL
Server doesn't have
>any clue what Excel is, never mind what Excel commands
you would embed to
>force the confusion. Besides, since you're exporting to
CSV, there's
>nothing you can embed anyway... It won't process any
Excel commands you
>include in a plain text CSV file...
>
>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
$a601280a@.phx.gbl,[vbcol=seagreen]
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
containing[vbcol=seagreen]
that
>.
>
|||SQL needs to convert your numeric_column to a string/char/varchar before it
can perform string manipulations on it. RTRIM does the job, as will CAST(
... AS VARCHAR(xx)).
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:7c5f01c52833$8a494d30$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you for your advice and it seems work properly.
> However, I would like to know why we have to use RTRIM. I
> have attempted not to use RTRIM, it gives me an error
> message.
>
> currency as a string,
> Server doesn't have
> you would embed to
> CSV, there's
> Excel commands you
> $a601280a@.phx.gbl,
> containing
> that
|||> However, I would like to know why we have to use RTRIM.
RTRIM implicitly casts the numeric value as a string. There are several
other functions you can use to do this, such as LTRIM, or you can cast it
explicitly using CAST or CONVERT (I just find those more cumbersome).
If you don't change the numeric value to a varchar, SQL Server will cock up
an eyebrow and look at you funny, asking how you expect it to add '$' to
4.75... They are incompatible types for the addition and/or string
concatenation operator.
A

Export to Excel Spreadsheet

I am going to export a numeric(14,2) field that containing
the Balance to a CSV file. It will be opened by the
Finance Staff with Excel.
They would like to get the Balance Field opened as
Currency Field (With $ sign). Is it possible for us to
convert the Balance Field or do some manuipulation so that
it means her need ?
ThanksYou might try just prefixing it with a $ e.g. Instead of
SELECT numeric_column
Use
SELECT '$' + RTRIM(numeric_column)
Not sure if Excel will automatically recognize it as currency as a string,
but that's probably as close as you're going to get--SQL Server doesn't have
any clue what Excel is, never mind what Excel commands you would embed to
force the confusion. Besides, since you're exporting to CSV, there's
nothing you can embed anyway... It won't process any Excel commands you
include in a plain text CSV file...
On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0$a601280a@.phx.gbl,
"Paul" <anonymous@.discussions.microsoft.com> wrote:
> I am going to export a numeric(14,2) field that containing
> the Balance to a CSV file. It will be opened by the
> Finance Staff with Excel.
> They would like to get the Balance Field opened as
> Currency Field (With $ sign). Is it possible for us to
> convert the Balance Field or do some manuipulation so that
> it means her need ?
> Thanks|||Thank you for your advice and it seems work properly.
However, I would like to know why we have to use RTRIM. I
have attempted not to use RTRIM, it gives me an error
message.
>--Original Message--
>You might try just prefixing it with a $ e.g. Instead of
>SELECT numeric_column
>Use
>SELECT '$' + RTRIM(numeric_column)
>Not sure if Excel will automatically recognize it as
currency as a string,
>but that's probably as close as you're going to get--SQL
Server doesn't have
>any clue what Excel is, never mind what Excel commands
you would embed to
>force the confusion. Besides, since you're exporting to
CSV, there's
>nothing you can embed anyway... It won't process any
Excel commands you
>include in a plain text CSV file...
>
>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
$a601280a@.phx.gbl,
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
>> I am going to export a numeric(14,2) field that
containing
>> the Balance to a CSV file. It will be opened by the
>> Finance Staff with Excel.
>> They would like to get the Balance Field opened as
>> Currency Field (With $ sign). Is it possible for us to
>> convert the Balance Field or do some manuipulation so
that
>> it means her need ?
>> Thanks
>.
>|||SQL needs to convert your numeric_column to a string/char/varchar before it
can perform string manipulations on it. RTRIM does the job, as will CAST(
... AS VARCHAR(xx)).
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:7c5f01c52833$8a494d30$a601280a@.phx.gbl...
> Thank you for your advice and it seems work properly.
> However, I would like to know why we have to use RTRIM. I
> have attempted not to use RTRIM, it gives me an error
> message.
>
>>--Original Message--
>>You might try just prefixing it with a $ e.g. Instead of
>>SELECT numeric_column
>>Use
>>SELECT '$' + RTRIM(numeric_column)
>>Not sure if Excel will automatically recognize it as
> currency as a string,
>>but that's probably as close as you're going to get--SQL
> Server doesn't have
>>any clue what Excel is, never mind what Excel commands
> you would embed to
>>force the confusion. Besides, since you're exporting to
> CSV, there's
>>nothing you can embed anyway... It won't process any
> Excel commands you
>>include in a plain text CSV file...
>>
>>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
> $a601280a@.phx.gbl,
>>"Paul" <anonymous@.discussions.microsoft.com> wrote:
>> I am going to export a numeric(14,2) field that
> containing
>> the Balance to a CSV file. It will be opened by the
>> Finance Staff with Excel.
>> They would like to get the Balance Field opened as
>> Currency Field (With $ sign). Is it possible for us to
>> convert the Balance Field or do some manuipulation so
> that
>> it means her need ?
>> Thanks
>>.|||> However, I would like to know why we have to use RTRIM.
RTRIM implicitly casts the numeric value as a string. There are several
other functions you can use to do this, such as LTRIM, or you can cast it
explicitly using CAST or CONVERT (I just find those more cumbersome).
If you don't change the numeric value to a varchar, SQL Server will cock up
an eyebrow and look at you funny, asking how you expect it to add '$' to
4.75... They are incompatible types for the addition and/or string
concatenation operator.
A

Export to Excel Spreadsheet

I am going to export a numeric(14,2) field that containing
the Balance to a CSV file. It will be opened by the
Finance Staff with Excel.
They would like to get the Balance Field opened as
Currency Field (With $ sign). Is it possible for us to
convert the Balance Field or do some manuipulation so that
it means her need ?
ThanksYou might try just prefixing it with a $ e.g. Instead of
SELECT numeric_column
Use
SELECT '$' + RTRIM(numeric_column)
Not sure if Excel will automatically recognize it as currency as a string,
but that's probably as close as you're going to get--SQL Server doesn't have
any clue what Excel is, never mind what Excel commands you would embed to
force the confusion. Besides, since you're exporting to CSV, there's
nothing you can embed anyway... It won't process any Excel commands you
include in a plain text CSV file...
On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0$a601280a@.phx.gbl,
"Paul" <anonymous@.discussions.microsoft.com> wrote:

> I am going to export a numeric(14,2) field that containing
> the Balance to a CSV file. It will be opened by the
> Finance Staff with Excel.
> They would like to get the Balance Field opened as
> Currency Field (With $ sign). Is it possible for us to
> convert the Balance Field or do some manuipulation so that
> it means her need ?
> Thanks|||Thank you for your advice and it seems work properly.
However, I would like to know why we have to use RTRIM. I
have attempted not to use RTRIM, it gives me an error
message.

>--Original Message--
>You might try just prefixing it with a $ e.g. Instead of
>SELECT numeric_column
>Use
>SELECT '$' + RTRIM(numeric_column)
>Not sure if Excel will automatically recognize it as
currency as a string,
>but that's probably as close as you're going to get--SQL
Server doesn't have
>any clue what Excel is, never mind what Excel commands
you would embed to
>force the confusion. Besides, since you're exporting to
CSV, there's
>nothing you can embed anyway... It won't process any
Excel commands you
>include in a plain text CSV file...
>
>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
$a601280a@.phx.gbl,
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
>
containing[vbcol=seagreen]
that[vbcol=seagreen]
>.
>|||SQL needs to convert your numeric_column to a string/char/varchar before it
can perform string manipulations on it. RTRIM does the job, as will CAST(
... AS VARCHAR(xx)).
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:7c5f01c52833$8a494d30$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you for your advice and it seems work properly.
> However, I would like to know why we have to use RTRIM. I
> have attempted not to use RTRIM, it gives me an error
> message.
>
> currency as a string,
> Server doesn't have
> you would embed to
> CSV, there's
> Excel commands you
> $a601280a@.phx.gbl,
> containing
> that|||> However, I would like to know why we have to use RTRIM.
RTRIM implicitly casts the numeric value as a string. There are several
other functions you can use to do this, such as LTRIM, or you can cast it
explicitly using CAST or CONVERT (I just find those more cumbersome).
If you don't change the numeric value to a varchar, SQL Server will cock up
an eyebrow and look at you funny, asking how you expect it to add '$' to
4.75... They are incompatible types for the addition and/or string
concatenation operator.
A

Export to excel issue

I Have a report, in SQL Reporting servies. THE report has a field where the value shows up as an integer ie 0. However, when the report is exported to excel, the field value shows up as a decimal ie 0.4.

How do i keep the number to show as an integer only ie 0, when exported to excel.

Are you specifying a format code to only display the integer, or is the underlying value actually an integer? If it is the former, what is the format code you are using?

Ian|||Yes, I am specifying a format code of F0 on that textbox for that field.|||Tarana,

This seems like a bug; however, the good news is there is a workaround: try the following format code.

##0

Ian|||Thanks a bunch Ian- that worked very well.

Export to Excel Custom Format

Hi,

I have a problem when exporting a report to Excel.

The problem is with the custom formatting. The report has a field named amount with its format property = C (on the properties window of the textbox in the report designer). When the user exports the report everything seems ok, calculations and so on... but the problem is when from another workbook a cell makes a reference to the cell amount of the exported report. The exported report, has this format [$-1010409]$#,##0.00;($#,##0.00) on the amount cell. In fact every format type of the report designer, begins with [$-1010409].

To reproduce this error:

Make a simple rdl with a textbox format C. Export it to excel. Create a new workbook and make a cell reference to the exported report formated textbox cell (='\\Computer\Folder[ExportedReport.xls]Sheet1'!$E$15). Close the exported report and the new workbook, open the new workbook (not the exported one) and update the reference. Results in a #Ref error.

Tnx of your time and effort.

Sorry for my bad english.

G

Hi,

Did you get a solution to your problem? I am having a similar problem: I would like to convert a value in the [$-1010409]Standard format into an integer. Do you know how that can be done? I am using VSTO.

Thanks in advance,

Pedro

|||

Hi Pedro,

No, I never got the solution for that problem. Sorry

I dont need the solution anymore because Im using SSAS (Cubes in Analisis Service) and Excel 2007 (pivot tables). Not using SSRS.

Export to Excel Custom Format

Hi,

I have a problem when exporting a report to Excel.

The problem is with the custom formatting. The report has a field named amount with its format property = C (on the properties window of the textbox in the report designer). When the user exports the report everything seems ok, calculations and so on... but the problem is when from another workbook a cell makes a reference to the cell amount of the exported report. The exported report, has this format [$-1010409]$#,##0.00;($#,##0.00) on the amount cell. In fact every format type of the report designer, begins with [$-1010409].

To reproduce this error:

Make a simple rdl with a textbox format C. Export it to excel. Create a new workbook and make a cell reference to the exported report formated textbox cell (='\\Computer\Folder[ExportedReport.xls]Sheet1'!$E$15). Close the exported report and the new workbook, open the new workbook (not the exported one) and update the reference. Results in a #Ref error.

Tnx of your time and effort.

Sorry for my bad english.

G

Hi,

Did you get a solution to your problem? I am having a similar problem: I would like to convert a value in the [$-1010409]Standard format into an integer. Do you know how that can be done? I am using VSTO.

Thanks in advance,

Pedro

|||

Hi Pedro,

No, I never got the solution for that problem. Sorry

I dont need the solution anymore because Im using SSAS (Cubes in Analisis Service) and Excel 2007 (pivot tables). Not using SSRS.

Export to Excel - time format

Hi,
I have a field with time in minutes. I have converted it to hh:mm:ss ->
= String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
and set format properties to hh:mm:ss
It looks just like it should, but when exporting to excel, the format type
is General.
I need to add these field, so they can not be General.
After double-clicking the field, they are Custom - hh:mm:ss.
I can not ask my users to double-click some 1000 fields each time they take
out the report...
Any advice?
ThanksUse the .ToString method instead of String.Format
=CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value).ToString("HH:mm:ss")
Cheers!
:-)
Ben Sullins
www.kingofthegreens.com
"Sissel" wrote:
> Hi,
> I have a field with time in minutes. I have converted it to hh:mm:ss ->
> => String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
> and set format properties to hh:mm:ss
> It looks just like it should, but when exporting to excel, the format type
> is General.
> I need to add these field, so they can not be General.
> After double-clicking the field, they are Custom - hh:mm:ss.
> I can not ask my users to double-click some 1000 fields each time they take
> out the report...
> Any advice?
> Thanks|||Hi Ben,
Thanks for your input, but unfortunately it is the same result.
I have been in contact with Microsoft, and they say I have to get the field
in date-format from the database (Oracle), then set time format in the report.
This is my solution:
to_date(TO_CHAR(TRUNC(sysdate)+ NUMTODSINTERVAL(psa.setup_time,'minute'),
'HH24:MI:SS'), 'HH24:MI:SS')
Sissel
"Ben Sullins" wrote:
> Use the .ToString method instead of String.Format
> =CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value).ToString("HH:mm:ss")
> Cheers!
> :-)
> Ben Sullins
> www.kingofthegreens.com
> "Sissel" wrote:
> > Hi,
> >
> > I have a field with time in minutes. I have converted it to hh:mm:ss ->
> > => > String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
> > and set format properties to hh:mm:ss
> >
> > It looks just like it should, but when exporting to excel, the format type
> > is General.
> > I need to add these field, so they can not be General.
> > After double-clicking the field, they are Custom - hh:mm:ss.
> > I can not ask my users to double-click some 1000 fields each time they take
> > out the report...
> >
> > Any advice?
> >
> > Thanks|||Hi again Ben,
I was too quick (it is Monday morning..)
This works
CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value)
and textbox properties Format = HH:mm:ss
It was the CDate function I was looking for (and MS support didn't know
about...)
Thank you very much :-)
"Ben Sullins" wrote:
> Use the .ToString method instead of String.Format
> =CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value).ToString("HH:mm:ss")
> Cheers!
> :-)
> Ben Sullins
> www.kingofthegreens.com
> "Sissel" wrote:
> > Hi,
> >
> > I have a field with time in minutes. I have converted it to hh:mm:ss ->
> > => > String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
> > and set format properties to hh:mm:ss
> >
> > It looks just like it should, but when exporting to excel, the format type
> > is General.
> > I need to add these field, so they can not be General.
> > After double-clicking the field, they are Custom - hh:mm:ss.
> > I can not ask my users to double-click some 1000 fields each time they take
> > out the report...
> >
> > Any advice?
> >
> > Thanks

Export to Excel - Change the Sheet Name

I need to change the sheetname with the field value retured by the stored
procedure.
Say the stored procedure returns 10 rows, when exporting the report , the
report should contain 10 sheets with the sheetname(field 1 value).
How can I do this.
Balaji
--
Message posted via http://www.sqlmonster.comSheet renaming isn't supported in RS SP1. I've seen a couple of threads
where people have created document maps to work around this 'limitation'.
Do a search in the newsgroup for 'document maps'.
--
Adrian M.
MCP
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ee446645890e4ae9a42b10c0d60a07e0@.SQLMonster.com...
>I need to change the sheetname with the field value retured by the stored
> procedure.
> Say the stored procedure returns 10 rows, when exporting the report , the
> report should contain 10 sheets with the sheetname(field 1 value).
> How can I do this.
> Balaji
> --
> Message posted via http://www.sqlmonster.com

Export to CSV with field headers

I need to export multiple tables to a csv file with field headers. BCP will not bring across the field headers and I need to do this on a daily basis.
Any suggestions?
John
You could do it using DTS.
"John" <anonymous@.discussions.microsoft.com> schreef in bericht
news:6EF27F05-C2CB-4339-ADF6-804924A47466@.microsoft.com...
> I need to export multiple tables to a csv file with field headers. BCP
will not bring across the field headers and I need to do this on a daily
basis.
> Any suggestions?
> John
|||Hi,
You can create a DTS package with SQL server as source and Excel file as
target. After saving the package you can schedule it to run daily based on
your requirement.
How to create the package:
1. In Enterprise manager
2. Data Transformation option
3. Local packages
4. Right click and create new package
5. Create one connection for sql server and dest connection for excel and
create a transformation task
6. Save this as a package and schedule it using SQL Agent.
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:6EF27F05-C2CB-4339-ADF6-804924A47466@.microsoft.com...
> I need to export multiple tables to a csv file with field headers. BCP
will not bring across the field headers and I need to do this on a daily
basis.
> Any suggestions?
> John
|||Thanks for the answer - I wanted to go to a csv test file to avoid some of the limitations of Excel - 65K records and stick to a neutral format like csv.
|||Hi John
In that case instead of Excel as destination select the destination as Text
file and in the file name give file_name.CSV. By default the file will be
comma seperated.
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2ABACAEE-F714-40B7-A8EA-D462856A21E0@.microsoft.com...
> Thanks for the answer - I wanted to go to a csv test file to avoid some of
the limitations of Excel - 65K records and stick to a neutral format like
csv.

Export table in comma delimited format - how to eliminate empty spaces

Hello:
When I export a table in comma delimited format, it works OK, but there is
one problem. If the field is varchar (or char) and has the size, for
instance, 50, there are empty spaces in the text file, so it looks like
this:
"CBAS ","Bass Gambling Supplies ",2
"CBW ","Ben's Wholesale ",3
"CTDI ","3 Diamond ",4
"NONE ","NON-CUSTOM ",1
I need to elimitate these free spaces somehow, so the text file would look
like this:
"CBAS","Bass Gambling Supplies",2
"CBW","Ben's Wholesale",3
"CTDI","3 Diamond",4
"NONE","NON-CUSTOM",1
How would I do it, either manually or programmatically?
I would appreciate your help.
Thank you,
Peter AfoninDid you try RTRIM() to remove the trailing spaces from the Stirngs
HTH
Satish Balusa
Corillian Corp.
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:efJOp115DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hello:
> When I export a table in comma delimited format, it works OK, but there is
> one problem. If the field is varchar (or char) and has the size, for
> instance, 50, there are empty spaces in the text file, so it looks like
> this:
> "CBAS ","Bass Gambling Supplies ",2
> "CBW ","Ben's Wholesale ",3
> "CTDI ","3 Diamond ",4
> "NONE ","NON-CUSTOM ",1
> I need to elimitate these free spaces somehow, so the text file would look
> like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
> I would appreciate your help.
> Thank you,
> --
> Peter Afonin
>

---

|||Peter,

quote:

---

> When I export a table in comma delimited format, it works OK, but
> there is one problem. If the field is varchar (or char) and has
> the size, for instance, 50, there are empty spaces in the text
> file, so it looks like this:
> I need to elimitate these free spaces somehow, so the text file
> would look like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?

---

You need to use a format file for this. Using the pubs..authors
tables
as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote that
just
returns NULL. It's just a little trick to get the leading quote on
the first
column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
Linda|||Thank you everyone for your help, I'll try this.
Peter
"lindawie" <lindawie@.my-deja.com> wrote in message
news:#f4ibO25DHA.488@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Peter,
>
> You need to use a format file for this. Using the pubs..authors
> tables
> as an example, we'll bcp out of a view that looks like this:
> use pubs
> go
> create view authors_csv as
> select null first_quote, * from authors
>
> Note that we are including a dummy column called first_quote that
> just
> returns NULL. It's just a little trick to get the leading quote on
> the first
> column.
> The format file looks like this:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 11 "\",\"" 2 au_id ""
> 3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
> 4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
> 5 SQLCHAR 0 12 "\",\"" 5 phone ""
> 6 SQLCHAR 0 40 "\",\"" 6 address ""
> 7 SQLCHAR 0 20 "\",\"" 7 city ""
> 8 SQLCHAR 0 2 "\",\"" 8 state ""
> 9 SQLCHAR 0 5 "\",\"" 9 zip ""
> 10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
> That dummy column is also in the format file to get the leading
> quote on au_id.
> Here's the command line:
> bcp pubs..authors_csv out
> authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
> Linda
>

---

Export table in comma delimited format - how to eliminate empty spaces

Hello:
When I export a table in comma delimited format, it works OK, but there is
one problem. If the field is varchar (or char) and has the size, for
instance, 50, there are empty spaces in the text file, so it looks like
this:
"CBAS ","Bass Gambling Supplies ",2
"CBW ","Ben's Wholesale ",3
"CTDI ","3 Diamond ",4
"NONE ","NON-CUSTOM ",1
I need to elimitate these free spaces somehow, so the text file would look
like this:
"CBAS","Bass Gambling Supplies",2
"CBW","Ben's Wholesale",3
"CTDI","3 Diamond",4
"NONE","NON-CUSTOM",1
How would I do it, either manually or programmatically?
I would appreciate your help.
Thank you,
--
Peter AfoninDid you try RTRIM() to remove the trailing spaces from the Stirngs
--
HTH
Satish Balusa
Corillian Corp.
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:efJOp115DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hello:
> When I export a table in comma delimited format, it works OK, but there is
> one problem. If the field is varchar (or char) and has the size, for
> instance, 50, there are empty spaces in the text file, so it looks like
> this:
> "CBAS ","Bass Gambling Supplies ",2
> "CBW ","Ben's Wholesale ",3
> "CTDI ","3 Diamond ",4
> "NONE ","NON-CUSTOM ",1
> I need to elimitate these free spaces somehow, so the text file would look
> like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
> I would appreciate your help.
> Thank you,
> --
> Peter Afonin
>|||Peter,
> When I export a table in comma delimited format, it works OK, but
> there is one problem. If the field is varchar (or char) and has
> the size, for instance, 50, there are empty spaces in the text
> file, so it looks like this:
> I need to elimitate these free spaces somehow, so the text file
> would look like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
You need to use a format file for this. Using the pubs..authors
tables
as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote that
just
returns NULL. It's just a little trick to get the leading quote on
the first
column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
Linda|||Thank you everyone for your help, I'll try this.
Peter
"lindawie" <lindawie@.my-deja.com> wrote in message
news:#f4ibO25DHA.488@.TK2MSFTNGP12.phx.gbl...
> Peter,
> > When I export a table in comma delimited format, it works OK, but
> > there is one problem. If the field is varchar (or char) and has
> > the size, for instance, 50, there are empty spaces in the text
> > file, so it looks like this:
> >
> > I need to elimitate these free spaces somehow, so the text file
> > would look like this:
> >
> > "CBAS","Bass Gambling Supplies",2
> > "CBW","Ben's Wholesale",3
> > "CTDI","3 Diamond",4
> > "NONE","NON-CUSTOM",1
> >
> > How would I do it, either manually or programmatically?
> You need to use a format file for this. Using the pubs..authors
> tables
> as an example, we'll bcp out of a view that looks like this:
> use pubs
> go
> create view authors_csv as
> select null first_quote, * from authors
>
> Note that we are including a dummy column called first_quote that
> just
> returns NULL. It's just a little trick to get the leading quote on
> the first
> column.
> The format file looks like this:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 11 "\",\"" 2 au_id ""
> 3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
> 4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
> 5 SQLCHAR 0 12 "\",\"" 5 phone ""
> 6 SQLCHAR 0 40 "\",\"" 6 address ""
> 7 SQLCHAR 0 20 "\",\"" 7 city ""
> 8 SQLCHAR 0 2 "\",\"" 8 state ""
> 9 SQLCHAR 0 5 "\",\"" 9 zip ""
> 10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
> That dummy column is also in the format file to get the leading
> quote on au_id.
> Here's the command line:
> bcp pubs..authors_csv out
> authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
> Linda
>

Export table containing BIT data

I'm trying to export a table that contains a bit field into a text file to be used for a bulk insert into another database.

when i export the data from SQL enterprise manager, the bit field is exported as the text TRUE or FALSE ?!!!

this data then cant be bulk inserted as its not a BIT anymore.

how can bit fields be properly exported as 1 or 0 ?Are you doing this in a DTS Package? Use CAST or CONVERT on the BIT field and change the output to an INTEGER or CHAR(1). They are basically the same thing but in this scenario either or is fine; since BIT transformation are not explicit and generally require no intervention to get the value of 1 or 0, however if I'm not mistaken, using a Text ODBC driver causes BITs to be converted to TRUE/FALSE. That's why I asked if you are using DTS, it's ODBC driven. If you are using DTS, you will need to write a query in the Source tab using one of the functions below.

Using CAST

CAST(bYourBit AS INTEGER) AS bYourBit

Using CONVERT

CONVERT(CHAR(1),bYourBit) AS bYourBit