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

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Jeff Moden from SQLServerCentral.com

Another in our series of articles to help you fill in the cracks in your knowledge with SQL Spackle. MVP Jeff Moden shows us how IsNumeric works and how you should use it. More »

Red Gate Software announces speaker line up for US SQL in the City tour

Press Release from Red-Gate

SQL in the City is a free, full day training and networking event for database professionals. After the success of last year’s event, Red Gate has expanded the event to cover six cities from sea to shining sea, including: New York, Austin, San Francisco, Chicago, Boston, and Seattle. More »

Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

Additional Articles from Microsoft MSDN

This white paper provides practical guidance to help BI professionals and decision makers decide whether SQL Server 2012 Analysis Services tabular or multidimensional modeling provides the best fit for your next BI solution. More »

From the SQLServerCentral Blogs - Change file logical name for mirror database

jamesxu98918 from SQLServerCentral.com

Today I encountered a interesting issue when rename logical name for mirror database. We have a database which has been setup... More »

From the SQLServerCentral Blogs - Sleepless in Seattle – SQL in the City 2012

Steve Jones from SQLServerCentral.com

A few years ago I went to Seattle a few days before the PASS Summit started. I took my middle... More »

I get accused of not writing enough technical editorials and it's probably true at times. Every once in awhile I wander down some strange path and get a bunch of writing done on subjects not necessarily related to DBA work, but I appreciate your indulgence. And some are kind of fun, like the every other month car updates . So ...

I was doing some household chores the other day and putting away some laundry. With my wife traveling out of town for half the week, I've got to pick up some slack or things start to get out of control. As I was putting shirts away, I thought this would be a good poll for the week:

How many SQL Server (or SQL Server vendor) shirts do you own?

I guess it could be any category since some of you are not DBAs and may go to other conferences like Exchange, etc. I figure my buddy Dean has 10 SMS shirts and Chris Rock probably lives in his collection of VB swag. By my count I have the following:

• 6 different SQL Server t-shirts, including 2 power-hour shirts
• 6 different Red Gate t-shirts
• 2 different Imceda t-shirts
• 2 SQL Sentry shirts
• 14 SQLServerCentral.com shirts - no surprise here, 9 Hawaiians, 3 polos, one oxford, and one t-shirt

That's a pretty good spread of T-shirts and then some nicer ones when I want to dress up

I'm sure some of you have me beat, though I've got a few more TechEd T-shirts, and various other MS products including a denim PDC shirt from 1998!

(This editorial was originally published on Aug 23, 2007. It is being re-run as Steve is on vacation.)

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

Let’s suppose that you have disconnected all database connections from your application so that the application cannot access the database. While taking that database offline, you noticed that it’s taking a long time. It could be because some external resources are accessing that database. How you can take the database offline?

By Naseer Ahmad

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

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

Can the following script be made to run without changes to the script and without error?

DECLARE @Test varchar(30)

SELECT TOP 1 @Test=name FROM sys.databases

PRINT @Test

NotGo

DECLARE @Test varchar(30)

SELECT TOP 1 @Test=name FROM sys.tables

PRINT @Test

NotGo

By Kenneth Fisher

Answer: True

Explanation: GO is not a true SQL command, it is a batch separator recognized by SSMS, sqlcmd, and osql and can be configured to be a different string. Changing it isn’t really a good idea since GO is the default, but it can be done.

Ref: Query Options Execution - http://msdn.microsoft.com/en-us/library/ms187516

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

Search for a Table across multiple databases on a server.

Kishore Tallapragada from SQLServerCentral.com

There are other options available like Sql search from Red Gate. Here's a simple script to search for table(s) containing a particular keyword. You can easily extend it to other type of objects by changing the Xtype. More »

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

SQL Server 2005 : Administering

Error while converting varchar to numeric - Below is the query which is giving the error:--- SELECT [Working ISIN], convert(decimal(18,3),[Split Old Paidup]) as [Split Old Paidup] , convert(decimal(18,3),[Split New...

Server patch testing - I am a QA for package testing. In our lab we also test the Microsoft patches on the test PCs...

How does SQL Server authenticate windows login ? - Hello, How does SQL Server authenticate windows login ? I got the following from Microsoft website. "When a user connects through a Windows...

Communications to SQL server "freeze", then resume. - I am looking to see if anyone else has a few ideas of where to start in looking for problems...

Question on ACID properties - Experts, One of the interviewer asked me something like below question. It was not clear to me anyway. "When we get an...

Reg: Not Connecting Named Instance through Public IP - Hi In my production server i installed sqlserver 2005 in default instance and one more named instance also .In this...

Unable to increase initial file size in SQL2000 - I need some help on resize a ndf file in SQL2000. Some of our databases have reached over 1TB. I've...

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

Change Database Status from Shutdown to Normal - HI, We have a database that the status is set to Shutdown. How can I change the DB Status to Normal. Thanks,

SQL Server 2005 : Business Intelligence

process - Hi All, in my solution we need to process weekly sasles cube through a ssis package. for this purpose there is a...

SSIS Package Fails - Hi All, The SSIS Package Fails saying "The AcquireConnection method call to the connection manager "Excel_Source" failed with error code 0xC0202009" Here...

Hide multiple Columns based on Parameter value in SSRS 2004 - Can anybody tell me how to hide/show multiple columns based on multivalued parameter. ex :i have two parameters 1)item 2)option(multivalued - ven,itm,qty)...

SQL Server 2005 : Data Corruption

How can i drop table? - Dear all, I have an error in my database. I write command that is 'Drop table tb_name'. When i run that command,...

SQL Server 2005 : SQL Server 2005 General Discussion

TRY CATCH 2005: SSMS still sees error (2012 does not) - Here's an interesting little thing I've found - SQL 2005, when given two errors, CATCHes one and lets the other pass...

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

Data Migration Options - We have SQL Server databases located on different servers for our hosted web application. Each database can house multiple 'customers' along...

rounding up to the next whole number - I'm trying to round a number up to the next whole number in sql server 2005 query analyzer. i.e. 12.2 needs...

SQL Server 2005 : SQL Server 2005 Integration Services

ODBC-connection to sublist failed - I used the Access upsize wizard to split my database. I still wish to use the Access for the frontend...

Problem with Dynamic Column Mapping - I am working on a Data Flow Task within an SSIS package. I've created three OLE DB connections, one source...

SSIS package fails only as job - I have a package that runs fine in development. But when I run it in a job, I get this...

String Literal in the expression exceeds the maximum allowed length of 4000 characters. - Hi All, I need a small help. In my ssis package, huge SQL statement is constructed through expressions Problem: SQL is exceeding more...

Save mail files(.msg) from Mail Server using SSIS 2005 - Hi Guys, I am working on SSIS packge where I want to save mail files(.msg) coming from Mail Server along...

Save Outlook mail file (.msg) using SSIS 2005 - Hi Guys, I am working on SSIS packge where I want to store Outlook mail file(.msg) along with attachment using...

deployment of the package - hi i am building my package ,but after that itdoesnt not show under MSDB folder. Any help?

send email - hi i need to give mail ,that if number of file deleted is 5. the message should be 5 file...

SSIS - FTP TASK - DYNAMIC FILENAME - Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with...

SQL Server 2005 : T-SQL (SS2K5)

Update using Max() value on multi join query - Hi, I have a problem where I need to update a table (Results). The one thing I can't get my head...

Minimum Value 3 Fields > 0 - This seems like it should be easy but I can't seem to come up with a simple formula. Need the...

Problem with BULK INSERT - I'm trying to use the BULK INSERT statement to populate some table from a TXT file. In the TXT file field...

Filling Buckets - A customer had reported an issue with one of our stored procedures so I took a look and found that...

SQL Server 2005 : SQL Server Newbies

Maintenance Cleanup Task can't delete trc files - Hi, I've created a maintenance plan with a Maintenance Cleanup Task as a subplan. In this Maintenance Cleanup Task : I...

column to calculate the balance stock dynamically - I have a table as shown below and would like to have the balance quantity which should be dynamically calucalted...

SQL Server 7,2000 : General

Compare two databases - I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is...

SQL Server 7,2000 : T-SQL

Stored procedure performance improvement - Hi All I have DB with 4 million records and a stored procedure which clears all the data from bunch of...

Date Range in my SQL report - I am new to creating reports and I have a few that I need to have a date range. Example: I...

SQL Server 2008 : SQL Server 2008 - General

Requeired Date format : 13 Sep, 2012 - Hi, am using below statement in a select statement, " e.expiration_date" is a date fromat column, i want the date should be...

Effect of adding update statistics as part of the stored procedure - Hi, What are the negative effects of running update statistics on few tables as part of stored procedure that has application...

Updateable Function Based SQL Index - Hi, How do I create an updateable index from these index definitions: CREATE INDEX LibSort ON dbo.Page ( REPLICATE('0', 5-len(dbo.Page.system_id)) + ltrim(dbo.Page.system_id)+'~'+ REPLICATE('0', 40-len(dbo.Page.Page_Type)) + ltrim(dbo.Page.Page_Type)+'~'+ REPLICATE('0',...

The last row in the sample data is incomplete. The column or row delimiter is missing or the text is qualified incorrectly. - I have a requirement of loading 2 CSV files from shared location into a SQL table. All 2 CSV files are...

Displaying Estimated Execution Plan for a Stored Procedure - [font="Tahoma"] Hi Friends, I am in the process of fine tuning a stored procedure and hence I am trying to view the...

linked database opinion? - What is your opinion of linked databases?  I've used both linked servers and linked databases in the past.  I seem...

ILM 2007 to FIM 2010 database migration - I have a 10gb ILM 2007 database, (6gb data, 4gb log) in production. The developers are telling me when then...

Update Column from Another Column - Plz check attachment - Hello Experts, I have a table with a column called [b][i]Z_Status[/i][/b]: the values of this column are DUP and Survivor, which...

UniqueIdentifier as a Primary Key - Is it ever considered a best practice to use a UniqueIdentifier(GUID) as a primary key? Is there ever a case where...

Understandig row length calculation for sparse columns - Hello, I hope someone can explain the internals for row length. We have a 3rd party vendor table with following definition: 7...

Using ROW_COUNT - Hi Below is the code for my stored procedure Alter proc [dbo].[InvoiceDisplayTest] (@InvoiceNumber nvarchar(50),@InvoiceDate datetime ,@InvoiceDate1 datetime, @AccountNumber nvarchar(50),@TradingPartnerID nvarchar(50),@Page nvarchar(50),@rownum...

SQL to create Timeslot Table between StartTime and Endtime of a day - Hi, Need help in dynamically creating a Timeslot table based on office hour StartTime and EndTime((like 2.30pm-4.30pm, timeslots hv to be...

Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed) - Hi All, (This is just for fun, so you may choose to ignore this topic.) Today I read a puzzle somewhere which...

SQL Replication - Can this be accomplished through Replication? - Hi I would like to know what replication model I should use to achieve the following if even achievable. This is...

Concatenate Rows - I am trying concatenate an instruction field for multiple rows. Once I get the result set I will then insert...

error handling - Hello, Can some one help me with a way that I can stop scripts from running if one of the scripts...

Where To Put the MCTS Logo on Resume? - Hi all; I just passed the MCTS exam 70-432 with 833 marks, I just want to Know that may I use...

SQL Server 2008 : T-SQL (SS2K8)

Bulk Insert with delimiter þ failing - Hi The bulk insert failing for the fieldterminator 'p' and i am getting the following error message. The statement working...

CONCATINATE 3 COLUMNS (int) INTO A WORKABLE DATE FORMAT - Hi I have been given a new system to work on and the dates are held in three seperate columns...

Data Comparison - I have a table that contains permissions based on a group id. How would I go about building a script...

Need Help In Query . - Could you please help me find records described as per below scenario. Campus code,address,CampusID(PK) Data:- DEL,ABC,1 CHN,DDJ,2 MUM,PQR,3 BNG,WRT,4 PUN,BNM,5 Enroll StudId,CampusID,EnrollDate,LstAttendDate,Enrolid(PK) 1,2,15-06-2012,NULL,1001 1,5,31-05-2011,31-05-201,1002 ChangeStatus StudId,Enrolid,NewStatId,PrevStatId,ChangeID(PK) 1,1001,1,0,5001 1,1001,2,1,5002 1

NOT IN query very expensive, 100% CPU - Hello all. Hopefully someone will be able to help me out with this query. The below query takes 42 minutes, returns...

ALTER COLUMN fails when changing datatype of PRIMARY KEY - I am currently working on a transaction table that has an IDENTITY column of type INT, which also has a...

T-SQL Help Needed - CREATE TABLE Issue ( id int not null identity(1,1) primary key, name varchar(10) ) CREATE TABLE Jrn ( id int not null identity(1,1) Primary Key, issue_id int, created_dt datetime ) ALTER...

Order by with case - Hi All I want to sort some table data using column index The following query works: [code="sql"] declare @index varchar(10) = 4 select CityInternalID, CityDefaultName, CityCountryID...

rCTE vs LIKE for Hierarchy - Hi people, I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID,...

SQL Server 2008 : SQL Server Newbies

Compile SQL Reference Table query into VS 2010 - I am completely new to SQL server i know a few of the basics. I have a VS2010 asp.net c#...

Bulk insert of large files (more then 450MB) - Hi everyone I have to populate a table with a pretty large text file and I'm happily using the very good...

How do I fix Damaged allocation pages - I get this result from dbcc checktable DBCC results for 'RELATED_PARTY'. Msg 8946, Level 16, State 3, Line 1 Table error: Allocation page...

Logshipping is not working. - Hi, We have a log-shipping set up on the production server for reporting purpose. Unknowingly, I have set up a backup strategy...

Trying to get data into one view - Hello, I have already posted this before, but gave too less info. I am trying to get a total of unique usernames...

Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access...

Cubes without A Data Warehouse? - Greetz! I'm new to the BI space and have been asked to look into using cubes without building a datawarehouse or...

SQL Server 2008 : Security (SS2K8)

Standard Edition Auditing - Hello All, We all know that in SQL Server standard edition we cannot do any auditing on Server and DB...

SQL Server 2008 : SQL Server 2008 High Availability

Pros and cons of having multiple instances on a Active Passive and Active\Active cluster - Hi guys, I have a sql 2008 r2 enterprise box which is running on windows 2008 R2 enterprise and the business...

Tlog backup on princple server? - Hi, one of the production server TLog backup taken every 30 minutes. the same server going to implement HA for database...

SQL Server 2008 : SQL Server 2008 Administration

Deadlock issue due to locking on resources - In our production environment, we are seeing frequest deadlocks due to locks on the resources. I have attached DeadlockGraphs captured...

New Windows 2008 R2 server with SQL 2008 R2 - Hard Drive Setup - Okay I my be a little out of my league here, but I am learning. Please bear with me. Sorry...

SQL Query Confusion - Hi All I'm really confused about something, any help would be great If I run a query and according to the execution...

page writes/sec high on mirrored server - Hello I have a mirrored server in which the SQLServer:Buffer Manager: 'page writes/sec' is high, it averages around 280 with the...

Some linked server connections stop working - I have a 2008R2 Standard instance (2008R2 SP1 10.50.2500) which has developed strange problems with linked server connections. All of...

Finding port numbers - Hi, can any one tell me the script for finding the port number of sqlserver

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

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

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

Query to get list of susbscriptions with params and values - I've be searching and trying for hours to get a query that gives me all current subscriptions from and SSRS...

Reporting Services : Reporting Services 2005 Administration

Configuring report manger Url on Local server asks Username and password - Hi friends, I am trying to Configuring report manger URL on Local server asks Username and password .I went to...

Data Warehousing : Integration Services

The last row in the sample data is incomplete. The column or row delimiter is missing or the text is qualified incorrectly. - I have a requirement of loading 2 CSV files from shared location into a SQL table. All 2 CSV files are...

Need Help --Data flow script transformation component / T-SQL / CLR Function ???? - Hi, I need a help in SSIS to choose Data flow script transformation component / T-SQL / CLR Function to solve below problem. I...

Consolidate or Decompose - I have an opinion question. I have a sort of round-robin sets of pulls and updates. I'm wondering viewpoints on...

First Time Migration of a File System 2008 SSIS package from Dev to Prod - Hi All, I developed SSIS packages in our development server. I used my windows id in the development. The packages are...

Integration Services contain Analysis Services Processing Task. Fails but works in Analysis Services - The dimensions have processed Successfully prior to the updating of the SEN Cubes The attribute key cannot be found when processing:...

Data Warehousing : Analysis Services

The CHILDREN function expects a member expression - Hi, using SSAS 2005 I have a fully functioning cube with many measure groups and dimensions all bar one of...

Microsoft Access : Microsoft Access

SQL Server nvarchar(max) fields are truncated - My Access 2003 (or 2007) has linked tables to SQL Server 2008 (I'm using SQL Native Client 10). Forms, reports...

Article Discussions by Author : Discuss Content Posted by Tiago Silva

Trigger defined for Insert, Update - Is there an "easy/quick" way to determine inside the trigger itself whether it is being fired because of an Insert...

Third Party Products : Red Gate Software

SQL Compare 10 - Working with SQL Compare 10 to build upgrade scripts we have found that SQL Compare will map columns of different...