http://www.sqllion.com/forums/ Welcome to the Database Jungle en-US Fri, 18 May 2012 17:29:30 +0000 http://bbpress.org/?v=1.0.3 q http://www.sqllion.com/forums/search.php http://www.sqllion.com/forums/topic/what-is-distribute-on#post-630 Thu, 12 Apr 2012 05:17:50 +0000 snt_cool 630@http://www.sqllion.com/forums/ <p>why it's used??? can any one explain with an example. Help please. Arun if you are free can you please call me 9777529752??</p> <p>--Sachin </p> http://www.sqllion.com/forums/topic/import-data-from-text-file-in-sql-server#post-548 Wed, 15 Feb 2012 13:19:30 +0000 JohnMathew 548@http://www.sqllion.com/forums/ <p>Hi JayPrakashSharma,<br /> Yes! MS-SQL Server has an option to import data from text file. To perform this task you have to use Bluk Query Command, with the help of this query you could import data from text file. But If you want to use a third party tool, there is lots of tool in market in which some famous too for this task such as MindStick DataConverter, AutConverter etc. MindStick DataConverter is a free charge tool so you could use easily and download too.<br /> MindStick DataConverter Download Path: http://www.mindstick.com/Product/ProductDC.aspx </p> <p>I hope it should be resolved your problem. </p> http://www.sqllion.com/forums/topic/import-data-from-text-file-in-sql-server#post-547 Wed, 15 Feb 2012 02:18:19 +0000 rahul.sh1 547@http://www.sqllion.com/forums/ <p>Use sql server Import Export wizard to import text file data to sql server :) </p> http://www.sqllion.com/forums/topic/import-data-from-text-file-in-sql-server#post-546 Tue, 14 Feb 2012 12:19:48 +0000 JayPrakashSharma 546@http://www.sqllion.com/forums/ <p>Hello Everyone,<br /> Is there any option in SQL Server to import txt file data. Please help me as soon as possible.<br /> Thanks in advance! </p> http://www.sqllion.com/forums/topic/sum-of-distinct-values-in-mdx#post-433 Mon, 06 Feb 2012 05:57:27 +0000 siva tataiah maganti 433@http://www.sqllion.com/forums/ <p>if u want to distinct product categorys ,apply distinct keyword<br /> WITH MEMBER MEASURES.AXISDEMO AS </p> <p>SUM(AXIS(1), [Measures].[Order Quantity]) </p> <p>SELECT {[Measures].[Order Quantity],MEASURES.AXISDEMO} ON 0, </p> <p>distinct{[Product].[Category].Children} ON 1 </p> <p>FROM [Adventure Works] WHERE PeriodsToDate(<br /> [Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].[August 2006]) </p> http://www.sqllion.com/forums/topic/sum-of-distinct-values-in-mdx#post-432 Mon, 06 Feb 2012 05:54:30 +0000 siva tataiah maganti 432@http://www.sqllion.com/forums/ <p>WITH MEMBER MEASURES.AXISDEMO AS </p> <p>SUM(AXIS(1), [Measures].[Order Quantity]) </p> <p>SELECT {[Measures].[Order Quantity],MEASURES.AXISDEMO} ON 0, </p> <p>{[Product].[Category].Children} ON 1 </p> <p>FROM [Adventure Works] WHERE PeriodsToDate(<br /> [Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].[August 2006])</p> <p>The above query works like<br /> 1.First it will display product categorys for jan2006-august2006<br /> 2.here making sum of product categorys ,i'm using axis() and sum functions.</p> <p>run the above query the output is :<br /> Order Quantity AXISDEMO<br /> Accessories 2,579 31,328<br /> Bikes 14,373 31,328<br /> Clothing 7,809 31,328<br /> Components 6,567 31,328</p> <p>i hope it will ulseful for u. </p> http://www.sqllion.com/forums/topic/how-to-schedule-failed-subscriptions-in-ssrs-report-manager#post-377 Fri, 03 Feb 2012 09:49:09 +0000 siva tataiah maganti 377@http://www.sqllion.com/forums/ <p>i created 100 subscription on report1 when they are executing 30 executed sucessfully<br /> and remaining are failed here my dougt is how to reschedule failed schedules. </p> http://www.sqllion.com/forums/topic/what-is-seo-and-how-can-it-improve-my-website#post-345 Fri, 20 Jan 2012 15:35:55 +0000 BerneweNmen 345@http://www.sqllion.com/forums/ <p>The internet has opened up a totally fresh world of products and services available from during the continuous sphere and more companies are looking to SEO optimise their website. Having a larger online self-possession is essential amazingly in search changed companies or short businesses that do not be struck by a unfettered budget or exposure. A SEO circle can arrogate generate more traffic for the sake of your chosen keywords. Online marketing is critical after any followers's celebrity and not ranking high in the search engines can be detrimental to your business.</p> <p>Companies that want to SEO optimize their website can easily set aside succour for an affordable price. First for those who are new to the body of laws of SEO, starter packages drink the aggregate required without the complex setup and euphoric establish fees, or veiled penalties. Chap support is also noteworthy as a remedy for beginners to start learning.</p> <p>A SEO society can tender services that either make over the buyer and introduction to reliable results with a starter package or warranty favourable ranks with example or wild packages. When an individual keyword may not be enough, weigh upgrading to steep plans that provide much more than basic or starter packs.</p> <p>A valuable charge of getting your company's website into sunday SEO optimise inure is to smell your online campaign. Profuse services do not proposal an opportunity to divine the development of boosting your online presence. This could at all events that you are paying for results and see trade that may or may not be there. A virtuous provider that can effectively mitigate your website filthy high should be masterful to forth extended sitting standings that you can see.</p> <p>More and more companies are upgrading their pages to be SEO kind-hearted to stratum high-priced in search apparatus results. This means like more probable than not, your competitors are charming benefit of being listed among the ace 5 into specific keywords. If you intend to perpetuate a wiry online presence then it is strongly recommended to put one's finger on a party of experts to help.</p> <p>In search guaranteed results, the small investment is good the price. The capacious subject about SEO optimization is that you will not have to make reoccurring turbulent payments like television advertisements require. It is essentially impossible to rank centre of the stopper results if you do not undertake expert guidance. Experts bear the know-how roughly the science behind SEO and are the terminal conversion between slowing climbing and leaping to the top. </p> http://www.sqllion.com/forums/topic/identity-option-in-server-management-stuido-express#post-334 Wed, 14 Dec 2011 17:11:15 +0000 Arun Mallick 334@http://www.sqllion.com/forums/ <p>Hi,<br /> check the below post for more information regarding Identity property on a column and its various features.<br /> <a href="http://www.sqllion.com/2011/08/identity-in-sql/">http://www.sqllion.com/2011/08/identity-in-sql/</a></p> <p>--Arun </p> http://www.sqllion.com/forums/topic/identity-option-in-server-management-stuido-express#post-326 Wed, 14 Dec 2011 08:41:39 +0000 sandee 326@http://www.sqllion.com/forums/ <p>Hi,</p> <p>For ID Value, first time when i run the query it is counting from 1 to n..</p> <p>Next time when i run the console once again and execute the query, my id Value is not counting from 1, It is counting from n(previously where it is stopped) and incrementing thereafter...</p> <p>How to resolve this one ??</p> <p>Thanks </p> http://www.sqllion.com/forums/topic/check-scan-count-and-disk-reads-for-a-t-sql-query#post-252 Sun, 17 Jul 2011 11:50:08 +0000 zeph2home 252@http://www.sqllion.com/forums/ <p>Hey thanks Arun, it really solved my issue. I am getting Scan count , logical reads , physical reads.<br /> Can you publish something more on this like how to optimize it.</p> <p>Thanks in advance. </p> http://www.sqllion.com/forums/topic/txt-file-load-bub#post-251 Sun, 17 Jul 2011 11:41:10 +0000 Arun Mallick 251@http://www.sqllion.com/forums/ <p>Hi,<br /> I am quite not sure what is type of error it is. But i got a feeling that i might be the issue with the formatting of source files you are loading.</p> <p>Kindly have a visit on the below link (might help you):<br /> <a href="http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/">http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/ </a> </p> http://www.sqllion.com/forums/topic/check-scan-count-and-disk-reads-for-a-t-sql-query#post-250 Sun, 17 Jul 2011 11:29:07 +0000 Arun Mallick 250@http://www.sqllion.com/forums/ <p>Hi,</p> <p>I would say that there is nothing else better than EXECUTION PLANS to fine tune your query. But you need to understand it message that it trying to give.<br /> What happening inside --&gt; the best answer is follow the graphical flow of execution plan.<br /> And to get the no. of times it hit a perticular table or row / IO reads, you need to enable some options in SSMS. Follow the below steps.<br /> 1) Navigate to Tools --&gt; Options<br /> 2) Expand "Query Execution" at the left pane and expand "SQL Server"<br /> 3) Select "Advanced" option under "SQL Server"<br /> 4) Check the two options "SET STATISTICS TIME" and "SET STATISTICS IO"<br /> 5) Be sure you haven't opened any query window while performing this action.</p> <p>Now this time when you will open any query window, it will be enabled by default.<br /> Just run any query, you will be able to see the statistics under "Messages" in Results pane.</p> <p>Hope it might help you get what you need.<br /> --Arun </p> http://www.sqllion.com/forums/topic/clean-server-name-browsing-history-in-ssms#post-249 Sun, 17 Jul 2011 11:14:50 +0000 Arun Mallick 249@http://www.sqllion.com/forums/ <p>Hi Rahul,<br /> I have a quick solution for this, but it will clear the whole history of server names from the drop down.<br /> If you are using SQL Server 2008, then navigate to the below path: (paste in RUN command)</p> <p><code>%APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell</code></p> <p>Search for file named <strong>"SqlStudio.bin"</strong>, and delete it.<br /> Make sure that you do not have any instance of SSMS opened while performing this operation.</p> <p>Once you deleted it, open SSMS, it will automatically create the deleted file but with no browsing history. ☺</p> <p>--Arun </p> http://www.sqllion.com/forums/topic/clean-server-name-browsing-history-in-ssms#post-248 Sun, 17 Jul 2011 06:49:52 +0000 rahul.sh1 248@http://www.sqllion.com/forums/ <p>Is there any way to remove or clear the browsing history which contains server names while you connect.<br /> Thanks </p> http://www.sqllion.com/forums/topic/check-scan-count-and-disk-reads-for-a-t-sql-query#post-245 Sun, 17 Jul 2011 06:26:35 +0000 zeph2home 245@http://www.sqllion.com/forums/ <p>Hi<br /> Recently i am involve in performance tuning of some sql queries that are used in SSRS reports. Somehow the queries are taking longer time to execute then expected. I tried checking the execution plans. But didn't got much out of it.<br /> Can anyone help me out in tracking this in a better way, like what exactly is happening inside and how many times it is scanning the rows and any I/O reads it is performing.</p> <p>Thanks in advance. </p> http://www.sqllion.com/forums/topic/txt-file-load-bub#post-237 Thu, 09 Jun 2011 20:08:41 +0000 addressNut 237@http://www.sqllion.com/forums/ <p>I've come across a very odd bug. SSIS for each loop is loading 30 + files from various directories. File volumes vary from 1 record to over 2 million records. The load table had been configured correctly to map to the file format given but I changed this - VARCHAR (100) for all fields which is way in excess of needs. Error output has been set to ignore on error and truncation. Duplicate records bar date stamp see one record load and the other fail. The underlying table has no constraints etc.</p> <p>Any ideas - have in been at work too long????? Its driving me slightly mad. </p> http://www.sqllion.com/forums/topic/sp_configure-network-packet-size#post-236 Tue, 31 May 2011 07:24:11 +0000 srinivasb 236@http://www.sqllion.com/forums/ <p>Hi,</p> <p>Links from BOL</p> <p>http://msdn.microsoft.com/en-us/library/ms189631.aspx<br /> Network Packet Size Max Value : 32767</p> <p>Network Packet Size Should Not Exceed 8060 Bytes<br /> http://msdn.microsoft.com/en-us/library/bb402885%28v=sql.100%29.aspx</p> <p>Which is the best value for Net Work Packet size w.R.To Performance?</p> <p>Thanks,<br /> Srinivas </p> http://www.sqllion.com/forums/topic/hi#post-234 Sun, 22 May 2011 04:59:19 +0000 srinivas48 234@http://www.sqllion.com/forums/ <p>please send me ssrs interview questions and answers </p> http://www.sqllion.com/forums/topic/how-to-connect-to-sybase-from-ssis-and-what-all-are-software-need-to-install#post-233 Wed, 23 Mar 2011 10:45:44 +0000 wallice 233@http://www.sqllion.com/forums/ <p>You may have to load the driver for OLEDB Sybase in your system before performing any loading operation.</p> <p>Or else, load any Sybase driver and create a DSN on top of that and access it from SSIS.</p> <p>Hope this might help you in resolving your issue. </p> http://www.sqllion.com/forums/topic/row-num#post-232 Wed, 23 Mar 2011 10:40:41 +0000 Arun Mallick 232@http://www.sqllion.com/forums/ <p>Use the below query: </p> <p><code>select ROW_NUMBER() OVER(Order by (select 1)) as RownumID, * from TableName</code> </p> http://www.sqllion.com/forums/topic/how-to-connect-to-sybase-from-ssis-and-what-all-are-software-need-to-install#post-231 Wed, 23 Mar 2011 05:22:13 +0000 Ciber-2010 231@http://www.sqllion.com/forums/ <p>Hi,</p> <p> I want to pass data from flat files to sybase by creating SSIS packages.For that what all are software need to install in my system.Can an y one help me.</p> <p>Thanks in Advance</p> <p>Venkat </p> http://www.sqllion.com/forums/topic/row-num#post-230 Tue, 22 Mar 2011 10:08:04 +0000 snt_cool 230@http://www.sqllion.com/forums/ <p>any simple query in sql server similar to 'select rownum from tablename' </p> http://www.sqllion.com/forums/topic/detect-sql-server-version#post-217 Tue, 08 Mar 2011 11:47:59 +0000 eralper 217@http://www.sqllion.com/forums/ <p>Hello anikonave,</p> <p>If you want to do this task within .NET codes, you can use SqlDataSourceEnumerator class.<br /> Please check the following sql article http://www.kodyaz.com/articles/sql-server-instances-SqlDataSourceEnumerator.aspx for a sample which also returns the target instance version.</p> <p>I hope that helps,<br /> Eralper </p> http://www.sqllion.com/forums/topic/odbc-vs-oledb#post-209 Thu, 10 Feb 2011 11:47:56 +0000 Ciber-2010 209@http://www.sqllion.com/forums/ <p>Hi,</p> <p> Can i get main Difference between ODBC and OLEDB in case of Stability,complexity,Performance,Maintainability.Its urgent,any one help me please.</p> <p>Thanks in advance,<br /> Venkat. </p> http://www.sqllion.com/forums/topic/alphanumeric-pattern-search-in-t-sql#post-203 Sun, 26 Dec 2010 12:51:30 +0000 Arun Mallick 203@http://www.sqllion.com/forums/ <p>Hello Vikash,<br /> use the below query to list all alphanumeric CallIDs from your CallLog table:</p> <pre><code>select * INTO CallLog_alphaNumeric from CallLog where CallID like &#39;%[^0-9]%&#39; and CallID like &#39;%[^a-z]%&#39;</code></pre> <p>Hope this will solve your problem </p> http://www.sqllion.com/forums/topic/alphanumeric-pattern-search-in-t-sql#post-202 Sun, 26 Dec 2010 08:27:05 +0000 vikash4all 202@http://www.sqllion.com/forums/ <p>Thanks Bhol,<br /> i followed your method, and it is working perfectly.<br /> But isn't there any way to do the same in t-sql, as per day i have go through lots of table where i have to face this type of problem.<br /> If there will be anyway to do the same in SSMS, then that will be more helpful.</p> <p>Anyways thanks a lot for the solution. </p> http://www.sqllion.com/forums/topic/alphanumeric-pattern-search-in-t-sql#post-201 Sun, 26 Dec 2010 07:52:50 +0000 bretbhol 201@http://www.sqllion.com/forums/ <p>Hi Vikash,<br /> this is quite easy by error handling method in SSIS,<br /> as you said that for a perticular region 'X', all the CallIDs will be numeric only.<br /> In SSIS, use "derived column" transformation or "data conversion" transformation to type cast the input CallID to int or bigint, and redirect the error line to a table i.e. "CallIDError".</p> <p>This way you will get all those CallIDs which consists of alphanumeric characters.<br /> --Bhol </p> http://www.sqllion.com/forums/topic/alphanumeric-pattern-search-in-t-sql#post-200 Sun, 26 Dec 2010 07:21:22 +0000 vikash4all 200@http://www.sqllion.com/forums/ <p>Hi All,<br /> i have a CallLog table with CallID as varchar column, for a perticular region, we have CallID as all numeric values, but i found some records where i found alphanumeric values.<br /> Is there any way in t-sql to separate these alphanumeric values to another table for auditing.</p> <p>Thanks in advance.<br /> Vikash </p> http://www.sqllion.com/forums/topic/replacing-scd-type2-with-merge-in-sql-server2008#post-189 Tue, 23 Nov 2010 12:59:45 +0000 Diwakar 189@http://www.sqllion.com/forums/ <p>Can we replace SCD type2 transformation task (which is used to update dimension table in DWH) with the newly added Merge function in sql server2008? </p> http://www.sqllion.com/forums/topic/list-all-kpis-in-mdx#post-186 Thu, 18 Nov 2010 12:15:23 +0000 sqltech56 186@http://www.sqllion.com/forums/ <p>Hi david,<br /> if you want to check whether the perticular KPI is present or not, you can go with the below query:</p> <pre><code>select KPIValue(&quot;Net Income&quot;) on 0, {} on 1 from [Adventure Works]</code></pre> <p>Here <code>&quot;Net Income&quot;</code> is the KPI name. If the KPI is available it will not throw error otherwise it will throw error as below:</p> <pre><code>Executing the query ... Query (1, 8) The &#39;Nedt Income&#39; argument passed to the &#39;KPIVALUE&#39; function must be a Key Performance Indicator (KPI) name. Execution complete</code></pre> http://www.sqllion.com/forums/topic/list-all-kpis-in-mdx#post-185 Thu, 18 Nov 2010 10:04:32 +0000 davidkp 185@http://www.sqllion.com/forums/ <p>HI all,</p> <p>I want to find out whether it is possible to list down all KPI's names by MDX query.</p> <p>Basically i want to check a perticular KPI is available or not.</p> <p>Thanks. </p> http://www.sqllion.com/forums/topic/long-running-query-and-tempdb#post-184 Fri, 12 Nov 2010 04:38:58 +0000 kumarraj 184@http://www.sqllion.com/forums/ <p>Hi Rick,<br /> Its obvious that it will utilize that much of resource while dealing with Billions of records. Moreover you know it also depends on the columns and the datatypes that is used for the final output.<br /> My suggestion is to check for the below points:<br /> 1) Do aggregation calculation on integer columns only and if possible extract only ids in the final output and later on join with the master table to get other relevant information.<br /> 2) Do (NOLOCK) for each of the tables you are joining.<br /> 3) Check for indexes for joining columns, a "cover index" is mostly recommended if possible.</p> <p>And regarding the tempdb size, .4 TB is not a surprising volume as it is joining Billions of records (joining wouldn't take much tempdb size) but aggregating and storing the aggregated value for millions of records will definitely take more space.</p> <p>--Kumar </p> http://www.sqllion.com/forums/topic/long-running-query-and-tempdb#post-182 Thu, 11 Nov 2010 22:01:34 +0000 infomod 182@http://www.sqllion.com/forums/ <p>We have a long running query that is working on a main table that has ~ 1 Billion rows.<br /> The query does a group by and creates aggregates along with calculations and also CONVERT statements...it has 4 joins with the other 3 tables having ~ 333 Million rows. Indexes seem ok, execution plan looks ok.</p> <p>Box is a 64 bit 2 proc with 4 cores each and 24 GB or RAM. The large table is ~ 75 GB w/out indexes.</p> <p>What we are seeing is heavy use of tempdb both reading and writing, tempdb has grown to .4 TB in size.</p> <p>Question: does this as you would expect in terms of resource utilization and time (1 day running so far)? Any ideas or advice on what to look at in terms of efficiency and also how to tell how far along it is in the the process of crunching thru?</p> <p>thank you.<br /> Rick </p> http://www.sqllion.com/forums/topic/surrogate-key#post-181 Wed, 10 Nov 2010 18:38:16 +0000 thomasb4all 181@http://www.sqllion.com/forums/ <p>@vikash, its very true that a surrogate key in a database is a unique identifier and because that only it is used as a key column in the dimension.<br /> Theoretically any unique column in the dimension can play the role of a surrogate key.<br /> Because we generally populate fact with only numeric values, hence keeping the unique identifier column in the dimension is mostly suggested. </p>