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 ownership of a object

Hi all,

I have a problem while executing a stored procedure. I have created a database called "cpd" and created some stored procedures. for all my stored procedure the owner is "CPDUSER". when ever i am executing any stored procedures i have to write the user name first else it is not working.

let's say i have a stored procedure called "cp_checklogin". it takes 2 parameters. to execute this i have to write

exec cpduser.cp_checklogin 'admin@.jk.com', 'admin'

but i don't want to write the user name there. and if without username "CPDUSER" i am trying to execute the stored procedure it is throwing me the error that "the stored procedure cp_checklogin is not exist in the database". can anybody suggest me. it's very urgent.

Thanks in advance

Krishna

The cp_checklogin stored procedure has been created in the cpduser schema. The user making the call likely has 'dbo' as their default schema.

To fix this, you can either recreate the stored procedure in the dbo schema or you can change the default schema for the user making the call (for help on this, see http://msdn2.microsoft.com/en-us/library/ms190387.aspx)

|||Another option would be if you use SQL 2000 (you did not mention that) to change the ownership of existing objects using sp_changeobjectowner.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Changing the IP address of a SQL 2000 SP3a server

What is the proper procedure for changing the IP address of a SQL 2000
Server?
Are there any gotchas, or is it as straight forward as just changing the
IP address?
Hi,
Rob Gordon
No procedures to follow to change the IP address. You can do straight away.
Nothing will happen.
"Rob Gordon" wrote:

> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?
>
|||Only impact will be if any clients/programs, etc. find it via the IP address
instead of the server name.. or is that changing, also.
Regards,
Hank Arnold
"Rob Gordon" <Robert.Gordon@.nospam.yosemitetech.com> wrote in message
news:ufFgHjCBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?
|||You will need to stop/start the service after the address change. SQL binds
to whatever IP addresses exist at service startup time.
If you are running a cluster, that is an entirely different proposition.
You should repost the question in the .clustering newsgroup.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rob Gordon" <Robert.Gordon@.nospam.yosemitetech.com> wrote in message
news:ufFgHjCBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?
|||On Wed, 26 Jan 2005 20:26:03 -0800, Rob Gordon
<Robert.Gordon@.nospam.yosemitetech.com> wrote:

>What is the proper procedure for changing the IP address of a SQL 2000
>Server?
Change the IP.

>Are there any gotchas, or is it as straight forward as just changing the
>IP address?
Remember to update any DNS records you may need to, and if a client
connects via the IP address, it will need changing as well.
Jeff

Changing the IP address of a SQL 2000 SP3a server

What is the proper procedure for changing the IP address of a SQL 2000
Server?
Are there any gotchas, or is it as straight forward as just changing the
IP address?Hi,
Rob Gordon
No procedures to follow to change the IP address. You can do straight away.
Nothing will happen.
"Rob Gordon" wrote:

> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?
>|||Only impact will be if any clients/programs, etc. find it via the IP address
instead of the server name.. or is that changing, also.
--
Regards,
Hank Arnold
"Rob Gordon" <Robert.Gordon@.nospam.yosemitetech.com> wrote in message
news:ufFgHjCBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?|||You will need to stop/start the service after the address change. SQL binds
to whatever IP addresses exist at service startup time.
If you are running a cluster, that is an entirely different proposition.
You should repost the question in the .clustering newsgroup.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rob Gordon" <Robert.Gordon@.nospam.yosemitetech.com> wrote in message
news:ufFgHjCBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?|||On Wed, 26 Jan 2005 20:26:03 -0800, Rob Gordon
<Robert.Gordon@.nospam.yosemitetech.com> wrote:

>What is the proper procedure for changing the IP address of a SQL 2000
>Server?
Change the IP.

>Are there any gotchas, or is it as straight forward as just changing the
>IP address?
Remember to update any DNS records you may need to, and if a client
connects via the IP address, it will need changing as well.
Jeff

Changing the IP address of a SQL 2000 SP3a server

What is the proper procedure for changing the IP address of a SQL 2000
Server?
Are there any gotchas, or is it as straight forward as just changing the
IP address?Hi,
Rob Gordon
No procedures to follow to change the IP address. You can do straight away.
Nothing will happen.
"Rob Gordon" wrote:
> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?
>|||Only impact will be if any clients/programs, etc. find it via the IP address
instead of the server name.. or is that changing, also.
--
Regards,
Hank Arnold
"Rob Gordon" <Robert.Gordon@.nospam.yosemitetech.com> wrote in message
news:ufFgHjCBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?|||You will need to stop/start the service after the address change. SQL binds
to whatever IP addresses exist at service startup time.
If you are running a cluster, that is an entirely different proposition.
You should repost the question in the .clustering newsgroup.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rob Gordon" <Robert.Gordon@.nospam.yosemitetech.com> wrote in message
news:ufFgHjCBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> What is the proper procedure for changing the IP address of a SQL 2000
> Server?
> Are there any gotchas, or is it as straight forward as just changing the
> IP address?|||On Wed, 26 Jan 2005 20:26:03 -0800, Rob Gordon
<Robert.Gordon@.nospam.yosemitetech.com> wrote:
>What is the proper procedure for changing the IP address of a SQL 2000
>Server?
Change the IP. :)
>Are there any gotchas, or is it as straight forward as just changing the
>IP address?
Remember to update any DNS records you may need to, and if a client
connects via the IP address, it will need changing as well.
Jeff

Changing the default stored procedure template (In MS-SQLSMStudio)

Well, the topic says it all, really.

I edit my stored procedures in Microsoft SQL Management Studio 9.00.2047, but whenever I click 'New Stored Procedure..' it coughs up some default and useless thing.

So I started looking how to change this but I can't find the -right- place. I have found a couple of files on my disk called 'Create Stored Procedure (New Menu).sql' - however altering these files does nothing for the default SP.

The template explorer happily lets me EDIT the same file, sadly the edits don't actually have any effect either.

Anyone?

i can edit the Template and add a new template in my scenario.... Just go to the template Press Right Button Select Edit do the modification as u want press the save button and close it...

Madhu

|||

Sadly, that doesn't work.

I can edit the template from the template explorer, sure. But saves don't affect the template my system actually uses. Useless.

I found my own solution though, although not as elegant. I edited the file:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql\Stored Procedure\Create Stored Procedure (New Menu).sql

And Presto.

sql

Changing the current database inside a stored procedure

Hi all,

I need to change the current database from within a stored procedure or something that can be called by a stored procedure. I know the use statement is ineffective in these circumstances. Just need to know if there's a method out there I can employ.

Any help would be really appreciated.

Richyou don't need to change DBs while in a stored procedure just reference the object(s) using the three part object name: <DATABASE>.<OWNER>.<OBJECT NAME>.|||Thanks Paul...one of those times when you miss the blind obvious.

Cheers

Rich

Changing the command buffer size?

Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!Originally posted by rmcat
Is it possible to change the command buffer size??

I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...

Thanks!!!

Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.|||Originally posted by kbk
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.

When I run the stored proc with exec output turned on, I get the following error...
"Query hints exceed maximum command buffer size of 1023 bytes (3952 bytes input)."|||What do you put for the "query hints"? INDEX=xxxxx ?|||You're playing with dynamic sql...right?|||Originally posted by rdjabarov
What do you put for the "query hints"? INDEX=xxxxx ?

Ok, I don't exactly know what "query hints" in the error message is referring to, but the command I'm trying to execute is...

bcp "select...." queryout <filename> <sql user id> <sql password> <server> <file format>

The select statement is 3953 characters long. This commmand has worked for other (shorter) selects, so I have to assume the problem here is the length of my query. Unfortunately, I can't make it any shorter, so I'm hoping to make the command buffer bigger...|||Originally posted by Brett Kaiser
You're playing with dynamic sql...right?

Yes, it's dynamic. Sections of the where clause are dependant on data passed in from a web app that calls the stored proc.|||I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!|||Convert your SELECT into a stored procedure, and you're home free!|||Originally posted by rdjabarov
Convert your SELECT into a stored procedure, and you're home free!

Success!!!!

You guys rock!

Thank you!!!

changing stored procedure ownership

Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thanks
See sp_changeobjectowner in SQL Server Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:59E67253-2682-4D1C-9802-630B5F335BFC@.microsoft.com...
Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thanks

changing stored procedure ownership

Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
ThanksSee sp_changeobjectowner in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:59E67253-2682-4D1C-9802-630B5F335BFC@.microsoft.com...
Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thankssql

Changing stored procedure Freezes Crystal

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.
Anyone?soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.

The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.

Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.

It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.

And of course the version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.

>
The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.
>
Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.
>
It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.
>
And of course the version of SQL Server you are using.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

It ended up being a crystal problem I think. I checked the box to
perform the query Asynchronously. Then following is what that does.

" Retrieving data from the database server can be divided into two
parts: executing the SQL statement and fetching the rowset from the
database server. Selecting this option allows Crystal Reports to
execute the SQL statement asynchronously, which means that instead of
waiting for the SQL statement to finish executing, the program checks
intermittently to see if the user wants to cancel the process. This
option is not selected by default ".

I am not sure why this would make a difference, or even if it was my
problem, but I am going to try to re-create the problem to see if this
actually fixed it.|||soulkitchen wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.

The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.

Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.

It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.

And of course the version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>
It ended up being a crystal problem I think. I checked the box to
perform the query Asynchronously. Then following is what that does.
>
" Retrieving data from the database server can be divided into two
parts: executing the SQL statement and fetching the rowset from the
database server. Selecting this option allows Crystal Reports to
execute the SQL statement asynchronously, which means that instead of
waiting for the SQL statement to finish executing, the program checks
intermittently to see if the user wants to cancel the process. This
option is not selected by default ".
>
I am not sure why this would make a difference, or even if it was my
problem, but I am going to try to re-create the problem to see if this
actually fixed it.

That did not seem to fix it. It am pretty sure it is a Crystal problem
though. The SP runs fine in query analyzer.|||soulkitchen wrote:

Quote:

Originally Posted by

soulkitchen wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

soulkitchen (esoulkitchen@.gmail.com) writes:
After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.
>
The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.
>
Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.
>
It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.
>
And of course the version of SQL Server you are using.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

It ended up being a crystal problem I think. I checked the box to
perform the query Asynchronously. Then following is what that does.

" Retrieving data from the database server can be divided into two
parts: executing the SQL statement and fetching the rowset from the
database server. Selecting this option allows Crystal Reports to
execute the SQL statement asynchronously, which means that instead of
waiting for the SQL statement to finish executing, the program checks
intermittently to see if the user wants to cancel the process. This
option is not selected by default ".

I am not sure why this would make a difference, or even if it was my
problem, but I am going to try to re-create the problem to see if this
actually fixed it.

>
That did not seem to fix it. It am pretty sure it is a Crystal problem
though. The SP runs fine in query analyzer

I hope someone can answer this question... The problem above was
happening, and I did not do anything to fix it, but the next day the
problem went away. I can't imagine what could have happened over night
to fix my problem. I do transaction log backups on the hour, and a full
database backup once a week, but no last night.|||soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can answer this question... The problem above was
happening, and I did not do anything to fix it, but the next day the
problem went away. I can't imagine what could have happened over night
to fix my problem. I do transaction log backups on the hour, and a full
database backup once a week, but no last night.

To answer what happened in an office building far far away is kind of
difficult, but I will have to guess that you restarted something.

A possibility is also that there was a blocking issue. With the situation
gone all we can do is guess.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Changing Permissions of a Stored Procedure

Can I programmatically change/set the permissions of a stored procedure, and
if so how would I do this?
I am creating several and am fed up having to go into EM to change
permissions.
Thanks
Keith
DENY CREATE PROCEDURE TO Username
or
You can make him,/her to be member of some fixed database roles (for more
details please refer to BOL)
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>
|||Hi,
Use the GRANT Stateemnt from Query ANalyzer.
GRANT EXEC on proc_name to <user_name/role_name>
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>

Changing Permissions of a Stored Procedure

Can I programmatically change/set the permissions of a stored procedure, and
if so how would I do this?
I am creating several and am fed up having to go into EM to change
permissions.
ThanksKeith
DENY CREATE PROCEDURE TO Username
or
You can make him,/her to be member of some fixed database roles (for more
details please refer to BOL)
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>|||Hi,
Use the GRANT Stateemnt from Query ANalyzer.
GRANT EXEC on proc_name to <user_name/role_name>
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>

Changing Permissions of a Stored Procedure

Can I programmatically change/set the permissions of a stored procedure, and
if so how would I do this?
I am creating several and am fed up having to go into EM to change
permissions.
ThanksKeith
DENY CREATE PROCEDURE TO Username
or
You can make him,/her to be member of some fixed database roles (for more
details please refer to BOL)
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>|||Hi,
Use the GRANT Stateemnt from Query ANalyzer.
GRANT EXEC on proc_name to <user_name/role_name>
--
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>