I am new to sql server and I am using SQL 2000 I have a table with a column "DIVISION" which is of type INT What I would like to do is this When a new entry is added to the table I would like to add a value to the DIVISION field starting with 1, then when I have say 50 rows where DIVISION...more >>

How can we pause database mirroring ? The reason for doing so is so we can take SAN snaps to multiple volumes and ensure that the database is in a consistent state at the receiving end of which we intend to take snaps of. ...more >>

Hi all, We are trying to copy a DB from SQL 2000 MSDE to SQL 2005 SP2 and this is the error message received. We are doing this by using the 'copy database' feature in SQL 2005, is this the correct way to migrate a DB to 2005? The database is not in use so we are unsure why this error would b...more >>

When I tried to restore a database says "TEST" from the "LIVE" backup in SQL2005, it reflected the change in "To database" field in the destination to "LIVE" when I selected "LIVE" in "From database" from the source. ie. I want this To database : TEST From database: LIVE But it cha...more >>

hi, has sql2005 sp come out ofr porduction yet? Thanks ...more >>

I have uncovered some interesting techniques that I wanted to share with everyone. In particular, they relate to the claimed "ambiguity" between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN) over *= syntax by Microsoft. A little background would be helpful. Microsoft...more >>

All, First a little background. My database backups are growing about 2 gigabytes a day. I would like to find out why of course. So I am looking to enable a trace flag to show me changed tables or files in the SQL DB file. Is this possible? Thanks, Noah ...more >>

Every so often I get blocks on actions that simply should not cause blocks, like selecting a text field from 100 rows of a 1000 row table. This causes other users to be "locked out", and of course that must be *my* fault. So I go into enterprise manager, find the culperit, and Kill Process it...more >>

Hi, I am using a Windows account that is a local admin on the server yet when I use Surface Area Configuration for features I only see Reporting Services and Integration Services. I understand that this feature is using WMI but what would stop me from seeing the actual Database Engine compo...more >>

I have the following Table Table1 ID = Int Division = VarChar FirstName = VarChar LastName = VarChar This is an example of the Data (Select * From Table1) ID Division FirstName LastName 1 E Chuck Martin 2 E Frank ...more >>

How do I determine the schema own by a user, remove them and drop the user? I am trying to drop a user and I get the following error message: The database principal owns a schema in the database, and cannot be dropped. Thanks....more >>

Hi, on a server I have installed SQL 2005 Enterprise Edition x64 Its a named instance and there is no default instance deployed. if I try to access some performance counters, I can't add them. the ADD button is disabled. some counters works fines, but others like "Active Transactions" can'...more >>

I found the following Q&A on Microsoft's site about multi-instance licensing with SQL 2005: How does licensing work with the multi-instance feature in SQL Server 2005? A. You can run multiple instances of SQL Server 2005 on a single computer. Multiple instances are used by organizations that ...more >>

Our monitoring system is notifying me "Log Flushes/sec Exceeds 2.0 fl/sec on SQL Server on xxx (server name) for 0 minutes. Last value was 22.77..." What shall I do to deal with this alert? Many thanks! Fei...more >>

There is one thing I am realy disappointed in SQL 2005. If there are table A and table B and if table B has a foreign key relis on table A, it makes me crazy when I need to upload table A from a test server to a production server. In SQL 2000 the Import/Export tool was excellent doing this...more >>

I'm trying to use OPTIMIZE FOR to optimize some queries and stored procedures against a table with over 1 billion rows in SQL 2005 Enterprise Edition x64. It seemed to be going well until I had to use a negative value for OPTIMIZE FOR. As soon as I tried a negative value, it told me that I ...more >>

The column I'm adding needs to be part of the clustered PK (it will be the last of three columns) so I need to recreate all the indexes. My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried two methods so far. Method 1: BEGIN TRANSACTION CREATE TABLE dbo.Tmp_cop...more >>

Is anybody has experience to DBA more than 100 SQl Servers at a time? If so what are the main startegy and main challenges you had? what is your best practice in terms of day to day admin and backup and recovery? ...more >>

why rowmodctr in sysindexes table shows negative values instead of zero after a index rebuild in sql 2000 ? Thanks, Ranga...more >>

hi.. the sql 2005 workgroup edition lists 2 cpus the sql 2005 standard edition lists up to 4 cpus is this a physical processor limit or does this include hyperthreading processors as far as windows sees ? we have a dual xeon processor proliant server here, so windows lists 4 cpus.. wil...more >>

I am currently trying to model a database and have great difficulty in determining the correct structure. General Subject - Aircraft Flights. Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc..... )) Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date, AirportDepar...more >>

Hi, I have a question on licensing. I have SQL 2005. The architecture I am going to follow for one application is like this; SQL 2005 database (backe-end), Mid layer also there will be SQL and the same also with a Front- Layer and the clients will be talking to the SQL servers which are loc...more >>

Hi, I have a table which contains several integer columns that represent similiar date related information. ie RenewalMonthDay,HomeMonthDay,MotorMonthDay etc. I need a view of the table where these different columns have been merged into a single column, so where there was one row with x...more >>

Hi, I have developed a database on my local Win Xp machine, using Express version of SQL server. The database is using a linked server to an Oracle database. On the XP machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver for the linked server. Now I'm trying to port...more >>

Hi Is it possible creating two databases in same instance of SQL Server when one of them case sensitive and another insensitive? Thanks ...more >>

Hi, I posted this same question a few months ago but still have not received a helpful response. As a certified DBA in 2000 I've done hundreds of views in Enterprise manager. I've just started to get my teeth into 2005. As one of my first forays, I thought I'd whip up a typical view. No ...more >>

Hi. We have a table called Master that has like ~400000 rows, and details table with ~5M rows that is masterid column and gets about 5-6k inserts during the business day, updates and deletes never happen. The details table get a lot of reads (dozens per second), and 90% of all queries to it ar...more >>

I need to work out a solution for my scenario. The Problem: two GUI applications that use SQL databases as back ends. We have a central office and three branch locations. The central office will have three users pulling from a combination of the applications at any given time. One branch o...more >>

MS update cannot install a Service Pack to SQL Server 2005 Express because a component of an earlier version (a CTP, the Reporting Services component) cannot be uninstalled. Can anyone tell me which set of registry keys I should delete after removing SQL Server 2005 in order to reinstall on a ...more >>

how do I determine the working dir? ...more >>

this is my DDL: CREATE TABLE [dbo].[Table1] ( [Code] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ParentCode] [int] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [...more >>

I have been designing a website for our fishing club and I need help with one of my queries because I'm not to good at it yet. The table has FishID, Angler, RecordDate, Species, Length, Photo for columns. The species can only be - Muskie, Northern, Walleye, Catfish, Crappie What I want is displ...more >>

Not sure which forum to post this in. Our application (VB.NET 2.0 framework) uses SQL 2000/2005 for the database. We have an Access DB that has some static data, such as the menu, the report names and queries, and all of the definations for all SQL objects (veiws, tables, stored procedures, fu...more >>

from sql server managment studio I click help, then tutorial. I get a nice window with links and explanations of lessons 1 thru 4. Just what I am looking for. I click lesson 1, get a page that bullets the topic of the lesson. But there is no link or button for starting the tutorial. I ha...more >>

Does anyone know the meaning of this error message? Event Type: Warning Event Source: MSSQLServer Event Category: (8) Event ID: 19011 Date: 4/27/2007 Time: 10:21:33 PM User: N/A Computer: Description: SuperSocket info: (SpnRegister) : Error -2146893006. Thank you. ...more >>

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a maximum set in the Management Studio. DOes it ever release and free up the memory. For example, thought the day SQL needs more memory and at the end of the day has 1 gig RAM used - if everyone logs out of our application so noth...more >>

Hi I need to create a User DSN based on SQL Server driver under Data Sources (ODBC). As I need to do it on multiple PCs, is there a script or another automated way to do this quickly? Thanks Regards ...more >>

I have an user where I need to give permission to spOA_Create but I don't want him to be a DBO. However if I give spOA_Create permission the user can run the below script. DECLARE @hr int DECLARE @ole_FileSystem int EXEC @hr = sp_OACreate 'WScript.Shell',@ole_FileSystem OUT EXEC @hr = sp_OAM...more >>

Is the below Where clause is sargable? Is using the function ISNULL makes it non-sargable? Create proc SearcCustomer @FirstName varchar(30) = null, @LastName varchar(30) = null, @Address varchar(30) = null as SELECT * from customers WHERE ((ISNULL(@FirstName,'')='' or FirstN...more >>

If I have an index on a varchar column and if I use like with a wildcard in front is the index is used? for example a query with a where clause. Where name like '%sam%' I read somewhere that SQL server 2000 will not consult statistics for query plan generation if the wildcard is put in fro...more >>

Maybe I just haven't found the right place yet, but... 1. In 2000 you could hold down the control key, select several tables and script them out into 1 file.... why doesn't this work in 2005 (or how do you do it in 2005) ? 2. In 2000 you could right click on a table and quickly export its ...more >>

Using SS2000 SP4. We have both named pipes (it's listed first) and TCP/IP enable in network libraries. I noticed in EM under current activity and process info for some processes named pipes is used and for other processes something called LPC is used. Is LPC aka TCP/IP? It looks like when the ...more >>

We have just started a new data cleansing project. One of the aims of the project is to identify similar records so that we can eliminate them. The problem lies in how to identify those similar records. Consider a table that has the columns: (ID, name …). A record whose name is “XYZ”...more >>

hello, i have a hierarchical select something like this with temp_containerCollect (id) as ( select c.id from LOGI_T_CONT4REFCOLLECT c where c.parent_id is null AND c.isActive like 'Y' /~filter_refuseCollection: AND c.refusecollection_id = {filter_refuseCollection} ~/ union all select ...more >>

Sorry if this angers anyone. I'm posting here and to the .NET group. I am unable to get a return value from a stored procedure in .NET using the following Sproc and .NET code Here is the code in my stored proc. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sprocRe...more >>

I want to drop a schema but it shows Fail to drop schema - Error 15150. I found that there is no object under the schema. Any idea? Thanks in advnace...more >>

Hi all, A friend has a db he needs to detach and xcopy over to another machine. He told me he's able to detach, xcopy it to another location on his hard drive, reattach it and it's fully functional; however, when he tries to xcopy it to another machine and attach it to the instance of SQL ...more >>

Using SQL 2005 Server Management Studio how can I: 1. List the Filegroup being used by each Table (or vice versa)? 2. Move a Table to a different Filegroup? Note on #2: My understanding is that if I change the storage location of a clustered index then the table should move with it. But ...more >>

Is there a way to identify users within sql server 2005/2000 that have not accessed a database within 90 days ? ...more >>

I am unable to run scheduled jobs that contain ActiveX steps. Jobs that contain only SQL excute successfully. The error in the job scheduler history is: Error creating security descriptor for shared objects (reason: No mapping between account names and security IDs was done). The step fail...more >>