T-SQL update one table from another

When you want to update one table values from another table using following code might help:

UPDATE t1

  SET t1.col2 = t2.col5

  FROM Table1 AS t1

  INNER JOIN Table2 AS t2

  ON t1.Indentifier = t2.Ref_2_t1_Identifier

  WHERE

Read more on Microsoft explanation of UPDATE or stackoverflow.com forum

astu...

Memory Access Violation

Lesson learnt today!

The old application designed for 16 bit or 32-bit may not run properly in new terminal server setting. Believe me we had a big one!! The application was originally designed in/or 16-bit architecture and we tried to run it in new MS Terminal Server will all bell and whistle but it did not work and gave us memory access violation.
To solve this following steps were followed in the MS Terminal Server:
1. Right click My Computer
2. Select Properties
3. Click on Advance System Settings
4. Click on Settings under Advance > Performance
5. Select Data Execution Prevention tab
6. Select Turn on DEP for all programs and services except those I select: option
7. Click Add
8. Browse to the exe of the file, it will show up in the "selected" box
9. Click OK


After this the application ran fine.

astu...

Padding zeros (0) in front of number or numerical string in T-SQL

To skip story start from the START:
I had to create a Alternate ID from the Employee ID in the following format
ABCD00000
4 characters followed by 5 digits. Employee ID was 4 or 5 digit so would have to pad 0 if 4 digits. I used STR combined with REPLACE function to do this.

START:
Syntax
STR (expression [, length [, decimal] ] )

Return type: varchar

expression: numerical vaue (numerical string would work too)
length: Length of resulting string including - + sign, decimal and digits
decimal: digits after decimal

REPLACE(string, pattern, replacement)

Return type: nvarchar or varchar
string: where you want to replace
pattern: what you want to replace
replacement: string to replace with

Example:

SELECT REPLACE(STR(123.45,10,4), ' ', '0')
SELECT REPLACE(STR('123.45',10,4), ' ', '0')
Result:
00123.4500


SELECT REPLACE(STR(123.45,10,0), ' ', '0')
SELECT REPLACE(STR('123.45',10,0), ' ', '0')
Result:
0000000123


SELECT REPLACE(STR(12345,10,0), ' ', '0')
SELECT REPLACE(STR('12345',10,0), ' ', '0')
Result:
0000012345


Astu...

Converting Number to Text in Crystal Reports

When changing numer to text, it puts comma and decimal, for eg

var = 1234

"aa" + CSTR(var)

would give: aa1,234.0

to get rid of , and . use following syntax

TOTEXT({variable}, 0, "")

Arguments: Variable is the one you are trying to convert into text

0: number of decimal places you want

"": thousand separator (here its nothing)

(Reference)

astu...

0x80070005 Access is Denied

I was creating a scheduled task in Windows XP SP3 and I was getting 0x80070005 Access is Denied error. I looked and tried all I could find in the net. I finally tried the following and it worked!

I made the user local administrator of the machine.

Everything went fine then after.

astu...

Alternating Color for GROUP HEADERS

I am hiding detail row and just showing group header in SSRS and needed alternating color, this is what i used

=IIf (RunningValue (Fields!SomeField.Value, CountDistinct, Nothing) Mod 2 = 0,
"Silver", "White")

it worked !

astu...

Reducing size of MS SQL Server Log File

Couple of days back I realized that my log file has grown out of control. Usually backing up transactions logs and then shrinking the log file should have taken care but I had to take one more step.

I changed the recovery model of the dB from 'Full' to 'Simple' then shrunk the log file and changed the recovery model back to 'Full'. I was able to reduce my .ldf file size from almost 100GB to about 500KB! Quite a resize.

NOTE: Take all cautions while dealing with backend dB. Backups, no users, front end program considerations.

astu. . .