INFORMATION_SCHEMA.SCHEMATA does not return all rows on SQL 2005

Hi,
In SQL 2000, the following query used to return all the database names:
SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
However, in SQL 2005, it just returns "master" as the database (that
too a number of times).
Can someone please confirm if this is a bug in SQL 2005?
Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
standard statements.
Thank you in advance for your help.
Pradeep> In SQL 2000, the following query used to return all the database names:
Which is incorrect behavior. This is fixed in SQL Server 2005 (this should
never have been a list of databases).
To get a list of database names,
SELECT name FROM sys.databases|||To add on to Aaron's response, the reason for the SQL 2005 change was to
make the INFORMATION_SCHEMA.SCHEMATA view consistent with the ANSI standard.
The SQL 2000 behavior (database list) was proprietary. This is listed in
the SQL 2005 Books Online under the breaking changes topic
<ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/47edefbd-a09b-4087-937a
-453cd5c6e061.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pradeep" <pradeep@.tapadiya.net> wrote in message
news:1136602519.887257.199350@.g43g2000cwa.googlegroups.com...
> Hi,
> In SQL 2000, the following query used to return all the database names:
> SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
> However, in SQL 2005, it just returns "master" as the database (that
> too a number of times).
> Can someone please confirm if this is a bug in SQL 2005?
> Although I could use sp_catalogs_rowset;2, I prefer using ANSI SQL
> standard statements.
> Thank you in advance for your help.
> Pradeep
>

INFORMATION_SCHEMA.PARAMETERS

Is there any way to find out the stored procedure output paramters like I
can findout the input params through following query but I want to findout
the output params list...
SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
thanks in advance.
select
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
PARAMETER_MODE,
NUMERIC_PRECISION,
NUMERIC_SCALE
from INFORMATION_SCHEMA.PARAMETERS
where specific_name = @.chvProcName
order by ordinal_position
look for PARAMETER_MODE it will have a value of OUT
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> Is there any way to find out the stored procedure output paramters like I
> can findout the input params through following query but I want to findout
> the output params list...
> SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
> thanks in advance.
>
>
>
|||No, I mean ... let's assume this is the stored procedure right ... I need
the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
Time...
CREATE PROCEDURE [dbo].[SPSelectTotalServices]
@.ModalityTypeID INT = NULL,
@.LHIN_Code INT = NULL,
@.Lan CHAR(2) = 'EN'
AS
IF (@.Lan = 'EN')
BEGIN
SELECT
MF_ModalityTypeID AS SiteID,
(
SELECT
MT_Alias
FROM tblModalityType
WHERE MT_ModalityTypeID = MF_ModalityTypeID
) AS Site,
CEILING(sum(MF_Median)) AS 'Median Wait Time',
CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
FROM tblModalityFacility
WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
AND
(@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
AND MF_ModalityTypeID IS NOT NULL
GROUP BY MF_ModalityTypeID
ORDER BY 2
END
GO
Is there any way ?
Thanks in advance.
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...[vbcol=seagreen]
> select
> PARAMETER_NAME,
> DATA_TYPE,
> CHARACTER_MAXIMUM_LENGTH,
> PARAMETER_MODE,
> NUMERIC_PRECISION,
> NUMERIC_SCALE
> from INFORMATION_SCHEMA.PARAMETERS
> where specific_name = @.chvProcName
> order by ordinal_position
> look for PARAMETER_MODE it will have a value of OUT
> http://sqlservercode.blogspot.com/
>
> "Rogers" wrote:
|||Thats not an output parameter but a resultset
You could use sp_helptext and parse thru that
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> No, I mean ... let's assume this is the stored procedure right ... I need
> the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
> Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
> AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
>
|||what are you trying to do with the output?
In any case, look up SET FRMONLY in books on line - that should get you there.
for instance:
SET FMTONLY ON
GO
SELECT *
FROM SPSelectTotalServices
GO
(remember to SET FMTONLY OFF when you're done!)
"Rogers" <Rogers@.mailstuff.com> wrote in message news:O7dV$StvFHA.2932@.TK2MSFTNGP10.phx.gbl...
> No, I mean ... let's assume this is the stored procedure right ... I need the output like SiteID,Site,Median Wait Time, 90% Wait
> Time,Average Wait Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID) AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
sql

INFORMATION_SCHEMA.COLUMNS query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
Simon
AFAIK, you can't use the information_schema views in SQL Server 2000 from a linked server as they
don't exists in each database, they only exist in the master database. Use syscolumns etc instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegro ups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

INFORMATION_SCHEMA.COLUMNS query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
SimonAFAIK, you can't use the information_schema views in SQL Server 2000 from a linked server as they
don't exists in each database, they only exist in the master database. Use syscolumns etc instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

INFORMATION_SCHEMA.COLUMNS query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
SimonAFAIK, you can't use the information_schema views in SQL Server 2000 from a
linked server as they
don't exists in each database, they only exist in the master database. Use s
yscolumns etc instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

INFORMATION_SCHEMA.COLUMNS error

Hi All,

For some reason, whenever I execute the following query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

I get a few rows returned then I get the following error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Is my INFORMATION_SCHEMA.COLUMNS view corrupt somehow?

Querries of other INFORMATION_SCHEMA views are fine, just this one is returning an error. Anyone have any ideas about how to fix this?

John

Can you post the table structure..|||

Manivannan.D.Sekaran wrote:

Can you post the table structure..

The table structure of what? The INFORMATION_SCHEMA.COLUMNS view? This is the default INFORMATION_SCHEMA.COLUMNS view that is created by default in SQL Server. Its what should be there by default. Its not a view that I've created.

So what table structure are you referring to?

John

|||

Ohh yes.. You dont know which table cause the problem.. Simply forget..

Ok try to execute the following query..

At one time you will get an error.. So that is the table causing the issue... Then post the DDL of that table...

sp_msforeachtable'Print ''? is Executing..'';Select Count(Column_Name) TotalColumn, ''?'' TableName fromINFORMATION_SCHEMA.COLUMNS Where object_id(table_name) = object_id(''?'');Print ''? is Completed..'';'

|||

Hi Manivannan,

Ok I executed your query. I didn't get any errors.

I think the issue is with the view in general on my server. I get the error when I execute the query against INFORMATION_SCHEMA.COLUMNS regardless of the database I execute against.

When I execute your script against ALL of my databases (on my server) I get no errors.

|||

Here's a little background on what I think may have caused my server to be in such a state.

I have been tasked with changing all columns, in all tables, in our database that are of varchar type to be nvarchar. I immediately issued a query such as

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='varchar'

That very simply returned ALL columns in my database that were of varchar type, I think there were hundreds of rows returned. So, I think this is what screwed things up for me, I executed an update statement against that view as a test to see if I could do such a thing. This is the update statement I executed:

UPDATE INFORMATION_SCHEMA.COLUMNS SET DATA_TYPE='nvarchar' WHERE DATA_TYPE='varchar'

I figured something was wrong when upon executing that statement it said that 1 row was updated. I got no error message, but now all querries of that view yields the error I documented.

|||

Its really worst idea changing the system tables directly. You should use the proper alter statement.

The update query you performed, is modified the data in System Table (spt_datatype_info) rather than your original table (on syscolumns).

If you only executed the above query then use the following query to revert back spt_datatype_info into original state..

UPDATE spt_datatype_info

SET local_type_name=type_name

WHERE local_type_name='varchar'

|||

WooHoo, it worked!

I changed your update statement slightly to:

UPDATE spt_datatype_info
SET local_type_name=type_name
WHERE local_type_name!=type_name

It restored all that were messed up (there were two rows actually, the row for "varchar" and the row for "text").

Thank you very much Manivannan!

John

sql

Information came from sql error log.

Hi,
Can someone explain the following info came from sql server error log? This
is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS is
Windows 2003 (64 bit, attached to SAN) named instance database server.
11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
specified network name is no longer available. [SQLSTATE 08S01]
Is above error means the net work not available for database instance while
application batch job running from other server connect to database server?
11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle is
0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\market_place\data\mp_orders_dat.ndf] in database [market_place](13). The
OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
0x000000307e4c000
Is above info means the SAN drive is very busy and took long time to
complete the database processes such as query, insert, update and delete, etc?
Thanks!
Chen
Check this out;
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
Also review this KB article:
http://support.microsoft.com/default.aspx/kb/897284
Linchi
"Chen" wrote:

> Hi,
> Can someone explain the following info came from sql server error log? This
> is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS is
> Windows 2003 (64 bit, attached to SAN) named instance database server.
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
> specified network name is no longer available. [SQLSTATE 08S01]
> Is above error means the net work not available for database instance while
> application batch job running from other server connect to database server?
> 11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle is
> 0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
> 11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\market_place\data\mp_orders_dat.ndf] in database [market_place](13). The
> OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
> 0x000000307e4c000
> Is above info means the SAN drive is very busy and took long time to
> complete the database processes such as query, insert, update and delete, etc?
> Thanks!
> Chen
>

Information came from sql error log.

Hi,
Can someone explain the following info came from sql server error log? This
is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS i
s
Windows 2003 (64 bit, attached to SAN) named instance database server.
11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
failure [SQLSTATE 08S01]
11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
specified network name is no longer available. [SQLSTATE 08S01]
Is above error means the net work not available for database instance while
application batch job running from other server connect to database server?
11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handle
is
0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
[M:\market_place\data\mp_orders_dat.ndf] in database [market_place](
13). The
OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is:
0x000000307e4c000
Is above info means the SAN drive is very busy and took long time to
complete the database processes such as query, insert, update and delete, et
c?
Thanks!
ChenCheck this out;
http://blogs.msdn.com/sqlserverstor.../21/642314.aspx
Also review this KB article:
http://support.microsoft.com/default.aspx/kb/897284
Linchi
"Chen" wrote:

> Hi,
> Can someone explain the following info came from sql server error log? Thi
s
> is the two node (active/passive) cluster SQL Server 2005 (with SP1) and OS
is
> Windows 2003 (64 bit, attached to SAN) named instance database server.
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 10004, Communication li
nk
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, Communication link
> failure [SQLSTATE 08S01]
> 11/28/2006 12:10:21 PM [298] SQL Server Error: 64, TCP Provider: The
> specified network name is no longer available. [SQLSTATE 08S01]
> Is above error means the net work not available for database instance whil
e
> application batch job running from other server connect to database server
?
> 11/29/2006 2:05:06 AM SQL Server has encountered 4677 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\bsi\data\bsi_Data.MDF] in database [bsi](5). The OS file handl
e is
> 0x00000000000009F4. The offset of the latest long I/O is: 0x000000badbc000
> 11/29/2006 2:47:39 AM SQL Server has encountered 993 occurrence(s) of I/O
> requests taking longer than 15 seconds to complete on file
> [M:\market_place\data\mp_orders_dat.ndf] in database [market_place
](13). The
> OS file handle is 0x0000000000000A9C. The offset of the latest long I/O is
:
> 0x000000307e4c000
> Is above info means the SAN drive is very busy and took long time to
> complete the database processes such as query, insert, update and delete,
etc?
> Thanks!
> Chen
>sql

Inflectional and/or not returning the correct results

When doing the following search (SearchAllProducts, keyword, 'FORMSOF (INFLECTIONAL, pens) and FORMSOF (INFLECTIONAL, pads)'), we get 170 records returned. But if we switch the and and replace it with an OR we get 1294 records returned. Whats the part w
e don't understand is that the OR is not pulling back any of the 170 records where both words are in the keyword field. Even though we do a pens search and we get 748 records and for pads we get 716 records. We would expect to get back 1464 records. I
s this a bug or we not doing something right? We are using Enterprise Edition running SP3a on Windows 2000 running SP4.
Any ideas?
Kris
Kris,
Could you post some of the actual data in the FT-enable column(s) from your
FT-enabled table(s) along with the actual FTS query and table structure?
Also, do you have multiple FT-enabled tables in one FT Catalogs or do you
use one FT Catalog for each FT-enabled table?
Thanks,
John
"Kris" <kmccarty@.distributorcentral.com> wrote in message
news:81F1AFB5-679F-4963-B8B9-77A0FAFB6A7E@.microsoft.com...
> When doing the following search (SearchAllProducts, keyword, 'FORMSOF
(INFLECTIONAL, pens) and FORMSOF (INFLECTIONAL, pads)'), we get 170 records
returned. But if we switch the and and replace it with an OR we get 1294
records returned. Whats the part we don't understand is that the OR is not
pulling back any of the 170 records where both words are in the keyword
field. Even though we do a pens search and we get 748 records and for pads
we get 716 records. We would expect to get back 1464 records. Is this a
bug or we not doing something right? We are using Enterprise Edition
running SP3a on Windows 2000 running SP4.
> Any ideas?
> Kris

Inflectional ?

Hi,
Ok, say I want to pull back all '"picture ~ frame"' and '"pictures ~
frames"' so I do the following; FormsOf(Inflectional, "picture ~ frame")
this results in syntax error. I thought inflectional would automatically do
it if I put " around the near words. The first ones I listed do not pull
back the plurals, if I use the wildcard I get what I need but wonder if this
is the way it should work say I also want framing so I do fram* but this
would also give me frampton which I do not want. Do I need to use the
thesuraus or is there some way of forming the inflectional that I am not
getting?
Thanks,
jc
Sorry forgot, sql 2k5 sp2a
jc
|||A freetext query will do an implicit near and the stemming you are looking
for.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Cantley" <johnca@.magenic.com> wrote in message
news:OoTJLiBcHHA.208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Ok, say I want to pull back all '"picture ~ frame"' and '"pictures ~
> frames"' so I do the following; FormsOf(Inflectional, "picture ~ frame")
> this results in syntax error. I thought inflectional would automatically
> do it if I put " around the near words. The first ones I listed do not
> pull back the plurals, if I use the wildcard I get what I need but wonder
> if this is the way it should work say I also want framing so I do fram*
> but this would also give me frampton which I do not want. Do I need to use
> the thesuraus or is there some way of forming the inflectional that I am
> not getting?
> Thanks,
> jc
>
sql

Infinitive recursion for my AS2000 calculated member

Hi,

Can someone please help me on this. I get following error when I browse my virtual AS2000 cube:

<Infinite recursion detected during execution of calculated member Sum({Descendants....>

I have implemented a 'dummy' utility dimension with one calculated member: (I have used a parentchild dimension because that's only way I know how to get the formula a from source view):

--

view [dbo].[vdimUtilityCalculation] as

select
'CalculationID' = 1,
'ParentID' = 1,
'CalculationName' = 'Currency',
'Formula' = 'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))',
'MemberOption' = 'SOLVE_ORDER=''-1'''
--

Any idea?

Thanks, Christer

You need to change coordinate in the utility dimension in order to prevent infinite recursion. I.e. something like that:

'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", (UtilityDim.DefaultMember,[Amount Fixr]), (UtilityDim.DefaultMember,[Amount Flor])) * ValidMeasure([Rate]))',

|||

Thanks for your reply.

I added a second 'dummy' member to my utility dimension and pointed to in the formula like this:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", ([CalculationUtility].&[2],[Amount Fixr]), ([CalculationUtility].&[2],[Amount Flor])) * ValidMeasure([Rate]))

It works for some accounts (account dimension with calculated members), not accounts with formula like account1/account2, they are not calculated correctly... and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

Not sure how to get this working, is this easier to implement in AS 2005?

Thanks, Christer

|||

and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

It should work. I suggest you start troubleshooting by creating a calculated member in this utility dimension with SOLVE_ORDER=-1 and then sending MDX query with Aggregate over Period dimension. It isn't easy to help through newsgroup, but if you will take it slow, step by step, you should be able to see that it does work.

|||

Thanks! After some struggle I got it to work!

sql

inedex - should I cluster?

Hi, I have the following delema:
I have a table that has the following fields:
companyid
warehouseid
year
month
productid
val1
...
valn
there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000 prodocts.
the primary key is : companyid, warehouseid, year, month, productid
a) At the end of each month new values are recorded on the table.
b) During the month updates are issued to the current month.
c) queries are usualy include:
* company, warehouse, year, month
* company, year, month, product
Question: what is a good clusterd index for this table, what other indexes
should I implement.
Thx,
Juan"jccondor" <jccondor@.discussions.microsoft.com> wrote in message
news:1373BADF-2A63-4D61-853C-C6A9B894DF73@.microsoft.com...
> Hi, I have the following delema:
> I have a table that has the following fields:
> companyid
> warehouseid
> year
> month
> productid
> val1
> ...
> valn
> there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000
> prodocts.
> the primary key is : companyid, warehouseid, year, month, productid
> a) At the end of each month new values are recorded on the table.
> b) During the month updates are issued to the current month.
> c) queries are usualy include:
> * company, warehouse, year, month
> * company, year, month, product
> Question: what is a good clusterd index for this table, what other indexes
> should I implement.
>
The primary key looks like a good clustered index. Since the queries
usually include the four leading PK columns (company, warehouse, year,
month), or the leading column and other PK columns (company, year, month,
product), a clustered PK will be helpful. Of course the updates will
specify the whole key.
For other indexes, look at the workload. You may want a secondary index on
(year,month) or (product).
David
David

Infinitive recursion for my AS2000 calculated member

Hi,

Can someone please help me on this. I get following error when I browse my virtual AS2000 cube:

<Infinite recursion detected during execution of calculated member Sum({Descendants....>

I have implemented a 'dummy' utility dimension with one calculated member: (I have used a parentchild dimension because that's only way I know how to get the formula a from source view):

--

view [dbo].[vdimUtilityCalculation] as

select
'CalculationID' = 1,
'ParentID' = 1,
'CalculationName' = 'Currency',
'Formula' = 'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))',
'MemberOption' = 'SOLVE_ORDER=''-1'''
--

Any idea?

Thanks, Christer

You need to change coordinate in the utility dimension in order to prevent infinite recursion. I.e. something like that:

'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", (UtilityDim.DefaultMember,[Amount Fixr]), (UtilityDim.DefaultMember,[Amount Flor])) * ValidMeasure([Rate]))',

|||

Thanks for your reply.

I added a second 'dummy' member to my utility dimension and pointed to in the formula like this:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", ([CalculationUtility].&[2],[Amount Fixr]), ([CalculationUtility].&[2],[Amount Flor])) * ValidMeasure([Rate]))

It works for some accounts (account dimension with calculated members), not accounts with formula like account1/account2, they are not calculated correctly... and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

Not sure how to get this working, is this easier to implement in AS 2005?

Thanks, Christer

|||

and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

It should work. I suggest you start troubleshooting by creating a calculated member in this utility dimension with SOLVE_ORDER=-1 and then sending MDX query with Aggregate over Period dimension. It isn't easy to help through newsgroup, but if you will take it slow, step by step, you should be able to see that it does work.

|||

Thanks! After some struggle I got it to work!

inedex - should I cluster?

Hi, I have the following delema:
I have a table that has the following fields:
companyid
warehouseid
year
month
productid
val1
...
valn
there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000 prodoct
s.
the primary key is : companyid, warehouseid, year, month, productid
a) At the end of each month new values are recorded on the table.
b) During the month updates are issued to the current month.
c) queries are usualy include:
* company, warehouse, year, month
* company, year, month, product
Question: what is a good clusterd index for this table, what other indexes
should I implement.
Thx,
Juan"jccondor" <jccondor@.discussions.microsoft.com> wrote in message
news:1373BADF-2A63-4D61-853C-C6A9B894DF73@.microsoft.com...
> Hi, I have the following delema:
> I have a table that has the following fields:
> companyid
> warehouseid
> year
> month
> productid
> val1
> ...
> valn
> there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000
> prodocts.
> the primary key is : companyid, warehouseid, year, month, productid
> a) At the end of each month new values are recorded on the table.
> b) During the month updates are issued to the current month.
> c) queries are usualy include:
> * company, warehouse, year, month
> * company, year, month, product
> Question: what is a good clusterd index for this table, what other indexes
> should I implement.
>
The primary key looks like a good clustered index. Since the queries
usually include the four leading PK columns (company, warehouse, year,
month), or the leading column and other PK columns (company, year, month,
product), a clustered PK will be helpful. Of course the updates will
specify the whole key.
For other indexes, look at the workload. You may want a secondary index on
(year,month) or (product).
David
David

inedex - should I cluster?

Hi, I have the following delema:
I have a table that has the following fields:
companyid
warehouseid
year
month
productid
val1
...
valn
there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000 prodocts.
the primary key is : companyid, warehouseid, year, month, productid
a) At the end of each month new values are recorded on the table.
b) During the month updates are issued to the current month.
c) queries are usualy include:
* company, warehouse, year, month
* company, year, month, product
Question: what is a good clusterd index for this table, what other indexes
should I implement.
Thx,
Juan
"jccondor" <jccondor@.discussions.microsoft.com> wrote in message
news:1373BADF-2A63-4D61-853C-C6A9B894DF73@.microsoft.com...
> Hi, I have the following delema:
> I have a table that has the following fields:
> companyid
> warehouseid
> year
> month
> productid
> val1
> ...
> valn
> there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000
> prodocts.
> the primary key is : companyid, warehouseid, year, month, productid
> a) At the end of each month new values are recorded on the table.
> b) During the month updates are issued to the current month.
> c) queries are usualy include:
> * company, warehouse, year, month
> * company, year, month, product
> Question: what is a good clusterd index for this table, what other indexes
> should I implement.
>
The primary key looks like a good clustered index. Since the queries
usually include the four leading PK columns (company, warehouse, year,
month), or the leading column and other PK columns (company, year, month,
product), a clustered PK will be helpful. Of course the updates will
specify the whole key.
For other indexes, look at the workload. You may want a secondary index on
(year,month) or (product).
David
David
sql

Indexing..... Which one will use....

I have a table with the following columns.
Customer Id, SSN and rest of the columns.
I have two queries, one with WHERE criteria as CustomerId and other with
WHERE criteria as SSN.
I have created two Index on the above table. One index uses CustomerId as
the indexed column and other index uses SSN as the Index column.
So when I run the queries how to find out that SQL Server used the
appropriate index. Is it possible to specify the index while running the
query through the application?
Thanks,
AkshayRun your query by enabling Execution plan in Query Analyzer
Thanks,
RK
"Akshay Sen" wrote:

> I have a table with the following columns.
> Customer Id, SSN and rest of the columns.
> I have two queries, one with WHERE criteria as CustomerId and other with
> WHERE criteria as SSN.
> I have created two Index on the above table. One index uses CustomerId as
> the indexed column and other index uses SSN as the Index column.
> So when I run the queries how to find out that SQL Server used the
> appropriate index. Is it possible to specify the index while running the
> query through the application?
> Thanks,
> Akshay
>
>
>
>|||and as for the second one: yes, you can specify which index to be used, via
a hint:
select ... from table with(index(index_name))
but you should better leave to sql server to determine the execution plan,
and the indexes used. sql server's reasoning is based on number of factors,
like column and index statistics, which change over time and which your
application doesn't know about. even worse, the execution plan that was
optimal once, might become less than optimal as tha data in tables change.
dean
"Ram Kumar Koditala" <RamKumarKoditala@.discussions.microsoft.com> wrote in
message news:CDC6F3B0-4377-4801-BDD2-A696BF76B791@.microsoft.com...
> Run your query by enabling Execution plan in Query Analyzer
> Thanks,
> RK
> "Akshay Sen" wrote:
>
as

Indexing Columns

If you have a table with 3 columns,

ID (Primary Key)

Col1

Col2

And you have to perform the following query frequently

Code Snippet

Select ID where Col1='SomeValue' and Col2='SomeOtherValue'

Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"

I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea

If you are using SQL 2005, and this is a frequent or common query, you may wish to explore using the new 'INCLUDE' option.

You could create an INDEX on Col1, Col2, and include [ID].

Something like this:

CREATE NONCLUSTERED INDEX ix_MyTable_Col1Col2
ON MySchema.MyTable( Col1, Col2 )
INCLUDE ( [ID] );

This is a 'covered' index. The entire query is satisfied by the index.

|||Thanks Arnie.... I have to support both SQL 2005 and SQL 2000 for this application at the moment.

|||

For SQL 2000, if you use this query frequently, index all three columns.

|||

you can not define ID as non clustered since it is a PK.

ID can be clustered index and check the unique checkbox.

you could define col1 and col2 as non clustered and ID as included column but beware of space used by index.

|||

You should create clustered primary indexes based off the 80/20 rule. If you are accessing this table 80% of the time by col1 and col2, then create a clustered primary index over col1, col2, ID. Creating an index over just the ID column will almost always cause bookup lookups. Create primary key indexes based off of usage, not how fast can I load data.

|||Chances are that your ID column is part of the automatically created clustered index since it's the primary key.

If that's the case, remember that all columns in the clustered index are appended to all non-clustered indexes for that table.

So there is no reason to add ID to your non-clustered index since it will be there already.

I think that SQL Server is smart enough to just ignore the ID column in the index definition since it knows that it's part of the clustered index, but I'm not sure on that one.

Having all three columns part of an index (ID in the clustered, and Col1 and Col2 in the non-clustered) creates, as somebody else mentioned, a "covering" index.

Basically, a covering index is an index which includes all references columns in your query (from the SELECT, JOIN, and WHERE clauses) so that no bookmark loops are necessary to return all the data. This data can come entirely from indexes... which is much faster than having to go read additional data pages to snag the original row from the table.

|||<P align=left><FONT face=Arial size=2>Hi,</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;We could arrive at a decision of using the index on&nbsp;columns based on the recommendation of the SQL Profiler utility. The input for the profiler would be a database&nbsp;trace file. This trace file will capture the usage of the table by the users and using this profiler will decide whether to use index. Also in this scenario, the table has only 3 columns and all the three columns are accessed by the user frequently. So the choice would be going for the covering index where all the three columns will be covered under index.</P>
<P align=left>&nbsp;</P>
<P align=left>Thanks.</P>

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?
Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced i
n
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustere
d)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the larges
t
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I ca
n
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?