The column attribute trimtrailingblanks is changed,
without notice, from yes to no when the length of the
column is altered via the Query Analyzer. Is there a way
to change the trimtrailingblanks property of a column on
the fly?Seems like ALTER TABLE doesn't honor the ANSI_PADDINGS setting and always set this to "no". See
script at end.
AFAIK, there's no way to change this with less that re-create the table.
I don't know if the current behavior is considered a bug or even reported, you might want to check
with MS.
SET ANSI_PADDING ON
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
SET ANSI_PADDING OFF --Doesn't help
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
--SET ANSI_PADDING ON --Doesn't matter
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Cordelia goh" <cordelia.goh@.gvrd.bc.ca> wrote in message
news:046901c3933d$71b19f20$a101280a@.phx.gbl...
> The column attribute trimtrailingblanks is changed,
> without notice, from yes to no when the length of the
> column is altered via the Query Analyzer. Is there a way
> to change the trimtrailingblanks property of a column on
> the fly?
>sql
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
More help with Contains
I had posted an earlier question about the CONTAINS predicate that uses the
full text search.
The entire process of comparing a column with a keyword variable using the
contains predicate was not only taking too long (cursors) but also breaking
the stored procedure when noise words were encountered.
I have come up with an alternative that involves copying all the data I want
to compare into a table say Table A. This table will contain three columns
ColA - The description Column
ColB - The column containing the keywords
ColC - A Bit/Flag column.
Can I do this?
Update TableA
SET ColC = 1
WHERE CONTAINS(COLA, COLB)
Can I use two columns in the contains predicate
I know I need to refresh the full text search index after I fill Table A
with data and this user needs DBO permission.
Can a user - datareader/datawriter be assigned privileges to update the full
text.
Thanks in advance
SanjaySanjay,
What is the version of SQL Server and on what OS platform do you have it
installed? Could you post the full output of -- SELECT @.@.version -- as this
is most helpful info in troubleshooting SQL FTS issues.
Yes, you can use CONTAINS an UPDATE statement's where clause. If fact, I've
been planning on writing an article entitled "Putting Full Text Search to
work" that would demonstrate how to use FTS queries with INSERT, DELETE and
UPDATE to do real database table data manipulation, and the article is still
on the drawing boards & perhaps will be posted to my blog soon...
Q. Can I use two columns in the contains predicate?
A. No. However, you can use multiple CONTAINS clauses, i.e., CONTAINS(COLA,
'search1') AND/OR CONTAINS(COLA, 'search2') in SQL Server 2000. Furthermore,
in SQL Server 2005 (Yukon) will support multiple columns in the CONTAINS
syntax.
Q. Can a user - datareader/datawriter be assigned privileges to update the
full text?
A. No, as sy
min privileges are required to update the Full Text
Index/Catalog via the system stored procs: sp_fulltext*. See SQL Server 2000
BOL title "sp_fulltext_catalog" - "Only members of the symin fixed server
role and the db_owner (or higher) fixed database roles can execute
sp_fulltext_catalog."
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp#AFHA.2112@.TK2MSFTNGP14.phx.gbl...
> I had posted an earlier question about the CONTAINS predicate that uses
the
> full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
breaking
> the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
want
> to compare into a table say Table A. This table will contain three columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
full
> text.
> Thanks in advance
> Sanjay
>|||Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sql Server 7
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||I setup full text searches on a table to test my solution and it DID NOT
WORK!!!
So it seems like you can't do this
Select * from TableA WHERE CONTAINS(COLA, COLB)
Where COLA & COLB are both columns in TableA
COLA has a full text search index defined on it.
Thanks for the replies though.
The details of the SQL Server are...
****************************************
***********
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
****************************************
***********
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sanjay,
I never said that you could use multiple columns in ONE contains statement
with SQL Server 2000.
What I said is that you could use MULTIPLE contains clauses in your WHERE
clause with SQL Server 2000, specifically:
CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
'search_word_here').
More than one column per contains statement is ONLY supported in SQL Server
2005 that is still in limited beta release.
Hope that helps,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I setup full text searches on a table to test my solution and it DID NOT
> WORK!!!
> So it seems like you can't do this
> Select * from TableA WHERE CONTAINS(COLA, COLB)
> Where COLA & COLB are both columns in TableA
> COLA has a full text search index defined on it.
> Thanks for the replies though.
> The details of the SQL Server are...
> ****************************************
***********
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
> May 29 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> ****************************************
***********
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
the
the
>|||I'm sorry if I gave you that impression. My original question was just that
and I finally got down to setting up the environment to see if I could
compare two columns and subsequently discovered I could not. I however have
another bainwave that involves the use of a UDF that takes the keyword as a
parameter and looks up columnA using a uniqueid to compare the value of the
row.
So esentially I plan to do this
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
I am not sure if this will work but I plan to try and see for myself.
Thanks for the reply though
Sanjay
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Sanjay,
> I never said that you could use multiple columns in ONE contains statement
> with SQL Server 2000.
> What I said is that you could use MULTIPLE contains clauses in your WHERE
> clause with SQL Server 2000, specifically:
> CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
> Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
> 'search_word_here').
> More than one column per contains statement is ONLY supported in SQL
> Server
> 2005 that is still in limited beta release.
> Hope that helps,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> the
> the
>|||Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
Do I have any other alternatives?
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
> I'm sorry if I gave you that impression. My original question was just
> that and I finally got down to setting up the environment to see if I
> could compare two columns and subsequently discovered I could not. I
> however have another bainwave that involves the use of a UDF that takes
> the keyword as a parameter and looks up columnA using a uniqueid to
> compare the value of the row.
> So esentially I plan to do this
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
>
> I am not sure if this will work but I plan to try and see for myself.
> Thanks for the reply though
> Sanjay
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||You're welcome, Sanjay,
As they say in the movies..., lets try again (or something like that ;-). If
not UDF, how about a cursor operation? I'm not sure if this will meet your
needs, but you may be able to fit it to your requirements.. The table
jtkane_Tables contains a list of keywords that is the source of the @.keyword
variable for FT Searching of the jtkane_SysComments table.
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select TableNane from jtkane_Tables order by TableNane
CREATE TABLE #table_name (tblname varchar(100))
OPEN keyword_cursor
DECLARE @.keyword varchar(100)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select distinct SysNames from jtkane_SysComments
where contains(*, @.keyword)
INSERT INTO #table_name (tblname) VALUES (@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
select * from #table_name order by tblname
drop table #table_name
If the above doesn't fit your needs, checkout the use of
"sp_help_fulltext_catalogs_cursor" in the code example at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308.e
ntry
Enjoy!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
> Do I have any other alternatives?
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
WHERE
'search_word_here')
NOT
uses
using
data
Table
>|||The problem with cursors is that they take just too long are inefficient and
create all sorts of problems with locks( though I am yet to experience
problems with locking)
We currently have an inefficient procedure that uses cursors that I am
trying to replace hence the discourse :)
Thanks once again for the reply though!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%231gBsdbBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> You're welcome, Sanjay,
> As they say in the movies..., lets try again (or something like that ;-).
> If
> not UDF, how about a cursor operation? I'm not sure if this will meet your
> needs, but you may be able to fit it to your requirements.. The table
> jtkane_Tables contains a list of keywords that is the source of the
> @.keyword
> variable for FT Searching of the jtkane_SysComments table.
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select TableNane from jtkane_Tables order by TableNane
> CREATE TABLE #table_name (tblname varchar(100))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(100)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select distinct SysNames from jtkane_SysComments
> where contains(*, @.keyword)
> INSERT INTO #table_name (tblname) VALUES (@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> select * from #table_name order by tblname
> drop table #table_name
> If the above doesn't fit your needs, checkout the use of
> "sp_help_fulltext_catalogs_cursor" in the code example at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308
.entry
> Enjoy!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> WHERE
> 'search_word_here')
> NOT
> uses
> using
> data
> Table
>
full text search.
The entire process of comparing a column with a keyword variable using the
contains predicate was not only taking too long (cursors) but also breaking
the stored procedure when noise words were encountered.
I have come up with an alternative that involves copying all the data I want
to compare into a table say Table A. This table will contain three columns
ColA - The description Column
ColB - The column containing the keywords
ColC - A Bit/Flag column.
Can I do this?
Update TableA
SET ColC = 1
WHERE CONTAINS(COLA, COLB)
Can I use two columns in the contains predicate
I know I need to refresh the full text search index after I fill Table A
with data and this user needs DBO permission.
Can a user - datareader/datawriter be assigned privileges to update the full
text.
Thanks in advance
SanjaySanjay,
What is the version of SQL Server and on what OS platform do you have it
installed? Could you post the full output of -- SELECT @.@.version -- as this
is most helpful info in troubleshooting SQL FTS issues.
Yes, you can use CONTAINS an UPDATE statement's where clause. If fact, I've
been planning on writing an article entitled "Putting Full Text Search to
work" that would demonstrate how to use FTS queries with INSERT, DELETE and
UPDATE to do real database table data manipulation, and the article is still
on the drawing boards & perhaps will be posted to my blog soon...
Q. Can I use two columns in the contains predicate?
A. No. However, you can use multiple CONTAINS clauses, i.e., CONTAINS(COLA,
'search1') AND/OR CONTAINS(COLA, 'search2') in SQL Server 2000. Furthermore,
in SQL Server 2005 (Yukon) will support multiple columns in the CONTAINS
syntax.
Q. Can a user - datareader/datawriter be assigned privileges to update the
full text?
A. No, as sy
min privileges are required to update the Full TextIndex/Catalog via the system stored procs: sp_fulltext*. See SQL Server 2000
BOL title "sp_fulltext_catalog" - "Only members of the sy
min fixed serverrole and the db_owner (or higher) fixed database roles can execute
sp_fulltext_catalog."
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp#AFHA.2112@.TK2MSFTNGP14.phx.gbl...
> I had posted an earlier question about the CONTAINS predicate that uses
the
> full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
breaking
> the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
want
> to compare into a table say Table A. This table will contain three columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
full
> text.
> Thanks in advance
> Sanjay
>|||Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sql Server 7
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||I setup full text searches on a table to test my solution and it DID NOT
WORK!!!
So it seems like you can't do this
Select * from TableA WHERE CONTAINS(COLA, COLB)
Where COLA & COLB are both columns in TableA
COLA has a full text search index defined on it.
Thanks for the replies though.
The details of the SQL Server are...
****************************************
***********
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
****************************************
***********
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sanjay,
I never said that you could use multiple columns in ONE contains statement
with SQL Server 2000.
What I said is that you could use MULTIPLE contains clauses in your WHERE
clause with SQL Server 2000, specifically:
CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
'search_word_here').
More than one column per contains statement is ONLY supported in SQL Server
2005 that is still in limited beta release.
Hope that helps,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I setup full text searches on a table to test my solution and it DID NOT
> WORK!!!
> So it seems like you can't do this
> Select * from TableA WHERE CONTAINS(COLA, COLB)
> Where COLA & COLB are both columns in TableA
> COLA has a full text search index defined on it.
> Thanks for the replies though.
> The details of the SQL Server are...
> ****************************************
***********
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
> May 29 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation
> Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> ****************************************
***********
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
the
the
>|||I'm sorry if I gave you that impression. My original question was just that
and I finally got down to setting up the environment to see if I could
compare two columns and subsequently discovered I could not. I however have
another bainwave that involves the use of a UDF that takes the keyword as a
parameter and looks up columnA using a uniqueid to compare the value of the
row.
So esentially I plan to do this
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
I am not sure if this will work but I plan to try and see for myself.
Thanks for the reply though
Sanjay
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Sanjay,
> I never said that you could use multiple columns in ONE contains statement
> with SQL Server 2000.
> What I said is that you could use MULTIPLE contains clauses in your WHERE
> clause with SQL Server 2000, specifically:
> CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
> Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
> 'search_word_here').
> More than one column per contains statement is ONLY supported in SQL
> Server
> 2005 that is still in limited beta release.
> Hope that helps,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> the
> the
>|||Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
Do I have any other alternatives?
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
> I'm sorry if I gave you that impression. My original question was just
> that and I finally got down to setting up the environment to see if I
> could compare two columns and subsequently discovered I could not. I
> however have another bainwave that involves the use of a UDF that takes
> the keyword as a parameter and looks up columnA using a uniqueid to
> compare the value of the row.
> So esentially I plan to do this
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
>
> I am not sure if this will work but I plan to try and see for myself.
> Thanks for the reply though
> Sanjay
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||You're welcome, Sanjay,
As they say in the movies..., lets try again (or something like that ;-). If
not UDF, how about a cursor operation? I'm not sure if this will meet your
needs, but you may be able to fit it to your requirements.. The table
jtkane_Tables contains a list of keywords that is the source of the @.keyword
variable for FT Searching of the jtkane_SysComments table.
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select TableNane from jtkane_Tables order by TableNane
CREATE TABLE #table_name (tblname varchar(100))
OPEN keyword_cursor
DECLARE @.keyword varchar(100)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select distinct SysNames from jtkane_SysComments
where contains(*, @.keyword)
INSERT INTO #table_name (tblname) VALUES (@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
select * from #table_name order by tblname
drop table #table_name
If the above doesn't fit your needs, checkout the use of
"sp_help_fulltext_catalogs_cursor" in the code example at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308.e
ntry
Enjoy!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
> Do I have any other alternatives?
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
WHERE
'search_word_here')
NOT
uses
using
data
Table
>|||The problem with cursors is that they take just too long are inefficient and
create all sorts of problems with locks( though I am yet to experience
problems with locking)
We currently have an inefficient procedure that uses cursors that I am
trying to replace hence the discourse :)
Thanks once again for the reply though!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%231gBsdbBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> You're welcome, Sanjay,
> As they say in the movies..., lets try again (or something like that ;-).
> If
> not UDF, how about a cursor operation? I'm not sure if this will meet your
> needs, but you may be able to fit it to your requirements.. The table
> jtkane_Tables contains a list of keywords that is the source of the
> @.keyword
> variable for FT Searching of the jtkane_SysComments table.
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select TableNane from jtkane_Tables order by TableNane
> CREATE TABLE #table_name (tblname varchar(100))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(100)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select distinct SysNames from jtkane_SysComments
> where contains(*, @.keyword)
> INSERT INTO #table_name (tblname) VALUES (@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> select * from #table_name order by tblname
> drop table #table_name
> If the above doesn't fit your needs, checkout the use of
> "sp_help_fulltext_catalogs_cursor" in the code example at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308
.entry
> Enjoy!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> WHERE
> 'search_word_here')
> NOT
> uses
> using
> data
> Table
>
Friday, March 23, 2012
Months appear in alphabetical order in Excel 2003 pivot table
I have a SSAS 2005 cube attached to a pivot table in Excel 2003. When the months are dragged to the column area, they appear in alphabetical order (Apr, Aug, etc) instead of Jan, Feb, Mar.
The same cube displays the months properly in Visual Studio.
I know I can re-arrange the columns manual but that sort of ruins the point of OLAP if you have to do that every time.
Any pointers would be appreciated.
Make sure you have set the OrderBy property to Key (assuming you have a numeric key column for the month) and set the Type property to Months on the attribute and Time on the dimension. Setting the Type properties will also enable date range filters in Excel 2007 on an attribute of type Date.|||Thanks for the response.
Unfortunately, all of the properties were already set as you ask.
One thing I am unclear on is the meaning of the term "key" in the OrderBy property. How do I know if the attribute has a numeric key?
Also, this is Excel 2003, not 2007.
|||The answer is to create another attribute containing the values 1 - 12 (representing the various months) and set the Month variable OrderBy property to AttributeKey and OrderByAttribute to contain the name of the new attribute (you will have to relate the new attribute to the month attribute by expanding the month attribute and dragging the new attribute to <new attribute relationship> under the month attribute.
Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.
|||
john shahan wrote:
Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.
It sounds like you are using the string of the month name as the attribute key, as such SSAS is just sorting them alphabetically. If you had set your key column to be an actual date or a number it would have sorted using that. (the name and key don't have to be the same column). SSAS works with multiple languages so it will not automatically true to parse month names and sort them in a date order.
Months appear in alphabetical order in Excel 2003 pivot table
I have a SSAS 2005 cube attached to a pivot table in Excel 2003. When the months are dragged to the column area, they appear in alphabetical order (Apr, Aug, etc) instead of Jan, Feb, Mar.
The same cube displays the months properly in Visual Studio.
I know I can re-arrange the columns manual but that sort of ruins the point of OLAP if you have to do that every time.
Any pointers would be appreciated.
Make sure you have set the OrderBy property to Key (assuming you have a numeric key column for the month) and set the Type property to Months on the attribute and Time on the dimension. Setting the Type properties will also enable date range filters in Excel 2007 on an attribute of type Date.|||Thanks for the response.
Unfortunately, all of the properties were already set as you ask.
One thing I am unclear on is the meaning of the term "key" in the OrderBy property. How do I know if the attribute has a numeric key?
Also, this is Excel 2003, not 2007.
|||The answer is to create another attribute containing the values 1 - 12 (representing the various months) and set the Month variable OrderBy property to AttributeKey and OrderByAttribute to contain the name of the new attribute (you will have to relate the new attribute to the month attribute by expanding the month attribute and dragging the new attribute to <new attribute relationship> under the month attribute.
Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.
|||
Works like a charm but I don't see why you should have to do it when Month has already be defined as a time type of attribute.
It sounds like you are using the string of the month name as the attribute key, as such SSAS is just sorting them alphabetically. If you had set your key column to be an actual date or a number it would have sorted using that. (the name and key don't have to be the same column). SSAS works with multiple languages so it will not automatically true to parse month names and sort them in a date order.
Month data
HI,
I have a datetime column and it has 2 years of data. How
do I select the data that is only belong to the current
month (Whether it is the beginning/middle/end of the
month) '
Thanks for any help.There's a couple of different options for doing this, but this one should do
the trick:
select <insert your column list here>
from table1
where month(YourDate) = month(current_timestamp)
and year(YourDate) = year(current_timestamp)
--Brian
(Please reply to the newsgroups only.)
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
> HI,
> I have a datetime column and it has 2 years of data. How
> do I select the data that is only belong to the current
> month (Whether it is the beginning/middle/end of the
> month) '
> Thanks for any help.|||Thanks.......
I found it
Select * from mytable
where column >= cast(month(getdate()) as varchar(2))
+ '/01/' + cast(year(getdate()) as varchar(4))
>--Original Message--
>There's a couple of different options for doing this, but
this one should do
>the trick:
>select <insert your column list here>
> from table1
> where month(YourDate) = month(current_timestamp)
> and year(YourDate) = year(current_timestamp)
>--
>--Brian
>(Please reply to the newsgroups only.)
>
>"Chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
>
>.
>sql
I have a datetime column and it has 2 years of data. How
do I select the data that is only belong to the current
month (Whether it is the beginning/middle/end of the
month) '
Thanks for any help.There's a couple of different options for doing this, but this one should do
the trick:
select <insert your column list here>
from table1
where month(YourDate) = month(current_timestamp)
and year(YourDate) = year(current_timestamp)
--Brian
(Please reply to the newsgroups only.)
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
> HI,
> I have a datetime column and it has 2 years of data. How
> do I select the data that is only belong to the current
> month (Whether it is the beginning/middle/end of the
> month) '
> Thanks for any help.|||Thanks.......
I found it
Select * from mytable
where column >= cast(month(getdate()) as varchar(2))
+ '/01/' + cast(year(getdate()) as varchar(4))
>--Original Message--
>There's a couple of different options for doing this, but
this one should do
>the trick:
>select <insert your column list here>
> from table1
> where month(YourDate) = month(current_timestamp)
> and year(YourDate) = year(current_timestamp)
>--
>--Brian
>(Please reply to the newsgroups only.)
>
>"Chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
>
>.
>sql
Month data
HI,
I have a datetime column and it has 2 years of data. How
do I select the data that is only belong to the current
month (Whether it is the beginning/middle/end of the
month) '
Thanks for any help.There's a couple of different options for doing this, but this one should do
the trick:
select <insert your column list here>
from table1
where month(YourDate) = month(current_timestamp)
and year(YourDate) = year(current_timestamp)
--
--Brian
(Please reply to the newsgroups only.)
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
> HI,
> I have a datetime column and it has 2 years of data. How
> do I select the data that is only belong to the current
> month (Whether it is the beginning/middle/end of the
> month) '
> Thanks for any help.|||Thanks.......
I found it
Select * from mytable
where column >= cast(month(getdate()) as varchar(2))
+ '/01/' + cast(year(getdate()) as varchar(4))
>--Original Message--
>There's a couple of different options for doing this, but
this one should do
>the trick:
>select <insert your column list here>
> from table1
> where month(YourDate) = month(current_timestamp)
> and year(YourDate) = year(current_timestamp)
>--
>--Brian
>(Please reply to the newsgroups only.)
>
>"Chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
>> HI,
>> I have a datetime column and it has 2 years of data. How
>> do I select the data that is only belong to the current
>> month (Whether it is the beginning/middle/end of the
>> month) '
>> Thanks for any help.
>
>.
>
I have a datetime column and it has 2 years of data. How
do I select the data that is only belong to the current
month (Whether it is the beginning/middle/end of the
month) '
Thanks for any help.There's a couple of different options for doing this, but this one should do
the trick:
select <insert your column list here>
from table1
where month(YourDate) = month(current_timestamp)
and year(YourDate) = year(current_timestamp)
--
--Brian
(Please reply to the newsgroups only.)
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
> HI,
> I have a datetime column and it has 2 years of data. How
> do I select the data that is only belong to the current
> month (Whether it is the beginning/middle/end of the
> month) '
> Thanks for any help.|||Thanks.......
I found it
Select * from mytable
where column >= cast(month(getdate()) as varchar(2))
+ '/01/' + cast(year(getdate()) as varchar(4))
>--Original Message--
>There's a couple of different options for doing this, but
this one should do
>the trick:
>select <insert your column list here>
> from table1
> where month(YourDate) = month(current_timestamp)
> and year(YourDate) = year(current_timestamp)
>--
>--Brian
>(Please reply to the newsgroups only.)
>
>"Chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
>> HI,
>> I have a datetime column and it has 2 years of data. How
>> do I select the data that is only belong to the current
>> month (Whether it is the beginning/middle/end of the
>> month) '
>> Thanks for any help.
>
>.
>
month ('jan','feb',...) string to date conversion fails
I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:
Derived Column [73]]
Error: The component "Derived Column" failed because error code
0xC0049064 occurred, and the error row disposition on "output column"...
I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?
srem wrote:
I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...
I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?
Its not an ideal situation but if you want to achieve this in a single derived column component what you will need to do is build some nested conditional operators. Its a bit of a fraught process to begin with but once you get the hang of it it isn't too bad.
I hope Microsoft give us the ability to extend the expression language in the next version by allowing us to build our own custom expression functions.
-Jamie
|||I suppose you could build a quick lookup table using a simple stored procedure. Then all you'd have to do is join on that "Jan 02 2005" column to get you a real date field in return.
Might even work better than doing all of the CASTs and conditional logic tests.|||
If you know exactly what the month strngs are, and they are all 3 chars long, you could do something like the following to get month number:
(FINDSTRING(month, "JANFEBMARAPR....DEC",1) + 2 ) / 3
If there are multiple options, or the lengths are irregular you could still do something similar, but it might not be worth it anymore over the nested conditionals.
Saturday, February 25, 2012
Monitor DB Usage at Column Level
I'm looking for a tool that will compile historical usage information for a
specific SQL Server 2000 database down to the column-level. This is a
read-only database and I need to know how many times each column of each
table has been included in a query over a period of time. This is a
production database, so it has to be a tool that won't have a major impact on
performance.
Any suggestions?
Thanks,
Hari
Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> I'm looking for a tool that will compile historical usage information
> for a specific SQL Server 2000 database down to the column-level. This
> is a read-only database and I need to know how many times each column of
> each table has been included in a query over a period of time. This is
> a production database, so it has to be a tool that won't have a major
> impact on performance.
You would have to run a server-side trace that captures the SP:StmtCompleted
and SQL:StmtCompleted events. Exactly what columns you should include
in the trace depends on your needs, but a minimum you would have to
include TextData. The trace should save data to a file.
The performance impact on such a query is not negligible, particularly if
users run many small queries. (If they main run long-running queries, the
number of events to track is much smaller.)
The easiest way to set up such a trace is to use Profiler, and then save
the trace as a script.
That was the easy part. Once you have the trace data, you need to analyse
it, one way or another. Unless you application generates command in a way
that makes things easy for you, for instance it always uses
tablename.columnname, this is a difficult task, and I don't know
of any tool that does this, thereby not said that it does not exist.
Presumably, a program would have to read the file, and parse each query to
determine which columns that are referred to in the query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"Erland Sommarskog" wrote:
> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Thanks. I really appreciate the info. I had a feeling this wasn't going to
be easy.
Hari
"Erland Sommarskog" wrote:
> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
specific SQL Server 2000 database down to the column-level. This is a
read-only database and I need to know how many times each column of each
table has been included in a query over a period of time. This is a
production database, so it has to be a tool that won't have a major impact on
performance.
Any suggestions?
Thanks,
Hari
Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> I'm looking for a tool that will compile historical usage information
> for a specific SQL Server 2000 database down to the column-level. This
> is a read-only database and I need to know how many times each column of
> each table has been included in a query over a period of time. This is
> a production database, so it has to be a tool that won't have a major
> impact on performance.
You would have to run a server-side trace that captures the SP:StmtCompleted
and SQL:StmtCompleted events. Exactly what columns you should include
in the trace depends on your needs, but a minimum you would have to
include TextData. The trace should save data to a file.
The performance impact on such a query is not negligible, particularly if
users run many small queries. (If they main run long-running queries, the
number of events to track is much smaller.)
The easiest way to set up such a trace is to use Profiler, and then save
the trace as a script.
That was the easy part. Once you have the trace data, you need to analyse
it, one way or another. Unless you application generates command in a way
that makes things easy for you, for instance it always uses
tablename.columnname, this is a difficult task, and I don't know
of any tool that does this, thereby not said that it does not exist.
Presumably, a program would have to read the file, and parse each query to
determine which columns that are referred to in the query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"Erland Sommarskog" wrote:
> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Thanks. I really appreciate the info. I had a feeling this wasn't going to
be easy.
Hari
"Erland Sommarskog" wrote:
> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
Monitor CPU Usage by Query?
The Profiler can do this. Create a new trace using the SQLProfilerStandard
trace template and add the CPU column. This will show both the query and the
CPU time, plus some other stuff. If this is a super busy production server
this can degrade performance so you might want to play with it on a test db
instance first. I typically run it from my desktop or from a remote desktop
and save the results to a local file cause if you write the result to the db
you are monitoring it too can impact your findings.
Netmon
"quixster@.gmail.com" wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.
>
Thanks for the tip! Do you know of any way to use this or some other
utility to create an alert for administrators to receive when a query
has risen above a certain threshold? I can see how I can filter the
trace for CPU usage of a certain level, but I don't see how I could use
that to create an alert. Any thoughts?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> The Profiler can do this. Create a new trace using the SQLProfilerStandard
> trace template and add the CPU column. This will show both the query and the
> CPU time, plus some other stuff. If this is a super busy production server
> this can degrade performance so you might want to play with it on a test db
> instance first. I typically run it from my desktop or from a remote desktop
> and save the results to a local file cause if you write the result to the db
> you are monitoring it too can impact your findings.
> Netmon
>
> "quixs...@.gmail.com" wrote:
|||No the trace is not going to do that. I'v heard that NetIQ has a product
that monitors for query execution but I'm not sure of the details on how it
works.
Like the other person (tootsuite) posted you could use perfmon however it
does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
alert. So anytime the CPU is above your threshold it will alert even if it
is a short lived spike you get alerted.
I'm not sure the context within which you want to filter the trace to
generate an alert. If you have any enterprise server monitoring tools like
ProIT, MOM or Nimbus available you could just monitor the CPU via those and
have it email your admin.
Are you hoping to find the rogue/cpu intense queries in action? Or are you
desiring to know when the CPU gets hammered so an admin can log in and kill
the offending query or find out the user running it?
"quixster@.gmail.com" wrote:
> Thanks for the tip! Do you know of any way to use this or some other
> utility to create an alert for administrators to receive when a query
> has risen above a certain threshold? I can see how I can filter the
> trace for CPU usage of a certain level, but I don't see how I could use
> that to create an alert. Any thoughts?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>
|||Perfmon is just a general monitoring tool... you cannot see individual
queries.
Usually it works like this:
1. end users complain
2. look at overall picture using tool like perfmon or Quest Performance
Analysis, or even profiler (I find profiler to be flaky/unpredictable
at times, at least in 2000 it was terrible)
3. isolate queries using Quest based on various factors
4. most of our performance problems in the past have stemmed from
inadequate memory, which then in turns affects cpu, disk i/o, which
leads to blocking locks, etc - fortunately this has been resolved with
the purchase of higher performance servers + memory
so it's not enough to just isolate cpu performance, as many factors are
usually involved
so your cpu might be hammered, but the real underlying problem could be
lack of memory, etc but you wouldn't know that unless you can look at
all the various possible factors
Netmon wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
|||The first; trying to find rogue/cpu intense queries in action so we can
identify not only the query causing the problem, but perhaps what else
is occuring simultaneously that could be contributing to the high CPU
situation. We have MOM, and it can alert us to CPU incidents, but I
was hoping to find something that could single out the individual
queries that are running to create the incident.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
>
>
|||Yes, you can do this easily using the query I provided in an earlier
post.
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
DBCC INPUTBUFFER (spid) -- shows query
it can't get much clearer than this - it will list processes by CPU
usage, highest to lowest, at the given point in time you run the stmt
Or you can invest in some type of monitoring tool, like Quest
(performance analysis) or some other tool that you like if you need to
examine various performance indicators - this is what I would recommend
for a long term solution
hth
quixster@.gmail.com wrote:[vbcol=seagreen]
> The first; trying to find rogue/cpu intense queries in action so we can
> identify not only the query causing the problem, but perhaps what else
> is occuring simultaneously that could be contributing to the high CPU
> situation. We have MOM, and it can alert us to CPU incidents, but I
> was hoping to find something that could single out the individual
> queries that are running to create the incident.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
|||Thanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server
Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, you can do this easily using the query I provided in an earlier
> post.
> SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> DBCC INPUTBUFFER (spid) -- shows query
> it can't get much clearer than this - it will list processes by CPU
> usage, highest to lowest, at the given point in time you run the stmt
> Or you can invest in some type of monitoring tool, like Quest
> (performance analysis) or some other tool that you like if you need to
> examine various performance indicators - this is what I would recommend
> for a long term solution
> hth
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks for all the help! I'm taking the tips mentioned on this post
> and also talking to a Quest rep about "Spotlight on SQL Server
> Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
> This has been immensely helpful!
> On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:
>
trace template and add the CPU column. This will show both the query and the
CPU time, plus some other stuff. If this is a super busy production server
this can degrade performance so you might want to play with it on a test db
instance first. I typically run it from my desktop or from a remote desktop
and save the results to a local file cause if you write the result to the db
you are monitoring it too can impact your findings.
Netmon
"quixster@.gmail.com" wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.
>
Thanks for the tip! Do you know of any way to use this or some other
utility to create an alert for administrators to receive when a query
has risen above a certain threshold? I can see how I can filter the
trace for CPU usage of a certain level, but I don't see how I could use
that to create an alert. Any thoughts?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> The Profiler can do this. Create a new trace using the SQLProfilerStandard
> trace template and add the CPU column. This will show both the query and the
> CPU time, plus some other stuff. If this is a super busy production server
> this can degrade performance so you might want to play with it on a test db
> instance first. I typically run it from my desktop or from a remote desktop
> and save the results to a local file cause if you write the result to the db
> you are monitoring it too can impact your findings.
> Netmon
>
> "quixs...@.gmail.com" wrote:
|||No the trace is not going to do that. I'v heard that NetIQ has a product
that monitors for query execution but I'm not sure of the details on how it
works.
Like the other person (tootsuite) posted you could use perfmon however it
does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
alert. So anytime the CPU is above your threshold it will alert even if it
is a short lived spike you get alerted.
I'm not sure the context within which you want to filter the trace to
generate an alert. If you have any enterprise server monitoring tools like
ProIT, MOM or Nimbus available you could just monitor the CPU via those and
have it email your admin.
Are you hoping to find the rogue/cpu intense queries in action? Or are you
desiring to know when the CPU gets hammered so an admin can log in and kill
the offending query or find out the user running it?
"quixster@.gmail.com" wrote:
> Thanks for the tip! Do you know of any way to use this or some other
> utility to create an alert for administrators to receive when a query
> has risen above a certain threshold? I can see how I can filter the
> trace for CPU usage of a certain level, but I don't see how I could use
> that to create an alert. Any thoughts?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
>
|||Perfmon is just a general monitoring tool... you cannot see individual
queries.
Usually it works like this:
1. end users complain
2. look at overall picture using tool like perfmon or Quest Performance
Analysis, or even profiler (I find profiler to be flaky/unpredictable
at times, at least in 2000 it was terrible)
3. isolate queries using Quest based on various factors
4. most of our performance problems in the past have stemmed from
inadequate memory, which then in turns affects cpu, disk i/o, which
leads to blocking locks, etc - fortunately this has been resolved with
the purchase of higher performance servers + memory
so it's not enough to just isolate cpu performance, as many factors are
usually involved
so your cpu might be hammered, but the real underlying problem could be
lack of memory, etc but you wouldn't know that unless you can look at
all the various possible factors
Netmon wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
|||The first; trying to find rogue/cpu intense queries in action so we can
identify not only the query causing the problem, but perhaps what else
is occuring simultaneously that could be contributing to the high CPU
situation. We have MOM, and it can alert us to CPU incidents, but I
was hoping to find something that could single out the individual
queries that are running to create the incident.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
>
>
|||Yes, you can do this easily using the query I provided in an earlier
post.
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
DBCC INPUTBUFFER (spid) -- shows query
it can't get much clearer than this - it will list processes by CPU
usage, highest to lowest, at the given point in time you run the stmt
Or you can invest in some type of monitoring tool, like Quest
(performance analysis) or some other tool that you like if you need to
examine various performance indicators - this is what I would recommend
for a long term solution
hth
quixster@.gmail.com wrote:[vbcol=seagreen]
> The first; trying to find rogue/cpu intense queries in action so we can
> identify not only the query causing the problem, but perhaps what else
> is occuring simultaneously that could be contributing to the high CPU
> situation. We have MOM, and it can alert us to CPU incidents, but I
> was hoping to find something that could single out the individual
> queries that are running to create the incident.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
|||Thanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server
Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:[vbcol=seagreen]
> Yes, you can do this easily using the query I provided in an earlier
> post.
> SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> DBCC INPUTBUFFER (spid) -- shows query
> it can't get much clearer than this - it will list processes by CPU
> usage, highest to lowest, at the given point in time you run the stmt
> Or you can invest in some type of monitoring tool, like Quest
> (performance analysis) or some other tool that you like if you need to
> examine various performance indicators - this is what I would recommend
> for a long term solution
> hth
>
> quixs...@.gmail.com wrote:
>
>
>
>
>
|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks for all the help! I'm taking the tips mentioned on this post
> and also talking to a Quest rep about "Spotlight on SQL Server
> Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
> This has been immensely helpful!
> On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:
>
Monday, February 20, 2012
Money type confusion
I need a little advice for the Money datatype. When entering a value like $18.20 into a column of datatype Money, it stores and returns a value of 18.2 . It is set to the default paramaters. How can I change this so that it is correctly displayed as $18.20. My results are printed onscreen through VB, is that where I need to reformat?You sure it's money?
DECLARE @.x money
SELECT @.x = 18.20
SELECT @.x
EDIT: It's a presentation layer issue...
Then There Also
SELECT CONVERT(varchar(15),@.x,1)|||Positive it's Money|||Where's the sql being executed from? a stored procedure or called from a front end?
Did you try the code I posted in QA?|||It's a .asp page with VB. It's a dynamic SQL statment. And after running your code I got 18.2000. So it looks like it's good to 4 decimal places. This may be dumb to ask now, but should I kill that column and recreate it?|||You should be able to use the Convert function to format your output as a string, but formatting is best handled by your application interface.
blindman|||Do you know how to script the table so we can see the DDL?
Did you use the convert from the page?|||I'm a graphic designer doing the basics on MSSQL, I didn't even know you could script a table let alone tell you what a DDL is. But I'm a smart guy, I'll catch on quick.
And by converting do you mean something like CSTR to cast the returned value into a string or is it done within the SQL statment?
Originally posted by Brett Kaiser
Do you know how to script the table so we can see the DDL?
Did you use the convert from the page?|||SELECT CONVERT(varchar(15),@.x,1)
Just replace the @.x with the column name
Do you have SQL Server client tools installed on your desktop?
Do you know what Enterprise Manager is?|||Actually you should make the varchar(25) to handle all possible money values...
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.|||[i]Actually you should make the varchar(25) to handle all possible money values...
quote:
------------------------
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
------------------------
Brett ...
That sounds like an awful lot of Money. Hope the person having so much can lend some to me :D|||HAHA, after 5 minutes of staring at the wrong webpage I was working on, I got myself straightened out and found the right one. The Convert expression works like magic.
And yeah, i'm working in Enterprise Manager. I guess I'm a little above basic.|||I thought 15 was enough...but you still have to take in the right side of the decimal, the decimal itself, commas, and a sign...25
922 Trillion Dollars...|||Thanks for you help, I should be able to manage from here|||If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.|||Actually, it's more a matter of a development methodolgy...always code for what can happen...always...
That way there will never be any holes...|||Originally posted by stmaher
If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.
There go my hopes down the drain :D.
BTW Brett is right. The code should take care of all possible conditions.
Brett ... though I think varchar(15) will take care of Bill Gates wealth for sure|||Free forum !? What is your mailing address ? Or you can call the toll free number and submit your credit card information.|||What? Have NONE of you guys been receiving your checks?
Must be a problem in the payroll database...
blindman|||Just send me your SSN's and mailing addresses and I will get you on the payroll. If you receive any unexpected mail, like credit cards ..., ignore it and just forward them to me. :-)|||> That sounds like an awful lot of Money
hang on a sec...you must be assuming USD. I wonder whether the amount would still look so large if it was to refer to Japanese Yen. Not to mention some inflated currency from a developing country.|||You'd be surprized.
We have a thing in the IT dept. we call the "2 comma rule".
In other words, if the number (currency) doesn't have at least 2 commas, it isn't worth worrying about.
Swear to God.
DECLARE @.x money
SELECT @.x = 18.20
SELECT @.x
EDIT: It's a presentation layer issue...
Then There Also
SELECT CONVERT(varchar(15),@.x,1)|||Positive it's Money|||Where's the sql being executed from? a stored procedure or called from a front end?
Did you try the code I posted in QA?|||It's a .asp page with VB. It's a dynamic SQL statment. And after running your code I got 18.2000. So it looks like it's good to 4 decimal places. This may be dumb to ask now, but should I kill that column and recreate it?|||You should be able to use the Convert function to format your output as a string, but formatting is best handled by your application interface.
blindman|||Do you know how to script the table so we can see the DDL?
Did you use the convert from the page?|||I'm a graphic designer doing the basics on MSSQL, I didn't even know you could script a table let alone tell you what a DDL is. But I'm a smart guy, I'll catch on quick.
And by converting do you mean something like CSTR to cast the returned value into a string or is it done within the SQL statment?
Originally posted by Brett Kaiser
Do you know how to script the table so we can see the DDL?
Did you use the convert from the page?|||SELECT CONVERT(varchar(15),@.x,1)
Just replace the @.x with the column name
Do you have SQL Server client tools installed on your desktop?
Do you know what Enterprise Manager is?|||Actually you should make the varchar(25) to handle all possible money values...
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.|||[i]Actually you should make the varchar(25) to handle all possible money values...
quote:
------------------------
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
------------------------
Brett ...
That sounds like an awful lot of Money. Hope the person having so much can lend some to me :D|||HAHA, after 5 minutes of staring at the wrong webpage I was working on, I got myself straightened out and found the right one. The Convert expression works like magic.
And yeah, i'm working in Enterprise Manager. I guess I'm a little above basic.|||I thought 15 was enough...but you still have to take in the right side of the decimal, the decimal itself, commas, and a sign...25
922 Trillion Dollars...|||Thanks for you help, I should be able to manage from here|||If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.|||Actually, it's more a matter of a development methodolgy...always code for what can happen...always...
That way there will never be any holes...|||Originally posted by stmaher
If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.
There go my hopes down the drain :D.
BTW Brett is right. The code should take care of all possible conditions.
Brett ... though I think varchar(15) will take care of Bill Gates wealth for sure|||Free forum !? What is your mailing address ? Or you can call the toll free number and submit your credit card information.|||What? Have NONE of you guys been receiving your checks?
Must be a problem in the payroll database...
blindman|||Just send me your SSN's and mailing addresses and I will get you on the payroll. If you receive any unexpected mail, like credit cards ..., ignore it and just forward them to me. :-)|||> That sounds like an awful lot of Money
hang on a sec...you must be assuming USD. I wonder whether the amount would still look so large if it was to refer to Japanese Yen. Not to mention some inflated currency from a developing country.|||You'd be surprized.
We have a thing in the IT dept. we call the "2 comma rule".
In other words, if the number (currency) doesn't have at least 2 commas, it isn't worth worrying about.
Swear to God.
Money to Varchar
From query analyzer how can I change the field datatype from money to varchar?
Alter table tablenaame alter column columnname varchar(30)--Is not working.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?
-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.
Alter table tablenaame alter column columnname varchar(30)--Is not working.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?
-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.
Money data type
Hi,
I have a Price column which has 'money' as the data type.
I have a Price column which has 'money' as the data type.
Then I populated some data into the table. I enter '1.00' in the Price column, then I used the following code to get the data:
Label2.Text = "$" + dataSet1.Tables["products"].Rows[0]["price"].toString();
However, the price is displayed as "$1.0000". But I believe it should display "$1.00". So how can I get rid off the two zeros at the end.
regardsUse string.format("{0:c}", yourvalue)
This will format it in the currency used by your web server.
If you want more control, use "$" & string.format("{0:#,##0.00}", yourvalue)|||Hi PDraigh
I think u may have goven me VB code, actually I was using C#..
string.format("{0:#,##0.00}", yourvalue)
In the above code, what does 'string' represent|||I assume it would still work. 'string' is just the System.String. It's not a variable or object I declared. "{0:#,##0.00}" is the string formatting instruction and 'yourvalue' is just whatever value you want to format using the instruction. Try it, I think it would work in C#, but I don't use C#, so not sure|||yep
String and Format should be capital, such as String.Format("{0:#,##0.00}", price);
However, I still get 2.0000 istead of 2.00|||try,
Label2.Text = String.Format("{0:$##,##0.00}",dataSet1.Tables["products"].Rows[0]["price"]);
or
Label2.Text = String.Format("{0:c}",dataSet1.Tables["products"].Rows[0]["price"]);
Subscribe to:
Posts (Atom)