long time process - what's the progress?

Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
--
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table> SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
--
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
>> How about below?
>> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL

long time process - what's the progress?

Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...n">
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table>
SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half
an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL

long time process - what's the progress?

Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PL
Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL
|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL

Logins across Linked Servers

Hi.

I wish to be bale to call a proc on a linked server but i am having difficulty with logins. I have a sql 2000 server linked to a sql 2005 server. To be able to call the proc on 2005 a login for the user must exist on both 2000 and 2005, and the login on 2005 must have access to the db and 'grant execute' on the proc.

Is there a way to allow access to the 2005 db without having to perform maintenance of the logins to ensure they match?

I know i could just have 1 login on 2005 that all users on 2000 are mapped to but this means new users added would have to also be mapped in the future.

Is it possible to call the stored proc on 2000 by impersonating a login that exists on both servers? Something similar to 'EXECUTE AS LOGIN = 'linklogin' in 2005? This would mean all users on 2000 could impersonate this one login and after this ine login is set up no further maintenance is required.

Thanks

Ewen

Yes, you can set up a more or less "permanent" login (read more at the link below) but that's normally a very bad idea. What it forces you to do is to take a less granular approach to managing your security. The temptation is that you will grant far more permissions than necessary to too many people - which violates the principal of "least rights", where you only grant what is necessary for the time.

It is a bit of a pain to manage two accounts. But if you'll use Windows security, you won't have to track passwords. Once you've set up your object security, you're essentially done - and safe!

http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx

Logins across Linked Servers

Hi.

I wish to be bale to call a proc on a linked server but i am having difficulty with logins. I have a sql 2000 server linked to a sql 2005 server. To be able to call the proc on 2005 a login for the user must exist on both 2000 and 2005, and the login on 2005 must have access to the db and 'grant execute' on the proc.

Is there a way to allow access to the 2005 db without having to perform maintenance of the logins to ensure they match?

I know i could just have 1 login on 2005 that all users on 2000 are mapped to but this means new users added would have to also be mapped in the future.

Is it possible to call the stored proc on 2000 by impersonating a login that exists on both servers? Something similar to 'EXECUTE AS LOGIN = 'linklogin' in 2005? This would mean all users on 2000 could impersonate this one login and after this ine login is set up no further maintenance is required.

Thanks

Ewen

Yes, you can set up a more or less "permanent" login (read more at the link below) but that's normally a very bad idea. What it forces you to do is to take a less granular approach to managing your security. The temptation is that you will grant far more permissions than necessary to too many people - which violates the principal of "least rights", where you only grant what is necessary for the time.

It is a bit of a pain to manage two accounts. But if you'll use Windows security, you won't have to track passwords. Once you've set up your object security, you're essentially done - and safe!

http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx