Hi,

I use the exportdata method under the DMO:

Dim oServer As SQLDMO.SQLServerClass
Dim oDatabase As SQLDMO._Database
Dim oBCP As SQLDMO.BulkCopyClass
oServer = New SQLDMO.SQLServerClass()
oBCP = New SQLDMO.BulkCopyClass()
oServer.EnableBcp = True
oServer.LoginSecure = True
oServer.Connect(strServername)
oDatabase = oServer.Databases.Item(strDBName)
oDatabase.DBOption.SelectIntoBulkCopy = True
oBCP.DataFilePath = strPath
oBCP.SetCodePage(SQLDMO.SQLDMO_BCP_CODEPAGE_TYPE.SQLDMOBCP_ACP)
oBCP.DataFileType =
SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_SpecialDelimitedChar
oBCP.ColumnDelimiter = vbTab
oBCP.RowDelimiter = vbCrLf
oBCP.ExportWideChar = False
oBCP.TableLock = True
oDatabase.Tables.Item(strTablename).ExportData(oBCP)

I have following problem:
Data in Columns that are of Image type, that are bigger than 64kB are
truncated to 64kB.

Is there a workaround for this "bug", or does I make a mistake.

I used the SQL 2000 Server (Standard-Edition) with SP3

Thanks,

Christian

This is a known problem, caused by the fact that by default DMO starts a new
connection for the BCP.
If you would run profiler you will see that

connection 1 calls set textsize 2147483647
but connection 2 on which the select for the BCP is execute calls SET
TEXTSIZE 64512

Since you can not control how the second connection is created the only work
around is to use
BulkCopy.UseExistingConnection = True like this

Option Explicit

Public Sub main()
On Error GoTo ErrHandler

Dim oSQLServer As SQLDMO.SQLServer2
Set oSQLServer = New SQLDMO.SQLServer2
oSQLServer.LoginSecure = True
oSQLServer.EnableBcp = True


oSQLServer.Connect "(local)\dev"

Dim oDatabase As SQLDMO.Database2
Set oDatabase = oSQLServer.Databases("tempdb")

oDatabase.DBOption.SelectIntoBulkCopy = True

Dim oBulkCopy As SQLDMO.BulkCopy2
Set oBulkCopy = New SQLDMO.BulkCopy2

oBulkCopy.UseExistingConnection = True
oBulkCopy.DataFilePath = "c:\test.bcp"
oBulkCopy.SetCodePage SQLDMOBCP_ACP

oBulkCopy.ColumnDelimiter = vbTab
oBulkCopy.RowDelimiter = vbCrLf
oBulkCopy.ExportWideChar = False
oBulkCopy.TableLock = True

Dim rows As Integer
rows = oDatabase.Tables("t1").ExportData(oBulkCopy)

oSQLServer.DisConnect
Set oSQLServer = Nothing
Exit Sub

ErrHandler:
Debug.Print "Error " & Err.Number & " " & Err.Description
Resume Next
End Sub

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.

[quoted text, click to view]