sql

I'm storing code snippets in my database. How can I enable revisions/versions of them?

I have a pretty primitive table schema now ( in Postgres ): CREATE TABLE "public"."sandbox_demo" ( "id" int4 DEFAULT nextval('sandbox_demo_id_seq'::regclass) NOT NULL, "source" text DEFAULT NULL NOT NULL, "created" timestamptz(6) DEFAULT NULL NOT NULL, "modified" timestamptz(6) DEFAULT NULL NOT NULL ) However, this tab...

How to resolve Memory Leak issue in SQLite ?

Hello All, I am using SQLite 3.0.7.1. I have written below code can you please explain is there any memory leak ? char *sql_get_ID_from_extid_userinfo = "SELECT ID FROM User_Info where (extended_id=?)"; sqlite_db_ctx_t * ct = (sqlite_db_ctx_t*)ctx; int rc=0; sqlite3_stmt *ppStmt; rc = sqlite3_prepare_v2(ct->c...

Output Parameter With Linq

Hi I am new to and getting used to LINQ. I have worked with SPROCS that return result sets. No problems there. However I am having a problem with OUTPUT params & LINQ. the stored procedure i simple enough CREATE PROCEDURE [dbo].[PROCNAME] -- Add the parameters for the stored procedure here @tcStageOccurrences smallint output ...

Incorrect Syntax near the keyword UNION

I am getting an error message as 'Incorrect Syntax near the keyword UNION. The target site is - Void ProcessResults(System.Data.OleDb.OleDbHResult) and the stacktrace is as follows:- at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbDataReader.NextResult() at System.Data.OleDb.OleDbCo...

CHARINDEX always 0 when looking for substring

I have the following in a field ABCD, EFG, HIJ and can confirm this by selecting it. I need to be able to search this string for ABCD so I used the following: case when CHARINDEX(g.letters ,'ABCD') <> 0 then (- 2) else (- 1) end However its always returning -1 ...

Using an IN clause in Vb.net to save something to the database using SQL

Hello, I have a textbox and a button on a form. I wish to run a query (in Vb.Net) that will produce a query with the IN Values. Below is an example of my code myConnection = New SqlConnection("Data Source=sqldb\;Initial Catalog=Rec;Integrated Security=True") myConnection.Open() myCommand = New SqlCommand("UPDATE dbo.Recordings SET S...

Oracle: update a column in table a from values in other tables

I'm trying to support multiple databases for an app that I'm writing. The app mostly uses Hibernate, but it's incredibly inefficient to iterate over millions of rows and process them individually when a DML statement can process them in a fraction of the time. Therefore, for certain operations, I need to work out the SQL. I'm more of ...

Mergin two tables from different databases

Hi all, I have two tables from two different databases. For example: Table: Articles1 - Database: db1 Attributes: id date headline text Table: Articles2 - Database: db2 Attributes: id date headline text Now i want to make an article feed with articles from both tables combined and sorted by date. From my knowledge it isnt possible ...

Why use COALESCE() in sql statement

Hi, I'm refactoring some old code and stumbled upon this named query (It's using hibernate on top of mysql): delete F from foo F inner join user DU on F.user_id = DU.id where (COALESCE(:userAlternateId,null) is null or DU.alternate_id like :userAlternateId ) and ( COALESCE(:fooId,null) is null or F.foo_id like :fooId ) and ...

Added a table to a dbml, but it doesn't show up in code

I am using VS2010 with C#, and linq-to-sql. Does anyone have any ideas of where to start looking for a solution? My database has a 'Cats' table and there is a 'Feet' and 'EyeColour' table that are both linked to it with a one-to-one relationship. The 'Feet' table works fine with linq-to-sql, but 'EyeColour' does not. I dragged the Eye...

Error in formation of my SQL Query....

The below is my query,its getting a formation error,the query will describe the structure of it .....Could anyone help for this SELECT (CAST(Empid AS VARCHAR)+' '+EmployeeName) AS Employee ,COUNT(ActualDate)Total_No_Days ,(SELECT COUNT(ActualDate) from BufferListforBilling where BufferEmpName IS N...

Case in where clause

SELECT * FROM O_PLATI_DAUNE WHERE LUNA LIKE CASE WHEN LUNA = '7' THEN SUMA ='363623.72' WHEN LUNA = '8' THEN SUMA ='825159.25' WHEN LUNA = '9' THEN SUMA ='182730.99' WHEN LUNA = '10' THEN SUMA ='361722.74' WHEN LUNA = '11' THEN SUMA ='1787574.67' WHEN LUNA = '12' THEN SUMA ='3605005.68' ELSE 'N/A' END I...

sql select without sort

I guess this is a long shot but, is there a way to list the sql query without sorting... eg. I have select * from items where manufacID = 2 or manufacID = 1 or manufacID = 4 and I don't want them to be listed in the asc or decs order, but as i typed in... so 2,1,4. So, can i do that? ...

Violation of UNIQUE KEY Contstraints - Cannot insert duplicate key

I am pulling out my hair over here. I have a table that has a UNIQUE Key (IX_tblTable) and the unique key is on a column Number. I am parsing some data from the web and storing it in the table. My latest collection of data from the web contains number THAT ARE NOT CONTAINED IN THE DATABASE. so I am getting data from the site and all ...

Select with two counts on same column

I have two select queries: SELECT MONTH(date) AS month, count(DISTINCT ip) FROM table_name WHERE field = 1 GROUP BY month and SELECT MONTH(date) AS month, count(DISTINCT ip) FROM table_name WHERE field = 2 GROUP BY month how can I write one query to select SELECT MONTH(date) AS month, count(DISTINCT ip) [ for field = 1],...

Bypass last INNER JOIN in query

I have the following stored procedure which takes a user ID, a starting date, an end date, and a list of codes in a comma-delimited list, and it returns all activity records between those two dates which match one of the codes in the list. ALTER PROCEDURE [dbo].[ActivitiesSummary] @UserID varchar(30), @StartDate datetime, @EndDate dat...

How to select items from table by using a @local_variable table

Currently in a simple form i have the following declared table in code: declare @FileIDs as table ( ID int not null ) and i can fill it up e.g. manually like this: insert into @FileIDs values (1) insert into @FileIDs values (2) insert into @FileIDs values (3) Also i have another table called Files and ...

Own values in a sql query

I'm using this sql query: SELECT id, datetime FROM (SELECT id, datetime FROM DanskAktieAnalyse.dbo.vTest UNION ALL SELECT id, datetime FROM vTest2) AS articles ORDER BY datetime Is it possible to get an extr...

select a set of values as a column without CREATE

I'm trying to write a query that will return all QUERY_ID values alongside all matching TABLE_ID values, where QUERY_ID is not specified in any table, and I can't create tables, so have to specify it in the query itself: QUERY_ID TABLE_ID 1 1 2 NULL 3 3 4 4 5 NULL I feel like there ...

Hadoop and MS SQL Server Best Practices

Hi, I've been following Hadoop for a while, it seems like a great technology. The Map/Reduce, Clustering it's just good stuff. But I haven't found any article regarding the use of Hadoop with SQL Server. Let's say I have a huge claims table (600 million rows) and I want to take advantage of Hadoop. I was thinking but correct me if I'm ...