sql-server

adding an index to sql server

I have a query that gets run often. its a dynmaic sql query because the sort by changes. SELECT userID, ROW_NUMBER(OVER created) as rownumber from users where divisionID = @divisionID and isenrolled=1 the OVER part of the query can be: userid created Should I create an index for: divisionID + isenrolled divisionID + isenrolle...

Column 'dbo.mdSpaceSales.wkid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I am getting the above error for the query below : SELECT a.stid, a.pfid, pf.pfname, a.mdid, mm.mdDescription, a.categoryid, a.SummedSpace, a.AvgSpace, mm.size_sqft as MML_size, a.TotalRev, a.TotalProfit, a.wkCount, a.Lastwkinst FROM (SELECT wkid, stid, mdid, pfid, categoryid, sum([space]) as SummedSpac...

SQL Server 2008: Very slow query performance when dealing with specific date boundary

Right, this is an odd one. We have a web service that returns data to a silverlight client. The queries are generated against a SQL Server 2008 database using the entity framework. Most of these queries are date range based - pull results between this date and that, for example. Also, a view is used to make things a little easier. We h...

Grouping dates by month in an sql server query (stored procedure)

Im having a bit of a mental block on this one. I got booking system for hotel rooms and it contains a table as so BookingRoomLink BookingId (FK) RoomId (FK) Start_Date End_Date I'd like to query the data to extract occupancy levels for each month. I could do this manually (ie for the past month do something like this). SELECT Book...

Streaming uploaded files directly into a database table

I have several applications that allow users to upload attachments that are stored inside a database table. This has worked fine for several years because it was initially intended for smallish image files, but now they want to upload very large files (~80MB). This is causing the server to run out of memory (and the upload to fail) bec...

How do I copy a SQL Server database

Hello, I was wondering what my options are for copying a SQL Server database. If this is a repeat I'll delete my question but I couldn't find an answer when using the search. This is what I already tried: -Write the database to a sql script. When I do this I always seem to run into a problem where I have to manually adjust stuff. ...

Reporting services only displaying the last row.

I have built a report pretty basic just a table wiht 45 rows of data. Problem is its only showing one row of data and its the last row. It displays just fine in the dataset and proc. any suggestiosn? The table is in the body of the report. ...

SQL Syntax question

What does the following statement mean? Is it an equivalent for TOP? select +10 as ContentID ...

Is using a table inheritance a valid way to avoid using a join table?

I've been following a mostly DDD methodology for this project, so, like any DDD'er, I created my domain model classes first. My intention is to use these POCO's as my LINQ-to-SQL entities (yes, they're not pure POCO's, but I'm ok with that). I've started creating the database schema and external mapping XML file, but I'm running into som...

Using UDF for default value of a column

Hello, I created a UDF that I am using to generate a default value for a column. It works great, but I want to pass another field as a parameter into the function. Is this possible? For example, one of the fields is a DealerID field, and I want to pass in the value of the DealerID field into my UDF because I will use it to calculate t...

Is there a SQL equivalent to Linq .Skip(1000).Take(100)

Is there a SQL equivalent to the Skip method of Linq? For example I would like to be able to select the 1000 - 1100 rows from a database table. Or am I going to have to select the whole table and then find the rows in memory? My problem is that the table can be quite big and I'd like to avoid doing that if possible. ...

Restore SQL Server 2008 database to SQL Server 2000

I have to move an entire database from a SQL Server 2008 machine to a SQL Server 2000 machine. I created a backup using Management Studio 2008, copied it to the hard drive of the 2000 box, and from withing Management Studio 2008, I choose Restore Database to the 2000 box. I get an error message stating, "The media family on device ... ...

isnull(email,'') = '' is NOT interpreted by it self as (email is null or email = '')?

Hi, as a fact of performance which one is better? Is there a difference between the actual 3 three versions of sql-server (2000 / 2005 / 2008)? ...

SQL Server - How many users do I *really* need?

I'm setting up an application, and I'm looking into purchasing a license for SQL Server. My question is pretty simple (though may have a complicated answer...) How many users accounts do I really need, for SQL Server? The way I see it, I'd give one master administration account, maybe 2 or 3 user accounts, and then one application-base...

SQL Server CLR Trigger with SMO or Linq?

Hello, I've been looking at the possibility of creating a CLR trigger for insert, but every tutorial found gives me examples of using ado.net objects to do the logic - this is not very convenient way..(I don't think I have to convince anyone about that) I was wondering if maybe there is another way for doing this? SMO? Linq? Anything e...

Consolidating a COUNT query

I have a page where I am running an initial SQL query to get a list of subjects, then I loop over this query and run two additional queries for each record returned from the original subjects query (I happen to be doing this in ColdFusion, but not sure that really matters). These two additional queries do COUNTs for that specific subjec...

What is the VBA equivalent for using Command.Prepare in ADO.NET

Is there a way to use Command.Prepare, and Command.ExecuteNonQuery in VBA? The section entitled "Consider Using Command.Prepare" in the following article on ADO.NET describes objects and methods that are similar to ADODB properties and methods and I am wondering if there is an equivalent for VBA. ADODB has .Command, .Prepared, and .Par...

update columns values with column of another table based on condition.

I have two tables... table1 ( id, item, price ) values: id | item | price ------------- 10 | book | 20 20 | copy | 30 30 | pen | 10 ....table2 ( id, item, price) values: id | item | price ------------- 10 | book | 20 20 | book | 30 Now I want to: update table1 set table1.Price = table2.price where table1.id = table2.i...

Does SQL Server replication support this scenario?

Suppose I have central site and branch sites. Branch sites also have its own distribution center; Northern center, Southern center, Western center, and Eastern center. I would like all information replicate among its own region. But only center site that should have all data in all regions. Data inserted in each region can be updated...

Converting Northwind 2007 to SQL2005 Database

I have recently downloaded the Access 2007 Northwind example database and would now like to convert the database into a SQL 2005 database. I am using the Developer Edition of SQL 2005 and not SQL Express. I installed the Office 12.0 Access Database Engine OLE DB Provider drivers for working with Access 2007. So far I have 'created' a n...