In the code below I am using a recursive CTE(Common Table Expression) in SQL Server 2005 to try and find the top level parent of a basic hierarchical structure. The rule of this hierarchy is that every CustID has a ParentID and if the CustID has no parent then the ParentID = CustID and it is the highest level. DECLARE @LookupID int --O...
I'm working on a database for a small web app at my school using SQL Server 2005. I see a couple of schools of thought on the issue of varchar vs nvarchar: Use varchar unless you deal with a lot of internationalized data, then use nvarchar. Just use nvarchar for everything. I'm beginning to see the merits of view 2. I know that nva...
Wikipedia says SQL Server Express Edition is limited to "one processor, 1 GB memory and 4 GB database files". Does anyone have practical experience with how well this scales? ...
I have a query that originally looks like this: select c.Id, c.Name, c.CountryCode, c.CustomerNumber, cacc.AccountNumber, ca.Line1, ca.CityName, ca.PostalCode from dbo.Customer as c left join dbo.CustomerAddress as ca on ca.CustomerId = c.Id left join dbo.CustomerAccount as cacc on cacc.CustomerId = c.Id where c.CountryCode = 'XX' and ...
Hi All, I'm working on an email solution in SQL Server ONLY that will use Database Mail to send out HTML formatted emails. The catch is that the images in the HTML need to be embedded in the outgoing email. This wouldn't be a problem if I were using a .net app to generate & send the emails but, unfortunately, all I have is SQL Server. ...
I have a table defined (see code snippet below). How can I add a constraint or whatever so that the LastUpdate column is automatically updated anytime the row is changed? CREATE TABLE dbo.Profiles ( UserName varchar(100) NOT NULL, LastUpdate datetime ...
It is generally accepted that the use of cursors in stored procedures should be avoided where possible (replaced with set based logic etc). If you take the cases where you need to iterate over some data, and can do in a read only manner, are fast forward (read only forward) cursor more or less inefficient than say while loops? From my in...
Has anyone used Lucene.NET rather than using the full text search that comes with sql server? If so I would be interested on how you implemented it. Did you for example write a windows service that queried the database every hour then saved the results to the lucene.net index? ...
First of all, I should point out that I'm not asking this question for any practical reason. I'm just asking out of sheer curiosity. Why is it that SQL distributions are so non-standard? I know there's an ANSI standard for SQL, but it just seems that developers of SQL databases go out of their way to find different ways to do things. ...
Hello, Does anyone know if you can and how to start off a scheduled Windows task on a Remote Server from within a SQL Server Integration Services (SSIS) package? Cheers Nigel ...
What tool(s) do you use to generate documentation for a database (preferably into a .chm help file)? ...
I have created a database model in Visio Professional (2003). I know that the Enterprise version has the ability to create a DB in Sql Server based on the data in Visio. I do not have the option to install Enterprise. Aside from going through the entire thing one table and relationship at a time and creating the whole database from scrat...
I'd like to find the different ways to solve a real life problem I had: imagine to have a contest, or a game, during which the users collect points. You have to build a query to show the list of users with the best "n" scores. I'm making an example to clarify. Let's say that this is the Users table, with the points earned: UserId - Po...
I work for a .NET/MSSQL shop that has trouble supporting customers running Novell, partially because we don't have Novell (or the money for it) and partially because we have no one with Novell experience. This question could easily be expanded to "How can we improve our support of technology X when we don't have technology X?" Obviously,...
I'm just getting dirty in WinForms, and I've discovered, through a lovely tutorial, the magic of dragging a database table onto the design view of my main form. So, all is lovely, I've got my DataGridView with all of the columns represented beautifully. BUT... When I run my application against this brand new, empty .sdf (empty save for...
Did Installing OneCare cause a "Generating user instances in SQL Server is disabled" error? The only change that I've made to my computer is uninstalling AVG and installing the trial for Microsoft OneCare. Did OneCare change the SQLServer installation somehow? This is a very "odd" question but is something I would post on EE in hopes o...
Hello, I have an SSIS Package that sets some variable data from a SQL Server Package Configuration Table. (Selecting the "Specify configuration setings directly" option) This works well when I'm using the Database connection that I specified when developing the package. However when I run it on a server (64 bit) in the testing environm...
What are the ways that you use to model and retrieve hierarchical info in a database? ...
What is the best full text search alternative to ms sql? (which works with ms sql) I'm looking for something similar to Lucene and Lucene.NET but without the .NET and Java requirements. I would also like to find a solution that is usable in commercial applications. ...
Is there a way to enforce constraint checking in MSSQL only when inserting new rows? I.e. allow the constraints to be violated when removing/updating rows? Update: I mean FK constraint. ...