database collation: code

Showing posts with label code. 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

Sunday, March 25, 2012

Changing the default database files path fails

i have the following code segment which works fine as is:

try

{

ServerConnection connection = new ServerConnection("localhost");

Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))

{

Database database = new Database(server, "SMO_TEST");

//FileGroup fileGroup = new FileGroup(database, "PRIMARY");

//database.FileGroups.Add(fileGroup);

//DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");

//dataFile.FileName = "D:\\SMO_TEST_Data.mdf";

//fileGroup.Files.Add(dataFile);

//LogFile logFile = new LogFile(database, "SMO_TEST_Log");

//logFile.FileName = "D:\\SMO_TEST_Log.ldf";

//database.LogFiles.Add(logFile);

database.Create();

}

catch (FailedOperationException ex)

{

Console.WriteLine(ex.Message);

}

when i uncomment the lines commented above it fails.

Any help?

Fails with which error ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

hi Jenz

thanks for your reply. the code posted earlier worked smoothly just after a computer restart.the error message was either

"Create failed for Database 'SMO_TEST'." or "Drop failed for Database 'SMO_TEST'." with whatever mistake i made, which provided no help in correcting the problem.

however another issue arised when i tried to use a transaction using either method connection.BeginTransaction(); or server.ConnectionContext.BeginTransaction();

(1) how to correct this problem? and

(2) what kind of transaction occures here? is it a distributed transaction?

also i would like to ask

(3)if there is any way to diable full text indexing other than using

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");?(see the code below) .

sql server management studio creates the database with full text indexing disabled by default. but when using smo it is enabled by default and the documentation claims the opposite.

(4) so where does smo take its default values from?

string sqlServerInstance = "localhost";

string databaseName = "SMO_TEST";

string dataFileLogicalName = databaseName + "_Data";

string logFileLogicalName = databaseName + "_Log";

string dataFilePhysicalName = dataFileLogicalName + ".mdf";

string logFilePhysicalName = logFileLogicalName + ".ldf";

string dataFilePath = "D:\\TEST_DIR\\";

string logFilePath = "D:\\TEST_DIR\\";

if (!Directory.Exists(dataFilePath))

{

Directory.CreateDirectory(dataFilePath);

//throw new DirectoryNotFoundException("The data file directory " + dataFilePath + " does not exists.");

}

if (!Directory.Exists(logFilePath))

{

Directory.CreateDirectory(logFilePath);

//throw new DirectoryNotFoundException("The log file directory " + logFilePath + " does not exists.");

}

string defaultFileGroupName = "PRIMARY";

ServerConnection connection;

connection = new ServerConnection(sqlServerInstance);

Server server = new Server(connection);

try

{

//connection.BeginTransaction();

server.ConnectionContext.BeginTransaction();

if (server.Databases.Contains(databaseName))

{

server.Databases[databaseName].Drop();

server.Alter();

}

Database database = new Database(server, databaseName);

FileGroup fileGroup = new FileGroup(database, defaultFileGroupName);

database.FileGroups.Add(fileGroup);

DataFile dataFile = new DataFile(fileGroup, dataFileLogicalName);

dataFile.FileName = dataFilePath + dataFilePhysicalName;

dataFile.GrowthType = FileGrowthType.KB;

dataFile.Growth = 1024d;

fileGroup.Files.Add(dataFile);

LogFile logFile = new LogFile(database, logFileLogicalName);

logFile.FileName = logFilePath + logFilePhysicalName;

logFile.GrowthType = FileGrowthType.Percent;

logFile.MaxSize = 2147483648d;

database.LogFiles.Add(logFile);

database.Create();

// disable full text indexing

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");

//connection.CommitTransaction();

server.ConnectionContext.CommitTransaction();

}

catch (FailedOperationException ex)

{

//connection.RollBackTransaction();

server.ConnectionContext.RollBackTransaction();

Console.WriteLine(ex.Message);

}

finally

{

if (connection != null)

{

if (connection.IsOpen)

{

connection.Disconnect();

}

connection = null;

}

thanks again

|||1) Some commands cannot be used within a transaction, but the problem in your case is that the database seems to be in use during the dropping action, see the blog post on my site for more information about that: 2) local one 3) DId not see anything for that, SMO tell that this functionality is deprecated in further versions.
 
4) Which values do you mean ? Some are coded in the SMO library, some are taken from the db system.
 Jens K. Suessmeyer <A href="http://www.sqlserver2005.de">http://www.sqlserver2005.de</A> |||

Waleed,

You need to add the data and log files before you can assign the filename properties. Try the code here (I've adjusted your code):

try
{
ServerConnection connection = new ServerConnection("localhost");
Server server = new Server(connection);

if (!server.Databases.Contains("SMO_TEST"))
{

Database database = new Database(server, "SMO_TEST");

FileGroup fileGroup = new FileGroup(database, "PRIMARY");
database.FileGroups.Add(fileGroup);
DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data");
fileGroup.Files.Add(dataFile);
dataFile.FileName = "D:\\SMO_TEST_Data.mdf";
LogFile logFile = new LogFile(database, "SMO_TEST_Log");
database.LogFiles.Add(logFile);
logFile.FileName = "D:\\SMO_TEST_Log.ldf";
database.Create();
}
}
catch (FailedOperationException ex)
{
Console.WriteLine(ex.Message);
}

|||

dear MVPs,

I tried to loop through the inner exception of the failedOperationException and it turned out that the problem is that Database.Create(), Database.Drop() and

connection.ExecuteNonQuery(databaseName + ".dbo.sp_fulltext_database @.action = 'disable'");

cannot be contained in a transaction

this answers my question about transactions

thank you very much

Sunday, March 11, 2012

changing schema owner through SMO

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Changing Scale Minimum value on a Graph

How can we change in code the Scale Minimum value via code, depending
on the data in a dataset?
What I would like to beable to do is find the min. value in the dataset
and then set Scale Minimum valueBryan,
I had contacted Dundas support via email on this subject on 3/29 and they
said that it was not possible via the RS version and noted they don't provide
support for RS and directed me to Microsoft's forum websites.
"Bryan Avery" wrote:
> How can we change in code the Scale Minimum value via code, depending
> on the data in a dataset?
> What I would like to beable to do is find the min. value in the dataset
> and then set Scale Minimum value
>|||RS 2000: If you leave the Min value empty, the chart control will
"auto-scale" the minimum based on the actual data values.
RS 2005: you will be able to specify an expression for the min/max values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"yrmeyer" <yrmeyer@.discussions.microsoft.com> wrote in message
news:6DDA957A-3D62-4CC9-87F9-7374DF9AA1C1@.microsoft.com...
> Bryan,
> I had contacted Dundas support via email on this subject on 3/29 and they
> said that it was not possible via the RS version and noted they don't
> provide
> support for RS and directed me to Microsoft's forum websites.
> "Bryan Avery" wrote:
>> How can we change in code the Scale Minimum value via code, depending
>> on the data in a dataset?
>> What I would like to beable to do is find the min. value in the dataset
>> and then set Scale Minimum value
>>

changing sa pwd, how?

hi all,
i want to change the sa password from my C# code.
i'm using SQL server 2000, win XP, .NET 2003 and need to develop a
simple application that changes the sa password from the code and not
the enterprise manager
i'll be logged in as an sa.
thanx for ur help and timeIf you are logging as sa then you can just run sp_password in your
code. Another option is to use SQL DMO, but I think that using
sp_password is much easier. By the way why do you need to create an
application for that? Only DBA should know the password and DBA won't
need an application to modify the SA's password.
Adi
Eng.R...@.gmail.com wrote:
> hi all,
> i want to change the sa password from my C# code.
> i'm using SQL server 2000, win XP, .NET 2003 and need to develop a
> simple application that changes the sa password from the code and not
> the enterprise manager
> i'll be logged in as an sa.
>
> thanx for ur help and time|||Eng.Rana@.gmail.com wrote:
> hi all,
> i want to change the sa password from my C# code.
> i'm using SQL server 2000, win XP, .NET 2003 and need to develop a
> simple application that changes the sa password from the code and not
> the enterprise manager
> i'll be logged in as an sa.
>
> thanx for ur help and time
>
Why would you possibly want to do this? The sa login/password should
NEVER be used by an application, let alone modified by one. That
information should be closely guarded and known by only those
individuals who truly need to know it. In my organization, only a few
of the DBA's know the true SA password, or where to find it. Everybody
else is granted sysadmin privileges through their Windows login.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||actually, i want to build an application to enforce some configurations
to my SQL server to ensure a checklist of configurations to harden my
server infrastructure.
actually, i found many recommanded checklists for securing sql server
like enforcing strong sa password, setting audit level to
failurem.,... and much much more. so i thought of implementing some
code that will enforce the configuration check list automatically by
the DBA once and for all.
thats why i need to change the sa password from my code.
so i was wondering how can i do it using SQL DMO
thanx for help and time :)

Thursday, March 8, 2012

changing sa pwd, how?

Changing record number

Hi,
I have cancelled some records due to null fields,then the record number is not coming in order.how to code it to get it in orderwow, that's a short description.........
So I'm gonna have to guess the detail. You are probably, from the brief description, experiencing a problem because your report is having trouble with the nulls. More information, including database type, OS, CR version always helps us better assess the problem.
I think you need a formula to convert fields likely to be nulls, eg:
@.AccountBalance

if IsNull({tablename.AccountBalance}) then
formula = 0
else
formula = {tablename.AccountBalance}
end if

I've deliberatley kept this simple to demonstrate the principles.

Dave

Thursday, February 16, 2012

Changing field type

I'm using my own data processing extension to provide a dataset to a report -
the dataset is beging generatied by VB code and passed in form of XML to the
report as a parameter. Everything works fine except from that type of all
fields is String (although they are passed as doubles in the code), so I'm
not able to show graphs on the report. I've tried to solve the problem by
adding following line:
<rd:TypeName>System.Double</rd:TypeName>
to each datafield in the RDL-file. But it didn't help.
Is it something else I could try to solve this problem?Right-click on the fields list and click "add...". Give it a name and
specify a calculated field of:
=cint(Fields!fieldname.Value)
Mike G.
"Anna" <Anna@.discussions.microsoft.com> wrote in message
news:75B8EF07-1159-4824-93F2-FDF74863DF20@.microsoft.com...
> I'm using my own data processing extension to provide a dataset to a
> report -
> the dataset is beging generatied by VB code and passed in form of XML to
> the
> report as a parameter. Everything works fine except from that type of all
> fields is String (although they are passed as doubles in the code), so I'm
> not able to show graphs on the report. I've tried to solve the problem by
> adding following line:
> <rd:TypeName>System.Double</rd:TypeName>
> to each datafield in the RDL-file. But it didn't help.
> Is it something else I could try to solve this problem?|||Anna,
Maybe it would work if you create a fuction in the Custom Code section,
which casts the strings to double's
Something like
Function ConvertToDouble(sValue as String)
return Double.Parse(sValue)
end Function
And as your chart data value you sould add
'=Code.ConvertToDouble(Fields!stringValue)' to use the convert values as your
chart input.
Jan Pieter Posthuma
"Anna" wrote:
> I'm using my own data processing extension to provide a dataset to a report -
> the dataset is beging generatied by VB code and passed in form of XML to the
> report as a parameter. Everything works fine except from that type of all
> fields is String (although they are passed as doubles in the code), so I'm
> not able to show graphs on the report. I've tried to solve the problem by
> adding following line:
> <rd:TypeName>System.Double</rd:TypeName>
> to each datafield in the RDL-file. But it didn't help.
> Is it something else I could try to solve this problem?|||Sorry, didn't notice the "double" until I read Jan's post. Of course, that
means using cdbl instead of cint. (or using Jan's suggestion and creating a
function)
Mike G.
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:e6Mh5j0TFHA.584@.TK2MSFTNGP15.phx.gbl...
> Right-click on the fields list and click "add...". Give it a name and
> specify a calculated field of:
> =cint(Fields!fieldname.Value)
> Mike G.
>
> "Anna" <Anna@.discussions.microsoft.com> wrote in message
> news:75B8EF07-1159-4824-93F2-FDF74863DF20@.microsoft.com...
>> I'm using my own data processing extension to provide a dataset to a
>> report -
>> the dataset is beging generatied by VB code and passed in form of XML to
>> the
>> report as a parameter. Everything works fine except from that type of all
>> fields is String (although they are passed as doubles in the code), so
>> I'm
>> not able to show graphs on the report. I've tried to solve the problem by
>> adding following line:
>> <rd:TypeName>System.Double</rd:TypeName>
>> to each datafield in the RDL-file. But it didn't help.
>> Is it something else I could try to solve this problem?
>

Sunday, February 12, 2012

Changing database within a stored proc

Is this possible in a stored procedure to receive a database name in parameter to be able to apply the code within that stored procedure to all table in the database?

I need to go trough all of the database of the server trough the database list of the master and gather data from all the table and i was wondering if there was a way for me to do a generic code for each database, the only thing that i would need to change would be the database it look into at the moment.

Would something like this work? :

*Code*

*a loop in the database list*

My_stored_proc current_database_name

My stored proc:

Use current_database_name

*Code that work with this database*

Unfortunately, that is not just a simple task.

Yes, you can create a loop through the databases from sys.databases (SQL 2005) or sysdatabases (SQL 2000).

However, you cannot simply use an 'Object' name as you would use a variable or parameter. So the statement [ USE @.Variable ] does not work. You could create an entire SQL statement as a variable, and then execute that statement using dynamic SQL -but that too has it issues.

First you have to 'build' the entire SQL statement; that's not too difficult. something like (peusdocode):

Code Snippet

DECLARE
@.dbName nvarchar(200),
@.SQLStatement nvarchar(1000)
{loop here}
SET @.dbName = name in sys.databases {selected from loop position}
SET @.SQLStatement = 'Use ' + @.dbname
EXECUTE( @.SQLStatement )

Now at this point, there is a problem, because the EXECUTE (or sp_executesql) statement operates in a separate thread, and any results are not directly accessible to the rest of your code. The next line of your code would NOT use any database other then the one you started out with because as soon as the EXECUTE statement completes, control is returned back to the original thread -which has no idea what happened when the EXECUTE statement ran.

There are methods to get around that behavior, but the most common ones require extensive knowledge of how EXECUTE() or sp_executesql operates.

I recommend that you first review this article, and then come back for assistance if you wish to continue down this path.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

The key element that I hope you get here is, yes, someone can create the functionality. But will you be able to understand and maintain the code, or will you be 'up a creek' if something changes? ... Your best option if you have to do this, is to learn and understand how to use and what is happening with dynamic SQL as it is being used. Without that understanding, you may continually be frustrated.

|||

Thanks for the text, it was very interesting.

I found out that using cursor will be much more usefull for going trough all the table of the databases.

Changing Database Structures Through Code

I am having a hard time finding materials on this subject. I am guessing I am using the wrong keywords to search. Basically, I want to be able to modify database tables through a web form. They can add columns and delete columns through the form. I would just want to default the type of column and the length. I am sure it has been done, I was just wondering if anyone had some resources they could throw my way. I would appreciate it. Thanks.

Hi, I wrote a stored procedure to alter table, share with you:)

create proc sp_alterTbl @.TblName sysname,@.ColName sysname,@.ColType varchar(50)=null,
@.ColLen int=null,@.Op varchar(6)='Add'
as
begin
declare @.cmd varchar(500),@.TypeLenStr varchar(50)
if object_id(@.TblName) is null
Raiserror('Table does not exist!',16,1)
else
if not exists (select * from systypes where name= @.ColType)
Raiserror('Type does not exists!',16,1)
else
begin

select @.TypeLenStr=CASE @.ColLen WHEN null Then ' '
Else '('+convert(varchar(6),@.ColLen)+')'
END
select @.cmd= CASE @.Op when 'Add' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
+@.ColType+ @.TypeLenStr
WHEN 'Drop' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
END
EXEC(@.cmd)
END
END

Changing database query (server, database, tables and query)

I'm working with ASP.NET web forms (using Visual Studio and C# code)
I've now introduced 'SQL Server 2000 Reporting Services' and am impressed
with what I've seen so far (albeit that I've only spent a few hours with it).
:)
1). I need the ability to point the report to a server / database rather
than always have it running against the same target. Similarly I may need to
change the SQL syntax of the query being run (rather than relying on
parameter passing).
It looks as if I need to do this via making use of the web service - but I
can't get my head around this.
Thanks in advance
SteveI've just posted a DPE to
http://workspaces.gotdotnet.com/appworld that may help you.
Regards
Toby
"Steve_S3T" <Steve_S3T@.discussions.microsoft.com> wrote in message
news:4AEF4962-798A-4C11-BFEF-B30E4800ADDA@.microsoft.com...
> I'm working with ASP.NET web forms (using Visual Studio and C# code)
> I've now introduced 'SQL Server 2000 Reporting Services' and am impressed
> with what I've seen so far (albeit that I've only spent a few hours with
it).
> :)
> 1). I need the ability to point the report to a server / database rather
> than always have it running against the same target. Similarly I may need
to
> change the SQL syntax of the query being run (rather than relying on
> parameter passing).
> It looks as if I need to do this via making use of the web service - but
I
> can't get my head around this.
> Thanks in advance
> Steve

Changing database path of my reports

hi!!!
i have problem in changing the database of my reports. i found a code here

Dim crxDatabaseTable As CRAXDRT.DatabaseTable
For Each crxDatabaseTable In report.Database.Tables
crxDatabaseTable.ConnectionProperties.Item("Database Name") = "C:\Temp\xtreme.mdb"
Next crxDatabaseTable

bu when i coded this to my project it says CRAXDRT.DatabaseTable

anyone can help me for this i have change paths with code because of when i run my project any drives it has to work...Why not use
crxDatabaseTable.Location = "C:\Temp\xtreme.mdb"
instead of setting ConnectionProperties?

Also, dont forget that a report can have subreports that will also need setting location info.|||how can i use it?

i cant do anything... i think i have to define something but i dont know what...

my report name is exitreportrep when i wrote it to a form load or anywhere i have 3 choice only
exitreportrep.EnableEventLog()
exitreportrep.Equals()
exitreportrep.ReferenceEquals()

i dont know what to do...

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

Sunday, March 25, 2012

Changing the default database files path fails

Sunday, March 11, 2012

changing schema owner through SMO

Changing Scale Minimum value on a Graph

changing sa pwd, how?

Thursday, March 8, 2012

changing sa pwd, how?

changing sa pwd, how?

Changing record number

Thursday, February 16, 2012

Changing field type

Sunday, February 12, 2012

Changing database within a stored proc

Changing Database Structures Through Code

Changing database query (server, database, tables and query)

Changing database path of my reports

database collation

Blog Archive

About Me