jump to navigation

Arithmetic overflow error converting varchar to numeric April 23, 2008

Posted by msrviking in Tips & Tricks.
2 comments

Sometime last week, I received an email from one of the developers saying that she was unable convert a varchar value of ‘3.43E-07’ to numeric.

I promised that I would help her, and here is what I could do and well she was delighted with the solution. Here is the example, I had given

DECLARE
@T  TABLE (VAL1 VARCHAR(50))
INSERT INTO @T VALUES (‘3.43E-07’)
SELECT * FROM @T
SELECT CONVERT(NUMERIC(38,10),CONVERT(FLOAT,VAL1)) FROM @T

I have been having tough time with the datatype FLOAT in several projects, and I owe you all a separate blog entry on this where in I shall cover the problems and solutions.

Thanks!

Advertisements

I am missing my regular blogging April 22, 2008

Posted by msrviking in General.
add a comment

Guys,

I slipped blogging again and I feel sorry for that. Anyways, I shall cover my missed topics and let me start off with this blog.

I keep reading a lot of blogs everyday, Techie and Non-Techie. I am going to share few techie blogs I read, and soon I should be bringing up my blog roll. Until then enjoy reading these,

From Microsoft Team:
http://blogs.msdn.com/psssql/default.aspx
http://sqlcat.com/

From SQL Experts:
http://sqlblog.com/blogs/adam_machanic/default.aspx
http://sqlserver-qa.net/blogs/default.aspx

Thanks.

SQL Server Performance tuning April 10, 2008

Posted by msrviking in Performance tuning.
add a comment

Guys,
 
It has been quite a while (7 days) that had my last blog entry. Well, I am back again with some stuff.

In my shop, I have been asked to share my knowledge on SQL Server performance tuning and here is what I am intending to cover during the session. Actually, I had taken one session earlier, but never blogged on it. In fact, these entries should be linked to kind of tuning I do on any of the projects having issues with performance on DB. Hey, lot of pending entries I owe the community (I am sure I shall add something on all these, pretty soon).

Coming back to what I intend to take on 4/11, here is the agenda

  • What should you know before query and index tuning?
    • Query processor architecture
    • Query and Index internals
    • STATISTICS PROFILE
  • Query and Index tuning – Demo
  • Indexing guidelines

In my next blog entry, I shall share the discussion points of the session, and of course the googlies too.

Wish me good luck for the session, guys!

Viking.

Dependent tables April 3, 2008

Posted by msrviking in Scripts.
add a comment

Yesterday, I had a query from one of my fellow workers on how to find the dependent tables of a primary key. I happen to do this script quickly for him, and he was something like "Wow!".

I haven’t yet revealed to him how got the script. Well, it is simple and I am going to share it with everyone out there.

I recollected that I had seen some results like what he wanted by running a system stored procedure. OK, so what next? I did these quickly

1) sp_helptext sp_help
2) Searched for Foreign Key keyword in the stored procedure and I couldn’t find.
    And I realized probably there could a call to a different system sp. So I scanned thro’ and found sp_helpconstraints
3) sp_helptxt sp_helpconstraint
4) Search for Foreign Key keyword, and once I found the query I tweaked around to give him more of what he needs (you see a value add Wink).

Finally the script is here, it is simple.

select 
    object_name (fk.referenced_object_id)
    , rtrim(schema_name(ObjectProperty(parent_object_id,’schemaid’))) 
     + ‘.’ + object_name(parent_object_id) 
   from sys.foreign_keys fk
    inner join sys.sysobjects so
    on fk.referenced_object_id = so.id
order by object_name(so.id)

Something about me April 2, 2008

Posted by msrviking in About self.
add a comment

Something about me!

Professional – Have been working on MS technologies for 8 years,
especially on SQL Server starting from 6.5, 7.0, 2000, 2005, to 2008.
Worked as a DB developer, DBA, SQL Server – Technical specialist.
Though, I have been in SQL Server world most of my professional life, I
haven’t ever blogged. I thought its time to contribute the community
from where I have been learning (and would still learn) a lot for long
time. Its pay back time! So stay tuned to hear a lot of day-to-day work of mine on SQL Server.

Personal – I am simple guy based out of Bangalore, married for 6 years.