What type of encryption is SQL Compact's "Engine Default"?
If you specify "Engine Default" when encrypting a SQL CE database, what specific type of encryption does it use? (Let's assume version 3.5) ...
sql
MS SQL 2005 "For XML Path" Node Layout Question
I have the following query Select field1 as 'node1/field1', field2 as 'node1/field2', (Select field3 as 'child1/field3', field4 as 'child1/field4' From table2 FOR XML PATH(''),TYPE,Elements) From Table1 FOR XML PATH('Root'),Elements This produces: <Root> <node1> <field1>data1</field1> <field2>data2<...
Does SQL Compact Edition Support Clustered Indexes?
Does SQL CE support clustered indexes? ...
How Do You Call an MSSQL System Function From ADO/C++?
...specifically, the fn_listextendedproperty system function in MSSQL 2005. I have added an Extended Property to my database object, named 'schemaVersion'. In my MSVC application, using ADO, I need to determine if that Extended Property exists and, if it does, return the string value out of it. Here is the T-SQL code that does what I ...
Passing comma delimited list as parameter to IN clause for db2 query using designer in visual studio 2008
I want to pass a comma delemited list of values as a parameter to a query I'm building using the designer in Visual Studio 2008 based on some strongly typed DAL tutorials I was going through. The query is going against a DB2 database. Here's what I want to do: select * from prices where customer in(?) It works fine win I pass in 1234...
Combining two MySQL Queries (One to select contents of another)
How would I combine the following two quieries to produce a combination of the two? SELECT * FROM post_attributes WHERE name IN ('title', 'body'); SELECT id, url, created_at, name, value FROM posts, post_attributes WHERE posts.id = post_attributes.post_id ORDER BY id; The idea would be to extract the post number, it's url...
What is the best way to store a threaded message list/tree in SQL?
I'm looking for the best way to store a set of "posts" as well as comments on those posts in SQL. Imagine a design similar to a "Wall" on Facebook where users can write posts on their wall and other users can comment on those posts. I need to be able to display all wall posts as well as the comments. When I first started out, I came u...
Importing XML into SQL Server
I can find lots of example on how to import certain types of XML data into SQL Server 2005. But I've been given data in the following format (repeating "row" and "cell" with ID's instead of the tags been named etc: <?xml version="1.0"?> <rows> <row id='1'> <cell id='category'>Simple</cell> <cell id='query'>summary</...
LINQ to SQL Classes
Is it better to have one fat data context class with all the tables in it or break them into smaller classes and potentially having "single table" classes? From organizational standpoint I feel like I shouldn't split relations over several data contexts (I don't even think it's doable). But on the other hand I think that creating a whol...
Save DateTime in mssql 2005 without hours, minutes and seconds
I want to save my dates in sql2005 as dates (without hour minutes and seconds). I want to do this because the between function isn't always correct if the hours, minutes and seconds are filled in. but neither datetime nor smalldatetime allows this, in 2008 you have the Date column which can do this. I've also found this question which ...
Entity Framework: Detect DBSchema for licensing
We're working on a product that may or may not have differing license schema for different databases. In particular, a lower-tier product would run on SQLExpress, but we don't want the end user to be able to use a "full-fat" SQL install - benefiting from the price cut. Clearly this must also be the case for other DBs, so Oracle may comma...
SQL - Using datediff in my SQL statement
This is my SQL statement that works using datediff.............. SELECT SUM(b134_nettpay) AS Total, b134_rmcid, b134_recdate FROM B134HREC WHERE datediff (dd, '2006-05-05', getdate()) > 90 GROUP BY b134_rmcid, b134_recdate ORDER BY b134_recdate DESC, b134_rmcid I need to Replace the Hardcoded date '2006-05-05' with MAX(b134_recdat...
MySQL query to extract first word from a field
Hi, I would like to run a query that returns the first word only from a particular field, this field has multiple words separated by spaces, I assume I may need to carry out some regex work to accomplish this? I know how to do this using a few ways in PHP but this would best be carried out on the database side. Any ideas much appreciate...
SQL: retrieve only the records whose value has changed
Sorry for the nondescript title. I'll edit as we go along. I have a table RateTable: | Code | Date | Rate | B001 2009-01-01 1.05 B001 2009-01-02 1.05 B001 2009-01-03 1.05 B001 2009-01-04 1.05 B001 2009-01-05 1.06 B001 2009-01-06 1.06 B001 2009-01-07 1.06 B001 2009...
What's the most elegant way to use stored procedures?
I'm re-writing the inline SQL in my repository class to use stored procedures instead (security requirement). After using Fluent NHibernate and Linq2Sql in the past I'm finding it to be extremely unwieldy and inelegant. EDIT: To clarify, I'm not looking for an ORM solution that works with stored procs. I just want some advice on a nice ...
Does anyone use Right Outer Joins?
I use INNER JOIN and LEFT OUTER JOINs all the time. However, I never seem to need RIGHT OUTER JOINs, ever. I've seen plenty of nasty auto-generated SQL that uses right joins, but to me, that code is impossible to get my head around. I always need to rewrite it using inner and left joins to make heads or tails of it. Does anyone a...
Uses of unequal joins
Of all the thousands of queries I've written, I can probably count on one hand the number of times I've used a non-equijoin. e.g.: SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.date > tbl2.date And most of those instances were probably better solved using another method. Are there any good/clever real-world uses for non-equijoins that ...
Re-indexing large table - how screwed am I?
I have a 1 TB, 600m row, table which has a misguided choice of indexed columns, specifically a clustered index on the primary key column which is never used in a select query. I want to remove the clustered index from this row and create it on a number of other rows. Table is currently like this: colA (PK, nvarchar(3)) [clustered i...
SQL select distinct rows
I have data like this (col2 is of type Date) | col1 | col2 | ------------------------------ | 1 | 17/10/2007 07:19:07 | | 1 | 17/10/2007 07:18:56 | | 1 | 31/12/2070 | | 2 | 28/11/2008 15:23:14 | | 2 | 31/12/2070 | How would select rows which col1 is distinct and the value of col2 is the...
How can I teach a beginner to write ASP.NET web applications quickly?
Related: Best ways to teach a beginner to program? I am teaching my brothers ASP.NET programming and they are starting with a very small amount of background knowledge. They know things like declaring a variable, what a class is and what a page is. I want to teach them to be good web developers in the shortest amount of time. The ...