OleDBException Overflow

Im getting the following error :

System.Data.OleDb.OleDbException was unhandled

ErrorCode=-2147217833

Message="Overflow"

Source="Microsoft JET Database Engine"

StackTrace:

at

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS

dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at

System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,

Object& executeResult)

at

System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior

behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

at

Home_Party_Solutions.PartyDBaseAccess.getCustomerID(Customer cust) in

C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\PartyDBaseAccess.vb:line 138

at

Home_Party_Solutions.Customer.getCustomerID(IPartyDBase& p_dbase)

in C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\Customer.vb:line 212

at

Home_Party_Solutions.PartyOrder.Done_Click(Object sender, EventArgs e)

in C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\PartyOrder.vb:line 150

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at

System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons

button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ButtonBase.WndProc(Message& m)

at System.Windows.Forms.Button.WndProc(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

at

System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32

msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

at

System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32

dwComponentID, Int32 reason, Int32 pvLoopData)

at

System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32

reason, ApplicationContext context)

at

System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32

reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at

Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at

Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]

commandLine)

at

Home_Party_Solutions.My.MyApplication.Main(String[] Args) in

17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

Basically I am inserting a row into a table. The sql line looks like :

Insert into Customer VALUES ('1', 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', '49024', 'a@.a.com', '3335551234')

When I copy and paste the command into Access, it successfully adds the

row into the table. However, I am getting that error when I run

it in my program. I create the string, then this is the code I am

using :

command = New OleDbCommand

command = m_Connection.CreateCommand()

command.CommandText = tempString

Dim tempInt As Integer = -1

tempInt = command.ExecuteNonQuery()

At the last line, I get the overflow.

Just for clarification, the values are (Cust ID as long, firstName as

text, lastName as text, Street as text, City as text, State as text,

Zip as long, email as text, phone as double).

Any insights into the problem? The error message isnt all that insightful.

Thanks

Hi,

first of all name the columns which have to be inserted, this would provide much more concistence accross your code and will be much easier to maintain if error occur.

Do something like the following:

INSERT INTO TableName
(
COL1,
COL2
)
VALUES
(
1,
'2'
)

Perhaps this could already solve the problem or help you to find where the problem is located.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Getting the same uninformative error when I try this.|||

You might want to try Data Access Tracing to find out what happened. Please see the link http://msdn2.microsoft.com/en-us/library/aa964124.aspx.

|||

Hi ab2034,

From the error code it seems you are getting the DB_E_DATAOVERFLOW error from the OLEDB provider. This error might be caused when "Literal value in the command exceeded the range of the type of the associated column.". So it means the provider did not like one or more of your values. I would recommend you to go ahead and try to use smaller input values and see if that works. I would start with the integral/double values first e.g. phone number. For example try putting 1 or 2 as the phone number values to see if that works and continue with other fields.

Thanks

Waseem

|||

Try
Insert into Customer VALUES (1, 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', 49024, 'a@.a.com', 3335551234)

Rather than
Insert into Customer VALUES ('1', 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', '49024', 'a@.a.com', '3335551234')

Or better yet, use placeholders and DbParameter to substitute values in sql string.

sql

OleDbCommand with Parameters

Hi,

I have application connected to MS Access DB using OleDB. When creating commands (Insert/Update/Select) I use OleDbParamater class to insert data into command. Examples :

Select ::

OleDbCommand select_cmd = new OleDbCommand("SELECT * FROM " + ObjectTable.TableName + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Update ::

OleDbCommand update_cmd = new OleDbCommand("Update " + ObjectTable.TableName + " SET " +
ObjectTable.idParent + "=@." + ObjectTable.idParent + " , " +
ObjectTable.idParentUnder + "=@." + ObjectTable.idParentUnder + " , " +
ObjectTable.License + "=@." + ObjectTable.License + " , " +
ObjectTable.Type + "=@." + ObjectTable.Type + " ," +
ObjectTable.Language + "=@." + ObjectTable.Language + " , " +
ObjectTable.Name + "=@." + ObjectTable.Name + " , " +
ObjectTable.Checksum + "=@." + ObjectTable.Checksum + " , " +
ObjectTable.VText + "=@." + ObjectTable.VText + " , " +
ObjectTable.VInt + "=@." + ObjectTable.VInt + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Parametes:: (Adding in separate method -> AddParameters(OleDbCommand command); )

command.Parameters.Add("@." + ObjectTable.idObject, OleDbType.BigInt).Value = this.IDUpper;
command.Parameters.Add("@." + ObjectTable.idObjectUnder, OleDbType.BigInt).Value = this.IDUnder;
command.Parameters.Add("@." + ObjectTable.Name, OleDbType.VarChar).Value = this.Name;
command.Parameters.Add("@." + ObjectTable.idParent, OleDbType.BigInt).Value = GetUpper(this.IDParent);
command.Parameters.Add("@." + ObjectTable.idParentUnder, OleDbType.BigInt).Value = GetUnder(this.IDParent);
command.Parameters.Add("@." + ObjectTable.License, OleDbType.BigInt).Value = this.License;
command.Parameters.Add("@." + ObjectTable.Language, OleDbType.BigInt).Value = this.Language;
command.Parameters.Add("@." + ObjectTable.Type, OleDbType.BigInt).Value = (int)this.Type;

command.Parameters.Add("@." + ObjectTable.VText, OleDbType.VarChar).Value = String.IsNullOrEmpty(this.VText) ? null : this.VText;
command.Parameters.Add("@." + ObjectTable.VInt, OleDbType.BigInt).Value = this.VInt;
command.Parameters.Add("@." + ObjectTable.Checksum, OleDbType.BigInt).Value = this.Checksum;

Question: Does the order of adding parameters to command matter? Because allways when the order of parameters added is diffrent from order in command text, I get weird Exceptions . I thought that the name matters, not the order, but it seems that system doesn't care about the parameter's name, it just picks next parameter in command.Parameters when putting values. How is it?Do you mean that if it could matter during the addition of the parameters ? It does not, as the .add method only puts the parameter in the collection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||You know what? It does. And it is the only thing that matters (talking about OleDb) -> ORDER. Despite of using names (I could questionmarks instead of parameter's names). I've done this using Oracle DB and (of cource) namespace and everything worked fine, but OleDb looks to handle thing in it's own weird way. Example :

OleDbCommand selectCmd = new OleDbCommand( "Select * from Customers where CustomerName=@.name AND CustomerAge=@.age);

selectCmd.Parameters.Add("@.age", OleDbType.Integer).Value = 20;

selectCmd.Parameters.Add("@.name", OleDbType.VarChar).Value = "Michael";

this is NOT going to work !!! If the order of parameters added to command's parameters collection is diffrent from order of parameters used in command itself, it won't work.

OLEDBcommand is too slow

Hello,

I'm using an OLEDB Command in a DataFlow which performs a parametric query to update thousands of rowsets but it is very slow.

Is there an alternative ?

it's not the component's fault it is slow. SQL faster at set based operations.

You can insert all the rows into a temporary table and then using a SQL task, run the update by joining the two tables together.|||

Thanks!

I've tested this solution, but it seems to persisted a certains slowness.

My dataflow uses these components:

OLEDB Source --> Lookup --

|||As with anything, you have to find out where the bottleneck is. Is it the source? is it the lookup? is it the dest?

Start simple. How quickly does the source get the records? Dump everything into Trash Destination. Then add the lookup? is the lookup taking a while to cache the rows? Are you selecting the whole table or just the keys that you need? etc etc.

Finally, having a poor query in SQL source will result in a slow data flow. Are the tables correctly indexed in the source query. The dest? Two many indexes? Lookup? Indexes etc etc.

Is the final SQL update correctly indexed?

Listing two components in your data flow and saying they slow is the vaguest statement you could say.

Many reasons, more possible solutions.sql

Oledb/Data Access Problems

Hi all,

I am going a bit crazy here. To give you my back ground I have done quite some work on SQL server 2000, VFP, VB6 and done quite a few web pages using frontpage. Now I am venturing into aspnet and have what seems to be some data access problems.

I got a win2k professional web server and its running/has been running my frontpage web pages quite nicely either accessing them as //localhost/myweb or www.mydomain/myweb.

Now I made some web pages in Visual Web Developer express to get my "feet wet" So I installed SQL 5.0 express on that machine, .net2.0 and VWD express and vfpoledb. I got 2 web sites designed. One to explore the login feature. Made a default web page and 2 more pages on which I display some SQL grid views from a local db I set up. Site works fine both in the test environment and also on the webserver accessed over the web. Then I added a login page with a login control and configured the site for "web Access", set up 3 test users and pw, no roles and configured the access rules to annonymous denie and all the users allow. Works great out of the VWD environment (view in browser) but when run from the web no PW is ever correct.

2nd problem on 2nd site.

Access to a VFP9.0 table via OLEDB works great again out of the design environment (View in browser) but then run from the wwroot./vfptest directory it comes up with an OLEDB error indicating it cannot find the file

any ideas?

Could you provide more details, exact error message, connection string, etc.?

Thanks

|||

I found the solution to all the problems above

Go to IIS and go to the ASPNET configuration and application tab and set the "Local impersonation" check mark and put in a user that can access the SQL or other files

OLEDB XML Reader

I need to read XML from an XML query using OLEDB reader. Is it possible?

I found code to load the XML from a data set. I tried the following code but the XML file was incorrect.

DataSet dsXML = new DataSet();

OleDbDataAdapter DBAdapter = new OleDbDataAdapter();

OleDbCommand command = new OleDbCommand();

XMLDataDocument xmlData;

command.Connection = conn;

command.CommandType = CommandType.StoredProcedure;

command.CommandText = "usp_GetXML"

DBAdapter.SelectCommand = command;

DBAdapter.Fill(dsXML, "XMLData");

xmlData = new XmlDataDocument(dsXML);

The XML file looked like:

<NewDataSet>
<XMLData>
<XML_F52E2B61-18A1-11d1-B105-00805F49916B> ... lots of letters ...</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
</XMLData>
</NewDataSet>

XML_F52E2B61-18A1-11d1-B105-00805F49916B is the name of the column of the XML generated in the SQL server.

anybody know what I am doing wrong?

Thanks in advance.

|||Hello

I am an asp.net developer.I have facing some problem for reading an XML from a given link. i have try it for another link it is ok. but the link which have authentication to open is not read into dataset.
i have put code like below

dim ds as new dataset
ds.readxml(link as string)

i have put the authenticated link directly to the browser. it the prompted for user name and password. there when i give the uname and pwd it is open.

but how can i authenticate the XML at the time of read xml in dataset in asp.net through coding.
Can any body know the process? then please help me. and send details code and procedure

waiting for kind reply

suk

oledb wait type

We have a production server that's cpu is throttled, and requests are timing
out. After running the get_waitstats sp, OLEDB is at the top of the list in
wait type (99%).
How do I determine which spid is involved in an oledb wait type?
This is a Windows 2000 sp 3 box, running sql 2000.
If you query master..sysprocesses, you can get the spids,
wait types and wait times.
If you want to see what the spids are actually executing,
pre-SP3, you can use:
dbcc inputbuffer(spid)
If you are on SP3, you can use:
declare @.Handle binary(20)
select @.Handle = sql_handle
from sysprocesses
where spid = <spid>
select * from ::fn_get_sql(@.Handle)
-Sue
On Mon, 7 Feb 2005 14:51:02 -0800, Ken
<Ken@.discussions.microsoft.com> wrote:

>We have a production server that's cpu is throttled, and requests are timing
>out. After running the get_waitstats sp, OLEDB is at the top of the list in
>wait type (99%).
>How do I determine which spid is involved in an oledb wait type?
>This is a Windows 2000 sp 3 box, running sql 2000.
>
|||OLEDB waittype is an overloaded waittype to begin with, it is set for:
* linked server calls
* bulk insert
* running SQL Trace
* running 6.x to 7.0 or 2000 conversion imports
* materializing virtual tables like select * from master.dbo.sysprocesses
and select * from master.dbo.syslockinfo
Also this wait state is set differently, since it is set when the call is
entered and unset when the call is done, so when you start a BULK INSERT it
will be set, when you are done it is unset, bt you are not really waiting,
you are doing real work, only inside an OLE DB provider, for example BULK
INSERT is hosted in the IMPROV.DLL provider, SQL Trace and materializing
virtual tables are internal providers.
In other words, high OLEDB waitstats are not very often a problem.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4utf015ilc6kaha46e8c5pme6972q7gdo7@.4ax.com...
> If you query master..sysprocesses, you can get the spids,
> wait types and wait times.
> If you want to see what the spids are actually executing,
> pre-SP3, you can use:
> dbcc inputbuffer(spid)
> If you are on SP3, you can use:
> declare @.Handle binary(20)
> select @.Handle = sql_handle
> from sysprocesses
> where spid = <spid>
> select * from ::fn_get_sql(@.Handle)
> -Sue
> On Mon, 7 Feb 2005 14:51:02 -0800, Ken
> <Ken@.discussions.microsoft.com> wrote:
>

OLEDB Wait type

I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
server, i have noticed that more than 95% of the wait time SQL Server is
experiencing is in the "OLEDB Wait" type
Can some one tell me more about OLEDB Wait type, and what causes the server
to experience such a high rate of OLEDB waits?
Thanks,
Ram
Have a look here:
http://sqldev.net/articles/WaitTypes.htm
Some of the more common things I have found with OLEDB waits are if you are
running a lot of traces, lots of DTS packages or linked servers.
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> server, i have noticed that more than 95% of the wait time SQL Server is
> experiencing is in the "OLEDB Wait" type
> Can some one tell me more about OLEDB Wait type, and what causes the
> server
> to experience such a high rate of OLEDB waits?
> Thanks,
> Ram
|||Thanks Andrew..
"Andrew J. Kelly" wrote:

> Have a look here:
> http://sqldev.net/articles/WaitTypes.htm
> Some of the more common things I have found with OLEDB waits are if you are
> running a lot of traces, lots of DTS packages or linked servers.
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>
>
|||The following article has more information on the wait types
and troubleshooting issues with different wait types:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Wed, 19 Jan 2005 07:21:02 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:

>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
>server, i have noticed that more than 95% of the wait time SQL Server is
>experiencing is in the "OLEDB Wait" type
>Can some one tell me more about OLEDB Wait type, and what causes the server
>to experience such a high rate of OLEDB waits?
>Thanks,
>Ram

OLEDB Wait type

I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
server, i have noticed that more than 95% of the wait time SQL Server is
experiencing is in the "OLEDB Wait" type
Can some one tell me more about OLEDB Wait type, and what causes the server
to experience such a high rate of OLEDB waits?
Thanks,
RamHave a look here:
http://sqldev.net/articles/WaitTypes.htm
Some of the more common things I have found with OLEDB waits are if you are
running a lot of traces, lots of DTS packages or linked servers.
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> server, i have noticed that more than 95% of the wait time SQL Server is
> experiencing is in the "OLEDB Wait" type
> Can some one tell me more about OLEDB Wait type, and what causes the
> server
> to experience such a high rate of OLEDB waits?
> Thanks,
> Ram|||Thanks Andrew..
"Andrew J. Kelly" wrote:

> Have a look here:
> http://sqldev.net/articles/WaitTypes.htm
> Some of the more common things I have found with OLEDB waits are if you ar
e
> running a lot of traces, lots of DTS packages or linked servers.
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>
>|||The following article has more information on the wait types
and troubleshooting issues with different wait types:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Wed, 19 Jan 2005 07:21:02 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:

>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
>server, i have noticed that more than 95% of the wait time SQL Server is
>experiencing is in the "OLEDB Wait" type
>Can some one tell me more about OLEDB Wait type, and what causes the server
>to experience such a high rate of OLEDB waits?
>Thanks,
>Ramsql

OLEDB Wait type

I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
server, i have noticed that more than 95% of the wait time SQL Server is
experiencing is in the "OLEDB Wait" type
Can some one tell me more about OLEDB Wait type, and what causes the server
to experience such a high rate of OLEDB waits?
Thanks,
RamHave a look here:
http://sqldev.net/articles/WaitTypes.htm
Some of the more common things I have found with OLEDB waits are if you are
running a lot of traces, lots of DTS packages or linked servers.
--
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> server, i have noticed that more than 95% of the wait time SQL Server is
> experiencing is in the "OLEDB Wait" type
> Can some one tell me more about OLEDB Wait type, and what causes the
> server
> to experience such a high rate of OLEDB waits?
> Thanks,
> Ram|||Thanks Andrew..
"Andrew J. Kelly" wrote:
> Have a look here:
> http://sqldev.net/articles/WaitTypes.htm
> Some of the more common things I have found with OLEDB waits are if you are
> running a lot of traces, lots of DTS packages or linked servers.
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
> >I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> > server, i have noticed that more than 95% of the wait time SQL Server is
> > experiencing is in the "OLEDB Wait" type
> >
> > Can some one tell me more about OLEDB Wait type, and what causes the
> > server
> > to experience such a high rate of OLEDB waits?
> >
> > Thanks,
> > Ram
>
>|||The following article has more information on the wait types
and troubleshooting issues with different wait types:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Wed, 19 Jan 2005 07:21:02 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
>server, i have noticed that more than 95% of the wait time SQL Server is
>experiencing is in the "OLEDB Wait" type
>Can some one tell me more about OLEDB Wait type, and what causes the server
>to experience such a high rate of OLEDB waits?
>Thanks,
>Ram

oledb wait type

We have a production server that's cpu is throttled, and requests are timing
out. After running the get_waitstats sp, OLEDB is at the top of the list in
wait type (99%).
How do I determine which spid is involved in an oledb wait type?
This is a Windows 2000 sp 3 box, running sql 2000.If you query master..sysprocesses, you can get the spids,
wait types and wait times.
If you want to see what the spids are actually executing,
pre-SP3, you can use:
dbcc inputbuffer(spid)
If you are on SP3, you can use:
declare @.Handle binary(20)
select @.Handle = sql_handle
from sysprocesses
where spid = <spid>
select * from ::fn_get_sql(@.Handle)
-Sue
On Mon, 7 Feb 2005 14:51:02 -0800, Ken
<Ken@.discussions.microsoft.com> wrote:

>We have a production server that's cpu is throttled, and requests are timin
g
>out. After running the get_waitstats sp, OLEDB is at the top of the list in
>wait type (99%).
>How do I determine which spid is involved in an oledb wait type?
>This is a Windows 2000 sp 3 box, running sql 2000.
>|||OLEDB waittype is an overloaded waittype to begin with, it is set for:
* linked server calls
* bulk insert
* running SQL Trace
* running 6.x to 7.0 or 2000 conversion imports
* materializing virtual tables like select * from master.dbo.sysprocesses
and select * from master.dbo.syslockinfo
Also this wait state is set differently, since it is set when the call is
entered and unset when the call is done, so when you start a BULK INSERT it
will be set, when you are done it is unset, bt you are not really waiting,
you are doing real work, only inside an OLE DB provider, for example BULK
INSERT is hosted in the IMPROV.DLL provider, SQL Trace and materializing
virtual tables are internal providers.
In other words, high OLEDB waitstats are not very often a problem.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4utf015ilc6kaha46e8c5pme6972q7gdo7@.
4ax.com...
> If you query master..sysprocesses, you can get the spids,
> wait types and wait times.
> If you want to see what the spids are actually executing,
> pre-SP3, you can use:
> dbcc inputbuffer(spid)
> If you are on SP3, you can use:
> declare @.Handle binary(20)
> select @.Handle = sql_handle
> from sysprocesses
> where spid = <spid>
> select * from ::fn_get_sql(@.Handle)
> -Sue
> On Mon, 7 Feb 2005 14:51:02 -0800, Ken
> <Ken@.discussions.microsoft.com> wrote:
>
>

OLEDB VS SQLServer destination

Hi All,

We want to take advantage of the performance benefit provided by SQL server destination in our packages. We are using a configuration variable to specify whether the SQL Server is remote or local to the packages. We are using a conditional split to redirect the process to either SQL Server destination or OLEDB destination based on the value of the variable. Is there any performance benefit in doing such a thing as it seems that the connection is made in both the paths during the runtime instead of in one particular path alone.

Thanks in advance

Kumbs

It may be opening the connection, but the data is sent to the SQL Server Destination, right? So you should get the benefits. If you really don't want to make the second connection, create two data flows, one with the OLEDB Dest., one with the SQL Server Dest.. In the control flow, put an expression of the constraints leading to the data flow to pick which one to execute.

OLEDB vs ODBC

I am new SQL Server 2005. The native client supports both OLEDB and ODBC
connections. Which one should I prefer and why? Thanks.
I would say it depends on the particular application. We use JD Edwards
which runs on SQL server and it has always been configured for ODBC
connections. Because of a couple of other apps we have in house which call
for an OLEDB connection and the performance was great we migrated from ODBC
to OLEDB on JD Edwards and the performance was severely degraded so we
switched back to ODBC on this particular app.
Hope this helps.
Robby
"mason" <masonliu@.msn.com> wrote in message
news:O81z$qzNGHA.208@.tk2msftngp13.phx.gbl...
>I am new SQL Server 2005. The native client supports both OLEDB and ODBC
>connections. Which one should I prefer and why? Thanks.

OLEDB vs ODBC

Maybe this group is more responsive for this particular question. TIA.
-- Original Message --
From: "mason"
Newsgroups: microsoft.public.sqlserver.clients
Sent: Tuesday, February 21, 2006 5:01 PM
Subject: OLEDB vs ODBC

>I am new to SQL Server 2005. The native client supports both OLEDB and ODBC
> connections. Which one should I prefer and why? Thanks.ODBC is the older technology and is harder to use. Always use OLEDB where
possible as it is easier to implement and provides faster connections.
Ian Logan
"mason" wrote:

> Maybe this group is more responsive for this particular question. TIA.
>
> -- Original Message --
> From: "mason"
> Newsgroups: microsoft.public.sqlserver.clients
> Sent: Tuesday, February 21, 2006 5:01 PM
> Subject: OLEDB vs ODBC
>
>
>|||"Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> ODBC is the older technology and is harder to use. Always use OLEDB where
> possible as it is easier to implement and provides faster connections.
> Ian Logan
>
While that is true, there are still quite a few legacy applications out
there that only support ODBC. As Ian said, in general use OLEDB when
possible, but you will probably still want to have the ODBC drivers loaded.
As an example, I believe (I could be wrong here) that Microsoft Visio (prior
to 2002) only used ODBC.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you very much. That's what I like hear. I am not sure about "easier"
part, but "faster connection" tells them apart.
"Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> ODBC is the older technology and is harder to use. Always use OLEDB where
> possible as it is easier to implement and provides faster connections.
> Ian Logan
> "mason" wrote:
>|||Thanks. Since the native client is a single DLL supporting both OLEDB and
ODBC connections, this shouldn't be a problem. We are still going to support
ODBC connection (DSNless), but to make OLEDB the default configuration for
MSSQL2005.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:e3xajlIOGHA.668@.TK2MSFTNGP11.phx.gbl...
> "Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
> news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> While that is true, there are still quite a few legacy applications out
> there that only support ODBC. As Ian said, in general use OLEDB when
> possible, but you will probably still want to have the ODBC drivers
> loaded. As an example, I believe (I could be wrong here) that Microsoft
> Visio (prior to 2002) only used ODBC.
>
> Rick Sawtell
> MCT, MCSD, MCDBAsql

OLEDB versus ODBC

HI,

I'm using a reporting tool for retrieving information from a sqlsrv 2005 env. using OLE DB for my connection.
When i've created a metric and whilst refreshing it i get the message 'object was open'. Changing the connection into ODBC everything went well.

By looking at the log file it's trying to write the metric record in the DB but it simply cannot (OLEDB) but using ODBC is all went fine...

Is something not properly installed on the server side?

anyone?

E10

Are you using a 3rd-party reporting tools? It looks like they do not use OleDb in a proper way. Better check with the reporting tools' vendor.

OLEDB TRANSACTION MAKING DIFFERENCE and error is raised

OLEDB transaction is making difference in my migrated application powerbuilder 10.5 which has new oledb driver which is replaced by MSS driver in previous Powerbuilder version.

1.)I am facing the below error on update/save this was not faced during my previous versino drive MSS in powerbuilder.This is oocure only after the change to oledb driver.

"Row change between retrieve and update.".

in front end of my application the error is poped as

"(STD1007) This row has been modified by another user. Save is canceled! ".

Please help to resolve this error.

Are you seeing this error when using Integration Services? If so, can you give more information on all the database access (e.g. ExecuteSQL, OLE DB Destination) in your package? If not, and this is specific to Powerbuilder, then I would start by looking at any settings available in your front end for handling database changes and transaction isolation level. If you want to focus on the provider itself, you can ask your question on the SQL Server Data Access forum, but they will likely have to rely on you to know how Powerbuilder is using the provider under the covers.

oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
--
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'AS400USERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
> Here is my linked server script, hope it will help you:
[...]

oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False
',@.locallogin=NULL,@.rmtuser=N'AS400U
SERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL

> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too
!)
> Here is my linked server script, hope it will help you:
[...]sql

Oledb stored procedure rowset binding

I have a strange problem with an OleDB call to a stored procedure that
returns a rowset.
Only the first time I execute the query, after I restart SqlServer, my
program crashes because
the rowset is empty. All next calls (after restarting my program, of
course) run successfully.

The context is:
1) if I do not bind the output rowset, my program doesn't crash
2) If I run the call without the binding and then I run the call with
the binding, it doesn't crash
3) It is not the first query I do in my session
4) the call crashes only if in the SP there are some inserts: it is a
well known problem with OleDB, but in many
other cases I have fixed it setting NOCOUNT to ONgigi (gigisoave@.libero.it) writes:
> I have a strange problem with an OleDB call to a stored procedure that
> returns a rowset.
> Only the first time I execute the query, after I restart SqlServer, my
> program crashes because
> the rowset is empty. All next calls (after restarting my program, of
> course) run successfully.
> The context is:
> 1) if I do not bind the output rowset, my program doesn't crash
> 2) If I run the call without the binding and then I run the call with
> the binding, it doesn't crash
> 3) It is not the first query I do in my session
> 4) the call crashes only if in the SP there are some inserts: it is a
> well known problem with OleDB, but in many
> other cases I have fixed it setting NOCOUNT to ON

Since you don't post any code, it is very difficult to tell. Then again,
even if you had, it would probably still have been difficult to tell.

First, exactly what API are you using? The OLE DB API itself? OLE DB
consumer templates? Something else? Which language do you use?

Next, let me try to see if I understand the scenario:

1) You start your machine.
2) SQL Server starts.
3) You start your application.
4) It runs some queries, and then it comes to this procedure that does
not return a rowset.
5) Your program crashes.
6) You restart your application, and now everything works fine.

Here I assumed that SQL Server ran locally. What happens if you have
SQL Server on a remote machine? Does the app still crash on first
access of SQL Server restart? What if you reboot the client machine
and run again?

Finally, while the start and stop of SQL Server could have something
to do with it, it could also be as trivial as buffer overrun, assuming
that you are programming in C++.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I can answer all your questions:

1) If I connect to a remote sqlserver the situation remains the same
2) I use AOleDB consuner template from VisualC++, but I know the
problem is in call to ICommand::Execute at oledb level.
3) when I restrat the server, the connection and the application are
closed.
4) Your scenario description is good.
Bye and thank you

> Since you don't post any code, it is very difficult to tell. Then again,
> even if you had, it would probably still have been difficult to tell.
> First, exactly what API are you using? The OLE DB API itself? OLE DB
> consumer templates? Something else? Which language do you use?
> Next, let me try to see if I understand the scenario:
> 1) You start your machine.
> 2) SQL Server starts.
> 3) You start your application.
> 4) It runs some queries, and then it comes to this procedure that does
> not return a rowset.
> 5) Your program crashes.
> 6) You restart your application, and now everything works fine.
> Here I assumed that SQL Server ran locally. What happens if you have
> SQL Server on a remote machine? Does the app still crash on first
> access of SQL Server restart? What if you reboot the client machine
> and run again?
> Finally, while the start and stop of SQL Server could have something
> to do with it, it could also be as trivial as buffer overrun, assuming
> that you are programming in C++.

OLEDB Source to Flat File

Hi,

I'm using an OLEDBSource to select some data and then putting to in a Flat File destination.

However, when I look at the data in the OLEDBSource, they′re like this:

1. id

2. name

3. address

...but in the flatfile it comes out in the wrong order.

How can I fix this?

Thank you so much.

Create the columns in the flat file connection manager in the order you need them to be in.

OLEDB Source Table Locks?

Hi All,

Is it possible that an OLEDB Data Flow Source is imposing locks on the source tables? The source is an SQL Server OLTP environment, and although the package will be scheduled to run nightly when the application sees little to no use, I want to be sure that the process isn't impacting any application functions.

Thanks for the advice!

Rocco

An OLE-DB source will impose the same locks as if you were running the SELECT satement yourself from any other tool, so normaly you would expect some shared locks to allow you to consistently read the data requested.

Using Profiler will show you details of the SQL statement, and you can also choose to show locks details in profier, just filter for the SSIS connection/machine/user/application to focus on SSIS generated information as opposed to regular application traffic.