tag:blogger.com,1999:blog-78837690065080813602024-03-06T00:31:49.531-08:00SQL Tips & TricksIts all about structured query language (SQL) - SQL Query Snippets, LINQ Snippets, SQL Stored Procedures, SQL Server Articles, SQL Server Feeds, SQL Server Analytics, SQL Server Integration Services (SSIS), Microsoft SQL Server BIG DATA - HortonWorks, SQL Server CRM IntegrationUnknownnoreply@blogger.comBlogger72125tag:blogger.com,1999:blog-7883769006508081360.post-6304791741624778942013-05-14T02:11:00.000-07:002013-05-14T02:11:02.155-07:00How to Perform Analysis with SQL Server Analysis Services Project (Step by Step Instruction)<strong><span style="color: red; font-size: large;">How to Create Cubes, Dimensions and Measures in SQL Server Business Intelligence </span></strong><br />
<br />
What is the use of data if it is not for Analysis. What is the use of RDBMS if it is not providing the Analytics functionality. Here we take a look at the SQL Services Project of SQL Server 2008<br />
<br />
There are multiple open source softwares like Pentaho / Jasper etc. that provide the functionality we will see below but nothing comes with the ease like SSAS<br />
<br />
To start, open Business Intelligence Studio and create Analysis Services Project<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLUGy1-h_Bx-pgq-vdAC8IM0QZQgI9bCRIVsfJNkUlhwqh7Vqc-Sk50kbh6k52zJzzJNVjZ1tXtlrjObGwDOpoUzFK5DuEsGTLAbXkF6zOfQxH0JoWP7GoSdgJtJOryQX1xUU1nUeMOc0/s1600/SQL+Server+Analysis+Services+Project.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="478" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLUGy1-h_Bx-pgq-vdAC8IM0QZQgI9bCRIVsfJNkUlhwqh7Vqc-Sk50kbh6k52zJzzJNVjZ1tXtlrjObGwDOpoUzFK5DuEsGTLAbXkF6zOfQxH0JoWP7GoSdgJtJOryQX1xUU1nUeMOc0/s640/SQL+Server+Analysis+Services+Project.png" width="640" /></a></div>
The wizard will create a project aling with required folders - Dimensions and Cubes the two most important of any Business Intelligence project<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQaeRbRPveaEBIL4YKX5dIvWG0jQKK3mCaV2pD75POSwsOcXvVoi3z-j-5PPPSTNo3YZArZlCekGdLCL6z1-ustA0e4n0IKLoKIuY9VfqHQtb99_A8FfOwjJz2ysTG_-pbCk3wYHGzY24/s1600/SQL+Server+Analysis+Services+Project+2+-+Project+Directory.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQaeRbRPveaEBIL4YKX5dIvWG0jQKK3mCaV2pD75POSwsOcXvVoi3z-j-5PPPSTNo3YZArZlCekGdLCL6z1-ustA0e4n0IKLoKIuY9VfqHQtb99_A8FfOwjJz2ysTG_-pbCk3wYHGzY24/s320/SQL+Server+Analysis+Services+Project+2+-+Project+Directory.png" width="219" /></a></div>
<br />
Creating Cube is easy in SQL Server. Right click on Cubes and select New Cube - a beautiful Wizard will guide <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwDPo6Sxr6-lX_-yDD54XLXLkFW1H71BR4dPYBUQiBp6WTPCHrgxFbS_-6n7v7zCdGohJ77BOeswNfbbGJtmSsl2rxZ6L91LZ7jMcjbRtd90Q6tL_EG1jhnRQplpTmLRQM3rHpqo1X6Xg/s1600/SQL+Server+Analysis+Services+Project+3+-+Cube+Wizard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwDPo6Sxr6-lX_-yDD54XLXLkFW1H71BR4dPYBUQiBp6WTPCHrgxFbS_-6n7v7zCdGohJ77BOeswNfbbGJtmSsl2rxZ6L91LZ7jMcjbRtd90Q6tL_EG1jhnRQplpTmLRQM3rHpqo1X6Xg/s320/SQL+Server+Analysis+Services+Project+3+-+Cube+Wizard.png" width="320" /></a></div>
<br />
Select the Generate tables option <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj118bEMSDc_62u2kMoALIHLk743Xhav4r57kg22DRXbIibwITOyQaT70mQxyM815CMSsrD2pTHNF7oTa8CMYj0hE-wFx1Gt4AcXpjYPHOwAyhEYbX9EFpC1ObNHbtktjBcm6VICk7zZe8/s1600/SQL+Server+Analysis+Services+Project+4+-+Cube+Wizard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj118bEMSDc_62u2kMoALIHLk743Xhav4r57kg22DRXbIibwITOyQaT70mQxyM815CMSsrD2pTHNF7oTa8CMYj0hE-wFx1Gt4AcXpjYPHOwAyhEYbX9EFpC1ObNHbtktjBcm6VICk7zZe8/s320/SQL+Server+Analysis+Services+Project+4+-+Cube+Wizard.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5vUByqMW6hdkcKJHolN0aErKvLxsJ-sdWT2GJKXEihErVJ-QlbgR0JLZ-oqAEFUfjDAO5DhEJO3cTLcfv5Xrq7yrhJsZayZfD50C1DzHoOOCKydKV-mrkvA8N3jhsaKtg5-TFr0SbFh8/s1600/SQL+Server+Analysis+Services+Project+5+-+Cube+Wizard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5vUByqMW6hdkcKJHolN0aErKvLxsJ-sdWT2GJKXEihErVJ-QlbgR0JLZ-oqAEFUfjDAO5DhEJO3cTLcfv5Xrq7yrhJsZayZfD50C1DzHoOOCKydKV-mrkvA8N3jhsaKtg5-TFr0SbFh8/s320/SQL+Server+Analysis+Services+Project+5+-+Cube+Wizard.png" width="320" /></a></div>
The below step helps you in Creating new Dimensions - OLAP is all about measures (Facts and Dimensions)<br />
<br />
<strong><span style="color: red;">Facts and Dimensions in SQL Server</span></strong><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRsVO9GPhOqzixiEi4LcT2eb3Bs3s6IZ_eNAsT1JCkNgs4CbOzLtiCn5RuamDArhavzSztvs6rcUJH1Kx8npcwM6T8qFAbFNtb8Mk44TCKwJeCE3aNZr31LUdcxM0okTCrYRD9UtaMjNQ/s1600/SQL+Server+Analysis+Services+Project+6+-+Cube+Wizard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRsVO9GPhOqzixiEi4LcT2eb3Bs3s6IZ_eNAsT1JCkNgs4CbOzLtiCn5RuamDArhavzSztvs6rcUJH1Kx8npcwM6T8qFAbFNtb8Mk44TCKwJeCE3aNZr31LUdcxM0okTCrYRD9UtaMjNQ/s320/SQL+Server+Analysis+Services+Project+6+-+Cube+Wizard.png" width="320" /></a></div>
<br />
Time dimension is created and filled automatically by SQL Server based on settings<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUpvKb_YFZS1YcKUxGZ7Uas7j-s3eOhwOf_NRfxA73voMgo1wD4sO9Dniraqu0-xl4uWo3cbwr-0MDl77TuPm_QdxJZfUyb-6viWD-ImSKAV4AH0E-_PkAnFngSrG4NyM7e2rOw3aWHEM/s1600/SQL+Server+Analysis+Services+Project+7+-+Cube+Wizard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUpvKb_YFZS1YcKUxGZ7Uas7j-s3eOhwOf_NRfxA73voMgo1wD4sO9Dniraqu0-xl4uWo3cbwr-0MDl77TuPm_QdxJZfUyb-6viWD-ImSKAV4AH0E-_PkAnFngSrG4NyM7e2rOw3aWHEM/s320/SQL+Server+Analysis+Services+Project+7+-+Cube+Wizard.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrmFUUX-pXGMCmsy-1xVc_iDH6LyyAq0kLlM7yulNfsgfm1rLhDUl3yUo3C4GeHmYK70lHvOjfdMJBn9Lsv_FWVzqdirBneBelT8b5zy6qymh4qQmX8BkfY_eCw21AFQKTjYsZnfWZZPg/s1600/SQL+Server+Analysis+Services+Project+8+-+Define+Dimension+Usage.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrmFUUX-pXGMCmsy-1xVc_iDH6LyyAq0kLlM7yulNfsgfm1rLhDUl3yUo3C4GeHmYK70lHvOjfdMJBn9Lsv_FWVzqdirBneBelT8b5zy6qymh4qQmX8BkfY_eCw21AFQKTjYsZnfWZZPg/s320/SQL+Server+Analysis+Services+Project+8+-+Define+Dimension+Usage.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVKuYPyMaOV-p2jW2we_DLU0Lj2RuiasYzVclXX46ZpgNARNzd_tEUW48kDaKeOVT0iYyD9btInKFj0Z_bMceNp01FsuQmsmxCJLgjyvmcTExIvPVPJvzDCe1iF2ejkKdgZXb6iWgCi_s/s1600/SQL+Server+Analysis+Services+Project+9+-+Review+Cube+and+Schema.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVKuYPyMaOV-p2jW2we_DLU0Lj2RuiasYzVclXX46ZpgNARNzd_tEUW48kDaKeOVT0iYyD9btInKFj0Z_bMceNp01FsuQmsmxCJLgjyvmcTExIvPVPJvzDCe1iF2ejkKdgZXb6iWgCi_s/s320/SQL+Server+Analysis+Services+Project+9+-+Review+Cube+and+Schema.png" width="320" /></a>The above figure shows the measures and dimensions created by SQL SERVER. </div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: left;">
Now we have the logical data model, we can create the Schema . <span style="color: red;"><strong>SQL SERver Schema Creation Wizard</strong></span> helps to create the required schema</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLTyQssWV0RPXlR8Alwc4U72tKcSAebvvfW0mKAKXLUmyJdy8Y6oB-_gQaylx-geTlXUJSUn_Jo3znvs6uu6J39CUUnbi5UE9cbp_PTg2HSmUcs3qULmIk6b4q8gsd2SSYT2TzF8l0FSo/s1600/SQL+Server+Analysis+Services+Project+10+-+Schema+Generation+Wizard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLTyQssWV0RPXlR8Alwc4U72tKcSAebvvfW0mKAKXLUmyJdy8Y6oB-_gQaylx-geTlXUJSUn_Jo3znvs6uu6J39CUUnbi5UE9cbp_PTg2HSmUcs3qULmIk6b4q8gsd2SSYT2TzF8l0FSo/s320/SQL+Server+Analysis+Services+Project+10+-+Schema+Generation+Wizard.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3vWSLTNu349qvxBnRR6iDxK9ywBZJCEdzdTWVMpZrjbvmgoLk8Ho9Sy0VW8bK5lCKzhjNQfolPM1SBRFaUpJnbuFEzbxKzOLL7pCCJt-MvPJFVlucfgO4vbpwaqpHDRQin4Yc8SiBe0k/s1600/SQL+Server+Analysis+Services+Project+11+-+Data+Source+Wizard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3vWSLTNu349qvxBnRR6iDxK9ywBZJCEdzdTWVMpZrjbvmgoLk8Ho9Sy0VW8bK5lCKzhjNQfolPM1SBRFaUpJnbuFEzbxKzOLL7pCCJt-MvPJFVlucfgO4vbpwaqpHDRQin4Yc8SiBe0k/s320/SQL+Server+Analysis+Services+Project+11+-+Data+Source+Wizard.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2KMWQWmTiWO9DpjNDlLZf4N3f3yPZ7anrAl1Lnr1oNSzc8MoMIk5NpzqNM8ZioDt7JdnW4ZHQg3CGAne1xT0H7ckwNTnA1NiVbnD03b-QjWHfwFk9-7mRjPS2YBWrVaUELJjSEk1dGrk/s1600/SQL+Server+Analysis+Services+Project+12+-+Schema+Generation+Progress.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="304" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2KMWQWmTiWO9DpjNDlLZf4N3f3yPZ7anrAl1Lnr1oNSzc8MoMIk5NpzqNM8ZioDt7JdnW4ZHQg3CGAne1xT0H7ckwNTnA1NiVbnD03b-QjWHfwFk9-7mRjPS2YBWrVaUELJjSEk1dGrk/s320/SQL+Server+Analysis+Services+Project+12+-+Schema+Generation+Progress.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKS98F1APCRbFKdgbG2JiA6EOwnvuh0ZHlnrV2PxxIvwZMSwIFC_1yARnG5FW_vm26oLWpwebs7VpETQL_ZTaqHK_9_soWYSV4dsv8jBbcUa-zy_H53dH7HVVNO1gVWr0gx60DiY2_MyI/s1600/SQL+Server+Analysis+Services+Project+13+-+Sales+Cube+Final.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="168" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKS98F1APCRbFKdgbG2JiA6EOwnvuh0ZHlnrV2PxxIvwZMSwIFC_1yARnG5FW_vm26oLWpwebs7VpETQL_ZTaqHK_9_soWYSV4dsv8jBbcUa-zy_H53dH7HVVNO1gVWr0gx60DiY2_MyI/s320/SQL+Server+Analysis+Services+Project+13+-+Sales+Cube+Final.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig1GLMC_SZyNZ_FxGp6VPgJUHV4NrM1-OHRdH974gd0_0Zx-E0XySKkTk2MvSlfuaU0y0CuV3oc-RN8vA35oS1KqwBmUfyAIZ69tyT_MKN9kfBLJvg70BEQDxRtReibpYe2IYqbr7Emy8/s1600/SQl+Server+2012.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="96" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig1GLMC_SZyNZ_FxGp6VPgJUHV4NrM1-OHRdH974gd0_0Zx-E0XySKkTk2MvSlfuaU0y0CuV3oc-RN8vA35oS1KqwBmUfyAIZ69tyT_MKN9kfBLJvg70BEQDxRtReibpYe2IYqbr7Emy8/s320/SQl+Server+2012.jpg" width="320" /></a></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-21429016625045792142013-05-08T21:22:00.000-07:002013-05-08T21:22:38.278-07:00How to import Large files (TXT / CSV) to SQL Server 2008 using SSIS<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY8xBklqFG32lzan9BXg_2hyU4Q7VyaVVn-vMNUWZe4UlEpjDfsMtbUT32CevnL6QatbklpNRQo4C8Jpj1iA5-gnRG_hJmF13w_l1hSw1Q5_z4KvLQD-CCsZGkVym-1lxtbitLXusepdQ/s1600/SQL+Server+Load+Files+Import+Large+Files+using+SSIS.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY8xBklqFG32lzan9BXg_2hyU4Q7VyaVVn-vMNUWZe4UlEpjDfsMtbUT32CevnL6QatbklpNRQo4C8Jpj1iA5-gnRG_hJmF13w_l1hSw1Q5_z4KvLQD-CCsZGkVym-1lxtbitLXusepdQ/s320/SQL+Server+Load+Files+Import+Large+Files+using+SSIS.png" width="239" /> SQL Server's Integration Services provides the functionality to laod</a></div>
<br />
<br />
This can be done from the SQL Server
by selecting the integration services project
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtQKWRv2WWbrcFGmSX4bRkYnsHQFzpMH62MtSNkQ7OqiPLxC02UPKpA_CsNAQRWj5QUUXEEBZOmsLXwP6SlwrANyQWbZli0W6x21lL3ZWdc9DAuJZFsHPfeYvceIJtHdj58ZZtmchaAZA/s1600/SQL+Server+Load+Files+Import+Large+Files+using+SSIS+3.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="478" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtQKWRv2WWbrcFGmSX4bRkYnsHQFzpMH62MtSNkQ7OqiPLxC02UPKpA_CsNAQRWj5QUUXEEBZOmsLXwP6SlwrANyQWbZli0W6x21lL3ZWdc9DAuJZFsHPfeYvceIJtHdj58ZZtmchaAZA/s640/SQL+Server+Load+Files+Import+Large+Files+using+SSIS+3.png" width="640" /></a>
</div>
<br />
The following
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4h9wb1n_W5VIJkdDsBm8HNBHNj3UZPFBKvPaSzl7XZTysI50ZdVftPJqWTCRNbDrfD4dBNhyTiy9YZoH0mikQn3iPj7BCbRtZlpDdpx8q1QvarAKlw2ux7eLoLpC5xrU0sm-C2l6xjQg/s1600/SQL+Server+Load+Files+Import+Large+Files+using+SSIS+s.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="287" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4h9wb1n_W5VIJkdDsBm8HNBHNj3UZPFBKvPaSzl7XZTysI50ZdVftPJqWTCRNbDrfD4dBNhyTiy9YZoH0mikQn3iPj7BCbRtZlpDdpx8q1QvarAKlw2ux7eLoLpC5xrU0sm-C2l6xjQg/s320/SQL+Server+Load+Files+Import+Large+Files+using+SSIS+s.png" width="320" /></a><br />
<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-65510963505925402652012-09-10T01:37:00.003-07:002012-09-10T01:37:55.993-07:00How to import CSV / Excel file to SQL Server Table How to Load Excel File / CSV File to a SQL Server Table / Create SQL Server Table from Excel File<br />
<br />
Step 1: Select Import and Export Wizard from Program<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZeeniy-quANMsdLRFffrgVyMh58_x6gmzgEDxzaXaehDc9BimHJv3XQ-JjLn11Yj1Cw95acrQrkn0RFWN69UpUJgAhaQ4hf-HEOA805A-TkqHtNQB5TuQWVIop8V4ASX3Nh9VlmIsJRM/s1600/SQL+Server+2008+-+Import+From+Excel+1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="235" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZeeniy-quANMsdLRFffrgVyMh58_x6gmzgEDxzaXaehDc9BimHJv3XQ-JjLn11Yj1Cw95acrQrkn0RFWN69UpUJgAhaQ4hf-HEOA805A-TkqHtNQB5TuQWVIop8V4ASX3Nh9VlmIsJRM/s320/SQL+Server+2008+-+Import+From+Excel+1.png" width="320" /></a><br />
<br />
Step 2: Select Import and Export Wizard from Program<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZxpgbSvsBECPgzFzfx8ydSVX9cXnWcA3A2DiK0V3bqjzlfPusTOJLq21lzdFH4OX4NRarx5B8BrPxr7KpuqdXuTKwj4irXcwuUxzKjVuPVzNOfLN4SUldwFIkf5tg1BmL5qOskktMQuU/s1600/SQL+Server+2008+-+Import+From+Excel+2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="179" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZxpgbSvsBECPgzFzfx8ydSVX9cXnWcA3A2DiK0V3bqjzlfPusTOJLq21lzdFH4OX4NRarx5B8BrPxr7KpuqdXuTKwj4irXcwuUxzKjVuPVzNOfLN4SUldwFIkf5tg1BmL5qOskktMQuU/s320/SQL+Server+2008+-+Import+From+Excel+2.png" width="320" /></a></div>
Step 3: Select Flat File as Source<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqSPHxJLHNCFHmqo0psREjCWMmgWHa-zuEcvKRlC0499ts5-KL_J_ocZcLlcyAJ_WLcOh3l6OZlLg5sgWad8tMcODu14M9khjA_nJUMHNT9Lo1RsMnZnqeh1FoTjP4yBM9GERN1R_S0qg/s1600/SQL+Server+2008+-+Import+From+Excel+3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="336" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqSPHxJLHNCFHmqo0psREjCWMmgWHa-zuEcvKRlC0499ts5-KL_J_ocZcLlcyAJ_WLcOh3l6OZlLg5sgWad8tMcODu14M9khjA_nJUMHNT9Lo1RsMnZnqeh1FoTjP4yBM9GERN1R_S0qg/s640/SQL+Server+2008+-+Import+From+Excel+3.png" width="640" /></a></div>
<br />
Step 4: Select the CSV / Text file<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPLPAfKKbyUpsPOL6LysmaI5-1M1eglZluavgDW7Js1IX9KVD8NgN_Lrc9qucSUrzz7OWV225m1_yKCTvDQuoyWyiZwASG8TQ5nn4rHdClQ4yJvJWHJvUZ1Hp2jcsmkm171Do83c658Ow/s1600/SQL+Server+2008+-+Import+From+Excel+4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPLPAfKKbyUpsPOL6LysmaI5-1M1eglZluavgDW7Js1IX9KVD8NgN_Lrc9qucSUrzz7OWV225m1_yKCTvDQuoyWyiZwASG8TQ5nn4rHdClQ4yJvJWHJvUZ1Hp2jcsmkm171Do83c658Ow/s640/SQL+Server+2008+-+Import+From+Excel+4.png" width="640" /></a></div>
<br />
Step 5: Choose the Server<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzeZxfpHN6wCBHJUN_TC8UOwbXbfQS91f7JvVJQPArW-zMyLPlnEIxKr4QnhzwiFlbIrFkIGemQsNm_jWfbVC-HwXxauFOLWIJ9Qbz1luj7u8X1ejJgxfgmLC7o7sxVXo1hnXzLiJsN6k/s1600/SQL+Server+2008+-+Import+From+Excel+5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzeZxfpHN6wCBHJUN_TC8UOwbXbfQS91f7JvVJQPArW-zMyLPlnEIxKr4QnhzwiFlbIrFkIGemQsNm_jWfbVC-HwXxauFOLWIJ9Qbz1luj7u8X1ejJgxfgmLC7o7sxVXo1hnXzLiJsN6k/s640/SQL+Server+2008+-+Import+From+Excel+5.png" width="640" /></a></div>
<br />
Step 6: Choose Database<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEia0eBEVvbsgB9Qt1hQOo__iMMFRTAzYLmLPS3DmCr0uB7ARJVN_JmzFilG-nNQqRfKrm8D7xhlRO9OGvf4FRWrdull4lVe_YZQgOhLV2MXlg_YOtqVh9Gy222j0XPsS8vhhbeBPVK5_l4/s1600/SQL+Server+2008+-+Import+From+Excel+5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEia0eBEVvbsgB9Qt1hQOo__iMMFRTAzYLmLPS3DmCr0uB7ARJVN_JmzFilG-nNQqRfKrm8D7xhlRO9OGvf4FRWrdull4lVe_YZQgOhLV2MXlg_YOtqVh9Gy222j0XPsS8vhhbeBPVK5_l4/s640/SQL+Server+2008+-+Import+From+Excel+5.png" width="640" /></a></div>
<br />
Step 7: Specify Schema and Table Names<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqCxQUYhtxuHnH5p2D51ZV-kMfm220LRAc-QLIQGnz0I-b5rdj0A6jbl5aQCv2WybF5IZc16fMai2nTibT1ZAKV_3w5i4AdVMWRiUCzJ-RCXbJUSTo4FeOu-mttn-5LyZnR6-nQsThEJs/s1600/SQL+Server+2008+-+Import+From+Excel+6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="336" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqCxQUYhtxuHnH5p2D51ZV-kMfm220LRAc-QLIQGnz0I-b5rdj0A6jbl5aQCv2WybF5IZc16fMai2nTibT1ZAKV_3w5i4AdVMWRiUCzJ-RCXbJUSTo4FeOu-mttn-5LyZnR6-nQsThEJs/s640/SQL+Server+2008+-+Import+From+Excel+6.png" width="640" /></a></div>
Step 8: Run the Pachage<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCjDxBhYNNbgcuN3E8RXz3_93cdmGz0RnkTd0ZWt9uD4dfxH9In88PMiRNwhPaaIiJ1aMuDkZBcd72WYhFEtMkOVMw2QDMHCqidkR4uDIRwwu7iF-uS_EFJl3pbmYdx5NC82er0fTey7E/s1600/SQL+Server+2008+-+Import+From+Excel+7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="336" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCjDxBhYNNbgcuN3E8RXz3_93cdmGz0RnkTd0ZWt9uD4dfxH9In88PMiRNwhPaaIiJ1aMuDkZBcd72WYhFEtMkOVMw2QDMHCqidkR4uDIRwwu7iF-uS_EFJl3pbmYdx5NC82er0fTey7E/s640/SQL+Server+2008+-+Import+From+Excel+7.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZeeniy-quANMsdLRFffrgVyMh58_x6gmzgEDxzaXaehDc9BimHJv3XQ-JjLn11Yj1Cw95acrQrkn0RFWN69UpUJgAhaQ4hf-HEOA805A-TkqHtNQB5TuQWVIop8V4ASX3Nh9VlmIsJRM/s1600/SQL+Server+2008+-+Import+From+Excel+1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a> </div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-85718965464873642362012-09-10T00:39:00.002-07:002012-09-10T00:39:40.655-07:00Specified column precision 50 is greater than the maximum precision of 38.<strong><span style="color: red;">What is the Preferred Data Type for Price and Amount Fields</span></strong><br />
<br />
This error occurs when you are trying to define a Decimal field with precision more than 38. If you would require more precission, please use any of the following:<br />
<br />
<span style="font-size: x-small;"> [Sales Price] [Numeric]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">50</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">2</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"> [Quantity] [int]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">50</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"> [Amount] [Numeric]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">50</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">2</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span><br />
Money<br />
Float<br />
<br />
<br />
<br />
<code>
CREATE TABLE [dbo].[Transact.Details](
<br />
[Store ID] [varchar](50) NULL,
<br />
[Customer ID] [varchar](50) NULL,
<br />
[Transaction ID] [varchar](50) NULL,
<br />
[Transaction Date] [varchar](50) NULL,
<br />
[Category ID] [varchar](50) NULL,
<br />
[Item ID] [varchar](50) NULL,
<br />
[Sales Price] [Numeric](38,2) NULL,
<br />
[Quantity] [int] NULL,
<br />
[Amount] [Numeric](38,2) NULL,
<br />
) ON [PRIMARY]
<br />
</code>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-62431273165312663272012-07-08T06:54:00.002-07:002012-07-08T06:54:48.823-07:00SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.The error occurs when I try to run the BCP utility<br />
<br />
<span style="font-size: xx-small;"></span>
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-60082342278784770522012-07-08T05:49:00.002-07:002012-07-08T05:49:52.084-07:00How to Export Query as XML - SQL Server 2008<strong><span style="color: red;">Save Recordset as XML using Select Query in SQL Server 2008</span></strong><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Let us consider the a table (shown below) with couple of columns that needs to be exported as XML</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkO4QH6PedE_ffbEa9R8MaGvhhQ8mBlFPq72pfJEXn70K3dmKmrcWI5-_MLUEVLNofqzC0cksMVlucNFUWtnlJ0IZtkyMTmtvgX0ijMRxBRVlG3ntPbzSagGM2-NHPXTAMBm3wnPDbwfs/s1600/SQL+Server+2008+-+Export+Query+as+XML+1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="462" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkO4QH6PedE_ffbEa9R8MaGvhhQ8mBlFPq72pfJEXn70K3dmKmrcWI5-_MLUEVLNofqzC0cksMVlucNFUWtnlJ0IZtkyMTmtvgX0ijMRxBRVlG3ntPbzSagGM2-NHPXTAMBm3wnPDbwfs/s640/SQL+Server+2008+-+Export+Query+as+XML+1.png" width="640" /></a></div>
<br />
The following query <br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TOP</span></span><span style="font-size: x-small;"> 1000 [TrainID] </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'TrainNum'</span></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">[TrainName]</span><br />
<span style="font-size: x-small;">
</span><br />
<span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> [OnlineTrans]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">[dbo]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">[TrainMaster] </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">for</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">XML</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">PATH</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"></span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">Is used for generating a XML </span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAH1yqcqj_2Moiw2Q4fbR8v9cqToKQ1SWzCHF5ZwoKAF4Ape97-0ZAOAg_biuWTLqqtQdMP8hTOwGQvALvt8Zm8j9DjtRKbN-q2U4X5eFNv-NevOmNDnqN8ttA1nyCOnG6gOj9Npu9UPc/s1600/SQL+Server+2008+-+Export+Query+as+XML+2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="356" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAH1yqcqj_2Moiw2Q4fbR8v9cqToKQ1SWzCHF5ZwoKAF4Ape97-0ZAOAg_biuWTLqqtQdMP8hTOwGQvALvt8Zm8j9DjtRKbN-q2U4X5eFNv-NevOmNDnqN8ttA1nyCOnG6gOj9Npu9UPc/s640/SQL+Server+2008+-+Export+Query+as+XML+2.png" width="640" /></a></div>
On the other hand, If you want to have a meaningfully named element instead of <row>, you need to specify the same in the query. The following query has the row element as well as the root element</row><br />
<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TOP</span></span><span style="font-size: x-small;"> 1000 [TrainID] </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'TrainNum'</span></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">[TrainName]</span><br />
<span style="font-size: x-small;">
</span><br />
<span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> [OnlineTrans]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">[dbo]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">[TrainMaster] </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">for</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">XML</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">path </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'TrainInfo'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">root </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'ParentInfo'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx6TgrDigOfBlZYLvoLzQNi75f8RbbbAZsl4xaLWO5y5VWEUS0A3pKY9CJk5vlJbqhFPuquD36OdZaMQyyfBis_e6zekVOyT2lfbDz1Dw9ywIpH9pqLgYL1BLTDt6laN9Q1EMVdwNSlYM/s1600/SQL+Server+2008+-+Export+Query+as+XML+3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="328" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx6TgrDigOfBlZYLvoLzQNi75f8RbbbAZsl4xaLWO5y5VWEUS0A3pKY9CJk5vlJbqhFPuquD36OdZaMQyyfBis_e6zekVOyT2lfbDz1Dw9ywIpH9pqLgYL1BLTDt6laN9Q1EMVdwNSlYM/s640/SQL+Server+2008+-+Export+Query+as+XML+3.png" width="640" /></a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-55677433771087244572012-05-06T08:21:00.002-07:002012-05-06T08:21:48.574-07:00'int' is not a recognized CURSOR option.<div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table </span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-fareast-language: EN-IN; mso-no-proof: yes;">declare</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-fareast-language: EN-IN; mso-no-proof: yes;"> #myi <span style="color: blue;">int<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-fareast-language: EN-IN; mso-no-proof: yes;">Should be replaced by<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-fareast-language: EN-IN; mso-no-proof: yes;">declare</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-fareast-language: EN-IN; mso-no-proof: yes;"> @myi <span style="color: blue;">int</span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-88430296344973327132012-05-06T08:21:00.001-07:002012-05-06T08:21:37.904-07:00While Loop in SQL Server<div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: red; font-family: Calibri;"><strong>While loop in T-SQL</strong></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Here is a simple example of While loop</span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">Declare</span><span style="font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;"> @temp <span style="color: blue;">tinyint</span> <span style="color: grey;">=</span> 1<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">while </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">(</span><span style="font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">@temp <span style="color: grey;"><</span> 11<span style="color: grey;">)<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">begin<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">print</span> <span style="color: magenta;">cast</span><span style="color: grey;">(</span>@temp <span style="color: blue;">as</span> <span style="color: blue;">varchar</span><span style="color: grey;">(</span>2<span style="color: grey;">))</span> <span style="color: grey;">+</span> <span style="color: red;">' X 2 = '</span> <span style="color: grey;">+</span> <span style="color: magenta;">cast</span><span style="color: grey;">(</span>@temp <span style="color: grey;">*</span> 2 <span style="color: blue;">as</span> <span style="color: blue;">char</span><span style="color: grey;">)<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 10pt; mso-fareast-language: EN-IN; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">set</span> @temp <span style="color: grey;">+=</span> 1<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%; mso-fareast-language: EN-IN; mso-no-proof: yes;">end</span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">The above code will be having the following output:</span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Output</span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">1 X 2 = 2<span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">2 X 2 = 4<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">3 X 2 = 6<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">4 X 2 = 8<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">5 X 2 = 10<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">6 X 2 = 12<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">7 X 2 = 14<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">8 X 2 = 16<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">9 X 2 = 18<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="font-family: "Courier New"; font-size: 8pt; mso-fareast-language: EN-IN; mso-no-proof: yes;">10 X 2 = 20<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-59440078260152345252012-05-06T08:21:00.000-07:002012-05-06T08:21:24.979-07:00How to create a SQL Function that Returns a Table<b><span style="color: red;">SQL Create Table Valued Function (SQL SERVER 2008) / How to Create SQL_TABLE_VALUED_FUNCTION</span></b><br />
<br />
The following snippet uses a Table variable to create a table and inserts value into it, which the function returns<br />
<br />
<pre class="SQL" name="code">CREATE FUNCTION ReturnATable()
RETURNS @TabVar TABLE
(
OrderID int not null,
OrderDate datetime,
OrderStatus bit,
OrderValue decimal,
BilledBy varchar(20)
)
AS
BEGIN
DEclare @Date datetime ;
Set @Date= GETDATE();
Insert Into @TabVar values
(21, @Date , 1, 212.42, 'Jose');
return
END
</pre>
<br />
The function can be executed as shown below<br />
<br />
<pre class="sql" name="code">Select * from ReturnATable()
</pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-3726695069616585672011-09-05T20:12:00.000-07:002011-09-05T20:12:35.109-07:00How to get Column Names of All Tables in a database through SQL Query / SysTables in SQL Server 2008<span style="color: red;">How to list all tables and each table, loop through each column using SQL query</span><br />
<br />
It is not uncommon to get the list of all column names from all available tables; at times some valuable information peeps out when one researches it. The following query will get you exactly that<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">select <span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">syscolumns</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">name </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> ColumnName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sysobjects</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">name </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> TableName </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">from</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">syscolumns</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sysobjects</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">where</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sysobjects</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">id </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">syscolumns</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">id </span></span></span><br />
<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;"></span></span></span></span>Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-7883769006508081360.post-81541155184430908622011-06-03T01:11:00.000-07:002011-06-03T01:11:11.195-07:00How to Split SQL String to Multiple String using Delimiter<strong><span style="color: red;">How to check if a text exists within another in SQL Server 2008 / How to split SQL Column based on Comma</span></strong><br />
<br />
You can use the combination of CharIndex and Substring function in SQL to split the string based on delimiter.<br />
<br />
Here is a way to split a column based on Comma<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">Select</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">LTRIM</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">EmpAddress</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CHARINDEX</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">EmpAddress</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)+</span></span><span style="font-size: x-small;">1</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">EmpAddress</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)))</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">from</span></span><span style="font-size: x-small;"> #TempEmployee</span><br />
<span style="font-size: x-small;"></span><br />
<br />
We have used Ltrim to remove any leading spacesUnknownnoreply@blogger.com1tag:blogger.com,1999:blog-7883769006508081360.post-26656093703967244102011-05-31T06:35:00.000-07:002011-05-31T06:35:22.449-07:00A RETURN statement with a return value cannot be used in this context. - Create FunctionIf this error occurs in the Create Function that uses a table then the variable after return statement should be removed<br />
<br />
The syntax should be like<br />
<br />
Create Function <function-name></function-name><br />
Returns <table_var> Table</table_var><br />
(<br />
Table Definition here<br />
..<br />
..<br />
)<br />
as <br />
BEGIN<br />
..<br />
.. <br />
RETURN -- note no value is returned here<br />
ENDUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-12760940182459237442011-05-31T06:06:00.000-07:002011-05-31T06:06:27.315-07:00How to get the list of Functions in Database<strong><span style="color: red; font-size: large;">How to Filter Functions from Sys.Objects</span></strong><br />
<br />
The following query will list of the Functions that are part of the database:<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">*</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"></span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">objects</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">where</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">type</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">in</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'FN'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'IF'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'TF'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span></span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span></span></span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-63426768687465740962011-02-21T01:31:00.000-08:002011-02-21T01:31:00.550-08:00Cannot alter column 'X' because it is 'timestamp'.How to Change DataType of TimeStamp Column.<br />
<br />
Datatype of Timestamp column cannot be deleted. Hence it is advised to drop the column and re-create it<br />
<br />
For example,<br />
<br />
ALTER TABLE [dbo].[PerformanceMaster] DROP COLUMN PerformanceDate <br />
ALTER TABLE [dbo].[PerformanceMaster] ADD PerformanceDate datetime2Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-38199460831766734772011-02-21T00:59:00.000-08:002011-02-21T00:59:50.975-08:00Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.<b>TimeStamp Column in SQL Server</b><br />
<br />
TimeStamp is used to store unique binary numbers within a database. This column is autogenerated with a storage size of 8 bytes.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUqxOtTfZblHsMqc8IlhH609OreO7ASZ-IsdMpDKrUsl5i4HeNX9DuhIjYgau11kzX51f0_B3NDvARzmBqLmhtfreh-6nTNiC5gr4izSxGCMwiDOw9oUe4LOnVu06MvIIJSa_RlIT1yw8/s1600/SQL+TimeStamp+1.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="110" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUqxOtTfZblHsMqc8IlhH609OreO7ASZ-IsdMpDKrUsl5i4HeNX9DuhIjYgau11kzX51f0_B3NDvARzmBqLmhtfreh-6nTNiC5gr4izSxGCMwiDOw9oUe4LOnVu06MvIIJSa_RlIT1yw8/s320/SQL+TimeStamp+1.JPG" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">SQL Server - TimeStamp Column</td></tr>
</tbody></table><br />
<br />
IF you want to store the Date and Time (e.g., updated time etc) use <b>datetime2</b> datatype instead of timestamp.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTEkXEonyTHTKtE0mifTluhDuCGB0X7AY81w6RGOkcU-wShkWyn-vkRmYaOxXAa1HNTRYKD5KwvE0309w7XZOWiPZZdTggkXbJCql8w_xLsVpbu0V19C2HO_KVwWGNmvA1gWkQ4HLKZNA/s1600/SQL+TimeStamp+2.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="104" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTEkXEonyTHTKtE0mifTluhDuCGB0X7AY81w6RGOkcU-wShkWyn-vkRmYaOxXAa1HNTRYKD5KwvE0309w7XZOWiPZZdTggkXbJCql8w_xLsVpbu0V19C2HO_KVwWGNmvA1gWkQ4HLKZNA/s320/SQL+TimeStamp+2.JPG" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">SQL Server DateTime Column to Record Date and Time</td></tr>
</tbody></table><br />
<br />
The error "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." occurs when you try to insert the value using Insert statement. Leaving the column in the Insert will automatically update the value<br />
<br />
Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, PerformanceDate )<br />
values (@PerformanceID, @EventID, @ArtistID, @UpdateDate )<br />
<br />
<br />
Should throw and error. Use the following instead:<br />
<br />
<br />
Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, )<br />
values (@PerformanceID, @EventID, @ArtistID)Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-12531814939431305022011-01-14T18:33:00.000-08:002011-01-14T18:33:10.279-08:00Information_Schema.Routines doesn't return all procedure names<strong><span style="color: red;">How to get a list of Procedures containing Specific Text / How to get a list of Procedures using Particular Table</span></strong><br />
<br />
<br />
<br />
Here is one method we generally use for returning Stored Procedures containing a particular text<br />
<br />
<span style="color: magenta;">SELECT ROUTINE_NAME </span><br />
<span style="color: magenta;"></span><br />
<span style="color: magenta;"><br />
</span><br />
<span style="color: magenta;">FROM INFORMATION_SCHEMA.ROUTINES </span><br />
<span style="color: magenta;"><br />
</span><br />
<span style="color: magenta;">WHERE ROUTINE_DEFINITION LIKE '%used_by_id%' </span><br />
<span style="color: magenta;">AND ROUTINE_TYPE = 'PROCEDURE'</span><br />
<span style="color: magenta;"><br />
</span><br />
<span style="color: magenta;">order by ROUTINE_NAME</span><br />
<br />
<br />
However, we found it has some problems when the text appears at the fag end of big procedures. Instead try the following also and match the result <br />
<br />
SELECT OBJECT_NAME(id) <br />
<br />
<br />
<br />
<br />
FROM syscomments <br />
<br />
<br />
<br />
WHERE [text] LIKE '%used_by_id%' <br />
<br />
<br />
<br />
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 <br />
<br />
<br />
<br />
GROUP BY OBJECT_NAME(id)<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
SELECT Name <br />
<br />
<br />
<br />
FROM sys.procedures <br />
<br />
<br />
<br />
WHERE OBJECT_DEFINITION(object_id) LIKE '%used_by_id%' <br />
<br />
<br />
<br />
order by name<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
SELECT OBJECT_NAME(object_id) <br />
<br />
<br />
<br />
FROM sys.sql_modules <br />
<br />
<br />
<br />
WHERE Definition LIKE '%used_by_id%' <br />
<br />
<br />
<br />
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 <br />
<br />
<br />
<br />
order by OBJECT_NAME(object_id)Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-71416889882366715492010-12-16T19:51:00.000-08:002010-12-16T19:51:33.344-08:00How to Specify Foriegn Key Relationships in SQL Server 2008<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWKbh1zJLz09sAP5tgReVuZpgNTI-HiIXr6KujPvepWTw6fVOUB0W5g7aRPwlqMct6J-BGFLmT9F7tkw72c0bMSrycThEsQJqjwabbogYy68Y5wJKiJPyqhaNaHKMnKREoNK-lOkZVzuI/s1600/SQL_2008_ForeignKEY_+RelationShip.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="400" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWKbh1zJLz09sAP5tgReVuZpgNTI-HiIXr6KujPvepWTw6fVOUB0W5g7aRPwlqMct6J-BGFLmT9F7tkw72c0bMSrycThEsQJqjwabbogYy68Y5wJKiJPyqhaNaHKMnKREoNK-lOkZVzuI/s640/SQL_2008_ForeignKEY_+RelationShip.PNG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Foriegn Key in SQL Server 2008 - Use the Relationships option</td></tr>
</tbody></table><br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi616j6UfSzZp4i2WVILAfOXni9_L_FZ__WH7MvA2zjLTJEW_o4ArsxA7w5hXgiI1Av4qR8Ns_AUEaXcppic4F7Por_w1jxE2Cd-u1_6k3uDhGF5A3zmfDaf8bk9HG5sOv35pyfV5qWMn0/s1600/SQL_2008_ForeignKEY_+RelationShip+3.PNG" imageanchor="1" style="clear: left; cssfloat: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="400" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi616j6UfSzZp4i2WVILAfOXni9_L_FZ__WH7MvA2zjLTJEW_o4ArsxA7w5hXgiI1Av4qR8Ns_AUEaXcppic4F7Por_w1jxE2Cd-u1_6k3uDhGF5A3zmfDaf8bk9HG5sOv35pyfV5qWMn0/s640/SQL_2008_ForeignKEY_+RelationShip+3.PNG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Specify the relationship between the columns</td></tr>
</tbody></table><div class="separator" style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0OzAOWUaT5kH3Fyl7Hgqj5U6rBBqh9s9xyjFhyphenhypheni-FUYuEgpmU29SWNxdfEr5j9gOjML5UajmXySxQbp3o44AUyO2_yUcW7Fz1TMC_0_FppVdETJnHoasGPf8tMTkSKwhF5LIGsFaLWN4/s1600/SQL_2008_ForeignKEY_+RelationShip+4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="294" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0OzAOWUaT5kH3Fyl7Hgqj5U6rBBqh9s9xyjFhyphenhypheni-FUYuEgpmU29SWNxdfEr5j9gOjML5UajmXySxQbp3o44AUyO2_yUcW7Fz1TMC_0_FppVdETJnHoasGPf8tMTkSKwhF5LIGsFaLWN4/s320/SQL_2008_ForeignKEY_+RelationShip+4.PNG" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div><br />
<div class="separator" style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2m3E0cqHHjEbV-DE7T9DVcxh6cTsx19NAh_1K0lMyLoxJQWgd2mXQoLbKqNAF8PrmMwAo2J-1NLK00PlS3Zx8jxVyfz0RsktE21Bjm734fKk8B-L8EPknT19uq5ZwSRGbkw5nFXxvick/s1600/SQL_2008_ForeignKEY_+RelationShip+2.PNG" imageanchor="1" style="clear: left; cssfloat: left; float: left; height: 231px; margin-bottom: 1em; margin-right: 1em; width: 443px;"><img border="0" height="232" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2m3E0cqHHjEbV-DE7T9DVcxh6cTsx19NAh_1K0lMyLoxJQWgd2mXQoLbKqNAF8PrmMwAo2J-1NLK00PlS3Zx8jxVyfz0RsktE21Bjm734fKk8B-L8EPknT19uq5ZwSRGbkw5nFXxvick/s320/SQL_2008_ForeignKEY_+RelationShip+2.PNG" width="320" />Add new Relationship name and save when prompted</a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-35217792131778916022010-12-16T19:41:00.000-08:002010-12-16T19:41:19.941-08:00Unable to change Identity Specification in SQL Server 2008<strong><span style="color: red;">Unable to change Identity Specification in SQL Server 2008</span></strong> <br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh41ggP77C2eNks4pvbgvS_K8sXpj5EgJUQyr0AEjbRS4kZoVJ27e8783Ddv2sGBE1Eh5rvRNrpUbyu8KOlHildD4PP3zhHem9v7t4-JkAvKcw-U4_Sd8riSHL0yuhq4XbHZ0LhFGA-hGU/s1600/SQL_2008_Unable_To_Change_Id_Spec.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh41ggP77C2eNks4pvbgvS_K8sXpj5EgJUQyr0AEjbRS4kZoVJ27e8783Ddv2sGBE1Eh5rvRNrpUbyu8KOlHildD4PP3zhHem9v7t4-JkAvKcw-U4_Sd8riSHL0yuhq4XbHZ0LhFGA-hGU/s400/SQL_2008_Unable_To_Change_Id_Spec.PNG" width="380" /></a></div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7883769006508081360.post-40027283699696432932010-12-16T19:37:00.000-08:002010-12-16T19:37:53.929-08:00How to make Server Explorer visible in Visual Studio<b>Server Explorer not visible in Visual Studio</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1ve9N4anab3hOGxy7edwPpKRwWm2AvLHtyOVxrZLcmwV8K22e-vKjNakHJ_QcjTKYcnoKn2WatvWPJtLEOOFr8Dbo1n8z0_Fkn2U1eHg_ViiG7sMZ8UAVLJfZiqoEEdN6XEiES_02n74/s1600/Server_Explorer_VS2010.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1ve9N4anab3hOGxy7edwPpKRwWm2AvLHtyOVxrZLcmwV8K22e-vKjNakHJ_QcjTKYcnoKn2WatvWPJtLEOOFr8Dbo1n8z0_Fkn2U1eHg_ViiG7sMZ8UAVLJfZiqoEEdN6XEiES_02n74/s400/Server_Explorer_VS2010.png" width="325" /></a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-59745765435788879112010-12-16T19:30:00.000-08:002010-12-16T19:30:55.394-08:00Your pending changes require the following tables to be dropped and re-created<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiC4QCz3-ahATCpRRnh5hD8GVWMBglVtD6FoAeofteZyNWAb0ULja2JjqQSFj7semvdlAlYoD-VFK8vVWzZ1N3Jj5unjZvcigWihifHyzgrsWnU8qM_Z_5Woxk3VhUuzsNe1EitklQNQ_s/s1600/SQL_2008_Tables_Recreated.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiC4QCz3-ahATCpRRnh5hD8GVWMBglVtD6FoAeofteZyNWAb0ULja2JjqQSFj7semvdlAlYoD-VFK8vVWzZ1N3Jj5unjZvcigWihifHyzgrsWnU8qM_Z_5Woxk3VhUuzsNe1EitklQNQ_s/s640/SQL_2008_Tables_Recreated.PNG" width="582" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div>Remove the checkbox 'Prevent saving changes'<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu7TU07K5pXjEkb_FsVYm8ofeFJH6vZgbZ7JTLwTrf_OmCzsUMtWZG0o1oOKTD4t_ZGC5TN-wrNZ29bH4YSeJhZG_C6Q-FwfxYrcCDra5PkLj81vsRMT35Lk3BgC2DyerFsb535aGoFDU/s1600/SQL_2008_Tables_Recreated+2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="426" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu7TU07K5pXjEkb_FsVYm8ofeFJH6vZgbZ7JTLwTrf_OmCzsUMtWZG0o1oOKTD4t_ZGC5TN-wrNZ29bH4YSeJhZG_C6Q-FwfxYrcCDra5PkLj81vsRMT35Lk3BgC2DyerFsb535aGoFDU/s640/SQL_2008_Tables_Recreated+2.PNG" width="640" /></a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-66322719725531926922010-12-16T19:26:00.000-08:002010-12-16T19:26:06.942-08:00How to create auto-increment column in SQL Server<b>How to Set Increment value in SQL Server</b><br />
<br />
Set Column properties - goto Identity Specification, set (Is Identity) to True (Yes)<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAdaBvJtgFjZYo247yQKDdJFAXX0CYfTDZPXUwCclelTTHkdZsr2gDpeGc9NyV9cqPY_xtuzzNc9H7JpBClNOEbMjfphbrI5ckIsG937EvgV-hESFqYp5ztk7bTAIWf4kknEnbkbLB7Mc/s1600/SQL_2008_Identity.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="292" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAdaBvJtgFjZYo247yQKDdJFAXX0CYfTDZPXUwCclelTTHkdZsr2gDpeGc9NyV9cqPY_xtuzzNc9H7JpBClNOEbMjfphbrI5ckIsG937EvgV-hESFqYp5ztk7bTAIWf4kknEnbkbLB7Mc/s320/SQL_2008_Identity.PNG" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">SQL Server Identity Specification</td></tr>
</tbody></table>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-89563460799704255572010-12-16T19:18:00.000-08:002010-12-16T19:18:55.635-08:00How to specify Default value for a column in SQL Server<b>SQL Server Default Column Values</b><br />
<br />
Here is a way to specify default values in Sql Server Management Studio for a column<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbZ5unntHEO8NMDayaUjxdOdhkLfpMVUwXz4BCCWZ0vR25gPokAFnr4hY5gyjUQvpi9kHbKqGuqLxnNU2P3elsOGM_aldnSzemCrEDakt7DfSyvNvelt3gaWEcN9zliyHK6AW8W-WTX1Q/s1600/SQL_2008_DefaultValue.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" n4="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbZ5unntHEO8NMDayaUjxdOdhkLfpMVUwXz4BCCWZ0vR25gPokAFnr4hY5gyjUQvpi9kHbKqGuqLxnNU2P3elsOGM_aldnSzemCrEDakt7DfSyvNvelt3gaWEcN9zliyHK6AW8W-WTX1Q/s400/SQL_2008_DefaultValue.PNG" width="378" /></a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-10577557599894680732010-04-05T18:36:00.000-07:002010-04-05T18:37:15.840-07:00SQL Server 2008 - Saving changes is not permitted<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhulcqVW9XAxvgRIu1LWVv14Ecs4HtutSDOiG-yQ4cObm4KNk2ILOdEmwaH073kGQnYG_0EyPblZltXI3EHrHNmCiyhgg-Tkb8CAGuXDdhGXvTAST3_7EWQ77HYv9fqsmvQCCyJQXoO1t0/s1600/SQL_SaveChanges_NotPermitted.PNG"><img style="cursor: pointer; width: 400px; height: 318px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhulcqVW9XAxvgRIu1LWVv14Ecs4HtutSDOiG-yQ4cObm4KNk2ILOdEmwaH073kGQnYG_0EyPblZltXI3EHrHNmCiyhgg-Tkb8CAGuXDdhGXvTAST3_7EWQ77HYv9fqsmvQCCyJQXoO1t0/s400/SQL_SaveChanges_NotPermitted.PNG" alt="" id="BLOGGER_PHOTO_ID_5456832449262800114" border="0" /></a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-12182218149514360752010-04-05T18:16:00.000-07:002010-04-05T18:18:52.915-07:00Error Message: String or binary data would be truncated. - SQL SERVERNo row was updated.<br /><br />The data in row 1 was not committed.<br />Error Source: .Net SqlClient Data Provider.<br />Error Message: String or binary data would be truncated.<br />The statement has been terminated.<br /><br />Correct the errors and retry or press ESC to cancel the change(s).<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8kuY5pvFnu7GfUwziUzIlVE4c_nl20KwFcmVC6f_ocV70UGcdn5T28KeH5wl7S_CqzQxc-9qnrVEc6wRvr1MbgaDWoKqKrD6O9TsHsB54tF59d9AYXmnmuJbyAJsQD0WF75T3kEfAjUs/s1600/SQL_Trunctate+Data.PNG"><img style="cursor: pointer; width: 400px; height: 205px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8kuY5pvFnu7GfUwziUzIlVE4c_nl20KwFcmVC6f_ocV70UGcdn5T28KeH5wl7S_CqzQxc-9qnrVEc6wRvr1MbgaDWoKqKrD6O9TsHsB54tF59d9AYXmnmuJbyAJsQD0WF75T3kEfAjUs/s400/SQL_Trunctate+Data.PNG" alt="Error Message: String or binary data would be truncated. " id="BLOGGER_PHOTO_ID_5456827406595331058" border="0" /></a><br /><br />The above error occurs in Microsoft SQL Server Management Studio when you attempt to enter more characters than the maximum level for that fieldUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-7883769006508081360.post-34188225679650139272010-04-05T18:13:00.000-07:002010-04-05T18:16:38.476-07:00How to set Case Sensitive comparison in SQL Server 2008<om name="Om Ganeshaya Namaha"><br /><om name="Om Satguru Seshadri Swamigal Thiruvadike"><br /><om name="Om Varahi Namaha"><br /><om name="Om Saravana Bhava"><br /><br /><br /><span style="font-weight: bold;">How to set a field as Case Sensitive in SQL Server 2008.</span><br /><br />Here is a simple way to do that<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguUCMKtDO_FT6FuvqAbOhiJETdWfahcroU5_Cb_N5pbSR3py40NvaT2y12gFqV7uRet3-jhGDVWTHUhou4G494f59WvuVola1rC6BngtilUy-7An3bUZQMgUwkiy0hL4kHCKbRWOUpmTo/s1600/SQL_Collation.PNG"><img style="cursor: pointer; width: 292px; height: 400px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguUCMKtDO_FT6FuvqAbOhiJETdWfahcroU5_Cb_N5pbSR3py40NvaT2y12gFqV7uRet3-jhGDVWTHUhou4G494f59WvuVola1rC6BngtilUy-7An3bUZQMgUwkiy0hL4kHCKbRWOUpmTo/s400/SQL_Collation.PNG" alt="" id="BLOGGER_PHOTO_ID_5456826782481495058" border="0" /></a><br /><br /></om></om></om></om>Unknownnoreply@blogger.com0