Can''t get at first row of input buffer...why?!

Hi

A script component receives some input. But I just can't get at the first row?

Basically, if i use the NextRow method in the in the Do statement, then it advances the row collection to the second row before it gets into the code inside the loop? BUT, if I use the EndOfRowset property to define my loop then I get an error:

[PipelineBuffer has encountered an invalid row index value]

I'm guessing this means...I have to call NextRow before i access the data in the collection? But thats retarted because then I miss the first row? what? What am I missing?

This is the code which works but I miss the first row:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strConcept As String

Do While Row.NextRow()

strConcept = Row.concept

updateDb(strConcept)

Loop

End Sub

This is the code which throws the invalid row index error:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strConcept As String

Do While Not Row.EndOfRowSet()

strConcept = Row.concept

updateDb(strConcept)

Row.NextRow()

Loop

End Sub

I've put some try catches in there an the error happens on the line which calls Row.concept....?

Can anyone help, it must be something I'm messing up

thanks!!

andy
You can't advance to the next row because the next row is fed in by the data flow.

What are you trying to do?

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) is executed for every row passing through the script component.|||hey, thanks for the quick reply.

I have to say I don't really know about the internal workings of things...I'm new to SSIS, SQLServer and .NET...BUT, what I do know is that within that Sub I can in fact advance through the rows.....I do this like so:

Do While row.NextRow()

someVariable = Row.whatever_Input_Column_Ive_defined_in_the_components_input

Loop

If I use the above construct, then it does loop through the rows...NextRow() must return a False when there are no more rows and then the loop stops. Perfect...except I miss the first Row...? I'm assuming this is because NextRow is called before i even get into the loop....?

I must be doing something wrong....but what? Thanks!!

andy
|||But what are you trying to do? You don't need to call NextRow because the next row will be provided automatically. You can setup a variable outside of the sub and then inside the sub populate that variable as you see fit.|||oh, sorry Phil, I'm a bit confused.

I have a bunch of rows coming into this script component....in fact I have 6 rows. I just want to access each column of each row and within each iteration I want to do a SQL INSERT.

Now, I'm not entirely sure what you mean, but are you saying I don't need to even do I DO LOOP block?

What I do know is that I have tried to access each Row without calling NextRow but its throws that error:

[PipelineBuffer has encountered an invalid row index value]

?

Thanks...sorry if I'm not getting what you're saying...you might need to spell it out

much appreciated!

|||

andyrose wrote:

oh, sorry Phil, I'm a bit confused.

I have a bunch of rows coming into this script component....in fact I have 6 rows. I just want to access each column of each row and within each iteration I want to do a SQL INSERT.

Why not just use an OLE DB Destination, instead of using a script, if this is what you want to do?|||ahhh...hold on, I think I get it...so SSIS calls that Input0_ProcessInpoutRow sub as many times as there are rows?

So it calls all the code in there...say...in my case, 6 times? But its thats kinda expensive....all this reduntant code...like connecting to a db is been called without need...?

am I right?
|||well...I lied a little. I kinda have to use a script just because within each interation I'm actually having to do some checks, which involve a load of other code...and only if I get a certain result does that row get added...so yeah, thats why I'm doing it in script
|||

andyrose wrote:

ahhh...hold on, I think I get it...so SSIS calls that Input0_ProcessInpoutRow sub as many times as there are rows?

So it calls all the code in there...say...in my case, 6 times? But its thats kinda expensive....all this reduntant code...like connecting to a db is been called without need...?

am I right?

Correct, and hence the question as to why you want to even use a script. SSIS comes with prebuilt destination components that will perform inserts for you.|||

andyrose wrote:

well...I lied a little. I kinda have to use a script just because within each interation I'm actually having to do some checks, which involve a load of other code...and only if I get a certain result does that row get added...so yeah, thats why I'm doing it in script

And these checks can't be done with lookup components and/or derived columns?|||yeah maybe....truth is I've been thrown this SSIS thing without ever even knwoing this stuff existed...I've never used anything like it...

Its new to me, so I only know how to do stuff in code. I am learning, and SSIS is very cool, but I've got to get this done quickly and so do it I usually jump in the code...but yeah, I have used all the other components for simple stuff...its just when it has to do loads of checks here and there I just write the code and it gets done.
|||

andyrose wrote:

Its new to me, so I only know how to do stuff in code. I am learning, and SSIS is very cool, but I've got to get this done quickly and so do it I usually jump in the code...but yeah, I have used all the other components for simple stuff...its just when it has to do loads of checks here and there I just write the code and it gets done.

Ah, okay, well, good luck! I hope I've helped you understand the script component. There's also a ProcessInput sub that actually passes each row to the ProcessInputRow sub. This is all in the books online though should you need a reference.|||

The script component tries to simplify things for you, so it builds the logic to loop through the rows in the buffer for you, and calls the ProcessInputRow method for you. If you want to see the code that's used, open your script component, click the design the script button to open VSA, and open the project explorer. The BufferWrapper code shows how they are putting friendly names for the column values, and the ComponentWrapper code shows how they are iterating the buffer. This probably goes without saying, but you shouldn't modify the code in either of these.

|||cool, thanks for your help phil....tried that but now am getting a validation VS_ISBROKEN error...though I guess thats whole other thread...thanks for your help!
|||ah yeah, had a look, thanks jwelch. My experience is in classic ASP and VB so its weird to have .NET do everything for you...well not everything, but yeah, cool, thanks for the heads up!

Cant delete single record? HELP!

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIAAny triggers on the table?|||Any triggers on the table?
No, no triggers on the table.|||Any triggles on this table?
No triggles either ;)|||what does the execution plan looks like for the delete statement?

Are there foreign key constraints on Invoices table ?
run something similar to the following:

select object_name(constid) as FkeyName,
object_name(fkeyid) as DependentTable from sysforeignkeys where rkeyid=object_id('Invoices')

Is anything locking Invoices table when you try the delete?

simas|||It won't even pull up an estimated execution plan. It just sits there doing nothing. There are several foreign keys, but I'm deleting from those tables prior to this delete, and they all delete without a hitch. It's just this table. I don't see anything locking the Invoices table.|||Have you ran a DBCC CHECKDB to see if there are any integrity issues?|||use <Your_Database_Name>
go
dbcc opentran
go

You have to have an open transaction that references this table. Oh, and do KILL your DELETE before you issue the DBCC. Make sure that any rollback (doubt it) completes before you fire DBCC.|||CHECKDB found 0 allocation errors and 0 consistency errors in database 'Accounting'.

Nice thought though.|||This is trying to pull a rabit out of the hat, but try to run UPDATE STATISTICS on the table. Also, try to drop and recreate the PRIMARY KEY index on that table. Then, see if you can delete the rows.

Also, I don't know why in the world I didn't suggest this earlier. In Query Analyzer before you do anything else, run: SELECT @.@.SPID. Set up a profiler to capture all activity using that SPID as a filter. In particular, look for SQL:Batch Started; SQL:Batch Completed; all errors, all recompiles, all locks and blocks; RPC:Started; RPC:Completed. See if you can see anything in the profiler that would lead to this problem.

Also, when this is running, if you run this in Query Analyzer do you get any results?

SELECT * FROM master..sysprocesses WHERE blocked <> 0|||UPDATE: DELETING THE PRIMARY KEY AND RECREATING IT WORKED. (no comment on how bad I think it is that this should ever have to be done, but it's fixed so I won't complain)

Mad Thanks go out to those that helped me out with this!

Special Thanks to Derrick! :D|||And you're sure you're on the latest service packs, critical updates, etc for Windows and SQL Server? Make sure the machine that has Query Analyzer on it has also been updated.|||SP3a on both, I'll have to check on the critical updates (since my boss thinks the internet is a fad). Thanks again for everything!

Cant Delete Row

I ran into a scenerio this morning

I made a table with no relations or primary id

It is an error log table basically

I went to delete a row and I got an error of

Cant delete row because it does not make them unigue or it alters multiple rows(3)

I didnt want this by no means, what did i not sdet right and how do you delete the rows without deleting the table and starting out new again?

Davids Learning

hi,

you'll often see this kind of problems on a table without a primary key... try defining one..

regards

|||

It wouldnt let me do that, so I deleted the table and started again(no big loss), but I did make a primary this time!

Thanks

Davids Learning

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
Ragnar
One method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>
|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I have a table in an SQL Server 2000 table that has no primary key, in
>> fact no indexes at all. (I didn't design this (:-)) )
>> I found that there is a duplicate row in the table but when I try to
>> delete it I get an error message:
>> "Key column information insufficient or incomplete. Too many rows were
>> affected by this update."
>> I had tried adding a primary key index but of course I couldn't since
>> there is a duplicate row.
>> I also tried just opening the table in EM and editing the data in the
>> column I want as primary key for one of the duplicate rows so I could set
>> the primary key, but I got an error message that the transaction can't
>> start in firehose mode.
>> Is there a simple way I can get out of this catch22 box? The DB supports
>> a busy Web site, so I was hoping to avoid shutting down the site.
>> Any help would be appreciated.
>> Ragnar
>

can't delete a row?

I have transaction replication setup between publisher server and subscriber
server.
I tried to delete a row from publisher's table,
but it looks like it's running forever.
delete from sym_type where type_id=1
And I don't know what to troubleshoot. but if I do an insert, then it's
working. i also see the row being replicated to subscriber when I do an
insert.
insert into sym_type values (-3, 'test', null)
scraching my head hard. replication monitor show no errors.
kevin
Kevin,
sounds like a contention issue - you could use sp_who2 and look for blocking
(use dbcc inputbuffer to see the cause of the block).
If not that, then try running select * from sym_type (nolock) where
type_id=1 to see how many rows there are - it might be a big delete?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||no... there is only 10 rows in the table.
and I inserted a row type_id = 1 to test replication, which it works
fine.
So I tried to delete the same row to rollback the change, but then it failed
to delete the row.
I think it maybe has to do with replication.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OgpkHnn1FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Kevin,
> sounds like a contention issue - you could use sp_who2 and look for
> blocking (use dbcc inputbuffer to see the cause of the block).
> If not that, then try running select * from sym_type (nolock) where
> type_id=1 to see how many rows there are - it might be a big delete?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin - what about the blocking results from sp_who2 - see any?
Paul Ibison
|||yes, sp_who2 shows BlockBy - spid 60.
Command delete.
interesting huh? why can't I delete it?
I even did dbcc checktable, nothing wrong.
so i go to spid 60
it's command "delete from sym_type where type_id =1"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGrJB6n1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> Kevin - what about the blocking results from sp_who2 - see any?
> Paul Ibison
>
|||Kevin,
strange - what's the spid/command command that is blocked by spid 60 - are
you saying it is another spid doing exactly the same command?
Paul
|||no.. there is [BlockBy] column in sp_who2 recordset,
there is value 60
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OuuDpro1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Kevin,
> strange - what's the spid/command command that is blocked by spid 60 - are
> you saying it is another spid doing exactly the same command?
> Paul
>
|||I'm still a little confused here You have 2 processes, and one blocks the
other. If I understand properly the one which is causing the block is the
delete, and the other one is blocked by the delete. If so, I agree that this
is strange and is the opposite of what I'd have expected. I suppose you
don't have some cunning triggers at work, or cascade deletes in action? -
worth checking out. DBCC OPENTRAN might also reveal something more but I
doubt it (still, worth a try). In your case I'd kill the delete spid, then
confirm that there's no blocking at all, and no open transactions. After
that, do a corresponding select, or perhaps just run the delete again if
it's ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||oh... I think I do have cascade delete in action.
my primary table is sym_type, foreign key table is portfolio_position ,
symbol_id is the key on both tables.
- it's enforce relationship for replication.
- it's enforce relationship for INSERTS and DELETES
it's cascading update related fields.
it's cascading delete related fields.
That must be it. when I do INSERT statement, there is no problem.
but when I do update or delete statement, query runs forever.
The next question is, why is this happen? I'm inserting a new symbol_id
which doesn't exist in foreign table,
why would this become a problem?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNLDCN2FHA.2444@.TK2MSFTNGP10.phx.gbl...
> I'm still a little confused here You have 2 processes, and one blocks
> the other. If I understand properly the one which is causing the block is
> the delete, and the other one is blocked by the delete. If so, I agree
> that this is strange and is the opposite of what I'd have expected. I
> suppose you don't have some cunning triggers at work, or cascade deletes
> in action? - worth checking out. DBCC OPENTRAN might also reveal something
> more but I doubt it (still, worth a try). In your case I'd kill the delete
> spid, then confirm that there's no blocking at all, and no open
> transactions. After that, do a corresponding select, or perhaps just run
> the delete again if it's ok.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin,
is there a self join, or a circular-relationship between the tables perhaps?
Do you see the same behaviour in the absence of replication (if this is
possible to test). Actually you could DTS the tables to a test server and
test there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

can't delete a row from primary table - is there an SP for this?

Hello,
I copied a database from our live server to a test server where I could
study the database, experiment and so on. I cannot delete/remove a row from
the primary table. The table had several relationships, dozens of triggers
,
a constraint, and was under replication on the live server.
I have removed all the relationships, triggers, constraints from the table
on the test server, and I do not have replication running on the test server
and there is no replication running on the database in the test server.
I did copy the contents of the primary table to a temp table and was able to
delete the row from the temp table. But I can't delete from the primary
table. Is there a property somewhere in the database that I need to look at
?
How come I can't delete from this table but I was able to delete from the
temp table? Is there an SP I can run to fix this or check what is going on?
Thanks,
RichCan you post the DELETE statement and the error message?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:ED97EF6C-6223-457C-A668-7BE409CAE593@.microsoft.com...
> Hello,
> I copied a database from our live server to a test server where I could
> study the database, experiment and so on. I cannot delete/remove a row fr
om
> the primary table. The table had several relationships, dozens of trigge
rs,
> a constraint, and was under replication on the live server.
> I have removed all the relationships, triggers, constraints from the table
> on the test server, and I do not have replication running on the test serv
er
> and there is no replication running on the database in the test server.
> I did copy the contents of the primary table to a temp table and was able
to
> delete the row from the temp table. But I can't delete from the primary
> table. Is there a property somewhere in the database that I need to look
at?
> How come I can't delete from this table but I was able to delete from the
> temp table? Is there an SP I can run to fix this or check what is going o
n?
> Thanks,
> Rich|||Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE,
DELETE
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:ED97EF6C-6223-457C-A668-7BE409CAE593@.microsoft.com...
> Hello,
> I copied a database from our live server to a test server where I could
> study the database, experiment and so on. I cannot delete/remove a row
> from
> the primary table. The table had several relationships, dozens of
> triggers,
> a constraint, and was under replication on the live server.
> I have removed all the relationships, triggers, constraints from the table
> on the test server, and I do not have replication running on the test
> server
> and there is no replication running on the database in the test server.
> I did copy the contents of the primary table to a temp table and was able
> to
> delete the row from the temp table. But I can't delete from the primary
> table. Is there a property somewhere in the database that I need to look
> at?
> How come I can't delete from this table but I was able to delete from the
> temp table? Is there an SP I can run to fix this or check what is going
> on?
> Thanks,
> Rich|||Jeff,
As of 7.0 you can have several triggers of the same type on a table. And as
of 2000, you can define
which to fire first and which to fire last.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jeff Dillon" <jeffdillon@.hotmail.com> wrote in message
news:OqFSPqreGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE, DE
LETE
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:ED97EF6C-6223-457C-A668-7BE409CAE593@.microsoft.com...
>

can't delete a row from primary table - is there an SP for thi

Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
updates and there was a trigger to rollback deletes. But I commented out al
l
of the triggers - all of them. So there are basically no triggers except fo
r
t_sometinging on PrimaryTbl
For Insert...
As
/* */
Return
I did that to all of the triggers so that nothing would fire. Right now I
am recreating the table with all the triggers, indexes, relationships,
constraints and so on and see if that table works (minus of course, the For
Delte As Rollback...)
"Tibor Karaszi" wrote:

> Jeff,
> As of 7.0 you can have several triggers of the same type on a table. And a
s of 2000, you can define
> which to fire first and which to fire last.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jeff Dillon" <jeffdillon@.hotmail.com> wrote in message
> news:OqFSPqreGHA.5040@.TK2MSFTNGP03.phx.gbl...
>
>A much easier way to disable triggers, that doesn't involve having to change
code, is to use ALTER TABLE ... DISABLE TRIGGER
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
> all
> of the triggers - all of them. So there are basically no triggers except
> for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the
> For
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||recreating the table seemed to do the trick. I was able to delete the row
from the re-created table.
Recreating the live table will be a bigger hassel because I will have to
stop replication first.
"Rich" wrote:
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
all
> of the triggers - all of them. So there are basically no triggers except
for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the Fo
r
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||yes, I have used that before, but I just needed to make sure there was
nothing in the triggers that could possibly run.
Well, as fate would have it, after I recreated my table and was able to
delete the desired row, I then readded the row and also re-added all the
triggers, relationships, etc. Now I can't delete the row again in the new
table. So I guess maybe there was nothing wrong with the original table.
So tommorrow I have to play the boring game of recreating the table, add the
row, delete the row, and keep adding triggers one at a time and relationship
s
until I can't delete the row and thus isolate the offending procedure,
relationship. I am already having indigestion thinking about it.
"Kalen Delaney" wrote:

> A much easier way to disable triggers, that doesn't involve having to chan
ge
> code, is to use ALTER TABLE ... DISABLE TRIGGER
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
>
>|||Well, I figured out what the problem was in deleteing rows on my table. It
was the Instead Of Delete trigger. Even though I had commented out the body
and only had
Create Trigger...
Instead Of Delete
As
--
--
Return
This would not allow deletes on the table. When I removed the trigger
entirely, I was able to delete rows.
"Rich" wrote:
> yes, I have used that before, but I just needed to make sure there was
> nothing in the triggers that could possibly run.
> Well, as fate would have it, after I recreated my table and was able to
> delete the desired row, I then readded the row and also re-added all the
> triggers, relationships, etc. Now I can't delete the row again in the new
> table. So I guess maybe there was nothing wrong with the original table.
> So tommorrow I have to play the boring game of recreating the table, add t
he
> row, delete the row, and keep adding triggers one at a time and relationsh
ips
> until I can't delete the row and thus isolate the offending procedure,
> relationship. I am already having indigestion thinking about it.
> "Kalen Delaney" wrote:
>|||I think that is because the code in the trigger runs in place of your delete
statement (on the rows affected by it). Since there was no code, and no
action to perform, nothing was done with these rows.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:92D437D9-CFDB-45F4-8991-90C49BBFD1E7@.microsoft.com...
> Well, I figured out what the problem was in deleteing rows on my table.
It
> was the Instead Of Delete trigger. Even though I had commented out the
body
> and only had
> Create Trigger...
> Instead Of Delete
> As
> --
> --
> Return
> This would not allow deletes on the table. When I removed the trigger
> entirely, I was able to delete rows.
> "Rich" wrote:
>
new
table.
the
relationships
change
some
commented out
except
now I
relationships,
the
table. And
UPDATE,
I
delete/remove a
of
the
test
was
need to
from
is

Can''t create a SQL Server package configuration

I've created the table [SSIS Configurations] and have added a dummy row to this with "XYZ" in the filter name.

When I use the package configuration wizard I can see that offered as a filter - so I know my connection is seeing the server ok.

The problem I get is when I get through to "Finish" and insert the configuration to the table - I get this error message and I can't find any help on it:

Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)

Could not complete wizard actions.

I'm connecting as system administrator and am just foxed as to why it won't insert ... Anyone got any pointers?

Many thanks

Matt

have you used SQL Profiler to watch what is being sent to the server?|||

*bump*

I am getting this error too.

Any ideas? Nothing odd in profiler.

|||

that is weird. If you are choosing a row that already exists in the configuration table; I think the wizard prompts you whether you wan to re-use the exiting row or not...do you see that prompt? what are you selecting?

|||

I've resolved my issue.

It was to do with a property value (an SQL string) that was too long to fit in the default configuration table data column (254 characters.)

I have moved to using an external xml file instead for the SQL statements.

I also tried making the column larger, but ultimately I think the more appropriate option for us is going to be external xml files.

Can't create a SQL Server package configuration

I've created the table [SSIS Configurations] and have added a dummy row to this with "XYZ" in the filter name.

When I use the package configuration wizard I can see that offered as a filter - so I know my connection is seeing the server ok.

The problem I get is when I get through to "Finish" and insert the configuration to the table - I get this error message and I can't find any help on it:

Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)

Could not complete wizard actions.

I'm connecting as system administrator and am just foxed as to why it won't insert ... Anyone got any pointers?

Many thanks

Matt

have you used SQL Profiler to watch what is being sent to the server?|||

*bump*

I am getting this error too.

Any ideas? Nothing odd in profiler.

|||

that is weird. If you are choosing a row that already exists in the configuration table; I think the wizard prompts you whether you wan to re-use the exiting row or not...do you see that prompt? what are you selecting?

|||

I've resolved my issue.

It was to do with a property value (an SQL string) that was too long to fit in the default configuration table data column (254 characters.)

I have moved to using an external xml file instead for the SQL statements.

I also tried making the column larger, but ultimately I think the more appropriate option for us is going to be external xml files.