I've been struggling with this one SQL query requirement today that I was wondering if someone could help me with. I have a table of sports questions. One of the columns is the team related to the question. My requirement is to return a set number of random questions where the teams are unique. So lets say we have the following table a...
I am writing a Silverlight client that interacts with an SQL database via ASP.NET 2.0 web services, which I am also developing. One of the services needs to return the results of a stored procedure and it works well. However, one parameter of the stored procedure needs to be retrieved from a different location before the stored procedure...
Does SQL Server Express 2005 contains Reporting Service. If yes I installed SQL SErver express 2005 edition but i am not able to see Reporting Service. Help... Advance services, doesnt helps.. ...
Is there a function for SAP DB to get the length of a string in SQL? ...
I would like to randomly sort a result in a repeatable fashion for purposes such as paging. For this NEWID() is too random in that the same results cannot be re-obtained. Order by Rand(seed) would be ideal as with the same seed the same random collection would result. Unfortunately, the Rand() state resets with every row… does anyone ...
I have the following data: ExamEntry Student_ID Grade 11 1 80 12 2 70 13 3 20 14 3 68 15 4 75 I want to find all the students that passed an exam. In this case, if there are few exams that one student attended ...
For a large database (thousands of stored procedures) running on a dedicated SQL Server, is it better to include SET NOCOUNT ON at the top of every stored procedure, or to set that option at the server level (Properties -> Connections -> "no count" checkbox)? It sounds like the DRY Principle ("Don't Repeat Yourself") applies, and the opt...
I have an index -- let's call it IX_MY_INDEX -- in a SQL Server table (both compatibility modes 80 and 90) that I would like to determine the size of. How do I do this? Update: Allain Lalonde's second solution below only works when the compatibility mode is set to 90; however, the particular database I am working on is in compatibility ...
I'd like the community's take on some thoughts I've had about Linq to Sql and other ORM mappers. I like Linq to Sql and the idea of expressing data access logic (or CRUD operations in general) in your native development tongue rather than having to deal with the "impedance mismatch" between C# and SQL. For example, to return an ObjectD...
I can't display the first entry from my query that returns multiple answers. For example, a search with a result set of five will return the second to fifth and miss the first entry. If I search for something that has one entry as output it doesn't return anything but just hangs because it is the first and only entry in the database. ...
Hi all, I'm using the SQLAlchemy Python ORM in a Pylons project. I have a class "Project" which has a one to many relationship with another class "Entry". I want to do a query in SQLAlchemy that gives me all of the projects which have one or more entries associated with them. At the moment I'm doing: [project for project in Session.que...
I'm working on a Data Warehouse which, in the end, will require me to create reports based on business hours. Currently, my time dimension is granular to the hour. I'm wondering if I should be modifying my Time dimension to include a bit field for "business hour" or should I be creating some sort of calculated measure for it on the anal...
What is a stored procedure? How do they work? What is the make-up of a stored procedure (things each must have to be a Stored Procedure)? ...
I have the following SQL statement SELECT table1.col1, table2.col2 FROM table1, table2 WHERE table1.t_id = table2.t_id UNION SELECT table1.col1 FROM table1 WHERE table1.col4 = null Problem is, I this syntax is invalid because the 2nd statement doesn't include the same # of columns as the 1st statement. I can't include table2.col2 in ...
Several colleagues and I are faced with an architectural decision that has serious performance implications: our product includes a UI-driven schema builder that lets non-programmers build their own data types for a web app. Currently, it builds properly normalized schemas behind the scenes and includes some complex logic to alter the sc...
Should I be storing the raw SQL queries in my c# code, or should I be delegating them to stored functions in the Postgres Backend? If I should be storing them in code, since they can get quite long, what is the optimal way to store them (ie. as constants in a separate static class)? Does using stored functions have any impact on deploy...
Are database triggers a bad idea? In my experience they are evil, because they can result in surprising side effects, and are difficult to debug (especially when one trigger fires another). Often developers do not even think of looking if there is a trigger. On the other hand, it seems like if you have logic that must occur evertime a...
I'm trying to pass the following String to a PreparedStatement: private static final String QUICK_SEARCH = "select * from c where NAME like '% ? %'"; However, I get an SQL exception that the bind variable is missing. Any suggestions? ...
Hello folks, I have some questions about partitioning and strategy, how and when to use it. As I understood from Partitioned Tables and Indexes in SQL Server 2005, partitioning is not used only for manageability but also to improve performance on very large database tables (VLDB). We have a table with millions of records. This table st...
Hi, i have 2 tables: tab1 (field1, field2, field3) tab2 (field1, field2,field3, field4) I want to copy a record from tab1 to tab2 taking all the fields and adding a value for field4. How can i select field1, field2 and field3 from tab2 and also add a value? I know that SELECT and VALUES in a INSERT query are mutually exclusive. I'm work...