I have a view that has a list of jobs in it, with data like who they're assigned to and the stage they are in. I need to write a stored procedure that returns how many jobs each person has at each stage. So far I have this (simplified): DECLARE @ResultTable table ( StaffName nvarchar(100), Stage1Count int, Stage2Count int ) INS...
What is the best way to request a random row in pure SQL? ...
Hi, I'm having issues with my SQL Reporting Services reports. I'm using a custom font for report headers, and when deployed to the server it does not render correctly when I print or export to PDF/TIFF. I have installed the font on the server. Is there anything else I need to do in order to use custom fonts? When viewing the font in th...
I have developed a high speed transactional server for transfering data over the internet so I do not need to rely upon a database implementation like MySQL to provide this. That opens up the question of which SQL version to use? I really like SQLite, but I am not convinced it is industrial strength yet What I do like is how lightweight...
I've written PL/SQL code to denormalize a table into a much-easer-to-query form. The code uses a temporary table to do some of its work, merging some rows from the original table together. The logic is written as a pipelined table function, following the pattern from the linked article. The table function uses a PRAGMA AUTONOMOUS_TRANSA...
I have a database with a table Customers that have some data I have another database in the office that everything is the same, but my table Customers is empty How can I create a sql file in SQL Server 2005 (T-SQL) that takes everything on the table Customers from the first database, creates a, let's say, buildcustomers.sql, I zip that f...
We've been trying to alter a lot of columns from nullable to not nullable, which involves dropping all the associated objects, making the change, and recreating the associated objects. We've been using SQL Compare to generate the scripts, but I noticed that SQL Compare doesn't script statistic objects. Does this mean its ok to drop them...
For my senior thesis, I developed a program that would automatically detect and suggest fixes to SQL injection vulnerabilities using prepared statements. Specifically the mysqli extension for PHP. My question for the SO community is this: What would your preferred approach be to detect the SQL in PHP source code? I used an enum contai...
Can/Should I use a LIKE criteria as part of an INNER JOIN when building a stored procedure/query? I'm not sure I'm asking the right thing, so let me explain. I'm creating a procedure that is going to take a list of keywords to be searched for in a column that contains text. If I was sitting at the console, I'd execute it as such: SELEC...
Why should I or shouldn't I use dirty reads: set transaction isolation level read uncommitted in SQL Server? ...
I'm new to SQL Server Reporting Services, and was wondering the best way to do the following: Query to get a list of popular IDs Subquery on each item to get properties from another table Ideally, the final report columns would look like this: [ID] [property1] [property2] [Select COUNT(*) FROM AnotherTable WHERE ForeignID=ID] There...
I seem to be missing something about LINQ. To me, it looks like it's taking some of the elements of SQL that I like the least and moving them into the C# language and using them for other things. I mean, I could see the benefit of using SQL-like statements on things other than databases. But if I wanted to write SQL, well, why not jus...
I have a collection of objects in a database. Images in a photo gallery, products in a catalog, chapters in a book, etc. Each object is represented as a row. I want to be able to arbitrarily order these images, storing that ordering in the database so when I display the objects, they will be in the right order. For example, let's say...
SQL Experts, Is there an efficient way to group runs of data together using SQL? Or is it going to be more efficient to process the data in code. For example if I have the following data: ID|Name 01|Harry Johns 02|Adam Taylor 03|John Smith 04|John Smith 05|Bill Manning 06|John Smith I need to display this: Harry Johns Adam Taylor Jo...
I am writing a program that needs to send out an email every hour on the hour, but at a time local to the user. Say I have 2 users in different time zones. John is in New York and Fred is in Los Angeles. The server is in Chicago. If I want to send an email at 6 PM local to each user, I'd have to send the email to John at 7 PM Server tim...
How do I select all records that contain "LCS" within the title column in sql. ...
Here is the issue I am having: I have a large query that needs to compare datetimes in the where clause to see if two dates are on the same day. My current solution, which sucks, is to send the datetimes into a UDF to convert them to midnight of the same day, and then check those dates for equality. When it comes to the query plan, thi...
Wondering if there is a better why in the WHERE clause of choosing records when you need to look at effective start and end dates? Currently this how I've done it in the past on MS SQL Server. Just worried about the date and not the time. I'm using SQL Server 2005. AND CAST(CONVERT( CHAR(10), ep.EffectiveStartDate, 101) AS DATETIME) <...
I am going to be using c/c++, and would like to know the best way to talk to a MySQL server. Should I use the library that comes with the server installation? Are they any good libraries I should consider other than the official one? ...
Assuming you can't use Linq for whatever reason, is it a better practice to place your queries in stored procedures, or is it just as good a practice to execute ad hoc queries against the database (say, Sql Server for argument's sake)? ...