If there is ever a need to run a SQL Server job on an adhoc basis outside of its normal schedule, don’t change its original schedule. Rather, create a new one-time only schedule for that job and leave it like that even after the the execution.
If you modify the original schedule then you’d have to remember to revert it back. This can be easily missed out and then the job would be running at the modified schedule after that! Oh, so you say you have a water-tight reminder mechanism and you’ll never forget to revert it?! Please read the next line.
If there was a requirement to run the job out-of-schedule once, then it could happen again too. The one-time schedule would be handy at that time and can be reused.
I’ve created this wiki page at TechNet today for SSIS design patterns. Please use and share the SSIS related patterns over there. In case you have any difficulty in adding a pattern to that page then please let me know. I’ll gladly update the wiki for you.
How to store text in multiple languages in SQL Server.
If you have to store and retrieve characters of any other language besides English in SQL Server, you must do the following –
Use a Unicode compatible data type for the table column. NVACHAR, NCHAR, NTEXT are the datatypes in SQL Server that can be used for storing non-English characters.
Precede the Unicode data values with an N (capital letter) to let the SQL Server know that the following data is from Unicode character set. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
The N should be used even in the WHERE clause.
REFERENCE: Microsoft Support KB 239530
You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server http://support.microsoft.com/kb/239530
If the correct data-type is not used or the data is not preceded with an N, SQL Server will save the data to the table as ‘?’ or another garbled character.
The following scripts demonstrate saving and retrieving multi-lingual data to and from SQL Server. I have used Google Translate to get the characters of other languages. I left out far-east languages like Japanese and Chinese from the following example on purpose because those languages have a few other considerations that I’ll save for another blog post.
DROP TABLE dbo.unicodeData;
GO
CREATE TABLE dbo.unicodeData
( languageUsed VARCHAR(50)
, unicodeData NVARCHAR(200)
, nonUnicodeData VARCHAR(200) -- same data in a normal VARCHAR column for comparison
, comments VARCHAR(100)
);
GO
INSERT INTO dbo.unicodeData
( languageUsed
, unicodeData
, nonUnicodeData
, comments)
VALUES
('English'
, N'This is an example'
, N'This is an example'
, NULL)
,('Hindi'
, N'यह एक उदाहरण है.'
, N'यह एक उदाहरण है.'
, 'Using the preceding N in both strings but VARCHAR is still a ?')
,('Hindi'
, 'यह एक उदाहरण है.'
, 'यह एक उदाहरण है.'
, 'Not using the preceding N in both strings so both are a ?')
,('Kannada'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, NULL)
,('Arabic'
, N'هذا مثال على ذلك.'
, N'هذا مثال على ذلك.'
, NULL)
,('Czech'
, N'To je příklad.'
, N'To je příklad.'
, NULL);
GO
SELECT *
FROM dbo.unicodeData;
GO
-- Example of using N' in the WHERE clause
SELECT *
FROM dbo.unicodeData
WHERE unicodeData like N'%एक%';
Would it be helpful if the status bar of SSMS query windows for PROD environment were red, QA were green and DEV were blue? Yes. The query windows will be distinguished and reduce the chances of oops moments. If you frequently open simultaneous query windows for multiple environments/servers in SSMS, then you must learn about this SSMS feature. Don’t miss reading a word of caution at the end!
The following is the default status bar for a query window in SSMS.
The status bar can be made Green (or any other color) like the example below.
All you need to do is to expand the Options on the connection dialog …
… and pick a color of your liking as shown below.
These color choices will persist even when you close and reopen the SSMS.
Although, setting up a different color for all individual servers is possible but having too many colors could be confusing! You might find it useful to set one specific color per environment. For example, Red for PROD servers, Green for QA servers and Yellow for TEST servers.
How to Reset it Back to Default Color:
Open the Connect to Database Engine dialog box and remove the check for the Use Custom Color checkbox. Click on Connect. All new query windows for the server you are trying to connect will revert back to the default color. Any open query windows for that server or others will not be affected. I’m not aware of any option to reset this for all connections at once.
A Word of Caution:
A new color selection is applied to the query window immediately but the effect of removing a color is visible only with new query windows, not the ones currently open. Whenever you change the connection of any open query window, always verify that the expected color is showing on the status bar.
I maintain a playlist of YouTube videos that I like about SQL Server. I’m sharing that playlist on a tab of its own now. Of course, I’ll be adding more videos to the playlist. The video collection oriented more towards performance tuning and general administration.
I work primarily on SQL Server these days but I had originally started learning SQL on Oracle. I find the sample tables EMP, DEPT, BONUS, SALGRADE and DUMMY provided with Oracle to be useful for beginners to practice SQL commands. Oracle provides the script by the name of DEMOBLD.SQL to create these tables which can be found in the <OracleHome>sqlplusdemo folder of your Oracle installation or at this URL. Some more useful scripts for Oracle users can be found at this OraFAQ page.
I thought that few folks might like to have those tables on SQL Server so I made some changes to the script so that it can run on SQL Server too. It might prove useful if you don’t have access to an Oracle installation but want want to practice SQL commands with these tables. Of course, MS SQL Server does not support all the SQL syntax as supported by Oracle, but the tables can be handy in some cases.
The SQL Server compatible script is at the TechNet link below. The basic difference from the original script is in the datatypes and the datetime convert function. The script will drop any tables with the same names in the current database so be sure that existing tables do not have any critical data.
I had to work on providing an effort estimate to convert multiple DTS packages into corresponding SSIS packages. An automated conversion tool was not available to us so the SSIS packages were to be built from scratch based on the existing DTS packages.
I took this opportunity to catalog all our DTS packages and created an Excel workbook to aid in the estimation. The formulas in the workbook calculate the effort in person-days for the rewrite of the current DTS logic into a SSIS package.
I approached this requirement as follows –
Open each DTS package and used the Excel workbook to make an inventory of the type and number of all the tasks and connections in the package. Obviously, this is a repetitive and the most time consuming activity in the whole process.
Decide on an estimated number of minutes required to recreate each type of task/connection in SSIS. This time could vary depending on the developer’s experience and skill.
Lastly, put in standard time additions for activities like analysis, error handling, logging and documentation.
You can try that estimation workbook by downloading it at the link give below and tweaking the values to suit your needs. While you are there, don’t forget to rate it! 🙂 Please let me know in the comments section of this post if there are any suggestions or questions.
