How to copy user in master database to new server?

I just install new server for testing only. I have problem about user in
master database. How to copy all user from production server to testing
server?Are you referring to users in the master database or logins? Also, what version of SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:A01991A7-16F8-4E16-BC3F-EA21842E8011@.microsoft.com...
>I just install new server for testing only. I have problem about user in
> master database. How to copy all user from production server to testing
> server?|||Hi
I assume you are meaning logins rather than users which can be scripted. To
transfer logins check out http://support.microsoft.com/kb/246133/
John
"Mesak" wrote:
> I just install new server for testing only. I have problem about user in
> master database. How to copy all user from production server to testing
> server?|||If I have backup site (another building). I test system at backup site one
time per year. Do you have any solution to copy login to new server at backup
site?
Thank you.
"John Bell" wrote:
> Hi
> I assume you are meaning logins rather than users which can be scripted. To
> transfer logins check out http://support.microsoft.com/kb/246133/
> John
> "Mesak" wrote:
> > I just install new server for testing only. I have problem about user in
> > master database. How to copy all user from production server to testing
> > server?|||Hi,
Use the below URL:
http://www.databasejournal.com/features/mssql/article.php/2228611
Thanks
Hari
SQL Server MVP
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:B4198F70-AAEC-4B94-921E-602D2ACF2D9C@.microsoft.com...
> If I have backup site (another building). I test system at backup site one
> time per year. Do you have any solution to copy login to new server at
> backup
> site?
> Thank you.
> "John Bell" wrote:
>> Hi
>> I assume you are meaning logins rather than users which can be scripted.
>> To
>> transfer logins check out http://support.microsoft.com/kb/246133/
>> John
>> "Mesak" wrote:
>> > I just install new server for testing only. I have problem about user
>> > in
>> > master database. How to copy all user from production server to testing
>> > server?|||The solution to copy login is in the URL that John posted:
http://support.microsoft.com/kb/246133
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:B4198F70-AAEC-4B94-921E-602D2ACF2D9C@.microsoft.com...
> If I have backup site (another building). I test system at backup site one
> time per year. Do you have any solution to copy login to new server at backup
> site?
> Thank you.
> "John Bell" wrote:
>> Hi
>> I assume you are meaning logins rather than users which can be scripted. To
>> transfer logins check out http://support.microsoft.com/kb/246133/
>> John
>> "Mesak" wrote:
>> > I just install new server for testing only. I have problem about user in
>> > master database. How to copy all user from production server to testing
>> > server?

How to copy user in master database to new server?

I just install new server for testing only. I have problem about user in
master database. How to copy all user from production server to testing
server?
Are you referring to users in the master database or logins? Also, what version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:A01991A7-16F8-4E16-BC3F-EA21842E8011@.microsoft.com...
>I just install new server for testing only. I have problem about user in
> master database. How to copy all user from production server to testing
> server?
|||Hi
I assume you are meaning logins rather than users which can be scripted. To
transfer logins check out http://support.microsoft.com/kb/246133/
John
"Mesak" wrote:

> I just install new server for testing only. I have problem about user in
> master database. How to copy all user from production server to testing
> server?
|||If I have backup site (another building). I test system at backup site one
time per year. Do you have any solution to copy login to new server at backup
site?
Thank you.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I assume you are meaning logins rather than users which can be scripted. To
> transfer logins check out http://support.microsoft.com/kb/246133/
> John
> "Mesak" wrote:
|||Hi,
Use the below URL:
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
SQL Server MVP
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:B4198F70-AAEC-4B94-921E-602D2ACF2D9C@.microsoft.com...[vbcol=seagreen]
> If I have backup site (another building). I test system at backup site one
> time per year. Do you have any solution to copy login to new server at
> backup
> site?
> Thank you.
> "John Bell" wrote:
|||The solution to copy login is in the URL that John posted:
http://support.microsoft.com/kb/246133
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:B4198F70-AAEC-4B94-921E-602D2ACF2D9C@.microsoft.com...[vbcol=seagreen]
> If I have backup site (another building). I test system at backup site one
> time per year. Do you have any solution to copy login to new server at backup
> site?
> Thank you.
> "John Bell" wrote:

How to copy user in master database to new server?

I just install new server for testing only. I have problem about user in
master database. How to copy all user from production server to testing
server?Are you referring to users in the master database or logins? Also, what vers
ion of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:A01991A7-16F8-4E16-BC3F-EA21842E8011@.microsoft.com...
>I just install new server for testing only. I have problem about user in
> master database. How to copy all user from production server to testing
> server?|||Hi
I assume you are meaning logins rather than users which can be scripted. To
transfer logins check out http://support.microsoft.com/kb/246133/
John
"Mesak" wrote:

> I just install new server for testing only. I have problem about user in
> master database. How to copy all user from production server to testing
> server?|||If I have backup site (another building). I test system at backup site one
time per year. Do you have any solution to copy login to new server at backu
p
site?
Thank you.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I assume you are meaning logins rather than users which can be scripted. T
o
> transfer logins check out http://support.microsoft.com/kb/246133/
> John
> "Mesak" wrote:
>|||Hi,
Use the below URL:
http://www.databasejournal.com/feat...cle.php/2228611
Thanks
Hari
SQL Server MVP
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:B4198F70-AAEC-4B94-921E-602D2ACF2D9C@.microsoft.com...[vbcol=seagreen]
> If I have backup site (another building). I test system at backup site one
> time per year. Do you have any solution to copy login to new server at
> backup
> site?
> Thank you.
> "John Bell" wrote:
>|||The solution to copy login is in the URL that John posted:
http://support.microsoft.com/kb/246133
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mesak" <Mesak@.discussions.microsoft.com> wrote in message
news:B4198F70-AAEC-4B94-921E-602D2ACF2D9C@.microsoft.com...[vbcol=seagreen]
> If I have backup site (another building). I test system at backup site one
> time per year. Do you have any solution to copy login to new server at bac
kup
> site?
> Thank you.
> "John Bell" wrote:
>

How to copy the value of one field into another using SQL Server 2000

I am a little embarrased to be asking this question because I know that the answer must be simple but I am still a nube at SQL. I am stuck with a simple query that copies one field of data into another. Below is the script that I am trying to execute.

UPDATE table
SET field2 = field1

I get an error from SQL Server that says that the String or binary data would be truncated. I am assuming that the SQL server is trying to concatenate all the value of field1 into field2. All I am trying to do is copy the values of one field into another for each and every row in the table. Thanks in advance.I am assuming that the SQL server is trying to concatenate all the value of field1 into field2.

No, the UPDATE command goes through each record that matches your WHERE criteria (which is blank here, so it goes through every record) and performs the SET portion on individual records. In your example, after the UPDATE is performed, field2 should be equal to field1 for the entire table.

I get an error from SQL Server that says that the String or binary data would be truncated.

That probably means that the two fields are different types. BTW, VARCHAR(25) and VARCHAR(15) are "different types" to SQL. Obviously a 25 character string isn't going to fit in a field that is only 15 characters long.|||...so you will need to either expand both columns to the same size, or use the CAST(), CONVERT(), or LEFT() functions to trim the long string to the shorter length. SQL Server will not do it for you because it entails a loss of data.|||Thanks guys!. The field size was the problem.

How to Copy the User

Hi
There are two different server A and B both are isolatted.
Is there is any way to copy all the user under security login of server A
from Server B
Regards
NizhamSee step 2 in this docuemnt.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Nizham" wrote:

> Hi
> There are two different server A and B both are isolatted.
> Is there is any way to copy all the user under security login of server A
> from Server B
> Regards
> Nizham
>
>

How to copy the image field

Hi,
I have to write a procedure which copies some data within and between
tables, including image fields of unknown size. Those image fields contain
documents in various formats and can vary from couple of kilobytes to couple
of megabytes. How can I do it? My first instinct was to look at READTEXT,
WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
recommend them any more and is going to drop them altogether. Can anyone
direct me to some materials that would show how to do it?
Thanks,
MiroslawMPA (miroslaw_pa@.pf.pl) writes:
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields
> contain documents in various formats and can vary from couple of
> kilobytes to couple of megabytes. How can I do it? My first instinct was
> to look at READTEXT, WRITETEXT and UPDATETEXT functions, but it seems
> Microsoft does not recommend them any more and is going to drop them
> altogether. Can anyone direct me to some materials that would show how
> to do it?
You are correct that these operations are depreacated, but so is the
image data type.
The preferred data type for image data in SQL 2005 is varbinary(MAX). If
your data type is image, and you can change that, then you probably
have to use WRITETEXT and UPDATETEXT.
varbinary(MAX) works very much like the regular varbinary(n) data type,
but you can work with chunks with the .write mutator, and this way
update only a part of the column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||MPA wrote:
> Hi,
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields contai
n
> documents in various formats and can vary from couple of kilobytes to coup
le
> of megabytes. How can I do it? My first instinct was to look at READTEXT,
> WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
> recommend them any more and is going to drop them altogether. Can anyone
> direct me to some materials that would show how to do it?
> Thanks,
> Miroslaw
You can use the regular UPDATE statement to copy data between IMAGE
columns. However, in SQL Server 2005 the IMAGE type exists for
backwards compatibility only. Microsoft recommends that you use
VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
also deprecated.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The customers have different servers. The first one has SQL Server 2000, so
it looks like I have to use READTEXT and UPDATETEXT. I just wanted first to
make a test to copy the small piece of data from one row of the table to
another, but it does not seem to work. It runs like this:
DECLARE @.ptrval varbinary(16)
DECLARE @.ptrval2 varbinary(16)
SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
and doc_zkz_id=35)
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
doc_zkz_id=35
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
I was trying here to read 16 first bytes of DOC_BLOB column from the first
row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
After the operation the written 16 bytes are completely different than the
data in the first row. On the other hand I was able to update a second row
with the statement
like:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 'abcdef"
What I am missing?
Thanks,
Miroslaw
Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> napisal w
wiadomosci news:1147621152.612045.54660@.j55g2000cwa.googlegroups.com...
> MPA wrote:
contain
couple
READTEXT,
> You can use the regular UPDATE statement to copy data between IMAGE
> columns. However, in SQL Server 2005 the IMAGE type exists for
> backwards compatibility only. Microsoft recommends that you use
> VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
> also deprecated.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||MPA (miroslaw_pa@.pf.pl) writes:
> The customers have different servers. The first one has SQL Server 2000,
> so it looks like I have to use READTEXT and UPDATETEXT. I just wanted
> first to make a test to copy the small piece of data from one row of the
> table to another, but it does not seem to work. It runs like this:
> DECLARE @.ptrval varbinary(16)
> DECLARE @.ptrval2 varbinary(16)
> SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
> and doc_zkz_id=35)
> READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
> SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
> doc_zkz_id=35
> WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
> I was trying here to read 16 first bytes of DOC_BLOB column from the first
> row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
> After the operation the written 16 bytes are completely different than the
> data in the first row. On the other hand I was able to update a second row
> with the statement
The statement
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
does not assign @.ptrval. Rather @.ptrval is a pointer to the blob data
in the column. The 16 bytes gets returned to the client. So when you
say:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
you are just writing that pointer to the blob.
You can copy blob data with UPDATETEXT. But please don't ask me for an
example. I very rarely work with any of the text operations, and when I
do, I need to study Books Online carefully.
Now, you mentioned that the source and target tables were on different
servers. This is going to make it even more painful.
I would try a straight UPDATE across the linked server. If this breaks
down, I would try to use BCP to bring the source table over the target
server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to copy the DTS Package from one server to another?

Hello,
I have created some DTS packages in my sql server now i want to copy them to some other SQLServer. Is it possible to do that? If so pls help me to do that.
Advance thanks...
MuraliWhen saving, choose "Save As" and then change "Location" to "Structured Storage File" instead of "SQL Server".

That will give you a file.dts that you can transfer and load onto another SQL Server.

(To open it on a target server, place yourself on "Data Transformation Services" in EM, and right-click the mouse on right blank field. You'll see "Open Package" in pop-up menu|||Hi KUKUK,
Thanks for your reply. but I know this method. Is there any other way to do this?. Instead of creating seperate dts files i want to eport all of them at one shot. same like import/export database.

If any one knows pls help me.

--Murali|||Well, maybe try to copy msdb ?
Or just transfer msdb's tables that are related to DTS ?|||The same explained in detail here (http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=204) .