A community of more than 1,500,000 database professionals and growing

Using SSIS to export data from a 64 bit server to Excel 2007

Krista Olson from SQLServerCentral.com

Instructions on how to set up the connection string and SSIS package to allow a 64 bit server to export to a 32 bit version of Excel More »

Free eBook: SQL Server Execution Plans, Second Edition

Press Release from SQLServerCentral.com

Get a free ebook from Red Gate Software and SQLServerCentral from MVP Grant Fritchey. Learn how to read and interpret execution plans. More »

Report Building 3.0: Adding Maps to Your Reports

Additional Articles from SimpleTalk

With Report Builder and SSMS, there isn't much you can't do in the way of the common reports and visualisations. One of the more interesting visualisations uses maps, and that, combined with layers, makes for an impressive way of representing data. More »

From the SQLServerCentral Blogs - Backup History

Jason Brimhall from SQLServerCentral.com

This is a quick rewrite of the script I just posted a few days ago.  The script had a few... More »

From the SQLServerCentral Blogs - How To Download Free Sessions From PASS

For all members of the Professional Association for Server (PASS), are you downloading slides and videos from all sorts of... More »

When we setup SQL Server, there are a few choices we need to make, some of which require a few items be prepared in advance. Service accounts are one of those preparation tasks that DBAs usually perform in advance of installing an instance. There are a few things, however, that aren't configured when SQL Server is installed, like alerts and operators. I find that often these items can be missed by inexperienced DBAs, who forget to configure standard alerts. They might also not have a mail account ready, which is required for the instance to send mail.

Perhaps there's a better way for SQL Server to request this information when it is installed. If one part of the setup program asked for default mail information, it would be easy to pre-configure the instance to setup alerts and enable them for communication with a DBA. Personally I'd prefer that setup get a mail profile and a default DBA group email address that receives information about the operation of the instance that the DBA should be aware of. 

There can be a hesitation from experienced DBAs that don't want setup to install mail for them, but it would be easy to make this an optional component, with a checkbox like the one for a blank sa password. You actively choose to install the server differently if necessary, but otherwise setup would push DBAs to ensure that notifications are configured. We could even see a pre-selected list of alerts that will be enabled, with the option to disable all or just selected items.

I don't know that Microsoft will build these types of defaults into SQL Server, but I wish they would. We've done that with SQL Monitor at Red Gate, and we have seen the advantages that come when you provide good practices and knowledge to less experienced DBAs: they gain some knowledge transfer and have software running at a high level from the very beginning.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 20.7MB WMV
• Watch the iPod Video Podcast - 20.4MB MP4
• Listen to the MP3 Audio Podcast - 4.1MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

I have the following set options

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON;

I then execute the following T-SQL statement

CREATE TABLE "SELECT" ("TABLE" int)
GO;

Is the table (Select) created ?

By bitbucket

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

This question is worth 1 point in this category: options. 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.

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

This question and answer have been validated for SQL Server versions 2008 and 2008 R2 (and may be correct for other releases too). If x is a char(1) variable, how many values can it take for which

@x LIKE '[aeiou]' collate Latin1_General_ci_ai AND NOT
  ( @x like 'A' collate Latin1_General_ci_ai  OR
    @x like 'E' collate Latin1_General_ci_ai  OR
    @x like 'I' collate Latin1_General_ci_ai  OR
    @x like 'O' collate Latin1_General_ci_ai  OR
    @x like 'U' collate Latin1_General_ci_ai
  )

is true?

By Tom Thomson

Answer: 2 values

Explanation: There are two such values. Like '[aeiou]' checks for all vowels, not just variants of the 5 shown, so it picks up the digraph ligatures Æ (char(198)) and æ (char(230)) as well as the variants of a,e,i,o and u.

I can't find documentation for this, but it can easily be verified: here is code to list all values that satisfy the conditions:

WITH sixteen(x) as(
  select x from (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),
                        (9),(10),(11),(12),(13),(14),(15) )v(x)),
asciiset(ch) as (
  select char(v1.x+v2.x*16) from sixteen v1 cross join sixteen v2)
select ch, ascii(ch) from asciiset 
  where ch LIKE '[aeiou]' collate latin1_general_ci_ai 
  AND NOT (
    ch like 'A' collate Latin1_General_ci_ai  OR
    ch like 'E' collate Latin1_General_ci_ai  OR
    ch like 'I' collate Latin1_General_ci_ai  OR
    ch like 'O' collate Latin1_General_ci_ai  OR
    ch like 'U' collate Latin1_General_ci_ai
    );

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

How much space could you get shrinking all databases

Gonzalo Moles from SQLServerCentral.com

This is script is useful when you are short of space on a drive and you need to know how much space can you get shrinking your databases More »

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

SQL Server 2005 : Administering

Checking for Available Disk Space (outside of SQL Server) - We are running SQL Server 2005 SP4 on Windows Server 2008 R2 SE SP1 and Windows Server 2003 R2 SE...

Maintenance plan for SQL DB maintenance order - Hi, Best maintenance plan order for Production Database(>100GB) sqlserver 2005. Thanks in advance

CPU Usage using T-SQL - Hi, Please help me, how can I get CPU usage in % using T-SQL? Regards - MSR

SQL Server 2005 : Backups

Error Restoring: Database was damaged when the backup occurred - I recently backed up a database and when trying to restore it to my local machine I got this error: [code="sql"] Msg...

SQL Server 2005 : Business Intelligence

i am working on ssis 2008 so i have written vb.net script in my script task that given below errors .please give solution to resolve the problems - Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly...

SQL Server 2005 : SQL Server 2005 General Discussion

Client application generating time-outs - Hello! We are having problems with an application which accesses our SQL Server DB. The application generates time-out errors. The application...

How to implement this DDL trigger? - According to BOL, DDL trigger respond to CREATE, ALTER, DROP + other T-SQL commands. I'm currently using two triggers for when a...

Regarding sql server 2005 - what are the concepts should know in sql server 2005

SQL Server 2005 : SQL Server 2005 Performance Tuning

need one doubt clarification application team trying to execute a simple query - hi all need one doubt clarification application team trying to execute a simple query but its taking long time i tried evry thing suppose if...

CXPACKET wait type - Hi guys, I'm seeing CXPACKET wait types in one of my database running SQL2005 which causing the CPU hit 100%. The...

SQL Server 2005 : SQL Server 2005 Integration Services

Pass SQL Execute task output to target tbl(Data flow). - Hi, we need to insert random values to target table. We are using the query in SQL Execute task with single row...

Data Import from CSV file - I have a CSV file which is having record like mentioned below. [code="other"] UserName User Id Type RamKumar ram MSAD RamaC rama MSAD RamesK rames MSAD Rameshv ramesh MSAD RamKumar ramp PCG Ramig rami ...

SQL Server 2005 : T-SQL (SS2K5)

CASE WHEN Statement when creating a VIEW - Hi Guys, I am having a few issues with some code and wanted some guidance ; I am creating a view based...

SQL Server 2005 : SQL Server Newbies

creating a table to show month and year - I need to refer to a temp table which has all the months and dynamically shows the years. for example...

SQL Server 7,2000 : Administration

User session memory - Is there a way to find out how much memory a user session is taking? I have an application that...

SQL Server 7,2000 : SQL Server Newbies

SQL Server 2005 Job email notification alerts - Hi there, I was wondering if somebody could tell me how to configure the notification alerts on a job properties....

SQL Server 2008 : SQL Server 2008 - General

plant count really high-Activity Monitor - Hi I am new to the DBA side, but activity montior shows a high plan clount of 23438 The query that an...

SPLIT RANGE TOOK 10 HRS to Complete - Hi All, I just split the Range (introduced new range) of my existing Partition Function but it took 10HRS to complete...

HOw do I update the another field of table which is not effected by Bulk Insertion . - HI Friends We want to bulk insert into table table1 Create table table1 { ID, ProjectID, Fname } Create view VwName As (select Fname from table1) Create procedure prc_insert @ProjectID int As BEGIN BULK...

Regard to SQL Version - I have to SQL Server and one has version, Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30...

Why session goes to Suspened?? - Hi Friends, I need to know on what cases a session goes to SUSPENDED state in SQL SERVER. Just for knowledge...

Mirror server hardisk I/O utlisiation 100% - Hi, Should my mirror server hard disk I/O utilization be between 90% and 100%. ? I understand its the mirror server and...

database is not connecting . - Hi All, While connecting to database am getting below error. "A transport-level error has occurred when sending the request to the...

Sessions and Requests - Hi all I am just trying to understand the relationship between sessions and requests. If I understand correctly then a request...

how to i find out the user when stored procedure last modified - Hi All, how to i find out the user when stored procedure last modified i am able to get the list of...

I could not take backup successfully - During backup process with NetBackup agent, I got the following error: Violation of PRIMARY KEY constraint 'PK__backupme__DAC69E4D04AFB25B'. Cannot insert duplicate key...

how to shrink logfile ? - Hi All, While running one sp am getting this below error 'The transaction log for database 'testdb' is full. To find...

Concatenation of strings with condition check,,, - hi Team, Need your help............. My table contains below columns with values. ColA ColB ColC ColD ----------------------------- Admin NULL NULL NULL Market 101 256 258 Sales 205 125 NULL Admin 256 258 548 and i want output like below... ColA:Admin, ColA:Market, ColB:101 ColC:256 ColD:258 ColA:Sales : ColB:205...

Operating mode change in mirrroring - I configured mirroring with High safety with automatic failover (synchronous) option. Now I want to change my operating mode to High...

cluster upgrade - We are planning to in place upgrade of sql cluster and also the db is configured replication.

IF EXISTS Logic - Hi Team, I am having a temp Table [b]"@Temp_Table[/b]" and a physical Table [b]"St_Value"[/b] [u]@Temp_Table Contains[/u] Case1: Column_Name | Value ------------------------------ Col1 123 Col2 NULL [u]"St_value" Contains[/u] Column_Name | Value ------------------------------- Col1 123 Col2 582 Col3 [b]My Query is :[/b] if @temp_table...

Steps to verify database diagram - In one of our project, where i recently joined , team leader gave me database diagram to verify whether they have...

A problem with using full text search with wildcard query - Hi, I have a weird case with the full text search in Sql Server 2008 R2 SP1 web edition. I have a...

sql query implementaion - hi friend i have a small doubt in sql server plz tell me how to solve table data contins like...

locking in DML statement - Hi what is the difference between IX and X lock on a table?(Totally I plus s,x,u,etc.) according to what i have read,'I'...

is there any difference between query run time and execution time - hi, is there any difference between query run time and execution time Thanks, Dastagiri

SQL STACK DUMP FILE - SQL has created a stack dump file of 2.5 GB I have copied the file elsewhere is it OK for...

Alternative to xp_cmdshell - i need to delete some backup files as per daily operation through SQL ,currently i am using xp_cmdshell ,is there...

Mirroring Endpoint Port Number - Dear, I want to configure database mirroring with automatic failover option. That is why I want to make my PC as...

Run a Query on a Database Restore - Hi, I'm looking for how I can have a query run against a database when it is restored. Basically, I have...

cluster upgrade - iam new to cluster environment can any one send me the approach how to upgrade SQL 2005 cluster to SQL...

Identity Seeding - Just curious Anyone found a reason to declare an Identity column that wasn't (1,1)? Sure, legacy data can cause you to start...

pages - HOW MANY datapages in sql ?

Read data from Excel and while inserting getting data type error - Hi, I am reading data from excel sheet and inserting in to temp table something like this SELECT * into Temp_table FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel...

Select query to determine unique values - I am trying to create a query to return certain information, but I am not sure how to get the...

TSQL Problem: Week between 2 different months - Hello, I have a script problem. I calculate data per week (group by week) However, for indicators, I have business rules which apply...

LIKE with and without wildcards in WHERE clause - I have a set of pretty well tuned queries with indexes and all the appropriate trinkets. This is a 'last...

Creating a Stored Procedure that accepts parameters - Err

rebuild index worsens query performance - we have a query which runs several times a day reads from several tables in one database ("W"), and two tables...

Best way to implement partitioning - I have my own idea on how to handle this, but want to se if there is a different perspective...

SSRS Save file to folder (Windows File Share) with date as prefix to filename - Hello - I am creating several reports. Each report runs daily and a team uses each of the reports on a...

SQL Agent Job succeeds even though step should fail - Does anyone know why a Transact-SQL job step that executes a stored procedure and returns a "Query timeout" response continues...

SQL Server 2008 Service SIDs - Hello, This is just a quick question about the security changes in SQL Server 2008. In 2005 during setup, SQL Server services...

Informix Linked Server problems - I have been asked to set up a few procs that will take data from our core data source in...

SQL Server 2008 : T-SQL (SS2K8)

Query for Selecting Two Tables - ProjectTable: ID ProjectID ProjectName 1 PRO14 Demo 2 PRO15 Test Task Table: ID ProjectID TaskID TakName 1 PRO14 12 module 2 PRO14 13 code 3 PRO15 14...

Need help in xml - [code] DECLARE @myDoc xml DECLARE @AppName VARCHAR(100) SET @myDoc = '<assembly xmlns="urn:schemas-microsoft-com:asm.v1"> <assemblyidentity version="2.2.2.2" Appname="Application2.app"> </assemblyidentity></assembly>' SET @AppName = @myDoc.value('(/assembly/assemblyidentity/@Appname)[1]', 'char(150)' ) SELECT @AppName [/code] may i know what...

Thousand Seperator Magic - All, I have recently faced the below scenario. i.e to add comma as the thousand seperator I have searched here and found the...

Calculate weekend - Hi, I have a scenario in which i need to create a function which takes 2 parameter (count,startdate). If I have...

Table Partitioning Help - Hi Gurus, Is someone can help me with Table Partition? with e.g or show me tutorial that would be awesome. Thanks...

Dynamic top per group - Hello All, I'd like to specify a dynamic Top criteria and return the top number of records per group. My desired results...

problem inner joininig a derived column - Hi, I'm trying to get a nice chart working, that gets data from SQL Server, but having trouble getting the...

Query Help - I've a table like below: [code="sql"]CREATE TABLE #Emp ( Emp char(6), UplineEmp char(6), Lvl tinyint) ; insert #Emp ([Emp], [UplineEmp], Lvl) values ('209156' ,'003033'...

xml in subselect and performance => alternatives? - hi there, many times there is the requirement of selecting multiple records into one xml-string. Usually you do this using a...

Exercises in t-SQL - I'm in the process of setting up a training environment here, it's a mechanism for me to bring high flyers...

SQL Server 2008 : Working with Oracle

Materialized View Logs on Log shipped secondary server database - Hello, I've a database in primary server that log ships to a database in secondary server. I want to capture only...

Data migration from sql to oracle - I have transferred one table from sql server 2008 r2 to oracle 11g. using import export wizard. all permissions in oracle...

SQL Server 2008 : SQL Server Newbies

unable to connect to sql server - Hi all! im posting this here since i am, in every sense of the word, a newbie. We have 3 MS failover...

Problem With AdventureWorks - Hello: I am new to this forum and would like some help with SQL Server 2008 R2 Express. I downloaded SQL...

Reduce database size - Have been asked by a customer to reduce the database size to latest 10% of its data. Not sure how...

html from sql server 2K8R2 - Hi all, I am trying to generate an HTML-formated email from sql server. I need to be able to control the...

SQL Server 2008 : Security (SS2K8)

encryption and decryption - whts the diff in using symetric and asymettic keys in encryption as i hve reffere http://sqlusa.com/bestpractices2008/password-encryption/ for asymetric encryptio & another set of...

Limit Concurrent Logins by database and/or user ID - In my environment I have a sql server 2008 database on 64bit architecture. The production database accessed by users is...

SQL Server 2008 : SQL Server 2008 High Availability

SQL server 2008r2 instlation - hi i tried to install sql server 2008r2 in mixed mode any one help on this.

Replication between SQL 08 r2 enterprise & standard - Hey probably a very common question but can you Replication between SQL 08 r2 enterprise & standard editions ? Thanks

SQL merge replication - Hi I am tryin to create a merge replcation and the snap shot is not being created. it shows 0% untill...

SQL Server 2008 : SQL Server 2008 Administration

Restore master db using a red gate backup file - I need to restored the master db file from one of my red gate backup files. I'd like to know...

Regard to SQL Version, - I have to SQL Server and one has version, Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012...

SCOM on SQL Server 2008 SP1 - Hi All, How can I know id SCOM is installed on my server or not. If yes, is there a place...

SQL Server index fragmentation is high after rebuilt - I have few indexes in my db, which report high fragmentation even after we rebuild indexes. The page count on...

How did you accumulate and where do you keep your administration scripts (or other scripts for that matter) - I have a question for the more experienced DBA's out there. im starting to develop queries and scripts that make...

Memory utilization by a single sqlserver instance alone. - Team, Is there any way to find how much how much memory the single sqlserver instance is actually using in...

SQL backup to network drive - Our 20 SQL Servers all running under "Local System account". Backup to network share doesn't work - Error. I guess because local account...

autogrowth - I would like to know as a dba practice, do you setup the autogrow for file size to a restricted...

Programming : General

Syntax request needed. Merge an 'openquery' select towards linkedserver - hello all, How can I merge these two queries to produce one row? i.e TESTSERVER | VERSION select * from openquery([linkedserver here],'select name from sys.servers...

Programming : XML

import and shred xml file to sql 2008 table (openrowset) - Hello, I'm also trying to import certain fields of an xml-file into an existing sql table (neutc_import). Problem is: I get '0...

SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Adding a detail row when none exist - I initially thought I would not need detail rows in my VS 2008 BIDS report, and deleted them while keeping...

last day of the month parameter in ssrs - I occasionally need to schedule reports that run for an entire month and automatically email them. A monthly report, of...

Data Warehousing : Integration Services

SSIS datediff and dateadd - Hi All, I wanted to find the starting of the week and poulate it as an additional column. Im trying to...

Data Warehousing : Analysis Services

Numeric Dimension Attribute in SSAS 2008 R2 - Good evening to all. I use SSAS 2008 R2 and i did some cubes. I built a dimension PRODUCT that contains 2...

Report Builder 3.0 - report displaying differently in a browser - Hi all, Just wanted to see if anyone else has had my problems with reports in a browser. I am creating...

Microsoft Access : Microsoft Access

QR Code-Integration of Microsoft Access and Microsoft Word - What is the easiest way to integrate Microsoft Access and Microsoft Word? We are converting permit numbers to QR code. 00000...