Exporting report to excel with subreports problem

Hello,

I got a error when exporting to excel one report that has a table and in a column it has a subreport.

Is this possible to do?

What is the work around?

The error that i got it is the following.

Server Error in '/Reports' Application.
--

For more information about this error navigate to the report server on the local server machine, or enable remote errors
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors]

[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +489
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +959
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +84
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +143
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

--

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

Thank you

It is my understanding that the sub reports do NOT export to excel. I re-wrote many reports when I had to provide that functionality.

I realize this dosn't directly answer your question but rather than spend hours addressing the problem, you could re-do the report and get the result you want.

Tim

|||Well not what i wanted to hear, but thanks anyway

Exporting report to excel [How to set worksheet name]

I am inserting page break after a table to put different data on different
sheet.How can i name the resultant work sheet {like table1 should be
DATA,table2 should be MYSummary}unfortunately, it is not possible. to rename your sheets.
Amarnath, MCTS
"Kamii47" wrote:
> I am inserting page break after a table to put different data on different
> sheet.How can i name the resultant work sheet {like table1 should be
> DATA,table2 should be MYSummary}

Exporting Problem in crystal reports

Hi all

I am dispalying the data in crystal reports using selection formula from table.I am using Asp.net 1.1 version.Data is coming properly according to the selection criteria. But while exporting to any format like (pdf,excel,word) irrespective of the selection formulae all the data from the table is exporting.
Can any one help me.

Regrds
Syamreply

Exporting MS SQL Server or Oracle Databases to MySQL

I also posted similarly in mailing.database.mysql

I have created a rather large (60 table database) website dealio in PHP that
works with MS SQL Server, Oracle, MySQL and presumably any other data base I
add to
the wrapper functions that I created. All the development was done in
Oracle, but now its time to create that large datastructure again in
MySQL -- now I've built this all in notepad for Oracle (twice) and built it
in SQL Server using the SQL Server import tools (pretty nice) -- is there
anything that I can do that to get this datastructure and maybe even the
data into MySQL using Oracle/MS SQL Server tools ?

James"James Alexander Starritt"
<jamesstarrittRemovethefollowingtoemailme@.hotmail.c om> wrote in message
news:403ac257_1@.newspeer2.tds.net...
> I also posted similarly in mailing.database.mysql
> I have created a rather large (60 table database) website dealio in PHP
that
> works with MS SQL Server, Oracle, MySQL and presumably any other data base
I
> add to
> the wrapper functions that I created. All the development was done in
> Oracle, but now its time to create that large datastructure again in
> MySQL -- now I've built this all in notepad for Oracle (twice) and built
it
> in SQL Server using the SQL Server import tools (pretty nice) -- is there
> anything that I can do that to get this datastructure and maybe even the
> data into MySQL using Oracle/MS SQL Server tools ?

SQL Server will permit the creation of scripts
for the data tables that should be useful, and the
bcp function should handle the data.

Pete Brown
Falls Creek
NSW
Oz
|||"James Alexander Starritt" <jamesstarrittRemovethefollowingtoemailme@.hotmail.c om> wrote in message news:<403ac257_1@.newspeer2.tds.net>...
> I also posted similarly in mailing.database.mysql
> I have created a rather large (60 table database) website dealio in PHP that
> works with MS SQL Server, Oracle, MySQL and presumably any other data base I
> add to
> the wrapper functions that I created. All the development was done in
> Oracle, but now its time to create that large datastructure again in
> MySQL -- now I've built this all in notepad for Oracle (twice) and built it
> in SQL Server using the SQL Server import tools (pretty nice) -- is there
> anything that I can do that to get this datastructure and maybe even the
> data into MySQL using Oracle/MS SQL Server tools ?
> James

From the MSSQL side, you could start by scripting the objects from
Enterprise Manager, then reviewing the code and making any
modifications needed to handle different data types, syntax etc. I
know nothing about MySQL, so I have no idea how large or small a task
this would be, except to guess that procedural code (stored
procedures, triggers, user-defined functions) may be the hardest part.
The resulting file can then be run in MySQL.

To move the data over, there are several different options. You can
use BCP or DTS to export to flat files, then import them into MySQL
using whatever tools it provides for bulk import. Alternatively, if
there is an ODBC driver for MySQL, you could use DTS to move the data
directly from MSSQL and/or Oracle to MySQL. Finally, you may be able
to create a linked server on MSSQL, then simply do an INSERT ...
SELECT ... to move the data.

Simon

Exporting From SQL SERVER

When I am trying to export a table from SQL Server Enterprise (using
Enterprise Manager), I am gettin errors at the end. It is saying the
'Employees' table does not exist. Please help.Hi

You don't say if this is the source or destination table, but I would assume
that this is may be caused by a permissions problem.

John

"Birdman" <brianwsnyder1@.comcast.net> wrote in message
news:Q8Gdnd5vlNpaN6jZnZ2dnUVZ_vqdnZ2d@.comcast.com. ..
> When I am trying to export a table from SQL Server Enterprise (using
> Enterprise Manager), I am gettin errors at the end. It is saying the
> 'Employees' table does not exist. Please help.

Exporting DataSets from multiple datasources

Is their away to have reporting services call a dataset from an access
database and export it into a sql server table through automation in a
project with 2 datasources. One datasource from access and the other from
sql server. I can do this with a DTS, but could I do it another way to work
around a DTS.
RandyUrghh, i don´t think that the way reporting services should be used. If you
really really really wanna do this, set up an linked server on sql server to
point to access and write a procedure in which you do your importing stuff.
Return from the procedure some kind of return code to display this in your
report.
But (BTW) is a really nasty way to use RS :(
HTH (either), Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Randy" <Randy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:5218EBE9-A92E-4528-B381-9E835A72A978@.microsoft.com...
> Is their away to have reporting services call a dataset from an access
> database and export it into a sql server table through automation in a
> project with 2 datasources. One datasource from access and the other from
> sql server. I can do this with a DTS, but could I do it another way to
> work
> around a DTS.
> Randy|||Let me add my voice to what Jens is saying. This is definitely NOT a good
idea.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ebfg4dERFHA.904@.tk2msftngp13.phx.gbl...
> Urghh, i don´t think that the way reporting services should be used. If
you
> really really really wanna do this, set up an linked server on sql server
to
> point to access and write a procedure in which you do your importing
stuff.
> Return from the procedure some kind of return code to display this in your
> report.
> But (BTW) is a really nasty way to use RS :(
> HTH (either), Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Randy" <Randy@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:5218EBE9-A92E-4528-B381-9E835A72A978@.microsoft.com...
> > Is their away to have reporting services call a dataset from an access
> > database and export it into a sql server table through automation in a
> > project with 2 datasources. One datasource from access and the other
from
> > sql server. I can do this with a DTS, but could I do it another way to
> > work
> > around a DTS.
> >
> > Randy
>

Exporting Data+Structure

Hello E'body,
I have to export a SQL Server Table (Structure+Data) to an access mdb file at run time. How can i do it? please help.
Lax.You can use DTS wizard in SQL Server to accomplish the task.
For information review topics available in SQL books online.

ONce you've designed the package, can schedule it to run as a job at run time.

EXporting data to xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,just adding for xml clause gives output in xml format
select * from <table> for xml. However this has lot of options too.
BOL has this example
CREATE VIEW p AS
SELECT od.OrderID,
pr.ProductName,
od.Quantity,
od.UnitPrice,
od.Quantity * od.UnitPrice AS total
FROM Products AS pr
JOIN
[Order Details] AS od
ON
pr.ProductID = od.ProductID
And then write the SELECT statement:
SELECT c.CompanyName,
o.OrderID,
o.OrderDate,
p.ProductName,
p.Quantity,
p.UnitPrice,
p.total
FROM Customers AS c
JOIN
Orders AS o
ON
c.CustomerID = o.CustomerID
JOIN
p
ON
o.OrderID = p.OrderID
FOR XML AUTO
--
Regards
R.D
--Knowledge gets doubled when shared
"Enric" wrote:

> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it customar
y,
> a xml.
> Any advice or though woud be greatly.
> Regards,|||thanks a lot,
"R.D" wrote:
> just adding for xml clause gives output in xml format
> select * from <table> for xml. However this has lot of options too.
> BOL has this example
> CREATE VIEW p AS
> SELECT od.OrderID,
> pr.ProductName,
> od.Quantity,
> od.UnitPrice,
> od.Quantity * od.UnitPrice AS total
> FROM Products AS pr
> JOIN
> [Order Details] AS od
> ON
> pr.ProductID = od.ProductID
> And then write the SELECT statement:
> SELECT c.CompanyName,
> o.OrderID,
> o.OrderDate,
> p.ProductName,
> p.Quantity,
> p.UnitPrice,
> p.total
> FROM Customers AS c
> JOIN
> Orders AS o
> ON
> c.CustomerID = o.CustomerID
> JOIN
> p
> ON
> o.OrderID = p.OrderID
> FOR XML AUTO
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Enric" wrote:
>|||Enric,
Also undestand that the XML that is produced is an XML fragment not an valid
XML document. You'll need to add a XML tag and a root element.
HTH
Jerry
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:5FC7B338-98E4-4C5A-A62E-A81E661DD1AF@.microsoft.com...
> just adding for xml clause gives output in xml format
> select * from <table> for xml. However this has lot of options too.
> BOL has this example
> CREATE VIEW p AS
> SELECT od.OrderID,
> pr.ProductName,
> od.Quantity,
> od.UnitPrice,
> od.Quantity * od.UnitPrice AS total
> FROM Products AS pr
> JOIN
> [Order Details] AS od
> ON
> pr.ProductID = od.ProductID
> And then write the SELECT statement:
> SELECT c.CompanyName,
> o.OrderID,
> o.OrderDate,
> p.ProductName,
> p.Quantity,
> p.UnitPrice,
> p.total
> FROM Customers AS c
> JOIN
> Orders AS o
> ON
> c.CustomerID = o.CustomerID
> JOIN
> p
> ON
> o.OrderID = p.OrderID
> FOR XML AUTO
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Enric" wrote:
>

Exporting data from SQL table to Excel File - How to delete rows before inserting new

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Quote:

Originally Posted by LimaCharlie

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Create a view that limits number of exported rows and use it in DTS package instead of a table.

Good Luck.|||hi iburyak,

just the same, the records are being appended to the excel file. what i want is to delete the rows in the destination file before inserting new records.

thanks.|||Did you create a view that removes unwanted records?
Execute a view on a Server side and make sure this is the data you want to export, don't create a view to select * from table_name it will not correct a problem.

You need to give me more information then just I want to delete records.
It is a wrong approach to load all records and delete on destination side you need to load only necessary records.|||Hi Iburyak,

The Question is... this excel file is getting populated everyday and everyday before porting data to the excel the old data has to be erased and new data filled up.

Any help on that.

Thanks in advance..

Exporting data from SQL table to Excel File - How to delete rows before insertin

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Thanks a lot.Is it possibe to create a link to the Excel and run a delete statement from the SQL Server side?

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=135&rl=1

ddave|||LimaCharlie, take a look at this post:

http://www.dbforums.com/showthread.php?t=981661

Cheers

kbk|||hi kbk,

that was my first design: i drop then recreate the table and it was working properly. but the problem is, the users are not permitted to drop & create tables in the database (only me & the DB admin are allowed to do this). so what i did was create a temporary table, from this temp table i export the data to excel. is there any other way so i can delete the rows before inserting new?

thanks a lot.|||What i want is to delete the rows in the destination file before inserting new records.

I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.

1. Create an activeX script as below: -This will remove the worksheet.

Function Main()

Dim srccsvfile
Dim objExcel
Dim objWorkbook, objWorksheet

srccsvfile = "C:\Test.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.displayalerts = False

Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet = objWorkbook.WorkSheets("New_Table")
objWorksheet.Activate
objWorksheet.Delete 'this is removing the worksheet instead of rows
objWorkbook.Save 'you must save the change otherwise in trouble

objExcel.Workbooks.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function

2. Create Execute SQL task as below: - create the worksheet again
CREATE TABLE `New_Table` (
`OfficePK` VarChar (8) ,
`DealerGroupFK` VarChar (4) ,
`StateManagerGroupFK` VarChar (20) ,
`OfficeType` VarChar (1) ,
`OfficeName` VarChar (255) ,
`OfficeAddress1` VarChar (255) ,
`OfficeAddress2` VarChar (255) ,
`OfficeSuburb` VarChar (255) ,
`OfficeState` VarChar (255) ,
`OfficePostCode` VarChar (255) ,
`OfficeCountry` VarChar (255) ,
`OfficePhone` VarChar (255) ,
`RIOfficeID` VarChar (50) ,
`RIFranchiseName` VarChar (255) ,
`VPServerIP` VarChar (50) ,
`LastReceived` DateTime
)
3. Then use data transform as you are doing now.
Make sure that the destination table name is New_Table and the connection is Excel.|||Hi TerryP,
I'll try this.
Thank you very much. =)sql

Exporting data from Excel Tables to SQL Server Tables

Hi all,

I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.

How can it be done?

Thanks, Michael.

If you are using SQL 2000 Standard or better, use DTS.

If you are using SQL2005/8 Standard or better, use SSIS.

If you are using SQL 2005/8 Express, get the Graphical User Interface for it and try that out.

p.s. What version of SQL Server are using?

Exporting Data from Database to a csv file

Hi,

How do I export data from my database table into a Comma separated value file format.

I am using SQL Server 2005 with vb.net

Thanks

you can try bcp.exe.|||It would use the SQL Server Import / Export wizard , it is more comfortable than the BCP command, although in some cases like automatic commandline export thats the only choice. Right click the database and choose the Export... command. You will be guided though a wizard for exporting.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI baroo,

Now I have the same task but I can't find out how to do it...I also have to transfer csv to SQL,although I did it but I don't know how to export data from SQL to a csv file...

Did you solve it?If so coúld you also explain me how to do it?

Thanks,

Can

|||Try bcp.exe or import/export wizard, as mentioned above.|||

Hi Greg,

Thank you for the reply,I am writing a program in VB Express (SQL server express 2005) and I have to do it through coding...I tried data reader writer.writeline but it didn't work out...

Do you have an idea how can I do it using ADO or BCP but through coding....

Thanks&regards,

Can

|||

Reference SQL 2005 Books Online topic "Overview of Bulk Import and Bulk Export" for more information.

Exporting Data from Database to a csv file

Hi,

How do I export data from my database table into a Comma separated value file format.

I am using SQL Server 2005 with vb.net

Thanks

you can try bcp.exe.|||It would use the SQL Server Import / Export wizard , it is more comfortable than the BCP command, although in some cases like automatic commandline export thats the only choice. Right click the database and choose the Export... command. You will be guided though a wizard for exporting.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI baroo,

Now I have the same task but I can't find out how to do it...I also have to transfer csv to SQL,although I did it but I don't know how to export data from SQL to a csv file...

Did you solve it?If so coúld you also explain me how to do it?

Thanks,

Can

|||Try bcp.exe or import/export wizard, as mentioned above.|||

Hi Greg,

Thank you for the reply,I am writing a program in VB Express (SQL server express 2005) and I have to do it through coding...I tried data reader writer.writeline but it didn't work out...

Do you have an idea how can I do it using ADO or BCP but through coding....

Thanks&regards,

Can

|||

Reference SQL 2005 Books Online topic "Overview of Bulk Import and Bulk Export" for more information.

Exporting data as XML file

To get the data from the database table as an XML file we will use " for xml" clause in sql statement in SQLSERVER.

But i am not able to generate the following things in that XML file.

please let me know how to get the following details in XML file using sql statement?

<?xml version="1.0" encoding="UTF-8" ?>

- <listOfIDXRefData xmlns=some url here>

Try the T-SQL forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

-Jamie

Exporting Data

Hi All
Win 2k pro
SQL 2k Dev ed
Using QA or EM how do I export data in a table to a text file.
I've used the "export" option and it always displays the data as comma
seperated values which is not what I was wanting.
How do I export them as SQL "INSERT" commands?
eg
INSERT Country (id, country) VALUES(1,"USA")
INSERT Country (id, country) VALUES(2,"UK")
INSERT Country (id, country) VALUES(3,"Canada")
INSERT Country (id, country) VALUES(4,"Etc...")
thanks for any help & tips.
Al.http://vyaskn.tripod.com/code.htm#inserts
--
David Portas
--
Please reply only to the newsgroup
--
"Harag" <harag@.softhome.net> wrote in message
news:meitmvc0ujnp3unukt1pcnkrn4o7g6died@.4ax.com...
> Hi All
> Win 2k pro
> SQL 2k Dev ed
> Using QA or EM how do I export data in a table to a text file.
> I've used the "export" option and it always displays the data as comma
> seperated values which is not what I was wanting.
> How do I export them as SQL "INSERT" commands?
> eg
> INSERT Country (id, country) VALUES(1,"USA")
> INSERT Country (id, country) VALUES(2,"UK")
> INSERT Country (id, country) VALUES(3,"Canada")
> INSERT Country (id, country) VALUES(4,"Etc...")
> thanks for any help & tips.
> Al.
>|||Use the script provided by SQL Server MVP Narayana Vyas Kondreddi:
http://vyaskn.tripod.com/code.htm#inserts
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Harag" <harag@.softhome.net> wrote in message
news:meitmvc0ujnp3unukt1pcnkrn4o7g6died@.4ax.com...
> Hi All
> Win 2k pro
> SQL 2k Dev ed
> Using QA or EM how do I export data in a table to a text file.
> I've used the "export" option and it always displays the data as comma
> seperated values which is not what I was wanting.
> How do I export them as SQL "INSERT" commands?
> eg
> INSERT Country (id, country) VALUES(1,"USA")
> INSERT Country (id, country) VALUES(2,"UK")
> INSERT Country (id, country) VALUES(3,"Canada")
> INSERT Country (id, country) VALUES(4,"Etc...")
> thanks for any help & tips.
> Al.
>

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the mysql table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If mysql can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:

> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the mysql table's strucutre and it was also set to varbinary(8)
> Please help.
>sql

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the MySQL table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If MYSQL can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:
> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the MySQL table's strucutre and it was also set to varbinary(8)
> Please help.
>

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

sql

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

Exporting a table to Excel using bcl utility (Newbie)

I am trying to learn SQL 2005 Express and I am having a problem exporting (if that is the correct word) a table to Excel.

I have created a view in the Northwind database called MyCustomerView and want to practice working with the table.

From the command prompt:

bcp Northwind.dbo.MyCustomerView out MCV.xls -S -T

I then get the error message:

c:\> bcp Northwind.dbo.MyCustomerView out MCV.xls -S -T

SQLState = 08001, NativeError = 10061

Error = [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.

SQLState = HYT00, NativeError = 0

Error = [Microsoft][SQL Native Client]Login timeout expired

SQLState = 08001, NativeError = 10061

Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

This is on a single machine running Widows XP Home.

Help Please

Did you enable remote connections on the SQL Server express machine ? See the Screencast on my site for more information and a walkthrough. Do you have SQL Browser started ? SQL Server will pick a port to bind it to the service. If SQL Browser is not started you will have to specify it within the server declaration. If started it will directly redirect you to the appropiate instance.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

Everything you suggested I have tried, but am still getting the errors.