how to force a commit in a sp

I've a complex stored procedure, that makes a lot of insert, update,
delete and so on.

I would like to make some commits durint this sp, but of course they
are not "real" commit because who call the sp could decide for a
rollback.

But I know that this commit has to be real. In fact, the transaction
log grows really too much during the execution.

Is there a way to force a commit durint a sp ?

thank you very much!Alberto (iltrex@.libero.it) writes:
> I've a complex stored procedure, that makes a lot of insert, update,
> delete and so on.
> I would like to make some commits durint this sp, but of course they
> are not "real" commit because who call the sp could decide for a
> rollback.
> But I know that this commit has to be real. In fact, the transaction
> log grows really too much during the execution.
> Is there a way to force a commit durint a sp ?

WHILE @.@.trancount > 1
COMMIT TRANSACTION

But it would be a really bad thing to do. If the caller has started a
trasaction, he would get an error when you exit the procedure. (Unless
you are so deceivious that perform equally many BEGIN TRANSACTION.

A much better approach is to add to the beginning of the procedure:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

That assumes of course that there are no business requirements that
calls for your procedure being part of a transaction. If there is,
you will have to find other ways to address the transaction log growth.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> WHILE @.@.trancount > 1
> COMMIT TRANSACTION
> But it would be a really bad thing to do. If the caller has started a

I know. But the sp calculates data for a olap cube, and it does the
calculation in an incremental way (it can be interrupted at any time
without losing data). So you solution should be the one I'm looking
for. Now I'm going to try it!

thank you!|||Alberto (iltrex@.libero.it) writes:
>> WHILE @.@.trancount > 1
>> COMMIT TRANSACTION
>>
>> But it would be a really bad thing to do. If the caller has started a
> I know. But the sp calculates data for a olap cube, and it does the
> calculation in an incremental way (it can be interrupted at any time
> without losing data). So you solution should be the one I'm looking
> for. Now I'm going to try it!

Yeah, but the caller might have done something which cannot be
committed half-way. So I really recommend the other way:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

how to fire triggers during log shipping

Hi,
We are trying to impliment log shipping. During the log shipping the target database will have insert, update or delete trigger on some of it's table.
Can some one let me know will these trigger get fired during log shipping. if not the is there some way to fire these triggers.

Thanks,
manojTriggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||It means these triggers will never fire on secondary database.
is there any way I can make them fire.

Thanks

Originally posted by Satya
Triggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.|||Hi satya,

I need to explain you the scenario

We have two system with two seperate production database on two physicaly seperate servers. one of the production database is search intensive and the other is transaction intensive. There are few common tables in these two databases.

As the data in transaction intensive database changes we want to move this data to the search intensive database to keep in sync.

The client don't want replication as solution.

client is planning to implement the runtime Log shifting for failover database of Transaction intensive database.

So we want to take this opportunity to run triggers on this failover database to move data to search database. as this we think will keep the down time to zero.

any suggestions?

Regards
Manoj

Originally posted by Satya
Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.

How to fire a trigger without changing table data

I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try:

update MyTable
set
Col1 = 'x'
where
1 = 2

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John Smith" <nobody@.nowhere.com> wrote in message
news:1143510748_10105@.sp6iad.superfeed.net...
I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
> Try:
> update MyTable
> set
> Col1 = 'x'
> where
> 1 = 2

Thanks, but it doesn't work.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||> Thanks, but it doesn't work.

The script Tom posted works for me: Please expand on what you mean by 'it
doesn't work'.

CREATE TABLE MyTable(Col1 int)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE AS
PRINT 'Trigger fired'
GO

UPDATE MyTable
SET Col1 = 'x'
WHERE 1 = 2
GO

DROP TABLE MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Smith" <nobody@.nowhere.com> wrote in message
news:1143512881_10135@.sp6iad.superfeed.net...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
>> Try:
>>
>> update MyTable
>> set
>> Col1 = 'x'
>> where
>> 1 = 2
> Thanks, but it doesn't work.
>
> --== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:_s1Wf.9738$tN3.2012@.newssvr27.news.prodigy.ne t...
>> Thanks, but it doesn't work.
> The script Tom posted works for me: Please expand on what you mean by 'it
> doesn't work'.

Thanks for the help. The problem was due to NULL values in some columns.

The trigger was firing but not changing data.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||>>The trigger was firing but not changing data.

Thats what your question says

Madhivanan

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
--
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find that the table column exists through program

i need to check that the column exist in the table if yes the update/insert value in the column else i need to add the new column like new browser name in the table.

after the search i found some thing like and make the procedure like

Dim daAs SqlDataAdapter, dsAs DataSet, dcAs DataColumn, foundsAsBoolean

Try

Conn.Open()

cmd =New SqlCommand(str, Conn)

da =New SqlDataAdapter(cmd)

ds =New DataSet

da.Fill(ds,"tbls")

ForEach dcIn ds.Tables(0).Columns

If UCase(colnames) = UCase(dc.ColumnName)Then

founds =True

ExitFor

Else

founds =False

EndIf

Next

Catch exAs Exception

Finally

Conn.Close()

EndTry

Return founds

sugesstions on this is required.................

How to filter chart with a bit value

I have a Report which gets several data which I want exactly.

And I want to insert a chart too.

But I want it to be filtered by one of the column in my dataset which is a "bit" column.

I tried 1,true,True but didn't worked.

Anyone knows how to??

Thanks

Bit is used to store 1 or 0 to represent Boolean True or False. Hope this helps.