tag:blogger.com,1999:blog-38602110329240599712024-02-07T16:45:14.353-08:00SQL TutorialsSQL Tutorial, SQL Server, SQL Statement, SQL Query, MS SQL 2000 Reporting Service, T-SQL, SQL Function, SQL Syntax, SQL User Define Function, SQL TriggerUnknownnoreply@blogger.comBlogger97125tag:blogger.com,1999:blog-3860211032924059971.post-85102728961845528252022-07-05T22:42:00.005-07:002022-07-05T22:42:58.934-07:00Difference between "Maven Install" and "Maven Build" <p> <span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;">First of all,</span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><code style="border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">build</code><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;">is</span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><span style="background-color: white; color: #232629; font-size: 15px;">not a phase</span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;">in the standard Maven lifecycles, whereas</span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><code style="border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">install</code><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;">is one.</span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><code style="border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">mvn install</code><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;">will invoke all the phases up to the phase</span><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;"> </span><code style="border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">install</code><span style="background-color: white; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px;">, which generally consists of compiling the source code, packaging the project and installing it in the local repository.</span></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">To be clear, we're talking about what M2Eclipse shows in the "Run As" selection.</p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://i.stack.imgur.com/ldflm.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="211" data-original-width="658" height="211" src="https://i.stack.imgur.com/ldflm.png" width="658" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><p></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">What are all those options? First of all, you need to be aware that you can:</p><h2 style="background-color: white; border: 0px; box-sizing: inherit; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: var(--fs-title); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-weight: 400; line-height: 1.3; margin: 1.667em 0px 0.7em; padding: 0px; vertical-align: baseline;">Configure custom "Run Configuration"s in Eclipse</h2><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">By going to:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://i.stack.imgur.com/ZaNba.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="145" data-original-width="250" height="145" src="https://i.stack.imgur.com/ZaNba.png" width="250" /></a></div><br /><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><br /></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">This will open a dialog where you can configure those custom configurations.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://i.stack.imgur.com/See2I.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="689" data-original-width="733" height="689" src="https://i.stack.imgur.com/See2I.png" width="733" /></a></div><br /><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><br /></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><br /></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">You can create a new "Maven Build" run configuration, by giving it:</p><ul style="background-color: white; border: 0px; box-sizing: inherit; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; list-style-image: initial; list-style-position: initial; margin-bottom: var(--s-prose-spacing); margin-left: 30px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><li style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing-condensed); margin-left: 0px; margin-right: 0px; margin-top: 0px; overflow-wrap: break-word; padding: 0px; vertical-align: baseline;">a name: this will be the unique name of the configuration. You can name it as you like. Above, it is named with the goals that it will invoke.</li><li style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing-condensed); margin-left: 0px; margin-right: 0px; margin-top: 0px; overflow-wrap: break-word; padding: 0px; vertical-align: baseline;">the base directory: this will be the folder where Maven will be invoked in. In the above screenshot, I used the Eclipse variable <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">${project_loc}</code>, which is replaced automatically by the base directory of the current selected project in the "Project Explorer" when run. (This allows to have a single run configuration for multiple projects).</li><li style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; overflow-wrap: break-word; padding: 0px; vertical-align: baseline;">goals, potential profiles and several options: all those options will make up for the exact command that will be launched. Adding a profile will launch Maven with a <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">-P...</code> attribute; checking "Update Snapshots" will launch Maven with the <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">-U</code> flag, etc.</li></ul><h2 style="background-color: white; border: 0px; box-sizing: inherit; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: var(--fs-title); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-weight: 400; line-height: 1.3; margin: 1.667em 0px 0.7em; padding: 0px; vertical-align: baseline;">So what are all those "Run As" options?</h2><h3 style="background-color: white; border: 0px; box-sizing: inherit; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: var(--fs-subheading); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-weight: 400; line-height: 1.3; margin: 0px 0px 0.74em; padding: 0px; vertical-align: baseline;">Maven install</h3><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">This is the simple one: "Maven install" will launch the configured Maven installation in Eclipse with the goal <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">install</code>. It will have the same effect as running the command <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">mvn install</code> on the command-line, with an external Maven installation.</p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">The options "Maven generate-sources", "Maven test" or "Maven clean" are actually following the same idea: all of those will directly invoke Maven with the <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">generate-sources</code> phase, the <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">test</code> phase or the <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">clean</code> phase.</p><h3 style="background-color: white; border: 0px; box-sizing: inherit; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: var(--fs-subheading); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-weight: 400; line-height: 1.3; margin: 1.4667em 0px 0.74em; padding: 0px; vertical-align: baseline;">Maven build...</h3><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">This will actually launch the previous dialog where we created a new run configuration. What happens is that M2Eclipse will create a new one, that you can fill exactly like above. You could see it as a short-cut for creating custom "Maven Build" run configurations.</p><h3 style="background-color: white; border: 0px; box-sizing: inherit; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: var(--fs-subheading); font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-weight: 400; line-height: 1.3; margin: 1.4667em 0px 0.74em; padding: 0px; vertical-align: baseline;">Maven build</h3><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">This will try to launch the configured custom run configurations.</p><ul style="background-color: white; border: 0px; box-sizing: inherit; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; list-style-image: initial; list-style-position: initial; margin-bottom: var(--s-prose-spacing); margin-left: 30px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><li style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing-condensed); margin-left: 0px; margin-right: 0px; margin-top: 0px; overflow-wrap: break-word; padding: 0px; vertical-align: baseline;">If you have only one custom "Maven Build" run configuration, it will launch that one.</li><li style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; overflow-wrap: break-word; padding: 0px; vertical-align: baseline;"><p style="border: 0px; box-sizing: inherit; clear: both; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing-condensed); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;">If you have more than one, it will ask you for the one to run:</p><p style="border: 0px; box-sizing: inherit; clear: both; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing-condensed); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><a href="https://i.stack.imgur.com/99he1.png" style="border: 0px; box-sizing: inherit; cursor: pointer; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; user-select: auto; vertical-align: baseline;"><img alt="enter image description here" src="https://i.stack.imgur.com/99he1.png" style="border: 0px; box-sizing: inherit; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; max-width: 100%; padding: 0px; vertical-align: bottom;" /></a></p><p style="border: 0px; box-sizing: inherit; clear: both; font-family: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">In the above screenshots, you can see that there was 2 custom "Maven Build" run configuration, which were named <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">clean</code> and <code style="background-color: var(--black-075); border-radius: var(--br-sm); border: 0px; box-sizing: inherit; color: var(--black-800); font-family: var(--ff-mono); font-size: var(--fs-body1); font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: var(--su2) var(--su4); vertical-align: baseline; white-space: pre-wrap;">clean install</code>. As such, this pop-up is asking the user to select one.</p></li></ul><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI Adjusted", "Segoe UI", "Liberation Sans", sans-serif; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Once the custom "Maven Build" configuration was chosen, it will then invoke Maven with the options in this run configuration.</p><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><p></p>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-86132077240002728922018-11-19T20:22:00.000-08:002018-11-19T20:24:28.011-08:00MSSQL - Database CPU Usage<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1SSWRrDA8mDf7fOm89UASoGk7WvWDe-jS9YtLe2ENlT5tI1j64j9KyN1h3u7Up5VcxsoCxTvvosXo9jhnIQLiH5CONPOEdLfMd98NbyuGwxpJbHOM3LM7khWTarqX_hzl9IkdoVphhsaO/s1600/db+CPU.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="184" data-original-width="636" height="115" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1SSWRrDA8mDf7fOm89UASoGk7WvWDe-jS9YtLe2ENlT5tI1j64j9KyN1h3u7Up5VcxsoCxTvvosXo9jhnIQLiH5CONPOEdLfMd98NbyuGwxpJbHOM3LM7khWTarqX_hzl9IkdoVphhsaO/s400/db+CPU.png" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Scripts to generate the CPU usage of a database.<br />
<br />
<br />
<span style="color: #38761d;"><br /></span>
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;">DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;">SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization], </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> SystemIdle AS [System Idle Process], </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;">FROM ( </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> AS [SystemIdle], </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> 'int') </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> AS [SQLProcessUtilization], [timestamp] </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> FROM ( </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> SELECT [timestamp], CONVERT(xml, record) AS [record] </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> FROM sys.dm_os_ring_buffers </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> AND record LIKE '%<systemhealth>%') AS x </systemhealth></span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;"> ) AS y </span><br />
<span style="color: #38761d; font-family: "arial" , "helvetica" , sans-serif;">ORDER BY record_id DESC;</span><br />
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Currently the CPU usage is showing per minutes.</div>
<div>
To change the CPU usage table to every 10 minutes.</div>
<div>
change the value below from -1 to -10.</div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg60DO39W-khya33za597AgO_jBLmTgCxV2w1OIkLcYYiK-lBc_757CEV5ADj4rOHA05utOP8WsKFCssJQLBIr1I398A9aby-gzsbzs5vpl6paDTybc0BqL3zdPt7sOJSmU715SWsWgMM3S/s1600/table+size.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="1" data-original-height="33" data-original-width="367" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg60DO39W-khya33za597AgO_jBLmTgCxV2w1OIkLcYYiK-lBc_757CEV5ADj4rOHA05utOP8WsKFCssJQLBIr1I398A9aby-gzsbzs5vpl6paDTybc0BqL3zdPt7sOJSmU715SWsWgMM3S/s1600/table+size.png" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-91477148894982869192018-11-19T20:14:00.001-08:002018-11-19T20:14:28.969-08:00MSSQL - Get size of all tables in database<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjb_djijOKqv19gB9sipzDBQo6r9j3MkqQOsWD1e5IRRrsX417oLsGtUIz_AQ8p34FQt_LHYOiePvOjpHtNnX72F6sN79CLRxsn0EdwXuN9xA8X-6e1xQ6xlljRQuT89vQYblrFpWhcdPi/s1600/table+size.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="68" data-original-width="824" height="52" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjb_djijOKqv19gB9sipzDBQo6r9j3MkqQOsWD1e5IRRrsX417oLsGtUIz_AQ8p34FQt_LHYOiePvOjpHtNnX72F6sN79CLRxsn0EdwXuN9xA8X-6e1xQ6xlljRQuT89vQYblrFpWhcdPi/s640/table+size.png" width="640" /></a></div>
<br />
below scripts use to generate the all the Database table size and number of rows of records in the table.<br />
<br />
<span style="color: #38761d;">use distribution</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SELECT </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> t.NAME AS TableName,</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> s.Name AS SchemaName,</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> p.rows AS RowCounts,</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> SUM(a.total_pages) * 8 AS TotalSpaceKB, </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> SUM(a.used_pages) * 8 AS UsedSpaceKB, </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">FROM </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> sys.tables t</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">INNER JOIN </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> sys.indexes i ON t.OBJECT_ID = i.object_id</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">INNER JOIN </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">INNER JOIN </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> sys.allocation_units a ON p.partition_id = a.container_id</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">LEFT OUTER JOIN </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> sys.schemas s ON t.schema_id = s.schema_id</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">WHERE </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> t.NAME NOT LIKE 'dt%' </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> AND t.is_ms_shipped = 0</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> AND i.OBJECT_ID > 255 </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">GROUP BY </span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> t.Name, s.Name, p.Rows</span><br />
<span style="color: #38761d; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">ORDER BY CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) desc</span>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-45013563270734526052017-12-02T07:17:00.004-08:002017-12-02T07:17:58.848-08:00How tempDB works in MSSQL<div style="background-color: white; border: 0px; clear: both; color: #242729; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 15px; font-stretch: inherit; line-height: 19.5px; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
Temp table is stored in tempdb until the connection is dropped (or in the case of a global temp tables when the last connection using it is dropped). You can also (and it is a good practice to do so) manually drop the table when you are finished using it with a drop table statement.</div>
<div style="background-color: white; border: 0px; clear: both; color: #242729; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 15px; font-stretch: inherit; line-height: 19.5px; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
No, others cannot see your temp tables if they are local temp tables (They can see and use global temp tables) Multiple people can run commands which use the same temp table name but they will not be overlapping in a local temp table and so you can have a table named #test and so can 10,000 other users, but each one has its own structure and data.</div>
<div style="background-color: white; border: 0px; clear: both; color: #242729; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 15px; font-stretch: inherit; line-height: 19.5px; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
You don't want to generally look up temp tables in tempdb. It is possible to check for existence, but that is the only time I have ever referenced tempdb directly. Simply use your temp table name. Example below of checking for existence</div>
<pre class="lang-sql prettyprint prettyprinted" style="background-color: #eff0f1; border: 0px; color: #393318; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-stretch: inherit; line-height: inherit; margin-bottom: 1em; max-height: 600px; overflow: auto; padding: 5px; vertical-align: baseline; width: auto; word-wrap: normal;"><code style="border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline; white-space: inherit;"><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">IF</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> OBJECT_ID</span><span class="pun" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">(</span><span class="str" style="border: 0px; color: #7d2727; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">'TempDB.dbo.#DuplicateAssignments'</span><span class="pun" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">)</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">IS</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">NOT</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">NULL</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">
</span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">BEGIN</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">
</span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">DROP</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">TABLE</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span class="pun" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">#</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">DuplicateAssignments
</span><span class="kwd" style="border: 0px; color: #101094; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">END</span><span class="pln" style="border: 0px; color: #303336; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </span></code></pre>
<div style="background-color: white; border: 0px; clear: both; color: #242729; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 15px; font-stretch: inherit; line-height: 19.5px; margin-bottom: 1em; padding: 0px; vertical-align: baseline;">
You name temp tables by prefacing the name with # (for local tables the ones you would use 999.9% of the time) and ## for global temp tables, then the rest of the name you want.</div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-5242681744508888612015-06-30T20:05:00.001-07:002015-06-30T20:05:43.114-07:00Query to get the names of all tables in SQL<b>SQL Server </b><br />
<b><span style="color: #6aa84f; font-size: large;">USE your_database
SELECT name FROM sys.tables</span></b><br />
<b><span style="color: #6aa84f; font-size: large;"><br /></span></b>
<span style="color: #6aa84f; font-size: large;"><b><br /></b></span>
<b>SQL Server - <span style="background-color: white; color: #222222; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 15px; line-height: 13px;">get the fields info</span></b><br />
<span style="color: #6aa84f; font-size: large;"><b>SELECT TABLE_SCHEMA, TABLE_NAME, </b></span><br />
<span style="color: #6aa84f; font-size: large;"><b> COLUMN_NAME, substring(DATA_TYPE, 1,1) AS DATA_TYPE</b></span><br />
<span style="color: #6aa84f; font-size: large;"><b>FROM information_schema.COLUMNS </b></span><br />
<b><span style="color: #6aa84f; font-size: large;"></span></b><br />
<span style="color: #6aa84f; font-size: large;"><b>ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION</b></span><br />
<span style="color: #6aa84f; font-size: large;"><b><br /></b></span>
<span style="color: #6aa84f; font-size: large;"><b><br /></b></span>
<b>MySQL</b><br />
<span style="color: #6aa84f; font-size: large;"><b>select TABLE_NAME from INFORMATION_SCHEMA.TABLES</b></span><br />
<span style="color: #6aa84f; font-size: large;"><b>where TABLE_TYPE = 'BASE TABLE'</b></span>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-40526750590833418172012-12-26T02:12:00.003-08:002012-12-26T02:12:53.488-08:00Check DB Table Size (sp_spaceused)<span style="color: #2a2a2a; font-family: Segoe UI, Lucida Grande, Verdana, Arial, Helvetica, sans-serif;"><span style="font-size: 12px; line-height: 17.983333587646484px;"><b>sp_spaceused</b></span></span><br />
<span style="color: #2a2a2a; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 11.818181991577148px; line-height: 17.999998092651367px;">Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.</span><br />
<span style="color: #2a2a2a; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 11.818181991577148px; line-height: 17.999998092651367px;"><br /></span>
<span style="color: #2a2a2a; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 11.818181991577148px; line-height: 17.999998092651367px;"><b>Example</b></span><br />
<span style="font-family: Segoe UI, Lucida Grande, Verdana, Arial, Helvetica, sans-serif;"><span style="font-size: 12px; line-height: 17.983333587646484px;"><span style="color: blue;">EXEC sp_spaceused N'</span><span style="color: #38761d;"><b>Sales.SalesPerson</b></span><span style="color: blue;">';</span></span></span><br />
<span style="font-family: Segoe UI, Lucida Grande, Verdana, Arial, Helvetica, sans-serif;"><span style="font-size: 12px; line-height: 17.983333587646484px;"><span style="color: blue;"><br /></span></span></span>
<span style="font-family: Segoe UI, Lucida Grande, Verdana, Arial, Helvetica, sans-serif;"><span style="font-size: 12px; line-height: 17.983333587646484px;"><b>Result</b></span></span><br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjY_NsTbzr6CHnv514PTXdEMYxJzOQ46KK_L9NfjQtUu6xAWO2DQ0VfktJjY2x8o687gNLaPOZij6BiLT-J9Cg5ID_M9fXgtQhK9jxDx71BHZWqa47eV0FhrCrUcx51jUNOnbWEn2P-muae/s1600/sp_spaceused.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjY_NsTbzr6CHnv514PTXdEMYxJzOQ46KK_L9NfjQtUu6xAWO2DQ0VfktJjY2x8o687gNLaPOZij6BiLT-J9Cg5ID_M9fXgtQhK9jxDx71BHZWqa47eV0FhrCrUcx51jUNOnbWEn2P-muae/s1600/sp_spaceused.png" /></a></div>
<span style="font-family: Segoe UI, Lucida Grande, Verdana, Arial, Helvetica, sans-serif;"><span style="font-size: 12px; line-height: 17.983333587646484px;"><span style="color: blue;"><br /></span></span></span>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-25439837845309124852012-11-19T21:56:00.002-08:002012-11-19T21:56:10.739-08:00Limit - MySQL Command<i style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;">Limit</i><span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"> is used to limit your MySQL query results to those that fall within a specified range. It is use for select number of row of record from your query.</span><br />
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><br /></span>
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;">your table record : </span><span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;">1,2,3,4,5,6,7,8,9,10,11,12,13,14,15</span><br />
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><br /></span>
<span style="background-color: white; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><span style="color: #38761d;"><b>SELECT * FROM 'YourTable' Limit 10</b></span></span><br />
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;">- It will select 10 rows of record from the table.</span><br />
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><br /></span>
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;">Result : </span><span style="background-color: white; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><span style="color: blue;"><b>1,2,3,4,5,6,7,8,9,10</b></span></span><br />
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><br /></span>
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><br /></span>
<div style="text-align: -webkit-auto;">
<span style="color: #38761d;"><b><span style="background-color: white; font-size: 12px; line-height: 18px; text-align: left;"><span style="font-family: Verdana;">SELECT * FROM '</span></span><span style="background-color: white; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;">YourTable</span><span style="background-color: white; text-align: left;">'</span><span style="background-color: white; font-size: 12px; line-height: 18px; text-align: left;"><span style="font-family: Verdana;"> LIMIT 0, 3 </span></span></b></span></div>
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;">- It will return 3 rows of record and start from index 0</span><br />
<span style="background-color: white; color: #333333; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><br /></span>
<span style="background-color: white; font-family: Verdana; font-size: 12px; line-height: 18px; text-align: left;"><span style="color: #333333;">Result : </span><b><span style="color: blue;">1,2,3</span></b></span><br />
<div style="text-align: left;">
<span style="color: blue; font-family: Verdana;"><span style="font-size: 12px; line-height: 18px;"><b><br /></b></span></span></div>
<div style="text-align: left;">
<span style="color: blue; font-family: Verdana;"><span style="font-size: 12px; line-height: 18px;"><b><br /></b></span></span></div>
<div style="text-align: left;">
<span style="font-family: Verdana;"></span></div>
<div style="font-weight: bold;">
<span style="font-family: Verdana;"><span style="font-size: 12px; line-height: 18px;"><span style="color: #38761d;">SELECT * FROM 'YourTable' LIMIT 2, 3 </span></span></span></div>
<span style="font-family: Verdana;"><span style="font-size: 12px; line-height: 18px;">- It will return 3 rows of record and start from index 2</span></span><br />
<div style="font-weight: bold;">
<span style="font-family: Verdana;"><span style="font-size: 12px; line-height: 18px;"><br /></span></span></div>
<span style="font-family: Verdana;"><span style="font-size: 12px; line-height: 18px;">Result : <span style="color: blue;"><b>3,4,5</b></span></span></span><br />
<div style="color: blue; font-size: 12px; font-weight: bold; line-height: 18px;">
<span style="font-family: Verdana;"><br /></span></div>
Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-89181662357195886672012-06-11T03:58:00.000-07:002012-06-11T03:59:14.298-07:00The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator<b>SQL Server Error Message :</b><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><b>The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.</b></span><br />
<br />
<span style="background-color: white; font-family: Verdana, Helvetica, Arial, sans-serif; font-size: 11px; line-height: 18px; text-align: justify;">In order to compare or sort text/ntext, you need to convert it to varchar (or similar datatype that can allow compare/sort). Note, text/ntext often has a large capacity for data than varchar.</span>
<br />
<span style="background-color: white; font-family: Verdana, Helvetica, Arial, sans-serif; font-size: 11px; line-height: 18px; text-align: justify;"><br /></span><br />
<div style="text-align: justify;">
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;"><span style="font-size: 11px; line-height: 18px;"></span></span></div>
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;"><b>Example SQL Code 1:</b></span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;">[...] ORDER BY TableColumn</span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;">change to</span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;">[...] <span style="color: red;">ORDER BY cast(TableColumn as varchar(500))</span></span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;"><b>Example SQL Code 2:</b></span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;">[...] GROUP BY TableColumn</span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;">change to</span><br />
<span style="font-family: Verdana, Helvetica, Arial, sans-serif;">[...] <span style="color: red;">GROUP BY cast(TableColumn as varchar(500))</span></span><br />
<br />
<pre style="background-color: white; font-size: 11px; text-align: justify;"></pre>
<br />
<div id="fastlookup_top" style="display: none;">
</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-32341959229514415622012-05-21T23:39:00.001-07:002012-05-21T23:40:21.281-07:00Saving changes is not permitted in SQL 2008 Management Studio<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both;">
When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the Database Management Studio will not allow you to save the changes.</div>
<div class="separator" style="clear: both;">
This is caused by a configuration setting that default results in the following dialog:</div>
<div class="separator" style="clear: both;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3yZBxb5jigfkqU0COrQ8XorSgAfuTcq9dNZy8F2TjVha5RpKubauvRa1LAuC01fFJUsaWffgNEzyDWV3gW7oy4_p2kWSFTctuI0b2HZTecxJ0Se6L3nmrRb-104Q-aKQsnPJyr0zvRF_f/s1600/image.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="255" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3yZBxb5jigfkqU0COrQ8XorSgAfuTcq9dNZy8F2TjVha5RpKubauvRa1LAuC01fFJUsaWffgNEzyDWV3gW7oy4_p2kWSFTctuI0b2HZTecxJ0Se6L3nmrRb-104Q-aKQsnPJyr0zvRF_f/s320/image.png" width="320" /></a></div>
<div style="text-align: center;">
Error Screen</div>
<br />
<br />
<br />
Solutions:<br />
<br />
This is by design and can be quickly fixed in Management Studio by unchecking a property.<br />
To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguQ_dWO7uCu6l7hmsr3vzqViKR6kmjJMTpTCiZImOhuXAKOOnyEHO_AIVt1fsDJdzdiUszYhWoeEnkT8y6Gwhq8o72uOCJvRWt63m3oYWoENTOToto4uEsHQcBdXYbO3qJcLuMcwT7hUFU/s1600/TableRecreation.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="186" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguQ_dWO7uCu6l7hmsr3vzqViKR6kmjJMTpTCiZImOhuXAKOOnyEHO_AIVt1fsDJdzdiUszYhWoeEnkT8y6Gwhq8o72uOCJvRWt63m3oYWoENTOToto4uEsHQcBdXYbO3qJcLuMcwT7hUFU/s320/TableRecreation.gif" width="320" /></a></div>
<br />
<div id="fastlookup_top" style="display: none;">
</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-50591859370289452852012-05-18T02:48:00.001-07:002012-05-18T05:55:58.183-07:00SharePoint webparts AJAX enabled?<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;">The web parts in SharePoint 2007 are NOT Ajax enabled. AJAX support didn't come along for 2007 until SP1. The web parts weren't re-written to add AJAX to them. Some of the SharePoint 2010 web parts are AJAX enabled, but not in 2007.</span>
<br />
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;"><br /></span><br />
<br />Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-50274346234363439962012-05-06T22:00:00.000-07:002012-05-06T22:00:01.120-07:00Add Linked server using Command<br />
<span style="font-family: Arial, Helvetica, sans-serif;">A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:</span><br />
<br />
<ul>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Remote server access.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">The ability to address diverse data sources similarly</span></li>
</ul>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><b>sp_addlinkedserver</b></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Command:</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">EXEC sp_addlinkedserver @server= <span style="color: lime;"><b>SERVER NAME</b></span></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: lime;"><b><br /></b></span></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">eg, your server name is myDBserver then </span></div>
<div>
<span style="color: #274e13;"><b><span style="font-family: Arial, Helvetica, sans-serif;">EXEC sp_addlinkedserver @server = '</span><span style="font-family: Arial, Helvetica, sans-serif;">myDBserver</span><span style="font-family: Arial, Helvetica, sans-serif;">'</span></b></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><b>sp_addlinkedsrvlogin</b></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Command:</span></div>
<div>
<div style="font-family: Arial, Helvetica, sans-serif;">
EXEC sp_addlinkedsrvlogin <span style="color: #274e13;"><span style="font-family: Consolas, Courier, monospace; font-size: 13px; text-align: left;">@rmtsrvname</span> , <span style="font-family: Consolas, Courier, monospace; font-size: 13px; text-align: left;">'TRUE' | 'FALSE' | NULL</span>, <span style="font-family: Consolas, Courier, monospace; font-size: 13px; text-align: left;">@locallogin</span>, <span style="font-family: Consolas, Courier, monospace; font-size: 13px; text-align: left;">@rmtuser</span>, </span></div>
<div style="font-family: Arial, Helvetica, sans-serif;">
<span style="font-family: Consolas, Courier, monospace; font-size: 13px; text-align: left;"><span style="color: #274e13;">@rmtpassword </span></span></div>
<div style="font-family: Arial, Helvetica, sans-serif;">
<span style="font-family: Consolas, Courier, monospace; font-size: 13px; text-align: left;"><span style="color: #274e13;"><br /></span></span></div>
<div style="text-align: left;">
<span style="font-family: Consolas, Courier, monospace; font-size: x-small;"><b>eg,</b></span></div>
<div style="font-family: Arial, Helvetica, sans-serif;">
<span style="font-family: Consolas, Courier, monospace; font-size: 13px; text-align: left;"><span style="color: #274e13;"><div>
EXEC sp_addlinkedsrvlogin <b style="font-family: 'Times New Roman'; font-size: medium; text-align: -webkit-auto;"><span style="font-family: Arial, Helvetica, sans-serif;">'</span><span style="font-family: Arial, Helvetica, sans-serif;">myDBserver</span><span style="font-family: Arial, Helvetica, sans-serif;">' </span></b>, 'false', NULL, 'sa', 'password'</div>
<div>
<br /></div>
</span></span></div>
</div>
<br />
<br />
<div id="fastlookup_top" style="display: none;">
</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-31775367262694750872012-02-17T02:14:00.000-08:002012-02-17T02:14:18.159-08:00Sharepoint - SPSecurityTokenServiceConfig is not recognized<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;">i try to run SET-</span><span style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: x-small;"><span style="line-height: 16px;">SPSecurityTokenServiceConfig command in powershell, but it return me error message</span></span><br />
<div style="text-align: -webkit-auto;">
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; line-height: 16px; text-align: left;"><span style="font-size: x-small;">"</span></span><span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;"> </span><span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;">SET-</span><span style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: x-small; line-height: 16px;">SPSecurityTokenServiceConfig</span><span style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: x-small; line-height: 16px;"> </span><span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;">recognized as the name of cmdlet, function..."</span></div>
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;"><br /></span><br />
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;"><b>Solution</b></span><br />
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;">Add-PSSnapin Microsoft.SharePoint.Powershell</span>
<br />
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;"><br /></span><br />
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px; text-align: left;">After this, sharepoint commands will be available in PowerShell.</span>
<br />
<div id="fastlookup_top" style="display: none;">
</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-5494798068935135892012-02-15T00:08:00.000-08:002012-02-15T00:08:37.170-08:00Microsoft.Ace.OLEDB.12.0 and OPENROWSET Errors<span style="background-color: #eaf2de;"></span><br />
<span style="color: #2f2f2f; font-family: verdana; font-size: x-small;"><span style="line-height: 19px;"><b>If you imports excel file into database using sql query </b></span></span><br />
<span style="color: #2f2f2f; font-family: verdana; font-size: x-small;"><span style="line-height: 19px;"><br /></span></span><br />
<span style="color: red; font-family: verdana; font-size: x-small;"><span style="line-height: 19px;">INSERT INTO newtable</span></span><br />
<span style="color: red; font-family: verdana; font-size: x-small;"><span style="line-height: 19px;">select * FROM</span></span><br />
<span style="color: red; font-family: verdana; font-size: x-small;"><span style="line-height: 19px;">OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\temp.xls;HDR=YES', 'SELECT * FROM [sheet$]')</span></span><br />
<div style="color: #2f2f2f; font-family: verdana; font-size: 13px; line-height: 19px;">
<br /></div>
<div style="color: #2f2f2f; font-family: verdana; font-size: 13px; line-height: 19px;">
<span style="font-size: x-small;"><b>and get below error message</b></span>
</div>
<div style="color: #2f2f2f; font-family: verdana; font-size: 13px; line-height: 19px;">
<span style="font-size: x-small;"><br /></span></div>
<br />
<span style="color: red;"><span style="background-color: #eaf2de; font-family: verdana; font-size: 13px; line-height: 19px;">Msg 7399, Level 16, State 1, Line 1</span><br style="background-color: #eaf2de; font-family: verdana; font-size: 13px; line-height: 19px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" /><span style="background-color: #eaf2de; font-family: verdana; font-size: 13px; line-height: 19px;">The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.</span><br style="background-color: #eaf2de; font-family: verdana; font-size: 13px; line-height: 19px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" /><span style="background-color: #eaf2de; font-family: verdana; font-size: 13px; line-height: 19px;">Msg 7330, Level 16, State 2, Line 1</span><br style="background-color: #eaf2de; font-family: verdana; font-size: 13px; line-height: 19px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" /><span style="background-color: #eaf2de; font-family: verdana; font-size: 13px; line-height: 19px;">Cannot fetch a row from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".</span></span>
<br />
<span style="background-color: #eaf2de; color: #2f2f2f; font-family: verdana; font-size: 13px; line-height: 19px;"><br /></span><br />
<span style="color: #2f2f2f; font-family: verdana; font-size: x-small;"><span style="line-height: 19px;"><br /></span></span><br />
<span style="background-color: #eaf2de; line-height: 19px;"><span style="color: #2f2f2f; font-family: verdana; font-size: x-small;"></span></span><br />
<span style="color: #2f2f2f; font-family: verdana; font-size: x-small;"><b>please run 2 sql below to solve it</b></span><br />
<span style="color: #2f2f2f; font-family: verdana; font-size: x-small;"><br /></span><br />
<span style="color: red; font-family: verdana; font-size: x-small;">EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1</span><br />
<span style="color: red; font-family: verdana; font-size: x-small;">GO</span><br />
<span style="color: red; font-family: verdana; font-size: x-small;">EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1</span><br />
<span style="color: red; font-family: verdana; font-size: x-small;">GO</span><br />
<br />
<div id="fastlookup_top" style="display: none;">
</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-73484819617583538752012-01-30T07:58:00.001-08:002012-01-31T21:14:52.834-08:00The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered<br />
<span style="font-family: Verdana, sans-serif;">If you run below SQL statement</span><br />
<span style="font-family: Verdana, sans-serif;">---------------------------------------------------------</span><br />
<span style="color: #274e13; font-family: Verdana, sans-serif; font-size: x-small;">Select * into DBTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')</span><br />
<span style="color: #274e13; font-family: Verdana, sans-serif; font-size: x-small;"><br /></span><br />
<span style="color: #274e13; font-family: Verdana, sans-serif; font-size: x-small;"><br /></span><br />
<span style="color: #274e13; font-family: Verdana, sans-serif; font-size: x-small;">INSERT INTO DBTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')</span><br />
<span style="font-family: Verdana, sans-serif;">--------------------------------------------------------</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">and you get an <b>error message</b> like below</span><br />
<span style="color: red; font-family: Verdana, sans-serif;"><b>The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered</b></span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;"><b><u>Solution</u></b></span><br />
<span style="font-family: Verdana, sans-serif;">you need to download </span><br />
<span style="font-family: Verdana, sans-serif;">"<b>Data Connectivity Components for 2007 office system Driver</b>".</span><br />
<span style="font-family: Verdana, sans-serif;">after you download, please restart your server.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">you can </span><a href="http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734" style="font-family: Verdana, sans-serif;" target="_blank">click link here to download from microsoft site</a><span style="font-family: Verdana, sans-serif;">.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">you will able to saw your microsoft excel driver 12.0 in your <b>Data Sources(</b>ODBC) in control panel</span><br />
<br />Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-75568616170357578452012-01-11T01:54:00.000-08:002012-01-11T01:54:10.831-08:00DTS not support in SQL Server 2008 R2<span style="font-family: Verdana, sans-serif;">Limited SQL Server 2000 DTS Functionality on 64-bit Operating Systems</span><br />
<div xmlns="http://www.w3.org/1999/xhtml">
<span style="font-family: Verdana, sans-serif;">SQL Server 2008 does not include support for DTS in the following circumstances:</span></div>
<ul style="margin-left: 40px; padding-left: 0px;" xmlns="http://www.w3.org/1999/xhtml">
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);"><span style="font-family: Verdana, sans-serif;">There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. For more information, see <a href="http://msdn.microsoft.com/en-us/library/ms143755.aspx" style="color: #960bb4; text-decoration: none;">How to: Install Support for Data Transformation Services Packages</a>.</span></li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);"><span style="font-family: Verdana, sans-serif;">There is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems.<br />
</span></li>
</ul>
<b><span style="font-family: Verdana, sans-serif;">Supported SQL Server 2000 DTS Functionality</span></b><br />
<div style="font-size: 13px; text-align: left;" xmlns="http://www.w3.org/1999/xhtml">
</div>
<div xmlns="http://www.w3.org/1999/xhtml">
<span style="font-family: Verdana, sans-serif;">SQL Server 2008 includes support for the following DTS features:</span></div>
<ul style="margin-left: 40px; padding-left: 0px;" xmlns="http://www.w3.org/1999/xhtml">
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);"><span style="font-family: Verdana, sans-serif;">The DTS runtime, the object model that it exposes, and the dtsrun.exe command prompt utility.</span></li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);"><span style="font-family: Verdana, sans-serif;">The Execute DTS 2000 Package task, for executing DTS packages within Integration Services packages.</span></li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);"><span style="font-family: Verdana, sans-serif;">The ActiveX Script task, for backward compatibility only.</span></li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);"><span style="font-family: Verdana, sans-serif;">The DTS Package Migration Wizard, for migrating DTS packages to the Integration Services package format.</span></li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);"><span style="font-family: Verdana, sans-serif;">The Upgrade Advisor rules for DTS packages, for identifying potential issues that may be encountered when migrating packages.</span></li>
</ul>
<span style="font-size: x-small;"><span style="font-family: Verdana, sans-serif;">more details on </span><a href="http://msdn.microsoft.com/en-us/library/bb500440.aspx"><b>http://msdn.microsoft.com/en-us/library/bb500440.aspx</b></a></span><br />
<br />
<div id="fastlookup_top" style="display: none;">
</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-45151273030187619172011-12-23T00:26:00.000-08:002011-12-23T00:26:30.174-08:00Import Excel Data into Mssql using SQL Statement<span style="background-color: white; font-family: monospace; font-size: 11px;">you need to run one line per line, if you run all SQL together.<br />you will get below error message:</span><br />
<span style="color: red; font-family: monospace;"><span style="font-size: 11px;"><b>Incorrect syntax near 'sp_configure'</b></span></span><span style="background-color: white; font-family: monospace; font-size: 11px;"> </span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><br /></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><u>Please Follow below Step </u></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><b>Step 1</b>: </span><span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: #6aa84f;">sp_configure 'show advanced options', 1</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;">Output Message: </span><span style="background-color: white; font-family: monospace; font-size: 11px;">Configuration option 'show advanced options' changed from 0 to 1. </span><span style="background-color: white; font-family: monospace; font-size: 11px;">Run the RECONFIGURE statement to install.</span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><br /></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><b>Step 2</b>: </span><span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: #6aa84f;">reconfigure</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;">Output </span><span style="background-color: white; font-family: monospace; font-size: 11px;">Message</span><span style="background-color: white; font-family: monospace; font-size: 11px;">: </span><span style="font-family: monospace;"><span style="font-size: 11px;">Command(s) completed successfully.</span></span><br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><br /></span></span><br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><b>Step 3</b>: </span></span><span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: #6aa84f;">sp_configure 'Ad Hoc Distributed Queries', 1</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;">Output </span><span style="background-color: white; font-family: monospace; font-size: 11px;">Message</span><span style="background-color: white; font-family: monospace; font-size: 11px;">: </span><span style="font-family: monospace;"><span style="font-size: 11px;">Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. </span></span><span style="font-family: monospace; font-size: 11px;">Run the RECONFIGURE statement to install.</span><br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><br /></span></span><br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><b>Step 4</b>: </span></span><span style="background-color: white; color: #6aa84f; font-family: monospace; font-size: 11px;">reconfigure</span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;">Output </span><span style="background-color: white; font-family: monospace; font-size: 11px;">Message</span><span style="background-color: white; font-family: monospace; font-size: 11px;">: </span><span style="font-family: monospace;"><span style="font-size: 11px;">Command(s) completed successfully.</span></span><br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><br /></span></span><br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><b>Step 5</b>: Run your SQL to import Excel Files</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: #6aa84f;"><br /></span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><b>Insert Excel Data into New Table (Create New Table)</b></span><br />
<br />
<div class="MsoNormal">
<span style="color: #6aa84f;"><span style="font-family: 'Courier New'; font-size: 10pt;">INSERT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> INTO myTableName<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: #6aa84f;"><span style="font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')</span></span></div>
<div class="MsoNormal">
<span style="color: blue;"><span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; font-size: 10pt;"><b>Insert Excel Data into Existing Table</b></span></div>
<div class="MsoNormal">
<span style="color: #6aa84f;"><span style="font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> * INTO </span> <span style="font-family: 'Courier New'; font-size: 13px;">myTableName</span> <span style="font-family: 'Courier New'; font-size: 10pt;">FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')</span></span>
</div>
<div class="MsoNormal">
<span style="color: #6aa84f;"><span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span></span></div>
<br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><u><b><br /></b></u></span></span><br />
<span style="font-family: monospace;"><span style="font-size: 11px;"><u><b>SQL Code to import Excel Data into New Table in Database</b></u></span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: blue;"><br /></span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: blue;">sp_configure 'show advanced options', 1</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: blue;">reconfigure</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: blue;">sp_configure 'Ad Hoc Distributed Queries', 1</span></span><br />
<span style="color: blue;"><span style="background-color: white; font-family: monospace; font-size: 11px;">reconfigure</span>
</span><br />
<br />
<br />
<div class="MsoNormal">
<span style="color: blue;"><span style="font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> * INTO </span>
<span style="font-family: 'Courier New'; font-size: 13px;">myTableName</span> </span><span style="font-family: "Courier New"; font-size: 10.0pt;"><span style="color: blue;">FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')</span><span style="color: grey;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"><span style="color: blue;"><br /></span></span></div>
<div class="MsoNormal">
<u style="font-family: monospace; font-size: 11px;"><b>SQL Code to import Excel Data into Existing Table in Database</b></u>
</div>
<div class="MsoNormal">
<u style="font-family: monospace; font-size: 11px;"><b><br /></b></u></div>
<div class="MsoNormal">
</div>
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: blue;">sp_configure 'show advanced options', 1</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: blue;">reconfigure</span></span><br />
<span style="background-color: white; font-family: monospace; font-size: 11px;"><span style="color: blue;">sp_configure 'Ad Hoc Distributed Queries', 1</span></span><br />
<span style="color: blue;"><span style="background-color: white; font-family: monospace; font-size: 11px;">reconfigure</span></span><br />
<br />
<div class="MsoNormal">
<span style="color: blue;"><span style="font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> * INTO </span> <span style="font-family: 'Courier New'; font-size: 13px;">myTableName</span> </span><span style="font-family: 'Courier New'; font-size: 10pt;"><span style="color: blue;">FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')</span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: 'Courier New'; font-size: x-small;"><br /></span></div>
<br />
<br />
<br />
<div id="fastlookup_top" style="display: none;">
</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-88973988601517997662011-09-30T04:44:00.000-07:002011-09-30T04:44:11.696-07:00Database owner is already a user in the databaseError : Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46<br />
The proposed new database owner is already a user in the database<br />
<br />
If you are getting above error message while changing the database owner. ‘DBuser’ cannot become the owner of the current database if it already has access/dbo access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or ‘user’ within the current database first. here is the solution.<br />
<br />
<br />
<span class="Apple-style-span" style="color: #073763; font-family: Verdana, sans-serif;">Use Database_Name;</span><br />
<span class="Apple-style-span" style="color: #073763; font-family: Verdana, sans-serif;">sp_dropuser ‘DBUser’</span><br />
<span class="Apple-style-span" style="color: #073763; font-family: Verdana, sans-serif;">sp_changedbowner ‘DBUser’</span><br />
<br />
<br />
<div id="fastlookup_top" style="display: none;"></div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-27278426017063181512011-09-30T04:36:00.000-07:002011-09-30T04:36:44.183-07:00Back Up the Transaction Log When the Database Is Damaged<div><span class="Apple-style-span" style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><h3 class="procedureSubHeading" style="color: #3f529c; font-family: 'Segoe UI', Verdana, Arial; font-size: 1.077em; font-weight: bold;">To create a backup of the currently active transaction log</h3><div class="subSection"><ol><li>Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:<br />
<ul style="list-style-type: disc; margin-left: 40px; padding-left: 0px;"><li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);">The name of the database to which the transaction log to back up belongs.</li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);">The backup device where the transaction log backup will be written.</li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);">The NO_TRUNCATE clause.<br />
<div style="margin-top: -6px;">This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.</div></li>
</ul></li>
<li>Optionally, specify:<br />
<ul style="list-style-type: disc; margin-left: 40px; padding-left: 0px;"><li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);">The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.</li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);">The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.</li>
<li style="list-style-image: url(http://i3.msdn.microsoft.com/Hash/fcc063e32e5c2395ba48efadd8423b60.gif);">The FORMAT clause, when you are using media for the first time, to initialize the backup media and rewrite any existing media header.<br />
<div style="margin-top: -6px;">The INIT clause is not required if the FORMAT clause is specified.</div><div style="margin-top: -6px;"><br />
</div><div style="margin-top: -6px;"><br />
</div><div style="margin-top: -6px;"><br />
</div></li>
</ul></li>
</ol></div></span></div><div><span class="Apple-style-span" style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;">This example backs up the currently active transaction log for the MyAdvWorks_FullRM database even though MyAdvWorks_FullRM has been damaged and is inaccessible. However, the transaction log is undamaged and accessible:</span></div><div><span class="Apple-style-span" style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><span class="Apple-style-span" style="color: blue;">BACKUP LOG </span><span class="Apple-style-span" style="color: #38761d;">DBNAME</span></span></div><div><span class="Apple-style-span" style="color: blue; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> TO MyAdvWorks_FullRM_log1</span></div><div><span class="Apple-style-span" style="color: blue; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> WITH NO_TRUNCATE</span></div><div><span class="Apple-style-span" style="color: blue; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">GO</span></div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-77641987464105358762011-09-29T02:35:00.000-07:002011-09-29T02:35:54.380-07:0010 reasons why go for SQL Server 2008<span class="Apple-style-span" style="background-color: #fcfaf0; color: #6a604f; font-family: Helvetica, Arial, Geneva, sans-serif;"></span><br />
<div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">10. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Plug-in model for SSMS</strong>. SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind. Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. </div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">9. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Inline variable assignment.</strong> I often wondered why, as a language, SQL languishes behind the times. I mean, it has barely any modern syntactic sugar. Well, in this version, they are at least scratching the the tip of the iceberg. </div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Instead of:</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><pre class="csharpcode" style="background-color: white; color: black; font-family: Consolas, 'Courier New', Courier, monospace; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">DECLARE</span> @myVar <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">int</span>
<span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">SET</span> @myVar = 5</pre></div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><br style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" />you can do it in one line:</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><pre class="csharpcode" style="background-color: white; color: black; font-family: Consolas, 'Courier New', Courier, monospace; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">DECLARE</span> @myVar <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">int</span> = 5</pre></div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><br style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" />Sweet. </div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">8. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">C like math syntax</strong>. <span style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">SET</span> @i += 5. Enough said. They finally let a C# developer on the SQL team. </div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">7. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Auditing.</strong> It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws. It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it. SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.</div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">6. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Compression</strong>. You may think that this feature is a waste of time, but it's not what it sounds like. The release will offer row-level and page-level compression. The compression mostly takes place on the metadata. For instance, page compression will store common data for affected rows in a single place. </div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes). For instance, length of the varchar will be stored in 3 bits. </div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Anyway, I don't really care about space savings - storage is cheap. What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization. Every single performance problem I ever dealt with had to do with I/O overloading. Will see how this plays out. I am skeptical until I see some real world production benchmarks.</div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">5. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Filtered Indexes.</strong> This is another feature that sounds great - will have to see how it plays out. Anyway, it allows you to create an index while specifying what rows are not to be in the index. For example, index all rows where Status != null. Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries. </div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">4. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Resource governor.</strong> All I can say is FINALLY. Sybase has had it since version 12 (that's last millennium, people). Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to. At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.</div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Actually Sybase is still ahead of MS on this feature. Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.</div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">3. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Plan freezing</strong>. This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...). If you've achieved your optimal query plan, now you can stick with it. Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.</div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">2. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Processing of delimited strings.</strong> This is awesome and I could have used this feature...well, always. Currently, we pass in delimited strings in the following manner:</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><pre class="csharpcode" style="background-color: white; color: black; font-family: Consolas, 'Courier New', Courier, monospace; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">exec</span> sp_MySproc <span class="str" style="color: #006080; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">'murphy,35;galen,31;samuels,27;colton,42'</span></pre></div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><br style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" />Then the stored proc needs to parse the string into a usable form - a mindless task.</div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">In 2008, Microsoft introduced Table Value Parameters (TVP). </div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><pre class="csharpcode" style="background-color: white; color: black; font-family: Consolas, 'Courier New', Courier, monospace; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">CREATE</span> TYPE PeepsType <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">AS</span> <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">TABLE</span> (Name <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">varchar</span>(20), Age <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">int</span>)
<span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">DECLARE</span> @myPeeps PeepsType
INSERT @myPeeps <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">SELECT</span> <span class="str" style="color: #006080; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">'murphy'</span>, 35
INSERT @myPeeps <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">SELECT</span> <span class="str" style="color: #006080; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">'galen'</span>, 31
INSERT @myPeeps <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">SELECT</span> <span class="str" style="color: #006080; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">'samuels'</span>, 27
INSERT @myPeeps <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">SELECT</span> <span class="str" style="color: #006080; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">'colton'</span>, 42
<span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">exec</span> sp_MySproc2 @myPeeps </pre></div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"></div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><br style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" />And the sproc would look like this:</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><pre class="csharpcode" style="background-color: white; color: black; font-family: Consolas, 'Courier New', Courier, monospace; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">CREATE</span> <span class="kwrd" style="color: blue; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">PROCEDURE</span> sp_MySproc2(@myPeeps PeepsType READONLY) ...</pre></div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><br style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" />The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc. Say goodbye to all those string parsing routines.</div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">1. <strong style="color: #d26941; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Intellisense</strong> in the SQL Server Management Studio (SSMS). This has been previously possible in SQL Server 2000 and 2005 with <img align="right" alt="Intellisense" border="0" height="129" src="http://angryhacker.com/blog/images/angryhacker_com/blog/WindowsLiveWriter/10reasonswhySQLServer2008isgoingtorock_13E6B/Intellisense_3.png" style="border-bottom-color: rgb(202, 202, 202); border-bottom-style: solid; border-bottom-width: 0px; border-left-color: rgb(202, 202, 202); border-left-style: solid; border-left-width: 0px; border-right-color: rgb(202, 202, 202); border-right-style: solid; border-right-width: 0px; border-top-color: rgb(202, 202, 202); border-top-style: solid; border-top-width: 0px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px;" width="295" />use of 3rd party add-ins like SQL Prompt ($195). But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing). <br style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" /></div><div style="margin-bottom: 1.5em; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Built-in intellisense is huge - it means new people can easily learn the database schema as they go.</div><br />
<div id="fastlookup_top" style="display: none;"></div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-42513444928241254642011-08-03T09:37:00.000-07:002011-08-03T09:37:47.066-07:00LINQ - Aggregate Operators<span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">Below code is to show the unique value:</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;"><br />
</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">Public Sub LinqSample1()<br />
Dim arr</span><span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">Distinct</span><span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">() = {1, 1, 1, 2, 2, 3, 4, 4, 5, 5}</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;"><br />
Dim strUniqueFactors = </span><span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">arr</span><span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">Distinct</span><span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">.Distinct().Count()<br />
<br />
Console.WriteLine(</span><span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">strUniqueFactors </span><span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;">& " is unique value.")<br />
End Sub</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px;"><strong style="border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #333333; font-family: inherit; font-style: inherit; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Result:</strong><br />
<span style="border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; font-family: 'Lucida Console', 'Courier New'; font-style: inherit; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><b>3 </b></span></span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace; font-size: 13px; line-height: 16px;"><b>is unique value.</b></span>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-373292982978325512011-06-20T08:15:00.000-07:002011-06-20T08:17:50.812-07:00mysql - sql injection prevention<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;">If you have ever taken raw user input and inserted it into a MySQL database there's a chance that you have left yourself wide open for a security issue known as</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;"> </span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;"><i>SQL Injection</i></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;">.</span><br />
<span class="Apple-style-span" style="font-family: arial; font-size: x-small;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px;"><br />
</span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;">SQL injection is someone inserting a SQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.</span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;">for PHP users, All you need to do is use the function <span class="Apple-style-span" style="color: red;"><i>mysql_real_escape_string</i>.</span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: x-small;"><span class="Apple-style-span" style="color: blue;">echo "Escaped Evil Injection:";</span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: blue; font-family: arial; font-size: x-small;">$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; </span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: arial; font-size: x-small;"><span class="Apple-style-span" style="color: blue;">$name_evil = mysql_real_escape_string($name_evil);</span></span><br />
<span class="Apple-style-span" style="color: blue; font-family: arial; font-size: x-small;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px;">$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";</span></span><br />
<span class="Apple-style-span" style="font-family: arial; font-size: x-small;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: arial; font-size: x-small;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px;"><b>Result</b></span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #274e13; font-family: verdana; font-size: xx-small;">Escaped Bad Injection:</span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: verdana;"><span class="Apple-style-span" style="color: #274e13; font-size: xx-small;"> SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''</span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: verdana;"><span class="Apple-style-span" style="color: #274e13; font-size: xx-small;"><br />
</span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: verdana;"><span class="Apple-style-span" style="color: #274e13; font-size: xx-small;"><a href="http://www.amazon.com/SQL-Hacks-Andrew-Cumming/dp/0596527993?ie=UTF8&tag=sqltut-20&link_code=bil&camp=213689&creative=392969" imageanchor="1" target="_blank"><img alt="SQL Hacks" src="http://ws.amazon.com/widgets/q?MarketPlace=US&ServiceVersion=20070822&ID=AsinImage&WS=1&Format=_SL160_&ASIN=0596527993&tag=sqltut-20" /></a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sqltut-20&l=bil&camp=213689&creative=392969&o=1&a=0596527993" style="border: none !important; margin: 0px !important; padding: 0px !important;" width="1" /> <a href="http://www.amazon.com/Injection-Attacks-Defense-Justin-Clarke/dp/1597494240?ie=UTF8&tag=sqltut-20&link_code=bil&camp=213689&creative=392969" imageanchor="1" target="_blank"><img alt="SQL Injection Attacks and Defense" src="http://ws.amazon.com/widgets/q?MarketPlace=US&ServiceVersion=20070822&ID=AsinImage&WS=1&Format=_SL160_&ASIN=1597494240&tag=sqltut-20" /></a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sqltut-20&l=bil&camp=213689&creative=392969&o=1&a=1597494240" style="border: none !important; margin: 0px !important; padding: 0px !important;" width="1" /> <a href="http://www.amazon.com/Web-Security-Testing-Cookbook-Systematic/dp/0596514832?ie=UTF8&tag=sqltut-20&link_code=bil&camp=213689&creative=392969" imageanchor="1" target="_blank"><img alt="Web Security Testing Cookbook: Systematic Techniques to Find Problems Fast" src="http://ws.amazon.com/widgets/q?MarketPlace=US&ServiceVersion=20070822&ID=AsinImage&WS=1&Format=_SL160_&ASIN=0596514832&tag=sqltut-20" /></a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sqltut-20&l=bil&camp=213689&creative=392969&o=1&a=0596514832" style="border: none !important; margin: 0px !important; padding: 0px !important;" width="1" /></span></span>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-55208669695884535952011-05-10T08:30:00.000-07:002011-05-10T08:32:02.577-07:00Microsoft Distributed Transaction Coordinator May Stop Responding in a Low Memory Situation<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;"><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=sqltut-20&o=1&p=8&l=bpl&asins=0735626014&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>When a server is in low memory situation, the Microsoft Distributed Transaction Coordinator (MS DTC) process (Msdtc.exe) may stop responding (crash).</span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;">When MS DTC tries to manage new transactions, the attempt fails because of a lack of resources.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;"><b>Workaround</b></span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;">To work around this problem, verify that the memory configuration of the computer is correct, and then correct the memory configuration if it is not.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;"><b>Microsoft Fix</b></span><br />
<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 16px;">To resolve this problem, obtain the latest service pack for Windows 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:</span><br />
<div class="indent" style="box-sizing: border-box; padding-bottom: 10px; padding-left: 20px; padding-right: 20px; padding-top: 0px;"><a class="KBlink" href="http://support.microsoft.com/kb/260910/EN-US" style="box-sizing: border-box; color: #07679a; text-decoration: none;">260910</a> How to Obtain the Latest Windows 2000 Service Pack</div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-21967120653040408862011-05-03T01:08:00.000-07:002011-05-03T01:09:21.296-07:00Pro LINQ - Language Integrated Query in C# - 2010<div class="separator" style="clear: both; text-align: center;"><a href="http://www.filefactory.com/file/cb1b06c/n/Pro_LINQ_-_Language_Integrated_Query_in_C#_-_2010.pdf" target="_blank"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPjI8cDKRL7ZpP1tIMiBD_NtzgGQ6PyQ1Dx4mcvkg39xaYY47jB3Y2eJC-btpmbyZQjX-qzBc4tJGYSCPjzfm-LvdAffaK6P69PDKFt3BP7NTJGi-G5fXDBxqH7UNmeDmQdNOYFXwxjz15/s320/LINQ.jpg" width="255" /></a></div><br />
<div class="separator" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; clear: both; color: #333333; font-family: Verdana, sans-serif; font-size: 13px; line-height: 19px; text-align: left;"><b><a href="http://sqltutorials.blogspot.com/2011/05/pro-linq-language-integrated-query-in-c.html">Pro LINQ - Language Integrated Query in C# - 2010</a></b></div><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #333333; font-family: Verdana, sans-serif; font-size: 13px; line-height: 19px;">I found LINQ tutorials for C#, here i </span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #333333; font-family: Verdana, sans-serif; font-size: 13px; line-height: 19px;">just share to who is interest</span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #333333; font-family: Verdana, sans-serif; font-size: 13px; line-height: 19px;"><br />
</span><br />
<div style="text-align: center;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #333333; font-family: Verdana, sans-serif; font-size: 13px; line-height: 19px;"><b><span class="Apple-style-span" style="background-color: white; color: blue; font-size: medium;"><a href="http://www.filefactory.com/file/cb1b06c/n/Pro_LINQ_-_Language_Integrated_Query_in_C#_-_2010.pdf" style="color: #6699cc;" target="_blank">Download It Now</a></span></b></span></div>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-88257134491169383502010-06-16T08:36:00.000-07:002010-06-16T08:36:10.763-07:00RESTORE DATABASE using commandI found this error message when i restore the database using the GUI and it appear this error message "<span style="color: #990000;">Error 3154: The backup set holds a backup of a database other than the existing database</span>"<br />
It because of trying to restore database on an existing active database.<br />
<br />
<b>Solution:</b><br />
<span style="color: blue;"><span style="color: #38761d;">RESTORE DATABASE</span> DatabaseName</span><br />
<span style="color: blue;"><span style="color: #38761d;">FROM DISK</span> = 'C:\myDatabase.bak'</span><br />
<span style="color: #38761d;">WITH REPLACE</span><br />
<br />
Use WITH REPLACE when using RESTORE command when u saw above error message "Error 3154: The backup set holds a backup of a database other than the existing database"<br />
<br />
Tested in <strong><span style="color: #990000;">MSSQL 2005</span></strong>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-3860211032924059971.post-42685484246125445772010-06-04T19:35:00.000-07:002010-06-04T19:35:54.899-07:00SQL @@ROWCOUNTReturns the number of rows affected by the last statement. It will let you to do a checking on the record you updated.<br />
<br />
If the number of rows is more than 2 billion, use ROWCOUNT_BIG.<br />
<br />
<strong>Example </strong><br />
<span style="color: #38761d;">USE DB2008;</span><br />
<span style="color: #38761d;">GO</span><br />
<span style="color: #38761d;">UPDATE User </span><br />
<span style="color: #38761d;">SET JobTitle = 'Manager'</span><br />
<span style="color: #38761d;">WHERE UserID = 'u10021'</span><br />
<span style="color: #38761d;">IF @@ROWCOUNT = 0</span><br />
<span style="color: #38761d;">PRINT 'Warning: No rows were updated';</span><br />
<span style="color: #38761d;">GO</span><br />
<br />
<iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=sqltut-20&o=1&p=8&l=bpl&asins=0735626014&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe><iframe align="left" frameborder="0" marginheight="0" marginwidth="0" scrolling="no" src="http://rcm.amazon.com/e/cm?t=sqltut-20&o=1&p=8&l=bpl&asins=067233044X&fc1=000000&IS2=1&lt1=_blank&m=amazon&lc1=0000FF&bc1=000000&bg1=FFFFFF&f=ifr" style="align: left; height: 245px; padding-right: 10px; padding-top: 5px; width: 131px;"></iframe>Unknownnoreply@blogger.com