An Auditing Slution with XML And XSL

David McKinney from SQLServerCentral.com

Auditing is something that almost every DBA needs to tackle at some point in his or her career. David McKinney brings a new twist on the solution by using XML and XSL to help implement auditing in your SQL Server application. More »

Raffle Winners and a Thank You

Press Release from SQLServerCentral.com

Here are the winners of our raffle and we should be sending out prizes soon. A thanks as well from Katie's father to all of you. More »

Close These Loopholes in Your Database Testing

Additional Articles from SimpleTalk

Alex Kuznetsov starts off a series of three articles on 'Unit Testing' your database development work. He begins by describing five simple rules that make all the difference. More »

Accessing and Updating Data in ASP.NET 2.0: Data Source Control Basics

Additional Articles from 4 Guys From Rolla

This article explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.More »

One Year Ago - Executing a Package from Visual Basic

Brian Knight from SQLServerCentral.com ( published: 08/18/2006)

We all take risks in our jobs, though most of us try to mitigate the impact of those risks. It seems that the more many of us continue to work in IT, the less likely we are to perform action that might put our systems or networks at risk for some virus or other infection.

Most of the people I know in IT work remotely some percentage of the time. It's usually an ad hoc, on-call type of work rather than telecommuting on a regular basis, but all of them have the need to work outside of the office. And they do it at hot spots or other mobile locations at times. Maybe I'm naive, but I think for the most part that these locations on and of themselves don't pose a risk to their companies.

Instead I think it's more the behavior of people that do things without thinking. I saw this article (Mobile I.T. Workers Taking Risks Online) and it makes perfect sense. I've definitely seen people that don't think security, and they're often not the IT folks, when clicking on jokes, IMs, etc. that are sent to them.

I'd like to think that lots of IT people, especially admins, are careful about what they do on their machines. I know many people have gotten used to using the "Run as" command and a privileged account for doing certain things on their machines or networks. And hopefully most people realize that downloading music and other non-work related materials, is something that they should do on a personal machine.

I'm sure people take chances. I know I had a friend asking me about some error on their computer last week that was actually a "fake error box" popped in a small browser by some web site. Thankfully I caught it in time and showed them how to "close" those boxes without clicking OK or Cancel.

In many ways the Internet is an amazing place that empowers, excites, and entertain us. But it can also be a very dangerous place and it pays to be prudent with your actions, especially when you are using your employer's assets.

Steve Jones

» Join the debate, and respond to today's editorial on the forums

» To submit a tip, rant or editorial, email articles@sqlservercentral.com.

Today's Question:

What are the two uses of % in T-SQL

Think you know the answer? Click Here, and find out if you are right.

This Question is worth 1 Point(s) in this category : T-SQL. We keep track of your score to give you bragging rights against your peers. If you have problems with submitting you can go to the QOD section inside the Test Center directly at http://www.sqlservercentral.com/testcenter/QOD.asp. We'd love to give you credit for your own question and answer. To submit a QOD, simply send the question, answers and explanation to articles@sqlservercentral.com

Today's Question is sponsored by:

SQL Professional Gift Pack for August

What happens to a deferred transaction if the filegroup that it is holding it up becomes defunct?

Answer: The transaction is moved out of the deferred state.

Explanation: One of the resolutions for removing a deferred transaction is to make the filegroup that is holding up the transaction, defunct. Once this happens the transaction is closed.

Ref: Deferred Transactions - http://msdn2.microsoft.com/en-us/library/ms175122.aspx
Defunct Transactions - http://msdn2.microsoft.com/en-us/library/ms175122.aspx

Here's a few of the new posts today on the forums. To see more, select Discussion Forums in the Community menu on the site:

   DATABASE BACKUPS - Hi Friends,My database plan is Every day 12:00 noon Full backupEvery day every two hours Differential BackupsEvery Day every Fifteen min Take Transactionlog BackupsBut Today my database is crassed at 4:15pm how can i resore m....(more online)

   Log Shipping With SQL2005 Or Maybe Something Else ... - Hi,I have a live SQL Server 2005 instance running on Win2K3 Enterprise Addition and I am wanting to move all the data in these databases to a warm standby on a regular basis (I cannot loose more than 15 minutes worth of data)....(more online)

   Should 64-bit SQL 2005 be considered for most installations vs. 32-bit? - Inside Microsoft SQL Server 2005: The Storage Engine, by Kalen Delaney says in Chapter 2..."Unless you are working only with very small databases and do not expect to need more than a couple of gigabytes of RAM, you shou....(more online)

   about sql 2005 server enterprise installation - I was trying to install sql 2005 server enterprise in my 32 bit windows vista operating system, but I am getting following eror message."Some components of this edition of SQL Server are not supported on this operating system....(more online)

   SQL Server version - Hi,          I just installed SP2 on SQL Server 2005 developer edition. But after the installation i ran Select @@ version on the machine and it is showing me the same version....(more online)

   point in time restore - Hello everybody,I have a small problem,thursday morning I had full backup of my user database and every 1 hour I had transaction log backup.so, it goes like thisfull backup - 8:00 AMtransaction - 9:00 AMtransaction - 10:00 AM....(more online)

   Using Multi-Select Parameters - Hi,What is the simple way to make use of multi-select parameters? I just added one and tried to make use of it but couldn't get it to work.The only solution I could gind via google was one where the multi-parameter string is ....(more online)

   Slow connection from MS Access - We have recently had a 3rd party application migrate from an Access mdb database to an SQL Server 2005 db. No issues with this, and the application itself works fine.After a bit of arm twisting, the vendor had given us ad-hoc....(more online)

   sorting puzzel - I have a sql query. When I run the query it brings me resultset unsorted. But when I convert this query in a stor procedure then it brings resultset in sorted order, I do not use any sort order either in query or in stor proc....(more online)

   Cannot connect to SQL Server 2005 on local machine - Hello all, been wrestling with a problem on my laptop.I have installed SQL Server 2005 Standard Edition.When I try to connect using the Management studio, I get the following error:********************************************....(more online)

   In-Place or Side-by-Side Upgrade? - When migrating a SQL Server 2000 database to 2005 how do I determine which is the best option to pursue (In-Place or Side-by-Side), and do you have a recommendation?  If you have a recommendation, do you have suggestions....(more online)

   can we create a synonym for a symmetric key - can we create a synonym for a symmetric key and acees the key via the synonym name Thanks in advance!!!....(more online)

   Can''t See SQL Server Integration Services Package In List - I have created a package and depolyed it to my server (File System), I now want to create a job to execute it but I cannot see the SQL Server Integration Services Package option in the Type list.Any ideas?....(more online)

   How can I pass value from one DB to another - Hi all, I want to CREATE a DTS in SQL server 2005 & SQL server 2000 , retrieve data from DB1 for example select EMPID from tblEmployee where LastModifiedDate >= DateAdd(minute,30,getdate())I want  to pass the....(more online)

   Failed to schedule DTS in SQL 2005 - I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this http://support.microsoft.com/kb/904796but I can?t understand how to solve it, it says ....(more online)

   SQL 2005 Server Performance and Maximum Memory property - A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?Server:OS Microsoft(R) Windows(R) Server 2003, Enterprise EditionVersion 5.2.3790 Service Pack 1 B....(more online)

   Performance Dashboard Missing Index reports - Does any one have any experience with Performance Dashboard's missing index report?I've recently been given a set of indexes to apply that seem very poor choices. For example the report include indexes on bit fields with incl....(more online)

   TempDB CPU - I read an article about having TEMPDB file group for each CPU..We have 4 cpu on some servers but only one TEMPDB Data and Log is it recommended for me to add another TEMPDB ...If any one has done that i like to see how to do ....(more online)

   Optimising a report to re-use query plans and use the correct indexes. - Hi I have a web based reporting page that lets admin select from a multitude of different options to search against web traffic data. They can select different combinations of options and date ranges and partial/full textual ....(more online)

   Sql server is not listening on TCP - Hello, I'm having an issue with a J2EE app trying to connect to Sql 2005 (note: I also have Sql 2k installed on this same laptop). Now I have Sql 05 configured to listen on the default of port 1433 (verified via Sql....(more online)

   Mirroring / Snapshot database design questions - Howdy,One of the fundamental requirements in my workplace is being able to provide select people access to a database that they can perform direct SQL queries on (hopefully well constructed ones) without perfor....(more online)

   Date Calculations - I would like to calculate dates to return number of years and number of day's to look like this  i.e 30 Years and 21 Days = Don't ask why it's how the work it. I would like to check for an joindate and calculate the....(more online)

   subquery??? - Hi, I am trying to write a query that joins 3 tables and produces counts on multiple columns within the three tables.  Below is my code and I am getting a syntax error message near the key word 'in'.  Can anyone hel....(more online)

   Help with Correlated Subquery - I have a qeury which pulls all customers with duplicate addresses and displays a concatenated, comma delimited list of those customers last names at that address.  It successfully uses a UDF to do it.  Is it possibl....(more online)

   Database server - Hi all,I need some advises about hardware and software configuration of a database server. I am new as DBA employer. I have noticed that there are a lot of BlkBy issues along one business day. There is an ERP system which use....(more online)

   conversion from varchar to datetime - I have a column that is a datetime.  I need to build a date from the start date and start time and insert into this datetime field.  I get an error saying:Server: Msg 241, Level 16, State 1, Line 259Syntax error con....(more online)

   Die SPID DIE! (Kill doesn''t do the trick....) - Hi,I've got a small situation here.I've got a SPID that don't want to die.I've noticed this SPID this morning, and noticed that it was started about 3 day ago.When I kill it I get this the following message back:SPID 90: tran....(more online)

   SQL 2000 take a long time to stop services - Hi, I am stopping the SQL services but it is taking an unusual long time to stop.  It normally take a few minutes to stop, today it is taking over 5 min and is still in a stopping stage.  I know the log file is larg....(more online)

   query active directory - after looking at other posts i still can't figure out what is wrong with my query. I tried to get all the user from a group. and it returned 0. I know there are atleast 10 people in that group. plz help guys. this is what i h....(more online)

   Restoring to point in time - Hello everybody,I have a small problem,thursday morning I had full backup of my user database and every 1 hour I had transaction log backup.so, it goes like thisfull backup - 8:00 AMtransaction - 9:00 AMtransaction - 10:00 AM....(more online)

   DTS and MSDB Questions - Long story but a vendor dropped and reattached my databases but did not reattach the MSDB database. As a result all (about 40) of my DTS packages are missing and I don't have a current backup (no flaming please). So my questi....(more online)

   Building Stored Procedure from tables to populate view - Hi,I am using Sql server 2000 and I am trying to find the best way to optimize my views. I am new at this but I have a view calling another view calling another view because of the manipulations I need to do. I realize the pe....(more online)

   Insert 8 million records from text file to a table - Hi !!I have a text file with records like this:05-06-07-08-09-1004-06-07-08-09-1004-05-07-08-09-10There would be around 8M lines like this.I tried using BULK INSERT DB_Name.Table_Name FROM 'D:\Sample.txt' WITH ( ....(more online)

   data insertion problem - Hello everyone, i need help regarding data import, i m using SQL Server 2000 i have a database in which i have diagrams, Data, and views and SPs, now i make a new database on MSDE and add as a new instance in SQL Server 2000,....(more online)

   sp_OAMethod Failure - Hello,I have the following to run a DTS package within a stored procedure. I'm using the sp_OA... automation procedures but I seem to get stuck on the following line. Below it is a print of the error message. Thanks for your ....(more online)

   looking for Performance Tool - Hi, GurusI have a q for you. I want to monitor our applications.Meaning is that I want to proactively monitor our application during business hrs when users are using. Since I can not monitor all of screens or buttons that us....(more online)

   ooak keys - Could someone comment on the wisdom of using an "ooak" as part of 'all' / 'most' database table keys and foreign keys...Thanks,Paul....(more online)

   SQL Server not utilizing free memory - We are running a 24-hour production server with SQL Server 2000 Enterprise SP4 running on a Windows Server 2003 Enterprise box with 8GB of RAM. As per articles on the topic, we have implemented the /3GB and /PAE switches in b....(more online)

   Optimal disk/RAID config for six drives? - I have a server running four instances of SQL Server 2000 on top of Win2k3 that has four drives in a RAID-5 config on one SCSI controller, the box has only two slots available for additional drives. It's about to be upgraded....(more online)

   What is difference between != and operators ? - What is difference between != and <> operators in SQL ?Thanks in advance Amit Choudhary ....(more online)

   BULK INSERT Error - Hello everybody,I have a table 'online_batch14' with the following structure :SerialNumber Varchar(20), Comments VarChar(50), batch intI have created the following formatfile :8.021 SQLCHAR 0 20 "\t" ....(more online)

   temp table - How can i get the result of this in temp table  sp_MSForEachtable 'sp_spaceused ''?'''....(more online)

   How to Missing Date range?? - Hi Folks,I have one query:1. I have a date range in a table.EmpCode, Start Date and End Date. (one row per employee)Sample Date:1    01-Jan-2006   31-Dec-20072    10-Oct-2006 ....(more online)

   Inserting records ( from one table ) to multiple tables - I have a source table like belowData_Staging:Unique_idGenderEthnicityRaceMCP_keyAdmission_DtDischarge_DateEnrollment_keyReasonDisabilityIncomeEmploymentI need to load the data from this table to three different tables all hav....(more online)

   Weirdest interview question? - I'm sure I'm not the only one out there who's been on an interview and been asked a question that seemed really strange.  So I figured I'd put this out there to see what other odd questions have been asked at interviews.....(more online)

   SQL server accessed as file system? - Hello all, I was wondering if there was any way to specify a path to SQL server table or DB like a regular path i.e. "C:\folder\path.txt"I need this to specify where my records will be goin to and some pulling from,....(more online)

   Taking Risks - We all take risks in our jobs, though most of us try to mitigate the impact of those risks. It seems that the more many of us continue to work in IT, the less likely we are to perform action that might put our systems or ....(more online)

   Die Access, Die - I had to lead with this one because I have a similar view as this post. Access is handy, quick, does a few things nicely, but creates headaches and many more poorly written applications than I think are necessary. And why....(more online)

   13 Disasters - This is an interesting list of disasters that can befall a production system. It comprises more than database servers, but certainly can happen to them as well. The list is pretty good and of these I've had more than a....(more online)

   Tuning Time - I wrote an editorial recently on challenging yourself and choices in tuning code. It got me thinking about a poll though, so I decided to keep writing when I finished that editorial and write this one. A long, long tim....(more online)

   Financial Squeeze - In case you haven't been paying attention to the financial markets, there have been issues in the US lately with the stock market sliding and a credit squeeze. It's caused some problems for some companies, particularly fi....(more online)

   Reporting Services Corrupted PDF files - Hi,I use the command line program rs.exe to create some exports in pdf format from a Reporting Services Server 2005. I sometimes have problems to open the export of one specific report. Acrobat Reader shows error 109, file co....(more online)

   Count zeros - Hi I am very new to AS, four months actually, and have been working on an example model, really to get me associated with AS. I have a server time dimension, a letter dimension (will explain below), and an agent authorizatio....(more online)

   Conforming dimensions - Hi.I'm pretty new to dw and currently having some problems with building conformed dimensions. I'm trying to conform the payment code dimension from 2 different source systems.Below is an extract of some o....(more online)

   My Scheduled SQL 2000 Backup Taking too long - Respected Sir/sMY Database size (including MDF, NDF & LDF) is 8 Gb, and the backup size for the same becomes 7 Gb , but when Scheduled backup start, the application goes very very slow down. Any process takes longer time ....(more online)

   how to compare the vaules of counters given by idera - Respected sir How do we compare the values given by Idera & with which default configuration.For ex.If suppose value of one counter like Processor Queue length given by idea is 2 So how do we come to know whether thi....(more online)

   Read Only Tables - Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/readonlytables.asp....(more online)

Script SQL Server DDL

By: Anonymous - Posted: 2/3/2007 9:10:44 AM (  (Stand-Alone SQL))

Scripting SQL Server DDL
Richard Sutherland

If you buy into the theory that all database objects should be contained in a source management system such as Visual SourceSafe, and that deployment of database projects should be done from the source management system, then the manner in which Microsoft's Visual Studio 2005 Team Edition for Database Professionals [a.k.a., the "Data Dude"] should be of interest to you. It provides a tool which, among other things, will script out every object in a database to individual files -- something SQL Server Management Studio is incapable of performing. These files can then be checked into source management, and from that point forward all database development would be done in a normal development check-out/modify/check-in fashion to which many application developers are accustomed.

The Data Dude contains many features beyond scripting, but if you are just seeking a way to script out databases, and if you consider the expense the Data Dude for this process to be extreme, then a free open source project may be your answer. A “Script SQL Database DDL” tool can be found at: http://sourceforge.net/projects/script-sql-db. It contains a downloadable version of ScriptDB.exe, as well as the source code used to develop it in a Visual Studio project.

This tool scripts out entire databases into a directory tree which mimics the structure produced by the Data Dude. It produces a separate file for every object. In the case of tables, its triggers, indexes, constraints and keys each get there own file [just as the Data Dude does]. This differs from SQL Server Management Studio which, when scripting a table, includes all of these objects in the same file as the CREATE TABLE statement -- and this makes it difficult to use such a file in a source management system.

Tables are the oddball item when it comes to using a source management system, but if they are handled as individual components it makes much more sen