sql-server

Check for changes to a SQL table?

How can I monitor a MSSQL database for changes to a table without using triggers or modifying the structure of the database in any way? My preferred programming environment is .Net and C# (edits) I'd like to support MS SQL 2000 SP4 or higher by "changes to a table" I mean changes to table data, not changes to table structure. Ultima...

How to export data from SQL Server 2005 to MySQL

I've been banging my head against SQL Server 2005 trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated so you can't deal with any row that has a str...

How do I version my MS SQL database in SVN?

I've been wanting to get my databases under version control. Does anyone have any advice or recommended articles to get me started? I'll always want to have at least some data in there (as alumb mentions: user types and administrators). And I'll probably often want a large collection of generated test data for performance measurement....

Upgrading SQL Server 6.5

Yes, I know. The existence of a running copy of SQL Server 6.5 in 2008 is absurd. That stipulated, what is the best way to migrate from 6.5 to 2005? Is there any direct path? Most of the documentation I've found deals with upgrading 6.5 to 7. Should I forget about the native SQL Server upgrade utilities, script out all of the object...

SQL Server 2005 implementation of MySQL REPLACE INTO?

MySQL has this incredibly useful yet properitary REPLACE INTO SQL Command. I wonder: Can this easily be emulated in SQL Server 2005? Starting a new Transaction, doing a Select() and then either UPDATE or INSERT and Commit is always a little bit a pain in the a.., especially when doing it in the application and therefore always keeping 2 ...

Deploying SQL Server Databases from Test to Live

I wonder how you guys manage deployment of a database between 2 SQL Servers, specifically SQL Server 2005. Now, there is a development and a live one. As this should be part of a buildscript (standard windows batch, even do with current complexity of those scripts, i might switch to PowerShell or so later), Enterprise Manager/Management ...

Editing database records by multiple users

I have designed database tables (normalised, on an MS SQL server) and created a standalone windows front end for an application that will be used by a handful of users to add and edit information. We will add a web interface to allow searching accross our production area at a later date. I am concerned that if two users start editing the...

Client collation and SQL Server 2005

We're upgrading an existing program from Win2k/SQL Server 2k to Windows 2003 and SQL Server 2005 as well as purchasing a new program that also uses 2k3/2k5. The vendor says that for us to host both databases we need to get the Enterprise version because the softwares clients use different collation for the connections and only Enterprise...

Why doesn't SQL Full Text Indexing return results for words containing #?

For instance, my query is like the following using SQL Server 2005: SELECT * FROM Table WHERE FREETEXT(SearchField, 'c#') I have a full text index defined to use the column SearchField which returns results when using: SELECT * FROM Table WHERE SearchField LIKE '%c#%' I believe # is a special letter, so how do I allow FREETEXT to w...

Is Windows Server 2008 "Server Core" appropriate for a SQL Server instance?

I'm setting up a dedicated SQL Server 2005 box on Windows Server 2008 this week, and would like to pare it down to be as barebones as possible while still being fully functional. To that end, the "Server Core" option sounds appealing, but I'm not clear about whether or not I can run SQL Server on that SKU. Several services are addresse...

Best way to copy a database in SQL Server 2005/8?

I always create a new empty database and then do a backup and restore of the existing database into it, but is this really the best way? As it seems very error prone and over complicated to me....

Can I logically reorder columns in a table?

If I'm adding a column to a table in Microsoft SQL Server, can I control where the column is displayed logically in queries? I don't want to mess with the physical layout of columns on disk, but I would like to logically group columns together when possible so that tools like SQL Server Management Studio list the contents of the table i...

Best Book for a new Database Developer

We have just had a graduate join the team with the end aim of assisting out our very busy DBA. He has only a basic SQL knowledge from his degree so we are looking for a really good getting started book preferably based on MS SQL server. Purchase Update: Thanks to the replies we have now purchased Head First SQL to review what he alr...

How do I unit test persistence?

As a novice in practicing test-driven development, I often end up on a quandary as to how to unit test persistence to a database. I know that technically this would technically be an integration test (not a unit test), but I want to find out the best strategies for the ff: Testing queries. Testing inserts. How do I know that it is th...

ASP.NET MVC "CRUD" Database Sample

I'm trying to get my head around ASP.NET MVC coming from a LAMP development environment. This isn't for anything production or mission-critical, just a guy trying to learn. I've looked at all I can on http://asp.net/mvc but a lot of those videos and tutorials seem to assume you know ASP.NET WebForms (which I don't) although I am quite ha...

Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?

Have you ever seen any of there error messages? -- SQL Server 2000 Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (256) was exceeded. -- SQL Server 2005 Too many table names in the query. The maximum allowable is 256. If yes, what have you done? Given up...

Appropriate pagefile size for SQL Server

Does any know a good rule of thumb for the appropriate pagefile size for a Windows 2003 server running SQL Server?...

What's the best way to determine if a temporary table exists in SQL Server?

When writing a T-SQL script that I plan on re-running, often times I use temporary tables to store temporary data. Since the temp table is created on the fly, I'd like to be able to drop that table only if it exists (before I create it). I'll post the method that I use, but I'd like to see if there is a better way. ...

I need to know how much disk space a table is using in SQL Server

I think most people know how to do this via the GUI (right click table, properties), but doing this in T-SQL totally rocks. ...

What's the BEST way to remove the time portion of a datetime value (SQL Server)

Here's what I use: SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME) I'm thinking there may be a better/more elegant way. Requirements: - It has to be as fast as possible (the less casting the better) - Final result has to be a datetime type, not a string ...