Encryption question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and check them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will change this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com...
> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, then
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this is
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>sql

Encryption question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and chec
k them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will chang
e this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com
..

quote:

---

> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, the
n
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this i
s
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>

---

Encryption and query performance

I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.

If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Thanks for the links. They supplied the information that I was afraid I'd find. In the example of using the MAC key, is there a benefit of having one column containing the encrypted SSN, if another column contains the MAC of the plaintext? That is, if the MAC of the plaintext is secure enough to be able to use for indexing, then are things more secure by separately encrypting the SSN with a symmetric key?

Two ideas that were proposed for our situation were:

1.) Encrypt the SSN and also store, let's say the last four of the SSN in plaintext. Our last four field would be included in the query condition. That would, in effect, reduce the amount of the index searched by a factor of 10000 (e.g search and decrypt only 1000 of the 10 million records)

2.) Create a separate table containing the plaintext SSN (indexed) and the encrypted value of the PK to the customer table. The SSN would be plaintext, but would not be linkable to any particular customer. Searches by SSN would use this table to retrieve the one row and then decrypt the encrypted value of the PK to link to the customer table. I'm not sure I like this idea, as the SSN are all visible, regardless of whether they can be linked to a particular person.

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

The HMAC cannot be used to retrieve the original data, hence the encrypted value needs to be stored as well. The HMAC is only used for the search, to leverage the index for retrieving the row. Once the row is retrieved, the data is extracted from the encrypted column value.

I would avoid both ideas, as both are disclosing information about your customers' SSNs. The solution we recommend is to index an HMAC of the SSN, instead of indexing portions of the unencrypted SSN. There is no significant overhead compared to the other two solutions, so why not implement this approach?

Thanks
Laurentiu

|||

Again, thanks for your reply. Your answer makes sense to me. I, too, was not real keen on leaving SSN (or parts of it) in plaintext, but I was running short on other ideas and short on time. I hope you'll forgive my ignorance when it comes to HMAC, actually when it comes to encryption in general. When I looked at Raul's sample code for implementing this solution, I'll have to admit I was rather intimidated by it. I wish I had more time to really digest the whole thing. I will re-read his blog and try to digest it completely. Are there any other places where I might be able to get a crash course on HMAC's?

Thanks,

Larry

|||One other question I have is regarding replication. This table is one that I am replicating to another database. If I create these same certificates and keys on both database, can I still search from database B on an indexed value that was created on database A?|||

Laurentiu,

The example you pointed me to in Raul's blog implements the encryption and the generation of the MAC in a trigger. All of our data access logic (INSERTs and UPDATEs) currently resides in stored procedures. What are benefits or drawbacks to implementing the encryption logic in triggers as opposed to the stored procedures that we already have?

|||

You can generate the HMAC in procedures as well. Raul's example shows how you can compute the HMACs without changing existing stored procedures - this is why he's doing the HMAC computations in a trigger. If changing the stored procedures is not an issue for you, then you can move the HMAC computation in their code.

Also, for HMAC generation, you can find out more by looking at a cryptography book or at online resources. The algorithm that Raul uses is to concatenate the original value (the sensitive data) with a secret value (@.key) and then to compute a SHA1 hash of the resulting concatenation - that is the HMAC of the original value:

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Encryption and query performance

I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.

If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Thanks for the links. They supplied the information that I was afraid I'd find. In the example of using the MAC key, is there a benefit of having one column containing the encrypted SSN, if another column contains the MAC of the plaintext? That is, if the MAC of the plaintext is secure enough to be able to use for indexing, then are things more secure by separately encrypting the SSN with a symmetric key?

Two ideas that were proposed for our situation were:

1.) Encrypt the SSN and also store, let's say the last four of the SSN in plaintext. Our last four field would be included in the query condition. That would, in effect, reduce the amount of the index searched by a factor of 10000 (e.g search and decrypt only 1000 of the 10 million records)

2.) Create a separate table containing the plaintext SSN (indexed) and the encrypted value of the PK to the customer table. The SSN would be plaintext, but would not be linkable to any particular customer. Searches by SSN would use this table to retrieve the one row and then decrypt the encrypted value of the PK to link to the customer table. I'm not sure I like this idea, as the SSN are all visible, regardless of whether they can be linked to a particular person.

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

The HMAC cannot be used to retrieve the original data, hence the encrypted value needs to be stored as well. The HMAC is only used for the search, to leverage the index for retrieving the row. Once the row is retrieved, the data is extracted from the encrypted column value.

I would avoid both ideas, as both are disclosing information about your customers' SSNs. The solution we recommend is to index an HMAC of the SSN, instead of indexing portions of the unencrypted SSN. There is no significant overhead compared to the other two solutions, so why not implement this approach?

Thanks
Laurentiu

|||

Again, thanks for your reply. Your answer makes sense to me. I, too, was not real keen on leaving SSN (or parts of it) in plaintext, but I was running short on other ideas and short on time. I hope you'll forgive my ignorance when it comes to HMAC, actually when it comes to encryption in general. When I looked at Raul's sample code for implementing this solution, I'll have to admit I was rather intimidated by it. I wish I had more time to really digest the whole thing. I will re-read his blog and try to digest it completely. Are there any other places where I might be able to get a crash course on HMAC's?

Thanks,

Larry

|||One other question I have is regarding replication. This table is one that I am replicating to another database. If I create these same certificates and keys on both database, can I still search from database B on an indexed value that was created on database A?|||

Laurentiu,

The example you pointed me to in Raul's blog implements the encryption and the generation of the MAC in a trigger. All of our data access logic (INSERTs and UPDATEs) currently resides in stored procedures. What are benefits or drawbacks to implementing the encryption logic in triggers as opposed to the stored procedures that we already have?

|||

You can generate the HMAC in procedures as well. Raul's example shows how you can compute the HMACs without changing existing stored procedures - this is why he's doing the HMAC computations in a trigger. If changing the stored procedures is not an issue for you, then you can move the HMAC computation in their code.

Also, for HMAC generation, you can find out more by looking at a cryptography book or at online resources. The algorithm that Raul uses is to concatenate the original value (the sensitive data) with a secret value (@.key) and then to compute a SHA1 hash of the resulting concatenation - that is the HMAC of the original value:

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Enabling E-Mail Delivery for RS

What is required in order to schedule a report for email delivery?
I have modified the RSReportServer.config file on SQL Server and created a
subscription for a report.
I can setup the subscription but it never runs.
Is there another step that I have left out?Carl,
Do you get any error messages in the Status column on the Subscriptions page
of report designer?
Andre
"Carl Meister" wrote:
> What is required in order to schedule a report for email delivery?
> I have modified the RSReportServer.config file on SQL Server and created a
> subscription for a report.
> I can setup the subscription but it never runs.
> Is there another step that I have left out?
>|||Andre,
On the subscriptions page for the report in the status column is "New
Subscription".
The Last Run column is empty.
I have this report scheduled to run every hour but it seems like it never
kicks off.
"Andre" wrote:
> Carl,
> Do you get any error messages in the Status column on the Subscriptions page
> of report designer?
> Andre
> "Carl Meister" wrote:
> > What is required in order to schedule a report for email delivery?
> >
> > I have modified the RSReportServer.config file on SQL Server and created a
> > subscription for a report.
> >
> > I can setup the subscription but it never runs.
> >
> > Is there another step that I have left out?
> >|||Hi Carl
I think the "SQL Agent" need to run, check this.
BR Per

Enabling AWE

Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
LeilaAt the OS level, you have to set the /PAE switch in BOOT.INI. That said,
your machine doesn't really have enough RAM. You don't want to give 100% of
the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
some before you turn on AWE.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
Leila|||That is NOT correct. AWE can be enabled without PAE. Using PAE allows the OS
to address more than 4 GB of memory. AWE is an API set that applications
(like SQL) can leverage to reserve chunks of memory for manipulation.
Since he only has 3.5 GB of memory, PAE would get him nothing. Also, SQL
will never be able to reserve all of the memory. The OS needs some, other
services, etc., will also use some memory.
"Tom Moreau" wrote:
> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100% of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>|||Does your boot.ini have the /3GB switch? If not, the operating system will
not be able to allocate all of your memory.
Also, I highly recommend NOT allowing SQL Server to consume *all* of the
memory. The OS needs some, too.
A
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||Thanks every body!
What's the exact parameter that I must add to boot.ini?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> Does your boot.ini have the /3GB switch? If not, the operating system
> will not be able to allocate all of your memory.
> Also, I highly recommend NOT allowing SQL Server to consume *all* of the
> memory. The OS needs some, too.
> A
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
>> Server 2005 EE to be able to use whole of memory if required.
>> I have already used "awe enabled" option with sp_configure to change the
>> run value to 1, but it seems other parameters must be configured on OS.
>> What's the easiest way to do it?
>> Many thanks in advance,
>> Leila
>|||/3GB
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:eH4iWF1BHHA.4992@.TK2MSFTNGP03.phx.gbl...
> Thanks every body!
> What's the exact parameter that I must add to boot.ini?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
>> Does your boot.ini have the /3GB switch? If not, the operating system
>> will not be able to allocate all of your memory.
>> Also, I highly recommend NOT allowing SQL Server to consume *all* of the
>> memory. The OS needs some, too.
>> A
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
>> Server 2005 EE to be able to use whole of memory if required.
>> I have already used "awe enabled" option with sp_configure to change the
>> run value to 1, but it seems other parameters must be configured on OS.
>> What's the easiest way to do it?
>> Many thanks in advance,
>> Leila
>>
>|||Run sp_configure to enable AWE, and set the max server memory. be sure to
leave some memory for the OS. Also, set the /PAE switch in BOOT.INI and
reboot.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
news:92951B59-BB92-47B3-A72F-C544D8DF01C1@.microsoft.com...
Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:
> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
> of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>

Enabling AWE

Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
LeilaAt the OS level, you have to set the /PAE switch in BOOT.INI. That said,
your machine doesn't really have enough RAM. You don't want to give 100% of
the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
some before you turn on AWE.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
Leila|||That is NOT correct. AWE can be enabled without PAE. Using PAE allows the OS
to address more than 4 GB of memory. AWE is an API set that applications
(like SQL) can leverage to reserve chunks of memory for manipulation.
Since he only has 3.5 GB of memory, PAE would get him nothing. Also, SQL
will never be able to reserve all of the memory. The OS needs some, other
services, etc., will also use some memory.
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the r
un
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>|||Does your boot.ini have the /3GB switch? If not, the operating system will
not be able to allocate all of your memory.
Also, I highly recommend NOT allowing SQL Server to consume *all* of the
memory. The OS needs some, too.
A
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>|||Thanks every body!
What's the exact parameter that I must add to boot.ini?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> Does your boot.ini have the /3GB switch? If not, the operating system
> will not be able to allocate all of your memory.
> Also, I highly recommend NOT allowing SQL Server to consume *all* of the
> memory. The OS needs some, too.
> A
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
>|||/3GB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:eH4iWF1BHHA.4992@.TK2MSFTNGP03.phx.gbl...
> Thanks every body!
> What's the exact parameter that I must add to boot.ini?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
>|||Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the r
un
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>|||Run sp_configure to enable AWE, and set the max server memory. be sure to
leave some memory for the OS. Also, set the /PAE switch in BOOT.INI and
reboot.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
news:92951B59-BB92-47B3-A72F-C544D8DF01C1@.microsoft.com...
Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
> of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>

Enabling AWE

Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
Leila
At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
your machine doesn't really have enough RAM. You don't want to give 100% of
the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
some before you turn on AWE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
Hi,
Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
Server 2005 EE to be able to use whole of memory if required.
I have already used "awe enabled" option with sp_configure to change the run
value to 1, but it seems other parameters must be configured on OS.
What's the easiest way to do it?
Many thanks in advance,
Leila
|||That is NOT correct. AWE can be enabled without PAE. Using PAE allows the OS
to address more than 4 GB of memory. AWE is an API set that applications
(like SQL) can leverage to reserve chunks of memory for manipulation.
Since he only has 3.5 GB of memory, PAE would get him nothing. Also, SQL
will never be able to reserve all of the memory. The OS needs some, other
services, etc., will also use some memory.
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100% of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>
|||Does your boot.ini have the /3GB switch? If not, the operating system will
not be able to allocate all of your memory.
Also, I highly recommend NOT allowing SQL Server to consume *all* of the
memory. The OS needs some, too.
A
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
|||You would be best served, UNTIL you get more memory, to use the /3GB switch.
The /3GB switch gives SQL Server up to 3 GB of memory. The OS requires
memory, so leaving the remaining 512 MB for the OS seems about right.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
|||Thanks every body!
What's the exact parameter that I must add to boot.ini?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
> Does your boot.ini have the /3GB switch? If not, the operating system
> will not be able to allocate all of your memory.
> Also, I highly recommend NOT allowing SQL Server to consume *all* of the
> memory. The OS needs some, too.
> A
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
>
|||/3GB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leila" <Leilas@.hotpop.com> wrote in message
news:eH4iWF1BHHA.4992@.TK2MSFTNGP03.phx.gbl...
> Thanks every body!
> What's the exact parameter that I must add to boot.ini?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OAryIzzBHHA.4892@.TK2MSFTNGP04.phx.gbl...
>
|||Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100% of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>
|||Run sp_configure to enable AWE, and set the max server memory. be sure to
leave some memory for the OS. Also, set the /PAE switch in BOOT.INI and
reboot.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Kamal Hassan" <KamalHassan@.discussions.microsoft.com> wrote in message
news:92951B59-BB92-47B3-A72F-C544D8DF01C1@.microsoft.com...
Hi All,
I need some help here:
We have Windows 2000 Advanced Server sp4
SQL Server 2000 EE sp4 plus HOT Fix (KB899761)
Boot.ini filoe has /3GB switch
Total Memory Available = 8.0 GB
SQL Server configuration as follow:
min memory per query (KB) = 2048
max server memory (MB) = 5097
The SQL Server service account seems to have all appropriate permisison
(lock memory etc.)
But it seems that SQL Server can only allocate approx 2.4 GB of memory
Any help would be greatly appreciated!
Kamal
"Tom Moreau" wrote:

> At the OS level, you have to set the /PAE switch in BOOT.INI. That said,
> your machine doesn't really have enough RAM. You don't want to give 100%
> of
> the RAM to SQL Server, since the OS needs memory, too. RAM is cheap. Buy
> some before you turn on AWE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O%23xDOXzBHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Our server has Windows 2003 EE with 3.5 GB RAM. I want to configure SQL
> Server 2005 EE to be able to use whole of memory if required.
> I have already used "awe enabled" option with sp_configure to change the
> run
> value to 1, but it seems other parameters must be configured on OS.
> What's the easiest way to do it?
> Many thanks in advance,
> Leila
>
>

Enable parameter selection

I have two reports. On Report1, I have a hyperlink action that brings up
Report2, passing the required parameters it needs. But when Report2 is shown,
I don't have the ability to change the parameters, E.g. in Report2, I have a
parameter in a list with a Show\Hide options. When getting redirected from
Report1, the default is set to hide. When I'm in Report 2, I then want to
change it to Show, but the parameter "section" is not on the report header.
Looking at the link, I can see the following
...%3d0%26rc%253aParameters%3dFalse%26rc%253aReplacementRoot%3dhttp...When I
change the False to True, I can then see the parameter area, but I'm not sure
how to enable it in the report. Can't seem to find the property I need to
change.Use jump to URL instead of jump to report and you can set this the way you
want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alex" <alex_dinu@.adp.com.(nospam)> wrote in message
news:EFDE9B94-308C-4050-9214-F7B9913F3659@.microsoft.com...
>I have two reports. On Report1, I have a hyperlink action that brings up
> Report2, passing the required parameters it needs. But when Report2 is
> shown,
> I don't have the ability to change the parameters, E.g. in Report2, I have
> a
> parameter in a list with a Show\Hide options. When getting redirected from
> Report1, the default is set to hide. When I'm in Report 2, I then want to
> change it to Show, but the parameter "section" is not on the report
> header.
> Looking at the link, I can see the following
> ...%3d0%26rc%253aParameters%3dFalse%26rc%253aReplacementRoot%3dhttp...When
> I
> change the False to True, I can then see the parameter area, but I'm not
> sure
> how to enable it in the report. Can't seem to find the property I need to
> change.

empty values in SQL sentence

i have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:

INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')

with empty values, but then it doent update in the dataBase-only if all the values appear-
what the solution of it?
ThanksCan you show us the whole part of your insert code. what kind of methods you used.

Alotaibi|||hi, u could build a dynamic SQL for this kind of problem. A stored procedure would be the best bet but let me know if u are using a sp or a SQL query inside your code. depending upon the values u pass to the stored proc, u could add the fields that need to be there in ur query. let me know if u want me to send the sp.|||for example in this code only if i have all the values its work-otherwise it doesnt update-the same thing happend if i want to insert new row data to dataBase.
shravan79 - im a beginner in ASP .net-i dint work yet with stored procedure , my senteces are SQL sentences...
as i said-this value are not requried in the database

 function UpdateDataStore(e as DataGridCommandEventArgs) _
 as boolean
 dim i,j as integer
 dim sDate1,sDone,sPeopleName,sResponse,sNextDate,sImidiate as string
 dim strText,sDate2,sAmlazot,sAct,sDesc as string
 dim blnGo as boolean = true
 dim lbGroupTemp,lbPlaceTemp,lbTypeTemp,lbLevelTemp as ListBox
 dim lbCostTemp,lbResponsableTemp,lbMainGroup As ListBox
 sDate1 = CType(e.Item.Cells(2).Controls(0), TextBox).Text
 sDone = CType(e.Item.Cells(3).Controls(1), checkBox).checked
 sPeopleName = CType(e.Item.Cells(4).Controls(0), TextBox).Text
 sResponse = CType(e.Item.Cells(5).Controls(0), TextBox).Text
 sNextDate = CType(e.Item.Cells(6).Controls(0), TextBox).Text
 sDate2 = CType(e.Item.Cells(9).Controls(0), TextBox).Text
 sImidiate = CType(e.Item.Cells(10).Controls(1), checkBox).checked
 sAmlazot = CType(e.Item.Cells(13).Controls(1), TextBox).Text
 sAct = CType(e.Item.Cells(14).Controls(1), TextBox).Text
 sDesc = CType(e.Item.Cells(19).Controls(1), TextBox).Text
 'get the list box info
 lbGroupTemp=e.Item.Cells(23).Controls(1)
 if lbGroupTemp.SelectedIndex = -1 then
 lbGroupTemp.SelectedIndex=0
 end if
 'get the list box info
 lbPlaceTemp=e.Item.Cells(21).Controls(1)
 if lbPlaceTemp.SelectedIndex = -1 then
 lbPlaceTemp.SelectedIndex=0
 end if
 'get the list box info
 lbTypeTemp=e.Item.Cells(18).Controls(1)
 if lbTypeTemp.SelectedIndex = -1 then
 lbTypeTemp.SelectedIndex=0
 end if
 'get the list box info
 lbLevelTemp=e.Item.Cells(16).Controls(1)
 if lbLevelTemp.SelectedIndex = -1 then
 lbLevelTemp.SelectedIndex=0
 end if
 'get the list box info
 lbCostTemp=e.Item.Cells(12).Controls(1)
 if lbCostTemp.SelectedIndex = -1 then
 lbCostTemp.SelectedIndex=0
 end if
 'get the list box info
 lbResponsableTemp=e.Item.Cells(8).Controls(1)
 if lbResponsableTemp.SelectedIndex = -1 then
 lbResponsableTemp.SelectedIndex=0
 end if
 'get the list box info
 lbMainGroup=e.Item.Cells(25).Controls(1)
 if lbMainGroup.SelectedIndex = -1 then
 lbMainGroup.SelectedIndex=0
 end if
 dim strSQL as string = "UPDATE SSFactory SET " & _
 "SSTypeID = '" & lbGroupTemp.selectedValue & "'," & _
 "PlaceId = '" & lbPlaceTemp.selectedValue & "'," & _
 "SSMaintypeID = '" & lbMainGroup.selectedValue & "'," & _
 "SSDesc = '" & sDesc & "'," & _
 "SSTypeID1 = '" & lbTypeTemp.selectedValue & "'," & _
 "SSShouldHandleID = '" & lbLevelTemp.selectedValue & "'," & _
 "SSHamlatzot = '" & sAmlazot & "'," & _
 "SSCostID = '" & lbCostTemp.selectedValue & "'," & _
 "SSDateToHandle = '" & sDate2 & "'," & _
 "SSResposible = '" & sResponse & "'," & _
 "SSFactoryAction = '" & sAct & "'," & _
 "SSPeopleName = '" & sPeopleName & "'," & _
 "SSDateWasDone = '" & sDate1 & "'," & _
 "SSDateShouldCheck = '" & sNextDate & "'" & _
 " WHERE SSFactoryID = " & dgData.DataKeys(e.Item.ItemIndex)
 response.write(strSQL)
 'exit function
 ExecuteStatement(strSQL)
 return blnGo
 end function

Thanks a lot!!!!|||please someone-i dont understand why if i have empty values its doesnt work...HELP!!

Empty rows in excel data sheet used in DTS

Hi Everyone,

I am using a DTS package where one of the inputs is an Excel Sheet. Actually this sheet is updated manually whenever required i.e once a week or sometimes once a month, but the DTS package runs everyday.

Whenever new rows are added or deleted manually in the excel sheet, empty rows are showed in the sheet after the last row of data. This hinders the DTS package, because the destination table to which the data in the Excel sheet is sent has Primary keys in it.

Can anyone suggest me how to avoid getting the empty spaces in the excel sheet.

Thanks in advance.

Regards,
kalyanExcel has a funny habit of recognising that there is data present when there really isn't. The easiest solution is when deleting records don't just press the delete key, actually right click the mouse and choose delete from the available options. It may even be better to run some macro inside the document that deletes all records below the last record in this manner.|||Hi SJP,

Thanks for the prompt reply. I think to run a macro to delete all records below the last record would be the most feasible solution.

Thanks again.

Regards,
kalyan|||The simple solution I applied was to delete all the named ranges in the Excel worksheet (From the Excel menu: Insert > Name > Define. It seems that after rows are deleted in the Excel sheet, the named ranges do not get updated. After deleting the named ranges, it was easy to Import the data.