Related to a question I asked earlier here, I've found a problem which is eluding me (obviously). The original question was how to select a min and max date from a daily table based on a monthly table where some daily table dates could be missing. Basically what I needed was columns containing the month date (always the first), the earl...
Hi, I am writing a c# unit test that validates string properties for an ORM class against the target database, always SQL 2008, and the class that the data maps to. Checking that a specified foreign key is valid in the DB is easy: static private bool ConstraintExsits(string table, string column, ConstraintType constraintType) ...
Hi, i need to dump my sql query result into the text file. i have created the following query, DECLARE @cmd VARCHAR(2048) SET @cmd = 'OSQL -localhost -CRN370 ' + ' -UCRN370 -PCRN370' + ' -Q"SELECT TOP 5 GageId FROM EwQMS370..msgages"' + ' -oc:\authors.txt' EXEC master..xp_cmdshell @cmd, NO_OUTPUT The above query created the tex...
Hi All, I have an XML file and I open that in SQL Server using OPENXML and then read the values in the XML file and insert them into the table. Assume that the XML structure is like this "<Student><name>XYZ</name><id>123</id><fathersname>XYS</fathersname><fathersid>3489</fathersid></Student>". Now I need to add this as two different row...
Our solution has a DB project for versioning DB structure. Whenever I open any SQL files in it, it is an absolute pain to edit it. Every keystroke takes literally 2 seconds to be executed (during which some thread in VS takes up 100% of the CPU core it is using). My machine is a fairly decent Intel Core2 Duo E6500 with 2GB of RAM, and no...
Hi, I am using SQL 2000. I have a stored proc (spGetApplicantList) which cannot be modified. I need to get the unique LastNameInitials of all the records in that stored proc, so the list of applicants can be sorted alphabetically. Basically what I need is Select DISTINCT LEFT(LastName, 1) as [LastNameInitial] from spGetApplicantList Or...
imagine there is an sql and in some part of that SP, there is WHERE clause which looks for two IDs; ... WHERE ID IN (123,1245) instead of using IN , if I use OR; what would I gain or loose. OR both of them is equal? ...
Hi, 1st disclaimers: I'm not a programmer, never was had never been taught "higher" math despite the upper statements sometimes I have to work with SQL. Now I need to create a view from a select of my colleagues (who had used four unions looked like he do not know how to use or in the where part...), and now I'm here. Is there a si...
We have a project where data is written to a logging table. Now, when investigating a problem, it would be useful to query for the problem row, and also get the surrounding rows, so that we can easily see what led up to the problem. The log table had a timestamp field, so we can order by that. Essentially I want something like "grep -C"...
Hi. I'm trying to do something and I'm not sure how to do it. I have some data like this: WITH a AS (SELECT theid, thename, thetimestamp FROM mytable) SELECT thename, TRUNC (thetimestamp, 'HH24'), COUNT (theid) FROM a group by thename,trunc(thetimestamp,'HH24') ORDER BY COUNT (theid) desc) which returns me the count grouped by...
Hello, I want to only grab the last post of all topics in a category (Category->Forum->Topic->Post) from a phpbb database in a single query. Currently I have cooked up this, but it returns only the first post, not the last. SELECT *, MAX(p.post_id) FROM phpbb_forums f, phpbb_topics t, phpbb_posts p WHERE f.parent_id IN (<categories>) A...
Dear all, I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module: module SqlHunter class ActiveRecord::ConnectionAdapters::AbstractAdapter @@queries = [] cattr_accessor :queri...
I have a SQL-statement like this: SELECT name FROM users WHERE deleted = 0; How can i create a result set with a running number in the first row? So the result would look like this: 1 Name_1 2 Name_3 3 Name_12 4 Name_15 ... ...
Hi, we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn't work: OracleCommand command = null; OracleDataReader dataReader = null; string sql = "select * from TEST_TABLE where COLUMN_1 = :COLUMN_1"; try {...
I posted an earlier version of this question last week, but after further consideration I realized the situation is a bit more complicated than first described, and I could use some further help: My SQL table has 3 fields that impact my problem: "Priority" as real, Start_Date as datetime, and Furnace_Name as varchar(10). As the user add...
Hello, I am looking for any tips or resources on importing from excel into a SQL database, but specifically when the information is NOT in column and row format. I am currently doing some pre-development work for a project, and in most ways I would like to use SSIS for the project. The one area that my research and googling is leaving...
I have an array of output from a database. I am wondering what the cleanest way to filter the values is example array Array ( [0] => Array ( [title] => title 1 [cat_title] => Drawings [sec_title] => Portfolio ) [1] => Array ( [title] => title 2 ...
How do I get the day of the week (in ddd format, so Mon, Tue etc.) in SQL ? I don't see anything about it in the CAST and CONVERT documentation.. ...
For example, I have a table, and there is a column named 'Tags'. I want to know if value 'programming' exists in this column. How can I do this in ADO.NET? I did this: OleDbCommand cmd = new OleDbCommand("SELECT * FROM table1 WHERE Tags='programming'", conn); OleDbDataReader = cmd.ExecuteReader(); What should I do next? ...
Hi Stackoverflow super stars... Here's my problem. I have a stored procedure that outputs a pretty straightforward table of grouped and summed values. Typical stuff... company name, number of clients at a company, business rep for the company, annual revenue for the company, etc. That works fine. What I need now, is the summary row....