In this issue

Featured Articles

Editorial

Featured Script

advertisement
SQL Compare "How does that work?! You leave work before me AND you get the coolest projects?"
Some SQL developers and DBAs have discovered a simple way to do more, in less time.
Find out what's making them laugh. Download your own free trial here.

ANTS Profiler Do you know how your code performs? First-rate developers wouldn't dream of releasing their application, before profiling the performance of their code. ANTS Profiler™ helps them identify bottlenecks, so they can fix known issues before shipping - and before anyone else sees anything! Download ANTS Profiler now.

Featured Articles

Reverse Engineering Alias Data Types in SQL Server 2000 - Part IV

In this article we are going to see how to change an ADT that is linked to columns with Unique Constraint(s) or Check Constraint(s).More »


The Subsystems of ETL Revisited

These 34 subsystems cover the crucial extract, transform and load architecture components required in almost every dimensional data warehouse environment. Understanding the breadth of requirements is the first step to putting an effective architecture in place.More »


Mirrored backup and restore commands in SQL Server 2005

Mirrored backup commands in SQL Server 2005 work with media sets to minimize data loss. They can also restore a mirrored backup from a different media family.More »


Editorial - Unstructured Data

I remember when Windows 3.1 started to gain widespread deployment in businesses. With all it's WYSIWYG features and multiple applications running at the same time, many people felt we'd get to a paperless office. Over a decade later I rarely see an office that doesn't have a copy machine and at least one printer. We've gotten better at shuffling bits, but we haven't gotten rid of paper.

Over the years I've also seen many applications that have tried to keep data inside the database, limit user's ability to work with it and drive our workflow along lines pre-determined by business analysts and developers. And what happens?

People suck out data into Excel and analyze it. Even if they have to do it by hand. Word documents often get used in place of other reports and PDFs abound throughout many companies when strict display and formatting needs arise. I've seen guesses that more data exists in unstructured sources, like Word and Excel, than in databases and it might be true.

I caught an article about how EMC was struggling to manage their unstructured data. They bought a data access and auditing tool to try and ensure that data was being properly secured and they could comply with various regulations like SOX.

Now I'm not necessarily thrilled with the need to micro-manage access controls. That can employ a few full-time people in any good sized company. And the tools to manage file permissions aren't the best in the world. The tool that EMC bought sounds like a combination of Sharepoint with a BI engine managing the permissions.

In fact, I think that's a good idea for a product. SQL Server 2008 is already moving in that direction with the FILESTREAM objects and Sharepoint is immensely popular. What if we could combine these two items together and then store all documents in the database/filesystem combination.

With all of the DDL triggers, event notifications, and data analysis components in the SQL Server platform, there should be a way to pre-package some auditing application around which you could write some general rules to manage permissions. Someone in Sales gets access to all kinds of information in their area, but if they pull more than xx documents in some period of time we raise an alert. Or if they start pulling more than yy customer contacts, we let someone know.

Managing permissions is hard, but it's something that's easier done in SQL Server, in my opinion, than in the filesystem. To me, this is the place that SQL Server's storage engine embedded in the filesystem could really shine.

Steve Jones


The Voice of the DBA Podcasts

The Great Music

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Today's podcast features music by Joe Sibol. If you like it, check out his stuff on iTunes or at www.joesibol.com.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you like it, tell the boss!

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

» To submit a tip, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

Can a server-side report can be accessed client-side? Sponsored by Apress and Asif Sayed, author of Client-Side Reporting with Visual Studio in C#

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

This question is worth 1 point. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.


Yesterday's Question of the Day

When you implement a group of Federated Database Servers in SQL Server 2005, how are you gaining scale?

Answer: Scale-Out

Explanation: A group of Federated Database Servers works by scaling out your servers.

Ref: Understanding Federated Database Servers - http://msdn2.microsoft.com/en-us/library/ms187467.aspx

» Discuss this question and answer on the forums


Featured Script

Extract values from a string using a delimiter

There are many occasions in programs where you need to manipulate strings of characters that are delimited by a particular character, such as a comma,...More »


Database Pros Who Need Your Help!

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

copy data from one column to another within the same table. - i've got a table [MyTable] with 3 columns. account, column2, column3 (column3 is empty) i basically have different accounts in the account...

Lost Connections - We have recently deployed our database application on a new client site. The application runs on 20 PC's (Windows XP) connected...

Can we block ODBC connections? - Is it possible to prevent or block ODBC connections to a specific database? We have a .Net web application that...

Logging into and Querying Read Only Standby Database - We have a Java web application that needs to be able to login to and query a warm standby database....

Stored Procs Timing out after 2005 Upgrade - We've experienced two applications timing out after running an "in-place" upgrade from 2000 to 2005. Are there some post-upgrade steps I...

owner name as a dbo problem - Please help... or give me ref where i can find the information on it. I browsed all around but couldn't...

Can SAN Read+Write Cache cause out of sync db calls? - I have a SQL Server database connected to a Dell/EMC CX300 SAN Drive array system, we bought a SAN package...

Cost and CAL question - I am very new to SQL Server 2005. I have bought two different CBTs and understand a small portion of...

Report Server Windows Service App Error 107 - I have an occasional recurring report server error in the app log: Event ID:107 Report Server Windows Serive [MSSQLSERVER] cannot connect to...

Login Authentication screen - I was having problems with Sql 2005 Sp1,sp2 versions were conflecting.i got that figured out but now in reporting services...

Impossible to attach Adventureworks - I just installed SQL 2005 and I tried (repeatedly) to attach Adventureworks; it fails displaying the Message Box below. I run DBCC...

SQL Server 2005 : Backups

Database Disappered - This morning went to use db sql express on vista home pc. ms access front end can't find it. see...

LiteSpeed and xp_delete_file - I have the following statement: EXECUTE master.dbo.xp_delete_file 0,N'M:\Backups\db_admin',N'bkp', N'2007-10-28 18:34:26' For files created with native SQL backup this deletes the files...

HOW TO: Detach an Mirrored SQL 2005 database from C: to the D: drive - Gents can you tell me how to do this? HOW TO: Detach an Mirrored SQL 2005 database from C: to the...

Restore db with 1 .mdf file instead of 2 files. - I have a db that has 2 data files. I'd like to restore as one data file. What's the best way...

Backup time increasing - Hi, I've been noticing that while taking a snapshot backup in MSSQL 2000 the time taken to backup the database completes...

Problem while restoring master database - I have to restore 'master' database in sql server 2005. The server has two instances. As part of restore strategy,...

Error in restoring SQL Server 2000 DB to SQL Server 2005 - Hi there, I'm trying to migrate a SQL Server 2000 DB to SQL Server 2005. Here's what I did: 1) Made a backup...

SQL Server 2005 : Business Intelligence

SSRS: adding custom page instead of default page - Hi, I can create an .rdl file and upload it to the report manager page i.e. the default page used...

SQL Server 2005 : Development

Getting data from XML using OPENXML - Hi Guys, I have the XML file (shown below) and i want to extract the category information.. I want to extract...

reporting on aggregates - Fairly new to non-trivial SQL statements and I have server logs that I have imported and I want to be...

Link Server OLE DB Problem - I created a link server from SQL Server 2000 to FoxPro database using vfpoledb driver. SQL server and FoxPro database...

SQL Server 2005 : SQL Server 2005 General Discussion

Metadata Structure / Design Issues - Hi People, I have been developing an application that needs to store 30 fields of meta data against each item on...

How do I create a fulltext index on a view? - Hello all, I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this...

Get longest length of data in a column - I am just curious about some data. I have a column in my table and it is varchar(3000). How can...

Issues running a backup from a trigger? - We have a trigger on a table and when the value of a field changes we run a xp_sqlmaint command...

Scripting out DB objects - In SQL 2000 there was an option to script each object to individual files. I can't find that option in...

Details of In-lined table-function - How to find details of In-lined table-function i.e size used etc. How to delete it why it is created??? Im running sql server...

Scripting Views in Management Studio VS Enterprise Manager - Good Morning, We have upgraded one of our environments from SQL2000 to SQL2005. There are several users (Billing System and Reporting)...

Help for query - On the following store procedure, if the error occurs on the second update statement, does the table be updated? [u]Store procedure:[/u] begin...

Importing spreadsheets with SQL Server 2005 - Hi, I am currently importing spreadsheets into SQL Server 2000 using A DTS package, all the spreadsheets import fine until I...

SQL query need help!!! urget - STUDENT (sid, sname, sex, age, year, averagegrade) SCHOOL (dname, numhonours) PROF (pname, dname) COURSE (dname, cno, cname) MAJOR (dname, sid) CLASS (dname, cno, classno, pname) ENROLL...

Connecting sql server 2005 remotely - I have a sql server 2005 remotely, need to connect from client using sql management studio. I am unable to...

SQL Server 2005 : SQL Server 2005 Strategies

Large Table Setup - I have begun a new project involving setting up a new database. This new database basically consists of one main...

Upgrade existing SQL 2000 Cluster to SQL 2005 - I have an existing multi instance SQL 2000 cluster with 3 nodes. I have each instance running on one of...

Powerful Dense Server Suggestions Please - We're currently running our production SQL Servers on 4U HP DL585s (4x dual-core AMDs). We continue to grow, scaling out...

Date encrytion - I have this situation where I need to retrieve information for a particular, date or a range of dates. My...

Migrate 2000 Database to 2005 - My manager decided this morning that we are going to take our SQL Server 2000 database and migrate it to...

SQL Server 2005 : SQL Server Express

SQL 2005 Express on Shared Server - I have looked all over the internet for an answer to this issue. We have one SQL 2005 instance installed...

SQL Server 2005 : SQL Server 2005 Performance Tuning

urgent: Extended procedure hanged...blocking other extended procedures to run - There is a extended procedure which got stuck....even though it get killed still in killed/rollback state and stopping other processes... stuck...

Using the -g Startup Parameter - Has anyone here ever worked with the -g startup parameter? I am troubleshooting issues with Red-Gate's SQL Backup program. The...

TSQL by Duration (Response Time) - Hi Friends, my application is getting very slow performance.:D Because sql server not responding properly in particular case like run some big...

Huge Table - Hi, I have got situation like that presently my huge table contains around 54 millions records and on daily basis it...

Disk I/O - I'm trying to get the best performance out of SQL2005 on our cluster. I have EMC Symetrix attached storage (via...

SQL Server 2005 : SQL Server 2005 Integration Services

Pulling Chinese characters into SQL Server via SSIS/DTS - I am pulling data from China using 2 different methods. In one case, a text file is sent to an...

SSIS Memory Allocation - 64 bit - I am trying to understand if SSIS can be configured to use more memory on a 64 bit machine? I...

Looking for seamless migration of SSIS pkg - Looking for seamless migration of SSIS pkg. My current design looks like this: [b]Define 2 config files on server A[/b] (all...

SSIS Diagrams [exporting to other programs] - Can anyone give me any information on the DTS/SSIS diagrams as to where they stored and in what format. I'm interested...

SQL Server 2005 : T-SQL (SS2K5)

How can one keep writing to the Log at minimum? - I saw this QotD where the answer was TRUNCATE TABLE. Then I read a bit more and was amazed that...

Update Statement With Incrementing Number - Hi, I am trying to write an update statement that will concatenate 2 fields and add an incrementing number. Something like...

Cusor Help - I need to change this cursor to update accounts to remove the letter 'P' in the prefix. This is the...

Dynamic Variable declaration to avoid CURSORS - Hi All, To optimize the performance, we did try to avoid and replace certain CURSORS. To avoid CURSORS, most of the...

Insert data from XML file - I have an XML File that contains a heirarchical set of data. There are 3 sets of data within this...

Temp Table 'vs' Table Variable 'vs' CTE. - Hi All, I wants to clear my concept about Temp Table, Table Variable & Common Table Expression Concept. Can anybody please clear me...

SQL Server 7,2000 : Administration

New user creation - Hi, I am exploring into DBA side. When I am trying to add user to the database, I am getting the...

Transactional Replication - I managed to create a transactional replication in our two testing servers. Replication Monitor shows 3 items namely : snaphot agent,log...

Large Table Delete 105+ Million Rows... - Well I have gotten into the middle of a pleasant situation...we have a MOM db server that the administrator for...

cannot generate SSPI context - can anyone help with what causes the following error Run-time error '-2147467259 (80004005)': cannot generate SSPI context it happen when i query the...

emails - I have a store proc which i want to make it as a sql job where in if it returns...

Getting lock on a nolock statement - Hi I have a query running on my production server SELECT * FROM table1 WITH (NOLOCK). But I can see that this spid...

SQL 2000 SP4 => Cannot restore a database across the network - When I try to restore a FULL db backup of a 200 GB database across the network I get the...

Job Step Type problem - When trying to select the job step type in EM on SQL2000, the only option available in the drop down...

sp_execute - When I see SQL Statemnet dbcc inputbuffer of a SPID, I am seeing as (sp_execute; 1). What ever SPID I...

Msg 7391 Unable to begin a distributed transaction -  Hi, I have two servers - On a daily basis Server A Executes a S. Proc on Server B which collects data (selects)...

SQL Server 2000 Enterprise Manager Snap-in missing - After running virus cleaning software on my HD, I suddenly find my Enterprise Manager snap-in missing and fail to initialize. Is the MMC erased...

Traces, traces, traces... - Since a few days I find messages in the eventviewer about traces: 19030 : SQL Trace started. Trace ID = '1' Login Name = 'sa' At the...

SQL Server 7,2000 : Backups

Large db restore, help needed - Scenario: Production database, total 640+GB, one mdf file and 4* 160gb add. files. This database is backed up once a week...

Archiving / Truncating - Hello, I could really use someones assistance with trying to archive / truncate a large database I have. Basically I want to...

Database Migration - hi, Actually, I've planned to move the data from source db to another db. In the source db, nearly 500...

SQL Server 7,2000 : General

How to store Audio files in SQL Server 2000 - Hi, Kindly provide solution for ,,,,,,,,,,, How to store Audio files in SQL Server 2000 regards pavan

How to make audit trigger 'capture' all rows affected? - I am working on a trigger for a table I would like to audit. I am quite new to triggers....

How to convert local path to network path to store my snapshot files - hello friends, i am trying to develop replication application using C# & .net 2005 with SQL 2005. i managed to setup replicaition using...

Command Line Zip Utility? - Scenario is this..I need to regular refreshes of a TEST system which is close to 60 gigs of databases across...

Full Text File Location - Best Practices - Any suggestions and/or supporting documentation for best practices on where to locate the full text catalog files. Especially as related...

SQL Server 7,2000 : Globalization

SQL collation and symbol characters - I have a database with Chinese_PRC_CI_AS collation. A varchar column (say ColumnA) in a table. When trying to insert [b]'Shim...

SQL Server 7,2000 : Strategies

No Business Logic in the Data Layer.... - I have an interesting position at present in a small firm that is growing fast. The IT Development Team are...

n-hibernate and dynamic sql - DBA vs Developers - I'm currently working with a development team that are trying to implement n-hibernate as a design tool to speed up...

SQL Server 7,2000 : SQL Server Newbies

INSERT Query - Hello! Using ASP, with the RecordSet Object (objRS), I have a question When running the following query: "SELECT something FROM table" To get the...

Standby / Trn Log Shipping - Few Questions - Hello Environment : SQL Server 2005 CE , Win 2003 I am new to SQL Server and has currently set up a (Production)...

Recursive query for a Bill of Materials table - Hi people, I could really do with a bit of help here. I have a table in the following format: StockCode1, StockCode2 StockCode1,...

SQL Server 7,2000 : Performance Tuning

Poor performance - Hi all, I have a SQL Server 2000 standard edition instance running on Windows 2000 SP4 with 2Gb of memory. The...

SQL Server 7,2000 : T-SQL

Varchar default length - Hi Experts, I am very confused about the default length for varchar datatype. I was read an article in our sqlservercentral.com...

Difference between Client cursor and server cursor - Hi Experts,;) I want to know the difference between client cursor and server cursor.Where exactly the server side cursor is used...

Dynamic INDEX - Hi all, I have a problem with my Stored Procedure. It runs longtime. How can i create Dynamic index for...

Missing Sequence - SQL2000 A table contains ID, CODE, and PRIORITY(varchar). For each ID Exists codes prioritized 1..n. Example :(ID, Code, PRI) 1 , A1, 01 1...

Retrieve the DST patch updated date in sqlserver 2000? - Hello all, Hope all of us know that DST Patch update will be on 4th November,07. Here my query is how...

how to remove numbers from strings? - hello, i have a question: i have a string and i need to remove the numbers from it. something like oracle's...

SQL Server 7,2000 : SQL Server Agent

how to connect to SQL SEVER 2k running in win2k server machine - hello everybody i am pretty new to sql server and msde2000 our server has sql server 2k and the client...

Career : Certification

Business Intelligence Developer Certification - Hello, I'm interested in obtaining Microsoft's "[url=http://www.microsoft.com/learning/mcp/mcitp/bid/default.mspx]Business Intelligence Developer[/url]" certification. In order to obtain the certification one must pass both...

SQLServerCentral.com : SQLServerCentral.com Website Upgrade

Where is the article - I was navigating through articles and got to this http://www.sqlservercentral.com/articles/Basics/20010422115709/78/ But where is the article? All I can see is the introduction.

SQLServerCentral.com : Suggestions

Editing a post - Steve, Just an FYI. If you post, then choose "Edit Post" after submitting the original, while you're in edit mode, the...

What is the best meathod of Importing Excel files. - hi everybody, I am maintaining a system which is importing CSV files into SQL Server. we use BULK insert from a...

Reporting Services : Reporting Services

Report Statements - I have a report that is basically a statement (invoice) The report has a list which is based on the...

Filter dropdown does not populate in Report Builder - We're using SQL 2005 Reporting Services. The Reporting Services is configured to use Integrated Windows Authentication in IIS. The problem is...

ORA-12154 TNS could not resolve service name when testing Oracle data source - Hello, I am trying to use SQL Server 2005 Reporting Services to access an Oracle 8i database. I can access the...

Reporting Services : Administration

report manager error need help - Hi, I have a sql server 2005 on a windows 2000 machine. I installed sql server 2005 but when I...

Data Warehousing : Strategies and Ideas

In search for good reading material about DW design - Good day all, I'm searching for any good books/white papers/articals about designing data warehouses. I've found a couple of books, but...

Datawarehouse, "large" INSERTS, memory usage - We are developing our first datawarehouse. When running a developer query (an INSERT of a modest dataset, 4 millions rows) the DW...

Data Warehousing : Data Transformation Services (DTS)

Working With Multiple Flat Files - Hello, We have a system that, on a daily basis, produces a flat file of fixed width records from a mainframe....

Microsoft Access : Microsoft Access

Matching the duplicate records and doing a count HELP? - The code below is the query . what im trying to do is to get all the personnelNumbers that occur greater...