Deafult NULL not working

I am using SQL Server 2000. I have a Column with DataType int and default value specified as (null). But, With Insert or Update if the column value is Blank, 0 is getting inserted instead of the desired NULL.

Thanks

Is there, by chance, a trigger on this table?|||

NO. There is no trigger on this Table.

|||Well... create a complete DDL script for this table and post it here - something must be there.

Also, how you make a insert / update - directly or via some kind of stored proc? There may be a preprocessing in there that you miss, for example.|||

What exactly do you mean by "column value is blank". If you are explicitly trying to force a blank into the field then yes, it is going to get assigned as zero:

create table dbo.testo
( rid int,
x int default (null)
)

insert into dbo.testo select 1, ' '

insert into dbo.testo (rid) select 1

select * from dbo.testo

/*
rid x
-- --
1 0
1 NULL
*/

If, however, you are wanting to insert a row and allow the default to occur you must do something similar to what I hilighted in red

If you want to UPDATE to the default value, you can use syntax something like this:

update dbo.testO
set x=default
where rid =1

|||

YES. The Column Value is getting evaluated to '' as the user did not enter anything for the field on the form. Is there any way '' can be evaluated to NULL instead of 0.

- vmrao

|||declare @.p1 varchar(255)

set @.p1 = ''

insert into Mytable (rid, myintcol)
select 10, nullif(@.p1, '')
|||Thanks. NULLIF worked.

deadlocking

Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>sql

deadlocking

Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
Richard
The only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:

> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>
|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>

deadlocking

Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:

> Recently we ran a script that added a new column to a table with 120 milli
on
> rows of data. A large table with a lot of wide columns. The script was r
an
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me tha
t
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>

Deadlock problem? 3 way conditional split of data from one table to another never completes

I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.

Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.

Aren't you using a union all transformation before the destination to bring your streams back together?|||

If your situation allows it simply uncheck the destination table option "Table Lock" and it will work.

Philippe

|||That was it! Thanks.|||Did not try that. Is that the recommeded technique to use in this situation?|||

Great, just make sure that the union all task is not better appropriate.

I use the do not lock table option only on tables that I kow for sure no other process is trying to update and or insert into.

And I do this at a time of night when nothing is accessing the table. Preferably against a staging table that will replace the production table using either sp_rename or things like that.

Philippe

|||

Jeff-B wrote:

Did not try that. Is that the recommeded technique to use in this situation?

If you were previously using separate destination connectors for the same table, then yes, that would be the recommended technique.

|||

I'd agree with Phil and Philippe - a UNION ALL component is the better way to go. It will be more performant too because there is only one insertion operation.

-Jamie

|||Would this still be the case if you were using different derived fields or different source table fields for each source to populate the fields of the target table. Does the UNION ALL allow for mapping of each source to the target or does each source to the UNION ALL have to have the same set of fields?|||You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.|||

Phil Brammer wrote:

You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.

I also want to clarify that if your different data flows were going to the same physical table, then yes, a union all transformation is what you want. It'll work, trust me! Come back here if you have issues with it.|||Thanks Phil. I think I understand how to use this feature now. I'll experiment and see if I achieve the same result with the 4 independent paths to the same table.

DDL Trigger

SQL 2005
I have a table that is referenced by a view. Whenever a column is added or
dropped from a table I want to update the view to include or remove the
column - so I thought, 'DDL Trigger!'
I constructed my trigger and immediately found that the Alter Table is not
committed at the time the trigger is run - the trigger is in the same
transaction as the Alter Table - so the column does not exist yet.
What I am looking for is an AFTER DDL Trigger. Until then I guess I am off
to figure out Notification Services.In this case, a DDL trigger -- in my mind at least -- is for logging and/or
rolling back the change to the table.
What were you expecting to do to the table directly while in the scope of
the trigger?
"Joe L" <jjj@.lll.com> wrote in message
news:etW5zLJOGHA.3888@.TK2MSFTNGP12.phx.gbl...
> SQL 2005
> I have a table that is referenced by a view. Whenever a column is added
> or dropped from a table I want to update the view to include or remove the
> column - so I thought, 'DDL Trigger!'
> I constructed my trigger and immediately found that the Alter Table is not
> committed at the time the trigger is run - the trigger is in the same
> transaction as the Alter Table - so the column does not exist yet.
> What I am looking for is an AFTER DDL Trigger. Until then I guess I am
> off to figure out Notification Services.
>
>|||I was going to dynamically update my view to include/remove the column that
was just added or dropped.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:udXODOJOGHA.3276@.TK2MSFTNGP09.phx.gbl...
> In this case, a DDL trigger -- in my mind at least -- is for logging
> and/or rolling back the change to the table.
> What were you expecting to do to the table directly while in the scope of
> the trigger?
>
>
> "Joe L" <jjj@.lll.com> wrote in message
> news:etW5zLJOGHA.3888@.TK2MSFTNGP12.phx.gbl...
>

DDL Primer?

Ent Manager is nice, but I need to make some DDL changes to a database
(convert a column from char to varchar, add a new column, etc) with
SQL. Is there a good primer for this? I'm sure it is not too hard, I
just have not had to do this. Thanks.
-JohnElementary cases are well presented in Books Online.
E.g. (to alter a table and/or columns):
http://msdn.microsoft.com/library/d...br />
3ied.asp
ML
http://milambda.blogspot.com/|||...and use Query Analyzer. I forgot to mention.
ML
http://milambda.blogspot.com/|||"John Baima" <john@.nospam.com> wrote in message
news:f17gq1l0e6cted806ef0ub0ab3tflqmlt4@.
4ax.com...
> Ent Manager is nice, but I need to make some DDL changes to a database
> (convert a column from char to varchar, add a new column, etc) with
> SQL. Is there a good primer for this? I'm sure it is not too hard, I
> just have not had to do this. Thanks.
> -John
EM is "nice" but does a lot of "unneccessary" stuff behind the scenes.
When you change a column from Char to Varchar, it actually:
-creates a new table with the new DDL
-copies the data from the original to the new table
-drops the old table
-renames the new table
-drops and recreates constaints
So do you your charges in Query Analyser, like ML suggests.|||Plus - QA won't "assist" you in "clicking up" a potential disaster. :)
ML
http://milambda.blogspot.com/

DDL on Huge Table with "text" Column

Hi,
I need to add a column to a SQL 2000 table that has a "text" column, say
Foo, and the table has gigabytes of data. I need to insert a column in the
table. The standard Enterprise Manager change script involves 1) creating a
tmp_Foo table with the new column, 2) copying the existing data from Foo to
tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
However, I know that the row data for a text field is just a pointer to the
head record for the blob data. Is there any way when I copy the data from
Foo to tmp_Foo that for my text data I could just copy the pointer and not
the actual data? That would make the change script run much faster and not
require us to open a maintenance window because the one script is going to
take half an hour to duplicate the blob data.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgDon't use the EM change script.
Use the ALTER TABLE command instead.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:uGz%238V$lHHA.4768@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I need to add a column to a SQL 2000 table that has a "text" column, say
> Foo, and the table has gigabytes of data. I need to insert a column in
> the table. The standard Enterprise Manager change script involves 1)
> creating a tmp_Foo table with the new column, 2) copying the existing data
> from Foo to tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
> However, I know that the row data for a text field is just a pointer to
> the head record for the blob data. Is there any way when I copy the data
> from Foo to tmp_Foo that for my text data I could just copy the pointer
> and not the actual data? That would make the change script run much
> faster and not require us to open a maintenance window because the one
> script is going to take half an hour to duplicate the blob data.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

DDL on Huge Table with "text" Column

Hi,
I need to add a column to a SQL 2000 table that has a "text" column, say
Foo, and the table has gigabytes of data. I need to insert a column in the
table. The standard Enterprise Manager change script involves 1) creating a
tmp_Foo table with the new column, 2) copying the existing data from Foo to
tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
However, I know that the row data for a text field is just a pointer to the
head record for the blob data. Is there any way when I copy the data from
Foo to tmp_Foo that for my text data I could just copy the pointer and not
the actual data? That would make the change script run much faster and not
require us to open a maintenance window because the one script is going to
take half an hour to duplicate the blob data.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
Don't use the EM change script.
Use the ALTER TABLE command instead.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:uGz%238V$lHHA.4768@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I need to add a column to a SQL 2000 table that has a "text" column, say
> Foo, and the table has gigabytes of data. I need to insert a column in
> the table. The standard Enterprise Manager change script involves 1)
> creating a tmp_Foo table with the new column, 2) copying the existing data
> from Foo to tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
> However, I know that the row data for a text field is just a pointer to
> the head record for the blob data. Is there any way when I copy the data
> from Foo to tmp_Foo that for my text data I could just copy the pointer
> and not the actual data? That would make the change script run much
> faster and not require us to open a maintenance window because the one
> script is going to take half an hour to duplicate the blob data.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

DDL on Huge Table with "text" Column

Hi,
I need to add a column to a SQL 2000 table that has a "text" column, say
Foo, and the table has gigabytes of data. I need to insert a column in the
table. The standard Enterprise Manager change script involves 1) creating a
tmp_Foo table with the new column, 2) copying the existing data from Foo to
tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
However, I know that the row data for a text field is just a pointer to the
head record for the blob data. Is there any way when I copy the data from
Foo to tmp_Foo that for my text data I could just copy the pointer and not
the actual data? That would make the change script run much faster and not
require us to open a maintenance window because the one script is going to
take half an hour to duplicate the blob data.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgDon't use the EM change script.
Use the ALTER TABLE command instead.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:uGz%238V$lHHA.4768@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I need to add a column to a SQL 2000 table that has a "text" column, say
> Foo, and the table has gigabytes of data. I need to insert a column in
> the table. The standard Enterprise Manager change script involves 1)
> creating a tmp_Foo table with the new column, 2) copying the existing data
> from Foo to tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
> However, I know that the row data for a text field is just a pointer to
> the head record for the blob data. Is there any way when I copy the data
> from Foo to tmp_Foo that for my text data I could just copy the pointer
> and not the actual data? That would make the change script run much
> faster and not require us to open a maintenance window because the one
> script is going to take half an hour to duplicate the blob data.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

DDL Changes breaks cubes

Hi,

Lately I had to increase the size of a table varchar column.

This column is used in a cube dimension attribute.

After the change, cube processing ended-up in error.

I had to first refresh the dataview and then go manually change the KeyColumns Datasize property to increase it as well.

Is there a better way? I find this very dangerous.
As a minimum, a dependency check should include cubes using that metadata.

I notticed the same type of problems on the SSIS sides, metadata becomes out of synch. This is not a show stopper but these are missing link in the dependency food chain.

Sub-systems are not integrated, they are in a vacuum. A little less than with 2000 but still in a vacuum.

Philippe

Very valuable observation.

This is something we are going to take a look at for the next version :)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

See this: http://support.microsoft.com/kb/251238/

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||

Thaks that′s the solution I was traying for two hours really thanks!!!!

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||

Thaks that′s the solution I was traying for two hours really thanks!!!!

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||

Thaks that′s the solution I was traying for two hours really thanks!!!!

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||Thaks that′s the solution I was traying for two hours really thanks!!!!

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||Thaks that′s the solution I was traying for two hours really thanks!!!!

dbo with user name in "users"

Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and in
the login name column a name like "webapp". In other databases, I see "dbo"
in the name column and nothing in the login name column.
What is the significance of having another user like "webapp" in the login
name column? And how could can I assign another user login to dbo?
Thanks,
--
Dan D.Dan
DBO is a priviliged user. There are difference between LOGIN and USER.
BOL has pertty good article about it
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||I've read BOL but I don't see anything that explains how a user ends up in
the "login name" column for "dbo" in the "users" part of EM. Sometimes "sa"
is in the "login name" column for "dbo", sometimes the "login name" column
for "dbo" is empty and sometimes, there is another user name in the "login
name" column for "dbo".
--
Dan D.
"Uri Dimant" wrote:

> Dan
> DBO is a priviliged user. There are difference between LOGIN and USER.
> BOL has pertty good article about it
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
You can add user to db_owner database fixed role and all objects will be
created as dbo.tablename ( If he/she is a member of sysadmin server role)
If he/she isn't you needs to specify
create table dbo.table (col1 int)
Waht does that mean "sometimes"? Can you reproduce the problem?
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...[vbcol=seagreen]
> I've read BOL but I don't see anything that explains how a user ends up in
> the "login name" column for "dbo" in the "users" part of EM. Sometimes
> "sa"
> is in the "login name" column for "dbo", sometimes the "login name" column
> for "dbo" is empty and sometimes, there is another user name in the "login
> name" column for "dbo".
> --
> Dan D.
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" wrote:

> Dan
> You can add user to db_owner database fixed role and all objects will be
> created as dbo.tablename ( If he/she is a member of sysadmin server role)
> If he/she isn't you needs to specify
> create table dbo.table (col1 int)
This I understand.

> Waht does that mean "sometimes"? Can you reproduce the problem?
If I create a new database, my name will show up in the "login name" column
for "dbo". That would explain some of what I see. But as I look through
different databases I see a lot of different users in the "login name" colum
n
for "dbo" in the "login" column. In one database, the "login name" column fo
r
"dbo" will be empty. In another database, a user called "jbanner" will be in
the "login name" column for "dbo". In another database, "sa" will be in the
"login name" column for "dbo". In another database "domain\tbrown" will be i
n
the "login name" column for "dbo".
Some of these users are used for applications only, and it's highly unlikely
that someone logged on as the user and created a database so there must be
some other way that it happens.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:913FEB8F-7EE7-4161-8EBA-54D71DBA6314@.microsoft.com...
>
>|||Hi Dan
The login name column should show the login name who is considered the owner
of the database, whose user name will be 'dbo' when they use the database. I
don't know why EM is sometimes showing a blank here. There should always be
a mapped login name. One guess is that the login of the owner is a domain
account, and the domain is not available to validate the name. You can try
to verify that by run sp_helpdb in a query window, and seeing what gets
listed for the owner of the database.
You can change the login name that owns a database by using a query window.
Use the database, and run the following:
EXEC sp_changedbowner '<new_owner_login_name>'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
> Using SS2000 SP4. Under "Users" in EM, I see "dbo" in the name column and
> in
> the login name column a name like "webapp". In other databases, I see
> "dbo"
> in the name column and nothing in the login name column.
> What is the significance of having another user like "webapp" in the login
> name column? And how could can I assign another user login to dbo?
> Thanks,
> --
> Dan D.|||When I run sp_helpdb on the database with a blank in the "login name" column
for dbo, I get an error: "cannot insert the value NULL into column '', table
''; column does not allow nulls. INSERT fails. I changed the dbowner to 'sa'
and it then worked correctly.
The other databases respond appropriately.
I do have some databases with a domain user as owner so I don't think the
domain is the problem.
In a database for an application, could I assign the database owner as a
user with limited (read/write) rights? Or would making the user the database
owner override the read/write permissions?
Thanks,
--
Dan D.
"Kalen Delaney" wrote:

> Hi Dan
> The login name column should show the login name who is considered the own
er
> of the database, whose user name will be 'dbo' when they use the database.
I
> don't know why EM is sometimes showing a blank here. There should always b
e
> a mapped login name. One guess is that the login of the owner is a domain
> account, and the domain is not available to validate the name. You can try
> to verify that by run sp_helpdb in a query window, and seeing what gets
> listed for the owner of the database.
> You can change the login name that owns a database by using a query window
.
> Use the database, and run the following:
> EXEC sp_changedbowner '<new_owner_login_name>'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:C8FC2930-1CC2-4BEF-BACA-C292396E498E@.microsoft.com...
>
>|||Dan
I have seen that problem with the NULL error message, and I still believe it
has something to do with not being able to validate the login. Other domain
logins might be able to be validated, or might be using cached credentials,
or something. But as you found, changing the owner to sa is usually a great
solution.
The owner of a database always has the user name dbo in the database, which
always give her full permissions. If you don't want that login to have full
permission, give ownership of the database to someone else.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...[vbcol=seagreen]
> When I run sp_helpdb on the database with a blank in the "login name"
> column
> for dbo, I get an error: "cannot insert the value NULL into column '',
> table
> ''; column does not allow nulls. INSERT fails. I changed the dbowner to
> 'sa'
> and it then worked correctly.
> The other databases respond appropriately.
> I do have some databases with a domain user as owner so I don't think the
> domain is the problem.
> In a database for an application, could I assign the database owner as a
> user with limited (read/write) rights? Or would making the user the
> database
> owner override the read/write permissions?
> Thanks,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>|||We've had some developers leave recently and I removed their logins. It migh
t
be that one of them owned the database and because the login/user has been
removed the system can't validate them anymore. I thought the system always
asked to reassign the object in that case but maybe not.
Thanks Kalen,
--
Dan D.
"Kalen Delaney" wrote:

> Dan
> I have seen that problem with the NULL error message, and I still believe
it
> has something to do with not being able to validate the login. Other domai
n
> logins might be able to be validated, or might be using cached credentials
,
> or something. But as you found, changing the owner to sa is usually a grea
t
> solution.
> The owner of a database always has the user name dbo in the database, whic
h
> always give her full permissions. If you don't want that login to have ful
l
> permission, give ownership of the database to someone else.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:841A0E2F-8DBE-437F-8661-2CFF7998404E@.microsoft.com...
>
>|||You can identify databases invalid owners with the following query:
SELECT name
FROM master..sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
Execute sp_changedbowner for the problem databases.

> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
The check for database ownership is done when you drop a standard login
(sp_droplogin) but not when you remove Windows logins (sp_revokelogin). In
fact, you can have a Windows login own a database even when no corresponding
login exists in SQL Server. This is often the case when an administrator
connects to SQL Server via BUILTIJN\Administrators group membership and then
creates, restores or attaches a database. The database is then owned by the
individual's Windows account. However, the database owner becomes invalid
if the Windows account is later deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:9B68CC18-5C0F-47E0-BB0E-276A7C92569A@.microsoft.com...[vbcol=seagreen]
> We've had some developers leave recently and I removed their logins. It
> might
> be that one of them owned the database and because the login/user has been
> removed the system can't validate them anymore. I thought the system
> always
> asked to reassign the object in that case but maybe not.
> Thanks Kalen,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>