sql

SQL: How to use a column that was just added

I am trying to add 2 columns and then I would like to set some values to them but I get a compile-error saying the column does not exist. I am using the following script: IF @LogProcessed = 0 Begin IF NOT EXISTS (select column_name from INFORMATION_SCHEMA.columns where table_name = 'SYSTM_FRM' and column_name = 'SF_Ip_TXT') ALTER TABLE...

Where can I get the Microsoft.SqlServer.Mso.dll & Microsoft.SqlServer.ConnectionInfo?

I can't seem to find these anywhere, I want to do some SMO stuff. I am using VS 2010 Ultimate and SQL Express 2008; thank you. ...

Speed up this query joining to a table multiple times

Hi, I have this query that (stripped right down) goes something like this : SELECT [Person_PrimaryContact].[LegalName], [Person_Manager].[LegalName], [Person_Owner].[LegalName], [Person_ProspectOwner].[LegalName], [Person_ProspectBDM].[LegalName], [Person_ProspectFE].[LegalName], [Person_Signatory].[LegalNa...

MySQL: LOAD DATA INFILE not trivial

I'm trying to import data from *txt to MySQL database with PHPMyAdmin. Using LOAD DATA INFILE. Text file looks like: Graph 1, order 7. 0000000 0000000 0000000 0000000 0000000 0000000 0000000 Graph 2, order 7. 0000001 0000000 0000000 0000000 0000000 0000000 1000000 Graph 3, order 7. 0000001 0000001 0000000 0000000 0000000 0000000 11000...

Sql (partial) search in a list and get matched fields

I have two tables, I want to search TermID in Table-A through TermID in Table-B and If there is a termID like in Table-A and then want to get result table as shown below. TermIDs are in different length. There is no search pattern to search with "like %" TermIDs in Table-A are part of the TermIDs in Table-B Regards, Table-A ID ...

Interesting Row_Number() bug

I was playing with the Stack Exchange Data Explorer and ran this query: http://odata.stackexchange.com/stackoverflow/q/2828/rising-stars-top-50-users-ordered-on-rep-per-day Notice down in the results, rows 11 and 12 have the same value and so are mis-numbered, even though the row_number() function takes the same order by parameter as th...

postgres subquery w/ derived column

The following query won't work, but it should be clear what I'm trying to do: split the value of 't' on space and use the last element in that array in the subquery (as it will match tl). Any ideas how to do this? Thanks! SELECT t, y, "type", regexp_split_to_array(t, ' ') as t_array, sum(dr), ( select uz from f.tfa where tl = t_array[-...

Database design -- does it respect 3rd NF?

Hi I have the following relations (tables) in a relational model Person person_id, first_name, last_name, address Student person_id, matr_nr Teacher person_id, salary Lecture lecture_id, lect_name, lect_description Attendees lecture_id, person_id, date I'm wondering about the functional dependencies of Student and Teach...

Remote SQL login page C#

Hey, I am trying to create a login page that checks the username and password with the database on the server. The server is located in a different country. This is the code I have so far: #region Building the connection string string Server = "XX.XXX.XX.XX, XXXX"; string Username = "_Username_"; ...

Help with interesting VS2010 and SQL2008 bug

Hey, I'm using Visual Studio 2010 to create a webpage. I'm calling some tables from SQL Server 2008. Here is where I'm confused... The code runs fine with no errors. The pages work except I'm missing my rows in my 3rd column from the table. Everything else shows up. Ive checked to make sure the names are matching everywhere and that ...

Twisted + SQLAlchemy and the best way to do it.

So I'm writing yet another Twisted based daemon. It'll have an xmlrpc interface as usual so I can easily communicate with it and have other processes interchange data with it as needed. This daemon needs to access a database. We've been using SQL Alchemy in place of hard coding SQL strings for our latest projects - those mostly done f...

MySQL - Limit a left join to the first date-time that occurs?

Simplified table structure (the tables can't be merged at this time): TableA: dts_received (datetime) dts_completed (datetime) task_a (varchar) TableB: dts_started (datetime) task_b (varchar) What I would like to do is determine how long a task took to complete. The join parameter would be something like ON task_a = task_b AN...

How do I assign different weights to columns in SQL Server Full Text Search?

I'm using SQL Server 2008 FTS and I'd like to be able to integrate weighted rankings into my search results. However, I'd like to weight the columns that a search term is found in differently. For example, if I have a Title column and a Description column, I want matches fond in Title to rank higher than matches in Description. Is th...

How to combine two rows and calculate the time difference between two timestamp values in MySQL?

I have a situation that I'm sure is quite common and it's really bothering me that I can't figure out how to do it or what to search for to find a relevant example/solution. I'm relatively new to MySQL (have been using MSSQL and PostgreSQL earlier) and every approach I can think of is blocked by some feature lacking in MySQL. I have a "...

How do I verify/validate a float before storing as decimal(4,1) in SQL?

I have defined a column in SQL to be decimal(4,1), null which means I am storing four digits, up to one of which can be to the right of the decimal point. The source data should always be in the range of 0 to 999.9, but due to an error beyond my control, I received the number -38591844.0. Obviously this won't store in SQL and gives the ...

send dbmail on @@error from sql server 2005

Hi, I am trying to send database mail when error occurs inside the transaction.My setup for dbo.sp_send_dbmail is correct , when I execute the proc I do get an email within 1 min. However when I try to use dbo.sp_send_dbmail inside another proc within transactions than I do not get the email. Sql server does show in the result window ...

Translate SQL to OCL ?

I have a piece of SQL that I want to translate to OCL. I'm not good at SQL so I want to increase maintainability by this. We are using Interbase 2009, Delphi 2007 with Bold and modeldriven development. Now my hope is that someone here both speaks good SQL and OCL :-) The original SQL: Select Bold_Id, MessageId, ScaniaId, MessageType, Me...

Return Empty String as NULL

I have a listbox select and I want when the user selects null for the empty string it produces to pull the nulls from the SQL table. Here's what I have now. Blank strings return nothing because there are no empty fields in the table. SELECT * FROM dbo.Table WHERE ID = " & TextBox2.Text & " and And Field1 IN (" & Msg1 & ") How do I co...

See queries that hit SQL

Is there a way using sql 2008 Management Studio to look at the queries that hit the server? I'm trying to debug a program and I get messages like "Incorrect syntax near the keyword 'AND'". Since the queries are being dynamically generated it's a hassle to figure out what is going to the server. Any help is appreciated! ...

Convert a Date to a String in Sqlite

Is there a way to convert a date to a string in Sqlite? For example I am trying to get the min date in Sqlite: SELECT MIN(StartDate) AS MinDate FROM TableName I know in SQL Server I would use the SQL below to accomplish what I am trying to do: SELECT CONVERT(VARCHAR(10), MIN(StartDate), 101) AS MinDate FROM TableName Thanks! ...