Exporting data to excel from T-SQL Resultset

I have started just using SSIS. I needed to transfer result set from SQL statements like SELECT * FROM Employee to excel spreadsheet. One way of getting this is using import and export wizard from management studio.

As my sql statement is complex joining many tables. I am wondering wheteher there are other best ways to deal such type of transformations.

I am sure this is very simple question but yours feedback will be much appreciated.

You can use a SQL statement in an OLE DB source and then you can hook that OLE DB source up to an Excel destination.|||

Notice that the Excel file must exists by the time the dataflow is reached and that if there is data on it; the new execution will append the new rows at the end of the existing ones; but youc an create a new excel file on each run (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=941982&SiteID=1)

There ahve been interesting discussion on this forum about diffrent escenarios involvong excel destination and source. make sure you use the search option at the top of this page

sql

Export Wizard disturb the order of data while exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.

Please give me the solutions.

If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query

|||i am using order by in my query . Data is correct in Access but order is not correct.|||

Dear All,

Thankyou for your replies.

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb the order of data while exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.

Please give me the solutions.

If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query

|||i am using order by in my query . Data is correct in Access but order is not correct.|||

Dear All,

Thankyou for your replies.

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb the order of data while exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.

Please give me the solutions.

If the only diffrence in the tables is the order of the rows I would not call it incorrect data. If you need the data to be shown in a especific order you need to use Order By clause in your query

|||i am using order by in my query . Data is correct in Access but order is not correct.|||

Dear All,

Thankyou for your replies.

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thankx again for your replies. However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb order of data during exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.

Please give me the solutions.

Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.

This is generally true of all Relational Databases.

Did you try placing an index on the Access table?

|||

True, I believe this more a call from Access 2003 settings to see how data is displayed.

Also check the code in Access about getting this data display.

|||

Dear All,

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thank you all of you for your replies.

However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb order of data during exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.

Please give me the solutions.

Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.

This is generally true of all Relational Databases.

Did you try placing an index on the Access table?

|||

True, I believe this more a call from Access 2003 settings to see how data is displayed.

Also check the code in Access about getting this data display.

|||

Dear All,

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thank you all of you for your replies.

However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export Wizard disturb order of data during exporting Data to Acess 2003 from SQL server 2005

I am using the following query to export data from sql server to ms access in export data wizard:

SELECT * FROM myView where myID = 123

Order by varcharColumnName1,varcharColumnName2 ,intColumnName3

This query will fetch about 7, 00,000 records.

SQL server 2005 shows the correct order, but Data in access table shows Incorrect order of data.

Please give me the solutions.

Speaking logically, sets and tables don’t have order. Order can be specified in a SELECT. Order can be the result of a clustered index but typically there is no guarantee of order without an explicit ORDER BY.

This is generally true of all Relational Databases.

Did you try placing an index on the Access table?

|||

True, I believe this more a call from Access 2003 settings to see how data is displayed.

Also check the code in Access about getting this data display.

|||

Dear All,

I have found a temperory solution. After exporting data to access file. Open table in design view,Right click on tble and select properties. A dialog box will open. Set the order by property like COL1,COL2 etc. CLose the dialog box. Open table, now data is sorted.

Thank you all of you for your replies.

However if you find any better solution please inform me at zeeshanahmedskt@.hotmail.com or post here.

Export window does not close

Report Server 2005; .NET framework 2.0; IE 6.0

I view a report in Report Manager, then I click the drop-down box and select an output type, select Save, and a new blank web page opens, then the small export window opens asking for the filename. The file is created, then I close the export window, but the blank web page window does not close. I can see that the web page showing my original view of the report is still open, but I have to close the blank web page myself. While not really an error, this is an annoyance for users. Is there a setting that will help prevent this problem? Is this a bug?

Thanks for any help.

Getting same problem as mentioned by DawnJ..

i want to rectify this problem as soon as possible..Reply back friends if u have relevant information for this thread...

Thanks in Advance

Bhoopathi...

Export window does not close

Report Server 2005; .NET framework 2.0; IE 6.0

I view a report in Report Manager, then I click the drop-down box and select an output type, select Save, and a new blank web page opens, then the small export window opens asking for the filename. The file is created, then I close the export window, but the blank web page window does not close. I can see that the web page showing my original view of the report is still open, but I have to close the blank web page myself. While not really an error, this is an annoyance for users. Is there a setting that will help prevent this problem? Is this a bug?

Thanks for any help.

Getting same problem as mentioned by DawnJ..

i want to rectify this problem as soon as possible..Reply back friends if u have relevant information for this thread...

Thanks in Advance

Bhoopathi...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

Export to XML file from SQL Server 2005

I have a stored procedure which returns XML using SELECT with the FOR XML PATH, TYPE commands.

How do I pipe the results to a file? I had assumed that Integration Services would do this, but I see nothing about XML as a destination, only as a source.

You can use the sp_OA stored procs.

HTH.

|||After you run the FOR XML command, you can get a string like xml. Click that you can see the xml file itself. Then you just save this file. Hope this answer your question.|||

Hello,

Well, I already try this, runing a query in Data flow source (OLE BD Source) and send the result to a flat file destination. The problem now is matching types. OLE DB Source generate DT_IMAGE and until now I couldn't put directily into a xmlt/html file...

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

Hello,

I'm using SQL Reporting services 2000 SP2.
I create a report and when i select pdf format in the export dropdownlist and i click on export, a popup (open/save) appears but when i click ok, i've an error message : "IE can't download Format=PDF of IP_ADRESSE".

If i do the same thing with excel type in the dropdownlist it works!

Do you know why?Have you tried looking in the log files or Event viewer on the server?|||Yes I have an exception in the reporting services log but nothing in the event viewer.
The exception :
[code]
w3wp!library!d10!08/25/2006-14:18:52:: i INFO: Call to RenderNext( '/EPMDB_Report/EPM_Full_Dashboard' )
w3wp!chunks!d10!08/25/2006-14:18:52:: i INFO: ### GetReportChunk('RenderingInfo_PDF', 2), chunk was not found! this=3ffd3365-b936-477e-9108-c7ad88799346
w3wp!reportrendering!d10!08/25/2006-14:18:52:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
at System.String.Substring(Int32 startIndex, Int32 length)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.ProcessString(TextBox context, String text, CanvasFontPDF font, CanvasPenPDF pen, Double left, Double top, Double paddingTop, Double width, Double height, Boolean wordTrim, String fontLanguage, Int32& charactersFitted, Int32& linesFilled, String& streamString)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.DrawString(TextBox context, OutlineObjects outlines, Boolean wordTrim, String text, CanvasFont font, CanvasPen pen, Double left, Double top, Double paddingTop, Double width, Double height, String fontLanguage, ReportUrl hyperLinkURL)
at Microsoft.ReportingServices.Rendering.ImageRenderer.TextBox.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.Rectangle.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.ProcessPages(CompositionBase theComposition, Report theReport, Boolean needTotalPages, Int32 startPage, Int32 endPage, Boolean render)
at Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport.RenderReport(Report report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStreamCallback)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
End of inner exception stack trace
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
End of inner exception stack trace
w3wp!webserver!d10!08/25/2006-14:18:52:: e ERROR: Reporting Services error Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
[/code]

It seems to be a problem with the length parameter but i don't known what to do.

Any idea?|||Ok I find th problem.
In my rdl file I have the language define as 'en' and for exporting in pdf the format has to been syntaxed like this: xx-XX.
I put my rdl in en-US and it works.

Thanks @.+|||I found I had put some HTTP headers in at the website level on IIS to disable caching on some non-related ASP applications running in the same IIS instance.

I removed the HTTP headers:

Cache-Control: no-cache

Pragma: no-cache

Export to pdf fail

Hello,

I'm using SQL Reporting services 2000 SP2.
I create a report and when i select pdf format in the export dropdownlist and i click on export, a popup (open/save) appears but when i click ok, i've an error message : "IE can't download Format=PDF of IP_ADRESSE".

If i do the same thing with excel type in the dropdownlist it works!

Do you know why?
Have you tried looking in the log files or Event viewer on the server?|||Yes I have an exception in the reporting services log but nothing in the event viewer.
The exception :
[code]
w3wp!library!d10!08/25/2006-14:18:52:: i INFO: Call to RenderNext( '/EPMDB_Report/EPM_Full_Dashboard' )
w3wp!chunks!d10!08/25/2006-14:18:52:: i INFO: ### GetReportChunk('RenderingInfo_PDF', 2), chunk was not found! this=3ffd3365-b936-477e-9108-c7ad88799346
w3wp!reportrendering!d10!08/25/2006-14:18:52:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
at System.String.Substring(Int32 startIndex, Int32 length)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.ProcessString(TextBox context, String text, CanvasFontPDF font, CanvasPenPDF pen, Double left, Double top, Double paddingTop, Double width, Double height, Boolean wordTrim, String fontLanguage, Int32& charactersFitted, Int32& linesFilled, String& streamString)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.DrawString(TextBox context, OutlineObjects outlines, Boolean wordTrim, String text, CanvasFont font, CanvasPen pen, Double left, Double top, Double paddingTop, Double width, Double height, String fontLanguage, ReportUrl hyperLinkURL)
at Microsoft.ReportingServices.Rendering.ImageRenderer.TextBox.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.Rectangle.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.ProcessPages(CompositionBase theComposition, Report theReport, Boolean needTotalPages, Int32 startPage, Int32 endPage, Boolean render)
at Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport.RenderReport(Report report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStreamCallback)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
End of inner exception stack trace
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
End of inner exception stack trace
w3wp!webserver!d10!08/25/2006-14:18:52:: e ERROR: Reporting Services error Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
[/code]

It seems to be a problem with the length parameter but i don't known what to do.

Any idea?
|||Ok I find th problem.
In my rdl file I have the language define as 'en' and for exporting in pdf the format has to been syntaxed like this: xx-XX.
I put my rdl in en-US and it works.

Thanks @.+
|||I found I had put some HTTP headers in at the website level on IIS to disable caching on some non-related ASP applications running in the same IIS instance.

I removed the HTTP headers:
Cache-Control: no-cache Pragma: no-cache

Export To Excel problem

Hi,
When I select the export to excel file format, I get the following in the
excel file:
"Data Regions within table/matrix cells are ignored".
Is this a known bug in RS..
Any info will be appreciated.
Thanks
SureshHi,
The message tells you exactly how the Excel export does with data regions
encapsulated in tables/matrixes. The same is for subreports.
This behavior is by design.
--
| Jan Pieter Posthuma
--
"Suresh" wrote:
> Hi,
> When I select the export to excel file format, I get the following in the
> excel file:
> "Data Regions within table/matrix cells are ignored".
> Is this a known bug in RS..
> Any info will be appreciated.
> Thanks
> Suresh
>

Export to Excel

I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?
MadhivananIf you actually want an Excel workbook as output, use a DTS package instead.
bcp is used only for text (or native) output.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115982106.363043.37730@.o13g2000cwo.googlegroups.com...
I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?
Madhivanan|||Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution
Madhivanan|||The books online (BOL) discuss DTS. You can also post the question in the
.dts newsgroup. Also, have you tried just using the wizard? This sounds
like a straightforward export.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115983608.621366.69350@.g14g2000cwa.googlegroups.com...
Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution
Madhivanan

Export sp_columns result into a temp table?

Can anyone show me the syntax for exporting the result of sp_columns into a new table?
I've tried using "SELECT EXEC sp_columns...INTO 'column_info'" but I keep getting syntax errors that are unclear to me.
Any help would be greatly appreciated.
abYou need to create a table with the same number of columns as the result set first

Look at

USE Master
GO
sp_helptext sp_columns
GO

to find the detail

Then do INSERT INTO yourTable(cOL LIST) exec SP_Columns TABLE|||Originally posted by Brett Kaiser
You need to create a table with the same number of columns as the result set first

Look at

USE Master
GO
sp_helptext sp_columns
GO

to find the detail

Then do INSERT INTO yourTable(cOL LIST) exec SP_Columns TABLE

I thought a SELECT INTO... type statement would create the table for me?|||It doesn't work for the execution of a sproc...

Export schema/DDL

Hey folks,

I am looking for a way to export the DDL for table objects from MS SQL 2K. In EM you can right click a table, select "All Tasks" then "Generate SQL". When you preview the script, you can copy it to the text editor of your choice and save it. I'd like to find a way to do this programatically - either through TSQL commands or DTS would be best. I'm OK to do VBScript w/in DTS but I'm not up to writing a full blown VB app.

I seem to remember in my Sybase days that there was a utility called "defncopy" that could be used to extract DDL but there seems to be no analogue in MS SQL 2K.

The purpose of all this is to extract the data and schema of a table in ASCII text so that we can zip it and burn to CD for archiving purposes.

Any help appreciated!You need version control if your db is in such a state of flux...

but that's just my own opinion (MOO)|||Originally posted by Brett Kaiser
You need version control if your db is in such a state of flux...

but that's just my own opinion (MOO)

Brett - nothing here to indicate "a state of flux" simply we need to be able to extract and archive the structure and data within these tables. We are archiving data that has gone stale and attempting to reduce the utilization on our hard drives. Unfortunately the restrictions of our industry require us to keep the data for a long period of time even if it is not "live".

Stick to the technical question at hand, MOO elsewhere please.|||Follow the Nigel's script from this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32519 to create the script.|||Originally posted by Satya
Follow the Nigel's script from this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32519 to create the script.

Thank you this seems like it will do the trick or serve as a basis for moddification.

Export report to Excel - not workin

HI,
I am running Excel 2000 with RS. I have created a report and when I right
click on the report and select 'Print With', 'Excel', the contents of the
excel file are all machine code and does not contain anything from the
report?
How do you export to excel?
ThanksWhen you do a right mouse click on the report you are just getting some
options for IE. Instead you should select a format and then click on export
in the toolbar. Make sure you have at least service pack 1 installed to
support Excel 2000 format otherwise you will just get a bunch of
gobbledeegook in Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:D40B5446-8385-44FD-BF77-7F7DA003009F@.microsoft.com...
> HI,
> I am running Excel 2000 with RS. I have created a report and when I right
> click on the report and select 'Print With', 'Excel', the contents of the
> excel file are all machine code and does not contain anything from the
> report?
> How do you export to excel?
> Thanks