Linking Server to MySql

Hello All,
I am stuck with this really frustrating problem...

I am using a linked server to run a qeury to mysql with sp_addlinked server etc...

The problem i am having is I am using this in a trigger with sql server 2000 and it always errors there for the row doesnt get inserted.

the error is
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'insert into mail_relay(ip_addr,timestamp) values('myip',1039087122)'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

With mysql I cant use 4 part names cause I get this error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

So I have to run
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122)')

and mysql also doesnt support multiple queries in the one statement so i cant do this to stop the error:
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122);select null')

so my question is,
I want to be able to stop the error message being reported in my trigger so the row still gets inserted

I am sure it is possible.. I really hope so... any help would be really greatly appreciated

thanks guysCan you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.|||Originally posted by zmichailov
Can you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.

Outside of the trigger the error still occurs...
Here is a the way i set the connection up...

EXEC sp_addlinkedserver
'MailServer',
'',
'MSDASQL',
NULL,
NULL,
'DSN=MailServer;UID=myuser;pwd=mypass;'
EXEC sp_addlinkedsrvlogin
@.rmtsrvname='MailServer',
@.useself=false,
@.locallogin=null,
@.rmtuser='myuser',
@.rmtpassword='mypass'
Exec sp_serveroption 'MailServer', 'data access', 'true'|||Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay|||Originally posted by zmichailov
Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay

Hey, Sorry for delay in reply,
in answer to these questions

1. The default database on the DSN is vpopmail, I am using the latest MyODBC driver for this...
In mySql you can have as many dbs as your want to on any server.

2. i get the following error message
Server: Msg 7313, Level 16, State 1, Line 17
Invalid schema or catalog specified for provider 'MSDASQL'.

i also found MyOLEDB ole db provider but It would create an instanmce of it it just errors saying

Server: Msg 7302, Level 16, State 1, Line 17
Could not create an instance of OLE DB provider 'MySQLProv'.

Well any help would be great, thanks again|||i have exactly same problem, do you have some solution?|||This may help.

http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.htmlsql

Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

sql

Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

servers and transcations

Hello

I'm trying to run an update on data in one SQL Server, based on data from another SQL Server (they're linked). It seemed to take forever, even with just one row in the table, so I tried making a dummy query that just printed some stuff on the screen. This also took forever, so I tried doing the same thing, but not as a transaction. Then it worked perfectly!

What is the problem with linked servers and transactions? Does anybody have an explanation as to why they can't be combined? Or even better, does anybody have a solution for combining them?

Thanks

Martin N Jensenwhen you do transactions you log the info

are your log files easily accessible ?|||Originally posted by Karolyn
when you do transactions you log the info
are your log files easily accessible ?

The transaction log files are stored with the datafiles as per the default installation settings. Do I need to assign special rights to some account in order to run the transaction?

Then again, wouldn't it only be the transaction log files on the machine on which I'm running the transaction from that are accessed?

MNJ|||check the Modification date of the log files on the different machines

and try to set the logging to off before the updating
to see if the command runs fastersql

servers "Client unable to establish connection"

We have several linked SQL servers running both SQL 7 (sp4) and SQL 2000 (sp3). Recently, when we run the following command from SQL 7 query analyzer to a SQL 2000 database, select * from remoteserver.database.dbo.tablename, we get this message:

[OLE/DB provider returned message: Client unable to establish connection]
[DBNMPNTW]ConnectionOpen (CreateFile()).

The databases are linked with specific remote login and password. The same command works the opposite direction, in other words from the SQL 2000 database to SQL 7 database. This was working last week but all of a sudden stopped. Of course, no one admits to changing anything on either of the servers. The SQL 2000 is on Windows 2003 server.

Thanks.

LccTom,

It is possible that some of the recent w2k3 patches disabled the file sharing and remote network connectivity on your w2k3 machine. Can you try if you can remote access to the w2k3 machine, such as file sharing? If you can't, you need to enable the file sharing because the namepipe provider[DBNMPNTW] depends on it.

Also, if you want TCP connection, you need to make firewall exception for the SQL tcp listening port, default to 1433 for a default instance.

|||We have no OS service packs installed but for some reason, it started working again Wednesday morning. Thanks for the reply and I'll make sure to look at the file sharing if it happens again. I reported it to my network people also.

servers - sql7 to sql2000

I am trying to run cross server queries from sql7 to sql2000.
I want to be able to use the NT login to authenticate accoss the link. I have created the linked server with "logins current security context" - however, when I run the sql2000 query from the sql7 server, l get the following message:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

it works fine sql2000 to sql2000

any ideas how I get this to work?What login did you specify in the Security Context?
If you are trying to use NT authen, make sure the 'NT AUTHORITY\ANONYMOUS' user exists in the db you are connecting to( not only the Master), run from your db to check:

select master..syslogins.name as login_name,
sysusers.name as user_name
from master..syslogins inner join sysusers
on master..syslogins.sid = sysusers.sid

Make sure the same user exists and was granted permissions at Win NT.|||You can also try connecting to other sql server using OPENROWSET

eg from BOL

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname

servers

sql 2000
I am completly lost.
While on dev01 I run:
select count(*)
from [DEV03].DBADB_local.dbo.Servers
I get:
Server: Msg 7411, Level 16, State 1, Line 1
Server 'DEV03' is not configured for DATA ACCESS.
DEV03 is not listed in EM under Security\Linked Servers on dev01 where I ran
this.
When I tried to add a linked server on dev01 to dev03 in EM, or using:
USE master
GO
EXEC sp_addlinkedserver
'DEV03',
N'SQL Server'
GO
I got the message:
Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
The server 'DEV03' already exists.
When I run 'sp_linkedservers' I get:
DEV01 SQLOLEDB SQL Server DEV01 NULL NULL NULL
DEV03 SQLOLEDB SQL Server DEV03 NULL NULL NULL
When I run 'sp_helpserver' I get:
DEV01 DEV01 rpc,sub,rpc out,use remote collation 0
NULL 0 0
DEV03 DEV03 rpc,sub,rpc out,use remote collation 3
NULL 0 0
JayOn dev01 I ran:
sp_serveroption 'dev03', 'data access', 'TRUE'
and the message on dev03 changed to:
Server: Msg 18452, Level 14, State 1, Line 8
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
"Jay" <nospan@.nospam.org> wrote in message
news:u185b%23u$HHA.2004@.TK2MSFTNGP06.phx.gbl...
> sql 2000
> I am completly lost.
> While on dev01 I run:
> select count(*)
> from [DEV03].DBADB_local.dbo.Servers
> I get:
> Server: Msg 7411, Level 16, State 1, Line 1
> Server 'DEV03' is not configured for DATA ACCESS.
> DEV03 is not listed in EM under Security\Linked Servers on dev01 where I
> ran this.
> When I tried to add a linked server on dev01 to dev03 in EM, or using:
> USE master
> GO
> EXEC sp_addlinkedserver
> 'DEV03',
> N'SQL Server'
> GO
> I got the message:
> Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line
> 79
> The server 'DEV03' already exists.
> When I run 'sp_linkedservers' I get:
> DEV01 SQLOLEDB SQL Server DEV01 NULL NULL NULL
> DEV03 SQLOLEDB SQL Server DEV03 NULL NULL NULL
> When I run 'sp_helpserver' I get:
> DEV01 DEV01 rpc,sub,rpc out,use remote collation 0
> NULL 0 0
> DEV03 DEV03 rpc,sub,rpc out,use remote collation 3
> NULL 0 0
>
> Jay
>|||Jay,
Did you map logins? Sp_addlinkedsrvlogin (or something like that). Also, the remote server might be
in Windows only mode, so mapping to an SQL login won't jive in that case.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:ejdKgQv$HHA.1188@.TK2MSFTNGP04.phx.gbl...
> On dev01 I ran:
> sp_serveroption 'dev03', 'data access', 'TRUE'
> and the message on dev03 changed to:
> Server: Msg 18452, Level 14, State 1, Line 8
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>
> "Jay" <nospan@.nospam.org> wrote in message news:u185b%23u$HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> sql 2000
>> I am completly lost.
>> While on dev01 I run:
>> select count(*)
>> from [DEV03].DBADB_local.dbo.Servers
>> I get:
>> Server: Msg 7411, Level 16, State 1, Line 1
>> Server 'DEV03' is not configured for DATA ACCESS.
>> DEV03 is not listed in EM under Security\Linked Servers on dev01 where I ran this.
>> When I tried to add a linked server on dev01 to dev03 in EM, or using:
>> USE master
>> GO
>> EXEC sp_addlinkedserver
>> 'DEV03',
>> N'SQL Server'
>> GO
>> I got the message:
>> Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
>> The server 'DEV03' already exists.
>> When I run 'sp_linkedservers' I get:
>> DEV01 SQLOLEDB SQL Server DEV01 NULL NULL NULL
>> DEV03 SQLOLEDB SQL Server DEV03 NULL NULL NULL
>> When I run 'sp_helpserver' I get:
>> DEV01 DEV01 rpc,sub,rpc out,use remote collation 0 NULL 0 0
>> DEV03 DEV03 rpc,sub,rpc out,use remote collation 3 NULL 0 0
>>
>> Jay
>|||SQL Server 2000
Well, Tibor was right about the login, however, that doesn't solve my issue.
To start, I'm deleting all linked servers on the dev and QA machines and
will recreate then as I need one at a time. However, there is one that
refuses to go.
QA01 has no linked servers, or replication defined in EM.
sp_helpdistpublisher & sp_helpsubscription both return no rows on QA01 (did
I get the commands right?)
DEV01
In EM I see QA01 listed in Linked Servers, when I tried to delete it I get
the message:
Cannot drop 'QA01' because it is used as a Subscriber in
replication.
sp_helpdistpublisher returns:
DEV01 distribution 1 sa 1 '\\DEV01\e$\Program Files\Microsoft SQL
Server\MSSQL\ReplData' 0 0
sp_helpsubscription returns nothing
On both dev01 & qa01 the output of sp_helpreplicationdboption is
db01 1 0 0 1 0
db02 2 0 0 1 0
db03 3 0 0 1 0
db04 4 0 0 1 0
db05 5 0 0 1 0
db06 6 0 0 1 0
db07 7 0 0 1 0
db08 8 0 0 1 0
db09 9 0 0 1 0
db10 10 0 0 1 0
db11 11 0 0 1 0
db12 12 0 0 1 0
No one knows anything about setting up replication between dev01 & qa01
either.
Jay|||OK, I cleared the link servers from dev01 & dev03 and ran the following
code. But I still can't do a simple select.
What I want to do is link the two servers such that only the DBADB_local
database can be accessed over the link. The final destination for these
links will be between servers that wouldn't otherwise be linked and will
include all production, QA and development servers.
-- Set a local database
-- On DEV03
USE DBADB_local
/*
-- Created a basic local Windows account on DEV03 for DBADB_local
-- Delete user (probably best if you comment this out, but I'm using it for
testing.
EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername ='DBADB_local'
EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername ='DBADB_local'
EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
EXEC sp_droplogin @.loginame = 'DBADB_local'
*/
-- Create the user account
EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb ='DBADB_local'
EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername ='DBADB_local'
EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername ='DBADB_local'
-- When accessing the 'Database Access' tab for the DBADB_local DB, I get an
error about 'Users Collection'
-- which I do not understand.
-- Reminder, still on DEV03
-- Probably comment
--EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
@.provider='SQLOLEDB', @.datasrc='DEV01'
--EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
tried both
EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
exec sp_helpserver
/* returns
DEV01 NULL data access,use remote collation 1 NULL 0 0
*/
-- Move to DEV01
-- Create basic local account in Windows.
-- Use same code to create the DBADB_local user on DEV01
EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
--EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
SELECT * FROM DEV01.DBADB_local.dbo.Servers
/* returns
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
*/|||Also:
sp_helplogins 'DBADB_local'
/* returns
DBADB_local 0x40AFA68175127440906B28234CCD413D DBADB_local us_english yes
no
DBADB_local DBADB_local db_datareader MemberOf
DBADB_local DBADB_local db_datawriter MemberOf
DBADB_local DBADB_local DBADB_local User
*/
on both machines.
"Jay" <nospan@.nospam.org> wrote in message
news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
> OK, I cleared the link servers from dev01 & dev03 and ran the following
> code. But I still can't do a simple select.
> What I want to do is link the two servers such that only the DBADB_local
> database can be accessed over the link. The final destination for these
> links will be between servers that wouldn't otherwise be linked and will
> include all production, QA and development servers.
>
> -- Set a local database
> -- On DEV03
> USE DBADB_local
> /*
> -- Created a basic local Windows account on DEV03 for DBADB_local
> -- Delete user (probably best if you comment this out, but I'm using it
> for testing.
> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
> EXEC sp_droplogin @.loginame = 'DBADB_local'
> */
> -- Create the user account
> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb => 'DBADB_local'
> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> -- When accessing the 'Database Access' tab for the DBADB_local DB, I get
> an error about 'Users Collection'
> -- which I do not understand.
> -- Reminder, still on DEV03
> -- Probably comment
> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
> @.provider='SQLOLEDB', @.datasrc='DEV01'
> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
> tried both
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin => 'DBADB_local'
> exec sp_helpserver
> /* returns
> DEV01 NULL data access,use remote collation 1 NULL 0 0
> */
> -- Move to DEV01
> -- Create basic local account in Windows.
> -- Use same code to create the DBADB_local user on DEV01
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>
> SELECT * FROM DEV01.DBADB_local.dbo.Servers
> /* returns
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> */
>|||Huge hurdle cleared. I replaced:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
with
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.useself = 'false',
@.locallogin = NULL, @.rmtuser = 'DBADB_local', @.rmtpassword = 'foobar'
and it works.
What else is gonna bite me?
"Jay" <nospan@.nospam.org> wrote in message
news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
> OK, I cleared the link servers from dev01 & dev03 and ran the following
> code. But I still can't do a simple select.
> What I want to do is link the two servers such that only the DBADB_local
> database can be accessed over the link. The final destination for these
> links will be between servers that wouldn't otherwise be linked and will
> include all production, QA and development servers.
>
> -- Set a local database
> -- On DEV03
> USE DBADB_local
> /*
> -- Created a basic local Windows account on DEV03 for DBADB_local
> -- Delete user (probably best if you comment this out, but I'm using it
> for testing.
> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
> EXEC sp_droplogin @.loginame = 'DBADB_local'
> */
> -- Create the user account
> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb => 'DBADB_local'
> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername => 'DBADB_local'
> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername => 'DBADB_local'
> -- When accessing the 'Database Access' tab for the DBADB_local DB, I get
> an error about 'Users Collection'
> -- which I do not understand.
> -- Reminder, still on DEV03
> -- Probably comment
> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
> @.provider='SQLOLEDB', @.datasrc='DEV01'
> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
> tried both
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin => 'DBADB_local'
> exec sp_helpserver
> /* returns
> DEV01 NULL data access,use remote collation 1 NULL 0 0
> */
> -- Move to DEV01
> -- Create basic local account in Windows.
> -- Use same code to create the DBADB_local user on DEV01
> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>
> SELECT * FROM DEV01.DBADB_local.dbo.Servers
> /* returns
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> */
>|||> What else is gonna bite me?
A lot. <g>
Check out the parameter list for sp_addlinkedsrvlogin. You specified NULL for @.locallogin. Now
*every* local login are mapped to the 'DBADB_local' on the DEV01 server. Is this what you want?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:OSCFv68$HHA.5312@.TK2MSFTNGP02.phx.gbl...
> Huge hurdle cleared. I replaced:
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
> with
> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.useself = 'false', @.locallogin = NULL, @.rmtuser
> = 'DBADB_local', @.rmtpassword = 'foobar'
> and it works.
> What else is gonna bite me?
> "Jay" <nospan@.nospam.org> wrote in message news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
>> OK, I cleared the link servers from dev01 & dev03 and ran the following code. But I still can't
>> do a simple select.
>> What I want to do is link the two servers such that only the DBADB_local database can be accessed
>> over the link. The final destination for these links will be between servers that wouldn't
>> otherwise be linked and will include all production, QA and development servers.
>>
>> -- Set a local database
>> -- On DEV03
>> USE DBADB_local
>> /*
>> -- Created a basic local Windows account on DEV03 for DBADB_local
>> -- Delete user (probably best if you comment this out, but I'm using it for testing.
>> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername = 'DBADB_local'
>> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername = 'DBADB_local'
>> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
>> EXEC sp_droplogin @.loginame = 'DBADB_local'
>> */
>> -- Create the user account
>> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb = 'DBADB_local'
>> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername = 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername = 'DBADB_local'
>> -- When accessing the 'Database Access' tab for the DBADB_local DB, I get an error about 'Users
>> Collection'
>> -- which I do not understand.
>> -- Reminder, still on DEV03
>> -- Probably comment
>> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
>> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='', @.provider='SQLOLEDB', @.datasrc='DEV01'
>> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' -- tried both
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin = 'DBADB_local'
>> exec sp_helpserver
>> /* returns
>> DEV01 NULL data access,use remote collation 1 NULL 0 0
>> */
>> -- Move to DEV01
>> -- Create basic local account in Windows.
>> -- Use same code to create the DBADB_local user on DEV01
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>>
>> SELECT * FROM DEV01.DBADB_local.dbo.Servers
>> /* returns
>> Server: Msg 18452, Level 14, State 1, Line 1
>> Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>> */
>|||Ya, I figured that out about 8PM. In this case it's not so bad as the login
is very restricted, but it's still wrong.
I also figured out why it kept failing, I was running under my login, not
the DBADB login. I need to figure out how to switch ID's.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6DE7CE2F-631B-404B-B124-DB44D0C94200@.microsoft.com...
>> What else is gonna bite me?
> A lot. <g>
> Check out the parameter list for sp_addlinkedsrvlogin. You specified NULL
> for @.locallogin. Now *every* local login are mapped to the 'DBADB_local'
> on the DEV01 server. Is this what you want?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:OSCFv68$HHA.5312@.TK2MSFTNGP02.phx.gbl...
>> Huge hurdle cleared. I replaced:
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin =>> 'DBADB_local'
>> with
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.useself = 'false',
>> @.locallogin = NULL, @.rmtuser = 'DBADB_local', @.rmtpassword = 'foobar'
>> and it works.
>> What else is gonna bite me?
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uYK%23be8$HHA.4164@.TK2MSFTNGP06.phx.gbl...
>> OK, I cleared the link servers from dev01 & dev03 and ran the following
>> code. But I still can't do a simple select.
>> What I want to do is link the two servers such that only the DBADB_local
>> database can be accessed over the link. The final destination for these
>> links will be between servers that wouldn't otherwise be linked and will
>> include all production, QA and development servers.
>>
>> -- Set a local database
>> -- On DEV03
>> USE DBADB_local
>> /*
>> -- Created a basic local Windows account on DEV03 for DBADB_local
>> -- Delete user (probably best if you comment this out, but I'm using it
>> for testing.
>> EXEC sp_droprolemember @.rolename = 'db_datareader', @.membername =>> 'DBADB_local'
>> EXEC sp_droprolemember @.rolename = 'db_datawriter', @.membername =>> 'DBADB_local'
>> EXEC sp_revokedbaccess @.name_in_db = 'DBADB_local'
>> EXEC sp_droplogin @.loginame = 'DBADB_local'
>> */
>> -- Create the user account
>> EXEC sp_addlogin @.loginame = 'DBADB_local', @.passwd = 'foobar', @.defdb =>> 'DBADB_local'
>> EXEC sp_adduser @.loginame = 'DBADB_local', @.name_in_db = 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datareader', @.membername =>> 'DBADB_local'
>> EXEC sp_addrolemember @.rolename = 'db_datawriter', @.membername =>> 'DBADB_local'
>> -- When accessing the 'Database Access' tab for the DBADB_local DB, I
>> get an error about 'Users Collection'
>> -- which I do not understand.
>> -- Reminder, still on DEV03
>> -- Probably comment
>> --EXEC sp_dropserver @.server='DEV01', @.droplogins='droplogins'
>> EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct ='',
>> @.provider='SQLOLEDB', @.datasrc='DEV01'
>> --EXEC sp_addlinkedserver @.server='DEV01', @.srvproduct=N'SQL Server' --
>> tried both
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'DEV01', @.locallogin =>> 'DBADB_local'
>> exec sp_helpserver
>> /* returns
>> DEV01 NULL data access,use remote collation 1 NULL 0 0
>> */
>> -- Move to DEV01
>> -- Create basic local account in Windows.
>> -- Use same code to create the DBADB_local user on DEV01
>> EXEC sp_serveroption 'DEV01', 'data access', 'TRUE'
>> --EXEC sp_serveroption 'DEV03', 'data access', 'TRUE'
>>
>> SELECT * FROM DEV01.DBADB_local.dbo.Servers
>> /* returns
>> Server: Msg 18452, Level 14, State 1, Line 1
>> Login failed for user '(null)'. Reason: Not associated with a trusted
>> SQL Server connection.
>> */
>>
>

servers

I added a remote server using sp_addlinkedserver commandn succesfully.

But when i run Select count(*) from Servername.dbname.tablename
it give me this error

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Servername' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Any reasons why?Did you add a login to the linked server too? If you did, go to EM and see if you can see the table list.
Originally posted by vmlal
I added a remote server using sp_addlinkedserver commandn succesfully.

But when i run Select count(*) from Servername.dbname.tablename
it give me this error

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Servername' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Any reasons why?|||You need a 4 part name...you're missing the owner, and youalso need to do sp_addlinkedsrvlogin

Try this

Select count(*) from Servername.dbname..tablename|||nope tried the sp_addlinkedsrvlogin still i get the same error and a new one:

Server: Msg 15015, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 40
The server 'ServerName' does not exist. Use sp_helpserver to show available servers.

and exec sp_helpserver shows the ServerName but the networkname field is NULL|||Can you post the code that added the linked server?

And what name did you give the link? You need to use that, not the actual server name, unless it's the same...|||/* Remove any previous references to the linked server */
EXEC sp_dropserver 'Server_I_want_to_add'

EXEC sp_addlinkedserver
@.server='Server_I_want_to_add', /* local SQL name given to the linked server */
@.srvproduct='Existing_Server_Name', /* not used */
@.provider='SQLOLEDB', /* OLE DB provider */
@.datasrc='Existing_Server_Name', /* analysis server name (machine name) */
@.catalog='Barney' /* default catalog/database */

EXEC sp_addlinkedsrvlogin
@.rmtsrvname='Server_I_want_to_add',
@.useself='true',
@.rmtuser ='**User_Name**',
@.rmtpassword = '***Password***'

/* Two additional procedures obtain information about the
tables and columns available in the cube. It is not
necessary to use them to complete the link.*/

/* This provides schema rowset information about
the dimensions available from the linked server */
EXEC sp_tables_ex
@.table_server='Server_I_want_to_add'

/* This provides schema rowset information about the
measures and levels of the dimensions
available from the linked server */
EXEC sp_columns_ex
@.table_server='Server_I_want_to_add',
@.table_name='Table_Name'|||Does your select look like
SELECT COUNT(*)
FROM Server_I_want_to_add.dbname..table

Notice the 2 dots, and the reference is to the linked server name, not the actual server name...

I always use the method in bol

like

EXEC sp_addlinkedserver
'LONDON Payroll',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO|||Yup i notice the 2 dots... last time it was a typo

thx for pointing that out

newayz i'll try ur code. thx|||Just curious...can you go in to Enterprise Manager and see the linked server?

It's in the Security folder for the server...|||yeah it works now, I see it in EM>security.

thx

servers

I used the stored procedures sp_addlinkedserver and sp_addlinkedserverlogin to add an Access db as a linked server. When I try and run SQL commands to this linked Access db I get the following error message:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]

Has anyone run into this before?This error message may indicate that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also check to make sure you have the correct parameters specified for the linked server configuration.

server-Oracle

Posted - 09/29/2004 : 11:46:27
Hi folks,
I have a linked server pointing to oracle server 8i on my sql server 2000 box.
Initially i was able to run queries on sql server using the four part naming
convention. As of today i started to get this error:
Server: Msg 7356, Level 16, State 1Line 239
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'CALL_COUNT' (compile-time
ordinal 4) of object '"SUMMARY_OWNER"."IVRU_CALL_TRANSACTION_SUMMARY "' was
reported to have a DBTYPE of 130 at compile time and 5 at run time].
What would be causing this error?
Any suggesstions, thank you
Ramdas
Hi Ramdas,
It seems that you are facing the issue described in the following Microsoft
Knowledge Base Article No 251238.
Please visit the following link and see if it helps.
http://support.microsoft.com/?id=251238
Thanks
Soma Sekhara Reddy

server, stored procedure and Views (Heterogeneous Error

I am using SQL passthough and simply running an "exec mysproc".
Then I simply run a "Select * from myView", which is a "Select a,b,c from
xxx.xxx.dbo.table"
This all runs vis MS Query, but if I turn off ANSI NULLS and WARNINGS in the
MS Query options, I get the same error.
"Uri Dimant" wrote:

> Ray
> Can you show us how you call the statemnet?
>
> "Ray" <rayc@.rsc.com> wrote in message
> news:FEEE24DA-571B-4C46-887B-33D4F772553E@.microsoft.com...
>
>Ray
You said that it failed from within a trigger. Try create a trigger
withANSI NULLS and WARNINGS set ON
What is your statement to fire a trigger?
"Ray" <rayc@.rsc.com> wrote in message
news:3CA11337-A751-449C-815A-35AEF75416C8@.microsoft.com...
>I am using SQL passthough and simply running an "exec mysproc".
> Then I simply run a "Select * from myView", which is a "Select a,b,c from
> xxx.xxx.dbo.table"
> This all runs vis MS Query, but if I turn off ANSI NULLS and WARNINGS in
> the
> MS Query options, I get the same error.
> "Uri Dimant" wrote:
>

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specified
as a login and database user with select priviledges on all tables and views.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
Thanks,
CGW
The permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin in
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:

> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run from
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an execution
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed are
> domain or SQL admins. Those who fail are members of a domain group, specified
> as a login and database user with select priviledges on all tables and views.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specified
as a login and database user with select priviledges on all tables and views.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
--
Thanks,
CGWThe permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin in
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:
> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run from
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an execution
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed are
> domain or SQL admins. Those who fail are members of a domain group, specified
> as a login and database user with select priviledges on all tables and views.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW|||Good guess, but actually, that is how they are set up. What else do you think
might be the problem?
--
Thanks,
CGW
"John Scragg" wrote:
> The permissions across the link are based on the logins associated with the
> link. You may have set them up with user accounts on the remote server, but
> they credentials passed to that remote server are those configured for the
> link. My guess is that you need to set up for users to make connections
> "using the login's current security context". In EM you can check under the
> Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> QA to see what you get.
> HTH,
> John Scragg
> "CGW" wrote:
> > We recently moved a database from one SQL server to another and replicated
> > logins, users, permissions, etc. The problem occurs when joins are run from
> > the original server, referencing tables on the new server, using the
> > four-part name. Using the same query, some users time out with an execution
> > plan that looks like it ignores the indexes on the remote server. Other's
> > execute in seconds.
> >
> > I've been over permissions with a fine-tooth comb. The users who succeed are
> > domain or SQL admins. Those who fail are members of a domain group, specified
> > as a login and database user with select priviledges on all tables and views.
> >
> > Ownership is sa, just as it is for databases on the original server. Can
> > anyone suggest what else I might look for? Thanks!
> > --
> > Thanks,
> >
> > CGW|||Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
be the case that the chain is broken for regular users.
Using Ownership Chaining (http://tinyurl.com/8q5dq)
Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
Best of luck,
John
"CGW" wrote:
> Good guess, but actually, that is how they are set up. What else do you think
> might be the problem?
> --
> Thanks,
> CGW
>
> "John Scragg" wrote:
> > The permissions across the link are based on the logins associated with the
> > link. You may have set them up with user accounts on the remote server, but
> > they credentials passed to that remote server are those configured for the
> > link. My guess is that you need to set up for users to make connections
> > "using the login's current security context". In EM you can check under the
> > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > QA to see what you get.
> >
> > HTH,
> >
> > John Scragg
> >
> > "CGW" wrote:
> >
> > > We recently moved a database from one SQL server to another and replicated
> > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > the original server, referencing tables on the new server, using the
> > > four-part name. Using the same query, some users time out with an execution
> > > plan that looks like it ignores the indexes on the remote server. Other's
> > > execute in seconds.
> > >
> > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > as a login and database user with select priviledges on all tables and views.
> > >
> > > Ownership is sa, just as it is for databases on the original server. Can
> > > anyone suggest what else I might look for? Thanks!
> > > --
> > > Thanks,
> > >
> > > CGW|||Thanks, we're still researching. When we link with a specific security
context, we can make the problem go away, which gave us some clues.
--
Thanks,
CGW
"John Scragg" wrote:
> Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
> be the case that the chain is broken for regular users.
> Using Ownership Chaining (http://tinyurl.com/8q5dq)
> Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
> Best of luck,
> John
> "CGW" wrote:
> > Good guess, but actually, that is how they are set up. What else do you think
> > might be the problem?
> > --
> > Thanks,
> >
> > CGW
> >
> >
> > "John Scragg" wrote:
> >
> > > The permissions across the link are based on the logins associated with the
> > > link. You may have set them up with user accounts on the remote server, but
> > > they credentials passed to that remote server are those configured for the
> > > link. My guess is that you need to set up for users to make connections
> > > "using the login's current security context". In EM you can check under the
> > > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > > QA to see what you get.
> > >
> > > HTH,
> > >
> > > John Scragg
> > >
> > > "CGW" wrote:
> > >
> > > > We recently moved a database from one SQL server to another and replicated
> > > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > > the original server, referencing tables on the new server, using the
> > > > four-part name. Using the same query, some users time out with an execution
> > > > plan that looks like it ignores the indexes on the remote server. Other's
> > > > execute in seconds.
> > > >
> > > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > > as a login and database user with select priviledges on all tables and views.
> > > >
> > > > Ownership is sa, just as it is for databases on the original server. Can
> > > > anyone suggest what else I might look for? Thanks!
> > > > --
> > > > Thanks,
> > > >
> > > > CGW

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specifie
d
as a login and database user with select priviledges on all tables and views
.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
--
Thanks,
CGWThe permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin i
n
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:

> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run fro
m
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an executio
n
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed a
re
> domain or SQL admins. Those who fail are members of a domain group, specif
ied
> as a login and database user with select priviledges on all tables and vie
ws.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW

server Update Error

Hi,
I have a linked server that is having problems when I run an Update query
against it.
I have a stored procedure in ServerA that updates records on my linked
server, ServerB.
This stored procedure accepts 2 pieces of criteria and applies the Update to
seven tables in the procedure.
A representative line would be:
UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I pass in the two parameters and lets say for @.ImportID I pass in the value
410, when the Procedure is run, it will set the ImportID field to a
different number.
The different number almost always seems to be a number that was ONCE in
this field for a since deleted record.
This behavior is identical in the 7 different tables in this stored
procedure. It happens when I use a .NET Command object (OLEDBCommand in this
case) as well as
when I run the procedure through the Query Analyzer. The only time it does
not happen is when I run a local version of the stored procedure on the
linked server (ServerB) itself:
UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
@.BillingInvoice
I have restarted my servers, shrunk the database, inspected and re-inspected
my Command objects parameters and can not get this odd behavior to go away.
I should note that no errors are returned and all my Select and Delete
queries seem to work fine. But it seems like old values are being restored.
Any ideas?
What operating system are you running on? What is the exact error message
that you are getting?
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?
>
>
|||Hi,
Both the Linked Server and the server that calls the linked server are XP
Pro with SQL2k.
As for an error message, there is none. The proc completes without error,
even in QA but when you inspect the results in the tables, you see that the
incorrect values were applied.
"Pat Brisbine" <PatBrisbine@.discussions.microsoft.com> wrote in message
news:0C3AE65A-C357-4CB4-820E-C38A7311CD8F@.microsoft.com...[vbcol=seagreen]
> What operating system are you running on? What is the exact error message
> that you are getting?
> "Walter" wrote:
query[vbcol=seagreen]
Update to[vbcol=seagreen]
value[vbcol=seagreen]
this[vbcol=seagreen]
does[vbcol=seagreen]
BILLG_INVC_ID=[vbcol=seagreen]
re-inspected[vbcol=seagreen]
away.[vbcol=seagreen]
restored.[vbcol=seagreen]
|||> The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
So even when you run the stored proc through query analyzer on ServerA you
do not get the expected results?
It is very strage that you only get the expected results on ServerB. Have
you tried debugging in query analyzer or putting multiple print statements in
to track the values through the life of the procedure?
If you could post the entire stored proc it might help.
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?
>
>
|||Walter,
Could this article help you?
http://support.microsoft.com/default...b;en-us;293328
BTW, It says "This problem was first corrected in SQL Server 2000 Service Pack 1"
HTH,
Igor
"Walter" <never@.mind.com> wrote in message news:<OGDykJWhEHA.704@.TK2MSFTNGP12.phx.gbl>...
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?

server Update Error

Hi,
I have a linked server that is having problems when I run an Update query
against it.
I have a stored procedure in ServerA that updates records on my linked
server, ServerB.
This stored procedure accepts 2 pieces of criteria and applies the Update to
seven tables in the procedure.
A representative line would be:
UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I pass in the two parameters and lets say for @.ImportID I pass in the value
410, when the Procedure is run, it will set the ImportID field to a
different number.
The different number almost always seems to be a number that was ONCE in
this field for a since deleted record.
This behavior is identical in the 7 different tables in this stored
procedure. It happens when I use a .NET Command object (OLEDBCommand in this
case) as well as
when I run the procedure through the Query Analyzer. The only time it does
not happen is when I run a local version of the stored procedure on the
linked server (ServerB) itself:
UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I have restarted my servers, shrunk the database, inspected and re-inspected
my Command objects parameters and can not get this odd behavior to go away.
I should note that no errors are returned and all my Select and Delete
queries seem to work fine. But it seems like old values are being restored.
Any ideas?Hi,
Both the Linked Server and the server that calls the linked server are XP
Pro with SQL2k.
As for an error message, there is none. The proc completes without error,
even in QA but when you inspect the results in the tables, you see that the
incorrect values were applied.
"Pat Brisbine" <PatBrisbine@.discussions.microsoft.com> wrote in message
news:0C3AE65A-C357-4CB4-820E-C38A7311CD8F@.microsoft.com...
> What operating system are you running on? What is the exact error message
> that you are getting?
> "Walter" wrote:
> > Hi,
> > I have a linked server that is having problems when I run an Update
query
> > against it.
> >
> > I have a stored procedure in ServerA that updates records on my linked
> > server, ServerB.
> > This stored procedure accepts 2 pieces of criteria and applies the
Update to
> > seven tables in the procedure.
> >
> > A representative line would be:
> >
> > UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> > ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> >
> > I pass in the two parameters and lets say for @.ImportID I pass in the
value
> > 410, when the Procedure is run, it will set the ImportID field to a
> > different number.
> > The different number almost always seems to be a number that was ONCE in
> > this field for a since deleted record.
> >
> > This behavior is identical in the 7 different tables in this stored
> > procedure. It happens when I use a .NET Command object (OLEDBCommand in
this
> > case) as well as
> > when I run the procedure through the Query Analyzer. The only time it
does
> > not happen is when I run a local version of the stored procedure on the
> > linked server (ServerB) itself:
> >
> > UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND
BILLG_INVC_ID=> > @.BillingInvoice
> >
> > I have restarted my servers, shrunk the database, inspected and
re-inspected
> > my Command objects parameters and can not get this odd behavior to go
away.
> > I should note that no errors are returned and all my Select and Delete
> > queries seem to work fine. But it seems like old values are being
restored.
> >
> > Any ideas?
> >
> >
> >|||> The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
So even when you run the stored proc through query analyzer on ServerA you
do not get the expected results?
It is very strage that you only get the expected results on ServerB. Have
you tried debugging in query analyzer or putting multiple print statements in
to track the values through the life of the procedure?
If you could post the entire stored proc it might help.
"Walter" wrote:
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?
>
>|||Walter,
Could this article help you?
http://support.microsoft.com/default.aspx?scid=kb;en-us;293328
BTW, It says "This problem was first corrected in SQL Server 2000 Service Pack 1"
HTH,
Igor
"Walter" <never@.mind.com> wrote in message news:<OGDykJWhEHA.704@.TK2MSFTNGP12.phx.gbl>...
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?

server update error

Does Anyone know what this means?

I run the query in the attached file on multiple servers with success but some of them refuse to cooperate. I've tried recreating the linked server on the problem servers but this doesn't help. All I want to do is check db file size and free space and get it to work. Why does it have to be so damn complicated?

Any ideas welcome - including an entirely different way of doing this.

The script and table definitions in question are attached

Could not open table '"Helpdesk_New"."dbo"."LogStats"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...The script fails at this line:

Update RemoteServer.RemoteDB.dbo.LogStats
Set Servername = @.Servername

Graham

server Update Error

Hi,
I have a linked server that is having problems when I run an Update query
against it.
I have a stored procedure in ServerA that updates records on my linked
server, ServerB.
This stored procedure accepts 2 pieces of criteria and applies the Update to
seven tables in the procedure.
A representative line would be:
UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I pass in the two parameters and lets say for @.ImportID I pass in the value
410, when the Procedure is run, it will set the ImportID field to a
different number.
The different number almost always seems to be a number that was ONCE in
this field for a since deleted record.
This behavior is identical in the 7 different tables in this stored
procedure. It happens when I use a .NET Command object (OLEDBCommand in this
case) as well as
when I run the procedure through the Query Analyzer. The only time it does
not happen is when I run a local version of the stored procedure on the
linked server (ServerB) itself:
UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
@.BillingInvoice
I have restarted my servers, shrunk the database, inspected and re-inspected
my Command objects parameters and can not get this odd behavior to go away.
I should note that no errors are returned and all my Select and Delete
queries seem to work fine. But it seems like old values are being restored.
Any ideas?What operating system are you running on? What is the exact error message
that you are getting?
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update
to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the valu
e
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in th
is
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_I
D=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspect
ed
> my Command objects parameters and can not get this odd behavior to go away
.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored
.
> Any ideas?
>
>|||Hi,
Both the Linked Server and the server that calls the linked server are XP
Pro with SQL2k.
As for an error message, there is none. The proc completes without error,
even in QA but when you inspect the results in the tables, you see that the
incorrect values were applied.
"Pat Brisbine" <PatBrisbine@.discussions.microsoft.com> wrote in message
news:0C3AE65A-C357-4CB4-820E-C38A7311CD8F@.microsoft.com...[vbcol=seagreen]
> What operating system are you running on? What is the exact error message
> that you are getting?
> "Walter" wrote:
>
query[vbcol=seagreen]
Update to[vbcol=seagreen]
value[vbcol=seagreen]
this[vbcol=seagreen]
does[vbcol=seagreen]
BILLG_INVC_ID=[vbcol=seagreen]
re-inspected[vbcol=seagreen]
away.[vbcol=seagreen]
restored.[vbcol=seagreen]|||> The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
So even when you run the stored proc through query analyzer on ServerA you
do not get the expected results?
It is very strage that you only get the expected results on ServerB. Have
you tried debugging in query analyzer or putting multiple print statements i
n
to track the values through the life of the procedure?
If you could post the entire stored proc it might help.
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update
to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the valu
e
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in th
is
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_I
D=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspect
ed
> my Command objects parameters and can not get this odd behavior to go away
.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored
.
> Any ideas?
>
>|||Walter,
Could this article help you?
http://support.microsoft.com/defaul...kb;en-us;293328
BTW, It says "This problem was first corrected in SQL Server 2000 Service Pa
ck 1"
HTH,
Igor
"Walter" <never@.mind.com> wrote in message news:<OGDykJWhEHA.704@.TK2MSFTNGP12.phx.gbl>...[vb
col=seagreen]
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update
to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the valu
e
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in th
is
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_I
D=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspect
ed
> my Command objects parameters and can not get this odd behavior to go away
.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored
.
> Any ideas?[/vbcol]

server to VFPOLEDB working in 2000 but not in 2005?

I have run the following command and opened a working linked server in SQL Server 2000 but it fails for 2005.

sp_addlinkedserver 'AZ','','VFPOLEDB','c:\comp01\company.dbc'

To test, I run: select * from openquery(AZ, 'select * from contact') and I ge

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "VFPOLEDB" for linked server "AZ" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "VFPOLEDB" for linked server "AZ".

The specs on the working machine: WinXP Pro, SQL Server 2000 Developer's Edition, SP4, latest VFPOLEDB from MS site.

The specs on the non-working machine: Windows Server 2003 (std), SQL Server 2005 Developer Edition SP4, latest VFPOLEDB from MS site.

Other things I have done and/or noticed:

- in the install notes, it says something about security through component services, MSDTC, having to check everything. Did that.

- if I don't use a SQL command from query analyzer and try to add the linked server by right clicking on Server Objects\Linked Servers, it demands a Product Name. Null (and ' ') is not a valid entry.

Looking for help on this.

Doug

Think I'm closer. Turns out that MDAC has not recognized VFPOLEDB despite having downloading and installed it on Windows Server 2003. I have now tried regsvr32 and it says it's registered but still not listed in MDAC (used the component checker). Thoughts?
|||Sorry, I don't have any suggestions for you. I just thought I'd sympathize because I am running into the same problem with MSDAORA instead of VFPOLEDB. I'd be interested to hear what progress you end up making.