Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.comHi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>sql

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.com
Hi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>
|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
--
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.comHi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>

Problem during data porting using Linked Server

Hi All,

I have problems while using the Linked Server in MS SQL Server 2000 for data porting.

The Scenario :

I have about 900 hundred tables created in SQL Server database. These tables are freshly created and has no records.

I have created a Linked Server with a DSN connecting to a Sybase database from which the data has to be ported to the newly created tables in SQL Server.

The database creation as well as data porting is done by a Delphi application by executing the scripts in several .sql files.

I have shown an example script below which does the data porting.

INSERT INTO TEST_DATA (COL1,COL2,COL3)
SELECT COL1,COL2,COL3 FROM [LINK_SYBASEDB]..[DBA].[TEST_DATA]

The Issue :

I often get the below error which stops the data porting process ( the error is logged in the Errorlog by the Delphi application )

D:\DB\Port\TEST_DATA.SQL
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]

NOTE : This error is NOT COMING CONSISTENTLY.Often I get this error and sometimes all the data is ported without this error.

It will be great if any of you can help me to resolve this issue.

Thanks in advance !!!

regards,

Hari Haran ArulmozhiI would bcp the data out and then back in...

Problem Displaying Japanese Characters

We are a software developer here and ran into a problem trying to get SQL Server to display Japanese Characters through a linked server properly. Does anybody have any similar experiences?

The following configurations were able to display Japanese characters properly:
===============================================================================
1. Write a C# application to get Japanese characters from an AS400 table containing Japanese texts. The Japanese text was displayed on the DataGrid. The provider used was HiT OLEDB Server/400. A 30 day eval copy can be obtained from the following link:
http://www.hitsw.com/products_services/register/register_oledb_svr_400.html

2. Create a table containing Japanese text on SQL Server A and create a linked server on SQL Server B linking to SQL Server A. Change the font type of SQL Server B's query analyzer to a font that is compatible with Japanese character. (See code sample one at the bottom of this message for the code used)

3. On a new PC, install Japanese edition of Windows 2000 plus Japanese Edition of SQL Server 2000. Create a linked server pointing to a Japanes table on the AS400 using the HiT OLEDB provider.

The following configurations were not able to display Japanese characters properly:
===================================================================================
i. On a PC with English version of Windows 2000 Professional and English version of SQL Server, change the query analyzer font to a font that is compitable with Japanese text and then use the HiT OLEDB provider to create a linked server pointing to the Japanese table on the AS400.

ii. On the same PC above, install IBM Client Access and make a new linked server pointing to the same Japanese table on the AS400 using Client Access. A Japanese compitable font was selected in the query anaylzer.

The odd thing is that in configuration (1), we were able to see the Japanese characters correctly which suggests that the HiT OLEDB provider has passed the information back to the PC correctly. And in configuration (2) and (3), we were able to show that SQL Server has no problem displaying Japanese characters regardless of its language edition.

We are not sure why it is behaving this way. Does anybody have any thought?

Here is the code used:

Code Sample One
===============
The following code was used on an English version of SQL Server 2000 running on an English version of Windows

2000 professional

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblJap]') and OBJECTPROPERTY(id,

N'IsUserTable') = 1)
drop table [dbo].[tblJap]
GO

CREATE TABLE [dbo].[tblJap] (
[CID] [int] IDENTITY (1, 1) NOT NULL ,
[Jap] [nchar] (30) NOT NULL ,
[Eng] [char] (30) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tblJap (Jap, Eng) VALUES ('織田信長','Oda Nobunaga')
INSERT INTO tblJap (Jap, Eng) VALUES ('豊臣秀吉','Toyotomi Hideyoshi')
INSERT INTO tblJap (Jap, Eng) VALUES ('徳川家康','Tokugawa Ieyasu')
--See note below
GO

SELECT * FROM tblJap

Note
====
Another odd thing was that if I run the code
INSERT INTO tblJap (Jap, Eng) VALUES ('織田信長','Oda Nobunaga')
from query analyzer, the Japanese characters showed up as ? in Enterprise Manager. But if I edit the information again from Enterprise Manager, it shows up properly in both Enterprise Manager and Query Analyzer.

Results you are seeing might be due to the fact that you are inserting characters as regular chars and not as Unicode chars, and their translation (effectively truncation) is affected by Query Analyzer.

Instead of

INSERT INTO tblJap (Jap, Eng) VALUES ('...(Japanese chars)...','Oda Nobunaga')

try

INSERT INTO tblJap (Jap, Eng) VALUES (N'...(Japanese chars)...','Oda Nobunaga')

and see if that fixes your problem.

Regards,
Boris.|||Your solution is working fine.

Problem Displaying Japanese Characters

We are a software developer here and ran into a problem trying to get SQL Server to display Japanese Characters through a linked server properly. Does anybody have any similar experiences?

The following configurations were able to display Japanese characters properly:
===============================================================================
1. Write a C# application to get Japanese characters from an AS400 table containing Japanese texts. The Japanese text was displayed on the DataGrid. The provider used was HiT OLEDB Server/400. A 30 day eval copy can be obtained from the following link:
http://www.hitsw.com/products_services/register/register_oledb_svr_400.html

2. Create a table containing Japanese text on SQL Server A and create a linked server on SQL Server B linking to SQL Server A. Change the font type of SQL Server B's query analyzer to a font that is compatible with Japanese character. (See code sample one at the bottom of this message for the code used)

3. On a new PC, install Japanese edition of Windows 2000 plus Japanese Edition of SQL Server 2000. Create a linked server pointing to a Japanes table on the AS400 using the HiT OLEDB provider.

The following configurations were not able to display Japanese characters properly:
===================================================================================
i. On a PC with English version of Windows 2000 Professional and English version of SQL Server, change the query analyzer font to a font that is compitable with Japanese text and then use the HiT OLEDB provider to create a linked server pointing to the Japanese table on the AS400.

ii. On the same PC above, install IBM Client Access and make a new linked server pointing to the same Japanese table on the AS400 using Client Access. A Japanese compitable font was selected in the query anaylzer.

The odd thing is that in configuration (1), we were able to see the Japanese characters correctly which suggests that the HiT OLEDB provider has passed the information back to the PC correctly. And in configuration (2) and (3), we were able to show that SQL Server has no problem displaying Japanese characters regardless of its language edition.

We are not sure why it is behaving this way. Does anybody have any thought?

Here is the code used:

Code Sample One
===============
The following code was used on an English version of SQL Server 2000 running on an English version of Windows

2000 professional

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblJap]') and OBJECTPROPERTY(id,

N'IsUserTable') = 1)
drop table [dbo].[tblJap]
GO

CREATE TABLE [dbo].[tblJap] (
[CID] [int] IDENTITY (1, 1) NOT NULL ,
[Jap] [nchar] (30) NOT NULL ,
[Eng] [char] (30) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tblJap (Jap, Eng) VALUES ('織田信長','Oda Nobunaga')
INSERT INTO tblJap (Jap, Eng) VALUES ('豊臣秀吉','Toyotomi Hideyoshi')
INSERT INTO tblJap (Jap, Eng) VALUES ('徳川家康','Tokugawa Ieyasu')
--See note below
GO

SELECT * FROM tblJap

Note
====
Another odd thing was that if I run the code
INSERT INTO tblJap (Jap, Eng) VALUES ('織田信長','Oda Nobunaga')
from query analyzer, the Japanese characters showed up as ? in Enterprise Manager. But if I edit the information again from Enterprise Manager, it shows up properly in both Enterprise Manager and Query Analyzer.

Results you are seeing might be due to the fact that you are inserting characters as regular chars and not as Unicode chars, and their translation (effectively truncation) is affected by Query Analyzer.

Instead of

INSERT INTO tblJap (Jap, Eng) VALUES ('...(Japanese chars)...','Oda Nobunaga')

try

INSERT INTO tblJap (Jap, Eng) VALUES (N'...(Japanese chars)...','Oda Nobunaga')

and see if that fixes your problem.

Regards,
Boris.

|||Your solution is working fine.

problem creating view on table from linked server DB using IP addr

Hello,
I can create a view on a table from a named linked server database.
select * from server1.RemoteDB.dbo.Table1
But I am having a problem creating a view on a table from a non-named linked
server that is just using the IP address of the server. Example:
Select * from [56.19.175.167].RemoteDB.dbo.Table1
When I run the view (in design mode) the square brackets get moved around
like this:
Select * from [56].[19.175.167.RemoteDB].dbo.Table1
The error message says it cannot find the server [56] and to re-run
sp_addlinkedserver. Could someone share the correct syntax for using the I
P
address as the server name?
Thanks,
Rich> When I run the view (in design mode)
STOP DOING THAT!
Create your view in Query Analyzer, and run the view in Query Analyzer.
Enterprise Mangler's tool for this is quite crippled and this is not the
only problem you'll encounter. Try using a CASE expression in your query,
for one.
A|||Just a few more details:
I am already aliasing the remote table
Select * from [56.19.175.167].RemoteDB.dbo.Table1 tblx
and
for the linked server that I can create a view on - that server resides on
the same server computer as the server I am working from.
The server I am having a problem with is a remote server which resides 3000
miles away from my local server. Does this make a difference?
"Rich" wrote:

> Hello,
> I can create a view on a table from a named linked server database.
> select * from server1.RemoteDB.dbo.Table1
> But I am having a problem creating a view on a table from a non-named link
ed
> server that is just using the IP address of the server. Example:
> Select * from [56.19.175.167].RemoteDB.dbo.Table1
> When I run the view (in design mode) the square brackets get moved around
> like this:
> Select * from [56].[19.175.167.RemoteDB].dbo.Table1
> The error message says it cannot find the server [56] and to re-run
> sp_addlinkedserver. Could someone share the correct syntax for using the
IP
> address as the server name?
> Thanks,
> Rich
>|||> Select * from [56.19.175.167].RemoteDB.dbo.Table1 tblx
Another thing to reduce the complexity here, of having IP addresses
hard-coded into your query, is to create a simply-named alias using Client
Network Utility, and then refer to the alias instead of the IP address. Not
that this makes it okay to use the view designer, but I think it is a better
approach overall. In addition to alleviating problems with 4-dot naming, it
also makes it much easier to update the system should that IP address
change - you just change the alias definition instead of all the places you
manually referred to it in code.

problem creating view on table from linked server DB using IP

>>create a simply-named alias using Client Network Utility
This sounds like a great idea. May I ask where I locate the Client Network
Utility and how to create the simply-named alias?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Another thing to reduce the complexity here, of having IP addresses
> hard-coded into your query, is to create a simply-named alias using Client
> Network Utility, and then refer to the alias instead of the IP address. N
ot
> that this makes it okay to use the view designer, but I think it is a bett
er
> approach overall. In addition to alleviating problems with 4-dot naming,
it
> also makes it much easier to update the system should that IP address
> change - you just change the alias definition instead of all the places yo
u
> manually referred to it in code.
>
>> This sounds like a great idea. May I ask where I locate the Client
> Network
> Utility
Start > Programs > Microsoft SQL Server >|||Thank you. So I went to the utility. I clicked on the Alias tab. Then I
clicked Add. In the Server alias box I entered the alias name which was
simultaneously showing up in the Connection Parameters Server Name. I
changed that to the IP address of the remote server. Then I selected TCP/IP
in the Network Libaries and clicked OK.
In the Linked Server (from Enterprise Manager), I added the Aliased remote
server using the same context as the link using the IP address. I was not
able to link to the Alias server. Do I need to remove the linked server tha
t
is using the IP address first? I am a little afraid of doing that because
I
already have my client app (VB.Net) working and can retrieve data from the
views (which use the IP address of the remote server).
Well, if anything, it is an interesting exercise.
Thanks for the help.
Rich
"Aaron Bertrand [SQL Server MVP]" wrote:

> Start > Programs > Microsoft SQL Server >
>
>|||Server alias: should be "myserver"
Server name: should "a.b.c.d"
When you add the linked server, use the name "myserver"
What does "not able to" mean? Did you get an error message? If so, what
was it?
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B2E4C610-E3DB-4229-93BD-D0B277868000@.microsoft.com...
> Thank you. So I went to the utility. I clicked on the Alias tab. Then I
> clicked Add. In the Server alias box I entered the alias name which was
> simultaneously showing up in the Connection Parameters Server Name. I
> changed that to the IP address of the remote server. Then I selected
> TCP/IP
> in the Network Libaries and clicked OK.
> In the Linked Server (from Enterprise Manager), I added the Aliased remote
> server using the same context as the link using the IP address. I was not
> able to link to the Alias server. Do I need to remove the linked server
> that
> is using the IP address first? I am a little afraid of doing that
> because I
> already have my client app (VB.Net) working and can retrieve data from the
> views (which use the IP address of the remote server).
> Well, if anything, it is an interesting exercise.
> Thanks for the help.
> Rich
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||When I add the Aliased server I use the Alias name "myServer"
I use the same security context as the original linked server - the UID,
impersonate, and the password, then apply. The new linked server shows up i
n
Enterprise manager, but when I click on tables - about 30 seconds later I ge
t
the error message that either the server "myServer" does not exist or Access
denied. In Query analyzer I try select * from myserver.remotedb.dbo.table1
I get the error message the same error message
"Server does not exist or Access denied"
when I look in sysservers, however
Use Master
select * from sysservers
I can see "myServer" in the list of servers. My question now is if the
Alias is correct - correctly referring to my remote server, and am I using
the correct security context.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Server alias: should be "myserver"
> Server name: should "a.b.c.d"
> When you add the linked server, use the name "myserver"
> What does "not able to" mean? Did you get an error message? If so, what
> was it?
>
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:B2E4C610-E3DB-4229-93BD-D0B277868000@.microsoft.com...
>
>|||Not sure, but you may have to restart the SQL Server service for the alias
to be picked up and routable? Also, have you tried any other authentication
methods (e.g. not defining a local login and checking "be made using this
security context" and putting the remote uid/password in that dialog)?
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:CC0FF896-33EF-4F9E-B881-1867D785255C@.microsoft.com...
> When I add the Aliased server I use the Alias name "myServer"
> I use the same security context as the original linked server - the UID,
> impersonate, and the password, then apply. The new linked server shows up
> in
> Enterprise manager, but when I click on tables - about 30 seconds later I
> get
> the error message that either the server "myServer" does not exist or
> Access
> denied. In Query analyzer I try select * from
> myserver.remotedb.dbo.table1
> I get the error message the same error message
> "Server does not exist or Access denied"
> when I look in sysservers, however
> Use Master
> select * from sysservers
> I can see "myServer" in the list of servers. My question now is if the
> Alias is correct - correctly referring to my remote server, and am I using
> the correct security context.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Problem creating indexed views with linked servers

Hi, I have a problem, maybe someone can help me.

I'm traing to create a view with a Linked Server

This query works great:

select id, descr
from SERVER.DB.dbo.TABLE

When I tray to create the view:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select id, descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3
Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'
is invalid for schema binding. Names must be in two-part format and an
object cannot reference itself.

So I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3
The number name 'SERVER.DB.dbo.TABLE' contains more than the maximum
number of prefixes. The maximum is 3.

Then I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select a.id, a.descr
from SERVER.DB.dbo.TABLE as A
GO

I Have this error

Server: Msg 4512, Level 16, State 3, Procedure View1, Line 3
Cannot schema bind view 'dbo.View1' because name
'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Names
must be in two-part format and an object cannot reference itself.

This query alone works great:

select a.id, a.descr
from SERVER.DB.dbo.TABLE as A

The names aren't what I describe here (id is not valid without []).

ANY IDEAS?!??!?!

I don't know what else can I do.

I need help!!!

TANKS A LOT!!!!!!!!Ariel (ap@.data54.com) writes:
> Hi, I have a problem, maybe someone can help me.
> I'm traing to create a view with a Linked Server

Stop! Take a step back and consider what you are doing!

An indexed view is a materialized view. This means that when an update
is made to a base table, this update is propagated to the view, not
only logically, but also physically.

Now, if that table on the remote server was updated would should happen?
Particularly, what would happen if our local server was unavailble? Or
the particular database was unavailable?

In practice, you cannot even have cross-database indexed views on the
same server, and you run into a stopblock already WITH SCHEMABINDING.
If you could create a schema-bounbd view that referenced another database,
each attempt to drop or rename a databaes, or set a it offline, would
need to check all other databases on the server for SCHEMABINDING
references.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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