Hi, We are using an application .It 's database is on SQL server 2000. One of its module is getting the information from pbx and writing the cost to customer's folios.But sometimes it cannot calculate the right cost because of signalization.Database has a table with column writing the time in mm:ss format.I want to write a trigger that will calculate and insert the right cost if it is wrong.But i have to change the time format from mm:ss to seconds only,for calculating .How can i do it.(It must convert to seconds only while calculating but it musn't change the format on the table else program won't run properly)Thanxis the column a datetime datatype or is the data stored as mm:ss in, I guess a varchar datatype?
if you have a datetime datatype then use DATEPART to extract the minutes and seconds and do the math.
select (DATEPART(mi,<column>) * 60) + DATEPART(ss,<column>)
if you have a varchar datatype then you follow the same idea but convert the strings into ints.
select cast(left(<column>,2) as int) * 60) + cast(right(<column>,2) as int)
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Changing the Server Name
Hi Guys,
I am using a MS SQL Server 2005 express edition for storing the meta-files of a b2b application.
The SQL 2005 is installed localy. Now I must change my server (machine) name cause a internal application need this action.
What is the best practice to do this?
I've got 2 databases and a lot of tabeles within the installed sql server.
I am happy for all tutorials or instructions to handle this proposal nearyl perfectyl.
Many Regards
Check out http://msdn2.microsoft.com/en-us/library/ms143799.aspx
Mike
Thursday, March 22, 2012
Changing the database from MSAccess to MSSQLServer.
Hello all,
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
JackAnswered in .newusers
Please keep your posts to one newsgreoup. What you want to do is well
documented in a few thousand different pages all over the internet
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:427EBA98-380A-42EC-A3C5-47097790D9F9@.microsoft.com...
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from
> MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack|||MS Access 2000 Migration Guide
http://www.microsoft.com/technet/pr...
/acssmgde.mspx
After porting over the tables and data, read up on the techniques for
optimizing a SQL Server application:
http://msdn.microsoft.com/library/d...
etHowTo03.asp
http://msdn.microsoft.com/library/d...
ethowto04.asp
http://msdn.microsoft.com/SQL/2000/...rf/default.aspx
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:427EBA98-380A-42EC-A3C5-47097790D9F9@.microsoft.com...
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from
> MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
JackAnswered in .newusers
Please keep your posts to one newsgreoup. What you want to do is well
documented in a few thousand different pages all over the internet
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:427EBA98-380A-42EC-A3C5-47097790D9F9@.microsoft.com...
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from
> MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack|||MS Access 2000 Migration Guide
http://www.microsoft.com/technet/pr...
/acssmgde.mspx
After porting over the tables and data, read up on the techniques for
optimizing a SQL Server application:
http://msdn.microsoft.com/library/d...
etHowTo03.asp
http://msdn.microsoft.com/library/d...
ethowto04.asp
http://msdn.microsoft.com/SQL/2000/...rf/default.aspx
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:427EBA98-380A-42EC-A3C5-47097790D9F9@.microsoft.com...
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from
> MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
Changing the database from MSAccess to MSSQLServer.
Hello all,
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
Jack
see the response in .newusers
Please only post the same question to one group...
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4C0633A4-44A2-402B-8C6B-91A646D8B988@.microsoft.com...
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from
> MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
sql
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
Jack
see the response in .newusers
Please only post the same question to one group...
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4C0633A4-44A2-402B-8C6B-91A646D8B988@.microsoft.com...
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from
> MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
sql
Changing the database from MSAccess to MSSQLServer.
Hello all,
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
Jack
Hi Jack
You don't say which version of SQL Server you wish to upgrade to?
The guys in the Access newsgroup tend to have their own opinons regarding
the Access upgrade wizard so you may want to ask in there!
The upgrade wizard may provide enough to upgrade your database but then you
should look at tuning and re-writing the code so that it is more compatible
with SQL server, such as choosing more appropriate data types etc.
As an alternative you can do this manually and piecemeal using the Import
Wizard in SQL server (i.e DTS/SSIS).
Before you try anything make sure that you have a recoverable backup. It
will be worthwhile honing the upgrade on a separate system before attempting
anything on the live database.
John
"Jack" wrote:
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
Jack
Hi Jack
You don't say which version of SQL Server you wish to upgrade to?
The guys in the Access newsgroup tend to have their own opinons regarding
the Access upgrade wizard so you may want to ask in there!
The upgrade wizard may provide enough to upgrade your database but then you
should look at tuning and re-writing the code so that it is more compatible
with SQL server, such as choosing more appropriate data types etc.
As an alternative you can do this manually and piecemeal using the Import
Wizard in SQL server (i.e DTS/SSIS).
Before you try anything make sure that you have a recoverable backup. It
will be worthwhile honing the upgrade on a separate system before attempting
anything on the live database.
John
"Jack" wrote:
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
Changing the database from MSAccess to MSSQLServer.
Hello all,
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
JackHi Jack
You don't say which version of SQL Server you wish to upgrade to?
The guys in the Access newsgroup tend to have their own opinons regarding
the Access upgrade wizard so you may want to ask in there!
The upgrade wizard may provide enough to upgrade your database but then you
should look at tuning and re-writing the code so that it is more compatible
with SQL server, such as choosing more appropriate data types etc.
As an alternative you can do this manually and piecemeal using the Import
Wizard in SQL server (i.e DTS/SSIS).
Before you try anything make sure that you have a recoverable backup. It
will be worthwhile honing the upgrade on a separate system before attempting
anything on the live database.
John
"Jack" wrote:
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from MS-Acce
ss
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
JackHi Jack
You don't say which version of SQL Server you wish to upgrade to?
The guys in the Access newsgroup tend to have their own opinons regarding
the Access upgrade wizard so you may want to ask in there!
The upgrade wizard may provide enough to upgrade your database but then you
should look at tuning and re-writing the code so that it is more compatible
with SQL server, such as choosing more appropriate data types etc.
As an alternative you can do this manually and piecemeal using the Import
Wizard in SQL server (i.e DTS/SSIS).
Before you try anything make sure that you have a recoverable backup. It
will be worthwhile honing the upgrade on a separate system before attempting
anything on the live database.
John
"Jack" wrote:
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from MS-Acce
ss
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
Changing the database from MSAccess to MSSQLServer.
Hello all,
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
JackHi Jack
You don't say which version of SQL Server you wish to upgrade to?
The guys in the Access newsgroup tend to have their own opinons regarding
the Access upgrade wizard so you may want to ask in there!
The upgrade wizard may provide enough to upgrade your database but then you
should look at tuning and re-writing the code so that it is more compatible
with SQL server, such as choosing more appropriate data types etc.
As an alternative you can do this manually and piecemeal using the Import
Wizard in SQL server (i.e DTS/SSIS).
Before you try anything make sure that you have a recoverable backup. It
will be worthwhile honing the upgrade on a separate system before attempting
anything on the live database.
John
"Jack" wrote:
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
JackHi Jack
You don't say which version of SQL Server you wish to upgrade to?
The guys in the Access newsgroup tend to have their own opinons regarding
the Access upgrade wizard so you may want to ask in there!
The upgrade wizard may provide enough to upgrade your database but then you
should look at tuning and re-writing the code so that it is more compatible
with SQL server, such as choosing more appropriate data types etc.
As an alternative you can do this manually and piecemeal using the Import
Wizard in SQL server (i.e DTS/SSIS).
Before you try anything make sure that you have a recoverable backup. It
will be worthwhile honing the upgrade on a separate system before attempting
anything on the live database.
John
"Jack" wrote:
> Hello all,
> I got a application which is developed using VB6.0 as front end and
> MS-Access as backend. Now i would like to change the database from MS-Access
> to MS-SQLserver.
> How do I change the databse from MS-Access to MS-SQLServer?
> Regards
> Jack
Tuesday, March 20, 2012
Changing System Time on SQL Server Hardware
I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.
Changing SQL Server Name
Greetings,
I have an application that runs on SQL Server XXXX. I want to change the
name of the server to YYYY.
Other than changing references within the application, is there anything
else I need to do because of the Server name change?
Platform: Win 2000 Server/SQL Server 2000
Thanks in advance,
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
After renaming a server, you also need to update the system table
references to the server name - something like:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName','local'
After executing the stored procedures, restart SQL Server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Oh and the jobs. You can script out your jobs and then go through the
script and change the server name and replace the jobs with the new
jobs you have just scripted.
The sysjobs table has the originating_server column which has the
server name value stored in it. It will be referencing the old server
name when you rename the server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||I've written an article on this particular topic:
http://www.karaszi.com/SQLServer/inf...rver_name.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"don larry" <donlarry17@.hotmail.com> wrote in message
news:%23w$LBrolEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Greetings,
> I have an application that runs on SQL Server XXXX. I want to change the
> name of the server to YYYY.
> Other than changing references within the application, is there anything
> else I need to do because of the Server name change?
> Platform: Win 2000 Server/SQL Server 2000
> Thanks in advance,
> Don
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thank you so much guys, i will implement all these steps.
Big Appreciation!
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sql
I have an application that runs on SQL Server XXXX. I want to change the
name of the server to YYYY.
Other than changing references within the application, is there anything
else I need to do because of the Server name change?
Platform: Win 2000 Server/SQL Server 2000
Thanks in advance,
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
After renaming a server, you also need to update the system table
references to the server name - something like:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName','local'
After executing the stored procedures, restart SQL Server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Oh and the jobs. You can script out your jobs and then go through the
script and change the server name and replace the jobs with the new
jobs you have just scripted.
The sysjobs table has the originating_server column which has the
server name value stored in it. It will be referencing the old server
name when you rename the server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||I've written an article on this particular topic:
http://www.karaszi.com/SQLServer/inf...rver_name.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"don larry" <donlarry17@.hotmail.com> wrote in message
news:%23w$LBrolEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Greetings,
> I have an application that runs on SQL Server XXXX. I want to change the
> name of the server to YYYY.
> Other than changing references within the application, is there anything
> else I need to do because of the Server name change?
> Platform: Win 2000 Server/SQL Server 2000
> Thanks in advance,
> Don
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thank you so much guys, i will implement all these steps.
Big Appreciation!
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sql
Monday, March 19, 2012
Changing SQL Server Name
Greetings,
I have an application that runs on SQL Server XXXX. I want to change the
name of the server to YYYY.
Other than changing references within the application, is there anything
else I need to do because of the Server name change?
Platform: Win 2000 Server/SQL Server 2000
Thanks in advance,
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!After renaming a server, you also need to update the system table
references to the server name - something like:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName','local'
After executing the stored procedures, restart SQL Server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!|||Oh and the jobs. You can script out your jobs and then go through the
script and change the server name and replace the jobs with the new
jobs you have just scripted.
The sysjobs table has the originating_server column which has the
server name value stored in it. It will be referencing the old server
name when you rename the server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!|||I've written an article on this particular topic:
http://www.karaszi.com/SQLServer/info_change_server_name.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"don larry" <donlarry17@.hotmail.com> wrote in message
news:%23w$LBrolEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Greetings,
> I have an application that runs on SQL Server XXXX. I want to change the
> name of the server to YYYY.
> Other than changing references within the application, is there anything
> else I need to do because of the Server name change?
> Platform: Win 2000 Server/SQL Server 2000
> Thanks in advance,
> Don
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thank you so much guys, i will implement all these steps.
Big Appreciation!
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I have an application that runs on SQL Server XXXX. I want to change the
name of the server to YYYY.
Other than changing references within the application, is there anything
else I need to do because of the Server name change?
Platform: Win 2000 Server/SQL Server 2000
Thanks in advance,
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!After renaming a server, you also need to update the system table
references to the server name - something like:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName','local'
After executing the stored procedures, restart SQL Server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!|||Oh and the jobs. You can script out your jobs and then go through the
script and change the server name and replace the jobs with the new
jobs you have just scripted.
The sysjobs table has the originating_server column which has the
server name value stored in it. It will be referencing the old server
name when you rename the server.
-Sue
On Thu, 09 Sep 2004 09:33:34 -0700, don larry <donlarry17@.hotmail.com>
wrote:
>Greetings,
>I have an application that runs on SQL Server XXXX. I want to change the
>name of the server to YYYY.
>Other than changing references within the application, is there anything
>else I need to do because of the Server name change?
>Platform: Win 2000 Server/SQL Server 2000
>Thanks in advance,
>Don
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!|||I've written an article on this particular topic:
http://www.karaszi.com/SQLServer/info_change_server_name.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"don larry" <donlarry17@.hotmail.com> wrote in message
news:%23w$LBrolEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Greetings,
> I have an application that runs on SQL Server XXXX. I want to change the
> name of the server to YYYY.
> Other than changing references within the application, is there anything
> else I need to do because of the Server name change?
> Platform: Win 2000 Server/SQL Server 2000
> Thanks in advance,
> Don
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thank you so much guys, i will implement all these steps.
Big Appreciation!
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Sunday, March 11, 2012
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 :)
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?
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 will be logged in as a sysadmin, easiest is probably
to just send the SQL statement in your code:
sp_password @.new = 'YourNewPassword', @.loginame = 'sa'
-Sue
On 10 Aug 2006 06:22:00 -0700, 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
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 will be logged in as a sysadmin, easiest is probably
to just send the SQL statement in your code:
sp_password @.new = 'YourNewPassword', @.loginame = 'sa'
-Sue
On 10 Aug 2006 06:22:00 -0700, 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
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
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
Changing publisher for Merge Replication
We have a database application that is currently being replicated to 2
remote sites. In the near future, I expect that this will increase to many
more sites. My question is this, as we move forward it is probable that at
some time we will run into the need to replace the server that currently
functions as the publisher server. Is there some way to change all of the
publications to use a different server without having to remove replication
and reinstalling it?
TIA
Ron Lounsbury
Ron,
in my experience it is only possible to migrate replication databases from
one server to another if the server names are identical. If they are not,
then the best thing to do is to script out replication, change the
references to servernames and job owners appropriately, drop the 'old'
publications on the old server then recreate the publications on the new
server.
Another poster ran into this type of issue after restoring a merge published
database to another server and found when he tried to add a column he got
the error: 21260 "Schema replication failed because database '%s' on server
'%s' is not the original Publisher of table '%s'".
I investigated this and in sysmerge articles there is a publisherid. This
ID needs to have a corresponding record in sysmergepublications. However, in
sysmergepublications, the 'publisher' column needs to match the servername,
which it didn't in his case as he had restored to another servername. So the
problem is that the replication metadata tables 'hardcode' the original
server name.
HTH,
Paul Ibison
|||Paul
Thanks for the response. It's not what I wanted to hear, but I was
pretty much expecting it. One of the problems we will have is that not all
of our subscribers are connected at any given time, making it a bit
difficult to push the database out again. Also, there is a certain amount
of time (@.20 min or so) that the subscriber can't use the system while we
are doing this. Oh well, as I said, I was afraid that this would be the
answer.
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6cJxkJeEHA.3476@.tk2msftngp13.phx.gbl...
> Ron,
> in my experience it is only possible to migrate replication databases from
> one server to another if the server names are identical. If they are not,
> then the best thing to do is to script out replication, change the
> references to servernames and job owners appropriately, drop the 'old'
> publications on the old server then recreate the publications on the new
> server.
> Another poster ran into this type of issue after restoring a merge
published
> database to another server and found when he tried to add a column he got
> the error: 21260 "Schema replication failed because database '%s' on
server
> '%s' is not the original Publisher of table '%s'".
> I investigated this and in sysmerge articles there is a publisherid. This
> ID needs to have a corresponding record in sysmergepublications. However,
in
> sysmergepublications, the 'publisher' column needs to match the
servername,
> which it didn't in his case as he had restored to another servername. So
the
> problem is that the replication metadata tables 'hardcode' the original
> server name.
> HTH,
> Paul Ibison
>
remote sites. In the near future, I expect that this will increase to many
more sites. My question is this, as we move forward it is probable that at
some time we will run into the need to replace the server that currently
functions as the publisher server. Is there some way to change all of the
publications to use a different server without having to remove replication
and reinstalling it?
TIA
Ron Lounsbury
Ron,
in my experience it is only possible to migrate replication databases from
one server to another if the server names are identical. If they are not,
then the best thing to do is to script out replication, change the
references to servernames and job owners appropriately, drop the 'old'
publications on the old server then recreate the publications on the new
server.
Another poster ran into this type of issue after restoring a merge published
database to another server and found when he tried to add a column he got
the error: 21260 "Schema replication failed because database '%s' on server
'%s' is not the original Publisher of table '%s'".
I investigated this and in sysmerge articles there is a publisherid. This
ID needs to have a corresponding record in sysmergepublications. However, in
sysmergepublications, the 'publisher' column needs to match the servername,
which it didn't in his case as he had restored to another servername. So the
problem is that the replication metadata tables 'hardcode' the original
server name.
HTH,
Paul Ibison
|||Paul
Thanks for the response. It's not what I wanted to hear, but I was
pretty much expecting it. One of the problems we will have is that not all
of our subscribers are connected at any given time, making it a bit
difficult to push the database out again. Also, there is a certain amount
of time (@.20 min or so) that the subscriber can't use the system while we
are doing this. Oh well, as I said, I was afraid that this would be the
answer.
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6cJxkJeEHA.3476@.tk2msftngp13.phx.gbl...
> Ron,
> in my experience it is only possible to migrate replication databases from
> one server to another if the server names are identical. If they are not,
> then the best thing to do is to script out replication, change the
> references to servernames and job owners appropriately, drop the 'old'
> publications on the old server then recreate the publications on the new
> server.
> Another poster ran into this type of issue after restoring a merge
published
> database to another server and found when he tried to add a column he got
> the error: 21260 "Schema replication failed because database '%s' on
server
> '%s' is not the original Publisher of table '%s'".
> I investigated this and in sysmerge articles there is a publisherid. This
> ID needs to have a corresponding record in sysmergepublications. However,
in
> sysmergepublications, the 'publisher' column needs to match the
servername,
> which it didn't in his case as he had restored to another servername. So
the
> problem is that the replication metadata tables 'hardcode' the original
> server name.
> HTH,
> Paul Ibison
>
Sunday, February 19, 2012
changing indexes
Hi, My application goes through a lengthy data processing phase before
it's ready to run. During the DP phase I'm mostly inserting records,
afterwards many of my tables will be read only. Is there anything
wrong with dropping indexes used for the DP and setting up clustered
indexes afterwards? Can I just declare my clustered indexes and go,
or do I have run some kind of statistics ...?Hi
it's ready to run. During the DP phase I'm mostly inserting records,
afterwards many of my tables will be read only. Is there anything
wrong with dropping indexes used for the DP and setting up clustered
indexes afterwards? Can I just declare my clustered indexes and go,
or do I have run some kind of statistics ...?Hi
There is nothing wrong in doing this, but it sounds like you are possibly
trying to reinvent what analysis services does!!!
If you are inserting a significant number of records your indexes may be
fragmented and therefore defragmenting them or re-creating them are
necessary activities. You may wish to change the fillfactor if your tables
are read only and have a lower fill factor whilst loading and 100% fill
factor when readonly.
You may also want to consider moving this data load offline into a separate
database, then you swap in the new data when it has been built and reduce
downtime. The whole database could be read only!
John
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1128630847.225685.79080@.g43g2000cwa.googlegro ups.com...
> Hi, My application goes through a lengthy data processing phase before
> it's ready to run. During the DP phase I'm mostly inserting records,
> afterwards many of my tables will be read only. Is there anything
> wrong with dropping indexes used for the DP and setting up clustered
> indexes afterwards? Can I just declare my clustered indexes and go,
> or do I have run some kind of statistics ...?
Changing identity seed and increment after the fact
Hi all,
I've been thrown a curve ball late in the game on an application I'm developing. Without getting into the specifics of the application I store a unique employee ID number for all person records in it. This ID is provided to us by the companies we're servicing. Up until this point in time we didn't have need of an externally visable ID other than the one provided to us. Now, a need for an internally generated (by our application) unique ID has been discovered. This number needs to be a minimum 5 digits (e.x. 10001, 10002, ...). I could achieve this nicely by seeding an identity column at 10000 with an increment of 1. However, I've alredy made different settings for this and there are records in the db with the old ones.
My question is: If I initially set the seed / increment at 1/1 can I change this after the fact without causing data integrity problems. Will all subsequent insertions into this table just start at the new values?
Or, ideally I'd like to create a new column seperate from the PK Identity column already in place that serves the same function, incrementing a 5 digit number by 1 for each new record. Seems that you're only allowed one identity column per table though. Is there another way to achieve get the same result as identity?
Thanks!You can use DBCC Checkident to reseed the seeding of the identity column.|||Thanks,
I'm Looking at the help files on DBCC checkident now. It doesn't look like it will convert any existing records in the table. If there are 100 records in the table with identity values 1 through 100 and I change the seed/increment valuse to 10000/+1, I'm asuming that the existing 100 records will maintain their current ident values?|||Yes, the value will be the same even you execute the DBCC. You can save the table to a temp, delete all rows out of the existing table, change the identity seeding, and then reload the data. That will do it.|||I think that will work. Thanks again for the help!
I've been thrown a curve ball late in the game on an application I'm developing. Without getting into the specifics of the application I store a unique employee ID number for all person records in it. This ID is provided to us by the companies we're servicing. Up until this point in time we didn't have need of an externally visable ID other than the one provided to us. Now, a need for an internally generated (by our application) unique ID has been discovered. This number needs to be a minimum 5 digits (e.x. 10001, 10002, ...). I could achieve this nicely by seeding an identity column at 10000 with an increment of 1. However, I've alredy made different settings for this and there are records in the db with the old ones.
My question is: If I initially set the seed / increment at 1/1 can I change this after the fact without causing data integrity problems. Will all subsequent insertions into this table just start at the new values?
Or, ideally I'd like to create a new column seperate from the PK Identity column already in place that serves the same function, incrementing a 5 digit number by 1 for each new record. Seems that you're only allowed one identity column per table though. Is there another way to achieve get the same result as identity?
Thanks!You can use DBCC Checkident to reseed the seeding of the identity column.|||Thanks,
I'm Looking at the help files on DBCC checkident now. It doesn't look like it will convert any existing records in the table. If there are 100 records in the table with identity values 1 through 100 and I change the seed/increment valuse to 10000/+1, I'm asuming that the existing 100 records will maintain their current ident values?|||Yes, the value will be the same even you execute the DBCC. You can save the table to a temp, delete all rows out of the existing table, change the identity seeding, and then reload the data. That will do it.|||I think that will work. Thanks again for the help!
Changing ID globally ...
Hi,
We have a help desk application with MS SQL Server backend which is at least
10 years old. This application mainly has 2 tables -
1. One for storing customer ID (Cust_Profile Table) and the
2. Other one for storing all the call details pertaining to customers.
Apart from these two main tables, there are other tables for storing informa
tion like configuration, history etc related to the customer.
My requirement is - I want to change the customer ID globally across the sys
tem. But I am not too sure as to how to proceed. Any suggestions/ideas welc
ome.
Thanks in advance,
HarishHarish,
Review the following psuedocode use the following as guideline only...do not
do this directly on the production system and please test it on the staging
or dev env.
-- NO IDENTITY PROPERTY
Pause access to the database from the Application
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
UPDATE table1 ..
Check for Errors...rollback if needed.
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
-- YES IDENTITY PROPERTY
Check if the Customer ID is of an Identity property
Pause access to the database from the Application
If it is of Identity property you can not Update you have to write a
migration utility
You may have to Create a shadow table with same schema as Customer Profile
(Without Identity Property on ID column)
Copy Data from Base Table (Original) to Shadow Table
Update the Value (Old ID - New ID) in the Shadow Table
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
DELETE Base Table (Original) -- Use Delete instead of Truncate for
Rollback purposes
Check for Errors...rollback if needed.
SET IDENTITY_INSERT Base Table ON
INSERT Base Table (Collist) SELECT Collist From Shadow Table
SET IDENTITY_INSERT Base Table OFF
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
Resume Access to the database from the Application
HTH
Satish Balusa
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:248AB598-83B4-4611-92E3-BE336DA65170@.microsoft.com...
least 10 years old. This application mainly has 2 tables -
information like configuration, history etc related to the customer.
system. But I am not too sure as to how to proceed. Any suggestions/ideas
welcome.
We have a help desk application with MS SQL Server backend which is at least
10 years old. This application mainly has 2 tables -
1. One for storing customer ID (Cust_Profile Table) and the
2. Other one for storing all the call details pertaining to customers.
Apart from these two main tables, there are other tables for storing informa
tion like configuration, history etc related to the customer.
My requirement is - I want to change the customer ID globally across the sys
tem. But I am not too sure as to how to proceed. Any suggestions/ideas welc
ome.
Thanks in advance,
HarishHarish,
Review the following psuedocode use the following as guideline only...do not
do this directly on the production system and please test it on the staging
or dev env.
-- NO IDENTITY PROPERTY
Pause access to the database from the Application
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
UPDATE table1 ..
Check for Errors...rollback if needed.
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
-- YES IDENTITY PROPERTY
Check if the Customer ID is of an Identity property
Pause access to the database from the Application
If it is of Identity property you can not Update you have to write a
migration utility
You may have to Create a shadow table with same schema as Customer Profile
(Without Identity Property on ID column)
Copy Data from Base Table (Original) to Shadow Table
Update the Value (Old ID - New ID) in the Shadow Table
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
DELETE Base Table (Original) -- Use Delete instead of Truncate for
Rollback purposes
Check for Errors...rollback if needed.
SET IDENTITY_INSERT Base Table ON
INSERT Base Table (Collist) SELECT Collist From Shadow Table
SET IDENTITY_INSERT Base Table OFF
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
Resume Access to the database from the Application
HTH
Satish Balusa
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:248AB598-83B4-4611-92E3-BE336DA65170@.microsoft.com...
quote:
> Hi,
> We have a help desk application with MS SQL Server backend which is at
least 10 years old. This application mainly has 2 tables -
quote:
> 1. One for storing customer ID (Cust_Profile Table) and the
> 2. Other one for storing all the call details pertaining to customers.
> Apart from these two main tables, there are other tables for storing
information like configuration, history etc related to the customer.
quote:
> My requirement is - I want to change the customer ID globally across the
system. But I am not too sure as to how to proceed. Any suggestions/ideas
welcome.
quote:
> Thanks in advance,
> Harish
Changing ID globally ...
Hi
We have a help desk application with MS SQL Server backend which is at least 10 years old. This application mainly has 2 tables -
1. One for storing customer ID (Cust_Profile Table) and th
2. Other one for storing all the call details pertaining to customers.
Apart from these two main tables, there are other tables for storing information like configuration, history etc related to the customer
My requirement is - I want to change the customer ID globally across the system. But I am not too sure as to how to proceed. Any suggestions/ideas welcome
Thanks in advance
HarishHarish,
Review the following psuedocode use the following as guideline only...do not
do this directly on the production system and please test it on the staging
or dev env.
-- NO IDENTITY PROPERTY
Pause access to the database from the Application
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
UPDATE table1 ..
Check for Errors...rollback if needed.
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
-- YES IDENTITY PROPERTY
Check if the Customer ID is of an Identity property
Pause access to the database from the Application
If it is of Identity property you can not Update you have to write a
migration utility
You may have to Create a shadow table with same schema as Customer Profile
(Without Identity Property on ID column)
Copy Data from Base Table (Original) to Shadow Table
Update the Value (Old ID - New ID) in the Shadow Table
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
DELETE Base Table (Original) -- Use Delete instead of Truncate for
Rollback purposes
Check for Errors...rollback if needed.
SET IDENTITY_INSERT Base Table ON
INSERT Base Table (Collist) SELECT Collist From Shadow Table
SET IDENTITY_INSERT Base Table OFF
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
Resume Access to the database from the Application
--
HTH
Satish Balusa
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:248AB598-83B4-4611-92E3-BE336DA65170@.microsoft.com...
> Hi,
> We have a help desk application with MS SQL Server backend which is at
least 10 years old. This application mainly has 2 tables -
> 1. One for storing customer ID (Cust_Profile Table) and the
> 2. Other one for storing all the call details pertaining to customers.
> Apart from these two main tables, there are other tables for storing
information like configuration, history etc related to the customer.
> My requirement is - I want to change the customer ID globally across the
system. But I am not too sure as to how to proceed. Any suggestions/ideas
welcome.
> Thanks in advance,
> Harish
We have a help desk application with MS SQL Server backend which is at least 10 years old. This application mainly has 2 tables -
1. One for storing customer ID (Cust_Profile Table) and th
2. Other one for storing all the call details pertaining to customers.
Apart from these two main tables, there are other tables for storing information like configuration, history etc related to the customer
My requirement is - I want to change the customer ID globally across the system. But I am not too sure as to how to proceed. Any suggestions/ideas welcome
Thanks in advance
HarishHarish,
Review the following psuedocode use the following as guideline only...do not
do this directly on the production system and please test it on the staging
or dev env.
-- NO IDENTITY PROPERTY
Pause access to the database from the Application
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
UPDATE table1 ..
Check for Errors...rollback if needed.
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
-- YES IDENTITY PROPERTY
Check if the Customer ID is of an Identity property
Pause access to the database from the Application
If it is of Identity property you can not Update you have to write a
migration utility
You may have to Create a shadow table with same schema as Customer Profile
(Without Identity Property on ID column)
Copy Data from Base Table (Original) to Shadow Table
Update the Value (Old ID - New ID) in the Shadow Table
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
DELETE Base Table (Original) -- Use Delete instead of Truncate for
Rollback purposes
Check for Errors...rollback if needed.
SET IDENTITY_INSERT Base Table ON
INSERT Base Table (Collist) SELECT Collist From Shadow Table
SET IDENTITY_INSERT Base Table OFF
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
Resume Access to the database from the Application
--
HTH
Satish Balusa
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:248AB598-83B4-4611-92E3-BE336DA65170@.microsoft.com...
> Hi,
> We have a help desk application with MS SQL Server backend which is at
least 10 years old. This application mainly has 2 tables -
> 1. One for storing customer ID (Cust_Profile Table) and the
> 2. Other one for storing all the call details pertaining to customers.
> Apart from these two main tables, there are other tables for storing
information like configuration, history etc related to the customer.
> My requirement is - I want to change the customer ID globally across the
system. But I am not too sure as to how to proceed. Any suggestions/ideas
welcome.
> Thanks in advance,
> Harish
Changing from Administration Mode to Application Mode
I have a W2K server hosting SQL2K and have need to switch it to running in A
pplication mode as the number of remote SQL users has made it difficult to w
ork with only 2 allowed connections as in Administration mode. I am wonderi
ng what the best practice/m
ethod to use to go about accomplishing this task with the least disruption t
o the SQL server (as when I was about to do it I received an error message i
ndicating that SQL would break). Thanks for a quick response.Terminal services probably shouldn't be on the same box as SQL if you want
to run application mode, but the warning you are likely getting is not
neccesarily going to break SQL but since it was installed before App mode
Terminal services were enabled it thinks that this app will need to be
reinstalled to take advantage of Application mode terminal services.
I would purchase a copy of VMware (my favourite tool of all time
www.vmware.com ) and try it out. install a VM session, install Win2k OS into
it, enable term services in admin mode, install SQL and then flip it to app
mode. see what happens.
My guess is you will be fine, but the most impact would be a reboot so a
weekender would be a good idea, but this is a change I would recommend
against. it will adversely affect performance... my $0.02
Neil MacMurchy
"FColvais" <fcolvais1@.comcast.net> wrote in message
news:758C7A26-D01B-4228-89E0-3BB33205560F@.microsoft.com...
> I have a W2K server hosting SQL2K and have need to switch it to running in
Application mode as the number of remote SQL users has made it difficult to
work with only 2 allowed connections as in Administration mode. I am
wondering what the best practice/method to use to go about accomplishing
this task with the least disruption to the SQL server (as when I was about
to do it I received an error message indicating that SQL would break).
Thanks for a quick response.
pplication mode as the number of remote SQL users has made it difficult to w
ork with only 2 allowed connections as in Administration mode. I am wonderi
ng what the best practice/m
ethod to use to go about accomplishing this task with the least disruption t
o the SQL server (as when I was about to do it I received an error message i
ndicating that SQL would break). Thanks for a quick response.Terminal services probably shouldn't be on the same box as SQL if you want
to run application mode, but the warning you are likely getting is not
neccesarily going to break SQL but since it was installed before App mode
Terminal services were enabled it thinks that this app will need to be
reinstalled to take advantage of Application mode terminal services.
I would purchase a copy of VMware (my favourite tool of all time
www.vmware.com ) and try it out. install a VM session, install Win2k OS into
it, enable term services in admin mode, install SQL and then flip it to app
mode. see what happens.
My guess is you will be fine, but the most impact would be a reboot so a
weekender would be a good idea, but this is a change I would recommend
against. it will adversely affect performance... my $0.02
Neil MacMurchy
"FColvais" <fcolvais1@.comcast.net> wrote in message
news:758C7A26-D01B-4228-89E0-3BB33205560F@.microsoft.com...
> I have a W2K server hosting SQL2K and have need to switch it to running in
Application mode as the number of remote SQL users has made it difficult to
work with only 2 allowed connections as in Administration mode. I am
wondering what the best practice/method to use to go about accomplishing
this task with the least disruption to the SQL server (as when I was about
to do it I received an error message indicating that SQL would break).
Thanks for a quick response.
Tuesday, February 14, 2012
Changing display-name in Database Mail
In our SQL Server 2005, VB.NET application, we want to send email to
outside clients, and have the email look like it came from the user who
sent it, meaning the Display Name and if possible, Email Address were
related to the person logged into our app.
>From my reading, it seems like we'd have to create a Database Mail
profile for each user. Is it instead possible to modify the Display
Name that sp_send_dbmail uses?
Thanks for any help.
I appreciate the response, but that's for older versions of SQL Server
as is way overkill for what I'm trying to achieve.
Tibor Karaszi wrote:
> You might want to check out xp_smtp_sendmail from www.sqldev.net instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
outside clients, and have the email look like it came from the user who
sent it, meaning the Display Name and if possible, Email Address were
related to the person logged into our app.
>From my reading, it seems like we'd have to create a Database Mail
profile for each user. Is it instead possible to modify the Display
Name that sp_send_dbmail uses?
Thanks for any help.
I appreciate the response, but that's for older versions of SQL Server
as is way overkill for what I'm trying to achieve.
Tibor Karaszi wrote:
> You might want to check out xp_smtp_sendmail from www.sqldev.net instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Subscribe to:
Posts (Atom)