Exec Stored Proc (C#) - the Size property has an invalid size of 0

Hi All
I am trying to execute a stored procedure that does a very simple
lookup and returns a text field. However, when I try to execute it, I
am getting a rather strange error that I can't seem to fix!
There is defiantely information coming back as I have tested this in
Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
String[1]: the Size property has an invalid size of 0.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: String[1]: the
Size property has an invalid size of 0.
Many thanks in advance for your help
Darren
STORED PROC CODE
=================
ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
-- Add the parameters for the stored procedure here
@.iID int,
@.tXML text = null output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
pRINT @.tXML
END
C# CODE
=======
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = m_sConnectionString;
oConn.Open();
SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
oConn);
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.StoredProcedure;
SqlParameter spID = oCmd.Parameters.Add("@.iID",
SqlDbType.Int);
spID.Direction = ParameterDirection.Input;
spID.Value = iSQLCacheID;
SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
SqlDbType.Text);
spXML.Direction = ParameterDirection.Output;
oCmd.ExecuteNonQuery();
oConn.Close();
XmlDocument xdDBCache = new XmlDocument();
xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
return xdDBCache;
}Just a guess, but since the sp is going to return data, I don't think you
should use ExecuteNonQuery. ExecuteNonQuery is used for executing statements
that don't return a result set (like UPDATE or DELETE).
"daz_oldham" wrote:

> Hi All
> I am trying to execute a stored procedure that does a very simple
> lookup and returns a text field. However, when I try to execute it, I
> am getting a rather strange error that I can't seem to fix!
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.InvalidOperationException: String[1]: the
> Size property has an invalid size of 0.
> Many thanks in advance for your help
> Darren
> STORED PROC CODE
> =================
> ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
> -- Add the parameters for the stored procedure here
> @.iID int,
> @.tXML text = null output
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
> pRINT @.tXML
> END
>
> C# CODE
> =======
> SqlConnection oConn = new SqlConnection();
> oConn.ConnectionString = m_sConnectionString;
> oConn.Open();
> SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
> oConn);
> oCmd.Connection = oConn;
> oCmd.CommandType = CommandType.StoredProcedure;
> SqlParameter spID = oCmd.Parameters.Add("@.iID",
> SqlDbType.Int);
> spID.Direction = ParameterDirection.Input;
> spID.Value = iSQLCacheID;
> SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
> SqlDbType.Text);
> spXML.Direction = ParameterDirection.Output;
> oCmd.ExecuteNonQuery();
> oConn.Close();
> XmlDocument xdDBCache = new XmlDocument();
> xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
> return xdDBCache;
> }
>|||daz_oldham (Darren.Ratcliffe@.gmail.com) writes:
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
The error message is unknown to me, and I can't say where it's coming
from. However, I do spot an error:

> @.tXML text = null output
This won't fly. text for output parameters is bound to fail. You
cannot assign to variables of the type text.
If you are on SQL 2005, use varchar(MAX) instead. Or even better the
xml data type.
If you are on SQL 2000, return the XML column as a result set instead.
(In which case you must use something different than ExecuteNonQuery
to retrieve the data.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> Just a guess, but since the sp is going to return data, I don't think
> you should use ExecuteNonQuery. ExecuteNonQuery is used for executing
> statements that don't return a result set (like UPDATE or DELETE).
But Daz's procedure does not return any result set, but returns data in
an OUTPUT parameter (or would have returned, had he chosen a data type
that is eligible for output parameters). The procedure also includes a
PRINT statement. Both of these are fine with ExecuteNonQuery. (To get
the data from the PRINT statement you need an InfoMessage event handler.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland
I have changed this to get the value out via a data reader, and it is
spot on.
I have never been aware in the past about having a text value as an
output parameter, but I know now!
I am currently using SQL 2000 so can't take advantage of the added XML
benefits in 2005 which is a shame really.
Many thanks for your help - and everyone else too.
Regards
Darren

Exclude Words

I have built a functioning full text search tool on my companies
extranet site. I use the containstable function. I was curious what
the syntax or process would be to do a "without these words" box to
allow users to filter their search results better.
Thanks,
Daniel Hirsch
Daniel,
You can use the NOT keyword in either your CONTAINS or CONTAINSTABLE query,
however, there are some restrictions on how it can be used, from the BOL
title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains
search conditions.
When <contains_search_condition> contains parenthesized groups, these
parenthesized groups are evaluated first.
After evaluating parenthesized groups, these rules apply when using these
logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not
allowed.
NOT cannot be specified before the first term (for example, CONTAINS
(mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can
therefore be applied in any order.
Below are two examples CONTAINSTABLE:
use pubs
-- returns 2 rows when NOT, not included and 0 rows when NOT is included
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]
-- another example of using Mutiple columns (same table) with a NOT
condition:
SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city,
KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY]
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dan Hirsch" <daniel.hirsch@.mckesson.com> wrote in message
news:1114200575.445065.76320@.f14g2000cwb.googlegro ups.com...
> I have built a functioning full text search tool on my companies
> extranet site. I use the containstable function. I was curious what
> the syntax or process would be to do a "without these words" box to
> allow users to filter their search results better.
> Thanks,
> Daniel Hirsch
>
|||Thanks Allot John. the NOT is exactly what I was looking for. I
figured there was syntax like that, but I wasn't sure exactly.
Thanks Again,
Daniel Hirsch
sql

Exchanging a "?" with NULL value

Hi SQL Champs!

I have a text file source where some data are an question mark (?). Importing this to SQL serever, I want to exchange these (?) with an NULL -value.

How do I do this most easy? Do I need a another tmptable first?

Many thaks

kurlan

While importing this you can use the replace function to replace these values on the fly.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Rule!

Thanks

EXCEPTION_ACCESS_VIOLATION using ntext

I get the following error when i execute the SQL below.
It works if the sp parameter is text rather than ntext or if the update uses
the full key.
(Windows 2K sp4, sql 2k sp3 no hot fixes)
================================================== === BugCheck Dump
================================================== ===
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 11:23:50 09/21/04.
1 Intel x86 level 15, 3 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 80%
Total Physical = 1021 MB
Available Physical = 196 MB
Total Page File = 1326 MB
Available Page File = 645 MB
Total Virtual = 2047 MB
Available Virtual = 948 MB
*Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDu
mp0170.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 09/21/04 11:23:50 spid 54
*
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 82 bytes -
* exec spStoreCompanyTest 1, 2, 3, N'test'
================Test.sql=========================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblCompanyTest]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spStoreCompanyTest]
GO
CREATE TABLE [dbo].[tblCompanyTest] (
[source] [int] NOT NULL ,
[feed] [int] NOT NULL ,
[code] [int] NOT NULL ,
[sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
(
[source],
[feed],
[code]
) ON [PRIMARY]
GO
CREATE PROCEDURE dbo.spStoreCompanyTest
@.source int,
@.feed int,
@.code int,
@.sourceData ntext --text
AS
IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND feed
= @.feed AND code = @.code)
BEGIN
INSERT INTO tblCompanyTest
VALUES(@.source, @.feed, @.code, @.sourceData)
END
ELSE
BEGIN
UPDATE tblCompanyTest
SET sourceData = @.sourceData
WHERE source = @.source
AND feed = @.feed
--and code = @.code
END
RETURN
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
exec spStoreCompanyTest 1, 2, 3, N'test'
GO
> I get the following error when i execute the SQL below.
> It works if the sp parameter is text rather than ntext or if the update
uses
> the full key.
> (Windows 2K sp4, sql 2k sp3 no hot fixes)
> ================================================== === BugCheck Dump

> ================================================== ===

> This file is generated by Microsoft SQL Server 8.00.760

> upon detection of fatal unexpected error. Please return this file,

> the query or program that produced the bugcheck, the database and

> the error log, and any other pertinent information with a Service
Request.
>

> Computer type is AT/AT COMPATIBLE.

> Current time is 11:23:50 09/21/04.

> 1 Intel x86 level 15, 3 Mhz processor(s).

> Windows NT 5.0 Build 2195 CSD Service Pack 4.

>
> Memory
> MemoryLoad = 80%
> Total Physical = 1021 MB
> Available Physical = 196 MB
> Total Page File = 1326 MB
> Available Page File = 645 MB
> Total Virtual = 2047 MB
> Available Virtual = 948 MB
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
> Server\MSSQL\log\SQLDu
> mp0170.txt

> *
>
************************************************** **************************
*
> **

> *

> * BEGIN STACK DUMP:

> * 09/21/04 11:23:50 spid 54

> *

> * Exception Address = 00000000

> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION

> * Access Violation occurred reading address 00000000

> * Input Buffer 82 bytes -

> * exec spStoreCompanyTest 1, 2, 3, N'test'

>
> ================Test.sql=========================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblCompanyTest]') and OBJECTPROPERTY(id,
N'IsUserTable') =
> 1)
> drop table [dbo].[tblCompanyTest]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[spStoreCompanyTest]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[spStoreCompanyTest]
> GO
> CREATE TABLE [dbo].[tblCompanyTest] (
> [source] [int] NOT NULL ,
> [feed] [int] NOT NULL ,
> [code] [int] NOT NULL ,
> [sourceData] [ntext] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCompanyTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCompanyTest] PRIMARY KEY CLUSTERED
> (
> [source],
> [feed],
> [code]
> ) ON [PRIMARY]
> GO
>
> CREATE PROCEDURE dbo.spStoreCompanyTest
> @.source int,
> @.feed int,
> @.code int,
> @.sourceData ntext --text
> AS
> IF NOT EXISTS(SELECT * FROM tblCompanyTest WHERE source = @.source AND
feed
> = @.feed AND code = @.code)
> BEGIN
> INSERT INTO tblCompanyTest
> VALUES(@.source, @.feed, @.code, @.sourceData)
> END
> ELSE
> BEGIN
> UPDATE tblCompanyTest
> SET sourceData = @.sourceData
> WHERE source = @.source
> AND feed = @.feed
> --and code = @.code
> END
> RETURN
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
> exec spStoreCompanyTest 1, 2, 3, N'test'
> GO
>
Paste the whole sqldmpxxxx.txt and I'll give it a go.
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

EXCEPTION_ACCESS_VIOLATION

I got this error when i try to insert a data in a table
with a Text Column in a distributed application (MSTDC
COM+ ASP), somebody have some one idea about this
error ? more descriptions from event viewer :
Error : 0 Severity : 19 State : 0
SqlDumpExceptionHandler: Process 61 generated fatal
exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.Check your SQL Server error log for the exact stack dump that goes with the
Access Violation (AV) message. Pull out the keywords from the first couple
of lines of the dump and search for them in the online Knowledge Base.
Also, use profiler to find out what the exact command is that results in
the AV and see if the same thing happens when you run that command from
Query Analyzer.
Are you on the latest service pack for SQL Server?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

EXCEPTION_ACCESS_VIOLATION

hello!

i am trying to import a text file using dts into a table created by the
package. everything checks out until it is run. the table is created
but then i get an error:
"need to run the object to perform the operation. Provider generated
code execution exception EXCEPTION_ACCESS_VIOLATION"

any ideas?
this is a development box. maybe the server cant handle it?
thanks in advance!
Tom<tomcaml@.yahoo.com> wrote in message
news:1106153734.711603.163950@.z14g2000cwz.googlegr oups.com...
> hello!
> i am trying to import a text file using dts into a table created by the
> package. everything checks out until it is run. the table is created
> but then i get an error:
> "need to run the object to perform the operation. Provider generated
> code execution exception EXCEPTION_ACCESS_VIOLATION"
>
> any ideas?
> this is a development box. maybe the server cant handle it?
> thanks in advance!
> Tom

You don't mention your version of MSSQL, but here are a couple of related KB
articles:

http://support.microsoft.com/kb/268413/EN-US/
http://support.microsoft.com/kb/271889/EN-US/

Another possibility is to try setting the task to execute on the main
package thread:

http://www.sqldts.com/default.aspx?232

If that doesn't help, you might want to post to
microsoft.public.sqlserver.dts, with more details of your environment and
exactly what task is failing.

Simon

Exception report in the SQL Profiler

Hello,
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
Thanks
Frank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David Gugick wrote:

> Frank Rizzo wrote:
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.
|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exception report in the SQL Profiler

Hello,
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Frank Rizzo wrote:
>> Hello,
>> I keep getting an Event Class 'Exception' in the SQL Profiler.
>> The text data is 'Error: 208, Severity: 16, State: 0'
>> However, neither ObjectID or the ObjectName colums are populated.
>> Error 208 states that object which does not exist is referenced.
>> However, I can't seem to pinpoint what it's actually complaining
>> about.
>> The application that calls the sproc where this occurs reports no
>> errors and the sprocs do execute fine.
>> How can I troubleshoot this problem?
>> Thanks
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exception report in the SQL Profiler

Hello,
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:

> Frank Rizzo wrote:
>
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exception handling with full text search

Hello,
I am dynamically generating and SQL query, which could contain illegal
words. I receive an exception and dialog box,
"System.Data.SqlClient.SqlException: A clause of the query contained only
ignored words".
Is there a way to catch that exception and resume processing (of the rest of
the application)?
My C# code looks like:
try {
...
myReader = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
...
}
How can I handle the exception?
Many thanks,
Urban
No, there is no way to do an error resume next.
You have to avoid this error by one of the three methods
1) use a FreeText search which solves this error almost all the time, but
you may get too many results returned
2) remove the noise words from the search phrase by client side parsing.
This is not always the best solution as your search may not always return
what you expect, but in general works well.
3) remove all entries from your noise word list and replace it with a blank
space. Rebuild your catalogs. The problem with this approach is that your
catalogs can get much larger and performance will consequently drop
somewhat.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Urban Bettag" <urban@.bettag.com> wrote in message
news:1092323291.76604.0@.demeter.uk.clara.net...
> Hello,
> I am dynamically generating and SQL query, which could contain illegal
> words. I receive an exception and dialog box,
> "System.Data.SqlClient.SqlException: A clause of the query contained only
> ignored words".
> Is there a way to catch that exception and resume processing (of the rest
of
> the application)?
> My C# code looks like:
> try {
> ...
> myReader = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
> }
> catch (Exception e)
> {
> Console.WriteLine(e.ToString());
> ...
> }
> How can I handle the exception?
> Many thanks,
> Urban
>
|||> No, there is no way to do an error resume next.
> You have to avoid this error by one of the three methods
> 1) use a FreeText search which solves this error almost all the time, but
> you may get too many results returned
> 2) remove the noise words from the search phrase by client side parsing.
> This is not always the best solution as your search may not always return
> what you expect, but in general works well.
> 3) remove all entries from your noise word list and replace it with a
blank
> space. Rebuild your catalogs. The problem with this approach is that your
> catalogs can get much larger and performance will consequently drop
> somewhat.
Thanks Hilary,
I am using SQL Server 2000 and within a hosted environment I possibly have
not
full control of the noise word list. Or is there a way to programmatically
access
that list?
It looks like I will try to go for 2), filtering out what is in the default
noise
word list.
Many thanks,
Urban
|||There is no way to programmatically access this list other than by using
trial and error methods to determine exactly what this list contains.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Urban Bettag" <urban@.bettag.com> wrote in message
news:1092324646.15314.0@.iris.uk.clara.net...[vbcol=seagreen]
but[vbcol=seagreen]
return[vbcol=seagreen]
> blank
your
> Thanks Hilary,
> I am using SQL Server 2000 and within a hosted environment I possibly have
> not
> full control of the noise word list. Or is there a way to programmatically
> access
> that list?
> It looks like I will try to go for 2), filtering out what is in the
default
> noise
> word list.
> Many thanks,
> Urban
>
>
|||Urban and Hilary,
Well you could do something like this... (from a previous post on this
subject):
Create Table noise_words ( Noiseword varchar(50) Not Null )
Go
Alter Table noise_words Add Constraint PK_noise_words Primary Key Clustered
( Noiseword )
Go
Then you can use BULK INSERT, BCP or DTS task to copy the contents of the
file into the database either at server startup or via a SQLServerAgent job
when the you change the noise word file. Before you copy in the
language-specific noise word file, you will need to make some changes to the
initial file from the end of the file as the noise word files contain a list
of "white space" single letters and characters at the end of the file, for
example, from noise.enu:
a b c d e f g h i j k l m n o p q r s t u v w x y z
BULK INSERT or BCP will fail or think this is one big string (no CR/LF), so
you will need to separate out the row above such that each letter takes up
its own row in the file. Open the language specific noise word file in a
text editor (notepad.exe) and change the above list to:
a
b
c
d
e
f
and so on. Be sure to eliminate any leading or trailing spaces for each
character. Once that's done, you can use BULK INSERT, BCP or DTS to copy the
data from the noise file to the noise_words table. Once the data is imported
correctly, you can use a standard SQL statement such as:
select Noiseword from noise_words where Noiseword = "between"
to use in a string parser function in your C# code to remove the noise words
in your users input string and then pass this edited string to a SQL Server
FTS query.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:elwYwTIgEHA.704@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> There is no way to programmatically access this list other than by using
> trial and error methods to determine exactly what this list contains.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Urban Bettag" <urban@.bettag.com> wrote in message
> news:1092324646.15314.0@.iris.uk.clara.net...
> but
parsing.[vbcol=seagreen]
> return
> your
have[vbcol=seagreen]
programmatically
> default
>

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman.
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.

> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =
1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.

> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman."ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.
> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.
> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...|||If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill
>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>|||My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
> >--Original Message--
> >Can anybody point me to a good text about
> >exception handling in triggers (errors and rollbacks) ?
> >
> >From 'Inside' :
> >ROLLBACK (because of a fatal error or an
> >explicit ROLLBACK command), the entire batch is aborted.
> >
> >
> >As I read this :
> >From within a trigger any FK or relational constraint
> >violation results in a fatal error aborting the complete
> >batch (and complete transaction).
> >Is this correct ?
> >
> >What if there was no transaction start ?
> >In the QA, does the QA supply a transaction
> >(increase the transaction count) if no transaction
> >was started ?
> >When sending several statements, it appears
> >that all statements are rolled back. (Are handled
> >as one transaction).
> >Does something similar happen when doing
> >similar statements from application programs ?
> >
> >Thanks for your attention,
> >
> >ben brugman.
> >
> >
> >.
> >

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman."ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.

> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =
1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.

> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...

Excel to SQL 2000

I have an excel spread sheet that contains a header and three different
sections (delineated by text statements on separate lines between the data).
Header One Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Two Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Three Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
I need to import the data from each section and import it into a different
SQL 2000 file for each section of the excel spreadsheet. I also need to
perform some data manipulation during the import.
I am a beginner so I would appreciate your advice on how to do this as well
as article, samples, links etc. that I might be able to learn from. Thank
you.Hi Mike
Check out sqldts.com for lots of information on how to do things using DTS!
The safest option would be if you can make each section a named range then
they could be imported separately. Another option, if all sections have the
same format would be to import the data into a staging table (with an
identity column) and then split it off from there (although I don't know if
the row order will be guaranteed!!!)
John
"Mike" wrote:

> I have an excel spread sheet that contains a header and three different
> sections (delineated by text statements on separate lines between the data
).
> Header One Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Two Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Three Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> I need to import the data from each section and import it into a different
> SQL 2000 file for each section of the excel spreadsheet. I also need to
> perform some data manipulation during the import.
> I am a beginner so I would appreciate your advice on how to do this as wel
l
> as article, samples, links etc. that I might be able to learn from. Thank
> you.
>
>

Excel to SQL 2000

I have an excel spread sheet that contains a header and three different
sections (delineated by text statements on separate lines between the data).
Header One Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Two Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Three Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
I need to import the data from each section and import it into a different
SQL 2000 file for each section of the excel spreadsheet. I also need to
perform some data manipulation during the import.
I am a beginner so I would appreciate your advice on how to do this as well
as article, samples, links etc. that I might be able to learn from. Thank
you.Hi Mike
Check out sqldts.com for lots of information on how to do things using DTS!
The safest option would be if you can make each section a named range then
they could be imported separately. Another option, if all sections have the
same format would be to import the data into a staging table (with an
identity column) and then split it off from there (although I don't know if
the row order will be guaranteed!!!)
John
"Mike" wrote:
> I have an excel spread sheet that contains a header and three different
> sections (delineated by text statements on separate lines between the data).
> Header One Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Two Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Three Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> I need to import the data from each section and import it into a different
> SQL 2000 file for each section of the excel spreadsheet. I also need to
> perform some data manipulation during the import.
> I am a beginner so I would appreciate your advice on how to do this as well
> as article, samples, links etc. that I might be able to learn from. Thank
> you.
>
>

Excel report - stop text from wrapping

How do I stop text from wrapping within a cell?
or
How do I make a cell automatically grow horizontally and not vertically?On Mar 14, 5:43 am, JRMalherbe <JRMalhe...@.discussions.microsoft.com>
wrote:
> How do I stop text from wrapping within a cell?
> or
> How do I make a cell automatically grow horizontally and not vertically?
As far as I can tell, this functionality (automatic horizontal growth)
does not exist in Reporting Services. To not allow cell/text wrapping,
you would remove the selection of 'Can increase to accommodate
contents' on the Format tab of the Textbox Properties (below Textbox
height). Sorry I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||That is correct.
The RDL CanGrow property only works in the vertical direction. Setting this
to false will disable this functionality.
"EMartinez" wrote:
> On Mar 14, 5:43 am, JRMalherbe <JRMalhe...@.discussions.microsoft.com>
> wrote:
> > How do I stop text from wrapping within a cell?
> > or
> > How do I make a cell automatically grow horizontally and not vertically?
> As far as I can tell, this functionality (automatic horizontal growth)
> does not exist in Reporting Services. To not allow cell/text wrapping,
> you would remove the selection of 'Can increase to accommodate
> contents' on the Format tab of the Textbox Properties (below Textbox
> height). Sorry I could not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||My report uses a table to display the data in.
Even though all the cells is set to CanGrow=False the cells will expand
vertically and the text will wrap.
For me it seems that the CanGrow=False has no effect at all.
Is it just me or did I miss something somewhere?
"Bruce Johnson [MSFT]" wrote:
> That is correct.
> The RDL CanGrow property only works in the vertical direction. Setting this
> to false will disable this functionality.
>
>
> "EMartinez" wrote:
> > On Mar 14, 5:43 am, JRMalherbe <JRMalhe...@.discussions.microsoft.com>
> > wrote:
> > > How do I stop text from wrapping within a cell?
> > > or
> > > How do I make a cell automatically grow horizontally and not vertically?
> >
> > As far as I can tell, this functionality (automatic horizontal growth)
> > does not exist in Reporting Services. To not allow cell/text wrapping,
> > you would remove the selection of 'Can increase to accommodate
> > contents' on the Format tab of the Textbox Properties (below Textbox
> > height). Sorry I could not be of greater assistance.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. SQL Server Developer
> >
> >

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen