Hi everybody,
I have couple of Access databases needed to be
converted to access/Sql Client/Server database, which is new to me. I
am trying to convert my access quries to Sql Views or Stored
Procedures. I have run into a problem currently when creating view in
Sql:
I need to create some columns based on conditions of other columns,
for example:
1. Column example 1:
IIf([Column1]=-1,[ListPrice]*[Volume],IIf([Column2]=-1,0,[ListPrice]*Volume]))
2. Column example 2:
IIf(Len([Product].[P_No])<10,"",SUBSTRING([Product].[P_No],6,5))
3.Column example 3:
IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
The above are actually the columns that I need to create.
I have these functions built in Access Queries, but not sure how to
build them in Sql views. Have tried different ways, but failed.
Any body knows how to do it?
Any help will be greatly appreciated!
Thanks in advance!"Shelley" <schow@.hersheys.com> wrote in message
news:e085e628.0402020838.450efcdc@.posting.google.com...
> 3.Column example 3:
> IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...
> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> > 3.Column example 3:
> > IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
> Take a look at the CASE statement in the online help.
Thanks Freddy.
This one works.
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 28, 2012
Problems in Creating Views in Sql
Hi everybody,
I have couple of Access databases needed to be
converted to access/Sql Client/Server database, which is new to me. I
am trying to convert my access quries to Sql Views or Stored
Procedures. I have run into a problem currently when creating view in
Sql:
I need to create some columns based on conditions of other columns,
for example:
1. Column example 1:
IIf([Column1]=-1,[ListPrice]*[Volume],IIf([Column2]=-1,0,[ListPrice]*Volume]))
2. Column example 2:
IIf(Len([Product].[P_No])<10,"",SUBSTRING([Product].[P_No],6,5))
3.Column example 3:
IIf([CustType]="D" ,([Field1]+[Field2])*[Volume],Field1*Vol
ume)
The above are actually the columns that I need to create.
I have these functions built in Access Queries, but not sure how to
build them in Sql views. Have tried different ways, but failed.
Any body knows how to do it?
Any help will be greatly appreciated!
Thanks in advance!"Shelley" <schow@.hersheys.com> wrote in message
news:e085e628.0402020838.450efcdc@.posting.google.com...
Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...
Thanks Freddy.
This one works.
I have couple of Access databases needed to be
converted to access/Sql Client/Server database, which is new to me. I
am trying to convert my access quries to Sql Views or Stored
Procedures. I have run into a problem currently when creating view in
Sql:
I need to create some columns based on conditions of other columns,
for example:
1. Column example 1:
IIf([Column1]=-1,[ListPrice]*[Volume],IIf([Column2]=-1,0,[ListPrice]*Volume]))
2. Column example 2:
IIf(Len([Product].[P_No])<10,"",SUBSTRING([Product].[P_No],6,5))
3.Column example 3:
IIf([CustType]="D" ,([Field1]+[Field2])*[Volume],Field1*Vol
ume)
The above are actually the columns that I need to create.
I have these functions built in Access Queries, but not sure how to
build them in Sql views. Have tried different ways, but failed.
Any body knows how to do it?
Any help will be greatly appreciated!
Thanks in advance!"Shelley" <schow@.hersheys.com> wrote in message
news:e085e628.0402020838.450efcdc@.posting.google.com...
quote:
> 3.Column example 3:
> IIf([CustType]="D" ,([Field1]+[Field2])*[Volume],Field1*Vol
ume)
Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...
quote:
> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> Take a look at the CASE statement in the online help.
Thanks Freddy.
This one works.
Monday, March 26, 2012
Problems executing Database Engine Tuning Advisor
We are trying to optimize one of our databases (SQL 2005) and we are running analysis on some of the stored procedures. However, the Database Engine Tuning Advisor is resulting in either
Event does not reference any tables.
Or
Statement does not reference any tables.
Anyone have any idea why this would happen?
PLease explain the process how you are trying analyze the SP using DTA?
ALso may try from another machine to see whether its a problem of tools on this machine.
|||Thanks for replying. Sorry I lost track of the thread. Essentially, all we are doing is executing a stored procedure in a query window and analyzing that directly.Also, if we log the execution and use the trace log to do the analysis we get the same.
It has been tried in multiple machines. It is weird since there's no errors with the stored procedures. Thanks again.|||I have the same problem, a lot of those errors, plus after a while (from 25 to 60 minutes) the DTA exits with an "unexpected error".|||this happens against any stored procedure that DTA analyzes, why?
Problems executing Database Engine Tuning Advisor
We are trying to optimize one of our databases (SQL 2005) and we are running analysis on some of the stored procedures. However, the Database Engine Tuning Advisor is resulting in either
Event does not reference any tables.
Or
Statement does not reference any tables.
Anyone have any idea why this would happen?
PLease explain the process how you are trying analyze the SP using DTA?
ALso may try from another machine to see whether its a problem of tools on this machine.
|||Thanks for replying. Sorry I lost track of the thread. Essentially, all we are doing is executing a stored procedure in a query window and analyzing that directly.Also, if we log the execution and use the trace log to do the analysis we get the same.
It has been tried in multiple machines. It is weird since there's no errors with the stored procedures. Thanks again.|||I have the same problem, a lot of those errors, plus after a while (from 25 to 60 minutes) the DTA exits with an "unexpected error".|||this happens against any stored procedure that DTA analyzes, why?
Problems executing Database Engine Tuning Advisor
We are trying to optimize one of our databases (SQL 2005) and we are running analysis on some of the stored procedures. However, the Database Engine Tuning Advisor is resulting in either
Event does not reference any tables.
Or
Statement does not reference any tables.
Anyone have any idea why this would happen?
PLease explain the process how you are trying analyze the SP using DTA?
ALso may try from another machine to see whether its a problem of tools on this machine.
|||Thanks for replying. Sorry I lost track of the thread. Essentially, all we are doing is executing a stored procedure in a query window and analyzing that directly.Also, if we log the execution and use the trace log to do the analysis we get the same.
It has been tried in multiple machines. It is weird since there's no errors with the stored procedures. Thanks again.|||I have the same problem, a lot of those errors, plus after a while (from 25 to 60 minutes) the DTA exits with an "unexpected error".
|||this happens against any stored procedure that DTA analyzes, why?
sql
Problems deleting transaction log files older than x days
Hi,
I have a problem with maintainance plans an deleting
transaction log files older than x days. I have made one
maintainance plan for databases and that one works.
But the one made for the transaction log doesn't delete
the old files. They remain and aren't deleted
automatically.
brgds
PeterBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Junell" <peter.junell@.silverhuset.se> wrote in message
news:9b0601c405c2$d5967c20$a101280a@.phx.gbl...
> Hi,
> I have a problem with maintainance plans an deleting
> transaction log files older than x days. I have made one
> maintainance plan for databases and that one works.
> But the one made for the transaction log doesn't delete
> the old files. They remain and aren't deleted
> automatically.
> brgds
> Peter
I have a problem with maintainance plans an deleting
transaction log files older than x days. I have made one
maintainance plan for databases and that one works.
But the one made for the transaction log doesn't delete
the old files. They remain and aren't deleted
automatically.
brgds
PeterBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Junell" <peter.junell@.silverhuset.se> wrote in message
news:9b0601c405c2$d5967c20$a101280a@.phx.gbl...
> Hi,
> I have a problem with maintainance plans an deleting
> transaction log files older than x days. I have made one
> maintainance plan for databases and that one works.
> But the one made for the transaction log doesn't delete
> the old files. They remain and aren't deleted
> automatically.
> brgds
> Peter
Friday, March 23, 2012
Problems connecting to SQL server on W2K3 from Win 2000 workstatio
We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
We are trying to access our databases using Enterprise manager from our
machines in the office using windows authentication. We have no problems
from Windows XP machines (using both TCP/IP and Named Pipes). I can connect
to the server using the SA account (TCP/IP) and Named Pipes from a windows
2000 workstation, but cannot access the SQL server from Windows 2000 machine
s
using TCP/IP.
When we try to log in using Windows 2000 we get "login failed for user
null". From xp machines it goes straight in.
Testing that I have done:
? Set up an alias to the Windows 2003 server name (SQL Server Client Netwo
rk
Utility). No luck
? I can map a folder to the machine using net use command
? I am in the same AD domain.
? The clients are connecting to the server using integrated security (not
allowing SQL authentication)
I cannot understand what’s going on; do you have any pointers on where to
start looking?
ThanksSame here, except we can not to connect from win98 machines. From XP
everything looks fine. Before everything worked for about 1,5 years and
only today morning crashed. Btw, connection to (local) on server machine
works fine. Nobody did any changes in TCP/IP nor in DSN settings.|||This is because you are using Windows authentication without a domain setup.
If you aren't using a domain you shouldn't use windows authentication
because it tends not to work, use SQL Authentication and you'll always
connect. The problem is mainly that the users you are signed in to the W2k
box doesn't match a user on the W2k3 box, or the user isn't passed properly
to SQL, or if you really want to setup Windows authentication use to put the
workstations that servers into a domain..
Thanks,
Mike Bulava, MCDBA
"Mike Allen" <MikeAllen@.discussions.microsoft.com> wrote in message
news:071EE849-F61A-4729-8598-67974D2D5269@.microsoft.com...
> We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
> We are trying to access our databases using Enterprise manager from our
> machines in the office using windows authentication. We have no problems
> from Windows XP machines (using both TCP/IP and Named Pipes). I can
> connect
> to the server using the SA account (TCP/IP) and Named Pipes from a windows
> 2000 workstation, but cannot access the SQL server from windows 2000
> machines
> using TCP/IP.
> When we try to log in using Windows 2000 we get "login failed for user
> null". From xp machines it goes straight in.
> Testing that I have done:
> . Set up an alias to the Windows 2003 server name (SQL Server Client
> Network
> Utility). No luck
> . I can map a folder to the machine using net use command
> . I am in the same AD domain.
> . The clients are connecting to the server using integrated security (not
> allowing SQL authentication)
> I cannot understand what's going on; do you have any pointers on where to
> start looking?
> Thanks
>
>
We are trying to access our databases using Enterprise manager from our
machines in the office using windows authentication. We have no problems
from Windows XP machines (using both TCP/IP and Named Pipes). I can connect
to the server using the SA account (TCP/IP) and Named Pipes from a windows
2000 workstation, but cannot access the SQL server from Windows 2000 machine
s
using TCP/IP.
When we try to log in using Windows 2000 we get "login failed for user
null". From xp machines it goes straight in.
Testing that I have done:
? Set up an alias to the Windows 2003 server name (SQL Server Client Netwo
rk
Utility). No luck
? I can map a folder to the machine using net use command
? I am in the same AD domain.
? The clients are connecting to the server using integrated security (not
allowing SQL authentication)
I cannot understand what’s going on; do you have any pointers on where to
start looking?
ThanksSame here, except we can not to connect from win98 machines. From XP
everything looks fine. Before everything worked for about 1,5 years and
only today morning crashed. Btw, connection to (local) on server machine
works fine. Nobody did any changes in TCP/IP nor in DSN settings.|||This is because you are using Windows authentication without a domain setup.
If you aren't using a domain you shouldn't use windows authentication
because it tends not to work, use SQL Authentication and you'll always
connect. The problem is mainly that the users you are signed in to the W2k
box doesn't match a user on the W2k3 box, or the user isn't passed properly
to SQL, or if you really want to setup Windows authentication use to put the
workstations that servers into a domain..
Thanks,
Mike Bulava, MCDBA
"Mike Allen" <MikeAllen@.discussions.microsoft.com> wrote in message
news:071EE849-F61A-4729-8598-67974D2D5269@.microsoft.com...
> We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
> We are trying to access our databases using Enterprise manager from our
> machines in the office using windows authentication. We have no problems
> from Windows XP machines (using both TCP/IP and Named Pipes). I can
> connect
> to the server using the SA account (TCP/IP) and Named Pipes from a windows
> 2000 workstation, but cannot access the SQL server from windows 2000
> machines
> using TCP/IP.
> When we try to log in using Windows 2000 we get "login failed for user
> null". From xp machines it goes straight in.
> Testing that I have done:
> . Set up an alias to the Windows 2003 server name (SQL Server Client
> Network
> Utility). No luck
> . I can map a folder to the machine using net use command
> . I am in the same AD domain.
> . The clients are connecting to the server using integrated security (not
> allowing SQL authentication)
> I cannot understand what's going on; do you have any pointers on where to
> start looking?
> Thanks
>
>
Wednesday, March 21, 2012
Problems connecting to SQL server on W2K3 from Win 2000 workstatio
We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
We are trying to access our databases using Enterprise manager from our
machines in the office using windows authentication. We have no problems
from windows XP machines (using both TCP/IP and Named Pipes). I can connect
to the server using the SA account (TCP/IP) and Named Pipes from a windows
2000 workstation, but cannot access the SQL server from windows 2000 machines
using TCP/IP.
When we try to log in using windows 2000 we get "login failed for user
null". From xp machines it goes straight in.
Testing that I have done:
?Set up an alias to the Windows 2003 server name (SQL Server Client Network
Utility). No luck
?I can map a folder to the machine using net use command
?I am in the same AD domain.
?The clients are connecting to the server using integrated security (not
allowing SQL authentication)
I cannot understand what’s going on; do you have any pointers on where to
start looking?
Thanks
Same here, except we can not to connect from win98 machines. From XP
everything looks fine. Before everything worked for about 1,5 years and
only today morning crashed. Btw, connection to (local) on server machine
works fine. Nobody did any changes in TCP/IP nor in DSN settings.
|||This is because you are using Windows authentication without a domain setup.
If you aren't using a domain you shouldn't use windows authentication
because it tends not to work, use SQL Authentication and you'll always
connect. The problem is mainly that the users you are signed in to the W2k
box doesn't match a user on the W2k3 box, or the user isn't passed properly
to SQL, or if you really want to setup Windows authentication use to put the
workstations that servers into a domain..
Thanks,
Mike Bulava, MCDBA
"Mike Allen" <MikeAllen@.discussions.microsoft.com> wrote in message
news:071EE849-F61A-4729-8598-67974D2D5269@.microsoft.com...
> We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
> We are trying to access our databases using Enterprise manager from our
> machines in the office using windows authentication. We have no problems
> from windows XP machines (using both TCP/IP and Named Pipes). I can
> connect
> to the server using the SA account (TCP/IP) and Named Pipes from a windows
> 2000 workstation, but cannot access the SQL server from windows 2000
> machines
> using TCP/IP.
> When we try to log in using windows 2000 we get "login failed for user
> null". From xp machines it goes straight in.
> Testing that I have done:
> . Set up an alias to the Windows 2003 server name (SQL Server Client
> Network
> Utility). No luck
> . I can map a folder to the machine using net use command
> . I am in the same AD domain.
> . The clients are connecting to the server using integrated security (not
> allowing SQL authentication)
> I cannot understand what's going on; do you have any pointers on where to
> start looking?
> Thanks
>
>
We are trying to access our databases using Enterprise manager from our
machines in the office using windows authentication. We have no problems
from windows XP machines (using both TCP/IP and Named Pipes). I can connect
to the server using the SA account (TCP/IP) and Named Pipes from a windows
2000 workstation, but cannot access the SQL server from windows 2000 machines
using TCP/IP.
When we try to log in using windows 2000 we get "login failed for user
null". From xp machines it goes straight in.
Testing that I have done:
?Set up an alias to the Windows 2003 server name (SQL Server Client Network
Utility). No luck
?I can map a folder to the machine using net use command
?I am in the same AD domain.
?The clients are connecting to the server using integrated security (not
allowing SQL authentication)
I cannot understand what’s going on; do you have any pointers on where to
start looking?
Thanks
Same here, except we can not to connect from win98 machines. From XP
everything looks fine. Before everything worked for about 1,5 years and
only today morning crashed. Btw, connection to (local) on server machine
works fine. Nobody did any changes in TCP/IP nor in DSN settings.
|||This is because you are using Windows authentication without a domain setup.
If you aren't using a domain you shouldn't use windows authentication
because it tends not to work, use SQL Authentication and you'll always
connect. The problem is mainly that the users you are signed in to the W2k
box doesn't match a user on the W2k3 box, or the user isn't passed properly
to SQL, or if you really want to setup Windows authentication use to put the
workstations that servers into a domain..
Thanks,
Mike Bulava, MCDBA
"Mike Allen" <MikeAllen@.discussions.microsoft.com> wrote in message
news:071EE849-F61A-4729-8598-67974D2D5269@.microsoft.com...
> We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
> We are trying to access our databases using Enterprise manager from our
> machines in the office using windows authentication. We have no problems
> from windows XP machines (using both TCP/IP and Named Pipes). I can
> connect
> to the server using the SA account (TCP/IP) and Named Pipes from a windows
> 2000 workstation, but cannot access the SQL server from windows 2000
> machines
> using TCP/IP.
> When we try to log in using windows 2000 we get "login failed for user
> null". From xp machines it goes straight in.
> Testing that I have done:
> . Set up an alias to the Windows 2003 server name (SQL Server Client
> Network
> Utility). No luck
> . I can map a folder to the machine using net use command
> . I am in the same AD domain.
> . The clients are connecting to the server using integrated security (not
> allowing SQL authentication)
> I cannot understand what's going on; do you have any pointers on where to
> start looking?
> Thanks
>
>
Tuesday, March 20, 2012
Problems after SP4 - 100%CPU time
We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of the
databases runs at 100% CPU when ever a query it made. Nothing else has
changed! Before SP4 the queries are quite small and took only 1-2 seconds to
run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
If I copy the database to another server with SP3 on it, it works fine. If I
then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds.
The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN with
RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
temp, msdb, etc And monitoring them there seems to be no disk problems,
queues are short, etc
Can anyone help? either hot fix, uninstall (don't have SP3 backup of master,
etc), sp changes.
Cheers
nigelDid you run update_statistics , reindex database.
Maybe recompile stored procedures!
Greetz
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Nigel" wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel|||Thanks.
I've run these manually and as part of the daily maintenance plan. The
database also has Auto update and create statistics.
At present the part where it all goes mad does not use any SP, but is called
via a JDBC Java client directly.
Task Manager view can be seen here.
http://www.callacomp.co.uk/sql/orion%20cpu.jpg
Performance monitor SP3 (live)
http://www.callacomp.co.uk/sql/sp3mon.jpg
Performance monitor SP4(test machine)
http://www.callacomp.co.uk/sql/sp4mon.jpg
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Hate_orphaned_users" wrote:
> Did you run update_statistics , reindex database.
> Maybe recompile stored procedures!
> Greetz|||Nigel wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel
Have you reviewed the execution plan for one of the problem queries?
Did you update statistics (manually, not automatically) when you moved
the database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It sounds like the query plan has changed so you may need to tune or change
that query slightly so that it gets a better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:85602962-D3D7-4951-8E08-4C70D67C4855@.microsoft.com...
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of
> the
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
> to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
> I
> then install SP4 on this machine it now runs at 100% CPU for 30-50
> seconds.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN
> with
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of
> master,
> etc), sp changes.
> Cheers
> nigel|||I've updated statistics -sp_updatestats
I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
The process uses no SP all queries are suppled from the client using JDBC,
doesn't this mean that the query plan is updated as and when. Certainly afte
r
a reboot? Or am I wrong?
I've run PSSDiag but I'm no expert at looking at the traces files. Either a
little help or lots of reading to do?
Regatds
NIgel|||It may very well generate a new query plan each and every time you execute
it. But that does not mean it will generate a good one or even a different
one. If it generated a bad plan the last time you executed it and the
conditions or parameters have not changed chances are it will generate a bad
one again and again and again. Some times the conditions are such or the
data is such that the optimizer thinks it has the right plan but ends up
with a bad one for your conditions. By changing the query slightly ( an IN
to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
and in this case hopefully better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
> I've updated statistics -sp_updatestats
> I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
> The process uses no SP all queries are suppled from the client using JDBC,
> doesn't this mean that the query plan is updated as and when. Certainly
> after
> a reboot? Or am I wrong?
> I've run PSSDiag but I'm no expert at looking at the traces files. Either
> a
> little help or lots of reading to do?
> Regatds
> NIgel|||OK Cheers, I start looking into this. So does this mean that SP4 has changed
how query plans are calculated?
--
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Andrew J. Kelly" wrote:
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a b
ad
> one again and again and again. Some times the conditions are such or the
> data is such that the optimizer thinks it has the right plan but ends up
> with a bad one for your conditions. By changing the query slightly ( an IN
> to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
> and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
>|||Every service pack and or edition has the potential to change query plans to
some extent and Sp4 is no exception. Most of the time it is due to fixing
bugs and occasionally a query plan may be affected by it. This does not mean
all of them will change.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:0DD973D6-5D6C-4633-B533-350115E0477C@.microsoft.com...[vbcol=seagreen]
> OK Cheers, I start looking into this. So does this mean that SP4 has
> changed
> how query plans are calculated?
> --
> Nigel
> Underpaid and working for the NHS.
> So help me and save lives!
>
> "Andrew J. Kelly" wrote:
>|||Hi, you need at least apply postSp hotfix (2187) and check again.
Regards,
Oleg.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOSI3EpPHHA.1252@.TK2MSFTNGP02.phx.gbl...
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a
> bad one again and again and again. Some times the conditions are such or
> the data is such that the optimizer thinks it has the right plan but ends
> up with a bad one for your conditions. By changing the query slightly ( an
> IN to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a
> different and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
> "Nigel" <Nigel@.discussions.microsoft.com> wrote in message
> news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
>
databases runs at 100% CPU when ever a query it made. Nothing else has
changed! Before SP4 the queries are quite small and took only 1-2 seconds to
run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
If I copy the database to another server with SP3 on it, it works fine. If I
then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds.
The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN with
RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
temp, msdb, etc And monitoring them there seems to be no disk problems,
queues are short, etc
Can anyone help? either hot fix, uninstall (don't have SP3 backup of master,
etc), sp changes.
Cheers
nigelDid you run update_statistics , reindex database.
Maybe recompile stored procedures!
Greetz
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Nigel" wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel|||Thanks.
I've run these manually and as part of the daily maintenance plan. The
database also has Auto update and create statistics.
At present the part where it all goes mad does not use any SP, but is called
via a JDBC Java client directly.
Task Manager view can be seen here.
http://www.callacomp.co.uk/sql/orion%20cpu.jpg
Performance monitor SP3 (live)
http://www.callacomp.co.uk/sql/sp3mon.jpg
Performance monitor SP4(test machine)
http://www.callacomp.co.uk/sql/sp4mon.jpg
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Hate_orphaned_users" wrote:
> Did you run update_statistics , reindex database.
> Maybe recompile stored procedures!
> Greetz|||Nigel wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel
Have you reviewed the execution plan for one of the problem queries?
Did you update statistics (manually, not automatically) when you moved
the database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It sounds like the query plan has changed so you may need to tune or change
that query slightly so that it gets a better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:85602962-D3D7-4951-8E08-4C70D67C4855@.microsoft.com...
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of
> the
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
> to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
> I
> then install SP4 on this machine it now runs at 100% CPU for 30-50
> seconds.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN
> with
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of
> master,
> etc), sp changes.
> Cheers
> nigel|||I've updated statistics -sp_updatestats
I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
The process uses no SP all queries are suppled from the client using JDBC,
doesn't this mean that the query plan is updated as and when. Certainly afte
r
a reboot? Or am I wrong?
I've run PSSDiag but I'm no expert at looking at the traces files. Either a
little help or lots of reading to do?
Regatds
NIgel|||It may very well generate a new query plan each and every time you execute
it. But that does not mean it will generate a good one or even a different
one. If it generated a bad plan the last time you executed it and the
conditions or parameters have not changed chances are it will generate a bad
one again and again and again. Some times the conditions are such or the
data is such that the optimizer thinks it has the right plan but ends up
with a bad one for your conditions. By changing the query slightly ( an IN
to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
and in this case hopefully better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
> I've updated statistics -sp_updatestats
> I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
> The process uses no SP all queries are suppled from the client using JDBC,
> doesn't this mean that the query plan is updated as and when. Certainly
> after
> a reboot? Or am I wrong?
> I've run PSSDiag but I'm no expert at looking at the traces files. Either
> a
> little help or lots of reading to do?
> Regatds
> NIgel|||OK Cheers, I start looking into this. So does this mean that SP4 has changed
how query plans are calculated?
--
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Andrew J. Kelly" wrote:
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a b
ad
> one again and again and again. Some times the conditions are such or the
> data is such that the optimizer thinks it has the right plan but ends up
> with a bad one for your conditions. By changing the query slightly ( an IN
> to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
> and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
>|||Every service pack and or edition has the potential to change query plans to
some extent and Sp4 is no exception. Most of the time it is due to fixing
bugs and occasionally a query plan may be affected by it. This does not mean
all of them will change.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:0DD973D6-5D6C-4633-B533-350115E0477C@.microsoft.com...[vbcol=seagreen]
> OK Cheers, I start looking into this. So does this mean that SP4 has
> changed
> how query plans are calculated?
> --
> Nigel
> Underpaid and working for the NHS.
> So help me and save lives!
>
> "Andrew J. Kelly" wrote:
>|||Hi, you need at least apply postSp hotfix (2187) and check again.
Regards,
Oleg.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOSI3EpPHHA.1252@.TK2MSFTNGP02.phx.gbl...
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a
> bad one again and again and again. Some times the conditions are such or
> the data is such that the optimizer thinks it has the right plan but ends
> up with a bad one for your conditions. By changing the query slightly ( an
> IN to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a
> different and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
> "Nigel" <Nigel@.discussions.microsoft.com> wrote in message
> news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
>
Problems Accessing SQL after going from SQL 2000 to Mgmt Studio SQL 2005
I installed Mgmt Studio and SQL 2005 on a new server - I imported my complete old SQL 2000 databases - then shut down my Original SQL 2000 server and used that Idenitical IP as the Mgmt Studio SQL 2005's IP
I am using Linux Jakarta Tomcat v5.0.27 and all SQL references are referring only to the IP number of ther server and not using DNS -
Originally I was getting an error when my system tried to access the SQL stating that it was an untrusted connection - I then realized that the IP number referred to was not the primary IP and I changed that so the it was.
Both the Linux and SQL 2005 are now using network IPs and then I got a message that the password that I was using was not strong enough so I changed that as well - then all my logging dissappeared
When I try to connect now there is no indication in any log (SQL nor Windows) that I am attempting to connect - the website just hangs for like 3 minutes and fails the login stating that the username/password is incorrect (or it could not verify it) even though I changed nothing on the Tomcat side and it was logging before the IP werent matching.
Is there a significance difference between SQL 2000 and SQL 2005 that will not allow my Tomcat to connect?
Few Addl Notes
I can ping to and from each server
I can telnet from Linux to SQL 2005
Linux of course is not part of my Windows 2003 Domain but it is on the same network
Any and all help would be greatly appreciated
Did you already enable remote connections on the SQL Server 2005 ? See more information in my site in the Screencast section.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Yes - I have SQL 2005 Standard Edition which listens over TCPIP but I changed it to Use both TCPIP and pipes just in case - no luck - I also have already made sure that both Authentications are allowed (Windows and SQL)
Here is an error I am getting
Date 11/14/2006 11:58:00 AM
Log Windows NT (Application)
Source MSSQLSERVER
Category Logon
Event 3221243928
Computer SLATE2
Message
Login failed for user 'swamy'. [CLIENT: 207.145.184.148]
Now I can get ODBC connections with this response from an XP Client
Date 11/14/2006 12:47:14 PM
Log SQL Server (Current - 11/14/2006 12:47:00 PM)
Source Logon
Message
Login succeeded for user 'BESTREG\Swamy'. Connection: trusted. [CLIENT: 207.145.184.93]
-Jens.
Monday, March 12, 2012
Problem:MS-Access.adp with MSDE link to csv file
Hi. I'm coming from a background of developing mdbs and am trying to migrate
our databases to adps running atop MSDE 2000. However, I've encountered a
problem while trying to do analogous things to what I've done before with
mdbs...for example:
our databases to adps running atop MSDE 2000. However, I've encountered a
problem while trying to do analogous things to what I've done before with
mdbs...for example:
-Linking to a csv file on another machine: I am able to establish a link
uning the 'Link Table Wizard' that shows up as a new view. However, upon
openning the view I see only a single column (left-most).
What am I missing here?
JamesMaybe I can help, drop me a email direct, I do this sort of thing for a
living
Regards
Andrew
JimJimJimJim wrote:
> Hi. I'm coming from a background of developing mdbs and am trying to migrate
> our databases to adps running atop MSDE 2000. However, I've encountered a
> problem while trying to do analogous things to what I've done before with
> mdbs...for example:
> -Linking to a csv file on another machine: I am able to establish a link
> uning the 'Link Table Wizard' that shows up as a new view. However, upon
> openning the view I see only a single column (left-most).
> What am I missing here?
> James
>
>
Friday, March 9, 2012
PROBLEM: Database Mainence plan "All user Databases" missing database
If I select "All user Databases" and click T-SQL, 3 our of 4
databases are listed- and the missing database has not been backed up
via the plan. All databases are listed, however, when I select
"Selected Databases"- they are shown in the drop downbox.
This is for SQL 2005 Service Pack 2 with Hotfix 3175.
My guess: The missing database was restored and not restored to
overwrite an existing database. Since the CREATE DATABASE command was
not executed, maybe there is system info in master that doesn't get
set'
Help please. Thanks
Erik> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
No, that should not be the reason. The same meta-data would exist regardless of how the database was
created (CREATE DATABASE, attach or by RESTORE). Perhaps the database is in 70 compatibility mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:25261d77-880b-4b83-9e9a-94866e14af6e@.e6g2000prf.googlegroups.com...
> If I select "All user Databases" and click T-SQL, 3 our of 4
> databases are listed- and the missing database has not been backed up
> via the plan. All databases are listed, however, when I select
> "Selected Databases"- they are shown in the drop downbox.
> This is for SQL 2005 Service Pack 2 with Hotfix 3175.
> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
> Help please. Thanks
> Erik|||Thanks. No, i've seent that 7.0 Compat mode issue before, it is
actually set to 9.0.|||Do the plan do both db and log backup? Is this missing backup a db or a log backup? Perhaps there's
a difference in the recovery model setting.
If not, then I'd check other database options, just doing a quick visual compare:
SELECT * FROM sys.databases
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:42c70b00-ac98-4d82-b150-69d1dbc3bf29@.i29g2000prf.googlegroups.com...
> Thanks. No, i've seent that 7.0 Compat mode issue before, it is
> actually set to 9.0.|||Thanks for suggstions. I found that Auto Update Statistics was
different and I turned it on and also set to FULL RECOVER MODE and it
got listed in View t-SQL preview !!
THAN I turned everything back to the previous settings for my missing
databses and the All Databases support still was fixed and in tact! t-
SQL Preview showed my previously missing database.
Updating sys.databases seems to fix the problem. maybe there is some
trigger in the background or something.
Thank you for all of your help.
Erik
databases are listed- and the missing database has not been backed up
via the plan. All databases are listed, however, when I select
"Selected Databases"- they are shown in the drop downbox.
This is for SQL 2005 Service Pack 2 with Hotfix 3175.
My guess: The missing database was restored and not restored to
overwrite an existing database. Since the CREATE DATABASE command was
not executed, maybe there is system info in master that doesn't get
set'
Help please. Thanks
Erik> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
No, that should not be the reason. The same meta-data would exist regardless of how the database was
created (CREATE DATABASE, attach or by RESTORE). Perhaps the database is in 70 compatibility mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:25261d77-880b-4b83-9e9a-94866e14af6e@.e6g2000prf.googlegroups.com...
> If I select "All user Databases" and click T-SQL, 3 our of 4
> databases are listed- and the missing database has not been backed up
> via the plan. All databases are listed, however, when I select
> "Selected Databases"- they are shown in the drop downbox.
> This is for SQL 2005 Service Pack 2 with Hotfix 3175.
> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
> Help please. Thanks
> Erik|||Thanks. No, i've seent that 7.0 Compat mode issue before, it is
actually set to 9.0.|||Do the plan do both db and log backup? Is this missing backup a db or a log backup? Perhaps there's
a difference in the recovery model setting.
If not, then I'd check other database options, just doing a quick visual compare:
SELECT * FROM sys.databases
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:42c70b00-ac98-4d82-b150-69d1dbc3bf29@.i29g2000prf.googlegroups.com...
> Thanks. No, i've seent that 7.0 Compat mode issue before, it is
> actually set to 9.0.|||Thanks for suggstions. I found that Auto Update Statistics was
different and I turned it on and also set to FULL RECOVER MODE and it
got listed in View t-SQL preview !!
THAN I turned everything back to the previous settings for my missing
databses and the All Databases support still was fixed and in tact! t-
SQL Preview showed my previously missing database.
Updating sys.databases seems to fix the problem. maybe there is some
trigger in the background or something.
Thank you for all of your help.
Erik
Subscribe to:
Posts (Atom)