Deadlocks same time Full DB backup

I have a cluster SQL Server 2000 Enterprise with SP3A
that has a full database backup that runs nightly at
12:15 AM.
The database system has functioning properly for about
six months.
Three consective dailys in a row I have received a
deadlock at the same as the full database is running
(12:15 AM). The full database executes for about 2
minutes. Thats about the same length of time as the
deadlock. This deadlock information comes from the DBCC
Traceon (1204) setting.
What could cause this problem?
Thank You,
FrankCan you post what U get in the SQL server errorlog this way we can see the
sequence of events leading to the deadlock issue..I guess that SQL server
service is starting with the -T1204 -T3605
to ensure that this info is sent to the error log.
--
Olu Adedeji
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:4fd801c4000e$53839150$a501280a@.phx.gbl...
> I have a cluster SQL Server 2000 Enterprise with SP3A
> that has a full database backup that runs nightly at
> 12:15 AM.
> The database system has functioning properly for about
> six months.
> Three consective dailys in a row I have received a
> deadlock at the same as the full database is running
> (12:15 AM). The full database executes for about 2
> minutes. Thats about the same length of time as the
> deadlock. This deadlock information comes from the DBCC
> Traceon (1204) setting.
>
> What could cause this problem?
> Thank You,
> Frank|||My 1st guess is that you are experiencing IO Saturation.
Is your backup occuring on a separate volume than your data ?
Greg Jackson
PDX, Oregonsql

Deadlocks same time Full DB backup

I have a cluster SQL Server 2000 Enterprise with SP3A
that has a full database backup that runs nightly at
12:15 AM.
The database system has functioning properly for about
six months.
Three consective dailys in a row I have received a
deadlock at the same as the full database is running
(12:15 AM). The full database executes for about 2
minutes. Thats about the same length of time as the
deadlock. This deadlock information comes from the DBCC
Traceon (1204) setting.
What could cause this problem?
Thank You,
FrankCan you post what U get in the SQL server errorlog this way we can see the
sequence of events leading to the deadlock issue..I guess that SQL server
service is starting with the -T1204 -T3605
to ensure that this info is sent to the error log.
Olu Adedeji
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:4fd801c4000e$53839150$a501280a@.phx.gbl...
> I have a cluster SQL Server 2000 Enterprise with SP3A
> that has a full database backup that runs nightly at
> 12:15 AM.
> The database system has functioning properly for about
> six months.
> Three consective dailys in a row I have received a
> deadlock at the same as the full database is running
> (12:15 AM). The full database executes for about 2
> minutes. Thats about the same length of time as the
> deadlock. This deadlock information comes from the DBCC
> Traceon (1204) setting.
>
> What could cause this problem?
> Thank You,
> Frank|||My 1st guess is that you are experiencing IO Saturation.
Is your backup occuring on a separate volume than your data ?
Greg Jackson
PDX, Oregon

deadlock victim

h
im the below output from profiler..processes r getting deadlock and most of the time the below processes spid is causing blocking..but im unable to get procedure associated with that...
RPC Event 0 sp_executesql;
wot is sp_executesql;1 ?There are some trace falgs you can turn on to help resolve deadlocking
problems.
DBCC TRACEON (1204, 3605,-1)
will output info into your SQL Server ErrorLog.
--
HTH
Ryan Waight, MCDBA, MCSE
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:B1BF5BC8-8889-4054-9FDD-1FE3B52418B9@.microsoft.com...
> hi
> im the below output from profiler..processes r getting deadlock and most
of the time the below processes spid is causing blocking..but im unable to
get procedure associated with that...
> RPC Event 0 sp_executesql;1
> wot is sp_executesql;1 ?
>|||h
thnks for yr reply..but i failied to identify which the procedure associated with this...RPC Event 0 sp_executesql;
this "RPC Event 0 sp_executesql;1" im in getting profiler as deadlock processes

Deadlock Question

Hi All,

Can multiple updates on one table using single
query generate deadlock ?
For example, at the same time, there are 2 users
run 2 queries as follows :

User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no

Note :
The content of the column "no" on table tab2 :
('A','B','C',...,'X','Y','Z')
The content of the column "no" on table tab3
is like in table tab2, but in different order :
('Z','Y','X',....,'C','B','A')

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Anita (anonymous@.devdex.com) writes:
> Can multiple updates on one table using single
> query generate deadlock ?
> For example, at the same time, there are 2 users
> run 2 queries as follows :
> User1 runs :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> User2 runs :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab3 on tab1.no = tab3.no
> Note :
> The content of the column "no" on table tab2 :
> ('A','B','C',...,'X','Y','Z')
> The content of the column "no" on table tab3
> is like in table tab2, but in different order :
> ('Z','Y','X',....,'C','B','A')

Tables in a relational database are sets, and data has no order.

But, of course, for the evaluation of a query the physical order may
affect such things as deadlock.

Anyway, I am not going to answer the question directly, because there
is a lot of unknown elements. Is tbl.v a primary key or at least
indexed? What about tbl2.no and tbl3.no? And what exactly is
different order?

CREATE TABLE statements for the tables and INSERT statemetns for the
data may help.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

Thanks for your reply.

Deadlock can be found easily in several command steps.
But, how can I find it in multiple updates using
only one step of command ?

This question is posted because I do not know
exactly how SQL Server handles my sample query.
And I become worry after reading many deadlock
articles here. Especially deadlock that is caused
by table index.

Below is the description of the tables :

Table tb1 :
- no CHAR(10); no2 CHAR(10); v INT
- Index possibility : only one index, on no or on no2
- no is unique, no2 is unique
- v is not a key.

Table tb2 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique

Table tb3 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique

Regards,

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> Deadlock can be found easily in several command steps.
> But, how can I find it in multiple updates using
> only one step of command ?

Testing deadlocks that may occur from single statements are indeed not
trivial to construct at will.

One possibility is to write a small app - could even be a stored procedure
- that runs the supicious SQL statement all over again in an infinite
loop. If you get a deadlock, you now know that it can happen. If you
don't get a deadlock - well you still don't know, because may the test
was not good enough.

Another approach is to introduce a waitstate somewhere, so that you get
a chance to start a second query window with the competing query. This
is not trivial either. For a simple case, I used this function some
time ago:

create function nisse () returns int as
begin
exec master..xp_cmdshell 'osql -E -n -Q "WAITFOR DELAY ''00:00:20''"'
return 1
end

In your case, at least one your updates should read:

UPDATE tbl
SET col = dbo.nisse() -- Or some expression including dbo.nisse().
...

But of course, this constructs a situation which is not really the same
as the real-world scenario, and the observations may not be transferrable.
(But it seems to me that in this case, they could.)

Since you did not provide CREATE TABLE statements and INSERT statements
with sample data, I was too lazy to actually try this technique with
your example. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||When SQL Server receive query :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
I expect it follows the procedure like this :
a. Find the rows that will be updated.
b. If they are not found then exit.
c. Try locking the rows found.
d. If locking is successfull then update the
rows and exit.
e. Wait for miliseconds.
f. If query timeout expires then exit.
g. goto c.

Since I do not have information about how SQL Server
handles the query, I usually insert tablock hint in the query :
update tab1 with (tablock) set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

Though by using tablock hint it will lock all rows
in the table (prevent other rows from being updated
by other users) but, I think I should take this way.
It is free from deadlock.

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> When SQL Server receive query :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> I expect it follows the procedure like this :
> a. Find the rows that will be updated.
> b. If they are not found then exit.
> c. Try locking the rows found.
> d. If locking is successfull then update the
> rows and exit.
> e. Wait for miliseconds.
> f. If query timeout expires then exit.
> g. goto c.

I have to admit that I don't fully master the internal procedure, but
I would expect it to be somewhat different. I would expect that already
when SQL Server finds the matching rows that it applies at least
shared locks, possible also intent locks. Once a row is found to
qualify, I would suppose SQL Server puts an exclusive lock on a
row.

You mention "query timeout". I suppose you mean lock timeout, which you
control with SET LOCK_TIMEOUT. Query timeout is a client (mis)feature,
and does not affect locking.

Going back to your original post, you had these two statements:

User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no

Working from my assumptions above - which I like to stress are nothing
but assumptions, you could get a deadlock here, if the statistics on
the table are such that the optimizer chooses different query plans.
For instance, for the first query, the optimizer decides to scan tab1
and then perform a nested join with tab2. But for the second query,
the optimizer scans tab3, and performs a nested join with index seek
on tab1. If the two queries start at the same time, they will find
matching rows in tab1 in different order, and therefor they will
deadlock.

> Since I do not have information about how SQL Server
> handles the query, I usually insert tablock hint in the query :
> update tab1 with (tablock) set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> Though by using tablock hint it will lock all rows
> in the table (prevent other rows from being updated
> by other users) but, I think I should take this way.
> It is free from deadlock.

Yes, this should be deadlock free. But there are of course other issues
with tablock. If most updates are on single rows, tablock might be too
heavy-handed and lead to concurrency issues.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

Thanks for your reply,

Yes, your assumption is somewhat different with what I expect. I expect
: if there are 10 matching rows and SQL Server can lock only 9 rows,
then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
rows again.
If my expectation is true, then the query is deadlock free and I will
avoid using tablock hint.

My last question is where I can get information that tell us your
assumption or my expectation is true ?

Regards,
Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> Yes, your assumption is somewhat different with what I expect. I expect
>: if there are 10 matching rows and SQL Server can lock only 9 rows,
> then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
> rows again.
> If my expectation is true, then the query is deadlock free and I will
> avoid using tablock hint.
> My last question is where I can get information that tell us your
> assumption or my expectation is true ?

So much I can tell with confidence, that SQL Server never releases locks
because it cannot get all locks it needs to carry out a task. While such
a strategy could reduce deadlock, it could have other nasty effects like
lock starvation. A process that needs to access many rows in a busy system
would never get all locks.

Also, I believe that the work order is something like: lock one row,
update that row, lock next row and so on. In this case, it is of course
even less possible to release rows.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

Thanks for all your replies.

Regards,
Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

dcdiag error

Hi all,
This morning my dcdiag command show this error
Starting test: systemlog
An Error Event occured. EventID: 0x00000457
Time Generated: 08/24/2007 08:41:46
(Event String could not be retrieved)
An Error Event occured. EventID: 0x00000457
Time Generated: 08/24/2007 08:41:48
(Event String could not be retrieved)
................... myDC failed test systemlog
Anybody can help me with this? or explain to me the error.
Tks
JFBHi
This is not a SQL Server issue, you may want to post in a more appropriate
newsgroup.
John
"JFB" wrote:
> Hi all,
> This morning my dcdiag command show this error
> Starting test: systemlog
> An Error Event occured. EventID: 0x00000457
> Time Generated: 08/24/2007 08:41:46
> (Event String could not be retrieved)
> An Error Event occured. EventID: 0x00000457
> Time Generated: 08/24/2007 08:41:48
> (Event String could not be retrieved)
> ................... myDC failed test systemlog
> Anybody can help me with this? or explain to me the error.
> Tks
> JFB
>
>|||Ups... sorry...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5A1ADB7C-D22F-4A9B-A1A3-0DA7A68F5437@.microsoft.com...
> Hi
> This is not a SQL Server issue, you may want to post in a more appropriate
> newsgroup.
> John
> "JFB" wrote:
>> Hi all,
>> This morning my dcdiag command show this error
>> Starting test: systemlog
>> An Error Event occured. EventID: 0x00000457
>> Time Generated: 08/24/2007 08:41:46
>> (Event String could not be retrieved)
>> An Error Event occured. EventID: 0x00000457
>> Time Generated: 08/24/2007 08:41:48
>> (Event String could not be retrieved)
>> ................... myDC failed test systemlog
>> Anybody can help me with this? or explain to me the error.
>> Tks
>> JFB
>>

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 owner

Hi Everyone,
I'm still with this problem and now I have little bit of time to find the
solution.
After any user import data to a table I want to have that table as dbo
owner.
I dont want to give my users full administrator permisssions.
I try to setup in each database access as db_owner, db_accesmin,
db_dlladmin but no luck
I know I can change the owner later with sp_changeobjectowner but sometimes
I run crazy with more than three users at the same time.
How can I fix this issue?
Tks
JFBCould you be more specific?
Do you use qualified object names? (two-part names: owner.object_name)
ML|||Yeah, a sample table, user, and a query you are trying to run in a script
would really make this easier.
Otherwise the best answer (as usual) is 42. Sadly we still don't know the
question to that answer either :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"ML" <ML@.discussions.microsoft.com> wrote in message
news:77548CF2-DE05-4C93-A509-4BDF04EF49C8@.microsoft.com...
> Could you be more specific?
> Do you use qualified object names? (two-part names: owner.object_name)
>
> ML|||Hi
If the structure of the data file is fixed, then you can create a DTS
package and schedule it as a job to import the data. This will mean that the
user does not have to "manually" import the data. You table structure (and
owner) can then be fixed. If you wish to manipulate the data it may be
worthwhile loading the data into a set of holding tables, where you can
subsequently work with that data and load it into your destination tables.
For more information on DTS look up DTS in books online and at l]
For example if you wish to process several files you can loop through all
the files in a given directory using the example code at
[url]http://www.sqldts.com/default.aspx?246" target="_blank">www.sqldts.com.[/ur
l]...efault.aspx?246
John
"JFB" wrote:

> Hi Everyone,
> I'm still with this problem and now I have little bit of time to find the
> solution.
> After any user import data to a table I want to have that table as dbo
> owner.
> I dont want to give my users full administrator permisssions.
> I try to setup in each database access as db_owner, db_accesmin,
> db_dlladmin but no luck
> I know I can change the owner later with sp_changeobjectowner but sometime
s
> I run crazy with more than three users at the same time.
> How can I fix this issue?
> Tks
> JFB
>
>|||Tks for you answer, I have DTS packages in my enviroment.
What I need is to fix this dbo when the user import data from a text file
manually
Tks
JFB
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1364EA0B-E2AA-416F-B34F-275CA4B57CC7@.microsoft.com...
> Hi
> If the structure of the data file is fixed, then you can create a DTS
> package and schedule it as a job to import the data. This will mean that
> the
> user does not have to "manually" import the data. You table structure
> (and
> owner) can then be fixed. If you wish to manipulate the data it may be
> worthwhile loading the data into a set of holding tables, where you can
> subsequently work with that data and load it into your destination tables.
> For more information on DTS look up DTS in books online and at
> www.sqldts.com.
> For example if you wish to process several files you can loop through all
> the files in a given directory using the example code at
> http://www.sqldts.com/default.aspx?246
>
> John
> "JFB" wrote:
>|||Hi
It is not clear how/why you allow users to import data manually.
John
"JFB" wrote:

> Tks for you answer, I have DTS packages in my enviroment.
> What I need is to fix this dbo when the user import data from a text file
> manually
> Tks
> JFB
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1364EA0B-E2AA-416F-B34F-275CA4B57CC7@.microsoft.com...
>
>|||Well each applicator developer has sql enterprise manager to conect to our
sql server with permissions on the database that they are working on it.
Base on bad experiences I don't want they to have full admin permissions.
But they need to import data manually for different purpose.
(analize data, test sp's, test vb app, ...)
Tks
JFB
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CD8D19DB-CF63-4410-965D-11D31A092965@.microsoft.com...
> Hi
> It is not clear how/why you allow users to import data manually.
> John
>
> "JFB" wrote:
>|||What I need is to fix this dbo when the users(developers) import data from a
text file manually
Tks
JFB
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ebjR8RVnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Yeah, a sample table, user, and a query you are trying to run in a script
> would really make this easier.
> Otherwise the best answer (as usual) is 42. Sadly we still don't know the
> question to that answer either :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:77548CF2-DE05-4C93-A509-4BDF04EF49C8@.microsoft.com...
>|||Hi
I have never come across the situation where a programmer has needed to
create tables for testing. If they have test data it should already be
in the destination table format; if this is from a previous version of
the database, then the data is re-exported after an upgrade; if this a
formal import process then the table definitions/process should be
formalised and the dbo owner in place.
If they do need to carry out an activity that may impact other users
then you could give them their own database and make them dbo.
John