database collation: databases

Showing posts with label databases. Show all posts

Thursday, March 29, 2012

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

Tuesday, March 27, 2012

Changing The Physical Server

Hi,
I have Production Database on one server which shares the space with
many other databases. Now I am planning to shift the database to
another (separate) physical server.
As long as data and logins are concerned I can transfer it to the
new database server. But I also have many of the critical scheduled
Jobs and DTS packages on the existing server. Is there any way through
which I can get these DTS packages and Jobs on the new server?
regards
Rohit
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Chang...ict250272.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=866311
DTS packages -- Open the package, Menu Package --> Save as... 1. to a new
server, or 2. structured storage file, import the file in the new server
Jobs -- Right click on the job, Generate SQL Script...
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Rohit" wrote:

> Hi,
> I have Production Database on one server which shares the space with
> many other databases. Now I am planning to shift the database to
> another (separate) physical server.
> As long as data and logins are concerned I can transfer it to the
> new database server. But I also have many of the critical scheduled
> Jobs and DTS packages on the existing server. Is there any way through
> which I can get these DTS packages and Jobs on the new server?
> regards
> Rohit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Server-Chang...ict250272.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=866311
>
|||Hi
You may want to look at using:
http://www.sqldts.com/default.aspx?242
Also check out:
http://support.microsoft.com/default...;en-us;Q314546
John
"Rohit" wrote:

> Hi,
> I have Production Database on one server which shares the space with
> many other databases. Now I am planning to shift the database to
> another (separate) physical server.
> As long as data and logins are concerned I can transfer it to the
> new database server. But I also have many of the critical scheduled
> Jobs and DTS packages on the existing server. Is there any way through
> which I can get these DTS packages and Jobs on the new server?
> regards
> Rohit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Server-Chang...ict250272.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=866311
>
sql

Changing The Physical Server

Hi,
I have Production Database on one server which shares the space with
many other databases. Now I am planning to shift the database to
another (separate) physical server.
As long as data and logins are concerned I can transfer it to the
new database server. But I also have many of the critical scheduled
Jobs and DTS packages on the existing server. Is there any way through
which I can get these DTS packages and Jobs on the new server?
regards
Rohit
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Chan...rm.php?p=866311DTS packages -- Open the package, Menu Package --> Save as... 1. to a new
server, or 2. structured storage file, import the file in the new server
Jobs -- Right click on the job, Generate SQL Script...
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Rohit" wrote:

> Hi,
> I have Production Database on one server which shares the space with
> many other databases. Now I am planning to shift the database to
> another (separate) physical server.
> As long as data and logins are concerned I can transfer it to the
> new database server. But I also have many of the critical scheduled
> Jobs and DTS packages on the existing server. Is there any way through
> which I can get these DTS packages and Jobs on the new server?
> regards
> Rohit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Server-Chan...rm.php?p=866311
>|||Hi
You may want to look at using:
http://www.sqldts.com/default.aspx?242
Also check out:
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"Rohit" wrote:

> Hi,
> I have Production Database on one server which shares the space with
> many other databases. Now I am planning to shift the database to
> another (separate) physical server.
> As long as data and logins are concerned I can transfer it to the
> new database server. But I also have many of the critical scheduled
> Jobs and DTS packages on the existing server. Is there any way through
> which I can get these DTS packages and Jobs on the new server?
> regards
> Rohit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Server-Chan...rm.php?p=866311
>

Changing The Physical Server

Hi,
I have Production Database on one server which shares the space with
many other databases. Now I am planning to shift the database to
another (separate) physical server.
As long as data and logins are concerned I can transfer it to the
new database server. But I also have many of the critical scheduled
Jobs and DTS packages on the existing server. Is there any way through
which I can get these DTS packages and Jobs on the new server?
regards
Rohit
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Changing-Physical-ftopict250272.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=866311DTS packages -- Open the package, Menu Package --> Save as... 1. to a new
server, or 2. structured storage file, import the file in the new server
Jobs -- Right click on the job, Generate SQL Script...
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Rohit" wrote:
> Hi,
> I have Production Database on one server which shares the space with
> many other databases. Now I am planning to shift the database to
> another (separate) physical server.
> As long as data and logins are concerned I can transfer it to the
> new database server. But I also have many of the critical scheduled
> Jobs and DTS packages on the existing server. Is there any way through
> which I can get these DTS packages and Jobs on the new server?
> regards
> Rohit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Server-Changing-Physical-ftopict250272.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=866311
>|||Hi
You may want to look at using:
http://www.sqldts.com/default.aspx?242
Also check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"Rohit" wrote:
> Hi,
> I have Production Database on one server which shares the space with
> many other databases. Now I am planning to shift the database to
> another (separate) physical server.
> As long as data and logins are concerned I can transfer it to the
> new database server. But I also have many of the critical scheduled
> Jobs and DTS packages on the existing server. Is there any way through
> which I can get these DTS packages and Jobs on the new server?
> regards
> Rohit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/Server-Changing-Physical-ftopict250272.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=866311
>

Changing the Logical/Physical name of a DB, or log

Sunday, March 25, 2012

Changing the Logical/Physical name of a DB, or log

The system I'm working with has many databases, almost all of which have a
logical name of "DBname_Data" and "DBname_Log" which have physical names if
"DBname_Data.MDF" and "DBname_Log.LDF".
All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
What do I have to do to change this, so it is consistant with all the others?
Thanks,
JayJust thought to add that I know how to do it with a backup and a restore with
move. Hoping for simpler.
"JayKon" wrote:
> The system I'm working with has many databases, almost all of which have a
> logical name of "DBname_Data" and "DBname_Log" which have physical names if
> "DBname_Data.MDF" and "DBname_Log.LDF".
> All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> What do I have to do to change this, so it is consistant with all the others?
> Thanks,
> Jay|||try alter database with the modify file portion:
e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
new_path/os_file_name ' )
BOL does a good job of explaining this so just look under 'alter database'
Robert Towne
"JayKon" wrote:
> Just thought to add that I know how to do it with a backup and a restore with
> move. Hoping for simpler.
> "JayKon" wrote:
> > The system I'm working with has many databases, almost all of which have a
> > logical name of "DBname_Data" and "DBname_Log" which have physical names if
> > "DBname_Data.MDF" and "DBname_Log.LDF".
> >
> > All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> >
> > What do I have to do to change this, so it is consistant with all the others?
> >
> > Thanks,
> > Jay|||Yes, it does.
Thank you.
"sql411@.nospam.com" wrote:
> try alter database with the modify file portion:
> e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
> new_path/os_file_name ' )
> BOL does a good job of explaining this so just look under 'alter database'
> Robert Towne
>
>
> "JayKon" wrote:
> > Just thought to add that I know how to do it with a backup and a restore with
> > move. Hoping for simpler.
> >
> > "JayKon" wrote:
> >
> > > The system I'm working with has many databases, almost all of which have a
> > > logical name of "DBname_Data" and "DBname_Log" which have physical names if
> > > "DBname_Data.MDF" and "DBname_Log.LDF".
> > >
> > > All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> > >
> > > What do I have to do to change this, so it is consistant with all the others?
> > >
> > > Thanks,
> > > Jay

changing the default drive and folder for DBs

Hi;

I feel like an idiot to be asking this,
but how do I change the default drive and folder
in which new databases are created?

I've been looking all over Server Mgmt Studio,
and I can't find anything. I'm thinking that someone
can tell me this off the top of their heads,
or slip me a link to a web page.

Thanks,

Never mind everyone - I found my own solution.

THANKS anyway.

|||

Please tell us how you fix that

Changing the default database

I have a large number of databases on my localhost. Whenever I open up
Management Studio, there is one database that is selected by default
(under Available Databases). But this is not the database that I use
regularly. Everytime I run a query, I have to make sure that the
database is changed.
Is there a way to change the default database selected in the
Available Databases drop down?
ThanksHow do you open the query window? The exact steps for opening the query window determines what steps
you need to take to make this your default database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"spdude" <sandeshmeda@.gmail.com> wrote in message
news:1178132157.841268.133800@.y80g2000hsf.googlegroups.com...
>I have a large number of databases on my localhost. Whenever I open up
> Management Studio, there is one database that is selected by default
> (under Available Databases). But this is not the database that I use
> regularly. Everytime I run a query, I have to make sure that the
> database is changed.
> Is there a way to change the default database selected in the
> Available Databases drop down?
> Thanks
>|||On May 2, 2:55 pm, spdude <sandeshm...@.gmail.com> wrote:
> I have a large number of databases on my localhost. Whenever I open up
> Management Studio, there is one database that is selected by default
> (under Available Databases). But this is not the database that I use
> regularly. Everytime I run a query, I have to make sure that the
> database is changed.
> Is there a way to change the default database selected in the
> Available Databases drop down?
> Thanks
Have you tried setting the default database for the login you use?|||Thanks for the replies.
How do I set the default database for the login?
I open the query window by clicking on the "New Query" button. I like
this method because its just one click away.
On May 2, 2:49 pm, tcappelle...@.gmail.com wrote:
> On May 2, 2:55 pm,spdude<sandeshm...@.gmail.com> wrote:
> > I have a large number of databases on my localhost. Whenever I open up
> > Management Studio, there is one database that is selected by default
> > (under Available Databases). But this is not the database that I use
> > regularly. Everytime I run a query, I have to make sure that the
> > database is changed.
> > Is there a way to change the default database selected in the
> > Available Databases drop down?
> > Thanks
> Have you tried setting the default database for the login you use?|||Look at sp_defaultdb in Books Online. You can also use:
ALTER LOGIN sa WITH DEFAULT_DATABASE = [master];
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spdude" <sandeshmeda@.gmail.com> wrote in message
news:1178286625.150157.211740@.h2g2000hsg.googlegroups.com...
> Thanks for the replies.
> How do I set the default database for the login?
> I open the query window by clicking on the "New Query" button. I like
> this method because its just one click away.
> On May 2, 2:49 pm, tcappelle...@.gmail.com wrote:
>> On May 2, 2:55 pm,spdude<sandeshm...@.gmail.com> wrote:
>> > I have a large number of databases on my localhost. Whenever I open up
>> > Management Studio, there is one database that is selected by default
>> > (under Available Databases). But this is not the database that I use
>> > regularly. Everytime I run a query, I have to make sure that the
>> > database is changed.
>> > Is there a way to change the default database selected in the
>> > Available Databases drop down?
>> > Thanks
>> Have you tried setting the default database for the login you use?
>

Changing the default database

I have a large number of databases on my localhost. Whenever I open up
Management Studio, there is one database that is selected by default
(under Available Databases). But this is not the database that I use
regularly. Everytime I run a query, I have to make sure that the
database is changed.
Is there a way to change the default database selected in the
Available Databases drop down?
ThanksHow do you open the query window? The exact steps for opening the query wind
ow determines what steps
you need to take to make this your default database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"spdude" <sandeshmeda@.gmail.com> wrote in message
news:1178132157.841268.133800@.y80g2000hsf.googlegroups.com...
>I have a large number of databases on my localhost. Whenever I open up
> Management Studio, there is one database that is selected by default
> (under Available Databases). But this is not the database that I use
> regularly. Everytime I run a query, I have to make sure that the
> database is changed.
> Is there a way to change the default database selected in the
> Available Databases drop down?
> Thanks
>|||On May 2, 2:55 pm, spdude <sandeshm...@.gmail.com> wrote:
> I have a large number of databases on my localhost. Whenever I open up
> Management Studio, there is one database that is selected by default
> (under Available Databases). But this is not the database that I use
> regularly. Everytime I run a query, I have to make sure that the
> database is changed.
> Is there a way to change the default database selected in the
> Available Databases drop down?
> Thanks
Have you tried setting the default database for the login you use?|||Thanks for the replies.
How do I set the default database for the login?
I open the query window by clicking on the "New Query" button. I like
this method because its just one click away.
On May 2, 2:49 pm, tcappelle...@.gmail.com wrote:
> On May 2, 2:55 pm,spdude<sandeshm...@.gmail.com> wrote:
>
>
>
> Have you tried setting the default database for the login you use?|||Look at sp_defaultdb in Books Online. You can also use:
ALTER LOGIN sa WITH DEFAULT_DATABASE = [master];
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spdude" <sandeshmeda@.gmail.com> wrote in message
news:1178286625.150157.211740@.h2g2000hsg.googlegroups.com...
> Thanks for the replies.
> How do I set the default database for the login?
> I open the query window by clicking on the "New Query" button. I like
> this method because its just one click away.
> On May 2, 2:49 pm, tcappelle...@.gmail.com wrote:
>sql

Thursday, March 22, 2012

Changing the ConnectionString property for a File Connection Manager

I have a package that I plan to run against about 700 databases to look for anomalies. I have several package variables in place that are passed in at runtime. One of them will hold the path and filename of the error log for the current database in process. I want each database to generate it's own error log for documentation and research purposes. However, when I run the package, it continues to use the path and filename that I entered when I created the File Connection Manager. I am trying to update that value in a Script Task by using the ConnectionManager class and setting the value for the "ConnectionString" property. This method is working for the OLEDB Connection Manager (which tells the package which Access database to process), but not for my File Connection Manager. Please help!

Don't try and use a script task to do this. Use expressions: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

-Jamie

|||Thanks, you've done it again.

changing the compatibility level

I am migrating from sql server 2000 to sql server 2005.

When i do back up and Restore of the databases the compatibility level remains 80 (2000).

my concern is

1) should i change the compatibility level to 90 (2005)

2) Am i missing out any thing if i dont change the compatibility level

3) What are the issues that can come up if changed to 90 (2005)

Please reply .I am in a fix

Thanks in advance

If you still have the original SQL 2000 server/database, don't kill it yet

Run the SQL 2005 Upgrade Advisor on it and it'll give you a list of "errors", and "warnings" if you upgrade to 2005 (Errors are must-fix)

You can decide for yourself if # of errors is huge enough to keep the new DB in 80 mode; if not I suggest 90 mode (we did that, don't require restart either)

But honestly, I can only believe MS' words on that SQL2005 IS BETTER, as I don't have any benchmark comparison

Top 30 features of SQL 2005

http://www.microsoft.com/sql/prodinfo/features/top30features.mspx

sql

Monday, March 19, 2012

Changing SQL server 2000's CPU Utilization for each databases

Hi ,
Know that SQL server 2000 will be able to utilise mutiple CPUs if there are
but is it possible for me to change the settings such that each databases
will be serviced by certain CPUs ?
could anyone show me how ? and will this improve performance instead of
letting SQL server manages its own CPUS utilization
tks & rdgsThere is no way to effectively do that unless you place each db in it's own
instance and affinitize the cpus to each instance. It's almost never worth
doing something like that.
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:1481E81C-D33C-4C20-9448-3EF3D1216285@.microsoft.com...
> Hi ,
> Know that SQL server 2000 will be able to utilise mutiple CPUs if there
> are
> but is it possible for me to change the settings such that each databases
> will be serviced by certain CPUs ?
> could anyone show me how ? and will this improve performance instead of
> letting SQL server manages its own CPUS utilization
> tks & rdgs|||Hi Andrew ,
assuming that i have multiple instances(e.g instance A , instance B) and i
have 5 CPUS. how do i get instance A to be serviced by 3 CPUS and instance B
serviced by 2 CPUS ?
actually , from my vendor , i knew that they have changed the cpu
utilization but no difference performance-wise , i am just curious how they
did it
rdgs
"Andrew J. Kelly" wrote:

> There is no way to effectively do that unless you place each db in it's ow
n
> instance and affinitize the cpus to each instance. It's almost never wort
h
> doing something like that.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:1481E81C-D33C-4C20-9448-3EF3D1216285@.microsoft.com...
>
>|||maxzsim wrote:
> Hi Andrew ,
> assuming that i have multiple instances(e.g instance A , instance B)
> and i have 5 CPUS. how do i get instance A to be serviced by 3 CPUS
> and instance B serviced by 2 CPUS ?
> actually , from my vendor , i knew that they have changed the cpu
> utilization but no difference performance-wise , i am just curious
> how they did it
>
You can just check off the processors you want to use on the Processor
tab in SQL EM. Or you can use sp_configure.
David Gugick
Imceda Software
www.imceda.com|||tks , i found it at SQL EM
"David Gugick" wrote:

> maxzsim wrote:
> You can just check off the processors you want to use on the Processor
> tab in SQL EM. Or you can use sp_configure.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Thursday, March 29, 2012

Changing the text of the code of multiple stored procedure

Changing the text of the code of multiple stored procedure

Changing the text of the code of multiple stored procedure

Tuesday, March 27, 2012

Changing The Physical Server

Changing The Physical Server

Changing The Physical Server

Changing the Logical/Physical name of a DB, or log

Sunday, March 25, 2012

Changing the Logical/Physical name of a DB, or log

Changing the Logical/Physical name of a DB, or log

changing the default drive and folder for DBs

Changing the default database

Changing the default database

Changing the default database

Thursday, March 22, 2012

Changing the ConnectionString property for a File Connection Manager

changing the compatibility level

Monday, March 19, 2012

Changing SQL server 2000's CPU Utilization for each databases

database collation

Blog Archive

About Me