tag:blogger.com,1999:blog-13426101066944757142024-03-14T23:32:30.598+08:00Manjuke's Blog:::::: Life Runs On Code ::::::Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.comBlogger79125tag:blogger.com,1999:blog-1342610106694475714.post-12409010846946539302022-05-04T00:42:00.000+08:002022-05-04T00:42:06.581+08:00Avoiding NULL or blank values when reading Excel containing mix data types using SSIS<p> Have you encountered the issue of getting null values or blank values when data is read using an SSIS package and exported to a text file?</p><p></p><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTo2oZk09dB3ZrxrkkBlLF7y-uTUUOm8CGC4bNjFTsCsRDotHLVY8LDbZ1qVS1u_igAqSufQ6w4-wY8Nz7H0UBDrZYRP29yj9h4idTFxZwMOc1Qtt9303AqT_oOyjJ4YHqC9-lgs_V145wZpk5i3Gpe_HPntMqLd6gKiDRUOhinSxgfbGYlU8iZBY4AA/s642/screen_01.png" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="642" data-original-width="549" height="451" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTo2oZk09dB3ZrxrkkBlLF7y-uTUUOm8CGC4bNjFTsCsRDotHLVY8LDbZ1qVS1u_igAqSufQ6w4-wY8Nz7H0UBDrZYRP29yj9h4idTFxZwMOc1Qtt9303AqT_oOyjJ4YHqC9-lgs_V145wZpk5i3Gpe_HPntMqLd6gKiDRUOhinSxgfbGYlU8iZBY4AA/w386-h451/screen_01.png" width="386" /></a></div><p></p><div><br /></div>As you can see that the emp_code from the 11th record onwards shows a null value.<div><br /></div><div>I am using the following excel file as the source:</div><div class="separator" style="clear: both; text-align: center;"><br /></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1ZvPeq219_rlTKKCcYQAsCs5sGAg5ZJQM_9W3VxhjMONHuGC9kBj5uiQXtclQvLDRF-ZdIzgH-8IaqSUdl7FheCJ5jwVxV9_kFdupH0ykXAsxdmYkil7E3CoHWkHl6c3pMVXiHMKmxgXbY2H95FR__dckIG_DOhi1z4ilPy0GVYWKhYh_lL8uElBQ6g/s799/screen_00.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="490" data-original-width="799" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1ZvPeq219_rlTKKCcYQAsCs5sGAg5ZJQM_9W3VxhjMONHuGC9kBj5uiQXtclQvLDRF-ZdIzgH-8IaqSUdl7FheCJ5jwVxV9_kFdupH0ykXAsxdmYkil7E3CoHWkHl6c3pMVXiHMKmxgXbY2H95FR__dckIG_DOhi1z4ilPy0GVYWKhYh_lL8uElBQ6g/w485-h297/screen_00.png" width="485" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>To illustrate this issue I have a data flow task which reads from an excel file and writes the details to a text file.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEgPnynPV_b1Gkqn057rOIwXZu3ytKv5pdVGyNihKda4GlwCBGO6mX3M8AAHwjOgJPO1MnoRlda9OQs7uDzKQXys0qxVxGe69xuA71pHG1Adt-jIgDNrKo0SQZIiPbsjXMx-h-yktO5xYyc3UJJ_cyagQXcb14ZxCpF1FmFlNo5lNm9qfVJ8IBDRTXSw/s318/screen_02.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="289" data-original-width="318" height="289" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEgPnynPV_b1Gkqn057rOIwXZu3ytKv5pdVGyNihKda4GlwCBGO6mX3M8AAHwjOgJPO1MnoRlda9OQs7uDzKQXys0qxVxGe69xuA71pHG1Adt-jIgDNrKo0SQZIiPbsjXMx-h-yktO5xYyc3UJJ_cyagQXcb14ZxCpF1FmFlNo5lNm9qfVJ8IBDRTXSw/s1600/screen_02.png" width="318" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>We are using an excel connection and a flat-file connection in order to connect the source and the destination using the default settings.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY9-0HjzROwBNGVBNKxrIDCWfrbZkFpecR7U8Fyh0EACUWyhv__G4rT0_1JtKzpK8yDEorgIosxCndmMQcVfIuu4EeJH5h6Qvj-ViEekuDjFIndgt-SM6iscuNv2Bj7LyfrWVOfs9jVaIA_4KzU9acgNAU6aqtmN50t1kXzGsb5KR45GmfgQkBoSebXQ/s439/screen_04.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="80" data-original-width="439" height="58" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY9-0HjzROwBNGVBNKxrIDCWfrbZkFpecR7U8Fyh0EACUWyhv__G4rT0_1JtKzpK8yDEorgIosxCndmMQcVfIuu4EeJH5h6Qvj-ViEekuDjFIndgt-SM6iscuNv2Bj7LyfrWVOfs9jVaIA_4KzU9acgNAU6aqtmN50t1kXzGsb5KR45GmfgQkBoSebXQ/s320/screen_04.png" width="320" /></a></div><br /><p><br /></p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCeiMb72Q9ujkOlTxhbaZUliY067IPwEY1YgqIvpOoLK1EcptQmAhZyYVqRTXk9An6ty2FGGmtsLqmkxWyFKakhK7YuA0ISzajzjOX37klbDXMubVO-mVWYH5zEBaL1oDjz570QcRpzrvwvW7yPhAFI0HIgPEJUxTR0WZPsrLO4qtlui14xP_EmgWA1w/s707/screen_03.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="444" data-original-width="707" height="283" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCeiMb72Q9ujkOlTxhbaZUliY067IPwEY1YgqIvpOoLK1EcptQmAhZyYVqRTXk9An6ty2FGGmtsLqmkxWyFKakhK7YuA0ISzajzjOX37klbDXMubVO-mVWYH5zEBaL1oDjz570QcRpzrvwvW7yPhAFI0HIgPEJUxTR0WZPsrLO4qtlui14xP_EmgWA1w/w450-h283/screen_03.png" width="450" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCBAwRXpG6zPGgggQTowqVM91n0AMfcjc861k9doJOQLrU-WKqB6siPvM5EoAIagJ7NwsvqVYWGbZ9m8x4WXkhnbNP1c_2WMpHIlL6Kb33iP6CVQsmsVmH4Li2Gn94pkz6C1FInk8rNNWfwqnTMPAqVC2xAK-Iot8aYjmPwwhRy3UbgSCtTNEvODV3dw/s745/screen_05.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="745" data-original-width="725" height="462" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCBAwRXpG6zPGgggQTowqVM91n0AMfcjc861k9doJOQLrU-WKqB6siPvM5EoAIagJ7NwsvqVYWGbZ9m8x4WXkhnbNP1c_2WMpHIlL6Kb33iP6CVQsmsVmH4Li2Gn94pkz6C1FInk8rNNWfwqnTMPAqVC2xAK-Iot8aYjmPwwhRy3UbgSCtTNEvODV3dw/w449-h462/screen_05.png" width="449" /></a></div><br /><br /><p></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>Once the task is executed the null columns be saved as blank values in the destination text files.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjavwiOz1Dsu8qNDJDmQ2fM1wYZ56assZEQjyA_iD2_lALcWL3poQibDfVWo0-XKEkxAYOBCRNPX2KZfEQPRPY0Ycg1C-apnQQpvaAxDiNWjfjGPqW2-VIP3Ka5Qdz_WdPTWOnKDX4EF9JKCr-e77h3WcrmGjZ9J6iimf4Dcp30KSrjGLBVxGaZyI3Pdg/s780/screen_06.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="696" data-original-width="780" height="406" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjavwiOz1Dsu8qNDJDmQ2fM1wYZ56assZEQjyA_iD2_lALcWL3poQibDfVWo0-XKEkxAYOBCRNPX2KZfEQPRPY0Ycg1C-apnQQpvaAxDiNWjfjGPqW2-VIP3Ka5Qdz_WdPTWOnKDX4EF9JKCr-e77h3WcrmGjZ9J6iimf4Dcp30KSrjGLBVxGaZyI3Pdg/w454-h406/screen_06.png" width="454" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><h4 style="text-align: left;">Root Cause (As per Microsoft explanation)</h4><p></p><blockquote>The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favour of the majority data type and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behaviour of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window</blockquote><p></p><p><br /></p><p>In order to overcome this issue, we need to do a few things.</p><p>Firstly we need to include the parameter "IMEX=1" in the connection string (or in the extended properties.)</p><p>Secondly, we need to consider switching the HDR=NO in the connection string (or set <b>FirstRowHasColumnNames</b> to <b>False</b>)</p><p>IMEX=1 Option: There are other types which can be used and each denotes a different option. In our case, we need to set it up as 1, which means during import all the data is to be considered as text type.</p><p>HDR=NO Option: This option will inform the OLEDB engine not to consider the first row as the header row. This is very important since excel will still determine the data type based on sampling (considering the first 8 rows), and it determines the data type based on the majority number of types. </p><div>The correct connection string should be similar to the one shown below:</div><div><br /></div><div>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<<path>>;Extended Properties="Excel 12.0 XML;<b>HDR=NO;IMEX=1</b>";</div><p></p><p>After doing those changes you will be able to see the data when you preview prior running your package.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8-0ddiGt92sfyyJtOb2UbGBQI22ZgD1Ev6hx3LoduAoXoiItboGGsxT0TdybDgxFz9SLWW6UXNASJfme6iGmpM-0KON03zoTc-7ULSkrl_zZ5p8Pv7z0_mm-5F6qItWxyI39pPN-3VbVNiVPwWVmORsSpVWUaLicuAGWc4E3yzOk_P-quYcRs9YQK0w/s677/screen_08.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="677" data-original-width="511" height="621" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8-0ddiGt92sfyyJtOb2UbGBQI22ZgD1Ev6hx3LoduAoXoiItboGGsxT0TdybDgxFz9SLWW6UXNASJfme6iGmpM-0KON03zoTc-7ULSkrl_zZ5p8Pv7z0_mm-5F6qItWxyI39pPN-3VbVNiVPwWVmORsSpVWUaLicuAGWc4E3yzOk_P-quYcRs9YQK0w/w470-h621/screen_08.png" width="470" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>But you will face a classic issue in which your data will contain the additional header row containing F1, F2... etc.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_2fAuTUuXMM4bZ_uzTIMcwrtJL97QMr98e3iP3UJaeFETGErvgMr3-dtjw-3yHZ9iEYQbFbXy7diZ0R4h3IqSuZIxp4SpGYt_PN0lVv1iHlTpAUzL4FoYdOWZWpEz94Jx9EinVKADEk-2pD6ETMsESSGg0DymOc0Y97_w3i-Cr0xsZACVAZsixo4XAQ/s832/screen_09.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="640" data-original-width="832" height="363" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_2fAuTUuXMM4bZ_uzTIMcwrtJL97QMr98e3iP3UJaeFETGErvgMr3-dtjw-3yHZ9iEYQbFbXy7diZ0R4h3IqSuZIxp4SpGYt_PN0lVv1iHlTpAUzL4FoYdOWZWpEz94Jx9EinVKADEk-2pD6ETMsESSGg0DymOc0Y97_w3i-Cr0xsZACVAZsixo4XAQ/w472-h363/screen_09.png" width="472" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>I haven't found a way to get rid of these excel column names. Hence I am using a conditional split to remove the header row (1 row in excel which contains the column names [id, emp_code etc...]).</p><p>Then I renamed all the columns to give a proper heading instead of showing F1, F2, etc...</p><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWfpLYQVUyXuj7vS9jpQ5aZUKAJkoYBvJBr4v4lmAlul0kWbglF6r10e-OhzoYcW-uMnv6UdceMfnuypk4tp6hUnQvjn39AUe3-CEumgCknYRleAZJcx8SCF5HMKR_d5762uZRnBGGAoDBOXPCMoUmz3tuRf8fenOqP7O3Fk3LobjS_3vjCPVaT7I3vQ/s1064/screen_10.png" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="149" data-original-width="1064" height="84" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWfpLYQVUyXuj7vS9jpQ5aZUKAJkoYBvJBr4v4lmAlul0kWbglF6r10e-OhzoYcW-uMnv6UdceMfnuypk4tp6hUnQvjn39AUe3-CEumgCknYRleAZJcx8SCF5HMKR_d5762uZRnBGGAoDBOXPCMoUmz3tuRf8fenOqP7O3Fk3LobjS_3vjCPVaT7I3vQ/w601-h84/screen_10.png" width="601" /></a></p><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhNBly3fnPGDpIqxmr_LBk4BQchraRcMA7zAFFO4i9jCALmWkM3uIMGfXqRT7VOAgbm2zONrjl7nrGk7vIv3iKUHeJd75F51awqeSl2kwu5YZ450NxHXLrC-xFLldy2r6COb-4qoPMxNl28I30omcx70kBwYEvf0hDM1grLcwRJMN4VgwxbfJeLtcvaQ/s745/screen_11.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="745" data-original-width="725" height="475" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhNBly3fnPGDpIqxmr_LBk4BQchraRcMA7zAFFO4i9jCALmWkM3uIMGfXqRT7VOAgbm2zONrjl7nrGk7vIv3iKUHeJd75F51awqeSl2kwu5YZ450NxHXLrC-xFLldy2r6COb-4qoPMxNl28I30omcx70kBwYEvf0hDM1grLcwRJMN4VgwxbfJeLtcvaQ/w462-h475/screen_11.png" width="462" /></a></div><br /><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div>After the above-mentioned changes, you will be able to get an output similar to that shown below.<div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL21otkY9XwHXtQ-iasHEmz0a8NOxgE85RMRIW9lFnqWtfXPS7DViTttgGftzv4bPqdGr1cRD1pJDuogmG3yP7x4YjY3hkM4Yz5s07t2lT0nUXqTM8DhFKxI8QHPtysyHKmKXyHvQY7wnUd8CXuQHCm6SpddKG0vMaIMd78HHja2TQT7tIDu9FlQDBsw/s779/screen_12.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="594" data-original-width="779" height="353" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL21otkY9XwHXtQ-iasHEmz0a8NOxgE85RMRIW9lFnqWtfXPS7DViTttgGftzv4bPqdGr1cRD1pJDuogmG3yP7x4YjY3hkM4Yz5s07t2lT0nUXqTM8DhFKxI8QHPtysyHKmKXyHvQY7wnUd8CXuQHCm6SpddKG0vMaIMd78HHja2TQT7tIDu9FlQDBsw/w463-h353/screen_12.png" width="463" /></a></div><br /><div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>Hope this will be helpful to you.</p></div></div><br />Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0Singapore1.352083 103.819836-26.958150836178845 68.663586 29.662316836178846 138.976086tag:blogger.com,1999:blog-1342610106694475714.post-15025013271046060002021-11-21T21:24:00.000+08:002021-11-21T21:24:36.797+08:00Missing SQL Server Configuration Manager (SSCM) from the Start Menu<p> Have you encountered an issue where the SQL Server Configuration Manager console is not available among the other SQL Server-related start menu applications?</p><p>This happened to me after upgrading from Windows 10 to Windows 11.</p><p>I have two instances installed on my PC (2016 & 2019). But SSCM is missing on both of them in the start menu.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyPhPzEc37gB2NO2Bw7zTMGtE0OLVAmPfjbC_nmXQU4n0f_PNpe9wfsAaRs_elLHGsIg6gGl2b8yh9-ZpOs3k51pX2g7PNEoV3T4Og6FZnxD0DXB5BDuWuPGFojaFku1N5j3JIcyU3fGWB/s702/01.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="702" data-original-width="697" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyPhPzEc37gB2NO2Bw7zTMGtE0OLVAmPfjbC_nmXQU4n0f_PNpe9wfsAaRs_elLHGsIg6gGl2b8yh9-ZpOs3k51pX2g7PNEoV3T4Og6FZnxD0DXB5BDuWuPGFojaFku1N5j3JIcyU3fGWB/s320/01.png" width="318" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>But it's available and you can access it either way shown below:</p><h4 style="text-align: left;">1. Directly from the directory.</h4><p>The relevant management consol service (.msc) file can be found in <b>C:\Windows\System32</b> folder. You can directly browse the file and open the application.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFPuzEb5Q_cOW2iJQmInU_byYuG8bT5H_dCIMeJGA_SQwRz44fSoc-Go55DvmXGxKqt3l7fJl-I5jHVWafWXO5_bEKTfKNaCcUpeYzlHYLpIEc-r1c0ZvTRy_-H28tIcsHvVnQT_1IeJhyphenhyphen/s609/02.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="236" data-original-width="609" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFPuzEb5Q_cOW2iJQmInU_byYuG8bT5H_dCIMeJGA_SQwRz44fSoc-Go55DvmXGxKqt3l7fJl-I5jHVWafWXO5_bEKTfKNaCcUpeYzlHYLpIEc-r1c0ZvTRy_-H28tIcsHvVnQT_1IeJhyphenhyphen/w495-h192/02.png" width="495" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><h4 style="text-align: left;">2. Accessing via Computer Management Console.</h4><p>SSCM will be available in the Computer Management Console, under "Services and Applications"</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW9IJ-B4XqY4FKLnmEyiye2l2RMMC6Uqd4ePYrd2w7YpHOxeBhp1EFL9UABAUYiRnUtQakoQ1zb4t4E9hE96_xwloQKkCVWl_Rvq7_FCX5IoFKZL_-opxTfO2DH6PBmsnP-MLfj2jS_hJc/s802/03.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="479" data-original-width="802" height="296" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW9IJ-B4XqY4FKLnmEyiye2l2RMMC6Uqd4ePYrd2w7YpHOxeBhp1EFL9UABAUYiRnUtQakoQ1zb4t4E9hE96_xwloQKkCVWl_Rvq7_FCX5IoFKZL_-opxTfO2DH6PBmsnP-MLfj2jS_hJc/w496-h296/03.png" width="496" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><h4 style="text-align: left;">3. Using Microsoft Management Console.</h4><p>Prior to using SSCM, you need to add it to MMC first. You can open Microsoft Management Console, by running "mmc" in the Run dialogue.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEighwXE6DRxO4tsXL3mW2ZxR36z12VIBQFxFhVQAaus4ljvbc7iUN2nZAZDMe31KIiWMEWTeR2JxmtSJDHWRX4jcQjmdVfPOTXLshp518FNWv-2K2Rdfz3pJ6jomKw2dLyxw2zC6pPJ5uoa/s456/04.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="272" data-original-width="456" height="191" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEighwXE6DRxO4tsXL3mW2ZxR36z12VIBQFxFhVQAaus4ljvbc7iUN2nZAZDMe31KIiWMEWTeR2JxmtSJDHWRX4jcQjmdVfPOTXLshp518FNWv-2K2Rdfz3pJ6jomKw2dLyxw2zC6pPJ5uoa/s320/04.png" width="320" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>Once the console is opened, add the SQL Server Configuration Manager using <b>Add/Remove Snap-In</b>.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqmsosEekyaJebHM2Hj8f5tBJ_esX-0kyPtxIr0hwzLSvGvpeu7VdFIpz78w9FL9FF8R5m_yrawsCgAsp5DFaSDSowEvNRJYc_InCAAO3TyL6QTSAsKeOoLevfLIExZ_LWD-SKJnys-Azn/s446/05.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="446" data-original-width="410" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqmsosEekyaJebHM2Hj8f5tBJ_esX-0kyPtxIr0hwzLSvGvpeu7VdFIpz78w9FL9FF8R5m_yrawsCgAsp5DFaSDSowEvNRJYc_InCAAO3TyL6QTSAsKeOoLevfLIExZ_LWD-SKJnys-Azn/w267-h291/05.png" width="267" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgz6q6EwH-skC_Di2jcvrFW8eCmVPCZj_g6xxCAwBEj9FxcD42FYdUGIoiOXd1tXarzhMu-msJMXUIzoTzPLJ8ruKG7uHpdDZF_Qo5JBm21HI9Nft5C19CF3qKUfmGLB1BJUsGz530QX3I8/s842/06.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="640" data-original-width="842" height="314" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgz6q6EwH-skC_Di2jcvrFW8eCmVPCZj_g6xxCAwBEj9FxcD42FYdUGIoiOXd1tXarzhMu-msJMXUIzoTzPLJ8ruKG7uHpdDZF_Qo5JBm21HI9Nft5C19CF3qKUfmGLB1BJUsGz530QX3I8/w413-h314/06.png" width="413" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaR9nmBbdLAyotMuL-gOTxHq488qk_VkLC-xS4HR8q3VYHtFe2MiFqEmXi04qRQIsih6SNKy2jlzJa5gW7B1iQVu-JpYEpI9dJdvmzWgRbjSOO5p-4t2Bum_WmNTbjxnIeNKGU07HDKVna/s1467/07.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="425" data-original-width="1467" height="168" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaR9nmBbdLAyotMuL-gOTxHq488qk_VkLC-xS4HR8q3VYHtFe2MiFqEmXi04qRQIsih6SNKy2jlzJa5gW7B1iQVu-JpYEpI9dJdvmzWgRbjSOO5p-4t2Bum_WmNTbjxnIeNKGU07HDKVna/w578-h168/07.png" width="578" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>Hope this will be helpful for you.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-87226953967272678262021-11-04T18:25:00.001+08:002021-11-04T18:25:28.309+08:00Full Backup Vs. Differential Backup Vs. Log BackupWhen it comes to backing up SQL Server databases, it's critical to know what backup types are there and what you should choose. In order to do this, you must first understand what SQL Server will include in each of these backup files.<script src="https://cdn.jsdelivr.net/gh/google/code-prettify@master/loader/run_prettify.js?lang=sql"></script><div><br /></div><div>If your database is in the "Full Recovery" model, you have the option to choose whether you want to back up<br /> the data file, log file or both.</div><div><br /></div><div>You will be given these options:</div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2yY-k4GK-n6Jry1IYRIAjFHi7sH-wqPVtYbZaRxRbuZZZLPhNpQgj8ylXZtAQ15IrZYPrOyHj2Lu5r1CWWoIC-hjElyFzDJ2WmAK70MJ0l9id7aaKxV6B2lElL5MKByWZM82oMQn1Pt_b/s672/01.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="94" data-original-width="672" height="83" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2yY-k4GK-n6Jry1IYRIAjFHi7sH-wqPVtYbZaRxRbuZZZLPhNpQgj8ylXZtAQ15IrZYPrOyHj2Lu5r1CWWoIC-hjElyFzDJ2WmAK70MJ0l9id7aaKxV6B2lElL5MKByWZM82oMQn1Pt_b/w593-h83/01.png" width="593" /></a></div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>To illustrate we will consider a hypothetical database which we will be backing up hourly.</div><div><br /></div><h3 style="text-align: left;">Full Backup</h3><div><br /></div><div>As the name implies full backup will include all the details (data), every time you back up the database.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTS6S_ca735WS4fr7rGv9vnw0yPXqZjrQFxkVYsLn8vNigYz2aU1wYmZ3DqgxQNZSETZXbB8s_WPG0RPrlTKoaYsosTamSUa1fxFoboGV6vcDxI_GV1vFyHcgg94NRqF7514ILDzIsBS3V/s794/02.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="794" data-original-width="749" height="569" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTS6S_ca735WS4fr7rGv9vnw0yPXqZjrQFxkVYsLn8vNigYz2aU1wYmZ3DqgxQNZSETZXbB8s_WPG0RPrlTKoaYsosTamSUa1fxFoboGV6vcDxI_GV1vFyHcgg94NRqF7514ILDzIsBS3V/w537-h569/02.png" width="537" /></a></div><br /><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>Consider the above case, where we will take full backup hourly.</div><div><br /></div><div><b>Full Backup #01</b> will contain all the data which was there since the beginning.</div><div><b>Full Backup #02</b> will contain all the data including the changes that happened between 8:00am - 9:00am</div><div><b>Full Backup #03</b> will contain all the data including the changes that happened between 9:00am - 10:00am</div><div><br /></div><div>The backup file size will grow since it will contain everything all the time. You need only one backup in order to restore the database and it will take the database to the status (or time) when the backup was initiated.</div><div><br /></div><h3 style="text-align: left;">Differential Backup</h3><div>Unlike the full backup, the differential backup will only contain the changes that happened since the last full backup</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHWvibvCqE8dqfA6gF9xS300O-FgHsyyRwIxY1x_JodxfT_QRLUnBTuqIoDe2LQh7vQlyf4ufWXfDJZp4eUyQY0vva9VzJ1E9UY1jTvySljo17nYTwozHI2gDavZ7Hz5bUQRv6VGHac7tv/s815/03.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="815" data-original-width="743" height="586" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHWvibvCqE8dqfA6gF9xS300O-FgHsyyRwIxY1x_JodxfT_QRLUnBTuqIoDe2LQh7vQlyf4ufWXfDJZp4eUyQY0vva9VzJ1E9UY1jTvySljo17nYTwozHI2gDavZ7Hz5bUQRv6VGHac7tv/w535-h586/03.png" width="535" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><br /></div><div><b>Full Backup #01</b> will contain all the data which was there since the beginning.</div><div><b>Differential Backup #01</b> will contain only the data changes that happened between 8:00am - 9:00am</div><div><b>Differential Backup #02</b> will contain only the data changes that happened between 8:00am - 10:00am</div><div><br /></div><div>The differential backup size is comparatively less than the full backup since it only contains changes since the last full backup. You need to have both full backup and the differential backup (last one) in order to restore the database.</div><div><br /></div><h3 style="text-align: left;">Log Backup</h3><div><br /></div><div><div class="separator" style="clear: both; text-align: left;">The log backup will only backup the transaction log file. It will not contain any data pages from the data file. A log backup will always contain changes in the log file since the last <b>log backup</b>.</div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi23Jf0MWgERQZnHaSuN4HmEwHxnLUPwUl7Db70HqoCHN-6BJYZklMKKsgOloBxW94zg3D8FgD3szT4tNR6qse5ipCFlrV9Zo_vIqnpCAVQ0bbusLhKRsNPNm790svTEhaiNFJVlKqsZ0cp/s792/04.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="792" data-original-width="769" height="547" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi23Jf0MWgERQZnHaSuN4HmEwHxnLUPwUl7Db70HqoCHN-6BJYZklMKKsgOloBxW94zg3D8FgD3szT4tNR6qse5ipCFlrV9Zo_vIqnpCAVQ0bbusLhKRsNPNm790svTEhaiNFJVlKqsZ0cp/w532-h547/04.png" width="532" /></a></div><br /><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>Consider the above case. Even though the database is fully backed up at 8:00AM the first transaction log backup taken at 9:00am will contain details since 7:00am.</div><div><br /></div><div><b>Full Backup #01</b> will contain details from both data and log file as of 8:00am</div><div><b>Log Backup #01 </b>will only contain details from the transaction log between 7:00am - 9:00am<b> </b></div><div><b>Log Backup #02 </b>will only contain details from the transaction log between 9:00am - 10:00am<b> </b></div><div><b><br /></b></div><div>With the log backups, you can take the database to any time of the day (in this case up to hourly blocks). But you need to retain all the log backups in order to do that.</div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-8233107509082221812021-11-01T19:14:00.006+08:002021-11-01T19:15:36.652+08:00Removing Invalid Logins (Both Server and Database Principals) from SQL Server<script src="https://cdn.jsdelivr.net/gh/google/code-prettify@master/loader/run_prettify.js?lang=sql"></script>
Have you ever come across a situation where you need to remove SQL Logins of users which have already left the organisation and not valid anymore ?<div><br /></div><div>This could be very tedious task since you need to remove the permission from all the databases in the server which users has access to prior removing the SQL login. It could be more painful when the user is owning a schema in the database. Then you need to assign the ownership of the schema to [dbo] prior dropping the permission from the database.</div><div><br /></div><div>We will see how we can achieve this step by step and finally we will make a script which we can run and ultimately produce a script which we can use to clean up the SQL Server.</div><div><br /></div><div>1. Frist we need to find out the logins which SQL Server is considered as invalid. We can use a system stored procedure "sys.sp_validatelogins" for this.</div><div><br /></div>
<?prettify linenums=1?>
<pre class="prettyprint">
CREATE TABLE #InvalidUsers(
[SID] VARBINARY(85)
,LOGIN_NAME SYSNAME
)
INSERT INTO #InvalidUsers (
[SID]
,LOGIN_NAME
)
EXEC sys.sp_validatelogins
</pre>
<div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGd76LO7s81wlEePi_VItM7xqNUnZpBKW1LvpNxyzcySV611EdVLeSWlUp2YPKHyZagYrBk8atFRLtbbnIuVTSGWnGq1tQbLUT42mL4BccCSROx4DdyLDclmscu5eyvczGKyWegZP8e2vM/s675/01.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="562" data-original-width="675" height="362" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGd76LO7s81wlEePi_VItM7xqNUnZpBKW1LvpNxyzcySV611EdVLeSWlUp2YPKHyZagYrBk8atFRLtbbnIuVTSGWnGq1tQbLUT42mL4BccCSROx4DdyLDclmscu5eyvczGKyWegZP8e2vM/w436-h362/01.png" width="436" /></a></div><br /><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>2. Next we need to iterate each user in all the databases on the server and remove them from the databases. We will generate a script and use it to drop users. We need to consider the following during the script generation.</div><div><br /></div><div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div style="text-align: left;">- Whether the user owns a schema in the database (if so we need to change the schema ownership to dbo)</div></blockquote></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><p style="text-align: left;">- Whether the user owns a the database. If so we need to change db ownership to a different user. In this example I will be changing the ownership to "sa"</p></div></blockquote><div><p>We will insert the details into another temporary table. I will explain part by part, but once the script is completed you can execute it as a whole. I have provided the completed script at the end.</p><p>We will use another system stored procedure to execute a query in all the databases in the SQL Server instance (sys.sp_MSforeachdb)</p>
<?prettify linenums=1?>
<pre class="prettyprint">
DECLARE @LoginName AS sysname
,@Sql AS NVARCHAR(2000)
DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT LOGIN_NAME FROM #InvalidUsers
OPEN cur
FETCH NEXT FROM cur INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = CONCAT(
'USE ?;INSERT INTO #InvalidUserDbInfo SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema
FROM sys.database_principals AS DP
LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id
WHERE [type] = ''U'' and DP.name = ''',@LoginName,''''
)
EXEC sys.sp_MSforeachdb @Sql
FETCH NEXT FROM cur INTO @LoginName
END
CLOSE cur
DEALLOCATE cur
</pre>
<div><br /></div><div>We will use the details populated into the table #InvalidUserDbInfo to generate a script to drop users from databases, change database ownerships and change schema ownerships.</div><div><br /></div>
<?prettify linenums=1?>
<pre class="prettyprint">
DECLARE
@LoginName AS sysname
,@Sql AS NVARCHAR(2000)
,@DBName AS NVARCHAR(128)
,@DbNameCurrent AS NVARCHAR(128) = ''
,@SqlSyntax AS NVARCHAR(MAX) = ''
,@OwnedSchema AS VARCHAR(128)
DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT
IUDI.DBNAME
,IUDI.LOGIN_NAME
,IUDI.OWNED_SCH
FROM
#InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME
OPEN cur
FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DbNameCurrent <> @DBName
BEGIN
IF @DbNameCurrent <> ''
SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13))
SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13))
SET @DbNameCurrent = @DBName
END
IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName)
BEGIN
SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13))
END
SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13))
FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema
END
CLOSE cur
DEALLOCATE cur
SELECT CAST('<root><![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]></root>' AS XML) AS CleanupScript_Step_2
</pre>
<div><br /></div><div>This will return a clickable result set. Upon clicking you will get the script to remove users from databases.</div><div><br /></div><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiZ7mUpgPoum45yhu6n9rsP05CXg6GG9O2tTL8EoZJuisS2u0ZdqyaodZt0lffMhqpadMLlB7TVxHIyd3MnLETGjRRkOP7_-57_ylwdGh9VnczdwtYFCZT0CerK-C4O-hlhEFSMJ3UdJAu/s438/02.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="67" data-original-width="438" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiZ7mUpgPoum45yhu6n9rsP05CXg6GG9O2tTL8EoZJuisS2u0ZdqyaodZt0lffMhqpadMLlB7TVxHIyd3MnLETGjRRkOP7_-57_ylwdGh9VnczdwtYFCZT0CerK-C4O-hlhEFSMJ3UdJAu/w477-h73/02.png" width="477" /></a></div><br /><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGF0cUHHS-nzXy36Apllt1_N7iMlk-jXKJUgmfacEeoHPvlcXyvgKeMfuxRteO3LdwlGR19pnX9O6cvLghuRV3hSZhq5uZ4oQJcOdm2i1HtQcu7aaaQoeghV6KdnZCTePyHpaQBEiuTUbn/s465/03.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="324" data-original-width="465" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGF0cUHHS-nzXy36Apllt1_N7iMlk-jXKJUgmfacEeoHPvlcXyvgKeMfuxRteO3LdwlGR19pnX9O6cvLghuRV3hSZhq5uZ4oQJcOdm2i1HtQcu7aaaQoeghV6KdnZCTePyHpaQBEiuTUbn/w427-h297/03.png" width="427" /></a></div><br /><div><br /></div><div>3. Finally we consider the database ownerships and drop the login from the SQL Server Instance</div><div><br /></div>
<?prettify linenums=1?>
<pre class="prettyprint">
DECLARE
@SqlSyntax AS NVARCHAR(MAX) = ''
SET @SqlSyntax += CONCAT(' --== Changing Database Ownership ==--',CHAR(13),CHAR(13),'USE master;',CHAR(13))
SELECT
@SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]')
FROM
sys.databases AS S
JOIN #InvalidUsers AS U
ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME
SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13))
------------------------------------------------------------------------------------------
SELECT
@SqlSyntax = @SqlSyntax + CHAR(13) +
CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N'''
,U.LOGIN_NAME
,''')'
,CHAR(13),CHAR(9)
,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13)
)
FROM
[sys].[server_principals] AS [SP]
JOIN #InvalidUsers AS U
ON U.LOGIN_NAME = SP.[name]
SELECT CAST('<root><![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]></root>' AS XML) AS CleanupScript_Step_3
</pre>
<div><br /></div><div>This will generate a script similar to the one shown below.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKudnp7jZwdMiYnDNjf4sO50FJN0nQLlzyy7c5QoQQeizcVBvsQR_UVQuPvMO59hwa5UwFIcEjuavuQvEfAaqEPdCV7Js2XoOPo8vaVW1cKgdj3A_PGyq5set7Wvu45p8iiGIRnmlJ-yae/s923/04.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="339" data-original-width="923" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKudnp7jZwdMiYnDNjf4sO50FJN0nQLlzyy7c5QoQQeizcVBvsQR_UVQuPvMO59hwa5UwFIcEjuavuQvEfAaqEPdCV7Js2XoOPo8vaVW1cKgdj3A_PGyq5set7Wvu45p8iiGIRnmlJ-yae/w564-h208/04.png" width="564" /></a></div><br /><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>Following is the full script which I have combine all the steps and which can be executed in a one go. You can download the script from the following link:</div><div><a href="https://drive.google.com/file/d/1DT3Rzdc4SkzCBDUxdP6kuYALV-Tkb-xI/view?usp=sharing">https://drive.google.com/file/d/1DT3Rzdc4SkzCBDUxdP6kuYALV-Tkb-xI/view?usp=sharing</a></div><div><br /></div>
<?prettify linenums=1?>
<pre class="prettyprint">
CREATE TABLE #InvalidUsers(
[SID] VARBINARY(85)
,LOGIN_NAME SYSNAME
)
INSERT INTO #InvalidUsers (
[SID]
,LOGIN_NAME
)
EXEC sys.sp_validatelogins
------------------------------------------------------------------------------------------
CREATE TABLE #InvalidUserDbInfo(
DBNAME NVARCHAR(128)
,[SID] VARBINARY(85)
,LOGIN_NAME SYSNAME
,OWNED_SCH VARCHAR(128)
)
DECLARE
@LoginName AS sysname
,@Sql AS NVARCHAR(2000)
,@DBName AS NVARCHAR(128)
,@DbNameCurrent AS NVARCHAR(128) = ''
,@SqlSyntax AS NVARCHAR(MAX) = ''
,@OwnedSchema AS VARCHAR(128)
DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT LOGIN_NAME FROM #InvalidUsers
OPEN cur
FETCH NEXT FROM cur INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = CONCAT(
'USE ?;INSERT INTO #InvalidUserDbInfo
SELECT DB_NAME(),DP.sid,DP.name, S.[name] as OwnedSchema
FROM sys.database_principals AS DP
LEFT JOIN sys.schemas as S on S.principal_id = DP.principal_id
WHERE [type] = ''U'' and DP.name = ''',@LoginName,''''
)
EXEC sys.sp_MSforeachdb @Sql
FETCH NEXT FROM cur INTO @LoginName
END
CLOSE cur
DEALLOCATE cur
------------------------------------------------------------------------------------------
DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT
IUDI.DBNAME
,IUDI.LOGIN_NAME
,IUDI.OWNED_SCH
FROM
#InvalidUserDbInfo AS IUDI WHERE IUDI.DBNAME <> 'master' ORDER BY IUDI.DBNAME
OPEN cur
FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DbNameCurrent <> @DBName
BEGIN
IF @DbNameCurrent <> ''
SET @SqlSyntax += CONCAT(CHAR(13),'---------------------------------',CHAR(13),CHAR(13))
SET @SqlSyntax += CONCAT('USE [',@DBName,'];',CHAR(13),CHAR(13))
SET @DbNameCurrent = @DBName
END
IF (ISNULL(@OwnedSchema,'') <> '' AND ISNULL(@OwnedSchema,'') <> @LoginName)
BEGIN
SET @SqlSyntax += CONCAT('ALTER AUTHORIZATION ON SCHEMA::[',@OwnedSchema,'] TO [dbo]', CHAR(13))
END
SET @SqlSyntax += CONCAT('DROP USER IF EXISTS [',@LoginName,']',CHAR(13))
FETCH NEXT FROM cur INTO @DBName,@LoginName,@OwnedSchema
END
CLOSE cur
DEALLOCATE cur
------------------------------------------------------------------------------------------
SET @SqlSyntax +=
CONCAT(CHAR(13),CHAR(13)
,' --== Changing Database Ownership ==--',CHAR(13),CHAR(13)
,'USE master;',CHAR(13))
SELECT
@SqlSyntax = @SqlSyntax + CHAR(13) + CONCAT('ALTER AUTHORIZATION ON DATABASE::[',S.[name],'] TO [sa]')
FROM
sys.databases AS S
JOIN #InvalidUsers AS U
ON SUSER_SNAME(S.owner_sid) = U.LOGIN_NAME
SET @SqlSyntax+= CONCAT(CHAR(13),CHAR(13))
------------------------------------------------------------------------------------------
SELECT
@SqlSyntax = @SqlSyntax + CHAR(13) +
CONCAT('IF EXISTS (SELECT 0 FROM [sys].[server_principals] AS [SP] WHERE [SP].[name] = N'''
,U.LOGIN_NAME,''')'
,CHAR(13),CHAR(9)
,'DROP LOGIN [',U.LOGIN_NAME,']',CHAR(13),CHAR(13))
FROM
[sys].[server_principals] AS [SP]
JOIN #InvalidUsers AS U
ON U.LOGIN_NAME = SP.[name]
SELECT CAST('<root><![CDATA[' + CHAR(13) + @SqlSyntax + CHAR(13) + ']]></root>' AS XML) AS CleanupScript_Step_3
</pre>
<div><br /></div><div>Hope this will be very useful to you!</div><div><br /></div><div><br /></div><div><br /></div></div>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-45012068824534664082021-10-31T23:26:00.000+08:002021-10-31T23:26:44.022+08:00Attaching SQL Database with FILESTREAM data<script src="https://cdn.jsdelivr.net/gh/google/code-prettify@master/loader/run_prettify.js?lang=sql"></script>
<p> Have you ever come across a situation where you need to attach a database using existing files (mdf, ldf & file stream data), but you don't have an option to mention the file stream folder when you use SSMS.</p><p>This won't be an issue if the filestream folder resides on its original path.</p><p>Let's look at an example. I have SQL data, log and filestream data which I will first attach using SSMS.</p><p></p><ol style="text-align: left;"><li>Right-click on the database folder in SSMS and choose "Attach"</li><li>Click the "Add" button and choose the data file (.mdf). Upon selecting the mdf file it will list the associated ldf file as well. But make sure to change the path accordingly from the second pane.</li><li>Click on Ok.</li></ol><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHDmticorOSNJiR2ZAKZcMfyrZGwwfL1ChWHKUgxoSo6DNfBIkr4_f0arOscPVhQR3iu_nv2FRG6BX0XOk87T1pBuuZ6xz4DSqYKUGeBQv1e464NeKzOlwQqDkfC1S4cwcglYogVl8wgI3/s364/00.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="344" data-original-width="364" height="339" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHDmticorOSNJiR2ZAKZcMfyrZGwwfL1ChWHKUgxoSo6DNfBIkr4_f0arOscPVhQR3iu_nv2FRG6BX0XOk87T1pBuuZ6xz4DSqYKUGeBQv1e464NeKzOlwQqDkfC1S4cwcglYogVl8wgI3/w359-h339/00.png" width="359" /></a></div><br /><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwi4KWohQ9mGHh_iPyvT-pK4L_2sTIDNgmTEGYRIVmLdHPMFqvimzLzTkFRcuVqOMC7qGa7kOF3JWR0_yiW6ODU_YHU13vnc8QGJAJTSHTyvIsjAUI17lMDTRZPlB8zM63FOqyn5MhKh2P/s930/03.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="812" data-original-width="930" height="522" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwi4KWohQ9mGHh_iPyvT-pK4L_2sTIDNgmTEGYRIVmLdHPMFqvimzLzTkFRcuVqOMC7qGa7kOF3JWR0_yiW6ODU_YHU13vnc8QGJAJTSHTyvIsjAUI17lMDTRZPlB8zM63FOqyn5MhKh2P/w599-h522/03.png" width="599" /></a></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">In the above example, it will succeed without an issue since I have not changed the paths.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">Now we will detach the database and change the file stream folder name to a different one. I will change the folder name to "SampleSqlDatabase_FSData_New" (This is the most common case where you will get the relevant files from the production environment which you need to attach to a test or development environment.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">Now if you try to attach the database using SSMS in the same way you will get the following error message.</div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhJuGxpFu_seuwBDxNrTUuXt7uWWM50RwlEe92bOM-EdE57fxOaLyI0ZGlhWAO5z7GTQ7nOFe0FQ6WqZ2cQyB7eFgOF1K2Aama2De_OREe3ipHHg_ARuPb3Aae1aRH7l5htIGdjK8karM9/s809/01.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="168" data-original-width="809" height="125" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhJuGxpFu_seuwBDxNrTUuXt7uWWM50RwlEe92bOM-EdE57fxOaLyI0ZGlhWAO5z7GTQ7nOFe0FQ6WqZ2cQyB7eFgOF1K2Aama2De_OREe3ipHHg_ARuPb3Aae1aRH7l5htIGdjK8karM9/w606-h125/01.png" width="606" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLzjUHvsdrVn6CIoXu_W9oPIsCYz-RC1huaiTRAoADqEkCWJAmupGwRN9rdnMiVsXy0RAhj7Famw3UmMvpO1WRRhPeD59EcImesSPtw_yf6TliHQ5RH5CH2M2XAKIxXfMuZviLwLCU82ek/s809/02.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="325" data-original-width="809" height="244" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLzjUHvsdrVn6CIoXu_W9oPIsCYz-RC1huaiTRAoADqEkCWJAmupGwRN9rdnMiVsXy0RAhj7Famw3UmMvpO1WRRhPeD59EcImesSPtw_yf6TliHQ5RH5CH2M2XAKIxXfMuZviLwLCU82ek/w604-h244/02.png" width="604" /></a></div><br /><div><br /></div><p></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p>Even if you try to generate the scripts using SSMS it doesn't provide you with the option to include the filestream folder.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqSmb-JcPwx5h0DPpHcki87EAjV1n9eDOgIAMclzAe-8wRCNYeuaKfTaW2cOlFeIswNeiRcGqou10UWHouQR8OUTnacUv5wY0u4GMjrH2b0q-e93i3Lf7g-dej8bUynymJ2RCVuzzhL4UY/s930/04.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="812" data-original-width="930" height="522" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqSmb-JcPwx5h0DPpHcki87EAjV1n9eDOgIAMclzAe-8wRCNYeuaKfTaW2cOlFeIswNeiRcGqou10UWHouQR8OUTnacUv5wY0u4GMjrH2b0q-e93i3Lf7g-dej8bUynymJ2RCVuzzhL4UY/w599-h522/04.png" width="599" /></a></div><br /><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>
<?prettify linenums=1?>
<pre class="prettyprint">
USE [master]
GO
CREATE DATABASE [SampleSqlDatabase] ON
( FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf' ),
( FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf' )
FOR ATTACH
GO
</pre>
<p>We can use the above code snippet and include the file stream path.</p>
<?prettify linenums=1?>
<pre class="prettyprint">
USE [master]
GO
CREATE DATABASE [SampleSqlDatabase] ON
(FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf'),
(FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf'),
FILEGROUP NewFSGroup CONTAINS FILESTREAM (
NAME = 'NewDatabaseFileStream'
,FILENAME = 'D:\_SQL_DATA\SampleSqlDatabase_FSData_New'
)
FOR ATTACH
GO
</pre>
<p>Then you will be able to attach the database without an issue. You can use any name for the <span style="font-family: courier;">FILEGROUP </span>and <span style="font-family: courier;">NAME </span>(under the <span style="font-family: courier;">FILEGROUP</span>). It doesn't need to be the same as your source database.</p><p>Hope this will be a helpful tip to you.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-70968838295118290032021-10-31T02:24:00.007+08:002021-10-31T02:39:07.745+08:00SQL Server Transaction Log file behaviour in Full Recovery Mode<script src="https://cdn.jsdelivr.net/gh/google/code-prettify@master/loader/run_prettify.js?lang=sql"></script>
<p>In a previous blog post, I have explained different types of recovery models in SQL Server databases. During the explanations, I have stated that the transaction log data will be truncated during certain backup types. We will look into this more closely and see how it works.</p>
<p><span style="color: #2b00fe;">Note: Even the details are removed from the transaction log file, the physical size may not change unless you issue a DBCC SHRINKFILE command. Only the file space will be re-used once the details are truncated.</span></p>
<p>We will start with creating a sample database named 'SqlTransactionLogExample'</p>
<?prettify linenums=1?>
<pre class="prettyprint">
USE master;
GO
CREATE DATABASE [SqlTransactionLogExample];
--== Makesure the database is in "Full" recovery model ==--
SELECT
[name]
,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model
FROM
sys.databases
WHERE
[name] = 'SqlTransactionLogExample'
</pre>
<br />
<p>We will create one table and insert records (1,000,000 rows to see a significant change in the file size).</p>
<?prettify linenums=1?>
<pre class="prettyprint">
USE SqlTransactionLogExample;
GO
CREATE TABLE dbo.UidInformation(
Id UNIQUEIDENTIFIER NOT NULL
,PayLoad CHAR(1000) NOT NULL CONSTRAINT [DF_SampleTable] DEFAULT (SPACE(1000))
)
INSERT INTO dbo.UidInformation (
Id
,PayLoad
)
SELECT
NEWID() AS Id,
([100000].Num
+ [10000].Num
+ [1000].Num
+ [100].Num
+ [10].Num
+ [1].Num) AS PayLoad
FROM (
SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 0
) AS [1]
CROSS JOIN (
SELECT 10 AS Num UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL
SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL
SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 UNION ALL
SELECT 0
) AS [10]
CROSS JOIN (
SELECT 100 AS Num UNION ALL SELECT 200 UNION ALL SELECT 300
UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
UNION ALL SELECT 0
) AS [100]
CROSS JOIN (
SELECT 1000 AS Num UNION ALL SELECT 2000 UNION ALL SELECT 3000
UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000
UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
UNION ALL SELECT 0
) AS [1000]
CROSS JOIN (
SELECT 10000 AS Num UNION ALL SELECT 20000 UNION ALL SELECT 30000
UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000
UNION ALL SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
UNION ALL SELECT 0
) AS [10000]
CROSS JOIN (
SELECT 100000 AS Num UNION ALL SELECT 200000 UNION ALL SELECT 300000
UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000
UNION ALL SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
UNION ALL SELECT 0
) AS [100000]
WHERE(
[100000].Num
+ [10000].Num
+ [1000].Num
+ [100].Num
+ [10].Num
+ [1].Num
) BETWEEN 1 AND 1000000
ORDER BY ([100000].Num + [10000].Num + [1000].Num + [100].Num + [10].Num + [1].Num)<span style="font-family: Times New Roman;"><span style="white-space: normal;">
</span></span></pre>
<p><br /></p>
<p>Now we will inspect the number of entries in the transaction log.</p>
<?prettify linenums=1?>
<pre class="prettyprint">
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)
--Returns 2511475
</pre>
<p><br /></p>
<p><span style="color: #2b00fe;">Note: Record count may be varied in the transaction log of your database.<span style="white-space: pre;"> </span></span></p>
<p><br /></p>
<p>We will check the size of the transaction log file </p>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3CYceLenPKHpUCEPxyESBv9LDMCfag5QhpQILkl4zKp-rkZFlhJTBvv7khuHmmLPRZ0F4G3uHB6R8ZBy2_0MzG11NIhaMWgdNjOxtjCatzZAyw7zQ_6yC4X6jjW701vyt4Fr7wxuNNZE_/s759/04.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="29" data-original-width="759" height="23" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3CYceLenPKHpUCEPxyESBv9LDMCfag5QhpQILkl4zKp-rkZFlhJTBvv7khuHmmLPRZ0F4G3uHB6R8ZBy2_0MzG11NIhaMWgdNjOxtjCatzZAyw7zQ_6yC4X6jjW701vyt4Fr7wxuNNZE_/w613-h23/04.png" width="613" /></a></div><br /><p><br /></p>
<p>Now we will take a full database backup</p>
<?prettify linenums=1?>
<pre class="prettyprint">
USE master;
GO
BACKUP DATABASE [SqlTransactionLogExample] TO DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_FullDB_Backup.bak'
Now we will inspect number of entries in the transaction log file again.
USE SqlTransactionLogExample
GO
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)
--Returns 2511475</pre>
<p><br /></p>
<p><span style="color: #2b00fe;">Note: There is a slight increment of the record count since there are new entries written to the log file during the database backup.</span></p>
<p>We will now inspect the transaction log entries</p>
<?prettify linenums=1?>
<pre class="prettyprint">
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)
-- Returns 2511545
</pre>
<p><br /></p><p>We will now take a backup of the transaction log </p>
<?prettify linenums=1?>
<pre class="prettyprint">
BACKUP LOG [SqlTransactionLogExample] TO
DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_LogBackup_AfterInsert.bak'</pre>
<p><br /></p>
<p>And if we inspect the number of entries in the log file it has drastically reduced.</p>
<?prettify linenums=1?>
<pre class="prettyprint">
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)
--Returns 10
</pre>
<p><br /></p>
<p>However, the space consumed by the log file is still the same. This is the reason I have mentioned it previously. In order to release the space, we have to issue the DBCC SHRINKFILE command.</p>
<p><br /></p>
<?prettify linenums=1?>
<pre class="prettyprint">
--This is to find the name of the logical log file name
SELECT * FROM sys.sysfiles
--SqlTransactionLogExample_log
DBCC SHRINKFILE('SqlTransactionLogExample_log',1)
</pre>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvqXF40UbBBiMqa0ONbN00MCT4cc66krZgW4sEx7pmjEaYoO7y2KZjyk9Had9Xbqy1IXnaxmgjxNO9fYHyfXEFs0-Ij7JIMyjtPSk3aqEo0HekhbcqAh9qMrkHrbRSNJn_1J82WUSjPIfx/s770/05.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="30" data-original-width="770" height="22" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvqXF40UbBBiMqa0ONbN00MCT4cc66krZgW4sEx7pmjEaYoO7y2KZjyk9Had9Xbqy1IXnaxmgjxNO9fYHyfXEFs0-Ij7JIMyjtPSk3aqEo0HekhbcqAh9qMrkHrbRSNJn_1J82WUSjPIfx/w598-h22/05.png" width="598" /></a></div><br /><p><br /></p>
<p>Hope this will be helpful to you.</p>
<p><br /></p>
<div><br /></div>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-10908038133859870692021-10-30T17:17:00.000+08:002021-10-30T17:17:22.081+08:00Recovery Models in SQL Server Databases<script src="https://cdn.jsdelivr.net/gh/google/code-prettify@master/loader/run_prettify.js?lang=sql"></script>
<p> "Recovery Model" determines how long the data is to be kept in the transaction logs. It also allows what sort of backups and restores you can perform on the database.</p>
<p><br /></p>
<h3 style="text-align: left;">Types of recovery models</h3>
<p>There are three types of recovery models in SQL Server</p>
<p></p>
<ol style="text-align: left;">
<li><span style="white-space: pre;"> </span>Simple</li>
<li><span style="white-space: pre;"> </span>Full </li>
<li><span style="white-space: pre;"> </span>Bulk-logged</li>
</ol>
<div><br /></div>
<p></p>
<h3 style="text-align: left;">How can I change the recovery model of a database?</h3>
<div>
<div>The recovery model of a database can be changed either using the GUI (SSMS) or using a T-SQL statement.</div>
<div><br /></div>
<div>To change using SSMS follow these steps:</div>
<div><span style="white-space: pre;"> </span>1. Right-click the database and choose options</div>
<div><span style="white-space: pre;"> </span>2. Select "Options" from the pages</div>
<div><span style="white-space: pre;"> </span>3. From the Recovery model drop-down list choose the appropriate one.</div>
<div><span style="white-space: pre;"> </span>4. Click OK</div>
<div><br /></div>
<div><br /></div>
</div>
<blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;">
<div>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQLTwJxS8NLKVzvrwUv1sf9V8PnPze3nI4QS9GgAn5H-y1GgXVbxCz5OhrkTNFur0QtBrN4UJr9qC9_3I2SqkK-7PPtdGCyO3JSg5GJnUUoA1aq9Z43Dw_hVbfugpMmvlMhQ3glCK50OYo/s477/01.png" style="clear: left; float: left; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="477" data-original-width="369" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQLTwJxS8NLKVzvrwUv1sf9V8PnPze3nI4QS9GgAn5H-y1GgXVbxCz5OhrkTNFur0QtBrN4UJr9qC9_3I2SqkK-7PPtdGCyO3JSg5GJnUUoA1aq9Z43Dw_hVbfugpMmvlMhQ3glCK50OYo/w153-h197/01.png" width="153" /></a></div>
</div>
</blockquote>
<div>
<br />
</div>
<blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;">
<div>
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0dlApY4OB5CirI1AllleoSoSA-MMC2UspZBKCKJYrte5UV6FTVmWIwI0_HltyB_Be8lx01AkzD4eLMIsNV9XZHekrmsIC4oCWbsOJ2OYy0-YTZPJr_M9gL1nUY_SW9VlkOpiMyfORIyKH/s929/03.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="807" data-original-width="929" height="353" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0dlApY4OB5CirI1AllleoSoSA-MMC2UspZBKCKJYrte5UV6FTVmWIwI0_HltyB_Be8lx01AkzD4eLMIsNV9XZHekrmsIC4oCWbsOJ2OYy0-YTZPJr_M9gL1nUY_SW9VlkOpiMyfORIyKH/w407-h353/03.png" width="407" /></a></div>
</div>
</blockquote>
<div>
<br />
<div class="separator" style="clear: both; text-align: center;"><br /></div>
<div><br /></div>
<div><span style="white-space: pre;"> </span></div>
<h4 style="text-align: left;">To change using T-SQL statement use the following syntax:</h4>
<div>
<?prettify linenums=1?>
<pre class="prettyprint">
USE master;
ALTER DATABASE YourDBName SET RECOVERY SIMPLE
</pre>
</div>
</div>
<div>You can use options <span style="font-family: courier;">SIMPLE, FULL</span> or <span style="font-family: courier;">BULK_LOGGED</span></div>
<div><br /></div>
<div>You can find out the recovery model of the databases which resides on your server using the following query.</div>
<div><br /></div>
<div>
<?prettify linenums=1?>
<pre class="prettyprint lang-scm">
SELECT
[name]
,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model
FROM
sys.databases
WHERE
database_id > 4
</pre>
</div>
<div><br /></div>
<div>Note: <span style="font-family: courier;">database_id > 4</span> will ensure that system databases information is excluded.<span style="white-space: pre;"> </span></div>
<div><br /></div>
<div>Now we will look into closely what options it may enable during backup/restore for each type of recovery model.</div>
<div><br /></div>
<h4 style="text-align: left;">Simple Recovery Model</h4>
<div><br /></div>
<div>
<div>
<ul style="text-align: left;">
<li>You cannot take backups of the transaction log explicitly</li>
<li>A database backup will always contain both data and transaction log file</li>
</ul>
</div>
</div>
<blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;">
<div>
<div>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZzYw2_wJcufKczLDrri13Xnf8xJlZ93SJPkMinlszY3I2Zw_qw2cDNbvHNQhM3UHsH3oj2NUF9887M8O148M7r2XnWr9iN4gXvOEG0KZZ8i6Nt1DMeOBw9RjjY9LloavGlda7sS3OiH4D/s634/04.png" style="clear: left; float: left; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="299" data-original-width="634" height="190" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZzYw2_wJcufKczLDrri13Xnf8xJlZ93SJPkMinlszY3I2Zw_qw2cDNbvHNQhM3UHsH3oj2NUF9887M8O148M7r2XnWr9iN4gXvOEG0KZZ8i6Nt1DMeOBw9RjjY9LloavGlda7sS3OiH4D/w403-h190/04.png" width="403" /></a></div>
</div>
</div>
</blockquote>
<div>
<br />
<div><br /></div>
</div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div>
<ul style="text-align: left;">
<li>Transaction log will not grow beyond what's been defined. The space in the transaction log file will be re-used (overwritten) once the transactions are written to the data file.</li>
<li>In case of a disaster transaction occurred between the last full backup and the disaster timeline cannot be recovered. (E.g: Assume we have a database in which the backups are taken every hour. (8am, 9am etc.) In case a disaster happens at 10:45am, transactions between 10am and 10:45am will be lost)</li>
</ul>
<div><br /></div>
</div>
<blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;">
<div>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUCW7UPWszQVxrdNqrZIm0d9-UVSvu9AZRK3h3_lU0a_ajhZJe2ILVn3qUuk6EiDRMHL-VNI1UmOxx9xHdEiYEHWsmBdqZs5V5BvSxeEJG_v-Cz3IT5KP5dlGUm4MPpTrCxqsbDb7qvJRS/s637/05.png" style="clear: left; float: left; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="424" data-original-width="637" height="292" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUCW7UPWszQVxrdNqrZIm0d9-UVSvu9AZRK3h3_lU0a_ajhZJe2ILVn3qUuk6EiDRMHL-VNI1UmOxx9xHdEiYEHWsmBdqZs5V5BvSxeEJG_v-Cz3IT5KP5dlGUm4MPpTrCxqsbDb7qvJRS/w439-h292/05.png" width="439" /></a></div>
</div>
</blockquote>
<div>
<br />
<div><br /></div>
</div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<div><br /></div>
<h4 style="text-align: left;">Bulk-logged Recovery Model</h4>
<div><br /></div>
<div>
<ul style="text-align: left;">
<li>You can take the backup of the transaction log explicitly</li>
<li>Log truncation won't happen once the transaction log backup is taken. Hence it needs to be truncated manually (Same as the "Full" recovery model)</li>
<li>In the bulk-logged recovery model, certain operations are logged minimally. E.g: Bulk import operations such as BCP operations and BULK INSERT, SELECT INTO operations. More details on such operations can be found on the following link: <a href="https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#MinimallyLogged">https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#MinimallyLogged</a></li>
</ul>
<div><br /></div>
</div>
<div>
<h4 style="text-align: left;">Full Recovery Model</h4>
<div> </div>
<div>
<ul style="text-align: left;">
<li>Supports transaction log backup</li>
<li>Chances of a data loss are minimal (subject to the backup strategy implemented)</li>
<li>Log truncation must be done manually (Upon taking the transaction log backup, it will mark unused space which will be overwritten later by future transactions)</li>
<li>Transaction log could grow large compared to the "Simple" recovery model</li>
</ul>
</div>
</div>
<div><br /></div>
<div>The scope of this post is to give a brief idea of the recovery models in SQL Server. In a future post, I will explain how to bring a database to a certain date and time by having a Full Recovery Model and a good backup strategy.</div>
<div><br /></div>
<div><br /></div>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-34283553587849242019-05-10T17:45:00.003+08:002019-05-10T17:46:51.280+08:00Strange behavior on JSON_VALUE when table contains blank and non-blank values (JSON text is not properly formatted. Unexpected character '.' is found at position 0.)<br />
Few days back we had a requirement to search whether a certain value exists in one of the table fields where the values are stored as JSON strings. The default constructor has been set up not to allow any NULLs but in case there’s no value, the default value has been setup as an empty string.<br />
So basically the query would be similar to something shown below<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<pre class="brush:sql">SELECT
'x'
FROM
[schema].[TableName] AS Src
WHERE
JSON_VALUE(Src.ColumnName,'$.Root.AttributeName') LIKE 'SearchValue%'
</pre>
<br />
How ever when we ran this query we got the following error<br />
<br />
<a href="https://www.blogger.com/u/1/$image[5].png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><span style="color: red;"><strong>Msg 13609, Level 16, State 2, Line 36<br /> JSON text is not properly formatted. Unexpected character '.' is found at position 0.</strong></span><br />
<span style="color: red;"><strong><br /></strong></span>
Initially we thought that we have typed the attribute incorrectly since it’s case sensitive. But in this case it was correct. <br />
<br />
We investigated further and found out few things. But prior explaining them we will replicate this issue. For this I will create one simple table and insert three records.<br />
<br />
<br />
<pre class="brush:sql">--== Create a table ==--
CREATE TABLE dbo.Employee_Information (
Id INT
,FirstName NVARCHAR(100)
,LastName NVARCHAR(100)
,JsonData NVARCHAR(MAX)
)
--== Insert few rows ==--
INSERT INTO dbo.Employee_Information (
Id
,FirstName
,LastName
,JsonData
)
VALUES
(1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}')
,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}')
,(3,'Luke','Skywalker','')
</pre>
Now we will use the following query to find any records which the LastName is like ‘Doe’.<br />
<br />
<br />
<pre class="brush:sql">SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
</pre>
<br />
<br />
<strong><span style="color: blue;"><br /></span></strong>
<br />
<span style="color: red;"><strong>Msg 13609, Level 16, State 2, Line 36<br />JSON text is not properly formatted. Unexpected character '.' is found at position 0.</strong></span><br />
<span style="color: red;"><strong><br /></strong></span>
<strong><span style="color: blue;">**Note : The query will return results till the error occurs. Hence you will see some rows in your result tab in SSMS.</span></strong><br />
<strong><span style="color: blue;"><br /></span></strong>
These are the observations we made during our investigation<br />
<br />
<strong><u>Observation 01</u></strong><br />
<strong><u><br /></u></strong>
If you query the table with a predicate and if that predicate doesn’t include any rows with blank values in the JSON (it’s an NVARCHAR column) field the query will executed successfully.<br />
<br />
<br />
<pre class="brush:sql">--== Success ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
AND Id IN (1,2)
--== Success ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
AND Id <> 3
--== Fail ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
AND Id = 3
</pre>
<br />
<strong><u><br /></u></strong>
<strong><u>Observation 02</u></strong><br />
<strong><u><br /></u></strong>
Even you use a filter to fetch only rows containing a valid JSON the execution will be successful.<br />
<br />
<br />
<pre class="brush:sql">--== Success ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
ISJSON(E.JsonData) > 0
AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
</pre>
<br />
<strong><u><br /></u></strong>
<strong><u>Observation 03</u></strong><br />
<strong><u><br /></u></strong>
Even you use a filter to fetch only rows containing a non-blank value in the JSON field, it will fail.<br />
<br />
<br />
<pre class="brush:sql">--== Fail ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
E.JsonData <> ''
AND JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
</pre>
<br />
<strong><u><br /></u></strong>
<strong><u>Observation 04</u></strong><br />
<br />
If you remove records and only keep either one type of rows (either only blank rows or only non-blank) the query will be executed successfully.<br />
<br />
<br />
<pre class="brush:sql">TRUNCATE TABLE dbo.Employee_Information
INSERT INTO dbo.Employee_Information (
Id
,FirstName
,LastName
,JsonData
)
VALUES
(1,'John','Doe','{"Employee":{"Id":1,"FirstName":"John","LastName":"Doe"}}')
,(2,'Jane','Doe','{"Employee":{"Id":2,"FirstName":"Jane","LastName":"Doe"}}')
--== Success ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
</pre>
<strong><br /></strong>
<strong><u><br /></u></strong>
<strong><u>Observation 05</u></strong><br />
<strong><u><br /></u></strong>
If you have rows only containing blank values in the JSON field the query will fail.<br />
<br />
<br />
<pre class="brush:sql">TRUNCATE TABLE dbo.Employee_Information
INSERT INTO dbo.Employee_Information (
Id
,FirstName
,LastName
,JsonData
)
VALUES
(1,'John','Doe','')
,(2,'Jane','Doe','')
--== Fail ==--
SELECT
Id
FROM
dbo.Employee_Information AS E
WHERE
JSON_VALUE(E.JsonData,'$.Employee.LastName') LIKE 'Doe%'
</pre>
<br />
Hope this might help you if you encounter this strange behavior during your development.<br />
<br />
<strong><span style="color: blue;">Note : All the above queries are executed under the following SQL Server Version (SELECT @@VERSION)</span></strong><br />
<strong><span style="color: blue;"><br /></span></strong>
<br />
<br />
<br />
<span style="color: blue;">Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) <br /> Oct 28 2016 18:17:30 <br /> Copyright (c) Microsoft Corporation<br /> Developer Edition (64-bit) on Windows Server 2012 Standard 6.2 <x64> (Build 9200: ) (Hypervisor)</x64></span><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com6tag:blogger.com,1999:blog-1342610106694475714.post-28074380238066636872018-09-10T14:15:00.001+08:002018-09-10T14:15:32.025+08:00Applying database principal through out the server (for all databases) for a particular user<p>Ever come across a requirement which you required to give db_datareader access to a specific user across all the databases on a particular SQL Server. The task is simple as long as you don’t have many databases in the same SQL Server. How ever if the number of databases are very high this can be a very time consuming one.</p><p>This can be done either using the GUI (SSMS) or using a T-SQL script. We will consider both options.</p><p><strong><u>Using SQL Server Management Studio</u></strong></p><p>In order to illustrate this we will create a SQL Login ‘db_user_read_only’ with ‘public’ server role and on the user mapping, we will apply the db_datareader principal.</p><p><a href="https://lh3.googleusercontent.com/-wQq-LfVXcjc/W5YL72bY3OI/AAAAAAAAFCY/UzrYvwgnxTMIW9e3J3lceoC3wC3083tiQCHMYCw/s1600-h/image%255B40%255D"><img width="518" height="464" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-ux5xjEoTg8k/W5YL8wyeZTI/AAAAAAAAFCc/_rk8qhc_vwgG4Njx7OF3CHuEnj0jweZ7QCHMYCw/image_thumb%255B24%255D?imgmax=800" border="0"></a></p><p><a href="https://lh3.googleusercontent.com/-VzL7psaXh_4/W5YL96aP1-I/AAAAAAAAFCg/uMADBKzJ8QcbrZ4_DSZp0kxNCDrzgbdcgCHMYCw/s1600-h/image%255B30%255D"><img width="517" height="463" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-wsUKapGCiOE/W5YL-1vtjMI/AAAAAAAAFCk/ZdxGg6Cso7go_FXmteGhn2jCeRZHWWf6QCHMYCw/image_thumb%255B18%255D?imgmax=800" border="0"></a></p><p><a href="https://lh3.googleusercontent.com/-9LHcRKhQo7I/W5YL_k3a5QI/AAAAAAAAFCo/pACCvVRXhbAKkN9lBgus9wspPPdNJztbACHMYCw/s1600-h/image%255B20%255D"><img width="518" height="464" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-_KycfGJk-yQ/W5YMAgOel_I/AAAAAAAAFCs/ms7T87tP7GcQDSbvCj5kuKQgR5htZehswCHMYCw/image_thumb%255B12%255D?imgmax=800" border="0"></a></p><p>Like mentioned it would be easy to use the GUI when you have less number of databases. But if the SQL Server contains lots of databases this will be a very time consuming job. Then it would be very handy to use the latter approach.</p><p><strong><u>Using T-SQL</u></strong></p><p>You can use the following script to apply the db_datareader principal across all the databases on a particular server.</p>
<br>
<pre class="brush:sql">DECLARE
@Sql AS NVARCHAR(MAX)
,@UserId AS VARCHAR(MAX) = 'YourLoginId'
SET @Sql = CONCAT('
USE [?];
IF EXISTS (SELECT 0 FROM sys.database_principals AS DP WHERE name = ''',@UserId,''')
BEGIN
EXEC sys.sp_change_users_login ''update_one'',''',@UserId,''',''',@UserId,'''
END
ELSE
CREATE USER [',@UserId,'] FOR LOGIN [',@UserId,']
ALTER ROLE [db_datareader] ADD MEMBER [',@UserId,']
')
EXEC sys.sp_MSforeachdb
@command1 = @Sql
,@replacechar = '?'
</pre>
<br>
<p>Please note the following:</p><ul><li>On the above code I haven’t excluded the system databases.</li><li>If the login exists on the database it will map the database user using sp_change_users_login</li></ul><p>Hope this might be very useful to you.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-935229344810935002018-07-06T14:42:00.001+08:002018-07-06T15:14:15.095+08:00Replacing sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entitiessp_depends have been one of the most used system stored procedures in SQL Server. Infact many of us still use that even though Microsoft had annouced that it will be removed from the future releases.<br />
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-2017" title="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-2017">https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-2017</a><br />
<a href="https://lh3.googleusercontent.com/-vVFjZIF6-iQ/Wz8PMN7myBI/AAAAAAAAE7I/7QmIIc4V3_UgdhWQEkvKAhvLk7bBxyoRgCHMYCw/s1600-h/image%255B4%255D"><img alt="image" border="0" height="156" src="https://lh3.googleusercontent.com/-d_cFxKiVPk8/Wz8POtTwNjI/AAAAAAAAE7M/fqj3wL3e5wsBWIUrWwemWsFnVuAwI6oKwCHMYCw/image_thumb%255B2%255D?imgmax=800" style="background-image: none; border: 0px currentcolor; display: inline;" title="image" width="543" /></a><br />
Alternatively Microsoft has provided two dynamic management views (these have been introduced with SQL Server 2008) in order to get similar kind of information.<br />
<ul>
<li><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referencing-entities-transact-sql?view=sql-server-2017">sys.dm_sql_referencing_entities</a></li>
<li><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-sql-referenced-entities-transact-sql?view=sql-server-2017">sys.dm_sql_referenced_entities</a></li>
</ul>
You can get further details on the aforementioned view by visiting the link. (links are embedded into the view name)<br />
However if you have used sp_depends you might have already faced the issue that the results which is being returned from this stored procedure is not very accurate (most of the time it seems fine)<br />
Otherday I was going through these two view in order to create an sp which is similar to sp_depends and thought of sharing the query so that it can be useful to anyone who depends on this sp.<br />
<br />
<br />
<pre class="brush:sql">DECLARE
@objname AS NVARCHAR(100) = 'Website.SearchForPeople'
,@objclass AS NVARCHAR (60) = 'OBJECT'
SELECT
CONCAT(sch.[name],'.',Obj.[name]) AS [name]
,(CASE Obj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
WHEN 'TR' THEN 'SQL trigger'
WHEN 'UQ' THEN 'UNIQUE constraint'
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'SQL scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'SQL inline table-valued function'
WHEN 'IT' THEN 'Internal table'
WHEN 'P' THEN 'SQL Stored Procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN 'PG' THEN 'Plan guide'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'RF' THEN 'Replication-filter-procedure'
WHEN 'S' THEN 'System base TABLE'
WHEN 'SN' THEN 'Synonym'
WHEN 'SO' THEN 'Sequence OBJECT'
WHEN 'U' THEN 'Table (user-defined)'
WHEN 'V' THEN 'VIEW'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'SQL table-valued-function'
WHEN 'TR' THEN 'SQL DML trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
WHEN 'X' THEN 'Extended stored procedure'
ELSE 'Undefined'
END) AS [type]
,Obj.create_date
,Obj.modify_date
,src.referenced_minor_name AS [column]
,IIF(src.is_selected = 1,'yes','no') AS is_selected
,IIF(src.is_updated = 1,'yes','no') AS is_updated
,IIF(src.is_select_all = 1,'yes','no') AS is_select_all
,IIF(src.is_insert_all = 1,'yes','no') AS is_insert_all
FROM
sys.dm_sql_referenced_entities (@objname,@objclass) AS src
JOIN sys.objects AS Obj
ON src.referenced_id = Obj.[object_id]
JOIN sys.schemas AS Sch
ON Sch.[schema_id] = Obj.[schema_id]
WHERE 1=1
SELECT
CONCAT(Src.referencing_schema_name,'.',Src.referencing_entity_name) AS [name]
,(CASE Obj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
WHEN 'TR' THEN 'SQL trigger'
WHEN 'UQ' THEN 'UNIQUE constraint'
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'SQL scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'SQL inline table-valued function'
WHEN 'IT' THEN 'Internal table'
WHEN 'P' THEN 'SQL Stored Procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN 'PG' THEN 'Plan guide'
WHEN 'PK' THEN 'PRIMARY KEY constraint'
WHEN 'R' THEN 'Rule (old-style, stand-alone)'
WHEN 'RF' THEN 'Replication-filter-procedure'
WHEN 'S' THEN 'System base TABLE'
WHEN 'SN' THEN 'Synonym'
WHEN 'SO' THEN 'Sequence OBJECT'
WHEN 'U' THEN 'Table (user-defined)'
WHEN 'V' THEN 'VIEW'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'SQL table-valued-function'
WHEN 'TR' THEN 'SQL DML trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
WHEN 'X' THEN 'Extended stored procedure'
ELSE 'Undefined'
END) AS [type]
,Obj.create_date
,Obj.modify_date
FROM
sys.dm_sql_referencing_entities (@objname,@objclass) AS Src
JOIN sys.objects AS Obj
ON Obj.[object_id] = Src.referencing_id
</pre>
I have even compiled a stored procedure using this syntax and it can be found on the following reporsitory: <a href="https://github.com/manjukefernando/sp_depends_v2" title="https://github.com/manjukefernando/sp_depends_v2">https://github.com/manjukefernando/sp_depends_v2</a><br />
Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-2322794095992718652018-05-30T02:22:00.000+08:002018-05-30T02:28:39.964+08:00Computed columns in SQL Server<div dir="auto">
Computed columns are type of columns which the values are derived based on one or more other columns. Hence the data type on the computed column depends on the result of the derived column values.</div>
Computed columns is a feature which has been there in SQL Server since version 2000. But in my experience I feel that it has been a feature which's been used less compared to many other features available, and during discussions and interviews this is something which most developers slips or fails to answer.<br />
<br />
<h2>
Why do we need computed columns ?</h2>
First we will consider a case where we need to store details on a table without the usage of computed columns.<br />
Consider we have a table which contains employee details. We have two columns to store employee’s first and last names. But we also required to have a column which we need to store their full name as well by concatenating the first and last names. So the correct way is to have the third column which contains the full name and the data needs to be inserted during the employee record is created and it should be maintained in the case where the details are updated as well. Otherwise the data integrity will be lost. (One might debate that the full name can be built from the business logic code using the first and last names. But for the illustration purpose we would consider that we are maintaining it using SQL Server)<br />
<br />
<pre class="brush:sql">CREATE TABLE dbo.Employee(
Id INT
,FirstName VARCHAR(30)
,LastName VARCHAR(30)
,FullName VARCHAR(61)
)
</pre>
How ever we could achieve the same with the use of a computed column and with a less effort compared to the first approach.<br />
<br />
<pre class="brush:sql">CREATE TABLE dbo.Employee(
Id INT
,FirstName VARCHAR(30)
,LastName VARCHAR(30)
,FullName AS CONCAT(FirstName,' ',LastName)
)
</pre>
<br />
Let’s insert few records to the table which we created now.<br />
<br />
<pre class="brush:sql">INSERT INTO dbo.Employee(Id, FirstName, LastName)
VALUES (1,’John’,’Doe'),(2,’Jane’,’Doe')</pre>
<br />
<a href="https://lh3.googleusercontent.com/-rBclwhkTjhE/Ww2a0yXglwI/AAAAAAAAE3c/CxtSD3lU8aMrZuqd0XqIa4PEcUkdxpQrwCHMYCw/s1600-h/image%255B5%255D"><img alt="image" border="0" height="86" src="https://lh3.googleusercontent.com/-yE3TMRbXD-Y/Ww2a16hBkmI/AAAAAAAAE3g/oelFDpE4IO4guvZZh8Gpd0Nt3EoTnpDaACHMYCw/image_thumb%255B3%255D?imgmax=800" style="background-image: none; border: 0px currentcolor; display: inline;" title="image" width="664" /></a>
<br />
<h2>
PERSISTED, DETERMINISTIC or NON-DETERMINISTIC ?</h2>
The values reflected on computed column can be either deterministic or persisted.<br />
<div dir="ltr">
When the values are deterministic or non-deterministic the value in the column will not be saved on to the table physically. Instead it always calculated during the query execution. Hence the value could differ based on the functions you use in the formula. E.g: If you use GETDATE() in the calculated column, it will always return a different value during each execution.</div>
<br />
<pre class="brush:sql">CREATE TABLE dbo.Employee2(
Id INT
,FirstName VARCHAR(30)
,LastName VARCHAR(30)
,CreatedDate AS GETDATE()
)
INSERT INTO dbo.Employee2(Id, FirstName, LastName) VALUES
(1,'John','Doe') </pre>
<pre class="brush:sql"></pre>
<div dir="ltr">
<br />
And when queried the calculated column returns different values as shown below.<br />
<br /></div>
<div dir="ltr">
<a href="https://lh3.googleusercontent.com/-dU24Lnw9kRA/Ww2a2Z3ZCiI/AAAAAAAAE3k/wmbJAuZ2taYAllC3YXEW7U73o1OvNz0bgCHMYCw/s1600-h/image%255B11%255D"><img alt="image" border="0" height="144" src="https://lh3.googleusercontent.com/-ybXZiXIZLJk/Ww2a3FOChGI/AAAAAAAAE3o/HKVwX_1ObJMXp5sof0v50f5Ly04uFsewQCHMYCw/image_thumb%255B7%255D?imgmax=800" style="background-image: none; border: 0px currentcolor; display: inline;" title="image" width="665" /></a></div>
<div dir="ltr">
<strong><span style="color: blue;">**Note: The above mentioned can be achieved using a default constraint as well. I have used that example on strictly illustration basis.</span></strong></div>
<div dir="ltr">
You can further read on deterministic and non-deterministic function on the following Microsoft documentation.<br />
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017" target="_blank" title="https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017">https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017</a></div>
Computed column values can be persisted by adding the keyword PERSISTED when the column is created using T-SQL or by the table designer in SSMS.<br />
We will drop ‘FullName’ column and recreate the column.<br />
<br />
<pre class="brush:sql">ALTER TABLE dbo.Employee DROP COLUMN FullName;
ALTER TABLE dbo.Employee
ADD FullName AS CONCAT(FirstName,' ',LastName) PERSISTED;
</pre>
<strong><span style="color: blue;">**Note: If you try to drop the ‘CreatedDate’ column on Employee2 and try to create it as PERSISTED, it will throw an error. Because computed columns can only be persisted when it’s deterministic.</span></strong><br />
<span style="color: red;"><strong>Msg 4936, Level 16, State 1, Line 45<br />Computed column 'CreatedDate' in table 'Employee2' cannot be persisted because the column is non-deterministic.</strong></span><br />
Now when the expression is evaluated during the execution, the ‘FullName’ will be saved into the table.<br />
The data is read-only to the developer and it’s maintained by the engine. When the data is changed on the columns which was used in the formula, the computed values will be changed.<br />
<br />
<br />
<br />
<br />Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.comtag:blogger.com,1999:blog-1342610106694475714.post-55545868930115229392018-03-20T14:48:00.000+08:002018-03-20T14:49:50.334+08:00Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)<p>Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the demand. E.g: a bunch of products, transactions such as invoices, receipts etc. or customer details.<p>But today data has become an important entity, which drives business towards success. In today’s fast-moving world, companies who owned data and does analytics has become the most successful companies.<p>However one of the major concerns we have today is how to protect these data. Especially the sensitive ones. Since more data is being exposed to the cloud, it’s essential to protect it from going to the wrong hands and it has become a major problem since hackers nowadays are well equipped and are always on the look for stealing this valuable information whenever possible, since it’ll be a valuable asset in the open market.<p>But protecting the data from unauthorized access is a must. Failing to do so can have unexpected consequences. Entire business could get wiped out of the business due to this. Hence enterprises should seriously consider protecting their data and we will discuss how we can achieve this in SQL Server through data encryption.<h2>Ways of Data Encryption in SQL Server</h2><p>There are few ways of encrypting data in SQL Server. We will discuss the advantages and disadvantages of each method. </p>
<p>SQL Server provides following methods to encrypt data:</p><ul><li>T-SQL Functions</li><li>Using Symmetric Keys**</li><li>Using Asymmetric Keys**</li><li>Using Certificates**</li><li>Transparent Data Encryption**</li></ul><p><strong><font color="#cc0000">**Note : In this article I only plan to explain encryption/decryption functionality using T-SQL. I will talk about other methods which is mentioned about in future articles.<strong><font color="#cc0000"><br></font></strong></font></strong></p><h3>Using T-SQL Functions</h3><p><u><strong>Encrypting data using ENCRYPTBYPASSPHRASE</strong></u> </p><p>Encryption is done using T-SQL function ENCRYPTBYPASSPHRASE.</p><pre class="brush:sql">ENCRYPTBYPASSPHRASE(passphrase,text_to_encrypt)
</pre>
<p>The first parameter is the passphrase which can be any text or a variable of type NVARCHAR, CHAR, VARCHAR, BINARY, VARBINARY, or NCHAR. The function uses this passphrase to generate a symmetric key.<p>For the illustration purpose we will create a table which to hold employee details
<p><pre class="brush:sql">CREATE TABLE dbo.Employee(
Id INT
,EmpName VARCHAR(100)
,EmpDOB SMALLDATETIME
,SSN VARBINARY(128)
)
</pre>
<p>This example is to demonstrate the data encryption during INSERT DML Statement</p>
<p><pre class="brush:sql">INSERT INTO dbo.Employee(
Id
,EmpName
,EmpDOB
,SSN
)
VALUES(
1
,'Luke'
,'01-June-1980'
,ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','111-22-3333')
)
</pre>
<p><a href="https://lh3.googleusercontent.com/-p2EOlW8VJb8/Wq7pptuhSYI/AAAAAAAAEx4/SgyUp2i-rDclMB5dAZKIQPwE1HN0Abm6ACHMYCw/s1600-h/image%255B5%255D"><img width="636" height="104" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-b2IgCsbqN_Y/Wq7pq6fVRxI/AAAAAAAAEx8/RKjt8BkDog8G7W1fnTmSYnd6f8VHl3Q6gCHMYCw/image_thumb%255B3%255D?imgmax=800" border="0"></a></p><p>Further details can be found in the Microsoft Documentation:<br>
<a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql">https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql</a></p><p><br></p><p><strong><u>Decrypting data using T-SQL function DECRYPTBYPASSPHRASE</u></strong> </p><p>Will take the same details which we inserted during the first case. The encrypted data can be decrypted using SQL function DECRYPTBYPASSPHRASE. If any attempt has been made to decrypt the data without using DECRYPTBYPASSPHRASE nor providing the proper details, it will fail the operation.</p><p><u>Without Decryption</u></p>
<p><pre class="brush:sql">SELECT
Id,EmpName,EmpDOB,CONVERT(VARCHAR(128),SSN) AS SSN
FROM
dbo.Employee
WHERE
Id = 1
</pre>
<p><a href="https://lh3.googleusercontent.com/--CAkvvlepQQ/Wq7prmWYsgI/AAAAAAAAEyA/oeccZgAVzl49iMYUXMixCh_QJubT0HGAgCHMYCw/s1600-h/image%255B10%255D"><img width="640" height="72" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-Wdp-geWRbLw/Wq7pseUtK9I/AAAAAAAAEyE/LTTi8LDHRc4Lgc3PEhNMlK588zUYuyWBgCHMYCw/image_thumb%255B6%255D?imgmax=800" border="0"></a></p><p><br></p><p><u>With Decryption (Incorrect Pass-phrase)</u></p>
<p><pre class="brush:sql">SELECT
Id
,EmpName
,EmpDOB
,DECRYPTBYPASSPHRASE('IncorrectPassword',SSN ) AS SSN
FROM
dbo.Employee
WHERE
Id = 1</pre>
<p><a href="https://lh3.googleusercontent.com/-YMAJDvxULDY/Wq7ptG_fBQI/AAAAAAAAEyI/xULZRu97x_MGVsPq7wwbXJAkGq4YDu2BQCHMYCw/s1600-h/image%255B16%255D"><img width="642" height="75" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-LXx3ae2vraM/Wq7pt3SyutI/AAAAAAAAEyM/BwHXmMIF9W0r42dqzDQbQOVF1PLPeMVrgCHMYCw/image_thumb%255B10%255D?imgmax=800" border="0"></a></p><p>But providing the correct pass-phrase will return the correct details</p>
<p><pre class="brush:sql">SELECT
Id
,EmpName
,EmpDOB
,CONVERT(VARCHAR(128),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',SSN )) AS SSN
FROM
dbo.Employee
WHERE
Id = 1
</pre>
<p><a href="https://lh3.googleusercontent.com/-XMJCssCReMg/Wq7pu73LaAI/AAAAAAAAEyQ/ivNd4d6L9s87I2xfmogZNBGg8GfwBoggACHMYCw/s1600-h/image%255B22%255D"><img width="636" height="90" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-8S5u2NFI21A/Wq7pv0Fq2EI/AAAAAAAAEyU/f04qtBwikrQkz0G1btcilyfHQhUEeLPqQCHMYCw/image_thumb%255B14%255D?imgmax=800" border="0"></a></p><p>However there could be a requirement which you need to protect your data, not from stealing, but from getting updated with someone else’s.</p><p>One classic example is a login table. Suppose we have a table which stores login credentials, which is having the following structure. </p><p><strong><font color="#0000ff">*Note: In real world cases, usually it’s more secure if you hash passwords rather than encrypting them. But I am using encryption for illustration purpose.</font></strong></p><p>So if a person has access to update the details on the password column, he/she can easily replace the contents with their own and log on using that. This can be stopped by providing two additional values when details are inserted to the table using ENCRYPTPASSPHRASE.</p>
<p><pre class="brush:sql">CREATE TABLE dbo.LoginCredentails(
UserId INT
,UserName VARCHAR(20)
,Pwd VARBINARY(128)
)
</pre>
<p>We will insert two records to the above created table.</p>
<p><pre class="brush:sql">INSERT INTO dbo.LoginCredentails(
UserId
,UserName
,Pwd
)
VALUES
(1001,'luke.skywalker',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','force be with you',1,CAST(1001 AS sysname)))
,(1002,'darth.vader',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','i am your father',1,CAST(1002 AS sysname)))
</pre>
<p>Please note that unlike the previous example, we are now providing two additional values to the ENCRYPTBYPASSPHRASE function. The first values is 1, which indicates whether whether an authenticator will be encrypted together with the password. If the value is 1 and authenticator will be added. The second value is the data which from which to derive an authenticator. In this example we will use a value similar to a user id, so that when the value is decrypted, we could use the same value.</p><p>Following is a function to fetch the decrypted password based on the UserId. Assume we will be using this when validating the credential prior login.</p>
<p><pre class="brush:sql">CREATE FUNCTION Fn_GetUserPwdById(@UserId AS INT)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Pwd AS VARCHAR(50)
SELECT
@Pwd = CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',LC.Pwd,1,CAST(LC.UserId AS sysname)))
FROM
dbo.LoginCredentails AS LC
WHERE
LC.UserId = @UserId
RETURN @Pwd
END
</pre>
<p>Using the aforementioned function we will retrieve the details.</p>
<p><pre class="brush:sql">SELECT
UserId
,UserName
,dbo.Fn_GetUserPwdById(UserId) AS Pwd
FROM
dbo.LoginCredentails</pre><pre class="brush:sql"><br></pre><pre class="brush:sql"></pre>
<p><a href="https://lh3.googleusercontent.com/-vSmt1d6Og8c/WrA7iWg1YUI/AAAAAAAAEzI/qS36ZGAlDHorlJZfo1B_Nr9Q0wJUt8W0wCHMYCw/s1600-h/image%255B28%255D"><img width="636" height="122" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-iG2o1WGZ--c/WrA7jiVVqcI/AAAAAAAAEzM/ndMUQLUsAngeFbPnNa_zVMok1mlCkI1_wCHMYCw/image_thumb%255B18%255D?imgmax=800" border="0"></a></p><p>But querying the data simply will get you the binary string of the encrypted value.</p>
<p><pre class="brush:sql">SELECT
UserId
,UserName
,Pwd
FROM
dbo.LoginCredentails
</pre>
<p><br></p><p>Suppose if a person has enough privileges to do an update the password with a known one (from an existing user) it’ll allow him/her to login to the system impersonating any user. </p>
<p><pre class="brush:sql">UPDATE LC SET LC.Pwd = (
SELECT LC2.Pwd FROM dbo.LoginCredentails AS LC2
WHERE LC2.UserName = 'luke.skywalker'
)
FROM dbo.LoginCredentails AS LC
WHERE
LC.UserName = 'darth.vader'</pre><pre class="brush:sql"><br></pre><pre class="brush:sql"></pre>
<p><a href="https://lh3.googleusercontent.com/-I9gPvpSblKI/WrA7ksdXE3I/AAAAAAAAEzQ/1Ba9I0f1Zq4BJUrMMxpy1WUmCVZJu4EzACHMYCw/s1600-h/image%255B37%255D"><img width="662" height="47" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-U1zA4-ItyMI/WrA7lls8cHI/AAAAAAAAEzU/dXLVu6CUMUccEy9PXYVz63xDRh1JxviRgCHMYCw/image_thumb%255B23%255D?imgmax=800" border="0"></a></p><p>But if when the same function is used for decryption, it will return NULL for the updated record, preventing the login to be invalid if it’s replaced using an existing one.</p><p><a href="https://lh3.googleusercontent.com/-_fn3hWRtd9k/WrA7mkTT4FI/AAAAAAAAEzY/JC3HVWl4UoAKF8JLm2oze8jo0jr1Bfa6QCHMYCw/s1600-h/image%255B43%255D"><img width="665" height="94" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-8Lf9qNpDrhU/WrA7nk7foKI/AAAAAAAAEzc/j_FHIwjbbeoaU2t4EDFkg8_Sb8_BMv6kwCHMYCw/image_thumb%255B27%255D?imgmax=800" border="0"></a></p><p><p><strong><u><br></u></strong></p><strong><u>Hashing data using HASBYTES</u></strong><p>Apart from the above mentioned function, there’s another function which can be used to hash data. Unlike encrypting, there’s no way you can reverse the hashed data and see the raw details.</p><p><strong>Syntax:</strong></p>
<p><pre class="brush:sql">HASHBYTES ( 'algorithm'<algorithm>, { @input | 'input' } )
/*
<algorithm>algorithm::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512
*/
</algorithm></algorithm></pre>
<p>There are two parameters which you require to provide. The first parameter is the algorithm which should be used for hashing. The hashing algorithm can be any of the following:</p><ul><ul><ul><li>MD2</li></ul></ul><li>MD4</li><li>MD5</li><li>SHA</li><li>SHA1</li><li>SHA2_256</li><li>SHA2_512</li></ul><p>The second parameter is the input, which needs to be hashed. This can be either a character or binary string. </p><p>The return value is VARBINARY(n). n = maximum 8000 bytes.</p><p><strong>Example:</strong></p>
<p><pre class="brush:sql">DECLARE
@TextToHash AS NVARCHAR(1000) = N'My Secret Message'
SELECT HASHBYTES('SHA1',@TextToHash) AS HashedData
</pre>
<p><br></p><p><a href="https://lh3.googleusercontent.com/-hkoLg3eR5FI/WrCvB-GP08I/AAAAAAAAEz4/lGJs9aJrP9s9OD2MOft6Nsv1Kr23IHufwCHMYCw/s1600-h/image%255B4%255D"><img width="653" height="106" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-3bVdYeZZd34/WrCvDEAoZLI/AAAAAAAAEz8/8yJMLc4WzlwhKrulr_s7M5WiKL8NXNQAwCHMYCw/image_thumb%255B2%255D?imgmax=800" border="0"></a></p><p><br></p>Further details can be found in the Microsoft Documentation:<br><a title="https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql" href="https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql">https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql</a><p><br></p><p>Hope this might be useful to you and please feel free to comment your ideas.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com1tag:blogger.com,1999:blog-1342610106694475714.post-29281855684870303132018-03-10T05:56:00.001+08:002018-03-10T05:56:57.236+08:00Strange behaviour converting NVARCHAR(MAX) to BINARY<p>Few days back I was writing a CLR function to be used for hashing string values. The only option was the CLR functions since T-SQL doesn’t have any functionality to convert a string to hashed value using a key. Using the HASHBYTES function you can only provide the algorithm.</p><p><pre class="brush:sql">DECLARE @Data NVARCHAR(4000);
SET @Data = CONVERT(NVARCHAR(4000),'My Secret Message');
SELECT HASHBYTES('SHA1', @Data);
</pre><p>I have written the CLR function to achieve the requirement, but during testing the validation was failing and when I go through the code I couldn’t find any issue in the function as well. But inspecting carefully I noticed that when a variable type NVARCHAR(n) and a variable type of NVARCHAR(MAX) gives different results when it’s converted to Binary. Which was the root cause for the issue I was facing.</p><p><br></p><p><pre class="brush:sql">DECLARE
@Data1 AS NVARCHAR(MAX) = '1111'
,@Data2 AS NVARCHAR(10) = '1111'
SELECT
CAST(@Data1 AS BINARY(30)) AS ValueMax
SELECT
CAST(@Data2 AS BINARY(30)) AS ValueN</pre><p><br></p><p><a href="https://lh3.googleusercontent.com/-gwnOhP2epY0/WqMDI5poHNI/AAAAAAAAEw4/hvaFC8bzbHAt-5oho2z065RezmMOE2MQACHMYCw/s1600-h/image_thumb12"><img width="510" height="225" title="image_thumb1" style="margin: 0px; border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image_thumb1" src="https://lh3.googleusercontent.com/-BjqpqKGpRKU/WqMDJxN8b2I/AAAAAAAAEw8/5KpsOjOC8mcWktrvZI-8e2QdUxBaA9yEgCHMYCw/image_thumb1_thumb?imgmax=800" border="0"></a></p><p>As you can see the above example the zero bytes are represented differently for NVARCHAR(MAX) when it’s converted to BINARY.</p><p>I do not have any explanation for this. I am sharing the information in case anyone come across this issue. Please feel free to comment.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com1tag:blogger.com,1999:blog-1342610106694475714.post-64216277737432298472017-12-03T16:50:00.000+08:002017-12-03T16:51:34.543+08:00Behaviour of IDENTITY Columns and SEQUENCES with TRANSACTIONS<p>Few days back, I was caught in a discussion with couple of my colleagues, regarding a problem they are facing with an IDENTITY column. </p><p>The issue was that when a transaction is rolled back the identity seed isn’t rolling back as expected. This was causing the business application to loose the id sequence.</p><p>There is no fix or a workaround for this. All that I could provide was an explanation.</p><p>I will illustrate the issue and an explanation why it’s happening.</p><p><strong><u>Behaviour of IDENTITY Columns</u></strong></p><p>We will create the following table to hold employee details.</p>
<p><pre class="brush:sql">CREATE TABLE dbo.EmployeeInfo(
Id INT IDENTITY(1,1) NOT NULL,
EmpName VARCHAR(100) NOT NULL
)
</pre>
<p><br></p><p>Now we will insert few records to the table in the following manner.</p><ol><li>Without a transaction</li><li>With a transaction. But we will rollback the transaction.</li><li>With a transaction. But we will commit it.</li></ol><p><br></p>
<p><pre class="brush:sql">INSERT INTO dbo.EmployeeInfo (EmpName)
VALUES('John')
BEGIN TRAN
INSERT INTO dbo.EmployeeInfo (EmpName)
VALUES('Jane')
ROLLBACK
INSERT INTO dbo.EmployeeInfo (EmpName)
VALUES('James')
SELECT
EI.Id
,EI.EmpName
FROM
dbo.EmployeeInfo AS EI
</pre>
<p><br></p><p>And when checked, you could see the following results.</p><p><a href="https://lh3.googleusercontent.com/-nz_EwiD8pb0/WiO7BwA2QWI/AAAAAAAAEs4/y-NSixr16UU2h4HA2V1xWEeXEhJjgSkiACHMYCw/s1600-h/image%255B10%255D"><img width="310" height="143" title="image" style="border: 0px currentcolor; border-image: none; display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/-Obz9J1_xSlo/WiO7Cf4QvPI/AAAAAAAAEs8/hUEbVEeNtysyakZ5tAElgVUp3KlxS4x4QCHMYCw/image_thumb%255B8%255D?imgmax=800" border="0"></a></p><p>Usually the expectation is to see the employee “James” with an Id of 2.</p><p>What you should understand here is that this isn’t a flaw or a bug. This is the exact intended behaviour and it has been explained in the following MSDN article.</p><p><a title="https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property" href="https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property">https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property</a></p><p><a href="https://lh3.googleusercontent.com/-XIsJIGn-iN8/WiO7DEOAZFI/AAAAAAAAEtA/7mFqGXWc9iEz9EocmhHX0yA67ee0xep2QCHMYCw/s1600-h/image%255B15%255D"><img width="772" height="138" title="image" style="display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/--o3AQPPqEiA/WiO7EMAxJ4I/AAAAAAAAEtE/LYpQ5cmt3JsdhuEx2jQ-ckZRHxu7iK0nACHMYCw/image_thumb%255B11%255D?imgmax=800" border="0"></a></p><p><br></p><p><strong><u>Behaviour of SEQUENCES</u></strong></p><p>SEQUENCEs were introduced in SQL Server 2012. The purpose of the SEQUENCE objects were to aid in handling the auto increment numbers, in case you prefer to handle the sequence without using an IDENTITY column.</p><p>First we will create a sequence object. The minimum syntax required to create a sequence object is a name and the data type. Additionally you can mention many other attributes like starting index, increment seed etc.</p>
<p><pre class="brush:sql">CREATE SEQUENCE dbo.TempNumberSequence AS INT
</pre>
<p>Further details regarding other options can be found on the following URL:</p><p><a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql">https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql</a></p><p><br></p><p>Now we will create a similar table like we created in the previous example, but without an IDENTITY column.</p>
<p><pre class="brush:sql">CREATE TABLE dbo.EmployeeInfoSeq(
Id INT
,EmpName VARCHAR(100) NOT NULL
)
</pre>
<p>We will insert 3 records in the same way like we did in the previous example.</p>
<p><pre class="brush:sql">DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
INSERT INTO dbo.EmployeeInfoSeq (
Id
,EmpName
)
VALUES (
@NextSeq
,'John'
)
GO
DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
BEGIN TRAN
INSERT INTO dbo.EmployeeInfoSeq (
Id
,EmpName
)
VALUES (
@NextSeq
,'Jane'
)
ROLLBACK
GO
DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
INSERT INTO dbo.EmployeeInfoSeq (
Id
,EmpName
)
VALUES (
@NextSeq
,'James'
)
GO
</pre>
<p>Afterwards if you check, you will see the following results.</p><p><a href="https://lh3.googleusercontent.com/-my-NQHjMs_k/WiO7EutEhuI/AAAAAAAAEtI/H-lnz_923lEqcXaq03vfSQNmVp8jO0B1wCHMYCw/s1600-h/image%255B19%255D"><img width="290" height="157" title="image" style="display: inline; background-image: none;" alt="image" src="https://lh3.googleusercontent.com/--eIzxZsIGNQ/WiO7FYQ8TLI/AAAAAAAAEtM/dT_KvNosYTY2kRXRXdk4cthCcY24E8oqACHMYCw/image_thumb%255B13%255D?imgmax=800" border="0"></a></p><p>Hope this will help you in you day to day development work.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-45369478464974809942016-10-18T22:15:00.000+08:002016-10-19T17:16:34.692+08:00Always Encrypted feature in SQL Server 2016<p>With the introduction of SQL Server 2016 in June (Official Final Release), Microsoft had introduced few, new and very useful features in to the SQL Server. One such feature is the ‘Always Encrypted’.</p> <p>‘Always Encrypted’ is the ability to perform SQL operations (there are restrictions) on your data as it were normal (non encrypted), while keeping them encrypted all the time. This means SQL Server will always get encrypted data to be stored into the tables. This will put an extra layer of protection on to your data making sure that even onsite DBA’s or Developers cannot see the plain text value behind the encrypted data using their level of access. (Users with ‘SysAdmin’ access won’t be able to see these details without the Key). Therefore ‘Always Encryption’ provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).</p> <h2> </h2> <h2>Why Always Encrypted ?</h2> <p>There are many benefits using Always Encrypted feature:</p> <ul> <li>It provides a clear separation between the data owners and people who manage it <li>Unless proper access is provided via encryption keys, even DBA’s or SysAdmin users cannot access the data in plain text</li></ul> <p>Ultimately aforementioned points will provide an unparalleled protection against data breaches and help to protect sensitive information such as credit card numbers, personal details etc. Also this will broaden the boundaries where such sensitive information can be kept. </p> <p> </p> <h2>How Always Encrypted Works ?</h2> <p>This is a client-side encryption technology which the SQL Server Client Driver plays a key role.</p> <p><a href="https://lh3.googleusercontent.com/-E0MIY0drlvY/WAOy82dSF_I/AAAAAAAAEBo/GEzTSflKlz4/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-xuHwU4mI0CE/WAOy9cu53_I/AAAAAAAAEBs/Q706V7wnBjQ/image_thumb%25255B1%25255D.png?imgmax=800" width="830" height="176"></a></p> <ul> <li>The data is transparently encrypted inside a client driver <li>Client manages the encryption key. SQL Server doesn’t have any information regarding the encryption key.</li></ul> <p>SQL Server can query and perform certain computations on the encryption data, such as equality comparisson, equality joins, group by etc.</p> <p> </p> <h2>Always Encrypted Demonstration</h2> <p>We will see how Always Encrypted can be implemented and used. In order to illustrate, we will use a table which contains employee information.</p> <p><pre class="brush:sql">CREATE TABLE Employee(
Id INT
,FirstName VARCHAR(100)
,LastName VARCHAR(100)
,DOB DATE
,SSN INT
,[Address] VARCHAR(255)
,PostalCode INT
)
INSERT INTO Employee (
[Id],[FirstName],[LastName],[DOB],[SSN],[Address],[PostalCode])
VALUES
(1,'James','Rubin','20-Jul-1986',173456858,'10585 N 600 E',46310)
,(2,'Austin','Pyatt','24-Dec-1985',138868248,'100 BENTBROOK CT',27519)
,(3,'Stacey','Munoz','23-Dec-1988',185682639,'1 WOODSIDE DR',4976)
,(4,'James','Tweed','03-Jan-1987',133890886,'1 AUNNEK CT',95023)
,(5,'James','Robles','11-Sep-1989',154135505,'101 FISHTRAP RD',35504)
,(6,'Ebony','Lewis','17-Jul-1988',120488337,'101 N OAKS DR',35180)
,(7,'Marian','Caro','20-Nov-1985',115281829,'1017 FISK ST SE',49507)
,(8,'Lynne','Martinez','22-Apr-1985',157900240,'103 UNITY CT',78214)
,(9,'Elsa','Cole','25-Apr-1990',150631885,'1001 E FERN AVE APT 201',78501)
,(10,'Kiley','Caldwell','03-Jan-1988',131368172,'103 NOB HILL LN APT 5',40206)
,(11,'Michael','Soluri','17-Jun-1985',173245124,'10770 S KILBOURN AVE',60453)
,(12,'Gregory','Emmons','06-Sep-1988',137693229,'10 LOUISA PL APT 2F',7086)
,(13,'Jessica','Barr','04-Feb-1989',155895863,'1 FAWNRIDGE DR',94945)
,(14,'Daniel','Mccabe','06-Sep-1985',148236776,'1 CALLE MARGINAL GARCIA',674)
,(15,'Sharon','Schwartz','06-Sep-1987',117569460,'1 KRITTER CT',8050)
,(16,'Dorthy','Wear','13-Dec-1988',170517705,'1 CLARK RD',35747)
,(17,'Betsy','Blansett','17-Jun-1990',182202498,'10 CALLE 1 DE FLORIDA',612)
,(18,'Margaret','Payne','25-Jul-1985',157359609,'1003 BLOOMFIELD AVE',7006)
,(19,'James','Walker','26-Jan-1989',142829150,'100 CONGLETON HOLLOW SPUR RD',40447)
,(20,'Sarah','Reeves','22-Jun-1990',146171169,'1 BLUEBERRY LN',1832)
</pre>
<p> </p>
<p>I have a small MVC Web Application which has a page to list out the aforementioned details from the SQL Server. The MVC Controller will load the details to a list of Employee records and pass it to the Html view which will be displayed as follows.</p>
<p><a href="https://lh3.googleusercontent.com/-IpPhR7rWJJw/WAav73N-IVI/AAAAAAAAEC8/r4Dj4ryaFgs/s1600-h/image4.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-Y2JfMJr9shM/WAav8qACDxI/AAAAAAAAEDA/LgRwBsZJ-hI/image_thumb1.png?imgmax=800" width="731" height="525"></a></p>
<p>In the MVC application I have the following data model to load details from the SQL Database Table.</p>
<p><pre class="brush:csharp">public class Employee {
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DOB { get; set; }
public int SSN { get; set; }
public string Address { get; set; }
public int PostalCode { get; set; }
public Employee() {
}
}
</pre>
<p>And I am using the following connection string in order to connect to the SQL Server Database.</p>
<p><pre class="brush:csharp"> const string zConnectionString =
@"Server=.\SQL2K16; Network Library=DBMSSOCN;Database=SQLTraining;Trusted_Connection=True;";
</pre>
<p> </p>
<p>There are few steps to be followed on both SQL Server and application side (Client Applications) in order to implement and use this feature.</p>
<p>From the SQL Server side, there are few ways to enable the Always Encrypted feature. We will look more details how to use these feature using the wizard.</p>
<p> </p>
<p>1. Right click the table which you want to encrypt details and select ‘Encrypt Columns’. This will take you the to wizard.</p>
<p><a href="https://lh3.googleusercontent.com/-beZ1L5sfE8U/WAX7qYorf8I/AAAAAAAAECA/gnjVCqvUuYw/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-G75AE8mEOiY/WAX7q3rU5YI/AAAAAAAAECE/4JZxRNrUfNg/image_thumb%25255B4%25255D.png?imgmax=800" width="402" height="612"></a></p>
<p> </p>
<p>2. You will get the introduction screen which contains few details about what ‘Always Encrypted’ is all about. Click next and proceed to the next screen.</p>
<p><a href="https://lh3.googleusercontent.com/-EJLn1-8L_lM/WAX7rQsG9AI/AAAAAAAAECI/6PecJwTmtic/s1600-h/image%25255B12%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-sKHrbhOiO9Y/WAX7rzd5YmI/AAAAAAAAECM/18zc9YWzbZk/image_thumb%25255B6%25255D.png?imgmax=800" width="576" height="535"></a></p>
<p>This is the column selection screen, which allows you to select which columns you want to encrypt and using which Encryption Type. There are two Encryption Types available in SQL Server 2016.</p>
<ul>
<li><strong>Deterministic</strong> –> Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.</li></ul>
<p> </p>
<ul>
<li> <strong>Randomized</strong> –> Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.</li></ul>
<p>This advice has been included in Microsoft Documentation: Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records and are not used to join tables.</p>
<p>So in our example we will choose DOB & SSN columns for encryption. For DOB we will choose Randomized and for SSN we will choose Deterministic.</p>
<p>Once the encryption type is chosen the wizard should be similar to the screen shown below.</p>
<p><a href="https://lh3.googleusercontent.com/-VdbFUZtx2no/WAX7sb5Lv1I/AAAAAAAAECQ/wWR1QfULFmI/s1600-h/image%25255B18%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-vuu7XZ73tWA/WAX7sx5XazI/AAAAAAAAECU/kk3RB_ZPN-A/image_thumb%25255B10%25255D.png?imgmax=800" width="582" height="541"></a></p>
<p> </p>
<p>If you look closely, you will be able to see that the Encryption Key combo is disabled. The reason for this is the fact that we haven’t created any Column encryption keys so far. If the keys are created prior to the column selection then you will have the option to choose whether to use an existing key or to generate a new key.</p>
<p><a href="https://lh3.googleusercontent.com/-g-r14QHhmOQ/WAX7tfsAaKI/AAAAAAAAECY/gRDW2B2yw3s/s1600-h/image%25255B22%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-Az2zAxvmmKQ/WAX7t9VqdGI/AAAAAAAAECc/kFqinIDosEA/image_thumb%25255B12%25255D.png?imgmax=800" width="590" height="548"></a></p>
<p>In this illustration, we will use the option which will create a new column encryption key. Click next to proceed to the next step.</p>
<p>3. The next step is the Column Master Key Configuration. A Column Master Key will be used to encrypt and protect the Column Encryption Key, which is used to encrypt the data. We will use the option ‘Auto generated column master key’, which the wizard will generate the key for us. When we are creating a new Master Key, there are two options available, where to store the newly generated key. Clicking on the small info button beside each option will give further details about each option</p>
<p><a href="https://lh3.googleusercontent.com/-FbU87zuaakY/WAav9KNiVQI/AAAAAAAAEDE/guHn0PEmGyA/s1600-h/image8.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-aoHnRI0nvaA/WAav9n7cYOI/AAAAAAAAEDI/sRnq1T5EuFc/image_thumb3.png?imgmax=800" width="594" height="552"></a></p>
<p> </p>
<p>4. Click next to move to the next step. In this step you can decide whether you require a PowerShellscript to be generated for the encryption process or to proceed with the encryption immediately. In this example we will select the second option and click on the next button.</p>
<p> </p>
<p><a href="https://lh3.googleusercontent.com/-chckVrcicQk/WAav-BwreOI/AAAAAAAAEDM/nHgBezZWA5M/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-tPkd2CAvpn4/WAav-g-RRhI/AAAAAAAAEDQ/FAA3j7x92ss/image_thumb%25255B2%25255D.png?imgmax=800" width="596" height="542"></a></p>
<p>In this step you will be presented with the steps which will be followed during the data encryption</p>
<p><a href="https://lh3.googleusercontent.com/-UWmC3i9Q15Q/WAav_A4F5SI/AAAAAAAAEDU/3Q-_LcUwQt0/s1600-h/image%25255B10%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-t7i-r-B3W2k/WAav__e6_XI/AAAAAAAAEDY/iVV0X-5Xpx0/image_thumb%25255B5%25255D.png?imgmax=800" width="597" height="542"></a></p>
<p>Click finish to complete the encryption process. Once process is completed click close button.</p>
<p><a href="https://lh3.googleusercontent.com/-S0GI39Aciwg/WAc5ERc0MuI/AAAAAAAAEDs/Kag52dmWMkg/s1600-h/image%25255B4%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-ikrSN7wT00E/WAc5Ey3eizI/AAAAAAAAEDw/li_Q4cbZop8/image_thumb%25255B2%25255D.png?imgmax=800" width="602" height="547"></a></p>
<p> </p>
<p>Now if you check the details on SQL Table you can see that, data in SSN and DOB columns are encrypted.</p>
<p><pre class="brush:sql">SELECT * FROM dbo.Employee
</pre>
<p><a href="https://lh3.googleusercontent.com/-kN071EP-Zec/WAc5FVKMfII/AAAAAAAAED0/e3Kcz9KDbNk/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-yD5LeN9HJ4E/WAc5GF_kjUI/AAAAAAAAED4/0Wecs-HbiwY/image_thumb%25255B4%25255D.png?imgmax=800" width="729" height="197"></a></p>
<p>If you see the Table creation script for the Employee table now, you could see few changes which has been done by the SQL Server after we enabled the encryption for those two columns.</p>
<p><pre class="brush:sql">CREATE TABLE [dbo].[Employee](
[Id] [INT] NULL,
[FirstName] [VARCHAR](100) NULL,
[LastName] [VARCHAR](100) NULL,
[DOB] [DATE] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[SSN] [INT] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Address] [VARCHAR](255) NULL,
[PostalCode] [INT] NULL
) ON [PRIMARY]
</pre>
<p>You can see that it had added the ENCRYPTED WITH clause for those two columns. ENCRYPTED WITH clause consist 3 attributes which are:</p>
<ul>
<li>COLUMN_ENCRYPTION_KEY –> CEK_Auto1 since we have chosen the option for SQL to generate a new key.
<li>ENCRYPTION_TYPE –> Can be either RANDOMIZED or DETERMINISTIC
<li>ALGORITHM –> This is always AES_256</li></ul>
<p>If you inspect the Always Encrypted keys in the object explorer in SSMS you could see the following meta data for the Master and the Column Encrypted Keys.</p>
<p><a href="https://lh3.googleusercontent.com/-d9MSU8VAh-I/WAc5GfBbS4I/AAAAAAAAED8/P3xwnel5L8I/s1600-h/image%25255B12%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-01A1idSiYyk/WAc5G_emG5I/AAAAAAAAEEA/sEb6OWDwQRU/image_thumb%25255B6%25255D.png?imgmax=800" width="352" height="348"></a></p>
<p> </p>
<p><strong><u>Column Encrypted Key – CEK_Auto1</u></strong></p>
<p><a href="https://lh3.googleusercontent.com/-B05Nd-0hT60/WAc5HVeW57I/AAAAAAAAEEE/3Kv7i29GC5Q/s1600-h/image%25255B16%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-a8ncisGqC7k/WAc5HyEACOI/AAAAAAAAEEI/7PqqwkgnxAQ/image_thumb%25255B8%25255D.png?imgmax=800" width="839" height="199"></a></p>
<ul>
<li><strong>COLUMN_MASTER_KEY</strong> –> Name of the column master key protecting the value of the column encryption key.
<li><strong>ALGORITHM</strong> –> Algorithm used to generate the encrypted value of the column encryption key (RSA_OAEP).
<li><strong>ENCRYPTED_VALUE</strong> –> Encrypted value of the column encryption key. The encrypted value is assumed to be produced by encrypting the plaintext of the column encryption key using the specified column master key and the specified algorithm.</li></ul>
<p>For further information please refer to the following url: <a title="https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/" href="https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/">https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/</a></p>
<p> </p>
<p><strong><u>Column Master Key - CMK_Auto1</u></strong></p>
<p><a href="https://lh3.googleusercontent.com/-nPm0rq9cnAc/WAc5IM26YaI/AAAAAAAAEEM/kydPGX5ws68/s1600-h/image%25255B20%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-11WHQ_bvobI/WAc5Iv71ZlI/AAAAAAAAEEQ/klmUXKZ_MVM/image_thumb%25255B10%25255D.png?imgmax=800" width="848" height="219"></a></p>
<ul>
<li><strong>KEY_STORE_PROVIDER_NAME</strong> –> Name of a provider for the key store that holds the column master key.
<li><strong>KEY_PATH</strong> –> Key path specifying the location of the column master key in the key store.</li></ul>
<p> </p>
<p>For further information please refer to the following url: <a title="https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/" href="https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/">https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/</a></p>
<p> </p>
<p>Now if we try to fetch details without doing anything on the sample .Net Application you will get a similar error like shown below.</p>
<p><a href="https://lh3.googleusercontent.com/-8lha690qQg4/WAc5JFAyQsI/AAAAAAAAEEU/69XLXrgxLwk/s1600-h/image%25255B24%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-FGZrtCwXDKE/WAc5JzFqSkI/AAAAAAAAEEY/SIKhtdv4sxE/image_thumb%25255B12%25255D.png?imgmax=800" width="853" height="368"></a></p>
<p>Now we will look into the things that we required to change on our application side (Business) in order to retrieve the required information.</p>
<p><strong>1. Make sure that the target framework is version 4.6 or higher.</strong></p>
<p><a href="https://lh3.googleusercontent.com/-taHyuqjlh0g/WAc5KBiqlrI/AAAAAAAAEEc/DW8pKsTLa4k/s1600-h/image%25255B28%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-WxRHrOMdu8U/WAc5KnbzyHI/AAAAAAAAEEg/FX5L5k_9wtQ/image_thumb%25255B14%25255D.png?imgmax=800" width="659" height="449"></a></p>
<p> </p>
<p><strong>2. In the Connection String include ‘Column Encryption Setting=enabled’</strong></p><pre><strong></strong></pre>
<p>And I am using the following connection string in order to connect to the SQL Server Database.</p>
<p><pre class="brush:csharp"> const string zConnectionString =
@"Server=.\SQL2K16; Network Library=DBMSSOCN;Database=SQLTraining;Trusted_Connection=True;Column Encryption Setting=enabled;";
</pre>
<p> </p>
<p>Now if we check the details from our application we can see that DOB and SSN values are fetched as plain text, even though the values are encrypted in the SQL Server.</p>
<p><a href="https://lh3.googleusercontent.com/-lCWM6JjNEmM/WAc5LYa777I/AAAAAAAAEEk/HZtVuj-584k/s1600-h/image%25255B34%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-6N65IuaXxEo/WAc5MOVKKeI/AAAAAAAAEEo/-F95IHI1WP4/image_thumb%25255B18%25255D.png?imgmax=800" width="787" height="611"></a></p>
<p><a href="https://lh3.googleusercontent.com/-sjEqff4Iwas/WAc5MprFOkI/AAAAAAAAEEs/Rfjdiv2mr9E/s1600-h/image%25255B39%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-M-NWS1VQ6lI/WAc5Na2CkqI/AAAAAAAAEEw/scJ9SByoj7k/image_thumb%25255B21%25255D.png?imgmax=800" width="791" height="250"></a></p>
<p>Hope this will help you to understand the ‘Always Encrypted’ feature in SQL Server 2016 and how to integrate it to an existing application.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com1tag:blogger.com,1999:blog-1342610106694475714.post-38087191744105919172016-10-09T00:36:00.000+08:002016-12-04T21:50:29.533+08:00Understanding JOINs in SQL ServerDuring my work I get the chance reviewing lots of T-SQL Procedures and Views and I often see that the SQL joins are mis-used in them. When I enquire the developers regarding this, it’s evident that most of the time it has been the case that they don’t have the proper understanding what each JOIN exactly does or how it behaves, ultimately causing the SQL Procedure or the View to return an unexpected resultset. Therefore I thought of writing this blog post.<br />
When we require to fetch details from multiple tables the JOIN caluse is there for the rescue. But in SQL Server there are various types of JOINs which will cater our requirement in different ways. So it’s very important to have a good understanding in these types of JOINs and their usage.<br />
In SQL Server following types of JOINs available.<br />
<ul>
<li>INNER JOIN</li>
<li>OUTER JOIN<ul>
<li>LEFT OUTER JOIN</li>
<li>RIGHT OUTER JOIN</li>
<li>FULL OUTER JOIN</li>
</ul>
</li>
<li>CROSS JOIN</li>
<li>CROSS APPLY</li>
<li>OUTER APPLY</li>
</ul>
We will look into the afrementioned JOINs more closely. The scope of this article is to give a high-level idea on the aforementioned JOINs and the APPLY operator in SQL Server.<br />
To illustrate the aforementioned JOINs I will use the following sample tables:<br />
<ul>
<li><strong>SalesRep</strong></li>
<li><strong>SalesDetails</strong></li>
<li><strong>RepRating</strong></li>
<li><strong>Settings</strong></li>
</ul>
We consider a case where we have 5 Sales Reps and the details will be saved in ‘RepDetails’ table and the sales transactions which they have done is recorded under ‘SalesDetails’ table. In the SalesDetails table we have included few transactions which we don’t have a matching Sales Rep. Similarly in the RepDetails table there are couple of sales reps which we don’t have any sales infromation.<br />
<br />
<pre class="brush:sql">--== Create Tables ==--
CREATE TABLE RepDetails(
RepId INT
,RepName VARCHAR(30)
)
CREATE TABLE SalesDetails(
RepId INT
,SaleMonth VARCHAR(6)
,OrderNo VARCHAR(6)
,SaleValue MONEY
)
CREATE TABLE RepRating(
RepId INT
,Rate INT
,YearMonth VARCHAR(6)
)
CREATE TABLE Settings(
S_Id INT
,S_Desc VARCHAR(20)
,S_Value VARCHAR(20)
)
--== Populate Sample Data ==--
INSERT INTO RepDetails (
[RepId]
,[RepName]
) VALUES
(1,'Eugene Thomas')
,(2,'John Wheeler')
,(3,'Curtis Bailey')
,(4,'Jeffrey Garrett')
,(5,'Rosemarie Hubbard')
INSERT INTO SalesDetails (
[RepId]
,[SaleMonth]
,[OrderNo]
,[SaleValue]
)
VALUES
(7,'201607','XpyDy3',839)
,(1,'201607','NR0RTp',496)
,(4,'201607','4552T4',299)
,(6,'201607','GKhkyC',877)
,(4,'201606','iyK65Z',291)
,(6,'201606','NFCszW',446)
,(7,'201606','D238bN',135)
,(1,'201607','bERDXk',304)
,(7,'201608','nykZqB',935)
,(4,'201608','R7ea5v',352)
,(6,'201606','VVjIdo',407)
,(7,'201608','vtLT4z',977)
,(2,'201608','xnHTnO',416)
,(1,'201606','jFAJIm',674)
,(6,'201606','0Q011m',480)
INSERT INTO dbo.RepRating(
RepId
,Rate
,YearMonth
)
VALUES
(1,1,'201608')
,(3,2,'201608')
,(4,1,'201609')
,(2,2,'201609')
INSERT INTO dbo.Settings(
S_Id
,S_Desc
,S_Value
)
VALUES
(1,'LedgerMonth','201609')
,(2,'TaxRate','10%')
</pre>
<br />
<strong><span style="color: red;">**Note: During the illustraion I will refer the table which is followed by the ‘FROM’ clause as the ‘Left Table’ and the table which is follwed by the JOIN clause as the ‘Right Table’.</span></strong><br />
<strong><span style="color: red;"></span></strong><br />
<h2>
<span style="font-size: large;">INNER JOIN / JOIN</span></h2>
When we join two or more tables using an INNER JOIN, it will only return us the results when records can only be found on both left and right tables which will satisfy the condition we supply.<br />
<a href="https://lh3.googleusercontent.com/-zl_a5GXN2lk/V-z1-T__bJI/AAAAAAAAD88/CYyyv-6Wetk/s1600-h/image%25255B3%25255D.png"><img alt="image" border="0" height="142" src="https://lh3.googleusercontent.com/-9bHlKCZHxHQ/V-z1_N9-XkI/AAAAAAAAD9A/0TRAG6Xl-u8/image_thumb%25255B1%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="853" /></a><br />
<br />
<br />
This can be illustrated using a venn diagram as follows:<br />
<a href="https://lh3.googleusercontent.com/-El2dImiftjE/V-1APaF5XzI/AAAAAAAAD9k/w5a8Z6hlOjo/s1600-h/image%25255B3%25255D.png"><img alt="image" border="0" height="227" src="https://lh3.googleusercontent.com/-nPWxDRm0DFw/V-1AQILbOOI/AAAAAAAAD9o/CLbLMMRN8gw/image_thumb%25255B1%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="323" /></a><br />
<br />
<pre class="brush:sql">SELECT *
FROM
dbo.RepDetails AS RD
JOIN dbo.SalesDetails AS SD
ON SD.RepId = RD.RepId
</pre>
<br />
<a href="https://lh3.googleusercontent.com/-EueDsy9Gg8E/V-1ARfXpYUI/AAAAAAAAD9s/NnEBo2I3zbQ/s1600-h/image%25255B7%25255D.png"><img alt="image" border="0" height="189" src="https://lh3.googleusercontent.com/-5EWA_vnKF0k/V-1ARwBlQpI/AAAAAAAAD9w/aWaS27D8PyU/image_thumb%25255B3%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="709" /></a><br />
<strong><span style="color: blue;"><br /></span></strong> <strong><span style="color: blue;">**Please note: We have sales reps having RepId’s 1,2,3,4, & 5. But in SalesDetails table we have sales details for RepId’s 1,2,4,6 &7. So when these tables are joined the RepId’s which resides on both tables, which are 1,2, and 4 will return the details, ultimately giving us the aforementioned result set.</span></strong><br />
<br />
<h2>
<span style="font-size: large;">LEFT OUTER JOIN / LEFT JOIN</span></h2>
In a LEFT OUTER JOIN, unlike the INNER JOIN, it will select all the records from the ‘Left’ table and based on the JOIN condition, it will select any matching records from the ‘Right’ table and return us the results. If there are no matching details on the ‘Right’ table, columns on related to those rows will return as ‘NULL’.<br />
<a href="https://lh3.googleusercontent.com/-GWpcoH64bZ8/V_IxyIidM2I/AAAAAAAAD-c/SiCuefhop5g/s1600-h/image%25255B4%25255D.png"><img alt="image" border="0" height="140" src="https://lh3.googleusercontent.com/-2aQvikctEzU/V_IxyqdGXmI/AAAAAAAAD-g/fs4o16spk2Y/image_thumb%25255B1%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="865" /></a><br />
<br />
This can be shown using a venn diagram as follows:<br />
<a href="https://lh3.googleusercontent.com/-vaqgVwbDoKg/V_Ixy43X7FI/AAAAAAAAD-k/D5C7bqi6Kv4/s1600-h/image%25255B12%25255D.png"><img alt="image" border="0" height="228" src="https://lh3.googleusercontent.com/-WVsgwr5Rv38/V_IxzUIfzNI/AAAAAAAAD-o/A9DumoydlNk/image_thumb%25255B5%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="332" /></a><br />
<br />
<pre class="brush:sql">SELECT *
FROM
dbo.RepDetails AS RD
LEFT JOIN dbo.SalesDetails AS SD
ON SD.RepId = RD.RepId
</pre>
<br />
<a href="https://lh3.googleusercontent.com/-KhrI96UMuGA/V_IxzhZ2F9I/AAAAAAAAD-s/O3PlRLX5KhE/s1600-h/image%25255B16%25255D.png"><img alt="image" border="0" height="215" src="https://lh3.googleusercontent.com/-qT-Lzpm_q9g/V_Ix0BgZNeI/AAAAAAAAD-w/k2SPYJXh-L8/image_thumb%25255B7%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="747" /></a><br />
<br />
<h2>
<span style="font-size: large;">RIGHT OUTER JOIN / RIGHT JOIN</span></h2>
In a RIGHT OUTER JOIN, it will select all records from the ‘Right’ table and based on the JOIN condition it will select any matching records from the left table and return. If there aren’t any matching records on the left table it will return a ‘NULL’ value.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzYGXTL9StQ8WCHNyd-oFLutfWagSDAqpovuBv9c275UxzFWxNLkMBnprX-DQ7V8eAgP0GXW9vLJV0LANyThapY8NAGVkGOlmTMJ1FV-3NOvHcblTkO5O2-mW6_qfXpJ_ue5EbinBbGhxM/s1600/Right+Join+Sample.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="128" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzYGXTL9StQ8WCHNyd-oFLutfWagSDAqpovuBv9c275UxzFWxNLkMBnprX-DQ7V8eAgP0GXW9vLJV0LANyThapY8NAGVkGOlmTMJ1FV-3NOvHcblTkO5O2-mW6_qfXpJ_ue5EbinBbGhxM/s640/Right+Join+Sample.PNG" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
This can be shown using a venn diagram as follows:<br />
<a href="https://lh3.googleusercontent.com/-hFebyfcCN5o/V_Ix1S6hy3I/AAAAAAAAD-8/5G5-GInDyh8/s1600-h/image%25255B24%25255D.png"><img alt="image" border="0" height="232" src="https://lh3.googleusercontent.com/-plFw_510J9c/V_Ix1_S7pGI/AAAAAAAAD_A/dI4zTMgVyYo/image_thumb%25255B11%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="333" /></a><br />
<br />
<pre class="brush:sql">SELECT *
FROM
dbo.SalesDetails AS SD
RIGHT JOIN dbo.RepDetails AS RD
ON SD.RepId = RD.RepId
</pre>
<br />
<a href="https://lh3.googleusercontent.com/-L088WNEEFg4/V_Ix2T3PYSI/AAAAAAAAD_E/0hFigoqVoO4/s1600-h/image%25255B29%25255D.png"><img alt="image" border="0" height="213" src="https://lh3.googleusercontent.com/-0wKkVvEiPGY/V_Ix2gcYG4I/AAAAAAAAD_I/6igcOmul3dI/image_thumb%25255B14%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="766" /></a><br />
<br />
<h2>
<span style="font-size: large;">FULL OUTER JOIN / FULL JOIN</span></h2>
FULL OUTER JOIN is kind of a mx of both LEFT & RIGHT OUTER JOINs. It will return all rows from both ‘Left’ and ‘Right’ tables based on the JOIN condition. When the details aren’t matched it will return a NULL value in those respective columns.<br />
<br />
<br />
<a href="https://lh3.googleusercontent.com/-mRbaidO3r_A/V_Ix3FG511I/AAAAAAAAD_M/1Y_Wz0V2vY4/s1600-h/image%25255B33%25255D.png"><img alt="image" border="0" height="192" src="https://lh3.googleusercontent.com/-CWHIK1hxdnM/V_Ix3YdGx-I/AAAAAAAAD_Q/-AGADrgMLfk/image_thumb%25255B16%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="873" /></a><br />
<br />
<br />
This can be shown using a venn diagram as follows:<br />
<a href="https://lh3.googleusercontent.com/-PuwHX7RF5N8/V_Ix3-yEQ8I/AAAAAAAAD_U/NcevyAQLP4A/s1600-h/image%25255B37%25255D.png"><img alt="image" border="0" height="230" src="https://lh3.googleusercontent.com/-KjQ-l_GcTOo/V_Ix4LW-pLI/AAAAAAAAD_Y/JwxMHMBlcP0/image_thumb%25255B18%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="352" /></a><br />
<br />
<pre class="brush:sql">SELECT *
FROM
dbo.RepDetails AS RD
FULL OUTER JOIN dbo.SalesDetails AS SD
ON SD.RepId = RD.RepId
</pre>
<br />
<br />
<a href="https://lh3.googleusercontent.com/-5z4RRvt2cM0/V_Ix4isLGcI/AAAAAAAAD_c/VGNIfEjbluk/s1600-h/image%25255B41%25255D.png"><img alt="image" border="0" height="371" src="https://lh3.googleusercontent.com/-qKfIbHRhoTE/V_Ix5D0ty_I/AAAAAAAAD_g/qJpzPYhEE5E/image_thumb%25255B20%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="764" /></a><br />
<br />
<h2>
<span style="font-size: large;">CROSS JOIN</span></h2>
CROSS JOIN will return a result set which the number of rows equal to rows in ‘Left’ table multiplied by the number of rows in ‘Right’ table. Usually this behaviour is present when there’s no condition provided in the WHERE condition. So each row in the left table is joined to each row in the right table. Usuually this behaviour is called ‘Cartisian Product’<br />
<br />
<br />
<a href="https://lh3.googleusercontent.com/-cPGlEHH0TuQ/V_Ix5mZ7_iI/AAAAAAAAD_k/-DU4HcLoXu0/s1600-h/image%25255B46%25255D.png"><img alt="image" border="0" height="243" src="https://lh3.googleusercontent.com/-zUmM-h7jeQI/V_Ix6LTF33I/AAAAAAAAD_o/jTV5FCH7N2k/image_thumb%25255B23%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="861" /></a><br />
<pre class="brush:sql">SELECT *
FROM
dbo.RepDetails AS RD
CROSS JOIN dbo.Settings AS S
</pre>
<br />
<a href="https://lh3.googleusercontent.com/-RjKeNrFOjXM/V_Ix6gw5p9I/AAAAAAAAD_s/UQhwl9Sswww/s1600-h/image%25255B50%25255D.png"><img alt="image" border="0" height="245" src="https://lh3.googleusercontent.com/-8Zh1UVyu21U/V_Ix7Jz_AEI/AAAAAAAAD_w/EE4vntagagU/image_thumb%25255B25%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="819" /></a><br />
<br />
<br />
But when some condition is provided via the WHERE clause CROSS JOIN will behave like an INNER JOIN<br />
<pre class="brush:sql">SELECT *
FROM
dbo.RepDetails AS RD
CROSS JOIN dbo.Settings AS S
WHERE
RD.RepId = S.S_Id
</pre>
<br />
<a href="https://lh3.googleusercontent.com/-ePcDevQEtU8/V_Ix7bwWuvI/AAAAAAAAD_0/iPd4_zdfiBU/s1600-h/image%25255B56%25255D.png"><img alt="image" border="0" height="99" src="https://lh3.googleusercontent.com/-GJxudIXp5CU/V_Ix73RFiTI/AAAAAAAAD_4/5E6VQtSfubs/image_thumb%25255B29%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="821" /></a><br />
**Note: In a CROSS JOIN it’s not possible to refer to a value in the Left table along with the right table. Example following code will result in an error.<br />
<pre class="brush:sql">SELECT *
FROM
dbo.RepDetails AS RD
CROSS JOIN (SELECT * FROM dbo.Settings AS S WHERE S.S_Id = RD.RepId ) AS ST
</pre>
<h2>
<span style="font-size: large;"></span> </h2>
CROSS APPLY behaves like an INNER JOIN and OUTER APPLY behaves like an OUTER JOIN. But the main differnce in APPLY compared to the JOIN is that the right side of the APPLY operator can reference columns in the table which is on the left side. This is not possible in a JOIN.<br />
For example, suppose we need to fetch sales rep details along with the maximum sale record which they have done. So the following query is not possible since it is returning an error due to the aforementioned reason.<br />
<pre class="brush:sql">SELECT
*
FROM
dbo.RepDetails AS RD
JOIN(
SELECT TOP 1 *
FROM
dbo.SalesDetails AS SD
WHERE
RD.RepId = SD.RepId
ORDER BY
SD.SaleValue DESC
) AS SData
ON 1=1
</pre>
It will result in an error:<br />
<span style="color: red;"><strong>Msg 4104, Level 16, State 1, Line 78<br />The multi-part identifier "RD.RepId" could not be bound.</strong></span><br />
<br />
<br />
The way to achieve this is by using an APPLY.<br />
<span style="font-size: large;"></span><br />
<h2>
<span style="font-size: large;">CROSS APPLY</span></h2>
Considering the above requirement, we can use a CROSS APPLY in order to achieve the aforementioned.<br />
<pre class="brush:sql">SELECT
*
FROM
dbo.RepDetails AS RD
CROSS APPLY(
SELECT TOP 1 *
FROM
dbo.SalesDetails AS SD
WHERE
RD.RepId = SD.RepId
ORDER BY
SD.SaleValue DESC
) AS SData
</pre>
<br />
<a href="https://lh3.googleusercontent.com/-z5vFUYB41lQ/V_sbAMBS7UI/AAAAAAAAEAg/MX-sbbqd_JM/s1600-h/image%25255B60%25255D.png"><img alt="image" border="0" height="115" src="https://lh3.googleusercontent.com/-ofxBlO-oQcg/V_sbA8YVSWI/AAAAAAAAEAk/pdpiunjuRYY/image_thumb%25255B31%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="786" /></a><br />
<br />
<br />
Noticed the above sample, you can see that it returned three records. But if you inspect closely, the SalesRep table consists with five Reps. But CROSS APPLY has only returned the maximum sales value if there’s a matching record in the table right side to the APPLY operator. (Similar to an INNER JOIN)<br />
<h2>
<span style="font-size: large;"><br /></span></h2>
<h2>
<span style="font-size: large;">OUTER APPLY</span></h2>
Using OUTER APPLY we can achieved a similar result like CROSS APPLY, but the difference is that even though there aren’t any matching records in the table right side to the APPLY operator, still it will return all the rows from the left side table, will NULL values for the columns in the right side table. We will consider the same query what we used in the above example, but changing the APPLY to an OUTER APPLY.<br />
<pre class="brush:sql">SELECT
*
FROM
dbo.RepDetails AS RD
OUTER APPLY(
SELECT TOP 1 *
FROM
dbo.SalesDetails AS SD
WHERE
RD.RepId = SD.RepId
ORDER BY
SD.SaleValue DESC
) AS SData
</pre>
<a href="https://lh3.googleusercontent.com/-rIAqX3IE4TQ/V_sbBe008fI/AAAAAAAAEAo/9SkrVW50lAQ/s1600-h/image%25255B64%25255D.png"><img alt="image" border="0" height="156" src="https://lh3.googleusercontent.com/-sU5ooEoTudk/V_sbB6L_QXI/AAAAAAAAEAs/hHzxiF9RZ9g/image_thumb%25255B33%25255D.png?imgmax=800" style="background-image: none; border-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="812" /></a><br />
There are other capabilities which is possible using the APPLY. The following article explains these capabilites really well: <a href="http://bradsruminations.blogspot.sg/2011/04/t-sql-tuesday-017-it-slices-it-dices-it.html" title="http://bradsruminations.blogspot.sg/2011/04/t-sql-tuesday-017-it-slices-it-dices-it.html">http://bradsruminations.blogspot.sg/2011/04/t-sql-tuesday-017-it-slices-it-dices-it.html</a><br />
<br />
Hope this will help you to understand the JOIN and the APPLY operator in SQL Server and where it can be used precisely.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-60107119856686582032016-09-29T01:23:00.001+08:002016-09-29T01:24:46.164+08:00Enable Code Lense feature on VS 2015 Community Edition<p>As per the Visual Studio 2015 feature comparisson between the editions, Microsoft has clearly mentioned that certain features will only be available in Professional & Enterprise editions of VS 2015.</p> <p>One such feature happens to be the ‘CodeLense’</p> <p>If you google you can easily find out what’s code lense and its features. This is a good article which explains about code lense (<a title="http://www.codeproject.com/Articles/794766/What-is-CodeLens" href="http://www.codeproject.com/Articles/794766/What-is-CodeLens">http://www.codeproject.com/Articles/794766/What-is-CodeLens</a>)</p> <p>This is what has been mentioned in the feature comparisson for Visual Studio 2015 (<a title="https://www.visualstudio.com/vs/compare/" href="https://www.visualstudio.com/vs/compare/">https://www.visualstudio.com/vs/compare/</a>)</p> <p><a href="https://lh3.googleusercontent.com/-yO50wcqWkiQ/V-v8plR_PhI/AAAAAAAAD8E/5k9UDHEaZ54/s1600-h/image_thumb3%25255B2%25255D.png"><img title="image_thumb3" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image_thumb3" src="https://lh3.googleusercontent.com/-N7-XRhI9bT4/V-v8qPr6sNI/AAAAAAAAD8I/bfG9WKw2OW0/image_thumb3_thumb.png?imgmax=800" width="782" height="403"></a></p> <p>However installing SQL Server Data Tools for VS 2015, will enable this feature in the Community Edition of VS 2015.</p> <p>SSDT can be obtained from the this site: <a title="https://msdn.microsoft.com/en-us/mt186501.aspx" href="https://msdn.microsoft.com/en-us/mt186501.aspx">https://msdn.microsoft.com/en-us/mt186501.aspx</a></p> <p><a href="https://lh3.googleusercontent.com/-i-pMeqanSOk/V-v8q1A4wbI/AAAAAAAAD8Q/PSvnzKkWrFs/s1600-h/image_thumb10%25255B2%25255D.png"><img title="image_thumb10" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image_thumb10" src="https://lh3.googleusercontent.com/-i8qIRLBt27U/V-v8rYhBh_I/AAAAAAAAD8U/cMaxmUdfNVg/image_thumb10_thumb.png?imgmax=800" width="682" height="518"></a></p> <p>Codel lense feature can be customize using option dialog.</p> <p><a href="https://lh3.googleusercontent.com/-TV0ixWQ7p_c/V-v8r5LNdTI/AAAAAAAAD8Y/21ADT5T9rIc/s1600-h/image_thumb8%25255B2%25255D.png"><img title="image_thumb8" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image_thumb8" src="https://lh3.googleusercontent.com/-h_sdWrR2oYg/V-v8sQto-xI/AAAAAAAAD8c/gG6HL0rE6To/image_thumb8_thumb.png?imgmax=800" width="684" height="401"></a></p> <p>Hope this will be useful to you.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-61715305892335381102016-09-07T23:08:00.000+08:002016-09-08T12:10:16.014+08:00Native JSON Support in SQL Server 2016<p>When it comes to modern web development, JSON is one of the well known technologies when you require to exchange information between different applications. Before JSON, XML was used (and still being used in various applications and technologies) to do this job. But compared to XML, JSON is less verbose (like XML, JSON doesn’t have a closing tag), and it will make the JSON data somewhat smaller in size, ultimately making the data flow much faster. Perhaps the most significant advantage that JSON has over XML is that JSON is a subset of JavaScript, so code to parse and package it fits very naturally into JavaScript code. This seems highly beneficial for JavaScript programs and that happens to be a good reason for JSON to be very popular among web application developers.</p> <p>However using XML or JSON is up to the personal preference and the requirement.</p> <p>Prior to SQL Server 2016, there’s wasn’t any support for JSON in the earlier editions. So native JSON support is one of the new features which Microsoft introduced in SQL Server 2016.</p> <p>Prior to SQL Server 2016 there are other databases which supports JSON.</p> <ul> <li>MongoDB <li>CouchDB <li>eXistDB <li>Elastisearch <li>BaseX <li>MarkLogic <li>OrientDB <li>Oracle Database <li>PostgresSQL <li>Riak</li></ul> <p>But my main focus in this post will be the JSON support in SQL Server 2016.</p> <p>In order to support JSON, in SQL 2016 following in built functions have been introduced:</p> <ul> <li>ISJSON <li>JSON_VALUE <li>JSON_QUERY <li>JSON_MODIFY <li>OPENJSON <li>FOR JSON</li></ul> <h3> </h3> <p><strong><font color="#c0504d">There’s no specific data type in SQL Server to be used for JSON (unlike XML). You have to use NVARCHAR when you interact with JSON in SQL Server.</font></strong></p> <p> </p> <p><a href="https://lh3.googleusercontent.com/-ZLnNs22C4AI/V8yo1vapNbI/AAAAAAAAD34/WMxPJxOcW74/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-iOAxH1APuYk/V8yo2kIeAhI/AAAAAAAAD38/exdz3Sxd45I/image_thumb%25255B1%25255D.png?imgmax=800" width="859" height="399"></a></p> <p> </p> <p>This is how we assign JSON data to a variable.</p> <p> <p> </p><pre class="brush:sql">DECLARE @varJData AS NVARCHAR(4000)
SET @varJData =
N'{
"OrderInfo":{
"Tag":"#ONLORD_12546_45634",
"HeaderInfo":{
"CustomerNo":"CUS0001",
"OrderDate":"04-Jun-2016",
"OrderAmount":1200.00,
"OrderStatus":"1",
"Contact":["+0000 000 0000000000", "info@abccompany.com", "finance@abccompany.com"]
},
"LineInfo":[
{"ProductNo":"P00025", "Qty":3, "Price":200},
{"ProductNo":"P12548", "Qty":2, "Price":300}
]
}
}'
</pre>
<p>We will look closely how the aforementioned functions can be used with some sample</p> <h2><font size="5">ISJSON()</font></h2>
<p>As the name implies ISJSON functions is used to validate a given JSON string. The function will return in INT value and If the provided string is properly formatted as JSON it will return 1 else it will return 0.</p>
<p>Eg:</p>
<p><pre class="brush:sql">SELECT ISJSON(@varJData)
</pre>
<p> </p>
<h2><font size="5">JSON_VALUE()</font></h2>
<p>JSON_VALUE function can be used to return a scalar value from a JSON string.</p>
<p>Eg:</p>
<p><pre class="brush:sql">DECLARE @varJData AS NVARCHAR(4000)
SET @varJData =
N'{
"OrderInfo":{
"Tag":"#ONLORD_12546_45634",
"HeaderInfo":{
"CustomerNo":"CUS0001",
"OrderDate":"04-Jun-2016",
"OrderAmount":1200.00,
"OrderStatus":"1",
"Contact":["+0000 000 0000000000", "info@abccompany.com", "finance@abccompany.com"]
},
"LineInfo":[
{"ProductNo":"P00025", "Qty":3, "Price":200},
{"ProductNo":"P12548", "Qty":2, "Price":300}
]
}
}'
SELECT JSON_VALUE(@varJData,'$.OrderInfo.Tag')
SELECT JSON_VALUE(@varJData,'$.OrderInfo.HeaderInfo.CustomerNo')
</pre>
<p>Please note that the provided key is case sensitive and instead of ‘Tag’ if you pass ‘tag’ it will return a NULL since the function cannot find the key.</p>
<p><pre class="brush:sql">SELECT JSON_VALUE(@varJData,'$.OrderInfo.tag') /* This will returns NULL */
</pre>
<p>In such case if you require to see the exact error or the root cause, you need to specify ‘strict’ prior to the key. Eg:</p>
<p><pre class="brush:sql">SELECT JSON_VALUE(@varJData,'strict $.OrderInfo.tag') /* This will thorw an Error */
</pre>
<p>This will return the following error message instead of returning a NULL value.</p>
<p><strong><font color="#ff0000">Msg 13608, Level 16, State 1, Line 62<br>Property cannot be found on the specified JSON path.</font></strong></p>
<p>Also JSON_VALUE can be used to fetch an element from a simple array (not an object array). In our sample JSON there are two arrays, which are ‘Contact’ and ‘LineInfo’, where the first being a simple string array and the latter is an object array. </p>
<p>Suppose if we require to fetch only the phone number from the contact details we can use the following query:</p>
<p><pre class="brush:sql">SELECT JSON_VALUE(@varJData,'$.OrderInfo.HeaderInfo.Contact[0]')
</pre>
<p>Also this can be used when we require to fetch an attribute from an array element as well. Suppose if we require to get the product number from the first element of the ‘LineInfo’ we could use:</p>
<p><pre class="brush:sql">SELECT JSON_VALUE(@varJData, '$.OrderInfo.LineInfo[0].ProductNo')
</pre>
<h1> </h1>
<h2><font size="5">JSON_QUERY()</font></h2>
<p>JSON_QUERY function is used when you require to extract an array of data or an object from a JSON.And we can extract the contact details and the line details which are arrays in this scenario as follows</p>
<p><pre class="brush:sql">SELECT JSON_QUERY(@varJData, '$.OrderInfo.HeaderInfo.Contact')
SELECT JSON_QUERY(@varJData, '$.OrderInfo.LineInfo')
</pre>
<p>And this can be used to fetch a certain element from an object array. Suppose if we want to fetch details for the second prodcut in the LineInfo section we can use the following:</p>
<p><pre class="brush:sql">SELECT JSON_QUERY(@varJData, '$.OrderInfo.LineInfo[1]')
</pre>
<p><strong><font color="#0000ff">**Note: If the JSON text contains duplicate properties - for example, two keys with the same name on the same level- the JSON_VALUE and JSON_QUERY functions will return the first value that matches the path.</font></strong></p>
<p> </p>
<h2><strong><font size="5">JSON_MODIFY()</font></strong></h2>
<p>JSON_MODIFY function updates the value of a property in a JSON string and return the updated JSON string. The syntax for this function is as follows:</p>
<p>JSON_MODIFY(expression, path, new_value)</p>
<p>Using this function you can either Update, Insert, Delete or Append a value to the JSON string. We will see each of these operations using the above JSON string.</p>
<p><strong><u>Updating an Exitsing Value</u></strong></p>
<p>In order to update an existing value you need to provide the exact path followed by the value which should be updated to.</p>
<p><pre class="brush:sql">SET @varJData = JSON_MODIFY(@varJData,'$.OrderInfo.Tag','#NEWTAG_00001')
SELECT JSON_VALUE(@varJData,'$.OrderInfo.Tag')
</pre>
<p> </p>
<p><strong><u>Deleting an Existing Value</u></strong></p>
<p>In order to delete an existing value you need to provide the exact path follwed by the value ‘NULL’.</p>
<p><pre class="brush:sql">SET @varJData = JSON_MODIFY(@varJData,'$.OrderInfo.Tag',NULL)
SELECT JSON_VALUE(@varJData,'$.OrderInfo.Tag')
PRINT @varJData
</pre>
<p>When the value is printed you can see that the ‘Tag’ attribute has been completely removed from the JSON string.</p>
<p><a href="https://lh3.googleusercontent.com/-umE9xw3V6OE/V83yRiPN0wI/AAAAAAAAD4Q/gNBsxnmunLQ/s1600-h/image%25255B4%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-BhSmlPSC_cU/V83ySCZcisI/AAAAAAAAD4U/7b21hdflhyA/image_thumb%25255B1%25255D.png?imgmax=800" width="784" height="310"></a></p>
<p> </p>
<p><strong><u>Inserting a Value</u></strong></p>
<p>In order to insert an attribute along with a value you need to provide a path which isn’t currently availble in the JSON followed by the value. If the provided path is already present then the existing value will be replaced by the new value. The new value will always be added at the end of the existing JSON string.</p>
<p><pre class="brush:sql">SET @varJData = JSON_MODIFY(@varJData,'$.OrderInfo.Batch','#B_100000')
SELECT JSON_VALUE(@varJData,'$.OrderInfo.Batch')
PRINT @varJData
</pre>
<p><a href="https://lh3.googleusercontent.com/-y2NBtqnwzlY/V83ySoFQl8I/AAAAAAAAD4Y/6oh-T_Gh2e8/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-rNv8vtiwyyc/V83yTPBD4QI/AAAAAAAAD4c/yYKhw9EZTa8/image_thumb%25255B4%25255D.png?imgmax=800" width="801" height="316"></a></p>
<p> </p>
<p>Appending a Value</p>
<p>In order to append an existing array in a JSON, you need to use ‘<strong>append</strong>’ before the path. Suppose if we require to add another element to the </p>
<p><pre class="brush:sql">SET @varJData = JSON_MODIFY(@varJData, 'append $.OrderInfo.HeaderInfo.Contact','+0010 111 1111111111')
SELECT JSON_QUERY(@varJData,'$.OrderInfo.HeaderInfo.Contact')
</pre>
<p><a href="https://lh3.googleusercontent.com/-6cvLN0XNpIg/V86UlpooKpI/AAAAAAAAD4w/z0AMZR6n7Hw/s1600-h/image%25255B4%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-Tnz2WygDBkM/V86UmSs5GaI/AAAAAAAAD40/n813BNXNMaE/image_thumb%25255B2%25255D.png?imgmax=800" width="816" height="99"></a></p>
<p> </p>
<p>JSON_MODIFY can only manipulate a single value at a time. Therefore if the requirement is to change multiple values within a single query, you need to use JSON_MODIFY function multiple times. Suppose if we require to change the ‘ProductNo’ and the ‘Price’ of the first product in the ‘LineInfo’ we coud use the following syntax.</p>
<p><pre class="brush:sql">SET @varJData =
JSON_MODIFY(
JSON_MODIFY(@varJData,'$.OrderInfo.LineInfo[0].ProductNo','P99999')
,'$.OrderInfo.LineInfo[0].Price'
,150
)
</pre>
<p><a href="https://lh3.googleusercontent.com/-05PFFkQ_5uc/V86Um6Jaw0I/AAAAAAAAD44/Sv-X94Qk_S8/s1600-h/image%25255B10%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-INjHLEH3EFw/V86UnXQNmEI/AAAAAAAAD48/ho2MFhYL-jM/image_thumb%25255B6%25255D.png?imgmax=800" width="817" height="194"></a></p>
<p> </p>
<h2><font size="5">FOR JSON</font></h2>
<p>FOR JSON functionality is used When we are required to export SQL Tabular data as JSON data. This is very similar to the functionality of ‘FOR XML’. Each row will be formatted as a JSON object and values in cells will be generated as values of those respective JSON objects. Column names (or aliases) will be used as key names. Based on the options provided there are two variations in ‘FOR JSON’ usage.</p>
<p>1. FOR JSON AUTO - This will automatically create nested JSON sub arrays based on the table hierarchy used in the query. (similar to FOR XML AUTO)</p>
<p>2. FOR JSON PATH - This enables you to define the structure of output JSON using the column names/aliases. If you put dot-separated names in the column aliases, JSON properties will follow the naming convention. (This is similar to FOR XML PATH where you can use slash separated paths)</p>
<p> </p>
<p>In order to illustrate the aforementioned concepts we need to prepare some sample data. Please use the following scripts to generate the necessary data.</p>
<p><pre class="brush:sql">--== Generate Required Schemas ==--
CREATE TABLE OrderHeader(
TAG VARCHAR(24)
,ORD_NO VARCHAR(10)
,CUST_NO VARCHAR(8)
,ORD_DATE DATE
,ORD_AMOUNT MONEY
,ORD_STATUS TINYINT
)
CREATE TABLE OrderLine(
ORD_NO VARCHAR(10)
,LINE_NO INT
,PROD_NO VARCHAR(8)
,ORD_QTY INT
,ITEM_PRICE MONEY
)
CREATE TABLE CustomerContact(
CONT_ID INT
,CUST_NO VARCHAR(8)
,CONTACT_DETAILS VARCHAR(24)
)
--== Insert Sample Data ==--
INSERT INTO dbo.OrderHeader(TAG,ORD_NO,CUST_NO,ORD_DATE,ORD_AMOUNT,ORD_STATUS)
VALUES('#ONLORD_12546_45634','ORD_1021','CUS0001','04-Jun-2016',1200.00,1)
INSERT INTO dbo.OrderLine(ORD_NO,LINE_NO,PROD_NO,ORD_QTY,ITEM_PRICE)
VALUES ('ORD_1021',1,'P00025',3,200.00), ('ORD_1021',1,'P12548',2,300.00)
INSERT INTO dbo.CustomerContact(CONT_ID, CUST_NO, CONTACT_DETAILS)
VALUES (1,'CUS0001','+0000 000 0000000000') ,(2,'CUS0001','info@abccompany.com'),(3,'CUS0001','finance@abccompany.com')
</pre>
<p> </p>
<h3><u>Extracting data as JSON using FOR JSON AUTO</u></h3>
<p><pre class="brush:sql">SELECT
H.TAG
,H.ORD_NO
,H.CUST_NO
,H.ORD_DATE
,H.ORD_AMOUNT
,H.ORD_STATUS
,L.ORD_NO
,L.LINE_NO
,L.PROD_NO
,L.ORD_QTY
,L.ITEM_PRICE
FROM
dbo.OrderHeader AS H
JOIN dbo.OrderLine AS L
ON L.ORD_NO = H.ORD_NO
WHERE
H.ORD_NO = 'ORD_1021'
FOR JSON AUTO
</pre>
<p> </p>
<p>You will get a similar result which is shown below:</p>
<p>[<br> {<br> "TAG":"#ONLORD_12546_45634",<br> "ORD_NO":"ORD_1021",<br> "CUST_NO":"CUS0001",<br> "ORD_DATE":"2016-06-04",<br> "ORD_AMOUNT":1200.0000,<br> "ORD_STATUS":1,<br> "L":[<br> {"ORD_NO":"ORD_1021","LINE_NO":1,"PROD_NO":"P00025","ORD_QTY":3,"ITEM_PRICE":200.0000},<br> {"ORD_NO":"ORD_1021","LINE_NO":1,"PROD_NO":"P12548","ORD_QTY":2,"ITEM_PRICE":300.0000}<br> ]<br> }<br>]</p>
<p>As described previously ‘FOR JSON AUTO’ will simply convert the column names or aliases as keys and produce the JSON. Table aliases will be used to create sub arrays.</p>
<p>But we could get a similar resultset like what we had in our previous examples by tweaking the above select statement as follows:</p>
<p><pre class="brush:sql">SELECT
H.TAG AS Tag
,H.ORD_NO AS OrderNo
,H.CUST_NO AS CustNo
,H.ORD_DATE AS OrderDate
,H.ORD_AMOUNT AS OrderAmount
,H.ORD_STATUS AS OrderStatus
,LineInfo.ORD_NO AS [OrderNo]
,LineInfo.LINE_NO AS [LineNo]
,LineInfo.PROD_NO AS [ProdNo]
,LineInfo.ORD_QTY AS [Qty]
,LineInfo.ITEM_PRICE AS [ItemPrice]
FROM
dbo.OrderHeader AS H
JOIN dbo.OrderLine AS LineInfo
ON LineInfo.ORD_NO = H.ORD_NO
WHERE
H.ORD_NO = 'ORD_1021'
FOR JSON AUTO, ROOT ('OrderInfo')
</pre>
<p>Then we will be able to get the following JSON string.</p>
<p>{<br> "OrderInfo":[<br> {<br> "Tag":"#ONLORD_12546_45634",<br> "OrderNo":"ORD_1021",<br> "CustNo":"CUS0001",<br> "OrderDate":"2016-06-04",<br> "OrderAmount":1200.0000,<br> "OrderStatus":1,<br> "LineInfo":[<br> {"OrderNo":"ORD_1021","LineNo":1,"ProdNo":"P00025","Qty":3,"ItemPrice":200.0000},<br> {"OrderNo":"ORD_1021","LineNo":1,"ProdNo":"P12548","Qty":2,"ItemPrice":300.0000}<br> ]<br> }<br> ]<br>}</p>
<p> </p>
<h3><u>Extracting data as JSON using FOR JSON PATH</u></h3>
<p>We can use the FOR JSON PATH functionality to format the output JSON the way we require easily. But there’s a restriction when we use ‘FOR JSON PATH’ to extract data, which is that you cannot have the same column name (or alias) duplicated among multiple columns. This will result in an error.</p>
<p>We will see how the details will be fetched using ‘FOR JSON PATH’</p>
<p><pre class="brush:sql">SELECT
H.TAG
,H.ORD_NO
,H.CUST_NO
,H.ORD_DATE
,H.ORD_AMOUNT
,H.ORD_STATUS
--,L.ORD_NO --If this line is uncommented it will throw an error
,L.LINE_NO
,L.PROD_NO
,L.ORD_QTY
,L.ITEM_PRICE
FROM
dbo.OrderHeader AS H
JOIN dbo.OrderLine AS L
ON L.ORD_NO = H.ORD_NO
WHERE
H.ORD_NO = 'ORD_1021'
FOR JSON PATH
</pre>
<p> </p>
<p>We will get the following JSON result.</p>
<p>[<br> {<br> "TAG":"#ONLORD_12546_45634",<br> "ORD_NO":"ORD_1021",<br> "CUST_NO":"CUS0001",<br> "ORD_DATE":"2016-06-04",<br> "ORD_AMOUNT":1200.0000,<br> "ORD_STATUS":1,<br> "LINE_NO":1,<br> "PROD_NO":"P00025",<br> "ORD_QTY":3,<br> "ITEM_PRICE":200.0000<br> },<br> {<br> "TAG":"#ONLORD_12546_45634",<br> "ORD_NO":"ORD_1021",<br> "CUST_NO":"CUS0001",<br> "ORD_DATE":"2016-06-04",<br> "ORD_AMOUNT":1200.0000,<br> "ORD_STATUS":1,<br> "LINE_NO":1,<br> "PROD_NO":"P12548",<br> "ORD_QTY":2,<br> "ITEM_PRICE":300.0000<br> }<br>]</p>
<p>Advantage in using ‘FOR JSON PATH’ is that you have the ability to control the structure using the column names/aliases. When dot seperated aliases are used, JSON properties will follow the naming convention. Please consider the below query and the results.</p>
<p><pre class="brush:sql">SELECT
H.TAG AS 'HeaderInfo.Tag'
,H.ORD_NO AS 'HeaderInfo.OrderNo'
,H.CUST_NO AS 'HeaderInfo.CustNo'
,H.ORD_DATE AS 'HeaderInfo.OrderDate'
,H.ORD_AMOUNT AS 'HeaderInfo.OrderAmount'
,H.ORD_STATUS AS 'HeaderInfo.OrderStatus'
,L.ORD_NO AS 'LineInfo.OrderNo'
,L.LINE_NO AS 'LineInfo.LineNo'
,L.PROD_NO AS 'LineInfo.ProdNo'
,L.ORD_QTY AS 'LineInfo.Qty'
,L.ITEM_PRICE AS 'LineInfo.ItemPrice'
FROM
dbo.OrderHeader AS H
JOIN dbo.OrderLine AS L
ON L.ORD_NO = H.ORD_NO
WHERE
H.ORD_NO = 'ORD_1021'
FOR JSON PATH
</pre>
<p>You will see the following JSON result.</p>
<p>[<br> {<br> "HeaderInfo":{<br> "Tag":"#ONLORD_12546_45634",<br> "OrderNo":"ORD_1021",<br> "CustNo":"CUS0001",<br> "OrderDate":"2016-06-04",<br> "OrderAmount":1200.0000,<br> "OrderStatus":1<br> },<br> "LineInfo":{"OrderNo":"ORD_1021","LineNo":1,"ProdNo":"P00025","Qty":3,"ItemPrice":200.0000}<br> },<br> {<br> "HeaderInfo":{<br> "Tag":"#ONLORD_12546_45634",<br> "OrderNo":"ORD_1021",<br> "CustNo":"CUS0001",<br> "OrderDate":"2016-06-04",<br> "OrderAmount":1200.0000,<br> "OrderStatus":1<br> },<br> "LineInfo":{"OrderNo":"ORD_1021","LineNo":1,"ProdNo":"P12548","Qty":2,"ItemPrice":300.0000}<br> }<br>]</p>
<p> </p>
<p><font size="5">OPENJSON</font></p>
<p>OPENJSON is a table value function which will go through a given JSON string, and returns a relational table with it’s contents. It’ll iterate through JSON object arrays, elemets and generates a row for each element. There are two variations of this functionality.</p>
<ul>
<li>Without a pre-defined schema where the values will be returned as key value pairs including it’s type to identify what sort of value is being returned.
<li>With a well defined schema. This schema will be provided by us in the OPENJSON statement.</li></ul>
<p> </p>
<h3><u>OPENJSON without a pre-defined schema</u></h3>
<p>We will use the following JSON data string to find out the types which will be returned based on the data type.</p>
<p>{<br> "Null Data":null,<br> "String Data":"Some String Data",<br> "Numeric Data": 1000.00,<br> "Boolean Data": true,<br> "Array Data":["A","B","C"],<br> "Object Data":{"SomeKey":"Some Value"}<br> }</p>
<p> </p>
<p><pre class="brush:sql">DECLARE @vJSON AS NVARCHAR(4000) = N'{
"Null Data":null,
"String Data":"Some String Data",
"Numeric Data": 1000.00,
"Boolean Data": true,
"Array Data":["A","B","C"],
"Object Data":{"SomeKey":"Some Value"}
}';
SELECT * FROM OPENJSON(@vJSON)
</pre>
<p><a href="https://lh3.googleusercontent.com/-HEiBRTEXxno/V9DknnqrJgI/AAAAAAAAD5w/E3Gk1WkOtU0/s1600-h/image%25255B4%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-n76EH3DuxDM/V9DkoNkLQpI/AAAAAAAAD50/VUJQATXwbPo/image_thumb%25255B1%25255D.png?imgmax=800" width="427" height="166"></a></p>
<p> </p>
<p>With some realistic set of JSON data.</p>
<p><pre class="brush:sql">DECLARE @vJSON AS NVARCHAR(4000) = N'{
"Tag":"#ONLORD_12546_45634",
"OrderNo":"ORD_1021",
"CustNo":"CUS0001",
"OrderDate":"2016-06-04",
"OrderAmount":1200.0000,
"OrderStatus":1
}';
SELECT * FROM OPENJSON(@vJSON)
</pre>
<p><a href="https://lh3.googleusercontent.com/-NPLWp6Esefo/V9DkoSfDQ4I/AAAAAAAAD54/zytlcKfmqnU/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-ZiFjdzr__NA/V9Dko1pkThI/AAAAAAAAD58/fdlsCJ0Qzck/image_thumb%25255B3%25255D.png?imgmax=800" width="418" height="187"></a></p>
<p> </p>
<h3><u>OPENJSON with a pre-defined schema</u></h3>
<p>We will use the same JSON string which we have used in the previous example and generate the result set with a pre-defined schema.</p>
<p><pre class="brush:sql">
DECLARE @vJSON AS NVARCHAR(4000) = N'{
"Tag":"#ONLORD_12546_45634",
"OrderNo":"ORD_1021",
"CustNo":"CUS0001",
"OrderDate":"2016-06-04",
"OrderAmount":1200.0000,
"OrderStatus":1
}';
SELECT * FROM OPENJSON(@vJSON) WITH(
Tag VARCHAR(24)
,OrderNo VARCHAR(8)
,CustNo VARCHAR(8)
,OrderDate DATE
,OrderAmount MONEY
,OrderStatus INT
)
</pre>
<p><a href="https://lh3.googleusercontent.com/-vEpQL3ksvyQ/V9DkpP6b_9I/AAAAAAAAD6A/xNbkramT5Dg/s1600-h/image%25255B13%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-OxFQ3PIewmA/V9Dkph3_4qI/AAAAAAAAD6E/XyLnknEev40/image_thumb%25255B6%25255D.png?imgmax=800" width="666" height="71"></a></p><pre class="brush: sql"> </pre>
<p>This is basically what has been provided to support with JSON data in SQL 2016 natively. Hope this would be helpful for you.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com3tag:blogger.com,1999:blog-1342610106694475714.post-13081178129359186642016-08-23T21:50:00.000+08:002016-08-24T09:50:49.369+08:00DROP IF EXISTS in SQL Server 2016 (DIE)<p> </p> <p>Prior to SQL Server 2016, when we need to drop a SQL Object, it's the best practice to check whether the respective object exists or not. Otherwise the operation will return in an error.</p> <p><br><font color="#0000ff" size="3" face="Courier Prime"><strong>DROP TABLE [SomeTable]</strong></font></p> <p>If the object is not found it will return the following error.</p> <p><font color="#ff0000">Msg 3701, Level 11, State 5, Line 11<br>Cannot drop the table 'SomeTable', because it does not exist or you do not have permission.</font></p> <p>Hence we need to change the syntax as:</p> <p><font color="#0000ff" size="3" face="Courier Prime"><strong>IF EXISTS(SELECT 'x' FROM sys.objects AS O WHERE O.name = 'SomeTable' AND O.[type] = 'U')<br> DROP TABLE [SomeTable]</strong></font><br> <br>OR</p> <p><font face="Courier Prime"><strong><font color="#0000ff">IF OBJECT_ID('dbo.SomeTable','U') IS NOT NULL<br> DROP TABLE [SomeTable]</font></strong><br></font> <br> <br>In SQL Server 2016 there is an easier way to do this using comparatively less amount for coding.</p> <p><strong><font color="#0000ff" face="Courier Prime">DROP TABLE IF EXISTS [SomeTable];<br>DROP PROCEDURE IF EXISTS [SomeProcedure];</font></strong></p> <p>Even this can be use when dropping columns and constraints from a table.</p> <p><strong><font color="#0000ff" face="Courier Prime">ALTER TABLE [TableName] DROP CONSTRAINT IF EXISTS [ConstraintName]<br>ALTER TABLE [TableName] DROP COLUMN IF EXISTS [TableName]</font></strong></p> <p><strong>Eg:</strong><br><strong><font color="#0000ff" face="Courier Prime">CREATE TABLE SomeTable(<br> Id INT<br> ,Name VARCHAR(10) NOT NULL CONSTRAINT [DF_SomeTable_Name] DEFAULT ('')<br>)</font></strong></p> <p><strong><font color="#0000ff" face="Courier Prime">ALTER TABLE dbo.SomeTable<br>DROP CONSTRAINT IF EXISTS [DF_SomeTable_Name]</font></strong></p> <p><strong><font color="#0000ff" face="Courier Prime">ALTER TABLE dbo.SomeTable<br>DROP COLUMN IF EXISTS [Name]</font></strong></p> <p><br>The beauty of this functionality is that even the object does not exists, it will not fail and execution will continue. </p> <p>Currently, the following objects can be dropped with the DIE functionality:</p> <ul> <li>ASSEMBLY <li>VIEW <li>DATABASE <li>DEFAULT <li>FUNCTION <li>PROCEDURE <li>INDEX <li>AGGREGATE <li>ROLE <li>RULE <li>SCHEMA <li>SECURITY POLICY <li>SEQUENCE <li>SYNONYM <li>TABLE <li>TRIGGER <li>TYPE <li>USER <li>VIEW</li></ul> <p>Hope this will be useful to you.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-63026193698063332552016-08-13T23:37:00.000+08:002016-08-14T01:44:02.598+08:00Connecting to an MS SQL Instance using NodeJS (Fixing ConnectionError: Port for SQLServer not found in ServerName & Failed to connect to localhost:undefined in 15000ms)After few debates and discussions on new technologies in the market and how to adapt to them, during the weekend I thought of exploring NodeJS and it applications. Since I spent most of my time designing data-centric applications at office, as the first step I thought of connecting to MS SQL Server using NodeJS.<br />
As a newbie to NodeJS, I went through the official documentation and managed to achieve it. However, during the course, I faced many difficulties and by referring to many of the articles, I was managed to resolve all these hurdles.<br />
So I thought of including these problems which I faced and how to overcome them. So it would be a great help to anyone who’s exploring or trying to achieve this more easily.<br />
For this, I will be using SQL Server 2014 on an Instance (.\SQL2K14).<br />
<br />
1. First you need to download and install NodeJS. (<a href="https://nodejs.org/en/" target="_blank" title="https://nodejs.org/en/">https://nodejs.org/en/</a>)<br />
2. Install MSSQL package for Node, using the following syntax: (Use windows command prompt)<br />
<blockquote>
<strong>npm install mssql</strong></blockquote>
3. Create a file named ‘connecttosql.js’ and include the following code:<br />
<br />
<blockquote>
<strong><span style="color: blue;">//We require mssql package for this sample<br />var sqlcon = require('mssql');</span></strong><br />
<strong><span style="color: blue;">function GetSQLData(queryCallback){ //A callback function is taken as an argument. Once the operation is completed we will be calling this<br /> <br /> //SQL Configuration<br /> var config = {<br /> user:'###' //SQL User Id. Please provide a valid user<br /> ,password:'######' //SQL Password. Please provide a valid password<br /> ,server:'localhost\\SQL2K14' <br /> /*<br /> Since my SQL is an instance I am using 'localhost\\Instance'.<br /> If you have SQL installed on the default instance, it should be server:'localhost'<br /> */<br /> ,database: 'master' //You can use any database here<br /> }</span></strong><br />
<strong><span style="color: blue;"> var connection = new sqlcon.Connection(config,function(err){<br /> //In case of an error print the error to the console. You can have your customer error handling<br /> if (err) console.log(err);<br /> <br /> //Query Database<br /> var dbQuery = new sqlcon.Request(connection);<br /> //Purposely we are delaying the results<br /> dbQuery.query("WAITFOR DELAY '00:00:05';SELECT * FROM INFORMATION_SCHEMA.TABLES",function(err,resultset){<br /> //In case of an error print the error to the console. You can have your customer error handling<br /> if (err) console.log(err);<br /> <br /> //Passing the resultset to the callback function<br /> queryCallback(resultset);<br /> })<br /> });<br />}</span></strong><br />
<strong><span style="color: blue;">function callback (resultset){<br /> console.dir('Results returned and printed from the call back function');<br /> console.dir(resultset);<br /> <br /> //Exit the application<br /> console.dir('Exiting the Application');<br /> process.exit(0);<br />}</span></strong><br />
<strong><span style="color: blue;">//Calling the function<br />console.dir('Calling GetSQLData');<br />GetSQLData(callback);<br />/*<br /> Once we call this function even there's a delay to return the results <br /> you will see the next line printing 'Waiting for callback function to get invoked...'<br />*/<br />console.dir('Waiting for callback function to get invoked...');</span></strong></blockquote>
<br />
I have provided the relevant information as comments.<br />
Before running the program please make sure the following configurations on the SQL server is already done:<br />
<br />
1. Enable TCP/IP Protocols in SQL Server Configuration Manager for both server and client.<br />
<br />
<a href="https://lh3.googleusercontent.com/-90Tr3Mp9goY/V68_BAqHFiI/AAAAAAAAD1M/di5wZEUM87g/s1600-h/image%25255B9%25255D.png"><img alt="image" border="0" height="257" src="https://lh3.googleusercontent.com/-SQqwWjJZ9QU/V68_CbiotiI/AAAAAAAAD1Q/P0wZL509Z58/image_thumb%25255B5%25255D.png?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="493" /></a><br />
<br />
Or else when running it will result an error shown below:<br />
<blockquote>
<span style="color: red;"><strong>{ [ConnectionError: Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\[YourPCName]\pipe\M<br />SSQL$SQL2K14\sql\query;;]<br /> name: 'ConnectionError',<br /> message: 'Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\\\[YourPCName]\\pipe\\MSSQL<br />$SQL2K14\\sql\\query;;',<br /> code: 'EINSTLOOKUP' }<br />{ [ConnectionError: Connection is closed.]<br /> name: 'ConnectionError',<br /> message: 'Connection is closed.',<br /> code: 'ECONNCLOSED' }</strong></span></blockquote>
<br />
2. In SQL Server Configuration Manager under SQL Server Services make sure that ‘SQL Server Browser’ service is running.<br />
<br />
<a href="https://lh3.googleusercontent.com/-0VNDXjxxaps/V68_C2Dk-yI/AAAAAAAAD1U/eVezHNV1hO4/s1600-h/image%25255B19%25255D.png"><img alt="image" border="0" height="176" src="https://lh3.googleusercontent.com/-xlm9deogGR4/V68_DWSL4lI/AAAAAAAAD1Y/pWOxrzm3Bkk/image_thumb%25255B11%25255D.png?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="756" /></a><br />
<br />
Or else when running the script it will result an error shown below:<br />
<blockquote>
<span style="color: red;"><strong>{ [ConnectionError: Failed to connect to localhost:undefined in 15000ms]<br /> name: 'ConnectionError',<br /> message: 'Failed to connect to localhost:undefined in 15000ms',<br /> code: 'ETIMEOUT' }<br />{ [ConnectionError: Connection is closed.]<br /> name: 'ConnectionError',<br /> message: 'Connection is closed.',<br /> code: 'ECONNCLOSED' }</strong></span></blockquote>
<br />
if the aforementioned issues are already addressed execute the above file using the following syntax in a Windows Command Window:<br />
<blockquote>
<strong><span style="color: black;">node connecttosql.js</span></strong></blockquote>
You should get a similar result which is shown below:<br />
<blockquote>
<span style="color: blue;">'Calling GetSQLData'<br />'Waiting for callback function to get invoked...'<br />'Results returned and printed from the call back function'<br />[ { TABLE_CATALOG: 'master',<br /> TABLE_SCHEMA: 'dbo',<br /> TABLE_NAME: 'spt_fallback_db',<br /> TABLE_TYPE: 'BASE TABLE' },<br /> { TABLE_CATALOG: 'master',<br /> TABLE_SCHEMA: 'dbo',<br /> TABLE_NAME: 'spt_fallback_dev',<br /> TABLE_TYPE: 'BASE TABLE' },<br /> { TABLE_CATALOG: 'master',<br /> TABLE_SCHEMA: 'dbo',<br /> TABLE_NAME: 'spt_fallback_usg',<br /> TABLE_TYPE: 'BASE TABLE' },<br /> { TABLE_CATALOG: 'master',<br /> TABLE_SCHEMA: 'dbo',<br /> TABLE_NAME: 'spt_values',<br /> TABLE_TYPE: 'VIEW' },<br /> { TABLE_CATALOG: 'master',<br /> TABLE_SCHEMA: 'dbo',<br /> TABLE_NAME: 'spt_monitor',<br /> TABLE_TYPE: 'BASE TABLE' },<br /> { TABLE_CATALOG: 'master',<br /> TABLE_SCHEMA: 'dbo',<br /> TABLE_NAME: 'MSreplication_options',<br /> TABLE_TYPE: 'BASE TABLE' } ]<br />'Exiting the Application'</span></blockquote>
<br />
I hope this will help anyone who’s using node to connect to SQL and facing the aforementioned issues.Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com1tag:blogger.com,1999:blog-1342610106694475714.post-18757039924095093202016-03-09T21:02:00.000+08:002016-03-09T21:03:26.589+08:00String or binary data would be truncated / Arithmetic overflow error converting numeric to data type numeric – Workaround<p> </p> <p>There’s nothing more annoying than getting the error ‘String or binary data would be truncated’ or ‘Arithmetic overflow error converting numeric to data type numeric’, when you need to insert data to a table using a SELECT statement. To make it more interesting, the SQL won’t be providing us the name of the column (or columns) which is causing this issue. (This is due to the SQL architecture on how it executes queries)</p> <p>To illustrate this I will use a small sample.</p> <p>Suppose we have a table to store some Customer details:</p> <blockquote> <p><font color="#0000ff"><strong>CREATE TABLE Customer_Data(<br> CustId TINYINT<br> ,CustFName VARCHAR(10)<br> ,CustLName VARCHAR(10)<br> ,MaxCredit NUMERIC(6,2)<br>)</strong></font></p></blockquote> <p>We will try to insert details to the above table. (In reality the SELECT statement will be very complex and could fetch lots of rows)</p> <blockquote> <p><br><font color="#0000ff"><font size="3"><strong>INSERT INTO dbo.Customer_Data(<br> CustId<br> ,CustFName<br> ,CustLName<br> ,MaxCredit<br>)</strong></font></font></p> <p><font color="#0000ff" size="3"><strong>SELECT 1 AS CustId,'John' AS CustFName,'Doe' AS CustLName,1000.00 AS MaxCredit UNION ALL<br>SELECT 2,'Jane','Doe',1000.00 UNION ALL<br>SELECT 3,'James','Whitacker Jr.',15000.00 </strong></font></p></blockquote> <p> </p> <p>This will result the following error:</p> <blockquote> <p><font color="#ff0000">Msg 8152, Level 16, State 14, Line 48<br>String or binary data would be truncated.</font><br>The statement has been terminated.</p></blockquote> <p>The challenge here is to find out actually which columns are having this issue. (As mentioned in reality number of columns could be very large)</p> <p>However there is a small workaround which we can use to find out the columns which is causing the insertion to fail. You need to do the following in order to find out these columns.</p> <p>1. First create a table using the same select statement. (You can either create a temporary table or an actual table based on the environment and your need). I will create two tables, one actual and one temporary to illustrate both the options.</p> <blockquote> <p><strong><font color="#0000ff">SELECT A.* <br>INTO Temp_Customer_Data<br>FROM(<br> SELECT 1 AS CustId,'John' AS CustFName,'Doe' AS CustLName,1000.00 AS MaxCredit UNION ALL<br> SELECT 2,'Jane','Doe',1000.00 UNION ALL<br> SELECT 3,'James','Whitacker Jr.',15000.00 <br>) AS A</font></strong></p> <p><br><strong><font color="#0000ff">SELECT A.* <br>INTO #Customer_Data<br>FROM(<br> SELECT 1 AS CustId,'John' AS CustFName,'Doe' AS CustLName,1000.00 AS MaxCredit UNION ALL<br> SELECT 2,'Jane','Doe',1000.00 UNION ALL<br> SELECT 3,'James','Whitacker Jr.',15000.00 <br>) AS A</font></strong></p></blockquote> <p>2. Use the following query to identify the issue columns</p> <p><u>Actual Table:</u></p> <blockquote> <p><strong><font color="#0000ff">;WITH Cte_Source AS (<br>SELECT <br> C.COLUMN_NAME<br> ,C.DATA_TYPE<br> ,C.CHARACTER_MAXIMUM_LENGTH<br> ,C.NUMERIC_PRECISION<br> ,C.NUMERIC_SCALE<br>FROM<br> INFORMATION_SCHEMA.TABLES AS T<br> JOIN INFORMATION_SCHEMA.COLUMNS AS C<br> ON C.TABLE_CATALOG = T.TABLE_CATALOG<br> AND C.TABLE_NAME = T.TABLE_NAME<br> AND C.TABLE_SCHEMA = T.TABLE_SCHEMA<br>WHERE<br> T.TABLE_NAME = 'Temp_Customer_Data' -- Source Table<br> AND T.TABLE_SCHEMA = 'dbo'<br>)<br>,Cte_Destination AS (<br>SELECT <br> C.COLUMN_NAME<br> ,C.DATA_TYPE<br> ,C.CHARACTER_MAXIMUM_LENGTH<br> ,C.NUMERIC_PRECISION<br> ,C.NUMERIC_SCALE<br>FROM<br> INFORMATION_SCHEMA.TABLES AS T<br> JOIN INFORMATION_SCHEMA.COLUMNS AS C<br> ON C.TABLE_CATALOG = T.TABLE_CATALOG<br> AND C.TABLE_NAME = T.TABLE_NAME<br> AND C.TABLE_SCHEMA = T.TABLE_SCHEMA<br>WHERE<br> T.TABLE_NAME = 'Customer_Data' -- Destination Table<br> AND T.TABLE_SCHEMA = 'dbo'<br>)<br>SELECT<br> S.COLUMN_NAME<br> ,S.DATA_TYPE<br> ,S.CHARACTER_MAXIMUM_LENGTH<br> ,S.NUMERIC_PRECISION<br> ,S.NUMERIC_SCALE</font></strong></p> <p><strong><font color="#0000ff"> ,D.COLUMN_NAME<br> ,D.DATA_TYPE<br> ,D.CHARACTER_MAXIMUM_LENGTH<br> ,D.NUMERIC_PRECISION<br> ,D.NUMERIC_SCALE<br>FROM<br> Cte_Source AS S<br> JOIN Cte_Destination AS D<br> ON D.COLUMN_NAME = S.COLUMN_NAME<br>WHERE<br> S.CHARACTER_MAXIMUM_LENGTH > D.CHARACTER_MAXIMUM_LENGTH<br> OR S.NUMERIC_PRECISION > D.NUMERIC_PRECISION</font></strong></p></blockquote> <p> </p> <p><u>Temporary Table:</u></p> <blockquote> <p><strong><font color="#0000ff">;WITH Cte_Source AS (<br>SELECT <br> C.COLUMN_NAME<br> ,C.DATA_TYPE<br> ,C.CHARACTER_MAXIMUM_LENGTH<br> ,C.NUMERIC_PRECISION<br> ,C.NUMERIC_SCALE<br>FROM<br> tempdb.sys.objects so<br> JOIN tempdb.INFORMATION_SCHEMA.TABLES AS T<br> ON so.name = T.TABLE_NAME<br> AND so.[object_id] = OBJECT_ID('tempdb..#Customer_Data')<br> JOIN tempdb.INFORMATION_SCHEMA.COLUMNS AS C<br> ON C.TABLE_CATALOG = T.TABLE_CATALOG<br> AND C.TABLE_NAME = T.TABLE_NAME<br> AND C.TABLE_SCHEMA = T.TABLE_SCHEMA<br> <br>WHERE<br> T.TABLE_SCHEMA = 'dbo'<br>)<br>,Cte_Destination AS (<br>SELECT <br> C.COLUMN_NAME<br> ,C.DATA_TYPE<br> ,C.CHARACTER_MAXIMUM_LENGTH<br> ,C.NUMERIC_PRECISION<br> ,C.NUMERIC_SCALE<br>FROM<br> INFORMATION_SCHEMA.TABLES AS T<br> JOIN INFORMATION_SCHEMA.COLUMNS AS C<br> ON C.TABLE_CATALOG = T.TABLE_CATALOG<br> AND C.TABLE_NAME = T.TABLE_NAME<br> AND C.TABLE_SCHEMA = T.TABLE_SCHEMA<br>WHERE<br> T.TABLE_NAME = 'Customer_Data' -- Destination Table<br> AND T.TABLE_SCHEMA = 'dbo'<br>)<br>SELECT<br> S.COLUMN_NAME<br> ,S.DATA_TYPE<br> ,S.CHARACTER_MAXIMUM_LENGTH<br> ,S.NUMERIC_PRECISION<br> ,S.NUMERIC_SCALE</font></strong></p> <p><strong><font color="#0000ff"> ,D.COLUMN_NAME<br> ,D.DATA_TYPE<br> ,D.CHARACTER_MAXIMUM_LENGTH<br> ,D.NUMERIC_PRECISION<br> ,D.NUMERIC_SCALE<br>FROM<br> Cte_Source AS S<br> JOIN Cte_Destination AS D<br> ON D.COLUMN_NAME = S.COLUMN_NAME<br>WHERE<br> S.CHARACTER_MAXIMUM_LENGTH > D.CHARACTER_MAXIMUM_LENGTH<br> OR S.NUMERIC_PRECISION > D.NUMERIC_PRECISION</font></strong></p></blockquote> <p> </p> <p>Both the aforementioned queries will return the following result.</p> <p><a href="https://lh3.googleusercontent.com/-P285S7WqPJg/VuAfG8nQYwI/AAAAAAAADow/O8gtVInsXk8/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://lh3.googleusercontent.com/-4nB1w8ybfKI/VuAfHYwUcQI/AAAAAAAADo0/ZF3GPpGYplo/image_thumb%25255B1%25255D.png?imgmax=800" width="802" height="70"></a></p> <p>The reason to return the above three columns as follows:</p> <p>1. <strong>CustId</strong> ==> In our destination table CustId’s data type is TINYINT. Even the select query is returning the results within the boundary, the data type which our insertion query is returning is an INT. So there could be a possibility that there could be large numbers that the destination table could not hold.</p> <p>2. <strong>CustName</strong> ==> ‘Whitacker Jr.’ is exceeding the maximum length of 10 which is in the destination table.</p> <p>3. <strong>MaxCredit</strong> ==> In the destination table the size of the column is numeric (6,2). Which means it can hold values up to <strong>9999.99</strong>. But our insertion query contains a record which consists of <strong>15000.00</strong>.</p> <p> </p> <p>Hope this might be helpful to you.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-80313078145801454852016-03-03T21:22:00.000+08:002016-03-03T21:24:28.940+08:00Extracting Date (Excluding Time) from a DateTime value in SQL Server<p> </p> <p>SQL Server supports many data types where we can store the Date along with the time, such as </p> <ul> <li>DateTime</li> <li>SmallDateTime</li> <li>DateTimeOffset</li> <li>DateTime2</li></ul> <p>But in some cases it’s required only to fetch only the date portion from an aforementioned type of field.</p> <p>There are few ways which we can achieve this task easily using T-SQL.</p> <p>The easiest of the method is to <strong>CAST</strong> the DateTime value directly to a DATE type.</p> <blockquote> <p><strong><font face="Consolas"><font color="#0000ff">SELECT CAST(GETDATE() AS DATE)</font> <font color="#008000">--==> 2016-03-03</font></font></strong></p></blockquote> <p>Also you can achieve this by using the CONVERT function providing different styles as per your requirement.</p> <blockquote> <p><font color="#0000ff" face="Consolas"><strong>SELECT CONVERT(VARCHAR(24),GETDATE(),101) <font color="#008000">--==> 03/03/2016<br></font>SELECT CONVERT(VARCHAR(24),GETDATE(),102) <font color="#008000">--==> 2016.03.03</font></strong></font></p></blockquote> <p>Please refer to the following <a title="https://msdn.microsoft.com/en-sg/library/ms187928.aspx" href="https://msdn.microsoft.com/en-sg/library/ms187928.aspx">URL (<u>https://msdn.microsoft.com/en-sg/library/ms187928.aspx</u>)</a> for more details on the <strong>CONVERT</strong> function and supported styles:</p> <p>But if your requirement is to return a DateTime type but having only the date portion you can use the following syntax:</p> <blockquote> <p><strong><font color="#0000ff" face="Consolas">SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) <font color="#008000">--==> 2016-03-03 00:00:00.000<br></font>SELECT CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS DATETIME) <font color="#008000">--==> 2016-03-03 00:00:00.000<br></font></font></strong></p></blockquote>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-21555168402372350092016-02-16T10:13:00.000+08:002016-02-16T21:16:04.597+08:00Index REBUILD vs. REORGANIZE in SQL SERVER<p>Couple of days back there was an interesting statement (or rather a question) was brought up by one of the colleagues in the company. Ultimately the initial stement left us with one simple question, which is the difference between Index REBUILD and REORGANIZE and when should be exactly use it.</p> <p>If you google the aforementioned you can find numerous posts/blogs regarding this. Therefore I will keep things very simple and easier way to understand.</p> <p>Rebuilding an index or Reorganizing is required when index fragmentation has reached to a considerable percentage. The fragmentation percentage can be identified using the Dynamic Management View - <strong>sys.dm_db_index_physical_stats</strong> in SQL Server. </p> <p>You may get more details on the view on the following link: <a title="https://msdn.microsoft.com/en-us/library/ms188917.aspx" href="https://msdn.microsoft.com/en-us/library/ms188917.aspx" target="_blank">https://msdn.microsoft.com/en-us/library/ms188917.aspx</a></p> <p>You can get a list of fragmented indexes using the following query:</p> <blockquote> <p><strong><font color="#0000ff" face="Consolas">SELECT <br> OBJECT_NAME(Stat.object_id)<br> ,I.name<br> ,Stat.index_type_desc<br> ,Stat.avg_fragmentation_in_percent<br>FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS Stat<br>JOIN sys.indexes AS I <br> ON Stat.index_id = I.index_id <br> AND Stat.object_id = I.object_id<br>WHERE <br> Stat.avg_fragmentation_in_percent > 30</font></strong></p></blockquote> <p>Executing the above query will give you a list of fragmented indexes which has more than 30% fragmentation. ‘index_type_desc’ will give you a hint what sort of index is it. (clustered, non-clustered, heap etc…)</p> <p>As per the guidlines provided by Microsoft, it’s the best practice to Reorganize the index if the fragmentation is less than or equal to 30% (more than 5%) and Rebuild it if it’s more than 30%</p> <p> </p> <p><strong><u>Rebuilding Indexes</u></strong></p> <ul> <li>Should perform this if the fragmentation is more than 30% <li>Operation can be done online or offline</li></ul> <p>Index rebuilding can be done useing the following syntax:</p> <p><u>In order to build all the indexes on a specific table:</u></p> <blockquote> <p><strong><font color="#0000ff" face="Consolas">USE <Database_Name><br>GO</font></strong></p> <p><strong><font color="#0000ff" face="Consolas">ALTER INDEX ALL ON <Table_Name> REBUILD<br>GO</font></strong></p> <p> </p></blockquote> <p><u>In order to build only a specific index:</u></p> <blockquote> <p><strong><font color="#0000ff" face="Consolas">USE <Database_Name><br>GO</font></strong></p> <p><strong><font color="#0000ff" face="Consolas">ALTER INDEX <Index_Name> ON <Table_Name> REBUILD<br>GO</font></strong></p></blockquote> <p> </p> <p><strong><u>Reorganizing Indexes</u></strong></p> <ul> <li>Should perform this if the fragmentation is more than 5% but less than or equal to 30% <li>Operation is always online</li></ul> <p>Index reorganizing can be done using the following syntx:</p> <p>In order to reorganize all the indexes on a specific table:</p> <p><strong><font color="#0000ff" face="Consolas">USE <Database_Name><br>GO</font></strong></p> <p><strong><font color="#0000ff" face="Consolas">ALTER INDEX ALL ON <Table_Name> REORGANIZE<br>GO</font></strong></p> <p>In order to reorganize only a specific index:</p> <p><strong><font color="#0000ff" face="Consolas">USE <Database_Name><br>GO</font></strong></p> <p><strong><font color="#0000ff" face="Consolas">ALTER INDEX <Index_Name> ON <Table_Name> REORGANIZE<br>GO</font></strong></p> <p> </p> <p>Optionally you can set many attributes during the Rebuild or Re-Organize process (Eg: FILLFACTOR, SORT_IN_TEMPDB etc..). Please check on the following link for more details on the REBUILD options: <a title="https://msdn.microsoft.com/en-us/library/ms188388.aspx" href="https://msdn.microsoft.com/en-us/library/ms188388.aspx">https://msdn.microsoft.com/en-us/library/ms188388.aspx</a></p> <p>How ever REBUILD or REORGANIZE will not have an effect on the HEAP fragmentation. In order to remove the heap fragmentation you can use the followng syntax (<strong><font color="#ff0000">*** NOT THE BEST PRACTICE</font></strong>):</p> <blockquote> <p><strong><font color="#ff0000" face="Consolas">USE <Database_Name><br>GO</font></strong></p> <p><strong><font color="#ff0000" face="Consolas">ALTER TABLE <Table_Name> REBUILD<br>GO</font></strong><br></p></blockquote> <p>** Eventhough the aforementioned syntax will remove the HEAP fragmentation, it is considered as bad as creating and dropping a clustered index, which will leave behind lots of fragmentation on non clustered indexes. The best practise would be to create a clustered index on the table to remove the HEAP fragmentation. You can find more details on this on the <a href="http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/">following blog post by <strong>Paul. S Randal</strong></a> which he had illustrated nicely.</p>Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-14158197957766669772016-01-20T11:52:00.000+08:002016-01-20T19:59:06.840+08:00Error accessing Oracle Database Objects via Linked Server in SQL Server (The OLE DB provider "OracleOLEDB.Oracle" for linked server reported an error. Access denied.)<br />
In one of the project we are working these days, there was a requirement to fetch some details, directly from the Oracle Database via VIEWS. Initially everything was setup correctly on the Oracle Database & Server side so that we can access the relevant schemas and fetch data without any issue. And once the oracle client is setup and the configurations are correctly setup (“<strong>tnsnames.ora</strong>”), we were able to fetch the details using .Net Code. And when we checked using the Oracle SQL Developer UI, it was evident that the details were easily fetched. <br />
How ever we faced an issue when we were asked to access and fetch the same set of details from SQL objects using OPENQUERY. Even when we try a simple query such as retrieving “sysdate”, we got an ‘Access Denied’ error.<br />
<br />
<pre>
</pre>
<pre><pre id="codeSnippet" style="background-color: #fbedbb; border-style: none; direction: ltr; font-family: consolas, 'courier new', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 0em; overflow: visible; padding: 0px; width: 636.672px;"><span style="color: blue;">SELECT </span>* <span style="color: blue;">FROM OPENQUERY</span><span style="font-size: 10pt; line-height: 12pt;"> ([LINKED_SERVER], '</span><span style="color: #006080; font-size: 10pt; line-height: 12pt;">SELECT sysdate FROM DUAL</span><span style="font-size: 10pt; line-height: 12pt;">')</span></pre>
</pre>
<strong><span style="color: red;"><br /></span></strong>
<strong><span style="color: red;">The OLE DB provider "OracleOLEDB.Oracle" for linked server reported an error. Access denied. Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "<Linked_Server>"</span></strong>
<br />
<strong><span style="color: red;"><br /></span></strong>
<a href="https://lh3.googleusercontent.com/-MwvZpo158Gw/Vp91FuVxB_I/AAAAAAAADmc/kyiCsU_WTUc/s1600-h/image_thumb%25255B2%25255D%25255B9%25255D.png"><img alt="image_thumb[2]" border="0" height="63" src="https://lh3.googleusercontent.com/-n-fFIrbdqqw/Vp91GMn8jgI/AAAAAAAADmg/yqzhpO_Zz-k/image_thumb%25255B2%25255D_thumb%25255B6%25255D.png?imgmax=800" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image_thumb[2]" width="613" /></a>
<br />
<br />
After spending some time with the configurations on both SQL and Oracle side, we were able to rectify this issue by allowing “<strong>Allow inprocess</strong>” option in linked server providers in SQL side.
<br />
<br />
<a href="https://lh3.googleusercontent.com/-FTLVn5nopmo/Vp91G8Gfc4I/AAAAAAAADms/0Dt4kmpQ61A/s1600-h/image_thumb%25255B4%25255D%25255B6%25255D.png"><img alt="image_thumb[4]" border="0" height="443" src="https://lh3.googleusercontent.com/-rCHonaF_8ls/Vp91HvEHO1I/AAAAAAAADmw/KvL7ahgmw7Q/image_thumb%25255B4%25255D_thumb%25255B3%25255D.png?imgmax=800" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image_thumb[4]" width="614" /></a>
<br />
<br />
I am sharing this hoping that it would help someone to resolve the similar kind of issue without any hassle.
<br />
<br />
<br />
<br />
<br />
<br />
Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0tag:blogger.com,1999:blog-1342610106694475714.post-35784847226511518832015-11-25T10:04:00.000+08:002015-11-25T22:06:33.494+08:00Analyzing SQL Server Error Logs / Agent Logs using T-SQL<div class="wlWriterEditableSmartContent" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:88d20bae-7405-4f73-b88c-3bf56f4e18d6" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
Even though you design our SQL Scripts with the best methods using best practices, or configure the SQL Server to perform correctly and in the optimized manner, you cannot prevent things going wrong. Luckily SQL Server does a great job on logging all the issues which we will be encountering during the course. Things could have been worse if you need to go through the error log file using only a text editor like the ‘Note Pad’ application (Favorite text editor of majority people). But fortunately SQL Server had provided us some help when you need to dig deep into Error Log.</div>
<br />
<a href="http://lh3.googleusercontent.com/-ZBS31Qn7XBo/VlRIrTgWouI/AAAAAAAADhs/C_5E2byFcXc/s1600-h/image%25255B3%25255D.png"><img alt="image" border="0" src="http://lh3.googleusercontent.com/-meXb9FcH1u0/VlRIsYWgKoI/AAAAAAAADhw/JVHii3f_e4c/image_thumb%25255B1%25255D.png?imgmax=800" height="608" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="470" /></a> <br />
<br />
But things could get more complicated if the Error Log contains lots of records and in those records if you require to swim for the issue which you are looking for.<br />
<br />
<a href="http://lh3.googleusercontent.com/-PdA6HjHXoOk/VlRItd-sBgI/AAAAAAAADh0/VNlVM-Og6Rk/s1600-h/image%25255B8%25255D.png"><img alt="image" border="0" src="http://lh3.googleusercontent.com/-peEvY9tSOHc/VlRIugmRd5I/AAAAAAAADh4/T_2kgw6AnK4/image_thumb%25255B4%25255D.png?imgmax=800" height="553" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="771" /></a> <br />
<br />
Even though it provides you some searching and filtering capabilities, it could still be very challenging and time consuming.<br />
<br />
<a href="http://lh3.googleusercontent.com/-aqxdsr7ts0M/VlRIvEjKMxI/AAAAAAAADh8/mn8C8a12Klc/s1600-h/image%25255B12%25255D.png"><img alt="image" border="0" src="http://lh3.googleusercontent.com/-p0twuY0-_ok/VlRIv1yRCKI/AAAAAAAADiA/RoGAktbuj5U/image_thumb%25255B6%25255D.png?imgmax=800" height="455" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="348" /></a> <br />
<br />
<a href="http://lh3.googleusercontent.com/-D2BPYVwZtJ8/VlRIw98vgvI/AAAAAAAADiE/8mC6nFMKbK0/s1600-h/image%25255B17%25255D.png"><img alt="image" border="0" src="http://lh3.googleusercontent.com/-EeW8n8j0UeA/VlRIxtUCQVI/AAAAAAAADiI/nsXEl_t49YE/image_thumb%25255B9%25255D.png?imgmax=800" height="148" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="512" /></a> <br />
<br />
However we do have another workaround which might come in handy. That’s to query the Error Logs using T-SQL. This can be done using the system procedure ‘sys.sp_readerrorlog’. This consists with few parameters.<br />
<div id="codeSnippetWrapper" style="background-color: #fbedbb; border-bottom: #fbedbb 1px solid; border-left: #fbedbb 1px solid; border-right: #fbedbb 1px solid; border-top: #fbedbb 1px solid; cursor: text; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #fbedbb; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">USE</span> [master]
<span style="color: blue;">GO</span>
<span style="color: green;">/****** Object: StoredProcedure [sys].[sp_readerrorlog] Script Date: 24/11/2015 7:11:36 PM ******/</span>
<span style="color: blue;">SET</span> ANSI_NULLS <span style="color: blue;">ON</span>
<span style="color: blue;">GO</span>
<span style="color: blue;">SET</span> QUOTED_IDENTIFIER <span style="color: blue;">OFF</span>
<span style="color: blue;">GO</span>
<span style="color: blue;">ALTER</span> <span style="color: blue;">proc</span> [sys].[sp_readerrorlog](
@p1 <span style="color: blue;">int</span> = 0,
@p2 <span style="color: blue;">int</span> = <span style="color: blue;">NULL</span>,
@p3 nvarchar(4000) = <span style="color: blue;">NULL</span>,
@p4 nvarchar(4000) = <span style="color: blue;">NULL</span>)
<span style="color: blue;">as</span>
<span style="color: blue;">begin</span>
<span style="color: blue;">if</span> (<span style="color: blue;">not</span> is_srvrolemember(N<span style="color: #006080;">'securityadmin'</span>) = 1)
<span style="color: blue;">begin</span>
<span style="color: blue;">raiserror</span>(15003,-1,-1, N<span style="color: #006080;">'securityadmin'</span>)
<span style="color: blue;">return</span> (1)
<span style="color: blue;">end</span>
<span style="color: blue;">if</span> (@p2 <span style="color: blue;">is</span> <span style="color: blue;">NULL</span>)
<span style="color: blue;">exec</span> sys.xp_readerrorlog @p1
<span style="color: blue;">else</span>
<span style="color: blue;">exec</span> sys.xp_readerrorlog @p1,@p2,@p3,@p4
<span style="color: blue;">end</span></pre>
<br /></div>
<br />
<ol><br />
<li><strong>@p1</strong> –> This represents the error log which you need to inspect (0 ~ Current | 1 ~ Archive #1 etc..) </li>
<li><strong>@p2</strong> –> Type of the error log which you want to inspect (NULL or 1 ~ Error Log | 2 ~ SQL Agent Log) </li>
<li><strong>@p3</strong> –> 1st Search Parameter (A value which you want to search the contents for) </li>
<li><strong>@p4</strong> –> 2nd Search Parameter (A value which you want to search to further refine the result set)</li>
</ol>
<br />
<strong><span style="color: red;">**Please note: Aforementioned parameters are optional. Therefore if you don’t provide any parameters, it will return the whole contents of the current/active Error Log.</span></strong><br />
<br />
Few Examples<br />
<br />
1. This will return all entries in the current Error Log<br />
<div id="codeSnippetWrapper" style="background-color: #fbedbb; border-bottom: #fbedbb 1px solid; border-left: #fbedbb 1px solid; border-right: #fbedbb 1px solid; border-top: #fbedbb 1px solid; cursor: text; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #fbedbb; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> sys.xp_readerrorlog @p1 = 0</pre>
<br /></div>
<br />
2. This will return all the entries in the current SQL Agent Log<br />
<div id="codeSnippetWrapper" style="background-color: #fbedbb; border-bottom: #fbedbb 1px solid; border-left: #fbedbb 1px solid; border-right: #fbedbb 1px solid; border-top: #fbedbb 1px solid; cursor: text; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #fbedbb; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> sys.xp_readerrorlog @p1 = 0, @p2 = 2</pre>
<br /></div>
<br />
3. This will return all the entries in the current SQL Error log where ever the value ‘CLR’ consist.<br />
<div id="codeSnippetWrapper" style="background-color: #fbedbb; border-bottom: #fbedbb 1px solid; border-left: #fbedbb 1px solid; border-right: #fbedbb 1px solid; border-top: #fbedbb 1px solid; cursor: text; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #fbedbb; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> sys.sp_readerrorlog @p1=0, @p2=1, @p3=<span style="color: #006080;">'CLR'</span></pre>
<br /></div>
<br />
<a href="http://lh3.googleusercontent.com/-Kl6ZSfj9hmg/VlUVVPImmyI/AAAAAAAADi4/YIlPv7ySgLk/s1600-h/image%25255B30%25255D.png"><img alt="image" border="0" src="http://lh3.googleusercontent.com/-wkefnuF6yZk/VlUVWAwMhhI/AAAAAAAADi8/-xzX4Z8yPrY/image_thumb%25255B18%25255D.png?imgmax=800" height="65" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="923" /></a> <br />
<br />
<br />
4. This will return the entries in the current SQL Error log when the value ‘CLR’ and ‘Framework’ exist.<br />
<div id="codeSnippetWrapper" style="background-color: #fbedbb; border-bottom: #fbedbb 1px solid; border-left: #fbedbb 1px solid; border-right: #fbedbb 1px solid; border-top: #fbedbb 1px solid; cursor: text; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #fbedbb; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> sys.sp_readerrorlog @p1=0, @p2=1, @p3=<span style="color: #006080;">'CLR'</span>, @p4=<span style="color: #006080;">'Framework'</span></pre>
<br /></div>
<br />
<a href="http://lh3.googleusercontent.com/-rhowWpVU9LA/VlUVWhywiyI/AAAAAAAADjA/_C7c5JFUQ28/s1600-h/image%25255B29%25255D.png"><img alt="image" border="0" src="http://lh3.googleusercontent.com/-gMzXtDY_fD8/VlUVXVoM-XI/AAAAAAAADjE/3rgviC2HvNY/image_thumb%25255B17%25255D.png?imgmax=800" height="62" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="925" /></a> <br />
<br />
When we execute the stored procedure ‘sys.sp_readerrorlog’, inside it will call an extended stored procedure which will accept 7 parameters, which is ‘<strong>sys.xp_readerrorlog</strong>’. The parameter details are as follows:<br />
<br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 922px;"><tbody>
<tr><td valign="top" width="70"><strong>Param #</strong></td><td valign="top" width="161"><strong>Parameter</strong></td><td valign="top" width="689"><strong>Details</strong></td></tr>
<tr><td valign="top" width="70">1</td><td valign="top" width="161">Log Number</td><td valign="top" width="689">0 – Current / 1 – Archive #1 / 2 – Archive #2 etc…</td></tr>
<tr><td valign="top" width="70">2</td><td valign="top" width="161">Log Type</td><td valign="top" width="689">1 – SQL Error Log / 2 – SQL Agent Log</td></tr>
<tr><td valign="top" width="70">3</td><td valign="top" width="161">Search Text 1</td><td valign="top" width="689">Search term which will be searched on the Text column</td></tr>
<tr><td valign="top" width="70">4</td><td valign="top" width="161">Search Text 2</td><td valign="top" width="689">Search term which will be searched on the Text column. <strong><span style="color: red;">**If both search texts are supplied it will return rows containing both texts.</span></strong></td></tr>
<tr><td valign="top" width="70">5</td><td valign="top" width="161">Start Date</td><td valign="top" width="689">Log entries which the ‘Log Date’ is newer than the date provided. (including the date provided)</td></tr>
<tr><td valign="top" width="70">6</td><td valign="top" width="161">End Date</td><td valign="top" width="689">Log entries which is between the Start Date and End Date</td></tr>
<tr><td valign="top" width="70">7</td><td valign="top" width="161">Sort Order</td><td valign="top" width="689">ASC – Ascending / DESC - Descending</td></tr>
</tbody></table>
<br />
<strong>Eg:</strong><br />
<br />
<div id="codeSnippetWrapper" style="background-color: #fbedbb; border-bottom: #fbedbb 1px solid; border-left: #fbedbb 1px solid; border-right: #fbedbb 1px solid; border-top: #fbedbb 1px solid; cursor: text; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #fbedbb; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: consolas, 'Courier New', courier, monospace; font-size: 10pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> sys.xp_readerrorlog 0,1,N<span style="color: #006080;">''</span>,N<span style="color: #006080;">''</span>, <span style="color: #006080;">'20151124'</span>,<span style="color: #006080;">'20151125'</span>,<span style="color: #006080;">'DESC'</span> </pre>
<br /></div>
<br />
I hope this information will help you when you need to query the Error Log in order to troubleshoot an issue.Manjuke Fernandohttp://www.blogger.com/profile/08488564354702356321noreply@blogger.com0