filter sqldatasource: 2012

Friday, March 30, 2012

How to FTP via VPN to sites with real adresses on different IP's ?

Hi,
I am using Windows 2003 Server. The VPN works fine, but I want to FTP
through the VPN so the connection is encrypted. The problem is each of my
IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
comes up using a private IP, I can't connect to any of the FTP sites using
the VPN server's IP, 10.10.10.2. Could you give me specific directions how
to deal with this?
Thank you for your help.
Hi
Am I correct in saying that you are trying to access an FTP site on your SBS
server? And presumably it has two network cards? If this is the case then
open up the IIS services manager, FTP sites, then right click and select
properties, then check under IP address that your internal IP address is
selected, this will mean that the ftp site is not available from your
external IP address though.
To be honest I'm entirely sure of your setup from the posting, the other
understanding I had would mean going down the line of setting up a static
route on the VPN router to ensure those packets are routed over the VPN
rather that the network.
Hope this helps
Dan
"Jason Robertson" <jason6869@.msn.com> wrote in message
news:5dKdnWgJSLggFTvcRVn-vQ@.comcast.com...
> Hi,
> I am using Windows 2003 Server. The VPN works fine, but I want to FTP
> through the VPN so the connection is encrypted. The problem is each of my
> IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
> comes up using a private IP, I can't connect to any of the FTP sites using
> the VPN server's IP, 10.10.10.2. Could you give me specific directions how
> to deal with this?
> Thank you for your help.
>

How to FTP via VPN to sites with real adresses on different IP's ?

Hi,
I am using Windows 2003 Server. The VPN works fine, but I want to FTP
through the VPN so the connection is encrypted. The problem is each of my
IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
comes up using a private IP, I can't connect to any of the FTP sites using
the VPN server's IP, 10.10.10.2. Could you give me specific directions how
to deal with this?
Thank you for your help.
Is this really a SQL Server question? You might do better posting this to a
Windows newsgroup or perhaps an IIS newsgroup.
"Jason Robertson" <jason6869@.msn.com> wrote in message
news:cMidnYAGMND8FzvcRVn-3Q@.comcast.com...
> Hi,
> I am using Windows 2003 Server. The VPN works fine, but I want to FTP
> through the VPN so the connection is encrypted. The problem is each of my
> IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
> comes up using a private IP, I can't connect to any of the FTP sites using
> the VPN server's IP, 10.10.10.2. Could you give me specific directions how
> to deal with this?
> Thank you for your help.
>
|||Create an Administrative FTP site hooked to the private IP on the IIS server. It is multi-homed; isn't it?
Now, this really isn't a SQL Server question, now is it?
Sincerely,
Anthony Thomas

"Jason Robertson" <jason6869@.msn.com> wrote in message news:cMidnYAGMND8FzvcRVn-3Q@.comcast.com...
Hi,
I am using Windows 2003 Server. The VPN works fine, but I want to FTP
through the VPN so the connection is encrypted. The problem is each of my
IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
comes up using a private IP, I can't connect to any of the FTP sites using
the VPN server's IP, 10.10.10.2. Could you give me specific directions how
to deal with this?
Thank you for your help.
sql

How to FTP via VPN to sites with real adresses on different IP's ?

Hi,
I am using Windows 2003 Server. The VPN works fine, but I want to FTP
through the VPN so the connection is encrypted. The problem is each of my
IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
comes up using a private IP, I can't connect to any of the FTP sites using
the VPN server's IP, 10.10.10.2. Could you give me specific directions how
to deal with this?
Thank you for your help.Is this really a SQL Server question? You might do better posting this to a
Windows newsgroup or perhaps an IIS newsgroup.
"Jason Robertson" <jason6869@.msn.com> wrote in message
news:cMidnYAGMND8FzvcRVn-3Q@.comcast.com...
> Hi,
> I am using Windows 2003 Server. The VPN works fine, but I want to FTP
> through the VPN so the connection is encrypted. The problem is each of my
> IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
> comes up using a private IP, I can't connect to any of the FTP sites using
> the VPN server's IP, 10.10.10.2. Could you give me specific directions how
> to deal with this?
> Thank you for your help.
>|||Create an Administrative FTP site hooked to the private IP on the IIS server
. It is multi-homed; isn't it?
Now, this really isn't a SQL Server question, now is it?
Sincerely,
Anthony Thomas
--
"Jason Robertson" <jason6869@.msn.com> wrote in message news:cMidnYAGMND8Fz
vcRVn-3Q@.comcast.com...
Hi,
I am using Windows 2003 Server. The VPN works fine, but I want to FTP
through the VPN so the connection is encrypted. The problem is each of my
IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
comes up using a private IP, I can't connect to any of the FTP sites using
the VPN server's IP, 10.10.10.2. Could you give me specific directions how
to deal with this?
Thank you for your help.

How to FTP via VPN to sites with real adresses on different IP's ?

Hi,
I am using Windows 2003 Server. The VPN works fine, but I want to FTP
through the VPN so the connection is encrypted. The problem is each of my
IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
comes up using a private IP, I can't connect to any of the FTP sites using
the VPN server's IP, 10.10.10.2. Could you give me specific directions how
to deal with this?
Thank you for your help.
Hey Jason, I think you have the wrong newsgroup. No worries though. I had a
similar problem. What I did to "fix" it was use the route command to a route
to the Public FTP IP addrs. The command looks like this:
route ADD w.x.y.z MASK 255.255.255.0 10.10.10.1
All you would need to change is w.x.y.z to the network of the public FTP.
Then the correct subnet mask instead of the 255.255.255.0 I guess. And
finally whatever gateway you have during your VPN session, I guess .1.
To get help try "route /?". To see that everyone looks good try "route
print"
I put my fix in a bat file, I would only run the bat upon connecting to the
VPN. I had another bat file that would undo it, "route DELETE w.x.y.z".
If this is not your problem, if you really just can't to the net when
VPNing, try going through a Proxy server.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Jason Robertson" <jason6869@.msn.com> wrote in message
news:0_idndGx5opzGjvcRVn-qA@.comcast.com...
> Hi,
> I am using Windows 2003 Server. The VPN works fine, but I want to FTP
> through the VPN so the connection is encrypted. The problem is each of my
> IIS sites has a real Internet address specified, w.x.y.z. So when the VPN
> comes up using a private IP, I can't connect to any of the FTP sites using
> the VPN server's IP, 10.10.10.2. Could you give me specific directions how
> to deal with this?
> Thank you for your help.
>

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP DB backup File from one server to the other

I would like to set up sort of schedule by using FTP to move database back up file from Production server to Staging server every night. Is someone telling me how to do this and any other good way to handle it? I appreciate help!
JQIf both servers are on your network, make a DTS package that does the backup and "On success" run a DOS batch command to copy the .BAK file from server to server. Then Schedule the package.|||tdudley,

thanks for your help! Can you give me more details for how to do that. I am kind of new to DTS package.

JQ|||Choose add new package:

Select the connection icon for SQL Server and point to the database of your choice.

Select the Task "Execute SQL Task" Input something like this into the "SQL Statement:" box: BACKUP DATABASE [YourDatabaseNameHere] TO DISK = N'e:\MSSQL\BACKUP\YourDatabaseNameHere.bak' WITH INIT , NOUNLOAD , NAME = N'Backup YourDatabaseNameHere', NOSKIP , STATS = 10, NOFORMAT

Then add another task "Execute process task" place your dos bat command call like this "E:\Files\Ftp\YourDatabaseNameHere.bat" in the "Win32 process:" box.

Then click the Execute SQL Task hold control and click the execute process task click workflow and choose "On Success"

Save this locally to SQL Server as say "OFF_SITE_BACKUP" and then schedule the job. Hope this was helpful.

How to FTP

How to automate FTP files to unix server from Windows server ? I dont want t
o
use SQL Server but run a bat file that migth ftp files to unix serverHi
You can use the FTP.exe program which comes with windows. Type in ftp -h at
a command prompt to get the options or look in windows help file.
If you are calling from a stored procedure this can be called using
xp_cmdshell.
John
"Disney" wrote:

> How to automate FTP files to unix server from Windows server ? I dont want
to
> use SQL Server but run a bat file that migth ftp files to unix server

How to from 1apple,1orange,1apple to 2apple,1*orange

Lets say there is a data table called ShoppingBasket. It has fields like "product", "price", "quantity" and "total" and so on. It is possible to have duplicates, but I think it is better to run a query and remove them. And update quantities. In the end, I plan to calculate total price.

For total price there seems to be a field or property "Formula" in column properties.

As this is really two questions, I take any help.

Leif

We need to see how your data looks like and also how you want it updated..

|||

"We need to see how your data looks like and also how you want it updated.." Here it comes.

I have a table called t_shopping_basket. There the user inserts items from products table "t_Tuote". I use this query:

"INSERT dbo.t_shopping_basket (Product_code, Name,Model,Quantity,Price,Alv) SELECT Tuotekoodi,Name,Model,Toimittajanimi,@.Quantity,Price,Alv FROM dbo.t_Tuote WHERE Product_code=@.Product_code", conn) ".

I have included also a gridView of "shopping basket" Its query is like:

"UPDATE [t_shopping_basket] SET [Product_code] = @.Product_code,[Quantity] = @.Quantity,[Name] = @.Name WHERE [Product_code] = @.Product_code"

Nothing prevents users pressing buy several times, so same item can be there in many places. If I leave them there, it has its good positive and negative sides. I probably should put an extra field like "item index" or so into the table then. Or I could leave quantity field out. 3 pieces means 3 rows of something.

Best way to my mind is to delete or prevent duplicates. But then, I have to find those duplicates first, and when I delete a record, I must increase item quantity. This feels like a complicated thing to do, especially with query.

How have others done this? What could my query look like?

Regards

Leif

How to Freeze the header for Reports

I am creating reports using SQL Server Management Studio 2005. When I
get my final report I am not seeing a frozed header, ie the header
keeps moving as I scroll down. Can somebody let me know on what I
should do to freeze my hearder in each page.On Dec 13, 6:00 pm, Dani <tharani.mahend...@.gmail.com> wrote:
> I am creating reports using SQL Server Management Studio 2005. When I
> get my final report I am not seeing a frozed header, ie the header
> keeps moving as I scroll down. Can somebody let me know on what I
> should do to freeze my hearder in each page.
Somewhere you turned on the Table's "Header should remain visible
while scrolling" option. Right click your table, Properties, General
Tab, and uncheck that option.
-- Scott|||On Dec 13, 4:13 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 13, 6:00 pm, Dani <tharani.mahend...@.gmail.com> wrote:
> > I am creating reports using SQL Server Management Studio 2005. When I
> > get my final report I am not seeing a frozed header, ie the header
> > keeps moving as I scroll down. Can somebody let me know on what I
> > should do to freeze my hearder in each page.
> Somewhere you turned on the Table's "Header should remain visible
> while scrolling" option. Right click your table, Properties, General
> Tab, and uncheck that option.
> -- Scott
No Prne, the option you mentioned is not available.|||Actually the option she mentioned is there. You need to make sure that you
are right clicking on the table and not a column,textbox or row in the
table. When the properties window appears it will say "Table Properties" at
the top. The option you are looking for is the very last check box on the
"General" tab.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Dani <tharani.mahendran@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Re: How to Freeze the header for Reports
> Date: Thu, 13 Dec 2007 15:21:25 -0800 (PST)
> On Dec 13, 4:13 pm, Orne <polysilly...@.yahoo.com> wrote:
> > On Dec 13, 6:00 pm, Dani <tharani.mahend...@.gmail.com> wrote:
> >
> > > I am creating reports using SQL Server Management Studio 2005. When I
> > > get my final report I am not seeing a frozed header, ie the header
> > > keeps moving as I scroll down. Can somebody let me know on what I
> > > should do to freeze my hearder in each page.
> >
> > Somewhere you turned on the Table's "Header should remain visible
> > while scrolling" option. Right click your table, Properties, General
> > Tab, and uncheck that option.
> >
> > -- Scott
> No Prne, the option you mentioned is not available.
>|||Is there a different place to do this in RS2000?
"Chris Alton [MSFT]" wrote:
> Actually the option she mentioned is there. You need to make sure that you
> are right clicking on the table and not a column,textbox or row in the
> table. When the properties window appears it will say "Table Properties" at
> the top. The option you are looking for is the very last check box on the
> "General" tab.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > From: Dani <tharani.mahendran@.gmail.com>
> > Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > Subject: Re: How to Freeze the header for Reports
> > Date: Thu, 13 Dec 2007 15:21:25 -0800 (PST)
> >
> > On Dec 13, 4:13 pm, Orne <polysilly...@.yahoo.com> wrote:
> > > On Dec 13, 6:00 pm, Dani <tharani.mahend...@.gmail.com> wrote:
> > >
> > > > I am creating reports using SQL Server Management Studio 2005. When I
> > > > get my final report I am not seeing a frozed header, ie the header
> > > > keeps moving as I scroll down. Can somebody let me know on what I
> > > > should do to freeze my hearder in each page.
> > >
> > > Somewhere you turned on the Table's "Header should remain visible
> > > while scrolling" option. Right click your table, Properties, General
> > > Tab, and uncheck that option.
> > >
> > > -- Scott
> >
> > No Prne, the option you mentioned is not available.
> >
>|||On Mar 4, 7:57=A0am, Scott <Sc...@.discussions.microsoft.com> wrote:
> Is there a different place to do this in RS2000?
>
> "Chris Alton [MSFT]" wrote:
> > Actually the option she mentioned is there. You need to make sure that y=ou
> > are right clicking on the table and not a column,textbox or row in the
> > table. When the properties window appears it will say "Table Properties"= at
> > the top. The option you are looking for is the very last check box on th=e
> > "General" tab.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no righ=ts.
> > --
> > > From: Dani <tharani.mahend...@.gmail.com>
> > > Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > > Subject: Re: How to Freeze the header for Reports
> > > Date: Thu, 13 Dec 2007 15:21:25 -0800 (PST)
> > > On Dec 13, 4:13 pm, Orne <polysilly...@.yahoo.com> wrote:
> > > > On Dec 13, 6:00 pm, Dani <tharani.mahend...@.gmail.com> wrote:
> > > > > I am creating reports using SQL Server Management Studio 2005. Whe=n I
> > > > > get my final report I am not seeing a frozed header, ie the header=
> > > > > keeps moving as I scroll down. Can somebody let me know on what I
> > > > > should do to freeze my hearder in each page.
> > > > Somewhere you turned on the Table's "Header should remain visible
> > > > while scrolling" option. =A0Right click your table, Properties, Gene=ral
> > > > Tab, and uncheck that option.
> > > > -- Scott
> > > No Prne, the option you mentioned is not available.- Hide quoted text =-
> - Show quoted text -
The feature is not available in RS 2000.|||Instead create the header in the page footer, remove the header for
the table. go to page header properties.see the properties of page
header if there is any option for remain visible
I didnt try this.this is just my idea. Thank youe
Raj Deep.A

How to free up the space ...

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 th
e
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||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 th
e
> 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...[vbcol=seagreen]
> 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:
>|||> 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 w
ith 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 t
able doesn't have a
clustered index, then you either have to create on (and possibly drop it), o
r 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 20
00, 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 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...[vbcol=seagreen]
> 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:
>|||> 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 w
ith 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 t
able doesn't have a
clustered index, then you either have to create on (and possibly drop it), o
r 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 20
00, 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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 unus
ed
> -- -- -- -- --
--
> --
> 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...
>|||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...
>sql

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 free up SQL 2000 transaction log space?

I have tried all of the Shrink database options provided by Enterprise
Manager to try and shrink my transaction log from its current size of 2Gig,
but no matter what I execute it stays 2Gig.
I selected the option to reduce to its minimum allowable size (which it
showed as about 300Meg), but even after that the file still shows as 2Gig.
Is there a different mechanism I need to do to free up this huge amount of
space? A stored procedure or other system proc?
Many thanks!
Marksee
http://www.nigelrivett.net/SQLAdmin...ileGrows_1.html
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Nigel explains on his site about LSN's. For a large database to have a 2 GI
G
log is not unheard of. For example, if a backup hasn't been performed for
several days and there is a rollback or a deadlock and the database has to
track forward from that time, the database log file can swell quickly. You
may want to look at the cause of the log size moreso than just trying to
shrink the log. If there is an unresolved transaction from a particular day
and the LSN's for a part of the log have overlapping values (ie LSN start of
Wednesday is less than LSN start for Tuesday and matches LSN end of Monday),
you may be able to simply take the database offline and run a restore
operation to clean up the log. Nigel's instruction page is very clear. I
hope it helps you with your problem.
--
Regards,
Jamie
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Thanks for the great help Jamie, that answered my question and more!
Thanks,
Mark
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:30313C61-ECCC-4294-8AC9-8EB6AD22FF78@.microsoft.com...
> Nigel explains on his site about LSN's. For a large database to have a 2
> GIG
> log is not unheard of. For example, if a backup hasn't been performed for
> several days and there is a rollback or a deadlock and the database has to
> track forward from that time, the database log file can swell quickly.
> You
> may want to look at the cause of the log size moreso than just trying to
> shrink the log. If there is an unresolved transaction from a particular
> day
> and the LSN's for a part of the log have overlapping values (ie LSN start
> of
> Wednesday is less than LSN start for Tuesday and matches LSN end of
> Monday),
> you may be able to simply take the database offline and run a restore
> operation to clean up the log. Nigel's instruction page is very clear. I
> hope it helps you with your problem.
> --
> Regards,
> Jamie
>
> "Mark Findlay" wrote:
>

how to free the memory occupied by "blob" in MS sql server

i'm working in microsoft sql server and i got following problem:

I have a text files Asia.txt in E:\ folder with some data in it as shown below

Asia.txt

1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong

And I have a table Region, in the database Companies, as shown below.

1>CREATE TABLE REGION (ID INT,REGION VARCHAR(25),DATA varbinary(MAX))
2>GO

I queried all the data from Asia.txt, using the OPENROWSET function.

1>INSERT INTO REGION (ID, REGION, DATA)
2>SELECT 1 AS ID, 'ASIA' AS REGION,
3> * FROM OPENROWSET( BULK 'E:\Asia.txt',SINGLE_BLOB)
4>AS MYTABLE
5>GO

it occupied some memory then i deleted this record using follwoing query

1>DELETE REGION
2>GO

then it deletes the record successfully but memory is not getting freed

can anyone help me out on this problem

When you delete from SQL server the memory will not be removed until the database is truncated. If you want to store data temporarily create a temporary table with a prefix of #

CREATE TABLE #TEMPTABLE|||

i want to remove one record from the table, and i did it by using "DELETE" statement with WHERE clause,

now i want to free memory which was occupied by recently deleted record.

what i know about TRUNCATE statement is that it deletes the whole table and we cannot use WHERE clause with TRUNCATE.

and i don't want to create temporary table.

so how can i use truncate?.

thanks for your suggestion

|||

Hi,

This problem make me concentrate to it ! I have the same problem, please read this article :

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

Althought I use temporary table, but, it does not free memory when I delete temp table, I do not know why ! For someone, they said that : "In AS, it free memory not well and Microsoft company does not anonounce this problem in a clear way !".

Can you tell me more,

Regards,

Tran Quang Phuong.

how to free the memory occupied by "blob" in MS sql server

i'm working in microsoft sql server and i got following problem:

I have a text files Asia.txt in E:\ folder with some data in it as shown below

Asia.txt

1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong

And I have a table Region, in the database Companies, as shown below.

1>CREATE TABLE REGION (ID INT,REGION VARCHAR(25),DATA varbinary(MAX))
2>GO

I queried all the data from Asia.txt, using the OPENROWSET function.

1>INSERT INTO REGION (ID, REGION, DATA)
2>SELECT 1 AS ID, 'ASIA' AS REGION,
3> * FROM OPENROWSET( BULK 'E:\Asia.txt',SINGLE_BLOB)
4>AS MYTABLE
5>GO

it occupied some memory then i deleted this record using follwoing query

1>DELETE REGION
2>GO

then it deletes the record successfully but memory is not getting freed

can anyone help me out on this problem

When you delete from SQL server the memory will not be removed until the database is truncated. If you want to store data temporarily create a temporary table with a prefix of #

CREATE TABLE #TEMPTABLE
|||

i want to remove one record from the table, and i did it by using "DELETE" statement with WHERE clause,

now i want to free memory which was occupied by recently deleted record.

what i know about TRUNCATE statement is that it deletes the whole table and we cannot use WHERE clause with TRUNCATE.

and i don't want to create temporary table.

so how can i use truncate?.

thanks for your suggestion

|||

Hi,

This problem make me concentrate to it ! I have the same problem, please read this article :

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

Can you tell me more,

Regards,

Tran Quang Phuong.

How to Free SQL Server 7 Transaction Log Space

Hi Guys,
My company SQL Server has allocated 6G+ of space for the transaction log space. However, only about 300MB of them are used.
Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?
Thanks in advance.
TangQ1 Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?
A1 Yes. For example:

USE YourDB
GO
DBCC SHRINKFILE (YourDBLog, 600)|||This doesn't work.
The free spaced allocated to Transaction Log still remain.

I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?

Originally posted by DBA
A1 Yes. For example:

USE YourDB
GO
DBCC SHRINKFILE (YourDBLog, 600)|||RE: This doesn't work.
The free spaced allocated to Transaction Log still remain.

I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?

It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.|||Thank you for your reply, I guess it is almost time to bring down the server for maintenance... :D

Originally posted by DBA
RE:

It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.|||RE: Thank you for your reply, I guess it is almost time to bring down the server for maintenance... :D

You are welcome.

You may be interested to know that MS supposedly changed the shrink algorithm somewhat in Sql Server 2k to make it act noticeably more quickly (apparently many users do not like to wait).

Actually I think actions taken by impatient 7.0 users to find a "quicker way" caused frequent headaches at Microsoft, and that led to the speedier shrink performance in Sql Server 2k. ;)|||The following is from BOL:

Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.

Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.

When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary.sql

how to free memory used by prior query statement within a batch by TSQL?

Just Like these:

-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)

/*
can I do something here to free res used by prior statement?
*/

select * from someOtherTable
--batch end

The Sqls above are written in a procedure to automating test for some select querys.What about this:

select ...
DBCC DROPCLEANBUFFERS
select ...|||Won't that force a recompile of everything?

Gotta look that up...

btw...SQL Server will grab as much memory is available, and will only release it if it's not using it and something else needs it...

It's not very sociable...|||thanks for help, but it seemed not work as I hoped.

Sqlserver used 20M memory before I run the select query;
Sqlserver used 123M memory after I run the select query;
Sqlserver still used 123M memory after I run 'DBCC DROPCLEANBUFFERS', but I want memory used by Sqlserver not larger than 20M;

I don't know exactly how memory useage affect the performance of next query's execution, so I write down my primal Intention:

select... -- query A

/* do something here to make query B to be executed just as query A was not executed before( or minish query A's affection). */

select... -- query B

could I make it?|||here is my test plan:

there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average time spending;
fetch next query from cursor;
end
...

Is there any better test plan?(just test time spending)

how to format this layout?

hi,
I have to incorporate a subreport into my main report. if my main report shows state, county, city and my subreport shows county and school name. how can I show just the schools that are in that county.
my question is how can I apply a condition to show schools in the subreport where county name (subreport ) = county name (main report) ?

attached is an image of the layout.

I know it's probably easy but I'm new to Crystal.

thank youYou can link the subreport with the main report. Right-click on sub-report and select 'Change Sub-report links' option. In the Link window,select the 'County Name' field from the Main report. Select the checkbox 'Select Data in Subreport based on field'.
Hope it helps !!!

Rashmi|||yup that was it .thank you very much

How to Format the Number for Total

Hai Friends,

I am using Crystal Report 9.2.
In my project, i am in need of finding the Subtotal, totals.

While doing so, its assigning the value as 2,234,456.00

i am in need of that display to be 22,34,456.00.

i tried for Formula Field & also Selection Formula's Group in Crystal Report.
In the Format Field, i am not able to customize for thousand.

Can any one help me to solve my problem for finding the total.Interesting question. I don't think you can do this easily. You might have to create a new formula field and use string manipulation.|||Hai Babu - Moderator,

Are u there? Why no reply from u?

Regards
Geetha|||hai Moderator,

What happened to u?
why u r not responding.
in crystal report as well as in access, we have this type of display for the totals.

rectify this.

Regards
geetha|||Convert the money to text and seperate the digits according to the format you want and combine them by ","

how to format the date data type input in DD-MM-YY

hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')

the date will automatically converted to 06-02-04 and store in DB .

Does it need to use SQL Rule or user-defined data type.Originally posted by verybrightstar
hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')

the date will automatically converted to 06-02-04 and store in DB .

Does it need to use SQL Rule or user-defined data type.

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,convert(varchar,'2/6/2004 11:07:46 AM',105))

how to format text fields

Hi all,
I´m just wondering, how to (RS2000):
How can I reach that a textfield content is being cut by a page break and
continued on page 2?
(In print preview mode: now if the textfield content doesn´t fit on the same
page, it´s printed on page 2, leaving a lot of blank space on page 1.
Let´s say the content is 30 lines long:
I simply want to get the first 20 lines on page 1 (till page end) and the
rest 10 lines on page 2.
I experimented, now CanGrow=True and CanShrink=True, but I didn´t find any
more properties controlling this behavior.)
Is there any way to format _parts_ of a textfield content in different ways
(f.e. some words bold)?
(Now each time a text shall be bold (f.e. headings and text) , I have to
create an own textbox and then an own textboxes for the text, followed by a
new sections and so on. That´s exhausting. Any better way to reach that?)
Thanks for tips!
Toni> How can I reach that a textfield content is being cut by a page break and
> continued on page 2?
> (In print preview mode: now if the textfield content doesn´t fit on the
> same page, it´s printed on page 2, leaving a lot of blank space on page 1.
Is the text field in a table or dumped to a textbox control?
-Tim
> Is there any way to format _parts_ of a textfield content in different
> ways (f.e. some words bold)?
There's no way I'm aware of that would allow this behavior. Font weight no
text boxes are pretty much an all or none property.
"Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
news:OUwE$OreGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I´m just wondering, how to (RS2000):
> How can I reach that a textfield content is being cut by a page break and
> continued on page 2?
> (In print preview mode: now if the textfield content doesn´t fit on the
> same page, it´s printed on page 2, leaving a lot of blank space on page 1.
> Let´s say the content is 30 lines long:
> I simply want to get the first 20 lines on page 1 (till page end) and the
> rest 10 lines on page 2.
> I experimented, now CanGrow=True and CanShrink=True, but I didn´t find any
> more properties controlling this behavior.)
> Is there any way to format _parts_ of a textfield content in different
> ways (f.e. some words bold)?
> (Now each time a text shall be bold (f.e. headings and text) , I have to
> create an own textbox and then an own textboxes for the text, followed by
> a new sections and so on. That´s exhausting. Any better way to reach
> that?)
> Thanks for tips!
> Toni
>|||Hi Tim,
The field content is bound to a textbox control.
(The whole report contains mostly text in a lot of textboxes.)
Thanks,
Toni
"Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> schrieb im Newsbeitrag
news:u8mnm6teGHA.4948@.TK2MSFTNGP04.phx.gbl...
>> How can I reach that a textfield content is being cut by a page break and
>> continued on page 2?
>> (In print preview mode: now if the textfield content doesn´t fit on the
>> same page, it´s printed on page 2, leaving a lot of blank space on page
>> 1.
> Is the text field in a table or dumped to a textbox control?
> -Tim
>> Is there any way to format _parts_ of a textfield content in different
>> ways (f.e. some words bold)?
> There's no way I'm aware of that would allow this behavior. Font weight
> no text boxes are pretty much an all or none property.
> "Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
> news:OUwE$OreGHA.1204@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I´m just wondering, how to (RS2000):
>> How can I reach that a textfield content is being cut by a page break and
>> continued on page 2?
>> (In print preview mode: now if the textfield content doesn´t fit on the
>> same page, it´s printed on page 2, leaving a lot of blank space on page
>> 1.
>> Let´s say the content is 30 lines long:
>> I simply want to get the first 20 lines on page 1 (till page end) and the
>> rest 10 lines on page 2.
>> I experimented, now CanGrow=True and CanShrink=True, but I didn´t find
>> any more properties controlling this behavior.)
>> Is there any way to format _parts_ of a textfield content in different
>> ways (f.e. some words bold)?
>> (Now each time a text shall be bold (f.e. headings and text) , I have to
>> create an own textbox and then an own textboxes for the text, followed by
>> a new sections and so on. That´s exhausting. Any better way to reach
>> that?)
>> Thanks for tips!
>> Toni
>|||Toni:
Are your text boxes within a Table? If so, use a List object instead of
Tables. I've found this to work much better. There seems to be a bug in the
Table object when displaying large amounts of text.
HTH.
Richard.
"Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
news:%23h763nxeGHA.4828@.TK2MSFTNGP05.phx.gbl...
> Hi Tim,
> The field content is bound to a textbox control.
> (The whole report contains mostly text in a lot of textboxes.)
> Thanks,
> Toni
> "Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> schrieb im Newsbeitrag
> news:u8mnm6teGHA.4948@.TK2MSFTNGP04.phx.gbl...
>> How can I reach that a textfield content is being cut by a page break
>> and continued on page 2?
>> (In print preview mode: now if the textfield content doesn´t fit on the
>> same page, it´s printed on page 2, leaving a lot of blank space on page
>> 1.
>> Is the text field in a table or dumped to a textbox control?
>> -Tim
>> Is there any way to format _parts_ of a textfield content in different
>> ways (f.e. some words bold)?
>> There's no way I'm aware of that would allow this behavior. Font weight
>> no text boxes are pretty much an all or none property.
>> "Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
>> news:OUwE$OreGHA.1204@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I´m just wondering, how to (RS2000):
>> How can I reach that a textfield content is being cut by a page break
>> and continued on page 2?
>> (In print preview mode: now if the textfield content doesn´t fit on the
>> same page, it´s printed on page 2, leaving a lot of blank space on page
>> 1.
>> Let´s say the content is 30 lines long:
>> I simply want to get the first 20 lines on page 1 (till page end) and
>> the rest 10 lines on page 2.
>> I experimented, now CanGrow=True and CanShrink=True, but I didn´t find
>> any more properties controlling this behavior.)
>> Is there any way to format _parts_ of a textfield content in different
>> ways (f.e. some words bold)?
>> (Now each time a text shall be bold (f.e. headings and text) , I have to
>> create an own textbox and then an own textboxes for the text, followed
>> by a new sections and so on. That´s exhausting. Any better way to reach
>> that?)
>> Thanks for tips!
>> Toni
>>
>|||I am haveing the exact same issue and my data is in a List object. Is
there anyone out there that has figured out a way to get the text to
flow spothly across pages?
Thanks
Edney Holder
Richard Wodabek wrote:
> Toni:
> Are your text boxes within a Table? If so, use a List object instead of
> Tables. I've found this to work much better. There seems to be a bug in t=he
> Table object when displaying large amounts of text.
> HTH.
> Richard.
> "Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
> news:%23h763nxeGHA.4828@.TK2MSFTNGP05.phx.gbl...
> > Hi Tim,
> >
> > The field content is bound to a textbox control.
> > (The whole report contains mostly text in a lot of textboxes.)
> >
> > Thanks,
> > Toni
> >
> > "Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> schrieb im Newsbeitrag
> > news:u8mnm6teGHA.4948@.TK2MSFTNGP04.phx.gbl...
> >> How can I reach that a textfield content is being cut by a page break
> >> and continued on page 2?
> >> (In print preview mode: now if the textfield content doesn=B4t fit on= the
> >> same page, it=B4s printed on page 2, leaving a lot of blank space on =page
> >> 1.
> >>
> >> Is the text field in a table or dumped to a textbox control?
> >>
> >> -Tim
> >>
> >> Is there any way to format _parts_ of a textfield content in different
> >> ways (f.e. some words bold)?
> >>
> >> There's no way I'm aware of that would allow this behavior. Font weig=ht
> >> no text boxes are pretty much an all or none property.
> >>
> >> "Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
> >> news:OUwE$OreGHA.1204@.TK2MSFTNGP02.phx.gbl...
> >> Hi all,
> >>
> >> I=B4m just wondering, how to (RS2000):
> >>
> >> How can I reach that a textfield content is being cut by a page break
> >> and continued on page 2?
> >> (In print preview mode: now if the textfield content doesn=B4t fit on= the
> >> same page, it=B4s printed on page 2, leaving a lot of blank space on =page
> >> 1.
> >> Let=B4s say the content is 30 lines long:
> >> I simply want to get the first 20 lines on page 1 (till page end) and
> >> the rest 10 lines on page 2.
> >> I experimented, now CanGrow=3DTrue and CanShrink=3DTrue, but I didn==B4t find
> >> any more properties controlling this behavior.)
> >>
> >> Is there any way to format _parts_ of a textfield content in different
> >> ways (f.e. some words bold)?
> >> (Now each time a text shall be bold (f.e. headings and text) , I have= to
> >> create an own textbox and then an own textboxes for the text, followed
> >> by a new sections and so on. That=B4s exhausting. Any better way to r=each
> >> that?)
> >>
> >> Thanks for tips!
> >> Toni
> >>
> >>
> >>
> >
> >|||I am haveing the exact same issue and my data is in a List object. Is
there anyone out there that has figured out a way to get the text to
flow spothly across pages?
Thanks
Edney Holder
Richard Wodabek wrote:
> Toni:
> Are your text boxes within a Table? If so, use a List object instead of
> Tables. I've found this to work much better. There seems to be a bug in t=he
> Table object when displaying large amounts of text.
> HTH.
> Richard.
> "Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
> news:%23h763nxeGHA.4828@.TK2MSFTNGP05.phx.gbl...
> > Hi Tim,
> >
> > The field content is bound to a textbox control.
> > (The whole report contains mostly text in a lot of textboxes.)
> >
> > Thanks,
> > Toni
> >
> > "Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> schrieb im Newsbeitrag
> > news:u8mnm6teGHA.4948@.TK2MSFTNGP04.phx.gbl...
> >> How can I reach that a textfield content is being cut by a page break
> >> and continued on page 2?
> >> (In print preview mode: now if the textfield content doesn=B4t fit on= the
> >> same page, it=B4s printed on page 2, leaving a lot of blank space on =page
> >> 1.
> >>
> >> Is the text field in a table or dumped to a textbox control?
> >>
> >> -Tim
> >>
> >> Is there any way to format _parts_ of a textfield content in different
> >> ways (f.e. some words bold)?
> >>
> >> There's no way I'm aware of that would allow this behavior. Font weig=ht
> >> no text boxes are pretty much an all or none property.
> >>
> >> "Toni Pohl" <atwork43@.hotmail.com__nospam> wrote in message
> >> news:OUwE$OreGHA.1204@.TK2MSFTNGP02.phx.gbl...
> >> Hi all,
> >>
> >> I=B4m just wondering, how to (RS2000):
> >>
> >> How can I reach that a textfield content is being cut by a page break
> >> and continued on page 2?
> >> (In print preview mode: now if the textfield content doesn=B4t fit on= the
> >> same page, it=B4s printed on page 2, leaving a lot of blank space on =page
> >> 1.
> >> Let=B4s say the content is 30 lines long:
> >> I simply want to get the first 20 lines on page 1 (till page end) and
> >> the rest 10 lines on page 2.
> >> I experimented, now CanGrow=3DTrue and CanShrink=3DTrue, but I didn==B4t find
> >> any more properties controlling this behavior.)
> >>
> >> Is there any way to format _parts_ of a textfield content in different
> >> ways (f.e. some words bold)?
> >> (Now each time a text shall be bold (f.e. headings and text) , I have= to
> >> create an own textbox and then an own textboxes for the text, followed
> >> by a new sections and so on. That=B4s exhausting. Any better way to r=each
> >> that?)
> >>
> >> Thanks for tips!
> >> Toni
> >>
> >>
> >>
> >
> >sql

Friday, March 30, 2012

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP via VPN to sites with real adresses on different IP's ?

How to FTP DB backup File from one server to the other

How to FTP

How to from 1apple,1orange,1apple to 2apple,1*orange

How to Freeze the header for Reports

How to free up the space ...

How to free up the space ...

How to free up SQL 2000 transaction log space?

how to free the memory occupied by "blob" in MS sql server

how to free the memory occupied by "blob" in MS sql server

How to Free SQL Server 7 Transaction Log Space

how to free memory used by prior query statement within a batch by TSQL?

how to format this layout?

How to Format the Number for Total

how to format the date data type input in DD-MM-YY

how to format text fields

filter sqldatasource

Blog Archive

About Me