sql

Does this mySQL "spatial" query work in SQL Server 2008 as well?

Before I embark on a a pretty decent overhaul of my web app to use a spatial query, I'd like to know if this MySQL query works in SQL Server 2008: SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FR...

SQL Query for filtering columns returned

I want to return columns based on some meta data in an other table. i.e. i have my table which contains 10 columns, and another table which contains those columns denormalise with metadata to do with them. i.e. Table - Car: columns - Make,Model,Colour and another table called "Flags" which has a row for each of the above columns and e...

Retrieving XML element name using t-SQL

If I have: <quotes> <quote> <name>john</name> <content>something or other</content> </quote> <quote> <name>mary</name> <content>random stuff</content> </quote> </quotes> How do I get a list of the element names 'name' and 'content' using T-SQL? The best I've got so far is: declare @xml xml set @xml = ... sele...

What is wrong with this sample query?

SELECT table0.person_name, table5.animal_name FROM table1 JOIN table0 ON table1.person_id = table0.person_id JOIN table5 ON table1.animal_id = table5.animal_id WHERE table1.aa = input1 AND table1.bb = input2 AND table1.cc = input3 AND table1.dd = input4 an answerer said If you ever even get close, you're doing it wrong :) ...

Database Modification or start over?

So I'm currently working on rebuilding an existing website that is used internally at my company for project management, at heart it is a bug tracking utility that has some customer support and accounting operations linked into it. Currently the database model is very repetitive, a good example of this is, currently a UserId is linked i...

Entity Framework Class with Extra Field

Hi, im working with Entity Framework, SQL and C#. I have a table on SQL called Clients. I have to show the clients on a Grid in a Form and select some of the clients using a Check. So, i need 1 check for each client on the grid. I try with "Anonymous Types" but this are ReadOnly properties and i need ReadWrite properties. It is possi...

Trigger insert old values- values that was updated

I need to create trigger in SQL Server 2008 that gone insert all values from one row in which some value was changed into Log table! For example if i have table Employees that have column id, name , password, and i update this table and insert new value for column name, than i need to insert values that was in table Employees after upda...

ORA-00933: SQL command not properly ended (in simple insert statement)

The following simple statement: INSERT INTO mydb.inventory (itemID) VALUES (:itemID) WHERE playerID = :ID; Generates the following error: ORA-00933: SQL command not properly ended I have tried it without the semi-colon as well as with, but both give me the error. I am certain that the variables are being bound as well. All my Goo...

C#: Services access the dataprovider class running the sql statements - correct approach ?

Hello, is this a common and/or good approach? In my ViewModel(Wpf) or Presenter(WinForms) I do this: ICustomerService customerService = MyService.GetService<ICustomerService>(); ICustomerList customerList = customerService.GetCustomers(); the CustomerService class looks like this: public class CustomerService : ICustomerService { ...

Player to class database relationship in a game

I'm attempting to figure out how to model a normalized database where a player may have multiple classes. What is the relationship between the player and the class objects? Each player will be able to have multiple classes and each class will then grant a player multiple skills as well. I'm familiar with normalized database and workin...

is there an error on this sql request?

$sql=mysql_query("INSERT INTO admin_cont (contact_idemail , contact_timestamp , contact_objet , contact_message) VALUES ('".$_COOKIE['zzzzz']."', '$timestamp', '$objet', '$message' ") or die(mysql_error()); i keep getting the error: You have an error in your SQL syntax; check th...

SQL message listener in C#

Hi, I have a C# Windows service that listens to a HTTP port and fires an action upon receiving a HTTP GET request. The action is typically the execution of an application that resides in the same machine (running Windows XP Professional or above). I now have the requirement for the C# Windows service to listen to incoming SQL queries ...

References to same table

Hi, Im writing a simple editor for a game, to manage the relationships between creatures. The creatures table is setup with a id, name etc. and a boolean is_prey column; what I need is to figure out how a) an ORM relation can be defined between id's from the same table b) how to limit the 'available' other-nodes to only include the ones...

How to concatenate more than 2 fields with SQL?

I am trying to use CONCAT with SQL to concatenate 3 fields, and get the following error: Incorrect parameters in the call to native function 'CONCAT' The query is as follows SELECT CONCAT(guests.lastname,', ',guests.firstname', ',guests.passport) AS display FROM guests WHERE guests.uuid = '1' How do you concatenate more than 2 ...

Cannot connect to SQL server 2008 Express instance

Hello, i have an instance of SQL server 2008 and an instance of SQL server 2008 Express. i have been mainly using the SQL server 2008 instance with the following connection string... <add name="Local-DB-connection" connectionString="Data Source=JONATHAN-PC; Database=dbname; User ID=uname; Password=pword;" providerName="System.Data.Sql...

alter table to create columns for other table

Hi, can anyone help me with creating column for table A that are from table B ? let's say i have table B: column name:WORDS row 1:ONE row 2:TWO row 3:THREE and want to have table A: column name: ONE | TWO | THREE I need this to be created and not some VIEW thanks ...

How do you put raw-sql projections in an object using Hibernate?

Let's say I have a query that begins with the following projections: SELECT t.term as term, count(g.id) as amount This is raw sql, and I am using createSqlQuery on the Hibernate session object. What I'd like to do is take these projections and put them in an object that has a "term" and "amount" properties. With HQL, we can use "sel...

Best Practice for Foreign Key column position in table

This might be a silly question, but here goes: Is there a standard or best practice, which specifies in what order the foreign key columns in a table should? I for one like the idea of the PK being the very first column in the table, followed by all the foreign keys, and then the columns thats relevant to that table.. Other way of do...

Date time string insert to db

Hi I am trying to insert the following to SQL Server 2005: INSERT INTO tb_UserLoginTimes (UserID, LoginDateTime) VALUES (1235,2010/07/06 10:38:44) But am getting the following error. Incorrect syntax near '10'. Do I need to escape the colon? If so how do I do that? Real noob at this so my apologies. ...

HTML5 Filereader and SQL Database Crashes Chrome

I'm not sure if I'm using incorrect syntax, but I am unable to read an HTML5 Web SQL database BLOB entry into a DataURL using the HTML5 Filereader API. Here, try it for yourself on Chrome Dev Channel: http://austin.99k.org/index.html Choose multiple files, then inspect the page and go to storage. You'll see that a database has been cr...