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

REPLACE Multiple Spaces with One

Jeff Moden from SQLServerCentral.com

Replacing multiple spaces with a single space is an old problem that people use loops, functions, and/or Tally tables for. Here's a set based method from MVP Jeff Moden. More »

Row Count Plus Transformation

Additional Articles from sqlis.com

As the name suggests we have taken the current Row Count Transform that is provided by Microsoft in the Integration Services toolbox and we have recreated the functionality and extended upon it. There are two things about the current version that we thought could do with cleaning up Lack of a custom UI You have to type the variable name yourself In the Row Count Plus Transformation we solve these issues for you. More »

Tell Us What You Think

Press Release from Red-Gate

Red Gate needs your help! Fill in our quick survey to help us improve our products – as a thank-you, you'll have the chance to win a Kindle Touch. More »

From the SQLServerCentral Blogs - Database Corruption, Transparent Data Encryption, and Trace Flag 5004

This one comes straight from the email bag.  A friend recently had a problem, they were placing TDE on a... More »

To those of us working in technology, it seems that the only constant is change. Windows 8 and Windows Server 2012 were just released as their RTM, and we had new versions of SQL Server and Sharepoint this year. Those technologies bring with them the need requirement that many of us upgrade our skills to work with new features over time. It's not that most of us need to learn to work with those versions right now, but the pace of upgrades continues to roll on, whether you are looking to move to the latest version or one that's slightly older.

As much as new features and capabilities are a part of version, it seems that the hype and promotion of different technologies can play as much a part in the decision to upgrade as the technical merits of any change. It seems no shortage of management is influenced by the reporting in the media and conversations with other executives about some new technology.

The "cloud" is a perfect example of this. It's in the news constantly, being promoted by many companies, despite the fact that the label is being applied to a mix of IaaS, PaaS, SaaS and everything in between. The Friday, I wanted to take a quick poll of how this amorphous beast might be influencing your career.

Do you think you'll work more with the cloud over the next year?

Define the cloud however you wish, but let us know. Do you think you'll see more cloud services used, deployments to the cloud, perhaps even some databases in the cloud? Let us know if the cloud is influencing your job or if it's just another fad you are hoping will fade away.

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 - 21.5MB WMV
• Watch the iPod Video Podcast - 16.8MB MP4
• Listen to the MP3 Audio Podcast - 3.5MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

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'd like to comment, post something here. The boss will be sure to read it.

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

Today's Question:

How many distinct T-SQL constructs can assign a value to a regular identifier with a leading '@' without using SET nor SELECT?

By Jack Hummer

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

This question is worth 2 points in this category: T-SQL. 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.

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!

I create the following table: 

DECLARE @NewTable TABLE 
(MyID INT IDENTITY, 
col1 varchar(20))

and then insert some data.  What will be the result of executing the following code?

TRUNCATE TABLE @NewTable

By Clare Newman

Answer: Incorrect syntax near '@NewTable

Explanation: It is not possible to execute the TRUNCATE as DDL statements are not supported against table variables.

Ref: (this made me smile when I found it) - http://connect.microsoft.com/SQLServer/feedback/details/331444/truncate-table-parameter

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

Defragment all indexes on Database

Jignesh Patel from SQLServerCentral.com

It finds the Indexes on database which needs to be Defragmented and Rebuilds those indexes. Only you need to replace 'database_name' with your database.  More »

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

SQL Server 2005 : Administering

disk space Report by email - Hi All, Can any one post a script that will send disk space details in a HTML format by a email...

Total backup size of backup of all databases in every month from backupset table in msdb - hi, Can you please provide a quick query to get the below requirement : Total backup size of backup of all databases...

msdb..backupset : Query Error. - While executing the below query to find the results getting the below error: Do I need to use cast/convert statement? Msg 8117,...

Sql Server Configuration Manager - Hi Experts, In one of my Stage server I am not able to open up the SQL Server Configuration Manager I...

SQL Agent Job History Retention Issue - I have seen strange issue pertaining to Retention of Job history on one of the production server. 1. Job is SQL...

good way to identify when was sql server installed? - I am using below query to identify when was sql server installed. [b]SELECT create_date as 'SQL Server Install Date' FROM sys.server_principals WHERE...

domain conversion and logins and users - I have a sql 2005 server that supports a 3rd party app. Users connect with win/sql authentication, the 3rd party...

whai is the need of adding T845 oarameter in startup in sql server - Hi, Please give me the explanation about when to add T845 parameter in start up for LPM(Lock pages in memory) for...

SQL Agent Error Flood - LOG] Unable to read local eventlog (reason: The parameter is incorrect)  I just had two seperate SQL Server 2005 Enterprise Edition...

SQL Server 2005 : Development

create procedure with execute as - I have stored procedure which includes bulk insert, activating and deactivating the access to xp_cmdshell and executing xp_cmdshell. I am...

SQL Server 2005 : SQL Server 2005 General Discussion

XML File Load via OpenRowSet - I am using the following code to import an XML file via Sql Server. The XML file is verified to...

SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy...

Minmize Down Time During Data Type Conversion - Hi Folks, We need to update a data type in an existing database from Int-->BigInt. Overall there are about 10 billion...

SQL Server 2005 : SS2K5 Replication

Best one-way replication option going forward - I'm looking for some insight into implementing replication with a SQL 2005 Standard database based on our needs as well...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Is UPDATE STATISTICS operation on columns of NTEXT Data Type Dead Slow. - Team, Is UPDATE STATISTICS operation on columns of NTEXT Data Type Dead Slow. I have few statistics in the database which...

Any info on Deadlock detection algorithms? - Hi Gurus, I'm trying to find a starting point from which to troubleshoot an issue we are facing where the same...

SQL Server 2005 : SQL Server 2005 Integration Services

Looking for info on handling error with script. - I have a package that uses a for-each loop to connect to a list of servers to transfer various pieces...

Why not processing any records further? - Hi Friends, I have come up with a weird problem....what i do is very straight forward process... i have a table...

Recordset Destination to SQL table? - Hello - I currently have an SSIS package being built where I want to upload rows from an excel document into...

Row_Number() in SSIS? - Hi Friends, I have a select statement having Row_number() over(partition by coulmn order by column) .... i see no option in any...

multiple datasources/servers? - Hi, What should be sql server integration package contents? For instance Query is ------------------ [code="sql"] select * from orders [/code] when connected to server...

"Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT' could not be opened. Operating system error code 5(Access is denied.). - I've created a set of SSIS packages which run fine in our test environment. The packages access a SQL server...

SQL Server 2005 : T-SQL (SS2K5)

How to list the source table name of columns in a VIEW (SQL Server 2005) - Just wondering how to list column name and table name in one query for a view for example a view named as...

Does/can OUTPUT clause turn identity_insert on & off - Does/can the OUTPUT clause turn identity_insert on & off ‘behind the scenes’? I think it does, but I can't find that...

adding specific column data w.rto date, where TOTAL_ELECTRONIC_VALUE which s already added - Pls help me with this issue.. this is my query SELECT PROCESSING_DATE, [ACL_BALANCE_CM] =SUM(TOTAL_ELECTRONIC_VALUE) FROM ODS.FUNDS_POOL_BALANCE WHERE PROCESSING_DATE between '16-May-2012'...

How do you use a sub query in a join? - Looking to take the following query: SELECT sales_order_number , count(sales_order_number) as Notification_cnt from ztb_IMP_Notifications group by sales_order_number and use it to feed the ztb_IMP_Notifications section...

SQL Server 2005 : SQL Server Newbies

SQL server table on a PC - Hello, can we create a SQL server table on a PC based on a query on the sql server ? Thank you

SQL Server 7,2000 : Administration

Perfmon output: Converting from binary to CSV file - Hi All, While collecting perfmon statistics, I saved the file as a binary file my mistake.  How do I read the...

SQL Server 7,2000 : Data Corruption

sysindexes consistancy error - Help needed to rectify the error below on SQL 2K SP4, any suggestion is welcome (DBCC REPAIR ALLOW DATA LOSS...

SQL Server 2008 : SQL Server 2008 - General

Service Key clarification - Hi, I am just after some clarification regarding keys. I understand that the Service Master Key is created on installation...

Job step indicates success when if fact failed, why? - Hello, I have a job with 4 steps in that didn't produce the results I'd expected. I checked the job history...

INeed to prepare a action plan by this requirement can any one send the information in a document fromat step by step IN very urgent basis. - INeed to prepare a action plan by this requirement can any one send the information in a document fromat step...

UTF-8 - Can any one tell me is it possible to enable UTF-8 for the SQL databases .If yes ,where can i...

shrinking the log file - Hi, we have a problem at work and the DBA has just gone on holiday (on an airplan right now!) We have...

Linked Server - insert data using Stored procedure - I have an stored proc which i have to run in multiple servers. my query insert into table exec serverB.database.schema.Storedproc Procedure...

New to SQL Server 2008 - Hi All, I have worked in SQL 2000 and 2005 environment till now. However my manager told me to take a new...

Unable to generate script - Hi , Im tryinig to Generate Script for all tables, StoredProcedures ,Functions in a database using Tasks->Generate Scripts->Select Specific Objects->Advance->Drop And...

SEPARATE WTH HYPHEN TO STRING again - HI ALL I HAVE STRING SUPPOSE STRING :- AD675498IJU76 I WANT TO SEPARATE WITH HYPHEN (-) BUT EVERY TIME POSITION IS NOT FIX. ASSUME FIRST TIME...

Bulk insert taking longer in 2008 R2 after upgrade form 2005 - I have upgraded my Production database from sql server 2005 to SQL 2008 with same configured Windows server. After upgraded...

login script - I need to write a script that creates will create login ID's based on the contents of a table. For...

Error while running sp_addsubscription on publisher - "This database is not enabled for replication." - Publisher (SQLCluster3\Inst1) - SQL Server Enterprise (10.0.2531) Distributor (SQL23) - SQL Server Enterprise (10.0.2531) Subscriber (Reporting) - SQL Server Enterprise (10.0.1600) I've added the publication, set...

Junior SQL Dba - training path - I am a junior dba with 1 years experience, including an mcts in SQL admin. I was wondering could an...

Do row constructors work in SSMS 2005 when connected to 2008? - I don't have access to this setup right now, otherwise I'd just try it, but I need to know if...

.csv format from ssms - Hello I want to take data extract into .csv format. I already try from select my result set and save as...

create new database - TITLE: Microsoft SQL Server Management Studio ------------------------------ Create failed for Database 'mydb'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476 ---

Catching the output of RAISERROR() - Hi, Is there anyway to capture the output of raiserror() ? Error Message is having placeholders %s %s .. so i need to...

sql logics1 - hi firend i have small doubt in sql plz tell me answer Auto commit has been turned off. 1- William logs into...

SQL Job - hello i have question about SQL Server Jobs I check the job properties and see the username is 'SA'. and i check...

SSRS 2008 - show all columns in matrix? - Hey everyone, I'm building a matrix report and I'm having an issue with all the columns not showing. Only columns...

SQL Server 2008 : T-SQL (SS2K8)

Bulk insert slower after upgrade in SQL2008 R2 - We did an inplace upgradeof Production database from sql server 2005 to SQL 2008 R2 with same configured Windows server....

running dos copy command from select statement - Here is my Query : --select item_number from item where item_number like '%Test%' For each item_number i would like to copy a...

Convert comma to single quotes - Hi Experts: This is my table [code="sql"] Condition Columnname akraft,crunckel TN AL,AZ State Atlanta,Austin-San Marcos MAC aney,aventura Area Alexandria, VA,Arlington, TX Market Alpharetta,Alexandria City 001,002 StoreS SameStore,HeitmanI Store

how to change the date value to null?? - hi , I have a column with date datatype. i updated the column with some date like 02-02-2012 00.00.000 ,for 100...

find max - hi friends, create table emp(ename varchar(34),job char(33),sal varchar(22)) insert into emp values('sa','sales','400') insert into emp values('ka','manager','4000') insert into emp values('sam','Markting','2500') insert into emp values('sabu','system','3900') in these...

script to create login ID's From the contents of a table - I need to write a script that creates will create login ID's based on the contents of a table. For...

Why doesn’t the last record win? - [quote] create table #tmpTest ( ClientKey int, GuarantorKey int ) insert #tmpTest select 10,0 create table #tmpGuarantor ( clientkey int, GuarantorKey int ) insert #tmpGuarantor select 10,1 union select 10,2 union select 10,3 update t1 set t1.GuarantorKey...

sqlquery to genrate xml - [code="<colname Code="xml"> </colname>"][/code] Hi here is the sample of xml code i need to generate .i am new to xml.please help me...

Converting Dynamic Pivot to Parameterized Query - Hi I have the following sample script which does a Dynamic Pivot on a table as follows: [code="sql"] --Creating Table Create Table Ex (InvoiceNumber int,...

How to get last 5weeks of data from currentdate - Hi, I am not able to get count in weekwise i.e 7 days shipped orders to be one week, please check...

comma separate value show as table - I have One table tbaleOne Column1 ------------------------- AA,BB,CC DD,EE,FF GG,HH,II I need out put col1 col2 col2 --------------------------------------------------------- AA BB CC DD EE FF GG HH II

How to grab characters before and after a string - Hi, I need to take the characters from the left before the '/' string and insert into a column and the...

Convert Text to Date DataType - Hello Everyone, I have an sql table which stores dates from some other system as text. Eg.: DD/MM/YYYY ,31/12/2012. I have made...

Varchar Exceeding 8000 characters - I had a dynamic query in which exceeds 8000 charactes adn will have approraxmately 15000 characters how can the same...

SQL Server 2008 : SQL Server Newbies

Need help with this query - I got this table LicenseId ClassificationCode GroupCode HasMultiple 10537 GA02 GA 0 10537 GA03 GA 0 10537 GB98 GB 1 10537 GF08 GF 0 10537 GF09 GF 0 Result set should return me...

Down grade from sql 2008r2 Enterprise to Standard - Since the only way to go from Enterprise to Standard edition is to uninstall enterprise and then install standard. I...

Format number thousands separator with point - Format number thousands separator with point Hi there, I need your help. I have this number in my database output result of...

Error Handling: TRY...CATCH - I'm trying to write an error handling query using the TRY...CATCH construct as a homework problem. I want to compare...

Just starting first job - SSIS & SSAS - I graduated recently from college and will by starting my first job in Business Intelligence in October. I'm currently going through...

Can't connect to sql server from an XP machine, but I can from win7 - Hi there, I've been given access to a SQL Server and I wrote a VBA script (in excel) to query the...

Landing The First SQL Job.... - Hello All! I currently am a plumber by trade, been in that industry courtesy of my father in law since graduating...

SQL Server 2008 : Security (SS2K8)

user mapped as dbo - hi, we are trying to drop a login that happens to be mapped/aliased as 'dbo'. it is not the owner of the...

SQL New Login can't see domain users - Hi I'm trying to set up Windows-based SQL logins on an existing SQL 2008 installation. The installation was created by cloning...

SQL Server 2008 : SQL Server 2008 High Availability

Netapp snap for files, log shipping for database? - One of our top challenges is to create a disaster recovery site so we can recover from an outage in...

unable to reconfigure mirroring - We are seeing the following error message when we are trying to reconfigure mirroring. We backed up the principal database,...

SQL Server 2008 : SQL Server 2008 Administration

Database removed from instance by automatically - Hi, I have recevied call from user side, they want change server side collation name in development server. I had done,...

Backing up to Mapped Drive - If I back up to a network mapped drive don't I have to use xp_commandshell? I asked that the drive be...

get a SQL alert - I just got a SQL alert: What does it mean, I see it generates some errors in sql log, see attachment. It...

Create index with DROP_Existing - Some Questions about Create index ... with Drop_Existing 1) in case of Create Clustered index ... with Drop_existing=on non-clustered indexes are rebuilt only...

Linked server to AD - Hi, I am trying to query our AD with LDAP through a linked server in SQL Server 2008 R2. This is...

SQL Plan Cache - Hi All I've been using the below script to check my Plan Cache for Ad-hoc plans [code="sql"]SELECT objtype , usecounts , COUNT(*) AS [no_of_plans] FROM...

Programming : Connecting

Oracle Linked Server - can anyone tell me how to set up a linked server to Oracle?   i have tried but keep getting Error 7399:...

Programming : Powershell

Powershell for network path - I am totally new to powershell(just 2 days) so please bear my ignorance.Here is the code I am trying to...

SQLServerCentral.com : Anything that is NOT about SQL!

Crazy Interviews - Reading the topic 'Stupid Interviewer Tricks' reminded me of an interview where I couldn't tell if I was crazy or...

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...

SQLServerCentral.com : Editorials

Using the Cloud this year? - Not this year, no, maybe not even next year, until they address all the security concerns. i don't see my...

Reporting Services : Reporting Services

How to Handle a column with Length more than 8000 characters - Hi, I have a report which consists of 40+ columns. one of the column's length is more than 8000 characters. Report is working...

Hyperlinks in IE - new tab rather than window? - Guys, I've added some hyperlinks to a test report, using the following code in the 'action' section of the text box...

Data Warehousing : Integration Services

SSIS: XML not being inserted into table - In SSIS I have a generated XML file based on a webservice. I need to read the file to generate...

Data type conversion error Money-> text in excel destination - Hello All, My SQL data type is money, when I load the data into excel it takes the value as text...

Data Warehousing : Strategies and Ideas

Object Oriented Source - Hi All, I've suddenly got a requirement (in truth it's a colleague's problem but I'm trying to help out) to get...

Data Warehousing : Analysis Services

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...

How to setup Roles Dimension Data - Hi, I'm looking at a SSAS Cube (version 2008) where I have made a role that has limited access to...

Notification Services : Administration

Multiple sqlservr.exe on Task Manager - I have only one instance of sql, MSSQLSERVER but on Task Manager, there are 2 sqlservr.exe running. Anyone have any...