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 server used by a report

hopefully have better luck with this.

I have two servers running the same database for two locations, I have a site setup to report for one of them and I want to make it report for both based on what location a person is in.

I have it working when there are no parameters, however as soon as you enter parameters into the mix it breaks.

here is what I use to do it when no parameters are required.

CrystalReportSource1.ReportDocument.DataSourceConnections[0].SetConnection(myConnectionInfo.ServerName, myConnectionInfo.DatabaseName, false);

CrystalReportSource1.ReportDocument.DataSourceConnections[0].SetLogon(myConnectionInfo.UserID, myConnectionInfo.Password);

I tried to put this in various locations, before the parameters are read, after reading but before setting, after everything.
Even before the report is loaded!

all gave me an error on the report.

I also tried to grab the parameter definitions, then set these and then copy the definitions back and that failed, it was read only.

Any ideas?

ThanksNM - threw in a verifydatabase on the report document and all is well.

Changing SQL Server Service Password

I need to change the password off the SQL Server service on about 70 servers,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via SQLMonster.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via SQLMonster.com" wrote:
>> Bruce
>> The main reason that I've come across for changing the service account
>> password only via SQLEM is to maintain connections with any Full Text
>> Indexes that you may be using.
>> Changing the password in any other way is guaranteed to require an FTI
>> catalog rebuild (or worse)
>> As for the frequency of password change, I would question why you feel
>> the
>> need to change these on the service accounts. IMHO these accounts should
>> never see the light of day outside of their designated purpose (ie don't
>> log in using these accounts), so should be relatively secure in the long
>> term.
>> Also, if you change these, you are likely to open a whole can of worms
>> regarding access to other resources (file shares, other SQL servers,
>> clustering, replication etc)
>> Yes, it does requrie a restart of services.
>> I have, however, implemented 'monthly' password changes on the sa
>> account,
>> as this is far more visible. It's relatively straightforward to automate
>> using a DTS package.
>> Doing this also discourages developers from hard coding apps to use the
>> sa
>> account ;-))
>> Hope this helps
>> Andy H

Changing SQL Server Service Password

I need to change the password off the SQL Server service on about 70 servers
,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:

> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
>sql

Changing SQL Server Service Password

I need to change the password off the SQL Server service on about 70 servers,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
Bruce
Bruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H
|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:

> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>
|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:

Changing SQL authentication method

Have a central SQL box (SQL server 2000 SP3a) which serves
requests from web servers. It is configured to use Windows
authentication. However I have a requirement for another
web server to use SQL authentication. Would I be able to
change the SQl Server's configuration to SQL and Windows
without breaking anything?
Many thanksOn Wed, 11 Aug 2004 01:45:08 -0700, Gary wrote:

>Have a central SQL box (SQL server 2000 SP3a) which serves
>requests from web servers. It is configured to use Windows
>authentication. However I have a requirement for another
>web server to use SQL authentication. Would I be able to
>change the SQl Server's configuration to SQL and Windows
>without breaking anything?
>Many thanks
Hi Gary,
Yes, this is possible. You can make this change through Enterprise
Manager: right-click the server, select Properties and on the Security
tab, set the required authentication mode.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi,
There will not be any impact in changing the authentication. Only thing is,
you need to stop and start the MSSQL server service
to make the changes (Mixed authentication) active.
Thanks
Hari
MCDBA
"Gary" <anonymous@.discussions.microsoft.com> wrote in message
news:3be701c47f7f$817996e0$a301280a@.phx.gbl...
> Have a central SQL box (SQL server 2000 SP3a) which serves
> requests from web servers. It is configured to use Windows
> authentication. However I have a requirement for another
> web server to use SQL authentication. Would I be able to
> change the SQl Server's configuration to SQL and Windows
> without breaking anything?
> Many thanks|||Gary,
Make sure the sa password is strong before doing this, as people will be
able to attempt to login with it now, and possibly even attempt a brute
force crack. Apart from that, should be OK.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Gary wrote:
> Have a central SQL box (SQL server 2000 SP3a) which serves
> requests from web servers. It is configured to use Windows
> authentication. However I have a requirement for another
> web server to use SQL authentication. Would I be able to
> change the SQl Server's configuration to SQL and Windows
> without breaking anything?
> Many thanks|||Hi Mark
Again thanks for the advice. The sa password has already
been set during service pack 3a installation and is strong.
Gary

Changing SQL authentication method

Have a central SQL box (SQL server 2000 SP3a) which serves
requests from web servers. It is configured to use Windows
authentication. However I have a requirement for another
web server to use SQL authentication. Would I be able to
change the SQl Server's configuration to SQL and Windows
without breaking anything?
Many thanksOn Wed, 11 Aug 2004 01:45:08 -0700, Gary wrote:
>Have a central SQL box (SQL server 2000 SP3a) which serves
>requests from web servers. It is configured to use Windows
>authentication. However I have a requirement for another
>web server to use SQL authentication. Would I be able to
>change the SQl Server's configuration to SQL and Windows
>without breaking anything?
>Many thanks
Hi Gary,
Yes, this is possible. You can make this change through Enterprise
Manager: right-click the server, select Properties and on the Security
tab, set the required authentication mode.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi,
There will not be any impact in changing the authentication. Only thing is,
you need to stop and start the MSSQL server service
to make the changes (Mixed authentication) active.
Thanks
Hari
MCDBA
"Gary" <anonymous@.discussions.microsoft.com> wrote in message
news:3be701c47f7f$817996e0$a301280a@.phx.gbl...
> Have a central SQL box (SQL server 2000 SP3a) which serves
> requests from web servers. It is configured to use Windows
> authentication. However I have a requirement for another
> web server to use SQL authentication. Would I be able to
> change the SQl Server's configuration to SQL and Windows
> without breaking anything?
> Many thanks|||Hi chaps
Thanks for the advice - thought as much, I'll give it a
whirl.
Gary|||Gary,
Make sure the sa password is strong before doing this, as people will be
able to attempt to login with it now, and possibly even attempt a brute
force crack. Apart from that, should be OK.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Gary wrote:
> Have a central SQL box (SQL server 2000 SP3a) which serves
> requests from web servers. It is configured to use Windows
> authentication. However I have a requirement for another
> web server to use SQL authentication. Would I be able to
> change the SQl Server's configuration to SQL and Windows
> without breaking anything?
> Many thanks|||Hi Mark
Again thanks for the advice. The sa password has already
been set during service pack 3a installation and is strong.
Gary

Changing SQL authentication method

Have a central SQL box (SQL server 2000 SP3a) which serves
requests from web servers. It is configured to use Windows
authentication. However I have a requirement for another
web server to use SQL authentication. Would I be able to
change the SQl Server's configuration to SQL and Windows
without breaking anything?
Many thanks
On Wed, 11 Aug 2004 01:45:08 -0700, Gary wrote:

>Have a central SQL box (SQL server 2000 SP3a) which serves
>requests from web servers. It is configured to use Windows
>authentication. However I have a requirement for another
>web server to use SQL authentication. Would I be able to
>change the SQl Server's configuration to SQL and Windows
>without breaking anything?
>Many thanks
Hi Gary,
Yes, this is possible. You can make this change through Enterprise
Manager: right-click the server, select Properties and on the Security
tab, set the required authentication mode.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi,
There will not be any impact in changing the authentication. Only thing is,
you need to stop and start the MSSQL server service
to make the changes (Mixed authentication) active.
Thanks
Hari
MCDBA
"Gary" <anonymous@.discussions.microsoft.com> wrote in message
news:3be701c47f7f$817996e0$a301280a@.phx.gbl...
> Have a central SQL box (SQL server 2000 SP3a) which serves
> requests from web servers. It is configured to use Windows
> authentication. However I have a requirement for another
> web server to use SQL authentication. Would I be able to
> change the SQl Server's configuration to SQL and Windows
> without breaking anything?
> Many thanks
|||Gary,
Make sure the sa password is strong before doing this, as people will be
able to attempt to login with it now, and possibly even attempt a brute
force crack. Apart from that, should be OK.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Gary wrote:
> Have a central SQL box (SQL server 2000 SP3a) which serves
> requests from web servers. It is configured to use Windows
> authentication. However I have a requirement for another
> web server to use SQL authentication. Would I be able to
> change the SQl Server's configuration to SQL and Windows
> without breaking anything?
> Many thanks
|||Hi Mark
Again thanks for the advice. The sa password has already
been set during service pack 3a installation and is strong.
Gary

changing servers dynamically via TSQL

I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!
Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.

changing servers dynamically via TSQL

I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.