|
Home > Archive > MS SQL Server > October 2006 > help textcopy
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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
| |
| Dan Guzman 2006-10-24, 6:28 pm |
| >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. UpdateMyTableImageDa
taFromFile
@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> 写入消息新闻:uojyazm
2GHA.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. UpdateMyTableImageDa
taFromFile
> @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...
>
>
| |
| Dan Guzman 2006-10-24, 6:29 pm |
| 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>
> 写入消息新闻:uojyazm
2GHA.4648@TK2MSFTNGP04.phx.gbl...
>
>
|
|
|
|
|