check query status

Is it possible to create an SSIS package that checks for a running Query on my SQL db?

I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?

There is nothing specific in SSIS that can give you that info; but perhaps you can put a query that gives you that inside of an execute sql task...|||

I want to use SSIS to create the package and use the SQL Task in the package, But how can I 'ping' the sql server to verify the sql query is still running or not? That's the portion I'm stuck on.

What would that query look like? Can this even be done?

|||Your question is one of a Transact-SQL nature and as such should probably be asked over in that forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Come back here if you need help implementing the resulting query inside SSIS.|||You can use sp_who or sys.sysprocesses to get lists of running processes. sys.sysprocesses would be easier to work with, if you are on 2005.

Check periodically if SQL server is running

Hi,

I have my SQL server installed and running on a windows 2003 server. Sometimes, the server stops unexpectedly. I want to know if there is any mechanism to alert as and when the SQL server or SQL server agent goes down.

I guess everything is a windows service. Can we have a mechanism to test this service from a script and send an alert mail when the server goes down? How can this be done from a script?

Thanks in advance

-SudhakarIf you have two servers, you could have each server send periodic commands to the other to see if they are awake.
Or you could schedule a job to e-mail you at regular intervals and when you don't get a notification you'd know something was wrong.

...or you could fix the f'ing server...|||set the services to autostart and you can use sp_procoption to send you an email everytime sql server restarts.

you ought to figure out why your server is stalling out though.|||You can also use EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'|||I think you should kick the server, that would fix it

Check 'instdist.out' in the install directory.

What does this mean? It appears to be keeping the wizard from running the
distributor setup on the server.
Code used is:
use master go
sp_adddistributor @.distributor = @.@.servername GO
sp_adddistributiondb @.database='distribution' GO
Error is:
Server: Msg 14113, Level 16, State 1, Procedure sp_adddistributiondb, Line 304
Could not execute 'copy "d:\Program Files\Microsoft SQL
Server\MSSQL\DATA\DISTMDL.MDF" "d:\Program Files\Microsoft SQL
Server\MSSQL\DATA\distribution.MDF"'. Check 'instdist.out' in the install
directory.
Regards,
Jamie
What does 'instdist.out say?
You can also issue this command
sp_adddistributiondb 'distributionNew',@.data_folder='c:\temp,
@.data_file='distribution.mdf',
@.log_folder='ctemp', @.log_file='distribution.ldf',
@.log_file_size=5,@.create_mode=1
where c:\temp already exists
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B7FBF6B2-F8D0-40C6-9844-A21E386C991E@.microsoft.com...
> What does this mean? It appears to be keeping the wizard from running the
> distributor setup on the server.
> Code used is:
> use master go
> sp_adddistributor @.distributor = @.@.servername GO
> sp_adddistributiondb @.database='distribution' GO
> Error is:
> Server: Msg 14113, Level 16, State 1, Procedure sp_adddistributiondb, Line
> 304
> Could not execute 'copy "d:\Program Files\Microsoft SQL
> Server\MSSQL\DATA\DISTMDL.MDF" "d:\Program Files\Microsoft SQL
> Server\MSSQL\DATA\distribution.MDF"'. Check 'instdist.out' in the install
> directory.
>
> --
> Regards,
> Jamie
|||Unfortunately, it does not exist on the server.
Regards,
Jamie
"Hilary Cotter" wrote:

> What does 'instdist.out say?
> You can also issue this command
> sp_adddistributiondb 'distributionNew',@.data_folder='c:\temp,
> @.data_file='distribution.mdf',
> @.log_folder='ctemp', @.log_file='distribution.ldf',
> @.log_file_size=5,@.create_mode=1
> where c:\temp already exists
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:B7FBF6B2-F8D0-40C6-9844-A21E386C991E@.microsoft.com...
>
>
|||Let's try again... the last post appears blank on my screen.
Hilary,
We have searched the server hoping to find this file. The file does not
exist on the server so I don't know what it says. Thus my question - any
idea what it may mean?
Regards,
Jamie
"Hilary Cotter" wrote:

> What does 'instdist.out say?
> You can also issue this command
> sp_adddistributiondb 'distributionNew',@.data_folder='c:\temp,
> @.data_file='distribution.mdf',
> @.log_folder='ctemp', @.log_file='distribution.ldf',
> @.log_file_size=5,@.create_mode=1
> where c:\temp already exists
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:B7FBF6B2-F8D0-40C6-9844-A21E386C991E@.microsoft.com...
>
>
|||There is a script called instdist.sql which is run. Any errors are logged to
a file called instdist.out. Somehow you are having a problem running this
script.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:225ACF20-532F-41E5-A1AB-C0FFB3EF6B35@.microsoft.com...[vbcol=seagreen]
> Let's try again... the last post appears blank on my screen.
> Hilary,
> We have searched the server hoping to find this file. The file does not
> exist on the server so I don't know what it says. Thus my question - any
> idea what it may mean?
> --
> Regards,
> Jamie
>
> "Hilary Cotter" wrote:
|||Someone deleted one of the folders that SQL Server installs. We reinstalled
SQL and voila, the file is there now.
Regards,
Jamie
"Hilary Cotter" wrote:

> There is a script called instdist.sql which is run. Any errors are logged to
> a file called instdist.out. Somehow you are having a problem running this
> script.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:225ACF20-532F-41E5-A1AB-C0FFB3EF6B35@.microsoft.com...
>
>

Check if the mailserver is running before using it

Hi,
We are using several packages to accomplish some database tasks overnight.
In these packages we have included some SQL statements with the xp_sendmail
command for reporting purposes.
Is there a way to check if our mailserver is running and ready to process
the xp_sendmail command rather than resolving an error when the command
fails? Because it takes quite a long time before SQL server reports an error
that the mailserver is not working properly.
I prefer to have a stored procedure that reports if the mailserver is ready
or not. Then I can use that result to decide wheter or not to skip the
xp_sendmail command.
We are using SQL Server 7.0 with Exchange 5.5.
The mailserver is a different server then the SQL server.
Any ideas are appreciated!I make use of this http://www.sqldev.net/xp/xpsmtp.htm rather than
xp_sendmail.
With it you have the ability to ping the server and check the Return Code
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.server = N'mail.sqldev.net',
@.port = 25,
@.ping = 1
select RC = @.rc
go
HTH. Ryan
"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
> xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
> error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
> ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
> Any ideas are appreciated!
>
>|||"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
My advice:....
Install the SMTP Server that comes with IIS on the SQL Server box and have
the MAPI client setup to send THROUGH that.
It can then forward to your Exchange server.
Using this basic technique, I've basically eliminated my problems with
xp_sendmail hanging.

> Any ideas are appreciated!
>
>

Check if the mailserver is running before using it

Hi,
We are using several packages to accomplish some database tasks overnight.
In these packages we have included some SQL statements with the xp_sendmail
command for reporting purposes.
Is there a way to check if our mailserver is running and ready to process
the xp_sendmail command rather than resolving an error when the command
fails? Because it takes quite a long time before SQL server reports an error
that the mailserver is not working properly.
I prefer to have a stored procedure that reports if the mailserver is ready
or not. Then I can use that result to decide wheter or not to skip the
xp_sendmail command.
We are using SQL Server 7.0 with Exchange 5.5.
The mailserver is a different server then the SQL server.
Any ideas are appreciated!I make use of this http://www.sqldev.net/xp/xpsmtp.htm rather than
xp_sendmail.
With it you have the ability to ping the server and check the Return Code
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.server = N'mail.sqldev.net',
@.port = 25,
@.ping = 1
select RC = @.rc
go
--
HTH. Ryan
"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
> xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
> error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
> ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
> Any ideas are appreciated!
>
>|||"a" <a@.b.c> wrote in message news:eqa5u8CeGHA.564@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are using several packages to accomplish some database tasks overnight.
> In these packages we have included some SQL statements with the
xp_sendmail
> command for reporting purposes.
> Is there a way to check if our mailserver is running and ready to process
> the xp_sendmail command rather than resolving an error when the command
> fails? Because it takes quite a long time before SQL server reports an
error
> that the mailserver is not working properly.
> I prefer to have a stored procedure that reports if the mailserver is
ready
> or not. Then I can use that result to decide wheter or not to skip the
> xp_sendmail command.
> We are using SQL Server 7.0 with Exchange 5.5.
> The mailserver is a different server then the SQL server.
My advice:....
Install the SMTP Server that comes with IIS on the SQL Server box and have
the MAPI client setup to send THROUGH that.
It can then forward to your Exchange server.
Using this basic technique, I've basically eliminated my problems with
xp_sendmail hanging.
> Any ideas are appreciated!
>
>

Check if job is running?

Hi,
I have a stored procedure that needs to start a job stored in sql =
server. How can I check if this job is already started before trying to =
start it? Right now, users are getting the "can't start job because it =
is started" error, and I'd like to get rid of that.
Thanks!
--MichaelLook here:
http://www.databasejournal.com/feat...cle.php/3491201
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
Hi,
I have a stored procedure that needs to start a job stored in sql server.
How can I check if this job is already started before trying to start it?
Right now, users are getting the "can't start job because it is started"
error, and I'd like to get rid of that.
Thanks!
--Michael|||http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Raterus" wrote:

> Hi,
> I have a stored procedure that needs to start a job stored in sql server. How can
I check if this job is already started before trying to start it? Right now, users
are getting the "can't start job because it is started" error, and I'd like to get
rid
of that.
> Thanks!
> --Michael
>|||Thanks, this is exactly what I was looking for!
"Jens S=FC=DFmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> =
wrote in message news:e2mmWKFRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Look here:
>=20
> http://www.databasejournal.com/feat...cle.php/3491201
>=20
> HTH, Jens Suessmeyer.
>=20
> --
> http://www.sqlserver2005.de
> --
>=20
>=20
> "Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag=20
> news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a stored procedure that needs to start a job stored in sql =
server.=20
> How can I check if this job is already started before trying to start =
it?=20
> Right now, users are getting the "can't start job because it is =
started"=20
> error, and I'd like to get rid of that.
>=20
> Thanks!
> --Michael=20
>=20
>|||Nice to get a feedback :-)
"Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
news:%23WoN0QFRFHA.4028@.tk2msftngp13.phx.gbl...
Thanks, this is exactly what I was looking for!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e2mmWKFRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Look here:
> http://www.databasejournal.com/feat...cle.php/3491201
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
> news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a stored procedure that needs to start a job stored in sql server.
> How can I check if this job is already started before trying to start it?
> Right now, users are getting the "can't start job because it is started"
> error, and I'd like to get rid of that.
> Thanks!
> --Michael
>

check if im reading cached data or hitting disks

I am running a query that returns more than 15 million rows.. If i run it
more than once, I want to know if the second run is being accessed directly
from cache or am I still hitting the disks just in case I am short of memory
or a combination of both ?
The query is a select statement and nothing more..
Look in BOL for SET STATISTICS IO ON / OFF
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O7Y$wCLRFHA.508@.TK2MSFTNGP12.phx.gbl...
>I am running a query that returns more than 15 million rows.. If i run it
> more than once, I want to know if the second run is being accessed
> directly
> from cache or am I still hitting the disks just in case I am short of
> memory
> or a combination of both ?
> The query is a select statement and nothing more..
>

check if im reading cached data or hitting disks

I am running a query that returns more than 15 million rows.. If i run it
more than once, I want to know if the second run is being accessed directly
from cache or am I still hitting the disks just in case I am short of memory
or a combination of both ?
The query is a select statement and nothing more..Look in BOL for SET STATISTICS IO ON / OFF
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O7Y$wCLRFHA.508@.TK2MSFTNGP12.phx.gbl...
>I am running a query that returns more than 15 million rows.. If i run it
> more than once, I want to know if the second run is being accessed
> directly
> from cache or am I still hitting the disks just in case I am short of
> memory
> or a combination of both ?
> The query is a select statement and nothing more..
>

check if im reading cached data or hitting disks

I am running a query that returns more than 15 million rows.. If i run it
more than once, I want to know if the second run is being accessed directly
from cache or am I still hitting the disks just in case I am short of memory
or a combination of both ?
The query is a select statement and nothing more..Look in BOL for SET STATISTICS IO ON / OFF
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O7Y$wCLRFHA.508@.TK2MSFTNGP12.phx.gbl...
>I am running a query that returns more than 15 million rows.. If i run it
> more than once, I want to know if the second run is being accessed
> directly
> from cache or am I still hitting the disks just in case I am short of
> memory
> or a combination of both ?
> The query is a select statement and nothing more..
>sql

Check if a field exists before ADD a new column.

Hi All.
We have some scripts adding columns to tables. Is there a simple way to
check if the column are there already in the table before running the ADD
command. Just like you get on the table when scripting it?
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Thanx all
ghHello,
You can use something like this:
IF NOT EXISTS (SELECT * FROM syscolumns
WHERE id=OBJECT_ID('TableName') AND name='ColumnName')
[...]
Razvan|||if col_length('tb','col') is null
print('col does not exist in tb')
-oj
"Geir Holme" <geir@.multicase.no> wrote in message
news:uA2GTzcPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> Hi All.
> We have some scripts adding columns to tables. Is there a simple way to
> check if the column are there already in the table before running the ADD
> command. Just like you get on the table when scripting it?
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>
> Thanx all
> gh
>

check for queries running with parallelism

I want to check for all queries running with parallelism on our sql servers
for a 24 hour period. How can I do so ?
THanksTake a look at http://msdn2.microsoft.com/en-us/library/ms187943.aspx, and
read up on server side traces.
Note that you are warned about the overhead of this particular eventclass -
so it may prove too much of a hit to be running for 24 hours straight.
"Hassan" <hassan@.test.com> wrote in message
news:ub1Y8GKYIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I want to check for all queries running with parallelism on our sql servers
>for a 24 hour period. How can I do so ?
> THanks|||To add on to Will's response about the server side trace, consider
specifying a filter to narrow down the number of events as much as possible.
Unfortunately, a filter can't be specified on the binary data column that
includes the number of CPUs used. Also, consider specifying a trace file
rollover to keep the trace file sizes reasonable if you have a busy server,
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:ub1Y8GKYIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I want to check for all queries running with parallelism on our sql servers
>for a 24 hour period. How can I do so ?
> THanks|||You can use this but it is built off of the procedure cache. There are
limitations especially if you have have memory bottleneck or plan reuse
issues.
select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time > qs.total_elapsed_time
http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:ub1Y8GKYIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I want to check for all queries running with parallelism on our sql servers
>for a 24 hour period. How can I do so ?
> THankssql

Check Execution status when manually running Snapshot agents

I am working on a script to run in Query Analyzer to run all of my SnapShot
agents manually rather than wait for the schedule to do it. I am using
sp_start_job to run each agent such as:
exec msdb.dbo.sp_start_job @.Job_Name='SQLSERVER-SomeSnapshotAgent-167'
waitfor delay '00:30:00' --some fixed time interval to try and have jobs not
run into each other
exec msdb.dbo.sp_start_job @.Job_Name='SQLSERVER-AnotherSnapshotAgent-154'
If I put all of my Snapshot agent jobs in a script to run back to back, how
can I check for execution status so the next job does not run into the
previous agent already running?
Thanks,
Steve
Steve,
you can use the following rputine (modified slightly) to determine the job's
current status.
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||How do I obtain the Guid Job_id for the job name(s) that I am wanting to run?
Thank You
Steve
"Paul Ibison" wrote:

> Steve,
> you can use the following rputine (modified slightly) to determine the job's
> current status.
> http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Steve,
it'll be something like this - just replace the name with the name of the
snapshot agent:
select job_id FROM sysjobs
where name = 'UK-3XSW02J\PAULS2000INST-Pub1-2'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Check Data and Index Linkage FAILED

Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode?
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc Ferguson
The message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode? Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson
|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode? Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson
|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>
|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

Check Data and Index Linkage FAILED

Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were checking and trying to
>> repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional parameters then the DB must
>> be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You can then try to repair
>> any single table at a later data during your maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
--
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were
>> checking and trying to repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional
>> parameters then the DB must be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You
>> can then try to repair any single table at a later data during your
>> maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>> From your response, I assumed that it's the Integrity tab that I
>> should modify. So I unchecked "Attempt to repair any minor
>> problems". Is that correct or am I way off. Thanks again.
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

Check Data and Index Linkage FAILED

Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you
do have a problem, you
don't want an automated routine to fox this (I have an article on this on my
website,
www.karaszi.com, but the site seems to be down at the moment, so I can't giv
e exact UTL).
If you do check this option, the database indeed need to be in single user m
ode. Main plan tries to
set it to single user, but that will fail if any user is in the database, wh
ich seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our
logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine
. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Ente
rprise Manager open? I
> don't have the live database extended. I normally work on our test databa
se and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You ca
n
> then try to repair any single table at a later data during your maintenanc
e
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unch
ecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks
again.
See my response. This is indeed what I was referring to. So you are set now.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. W
hat is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should mo
dify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off.
Thanks again.
> Marc F.
> Ryan wrote:|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

CHARINDEX returns zero in TEXT column

I'm running into an issue where CHARINDEX on a text datatype column returns
0
if the expression I'm searching for exists at a position greater than 8000.
For example:
use pubs;
select charindex('New Moon Books',pr_info,8000)
from pub_info
returns zero, even though I know 'New Moon Books' exists past character
8000. Is this a known issue, and is there a workaround? It's causing my
search and replace procedure (using the UPDATETEXT function) to fail, i.e.
LIKE '%searchfor%'
is true but
CHARINDEX('searchfor',textColumn)
is zero.
Any help is appreciated.Did you try PATINDEX?
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||CHARINDEX will not work for strings larger than 8000. To work with TEXT
fields larger than this size, you will need to use the TEXT functions in SQL
Server 2000 like READTEXT, WRITETEXT etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
> I'm running into an issue where CHARINDEX on a text datatype column
> returns 0
> if the expression I'm searching for exists at a position greater than
> 8000.
> For example:
> use pubs;
> select charindex('New Moon Books',pr_info,8000)
> from pub_info
> returns zero, even though I know 'New Moon Books' exists past character
> 8000. Is this a known issue, and is there a workaround? It's causing my
> search and replace procedure (using the UPDATETEXT function) to fail, i.e.
> LIKE '%searchfor%'
> is true but
> CHARINDEX('searchfor',textColumn)
> is zero.
> Any help is appreciated.|||charindex won't work with text datatype. Use patindex|||Thanks Aaron, you of course are correct, PATINDEX works! (I was sure I had
tried that before, but I think I left out the wildcard character).
Anyway, much thanks (and thanks to Omnibuzz too!)
A.S.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Did you try PATINDEX?
>
> "Alan Smithee" <AlanSmithee@.discussions.microsoft.com> wrote in message
> news:F8D5F76C-9114-47F1-A584-96035BFA38B6@.microsoft.com...
>
>

Characters replaced by squares

Hi!
When running a report in our production environment we get squares instead
of characters. Does anyone have an idea why this is happening?Are there controls characters in the data you are displaying...? In some
instances if you have a carraige return, line feed, tab or something else
along those lines they'll be displayed as a square... In most cases in html
they're escaped the browser, considered white space, try exporting your
report to another format like PDF or Excel and see how the squares show up.
"Anders" wrote:
> Hi!
> When running a report in our production environment we get squares instead
> of characters. Does anyone have an idea why this is happening?|||Hi!
The squares also appears in the drop down lists when selecting report
parameters. What font is Reporting Services using in those drop downs?
/A
"Alien2_51" wrote:
> Are there controls characters in the data you are displaying...? In some
> instances if you have a carraige return, line feed, tab or something else
> along those lines they'll be displayed as a square... In most cases in html
> they're escaped the browser, considered white space, try exporting your
> report to another format like PDF or Excel and see how the squares show up.
> "Anders" wrote:
> > Hi!
> >
> > When running a report in our production environment we get squares instead
> > of characters. Does anyone have an idea why this is happening?

Character sets

So, I've looked around, read what I can find and I think I know the answer
to this, but would like some confirmation. I am running SQL Server 2000 on a
Windows 2003 server machine. The database is installed with the standard
English collation. Is it possible for me to insert Japanese characters into
the database like in, perhaps the name field so that I have English and
Japanese characters within the same field in the same table? I know I can do
some special characters (like an umlaut or grave etc.), but haven't had any
luck with double byte characters, and perhaps I am just beating my head
against the wall? Thanks for any information you can help me with.
WillieYou can use a Unicode datatype (e.g. nvarchar) in order to store both
English and Japanese characters in the same column. Values will be stored
using UCS-2 encoding.
Hope this helps.
Dan Guzman
SQL Server MVP
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:uKddbr1rGHA.1592@.TK2MSFTNGP02.phx.gbl...
> So, I've looked around, read what I can find and I think I know the answer
> to this, but would like some confirmation. I am running SQL Server 2000 on
> a Windows 2003 server machine. The database is installed with the standard
> English collation. Is it possible for me to insert Japanese characters
> into the database like in, perhaps the name field so that I have English
> and Japanese characters within the same field in the same table? I know I
> can do some special characters (like an umlaut or grave etc.), but haven't
> had any luck with double byte characters, and perhaps I am just beating my
> head against the wall? Thanks for any information you can help me with.
> Willie
>

Character sets

So, I've looked around, read what I can find and I think I know the answer
to this, but would like some confirmation. I am running SQL Server 2000 on a
Windows 2003 server machine. The database is installed with the standard
English collation. Is it possible for me to insert Japanese characters into
the database like in, perhaps the name field so that I have English and
Japanese characters within the same field in the same table? I know I can do
some special characters (like an umlaut or grave etc.), but haven't had any
luck with double byte characters, and perhaps I am just beating my head
against the wall? Thanks for any information you can help me with.
WillieYou can use a Unicode datatype (e.g. nvarchar) in order to store both
English and Japanese characters in the same column. Values will be stored
using UCS-2 encoding.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:uKddbr1rGHA.1592@.TK2MSFTNGP02.phx.gbl...
> So, I've looked around, read what I can find and I think I know the answer
> to this, but would like some confirmation. I am running SQL Server 2000 on
> a Windows 2003 server machine. The database is installed with the standard
> English collation. Is it possible for me to insert Japanese characters
> into the database like in, perhaps the name field so that I have English
> and Japanese characters within the same field in the same table? I know I
> can do some special characters (like an umlaut or grave etc.), but haven't
> had any luck with double byte characters, and perhaps I am just beating my
> head against the wall? Thanks for any information you can help me with.
> Willie
>

Chaning password

I have several merge replications running pushing subscriptions from one
server to the other. After some problems we recreated the databases (se
previous questions). However after pushing up all data everything worked fine
until we changed the administrator password on the subsciber server. Now I
get an error message 20084 at the publisher /18452 at the subscriber and the
message "Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection." What is the problem? i have registred the
servers via the 'sa' user and connect via the 'sa' user and have both
sql-server and windows as login possibilities. Sql-server and agent is
started via the administrator account (win2000) but i have stopped and
restarted the service changing the password in the services.
Best wishes
Mats
The "Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection." error normally means one of three things.
The NT account you are using to authenticate with does not have rights to
access the machine, its password is invalid, or you are using windows
authentication only and trying to connect using a SQL account and password.
You have to be a little careful with the account on the subscriber.
When the subscriber's SQL Server agent connects to the publisher it by
default will impersonate the SQL Server agent account on the publisher. In
other words the accounts and passwords of the account the SQL Server agent
on the Subscriber will have to match the account on the Publisher, or will
have to be in the administrator group on the publisher.
You can also specify a SQL account. In this case your SQL Server agent
account on the subscriber will have to match the SQL Server agent account on
the publisher, or be in the administrator group, or have rights to access
the snapshot folder share on the publisher.
I think your best approach, if you can do it, is to make the administrator
accounts on both servers have the same password, or to use another
administrator account on the subscriber, and add this account to the admin
group on the publisher.
HTH - its a little tricky to implement.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mats" <Mats@.discussions.microsoft.com> wrote in message
news:E2956017-73FA-4EBA-8D86-F58A0B4D24C7@.microsoft.com...
>I have several merge replications running pushing subscriptions from one
> server to the other. After some problems we recreated the databases (se
> previous questions). However after pushing up all data everything worked
> fine
> until we changed the administrator password on the subsciber server. Now I
> get an error message 20084 at the publisher /18452 at the subscriber and
> the
> message "Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection." What is the problem? i have registred the
> servers via the 'sa' user and connect via the 'sa' user and have both
> sql-server and windows as login possibilities. Sql-server and agent is
> started via the administrator account (win2000) but i have stopped and
> restarted the service changing the password in the services.
>
> --
> Best wishes
> Mats