Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:

> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> doesn't
>
>

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....
Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....
|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:

> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> doesn't
>
>

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:
> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> > I have a table wherein previous entries were deleted but the fields
> doesn't
> > go away
> > ex.
> > tbl_name
> > 1 name 1
> > 2 (the entry is deleted but this is still showing a blank space)
> > 3 (the entry is deleted but this is still showing a blank space)
> > 4 (the entry is deleted but this is still showing a blank space)
> > 5 name 2
> >
> > How do i delete the blank spaces in entries 2-4?
> > thanks....
>
>

De-install and re-install problems SQL server 2005

I never installed previous beta versions on my computer before. But now I have some serious problem after an incomplete de-install and a re-install of SQL server 2005 express advanced. I hope anybody can help me to solve this. Because the first installation eas not succesfull I had to do an de-install. I have tried to unstall through start/control panel/ add-remove programms and even there not all programs were removed. And that caused the problem. After that I wanted to re-install again but an msi file was missing. So I did a de-install agian. Tried to re-install again but with a lot of errors. After looking at the library I found a program: Windows Install CleanUp. I installed it and that even gives an error for all programs installed with SQL 2005.

After that I ran a program called MsiInv to clean but without any succes because this program reported a run-tim error. I have all reports on my computer but don't know how to attach them. If you think you can help me I could send you all the reports.

Regards,

Jan J

When you go to Add/Remove Programs, do you still have a SQL Server entry? If so, remove it. If not, what is the error you receive when you try to install again? You mentioned a missing MSI file. Can you give the exact error message? Also, can you check your SQL install folder (%windir%\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG) for the text string "value 3"? If an error was returned during setup, it will appear about 5-10 lines above this return code in the logs.

Thanks,
Sam Lester (MSFT)

|||

Hi Sam,

Indeed there still are SQL entries left without the options to change/remove, only the date last viewed is available. When clicking you are beeing informed that the entry no longer exists and beeing asked to remove it. When clicking YES, nothing happens, the entry remains.

When re-installing again I am beeing asked for the msi file needed for the setup with a choice to read it from a CD or to download it from the internet. I first choose to use the download option and second I choose to use a msi file from the substracted install file I downloaded from the internet. Both result in a failed Setup and error messages for the install of MSXML6, SQL VSS Writer, SQL Server Database Services, OXC11, SQL Server Backward-Compatibility Files and Reporting Services. I decided run the uninstall tool I found in the MS library at "Unistalling Previous Versions of Visual Studio 2005", but without any succes. When running MSICUU it gave "run-time error '-2147024882 (8007000e)': System Error &H80004005(-2147467259). A not further defined error." And no program could be de-installed that way. Then I decided to run msiinv but I couldn't get a *.txt file output. Unless I attempted several times to de-install programs as SQL Server 2005 (and accompanying programs, report server, msvml6, vss, etc) and Visual Studio 2005. msiinv still reports their presence.

All this information I visible stored in a mail message that I first sent to an other Microsoft department. If you are interested, please give me a mail address where I can send it so you can have a good look at all the error messages.

I can not work without a computer so I used a recently back-up file, without the SQL and Visual installation, to restore my C-drive. All I have to do now is re-install everything. But I think there should be an improvement on how the add/remove programs work for SQL and Visual Studio. To me it seems that there is beeing left maps and folders regarding these programs that were not there before the install. One is the %\Program Files|Microsoft SQL Server folder and the other is the My Documents\Visual Web Developer folder. It might be usefull to let the user decide which files/folders to be left on his computer.

I also would like to enter another comment. I noticed during this "new" install of MSVML6 SQL, Setup Support Files and SQL Server Database Services the Status reports that items are beeing removed, not for sure if all is been backed-up, from the system and later for some components during the Setup Progress back-up files were removed. I remember that during installations of windows people always had the choice to backup their old version which could be restored later after a de-install. I keep on feeling that with the SQL install this not occurs and that this might cause a problem during de-install.

I am sorry but with the restore there is no longer a LOG file for the Setup Bootstrap.

Regards Jan J

Defaulting Start and End dates to the previous month.

I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.

For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.

How can I do this?

Nevermind... I used the following two functions. However, if you know of a better way to do this, please let me know.

Public Function EndDt() As Date

Dim Today As Date = Date.Today

Dim Day As Integer = Today.Day

Dim EndDate As Date

EndDate = Today.AddMonths(1)

EndDate = Today.AddDays(-Day)

Return EndDate

End Function

Public Function StartDt() As Date

Dim Today As Date = Date.Today

Dim Day As Integer = Today.Day - 1

Dim StartDate As Date

StartDate = Today.AddMonths(-1)

StartDate = Today.AddDays(-Day)

Return StartDate

End Function

|||

I can't think of a quick direct way

but I do want to suggest using BETWEEN, and first of month (so that you don't have to worry about # of days in a month)

e.g. for 05/17/2007

you'd want SQL code to be

Code Snippet

column BETWEEN 04/01/2007 AND 05/01/2007

since BETWEEN is inclusive, but only up to '05/01/2007 00:00:00', anything after 05/01/2007 midnight will NOT show up

so in backend SQL

it'll be like

Code Snippet

SELECT
previous_month = CAST( MONTH(getdate())-1 AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)
, current_month = CAST( MONTH(getdate()) AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)

then you get

previous_month current_month

4/01/2007 5/01/2007

Default SqlServer database creation path

Hi,
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.

Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS

declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT

SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]

|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.
Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||

The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.

The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot

and

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath

The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...

If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.

You can find the registry key for your SQL 2005 instance here:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>

...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>

Now go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup

to get SQLDataRoot and SQLPath...

then go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer

to get DefaultData and DefaultLog...

Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.

|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

But fails for

master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures',

N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT

it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths

Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1

Default SqlServer database creation path

Hi,
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.

Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS

declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT

SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]

|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.
Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||

The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.

The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot

and

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath

The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...

If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.

You can find the registry key for your SQL 2005 instance here:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>

...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>

Now go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup

to get SQLDataRoot and SQLPath...

then go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer

to get DefaultData and DefaultLog...

Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.

|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

But fails for

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures', N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT

it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths

Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1

Default SqlServer database creation path

Hi,
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.

Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS

declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT

SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]

|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.
Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||

The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.

The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot

and

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath

The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...

If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.

You can find the registry key for your SQL 2005 instance here:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>

...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>

Now go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup

to get SQLDataRoot and SQLPath...

then go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer

to get DefaultData and DefaultLog...

Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.

|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

But fails for

master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures',

N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT

it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths

Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1