LessThanDot Site Logo

LessThanDot

A decade of helpful technical content

This is an archive of the posts published to LessThanDot from 2008 to 2018, over a decade of useful content. While we're no longer adding new content, we still receive a lot of visitors and wanted to make sure the content didn't disappear forever.

SQL Server Precision And Scale Problems

Many people are confused about SQL Server’s precision and scale. This is unfortunate because choosing the correct values for precision and scale is critically important when you perform math operations using the decimal/numeric data type. The point of this blog is to explain how SQL Server determines the data type for math operations, and the order in which conversions occur. For example: Select 10 / 3 UNION All Select 10 / 3.0 UNION All Select 10 / 3.00 UNION All Select 10 / 3.000 UNION All Select 10 / 3.0000 UNION All Select 10 / 3.00000 UNION All Select 10 / 3.000000 UNION All Select 10 / 3.0000000 UNION All Select 10 / 3.00000000 Let’s take a close look at the above query so that we can predict the output. Of course, it will help if we know the data types that SQL Server uses. There is a relatively obscure function that you can use to determine the data types. SQL_VARIANT_PROPERTY

Read More...

How threads can mess up the order of things.

Today I was trying to print a bunch of reports. The reports were supposed to come out in order because the are numbered. You can do this in one report I hear you say… no because I use an old version of a reporting engine and that engine doesn’t really support multipage reports very well. You see I need to print a dutch version on one side and the french version on the other side. I could do this by printing all the dutch first and then feeding them back in and printing the french side, but dutch and french version need to have the same number obviously. Not so obvious if the printer has a hickup from time to time. But we now have a duplex printer so I just have to send out page 1 in dutch and then page 2 in french with the same number then page 3 dutch again with the following number. This is something my version of the reporting engine can’t handle but I had a quick fix. Making it print one page dutch and then a page french with the same number can be done in the same report. So I just send out n-times such a report with a changing number. Simple.

Read More...

How to export an outlook calendar to word

Someone today asked me how to copy paste the calendar in outlook to a word document. I couldn’t find the answer to that question but I found a suitable alternative instead. The alternative is to use [this dot (template)][1] file instead. I found it on [this Microsoft site.][2]. This isn’t made for us europeans, as the Note says. NOTE: This template solution is designed to work only with U.S. versions of Microsoft Windows and Microsoft Office. It may not function correctly with non-U.S. versions of these products.

Read More...

The donkey and unit testing

In dutch there is this saying. Een ezel stoot zich nooit tweemaal aan dezelfde steen. A literal translation would go like this. A donkey never bumps into the same stone twice. or Even a donkey does not bump himself twice against the same stone… A less literal translation would be this. Fool me once, shame on you. fool me twice, shame on me. So unit testing is all about being the donkey or the fool or both ;-).

Read More...

Sometimes I could hit myself.

Of course I would never hit a beautiful, charming and intelligent man like myself but I sometimes think about it. Especially if I do stupid things like this. Public Shadows Sub ShowDialog(ByVal Parent As System.Windows.Forms.Form) Implements Forms.Interfaces.Ifrm.ShowDialog MyBase.ShowDialog(Me) End Sub``` You get this error if you do that. > System.ArgumentException: Form showDialog tried to set an ineligible form as its owner. Forms cannot own themselves or their owners. > Parameter name: owner Makes perfect sense. So don’t ever do that again. The good thing is that there is an exception for this, so someone did this before.

Read More...

Setting a standard DateFormat for SQL Server

Each SQL Server has a default language. You can see what the default language is by executing these commands (in a query window). sp_configure 'default language' This will tell you what the default language is (sort of). It actually returns a config_value with an integer that represents the language id. You can then run… sp_helplanguage You will see a list of languages that SQL Server supports. The odd thing here is that the server’s language setting will not solve your problem. This setting configures the default language for NEW users. By changing this setting, existing users will continue to have their original language. This is where it gets interesting because it’s the login’s language setting that determines SQL Server’s date format.

Read More...

Do not truncate your ldf files!

You’re the perfect DBA. You have a small environement but critical none the less. You’ve setup your disaster recovery plans based on a backup schedule and solid plan. You’ve gone as far as to copy backups to external disk along with the tape backups the server administrators take nightly. Logs are backed up every 15 minutes just to make sure you can recover from anything including corrupt data and the largest of all fall outs. This morning however you saw your 70GB database all the sudden has a log size of 11GB and you’re running out of space on the 36 drive you allocated for the logs. What’s the first thing you do? Google regain disk space log files sql server. Sweet, I have a billion hits and scripts to truncate my log and all my space will be recovered. In particular you find something like this, “BACKUP LOG db_name WITH truncate_only”. Happy DBA!!!

Read More...

Cumulative update package 2 for SQL Server 2008 is available

Cumulative update package 2 for SQL Server 2008 is available. Here is what is fixed in this cumulative update SQL Hotfix bug number <th> KB article number </th> <th> Description </th> 50003172 <td> <a href="http://support.microsoft.com/kb/959783/">959783</a> </td> <td> FIX: A deadlock occurs when you implement DDL statements against the partitions that are defined in a partitioned table in SQL Server 2008 </td> 50003229 <td> <a href="http://support.microsoft.com/kb/945127/">945127</a> </td> <td> FIX: You may still experience the performance issue that is described in KB article 940945 after you install Cumulative Update 3 for SQL Server 2005 Service Pack 2 </td> 50003353 <td> <a href="http://support.microsoft.com/kb/957812/">957812</a> </td> <td> cannot merge a publication that has spatial types that include large values with a SQL Server CE subscriber </td> 50003372 <td> <a href="http://support.microsoft.com/kb/956216/">956216</a> </td> <td> FIX: The Transact-SQL debugger automatically saves changes to a script file when you run the script in debugging mode in SQL Server 2008 Management Studio </td> 50003413 <td> <a href="http://support.microsoft.com/kb/959889/">959889</a> </td> <td> FIX: When you try to install a failover instance, the unsupported X86 option is available for you to select on the options landing page in SQL Server 2008 </td> 50003417 <td> <a href="http://support.microsoft.com/kb/959767/">959767</a> </td> <td> FIX: Memory consumption by MEMORYCLERK_SOSNODE and USERSTORE_SCHEMAMANAGER may cause a performance slowdown of a SQL Server 2008 database </td> 50003451 <td> <a href="http://support.microsoft.com/kb/959768/">959768</a> </td> <td> FIX: Writeback to a measure group is not reflected in a linked measure group </td> 50003457 <td> <a href="http://support.microsoft.com/kb/957815/">957815</a> </td> <td> FIX: A SQL Server 2008 query returns incorrect results when you create a spatial index in a table that contains a composite primary key </td> 50003504 <td> <a href="http://support.microsoft.com/kb/959769/">959769</a> </td> <td> FIX: A DMX prediction query returns incorrect results when you query a logistic regression model in SQL Server 2008 Analysis Services </td> 50003529 <td> <a href="http://support.microsoft.com/kb/959770/">959770</a> </td> <td> FIX: Column data is incorrect when a lot of data is processed by the SSIS Fuzzy Grouping transformation </td> 50003541 <td> <a href="http://support.microsoft.com/kb/959771/">959771</a> </td> <td> FIX: Excel 2007 cannot connect to an OLAP cube on a server that is running SQL Server 2005 </td> 50003542 <td> <a href="http://support.microsoft.com/kb/959773/">959773</a> </td> <td> FIX: When you use Excel 2007 Service Pack 2 to make a subselect query with calculated members, the query returns unexpected error </td> 50003543 <td> <a href="http://support.microsoft.com/kb/959774/">959774</a> </td> <td> FIX: The Excel 2007 Filter function does not evaluate calculated members so that Excel PivotTable advanced filters return unexpected results </td> 50003562 <td> <a href="http://support.microsoft.com/kb/959776/">959776</a> </td> <td> FIX: Visual Studio 2008 stops responding when you leave a SQL Server 2008 Analysis Services-based database project idle for some time </td> 50003580 <td> <a href="http://support.microsoft.com/kb/958479/">958479</a> </td> <td> FIX: Error message when you try to export a SQL Server 2008 Reporting Services report by using Report Manager: "Internet Explorer cannot download <ReportName>.xls from <URL>" </td> 50003600 <td> <a href="http://support.microsoft.com/kb/959788/">959788</a> </td> <td> FIX: In SQL Server 2008, Dr. Watson error information is not posted in the Problem Reports and Solutions window if setup fails </td> 50003601 <td> <a href="http://support.microsoft.com/kb/959792/">959792</a> </td> <td> FIX: Some screens in the Cumulative Update 1 for Microsoft SQL Server 2008 setup contain incorrect information </td> 50003608 <td> <a href="http://support.microsoft.com/kb/959794/">959794</a> </td> <td> FIX: Error message when you use a SQL Server 2008 SSIS package to query a table in an Oracle database: "SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR" </td> 50003611 <td> <a href="http://support.microsoft.com/kb/958611/">958611</a> </td> <td> FIX: You may receive incorrect results when you run a query that references three or more tables in the FROM clause in SQL Server 2008 </td> 50003617 <td> <a href="http://support.microsoft.com/kb/958599/">958599</a> </td> <td> FIX: The output from the DBCC MEMORYSTATUS command indicates that the Schema Manager cache uses more buffer space than expected in SQL Server 2008 </td> 50003623 <td> <a href="http://support.microsoft.com/kb/958496/">958496</a> </td> <td> FIX: You receive error code 18834 from Replication Log Reader agent when transactional replication starts in SQL Server 2008 </td> 50003626 <td> <a href="http://support.microsoft.com/kb/956031/">956031</a> </td> <td> FIX: Error message when you estimate the compression on a table in SQL Server 2008: "Subquery returned more than 1 value" </td> 50003627 <td> <a href="http://support.microsoft.com/kb/959796/">959796</a> </td> <td> FIX: An ODBC function sequence error occurs when you try to send a table-valued parameter by using the data-at-execution mechanism in SQL Server 2008 </td> 50003644 <td> <a href="http://support.microsoft.com/kb/959780/">959780</a> </td> <td> FIX: When an AMO object is used in the SQL Server 2008 CLR stored procedure to create or update Analysis Services objects, the stored procedure may fail </td> 50003660 <td> <a href="http://support.microsoft.com/kb/958757/">958757</a> </td> <td> FIX: You cannot use the SQL Server 2008 SMO to manage certificates in Microsoft SQL Server 2005 and 2000 </td> 50003661 <td> <a href="http://support.microsoft.com/kb/958758/">958758</a> </td> <td> FIX: Full-text search functionality does not work in SQL Server 2008 Express Edition </td> 50003662 <td> <a href="http://support.microsoft.com/kb/958759/">958759</a> </td> <td> FIX: The recovery model is different when you create a new database by using SSMS </td> 50003665 <td> <a href="http://support.microsoft.com/kb/958762/">958762</a> </td> <td> FIX: When you delete a maintenance plan in a restored SQL Server 2008 database, the maintenance plan is also deleted on the server from which the database was originally backed up </td> 50003668 <td> <a href="http://support.microsoft.com/kb/958790/">958790</a> </td> <td> FIX: Management Studio: When you try to generate a "Drop Table" script SQL Server Management Studio generates a "Create Table" script instead </td> 50003669 <td> 958792 </td> <td> FIX: You cannot rebuild an index by using the REBUILD argument together with the ALTER TABLE statement or the ALTER INDEX statement if the table uses Sparse Columns as defined in SQL Server Management Studio </td> 50003670 <td> <a href="http://support.microsoft.com/kb/958793/">958793</a> </td> <td> FIX: Generate script wizard is missing tables when the source database is case-insensitive </td> 50003675 <td> <a href="http://support.microsoft.com/kb/958214/">958214</a> </td> <td> FIX: Query could return incorrect results if a passthrough predicate is used with a filter in its query plan </td> 50003676 <td> <a href="http://support.microsoft.com/kb/958208/">958208</a> </td> <td> FIX: When you call the SQLExecute function to run a query after you set the SQL_ATTR_ROW_ARRAY_SIZE attribute, you may obtain incorrect results in SQL Server 2008 </td> 50003684 <td> <a href="http://support.microsoft.com/kb/959798/">959798</a> </td> <td> FIX: A query that uses a DMV to query the sys.dm_os_performance_counters seems to stop responding in SQL Server 2008 </td> 50003690 <td> <a href="http://support.microsoft.com/kb/958918/">958918</a> </td> <td> FIX: The setup of SQL Server 2008 may not be successful </td> 50003693 <td> <a href="http://support.microsoft.com/kb/958941/">958941</a> </td> <td> FIX: The "Most Expensive Queries" chart in the Activity Monitor does not show database name or database ID number on a server that is running SQL Server 2008 </td> 50003695 <td> <a href="http://support.microsoft.com/kb/958942/">958942</a> </td> <td> FIX: Activity Monitor shows high wait times when the FSAgent wait type is enabled on a server that is running SQL Server 2008 </td> 50003696 <td> <a href="http://support.microsoft.com/kb/958943/">958943</a> </td> <td> FIX: The Database or Task State columns may be blank for some processes in Process chart of Activity Monitory on a server that is running SQL Server 2008 </td> 50003697 <td> <a href="http://support.microsoft.com/kb/959782/">959782</a> </td> <td> FIX: You cannot access Books Online help from the Activity Monitor feature in SQL Server 2008 when you press F1 </td> 50003699 <td> <a href="http://support.microsoft.com/kb/959801/">959801</a> </td> <td> FIX: When you try to patch SQL Server 2008, the patch for SQL Support is not successful </td> 50003700 <td> <a href="http://support.microsoft.com/kb/959800/">959800</a> </td> <td> FIX: When you try to install Business Intelligence Development Studio in SQL Server 2008, the installation may not be successful </td> 50003701 <td> <a href="http://support.microsoft.com/kb/954950/">954950</a> </td> <td> FIX: Error message when you run a distributed query in SQL Server 2005 or SQL Server 2008: "OLE DB provider 'SQLNCLI' for linked server '<Linked Server>' returned message 'No transaction is active'" </td> 50003706 <td> <a href="http://support.microsoft.com/kb/959795/">959795</a> </td> <td> FIX: The License Terms screen of the Setup program for Cumulative Update 1 for SQL Server 2008 incorrectly states "To install Service Pack" </td> 50003715 <td> <a href="http://support.microsoft.com/kb/959376/">959376</a> </td> <td> FIX: Error message when you run the DBCC SHRINKFILE command or the DBCC SHRINKDATABASE command on a server that contains a SQL Server 2008 database: "5205 : DBCC SHRINKFILE: Moving Page <FileId>:<PageId> failed" </td> 50003717 <td> <a href="http://support.microsoft.com/kb/959790/">959790</a> </td> <td> FIX: Database Engine Tuning Advisor in SQL Server 2008 does not recommend any index to analyze a query </td> 50003724 <td> <a href="http://support.microsoft.com/kb/956298/">956298</a> </td> <td> FIX: When you download the Report Builder ClickOnce application of SQL Server 2005 Reporting Services or SQL Server 2008 Reporting Services, the Reportbuilder.chm.deploy file is downloaded as the text/html content type instead of the application/octet-str </td> 50003728 <td> <a href="http://support.microsoft.com/kb/956254/">956254</a> </td> <td> FIX: You may obtain an incorrect result when you use a cursor to perform DML operations on a table that does not have a clustered index in SQL Server 2008 or in SQL Server 2005 </td> 50003732 <td> <a href="http://support.microsoft.com/kb/959789/">959789</a> </td> <td> FIX: Error message when you run the snapshot agent on a replication of a stored procedure in SQL Server 2005: "The Name property cannot have a value of @param&#65343;name for this version of SQL Server" </td> 50003735 <td> <a href="http://support.microsoft.com/kb/956889/">956889</a> </td> <td> FIX: Error message when you run the snapshot agent on a replication of a stored procedure in SQL Server 2005 or in SQL Server 2008: "The Name property cannot have a value of @param&#65343;name for this version of SQL Server" </td> 50003736 <td> <a href="http://support.microsoft.com/kb/959032/">959032</a> </td> <td> FIX: A peer-to-peer transactional replication does not replicate rows if you rebuild the index for a published table at the same time as you insert data in the published table in SQL Server 2005 </td> 50003741 <td> <a href="http://support.microsoft.com/kb/958555/">958555</a> </td> <td> FIX: A SQL Server Agent job that creates a ServerXMLHTTP object by using an ActiveX script remains in the Executing status and is never completed in SQL Server 2005 </td> 50003750 <td> <a href="http://support.microsoft.com/kb/958006/">958006</a> </td> <td> FIX: The size of the SQL Server 2005 error log file grows very quickly when query notifications are created and destroyed in a high ratio </td> 50003792 <td> <a href="http://support.microsoft.com/kb/959785/">959785</a> </td> <td> FIX: Error message when you try to add articles to a publication that uses an Oracle Database 11g database as the publisher in SQL Server 2008: "Constraint column '<ColumnName>' not found in table '<TableName>'" </td> 50003796 <td> <a href="http://support.microsoft.com/kb/959784/">959784</a> </td> <td> FIX: Error message when you synchronize data for a transactional publication in SQL Server 2008: "Procedure or function 'sp_MSins_dbo<TableName>_msrepl_ccs' expects parameter '@c7', which was not supplied" </td> You can get information on how to obtain this cumulative update here: http://support.microsoft.com/default.aspx/kb/958186/en-us

Read More...

SQL Server Fabric and DATAllegro demo at PASS, SQL Data Services Public CTP Available

Microsoft has shown DATAllegro at the PASS conference. They have removed Linux, the database it was running on and also the old file system. DATAllegro is running on Windows with NTFS and SQL2008 now. Can’t wait for the Kilimanjaro CTP 🙂 Another thing shown at PASS is SQL Server Fabric. SQL Server Fabric gives you the ability to manage your SQL Server environment as a whole. Inside SQL Server Management Studio there will be a new view named Fabric Explorer connecting to a Fabric Control Point. The Dashboard will show the overal health

Read More...

Why English is really important to us non-english speakers.

Today I read We are Typists first, Programmers second by Jeff Atwood. The man has a point but that’s not what I want to talk about. I want to talk about the fact that the IT business seems to be centered around the English language. Don’t get me wrong, that is not bad, I think it is actually a good thing that we can speak the same language around the world. This also means that our relatively small world is much bigger than if we stuck to our own language.

Read More...