Cursors in SQL Server
Possible Duplicate: What is wrong with Cursors ? Why we say cursor will affect the performance. Even we use some other looping instead of cursors it works similarly right? Please advise ...
sql
Insert problem in oracle database
HRESULT: 0X80040E2F (DB_E_INTEGRITYVIOLATION) Error message: Value violated the integrity constraints for a column or table. Can anybody tell me how to solve this error. ...
"column XYZ must be in group by" --> "cannot group by aggregate column"
Hi, i have trouble with following sql-statement (trimmed): SELECT nr, (CASE WHEN SUM(vkdtab.amount*liter)<>0 AND jjjjmm BETWEEN 201001 and 201009 THEN SUM(net)/SUM(vkdtab.amount*liter) ELSE 0 END) as return FROM tab GROUP BY 1,2,3 It should give me the amount/liter of items in a special timeframe, but I...
Is there an easy way to revert the changes that SQL Azure Data Sync Tool does to a local database?
Hi, I tried using the SQL Azure Data Sync Tool and successfully transfered a local database from my dev machine to a SQL Azure account. However, later on I noticed that the tool has silently made a bunch of changes to my local database - definitely not a nice thing to do without any notification or confirmation dialog. Does anybody kno...
NHibernate "SELECT ... FROM (SELECT ..." Criteria
I have a SQL query I'm having trouble creating using NHibernate Criteria: SELECT ID, COLA, COLB, COLC FROM table WHERE COLC='something' AND ID IN (SELECT ID FROM (SELECT MAX(ID) as ID, COLA FROM table WHERE COLB='something' GROUP BY COLA) subquery) ORDER BY ID DESC I originally had this slightly simpler query: SELECT ID, COLA, COLB, ...
Procedure or function "" expects parameter '', which was not supplied
Hello, I have a problem with Database thing. The stored Proc has to be called with either 3 or 4 parameters. If ImageID is not given, then it has to get into If loop and execute. if ImageID is given, execute the else part of the stored proc. But i have no idea why is that showing Procedure or function "" expects parameter '@ImageID', ...
Setting rank to NULL using RANK() OVER in SQL.
In a SQL Server DB, I have a table of values that I am interested in ranking. When I perform a RANK() OVER (ORDER BY VALUE DESC) as RANK, I get the following results (in a hypothetical table): RANK | USER_ID | VALUE ------------------------ 1 | 33 | 30000 2 | 10 | 20000 3 | 45 | 10000 4 | 12 | 500...
SQL statement won't insert into DB.sdf
Hi guys, I am using Visual Studio 2008 and have connected a database correctly as I have done a login that works fine, although when I try to insert information submitted in the text boxes a different table, it doesn't enter after I end program to check it still has no data in. Any ideas? Dim con As SqlCeConnection = New SqlCeConnec...
mysql, using if
I have this sql query: ... LEFT JOIN users ON users.id = mod.id and mod.level = 1 ... But if don't found any result with mod.level = 1, i wish search with mod.data > 1 (users.id = mod.id and mod.data > 1) ...
Joining Tables,- Normalizing Fields
I have two tables. One table is named "Posts", the other is "Threads". Each of these has 3 columns named (id, author, content). The Posts table has a specific (thread) column. The thread column has to correspond to the id in the Threads table. What I'm trying to do is make one query that will select the thread, and all of its posts,...
Export all sql generated by NHibernate to a text file.
I have a console app that exports my entity models to my sql db and I am able to export that schema to an .sql file without issue. However, my DBA's want .sql files for all the initial data that should be populated in the db as well. My console app uses NHibernate to save a bunch of objects to the database right after it creates the data...
Left Outer join and an additional where clause
Hi, I have a join on two tables defined as a left outer join so that all records are returned from the left hand table even if they don't have a record in the right hand table. However I also need to include a where clause on a field from the right-hand table, but.... I still want a row from the left-hand table to be returned for each re...
query works on MySQL, but no in PHP, why ??
Hi, I need to display the results of this query : SELECT * FROM projects WHERE PrestaCmd LIKE '% A - CREP - DPE - %' but in PHP, this query doesn't work :s This is my code : $req = "SELECT * FROM ".$table." WHERE PrestaCmd LIKE '%".$ch."%'"; echo $req; //returns : SELECT * FROM jos_projectlog_projects WHERE PrestaCmd LIKE '% A ...
Stored Proc in sql that does not return the value
My function isn't returning anything - strReturn is empty: try { SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@MerchantID", MercahntID), new SqlParameter("@LoactionID", LoactionID) }; SqlHelper.ExecuteNonQuery(DbConnStri...
SQL conundrum, how to select latest date for part, but only 1 row per part (unique)
I am trying to wrap my head around this one this morning. I am trying to show inventory status for parts (for our products) and this query only becomes complex if I try to return all parts. Let me lay it out: single table inventoryReport I have a distinct list of X parts I wish to display, the result of which must be X # of rows (1 r...
How can I efficiently and easily import/export large business data in .NET ?
Hi, What would be the best data format (XML, Database, etc.) for large business data when we want to efficiently and easily load all of it (import) into business objects in .NET ? I want to load all the data in one go and create all business objects. The data must have a kind of a "schema" to enable other people to create it. Also n...
Is bulk insert atomic?
I have table with auto increment primary key. I want insert a bunch of data into it and get keys for each of them without additional queries. START TRANSACTION; INSERT INTO table (value) VALUES (x),(y),(z); SELECT LAST_INSERT_ID() AS last_id; COMMIT; Could MySQL guarantee, that all data would be inserted in one continuous ordered flow...
Semantic difference between join queries
I have two queries that I thought meant the same thing, but I keep getting different results and I was hoping someone could explain how these are different: 1. select * from table1 a left join table2 b on a.Id = b.Id and a.val = 0 where b.Id is null 2. select * from table1 a left join table2 b on a.Id = b.Id where b.Id is null ...
sql search query still not producing expected results.
I need a count and search query to work properly. The count query appears to be working properly, however the search query is not. Count query: SELECT COUNT(DISTINCT tg_id) FROM tg_keywords WHERE tg_keyword LIKE 'keyword_1' OR tg_keyword LIKE 'keyword_2' ORDER BY tg_keyword LIKE 'keyword_1' AND tg_keyword LIKE 'keyword_2 DESC Re...
SQL Server 2008 - sql_variant in XML nodes.value
Hi All, Trying to understand why you can't use sql_variant for the value type when using the XML nodes function within SQL Server? I have a scenario where I will be dynamically parsing some XML input and being able to use the sql_variant would be a nice alternative to having to assign a datatype variable or doing a lookup on sys.columns...