How to add strings to NTEXT data type

Hi, All,
First of all thanks for reading my post. I’m trying to figure out how to
add strings to a NTEXT data type column.
TABLE (
NoteID VARCHAR(10) PRIMARY KEY,
NoteOppID VARCHAR(10) FOREIGN KEY,
NoteDetails VARCHAR(1000)
)
I need to take all NoteDetails in the above table and group by NoteOppID and
insert them into a single NTEXT field…
i.e)
NoteOppID NoteDetails (VARCHAR)
----
Opp123 Matt please send this customer a prototype
Opp123 Customer wants to see the G Series
Opp123 John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
components.
Opp456 John - Please give me your thoughts on this
-- End result should look like
OppId Details (NTEXT)
----
Opp123 Matt please send this customer a prototype
[New Line]
Customer wants to see the MIR 900 Series
[New Line]
John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
Components
[New Line]
John - Please give me your thoughts on this
I cannot leave it at VARCHAR because I'll go over the 8kb constraint.
I’ve tried…
SELECT [NTEXT] + VARCHAR -- Errors
SELECT SUBSTRING([NTEXT], 0, DATALEN([NTEXT]) + VARCHAR – works but
potentially [NTEXT] will be larger than the 8kb constraint.
Thanks,
-NeilAphex3000 (Aphex3000@.discussions.microsoft.com) writes:
> First of all thanks for reading my post. I'm trying to figure out how to
> add strings to a NTEXT data type column.
You will most likely need to use WRITETEXT and UPDATETEXT. Please look
these up in Books Online.
(Or you upgrade to SQL 2005 where you can use nvarchar(MAX) instead
which is a lot easier to deal with.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to add RESTORE permission to Sql server 2000 & 2005 users

How to add RESTORE permission to Sql server 2000 & 2005 users. I need to
add only Restore permission to the existing user.
*** Sent via Developersdex http://www.codecomments.com ***
Mohammed,
Both SQL Server 2000 and 2005 have the same basic comments on RESTORE
permissions. Look down toward the bottom of the Books Online article on
RESTORE. It says:
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database ... members of the db_owner
fixed database role do not have RESTORE permissions.
If the dbo, not db_owner, seems confusing it is like this. One login owns
the database, it may be 'sa' or 'MyDomain\MyLogin'. That login maps to the
dbo user and should have rights to restore the database. Inside the
database, many users may be in the db_owner role, but since those are inside
the database to be restored, they do not get the RESTORE permission.
So, you can make logins members of the dbcreator fixed server role if you
are satisfied with the other rights that they will also get.
Or (apparently) you can make one user the owner of a database and he should
then be able to do a restore of that database. (I have not tested the owner
of the database approach today.)
RLF
"Mohamed Kaleel" <compguyy@.gmail.com> wrote in message
news:e5j1rZoUIHA.1212@.TK2MSFTNGP05.phx.gbl...
>
> How to add RESTORE permission to Sql server 2000 & 2005 users. I need to
> add only Restore permission to the existing user.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hi Russell,
Thanks for reply, i tried this scenario,i am not able to restore the DB.
If i grant sysadmin privilages to the user then i can able to RESTORE
the DB.
Please suggest me.
Thanks,
Kaleel
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi Russell,
Thanks for reply, i tried this scenario,i am not able to restore the DB.
If i grant sysadmin privilages to the user then i can able to RESTORE
the DB.
Please suggest me.
Thanks,
Kaleel
*** Sent via Developersdex http://www.codecomments.com ***
|||Mohammed,
Of course, sysadmin will work, but I created a login RLFTest that is not a
sysadmin for these tests.
Test 1: Granted RLFTest the "dbcreator" server role and the "public" role in
MyDatabase.
RESTORE DATABASE MyDatabase ... was successful.
Test 2: Revoked RLFTest from the "dbcreator" server role and the "public"
role in MyDatabase. Made RLFTest the owner of the database.
USE MyDatabase
exec sp_changedbowner 'RLFTest'
RESTORE DATABASE MyDatabase ... was successful.
So, for me both of the approved non-sysadmin routes worked just fine. You
might retest using the details of what I did. If you still are having
problems, please let me know the details of error messages, etc.
RLF
"Mohamed Kaleel" <Mohamed.Kaleel@.kaleel.com> wrote in message
news:%231HKhE1UIHA.5164@.TK2MSFTNGP03.phx.gbl...
> Hi Russell,
> Thanks for reply, i tried this scenario,i am not able to restore the DB.
> If i grant sysadmin privilages to the user then i can able to RESTORE
> the DB.
> Please suggest me.
> Thanks,
> Kaleel
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||For the first Scenario, i get the following error
===================================
Restore failed for Server 'MyMachine\HTMS_PROD'.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
0.3042.00&EvtSrc=Microsoft.SqlServer.Management.Sm o.ExceptionTemplates.F
ailedOperationExceptionText&EvtID=Restore+Server&L inkId=20476
Program Location:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRes toreDatabaseOptions.Ru
nRestore()
===================================
System.Data.SqlClient.SqlError: Server user 'TestHtms' is not a valid
user in database 'HTMS_Temp1'. (Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
0.3042.00&LinkId=20476
Program Location:
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQueryWithM
essage(StringCollection queries, ServerMessageEventHandler
dbccMessageHandler, Boolean errorsAsMessages)
at
Microsoft.SqlServer.Management.Smo.BackupRestoreBa se.ExecuteSql(Server
server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
*** Sent via Developersdex http://www.codecomments.com ***
|||Mohamed,
Did you grant 'TestHtms' access to the 'HTMS_Temp1' database?
You will notice in my first scenario that it was necessary to grant some
rights to the database, though in my case simply making 'RLFTest' a member
of the public role was enough.
A note: If the restored database did not already have RLFTest as a user,
RLFTest lost access to the database once the restore was complete.
I notice that you are using SQL Server 2005 Express. That is was I also
used to test.
RLF
"Mohamed Kaleel" <Mohamed.Kaleel@.kaleel.com> wrote in message
news:e50kH3DVIHA.4360@.TK2MSFTNGP06.phx.gbl...
> For the first Scenario, i get the following error
> ===================================
> Restore failed for Server 'MyMachine\HTMS_PROD'.
> (Microsoft.SqlServer.Express.Smo)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
> 0.3042.00&EvtSrc=Microsoft.SqlServer.Management.Sm o.ExceptionTemplates.F
> ailedOperationExceptionText&EvtID=Restore+Server&L inkId=20476
> --
> Program Location:
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
> at
> Microsoft.SqlServer.Management.SqlManagerUI.SqlRes toreDatabaseOptions.Ru
> nRestore()
> ===================================
> System.Data.SqlClient.SqlError: Server user 'TestHtms' is not a valid
> user in database 'HTMS_Temp1'. (Microsoft.SqlServer.Express.Smo)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
> 0.3042.00&LinkId=20476
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQueryWithM
> essage(StringCollection queries, ServerMessageEventHandler
> dbccMessageHandler, Boolean errorsAsMessages)
> at
> Microsoft.SqlServer.Management.Smo.BackupRestoreBa se.ExecuteSql(Server
> server, StringCollection queries)
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
>
>
> *** Sent via Developersdex http://www.codecomments.com ***