hi
well I am working on a project that our database has 2, 8000 record table that they have triggers on the update of their feilds
our application must update these tables but it takes a long time to do this
I know there is something named bulk insert but I couldn't find sth similar to this command for update
so would you please help me to find a faster way to update these tables?
thanks for your attention
Best Regards
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
hi,
netman Mo wrote:
> hi
> well I am working on a project that our database has 2, 8000 record
> table that they have triggers on the update of their feilds our
> application must update these tables but it takes a long time to do
> this
> I know there is something named bulk insert but I couldn't find sth
> similar to this command for update
> so would you please help me to find a faster way to update these
> tables?
nope.. update syntax is not overloaded with bulk operators..
if the cause of your delay is dependent on the trigger fired by the update
statement, you should perhaps check it's code... or... if you are sure the
updates you are performing do not involve the trigger check, you can disable
it before executing the statements..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply
Showing posts with label triggers. Show all posts
Showing posts with label triggers. Show all posts
Tuesday, March 27, 2012
A cursor with the name 'MyRS' already exists
Any help appreciated on this one as I have scoured the internet and got no j
oy!
I have a complex set of triggers and stored procedures that should result in
changes to my _Company table being replicated to an equivalent table in a
different database on a different SQL 2000 Server.
This works 99% of the time. However, when I update a particular record, the
trigger associated with that table fires, and then returns error 16915 (A
cursor with the name 'MyRS' already exists).
It appears as though the update tried to fire previously and then some error
occurred, so that it now thinks this record is mid-transaction' I don't
really know.
The main question is, can I trace the root cause of this error message, and
remove it?
As I stated earlier, in 99% of cases, everything works fine, so I think this
is a problem with the cursor being left in an "open" state, rather than a
fundamental issue with the trigger code.
Here is the test code I am using to update the record and hence fire the
trigger:
UPDATE _Company SET update_note_at='Not Synchronised with Sage, Telesales'
WHERE (update_note_at IS NULL) AND reference IN('ABC1234567')
Here is the output received when running the above in query analyser:
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Procedure at_Sage_Cust_Exp, Line 16
A cursor with the name 'MyRS' already exists.
The statement has been terminated.
Here is the actual code of the trigger:
CREATE TRIGGER at_Sage_Cust_Exp ON dbo._Company
FOR UPDATE
NOT FOR REPLICATION
AS
DECLARE @.TrigDate AS datetime
SET @.TrigDate=getdate()
DECLARE MyRS CURSOR
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage, update_note_at,reference
FROM inserted
OPEN MyRS
declare @.coId as uniqueidentifier
DECLARE @.update_at AS bit
DECLARE @.Update_Sage AS bit
DECLARE @.On_AT AS bit
DECLARE @.On_Sage AS bit
DECLARE @.update_note_at AS nvarchar(100)
DECLARE @.reference AS nvarchar(30)
DECLARE @.Old_update_note_at AS nvarchar(100)
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.Update_Sage, @.On_AT, @.On_Sage,
@.update_note_at, @.Reference
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.Old_update_note_at=(SELECT update_note_at FROM deleted WHERE id=@.coId)
IF @.update_at = 1 OR @.On_AT = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_CustAT_Exp @.coId
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
IF @.Update_Sage = 1 OR @.On_Sage = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_Cust_Exp @.coId, @.Reference
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.update_sage, @.On_AT,
@.On_Sage, @.update_note_at,@.Reference
END
CLOSE MyRS
DEALLOCATE MyRS
THANKS,
Andy, MCDBAChange your cursor to "local"
that is
DECLARE MyRS CURSOR LOCAL
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage,
update_note_at,reference
FROM inserted|||Never use cursors in triggers, is my advice. Why would you want to turn
every set-based update into a cursor?
Most of what you have can be done with two UPDATE statements so the
cursor looks superfluous. The only question is what your two SPs do.
Change them to set-based logic and you won't need the cursor at all.
David Portas
SQL Server MVP
--
oy!
I have a complex set of triggers and stored procedures that should result in
changes to my _Company table being replicated to an equivalent table in a
different database on a different SQL 2000 Server.
This works 99% of the time. However, when I update a particular record, the
trigger associated with that table fires, and then returns error 16915 (A
cursor with the name 'MyRS' already exists).
It appears as though the update tried to fire previously and then some error
occurred, so that it now thinks this record is mid-transaction' I don't
really know.
The main question is, can I trace the root cause of this error message, and
remove it?
As I stated earlier, in 99% of cases, everything works fine, so I think this
is a problem with the cursor being left in an "open" state, rather than a
fundamental issue with the trigger code.
Here is the test code I am using to update the record and hence fire the
trigger:
UPDATE _Company SET update_note_at='Not Synchronised with Sage, Telesales'
WHERE (update_note_at IS NULL) AND reference IN('ABC1234567')
Here is the output received when running the above in query analyser:
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Procedure at_Sage_Cust_Exp, Line 16
A cursor with the name 'MyRS' already exists.
The statement has been terminated.
Here is the actual code of the trigger:
CREATE TRIGGER at_Sage_Cust_Exp ON dbo._Company
FOR UPDATE
NOT FOR REPLICATION
AS
DECLARE @.TrigDate AS datetime
SET @.TrigDate=getdate()
DECLARE MyRS CURSOR
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage, update_note_at,reference
FROM inserted
OPEN MyRS
declare @.coId as uniqueidentifier
DECLARE @.update_at AS bit
DECLARE @.Update_Sage AS bit
DECLARE @.On_AT AS bit
DECLARE @.On_Sage AS bit
DECLARE @.update_note_at AS nvarchar(100)
DECLARE @.reference AS nvarchar(30)
DECLARE @.Old_update_note_at AS nvarchar(100)
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.Update_Sage, @.On_AT, @.On_Sage,
@.update_note_at, @.Reference
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.Old_update_note_at=(SELECT update_note_at FROM deleted WHERE id=@.coId)
IF @.update_at = 1 OR @.On_AT = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_CustAT_Exp @.coId
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
IF @.Update_Sage = 1 OR @.On_Sage = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_Cust_Exp @.coId, @.Reference
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.update_sage, @.On_AT,
@.On_Sage, @.update_note_at,@.Reference
END
CLOSE MyRS
DEALLOCATE MyRS
THANKS,
Andy, MCDBAChange your cursor to "local"
that is
DECLARE MyRS CURSOR LOCAL
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage,
update_note_at,reference
FROM inserted|||Never use cursors in triggers, is my advice. Why would you want to turn
every set-based update into a cursor?
Most of what you have can be done with two UPDATE statements so the
cursor looks superfluous. The only question is what your two SPs do.
Change them to set-based logic and you won't need the cursor at all.
David Portas
SQL Server MVP
--
Monday, March 19, 2012
8updating a sql servi stored procedures/triggers
hi all,
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaedI would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).
Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.
That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.
Regards,
Hugh Scott
Originally posted by rdavidoff
hi all,
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaed|||thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert
Originally posted by hmscott
I would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).
Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.
That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.
Regards,
Hugh Scott|||Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.
Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?
If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.
Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.
Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).
Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.
Or you could simply find a web developer willing to work with Oracle!!!
Sorry, I hope one of these helps!
Regards,
Hugh Scott
Originally posted by rdavidoff
thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert|||Originally posted by hmscott
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
I am atempting this very thing. Only difference is it is not Oracle it is a remote Turbo Image Database (ISAM files).
I have a stored procedure written that does a set of queries using OPENQUERY and updates three tables. My plan is to run it once every 10 - 15 minutes or so.
My stumbling block is that the job I scheduled to run the stored procedure is failing, but once I get that worked out it should be fine. I have talked to the owners of the data (it is a registration database for a community college) and the delay is acceptable in this situation.
So, if the data is not life threatining if there is a delay, I'd say that this is your best bet.|||i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob
Originally posted by hmscott
Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.
Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?
If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.
Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.
Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).
Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.
Or you could simply find a web developer willing to work with Oracle!!!
Sorry, I hope one of these helps!
Regards,
Hugh Scott|||Some gotcha's that you may want to consider and a site that is well worth looking at:
1. Data Transformation Services (DTS) are essentially mini-programs that are created in a GUI environment. As such, there is a LOT of complexity that is hidden from you. As you can imagine, there are both strong points and weak points to this:
a. Strong point: they are fairly easy to create and work with
b. Strong point: there are a lot of objects to work with and that makes just about any task doable.
c. Weak point: they are sometimes difficult to manage unless you spend a lot of time learning how to use dynamic properties that can be stored on the server
d. Weak point: since it's easy for a novice to work with these, it's easy to make a novice mistake and mess up a bunch of things
2. DTS runs in a separate memory space from the SQL server application. I have recently experienced some very negative side effects from having too many DTS packages running simultaneously. If it is at all possible, consider running DTS on a separate server from the database server.
3. This is a hard one for people new to DTS to understand. DTS always runs in the context of the client machine on which you are viewing the DTS package. Even though you are in Enterprise Manager and you THINK you are executing the DTS package on the SQL server, it is actually running using the DLL context of your client workstation. Send me an e-mail and I'll explain this one further. It's an important concept and not very well explained (in my opinion) in SQL BOL.
4. I have had a VERY bad experience with Meta Data Services. I would recommend avoiding this feature in SQL Server in a production environment.
5. Be sure that your client workstation and the SQL Server where the packages are stored are running the SAME version of SQL Server (same SP, too). DTS pacakges can be migrated from older versions to newer versions, but they are not necessarily backward compatible. DTS pacakges in SQL 7.0 were pretty bad until around SP3.
66. A good website to check out is www.sqldts.com. I found a really great tool for backing up DTS pacakges on there.
Good luck,
Hugh Scott
Originally posted by rdavidoff
i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaedI would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).
Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.
That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.
Regards,
Hugh Scott
Originally posted by rdavidoff
hi all,
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaed|||thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert
Originally posted by hmscott
I would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).
Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.
That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.
Regards,
Hugh Scott|||Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.
Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?
If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.
Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.
Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).
Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.
Or you could simply find a web developer willing to work with Oracle!!!
Sorry, I hope one of these helps!
Regards,
Hugh Scott
Originally posted by rdavidoff
thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert|||Originally posted by hmscott
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
I am atempting this very thing. Only difference is it is not Oracle it is a remote Turbo Image Database (ISAM files).
I have a stored procedure written that does a set of queries using OPENQUERY and updates three tables. My plan is to run it once every 10 - 15 minutes or so.
My stumbling block is that the job I scheduled to run the stored procedure is failing, but once I get that worked out it should be fine. I have talked to the owners of the data (it is a registration database for a community college) and the delay is acceptable in this situation.
So, if the data is not life threatining if there is a delay, I'd say that this is your best bet.|||i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob
Originally posted by hmscott
Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.
We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.
To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.
Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?
If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.
Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.
Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).
Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.
Or you could simply find a web developer willing to work with Oracle!!!
Sorry, I hope one of these helps!
Regards,
Hugh Scott|||Some gotcha's that you may want to consider and a site that is well worth looking at:
1. Data Transformation Services (DTS) are essentially mini-programs that are created in a GUI environment. As such, there is a LOT of complexity that is hidden from you. As you can imagine, there are both strong points and weak points to this:
a. Strong point: they are fairly easy to create and work with
b. Strong point: there are a lot of objects to work with and that makes just about any task doable.
c. Weak point: they are sometimes difficult to manage unless you spend a lot of time learning how to use dynamic properties that can be stored on the server
d. Weak point: since it's easy for a novice to work with these, it's easy to make a novice mistake and mess up a bunch of things
2. DTS runs in a separate memory space from the SQL server application. I have recently experienced some very negative side effects from having too many DTS packages running simultaneously. If it is at all possible, consider running DTS on a separate server from the database server.
3. This is a hard one for people new to DTS to understand. DTS always runs in the context of the client machine on which you are viewing the DTS package. Even though you are in Enterprise Manager and you THINK you are executing the DTS package on the SQL server, it is actually running using the DLL context of your client workstation. Send me an e-mail and I'll explain this one further. It's an important concept and not very well explained (in my opinion) in SQL BOL.
4. I have had a VERY bad experience with Meta Data Services. I would recommend avoiding this feature in SQL Server in a production environment.
5. Be sure that your client workstation and the SQL Server where the packages are stored are running the SAME version of SQL Server (same SP, too). DTS pacakges can be migrated from older versions to newer versions, but they are not necessarily backward compatible. DTS pacakges in SQL 7.0 were pretty bad until around SP3.
66. A good website to check out is www.sqldts.com. I found a really great tool for backing up DTS pacakges on there.
Good luck,
Hugh Scott
Originally posted by rdavidoff
i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob
8K/page limit
Hi there,
Does the 8K/page (row) limit also involve views? We're trying to insert a
record into a view (via nested instead of insert triggers) and the query
results in:
Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line 1
String or binary data would be truncated.
The statement has been terminated.
The view is made up of tables of which none are exceeding the 8K limiet. The
query does not pass [n][var]char values bigger than defined on table level.
Any clues?
Thanks,
SLE"SLE" <info@.NOSPAM.dataworx.be> wrote...
> Does the 8K/page (row) limit also involve views? We're trying to insert a
> record into a view (via nested instead of insert triggers) and the query
> results in:
> Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line
1
> String or binary data would be truncated.
> The statement has been terminated.
> The view is made up of tables of which none are exceeding the 8K limiet.
The
> query does not pass [n][var]char values bigger than defined on table
level.
>
FYI:
Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
insert (and all columns are populated correctly) but we don't feel quite
comfortable yet :-|
I've checked books online: there are no aggregate functions involved, nor
binary or varbinary data, nor distributed queries.
Any thoughts?
Thanks,
SLE|||The error indicates that you _are_ passing character values that are larger
than the columns they are stored in. If the row size was the problem you
would get a different error message. Are you not accidentally passing a char
to a varchar column? I assume you have checked your code already, but the
only explanation I can give at this moment is that you have missed
something. If you're sure you haven't can you please post your code so we
can have a look through it?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:OqGhJqOrDHA.2688@.TK2MSFTNGP09.phx.gbl...
> "SLE" <info@.NOSPAM.dataworx.be> wrote...
> >
> > Does the 8K/page (row) limit also involve views? We're trying to insert
a
> > record into a view (via nested instead of insert triggers) and the query
> > results in:
> >
> > Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD,
Line
> 1
> > String or binary data would be truncated.
> > The statement has been terminated.
> >
> > The view is made up of tables of which none are exceeding the 8K limiet.
> The
> > query does not pass [n][var]char values bigger than defined on table
> level.
> >
> FYI:
> Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
> insert (and all columns are populated correctly) but we don't feel quite
> comfortable yet :-|
> I've checked books online: there are no aggregate functions involved, nor
> binary or varbinary data, nor distributed queries.
> Any thoughts?
> Thanks,
> SLE
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> The error indicates that you _are_ passing character values that are
larger
> than the columns they are stored in. If the row size was the problem you
> would get a different error message. Are you not accidentally passing a
char
> to a varchar column? I assume you have checked your code already, but the
> only explanation I can give at this moment is that you have missed
> something. If you're sure you haven't can you please post your code so we
> can have a look through it?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Okay, a bit lengthy, but here we go. From Profiler:
SET ANSI_WARNINGS OFF
exec sp_executesql
N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
[DriveIntegrity], [FileSystemFlags],
[FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
[MediaLoaded], [MediaType],
[MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
[SCSIPort], [SCSITargetId],
[Size], [TransferRate], [VolumeName], [VolumeSerialNumber], [Capabilities],
[CapabilityDescriptions],
[ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
[MaxMediaSize], [DefaultBlockSize],
[MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
[Security], [LastCleaned],
[MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
[MountCount], [TimeOfLastMount],
[TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning], [UnitsUsed],
[PowerManagementCapabilities],
[OtherIdentifyingInfo], [IdentifyingDescriptions], [AdditionalAvailability],
[SystemCreationClassName],
[SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
[Availability], [StatusInfo],
[LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
[TotalPowerOnHours], [MaxQuiesceTime],
[EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
[OperationalStatus],
[StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
[Description], [ElementName],
[LastModifiedBy], [ToArchive], [TimeOfCreation])
VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
@.FileSystemFlagsExValue, @.IdValue,
@.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
@.MfrAssignedRevisionLevelValue,
@.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
@.SCSITargetIdValue,
@.SizeValue, @.TransferRateValue, @.VolumeNameValue, @.VolumeSerialNumberValue,
@.CapabilitiesValue,
@.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
@.CompressionMethodValue,
@.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
@.MaxBlockSizeValue,
@.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
@.LastCleanedValue, @.MaxAccessTimeValue,
@.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
@.MountCountValue, @.TimeOfLastMountValue,
@.TotalMountTimeValue, @.UnitsDescriptionValue, @.MaxUnitsBeforeCleaningValue,
@.UnitsUsedValue,
@.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
@.IdentifyingDescriptionsValue,
@.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
@.SystemNameValue, @.CreationClassNameValue,
@.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
@.LastErrorCodeValue, @.ErrorDescriptionValue,
0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
@.EnabledStateValue,
@.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
@.OperationalStatusValue,
@.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
@.DescriptionValue,
@.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
int,@.FileSystemFlagsExValue bigint,
@.IdValue varchar(2),@.ManufacturerValue
varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue varchar(6),
@.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
varchar(8000),@.SCSIBusValue bigint,
@.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
int,@.SizeValue bigint,
@.TransferRateValue float,@.VolumeNameValue
varchar(8),@.VolumeSerialNumberValue varchar(8),
@.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
varchar(8000),@.ErrorMethodologyValue varchar(8000),
@.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
bigint,@.MaxMediaSizeValue bigint,
@.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
bigint,@.MediaIsLockedValue bigint,
@.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
bigint,@.UncompressedDataRateValue bigint,
@.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
bigint,@.TimeOfLastMountValue datetime,
@.TotalMountTimeValue bigint,@.UnitsDescriptionValue
varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
@.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
@.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
varchar(8000),
@.SystemCreationClassNameValue varchar(20),@.SystemNameValue
varchar(6),@.CreationClassNameValue varchar(16),
@.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
int,@.LastErrorCodeValue bigint,
@.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
bigint,@.TotalPowerOnHoursValue bigint,
@.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
varchar(8000),
@.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
varchar(8000),
@.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
varchar(2),
@.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
varchar(8000),
@.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
@.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue = 0,
@.FileSystemFlagsExValue = 0,
@.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
@.MaximumComponentLengthValue = 110,
@.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
@.RevisionLevelValue = '',
@.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
@.SCSITargetIdValue = 0,
@.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
@.VolumeNameValue = 'BTS2004B',
@.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
@.CapabilityDescriptionsValue = NULL,
@.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
@.NumberOfMediaSupportedValue = 0,
@.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue = 0,
@.MinBlockSizeValue = 0,
@.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue = NULL,
@.MaxAccessTimeValue = NULL,
@.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue =NULL, @.MountCountValue = NULL,
@.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
@.UnitsDescriptionValue = NULL,
@.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
@.PowerManagementCapabilitiesValue = NULL,
@.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
@.AdditionalAvailabilityValue = NULL,
@.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue ='WS9843',
@.CreationClassNameValue = 'Win32_CDROMDrive',
@.DeviceIDValue ='IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
0',
@.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
@.ErrorDescriptionValue = '',
@.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
@.MaxQuiesceTimeValue = NULL,
@.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
@.RequestedStateValue = NULL,
@.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
@.StatusDescriptionsValue = NULL,
@.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK', @.CaptionValue
= 'HL-DT-ST DVD-ROM GDR8161B',
@.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
@.LastModifiedByValue = 'DOSIM000\SIDLECS',
@.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
And these are the affected tables contained within the view (from bottom to
top):
CREATE TABLE [dbo].[Win32_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[DriveIntegrity] [bit] NULL ,
[FileSystemFlags] [int] NULL ,
[FileSystemFlagsEx] [bigint] NULL ,
[Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaximumComponentLength] [bigint] NULL ,
[MediaLoaded] [bit] NULL ,
[MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
NULL ,
[RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[SCSIBus] [bigint] NULL ,
[SCSILogicalUnit] [int] NULL ,
[SCSIPort] [int] NULL ,
[SCSITargetId] [int] NULL ,
[Size] [bigint] NULL ,
[TransferRate] [float] NULL ,
[VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[NumberOfMediaSupported] [bigint] NULL ,
[MaxMediaSize] [bigint] NULL ,
[DefaultBlockSize] [bigint] NULL ,
[MaxBlockSize] [bigint] NULL ,
[MinBlockSize] [bigint] NULL ,
[NeedsCleaning] [bit] NULL ,
[MediaIsLocked] [bit] NULL ,
[Security] [int] NULL ,
[LastCleaned] [datetime] NULL ,
[MaxAccessTime] [bigint] NULL ,
[UncompressedDataRate] [bigint] NULL ,
[LoadTime] [bigint] NULL ,
[UnloadTime] [bigint] NULL ,
[MountCount] [bigint] NULL ,
[TimeOfLastMount] [datetime] NULL ,
[TotalMountTime] [bigint] NULL ,
[UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaxUnitsBeforeCleaning] [bigint] NULL ,
[UnitsUsed] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[PowerManagementCapabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL
,
[SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[PowerManagementSupported] [bit] NULL ,
[Availability] [int] NULL ,
[StatusInfo] [int] NULL ,
[LastErrorCode] [bigint] NULL ,
[ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ErrorCleared] [bit] NULL ,
[PowerOnHours] [bigint] NULL ,
[TotalPowerOnHours] [bigint] NULL ,
[MaxQuiesceTime] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[EnabledState] [int] NULL ,
[OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[RequestedState] [int] NULL ,
[EnabledDefault] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[InstallDate] [datetime] NULL ,
[Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
[Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[ToArchive] [bit] NULL ,
[TimeOfCreation] [datetime] NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]|||Can you also post the definition of and trigger on
DATAVIEW_Win32_CDROMDrive?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:%23J0t3FQrDHA.648@.TK2MSFTNGP11.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> > The error indicates that you _are_ passing character values that are
> larger
> > than the columns they are stored in. If the row size was the problem you
> > would get a different error message. Are you not accidentally passing a
> char
> > to a varchar column? I assume you have checked your code already, but
the
> > only explanation I can give at this moment is that you have missed
> > something. If you're sure you haven't can you please post your code so
we
> > can have a look through it?
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> Okay, a bit lengthy, but here we go. From Profiler:
> SET ANSI_WARNINGS OFF
> exec sp_executesql
> N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
> [DriveIntegrity], [FileSystemFlags],
> [FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
> [MediaLoaded], [MediaType],
> [MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
> [SCSIPort], [SCSITargetId],
> [Size], [TransferRate], [VolumeName], [VolumeSerialNumber],
[Capabilities],
> [CapabilityDescriptions],
> [ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
> [MaxMediaSize], [DefaultBlockSize],
> [MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
> [Security], [LastCleaned],
> [MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
> [MountCount], [TimeOfLastMount],
> [TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning],
[UnitsUsed],
> [PowerManagementCapabilities],
> [OtherIdentifyingInfo], [IdentifyingDescriptions],
[AdditionalAvailability],
> [SystemCreationClassName],
> [SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
> [Availability], [StatusInfo],
> [LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
> [TotalPowerOnHours], [MaxQuiesceTime],
> [EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
> [OperationalStatus],
> [StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
> [Description], [ElementName],
> [LastModifiedBy], [ToArchive], [TimeOfCreation])
> VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
> @.FileSystemFlagsExValue, @.IdValue,
> @.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
> @.MfrAssignedRevisionLevelValue,
> @.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
> @.SCSITargetIdValue,
> @.SizeValue, @.TransferRateValue, @.VolumeNameValue,
@.VolumeSerialNumberValue,
> @.CapabilitiesValue,
> @.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
> @.CompressionMethodValue,
> @.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
> @.MaxBlockSizeValue,
> @.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
> @.LastCleanedValue, @.MaxAccessTimeValue,
> @.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
> @.MountCountValue, @.TimeOfLastMountValue,
> @.TotalMountTimeValue, @.UnitsDescriptionValue,
@.MaxUnitsBeforeCleaningValue,
> @.UnitsUsedValue,
> @.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
> @.IdentifyingDescriptionsValue,
> @.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
> @.SystemNameValue, @.CreationClassNameValue,
> @.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
> @.LastErrorCodeValue, @.ErrorDescriptionValue,
> 0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
> @.EnabledStateValue,
> @.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
> @.OperationalStatusValue,
> @.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
> @.DescriptionValue,
> @.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
> N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
> int,@.FileSystemFlagsExValue bigint,
> @.IdValue varchar(2),@.ManufacturerValue
> varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue
varchar(6),
> @.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
> varchar(8000),@.SCSIBusValue bigint,
> @.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
> int,@.SizeValue bigint,
> @.TransferRateValue float,@.VolumeNameValue
> varchar(8),@.VolumeSerialNumberValue varchar(8),
> @.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
> varchar(8000),@.ErrorMethodologyValue varchar(8000),
> @.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
> bigint,@.MaxMediaSizeValue bigint,
> @.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
> bigint,@.MediaIsLockedValue bigint,
> @.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
> bigint,@.UncompressedDataRateValue bigint,
> @.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
> bigint,@.TimeOfLastMountValue datetime,
> @.TotalMountTimeValue bigint,@.UnitsDescriptionValue
> varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
> @.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
> varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
> @.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
> varchar(8000),
> @.SystemCreationClassNameValue varchar(20),@.SystemNameValue
> varchar(6),@.CreationClassNameValue varchar(16),
> @.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
> int,@.LastErrorCodeValue bigint,
> @.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
> bigint,@.TotalPowerOnHoursValue bigint,
> @.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
> varchar(8000),
> @.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
> varchar(8000),
> @.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
> varchar(2),
> @.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
> varchar(8000),
> @.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
> @.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue =0,
> @.FileSystemFlagsExValue = 0,
> @.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
> @.MaximumComponentLengthValue = 110,
> @.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
> @.RevisionLevelValue = '',
> @.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
> @.SCSITargetIdValue = 0,
> @.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
> @.VolumeNameValue = 'BTS2004B',
> @.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
> @.CapabilityDescriptionsValue = NULL,
> @.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
> @.NumberOfMediaSupportedValue = 0,
> @.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue =0,
> @.MinBlockSizeValue = 0,
> @.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue =NULL,
> @.MaxAccessTimeValue = NULL,
> @.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue
=> NULL, @.MountCountValue = NULL,
> @.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
> @.UnitsDescriptionValue = NULL,
> @.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
> @.PowerManagementCapabilitiesValue = NULL,
> @.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
> @.AdditionalAvailabilityValue = NULL,
> @.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue => 'WS9843',
> @.CreationClassNameValue = 'Win32_CDROMDrive',
> @.DeviceIDValue =>
'IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
> 0',
> @.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
> @.ErrorDescriptionValue = '',
> @.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
> @.MaxQuiesceTimeValue = NULL,
> @.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
> @.RequestedStateValue = NULL,
> @.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
> @.StatusDescriptionsValue = NULL,
> @.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK',
@.CaptionValue
> = 'HL-DT-ST DVD-ROM GDR8161B',
> @.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
> @.LastModifiedByValue = 'DOSIM000\SIDLECS',
> @.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
> And these are the affected tables contained within the view (from bottom
to
> top):
> CREATE TABLE [dbo].[Win32_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [DriveIntegrity] [bit] NULL ,
> [FileSystemFlags] [int] NULL ,
> [FileSystemFlagsEx] [bigint] NULL ,
> [Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaximumComponentLength] [bigint] NULL ,
> [MediaLoaded] [bit] NULL ,
> [MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
> NULL ,
> [RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [SCSIBus] [bigint] NULL ,
> [SCSILogicalUnit] [int] NULL ,
> [SCSIPort] [int] NULL ,
> [SCSITargetId] [int] NULL ,
> [Size] [bigint] NULL ,
> [TransferRate] [float] NULL ,
> [VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [NumberOfMediaSupported] [bigint] NULL ,
> [MaxMediaSize] [bigint] NULL ,
> [DefaultBlockSize] [bigint] NULL ,
> [MaxBlockSize] [bigint] NULL ,
> [MinBlockSize] [bigint] NULL ,
> [NeedsCleaning] [bit] NULL ,
> [MediaIsLocked] [bit] NULL ,
> [Security] [int] NULL ,
> [LastCleaned] [datetime] NULL ,
> [MaxAccessTime] [bigint] NULL ,
> [UncompressedDataRate] [bigint] NULL ,
> [LoadTime] [bigint] NULL ,
> [UnloadTime] [bigint] NULL ,
> [MountCount] [bigint] NULL ,
> [TimeOfLastMount] [datetime] NULL ,
> [TotalMountTime] [bigint] NULL ,
> [UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaxUnitsBeforeCleaning] [bigint] NULL ,
> [UnitsUsed] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [PowerManagementCapabilities] [varchar] (1024) COLLATE
Latin1_General_CI_AS
> NULL ,
> [OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
> [IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
> NULL ,
> [AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [PowerManagementSupported] [bit] NULL ,
> [Availability] [int] NULL ,
> [StatusInfo] [int] NULL ,
> [LastErrorCode] [bigint] NULL ,
> [ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ErrorCleared] [bit] NULL ,
> [PowerOnHours] [bigint] NULL ,
> [TotalPowerOnHours] [bigint] NULL ,
> [MaxQuiesceTime] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [EnabledState] [int] NULL ,
> [OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [RequestedState] [int] NULL ,
> [EnabledDefault] [int] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [InstallDate] [datetime] NULL ,
> [Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [ToArchive] [bit] NULL ,
> [TimeOfCreation] [datetime] NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> Can you also post the definition of and trigger on
> DATAVIEW_Win32_CDROMDrive?
>
Here it is, this is the "bottom" trigger (triggers on views are nested
(cascaded) all the way up to the top level tabel):
create trigger [DATAVIEW_Win32_CDROMDrive-InsertTrigger] on
[DATAVIEW_Win32_CDROMDrive] instead of INSERT as
INSERT INTO DATAVIEW_CIM_CDROMDrive ("GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation") SELECT "GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation" FROM inserted
INSERT INTO Win32_CDROMDrive ("GUID", "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber") select guid, "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber" from inserted
Thanks,
SLE
Does the 8K/page (row) limit also involve views? We're trying to insert a
record into a view (via nested instead of insert triggers) and the query
results in:
Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line 1
String or binary data would be truncated.
The statement has been terminated.
The view is made up of tables of which none are exceeding the 8K limiet. The
query does not pass [n][var]char values bigger than defined on table level.
Any clues?
Thanks,
SLE"SLE" <info@.NOSPAM.dataworx.be> wrote...
> Does the 8K/page (row) limit also involve views? We're trying to insert a
> record into a view (via nested instead of insert triggers) and the query
> results in:
> Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line
1
> String or binary data would be truncated.
> The statement has been terminated.
> The view is made up of tables of which none are exceeding the 8K limiet.
The
> query does not pass [n][var]char values bigger than defined on table
level.
>
FYI:
Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
insert (and all columns are populated correctly) but we don't feel quite
comfortable yet :-|
I've checked books online: there are no aggregate functions involved, nor
binary or varbinary data, nor distributed queries.
Any thoughts?
Thanks,
SLE|||The error indicates that you _are_ passing character values that are larger
than the columns they are stored in. If the row size was the problem you
would get a different error message. Are you not accidentally passing a char
to a varchar column? I assume you have checked your code already, but the
only explanation I can give at this moment is that you have missed
something. If you're sure you haven't can you please post your code so we
can have a look through it?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:OqGhJqOrDHA.2688@.TK2MSFTNGP09.phx.gbl...
> "SLE" <info@.NOSPAM.dataworx.be> wrote...
> >
> > Does the 8K/page (row) limit also involve views? We're trying to insert
a
> > record into a view (via nested instead of insert triggers) and the query
> > results in:
> >
> > Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD,
Line
> 1
> > String or binary data would be truncated.
> > The statement has been terminated.
> >
> > The view is made up of tables of which none are exceeding the 8K limiet.
> The
> > query does not pass [n][var]char values bigger than defined on table
> level.
> >
> FYI:
> Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
> insert (and all columns are populated correctly) but we don't feel quite
> comfortable yet :-|
> I've checked books online: there are no aggregate functions involved, nor
> binary or varbinary data, nor distributed queries.
> Any thoughts?
> Thanks,
> SLE
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> The error indicates that you _are_ passing character values that are
larger
> than the columns they are stored in. If the row size was the problem you
> would get a different error message. Are you not accidentally passing a
char
> to a varchar column? I assume you have checked your code already, but the
> only explanation I can give at this moment is that you have missed
> something. If you're sure you haven't can you please post your code so we
> can have a look through it?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Okay, a bit lengthy, but here we go. From Profiler:
SET ANSI_WARNINGS OFF
exec sp_executesql
N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
[DriveIntegrity], [FileSystemFlags],
[FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
[MediaLoaded], [MediaType],
[MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
[SCSIPort], [SCSITargetId],
[Size], [TransferRate], [VolumeName], [VolumeSerialNumber], [Capabilities],
[CapabilityDescriptions],
[ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
[MaxMediaSize], [DefaultBlockSize],
[MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
[Security], [LastCleaned],
[MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
[MountCount], [TimeOfLastMount],
[TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning], [UnitsUsed],
[PowerManagementCapabilities],
[OtherIdentifyingInfo], [IdentifyingDescriptions], [AdditionalAvailability],
[SystemCreationClassName],
[SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
[Availability], [StatusInfo],
[LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
[TotalPowerOnHours], [MaxQuiesceTime],
[EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
[OperationalStatus],
[StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
[Description], [ElementName],
[LastModifiedBy], [ToArchive], [TimeOfCreation])
VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
@.FileSystemFlagsExValue, @.IdValue,
@.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
@.MfrAssignedRevisionLevelValue,
@.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
@.SCSITargetIdValue,
@.SizeValue, @.TransferRateValue, @.VolumeNameValue, @.VolumeSerialNumberValue,
@.CapabilitiesValue,
@.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
@.CompressionMethodValue,
@.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
@.MaxBlockSizeValue,
@.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
@.LastCleanedValue, @.MaxAccessTimeValue,
@.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
@.MountCountValue, @.TimeOfLastMountValue,
@.TotalMountTimeValue, @.UnitsDescriptionValue, @.MaxUnitsBeforeCleaningValue,
@.UnitsUsedValue,
@.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
@.IdentifyingDescriptionsValue,
@.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
@.SystemNameValue, @.CreationClassNameValue,
@.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
@.LastErrorCodeValue, @.ErrorDescriptionValue,
0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
@.EnabledStateValue,
@.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
@.OperationalStatusValue,
@.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
@.DescriptionValue,
@.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
int,@.FileSystemFlagsExValue bigint,
@.IdValue varchar(2),@.ManufacturerValue
varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue varchar(6),
@.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
varchar(8000),@.SCSIBusValue bigint,
@.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
int,@.SizeValue bigint,
@.TransferRateValue float,@.VolumeNameValue
varchar(8),@.VolumeSerialNumberValue varchar(8),
@.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
varchar(8000),@.ErrorMethodologyValue varchar(8000),
@.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
bigint,@.MaxMediaSizeValue bigint,
@.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
bigint,@.MediaIsLockedValue bigint,
@.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
bigint,@.UncompressedDataRateValue bigint,
@.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
bigint,@.TimeOfLastMountValue datetime,
@.TotalMountTimeValue bigint,@.UnitsDescriptionValue
varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
@.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
@.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
varchar(8000),
@.SystemCreationClassNameValue varchar(20),@.SystemNameValue
varchar(6),@.CreationClassNameValue varchar(16),
@.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
int,@.LastErrorCodeValue bigint,
@.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
bigint,@.TotalPowerOnHoursValue bigint,
@.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
varchar(8000),
@.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
varchar(8000),
@.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
varchar(2),
@.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
varchar(8000),
@.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
@.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue = 0,
@.FileSystemFlagsExValue = 0,
@.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
@.MaximumComponentLengthValue = 110,
@.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
@.RevisionLevelValue = '',
@.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
@.SCSITargetIdValue = 0,
@.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
@.VolumeNameValue = 'BTS2004B',
@.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
@.CapabilityDescriptionsValue = NULL,
@.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
@.NumberOfMediaSupportedValue = 0,
@.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue = 0,
@.MinBlockSizeValue = 0,
@.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue = NULL,
@.MaxAccessTimeValue = NULL,
@.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue =NULL, @.MountCountValue = NULL,
@.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
@.UnitsDescriptionValue = NULL,
@.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
@.PowerManagementCapabilitiesValue = NULL,
@.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
@.AdditionalAvailabilityValue = NULL,
@.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue ='WS9843',
@.CreationClassNameValue = 'Win32_CDROMDrive',
@.DeviceIDValue ='IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
0',
@.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
@.ErrorDescriptionValue = '',
@.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
@.MaxQuiesceTimeValue = NULL,
@.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
@.RequestedStateValue = NULL,
@.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
@.StatusDescriptionsValue = NULL,
@.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK', @.CaptionValue
= 'HL-DT-ST DVD-ROM GDR8161B',
@.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
@.LastModifiedByValue = 'DOSIM000\SIDLECS',
@.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
And these are the affected tables contained within the view (from bottom to
top):
CREATE TABLE [dbo].[Win32_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[DriveIntegrity] [bit] NULL ,
[FileSystemFlags] [int] NULL ,
[FileSystemFlagsEx] [bigint] NULL ,
[Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaximumComponentLength] [bigint] NULL ,
[MediaLoaded] [bit] NULL ,
[MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
NULL ,
[RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[SCSIBus] [bigint] NULL ,
[SCSILogicalUnit] [int] NULL ,
[SCSIPort] [int] NULL ,
[SCSITargetId] [int] NULL ,
[Size] [bigint] NULL ,
[TransferRate] [float] NULL ,
[VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[NumberOfMediaSupported] [bigint] NULL ,
[MaxMediaSize] [bigint] NULL ,
[DefaultBlockSize] [bigint] NULL ,
[MaxBlockSize] [bigint] NULL ,
[MinBlockSize] [bigint] NULL ,
[NeedsCleaning] [bit] NULL ,
[MediaIsLocked] [bit] NULL ,
[Security] [int] NULL ,
[LastCleaned] [datetime] NULL ,
[MaxAccessTime] [bigint] NULL ,
[UncompressedDataRate] [bigint] NULL ,
[LoadTime] [bigint] NULL ,
[UnloadTime] [bigint] NULL ,
[MountCount] [bigint] NULL ,
[TimeOfLastMount] [datetime] NULL ,
[TotalMountTime] [bigint] NULL ,
[UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaxUnitsBeforeCleaning] [bigint] NULL ,
[UnitsUsed] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[PowerManagementCapabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL
,
[SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[PowerManagementSupported] [bit] NULL ,
[Availability] [int] NULL ,
[StatusInfo] [int] NULL ,
[LastErrorCode] [bigint] NULL ,
[ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ErrorCleared] [bit] NULL ,
[PowerOnHours] [bigint] NULL ,
[TotalPowerOnHours] [bigint] NULL ,
[MaxQuiesceTime] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[EnabledState] [int] NULL ,
[OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[RequestedState] [int] NULL ,
[EnabledDefault] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[InstallDate] [datetime] NULL ,
[Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
[Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[ToArchive] [bit] NULL ,
[TimeOfCreation] [datetime] NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]|||Can you also post the definition of and trigger on
DATAVIEW_Win32_CDROMDrive?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:%23J0t3FQrDHA.648@.TK2MSFTNGP11.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> > The error indicates that you _are_ passing character values that are
> larger
> > than the columns they are stored in. If the row size was the problem you
> > would get a different error message. Are you not accidentally passing a
> char
> > to a varchar column? I assume you have checked your code already, but
the
> > only explanation I can give at this moment is that you have missed
> > something. If you're sure you haven't can you please post your code so
we
> > can have a look through it?
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> Okay, a bit lengthy, but here we go. From Profiler:
> SET ANSI_WARNINGS OFF
> exec sp_executesql
> N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
> [DriveIntegrity], [FileSystemFlags],
> [FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
> [MediaLoaded], [MediaType],
> [MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
> [SCSIPort], [SCSITargetId],
> [Size], [TransferRate], [VolumeName], [VolumeSerialNumber],
[Capabilities],
> [CapabilityDescriptions],
> [ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
> [MaxMediaSize], [DefaultBlockSize],
> [MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
> [Security], [LastCleaned],
> [MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
> [MountCount], [TimeOfLastMount],
> [TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning],
[UnitsUsed],
> [PowerManagementCapabilities],
> [OtherIdentifyingInfo], [IdentifyingDescriptions],
[AdditionalAvailability],
> [SystemCreationClassName],
> [SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
> [Availability], [StatusInfo],
> [LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
> [TotalPowerOnHours], [MaxQuiesceTime],
> [EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
> [OperationalStatus],
> [StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
> [Description], [ElementName],
> [LastModifiedBy], [ToArchive], [TimeOfCreation])
> VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
> @.FileSystemFlagsExValue, @.IdValue,
> @.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
> @.MfrAssignedRevisionLevelValue,
> @.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
> @.SCSITargetIdValue,
> @.SizeValue, @.TransferRateValue, @.VolumeNameValue,
@.VolumeSerialNumberValue,
> @.CapabilitiesValue,
> @.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
> @.CompressionMethodValue,
> @.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
> @.MaxBlockSizeValue,
> @.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
> @.LastCleanedValue, @.MaxAccessTimeValue,
> @.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
> @.MountCountValue, @.TimeOfLastMountValue,
> @.TotalMountTimeValue, @.UnitsDescriptionValue,
@.MaxUnitsBeforeCleaningValue,
> @.UnitsUsedValue,
> @.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
> @.IdentifyingDescriptionsValue,
> @.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
> @.SystemNameValue, @.CreationClassNameValue,
> @.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
> @.LastErrorCodeValue, @.ErrorDescriptionValue,
> 0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
> @.EnabledStateValue,
> @.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
> @.OperationalStatusValue,
> @.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
> @.DescriptionValue,
> @.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
> N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
> int,@.FileSystemFlagsExValue bigint,
> @.IdValue varchar(2),@.ManufacturerValue
> varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue
varchar(6),
> @.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
> varchar(8000),@.SCSIBusValue bigint,
> @.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
> int,@.SizeValue bigint,
> @.TransferRateValue float,@.VolumeNameValue
> varchar(8),@.VolumeSerialNumberValue varchar(8),
> @.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
> varchar(8000),@.ErrorMethodologyValue varchar(8000),
> @.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
> bigint,@.MaxMediaSizeValue bigint,
> @.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
> bigint,@.MediaIsLockedValue bigint,
> @.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
> bigint,@.UncompressedDataRateValue bigint,
> @.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
> bigint,@.TimeOfLastMountValue datetime,
> @.TotalMountTimeValue bigint,@.UnitsDescriptionValue
> varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
> @.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
> varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
> @.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
> varchar(8000),
> @.SystemCreationClassNameValue varchar(20),@.SystemNameValue
> varchar(6),@.CreationClassNameValue varchar(16),
> @.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
> int,@.LastErrorCodeValue bigint,
> @.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
> bigint,@.TotalPowerOnHoursValue bigint,
> @.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
> varchar(8000),
> @.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
> varchar(8000),
> @.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
> varchar(2),
> @.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
> varchar(8000),
> @.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
> @.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue =0,
> @.FileSystemFlagsExValue = 0,
> @.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
> @.MaximumComponentLengthValue = 110,
> @.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
> @.RevisionLevelValue = '',
> @.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
> @.SCSITargetIdValue = 0,
> @.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
> @.VolumeNameValue = 'BTS2004B',
> @.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
> @.CapabilityDescriptionsValue = NULL,
> @.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
> @.NumberOfMediaSupportedValue = 0,
> @.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue =0,
> @.MinBlockSizeValue = 0,
> @.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue =NULL,
> @.MaxAccessTimeValue = NULL,
> @.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue
=> NULL, @.MountCountValue = NULL,
> @.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
> @.UnitsDescriptionValue = NULL,
> @.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
> @.PowerManagementCapabilitiesValue = NULL,
> @.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
> @.AdditionalAvailabilityValue = NULL,
> @.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue => 'WS9843',
> @.CreationClassNameValue = 'Win32_CDROMDrive',
> @.DeviceIDValue =>
'IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
> 0',
> @.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
> @.ErrorDescriptionValue = '',
> @.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
> @.MaxQuiesceTimeValue = NULL,
> @.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
> @.RequestedStateValue = NULL,
> @.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
> @.StatusDescriptionsValue = NULL,
> @.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK',
@.CaptionValue
> = 'HL-DT-ST DVD-ROM GDR8161B',
> @.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
> @.LastModifiedByValue = 'DOSIM000\SIDLECS',
> @.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
> And these are the affected tables contained within the view (from bottom
to
> top):
> CREATE TABLE [dbo].[Win32_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [DriveIntegrity] [bit] NULL ,
> [FileSystemFlags] [int] NULL ,
> [FileSystemFlagsEx] [bigint] NULL ,
> [Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaximumComponentLength] [bigint] NULL ,
> [MediaLoaded] [bit] NULL ,
> [MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
> NULL ,
> [RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [SCSIBus] [bigint] NULL ,
> [SCSILogicalUnit] [int] NULL ,
> [SCSIPort] [int] NULL ,
> [SCSITargetId] [int] NULL ,
> [Size] [bigint] NULL ,
> [TransferRate] [float] NULL ,
> [VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [NumberOfMediaSupported] [bigint] NULL ,
> [MaxMediaSize] [bigint] NULL ,
> [DefaultBlockSize] [bigint] NULL ,
> [MaxBlockSize] [bigint] NULL ,
> [MinBlockSize] [bigint] NULL ,
> [NeedsCleaning] [bit] NULL ,
> [MediaIsLocked] [bit] NULL ,
> [Security] [int] NULL ,
> [LastCleaned] [datetime] NULL ,
> [MaxAccessTime] [bigint] NULL ,
> [UncompressedDataRate] [bigint] NULL ,
> [LoadTime] [bigint] NULL ,
> [UnloadTime] [bigint] NULL ,
> [MountCount] [bigint] NULL ,
> [TimeOfLastMount] [datetime] NULL ,
> [TotalMountTime] [bigint] NULL ,
> [UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaxUnitsBeforeCleaning] [bigint] NULL ,
> [UnitsUsed] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [PowerManagementCapabilities] [varchar] (1024) COLLATE
Latin1_General_CI_AS
> NULL ,
> [OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
> [IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
> NULL ,
> [AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [PowerManagementSupported] [bit] NULL ,
> [Availability] [int] NULL ,
> [StatusInfo] [int] NULL ,
> [LastErrorCode] [bigint] NULL ,
> [ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ErrorCleared] [bit] NULL ,
> [PowerOnHours] [bigint] NULL ,
> [TotalPowerOnHours] [bigint] NULL ,
> [MaxQuiesceTime] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [EnabledState] [int] NULL ,
> [OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [RequestedState] [int] NULL ,
> [EnabledDefault] [int] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [InstallDate] [datetime] NULL ,
> [Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [ToArchive] [bit] NULL ,
> [TimeOfCreation] [datetime] NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> Can you also post the definition of and trigger on
> DATAVIEW_Win32_CDROMDrive?
>
Here it is, this is the "bottom" trigger (triggers on views are nested
(cascaded) all the way up to the top level tabel):
create trigger [DATAVIEW_Win32_CDROMDrive-InsertTrigger] on
[DATAVIEW_Win32_CDROMDrive] instead of INSERT as
INSERT INTO DATAVIEW_CIM_CDROMDrive ("GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation") SELECT "GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation" FROM inserted
INSERT INTO Win32_CDROMDrive ("GUID", "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber") select guid, "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber" from inserted
Thanks,
SLE
Thursday, February 9, 2012
3rd party Data Administration Tools
I'm looking for a 3rd party tool to manage
disabling/enabling triggers. I evaluated Teratraw
Database Manager and it easily allows disabling/enabling
triggers via the gui but only 1 trigger at a time. I need
a tool that can disable/enable ALL triggers in a database
easily. (Similar to TOAD for Oracle).
Anyone know of such a management tool?
Thanks Rich
SQLExecMS from www.laplas-soft.com will help you
"Rich" <rstoll@.cadencenet.com> wrote in message
news:1c5701c5317f$dc1023a0$a601280a@.phx.gbl...
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
|||Would a stored procedure work for you?
"Rich" wrote:
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
>
|||
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
Database Workbench - www.upscene.com
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||Hello Rich,
TOAD for SQL Server is currently in freeware (http://www.toadsoft.com/toadsqlserve..._sqlserver.htm), but a -much- more feature-laden version will go into beta in June. Take a look at the current version and stay on the lookout for the beta announceme
nt for the big summer release.
Hope this helps,
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> I'm looking for a 3rd party tool to manage disabling/enabling
> triggers. I evaluated Teratraw Database Manager and it easily allows
> disabling/enabling triggers via the gui but only 1 trigger at a time.
> I need a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
>
disabling/enabling triggers. I evaluated Teratraw
Database Manager and it easily allows disabling/enabling
triggers via the gui but only 1 trigger at a time. I need
a tool that can disable/enable ALL triggers in a database
easily. (Similar to TOAD for Oracle).
Anyone know of such a management tool?
Thanks Rich
SQLExecMS from www.laplas-soft.com will help you
"Rich" <rstoll@.cadencenet.com> wrote in message
news:1c5701c5317f$dc1023a0$a601280a@.phx.gbl...
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
|||Would a stored procedure work for you?
"Rich" wrote:
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
>
|||
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
Database Workbench - www.upscene.com
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||Hello Rich,
TOAD for SQL Server is currently in freeware (http://www.toadsoft.com/toadsqlserve..._sqlserver.htm), but a -much- more feature-laden version will go into beta in June. Take a look at the current version and stay on the lookout for the beta announceme
nt for the big summer release.
Hope this helps,
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> I'm looking for a 3rd party tool to manage disabling/enabling
> triggers. I evaluated Teratraw Database Manager and it easily allows
> disabling/enabling triggers via the gui but only 1 trigger at a time.
> I need a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
>
Subscribe to:
Posts (Atom)