Removing Zeros

Hi,
I have a column cost with sample record as '00003433' . how can i remove all zeros in left side alone?.

Out put : 3433.

Thanks
Raj

You can just convert the value to INT.

e.g.

Code Snippet

select convert(int,'00003433')

|||

Try this..

Select replace(Columnname, '0','')

From table

*** Place your tablename and column name in the query..

|||

Hi,

Oj is correct, use convert function to removes all zeros in left side of your column data, but when you use "repalce" function it'll removes all zeros, not only in left side.

for eg:

declare @.a varchar(8)
set @.a = '00003405'

select convert(int,@.a) ,replace(@.a, '0','')

Thanks & Regards,

Kiran.Y

Removing Windows User from the Security folder

Can't seem to locate the stored procedure for removing a Windows Authenticat
ed User from the Security folder.
sp_droplogin doesn't seem to work. It only works on SQL Logins.
Can't find what appears to be proper procedure in Master database.
What I get is the message "The Login [login name] doesn't exist".
Thanks for any help.
ScottSee sp_revokelogin in Books On Line, sp_droplogin is for SQL logins.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Scott" <scasti1@.cox.net> wrote in message
news:E38AC590-50C0-44B3-BDA2-91FCC0E43CB3@.microsoft.com...
> Can't seem to locate the stored procedure for removing a Windows
Authenticated User from the Security folder.
> sp_droplogin doesn't seem to work. It only works on SQL Logins.
> Can't find what appears to be proper procedure in Master database.
> What I get is the message "The Login [login name] doesn't exist".
> Thanks for any help.
> Scott
>

removing windows group that's a database user

When attempting to remove a windows group using the
stored procedure xp_dropuser obtain the following error:
Server: Msg 15008, Level 16, State 1, Procedure
sp_dropuser, Line 12
User 'D0550000\CSMBalanceInquiry' does not exist in the
current database.
D0550000 is the domain and CSMBalanceInquiry is the
windows group.
Before removing this group I execute sp_helpuser and do a
copy and paste of the name.
If I attempt to remove this group through the enterprise
manager, I do not have a problem. I tried to search in
the Microsoft knowledge base and could not find an
article explaining this problem. Thank you for your help
in advance.You need to specify the value returned from sp_helpuser for the "name" of
the user\group.
You can see the valid names in the database by running select * from
sysusers while in that database.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||is the group a role or a user? Note that sp_helpuser can return information
about roles as well as users...
If you think that might be the case try sp_droprole.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:009e01c3c3fe$8e7b9880$a401280a@.phx.gbl...

quote:

---

> When attempting to remove a windows group using the
> stored procedure xp_dropuser obtain the following error:
> Server: Msg 15008, Level 16, State 1, Procedure
> sp_dropuser, Line 12
> User 'D0550000\CSMBalanceInquiry' does not exist in the
> current database.
> D0550000 is the domain and CSMBalanceInquiry is the
> windows group.
> Before removing this group I execute sp_helpuser and do a
> copy and paste of the name.
> If I attempt to remove this group through the enterprise
> manager, I do not have a problem. I tried to search in
> the Microsoft knowledge base and could not find an
> article explaining this problem. Thank you for your help
> in advance.

---

Removing white spaces in a varchar column

I have a table . It has a nullable column called AccountNumber, which
is of varchar type. The AccountNumber is alpha-numeric. I want to take
data from this table and process it for my application. Before doing
that I would like to filter out duplicate AccountNumbers. I get most of
the duplicates filtered out by using this query:

select * from customers
where AccountNumber NOT IN (select AccountNumber from customers where
AccountNumber <> '' group by AccountNumber having count(AccountNumber)
> 1)

But there are few duplicate entries where the actual AccountNumber is
same, but there is a trailing space in first one, and hence this
duplicate records are not getting filtered out. e.g
"abc123<white-space>" and "abc123" are considered two different entries
by above query.

I ran a query like :

update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)

But even after this query, the trailing space remains, and I am not
able to filter out those entries.

Am I missing anything here? Can somebody help me in making sure I
filter out all duplicate entries ?

Thanks,
RadAre you sure it's a whitespace? It might be a line break. To verify,
you might try something like:

SELECT AccountNumber
FROM customers
WHERE AccountNumber LIKE '%" + CHAR(10)
OR AccountNumber LIKE '%' + CHAR(13)

Oh, and always be cautious about running an UPDATE statement with no
WHERE clause.

HTH,
Stu|||Thanks Stu, you made my day. :)

It was a line break after a white space. I was concentrating only on
the white space and did not notice the line break. So in my first
statement, I first use this query:

UPDATE customers
SET AccountNumber = substring(AccountNumber, 1, PATINDEX('CHAR(13)',
AccountNumber))
WHERE AccountNumber like '%' + CHAR(13)

and it worked!

Thanks again!

Regards,
Rad

Stu wrote:
> Are you sure it's a whitespace? It might be a line break. To verify,
> you might try something like:
> SELECT AccountNumber
> FROM customers
> WHERE AccountNumber LIKE '%" + CHAR(10)
> OR AccountNumber LIKE '%' + CHAR(13)
> Oh, and always be cautious about running an UPDATE statement with no
> WHERE clause.
> HTH,
> Stu|||>> table . It has a nullable column called AccountNumber, which is of VARCHAR(n) type. <<

That is a very bad code design because it prevents check digits and
makes validation rules more complex. If you had validation rules in the
DDL, you would not have this problem. First mop the floor, then fix
the leak.

Removing White Space

I have created a report with a drill down button for each unique record. However, when the nodes are not expanded (the default), the report displays with large amounts of white space (varying according to how much space is required once the node is fully expanded). Is there any way to remove this whitespace? Thanks in advance.

I've seen this before where I explicitly set the group header for the inner group to be invisible, but i accidentally left the footer visible. Got the exact same symptom.

Hope that's useful.

|||

I tried this but unfortunately it didn't work - all my group headings are set to invisible... (when i initially load the report, I only view the resource names, no other information - indicating that they are set to invisible)

|||You need to make sure you are hiding the groups, not the individual rows inside the group. Edit the group properties and set the visibility there.

Removing Weekends?

Hi, i'm new to the SQL game and have been given the task of removing all the weekends in a report so as it only shows the weeks as mon-fri.

I've checked out a few pieces of code but can't seem to get it to work. Anyone able to help?

Determing the Datenumber of the Week depends on the user settings (session settings) of Datefirst, you could also check for the DATEPARTed full day, but this is not language independent, see the following snippet to see how to use the query.

SET Datefirst 1
SELECT 'This is a workday' WHERE Datepart(dw,GETDATE()) < 6 -- 6 is Saturday.

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||

Also consider using a calendar table. Joining on the date you can eliminate weekends, holidays, whatever.

Here is an article I wrote that loads a calendar table:

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||The next thing they will ask is to remove holidays, so you should just go ahead and do the calendar file.

My method creates a calendar file with all holidays and Sat/Sun for the year that are NOT holidays. Then this is easy to do in a report:

SET @.calendardays = DATEADD(@.date,-1*(SELECT COUNT(*) FROM HOLIDAYS WHERE HolidayDate >= @.date AND HolidayDate <= @.date),d)

This works well for short periods, but not if you want to subtract weekends between 1/1/1923 and 10/24/2006. Then you need to program it.

Removing User Right Assignments form SQL-Server Account

Hello everybody,
I'm currently securing a w2k box according to some recommendations
made by a company that audited our server.
Now among their recommendations is to remove the following rights from
the sql-server account. (The SQL Server runs under its own 'user
account' and in mixed mode)
According to them I should remove these permissions:
- Act as part of the Operating System
- Replace a process level token
- Increase quotas
- Log on as batch job
- Log on as service
I've browsed the web and found some documents, however none could
really answer which SQL-Features depend on these services.
Maybe someone in this forum has some experience or helpful urls.
Thanks very much.
the document:
SQL Server 2000 C2 Administrator's and User's Security Guide
at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/security/sqlc2.asp
somehow mentions that these four services are necessary, but not why.
* Act as part of the operating system.
* Increase quotas.
* Replace a process-level token.
* Log on as a service.Hallo Beat(e) M=FCller,
at first you must NOT REMOVE the right
- Log on as service (The sql-Server does not start anymore!)
second the others you may try but I do not suggest it. Please look at
http://www.microsoft.com/sql/techinfo/administration/2000/s
ecurity/securingsqlserver.asp
there are useful tipps to securing the sql-Server
- Log on as a batch job (removable depends on your Envoirement - !CHECK! does your sqlserver service use a start batch?)
- the others will be used for internal tuning and interaction - I you REALLY must to remove them try it on your on risk!
CU Ralf

removing user

Hello,
I have a problem with removing an user on a SQLSERVER V7 base.
I can suppress the user, but it still exist for the role PUBLIC.
Can you help me ?Do you mean that you remove the user from the database, but they can still get in through Public, or are you getting an error when you try to delete the user?

blindman|||Hi...
What u mean by suppress the user...? What errror message u r getting?
U can remove user from User Roles still it will exists in Public. Do the user owns some objects ? guests are allowed in the DB ?

Cheers :)

Shaji

Originally posted by jleb
Hello,
I have a problem with removing an user on a SQLSERVER V7 base.

I can suppress the user, but it still exist for the role PUBLIC.

Can you help me ?|||I remove the user from the database, but it still exists whem I look the Public role.

Thank you fopr your help, I have found a solution with the command sp_revokedbaccess.

Originally posted by blindman
Do you mean that you remove the user from the database, but they can still get in through Public, or are you getting an error when you try to delete the user?

blindman

Removing un necessary log

In a production database I'm finding the log file is increasing rapidly and
after taking a backup I find the log file is still increasing fast. Since th
e database is running and highly in use in production evironment I can't use
trunc,log on chkpt option
on the database.
What should I do at this moment plz suggest me keeping in view the database
is running and production database.
Regards and thanks in advance,
Sunil DashIf the database is in Full Recovery mode, backing up the transaction log
will make space available for re-use..(But you must have done at least one
full database backup first.)
If you have long running transactions, that will prevent the transaction log
from truncating properly as well...
DBCC opentran ( in books on line) will show you the SPID of the longest
running open transaction... First find out if there is a long running
transaction and what it is..
I suggest you backup the t-log now...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E45928-E815-428E-836A-B13A4E080BF1@.microsoft.com...
> In a production database I'm finding the log file is increasing rapidly
and after taking a backup I find the log file is still increasing fast.
Since the database is running and highly in use in production evironment I
can't use trunc,log on chkpt option on the database.
>
> What should I do at this moment plz suggest me keeping in view the
database is running and production database.
>
> Regards and thanks in advance,
>
> Sunil Dash

Removing un necessary log

In a production database I'm finding the log file is increasing rapidly and after taking a backup I find the log file is still increasing fast. Since the database is running and highly in use in production evironment I can't use trunc,log on chkpt option
on the database.
What should I do at this moment plz suggest me keeping in view the database is running and production database.
Regards and thanks in advance,
Sunil Dash
If the database is in Full Recovery mode, backing up the transaction log
will make space available for re-use..(But you must have done at least one
full database backup first.)
If you have long running transactions, that will prevent the transaction log
from truncating properly as well...
DBCC opentran ( in books on line) will show you the SPID of the longest
running open transaction... First find out if there is a long running
transaction and what it is..
I suggest you backup the t-log now...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E45928-E815-428E-836A-B13A4E080BF1@.microsoft.com...
> In a production database I'm finding the log file is increasing rapidly
and after taking a backup I find the log file is still increasing fast.
Since the database is running and highly in use in production evironment I
can't use trunc,log on chkpt option on the database.
>
> What should I do at this moment plz suggest me keeping in view the
database is running and production database.
>
> Regards and thanks in advance,
>
> Sunil Dash

Removing un necessary log

In a production database I'm finding the log file is increasing rapidly and after taking a backup I find the log file is still increasing fast. Since the database is running and highly in use in production evironment I can't use trunc,log on chkpt option on the database
What should I do at this moment plz suggest me keeping in view the database is running and production database
Regards and thanks in advance
Sunil DashIf the database is in Full Recovery mode, backing up the transaction log
will make space available for re-use..(But you must have done at least one
full database backup first.)
If you have long running transactions, that will prevent the transaction log
from truncating properly as well...
DBCC opentran ( in books on line) will show you the SPID of the longest
running open transaction... First find out if there is a long running
transaction and what it is..
I suggest you backup the t-log now...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E45928-E815-428E-836A-B13A4E080BF1@.microsoft.com...
> In a production database I'm finding the log file is increasing rapidly
and after taking a backup I find the log file is still increasing fast.
Since the database is running and highly in use in production evironment I
can't use trunc,log on chkpt option on the database.
>
> What should I do at this moment plz suggest me keeping in view the
database is running and production database.
>
> Regards and thanks in advance,
>
> Sunil Dash

Removing Tuning Wizard Indexes

This summary is not available. Please click here to view the post.

Removing the time from the results of GETDATE()

I have been using CONVERT(datetime, CONVERT(varchar,
GETDATE(), 1)) to remove the time portion of the date,
and it has worked reliably for a long time. Due to
changes in the server's environment (it has been moved to
Norway), this statement will no longer work when invoked
from within Access. Is there an alternative method for
doing this that does not risk complications due to
differing date formats?
Any help would be greatly appreciated - the errors this
is causing is wreaking havoc on an application that has,
up to now, run reliably for almost nine years and three
versions of SQL Server!
Daniel Inman
Intelligent Query EnginesThe best date format for international apps is yyyymmdd. Try:
SELECT CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112))
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Daniel Inman" <anonymous@.discussions.microsoft.com> wrote in message
news:069601c3aa51$9a6e5220$a301280a@.phx.gbl...
> I have been using CONVERT(datetime, CONVERT(varchar,
> GETDATE(), 1)) to remove the time portion of the date,
> and it has worked reliably for a long time. Due to
> changes in the server's environment (it has been moved to
> Norway), this statement will no longer work when invoked
> from within Access. Is there an alternative method for
> doing this that does not risk complications due to
> differing date formats?
>
> Any help would be greatly appreciated - the errors this
> is causing is wreaking havoc on an application that has,
> up to now, run reliably for almost nine years and three
> versions of SQL Server!
> Daniel Inman
> Intelligent Query Engines|||Some of the ways to get this are :
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112))
SELECT CAST(CONVERT(CHAR(8),CURRENT_TIMESTAMP,112) AS DATETIME)
SELECT CURRENT_TIMESTAMP - {fn CURRENT_TIME}
SELECT CONVERT(DATETIME, {fn CURRENT_DATE()})
SELECT {fn curdate()}
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Daniel Inman" <anonymous@.discussions.microsoft.com> wrote in message
news:069601c3aa51$9a6e5220$a301280a@.phx.gbl...
> I have been using CONVERT(datetime, CONVERT(varchar,
> GETDATE(), 1)) to remove the time portion of the date,
> and it has worked reliably for a long time. Due to
> changes in the server's environment (it has been moved to
> Norway), this statement will no longer work when invoked
> from within Access. Is there an alternative method for
> doing this that does not risk complications due to
> differing date formats?
>
> Any help would be greatly appreciated - the errors this
> is causing is wreaking havoc on an application that has,
> up to now, run reliably for almost nine years and three
> versions of SQL Server!
> Daniel Inman
> Intelligent Query Engines|||Hi,
If you want the date in a varchar, use:
select convert(varchar, Getdate(),1)
If you want it as a datetime:
select convert(datetime,convert(varchar, Getdate(),1))
(the time portion will be 00:00:00.000)
Rups|||> If you want it as a datetime:
> select convert(datetime,convert(varchar, Getdate(),1))
> (the time portion will be 00:00:00.000)
This is what Daniel was using already, and the problem is that we shouldn't
write code like this as it doesn't work in an international environment:
set language us_english
select convert(datetime,convert(varchar, Getdate(),1))
set language FRENCH
select convert(datetime,convert(varchar, Getdate(),1))
set language us_english
Code 112 should be used (as per the pther posts) as it prodices a "safe"
format: 'yyyymmdd'.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rupesh Kaslay" <rupeshkaslay@.yahoo.com> wrote in message
news:08eb01c3aa7a$28595120$a101280a@.phx.gbl...
> Hi,
> If you want the date in a varchar, use:
> select convert(varchar, Getdate(),1)
> If you want it as a datetime:
> select convert(datetime,convert(varchar, Getdate(),1))
> (the time portion will be 00:00:00.000)
> Rups
>

Removing the spaces left by hidden columns or rows

I have written a comprehensive report that is used as a subreport for many
others. The comprehensive report supports dynamic grouping and the dynamic
hiding of columns or rows.
My problem is that if a column is hidden, a huge gap is seen in the report
where the column is hidden.
A C D
+=====+ (hidden column) +=====+==========+
|xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
...
Is there any way to make column A move to live where the hidden column "B"
would have been had it not been hidden?
Thanks.
--
Jim Campbell
FujiFilm eSystemsYou have to put the colum width to 0 (in the properties window)
"Jim Campbell" wrote:
> I have written a comprehensive report that is used as a subreport for many
> others. The comprehensive report supports dynamic grouping and the dynamic
> hiding of columns or rows.
> My problem is that if a column is hidden, a huge gap is seen in the report
> where the column is hidden.
> A C D
> +=====+ (hidden column) +=====+==========+
> |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> ...
> Is there any way to make column A move to live where the hidden column "B"
> would have been had it not been hidden?
> Thanks.
> --
> Jim Campbell
> FujiFilm eSystems|||If only it were that simple... I need to set the width based on whether or
not the column is hidden. Yet, I read the post below:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
that seems to imply there is no way to do this under the current release.
Has anyone found a work around?
- Jim
--
Jim Campbell
FujiFilm eSystems
"Antoon" wrote:
> You have to put the colum width to 0 (in the properties window)
> "Jim Campbell" wrote:
> > I have written a comprehensive report that is used as a subreport for many
> > others. The comprehensive report supports dynamic grouping and the dynamic
> > hiding of columns or rows.
> >
> > My problem is that if a column is hidden, a huge gap is seen in the report
> > where the column is hidden.
> > A C D
> > +=====+ (hidden column) +=====+==========+
> > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> >
> > ...
> >
> > Is there any way to make column A move to live where the hidden column "B"
> > would have been had it not been hidden?
> >
> > Thanks.
> > --
> > Jim Campbell
> > FujiFilm eSystems|||You probably have a condition that you use to hide the column; try setting
the colum width to =iif(condition;0;20)
"Jim Campbell" wrote:
> If only it were that simple... I need to set the width based on whether or
> not the column is hidden. Yet, I read the post below:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> that seems to imply there is no way to do this under the current release.
> Has anyone found a work around?
> - Jim
> --
> Jim Campbell
> FujiFilm eSystems
>
> "Antoon" wrote:
> > You have to put the colum width to 0 (in the properties window)
> >
> > "Jim Campbell" wrote:
> >
> > > I have written a comprehensive report that is used as a subreport for many
> > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > hiding of columns or rows.
> > >
> > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > where the column is hidden.
> > > A C D
> > > +=====+ (hidden column) +=====+==========+
> > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > >
> > > ...
> > >
> > > Is there any way to make column A move to live where the hidden column "B"
> > > would have been had it not been hidden?
> > >
> > > Thanks.
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems|||But I am pretty sure that u can't write an expression in the width property.
U can just enter numeric values..
am I wrong ?
"Antoon" wrote:
> You probably have a condition that you use to hide the column; try setting
> the colum width to =iif(condition;0;20)
> "Jim Campbell" wrote:
> > If only it were that simple... I need to set the width based on whether or
> > not the column is hidden. Yet, I read the post below:
> >
> > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> >
> > that seems to imply there is no way to do this under the current release.
> > Has anyone found a work around?
> >
> > - Jim
> > --
> > Jim Campbell
> > FujiFilm eSystems
> >
> >
> > "Antoon" wrote:
> >
> > > You have to put the colum width to 0 (in the properties window)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > I have written a comprehensive report that is used as a subreport for many
> > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > hiding of columns or rows.
> > > >
> > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > where the column is hidden.
> > > > A C D
> > > > +=====+ (hidden column) +=====+==========+
> > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > >
> > > > ...
> > > >
> > > > Is there any way to make column A move to live where the hidden column "B"
> > > > would have been had it not been hidden?
> > > >
> > > > Thanks.
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems|||Yeah you're rigth, sorry about that.
Now I rember how to do it. Put the width value to zero and flag the
properties/"can increase to accomadate length" .
When visible the colomn width will be fine, when invisible it will
be...truly invisible
"Jerome" wrote:
> But I am pretty sure that u can't write an expression in the width property.
> U can just enter numeric values..
> am I wrong ?
> "Antoon" wrote:
> > You probably have a condition that you use to hide the column; try setting
> > the colum width to =iif(condition;0;20)
> >
> > "Jim Campbell" wrote:
> >
> > > If only it were that simple... I need to set the width based on whether or
> > > not the column is hidden. Yet, I read the post below:
> > >
> > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > >
> > > that seems to imply there is no way to do this under the current release.
> > > Has anyone found a work around?
> > >
> > > - Jim
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > You have to put the colum width to 0 (in the properties window)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > hiding of columns or rows.
> > > > >
> > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > where the column is hidden.
> > > > > A C D
> > > > > +=====+ (hidden column) +=====+==========+
> > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > >
> > > > > ...
> > > > >
> > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > would have been had it not been hidden?
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems|||Hi,
Access to the Reporting Service site at Microsoft appears to be
hanging, so I'll reply using Google Groups...
Can you tell me where to find this 'can increase to accommodate length'
option? The only thing I see that is like this is "can increase to
accommodate height."
Can this width option be used if there is a textbox in the column? Can
a textbox widen to accommodate contents, too?
Thanks,
Jim Campbell
Antoon wrote:
> Yeah you're rigth, sorry about that.
> Now I rember how to do it. Put the width value to zero and flag the
> properties/"can increase to accomadate length" .
> When visible the colomn width will be fine, when invisible it will
> be...truly invisible
> "Jerome" wrote:
> > But I am pretty sure that u can't write an expression in the width property.
> > U can just enter numeric values..
> > am I wrong ?
> >
> > "Antoon" wrote:
> >
> > > You probably have a condition that you use to hide the column; try setting
> > > the colum width to =iif(condition;0;20)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > If only it were that simple... I need to set the width based on whether or
> > > > not the column is hidden. Yet, I read the post below:
> > > >
> > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > >
> > > > that seems to imply there is no way to do this under the current release.
> > > > Has anyone found a work around?
> > > >
> > > > - Jim
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > You have to put the colum width to 0 (in the properties window)
> > > > >
> > > > > "Jim Campbell" wrote:
> > > > >
> > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > hiding of columns or rows.
> > > > > >
> > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > where the column is hidden.
> > > > > > A C D
> > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > >
> > > > > > ...
> > > > > >
> > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > would have been had it not been hidden?
> > > > > >
> > > > > > Thanks.
> > > > > > --
> > > > > > Jim Campbell
> > > > > > FujiFilm eSystems|||I did my own research here, and I see that the "CanGrow" feature of
textboxes applies only to height and not width in this particular
version of MS Reporting Services. I do not see any way to do what I am
asking.
To illustrate what I have today:
col1--col2--col3--data1--data2
X Y Z
where col1 may or may not show field X if the user groups by field X,
col2 may or may not show field Y if the user groups by field Y, etc...
This means that if, for example, the user chooses not to group by field
Y, the report will appear like this
col1 col3--data1--data2
X Z
I suppose that if I found a way to have true dynmaic grouping, e.g., by
having col3 group by X, Y, or Z, rather than have each column represent
one field toggled on or off with grouping, I could it make it all
better, but I would still have the problem of spaces to the left of the
report, representing all the non-grouped columns.
I guess we'll have to put this problem down to the limitations inherent
in this "1.0-version" piece of software.
- Jim
jtk6204@.gmail.com wrote:
> Hi,
> Access to the Reporting Service site at Microsoft appears to be
> hanging, so I'll reply using Google Groups...
> Can you tell me where to find this 'can increase to accommodate length'
> option? The only thing I see that is like this is "can increase to
> accommodate height."
> Can this width option be used if there is a textbox in the column? Can
> a textbox widen to accommodate contents, too?
> Thanks,
> Jim Campbell
> Antoon wrote:
> > Yeah you're rigth, sorry about that.
> > Now I rember how to do it. Put the width value to zero and flag the
> > properties/"can increase to accomadate length" .
> > When visible the colomn width will be fine, when invisible it will
> > be...truly invisible
> >
> > "Jerome" wrote:
> >
> > > But I am pretty sure that u can't write an expression in the width property.
> > > U can just enter numeric values..
> > > am I wrong ?
> > >
> > > "Antoon" wrote:
> > >
> > > > You probably have a condition that you use to hide the column; try setting
> > > > the colum width to =iif(condition;0;20)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > If only it were that simple... I need to set the width based on whether or
> > > > > not the column is hidden. Yet, I read the post below:
> > > > >
> > > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > > >
> > > > > that seems to imply there is no way to do this under the current release.
> > > > > Has anyone found a work around?
> > > > >
> > > > > - Jim
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems
> > > > >
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > You have to put the colum width to 0 (in the properties window)
> > > > > >
> > > > > > "Jim Campbell" wrote:
> > > > > >
> > > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > > hiding of columns or rows.
> > > > > > >
> > > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > > where the column is hidden.
> > > > > > > A C D
> > > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > > >
> > > > > > > ...
> > > > > > >
> > > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > > would have been had it not been hidden?
> > > > > > >
> > > > > > > Thanks.
> > > > > > > --
> > > > > > > Jim Campbell
> > > > > > > FujiFilm eSystems

Removing the negative sign from an integer

How can I remove the - after an import into a database. I want to be able to convert all negative numbers to positive, once the data has been imported into the table

Any ideas?

If you dont have too many rows (not in millions) you can run a quick UPDATE statement.

UPDATE yourTable SET colA = colA * -1 WHERE colA < 0

|||

forgot to say thanks, did the trick.

removing the logs

does anyone know if there is a way to remove transaction
logs applied to database that was restored in a standby
mode ? i want to be able to read data from a database
restored from a full db backup before the tran logs were
applied.
Thanks very much,
Natasa
Restores only go forward, not backward. You will have to restore the
databas to an earlier point in time.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"natasa" <anonymous@.discussions.microsoft.com> wrote in message
news:041a01c4dc6f$a4b59260$a601280a@.phx.gbl...
> does anyone know if there is a way to remove transaction
> logs applied to database that was restored in a standby
> mode ? i want to be able to read data from a database
> restored from a full db backup before the tran logs were
> applied.
> Thanks very much,
> Natasa

removing the logs

does anyone know if there is a way to remove transaction
logs applied to database that was restored in a standby
mode ? i want to be able to read data from a database
restored from a full db backup before the tran logs were
applied.
Thanks very much,
NatasaRestores only go forward, not backward. You will have to restore the
databas to an earlier point in time.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"natasa" <anonymous@.discussions.microsoft.com> wrote in message
news:041a01c4dc6f$a4b59260$a601280a@.phx.gbl...
> does anyone know if there is a way to remove transaction
> logs applied to database that was restored in a standby
> mode ? i want to be able to read data from a database
> restored from a full db backup before the tran logs were
> applied.
> Thanks very much,
> Natasa

Removing the default value of RS Parameter (Value:"<Select a Value>")

I want to remove the default value of RS Parameter (Value:"<Select a Value>") but don't know how. Is anyone here can help me out?

HI

You can do this by giving the parameter, your own default
value, or you van go to the report properties Parameters
in the report manager and remove the default value by
de-selecting the checkbox.

Not sure what you wanted but this is how I
understood the question.

G

Removing the \SQLEXPRESS instance name on Dell laptops

I am installing SQL 2005 Express on several laptops (IBM Thinkpads and Dell latitudes) and I've noticed that doing the exact came configuration options with the exact same file, the IBM thinkpads just take the Machine name for SQL Server, while the Dells take Machinename\SQLEXPRESS. I am wondering how I can get it to just take the machine name on the Dells.

Thanks a lot

You might want to take a look at what's already installed on those laptops before you install SQL Express. I know that on our Dell servers there's an application called IT Assist, which uses a local SQL Server database. Dell may have already installed SQL Express on those laptops for this application, and it's using the default instance. Then, when you install SQL Express, the default instance is already used, so it installs the named installed you've identified.|||I believe I have removed all traces of anything SQL Server related that I could find as far as the add or remove programs, but I will take a look at the IT assist as well. Thanks a lot.

Removing the \SQLEXPRESS instance name on Dell laptops

I am installing SQL 2005 Express on several laptops (IBM Thinkpads and Dell latitudes) and I've noticed that doing the exact came configuration options with the exact same file, the IBM thinkpads just take the Machine name for SQL Server, while the Dells take Machinename\SQLEXPRESS. I am wondering how I can get it to just take the machine name on the Dells.

Thanks a lot

You might want to take a look at what's already installed on those laptops before you install SQL Express. I know that on our Dell servers there's an application called IT Assist, which uses a local SQL Server database. Dell may have already installed SQL Express on those laptops for this application, and it's using the default instance. Then, when you install SQL Express, the default instance is already used, so it installs the named installed you've identified.|||I believe I have removed all traces of anything SQL Server related that I could find as far as the add or remove programs, but I will take a look at the IT assist as well. Thanks a lot.