Changing the value of a variable

I'm looking for all possible ways to change the value of a variable.

I know this ways:

- edit direct

- configuration file

- scripting task

- scripting component

It is possible to change the value with other components?

Can we assign the output of Execute SQL Task to a variable?|||

It is unclear whether you are looking for tasks/components that change the value of a variable or whether you are looking for ways that it can be done. Any task/component can theoretically change the value of any variable in scope (excluding system and read only variables of course). Additionally expressions can change the value of a variable and as you mention configurations can as well. Also the ForEach loop via its variable mappings will change variable values.

Off the top of my head the sql task, row count component, execute dts package task, and recordset destination change variable, but I wouldn't say this list is exhuastive.

Matt

|||

@. Matt:

I'm looking for a general overview. Perhaps there is an overview in BO or in a blog or anywhere else.

@.Nitesh:

try this:

1. Define a variable with the datatyp = Object

2. Set ResultSet to Single Row

3. At the ResultSet-Tab add a line and configure your variable to a Outputname

4. Write a SQL-Statement : select max(abst) as output from art

Loom

|||

There is a walkthrough of the Exec SQL Task here
(http://www.sqlis.com/default.aspx?58)

Another variable setting solution is the ExecValueVariable. Several tasks support this, which is a way to specify a variable into which the task puts some form of execution result data, for example the Transfer SQL Server Objects Task puts the number of objects transferred. These can be usefull, although MS's own implementation seems to be a bit sparse, but use them when you can.

sql

changing the value of @join_unique_key on existing filter

I need to change a filter on an existing publication and need to change the
value of @.join_unique_key to 0 from 1. I can't find any documented way of
doing this, is there any way to do this? Thanks, Scott
use sp_helpmergearticle to get the filter name. Then use
sp_changemergefilter to change this option. Here is an example
sp_changemergefilter @.publication = 'pubs',@.article =
'titleauthor',@.filtername = 'titleauthor_authors', @.property
'join_unique_key', @.value = 'true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription =1
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Scott Simons" <Scott.Simons.At.MealMagic.Com.Remove.This> wrote in message
news:6E5C0638-F986-47B0-8FE7-FD80C87FC5C5@.microsoft.com...
> I need to change a filter on an existing publication and need to change
the
> value of @.join_unique_key to 0 from 1. I can't find any documented way of
> doing this, is there any way to do this? Thanks, Scott

changing the types of particular columns in particular table in the database

hi,
i have more than 300 tables in the database. Out of that tables some table has column whose type is "char".I want to change only those tables' column to the type "nvarchar".
Is it possible to make the modification all at a once?
since i am changing each and every table , this takes a lots of time.

thanks in advance,
by
singam

Hi,

first of all there is no built in function for this. YOu have to do that one by one in the script, there is sure a chance to do this automagically but I am no fan of a full automatic procedure to modify the schema of a bunch of tables.

You can (as I always do ) let the SQL Server create the script for you and execute this on your own in bacthes for better error handling (as you didn′t point out if error handling is applicable for you like in SQL Server 2005)

SELECT 'ALTER TABLE ' + C.TABLE_NAME +' ALTER COLUMN ' + C.COLUMN_NAME + ' VARCHAR(50)' --New data type and length
FROM INFORMATION_SCHEMA.Columns C
INNER JOIN INFORMATION_SCHEMA.Tables T
ON
T.TABLE_CATALOG = C.TABLE_CATALOG AND
T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE
DATA_TYPE = 'NVARCHAR' AND --Old Type
CHARACTER_MAXIMUM_LENGTH = 50 AND --Old lenght
TABLE_TYPE = 'BASE TABLE'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

changing the time format

Hi, We are using an application .It 's database is on SQL server 2000. One of its module is getting the information from pbx and writing the cost to customer's folios.But sometimes it cannot calculate the right cost because of signalization.Database has a table with column writing the time in mm:ss format.I want to write a trigger that will calculate and insert the right cost if it is wrong.But i have to change the time format from mm:ss to seconds only,for calculating .How can i do it.(It must convert to seconds only while calculating but it musn't change the format on the table else program won't run properly)Thanxis the column a datetime datatype or is the data stored as mm:ss in, I guess a varchar datatype?

if you have a datetime datatype then use DATEPART to extract the minutes and seconds and do the math.

select (DATEPART(mi,<column>) * 60) + DATEPART(ss,<column>)

if you have a varchar datatype then you follow the same idea but convert the strings into ints.

select cast(left(<column>,2) as int) * 60) + cast(right(<column>,2) as int)

Changing the text of the code of multiple stored procedure

Hi,
We have four different SQL Servers and each server has 2-3 databases.
The stored procedures on each database use link servers to access each
other. The link server names are based on the actual server name.
The Issue:- We are migrating to SQL 2005 and to save on the licence
cost we are planning to consolidate all our databases on one more
powerful machine which can take load of peak hours. Now will it be
advisable to create link servers on the new server by same names as
existing on the current servers, so that we don't have to change the
code. Or should we change the code and remove link servers from in the
code of each SP and trigger.
I think quires using the link server will be slower then directly
assessing other databases.
For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
If the best solution is to change the code then what is the best &
fast way to do so (there are more then 1000 SP, functions and triggers
to be changed).
Following are the few differrent approches I've followed:-
1) I tried to change in the syscomments table but SQL Server 2005
won't allow me to change the sys tables.
2) I wrote a SP to find out the search string in the code using
systables and once I had the object name where the search string is
used then i imported the code in a temp table using sp_helptext. Then
removed the linkserver in the temp table and bcp the temp table into a
txt file (upto this point I have created a script of SP/trigger with
the changed code)
The issue:- a) BCP is inserting multiple blank lines in the txt file.
How do I make sure these unwanted blank lines do not appear in the txt
file. The reason why I'm more worried abt the blank lines is because
each SP might go thru multiple change cycles as I described above (if
different linkservers are used in the same SP/trigger) and every time
there will be balnak lines added which will blot the code in terms of
LOC and makes it difficult to maintain long term (readability issues).
The bcp command I'm using as follows:-
'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
@.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
b) How do I execute 1000 or so scripts automatically.
Thanks very much in advance to respond.
Cheers,
SG
Hi
"subodh97@.gmail.com" wrote:

> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
>
The easiest way would be to script out your stored procedures using the
scripting options in SSMS or Enterprise Manager and then globally replace
each instance with a text editor. If you used source code control for the
your database code this would not be an issue and you can track and control
the changes to the source when you start to implement SQL 2005 features.
John
|||SG,
I'm not sure about SQL Server 2005, but with SQL Server 2K I remember having
performance overhead when using a 4 part name with a database on the same
server. SQL Server still goes through unnecessary additional security,
collation conversion, etc. layers as if it was linking to another server.
You might want to consider the following low tech approach:
1. Script out all stored procs to a file.
2. Replace "ServerName." with "".
3. Replace "Create Procedure" with "Alter Procedure".
4. Apply the script.
-- Bill
<subodh97@.gmail.com> wrote in message
news:1173977536.347970.223200@.p15g2000hsd.googlegr oups.com...
> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
>
|||On Mar 15, 1:39 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
> "subod...@.gmail.com" wrote:
>
>
>
>
>
>
>
> The easiest way would be to script out your stored procedures using the
> scripting options in SSMS or Enterprise Manager and then globally replace
> each instance with a text editor. If you used source code control for the
> your database code this would not be an issue and you can track and control
> the changes to the source when you start to implement SQL 2005 features.
> John
Thanks John, The thing is we don't have any source control at this
point (may be future we'll have VSS). And also to run the scripts
manually which I don't want to do on the final day of migration. If
somebody can help me telling the way how to get away with the numerous
blank lines while bcp..ing the code to a text file then half of the
battle is won. Following is the actual code of the SP:-
/*
Author - SG
*/
ALTER PROC [dbo].[sp_use_linkserver]
AS
BEGIN
DECLARE
@.count INT
-- Just a comment
SET @.count = (SELECT count(*) FROM LINKSRV2.testdb.dbo.testtable)
print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
END
Following is the code in txt file generated:-
/*
Author - Subodh Goyal
*/
CREATE PROC [dbo].[sp_use_linkserver]
AS
BEGIN
DECLARE
@.count INT
-- Just a comment
SET @.count = (SELECT count(*) FROM LINKSRV1.testdb.dbo.testtable)
print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
END
Hope this help in understanding the issue.
SG
|||On Mar 15, 2:33 pm, "AlterEgo" <altereg...@.dslextreme.com> wrote:[vbcol=seagreen]
> SG,
> I'm not sure about SQL Server 2005, but with SQL Server 2K I remember having
> performance overhead when using a 4 part name with a database on the same
> server. SQL Server still goes through unnecessary additional security,
> collation conversion, etc. layers as if it was linking to another server.
> You might want to consider the following low tech approach:
> 1. Script out all stored procs to a file.
> 2. Replace "ServerName." with "".
> 3. Replace "Create Procedure" with "Alter Procedure".
> 4. Apply the script.
> -- Bill
> <subod...@.gmail.com> wrote in message
> news:1173977536.347970.223200@.p15g2000hsd.googlegr oups.com...
>
>
>
>
>
>
>
Thanks Bill,
The only issue in following this approach is sheer amount of work on
the final migration day. There are about 12 different databases which
will be migrating on single machine. Also I can not script all the
databases before hand they need to be scripted on the same day. Also
there are SP. functions and triggers which will make this exercise not
smooth. Anyways this is going to be my last option if nothing else can
be done before the final migration day.
SG
|||Hi
I am surprised that your stored procedures/functions/triggers are so
volatile you can not do this till the last minute? It is not a good idea to
combine an upgrade with major changes to the software unless it is necessary
to make it work on the new system. It is not a good idea to implement these
changes blindly without doing an test migration and regression test cycle
before you implement this on a live system.
Have you run the SQL 2005 upgrade advisor on these systems?
John
|||On Mar 15, 9:52 pm, subod...@.gmail.com wrote:
> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
Please check synonym in BOL
|||On Mar 16, 4:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I am surprised that your stored procedures/functions/triggers are so
> volatile you can not do this till the last minute? It is not a good idea to
> combine an upgrade with major changes to the software unless it is necessary
> to make it work on the new system. It is not a good idea to implement these
> changes blindly without doing an test migration and regression test cycle
> before you implement this on a live system.
> Have you run the SQL 2005 upgrade advisor on these systems?
> John
Hi John, Thanks for the reply. We are getting the new hardware for the
migration and planning to do a mock migration first on the new server
and put that new server and applications on testing mode. Our current
production servers will still run in parallel. There will be a
difference of abt a month or so in mock and actual migration and I
cannot stop any release of new code to the current production servers
during that one month or so period due to the nature of business and
4-5 different teams involved in it. The solution I'm looking is to
generate the right scripts dynamically so that I can incorporate any
changes done during the month of testing in the final migration.
By the way I've run Upgrade adviser on all the servers and there were
no major issues other then few of the DTS packages we have to re-write
in 2005 SSIS.
I'll try DMO option to see how effective and easy it can be.
Thanks,
SG
|||On Mar 16, 2:07 pm, subod...@.gmail.com wrote:
> On Mar 16, 4:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi John, Thanks for the reply. We are getting the new hardware for the
> migration and planning to do a mock migration first on the new server
> and put that new server and applications on testing mode. Our current
> production servers will still run in parallel. There will be a
> difference of abt a month or so in mock and actual migration and I
> cannot stop any release of new code to the current production servers
> during that one month or so period due to the nature of business and
> 4-5 different teams involved in it. The solution I'm looking is to
> generate the right scripts dynamically so that I can incorporate any
> changes done during the month of testing in the final migration.
>
Not freezing development is making this task significantly more risky.
Source code control would have made your task significantly easier. An
option may be to have a second cut over for a shorter period where you
run through all the tests and freeze the code for that shorter time? A
different approach would be to take backups of the databases that were
cut over and then compare a restored backup against the live database
at the final cutover using a tool such as Red Gate SQL Compare or
DBGhost. This will show you everything that you will need to change in
the migration. Having two copies of a given database may require
significant disc space, but they do not have to be on the same
machines.

> By the way I've run Upgrade adviser on all the servers and there were
> no major issues other then few of the DTS packages we have to re-write
> in 2005 SSIS.
> I'll try DMO option to see how effective and easy it can be.
> Thanks,
> SG
John

Changing the text of the code of multiple stored procedure

Hi,
We have four different SQL Servers and each server has 2-3 databases.
The stored procedures on each database use link servers to access each
other. The link server names are based on the actual server name.
The Issue:- We are migrating to SQL 2005 and to save on the licence
cost we are planning to consolidate all our databases on one more
powerful machine which can take load of peak hours. Now will it be
advisable to create link servers on the new server by same names as
existing on the current servers, so that we don't have to change the
code. Or should we change the code and remove link servers from in the
code of each SP and trigger.
I think quires using the link server will be slower then directly
assessing other databases.
For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
If the best solution is to change the code then what is the best &
fast way to do so (there are more then 1000 SP, functions and triggers
to be changed).
Following are the few differrent approches I've followed:-
1) I tried to change in the syscomments table but SQL Server 2005
won't allow me to change the sys tables.
2) I wrote a SP to find out the search string in the code using
systables and once I had the object name where the search string is
used then i imported the code in a temp table using sp_helptext. Then
removed the linkserver in the temp table and bcp the temp table into a
txt file (upto this point I have created a script of SP/trigger with
the changed code)
The issue:- a) BCP is inserting multiple blank lines in the txt file.
How do I make sure these unwanted blank lines do not appear in the txt
file. The reason why I'm more worried abt the blank lines is because
each SP might go thru multiple change cycles as I described above (if
different linkservers are used in the same SP/trigger) and every time
there will be balnak lines added which will blot the code in terms of
LOC and makes it difficult to maintain long term (readability issues).
The bcp command I'm using as follows:-
'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
@.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
b) How do I execute 1000 or so scripts automatically.
Thanks very much in advance to respond.
Cheers,
SGHi
"subodh97@.gmail.com" wrote:
> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
>
The easiest way would be to script out your stored procedures using the
scripting options in SSMS or Enterprise Manager and then globally replace
each instance with a text editor. If you used source code control for the
your database code this would not be an issue and you can track and control
the changes to the source when you start to implement SQL 2005 features.
John|||SG,
I'm not sure about SQL Server 2005, but with SQL Server 2K I remember having
performance overhead when using a 4 part name with a database on the same
server. SQL Server still goes through unnecessary additional security,
collation conversion, etc. layers as if it was linking to another server.
You might want to consider the following low tech approach:
1. Script out all stored procs to a file.
2. Replace "ServerName." with "".
3. Replace "Create Procedure" with "Alter Procedure".
4. Apply the script.
-- Bill
<subodh97@.gmail.com> wrote in message
news:1173977536.347970.223200@.p15g2000hsd.googlegroups.com...
> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
>|||On Mar 15, 1:39 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
> "subod...@.gmail.com" wrote:
> > Hi,
> > We have four different SQL Servers and each server has 2-3 databases.
> > The stored procedures on each database use link servers to access each
> > other. The link server names are based on the actual server name.
> > The Issue:- We are migrating to SQL 2005 and to save on the licence
> > cost we are planning to consolidate all our databases on one more
> > powerful machine which can take load of peak hours. Now will it be
> > advisable to create link servers on the new server by same names as
> > existing on the current servers, so that we don't have to change the
> > code. Or should we change the code and remove link servers from in the
> > code of each SP and trigger.
> > I think quires using the link server will be slower then directly
> > assessing other databases.
> > For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> > If the best solution is to change the code then what is the best &
> > fast way to do so (there are more then 1000 SP, functions and triggers
> > to be changed).
> > Following are the few differrent approches I've followed:-
> > 1) I tried to change in the syscomments table but SQL Server 2005
> > won't allow me to change the sys tables.
> > 2) I wrote a SP to find out the search string in the code using
> > systables and once I had the object name where the search string is
> > used then i imported the code in a temp table using sp_helptext. Then
> > removed the linkserver in the temp table and bcp the temp table into a
> > txt file (upto this point I have created a script of SP/trigger with
> > the changed code)
> > The issue:- a) BCP is inserting multiple blank lines in the txt file.
> > How do I make sure these unwanted blank lines do not appear in the txt
> > file. The reason why I'm more worried abt the blank lines is because
> > each SP might go thru multiple change cycles as I described above (if
> > different linkservers are used in the same SP/trigger) and every time
> > there will be balnak lines added which will blot the code in terms of
> > LOC and makes it difficult to maintain long term (readability issues).
> > The bcp command I'm using as follows:-
> > 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> > @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> > b) How do I execute 1000 or so scripts automatically.
> > Thanks very much in advance to respond.
> > Cheers,
> > SG
> The easiest way would be to script out your stored procedures using the
> scripting options in SSMS or Enterprise Manager and then globally replace
> each instance with a text editor. If you used source code control for the
> your database code this would not be an issue and you can track and control
> the changes to the source when you start to implement SQL 2005 features.
> John
Thanks John, The thing is we don't have any source control at this
point (may be future we'll have VSS). And also to run the scripts
manually which I don't want to do on the final day of migration. If
somebody can help me telling the way how to get away with the numerous
blank lines while bcp..ing the code to a text file then half of the
battle is won. Following is the actual code of the SP:-
/*
Author - SG
*/
ALTER PROC [dbo].[sp_use_linkserver]
AS
BEGIN
DECLARE
@.count INT
-- Just a comment
SET @.count = (SELECT count(*) FROM LINKSRV2.testdb.dbo.testtable)
print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
END
Following is the code in txt file generated:-
/*
Author - Subodh Goyal
*/
CREATE PROC [dbo].[sp_use_linkserver]
AS
BEGIN
DECLARE
@.count INT
-- Just a comment
SET @.count = (SELECT count(*) FROM LINKSRV1.testdb.dbo.testtable)
print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
END
Hope this help in understanding the issue.
SG|||On Mar 15, 2:33 pm, "AlterEgo" <altereg...@.dslextreme.com> wrote:
> SG,
> I'm not sure about SQL Server 2005, but with SQL Server 2K I remember having
> performance overhead when using a 4 part name with a database on the same
> server. SQL Server still goes through unnecessary additional security,
> collation conversion, etc. layers as if it was linking to another server.
> You might want to consider the following low tech approach:
> 1. Script out all stored procs to a file.
> 2. Replace "ServerName." with "".
> 3. Replace "Create Procedure" with "Alter Procedure".
> 4. Apply the script.
> -- Bill
> <subod...@.gmail.com> wrote in message
> news:1173977536.347970.223200@.p15g2000hsd.googlegroups.com...
> > Hi,
> > We have four different SQL Servers and each server has 2-3 databases.
> > The stored procedures on each database use link servers to access each
> > other. The link server names are based on the actual server name.
> > The Issue:- We are migrating to SQL 2005 and to save on the licence
> > cost we are planning to consolidate all our databases on one more
> > powerful machine which can take load of peak hours. Now will it be
> > advisable to create link servers on the new server by same names as
> > existing on the current servers, so that we don't have to change the
> > code. Or should we change the code and remove link servers from in the
> > code of each SP and trigger.
> > I think quires using the link server will be slower then directly
> > assessing other databases.
> > For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> > If the best solution is to change the code then what is the best &
> > fast way to do so (there are more then 1000 SP, functions and triggers
> > to be changed).
> > Following are the few differrent approches I've followed:-
> > 1) I tried to change in the syscomments table but SQL Server 2005
> > won't allow me to change the sys tables.
> > 2) I wrote a SP to find out the search string in the code using
> > systables and once I had the object name where the search string is
> > used then i imported the code in a temp table using sp_helptext. Then
> > removed the linkserver in the temp table and bcp the temp table into a
> > txt file (upto this point I have created a script of SP/trigger with
> > the changed code)
> > The issue:- a) BCP is inserting multiple blank lines in the txt file.
> > How do I make sure these unwanted blank lines do not appear in the txt
> > file. The reason why I'm more worried abt the blank lines is because
> > each SP might go thru multiple change cycles as I described above (if
> > different linkservers are used in the same SP/trigger) and every time
> > there will be balnak lines added which will blot the code in terms of
> > LOC and makes it difficult to maintain long term (readability issues).
> > The bcp command I'm using as follows:-
> > 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> > @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> > b) How do I execute 1000 or so scripts automatically.
> > Thanks very much in advance to respond.
> > Cheers,
> > SG
Thanks Bill,
The only issue in following this approach is sheer amount of work on
the final migration day. There are about 12 different databases which
will be migrating on single machine. Also I can not script all the
databases before hand they need to be scripted on the same day. Also
there are SP. functions and triggers which will make this exercise not
smooth. Anyways this is going to be my last option if nothing else can
be done before the final migration day.
SG|||Hi
sp_helptext puts in the extra lines, you could replace the extra char(10)
and/or char(13) characters using the SQL REPLACE function, but I am not sure
that the method you are using is going to work as it may not reliably produce
line breaks in the correct place and you may get words broken.
If you do not want to use scripting then look at using DMO to get the
procedure definition and change them accordingly. This will allow you to only
change the ones where you find the linked servers.
John
"subodh97@.gmail.com" wrote:
> On Mar 15, 1:39 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> >
> >
> > "subod...@.gmail.com" wrote:
> > > Hi,
> >
> > > We have four different SQL Servers and each server has 2-3 databases.
> > > The stored procedures on each database use link servers to access each
> > > other. The link server names are based on the actual server name.
> >
> > > The Issue:- We are migrating to SQL 2005 and to save on the licence
> > > cost we are planning to consolidate all our databases on one more
> > > powerful machine which can take load of peak hours. Now will it be
> > > advisable to create link servers on the new server by same names as
> > > existing on the current servers, so that we don't have to change the
> > > code. Or should we change the code and remove link servers from in the
> > > code of each SP and trigger.
> >
> > > I think quires using the link server will be slower then directly
> > > assessing other databases.
> >
> > > For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> >
> > > If the best solution is to change the code then what is the best &
> > > fast way to do so (there are more then 1000 SP, functions and triggers
> > > to be changed).
> >
> > > Following are the few differrent approches I've followed:-
> >
> > > 1) I tried to change in the syscomments table but SQL Server 2005
> > > won't allow me to change the sys tables.
> > > 2) I wrote a SP to find out the search string in the code using
> > > systables and once I had the object name where the search string is
> > > used then i imported the code in a temp table using sp_helptext. Then
> > > removed the linkserver in the temp table and bcp the temp table into a
> > > txt file (upto this point I have created a script of SP/trigger with
> > > the changed code)
> >
> > > The issue:- a) BCP is inserting multiple blank lines in the txt file.
> > > How do I make sure these unwanted blank lines do not appear in the txt
> > > file. The reason why I'm more worried abt the blank lines is because
> > > each SP might go thru multiple change cycles as I described above (if
> > > different linkservers are used in the same SP/trigger) and every time
> > > there will be balnak lines added which will blot the code in terms of
> > > LOC and makes it difficult to maintain long term (readability issues).
> >
> > > The bcp command I'm using as follows:-
> > > 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> > > @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> >
> > > b) How do I execute 1000 or so scripts automatically.
> >
> > > Thanks very much in advance to respond.
> >
> > > Cheers,
> >
> > > SG
> >
> > The easiest way would be to script out your stored procedures using the
> > scripting options in SSMS or Enterprise Manager and then globally replace
> > each instance with a text editor. If you used source code control for the
> > your database code this would not be an issue and you can track and control
> > the changes to the source when you start to implement SQL 2005 features.
> >
> > John
> Thanks John, The thing is we don't have any source control at this
> point (may be future we'll have VSS). And also to run the scripts
> manually which I don't want to do on the final day of migration. If
> somebody can help me telling the way how to get away with the numerous
> blank lines while bcp..ing the code to a text file then half of the
> battle is won. Following is the actual code of the SP:-
> /*
> Author - SG
> */
> ALTER PROC [dbo].[sp_use_linkserver]
> AS
> BEGIN
> DECLARE
> @.count INT
> -- Just a comment
> SET @.count = (SELECT count(*) FROM LINKSRV2.testdb.dbo.testtable)
> print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
> END
>
> Following is the code in txt file generated:-
>
> /*
> Author - Subodh Goyal
> */
>
> CREATE PROC [dbo].[sp_use_linkserver]
>
> AS
>
> BEGIN
>
> DECLARE
> @.count INT
> -- Just a comment
> SET @.count = (SELECT count(*) FROM LINKSRV1.testdb.dbo.testtable)
> print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
> END
>
>
>
>
>
>
>
> Hope this help in understanding the issue.
> SG
>|||Hi
I am surprised that your stored procedures/functions/triggers are so
volatile you can not do this till the last minute? It is not a good idea to
combine an upgrade with major changes to the software unless it is necessary
to make it work on the new system. It is not a good idea to implement these
changes blindly without doing an test migration and regression test cycle
before you implement this on a live system.
Have you run the SQL 2005 upgrade advisor on these systems?
John|||On Mar 15, 9:52 pm, subod...@.gmail.com wrote:
> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
Please check synonym in BOL|||On Mar 16, 4:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I am surprised that your stored procedures/functions/triggers are so
> volatile you can not do this till the last minute? It is not a good idea to
> combine an upgrade with major changes to the software unless it is necessary
> to make it work on the new system. It is not a good idea to implement these
> changes blindly without doing an test migration and regression test cycle
> before you implement this on a live system.
> Have you run the SQL 2005 upgrade advisor on these systems?
> John
Hi John, Thanks for the reply. We are getting the new hardware for the
migration and planning to do a mock migration first on the new server
and put that new server and applications on testing mode. Our current
production servers will still run in parallel. There will be a
difference of abt a month or so in mock and actual migration and I
cannot stop any release of new code to the current production servers
during that one month or so period due to the nature of business and
4-5 different teams involved in it. The solution I'm looking is to
generate the right scripts dynamically so that I can incorporate any
changes done during the month of testing in the final migration.
By the way I've run Upgrade adviser on all the servers and there were
no major issues other then few of the DTS packages we have to re-write
in 2005 SSIS.
I'll try DMO option to see how effective and easy it can be.
Thanks,
SG|||On Mar 16, 2:07 pm, subod...@.gmail.com wrote:
> On Mar 16, 4:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi John, Thanks for the reply. We are getting the new hardware for the
> migration and planning to do a mock migration first on the new server
> and put that new server and applications on testing mode. Our current
> production servers will still run in parallel. There will be a
> difference of abt a month or so in mock and actual migration and I
> cannot stop any release of new code to the current production servers
> during that one month or so period due to the nature of business and
> 4-5 different teams involved in it. The solution I'm looking is to
> generate the right scripts dynamically so that I can incorporate any
> changes done during the month of testing in the final migration.
>
Not freezing development is making this task significantly more risky.
Source code control would have made your task significantly easier. An
option may be to have a second cut over for a shorter period where you
run through all the tests and freeze the code for that shorter time? A
different approach would be to take backups of the databases that were
cut over and then compare a restored backup against the live database
at the final cutover using a tool such as Red Gate SQL Compare or
DBGhost. This will show you everything that you will need to change in
the migration. Having two copies of a given database may require
significant disc space, but they do not have to be on the same
machines.
> By the way I've run Upgrade adviser on all the servers and there were
> no major issues other then few of the DTS packages we have to re-write
> in 2005 SSIS.
> I'll try DMO option to see how effective and easy it can be.
> Thanks,
> SG
Johnsql

Changing the text of the code of multiple stored procedure

Hi,
We have four different SQL Servers and each server has 2-3 databases.
The stored procedures on each database use link servers to access each
other. The link server names are based on the actual server name.
The Issue:- We are migrating to SQL 2005 and to save on the licence
cost we are planning to consolidate all our databases on one more
powerful machine which can take load of peak hours. Now will it be
advisable to create link servers on the new server by same names as
existing on the current servers, so that we don't have to change the
code. Or should we change the code and remove link servers from in the
code of each SP and trigger.
I think quires using the link server will be slower then directly
assessing other databases.
For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
If the best solution is to change the code then what is the best &
fast way to do so (there are more then 1000 SP, functions and triggers
to be changed).
Following are the few differrent approches I've followed:-
1) I tried to change in the syscomments table but SQL Server 2005
won't allow me to change the sys tables.
2) I wrote a SP to find out the search string in the code using
systables and once I had the object name where the search string is
used then i imported the code in a temp table using sp_helptext. Then
removed the linkserver in the temp table and bcp the temp table into a
txt file (upto this point I have created a script of SP/trigger with
the changed code)
The issue:- a) BCP is inserting multiple blank lines in the txt file.
How do I make sure these unwanted blank lines do not appear in the txt
file. The reason why I'm more worried abt the blank lines is because
each SP might go thru multiple change cycles as I described above (if
different linkservers are used in the same SP/trigger) and every time
there will be balnak lines added which will blot the code in terms of
LOC and makes it difficult to maintain long term (readability issues).
The bcp command I'm using as follows:-
'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
@.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
b) How do I execute 1000 or so scripts automatically.
Thanks very much in advance to respond.
Cheers,
SGHi
"subodh97@.gmail.com" wrote:

> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
>
The easiest way would be to script out your stored procedures using the
scripting options in SSMS or Enterprise Manager and then globally replace
each instance with a text editor. If you used source code control for the
your database code this would not be an issue and you can track and control
the changes to the source when you start to implement SQL 2005 features.
John|||SG,
I'm not sure about SQL Server 2005, but with SQL Server 2K I remember having
performance overhead when using a 4 part name with a database on the same
server. SQL Server still goes through unnecessary additional security,
collation conversion, etc. layers as if it was linking to another server.
You might want to consider the following low tech approach:
1. Script out all stored procs to a file.
2. Replace "ServerName." with "".
3. Replace "Create Procedure" with "Alter Procedure".
4. Apply the script.
-- Bill
<subodh97@.gmail.com> wrote in message
news:1173977536.347970.223200@.p15g2000hsd.googlegroups.com...
> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
>|||On Mar 15, 1:39 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
> "subod...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
> The easiest way would be to script out your stored procedures using the
> scripting options in SSMS or Enterprise Manager and then globally replace
> each instance with a text editor. If you used source code control for the
> your database code this would not be an issue and you can track and contro
l
> the changes to the source when you start to implement SQL 2005 features.
> John
Thanks John, The thing is we don't have any source control at this
point (may be future we'll have VSS). And also to run the scripts
manually which I don't want to do on the final day of migration. If
somebody can help me telling the way how to get away with the numerous
blank lines while bcp..ing the code to a text file then half of the
battle is won. Following is the actual code of the SP:-
/*
Author - SG
*/
ALTER PROC [dbo].[sp_use_linkserver]
AS
BEGIN
DECLARE
@.count INT
-- Just a comment
SET @.count = (SELECT count(*) FROM LINKSRV2.testdb.dbo.testtable)
print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
END
Following is the code in txt file generated:-
/*
Author - Subodh Goyal
*/
CREATE PROC [dbo].[sp_use_linkserver]
AS
BEGIN
DECLARE
@.count INT
-- Just a comment
SET @.count = (SELECT count(*) FROM LINKSRV1.testdb.dbo.testtable)
print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
END

Hope this help in understanding the issue.
SG|||On Mar 15, 2:33 pm, "AlterEgo" <altereg...@.dslextreme.com> wrote:[vbcol=seagreen]
> SG,
> I'm not sure about SQL Server 2005, but with SQL Server 2K I remember havi
ng
> performance overhead when using a 4 part name with a database on the same
> server. SQL Server still goes through unnecessary additional security,
> collation conversion, etc. layers as if it was linking to another server.
> You might want to consider the following low tech approach:
> 1. Script out all stored procs to a file.
> 2. Replace "ServerName." with "".
> 3. Replace "Create Procedure" with "Alter Procedure".
> 4. Apply the script.
> -- Bill
> <subod...@.gmail.com> wrote in message
> news:1173977536.347970.223200@.p15g2000hsd.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Thanks Bill,
The only issue in following this approach is sheer amount of work on
the final migration day. There are about 12 different databases which
will be migrating on single machine. Also I can not script all the
databases before hand they need to be scripted on the same day. Also
there are SP. functions and triggers which will make this exercise not
smooth. Anyways this is going to be my last option if nothing else can
be done before the final migration day.
SG|||Hi
sp_helptext puts in the extra lines, you could replace the extra char(10)
and/or char(13) characters using the SQL REPLACE function, but I am not sure
that the method you are using is going to work as it may not reliably produc
e
line breaks in the correct place and you may get words broken.
If you do not want to use scripting then look at using DMO to get the
procedure definition and change them accordingly. This will allow you to onl
y
change the ones where you find the linked servers.
John
"subodh97@.gmail.com" wrote:

> On Mar 15, 1:39 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Thanks John, The thing is we don't have any source control at this
> point (may be future we'll have VSS). And also to run the scripts
> manually which I don't want to do on the final day of migration. If
> somebody can help me telling the way how to get away with the numerous
> blank lines while bcp..ing the code to a text file then half of the
> battle is won. Following is the actual code of the SP:-
> /*
> Author - SG
> */
> ALTER PROC [dbo].[sp_use_linkserver]
> AS
> BEGIN
> DECLARE
> @.count INT
> -- Just a comment
> SET @.count = (SELECT count(*) FROM LINKSRV2.testdb.dbo.testtable)
> print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
> END
>
> Following is the code in txt file generated:-
>
> /*
> Author - Subodh Goyal
> */
>
> CREATE PROC [dbo].[sp_use_linkserver]
>
> AS
>
> BEGIN
>
> DECLARE
> @.count INT
> -- Just a comment
> SET @.count = (SELECT count(*) FROM LINKSRV1.testdb.dbo.testtable)
> print 'Total # of sites in R3 = ' + CAST(@.count AS VARCHAR(10))
> END
>
>
>
>
>
>
>
> Hope this help in understanding the issue.
> SG
>|||Hi
I am surprised that your stored procedures/functions/triggers are so
volatile you can not do this till the last minute? It is not a good idea to
combine an upgrade with major changes to the software unless it is necessary
to make it work on the new system. It is not a good idea to implement these
changes blindly without doing an test migration and regression test cycle
before you implement this on a live system.
Have you run the SQL 2005 upgrade advisor on these systems?
John|||On Mar 15, 9:52 pm, subod...@.gmail.com wrote:
> Hi,
> We have four different SQL Servers and each server has 2-3 databases.
> The stored procedures on each database use link servers to access each
> other. The link server names are based on the actual server name.
> The Issue:- We are migrating to SQL 2005 and to save on the licence
> cost we are planning to consolidate all our databases on one more
> powerful machine which can take load of peak hours. Now will it be
> advisable to create link servers on the new server by same names as
> existing on the current servers, so that we don't have to change the
> code. Or should we change the code and remove link servers from in the
> code of each SP and trigger.
> I think quires using the link server will be slower then directly
> assessing other databases.
> For Ex <linkserver>.<dbname>.dbo.<tablename> vs. <dbname>..<tablename>
> If the best solution is to change the code then what is the best &
> fast way to do so (there are more then 1000 SP, functions and triggers
> to be changed).
> Following are the few differrent approches I've followed:-
> 1) I tried to change in the syscomments table but SQL Server 2005
> won't allow me to change the sys tables.
> 2) I wrote a SP to find out the search string in the code using
> systables and once I had the object name where the search string is
> used then i imported the code in a temp table using sp_helptext. Then
> removed the linkserver in the temp table and bcp the temp table into a
> txt file (upto this point I have created a script of SP/trigger with
> the changed code)
> The issue:- a) BCP is inserting multiple blank lines in the txt file.
> How do I make sure these unwanted blank lines do not appear in the txt
> file. The reason why I'm more worried abt the blank lines is because
> each SP might go thru multiple change cycles as I described above (if
> different linkservers are used in the same SP/trigger) and every time
> there will be balnak lines added which will blot the code in terms of
> LOC and makes it difficult to maintain long term (readability issues).
> The bcp command I'm using as follows:-
> 'bcp '+ @.db_name + '.dbo.temp_helptext out '+ @.bcp_path + '' +
> @.obj_name + '.txt -c -U<xx> -P<xxxxx> -S<xxxxxxxxxx>'
> b) How do I execute 1000 or so scripts automatically.
> Thanks very much in advance to respond.
> Cheers,
> SG
Please check synonym in BOL|||On Mar 16, 4:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I am surprised that your stored procedures/functions/triggers are so
> volatile you can not do this till the last minute? It is not a good idea t
o
> combine an upgrade with major changes to the software unless it is necessa
ry
> to make it work on the new system. It is not a good idea to implement thes
e
> changes blindly without doing an test migration and regression test cycle
> before you implement this on a live system.
> Have you run the SQL 2005 upgrade advisor on these systems?
> John
Hi John, Thanks for the reply. We are getting the new hardware for the
migration and planning to do a mock migration first on the new server
and put that new server and applications on testing mode. Our current
production servers will still run in parallel. There will be a
difference of abt a month or so in mock and actual migration and I
cannot stop any release of new code to the current production servers
during that one month or so period due to the nature of business and
4-5 different teams involved in it. The solution I'm looking is to
generate the right scripts dynamically so that I can incorporate any
changes done during the month of testing in the final migration.
By the way I've run Upgrade adviser on all the servers and there were
no major issues other then few of the DTS packages we have to re-write
in 2005 SSIS.
I'll try DMO option to see how effective and easy it can be.
Thanks,
SG|||On Mar 16, 2:07 pm, subod...@.gmail.com wrote:
> On Mar 16, 4:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi John, Thanks for the reply. We are getting the new hardware for the
> migration and planning to do a mock migration first on the new server
> and put that new server and applications on testing mode. Our current
> production servers will still run in parallel. There will be a
> difference of abt a month or so in mock and actual migration and I
> cannot stop any release of new code to the current production servers
> during that one month or so period due to the nature of business and
> 4-5 different teams involved in it. The solution I'm looking is to
> generate the right scripts dynamically so that I can incorporate any
> changes done during the month of testing in the final migration.
>
Not freezing development is making this task significantly more risky.
Source code control would have made your task significantly easier. An
option may be to have a second cut over for a shorter period where you
run through all the tests and freeze the code for that shorter time? A
different approach would be to take backups of the databases that were
cut over and then compare a restored backup against the live database
at the final cutover using a tool such as Red Gate SQL Compare or
DBGhost. This will show you everything that you will need to change in
the migration. Having two copies of a given database may require
significant disc space, but they do not have to be on the same
machines.

> By the way I've run Upgrade adviser on all the servers and there were
> no major issues other then few of the DTS packages we have to re-write
> in 2005 SSIS.
> I'll try DMO option to see how effective and easy it can be.
> Thanks,
> SG
John

Changing the text for the Document Map top level node

Can anyone tell me if it is possible to change the text on the top node of the Document Map? I was looking for a property in the Visual Studio designer or a property I could set programmatically but I couldn't find either.

The text on the top node is the file name of my Microsoft report. That is, the report file name isSalesRepOrderDetail.rdlc and the Document Map top node text isSalesRepOrderDetail. It looks pretty unpolished.

Thanks!

Black Cat Bone

I found it. In designer, expand theLocalReport property for the report viewer control and assign a string to theDisplayName property. This becomes the text on the Document Map's top node.

The code equivalent is: ReportViewer1.LocalReport.DisplayName = "Top Node Text";

|||

Thank,

How can I bring the DisplayName into the report, for example I want to show

the DisplayName in the footer of the report too.

Changing the Table Type

Is there a to change the Type property on a table? I would like to be able
to change the Type from 'User' to 'System' through code.
Adv-thanks-anceWhat do you need this for?
True system tables have object id values less than 100, and there is no way
you can create a table with such an id.
If you are only talking about what shows up in the 'type' column when you
list the objects in Enterprise Manager, you can run the procedure
exec sp_MS_marksystemobject 'mytable'
However, it would not actually be a system table, even though Enterprise
Manager lists it as such. For example, it would still show 'user table' when
using sp_help, and you would not need to set any special flags to
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:A946CD68-5A88-4E55-B813-4D1ADBF49DA6@.microsoft.com...
> Is there a to change the Type property on a table? I would like to be
> able
> to change the Type from 'User' to 'System' through code.
> Adv-thanks-ance
>

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-December
This is a transactional replication (immediate updating).
|||This is a transactional replication (immediate updating).
|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com
|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com
|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
>
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.comsql

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:
> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> > This is a transactional replication (immediate updating).
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
> I'm using SQL Server 2000
> amish wrote:
> > december wrote:
> >
> > > This is a transactional replication (immediate updating).
> > which version of SQL Server you are using ?
> > SQL Server 2000 or 2005?
> >
> > If you have SQL Server 2005 then alter table will work for most
> > changes.
> >
> > Regards
> > Amish Shah
> > http://shahamishm.tripod.com
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing the system (master) collation setting in SQL Server 2005 Express Edition

Does anyone know how to do the above without having to re-install SQL Server?

Cheers

Simon.

This is documented in the Books Online Topic located at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3242deef-6f5f-4051-a121-36b3b4da851d.htm or on MSDN at http://msdn2.microsoft.com/en-us/library/ms179254(en-US,SQL.90).aspx

Cheers,
Dan

changing the sync_type option of a subscription

If I create a subscription with a sync_type set to 'replication support only' is it possible to change that option to 'automatic' down the road without destroying and recreating the
subscription?

-mike

You can look into the following articles on changing the subscription property.

How to: View and Modify Push Subscription Properties (SQL Server Management Studio)

http://msdn2.microsoft.com/en-us/library/ms151741.aspx

How to: View and Modify Push Subscription Properties (Replication Transact-SQL Programming)

http://msdn2.microsoft.com/en-us/library/ms147323.aspx

However, I don't see @.sync_type as one of the property you can change.

Do you mind I ask why you want to change from "automatic" to "replication support only" later? What is your scenario?

Gary

|||Automatic appears (and I need to do more reading on this) to give you a bit more flexability when adding items to a publication. For example, I have a publication and subscription (w/ sync_type = automatic) up and running and now I want to add another object to that publication. Once I've added the object all I need to do is regenerate the snapshot and that object is replicated to the subscriber.

So, that being said, I have a very large database that takes around four hours to deliver the initial snapshot. Due to the size and time of delivering that snapshot, it makes sense to create a publication with a subscription set to 'replication support only'. But down the road I may want to add objects to that publication and don't want to have to destroy and recreate the subscription and have to deliver the snapshot.|||

I guess no ..i have the same scenario still lurking and i am sure we have to drop and re-create the subscription to chnage the setting.

Script out replication run the script for subscription you want to change @.sync_type or u may use wizard to create the article and subscription for the same.

changing the sync_type option of a subscription

If I create a subscription with a sync_type set to 'replication support only' is it possible to change that option to 'automatic' down the road without destroying and recreating the
subscription?

-mike

You can look into the following articles on changing the subscription property.

How to: View and Modify Push Subscription Properties (SQL Server Management Studio)

http://msdn2.microsoft.com/en-us/library/ms151741.aspx

How to: View and Modify Push Subscription Properties (Replication Transact-SQL Programming)

http://msdn2.microsoft.com/en-us/library/ms147323.aspx

However, I don't see @.sync_type as one of the property you can change.

Do you mind I ask why you want to change from "automatic" to "replication support only" later? What is your scenario?

Gary

|||Automatic appears (and I need to do more reading on this) to give you a bit more flexability when adding items to a publication. For example, I have a publication and subscription (w/ sync_type = automatic) up and running and now I want to add another object to that publication. Once I've added the object all I need to do is regenerate the snapshot and that object is replicated to the subscriber.

So, that being said, I have a very large database that takes around four hours to deliver the initial snapshot. Due to the size and time of delivering that snapshot, it makes sense to create a publication with a subscription set to 'replication support only'. But down the road I may want to add objects to that publication and don't want to have to destroy and recreate the subscription and have to deliver the snapshot.|||

I guess no ..i have the same scenario still lurking and i am sure we have to drop and re-create the subscription to chnage the setting.

Script out replication run the script for subscription you want to change @.sync_type or u may use wizard to create the article and subscription for the same.

changing the structure of data in a table

I have a table that looks like this:
ID Type
123 Phone
123 Meeting
123 Phone
and I would like the data to look like this
ID phone Meeting
123 2 1
How do I do this?Search for crosstab, but I am sure someone will post you a code, just sit tight :)|||Code Please ?|||See this:

http://www.dbforums.com/showthread.php?threadid=978654sql

Changing the SQL Server Service Account

I have a SQL 2000 (SP3) running on a Windows NT 4.0 (SP6) box used in our test environment. The SQL Server was configured to run under the local system account before I got here. In an effort to standardize things, I tried changing the SQL Service account to run under a designated domain user account purpose built for the job. We use this particular account for all of our new-build servers (which are W2K). This domain account is configured to be a "Power User" on the NT 4.0 Server in question.

Soon after changing things over to run under the new account, all the developers complained that they could no longer connect to the server. I could through QA and EM, but none of the developers could.

The developers are using WebLogic and JDBC drivers for the most part. I wasn't aware that the SQL Server service account affected client connectivity. Was I wrong or is there something else at work here?

Thanks,

hmscottDamn...I'm dealing with something similar right now...

The SSSA run things on behalf of the server...

My guess is that they all connect using sa blank (or whatever, connection pooling id and are still connectiong using sql server auth) and now that it's trusted, their connections are wrong...

How do they connect?

Like when you register a server in EM?

I don't think the service account has anything to do with it...

MOO|||Hi Brett,

They're connecting via various user accounts that are application-specific. Unfortunately, I am only beginning to make progress on convincing management to swing to all trusted connections. The developers insist that WebLogic doesn't support the concept of running under a service account.

Anyway, one guy was using sa, others were using different application accounts. I was able to connect successfully using a trusted connection (but then, I'm a Domain Admin, too, so there's no telling for sure.

It's far from an ideal setup; I'm just trying to correct things bit by bit.

Regards,

Hugh|||Hi Brett,
The developers insist that WebLogic doesn't support the concept of running under a service account.

That may be true...we use websphere and we set a connection pooling account that authenticates through sql server...but it's 1 id and many connections

Anyway, one guy was using sa
Hugh

There's always one...

Still no one is using the SSSA to connect...right?|||No, no one is using that account. I'm sure because the pasword is quite complex and other than being documented in a restricted folder for the DBAs, it's not known.

Thanks for your time...

Regards,

hmscott

Changing the SQL Server Name

I have SQL 2000 std installed on a win2000 box with latest svc packs and box
was originally designated with a development name which SQL also inherited.
Now the server box name has been changed but the server still indicates the
old box name.
What are the implications if I leave as is and how difficult is it to change
it?
Can I just update sysserver in the master db through sp_addserver or are
there other steps?
Any registry impaacts? I noticed that some maintenance jobs I had defined
within SQL became orphaned after making changes to the maintenance plan and
had to be re-defined.
thks
Did SQL Server start with an error along the lines of "has beeen tampered
with?"
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
In order to fix the jobs you will have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
> I have SQL 2000 std installed on a win2000 box with latest svc packs and
box
> was originally designated with a development name which SQL also
inherited.
> Now the server box name has been changed but the server still indicates
the
> old box name.
> What are the implications if I leave as is and how difficult is it to
change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan
and
> had to be re-defined.
> thks
>
|||Some info here: http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
>I have SQL 2000 std installed on a win2000 box with latest svc packs and box
> was originally designated with a development name which SQL also inherited.
> Now the server box name has been changed but the server still indicates the
> old box name.
> What are the implications if I leave as is and how difficult is it to change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan and
> had to be re-defined.
> thks
>

Changing the SQL Server Name

I have SQL 2000 std installed on a win2000 box with latest svc packs and box
was originally designated with a development name which SQL also inherited.
Now the server box name has been changed but the server still indicates the
old box name.
What are the implications if I leave as is and how difficult is it to change
it?
Can I just update sysserver in the master db through sp_addserver or are
there other steps?
Any registry impaacts? I noticed that some maintenance jobs I had defined
within SQL became orphaned after making changes to the maintenance plan and
had to be re-defined.
thksDid SQL Server start with an error along the lines of "has beeen tampered
with?"
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
In order to fix the jobs you will have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
> I have SQL 2000 std installed on a win2000 box with latest svc packs and
box
> was originally designated with a development name which SQL also
inherited.
> Now the server box name has been changed but the server still indicates
the
> old box name.
> What are the implications if I leave as is and how difficult is it to
change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan
and
> had to be re-defined.
> thks
>|||Some info here: http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
>I have SQL 2000 std installed on a win2000 box with latest svc packs and bo
x
> was originally designated with a development name which SQL also inherited
.
> Now the server box name has been changed but the server still indicates th
e
> old box name.
> What are the implications if I leave as is and how difficult is it to chan
ge
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan an
d
> had to be re-defined.
> thks
>

Changing the SQL Server Name

I have SQL 2000 std installed on a win2000 box with latest svc packs and box
was originally designated with a development name which SQL also inherited.
Now the server box name has been changed but the server still indicates the
old box name.
What are the implications if I leave as is and how difficult is it to change
it?
Can I just update sysserver in the master db through sp_addserver or are
there other steps?
Any registry impaacts? I noticed that some maintenance jobs I had defined
within SQL became orphaned after making changes to the maintenance plan and
had to be re-defined.
thksDid SQL Server start with an error along the lines of "has beeen tampered
with?"
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
In order to fix the jobs you will have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
> I have SQL 2000 std installed on a win2000 box with latest svc packs and
box
> was originally designated with a development name which SQL also
inherited.
> Now the server box name has been changed but the server still indicates
the
> old box name.
> What are the implications if I leave as is and how difficult is it to
change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan
and
> had to be re-defined.
> thks
>|||Some info here: http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
>I have SQL 2000 std installed on a win2000 box with latest svc packs and box
> was originally designated with a development name which SQL also inherited.
> Now the server box name has been changed but the server still indicates the
> old box name.
> What are the implications if I leave as is and how difficult is it to change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan and
> had to be re-defined.
> thks
>