help textcopy

i like textcopy in sql server 2000, but i don't find it in sql server 2005.
is there has replace_tool in sql server 2005?
thanks
jason
>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
SQL 2005, you can accomplish the same result in Transact-SQL using
OPENROWSET...BULK. The proc example below imports file contents into a temp
table and then updates the specified main table row with the blob data.
CREATE PROC dbo.UpdateMyTableImageDataFromFile
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(MAX))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
> thanks
>
> jason
>
|||thanks!
but if i need export image/text data, how do?
SSIS or command?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> д?:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl ...
> Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
> SQL 2005, you can accomplish the same result in Transact-SQL using
> OPENROWSET...BULK. The proc example below imports file contents into a
> temp table and then updates the specified main table row with the blob
> data.
>
> CREATE PROC dbo.UpdateMyTableImageDataFromFile
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(MAX))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "udb" <datacen@.vip.sina.com> wrote in message
> news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>
|||I would use SSIS. It's quite flexible and allows you to create any sort of
standard or custom file output.
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:Ow%23YLgz2GHA.1548@.TK2MSFTNGP02.phx.gbl...
> thanks!
> but if i need export image/text data, how do?
> SSIS or command?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> д?:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl ...
>