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

inerting/updating a collection of data values into SQL Server db all at once.

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

Assuming that you have the list as a joined string array, you could something like this with the following function I once wrote:

CREATE FUNCTION dbo.Split

(

@.String VARCHAR(200),

@.Delimiter VARCHAR(5)

)

RETURNS @.SplittedValues TABLE

(

OccurenceId SMALLINT IDENTITY(1,1),

SplitValue VARCHAR(200)

)

AS

BEGIN

DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0

BEGIN

SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN

LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues

SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)

END

RETURN

END

So this would evaluate in your case to:

Set @.ListOfIDs = '1, 2, 3, 4, 5'

INSERT INTO SomeTable
(Column)
Select SplitValue From dbo.Split(@.ListOfIDs,',')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I thought that SQL Server 2005 allowed the insertion of VS 2005 DataTables etc. no?|||

MarilynJ wrote:

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

fill the dataset from data from sql server

update on the front end. vs2005 is using twoway binding

so there's not much work to be done

then

call the tableadapter update method

to commit chages to the database as a batch.

this is known as batch update

|||And remember that i you are working completly disconnected from the database that you have to specify your own Commands to update / insert / delete the data. Otherwise you could use the commandbuilder which will get you the appropiate commands if you read the schema from the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.desql

Indexes updating.

hi.
pls guide me how to check whether indexes are updating or not..
i want to check whether inserts/updates are causing slowness and hampering p
erformance of site bcox of unnecessary indexes..Hi Sanjay,
Get the query that you want to inspect into Query Analyzer and get the
estimated execution plan for it with Ctrl+L. You can now see what the query
will do during it's execution and whether it will update any indexes or not.
Jacco Schalkwijk
SQL Server MVP
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFF68260-5288-427E-A5CB-3EBDB15E0AAA@.microsoft.com...

quote:

---

> hi.
> pls guide me how to check whether indexes are updating or not..
> i want to check whether inserts/updates are causing slowness and hampering

---

performance of site bcox of unnecessary indexes..

quote:

---

>

---

Indexes updating.

hi.
pls guide me how to check whether indexes are updating or not..
i want to check whether inserts/updates are causing slowness and hampering performance of site bcox of unnecessary indexes..Hi Sanjay,
Get the query that you want to inspect into Query Analyzer and get the
estimated execution plan for it with Ctrl+L. You can now see what the query
will do during it's execution and whether it will update any indexes or not.
--
Jacco Schalkwijk
SQL Server MVP
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFF68260-5288-427E-A5CB-3EBDB15E0AAA@.microsoft.com...
> hi.
> pls guide me how to check whether indexes are updating or not..
> i want to check whether inserts/updates are causing slowness and hampering
performance of site bcox of unnecessary indexes..
>