Is there a better way to move a huge table that has no index on it from one Filegroup to another ? Using SQL 2000 ...more >>

A) When i run a delete against a table that has 50 million records based upon a where clause , what entries does the T log file hold ? Does it log 50 million delete statements along with 50 million inserts just incase it needs to rollback. B) Also if there is a clustered index on the coulimn...more >>

Using SQL 2000 and wanted to know if DBCC shrinkfile executed from QA is faster than doing the equivalent through Enterprise Manager by selecting the file and selecting shrink file to <size> Thank you ...more >>

Hi, we are trying to get some data from a linked server via an ODBC connection. This server has 3 intances of the same software, we load 2 tables from each installation. From one instance we get both tables, from the two others we can only acces one of the two. I explain that only because I...more >>

As I have discovered over several communications with you (have been very helpful), its seems best that I have our production database in a "Full" model recovery, with the database file and transaction file in autogrowth mode. Because my transaction file was extremely large I also found ou...more >>

Hi, Is there a way of allocating processors to be used by SQLSERVERAGENT only? Thanks Sam ...more >>

Is there another way to search a string value ( in this case comma separated ) and count the occurence within the column? Below, I was counting the occurence of comma within the string. select len(replace(replace(replace(replace(replace(replace (replace(replace(replace(replace ('1234,5...more >>

When i'm taking backup off the log file, the log file is still the same size. I'm using SQL Manager to do this. Is there some way to truncate the log file without taking backup? I now that the file cant be truncatet when it is transactions in it thats not finish, but there shall not be any trans...more >>

If I nstall 4gb of ram in a server, I suppose I will need sql enterprise. With that said, can I license the server with 2 enterprise per processor licenses or will 1 enterprise license take care of both processors? and how much is it? Thanks...more >>

How can i find out which users have exec priviliges to a sproc using TSQL ? ...more >>

Hi, How much physical ram does SQL Standard and SQL Enterprise supports? If I am purchasing a server that has 2 processors and 4- 6GB of ram, which version of sql should I purchase?...more >>

Hi, Someone can help me? Since I did a truncate of my transaction log, I cannot backup my database anymore. I have this error on the SQL Server error log: Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'GATSDB02' as 'GATSDB01\piadmin' (trusted) Starting main...more >>

Hi again, i was wondering if anyone has encountered this problem: I make a database maintenance plan (through the wizard) because i want to backup the server files to another computer on my network I have made a map to the other computer (as G:), and i then go to the tab "Complete Backup" ...more >>

Is there any possible way I can Update a set of records using the DISTINCT funstion in SQL. My current update is not giving me the required result. Or can the Distinct function be only used with SELECT and COUNT statements? Any other suggestions would be very welcome. B -- Posted via http:...more >>

Hi, I have a database that was migrated from sql7 to sql2000. After, the migration it seems that sql2000 is ignoring the default values for the tables and inserting nulls instead? Any ideas? Thanks ...more >>

Hi I have a local copy of SQLServer developer on my machine. It's set to start when the operating system starts. However, it's refusing to start. When I go to Services> MSSQL$myServer or MSSQLSERVER and try to start it, it comes up with the following message: Could not start the MSSQLSERVER se...more >>

I'm currently performing a database backup at noon and 8pm every evening, along with a transacation log backup at 10am and 2pm. My Recoery Model is set to "Full" and have realized that the transaction log has every transaction ever done against the database, getting pretty large. Consideri...more >>

A suggestion for the Microsoft folks (those who have access to them can forward?): when editing stored proc from EM, the screen estate is vastly wasted. Some of us don't care, some of us do care about this. For those who don't care, they should not have much to object the idea of making more...more >>

I have SQL Server 2000 SP3 Developer Edition running on a W2K Professional with Service Pack 2. I am attempting to Import a number of large (1-2 gb sections, ~40gb total) data files. The data drive has > 50gb free and the log drive has > 25gb free. I can neither Create or grow the db...more >>

Help, A server became unresponsive yesterday, starting with disk write errors that we've seen before, but then around 9am this morning a crash dump was written. Can anybody help me to chase this down. I think the best resolution is PSS, but I'd like to get as much info as I can before maki...more >>

I've searched microsoft.com and can not find a comparison between sql 7 and sql 2000. I need this to justify migrating to 2000. Can anyone point me to documentation to justify migrating to sql 2000 from sql7?? Query or transaction performance between the two versions would really help. ...more >>

I have a senario and am not sure which license I need (processor license or CALs). I have a Web server with a single Web application. It's public so users will hit the Web application which will talk to the SQL Server. The Web application will use the same user login to connect to the SQL Serv...more >>

I am desinging a table that will contain information on employees. One of the things I would like to store are word documents or excel documents. How do I store the documents in the database?...more >>

I am new to this Group, and have little Sql experience. As an Access guy I have long used Access Crosstab queries to wow and amaze customers. As you all know (as I have just discovered) Sql 2000 doesn't know anything about Transform and Pivot. Soo... How can I create the equivalent of an Acc...more >>

Hi all: I'm attempting to apply Service Pack 5a to a SQL Server 6.5 machine, in order to migrate the databases to SQL 2000. When running "setup.exe" for the SP 5a, I am prompted for the sa password, which I enter. However, I received the following error message. The entered password faile...more >>

Hello... I'm facing two problems: 1. When I try to delete a user from a specific database, it reports that that user cannot be dropped as it owns objects. The db_owner is 'sa' not this user. 2. When I try to shrink the size of my log file, it reports, "Cannot shrink log file 2 (cms1_log)...more >>

Have SQL2000 with sp3 installed Start sql with sqlservr -m -c -dPathtoCopyofMaster.mdf -lPathToMastLog.ldf Instance starts Open Enterprise manager Run restore on local backup of master Get ODBC State error HY00 Then have to shut down SQLEntMgr from Task manager What's up? Any answers hmNic...more >>

Hi group, is there a way to do this? Like create procedure a as declare @db varchar(33) declare @sql varchar(99) set @db = 'db1' set @sql = 'use ' + @db exec (@sql) set @db = 'db2' set @sql = 'use ' + @db exec (@sql) My application needs to cursor the sysservers and get th...more >>

Platform: SQL Server 2k with sp2 OS: W2k sp2 I tried to move log files at one database from location A to another location B. I first created new log fileB.ldf for this database at locationB. Then shrank the original log fileA.ldf as much as possible. Next I tried to delete fileA from ...more >>

Hi group, I need to retrieve information about a running step, specifically its status. When a step is being executed, the EM always knows and shows so (under status showing Executing Job Step 'n (step name)'). However, I can not find such information in the system tables. where does EM get...more >>

I am running Sql2k to house my 7GB database. I ran a full backup this morning and saw amount of data backed up was 7GB. I then decided to try doing a differential backup to see if it might be a good mid-day solution. Normally I would do several log backups during the day but because the applicat...more >>

Probably a daft question but....... Should service packs be installed on PCs only running client tools or are they only applicable to database engine machines? ...more >>

Hey all.... I am an IT consultant, do not really know that much about the performance characteristics of SQL server, but I do know one of my clients that has a server that acts slow for about 5 minutes total of every hour (at random times, usually for 30 seconds at a time) In performance monit...more >>

SQL2K Ent SP3a Win2k SP3 I've recently enabled awe memory on 2 of our servers. Everything seems to be going OK, AWE memory counters are active in performance monitor and the sql serror log shows awe memory enabled on startup. My question is, is the above information enough to know that a...more >>

Using SQL Server 2000 on Windows 2000 - if I look in Windows Explorer, the date on the MDF file does not always match the date the last time data was entered. Auto Close is not selected on the options tab. When does this date (modified) change? What causes the change? Thanks...more >>

Hi, I have set a restore job for a Database using Enterprise Manager. The job ran quite quickly but the Database now appears on the database list with (loading) written after it and none of the objects are visible. It has been sitting there for about n hours. The database file is only about 1,...more >>

Hello All Can some one give me a quick lesson in data types - What is the basic difference between a char data type and varchar data type and when does one use one over the other and advantages and disadvantages of one over the other. Here is the reason I asked. Initially I had a table t...more >>

We upgraded to sql 2000 last night and now the maintenance plans do not work. I'm using simple model, no tlog backup. The error I'm working with is this: sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. The log looks like this: Microsoft (R) SQLMaint Utility (U...more >>

Is there a way to launch an application(c:\hello.exe) from a trigger when a new record is inserted into the table or a record is updated in the table? If so, what is the correct syntax ? THANKS!...more >>

I have a SQL database called "Acts" on SQL2000 server that was created by installation of an application. On that SQL server we have created Logins for this particular "Acts" database so users can use the application. Management has now asked us to move this database to another server with ...more >>

Hi, I have a SQL7 user database backup that I load into the SQL2000 without problem. When the restore operation is finished I got the database loaded with Compatibility_52_409_300003 COLLATION. How could I change the COLLATION set for that database? I am trying to use this following com...more >>

We have just uprated our SQL 7 server. We are now using a quad zeon processor windows 2K server (from a twin pIII system) and everything runs fine for several hours. Suddenly, the processor utilization will jump to 40-60% on all processors (from about 5%) and effectively lock everyone out of the...more >>

I am getting this error message over night. The server is not busy during this time but a job that runs at 1 AM seems to generate this error only when it runs at 1 AM. When I rerun it at 7 AM when I come in to work it runs fine. The error is: Error: 1204, Severity: 19, State: 1 The SQL ...more >>

I am running 33 queries (inserts) from a table with about 20 million records. (I am parsing media URLs by filetype into separate tables) The 33 queries are nearly identical. (each a filetype and similar to below) Sample (2 inserts) : insert into dbAVI (filespec,name,heading) select distinct(...more >>

We have a database running on SQL Server 2000. The database file size is 41mb and the transaction log file is more than 4gb. can anyone help me in truncating the transaction log. I tryied shrinking and backing up of the database but no use. thanks in advance Boopathi...more >>

Can I rename my sql server name without changing the computer name? If I change the computer name will it automatically change sql server name? Or I need to reinstall sql server?...more >>

I recently read "SQL Server data and log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems". Is this true for drives with NTFS that have drive compression enabled? If this is ok to do, what is the performance degrade as opposed to an uncomp...more >>

When I used sql profiler to trace exception error of my sql server, I got a bunch of error 16945,16955 and 208, I wonder if anyone knows how could I locate where the error raised? Thanks. ...more >>

If i create indexes on say filegroupA that spans the X drive and create tables on say filegroupB that spans the Y drive and if i have a clustered index, what gets stored on FGA and what on FGB. Does the leaf level of the Clustered index which are the data pages get stored on FGB and does the n...more >>

SQL2000, standart, SP3 I just added 2 new drives to my SQL Server with raid 1 mirroring and planned to move the trasaction logs, which set up directory was the same as the data drive. Some ideas what will be the fastest way to do this - the logs are not so big as they are backed up every 2 ...more >>