SQLServerCentral / Administration / SQL Server 7,2000

Cannot Kill Process stays in KILLED/ROLLBACK status

krishnaprasad14 — Thu, 26 Apr 2012 05:16:38 GMT

Hi I have killed one spid bu its still showing this KILLED/ROLLBACK statusSugest me to resolve this with out restating the server please.....[Foun a link here but it's not resolved my issue http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/71d3cd0b-ce4d-4202-8e3b-38b38da90a5b]Thank you in Advance .

SQL SERVER services get stopped

Alone — Wed, 23 Jan 2013 02:17:08 GMT

Hi, Everyday we notice SQL SERVER services get stopped and we have to manually then start both services and agent.We have checked event logs but didn't get any clue.Is there any way to find out who stop the SQL service?

view the data from the sysmail_sentitems and sysmail_unsentitems and sysmail_faileditems without giving sysadmin role MSDB databasse

piliki — Fri, 12 Apr 2013 14:29:56 GMT

sysmail_sentitemssysmail_unsentitems sysmail_faileditems these are the views in msdb and without giving sysadmin role MSDB databasse,how to access the data????Plzz help me....

SQL Exception: Timeout

defiantclass — Thu, 02 May 2013 10:55:26 GMT

We have a web application manufactured by a company that no longer exists. I'm running the front end on a Windows 2003 server with IIS6. The database server is Windows 2003, SQL Server 2000. We do batch postings to AR daily. Smaller batch jobs post without a problem, within minutes. Larger ones however fail after just about 1 hour. (the application generates a message saying it failed). In the event log on the web server I get an entry each time this happens that reads:Error:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.I have been reading about how to fix this, most information I find seems to point me to increasing the timeout period of the connection string. I also found a KB article referring to the .Net SP1 update that allows you to change this property. But I have no idea how to implement this change, or if that is really what I even need to be doing. I do not see this hotfix listed in my add/remove programs list. I do see .Net 2.0 and 3.0 SP2, and 3.5 SP1. But not .Net 1.1 SP1. I do know that my Query timeout in SQL Server Properties is set to 0=Unlimited. But apparently that is not enough. I also just configured the Maintenance Planner to run tonight to rebuild/re-index the database, on a suggestion from another article. But I don't know what results that will produce, if any.Basically, I'm getting this timeout error and need to get it fixed and have no idea how to do it. Sorry for my lack of knowledge, but I just don't have to do this kind of thing enough to know it.Any help would be appreciated. Thank you.

Profiler Questions

Jared Metzger — Wed, 01 Jun 2005 09:08:00 GMT

I am battling with profiler trying to trace all select, insert, delete, and updates against a specific table. I have specified a Database ID in the filter as well as an object ID for the table in question. However the results are still displaying all actions for the Database and not filtering out the table I need. I haven't been able to find any good documentation on profiler. Any thoughts on a better way to trace these events for a specific table? Or any links to some in depth reading on SQL Profiler? Thanks for your help!

msdb database not REALLY in single user mode?

Erin Ramsay — Thu, 16 May 2013 10:13:50 GMT

I've run the following commands:[code="sql"]Sp_configure 'allow updates', 1 Reconfigure with overrideGOsp_resetstatus 'msdb' GOupdate master..sysdatabases set status = status | -32768where name = 'msdb' Sp_configure 'allow updates', 0Reconfigure with overrideDBCC checkdb(msdb)ALTER DATABASE msdb SET SINGLE_USER[/code]When I look at the properties for the database I see that it IS in single user mode and it's in standby/emergency mode but when I run the following command[code="sql"]DBCC CheckDB (msdb, REPAIR_ALLOW_DATA_LOSS)[/code]I get the following error:[quote]Msg 7919, Level 16, State 2, Line 2Repair statement not processed. Database needs to be in single user mode.DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/quote]Am I missing something? Is it not REALLY in single user mode?

BCP with query how input file

jodiseo — Thu, 16 May 2013 09:56:50 GMT

Hi: I want to generate a BCP commnad, but my query is very very large: ¿Is possible to put the query into a file in order to make my BCP command?Thankyou very much

SQL VDI issue

devanandiiway — Tue, 14 May 2013 05:03:24 GMT

Hi ,Am using SQL server 2005 version in my project..We have configured the backups using the Data protector tool. For the past 2 days I could see the below error in my event viewer. Could you please let me know what needs to be done to avoid this error. SQLVDI: Loc=CVDS::CreateEx. Desc=Memory in use. ErrorCode=(183)Cannot create a file when that file already exists.. Process=9680. Thread=9604. Client. Instance=. VD=Global\Data Protector_(DEFAULT)_witness_100218242_0_SQLVDIMemoryName_0. Witness is my database name.The same error am receiving for Model database also. Appreciate your quick response.thanks,Dev.

I would REALLY like some fast help with this problem!

hamed1337 — Sat, 11 May 2013 15:56:27 GMT

[font="Times New Roman"][size="4"][b][center]I would really like some help with this annoying longlasting problem[/center][/b][b]So i have built my own computer, and i have another one (not built by me, it's built by Hewlett-Packard).I use Windows 7 Ultimate 64-bit, and the problem is that SQL Server 2000 installs perfectly on this new made computer but not the service pack for it (sp4).The installation keeps on going until it comes to this and stops, almost forever "Setup is initializing the server for the upgrade process".When i wait long enough then a error pops up (didn't pay attention to it and clicked Ok, and then another one and i pressed ok. Then the setup just closes itself and doesn't allow me to retry the installation because this time it stops at validating password...Please help me if you maybe know a solution for this. I'll give you my skype and please install TeamViewer so you can control my computer and change everything that must be changed in order for the installation to proceed.[/b][/size][/font][/color][b][size="3"]Skype: Hamedhaghjo[/size][/b]

SP4 Install hangs

kwitzell — Thu, 12 Apr 2007 10:58:00 GMT

Has anyone encountered this: I attempted to install SQL Server 2000 SP4 on a Windows 2003 standard edtion server. The installation proceeds normally until it reaches the point where it says "Setup is initializing the server for the upgrade process". This is as far as the install gets, it just hangs at this point. I waited at least 15 minutes and nothing happened. So we stopped the install, restarted the server and again attempted the install, with the same result. I ran a query on serverproperty, product level and it shows SP4! (I know it was SP3a before we started) So I am not sure what is happening here. Any suggestions will be greatly appreciated.

Ken

CmdExec - Jobs

andriancruz — Mon, 31 May 2010 01:34:51 GMT

Hi Everyone,I'm using CmdExec to call VB Application to extract records. The application is running fine. But when I put into Jobs the status is always executing. Anyone please help why the atatus is always Executing. Is this means the application is running continously? Any suggestion will high appriciated. Thank in Advance.Regards,Andi

Delete or move tempdb .ndf file

Dave-148053 — Fri, 01 Jun 2007 11:51:00 GMT

We have tempdb on a different physical disk than our user databases. A few weeks ago, we were running out of space on that disk during a data warehouse load. Until we could buy a bigger disk, we created a .ndf file on our main data drive. We now have acres of space on the tempdb disk, but SQL Server insists on using both the .ndf and .mdf files during the load. We tried deleting the .ndf file, but got an error message. I found a script example here (love SSC, Steve!) to move the file, but I don't think I need the .ndf anymore and would like to delete it. (A colleague tried the delete from EM, so I don't have the text of the message). Any ideas?

xp_cmdshell

Lester Policarpio — Mon, 26 Nov 2007 22:04:22 GMT

How can i disable this command and any other related commands??

User Authentication

forefj — Mon, 06 May 2013 11:57:38 GMT

I am trying to login with an account (for example user name: SQL1) using SQL Authentication and having problems, it is not accepting the username/password. I know it is an active user because from a different domain I am able to login successfully using Windows Authentication. For testing logging from another domain I want to user SQL Authentication but I can't seem to get it working. When I login using the SA account, how can I change the password (SQL Authentication) for the user account: SQL1?Thanks.

Need Help Stopping a Rouge Extended Procedure (XPSMTP)

qgudex — Thu, 25 Apr 2013 11:05:51 GMT

I'm experiencing a very unusual problem with one of our old SQL 2000 installs. Backstory: we've used xpsmtp80.dll on all of our SQL 2000 SQL Agent jobs in the past, with much success. If certain steps in a job ever fail, the agent jumps to a step in the job which uses xp_smtp_sendmail to notify the proper individuals. On one of our remaining SQL 2000 systems, we started receving email alerts that the tlog backup job failed, although there was no failure anywhere in the job. Fast forward a couple weeks, and we have completely deleted the job in question, deleted the extended procedure from SQL, deleted the .dll from the file system, and rebooted the sever. We are still getting these emails exactly when the old job used to run (we do have a new tlog back job that runs at slightly different times and does not use xp_smtp_sendmail).Has anyone seen anything like this before? Is there something in the msdb db that needs to be cleaned up? I'm not sure where to go from here. Thanks in advance.

adding a 2nd Tempdb file.

PearlJammer1 — Wed, 01 May 2013 02:48:18 GMT

HiI am considering adding a secondary tempdb file to my datawarehouse to hopefully improve performance. I am aware that I have to size this secondary file to the same size of the primary file so i get proportional fill going on.when i run a query to get the current size of the tempdb file it returns 40159.50mb / percent used 0.02 and templog is 250mb /percent used 41.61When i view tempdb via ssms and click properties then look at the files from there it says "Initial size 22669 mb" autogrow by 10% unrestricted.So what size should my new secondary be ? the size returned by the query or the size returned by the /properties files view in ssmsAlso am i correct in saying i dont need to create a transaction log file for this new tempdb file ?

DTS Package Under SVN control

chulheekim — Mon, 29 Apr 2013 11:04:03 GMT

I'm trying to export the DTS packages to have them under svn version control. When you export, you can either save it as dts or bas file. which file format will be ideal for version control? I prefer dts because it is easy to export/import and I haven't been able to figure out how to import bas file. Do you have any concern with going with dts file?

Question about clearing tempdb

defiantclass — Sat, 27 Apr 2013 08:53:28 GMT

Hello,My question is simply, once I have cleared the temp database either by rebooting or restarting the service, should the tempdb.mdf file located at Drive:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf go to the size I have allocated for it in the tempdb properties, or should it go closer to a 0 file size? I have cleared the tempdb, both by rebooting and restarting the services, I can see in the log where it said it started the operation, but I don't know how to tell for sure if it is emptied since the tempdb.mdf file is always at the allocated size I set for it when I look at the file.I guess I'm just not sure if setting the allocation size means that is the size it is allowed to grow to, or if it is the size the file (database) should physically be by default, at all times.Thanks!

Detecting Detached Databases

rflewitt — Wed, 14 Aug 2002 17:28:00 GMT

I've many databases on the server I've inherited. Some are detached and rather than manually check each db to see which is attached/detached I'm trying to find a sql script method that will tell me. I've searched through the site but cannot find anything that may help. Is it possible? If so, how? Thanks for any help.

Moving TEMPDB

willian.funes — Fri, 19 Apr 2013 16:27:46 GMT

Hi all,I have a default installation and I need to move just the TEMPDB database from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to another partition (E:\) because this database is too big and I don't have enogh space in C:\.I did a backup database but I don't know [b]What other considerations should I have[/b][u]Using this code:[/u]use mastergoAlter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')goAlter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')goThanks,

SQL server 2000 - Profiler error

ciki022 — Thu, 18 Apr 2013 01:26:39 GMT

Hi,I am using Query Analiyer to execute procedure integrated into database ( I am not author and I dont know what is in procedure, until now procedure worked well ) and I get error like this"ODBC: Msg 0, Level 16, State 1Cannot load the DLL C:\Program Files\Microsoft SQL Server\MSSQL\Binn\xplog70.dell, or one of the DLLs it references. Reason: 126(The specified module could not be found.)."I copied this file, xplog70.dll, from another computer with identical SQL server, to problematic computer, but that not helpedWhat should I try, before I reinstall SQL server ( I dont like that idea ) ?Thanks in advanceGoran

SQL server 2000- Maximum RAM support

vijayakumarn — Thu, 18 Apr 2013 01:49:25 GMT

Hi,What is the amount of total RAM supported by SQL server 2000 irrespective of any operating system compatible with it. Just please let me know, how much SQL 2000 can support. With my knowledge, I think it supports 2Gb maximum, by doing some os tweak, it can be boosted to 4Gb at most. Please clarify.Thanks in Advance!!!

schema lock permission denied

arr.nagaraj — Sun, 31 May 2009 04:45:04 GMT

Hi all,I am using linked server to update some data on a target server.The linked server call fails with the error 'Schema lock permission denied on object xyz'.I have provided the neccassary rights(permission on the object xyz) for the login that calls the spon the target server.I am using Windows Authentication..this problem occured when we moved fm Windows to SQL Authentication..SPN registraton, trusted domain ,delegation settings r done..Any clue wat can be the cause for this error..Thanks in Advance,Regards,S.V.Nagaraj

require db monitor script for sql 2000

Oracle_91 — Mon, 15 Apr 2013 08:45:59 GMT

Hi,Need help in writing a script for sql server 2000 to check the status of all db's in the instance and if anyone is not online for any reason (be it suspect,recovery....), I should get any alert. How to implement this?Thanks in advance,

Connecting to different instance of SQL Server

prasad-994585 — Wed, 21 Jan 2009 08:33:57 GMT

Hi all, I have installed 2 instance of SQL Server on a same computer . One is a defualt instance and other is named instance:I am able to connect to default instance since it is on port 1433.My named instance is on port 2085.Can any body plz tell me how to connect to this named instance form cmd line and likewise from the SQL Studio .Regards,Prasad

Microsoft Support of SQL 2000 in Compatability Mode

hayden_jones — Wed, 10 Apr 2013 11:18:49 GMT

SQL 2000 Extended support seems to have ended. My company is taking on the hosting of an externally hosted application which is SQL 2000 that does not support the DB on any higher release. We will put it on a SQL 2008 R2 instance and keep it in 2000 compatability mode until we can figure out what to do with it. I've been asked if being in compatability mode affects the support dates in any way. I wouldn't think so. What do you think?

The log for database "databasename" is not available.

diogenes1331 — Thu, 17 Aug 2006 18:36:00 GMT

"The log for database MYDB is not avaiable" .. when writing an UPDATE statement to a table.We first observed this problem today in a database but I found out that in addition we have had an error in the application log "Backup Request: WaitForIOCompletion: Write failure on backup device "Y:\MyDB_Log.ld.Operating Syste Rror 1117: The request could not be perfoirmed because of an IO error".I can perfectly see the LUN where the transaction log is and I can get to that folder an d connect to the database to do SELECTS or even see the properties from Enterprise Manager, but we cannot do anything that involves the transaction log.

sqlservr.exe process increase CPU when antivirus is enabled

pgrassin — Mon, 01 Feb 2010 10:34:36 GMT

Hi,This is the first time I add a topic here, but I recieve the SQLserverCentral Newsletter every week and find some topics very interesting. Thanks.So I have the following issue on a SQL 2000 server protected by McAfee antivirus.When McAfee is disabled, sqlserver.exe process uses less than 10% of the CPUWhen I enabled McAfee, sqlserver.exe process jumps to about 60% of the CPUIt slows down the t-sql queries and some sql clients get timeout that affect client applications.I opened a ticket to McAfee, but get no answer expected to install the last version. That is done but the issue remains.Here are the McAfee exclusions: BAK, LDF, MDF,NDF,TRN files C:\Program Files\CA\ C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP and subfolders C:\Program Files\Microsoft SQL Server\MSSQL\Binn and subfolders C:\Program Files\Microsoft SQL Server\MSSQL\Data and subfolders C:\Program Files\Microsoft SQL Server\MSSQL\FTData and subfolders D:\BACKUP and subfolders D:\MSLSQL\Data and subfolders D:\Tracability Manager and subfolders (databases are located in this directory)Here are the McAfee Low Risk Process: Aexauditpls.exe Aexnsclient.exe Aexnsclienttransport.exe Aexnswdusr.exe Agentsvr (SQL Agent) Sqlservr.exe UnivAgent.exeConfiguration Windows Server 2003 Standard Edition SP2 – intel Xeon 2 CPU 3,06GHz 4GO RAM (3GB swith) SQL 2000 SP4 McAfee VirusScan Entreprise 8.7.0iIf someone has an idea ?? Pierre

SMTP Mail - Need xpsmtp80.dll

Luk - ARG — Fri, 12 Feb 2010 10:06:05 GMT

Hi, I'm trying to set up SMTP mail, and I can't find the required DLL, all the posts I found refer to the zip file located at www.sqldev.net but that site is no longer working. Does anyone have that dll and could send it to me or does anybody know where I can find it? I've googled it with no luck.ThanksLucas

troubleshooting sql server 2000 slowness

anoop.mig29 — Sat, 30 Mar 2013 22:24:09 GMT

Hi for few days i am going to work at client side which has sql server 2000. I am knew to it show just want to know where to look for helps . I mean suppose i want to know which query are executing at present then where to look . dbbc inputbuffer does not shows complete query then where to look.I want to know how much time it would require to complete a database backup/restore then which queries to use.some faster way to know fragmenatation in table Thanks and regardsAnoop

Buffer cache hit ratio ( week)

OracleBoy — Thu, 28 Mar 2013 03:34:34 GMT

Hi everybody , I need your help please :I want to create a graph to show the progression of the "buffer cache hit ratio" for last 7 days (a week). Until now I use this script (actual moment ) :[code="sql"]SELECT CAST( ( SELECT CAST (cntr_value AS BIGINT) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio' )* 100.00 / ( SELECT CAST (cntr_value AS BIGINT) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base' ) AS NUMERIC(6,3) )[/code]

Replacing sqlmap70.dll for xp_sendmail with a third party .dll. Is it even possible?

GabyYYZ — Tue, 26 Mar 2013 11:50:55 GMT

Hello, we have some legacy sql 2000 systems (and we understand end of life is in April) which use MAPI/Outlook mail boxes. We have instructions to look into switching to SMTP relay. However, this would involve a re-write of all procs/jobs that use xp_sendmail as well as modifying alerting system to use the new proc. What I would like to know, is there a way to keep the interface the same (i.e. xp_sendmail) while replacing the underlying .dll it is linked to, sqlmap70.dll, with a third party application?I have hunted online but could not find a product that does so? Everywhere I look, it's a new/alternative proc name. It may be a wild goose chase but interested in people's thoughts on this.

Alter column order

niranjankumar_k — Thu, 28 Feb 2008 02:16:15 GMT

Table contain 6 column in order like ABDEFGNeed to addd one more column "C" after B column. (in between B and D ). how to write alter script for this.

Help in the Delete Statement

montse 22199 — Wed, 20 Mar 2013 13:00:03 GMT

Hello all,I need to delete a record that is duplicate between 2 tables.The query looks like this:Delete from request_form_table_testing as t1where EXISTS (select tEmail from request_form_temp_emails as t2 where t1.email = t2.tEmail) but is giving me an error:Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'as'.I dont really know where is my syntax error.Could you guys help me?Thanks a lot

CAST CONVERT doesn't work

walter.habegger — Mon, 18 Mar 2013 03:08:25 GMT

Hello everybodyI have th following CAST Convert statement:SELECT CAST(CONVERT(CHAR(8), EZACDT) AS DATE) AS Accounting_Date FROM SYM_SPT_SNI_01.dbo.DBA_MVX_CINACC00 WHERE EZCONO = 2 AND EZDIVI = '001'And get the following error:Msg 241, Level 16, State 1, Line 1Conversion failed when converting date and/or time from character string.This happens since last week, and our IT isn't able to give me any information about installed updates (see Server information below).Microsoft SQL Server Management Studio 10.50.1600.1Microsoft Analysis Services Client Tools 10.50.1600.1Microsoft Data Access Components (MDAC) 6.1.7601.17514Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 8.0.7601.17514Microsoft .NET Framework 2.0.50727.5466Operating System 6.1.7601Does anybody know why this could happen?Thanks in advance, Walter

Determine who created trc files

Bongkerz — Fri, 15 Mar 2013 07:07:30 GMT

Hi I need to find out who created *trc (trace) files on a sql server 2000 instance, is there anyway i can get the user that created them by running a script, or is there a specific log file i need to go through in order aquire this information?Thank you.

query data using linked server

nihal9200_kwada — Wed, 13 Mar 2013 16:10:31 GMT

HI,Linked servers in test environment working differently than prod. Not sure what is causing slowness in test environment? is this issue with SQL version? Please advise.Test ServerA – SQL 2000 dev edition 8.00.2055 (X86), Windows NT 5.2 (Build 3790 SP2)TestServerB -- SQL 2000 std edition 8.00.2055 (X86), Windows NT 5.2 (Build 3790 SP2)Linked server exists between TestServerA and Test ServerB (also vice versa)ProdServerA-- SQL 2000 EE edition 8.00.2187 (X86), Windows NT 5.2 (Build 3790 SP2)ProdServerB-- SQL 2000 EE edition 8.00.2055 (X86), Windows NT 5.2 (Build 3790 SP2)Linked server exists between ProdServerA and ProdServerB (also vice versa)Pulling data from ProdServerA to ProdServerB:• Estimated Execution Plan shows an Inner Join being used.• Runs quickly.Pulling data Test ServerA to TestServerB :• Estimated Execution Plan shows a Hash Join being used.• Very slow due to Hash. Trying to bring entire source table of rows across.TestServerB -Is setup with linked server to TestServerA. TestServerA on this server has system tables named SysRemote_... TestServerA Is setup with linked server to TestServerB TestServerB on this server does NOT have system tables named SysRemote_...Here’s the query:SELECT rp.ReadID, rp.ReadPositionIndex, rp.ConvertedValue, rp.LaserPower, rp.LaserDuration, rp.Counts, rp.CalibrationID, rp.GlowCurveDataFROM-- To run on ProdServerB (Production) TableB arp JOIN ProdServerA.DatabaseA.dbo.TableA rp ON arp.ReadID = rp.ReadID AND arp.ReadPositionIndex = rp.ReadPositionIndex -- To run on TestServerB (Test)-- JOIN TestServerA.DatabaseA.dbo.TableA rp ON arp.ReadID = rp.ReadID -- AND arp.ReadPositionIndex = rp.ReadPositionIndex

xp_sendmail not sending @message text

jshmccal — Wed, 13 Jul 2011 10:24:58 GMT

xp_sendmail will send out the email with correct to address, correct subject line but the message text is blank. This has been working.We were just migrated to Exchange 2010. We have ran xp_stopmail, recreated outlook profile and SQL mail profile, then ran xp_startmail.We are out of ideas. Any help will be greatly appreciated.ThanksJack

Pipe Symbol as Column delimiter

Sara_DBA-629440 — Wed, 13 Aug 2008 00:42:33 GMT

I need pipe sysmbol as column delimiter., eg.,FirstName | LastName | Address | PhoneCan anyone explain me how to do this (except +'|'+ option) please? I used +Char(166)+ and export into .dat file. When I open into notepad the sysmbol is different but it is opening in DOS prompt / editor :w00t:I need this .dat file should open in notepad also with '|' symbol.Thanks in advance...Your help is much appreciated...

Memory Error - Failed to reserve contiguous memory

Steven. — Fri, 22 Nov 2002 04:06:00 GMT

I had the following warning in the sql log file 'WARNING: Failed to reserve contiguous memory of Size= 65536'. (SQL 2000 SP2)Having looked on Google groups I see this 'generally implies that there is insufficient contiguous free memory in the MemToLeave area'Does anyone know if there an alert condition which I can use to monitor this 'MemToLeave' area?thanksSteven