BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP & DMO

I can script out a table's data using dmo and the bulkcopy2 object but how
can I get it to script out using ORDER BY?
Thanks
FrankAs a workaround, create a view with a SELECT TOP 100 PERCENT... ORDER BY and
then export from there.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Frank Ashley" <aa@.aa.com> wrote in message
news:exUksDmIFHA.1096@.tk2msftngp13.phx.gbl...
I can script out a table's data using dmo and the bulkcopy2 object but how
can I get it to script out using ORDER BY?
Thanks
Frank|||I could but that would mean creating temporary views for each table that i
want to script out them dropping them at the end. Unless somebody can come
up with the solution that's what i'll do.
Frank
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eTWj63mIFHA.236@.TK2MSFTNGP14.phx.gbl...
> As a workaround, create a view with a SELECT TOP 100 PERCENT... ORDER BY
> and
> then export from there.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Frank Ashley" <aa@.aa.com> wrote in message
> news:exUksDmIFHA.1096@.tk2msftngp13.phx.gbl...
> I can script out a table's data using dmo and the bulkcopy2 object but how
> can I get it to script out using ORDER BY?
>
> Thanks
> Frank
>|||hi Frank,
Frank Ashley wrote:
> I could but that would mean creating temporary views for each table
> that i want to script out them dropping them at the end. Unless
> somebody can come up with the solution that's what i'll do.
AFAIK, the solution proposed by Tom is the only way you can achieve the
desired result..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

BC30311: Value of type System.Data.SqlClient.SqlDataReader cannot be converted to String.

Does anyone know what the problem is?

Sub Page_Init(sender As Object, e As EventArgs)
 Dim txtName As New TextBox()
 Dim txtPart As New TextBox()
 Dim txtEach As New TextBox()
 Dim txtTotal1 As New TextBox()
 Dim txtSubtotal As New TextBox()
 Dim txtTax As New TextBox()
 Dim txtShipping As New TextBox()
 Dim txtTotal2 As New TextBox()
 Dim prod_id As Integer
 Dim id As Integer
 id = Request.Querystring("prod_id")
 txtName.Text = GetName(id).............this is the problem line
 phName.Controls.Add(txtName)
 phPart.Controls.Add(txtPart)
 phEach.Controls.Add(txtEach)
 phTotal1.Controls.Add(txtTotal1)
 phSubtotal.Controls.Add(txtSubtotal)
 phTax.Controls.Add(txtTax)
 phShipping.Controls.Add(txtShipping)
 phTotal2.Controls.Add(txtTotal2)
End Sub

I'm trying to populate dynamically rendered textbox's, how should I do that too?What are you doing within the GetName function call? Seems to me the problem is in there.