Happy Thanksgiving to all the US members of the community and I hope those of you in the rest of the world have a quiet, easy day at work.
This is traditionally a day where the US remembers the origins of the country, often with big meals shared with family and quite a few sporting events on television. However it's not all fun and games. Typically this is the day and weekend when I've had to polish my electrical skills as I hang lights on the house and around the yard to get ready for Christmas. I don't go crazy, and there's no computer controls for my display, but I have found myself up on a ladder on quite a few Thanksgiving weekends.
I'm not sure if I'll be doing that this year. My kids are getting older, and we've spent the last 6 or 7 years skiing in the mountains. We tentatively have plans to return Thursday this year and have family over, so it's possible I'll be calculating amperage loads on circuits as we try to decorate for the holiday season.
I've included a blooper reel with my mistakes from the last month or so for you to enjoy. Hopefully I won't make any mistakes this weekend if I'm 10 feet in the air on a ladder.
Note: Most of these are the hands and face bloopers from the last couple months.
A classic article by Gerg Larsen explaining how and how not to use a function within your T-SQL statements. Where you place your function within your T-SQL statements determines how your query will be processed and can severely impact the query execution plan and the performance of your query. More »
SQL Saturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Dec 13 2013 at Università Politecnica delle Marche - Ancona, Italy. This SQL Saturday will feature 3 tracks focused on BI, DBA, and Development. More »
A demonstration of Power BI for Office 365, showing you how all the various tools and technologies work together: Power... More »
Question of the Day
Today's Question (by Steve Jones):
It's Thanksgiving in the US. Assuming we have a table of colors associated with holidays, what should be returned by this query?
SELECT sales = color + ' ' + datename( dw, DATEADD( d, 1, GETDATE()))
WHERE holiday = 'Thanksgiving'
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
7 points in this category: humor.
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
Securing SQL Server
Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.
Sorry about the dated referrence, but this information has not changed. I have tested this in versions: 2000, 2005, 2008 R2, & 2012. When attempting to created a transaction log file smaller then 512 KB, the following error is displayed:
Msg 5174, Level 16, State 1, Line 8
Each file size must be greater than or equal to 512 KB.
It's been rare in my career that I've needed to find a string of text within any and all of the many possible tables of a database. For the few occasions when I did, I would just go to Google, find something that worked, run it, and be done.
More recently my need for this type of script has increased, and I began to notice that most of the code out there was either slow (even when doing an extremely small search), didn't return all of the fields I wanted to see in the output, didn't search all of the character data types I was interested in, gave limited control / filter options (if any), and / or needed to be run from within the context of the database that was being searched (AKA: not feasible to make it a stored procedure unless you are only ever going to search within a specific database or deploy the procedure to all existing and future databases).
Hence my inspiration for creating my own stored procedure which allows one to search a multitude of data types in any specified database and includes some optional input parameter options / filters.
NOTE: Depending on your environment and needs (number of tables / columns / search criteria, etc.) these types of queries have the possibility to run long and have a performance impact on the system. Always use common sense when running queries such as these and limit your search via the input parameter options rather than casting as wide a net as possible to see what you will get back as results (especially in a production or shared environment).
The procedure accepts the following input parameters:
@v_Search_String: Text string you wish to search for.
@v_Database_Name: Database whose table(s) and / or view(s) you wish to search against.
@v_Object_Types: Object types to search against (U for tables, V for views, default is both).
@v_Data_Types: Data types to search against (default includes CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, and XML).
@v_Table_Max_Rows: Limits the search to objects of a maximum row count, avoiding scans against large datasets (default is no limit, though using this option will remove NTEXT, TEXT, XML and views from the search).
@v_Column_Max_Length: Limits the search to columns of a maximum defined data length; handy for when you only want to search against short description fields (default is no limit).
The procedure will output the following values:
object_type: Indicates if the object containing the search result is a table or view.
data_type: Indicates the data type of the column containing the search result.
data_length: Indicates the column length, as defined in the database, containing the search result.
database_name: Indicates the database name the search was performed against.
schema_name: Indicates the schema name the search result was found in.
object_name: Indicates the object name the search result was found in.
column_name: Indicates the column name the search result was found in.
column_data: Full contents of the column containing the search criteria match.
column_data_xml: When the contents of the column_data field are XML then this column will return the same data but converted to the XML data type.
occurrences: Count of the total number of occurrences of the full content of the column_data field.
To execute the stored procedure you must at least populate the @v_Search_String and @v_Database_Name input parameters:
Nulls last in order by
- I have the following select statement in a stored proc
SELECT DISTINCT P.PlantNumber
and an order by...
I have 500 tables in a database. i want to take the backup and restore the 3 tables along...
Duplicate row data on condition...
I have a table with following structure:
SaleId INT, SaleRowId, TaxCode INT, TaxValue INT, Price INT, ExtraTaxCode INT, ExtraTaxValue INT
Is the following possible?
Two stored procedures, sp1 and sp2
1-sp1 to output the result of a select query into a table...
sp_makewebtask is not available in sql 2008
- Any Alternate code for below query, because sp_makewebtask is not available in sql 2008
EXECUTE sp_makewebtask @outputfile = 'C:\WEB\MULTIPLE.HTM',
Looking for a Sr. SQL DBA out of SF
- Contact: Amber.Richard@staffmark.com
Phone: (925) 969-4433
Searching for a Senior level SQL DBA. This is a F/T or contract-to-hire position. SQL...
DBA Resume for US market
- Guys, I created resume but I am not sure will it be competitive on US job market? May be you...
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.