sql-server-2005

Log changes made to all fields in a table to another table (SQL Server 2005)

I would like to log changes made to all fields in a table to another table. This will be used to keep a history of all the changes made to that table (Your basic change log table). What is the best way to do it in SQL Server 2005? I am going to assume the logic will be placed in some Triggers. What is a good way to loop through all th...

Why is SQL Server not loading my index in memory?

I have a table that has 8 million records, with many fields, including lat/long values, and it has an index over the Lat/Long fields. I'm making a query to find the records that fall within a square around a point (to later refine into a circle), which is kind of: SELECT Lat,Long FROM Data WHERE (Lat BETWEEN 1 AND 2) AND (Long BETWEEN ...

Sql Query Linking Two Tables

Hi there, One doubt in MSSQL. There are two tables in a databases. Table 1 named Property contain fields PRPT_Id(int),PRPT_Name(varchar), PRPT_Status(bit) Table 2 named PropertyImages contain fields PIMG_Id(int),PIMG_ImageName(varchar),PRPT_Id(int),PIMG_Status(bit) These two tables follow a one-to-many relationship. That means the e...

Changing ASPNETDB name

Is there anyway to change the ASPNETDB and also using SQLExpress (2005) user instance ? I have changed my web.config's connectin string to <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Kooft.mdf; User Instance=true; Integrated...

T-SQL: Comparing Two Tables - Records that don't exist in second table

If UNION ALL is an "addition" in T-SQL. What is the equivalent of subtraction? For example, if I have a table PEOPLE and a table EMPLOYEES. And I know if I remove EMPLOYEES records from PEOPLE I will be left with my companies "CONTRACTORS". Is there a way of doing this that is similar to UNION ALL? One where I don't have to specify any ...

Inserting taking longer on Sql Server 2005 table

I have a table with about 45 columns and as more data goes in, the longer it takes for the inserts to happen. I have increased the size of the data and log files, reduced the fill factor on all the indexes on that table, and still slower and slower insert times. Any ideas would be GREATLY appreciated. ...

Permissions required for 'CREATE USER' in SQL Server 2005?

Hi - I am trying to create a SQL server login and database user from within my application, along with a custom application user row. I want these users to be able to create other users - i.e. the application will control who can/can't create users but I need all users to have permissions for creating SQL server logins and database users...

How to define a user-defined data type with addressable members?

I have an unusual situation to model in a MS SQL Server database: the primary key of a table is a multi-segment 'natural' key composed of 5 foreign keys (of fixed sizes). I'd like to be able to define a user-defined data type to implement the data structure based on a CHAR(8) primitive in such a way that the elements are addressable as ...

Does large SQL Server Memory Usage cause errors?

Just started getting a bunch of errors on our C# .Net app that seemed to be happening for no reason. Things like System.IndexOutOfRangeException on a SqlDataReader object for an index that should be returned and has been returning for a while now. Anyways, I looked at the Task Manager and saw that sqlservr.exe was running at around 1,50...

Which settings should be thoroughly evaluated in SQL Server?

I am not a DBA by any means, but being a web developer means that I will have to install, setup, and administer databases. In the past, I have just followed the default installation for SQL Server. Over time, I have grown smarter and learned that default installations almost always leave doors open and leak. So...What are the critical s...

SqlBulkInsert - How to set Fire Triggers, Check Constraints?

I'm performing a bulk insert with an ADO.NET 2.0 SqlBulkCopy object from a C# method into a MS SQL 2005 database, using a database user with limited permissions. When I try to run the operation, I get the error message: Bulk copy failed. User does not have ALTER TABLE permission on table 'theTable'. ALTER TABLE permission is r...

SQL Server View Problem

We have a View (call it X) that is the base view called by 2 other views(call them Y and Z). Today we made a change to view X, after that view Y and Z started bringing back data that was incorrect. When we were in Management Studio and ran Select * from Y(which is exactly how the view is called in in code) it would get back data that w...

Group repeated rows in TSQL

I have the following table and data in SQL Server 2005: create table LogEntries ( ID int identity, LogEntry varchar(100) ) insert into LogEntries values ('beans') insert into LogEntries values ('beans') insert into LogEntries values ('beans') insert into LogEntries values ('cabbage') insert into LogEntries values ('cabbage') insert...

Mars(multiple active resultset) : Disadvantages?

Does any one know what are the Disadvantages of Mars(multiple active resultset)? i.e does any one know any reason which should make one avoid to use MARS e.g. there are cases where cursors are more useful then MARS. ...

C#, sp_executesql and Incorrect Syntax

Hi, I'm calling the code below. On the line (IDataReader dr = cmd.ExecuteReader()) sql barfs with an Incorrect syntax near 'CompanyUpdate'. using (SqlCommand cmd = new SqlCommand("CompanyUpdate")) { cmd.Parameters.Add("@CompanyID",SqlDbType.Int); cmd.Parameters.Add("@Description",SqlDbType.VarChar,50...

SQL Server 2005: natural sort order for 2-columns tables with unique constraint

I have this table in my SQL Express 2005 database: CREATE TABLE [dbo].[test_sort_order]( [Col1] [int] IDENTITY(1,1) NOT NULL, [Col2] [nchar](50) COLLATE French_CI_AS NULL, CONSTRAINT [PK_test_sort_order] PRIMARY KEY CLUSTERED ( [Col1] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [UQ_test_sor...

Field value must be unique unless it is NULL

I'm using SQL Server 2005. I have a field that must either contain a unique value or a NULL value. I think I should be enforcing this with either a CHECK CONSTRAINT or a TRIGGER for INSERT, UPDATE. Is there an advantage to using a constraint here over a trigger (or vice-versa)? What might such a constraint/trigger look like? Or is the...

How to truncate and shrink log files?

How to truncate and shrink large log files in SQL Server 2005? How to apply truncation at regular intervals? Is there any difference between truncation and shrinking? Thanks in advance ...

Join in linked server or join in host server?

Here's the situation: we have an Oracle database we need to connect to to pull some data. Since getting access to said Oracle database is a real pain (mainly a bureaucratic obstacle more than anything else), we're just planning on linking it to our SQL Server and using the link to access data as we need it. For one of our applicati...

Map to Network Drive By SQL SP

Is there any way to map to a network drive by using a stored procedure? I have tried: xp_cmdshell 'net use Q: [shared_network_drive] [pwd] /user:[username]' but I got an error saying something like 'System error 1312 has occurred.' 'A specified logon session does not exist. It may already have been terminated.' However, when I ru...