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_SCHEMA query question: constraint columns

Hi Folks:

I'm a little new to SQLServer, so please pardon my ignorance!

I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
TABLE_CONSTRAINTS. I'm looking for the views that will give me the list of
columns by constraint.

For instance, if Table1 has a unique key called Table1_UK01, I can find that
under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know the
columns in that UK constraint. I've tried
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this user
table doesn't seem to show up in either of those views.

Can anyone point me in the right direction? Any sample queries would be
tremendously appreciated. I'm going to be using this meta-data to
automatically generate quite a bundle of stored procs that do updates based
on finding rows via unique keys...

TIA,
DaveUnique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
constraint or an index? Use constraints and there shouldn't be a problem.
There is no physical difference between a unqiue constraint and a unique
index.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<vpGdnZ-5heemC1Pd4p2dnA@.giganews.com>...
> Unique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
> KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
> constraint or an index? Use constraints and there shouldn't be a problem.
> There is no physical difference between a unqiue constraint and a unique
> index.

Yes, these are declared as constraints, not just indexes.

I think I've figured out the problem, but I don't know how to fix it.
The user tables are all owned by a user we created called "dw". The
docs say that these views return info about objects the current user
has access to. If I select current_user, I get "dbo". I notice that
the information_schema.constraint_column_usage only returns info about
constraints where the table is owned by dbo.

When I connect, I'm connecting (in Query Analyzer, for instance) as
user dw, but if I immediately select current_user, it shows me "dbo".
I'd assume if I can connect as "dw" rather than "dbo", I'll actually
see the constraint_column_usage meta-data for tables owned by "dw"
rather than "dbo".

So, how do I "get connected" as the user "dw" rather than "dbo".
Logging in as SQLServer authenticated user "dw" obviously isn't doing
the trick. Is there some sort of ALTER statement to change my
current_user? (This is SQLServer 7.0, btw).

TIA!
Dave|||Dave Sisk (dsisk@.nc.rr.com.0nospam0) writes:
> I'm a little new to SQLServer, so please pardon my ignorance!
> I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
> TABLE_CONSTRAINTS. I'm looking for the views that will give me the list
> of columns by constraint.
> For instance, if Table1 has a unique key called Table1_UK01, I can find
> that under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to
> know the columns in that UK constraint. I've tried
> INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this
> user table doesn't seem to show up in either of those views.
> Can anyone point me in the right direction? Any sample queries would be
> tremendously appreciated. I'm going to be using this meta-data to
> automatically generate quite a bundle of stored procs that do updates
> based on finding rows via unique keys...

Rather than getting lost in the maze of the INFORMATION_SCHEMA views,
access the system tables directly. You will need to do that anyway if
you need information about indexes that are not constraints. Here is a
query:

SELECT i.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
AND ik.colid = c.colid
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND o.name = 'accountstats'
AND o.uid = USER_ID('dw')
ORDER BY i.name, ik.keyno

Gives you all indexes and their columns for this table. (It's possible
to constrain it to only unique constriaints, but I'm too lazy for that
now. Hint is that Unique constratins live in sysobjects too, and with
a parentobj = the object id for the table.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement = 'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
--
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>sql

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
Simon
Only with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan
|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon
|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegro ups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

Information Schema Query Question

Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
AkintoyeHere's an example:
SELECT TC.TABLE_SCHEMA AS TableOwner,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN
(
'PRIMARY KEY',
'UNIQUE'
)
ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME, KCU.COLUMN_NAME
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in message
news:4366775c$1@.ibixwebf.ibi.com...
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoye
>|||"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in
news:4366775c$1@.ibixwebf.ibi.com:

> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
You're looking for simething like this? (SQL Server 2005):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_schema + '.' + tc.table_name = <schema>.<table_name> )
I guess that would be approximately like this in SQL Server 2000 (Not
tested):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_name = <table_name> )
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9700F370E9A4Dolekristianbangaas@.
207.46.248.16:
(...)
Ooops, stayed a littlebit too long in my outbox :( Sorry folks.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

Information Schema Query Question

Hello All,

Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?

Thanks in Advance,

Akintoyeselect t.table_name, t.constraint_type, t.constraint_name,
c.column_name
from information_schema.TABLE_CONSTRAINTS t
join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name
= c.constraint_name
where constraint_type IN ('UNIQUE', 'PRIMARY KEY')
and t.table_name = <Table Name
Akintoye Olorode wrote:
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoyesql

Information Schema Query on linked server fails.

Hi, We had a simple application which compared schemas on local servers. We
are updating it to compare schemas across servers. Unfortunately the query
which refers to the Information_Schema fails, and I cant find any syntax to
make it work!
Here is the simplified version of the query.
SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].INFORMATION_SCHEMA.TABLES
tried the following combinations out of frustration, but none worked.
SELECT 1 FROM [Matrix].[ReviewRecorder].[INFORMATION_SCHEMA].TABLES
SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].[INFORMATION_SCHEMA].[TABLES]
Obviously Matrix is the name of the remote Database Server, and it has been
linked already to the local Database.
The error i get is
--
The object name 'Matrix.ReviewRecorder.DBO.INFORMATION_SCHEMA.' contains
more than the maximum number of prefixes. The maximum is 3.
--
sp_linkedservers shows
--
Matrix SQLOLEDB SQL Server Matrix NULL NULL NULL
--
HELP !
Thanks
MohammedHi
Have you tried using select * from
{RemoteSvr}.{dbname}.information_schema.tables
Substitute the name in {RemoteSvr} and {dbname}
John
"MLokhandwala" wrote:

> Hi, We had a simple application which compared schemas on local servers. W
e
> are updating it to compare schemas across servers. Unfortunately the query
> which refers to the Information_Schema fails, and I cant find any syntax t
o
> make it work!
> Here is the simplified version of the query.
> SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].INFORMATION_SCHEMA.TABLES
> tried the following combinations out of frustration, but none worked.
> SELECT 1 FROM [Matrix].[ReviewRecorder].[INFORMATION_SCHEMA].TABLES
> SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].[INFORMATION_SCHEMA].[TABLES]
> Obviously Matrix is the name of the remote Database Server, and it has bee
n
> linked already to the local Database.
> The error i get is
> --
> The object name 'Matrix.ReviewRecorder.DBO.INFORMATION_SCHEMA.' contains
> more than the maximum number of prefixes. The maximum is 3.
> --
> sp_linkedservers shows
> --
> Matrix SQLOLEDB SQL Server Matrix NULL NULL NULL
> --
> HELP !
> Thanks
> Mohammed|||Yes,
I have tried all combinations, including dropping the owner name etc. but no
luck.
Any other suggestions are welcome.
Still awaiting a solution.
Mohammed
"John Bell" wrote:
> Hi
> Have you tried using select * from
> {RemoteSvr}.{dbname}.information_schema.tables
> Substitute the name in {RemoteSvr} and {dbname}
> John
> "MLokhandwala" wrote:
>|||Hi
It seems 4 part naming only works in master!!!! You could try either calling
a stored procedure in the remote database or creating a view e.g.
-- On Remote Server database run:
CREATE VIEW MyTables AS SELECT * FROM INFORMATION_SCHEMA.TABLES
-- From Local Server Access Remove server
SELECT * FROM Matrix.ReviewRecorder.dbo.MyTables
John
"MLokhandwala" wrote:
> Yes,
> I have tried all combinations, including dropping the owner name etc. but
no
> luck.
> Any other suggestions are welcome.
> Still awaiting a solution.
> Mohammed
>
> "John Bell" wrote:
>|||If you are willing to use Java, there is a free open-source tool called
SchemaCrawler on SourceForge that can compare schemas between databases
on two different servers. Download SchemaCrawler from:
http://sourceforge.net/project/show...group_id=148383

Info!

Hi All, 2 doubts
1. How can I retrieve the exact command (T-SQL) being executed by an SPID on
the query Analyzer. You can get the same info using Ent. Mgr and right click
on SPID.
2. What's the best technic (light weight method) to log errors from a Stored
Proc? I don't wanna use xp_cmdshell...
TIA1) DBCC INPUTBUFFER (SPID) capped at 255 I believe, or use
fn_get_sql() SP3 + on SQl server 2000 take a look here
(http://vyaskn.tripod.com/fn_get_sql.htm)
2) log the errors in a ErrorLog table with procname, date, any other
info (parameters maybe) etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||DBCC INPUTBUFFER (SPID)
note - it will truncate the statement after a particular length.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:e9STBJddGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Hi All, 2 doubts
> 1. How can I retrieve the exact command (T-SQL) being executed by an SPID
> on
> the query Analyzer. You can get the same info using Ent. Mgr and right
> click
> on SPID.
> 2. What's the best technic (light weight method) to log errors from a
> Stored
> Proc? I don't wanna use xp_cmdshell...
> TIA
>|||> 2. What's the best technic (light weight method) to log errors from a Storedd">
> Proc? I don't wanna use xp_cmdshell...
Where do you want these errors to be logged? RAISERROR and xp_logevent can b
e options, but hard to
tell without more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vai2000" <nospam@.microsoft.com> wrote in message news:e9STBJddGHA.1204@.TK2MSFTNGP02.phx.gb
l...
> Hi All, 2 doubts
> 1. How can I retrieve the exact command (T-SQL) being executed by an SPID
on
> the query Analyzer. You can get the same info using Ent. Mgr and right cli
ck
> on SPID.
> 2. What's the best technic (light weight method) to log errors from a Stor
ed
> Proc? I don't wanna use xp_cmdshell...
> TIA
>sql

Info about Error 8623

Hi,

SQL Server returned me error 8623 "Internal Query Processor Error: The query processor could not produce a query plan." I've looked for info in the SQL books, but there's no info about this error. I would appreciate it a lot if somebody could tell me what this error means, how is it triggered, and if it's possible, how to avoid it.

Thanks a lot,

FedericoWhere are you doing this?

What's the query?

It's dynamic, isn't it...|||It would be better if you quote the query involved as per Brett's reply.
This KBA1 (http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729) and KBA2 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;286255) refers about 863 error.

HTH|||I can't reproduce this error. So I think it's sporadyc. Anyway I'll check my SELECT clauses to see if there's something similar to what Satya's link mention. Again, thanks a lot|||PROFILER may help you to track the process of query against database, just in case if you don't know.

inflectional causing "query contained only ignored words" error

Hi
I am receiving the error "query contained only ignored words" from the word
"best" that is not in the ignored words list. While researching the problem
online I found the following link:
http://www.webservertalk.com/archive.../t-965368.html
In the post the poster states this is a known bug in full-text indexing and
has been written up as a "DOC bug" (not sure what that means) by MS. I am
hoping there is an update and/or fix for this problem.
Any help would be appreciated.
Thanks
Tony
remove the word well from your noise word list. Stop MSSearch before making
this change and restart it after. Then rebuild your index.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tony Vargas" <Tony Vargas@.discussions.microsoft.com> wrote in message
news:8CA64E3E-2645-476C-859B-776BA62108CD@.microsoft.com...
> Hi
> I am receiving the error "query contained only ignored words" from the
word
> "best" that is not in the ignored words list. While researching the
problem
> online I found the following link:
> http://www.webservertalk.com/archive.../t-965368.html
> In the post the poster states this is a known bug in full-text indexing
and
> has been written up as a "DOC bug" (not sure what that means) by MS. I am
> hoping there is an update and/or fix for this problem.
> Any help would be appreciated.
> Thanks
> Tony
|||Tony,
A "DOC bug" is a documentation bug, i.e., a "by design" feature that is not
documented in Books Online (BOL) or in a KB article.
I'll confirm that the DOC bug, however, and to the best of my knowledge, no
KB article for this has been written or made public, I'm sad to say. The
workaround at this time, is not to use the 'FORMSOF(INFLECTIONAL) parameter.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Tony Vargas" <Tony Vargas@.discussions.microsoft.com> wrote in message
news:8CA64E3E-2645-476C-859B-776BA62108CD@.microsoft.com...
> Hi
> I am receiving the error "query contained only ignored words" from the
word
> "best" that is not in the ignored words list. While researching the
problem
> online I found the following link:
> http://www.webservertalk.com/archive.../t-965368.html
> In the post the poster states this is a known bug in full-text indexing
and
> has been written up as a "DOC bug" (not sure what that means) by MS. I am
> hoping there is an update and/or fix for this problem.
> Any help would be appreciated.
> Thanks
> Tony

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:

> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>
|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegr oups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>
|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/de...filedatats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
--
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:

> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegroups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/d...r />
atats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
--
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegroups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/filedatats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--

Indexing Service and hyphens

I am trying to search for a word such as "e-business" using the Indexing
Service Query object (CissoQuery). Now what I would like to do is to be abl
e
to search for e-bus and return results of variations of this term, e.g.
e-business, e-busi. So effectively, I would like to a do a wildcard search.
Unfortunately, when I search for this term, it returns to me documents that
do not have e-business in them but variations of e (I have modified the nois
e
list to remove noise words) and business as well as ebusiness. I don't want
this to happen. I can search for the phrase "e-business" and it returns the
correct results back. However if I search for "e-bus" it returns no results
back because it is looking for the entire phrase. If I search for e-busines
s
without the quotes, I get the variations of which I talked about earlier for
documents that don't contain that phrase. How do I configure Indexing
Service to return me results with hyphens back. I have yet to find any
answer on the web anywhere where this question has been asked sufficiently.
If this is a bug and cannot be done in indexing service, please tell me and
I
will stop attempting to try and figure this out. I am aware that this is a
general indexing service question but I know sql server uses the service
internally or something like it, so I am posting this question to this
newsgroup.Hammad,
It might be best to post this question to
microsoft.public.sqlserver.fulltext or
microsoft.public.inetserver.indexserver newsgroups as this is a somewhat
specialized area...
The Indexing Service (IS) uses the same OS-supplied word breakers that
determine the language specific breaking of words into tokens. For example,
using a URL, such as 'http://jtkane.com?search=what#is#my+name' that
includes punctuation characters such as :, /, ?, =, and + will be tokenized
as follows on Windows Server 2003 and Windows XP using the LangWrbk.dll
wordbreaker:
Original text: 'http://jtkane.com?search=what#is#my+name'
IWordSink::PutWord: cwcSrcLen 4, cwcSrcPos 0, cwc 4, 'http'
IWordSink::PutWord: cwcSrcLen 6, cwcSrcPos 7, cwc 6, 'jtkane'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 14, cwc 3, 'com'
IWordSink::PutWord: cwcSrcLen 6, cwcSrcPos 18, cwc 6, 'search'
IWordSink::PutWord: cwcSrcLen 4, cwcSrcPos 25, cwc 4, 'what'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 30, cwc 2, 'is'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 33, cwc 2, 'my'
IWordSink::PutWord: cwcSrcLen 4, cwcSrcPos 36, cwc 4, 'name'
However, on Windows 2000 Server the same URL will be tokenized as a single
token using the infosoft.dll wordbreaker:
Original text: 'http://jtkane.com?search=what#is#my+name'
IWordSink::PutWord: cwcSrcLen 40, cwcSrcPos 0, cwc 39,
'http://jtkane.com?searchwhat#is#my+name'
The same is true for SQL Server's Full Text Search (FTS) component as is
true for the Indexing Service as both depend upon the OS-supplied
wordbreakers. Could you post the full output of -- SELECT @.@.version -- as
this would be most helpful in troubleshooting your questions.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Hammad" <Hammad@.discussions.microsoft.com> wrote in message
news:80C23DCB-6475-4555-93D8-DD30DF5EA337@.microsoft.com...
> I am trying to search for a word such as "e-business" using the Indexing
> Service Query object (CissoQuery). Now what I would like to do is to be
able
> to search for e-bus and return results of variations of this term, e.g.
> e-business, e-busi. So effectively, I would like to a do a wildcard
search.
> Unfortunately, when I search for this term, it returns to me documents
that
> do not have e-business in them but variations of e (I have modified the
noise
> list to remove noise words) and business as well as ebusiness. I don't
want
> this to happen. I can search for the phrase "e-business" and it returns
the
> correct results back. However if I search for "e-bus" it returns no
results
> back because it is looking for the entire phrase. If I search for
e-business
> without the quotes, I get the variations of which I talked about earlier
for
> documents that don't contain that phrase. How do I configure Indexing
> Service to return me results with hyphens back. I have yet to find any
> answer on the web anywhere where this question has been asked
sufficiently.
> If this is a bug and cannot be done in indexing service, please tell me
and I
> will stop attempting to try and figure this out. I am aware that this is
a
> general indexing service question but I know sql server uses the service
> internally or something like it, so I am posting this question to this
> newsgroup.|||Hi John,
Thanks for your quick response. The version I obtained from using that
command is the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
I've done a little bit of reading on word breakers but I'm not sure how to
actually configure programatically a word breaker to use for indexing or
whether this is even necessary. I'm not exactly sure how the indexing
service works but I assume if it finds a word e-business in a document, it
will index e, business, ebusiness, and e-business, because when I do use the
CissoQuery object and specify the exact phrase "e-business" using Dialect 2,
it does find it. The only issue I have is how to specify a wildcard type
search such that if I type in "e-bus" it will find all variations of words
with e-bus as a prefix. If I don't specify quotes around e-business then it
will find documents that contain variations of e-business like I detailed
previously, so documents that don't have e-business in them actually show up
because they have those variations. If I specify just "e-bus" in quotes
then it looks for the exact phrase and not prefix based words and so it won'
t
find documents that contain that variations of words that start with that
prefix. Is it possible to do such a thing?
Thanks,
Hammad
"John Kane" wrote:

> Hammad,
> It might be best to post this question to
> microsoft.public.sqlserver.fulltext or
> microsoft.public.inetserver.indexserver newsgroups as this is a somewhat
> specialized area...
> The Indexing Service (IS) uses the same OS-supplied word breakers that
> determine the language specific breaking of words into tokens. For example
,
> using a URL, such as 'http://jtkane.com?search=what#is#my+name' that
> includes punctuation characters such as :, /, ?, =, and + will be tokenize
d
> as follows on Windows Server 2003 and Windows XP using the LangWrbk.dll
> wordbreaker:
> Original text: 'http://jtkane.com?search=what#is#my+name'
> IWordSink::PutWord: cwcSrcLen 4, cwcSrcPos 0, cwc 4, 'http'
> IWordSink::PutWord: cwcSrcLen 6, cwcSrcPos 7, cwc 6, 'jtkane'
> IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 14, cwc 3, 'com'
> IWordSink::PutWord: cwcSrcLen 6, cwcSrcPos 18, cwc 6, 'search'
> IWordSink::PutWord: cwcSrcLen 4, cwcSrcPos 25, cwc 4, 'what'
> IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 30, cwc 2, 'is'
> IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 33, cwc 2, 'my'
> IWordSink::PutWord: cwcSrcLen 4, cwcSrcPos 36, cwc 4, 'name'
> However, on Windows 2000 Server the same URL will be tokenized as a single
> token using the infosoft.dll wordbreaker:
> Original text: 'http://jtkane.com?search=what#is#my+name'
> IWordSink::PutWord: cwcSrcLen 40, cwcSrcPos 0, cwc 39,
> 'http://jtkane.com?searchwhat#is#my+name'
> The same is true for SQL Server's Full Text Search (FTS) component as is
> true for the Indexing Service as both depend upon the OS-supplied
> wordbreakers. Could you post the full output of -- SELECT @.@.version -- as
> this would be most helpful in troubleshooting your questions.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Hammad" <Hammad@.discussions.microsoft.com> wrote in message
> news:80C23DCB-6475-4555-93D8-DD30DF5EA337@.microsoft.com...
> able
> search.
> that
> noise
> want
> the
> results
> e-business
> for
> sufficiently.
> and I
> a
>
>