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

Featured Script

SQLCMD to export data in txt file

The Voice of the DBA

Byte Me: Development Haze

Bob Lang from SQLServerCentral.com

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

How to fix SQL Server disk I/O bottlenecks (without a hammer)

In this new article, Simple-Talk editor Tony Davis explains step-by-step how to find and fix the root causes of disk I/O bottlenecks, including gathering data, avoiding knee-jerk fixes, and how monitoring tools can help. Read now.

Not enough hours in your day?

The SQL Toolbelt lets you reduce the time spent on SQL Server development and administration. Cambridge University developer David Spaxman, for example, says: “I’m saving 10-12 hours a week using the SQL Toolbelt.” Learn how you can double your productivity, speed up deployments and protect your data. Download a free trial.

SQL Clone – Create database copies fast!

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test, and diagnose issues faster. Download the technical preview.

Featured Contents

Stairway to U-SQL Level 10: Table-Valued Functions and UDTs

Mike McQuillan from SQLServerCentral.com

Table-Valued Functions and User-Defined Types can help you write parameterised U-SQL views in an elegant manner. They also aid code reuse. More »

Free eBook: Performance Tuning with SQL Server Dynamic Management Views

Additional Articles from Redgate

Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA's troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions. More »

Migrating data to Azure SQL Data Warehouse in practice

Additional Articles from SQLCAT

Rangarajan Srirangam from the Azure Customer Advisory Team shares some tips and techniques to help achieve an efficient data migration to Azure SQL Data Warehouse. More »

From the SQLServerCentral Blogs - Power BI Desktop hosted on premises with SSRS 2016

Koen Verbeeck from SQLServerCentral Blogs

At Microsoft Ignite, it’s announced that very soon, we will be able to host Power BI Desktop files on premises... More »

Question of the Day

Today's Question (by Steve Jones):

I examine sys.indexes for my database and find a few tables that have a row in this table with an index_id = 0. What does this mean?

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

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

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

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

With Microsoft SQL Server 2016, a variety of new features and enhancements to the data platform deliver breakthrough performance, advanced security, and richer, integrated reporting and analytics capabilities. In this ebook, we introduce new security features: Always Encrypted, Row-Level Security, and dynamic data masking; discuss enhancements that enable you to better manage performance and storage: TemDB configuration, query store, and Stretch Database Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

What happens if you run this code?

 CREATE TABLE MyNewTable ( id INT , firstname VARCHAR(200) , lastname VARCHAR(200) , )

Answer: The table is created and no error returned.

Explanation:

This is a strange one, but it seems to be something the SQL Server parser accepts in most, if not all, version of SQL Server. The table is created and no error or warning is returned.

Ref: CREATE TABLE - https://msdn.microsoft.com/en-us/library/ms174979.aspx, however, the template for the statement does not seem to show a trailing comma is acceptable.

You'll have to test this.

» Discuss this question and answer on the forums

Featured Script

SQLCMD to export data in txt file

Sheraz Mirza from SQLServerCentral.com

By using SQLCMD we can export data from SQL Database to a text file in two ways

By using SQLCMD on Command Prompt
By selecting SQLCMD Mode in SSMS

By using Command Prompt is as simple as running the command which is mentioned in code section, but please don't forget to replace server name, instance name and database name as per your environment.

By using SQLCMD Mode in SSMS

First you need to select SQLCMD Mode in SSMS by selecting QUERY Menu

then run the code below

Here you can see first 3 rows are highlighted which shows SQLCMD command, actually " : " tells SSMS that next is SQLCMD command

In first line we are checking if target file already exist then delete it first because in 3rd line of code we are creating target text file.

Second line of code is connecting with SQLServer Instance

Third line is creating Target file

In both ways , one text file will be created on c:drive with column heading and footer detail messages.

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 2016 : SQL Server 2016 - Administration

Sql Server 2016 Installation Issue - Hi All, when i trying to install sql server 2016.iso file i am getting below error . Please give me solution for...

SQL Server 2014 : Administration - SQL Server 2014

Kerberos issues and Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' - We have a linked server set up. It will connect with no problem, but it seems that we periodically are...

SQL Server 2014 : Development - SQL Server 2014

CONVERT DATETIME to TIME and remove seconds and milliseconds - Hello Folks, How can I convert StartTime2 so that it equals StartTime1 DECLARE @StartTime1 TIME SET @StartTime1 = '17:41:00.0000000' SELECT @StartTime1 DECLARE @StartTime2 DATETIME SET @StartTime2 = '2016-09-22...

SQL Server 2012 : SQL 2012 - General

Performance issue when testing the value of ROW_NUMBER in a WHERE clause - I have a (moderately complex) statement that performs pretty well. Adding a ROW_NUMBER() column to it does not significantly change...

Hacking the Report Server Database - Hi, Not sure if anyone has any advice here. I'm doing data driven subscriptions without enterprise edition and I've hacked together...

SQL Server Setup - I have installed yesterday SQL server 2012 SP1 with update enabled for SP2 on a 4 node cluster. Now i want...

Run a script file against Multiple databases - I have a script file in C:\Script.sql that I need to run against multiple databases. I can run on one database...

SQL Server 2012 : SQL Server 2012 - T-SQL

t-sql 2012 update primary key value into column of the second table - In a t-sql 2012, I want to place the new lockid values obtain from the first insert statement (for the...

Extract a uniform pattern from a file path - Hi All I have a strange problem here which I can't quite get to the bottom of. I have a file path...

How to Upgrade T-SQL Skills - Hi All, I'm a production DBA and have been for a good while. I can query, join filters but how do...

SQL Server 2008 : SQL Server 2008 - General

How to Make a SQL Query Run Faster - Hello: I know that this is a broad question. But, below is my SQL query code. I have tried running it, at...

sql query to display a trailer record at the end in txt file (using batch file) - hello, I want to display a trailer record at the end of records : the spec is as follows Trailer Record: Field Name Length Begin ...

sp_msforeachdb DBCC SHRINKFILE Query Problems - I was wondering if anyone could help me with the following (im trying to automate a way of shrinking all...

SQL Server 2008 : Working with Oracle

Anyway to connect to Oracle on the fly without tnsnames entry? - As the title indicates, I'm curious if anyone knows of a way to connect to an Oracle database from SQL...

SQL Server 2008 : SQL Server 2008 Administration

SQL Server listening on multiple ports - Here is a question that has been posited to me. Can an instance of SQL Server listen for traffic on...

Data Warehousing : Integration Services

How to force column explicitly to string type before we export to excel - I have a package which laods data to excel ,but my manger wants to force column explicitly to string type...

Data Warehousing : Strategies and Ideas

Star Schema Design - Multiple Sources - Hi, I am planning the design of a star schema for a data warehouse. The data will be loaded from 2...

Data Warehousing : Analysis Services

MDX Calculated Measures not affected by slicers - Hi! I'm new with this howl MDX thing and cant seem to figure out how to make the following work. I...

SQL Server 2005 : Business Intelligence

SSIS slow in Job Agent - Hi I have various packages which checks if a file is in a directory and if found kicks off the rest...

SQL Server 7,2000 : Administration

SQL 2000 cant not login using servername\Instancename - Hello, After sql server restart I've noticed that I [b]can[/b] login remotly only using: instancename instancename,port IP,port But [b]can not [/b]login using only: Instancename...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.