How to free up the space ...

Hi all,
I have a table with column A char(1000) and then alter the table to
varchar(1000) and update tableA set A = rtrim(A)
I know a stupid way to free up the space by create a new table and move the
data to the new table. Is there a command to free up the empty space?
Thanks in advance!Hi
DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move
> the data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>|||Hi,
The update using rtrim was not needed here as SQL Server does not store
spaces after the data. If you insert 'Pink Floyd ' SQL Server only
stores 'Pink Floyd'. Changing to varchar was enough.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Atenza" wrote:
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move the
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||Hi Ben,
I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from ''Pink Floyd' into varchar. SQL Server does not store
spaces after the data applied to SQLSever 2005? coz i am using SQLSever
2000, is this the reason?
In CASE 1, use char(100)
In CASE 2, use varchar(100) by insert 'Pink Floyd '
In CASE 3, use varchar(100) by insert 'Pink Floyd'
In CASE 4, use char(100) and alter to varchar(100)
In CASE 5, use char(100) and move to new table varchar(100)
In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems that spaces cannot be released.
It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or
i have misunderstand something?
Here is my test result:
CASE 1:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
db size 16128KB
CASE 2:
CREATE TABLE dbo.Table2
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table2 values('Pink Floyd ')
while (select count(*) from table2) < 100000
insert into table2 select * from table2
sp_spaceused table2
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 4552 KB 4488 KB 8 KB
56 KB
db size 5504KB
CASE 3:
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 values('Pink Floyd')
while (select count(*) from table3) < 100000
insert into table3 select * from table3
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
db size 4096KB
CASE 4:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 15240 KB 15200 KB 8 KB
32 KB
CASE 5:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 select rtrim(col1) from table1
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
> Hi,
> The update using rtrim was not needed here as SQL Server does not store
> spaces after the data. If you insert 'Pink Floyd ' SQL Server
> only
> stores 'Pink Floyd'. Changing to varchar was enough.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>|||> SQL Server does not store
> spaces after the data applied to SQLSever 2005?
For char, SQL Server always store the specified length. It pads the string with spaces.
For varchar, SQL Server by default store trailing spaces. Whether or not to do this depends on the
setting of ANSI_PADDING when the table/column is *created*.
Rebuilding the indexes on the table should give you back the space. If the table doesn't have a
clustered index, then you either have to create on (and possibly drop it), or export/import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Ben,
> I have done few tests on char and varchar. I found that insert 'Pink Floyd ' is different from
> ''Pink Floyd' into varchar. SQL Server does not store
> spaces after the data applied to SQLSever 2005? coz i am using SQLSever 2000, is this the reason?
> In CASE 1, use char(100)
> In CASE 2, use varchar(100) by insert 'Pink Floyd '
> In CASE 3, use varchar(100) by insert 'Pink Floyd'
> In CASE 4, use char(100) and alter to varchar(100)
> In CASE 5, use char(100) and move to new table varchar(100)
> In CASE 4, for new create data, i think it should be saved spaces. But for the old data, it seems
> that spaces cannot be released.
> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or i have misunderstand
> something?
>
> Here is my test result:
> CASE 1:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> db size 16128KB
>
> CASE 2:
> CREATE TABLE dbo.Table2
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table2 values('Pink Floyd ')
> while (select count(*) from table2) < 100000
> insert into table2 select * from table2
> sp_spaceused table2
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
> db size 5504KB
>
> CASE 3:
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 values('Pink Floyd')
> while (select count(*) from table3) < 100000
> insert into table3 select * from table3
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
> db size 4096KB
>
> CASE 4:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> COMMIT
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>
>
> CASE 5:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 select rtrim(col1) from table1
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:utEoaQQkGHA.4672@.TK2MSFTNGP02.phx.gbl...
> Hi
> DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much! it works!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKxJ6QSkGHA.4652@.TK2MSFTNGP05.phx.gbl...
>> SQL Server does not store
>> spaces after the data applied to SQLSever 2005?
> For char, SQL Server always store the specified length. It pads the string
> with spaces.
> For varchar, SQL Server by default store trailing spaces. Whether or not
> to do this depends on the setting of ANSI_PADDING when the table/column is
> *created*.
> Rebuilding the indexes on the table should give you back the space. If the
> table doesn't have a clustered index, then you either have to create on
> (and possibly drop it), or export/import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi Ben,
>> I have done few tests on char and varchar. I found that insert 'Pink
>> Floyd ' is different from ''Pink Floyd' into varchar. SQL Server does not
>> store
>> spaces after the data applied to SQLSever 2005? coz i am using SQLSever
>> 2000, is this the reason?
>> In CASE 1, use char(100)
>> In CASE 2, use varchar(100) by insert 'Pink Floyd '
>> In CASE 3, use varchar(100) by insert 'Pink Floyd'
>> In CASE 4, use char(100) and alter to varchar(100)
>> In CASE 5, use char(100) and move to new table varchar(100)
>> In CASE 4, for new create data, i think it should be saved spaces. But
>> for the old data, it seems that spaces cannot be released.
>> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution
>> or i have misunderstand something?
>>
>> Here is my test result:
>> CASE 1:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> db size 16128KB
>>
>> CASE 2:
>> CREATE TABLE dbo.Table2
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table2 values('Pink Floyd ')
>> while (select count(*) from table2) < 100000
>> insert into table2 select * from table2
>> sp_spaceused table2
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
>> db size 5504KB
>>
>> CASE 3:
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 values('Pink Floyd')
>> while (select count(*) from table3) < 100000
>> insert into table3 select * from table3
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>> db size 4096KB
>>
>> CASE 4:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> BEGIN TRANSACTION
>> SET QUOTED_IDENTIFIER ON
>> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> SET ARITHABORT ON
>> SET NUMERIC_ROUNDABORT OFF
>> SET CONCAT_NULL_YIELDS_NULL ON
>> SET ANSI_NULLS ON
>> SET ANSI_PADDING ON
>> SET ANSI_WARNINGS ON
>> COMMIT
>> BEGIN TRANSACTION
>> CREATE TABLE dbo.Tmp_Table1
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> IF EXISTS(SELECT * FROM dbo.Table1)
>> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
>> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
>> GO
>> DROP TABLE dbo.Table1
>> GO
>> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
>> GO
>> COMMIT
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>>
>>
>> CASE 5:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 select rtrim(col1) from table1
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>>
>>
>>
>> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
>> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server
>> only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>>
>

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence
> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
http://www.microsoft.com/technet/pro...e14.mspx#EDAA.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
]
Dejan Sarka, SQL Server MVP
Associate Mentor
[url]www.SolidQualityLearning.com" target="_blank">http://www.microsoft.com/technet/pr...ityLearning.com

How to force some commands to run by using certain index

Hi,
Can i force some of the update command by using the
index that i want. Normally when we update something, we
will let sql to select the index, how can i choose the
index that i want in the transact-sql statement?
Can anyone teach me and give me an example?
Thanks a lot!
regards,
florence> Can i force some of the update command by using the
> index that i want. Normally when we update something, we
> will let sql to select the index, how can i choose the
> index that i want in the transact-sql statement?
Yes, you can use optimizer hints. But a common advice is to do everything
else before using hnts. Check how to tune queries, including optimzer hints,
at
http://www.microsoft.com/technet/prodtechnol/sql/70/books/inside14.mspx#EDAA.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to force CRUDs be handled through Stored Proc.

Hi,
If I want no one to be able to use then native select, delete, update etc..
and rather force the user to use stored procedure that I have included in th
e
server. how to do that?
Give:
the database has 2- accounts...one limited privileges account for the users
to use and one for me the owner. I want to deny usage of stored procedures.
plus I want to encrypt the procedure listing so now one can see the inside
Thank youDon't give the users permissions directly on the tables, only the stored pro
cedures. As for
encryption, create the procs using WITH ENCRYPTION (however, if someone want
s to, they can Google
for decryption and find it within a minute).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
> Hi,
> If I want no one to be able to use then native select, delete, update etc.
.
> and rather force the user to use stored procedure that I have included in
the
> server. how to do that?
> Give:
> the database has 2- accounts...one limited privileges account for the user
s
> to use and one for me the owner. I want to deny usage of stored procedures
.
> plus I want to encrypt the procedure listing so now one can see the inside
> Thank you|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Don't give the users permissions directly on the tables, only the stored
> procedures. As for encryption, create the procs using WITH ENCRYPTION
> (however, if someone wants to, they can Google for decryption and find it
> within a minute).
Yeah, WITH ENCRYPTION tends to keep "honest people honest"|||Then how do i protect my stored procedures for listing?
"Michael C#" wrote:

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Yeah, WITH ENCRYPTION tends to keep "honest people honest"
>
>|||You can't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:2505B075-0207-48EB-BA08-87C57FE3088D@.microsoft.com...
> Then how do i protect my stored procedures for listing?
> "Michael C#" wrote:
>|||Thank you all for the help,
I want to highlight that the user account is secret as well. the users must
use an application to access the db. the application has an obfuscated user
account and password that is authorized to do cruds through stored procedure
s
(assuming I denied direct access to the table) so this user account is the
only way a user can access the db. For a user to log to the SQl sever he mus
t
guess the account and password. which as securely saved/protected PKI model.
But assume he guessed the account (limited privileges) and password, and he
is now on the server. he will not be able to use the stored procedures
because I designed the procedures to take a parameter that is secret and
saved again within the application that user must use to access the
database... so the user can see the stored procedure signature I presume bu
t
have to guess the key. he is not the owner so he cant delete, and it was
saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
hence see the" IF ELSE" where I check for the secret key value passed to the
procedure. Now the nightmare is that he decrypts the stored procedure...so
I have 3 questions
1- How can I protect him from opening the stored procedures?
2- Can I program the stored procedure to include check such if else
etc,,(obviously I'm novice to T-Sql)
3- can I use the e-mail mechanism from within a stored procedure to notify
me of suspicious attempts. such as when the key entered was bad. based on my
closed model on failed key attempt is too many and would trigger a
notification email.
Thank you so very much
"Tibor Karaszi" wrote:

> Don't give the users permissions directly on the tables, only the stored p
rocedures. As for
> encryption, create the procs using WITH ENCRYPTION (however, if someone wa
nts to, they can Google
> for decryption and find it within a minute).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
>
>|||1. You can't.
2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE i
n Books Online.
3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI,
but you need to download
and install from www.sqldev.net). Or put enough info in a table and have an
outside process (like
SQL Server Agent job) regularly read this table and send emails. Or use Noti
fication Services (free
download from MS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
> Thank you all for the help,
> I want to highlight that the user account is secret as well. the users mus
t
> use an application to access the db. the application has an obfuscated use
r
> account and password that is authorized to do cruds through stored procedu
res
> (assuming I denied direct access to the table) so this user account is the
> only way a user can access the db. For a user to log to the SQl sever he m
ust
> guess the account and password. which as securely saved/protected PKI mode
l.
> But assume he guessed the account (limited privileges) and password, and h
e
> is now on the server. he will not be able to use the stored procedures
> because I designed the procedures to take a parameter that is secret and
> saved again within the application that user must use to access the
> database... so the user can see the stored procedure signature I presume
but
> have to guess the key. he is not the owner so he cant delete, and it was
> saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
> hence see the" IF ELSE" where I check for the secret key value passed to t
he
> procedure. Now the nightmare is that he decrypts the stored procedure...so
> I have 3 questions
> 1- How can I protect him from opening the stored procedures?
> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
> "Tibor Karaszi" wrote:
>|||If i cant prevemt decrymption, then my only lien of defence the is embeded
acount and password. and hope the user will never abe able to guess.
Do you have better suggestions?
"Tibor Karaszi" wrote:

> 1. You can't.
> 2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE
in Books Online.
> 3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI
, but you need to download
> and install from www.sqldev.net). Or put enough info in a table and have a
n outside process (like
> SQL Server Agent job) regularly read this table and send emails. Or use No
tification Services (free
> download from MS).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
>
>|||> 1- How can I protect him from opening the stored procedures?
You can't easily. The better way to get the level of security you are reques
ting
here would be to encrypt the actual data and have the stored procs merely
provide CRUD services. Thus, even if the user sees the stored proc, without
the
ability to decrypt the data itself, the stored proc by itself would useless.

> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
Not sure what you mean here.

> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
Yes but it might be trickier than you think. This can be done in the stored
procs themselves and/or in your middle layer code.
Thomas|||"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:7EB4CFB4-BCC6-4B7D-9204-B17B16C916E6@.microsoft.com...
> If i cant prevemt decrymption, then my only lien of defence the is embeded
> acount and password. and hope the user will never abe able to guess.
> Do you have better suggestions?
WITH ENCRYPTION keeps "honest people honest", and prevents novices from
hacking into your code, and it's not all that secure. There are just too
many tools available to decrypt SP's. Another idea might be to store your
queries internally to your application in an encrypted format and decrypt
right before execution, instead of using SP's. You'll take a performance
hit on this, however, which may or may not be negligible. This brings you
back full-circle to your original question, however, about forcing access to
tables only via SP's...sql

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 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 find rows marked for replication?

SQL Server 2005
Someone here attempted to update about 3000 rows in a table that is in
replication (on the publisher side). We are using push replication.
The rows were updated successfully on the publisher table, but for some
reason, it serioulsy locked up the replicated table on the subscriber
side. Because of the locking problem, none of the rows actually got
updated on the subscriber side.
At this point, the DBA turned off replication to release the locks on
the table (which it did).
So, now the situation is, there's a bunch of rows marked for
replication that haven't yet been successfully replicated. My question
is, is there a way to "undo" the changes? If so, how would one do this.
The other option is to turn replication on later tonight when no users
are online, I suppose.
Has anyone else had this problem and how did you solve it?
Thanks
Are you using transactional replication? If so, then please try using
sp_browsereplcmds. If you are using merge, then you could try my routine
(http://www.replicationanswers.com/Script9.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes, thank you
This information also corresponds to the number of rows in the
"msrepl_commands" table.
I was told that I can simply truncate the msrepl_commands table to
clear the slate, so to speak. Is this correct? And will it hurt
anything?
Paul Ibison wrote:
> Are you using transactional replication? If so, then please try using
> sp_browsereplcmds. If you are using merge, then you could try my routine
> (http://www.replicationanswers.com/Script9.asp).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||This sort of thing is possible - after all it is essentially what the
cleanup agent does. However you'll also need to take into account
msrepl_transactions, and be sure to delete only relevant commands ie not
ones belonging to other articles or publications and finally you'll be
creating a situation of non-convergence which might lead to synchronization
errors later on. All this is taking you into unsupported territory so I'd
recommend simply synchronizing during a quiet time.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How to find rows marked for replication?

SQL Server 2005
Someone here attempted to update about 3000 rows in a table that is in
replication (on the publisher side). We are using push replication.
The rows were updated successfully on the publisher table, but for some
reason, it serioulsy locked up the replicated table on the subscriber
side. Because of the locking problem, none of the rows actually got
updated on the subscriber side.
At this point, the DBA turned off replication to release the locks on
the table (which it did).
So, now the situation is, there's a bunch of rows marked for
replication that haven't yet been successfully replicated. My question
is, is there a way to "undo" the changes? If so, how would one do this.
The other option is to turn replication on later tonight when no users
are online, I suppose.
Has anyone else had this problem and how did you solve it?
ThanksAre you using transactional replication? If so, then please try using
sp_browsereplcmds. If you are using merge, then you could try my routine
(http://www.replicationanswers.com/Script9.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Yes, thank you
This information also corresponds to the number of rows in the
"msrepl_commands" table.
I was told that I can simply truncate the msrepl_commands table to
clear the slate, so to speak. Is this correct? And will it hurt
anything?
Paul Ibison wrote:
> Are you using transactional replication? If so, then please try using
> sp_browsereplcmds. If you are using merge, then you could try my routine
> (http://www.replicationanswers.com/Script9.asp).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||This sort of thing is possible - after all it is essentially what the
cleanup agent does. However you'll also need to take into account
msrepl_transactions, and be sure to delete only relevant commands ie not
ones belonging to other articles or publications and finally you'll be
creating a situation of non-convergence which might lead to synchronization
errors later on. All this is taking you into unsupported territory so I'd
recommend simply synchronizing during a quiet time.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How to find rows marked for replication?

SQL Server 2005
Someone here attempted to update about 3000 rows in a table that is in
replication (on the publisher side). We are using push replication.
The rows were updated successfully on the publisher table, but for some
reason, it serioulsy locked up the replicated table on the subscriber
side. Because of the locking problem, none of the rows actually got
updated on the subscriber side.
At this point, the DBA turned off replication to release the locks on
the table (which it did).
So, now the situation is, there's a bunch of rows marked for
replication that haven't yet been successfully replicated. My question
is, is there a way to "undo" the changes? If so, how would one do this.
The other option is to turn replication on later tonight when no users
are online, I suppose.
Has anyone else had this problem and how did you solve it?
ThanksAre you using transactional replication? If so, then please try using
sp_browsereplcmds. If you are using merge, then you could try my routine
(http://www.replicationanswers.com/Script9.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Yes, thank you
This information also corresponds to the number of rows in the
"msrepl_commands" table.
I was told that I can simply truncate the msrepl_commands table to
clear the slate, so to speak. Is this correct? And will it hurt
anything?
Paul Ibison wrote:
> Are you using transactional replication? If so, then please try using
> sp_browsereplcmds. If you are using merge, then you could try my routine
> (http://www.replicationanswers.com/Script9.asp).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||This sort of thing is possible - after all it is essentially what the
cleanup agent does. However you'll also need to take into account
msrepl_transactions, and be sure to delete only relevant commands ie not
ones belonging to other articles or publications and finally you'll be
creating a situation of non-convergence which might lead to synchronization
errors later on. All this is taking you into unsupported territory so I'd
recommend simply synchronizing during a quiet time.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
There is no built-in and generally applicable method to determine the
progress of an UPDATE (e.g. to be able to answer how much longer it will take
for the UPDATE to complete). The reason being that UPDATE can be processed in
many different way.
If we are talking about a very large update, you may want to break it into
multiple smaller batches of update, and insert code yourself to report the
progress.
Linchi
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Answered in .programming. Please do not double-post. If you want to post
to multiple newsgroups, post to all relevant groups at once so people will
not waste time trying to answer questions that have already been answered.
"DR" <softwareengineer98037@.yahoo.com> wrote in message
news:uaiJVZ7MIHA.5224@.TK2MSFTNGP02.phx.gbl...
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time,
> is there anyway for me to check the status of that update process?
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specifi
c
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, i
s
> there anyway for me to check the status of that update process?
>
>