Recursive Concatenation of Parent Elements

I have a table that looks like this: ID |Name |Parent 1 |A |NULL 2 |B |NULL 3 |C |1 4 |D |1 5 |E |3 6 |F |5 I would like to have a view return this: ID |Name |ParentNames 1 |A | 2 |B | 3 |C |A 4 |D |A 5 |E […]

How do I get the result output from an SQL BACKUP command into a Delphi program?

The output of sql commands that is visible to users who interactively run SQL commands from SQL Server Management studio, is different than the output you get back from executing an ADO command or ADO query object. USE [DBNAME] BACKUP DATABASE [DBNAME] TO DISK = ‘C:\SqlBackup\Backup.mdf’ The successful completion output is like this: Processed 465200 […]

How do I kill connections to Azure SQL database if I can't access it?

Today we migrated to AzureSQL V12. Tonight my site is offline, because of a persistent issue with the following message: Resource ID : 3. The LoginLimit limit for the database is 90 and has been reached. See ‘’ for assistance. (Microsoft SQL Server, Error: 10928) I’ve tried the following: Restarted my web site’s server (also […]

Delete All / Bulk Insert

First off let me say I am running on SQL Server 2005 so I don’t have access to MERGE. I have a table with ~150k rows that I am updating daily from a text file. As rows fall out of the text file I need to delete them from the database and if they change […]

Re-Importing/Updating a SQL Server database project after I modify the DB in Mgmt Studio

My team is working with a .NET 2008 DB Project in their solution so we have some way to persist the schema between machines and through development and keep things consistent. My preferred way of changing the database and keeping track of said schema is through the SQL Server Mangement Studio — basically, I have […]

Removing duplicates using partition by SQL Server

I need to remove duplicates from a table: ;WITH cte as( SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id] ORDER BY ( SELECT 0 ) ) RN FROM quicklabdump) delete from cte where RN>1 The column quicklabdumpID is the primary key. I would like to know how to keep only the largest quicklabdumpID where there are […]

Javascript date to sql date object

I’m trying to write a query that takes a Javascript date object and then puts it in an object type that is recognized by both SQL Server and Oracle database types. The issue is that I’m using webservices. So it has to be a string, not an actual passed parameter. Here’s what I mean: var […]

How to use parameter with LIKE in Sql Server Compact Edition

I’m trying to parameterise a search query that uses the LIKE keyword with a wildcard. The original sql has dynamic sql like this: “AND JOB_POSTCODE LIKE ‘” + isPostCode + “%’ ” So I’ve tried this instead, but I get a FormatException: “AND JOB_POSTCODE LIKE @postcode + ‘%’ ” Edit: I guess the FormatException isn’t […]

Delphi: how to pass a list as a parameter to a SQL query?

I have a list of integers or of strings and need to pass it as a parameter for a Delphi DataSet. How to do it? Here is an example. MyQuery is something like: select * from myTable where intKey in :listParam I’d set a parameter as a list or array or something else: MyQuery.ParamByName(‘listParam’).AsSomething := […]

Do statistics referencing a column prevent that column from being dropped?

I’m trying a very simple drop column statement: alter table MyTable drop column MyColumn and receiving several errors along the lines of Msg 5074, Level 16, State 1, Line 1 The statistics ‘_dta_stat_1268251623_3_2’ is dependent on column ‘MyColumn’. followed ultimately by Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN MyColumn failed […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.