Skip to content

Commit 2125cb3

Browse files
jodebruijodebrui
authored
writetext is supported in Azure SQL DB
1 parent 5bca339 commit 2125cb3

1 file changed

Lines changed: 89 additions & 89 deletions

File tree

‎docs/t-sql/queries/writetext-transact-sql.md‎

Lines changed: 89 additions & 89 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
title: "WRITETEXT (Transact-SQL) | Microsoft Docs"
33
ms.custom: ""
4-
ms.date: "03/16/2017"
4+
ms.date: "10/23/2017"
55
ms.prod: "sql-non-specified"
66
ms.reviewer: ""
77
ms.suite: ""
@@ -29,92 +29,92 @@ ms.author: "rickbyh"
2929
manager: "jhubbard"
3030
---
3131
# WRITETEXT (Transact-SQL)
32-
[!INCLUDE[tsql-appliesto-ss2008-xxxx-xxxx-xxx_md](../../includes/tsql-appliesto-ss2008-xxxx-xxxx-xxx-md.md)]
32+
[!INCLUDE[tsql-appliesto-ss2008-asdb-xxxx-xxx_md](../../includes/tsql-appliesto-ss2008-asdb-xxxx-xxx-md.md)]
3333

34-
Permits minimally logged, interactive updating of an existing **text**, **ntext**, or **image** column. WRITETEXT overwrites any existing data in the column it affects. WRITETEXT cannot be used on **text**, **ntext**, and **image** columns in views.
35-
36-
> [!IMPORTANT]
37-
> [!INCLUDE[ssNoteDepFutureAvoid](../../includes/ssnotedepfutureavoid-md.md)] Use the large-value data types and the **.**WRITE clause of the [UPDATE](../../t-sql/queries/update-transact-sql.md) statement instead.
38-
39-
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
40-
41-
## Syntax
42-
43-
```
44-
45-
WRITETEXT [BULK]
46-
{ table.column text_ptr }
47-
[ WITH LOG ] { data }
48-
```
49-
50-
## Arguments
51-
BULK
52-
Enables upload tools to upload a binary data stream. The stream must be provided by the tool at the TDS protocol level. When the data stream is not present the query processor ignores the BULK option.
53-
54-
> [!IMPORTANT]
55-
> We recommend that the BULK option not be used in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]-based applications. This option might be changed or removed in a future version of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].
56-
57-
*table* **.column**
58-
Is the name of the table and **text**, **ntext**, or **image** column to update. Table and column names must comply with the rules for [identifiers](../../relational-databases/databases/database-identifiers.md). Specifying the database name and owner names is optional.
59-
60-
*text_ptr*
61-
Is a value that stores the pointer to the **text**, **ntext**, or **image** data. *text_ptr* must be **binary(16)**.To create a text pointer, execute an [INSERT](../../t-sql/statements/insert-transact-sql.md) or [UPDATE](../../t-sql/queries/update-transact-sql.md) statement with data that is not null for the **text**, **ntext**, or **image** column.
62-
63-
WITH LOG
64-
Ignored by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Logging is determined by the recovery model in effect for the database.
65-
66-
*data*
67-
Is the actual **text**, **ntext** or **image** data to store. *data* can be a literal or a parameter. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 KB for **text**, **ntext**, and **image** data.
68-
69-
## Remarks
70-
Use WRITETEXT to replace **text**, **ntext**, and **image** data and UPDATETEXT to modify **text**, **ntext**, and **image** data. UPDATETEXT is more flexible because it changes only a part of a **text**, **ntext**, or **image** column instead of the whole column.
71-
72-
For best performance we recommend that **text**, **ntext**, and **image** data be inserted or updated in chunk sizes that are multiples of 8040 bytes.
73-
74-
If the database recovery model is simple or bulk-logged, **text**, **ntext**, and **image** operations that use WRITETEXT are minimally logged operations when new data is inserted or appended.
75-
76-
> [!NOTE]
77-
> Minimal logging is not used when existing values are updated.
78-
79-
For WRITETEXT to work correctly, the column must already contain a valid text pointer.
80-
81-
If the table does not have in row text, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] saves space by not initializing **text** columns when explicit or implicit null values are added in **text** columns with INSERT, and no text pointer can be obtained for such nulls. To initialize **text** columns to NULL, use the UPDATE statement. If the table has in row text, you do not have to initialize the text column for nulls and you can always get a text pointer.
82-
83-
The ODBC SQLPutData function is faster and uses less dynamic memory than WRITETEXT. This function can insert up to 2 gigabytes of **text**, **ntext**, or **image** data.
84-
85-
In [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], in row text pointers to **text**, **ntext**, or **image** data may exist but may not be valid. For information about the text in row option, see [sp_tableoption (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-tableoption-transact-sql.md). For information about invalidating text pointers, see [sp_invalidate_textptr (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-invalidate-textptr-transact-sql.md).
86-
87-
## Permissions
88-
Requires UPDATE permission on the specified table. Permission is transferable when UPDATE permission is transferred.
89-
90-
## Examples
91-
The following example puts the text pointer into the local variable `@ptrval`, and then `WRITETEXT` places the new text string into the row pointed to by `@ptrval`.
92-
93-
> [!NOTE]
94-
> To run this example, you must install the pubs sample database.
95-
96-
```
97-
USE pubs;
98-
GO
99-
ALTER DATABASE pubs SET RECOVERY SIMPLE;
100-
GO
101-
DECLARE @ptrval binary(16);
102-
SELECT @ptrval = TEXTPTR(pr_info)
103-
FROM pub_info pr, publishers p
104-
WHERE p.pub_id = pr.pub_id
105-
AND p.pub_name = 'New Moon Books'
106-
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!';
107-
GO
108-
ALTER DATABASE pubs SET RECOVERY SIMPLE;
109-
GO
110-
```
111-
112-
## See Also
113-
[Data Types (Transact-SQL)](../../t-sql/data-types/data-types-transact-sql.md)
114-
[DECLARE @local_variable (Transact-SQL)](../../t-sql/language-elements/declare-local-variable-transact-sql.md)
115-
[DELETE (Transact-SQL)](../../t-sql/statements/delete-transact-sql.md)
116-
[SELECT (Transact-SQL)](../../t-sql/queries/select-transact-sql.md)
117-
[SET Statements (Transact-SQL)](../../t-sql/statements/set-statements-transact-sql.md)
118-
[UPDATETEXT (Transact-SQL)](../../t-sql/queries/updatetext-transact-sql.md)
119-
120-
34+
Permits minimally logged, interactive updating of an existing **text**, **ntext**, or **image** column. WRITETEXT overwrites any existing data in the column it affects. WRITETEXT cannot be used on **text**, **ntext**, and **image** columns in views.
35+
36+
> [!IMPORTANT]
37+
> [!INCLUDE[ssNoteDepFutureAvoid](../../includes/ssnotedepfutureavoid-md.md)] Use the large-value data types and the **.**WRITE clause of the [UPDATE](../../t-sql/queries/update-transact-sql.md) statement instead.
38+
39+
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
40+
41+
## Syntax
42+
43+
```
44+
45+
WRITETEXT [BULK]
46+
{ table.column text_ptr }
47+
[ WITH LOG ] { data }
48+
```
49+
50+
## Arguments
51+
BULK
52+
Enables upload tools to upload a binary data stream. The stream must be provided by the tool at the TDS protocol level. When the data stream is not present the query processor ignores the BULK option.
53+
54+
> [!IMPORTANT]
55+
> We recommend that the BULK option not be used in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]-based applications. This option might be changed or removed in a future version of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].
56+
57+
*table* **.column**
58+
Is the name of the table and **text**, **ntext**, or **image** column to update. Table and column names must comply with the rules for [identifiers](../../relational-databases/databases/database-identifiers.md). Specifying the database name and owner names is optional.
59+
60+
*text_ptr*
61+
Is a value that stores the pointer to the **text**, **ntext**, or **image** data. *text_ptr* must be **binary(16)**.To create a text pointer, execute an [INSERT](../../t-sql/statements/insert-transact-sql.md) or [UPDATE](../../t-sql/queries/update-transact-sql.md) statement with data that is not null for the **text**, **ntext**, or **image** column.
62+
63+
WITH LOG
64+
Ignored by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. Logging is determined by the recovery model in effect for the database.
65+
66+
*data*
67+
Is the actual **text**, **ntext** or **image** data to store. *data* can be a literal or a parameter. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 KB for **text**, **ntext**, and **image** data.
68+
69+
## Remarks
70+
Use WRITETEXT to replace **text**, **ntext**, and **image** data and UPDATETEXT to modify **text**, **ntext**, and **image** data. UPDATETEXT is more flexible because it changes only a part of a **text**, **ntext**, or **image** column instead of the whole column.
71+
72+
For best performance we recommend that **text**, **ntext**, and **image** data be inserted or updated in chunk sizes that are multiples of 8040 bytes.
73+
74+
If the database recovery model is simple or bulk-logged, **text**, **ntext**, and **image** operations that use WRITETEXT are minimally logged operations when new data is inserted or appended.
75+
76+
> [!NOTE]
77+
> Minimal logging is not used when existing values are updated.
78+
79+
For WRITETEXT to work correctly, the column must already contain a valid text pointer.
80+
81+
If the table does not have in row text, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] saves space by not initializing **text** columns when explicit or implicit null values are added in **text** columns with INSERT, and no text pointer can be obtained for such nulls. To initialize **text** columns to NULL, use the UPDATE statement. If the table has in row text, you do not have to initialize the text column for nulls and you can always get a text pointer.
82+
83+
The ODBC SQLPutData function is faster and uses less dynamic memory than WRITETEXT. This function can insert up to 2 gigabytes of **text**, **ntext**, or **image** data.
84+
85+
In [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], in row text pointers to **text**, **ntext**, or **image** data may exist but may not be valid. For information about the text in row option, see [sp_tableoption (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-tableoption-transact-sql.md). For information about invalidating text pointers, see [sp_invalidate_textptr (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-invalidate-textptr-transact-sql.md).
86+
87+
## Permissions
88+
Requires UPDATE permission on the specified table. Permission is transferable when UPDATE permission is transferred.
89+
90+
## Examples
91+
The following example puts the text pointer into the local variable `@ptrval`, and then `WRITETEXT` places the new text string into the row pointed to by `@ptrval`.
92+
93+
> [!NOTE]
94+
> To run this example, you must install the pubs sample database.
95+
96+
```
97+
USE pubs;
98+
GO
99+
ALTER DATABASE pubs SET RECOVERY SIMPLE;
100+
GO
101+
DECLARE @ptrval binary(16);
102+
SELECT @ptrval = TEXTPTR(pr_info)
103+
FROM pub_info pr, publishers p
104+
WHERE p.pub_id = pr.pub_id
105+
AND p.pub_name = 'New Moon Books'
106+
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!';
107+
GO
108+
ALTER DATABASE pubs SET RECOVERY SIMPLE;
109+
GO
110+
```
111+
112+
## See Also
113+
[Data Types (Transact-SQL)](../../t-sql/data-types/data-types-transact-sql.md)
114+
[DECLARE @local_variable (Transact-SQL)](../../t-sql/language-elements/declare-local-variable-transact-sql.md)
115+
[DELETE (Transact-SQL)](../../t-sql/statements/delete-transact-sql.md)
116+
[SELECT (Transact-SQL)](../../t-sql/queries/select-transact-sql.md)
117+
[SET Statements (Transact-SQL)](../../t-sql/statements/set-statements-transact-sql.md)
118+
[UPDATETEXT (Transact-SQL)](../../t-sql/queries/updatetext-transact-sql.md)
119+
120+

0 commit comments

Comments
 (0)