I'd like to be able to return all columns in a table or in the resulting table of a join and still be able to transform a date to a string by name. For example Select ID, DESCRIPTION, TO_CHAR(CHANGE_DATE,'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE; This is all well and good for just these three columns. But, the table will...
Is there any function to encode HTML strings in T-SQL? I have a legacy database which contains dodgey characters such as '<', '>' etc. I can write a function to replace the characters but is there a better way? I have an ASP.Net application and when it returns a string it contains characters which cause an error. The ASP.Net application...
I'm trying to create a custom generator in Visual Studio team suite, I have installed the database edition GDR, I've followed the walkthroughs on MSDN for doing so for the GDR version (and triple checked my solution against the walkthrough) and downloaded projects from codeplex which illustrate the functionality. But no matter what I try...
How can I find all database objects in a given database using an object name? We prefix all site specific tables, views, indexes, functions, constraints etc. with a constant string. I need to find all objects with names starting with that string. ...
In a previous question, I asked whether ORM libraries were suboptimal solutions and received a lot of great feedback. As I've thought about the issue further, I've concluded that the primary benefit of LinqToSQL (now) and the promise of LinqToEntities (down the road) lies in their ability to reduce the "mismatch" between procedural code...
I have a table which contains a book and then multiple prices about the book (this is a highly simplified sample): ID BOOK PRICE 1 BOOK1 10 2 BOOK1 15 3 BOOK1 12 4 BOOK2 8 5 BOOK2 2 I am easily calculating the average, but there must be a nice way to calculate the median? Current SQL: SELECT DISTINCTROW Books.BOOK, Avg...
What would be the best practice for the following scenario: There´s a grid that will be filled and must be changed according to each row. For instance, there´s a grid filled with products, then according to each product one of the columns will be dynamically populated. Is it better to return from the service all the productdetail tabl...
If I have a table with the hypothetical columns foo and bar. bar might have 50-60 distinct values in it. My goal here is to pick say, up to 5 rows for say 6 unique bars. So if the 6 unique bars that get selected out of the 50-60 each happen to has at least 5 rows of data, we'll have 30 rows in total. ...
I've got a table in a mysql database that contains monetary transactions. Let's say the tabled is called transactions: id int, to_user int, from_user int, created datetime, amount double I'm trying to create a query that returns the average amount of a transaction grouped by the number of transactions that appear per day. The applic...
Hi folks, I have a site like SO, Wordpress, etc, where you make a post and u can have (optional) tags against it. What is a common database schema to handle this? I'm assuming it's a many<->many structure, with three tables. Anyone have any ideas? ...
What is a good method for parsing an sql string into it separate components. I'v tried with a regex, however I can't get it to work just right. Say for instance: "SELECT * FROM tbl WHERE user_id > 50" Would create an array with all the components $arr[0] = "SELECT"; $arr[1] = "*"; $arr[2] = "FROM"; ect... Thank you ...
General Info: ASP.NET MVC app using ADO.NET Entity Framework SQL Server 2005 I have a few tables that more or less have a hierarchical structure of mostly one-to-many relationships. Most if not all of them have a Last Modified column and my question is simply how best to go about making sure that this column is updated properly throu...
In SQL Server 2005, we defined some UDT (User Defined Datatypes) on in particular was SK (for Surrogate Key). These were defined as 32 bit 'int'. And consequently the size was 4 bytes. In SQL Server 2008, the UDT for the integer datatypes uses a different storage mechanism, depending upon the precision: Storage Displays the max...
My work company has a MSSQL server 2005. I have two questions about finding out current log user and any way to send out a warning message: First question is if there is any T-SQL or SP available to find out current login user name and machine name. If the user is using SQL server sa name to remotely access to SQL server, is there any w...
Given these two tables: Foo (id, name) -- PK = id Bar (fooId, value) -- PK = composite, fooId + value -- value is a non-negative int How can I find all the Foo.names where there is no corresponding Bar,value above 0? eg: Foo id name 1 a 2 b 3 c Bar fooid value 1 0 1 1 2 ...
After reading through many of the questions here about DB schema migration and versions, I've come up with a scheme to safely update DB schema during our update process. The basic idea is that during an update, we export the database to file, drop and re-create all tables, and then re-import everything. Nothing too fancy or risky there...
Hello - I'm running a SQL SELECT query through an ADO connection to an Excel 2007 workbook with the following code (using a custom version of VBScript) dim ado, rs set ado = CreateObject("ADODB.Connection") ado.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=workbook.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IM...
This is something I struggle with since yesterday. I have appointments to save in a database. They consist of a date and a time, like: 01.02.1970 14:00 (german format, in american I think it would be something like 02/01/1970 2:00pm). First idea: Save it as a SQL.DATE! So i created a table: CREATE TABLE appointments (id NUMBER(10...
I have the following query DECLARE @userId INT DECLARE @siteId INT SET @siteId = -1 SET @userId = 1828 SELECT a.id AS alertId, a.location_id, a.alert_type_id, a.event_id, a.user_id, a.site_id, a.accepted_by FROM alerts AS a JOIN alert_types AS ats ON a...
I'm using a threadpool to do some heavy processing and also bits of sql. Currently I open sql connections when I need them, run a query and then close them. This works fine. The application has been running with no issues. As more work is being done by this application it's using more threads. More threads mean more opening/closing of SQ...