sql-server

SQL Server 2000: Is there a way to tell when a record was last modified?

The table doesn't have a last updated field, obviously. :-) ... and I need to know when existing data was updated. So adding a last updated field won't help (AFAIK). ...

How do you get leading wildcard full-text searches to work in SQL Server?

Note: I am using SQL's Full-text search capabilities, CONTAINS clauses and all - the * is the wildcard in full-text, % is for LIKE clauses only. I've read in several places now that "leading wildcard" searches (e.g. using "*overflow" to match "stackoverflow") is not supported in MS SQL. I'm considering using a CLR function to add regex...

SQL Client for Mac OS X that works with MS SQL Server

What's a good SQL client for Mac OS X that works with MS SQL Server? I don't really need a GUI, but it's nice to have for the color coding and resultset grid. ...

Transform Columns into Rows

I have a very simple problem which requires a very quick and simple solution in SQL Server 2005. I have a table with x Columns. I want to be able to select one row from the table and then transform the columns into rows. TableA Column1, Column2, Column3 SQL Statement to ruturn ResultA Value of Column1 Value of Column2 Value of Colum...

Sql to query a dbs scheme

In SqlServer. How do you query a db to bring back all the tables that have a field of a specific name? ...

Backup SQL Schema Only?

I need to create a backup of a SQL Server 2005 Database that's only the structure...no records, just the schema. Is there any way to do this? EDIT: I'm trying to create a backup file to use with old processes, so a script wouldn't work for my purposes, sorry...

Suggestions for implementing audit tables in SQL Server?

One simple method I've used in the past is basically just creating a second table whose structure mirrors the one I want to audit, and then create an update/delete trigger on the main table. Before a record is updated/deleted, the current state is saved to the audit table via the trigger. While effective, the data in the audit table is...

T-Sql Remove Decimal Point From Money Data Type

Given the constraint of only using T-Sql in Sql Server 2005, is there a better way to remove the decimal point from a money datatype than a conversion to a varchar (here implicitly) and then a replace of the decimal point? Here is what I have currently. SELECT REPLACE(1.23, '.', ''), REPLACE(19.99, '.', '') Which returns the desired 1...

Long-time LAMP Developer moving to VB.NET

I've been developing with PHP5/MySQL for years, but recent circumstances have pushed me to move towards the .NET platform. I'd be interested to hear any advise from someone who has gone through this transition... What are some things you wish you knew when you started? I also have a Java background so I'm not scared of the OO aspect ...

How do I know which SQL Server 2005 index recommendations to implement, if any?

We're in the process of upgrading one of our SQL Server instances from 2000 to 2005. I installed the performance dashboard (http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en) for access to some high level reporting. One of the reports shows missing (recommended) indexes. I th...

Why use .NET?

I've often wondered, what is so great about .NET and MsSQL? I can see a downside being that you have to run it on Windows but I am unaware of any upsides besides it being quite fast, yet so many here use it. Edit: You are meant to run it on Windows but as Atrophius pointed out you can use Mono ...

How to enable Full-text Indexing in SQL Server 2005 Express?

I am trying to enable Full-text indexing in SQL Server 2005 Express. I am running this on my laptop with Vista Ultimate. I understand that the standard version of SQL Server Express does not have full-text indexing. I have already downloaded and installed "Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 2" ...

What program can I use to generate diagrams of SQL view/table structure?

I've been tasked with redesigning part of a ms-sql database structure which currently involves a lot of views, some of which contain joins to other views. Anyway, I wonder if anyone here could recommend a utility to automatically generate diagrams to help me visualise the whole structure. What's the best program you've used for such p...

What is best practice for FTP from a SQL Server 2005 stored procedure?

What is the best method for executing FTP commands from a SQL Server stored procedure? we currently use something like this: EXEC master..xp_cmdshell 'ftp -n -s:d:\ftp\ftpscript.xmt 172.1.1.1' The problem is that the command seems to succeed even if the FTP ended in error. Also, the use of xp_cmdshell requires special permissions and m...

MS-SQL: Drop all tables whose names begin with a certain string

I'd like a script to drop all tables whose name begins with a given string. I'm sure this can be done with some dynamic sql and the INFORMATION_SCHEMA tables. If anyone has a script, or can knock one up quickly, please post it. If no-one posts an answer before I figure it out myself, I'll post my solution. ...

Is there any trick that allows to use Management Studio's (ver. 2008) IntelliSense feature with earlier versions of SQL Server?

New version of Management Studio (i.e. the one that ships with SQL Server 2008) finally has a Transact-SQL IntelliSense feature. However, out-of-the-box it only works with SQL Server 2008 instances. Is there some workaround for this? ...

SQL Case Statement Syntax?

What is the full correct syntax for the SQL Case statement? ...

MOSS SSP problem - Failed database logons from deleted SSP

We've been having some issues with a SharePoint instance in a test environment. Thankfully this is not production ;) The problems started when the disk with the SQL Server databases and search index ran out of space. Following this, the search service would not run and search settings in the SSP were not accessible. Reclaiming the disk s...

How much database performance overhead when using LINQ?

How much database performance overhead is involved with using C# and LINQ compared to custom optimized queries loaded with mostly low-level C, both with a SQL Server 2008 backend? I'm specifically thinking here of a case where you have a fairly data-intensive program and will be doing a data refresh or update at least once per screen an...

How should I organize my master ddl script.

I am currently creating a master ddl for our database. Historically we have used backup/restore to version our database, and not maintained any ddl scripts. The schema is quite large. My current thinking: Break script into parts (possibly in separate scripts): table creation add indexes add triggers add constraints Each script wo...