DW using stored procedure updates has problems when using ole-db

I'm still suffering in trying to use OLE-Db for my
application rather than MSSQL driver.

here is my Stored Proc parameter definition
ALTER       PROCEDURE usp_vtk_program_insert
@program_id int OUTPUT,
@program_grp_id int,
@program_name varchar(50),
@program_type_id int,
@manufacturer_id int,
@start_date datetime,
@end_date datetime,
@locked_ind int,
@active_ind int,
@contact_fname varchar(50)= NULL,
@contact_lname varchar(50) =NULL,
@contact_address1 varchar(50) =NULL,
@contact_address2 varchar(50) =NULL,
@contact_city varchar(50) =NULL,
@contact_state varchar(2) =NULL,
@contact_zip varchar(10) =NULL,
@contact_email varchar(500) =NULL,
@contact_phone numeric(10, 0) =NULL,
@contact_fax numeric(10, 0) =NULL,
@exception	varchar(255) = null


within the dw I use the stored procedure update method and
assign the above SP to the INSERT tab.

Mind you this DW and Procedure have been working for 2 years
using the MSSQL driver, but now I get the following error
when I try to insert a new record using the DW.

A database error has occurred

Database error code: 999

Database error message:
SQLSTATE = 22018
Microsoft OLE DB Provider for SQL Server
Invalid character value for cast specification.

No changes made to database

execute dbo.usp_vtk_program_insert; 1

Note: when I got the error the SQL Profiler did not register
the call the procedure meaning that the error is coming from
the ole-db driver itself.

Within the DW painter I started associating the procedure's
arguments to column "unused" to see if I can get pass the
ole-db driver and get a hit within the SQL profiler.

The fields that the driver were having was the start_date
and end_date.

Within dw painter, and view the column specification the
fields' datatype were DATETIME, just for kicks I used the
EDIT SOURCE to change the datatype to DATE, and tried again.

The insert worked this time.

Here is what I don't get, the DW was created while connected
using MSSQL driver and after entering the SQL select
statement came back as datetime datatype for those fields.

When I created a new DW while connected using the OLE-DB and
after entering the select statement the datatype still comes
back as datetime.

So why does datetime fail, but date work, and why do both
drivers come back with datetime as the proper datatype?

Aren't date fields in MSSQl Server all datetime?
0
cfauvel
12/9/2003 11:51:02 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

2 Replies
742 Views

Similar Articles

[PageSpeed] 4

I figured out my problem...apparently OLE-DB is less
forgiving than MSSQLs driver.

In my application I had the following code when a user
entered a new row.

 If ll_rtc =1 THEN
   dw_1.Object.start_date[1] = &
        date(string(year(today()))+'-01-01' )
   dw_1.object.end_date[1] = &
        date(string(year(today()))+'-12-31' )
 END IF

I thought of coming here when I noticed that when I tabbed
to the start_date field the field would change from the
default value of 01/01/2003 to 00/00/0000 and then back to
01/01/2003 when I tabbed out to end_date.

Figuring the datatype of the field in the database is a
datetime I changed the code to:
 If ll_rtc =1 THEN
   dw_1.Object.start_date[1] = &
        datetime(date(string(year(today()))+'-01-01' ))
   dw_1.object.end_date[1] = &
        datetime(date(string(year(today()))+'-12-31' ))
 END IF

That worked using the OLE-DB driver.

Interesting that the original code worked fine for 2 years
while using the MSSQl driver.

So the original error was indeed correct...

> I'm still suffering in trying to use OLE-Db for my
> application rather than MSSQL driver.
>
> here is my Stored Proc parameter definition
> ALTER       PROCEDURE usp_vtk_program_insert
> @program_id int OUTPUT,
> @program_grp_id int,
> @program_name varchar(50),
> @program_type_id int,
> @manufacturer_id int,
> @start_date datetime,
> @end_date datetime,
> @locked_ind int,
> @active_ind int,
> @contact_fname varchar(50)= NULL,
> @contact_lname varchar(50) =NULL,
> @contact_address1 varchar(50) =NULL,
> @contact_address2 varchar(50) =NULL,
> @contact_city varchar(50) =NULL,
> @contact_state varchar(2) =NULL,
> @contact_zip varchar(10) =NULL,
> @contact_email varchar(500) =NULL,
> @contact_phone numeric(10, 0) =NULL,
> @contact_fax numeric(10, 0) =NULL,
> @exception    varchar(255) = null
>
>
> within the dw I use the stored procedure update method and
> assign the above SP to the INSERT tab.
>
> Mind you this DW and Procedure have been working for 2
> years using the MSSQL driver, but now I get the following
> error when I try to insert a new record using the DW.
>
> A database error has occurred
>
> Database error code: 999
>
> Database error message:
> SQLSTATE = 22018
> Microsoft OLE DB Provider for SQL Server
> Invalid character value for cast specification.
>
> No changes made to database
>
> execute dbo.usp_vtk_program_insert; 1
>
> Note: when I got the error the SQL Profiler did not
> register the call the procedure meaning that the error is
> coming from the ole-db driver itself.
>
> Within the DW painter I started associating the
> procedure's arguments to column "unused" to see if I can
> get pass the ole-db driver and get a hit within the SQL
> profiler.
>
> The fields that the driver were having was the start_date
> and end_date.
>
> Within dw painter, and view the column specification the
> fields' datatype were DATETIME, just for kicks I used the
> EDIT SOURCE to change the datatype to DATE, and tried
> again.
>
> The insert worked this time.
>
> Here is what I don't get, the DW was created while
> connected using MSSQL driver and after entering the SQL
> select statement came back as datetime datatype for those
> fields.
>
> When I created a new DW while connected using the OLE-DB
> and after entering the select statement the datatype still
> comes back as datetime.
>
> So why does datetime fail, but date work, and why do both
> drivers come back with datetime as the proper datatype?
>
> Aren't date fields in MSSQl Server all datetime?
0
cfauvel
12/17/2003 10:12:32 PM
I have a feeling that this may be an issue with the specific version of PB
that you are using.  Manipulating the data shouldn't rely on the interface.
The code you supplied works correctly in PB 9 6533 (9.01) with an OLEDB
connection (note that my test involved a column using an editmask of
mm/dd/yy).

<cfauvel> wrote in message news:3fe0d4d0.1b4f.846930886@sybase.com...
> I figured out my problem...apparently OLE-DB is less
> forgiving than MSSQLs driver.
>
> In my application I had the following code when a user
> entered a new row.
>
>  If ll_rtc =1 THEN
>    dw_1.Object.start_date[1] = &
>         date(string(year(today()))+'-01-01' )
>    dw_1.object.end_date[1] = &
>         date(string(year(today()))+'-12-31' )
>  END IF
>
> I thought of coming here when I noticed that when I tabbed
> to the start_date field the field would change from the
> default value of 01/01/2003 to 00/00/0000 and then back to
> 01/01/2003 when I tabbed out to end_date.
>
> Figuring the datatype of the field in the database is a
> datetime I changed the code to:
>  If ll_rtc =1 THEN
>    dw_1.Object.start_date[1] = &
>         datetime(date(string(year(today()))+'-01-01' ))
>    dw_1.object.end_date[1] = &
>         datetime(date(string(year(today()))+'-12-31' ))
>  END IF
>
> That worked using the OLE-DB driver.
>
> Interesting that the original code worked fine for 2 years
> while using the MSSQl driver.
>
> So the original error was indeed correct...
>
> > I'm still suffering in trying to use OLE-Db for my
> > application rather than MSSQL driver.
> >
> > here is my Stored Proc parameter definition
> > ALTER       PROCEDURE usp_vtk_program_insert
> > @program_id int OUTPUT,
> > @program_grp_id int,
> > @program_name varchar(50),
> > @program_type_id int,
> > @manufacturer_id int,
> > @start_date datetime,
> > @end_date datetime,
> > @locked_ind int,
> > @active_ind int,
> > @contact_fname varchar(50)= NULL,
> > @contact_lname varchar(50) =NULL,
> > @contact_address1 varchar(50) =NULL,
> > @contact_address2 varchar(50) =NULL,
> > @contact_city varchar(50) =NULL,
> > @contact_state varchar(2) =NULL,
> > @contact_zip varchar(10) =NULL,
> > @contact_email varchar(500) =NULL,
> > @contact_phone numeric(10, 0) =NULL,
> > @contact_fax numeric(10, 0) =NULL,
> > @exception    varchar(255) = null
> >
> >
> > within the dw I use the stored procedure update method and
> > assign the above SP to the INSERT tab.
> >
> > Mind you this DW and Procedure have been working for 2
> > years using the MSSQL driver, but now I get the following
> > error when I try to insert a new record using the DW.
> >
> > A database error has occurred
> >
> > Database error code: 999
> >
> > Database error message:
> > SQLSTATE = 22018
> > Microsoft OLE DB Provider for SQL Server
> > Invalid character value for cast specification.
> >
> > No changes made to database
> >
> > execute dbo.usp_vtk_program_insert; 1
> >
> > Note: when I got the error the SQL Profiler did not
> > register the call the procedure meaning that the error is
> > coming from the ole-db driver itself.
> >
> > Within the DW painter I started associating the
> > procedure's arguments to column "unused" to see if I can
> > get pass the ole-db driver and get a hit within the SQL
> > profiler.
> >
> > The fields that the driver were having was the start_date
> > and end_date.
> >
> > Within dw painter, and view the column specification the
> > fields' datatype were DATETIME, just for kicks I used the
> > EDIT SOURCE to change the datatype to DATE, and tried
> > again.
> >
> > The insert worked this time.
> >
> > Here is what I don't get, the DW was created while
> > connected using MSSQL driver and after entering the SQL
> > select statement came back as datetime datatype for those
> > fields.
> >
> > When I created a new DW while connected using the OLE-DB
> > and after entering the select statement the datatype still
> > comes back as datetime.
> >
> > So why does datetime fail, but date work, and why do both
> > drivers come back with datetime as the proper datatype?
> >
> > Aren't date fields in MSSQl Server all datetime?


0
Scott
12/18/2003 1:55:12 PM
Reply:

Similar Artilces:

stored procedure problem using ole db
We are testing changing our application from using dblib to oledb. The following powerbuilder code is producing an error. Any ideas on how to fix it? PB 9.01 7096 DECLARE proc_chargefirstlive procedure for @ll_error = sp_charge_golive_firsttime :al_PromotionId USING SQLCA; Recieves the following error: SQLSTATE = 42000 Microsoft OLE DB Provider for SQL Server Must delcare the variable '@ll_error'. Thanks, Gaye Your best bet is to convert these calls to RPCFuncs, assuming you are only getting a return value and/or output args and NOT retrieving a result set. ...

Problem with sqlpreview with dw using stored procedure update in PB7
pb7 documentation states that returning a value of 2 in sqlpreview event of a datawindow, skips the executing of the current sql statement. however, it seems that this is ignored if your datawindow uses stored procedure update. the stored procedure gets executed regardless of the return value. can anyone please help me how should i correct this? thanks in advance. > pb7 documentation states that returning a value of 2 in > sqlpreview event of a datawindow, skips the executing of > the current sql statement. however, it seems that this is > ignored if your datawindow u...

Web ASP.NET
Hi all, I have created a simple web app and tried to understand LINQ. And I have problem with UPDATE operation (I can do this opertion with LINQ + Store procedure, but can not do it without store procedure) In database, I have 2 tables. Category, Item (one Category has many Item). I use wizard to create LINQ dbml, and I have DataContext named "FlowerShopDataContext" (The website I create is to manage flowers (Item) by catefory). Item has one relationship with Category only. Therefore, We do not need to worry about the case in which Item object contains child entity.(But with Cat...

SQL OLE DB vs DW using a stored procedure as a data source
I have a datawindow that uses a sql store procedure as the source. It works fine using native db interface, although it does not work if I use SQL OLE DB interface. Does anyone know why. I'm using PB 7 AND MS SQL sERVER 2000 Does the application generate any error message? Could you please post the error details? I have DWs that use Stored Procs as a source that work with OLE DB with SQL Server 2000, but in PB9, not PB7. I unfortunately do not have PB7 running now to test it with. Sanjiv. <RFALAGAN> wrote in message news:41e4572e.7b6e.1681692777@sybase.com....

Problem using Stored Procedure as source in PB 8/MSSQL 2000/OLE DB...
Hi, I'm using PB 8.0.1/MS SQL Server 2000 & use OLE DB to connect to the SQL server. Everything works fine except when I use the stored procedure as source for the datawindow. The details are as follows. Problem 1: If I try to create a new datawindow with a stored procedure with arguments as source, I get the following error. --------------------------- Cannot create DataWindow. 'SQLSTATE = 42000 Microsoft OLE DB Provider for SQL Server Procedure '<procname>' expects parameter '@param1', which was not supplied. 1 execute dbo.prom_s...

Problems using Stored Procedures for updates
Hi I'm having a problem when trying to issue an UpdateData method. The datawindow is retrieved fine via a SP. If you update a value and issue an UpdateData then the following error is generated System.NullReferenceException: Object reference not set to an instance of an object at ..... I've defined a Stored Procedure Update entry for the Update statement, so I'm now at a bit of a loss !! Jon ...

DW update using stored procedure
I am little new to stored procedure but.. here's my question: I want to send multiple rows for update using a stored procedure. But I want all rows to be updated as one logical unit of work. For example, I may want all of the following as one transaction: 1. Delete row #1 2. Update row # 2. 3. Insert a new row. What do I pass to the stored procedure? Do I have to call the stored procedure 3 times in above example? If I call it 3 times, how do I tell the sp when to commit? TIA Jennifer Peters So the commits and rollbacks are done from PB and not by stored pr...

How to use three update statement using one store procedure
 Hi, I am trying to execute three update statment using one store procedure here is my code.        Code Behind:        Dim com As New SqlCommand("updatelist", con)        com.CommandType = CommandType.StoredProcedure        Dim param As New SqlParameter("@pknew", SqlDbType.Int)        param.Value = newi        Dim param1 As New SqlParameter("@pkold&q...

A question about using stored procedure to update database?
A stored procedure was created to update a database. But, I don't know how to trigger it in code: This is the example to create a dataset for Dropdownlist, Dim da As New SqlDataAdapter(myCommand) Dim ds AS DataSet = new DataSet() da.Fill(ds) This way doesn't work for triggering the stored procedure for updating database How to trigger the stored Procedure for updating the database in the code?   Thx. You need to create a SqlCommand object - in the properties for this you can set it to be a stored procedure and set the text to be something like [dbo].sprocna...

Update DW using stored procedure in PB7
Has anyone tried this? I'm running PB7.0.2 and Oracle 8.0.5. I've assigned a stored procedure for update. PB sends the stored procedure as "EXECUTE SCHEMA.UPDATE_TABLE;0", which gives an error saying "unidentified symbol 0". Please let me know if you've got it working. Regards ...

Update DW using stored procedure in PB7
Has anyone tried this? I'm running PB7.0.2 and Oracle 8.0.5. I've assigned a stored procedure for update. PB sends the stored procedure as "EXECUTE SCHEMA.UPDATE_TABLE;0", which gives an error saying "unidentified symbol 0". Please let me know if you've got it working. Regards ...

Problems with db-library and ODBC using with MS SQL Server ver 7.0 PowerBuilder ver 6.5. And OLE-DB for Microsoft OLAP Services
There are many problems when running the aplication using the new ODBC driver for SQL ver 7. Simple, does not work. It�s any patch to this problem ? Using DB Library makes poor query plans in some case using SQL Server 7.0. It�s any path ? It�s any patch or utility to use OLE-DB for connecting Microsoft OLAP Services ? Carlos Lara darsa@solnet.com.mx ...

Using pfc_Save Event to Update a DataWindow using Stored Procedures (Re-Post)
I'm re-posting this question if hopes of getting a qualified response: I'm using a datawindow utilizing a stored procedure as its' datasource. The datawindow also uses stored procedures for Insert, Update, & Delete. I use the pfc_Save event of the Parent window to update all SUO's on that window. Eventually, the of_Update( ) function of the datawindow control, inherited from u_dw, will be executed. I've read that the datawindow control knows which operation to perform, Update or Insert, based on the ItemStatus of the row or column in the datawindow. For som...

Problem Updating the Database from a Gridview using a Stored Proc.
 Hi all, I have a gridview displaying data from a database. The data comes from a Sql 2005 express database table - via a Stored Proc which is bound to a Dataset. The dataset in turn is connected to the Gridview with "Editable" enabled. All of this works fine - so I added another StoredProc which will update the table. I connect it to the Dataset and then bind that to the Gridview and all is fine until you Click "Save" in the GridView. Somehow the functions generated always has the wrong parameters for the Update() method. Does anyone know of / have a demo project w...

Web resources about - DW using stored procedure updates has problems when using ole-db - sybase.powerbuilder.database

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'

Robust Growth In Procedures, Beneficial Product Mix Drive Intuitive Surgical's Strong Q3 Performance
The company’s non-GAAP gross margin improved by over 2 percentage points year-on-year due to higher average selling prices, cost savings, and ...

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 12/7/2015 10:20:21 PM