I do this as you have shown in second example.

Here is a good example function written by Simon Sabin.

Create FUNCTION [dbo].[fn_FileExist]

/*******************************************************************************

Written By : Simon Sabin

...

The results you are getting appear correct in a one-many relationship.

If this isn't what you need, I'd review the business requirments and the database design.

what is the datatype of the field containing the decimal point?

If it's varchar you could:

declare @mynum varchar(20)

set @mynum = '10.7333'

select replace(@mynum,'.','')

declare @mynum decimal(10,2)

declare @mynum1 int

set @mynum = 10.73

select @mynum1 = @mynum * 100

print @mynum

print @mynum1

or

declare @mynum decimal(10,2)

set @mynum = 10.73

select cast(@mynum * 100 as int)

In a case such as this, I'd look at the size of the .mdf files. You have > billion records. Are your indexes in their own filegroup? ...

do you have Begin tran .... end tran statements in the sp?

If so, inside the transaction look for "select" statements that are writing to variables.

ie.,

begin tran

Select @id = name...

what type of connection to the databases is the application using? It's possible the application is not using the most efficient protocol.

sounds like an MDAC issue. make sure you have the latest MDAC installed on the client.

I found openrowset to be the best way to import csv files.

Select * into TmpMyTable FROM OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',

''Text;Database='c:\directory_of_csv_file\';'', ''SELECT...

I see this error primarily when a column definition or variable is incorrectly defined or is too small to hold the value being inserted.

Check all the tables and recompile the...

see if this helps.  This code looks at a queue table for records that have been sitting in queue longer than 5 minutes.  If it...

After reading your message again, I realize I didn't answer it.  Am not going to delete my post, but I am going to get your answer. 

My technique (SQL Server 2005) to capture all alerts using SQL Tokens and emailing the message.

Configure the alert and on Response select "Execute a job"..select the Raise Alert Error job.

Create a new...

I've attached code to show you how I do this.

This creates a list of 3 servers.  DB1 DB2 and DB3.  Cycles through each server and return the status of all...

I gave up trying to import a text file using SSIS.  I found OpenRowSet to be more efficient and easier to program around.  I prefer Stored Procedures and TSQL over...