Auto field calculation

Hi,
I would like to calculate field based on the entry in another in SQL server
2000 i.e.; field3 = field2 * field1
field1 field2 field3
10 2.5 25.00( =sum(field1*field2))
How do I go about this in the SQL DB itself, can it be done within field3?
Regards
SimonLook up "computed columns" in Books Online.
It's simple:
create table <table name>
(
Column1 <data type>
,Column2 <data type>
,Column3 as Column1 * Column2
)
go
or:
alter table <table name>
add Column3 as Column1 * Column2
go
ML
http://milambda.blogspot.com/

auto backup jobs wont run - not in single user mode

Hello!
I am trying to get a SQL server that was configured by someone else up
and running the backup and maintainance jobs from within SQL server
2000. when the job runs it backs up all but 3 of the databases, the 3
it doesnt is our ASDB (our arcserv) database, master and model.
the error that shows for the asdb database is ..
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
The one that shows for the others is this...
12] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[13] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Database state cannot be changed
while other users are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
[14] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Can anyone advise me on how to resolve this issue?
Thanks in advance!
/ehren
Wingnut wrote:
> Hello!
> I am trying to get a SQL server that was configured by someone else up
> and running the backup and maintainance jobs from within SQL server
> 2000. when the job runs it backs up all but 3 of the databases, the 3
> it doesnt is our ASDB (our arcserv) database, master and model.
> the error that shows for the asdb database is ..
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> The one that shows for the others is this...
>
> 12] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [13] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Database state cannot be changed
> while other users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
> failed.
> [14] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Can anyone advise me on how to resolve this issue?
> Thanks in advance!
> /ehren
>
In your maintenance plan, turn off the "repair problems" option...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks, I will give that a try.
On Jan 29, 9:38 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> Wingnut wrote:
>
>
>
>
>
>
>
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com- Hide quoted text -- Show quoted text -
sql

auto backup jobs wont run - not in single user mode

Hello!
I am trying to get a SQL server that was configured by someone else up
and running the backup and maintainance jobs from within SQL server
2000. when the job runs it backs up all but 3 of the databases, the 3
it doesnt is our ASDB (our arcserv) database, master and model.
the error that shows for the asdb database is ..
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
The one that shows for the others is this...
12] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[13] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Database state cannot be changed
while other users are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd
failed.
[14] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Can anyone advise me on how to resolve this issue?
Thanks in advance!
/ehrenWingnut wrote:
> Hello!
> I am trying to get a SQL server that was configured by someone else up
> and running the backup and maintainance jobs from within SQL server
> 2000. when the job runs it backs up all but 3 of the databases, the 3
> it doesnt is our ASDB (our arcserv) database, master and model.
> the error that shows for the asdb database is ..
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> The one that shows for the others is this...
>
> 12] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed
.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [13] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Database state cannot be chang
ed
> while other users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand
> failed.
> [14] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed
.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Can anyone advise me on how to resolve this issue?
> Thanks in advance!
> /ehren
>
In your maintenance plan, turn off the "repair problems" option...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I will give that a try.
On Jan 29, 9:38 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> Wingnut wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com- Hide quoted text -- Show quoted text -

Authentication Issue - Cannot connect from one wkstation

Hi,
I am having trouble using windows authentication to connect to a SQL Server
2000 instance on a Windows 2003 Server. I can connect with my domain account
from Computer A but not from Computer B. Other authentication requirements
to the server seem to be fine from Computer B eg I use my domain account to
logon to Computer B and I can remote to the server from Computer B.
I was having some issues with my profile on Computer B so I wonder if this
has affected some cached credentials for SQL Server or something else. Any
help with this would be most appreciated.
Thanks, Tad
Hi,
which error do you get ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hi,
In SQL Query Analyzer:
Unable to connect to server XXXX:
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
In MS SQL Server Mgmt Studio:
Cannot connect to XXXXX.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 2)
In SQL Enterprise Manager (while trying to register server):
XXXX - SQL Server does not exist or access denied.
ConnectionOpen (Connect())
"Jens" wrote:

> Hi,
> which error do you get ?
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||Are the computers in the same domain ? Which authentication method are
you using ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||yes, same domain and Windows authentication
"Jens" wrote:

> Are the computers in the same domain ? Which authentication method are
> you using ?
> Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||Tadwick wrote:
> Hi,
> I am having trouble using windows authentication to connect to a SQL Server
> 2000 instance on a Windows 2003 Server. I can connect with my domain account
> from Computer A but not from Computer B. Other authentication requirements
> to the server seem to be fine from Computer B eg I use my domain account to
> logon to Computer B and I can remote to the server from Computer B.
> I was having some issues with my profile on Computer B so I wonder if this
> has affected some cached credentials for SQL Server or something else. Any
> help with this would be most appreciated.
> Thanks, Tad
Are you sure it's an authentication error and not something network
related? Can you even SEE the server from this workstation? Can you
ping it by hostname AND by IP address? Can you connect to SQL via the
IP address?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy,
Your note just prompted me to the solution. It was not authentication - it
was because I used a custom instance name and was so used to it being there
by default when I open a client side tool that I forgot to specify when my
profile changed on Computer B. Embarassing, but true.
Thanks to you and Jens for getting me there.
Tad
"Tracy McKibben" wrote:

> Tadwick wrote:
> Are you sure it's an authentication error and not something network
> related? Can you even SEE the server from this workstation? Can you
> ping it by hostname AND by IP address? Can you connect to SQL via the
> IP address?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Authentication Issue - Cannot connect from one wkstation

Hi,
I am having trouble using windows authentication to connect to a SQL Server
2000 instance on a Windows 2003 Server. I can connect with my domain accoun
t
from Computer A but not from Computer B. Other authentication requirements
to the server seem to be fine from Computer B eg I use my domain account to
logon to Computer B and I can remote to the server from Computer B.
I was having some issues with my profile on Computer B so I wonder if this
has affected some cached credentials for SQL Server or something else. Any
help with this would be most appreciated.
Thanks, TadHi,
which error do you get ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Hi,
In SQL Query Analyzer:
Unable to connect to server XXXX:
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or
access denied.
In MS SQL Server Mgmt Studio:
Cannot connect to XXXXX.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 2)
In SQL Enterprise Manager (while trying to register server):
XXXX - SQL Server does not exist or access denied.
ConnectionOpen (Connect())
"Jens" wrote:

> Hi,
> which error do you get ?
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Are the computers in the same domain ? Which authentication method are
you using ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||yes, same domain and Windows authentication
"Jens" wrote:

> Are the computers in the same domain ? Which authentication method are
> you using ?
> Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Tadwick wrote:
> Hi,
> I am having trouble using windows authentication to connect to a SQL Serve
r
> 2000 instance on a Windows 2003 Server. I can connect with my domain acco
unt
> from Computer A but not from Computer B. Other authentication requirement
s
> to the server seem to be fine from Computer B eg I use my domain account t
o
> logon to Computer B and I can remote to the server from Computer B.
> I was having some issues with my profile on Computer B so I wonder if this
> has affected some cached credentials for SQL Server or something else. An
y
> help with this would be most appreciated.
> Thanks, Tad
Are you sure it's an authentication error and not something network
related? Can you even SEE the server from this workstation? Can you
ping it by hostname AND by IP address? Can you connect to SQL via the
IP address?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Your note just prompted me to the solution. It was not authentication - it
was because I used a custom instance name and was so used to it being there
by default when I open a client side tool that I forgot to specify when my
profile changed on Computer B. Embarassing, but true.
Thanks to you and Jens for getting me there.
Tad
"Tracy McKibben" wrote:

> Tadwick wrote:
> Are you sure it's an authentication error and not something network
> related? Can you even SEE the server from this workstation? Can you
> ping it by hostname AND by IP address? Can you connect to SQL via the
> IP address?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>sql