SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

The Voice of the DBA

SQL Security Crossword

Enjoy our latest crossword puzzle and see if you can solve all the clues. These are all related to security in SQL Server. The key will be posted in the comments tomorrow and a word document version is attached below.

SQL Server Security

Across

2. Second highest principal in SQL Server.

4. Turned off when a database is attached

11. Not NTLM

12. Not even the sysadmin's can see this data.

13. aXXX@XXXX.com

16. Protocol used when connecting from the server SQL is on.

17. Pretending to be someone else

19. One SQL instance talking to another.

20. Security trump

22. Contains 0 or more windows users.

24. Uses the private key to encrypt and decrypt data.

25. Used to contain a windows id and password.

Down

1. Add a permission

3. Permission required to create stored procedures.

5. Required for all encryption in SQL Server

6. One way encryption.

7. This database is isolated from other databases.

8. Used to add a John Hancock to a stored procedure.

9. Workstation-Server-Server

10. Alias for a credential

14. Uses both a public and private key.

15. Bobby

18. Add random data to the string before encrypting it.

21. Principal that contains other principals.

23. Check who did what

Kenneth Fisher from SQLServerCentral.com

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

ADVERTISEMENT
SQL Clone

SQL Clone: Now supporting databases up to 64TB

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. Try it free

SQL Compare

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Featured Contents

 

Using SQL Server Dynamic Online Catalog to Explore Keys and Relationships: Stairway to Exploring Database Metadata Level 4

Phil Factor from SQLServerCentral.com

This level introduces keys, constraints, and relationships. More »


 

Free eBook: Inside the SQL Server Query Optimizer

Press Release from SQLServerCentral.com

This free eBook from Redgate Software 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. More »


 

Five Mistakes Performance Tuners Make

Additional Articles from Brent Ozar Unlimited Blog

Erik Darling explains why your queries aren't scaling in production. More »


 

From the SQLServerCentral Blogs - Changing the version of an SSIS package.

Kenneth Fisher from SQLServerCentral Blogs

Of all of the annoying parts of SSIS, the major version sensitivity has to be the most annoying. Let’s say... More »


 

From the SQLServerCentral Blogs - Runtime Metrics In Execution Plans

Grant Fritchey from SQLServerCentral Blogs

Capturing query execution metrics is much easier now that you can see the runtime metrics in execution plans when you’re... More »

Question of the Day

Today's Question (by Steve Jones):

I have this R code, setting an American football position list:

football <- c("CB", "OLB", "LT", "WR", "SS", "DE", "LG", "HB", "CB", "MLB", "C", "QB", "FS", "DT", "RG", "FB" ,"CB", "SLB", "RT", " ", "CB", "DE", "TE", "WR")
dim(football) <- c(4, 6)

When I print this, I get these results:

     [,1]  [,2] [,3]  [,4] [,5]  [,6]
[1,] "CB"  "SS" "CB"  "FS" "CB"  "CB"
[2,] "OLB" "DE" "MLB" "DT" "SLB" "DE"
[3,] "LT"  "LG" "C"   "RG" "RT"  "TE"
[4,] "WR"  "HB" "QB"  "FB" " "   "WR"
 

The defense is the first two rows of the matrix. If I just want to show the defense, what do I type?

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: R Language.

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

ADVERTISEMENT

The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win

The company's new IT initiative, code named Phoenix Project, is critical to the future of Parts Unlimited, but the project is massively over budget and very late. The CEO wants Bill to report directly to him and fix the mess in ninety days or else Bill's entire department will be outsourced.  Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Carlo Romagnano):

From BOL "Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE". In fact, the first batch returns error: Msg 208, Level 16, State 0, Line 4 Invalid object name '#t'.

What does the second batch return? (select 2)

-- first batch
drop table if exists #t
execute ('create table #t (i int, z int)')
select * from #t
GO
-- second batch
drop table if exists #t
create table #t (i int)
execute ('alter table #t add z int')
select * from #t

Answer:

  • The second batch completes successfully.
  • output column i,z

Explanation:

click here

The temporary table can be referenced by any nested batches or stored procedures.

Here an example of how to read any CSV into a temporary table and building it dynamically with the proper columns. Assume that the first row contains columns name.

IF OBJECT_ID('tempdb.dbo.#csv') IS NOT NULL
    DROP TABLE #csv

-- load of first row with fieldnames
CREATE TABLE #csv (txt VARCHAR(MAX))
BULK INSERT #csv
FROM 'C:\TEMP\DATA_20170808.CSV'
WITH(FIELDTERMINATOR = '\t',LASTROW = 1) -- adjust the FIELDTERMINATOR option

-- build the dynamic table
IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
CREATE TABLE #t(filler INT) -- base of the temp table

DECLARE @sql VARCHAR(MAX)
,@tbl VARCHAR(MAX) = 'ALTER TABLE #t ADD '
,@comma CHAR(1) =''
SELECT @sql = txt FROM #csv
SELECT @tbl += @comma
             + '['+ Item +'] VARCHAR(MAX)' --
      ,@comma = ','
FROM master.dbo.DelimitedSplit8K(@sql,CHAR(9)) -- google DelimitedSplit8K to see function
ORDER BY ItemNumber

PRINT @tbl -- debug

EXECUTE(@tbl) -- alter table

ALTER TABLE #t DROP COLUMN filler
BULK INSERT #t
FROM 'C:\TEMP\DATA_20170808.CSV'
WITH(FIELDTERMINATOR = '\t',FIRSTROW = 2) -- adjust the FIELDTERMINATOR option

SELECT * FROM #t

» Discuss this question and answer on the forums

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

Replication Error - I am getting the following error when adding a new subscription. .OLE DB provider "SQLNCLI11" for linked server "REPLLINK_RIPNCDB1-268974458_PNC60785638_PUBPNC_COM-1026461326_PNC60785638" returned message...

copying a database from one server instance to another server instance - I am trying to copy a database from one server instance to another server instance. Both instances are viewable in...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

remove spaces in strings - hi , i hvave a value something like this column scrapetype "WebCrawler.Crawlable.EURRUSSIA.C_Darty ". My join is on this column and it does not...

Zero to Many relationship - One to Many is easy with a Foreign Key. How can we create a Zero to Many relationship in SQL Server?...


SQL Server 2014 : Administration - SQL Server 2014

Rebuilt SQL Server 14 system databases, not functioning, pointing at non-existent files - I rebuilt system databases for a SQL Server 2014 installation. The server will still not restart, it cannot find MSDB and...

Object Level Recovery Tool - I'm looking for an Object Level Recovery Tool. The only references I've found by searching online is for Red Gate's...


SQL Server 2014 : Development - SQL Server 2014

Update data with same values - Hello, I have a table with the header and child record in the same table. I need to be able to...

Determine Monday of next month and Monday of next week - I am trying to determine the first Mondayof the next Month when the first Monday of this month has passed....

Hourly Sum of Data - Hi, Newbie in SQL. I have 2 tables session & record. Session table stored job start & end time data in 2 session,...


SQL Server 2012 : SQL 2012 - General

Remote query gets killed after ten minutes - Hello We have a server with sql server 2012 SP3 CU5, when we run a query directly at the server it...

To automatically change the value of a column if a particular date time is reached - Hi, I am using SQL Server 2012 Express Edition. I need to automatically change the value of status field to "2"  if...


SQL Server 2012 : SQL Server 2012 - T-SQL

Column headers from a date range and populating a matrix - Hello everyone… I have an interesting problem and, while I can think of a couple of horrible solutions, I’d like the...


SQL Server vNext : SQL Server 14 - Administration

Create a database from backup when the backup transaction log is damage. - Good day everyone. I would like to know if it possible to restore a dump file to create a database even...


SQL Server 2008 : SQL Server 2008 - General

Removing records from SELECT - Hi everyone I have a table in SQL called dbo.Orders The table has the following columns: Customer, OrderId, OrderDate, Region, Department, RegistrationDate When a new order has been made...

Index Seek not possible if you want every row of table, correct? - I have a table, it has a clustered index on customer_ID. I need to return every row of this table via...

Update Statistics.. - Hi Experts, Trying to figure out if it might help in solving any performance issues by introducing an additional manual update...


SQL Server 2008 : T-SQL (SS2K8)

Slow queries during busy times - We have a SQL 2008 Enterprise Edition server with 32 GB of memory and 16 CPU. During quiet times of the...


Data Warehousing : Integration Services

Data Flow Task Error: The version of ODBC Source is not compatible with this version of the DataFlow - So, that is the error I am getting when I attempt to execute my extremely simple SSIS package.  How simple?...


SQLServerCentral.com : Articles Requested

The OUTPUT clause - Looking for a couple articles here. One on basics of OUTPUT for inserts and deletes. One for updates and combining...

This email has been sent to {user_email}. 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.
This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com