SQL Errors and Solutions,The conversion of a varchar data type to a datetime data type resulted in an out-of-range value,SQL,SQL tips
SQL Server Error Message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
This error occurs when the varchar value does not form a valid date.
Error Message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data
type resulted in an out-of-range datetime value.
|
Causes:
This error occurs when trying to convert a string date value into a DATETIME data type but the date value contains an invalid date. The individual parts of the date value (day, month and year) are all numeric but together they don’t form a valid date.
To illustrate, the following SELECT statements (all based on US date format MM/DD/YYYY)will generate the error:
SELECT CAST('02/29/2006' AS DATETIME) -- 2006 Not a Leap Year
SELECT CAST('06/31/2006' AS DATETIME) -- June only has 30 Days
SELECT CAST('13/31/2006' AS DATETIME) -- There are only 12 Months
SELECT CAST('01/01/1600' AS DATETIME) -- Year is Before 1753
Another way the error may be encountered is when the format of the date string does not conform to the format expected by SQL Server as set in the SET DATEFORMAT command. For example, in United
To illustrate, if the date format expected by SQL Server is in the MM-DD-YYYY (US date format) format, the following statement will generate the error:
SELECT CAST('31-01-2006' AS DATETIME)
Solution/Workaround:
To avoid this error from happening, you can check first to determine if a certain date in a string format is valid using the ISDATE function. The ISDATE function determines if a certain expression is a valid date. So if you have a table where one of the columns contains date values but the column is defined as VARCHAR data type, you can do the following query to identify the invalid dates:
SELECT * FROM [dbo].[Orders]
WHERE ISDATE([OrderDate]) = 1
Once the invalid dates have been identified, you can have them fixed manually then you can use the CAST function to convert the date values into DATETIME data type:
SELECT CAST([OrderDate] AS DATETIME) AS [Order Date]
FROM [dbo].[Orders]
Another way to do this without having to update the table and simply return a NULL value for the invalid dates is to use a CASE condition:
SELECT CASE ISDATE([OrderDate]) WHEN 0
THEN CAST([OrderDate] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END AS [Order Date]
FROM [dbo].[Orders]
SQL Errors and Solutions,.NET Errors and Solutions,Unable to find the requested .Net Framework Data Provider. It may not be installed.,.NET
I got this error "Unable to find the requested .Net Framework Data Provider. It may not be installed." when I try to retrieve the data from SQL Server.
Reason:
Whoo!!
I had wrongly mentioned the Data provider name in my dll.
I had mentioned as
Conndll.ProviderName = "System.Data.Sqlclient" 'c in client should have been upper case letter
instead of
Conndll.ProviderName = "System.Data.SqlClient"
Convert CYYMMDD to DateTime,Convert CYYMMDD to DateTime Format,SQL,SQL Query,AS400 datetime
In the date format CYYMMDD(AS400 date format), C is Century.
If C is 0 the year is 19XX, and it is 20XX if C is 1(Where XX can be any
two digit year).
YYMMDD is Year Month and Day(All two digit).
In CYYMMDD format, today's date (May 27, 2010) would be 1100527.
It is '0' for 19 and '1' for 20 to make our comparisons easy.
But the '0' will not present in the table data as the leading zeroes of numeric will be truncated.
The following query converts the CYYMMDD format to datetime format:
SELECT dbo.TBL.ACTDT AS ActualDate, CAST(CASE WHEN SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 1, 1) = '0' THEN '19' ELSE '20' END + SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 2, 2) + SUBSTRING(RIGHT('00' + CAST(dbo.TBL.ACTDT AS VARCHAR), 7), 4, 4) AS DATETIME) AS [Converted Date] FROM TBL
Query Result:
 |
| Convert CYYMMDD Format to datetime format |
Invalid object name 'INFORMATION_SCHEMA.tables',SQL Errors and Solutions,SQL
The error Invalid object name 'INFORMATION_SCHEMA.tables' was thrown when I executed the following query,
Select * from INFORMATION_SCHEMA.tables
for a particular database.
Reason: The particular database was case sensitive.
Solution:
I re-ran the same query with upper-case letters and it worked fine.
Select * from INFORMATION_SCHEMA.TABLES - did the trick for me.
Ctrl-Alt-Del in Remote Desktop Connection Window,Remote Machine, Keyboard Shortcuts
Google Chrome offers some tips on keyboard shortcut for easy navigation and operation. The below keyboard hotkeys or accelerators are the lists of common and useful built-in shortcuts .
CTRL + SHIFT + N : automatically opens up a Chrome ‘incognito’ window which allows you to surf on a PC without leaving behind any digital footprints.
SHIFT + Escape: allows for fast access to Chrome’s task Manager utility that allows you to nix browser processes that have gone awry.
CTRL + SHIFT + T: will open recently closed browser tabs.
Window And Tab Shortcuts:
Ctrl+N – Open a new window
Ctrl+Shift+N – Open a new window in incognito mode
Press Ctrl,and click a link – Open link in a new tab
Press Shift, and click a link – Open link in a new window
Alt+F4 - Close current window
Ctrl+T - Open a new tab
Ctrl+Shift+T – Reopen the last tab you’ve closed. Google Chrome remembers the last 10 tabs you’ve closed.
Drag link to tab – Open link in specified tab
Drag link to space between tabs – Open link in a new tab in the specified position on the tab strip
Ctrl+1 through Ctrl+8 – Switch to the tab at the specified position number. The number you press represents a position on the tab strip.
Ctrl+9 – Switch to the last tab
Ctrl+Tab or Ctrl+PgDown – Switch to the next tab
Ctrl+Shift+Tab or Ctrl+PgUp – Switch to the previous tab
Ctrl+W or Ctrl+F4 – Close current tab or pop-up
Alt+Home – Open your homepage
Ctrl+O, then select file – Open a file from your computer in Google Chrome
Shortcuts To Open Google Chrome Features:
Ctrl+B – Toggle bookmarks bar on and off
Ctrl+H – View the History page
Ctrl+J – View the Downloads page
Shift+Escape – View the Task manager
Webpage Shortcuts:
Ctrl+P – Print your current page
F5 – Reload current page
Esc – Stop page loading
Ctrl+F5 or Shift+F5 – Reload current page, ignoring cached content
Press Alt, and click a link – Download link
Ctrl+F – Open find-in-page box
Ctrl+G or F3 – Find next match for your input in the find-in-page box
Ctrl+Shift+G or Shift+F3 – Find previous match for your input in the find-in-page box
Ctrl+U – View source
Drag link to bookmarks bar – Bookmark the link
Ctrl+D – Bookmark your current webpage
Ctrl++ – Make text larger
Ctrl+- – Make text smaller
Ctrl+0 – Return to normal text size
Text shortcuts:
Highlight content, then press Ctrl+C – Copy content to the clipboard
Place your cursor in a text field, then press Ctrl+V or Shift+Insert – Paste current content from the clipboard
Place your cursor in a text field, then press Ctrl+Shift+V – Paste current content from the clipboard without formatting
Highlight content in a text field, then press Ctrl+X or Shift+Delete – Delete the content and copy it to the clipboard
Ctrl-Alt-Del in Remote Desktop Connection Window,Remote Machine, Keyboard Shortcuts
Ctrl-Alt-Del keyboard shortcut has been commonly used since IBM PC with DOS era to perform soft reboot, and used in modern Windows system to activate Winlogon process (log on to Windows NT), open Task Manager or Windows Security dialog box that allows user to log off, lock computer, shutdown PC, run Task Manager, switch user and other functions.
However, whenever trying to trigger Ctrl-Alt-Del function on remote system connected via Remote Desktop Connection, the keyboard shortcut combination pressed will bring up the Ctrl+Alt-Del function on the local machine instead of the machine remotely logged on via RDC.
This behavior is by design though, as Ctrl-Alt-Del key combination is too important for any PC (to run especially when computer encounters halt error or hang). As such, Ctrl-Alt-Del keyboard shortcut is always reserved for local host computer.
In order to send Ctrl-Alt-Del keystrokes to remote computer connected via Remote Desktop client, just press the following Key combination:
Ctrl+Alt+End
a work around keyboard shortcut specially for Remote desktop machine.
Not enough storage is available to process this command
I recently faced an issue in the development server, the error message "Not enough storage is available to process this command" popped up when try to ran the longer query in the sql server.
 |
| Not enough storage is available to process this command |
In my case the IPRStackSize registry entry was missing .
To resolve these errors I did the following:
1) Start -> Run ->Type regedit
2) Locate and click the following entry
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters
3)If the IRPStackSize entry is not present in this subkey, follow these steps:
Click Edit, point to New, and then click DWORD Value.
Type IRPStackSize, and then press ENTER.
Note: Type IRPStackSize exactly as it appears. The value name is case sensitive.
4) Right Click IRPStackSize and then click Modify.
5) Select the Base as decimal. In the Data Value box, type a larger value and then click OK.
Convert YYYYMMDD to DateTime,Convert YYYYMMDD to DateTime Format,SQL,SQL Query
The following SQL query converts the date stored as decimal or numeric in the form of YYYYMMDD to datetime.
1) SELECT CAST(CAST(YourDateField AS VARCHAR(8)) AS DATETIME)
For example:
SELECT CAST(CAST(20060204 AS VARCHAR(8)) AS DATETIME) AS [DateTime]
will be displayed as
2006-02-04 00:00:00.000
2) CONVERT(datetime, YourDateField) AS [DateTime]
For example:
CONVERT(datetime, '20060204') AS [DateTime]
will be displayed as
02/04/2006
In windows forms, Application.ExecutablePath gives the executable path of the application.
What is the alternative in WPF?
System.Reflection.Assembly.GetExecutingAssembly().Location will give the executable path of the WPF application.
Convert YYYYDDD to datetime,Convert Julian Date YYYYDDD to Datetime Format,Convert Julian Date YYYYDDD to Gregarian Format,SQL,SQL Query
I got a requirement to display the date which is the format 7-digit YYYYDDD (Julian Date) to display in Gregarian format.
This is the query I wrote to convert YYYYDDD format to YYYY-MM-DD format.
YYYY - 4 - digit Year
DDD - number of the day in the year
SQL Query:
SELECT [Julian Date], DATEADD(DAY, [Julian Date]% 1000 - 1, DATEADD(YEAR,
[Julian Date]/ 1000 - 1900, 0)) AS [Gregarian Date]
FROM dbo.Test
Sample Output:
Computer Tips,Remove grid lines in remote desktop, Remote desktop issue
I am having been of late developing a Soft ware package in .NET Framework 3.5 for the LegaSync Project, to migrate the data from various legacy ERP systems (SAP,SYTELINE,MACPAC,MANMAN,JDE etc) to SQL server. when it came to production deployment, the client had hosted a server with the latest configuration ( Windows Server 2008 R2, which was a 64 bit machine.)
One of the client from an ERP had faced the issue of getting annoying gridlines in her remote desktop sessions while logging into the remote machine. It was occuring regularly during every session. Sometimes it will take the entire window and other times it will only occupy a sub-area.
If you use Remote Desktop to remotely manage servers you might have probably ran into a situation recently where you've connected to a remote server and noticed grid lines or occasional "stray pixels" on your RDP window. This is due to a minor incompatibility between Windows Server Terminal Services and older versions of the RDP client.
Fortunately, the solution is very simple. Just download the Remote Desktop/Terminal Services Client 6.0 and install. You can find it here:
http://www.microsoft.com/downloads/details.aspx?familyid=26F11F0C-0D18-4306-ABCF-D4F18C8F5DF9&displaylang=en
.NET Tips,Display line numbers in code editor, display line numbers in VS .NET IDE
We can display the line numbers in the Visual studio .NET code editor.
In the VS.NET IDE ,
Click TOOLS > OPTIONS
Expand TEXT EDITOR (in left window)
Select Basic > Editor
You will find the Line Numbers Option Under Interaction Group box.
Check Line Numbers option
Click OK
 |
| code Editor with Line Number |
