sql-server

SQL Server ORDER BY/WHERE with nested select

I'm trying to get SQL Server to order by a column from a nested select. I know this isn't the best way of doing this but it needs to be done. I have two tables, Bookings and BookingItems. BookingItems contains StartDate and EndDate fields, and there can be multiple BookingItems on a Booking. I need to find the earliest startdate and lat...

'Invalid attempt to read when no data is present' - exception happens "sometimes" in Entity Framework

I get the above error sometimes during the read. The exception originates from ASP.NET SqlDataReader whenever you try to read data before calling the Read() method. Since EF does all these internally, I am wondering what else can cause this error. could it be network (or) db connectivity? thanks ...

Re-using aggregate level formulas in SQL - any good tactics?

Imagine this case, but with a lot more component buckets and a lot more intermediates and outputs. Many of the intermediates are calculated at the detail level, but a few things are calculated at the aggregate level: DECLARE @Profitability AS TABLE ( Cust INT NOT NULL ,Category VARCHAR(10) NOT NULL ,Income DECIMAL(10, ...

Creating LINQ to SQL Data Models' Data Contexts with ASP.NET MVC

I'm just getting started with ASP.NET MVC, mostly by reading ScottGu's tutorial. To create my database connections, I followed the steps he outlined, which were to create a LINQ-to-SQL dbml model, add in the database tables through the Server Explorer, and finally to create a DataContext class. That last part is the part I'm stuck on. I...

Conditional Operator in SQL Where Clause

I'm wishing I could do something like the following in SQl Server 2005 (which I know isnt valid) for my where clause. Sometimes @teamID (passed into a stored procedure) will be the value of an existing teamID, otherwise it will always be zero and I want all rows from the Team table. I researched using Case and the operator needs to c...

To store images in SQL or not?

Generally, I had thought it was always better to store images in the filesystem and link to it via the database entry. However, I am trying to optimize my db design and am having a few questions. My images are all really small thumbmails in black and white (not greyscale, but true B&W) and are 70x70 in size. If we take the images (whi...

to_date in SQL Server 2005

Does any one know how I would have to change the following to work with ms sql? WHERE registrationDate between to_date ('2003/01/01', 'yyyy/mm/dd') AND to_date ('2003/12/31', 'yyyy/mm/dd'); What I have read implies I would have to construct it using DATEPART() which could become very long winded. Especially when the goal would be to c...

Is there any better search method instead of string.Contains("keyword") using asp.net?

For example， the users of our app want to search products by entering a keyword of productName. The Products table of the sql server database contains about 10 million records. Is there any better and higher-performance search method implement instead of productName.Contains("keyword") using asp.net C# ? I use store procedure now, bu...

what really are database Constraints?

What really is constraints? Why i need to know about them? What are the types of constraints? ...

Best practices or tools for installing a SQL Server database

Best practices or tools for installing a SQL Server database I have a SQL Server database designed with the SQL Server GUI database editor/Visual Studio. What is the best way to "install" that database on other systems. Said another way how should I ship this thing? I know I can save the scripts and set the primary/foreign keys with T...

sql rounding value

I have been successful at rounding my value to one decimal point, but I would like to trim off the multitude of trailing zeros. For example, this: ROUND(SUM(Duration),1) ...ends up giving me: 16.9000000000000000 how do I trim all those trailing zeros. mssql ...

Copy data from SQL Server Express keep primary key

I want to copy data from a database to another, but I'd like to keep the primary key from the original database so it won't mess with the relationships. I'm using SQL Server 2008. ...

SQL Server: pulling and updating local data

Hi guys, I have two SQL Server 2008 databases called Anna and Bob. Bob has to pull and transform data from Anna to keep updated his tables. Ideally Bob will be always synchronized with Anna, but some delay would be acceptable. What is the best way to do this? Thanks in advance. ...

Are there disadvantages to using VARCHAR(MAX) in a table?

Here is my predicament. Basically, I need a column in a table to hold up an unknown length of characters. But I was curious if in Sql Server performance problems could arise using a VARCHAR(MAX) or NVARCHAR(MAX) in a column, such as: 'This time' I only need to store 3 characters and most of the time I only need to store 10 characters. B...

SQL Server Concatenate string column value to 5 char long

Scenario: I have a table1(col1 char(5)); A value in table1 may '001' or '01' or '1'. Requirement: Whatever value in col1, I need to retrive it in 5 char length concatenate with leading '0' to make it 5 char long. Technique I applied: select right(('00000' + col1),5) from table1; I didn't see any reason, why it doesn't work? but i...

is there anyway to add a primary autonumber key to an already existing database table

i have inherited a database application from someone else and there are a few tables that dont have any primary keys. I want to add a new column into an already existing table and have it autonumber (starting from 1). how would i go about doing this? ...

Inserting an image into sqlserver gives an "operand type clash"

I'm trying to save an image in a sql server 2000 database. The data type of the column is image. I get the following error: Error: Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: text is incompatible with image, SQL state 22005 in SQLExecDirect in C...

Nature of Lock is child table while deletion(sql server)

Dear Devs From couple of days i am thinking of a following scenario Consider I have 2 tables with parent child relationship of kind one-to-many. On removal of parent row i have to delete the rows in child those are related to parents. simple right? i have to make a transaction scope to do above operation i can do this as following; (i...

How to Convert using of SQLite to Simple SQL command in C#

I want to get start with DayPilot control I do not use SQLite and this control documented based on SQLite. I want to use SQL instead of SQLite so if you can, please do this for me. main site with samples http://www.daypilot.org/calendar-tutorial.html The database contains a single table with the following structure CREATE TABLE...

SQL Server - Select one random record not showing duplicates

I have two tables, events and photos, which relate together via the 'Event_ID' column. I wish to select ONE random photo from each event and display them. How can I do this? I have the following which displays all the photos which are associated. How can I limit it to one per event? SELECT Photos.Photo_Id, Photos.Photo_Path, Photos.Ev...