query timeout expired

Using VB, I am running a bulk insert query from csv file into a newly created table. It works fine on small test files; but when I try it on the production data, I get a "query timeout expired" message and processing ends. The text files contain several hundred thousand lines.

How can I resolve this problem. I have several hundred of these csv files and more coming.

Here's the code:

Dim sSQL As String
sSQL = "BULK INSERT " & TableName & " "
sSQL = sSQL & "FROM '" & DataPath & "' WITH "
sSQL = sSQL & "(FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)"

DbConn.Execute sSQL

I commented out the DbConn.Execute sSQL and discovered a different error which is probably causing the timeout. Prior to the bulk insert, I check if the table exists, drop it if it does and then do the bulk insert. So now I'm getting the message:

Invalid object name 'XLDS_ds1'

The XLDS_ds1 is the table name. I was able to create the table originally so how can it be invalid? Here's the SQL code assigned to a string

sSQLExists = "IF OBJECT_ID(N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"

|||

Still can't get past the invalid object name. Since the table does not currently exist (apparently it was dropped at some point), I commented out the sSQLExists code and tried executing just the sSQL code in the original post. Still getting query timeout expired.

BTW, I ran the following query in MSSMS and got a success message but the table was NOT dropped. Anybody have a clue as to what is happenning?

IF OBJECT_ID (N'ImEx.dbo.XLDS_ds1', N'U') IS NOT NULL DROP TABLE ImEx.dbo.XLDS_ds1;

Command(s) completed successfully.

Query Syntax - Right

I have a column that has dollar amounts with the dollar sign present (i.e. $500), due to bulk insert.
I need to convert the values in that column to Float, but I get error converting varchar to float, due to the $.
I can't do something like right(amount, 3), because the amounts aren't consistent ($500, $5000, $500000) . . .
Is there a way to select right minus one? or left 2 and on?
Help appreciated.
Thanks!
Ysandre,
You need to convert it to MONEY first, then to FLOAT:
SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
That said, why are you using FLOAT? I recommend you use DECIMAL instead;
FLOAT is an inexact type and you could end up with rounding errors. That is
not the case with DECIMAL.
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> I have a column that has dollar amounts with the dollar sign present (i.e.
$500), due to bulk insert.
> I need to convert the values in that column to Float, but I get error
converting varchar to float, due to the $.
> I can't do something like right(amount, 3), because the amounts aren't
consistent ($500, $5000, $500000) . . .
> Is there a way to select right minus one? or left 2 and on?
> Help appreciated.
> Thanks!
>
|||Thank you adam! that worked!
Our developer is the one who made everything float, I don't know why I just work with it
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
"Adam Machanic" wrote:

> Ysandre,
> You need to convert it to MONEY first, then to FLOAT:
> SELECT CONVERT(FLOAT, CONVERT(MONEY, '$5000.00'))
> That said, why are you using FLOAT? I recommend you use DECIMAL instead;
> FLOAT is an inexact type and you could end up with rounding errors. That is
> not the case with DECIMAL.
>
> "Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
> news:164A2AA4-B9EA-4ABA-8821-0E6994ADE5CC@.microsoft.com...
> $500), due to bulk insert.
> converting varchar to float, due to the $.
> consistent ($500, $5000, $500000) . . .
>
>