Could not get the column information from the OLE DB provider IBMDA400

Hello

I'm trying to get data from as400 server to sql server, trough a job.
The job executes a stored procedure that retrieves data from a view in an other database on the same server. That view gets the data from as400.
Now, when i run the mentioned stored procedure in the query analyzer, everything works fine, but when the stored procedure is executed from the scheduled job, it fails each time giving the error

"Could not get the column information from the OLE DB provider 'IBMDA400'. [SQLSTATE 42000] (Error 7350). The step failed."

on random points during that process (by that i mean the lenght of time it runs)

does anyone know what the diffrence is between executing an sp in query analyzer or using a job, keeping in mind retrieving data from as400?

thanksSQLAgent service account should have the same permissions as yours. If you're piping data from AS/400 using linked server, make sure mapping entry exists for SQLAgent account as well.

Could not find server

I have linked a server withinh the "SQL Server Management Studio
Express"
I also created a database where I created a view using the openquiery
statement.
The view runs perfectly within the "SQL Server Management Studio
Express"
I created a new project with "Microsoft Visual Studio 2005"
I created a shared data source to the database where I created the
views.
The data source is OK because I can see the cretaed views on the Query
Builder.
Once I created the query on the Query Builder and I try to run it, the
following message appears:
"SQL Execution Error
Error Source: .NetSqlClient Data Provider
Error Message: Could not find server 'xxx' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
Could not use view or function 'xxx' because of binding errors"
I have looked for server 'xxx' in sysservers and it is there, so I do
not understand where the error originates.
Any help would be most appreciated.
Abraham Hirsch
Mexico CityAbraham,
Is the working connection local and the non-working connection remote?
If so, open SQL Server Configuration Manager and adjust connection
protocols for SQL Express.
Andrew Watt MVP
On 9 Jun 2006 11:25:03 -0700, "Eibi" <ahirschp@.gmail.com> wrote:
>I have linked a server withinh the "SQL Server Management Studio
>Express"
>I also created a database where I created a view using the openquiery
>statement.
>The view runs perfectly within the "SQL Server Management Studio
>Express"
>I created a new project with "Microsoft Visual Studio 2005"
>I created a shared data source to the database where I created the
>views.
>The data source is OK because I can see the cretaed views on the Query
>Builder.
>Once I created the query on the Query Builder and I try to run it, the
>following message appears:
>"SQL Execution Error
>Error Source: .NetSqlClient Data Provider
>Error Message: Could not find server 'xxx' in sysservers. Execute
>sp_addlinkedserver to add the server to sysservers.
>Could not use view or function 'xxx' because of binding errors"
>I have looked for server 'xxx' in sysservers and it is there, so I do
>not understand where the error originates.
>Any help would be most appreciated.
>Abraham Hirsch
>Mexico City|||Both connections are local!
Andrew Watt [MVP] wrote:
> Abraham,
> Is the working connection local and the non-working connection remote?
> If so, open SQL Server Configuration Manager and adjust connection
> protocols for SQL Express.
> Andrew Watt MVP
> On 9 Jun 2006 11:25:03 -0700, "Eibi" <ahirschp@.gmail.com> wrote:
> >I have linked a server withinh the "SQL Server Management Studio
> >Express"
> >I also created a database where I created a view using the openquiery
> >statement.
> >
> >The view runs perfectly within the "SQL Server Management Studio
> >Express"
> >
> >I created a new project with "Microsoft Visual Studio 2005"
> >I created a shared data source to the database where I created the
> >views.
> >
> >The data source is OK because I can see the cretaed views on the Query
> >Builder.
> >
> >Once I created the query on the Query Builder and I try to run it, the
> >following message appears:
> >"SQL Execution Error
> >Error Source: .NetSqlClient Data Provider
> >Error Message: Could not find server 'xxx' in sysservers. Execute
> >sp_addlinkedserver to add the server to sysservers.
> >Could not use view or function 'xxx' because of binding errors"
> >
> >I have looked for server 'xxx' in sysservers and it is there, so I do
> >not understand where the error originates.
> >
> >Any help would be most appreciated.
> >
> >Abraham Hirsch
> >Mexico City

Could not fetch a row using a bookmark from OLE DB provider 'VFPOLEDB'.

I'm trying to update a VFP table via a linked server in SQL 2000, I'm able to use "openquery" to view the records from the table, but when I try the following command:

Update M2MEDI...x830rels

Set favailship = 100

I get the following error

[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IRowsetLocate::GetRowsByBookmark returned 0x80040e21: 01000000].

Msg 7333, Level 16, State 2, Line 1

Could not fetch a row using a bookmark from OLE DB provider 'VFPOLEDB'.

How do I fix this

Thanks

David Davis

For the linked tables use the four-part naming and make sure that the 'Distributed Transaction Coordinator' service is running on your server.

HTH

|||

Isn't this

M2MEDI...x830rels

the 4 part naming convention

David Davis

|||Looks like you have three dots, not two

Could not allocate new page for database TEMPDB.

I tried to create a new table in VIEW to accomplish some urgent work and I have received the following message:

Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

It should be recognized that the MS SQL Server performance is slower than before. What files the TEMPDB contains, which need to be cleared from time to time, and how can I solve this above mentioned matter.
Thank you.Hi,

execute following statment;
use tempdb
go
sp_helpfile

If growth is set to 0 (Zero), your tempdb can't allocate more space. See books online to read how to change growth and how to allocate.

/Mada|||Thank you !!
I will try to use it and I will search for books online to have the solution.

Best regards.

cost of view

hi all,

If I make a view on big big table in Join with a small table, and If I rarely make a SELECT over this How muct does it cost the overhead on DB for maintain this view ?

Thanks

I hope to explained my doubt.

Bye Bye

Hid

Having a view on big table (using specific filters) is not bad idea. You can utilize add the indexes on view to fine tune your query. The indexed view will be act as de-normalized table..|||

Unless your View is indexed (i.e. materialised) then there is no/extremely minimal overhead associated with the View when it is not being used in queries.

Chris

|||

Thanks for reply!

Bye Bye

Hid

Corrupted view

Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand

Corrupted view

Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
Ferdinand
Ferdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand

Corrupted view

Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand

Corrupted Table removal problem

I have a table that I can no longer make design changes too, remove
altogether, or view all rows for. Enterprise manager just hangs. I can do
a view top 1 record, but not view all. I'm testing some stored procedures
to fill ntext fields using writetext and seem to be corrupting it somehow.
All other tables and database work fine ... problems occur here only.
Ideas on how to fix or move forward with this.
Thanks,
Andrew
Your best bet is usually to restore from a previous good backup. If this is
a table in which the data is not critical or can be rebuilt you might want
to take a look at DBCC CHECKTABLE in BooksOnLine.
Andrew J. Kelly SQL MVP
"Andrew Jones" <ajones@.kvmhc.org> wrote in message
news:10fdjev1ikfg8ac@.corp.supernews.com...
> I have a table that I can no longer make design changes too, remove
> altogether, or view all rows for. Enterprise manager just hangs. I can
do
> a view top 1 record, but not view all. I'm testing some stored procedures
> to fill ntext fields using writetext and seem to be corrupting it somehow.
> All other tables and database work fine ... problems occur here only.
> Ideas on how to fix or move forward with this.
> Thanks,
> Andrew
>

Corrupted indexed view

Windows Server 2003
SQL Server 2000 Enterprise
One of our indexed views is periodically getting corrupted. When it
occurs, one of the aggregate money fields returns an incorrect value.
Rebuilding the view corrects the problem.
This is a financial system and these incorrect money amounts are
causing major problems. But removing the indexed view causes certain
summation functions of the system to be unacceptably slow.
How do I fix?
You should probably contact PSS to get tot he root of the problem.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
<thegap2@.excite.com> wrote in message
news:1129901119.648845.24860@.o13g2000cwo.googlegro ups.com...
> Windows Server 2003
> SQL Server 2000 Enterprise
> One of our indexed views is periodically getting corrupted. When it
> occurs, one of the aggregate money fields returns an incorrect value.
> Rebuilding the view corrects the problem.
> This is a financial system and these incorrect money amounts are
> causing major problems. But removing the indexed view causes certain
> summation functions of the system to be unacceptably slow.
> How do I fix?
>

Corrupted indexed view

Windows Server 2003
SQL Server 2000 Enterprise
One of our indexed views is periodically getting corrupted. When it
occurs, one of the aggregate money fields returns an incorrect value.
Rebuilding the view corrects the problem.
This is a financial system and these incorrect money amounts are
causing major problems. But removing the indexed view causes certain
summation functions of the system to be unacceptably slow.
How do I fix?You should probably contact PSS to get tot he root of the problem.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
<thegap2@.excite.com> wrote in message
news:1129901119.648845.24860@.o13g2000cwo.googlegroups.com...
> Windows Server 2003
> SQL Server 2000 Enterprise
> One of our indexed views is periodically getting corrupted. When it
> occurs, one of the aggregate money fields returns an incorrect value.
> Rebuilding the view corrects the problem.
> This is a financial system and these incorrect money amounts are
> causing major problems. But removing the indexed view causes certain
> summation functions of the system to be unacceptably slow.
> How do I fix?
>

Corrupted indexed view

Windows Server 2003
SQL Server 2000 Enterprise
One of our indexed views is periodically getting corrupted. When it
occurs, one of the aggregate money fields returns an incorrect value.
Rebuilding the view corrects the problem.
This is a financial system and these incorrect money amounts are
causing major problems. But removing the indexed view causes certain
summation functions of the system to be unacceptably slow.
How do I fix?You should probably contact PSS to get tot he root of the problem.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly SQL MVP
<thegap2@.excite.com> wrote in message
news:1129901119.648845.24860@.o13g2000cwo.googlegroups.com...
> Windows Server 2003
> SQL Server 2000 Enterprise
> One of our indexed views is periodically getting corrupted. When it
> occurs, one of the aggregate money fields returns an incorrect value.
> Rebuilding the view corrects the problem.
> This is a financial system and these incorrect money amounts are
> causing major problems. But removing the indexed view causes certain
> summation functions of the system to be unacceptably slow.
> How do I fix?
>