how to show progress percent

I have a script file which do the replication and in the script there is a lot of table need to be addad as article.
So while use osql execute the script there would be cost a lot of time. What I want to do is show a dialog which could show the progress percent and the file name which is now being addad as article. when i use osql I just want whenever I execute a stat
ement the osql or the script could return a number!
hwo can i use Transact-SQL to do this?
Thanks a lot
Out of the box for standard initialization this is not possible. If you want
to monitor the standard process you could poll the relevant history table -
MSmerge_history for merge and MSdistribution_history for transactional and
snapshot and filter these results. If you are implementing your own script
to create the tables at the subscriber you could maintain your own counter
and increment it each time a table is added.
HTH,
Paul Ibison
|||the replication ActiveX controls have this functionality. Unfortunately you have to use the status event which is not accessible from a script.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" wrote:

> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||I could maintain my own counter and increment it but how can i return the counter to the outside when executed each one statement ?
In fact, now I want to build a setup program with InstallShield Profession and during the install, a replication should be done. The database which need to be replicated is very big. So I want to show a dialog which show the progress precent to improve th
e setup UI. It is not complex to show a dialog in InstallShield script. but I can not return the counter in the MSSQL script to the InstallShield script, when using the osql utility( like osql ... -i script file name ).
Could you like to give any suggestion?
Thanks a lot
"Paul Ibison" wrote:

> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||Lowiq,
you have a few choices. You can have many scripts and get a count by virtue
of the number of scripts processed. If InstallShield is multi-threaded
(don't know offhand), you can set off the osql script asynchronously. The
script would increment a counter in a table each time it adds a table,
populates a table etc. Your main execution thread would poll this counter
table to see the level of progress. Alternatively you could poll the
relevant history table although this would require a bit of complex
filtering. BTW nosync initializations can be a little restrictive as far as
future modifications are concerned.
HTH,
Paul Ibison
"lowiq" <lowiq@.discussions.microsoft.com> wrote in message
news:331E585D-28A2-473A-8B6D-EA38D2A841BA@.microsoft.com...
> I could maintain my own counter and increment it but how can i return the
counter to the outside when executed each one statement ?
> In fact, now I want to build a setup program with InstallShield Profession
and during the install, a replication should be done. The database which
need to be replicated is very big. So I want to show a dialog which show the
progress precent to improve the setup UI. It is not complex to show a dialog
in InstallShield script. but I can not return the counter in the MSSQL
script to the InstallShield script, when using the osql utility( like osql
... -i script file name ).[vbcol=seagreen]
> Could you like to give any suggestion?
> Thanks a lot
> "Paul Ibison" wrote:
want[vbcol=seagreen]
table -[vbcol=seagreen]
and[vbcol=seagreen]
script[vbcol=seagreen]
counter[vbcol=seagreen]

How to set up permissions for a user, who need to work with SQL Server Agent

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Owner can execute job as well.|||

Thanks for the hint, but I do not want to have this user as an owner. The owner should be another account. This user (developer) should only have the capability to execute jobs we (DBA) provided to him. He should not see ALL jobs nor should he execute ALL jobs. I would like to set permissions in a way that he can start/stop/enable/disable ONE specific job.

Is there an easy way to realize it? Or maybe a more complicated way?

Regards

Norbert

How to set up a login that should execute a specific SQL Server Agent job

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Has no one an idea?

Regards

Norbert

|||

I hope, that someone xould give me some hints if it is possible to realize the scenario or not.

Regards

Norbert

How to set up a login that should execute a specific SQL Server Agent job

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Has no one an idea?

Regards

Norbert

|||

I hope, that someone xould give me some hints if it is possible to realize the scenario or not.

Regards

Norbert

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set Concurrent execution of 2 insert statements

Hi,
I ahve the followign trigger. In this I want to set the 2 Insert statements
at the bottom, to execute concurrently. How can I do that?
CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
FOR INSERT
AS
DECLARE @.CompOrderNo VarChar(30)
DECLARE @.Sno int
DECLARE @.EndUser varchar(25)
DECLARE @.ESN varchar(20)
DECLARE @.MinNo varchar(12)
SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
inserted --where CompanyORderNo is null
UPDATE WyethDataCard
SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanySno
)
VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
INSERT INTO EndUserChangeHistory
(MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
VALUES ( @.CompOrderNo,
@.ESN,
@.MinNo,
@.EndUser,
'Database Import / Manual Insert',
GetDate()
)
Is there some statement like CONCURRENT EXEC to achieve this?
Thanks
pmudWhy do you want to make them concurrent? Even if the machine has several pro
cessors, there's no
guarantee that this will happen at the same time. If you want both or nothin
g, you should have some
error handling in the trigger (see the articles on error handling at www.sommarsko
g.se).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert statement
s
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanyS
no)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||Hi Tibor,
Actually, by concurrent execution, I meant parallel execution of the 2
statements. Is tehre any way I can set parallel execution fo teh 2 statement
s
rather than having one after the other?
Thanks
--
pmud
"Tibor Karaszi" wrote:

> Why do you want to make them concurrent? Even if the machine has several p
rocessors, there's no
> guarantee that this will happen at the same time. If you want both or noth
ing, you should have some
> error handling in the trigger (see the articles on error handling at www.sommars
kog.se).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>|||Do you mean that the inserts should be atomic or within a single
transaction?
If so, then refer to this document, specifically the section titled
"Database Transactions".
http://msdn.microsoft.com/library/d...ent.
asp
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus (
> Company_OrderNo,Status_ID,Created_Date,C
ompanySno)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||no
why?
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B0D56D29-D70C-4684-9F7F-0957CBFF6443@.microsoft.com...
> Hi Tibor,
> Actually, by concurrent execution, I meant parallel execution of the 2
> statements. Is tehre any way I can set parallel execution fo teh 2
> statements
> rather than having one after the other?
> Thanks
> --
> pmud
>
> "Tibor Karaszi" wrote:
>|||Is there any tutorial or book you rae aware which I can read to quickly come
up to speed with transctions and how they are implemeted?
Actually, I am aware of the basic concept of trnsctions but have never
implemeted them.
Do you have any suggestions for me to get up to speed Quickly?
Thanks
--
pmud
"JT" wrote:

> Do you mean that the inserts should be atomic or within a single
> transaction?
> If so, then refer to this document, specifically the section titled
> "Database Transactions".
> http://msdn.microsoft.com/library/d...en
t.asp
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>
>|||You will not need a book to understand the concept of basic transactions.
Database transaction:
http://en.wikipedia.org/wiki/Database_transaction
BEGIN TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
COMMIT TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
ROLLBACK TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
The simplified sample below attempts to transfer a balance of $100 between 2
accounts. If all goes well, the transaction is committed (saved) and the
value of 1 is returned. If there is an error, then the transaction is rolled
back (cancelled), and a value of -1 or -2 is returned. By executing both
updates within a transactions, we insure that the work is either all saved
or all cancelled and the accounts are balanced.
begin transaction
update Accounts set balance = balance - 100 where acct_number = 4625
if @.@.error <> 0
begin
rollback transaction
return -1
end
update Accounts set balance = balance + 100 where acct_number = 4650
if @.@.error <> 0
begin
rollback transaction
return -2
end
commit transaction
return 1
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
> Is there any tutorial or book you rae aware which I can read to quickly
> come
> up to speed with transctions and how they are implemeted?
> Actually, I am aware of the basic concept of trnsctions but have never
> implemeted them.
> Do you have any suggestions for me to get up to speed Quickly?
> Thanks
> --
> pmud
>
> "JT" wrote:
>|||Thanks JT. That is definitely helpful. :)
--
pmud
"JT" wrote:

> You will not need a book to understand the concept of basic transactions.
> Database transaction:
> http://en.wikipedia.org/wiki/Database_transaction
> BEGIN TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
> COMMIT TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
> ROLLBACK TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
> The simplified sample below attempts to transfer a balance of $100 between
2
> accounts. If all goes well, the transaction is committed (saved) and the
> value of 1 is returned. If there is an error, then the transaction is roll
ed
> back (cancelled), and a value of -1 or -2 is returned. By executing both
> updates within a transactions, we insure that the work is either all saved
> or all cancelled and the accounts are balanced.
> begin transaction
> update Accounts set balance = balance - 100 where acct_number = 4625
> if @.@.error <> 0
> begin
> rollback transaction
> return -1
> end
> update Accounts set balance = balance + 100 where acct_number = 4650
> if @.@.error <> 0
> begin
> rollback transaction
> return -2
> end
> commit transaction
> return 1
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
>
>|||pmud (pmud@.discussions.microsoft.com) writes:
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements at the bottom, to execute concurrently. How can I do that?
You can't, and there would be no point with it.
On the other hand:

> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
This is not going to work out well. Triggers in SQL Server fires
once *per statement*, so the inserted table can hold many rows, which
your trigger fails to handle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx