sql

SQL query ...multiple max value selection. Help needed

Business World 1256987 monthly 10 2009-10-28 Business World 1256987 monthly 10 2009-09-23 Business World 1256987 monthly 10 2009-08-18 Linux 4 U 456734 monthly 25 2009-12-24 Linux 4 U 456734 monthly 25 2009-11-11 Linux 4 U 456734 monthly 25 2009-10-28 I get this result with the query: ...

Is it possible to run replication on a SQL 2005 DB which is a mirrored principal server?

We have a client that currently has a large busy database that they back up using database mirroring. As part of a reporting requirement we need to use some of the data stored in the database to run reports against but we cannot access the data directly. Is it possible for us to run transaction replication on that server to get our d...

Help with linq query with subqueries

Hi there, can anyone help?, i am stuck with a linq query.. basically i have a standard linq query that returns fields, 1 field (insurances) is actually another linq query like so // original from this in etc not included to keep msg short> select new Models.Custom.House.Insurance() { Id = v.IdH...

MySql: Can a stored procedure/function return a table?

Hi! Can a MySql stored procedure/function return a table without the use of temp table? Creating the following procedure CREATE PROCEDURE database.getExamples() SELECT * FROM examples; and later calling it with CALL database.getExamples() displays the example table - just as expected - but the following doesn't seem to be possib...

Help with writing a linq query

Hi there, can anyone help me write a linq query, its a bit confusing... Basically i have a variable which numeberOfDays in my code which for this example is 8 I have a table that lists this. DayFrom DayTo Price 1 3 20 4 5 30 6 8 40 8 25 150 I need to return fr...

Query Performance of joining before or after UNION

Let's say we have a query that is essentially using a union to combine 2 recordsets into 1. Now, I need to duplicate the records by way of typically using a join. I feel option 1 is in my opinion the best bet for performance reasons but was wondering what the SQL Query experts thought. Basically, I "know" the answer is "1". But, I am...

Encoding strings in XML from Oracle query

I'm producing XML right from PL/SQL in Oracle. What is the preferred way of ensuring that outputted strings are XML-conformant, with regards to special characters and character encoding ? Most of the XML file is static, we only need to output data for a few fields. Example of what I consider bad practice: DECLARE @s AS NVARCHAR(100...

How do I get around a 'circular reference' in an Inner join

Hi, I have the following 'circular dependency' in my Inner Join, any ideas how to get round it? SELECT *FROM Reference INNER JOIN ReferenceInActivity ON Activity.ActivityID = ReferenceInActivity.ActivityID INNER JOIN @tbActivity AS Activity ON ReferenceInActivity.ReferenceID = Reference.ReferenceID I get the error: Msg 4104, Level 1...

CREATE DATABASE using file in default path

I want to create an SQL script that creates a database. Right now, I have this: CREATE DATABASE [Documents] ON PRIMARY ( NAME = N'Documents', FILENAME = N'Documents.mdf') LOG ON ( NAME = N'Documents_log', FILENAME = N'Documents_log.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS However, this generates the following error: Msg 5105,...

Query a dataTable by large Query String

I have a datatable that i want to query. the query is very large and complicated and it works when i run it in the SQl Server Editor - so i have the query text. i need to query the datatable with this query String. To Translate the query into linq will take years, and also Select() method of DataTable won't handle it. How can i operate...

Need a return value from a stored procedure in C#

With the following Stored Procedure in SQL Server 2005 IF OBJECT_ID('[dbo].[UserProfile]') IS NOT NULL DROP PROCEDURE [dbo].[UserProfile] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE [dbo].[UserProfile] ( @UserId VARCHAR(20) ) AS IF @UserId = 'userId' BEGIN SELECT @UserId = 'Yes' END ELS...

What is the problem with foreign key cascade multiple paths and cycles?

In MSSQL 2005 I just struck the infamous error message: Introducing FOREIGN KEY constraint XXX on table YYY may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Now, StackOverflow has several topics about this error message, so I've already got the...

Is there a way to get rows_examined in MySQL without the slow log?

I'm building some profile information for a home grown app. I'd like the debug page to show the query sent along with how many rows were examined without assuming that slow_log is turned on, let alone parsing it. Back in 2006, what I wanted was not possible. Is that still true today? I see Peter Zaitsev has a technique where you start ...

How to use an IN clause in iBATIS?

I'm using iBATIS to create select statements. Now I would like to implement the following SQL statement with iBATIS: SELECT * FROM table WHERE col1 IN ('value1', 'value2'); With the following approach, the statement is not prepared correctly and no result returns: SELECT * FROM table WHERE col1 IN #listOfValues#; iBATIS seems to re...

Atomic Upgrade Scripts

With my database upgrade scripts, I typically just have one long script that makes the necessary changes for that database version. However, if one statement fails halfway through the script, it leaves the database in an inconsistent state. How can I make the entire upgrade script one atomic operation? I've tried just wrapping all of ...

Best practise when updating individual fields in a database record

I have created a Data Access Layer using .NET. Everywhere that I update a database record, I have used sql along the lines of UPDATE Customer SET FirstName=:FirstName, LastName=:LastName, Address1=:Address1, Address2=:Address2,....etc This means that every field in the record is updated, even though only one field may have been cha...

DataSet Takes Only One DataGridView Change When Calling DataAdapter.Update

I am using the CellEndEdit event in DataGridView to detect when a user has finished editing a cell. I want to post the results back to the database. The datagridview is bound to a DataView. I retain the original DataAdapter (SQLDataAdapter) used to fill the dataset and thus display the data to the user. All update and select commands are...

Multiple SQL UPDATE statements OR Single with CASE in it?

I've built a trigger where I am trying to keep priorities in order and not allow duplicate priority values. There are some things to consider. The user can free form the priority in. There is nothing blocking them from picking the same priority as another item. When a value is entered equal to another value the newly prioritized ite...

How can I determine type of mysql database : whether it is InnoDB or MyISAM ?

How can I determine type of mysql database : whether it is InnoDB or MyISAM ? How can I convert MyISAM to InnoDB or vice-versa ? ...

More advanced logic for ordering results of a query in T-SQL?

I am currently writing a SQL query that should display a tree-view of areas inside a building with areas, sub-areas, etc. Unfortunately I have been unable to mimic the ordering used by some of our software tools. I'm restricted to MS SQL 2000 so the question of order becomes much more complicated and I'm just over my head at this point. ...