Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

-

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

Did you check permissions of remoteUser on the remote server instance? You can do this by just using the remote login to connect to the server using ISQLW and see what tables you can access or check your permissions.|||Yes, remoteUser is able to access RemoteServerName through Query Analizer.|||I didn't mean just accessing the server. That works since you didn't get any errors. What tables can you access? What happens if you try to select from a known table?|||

You have to use the Servername.Database.UserObject.Table on the query..

example,

Select * from [RemoteServer\MSSS2000].NorthWind.dbo.Orders

Linked server from SQL2005 to SQL2000

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

--

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

What do you mean by "I am not able to see the tables under it"... have you tried:

SELECT * FROM [targetServer].[master].dbo.SysObjects to see if you are actually connected (and can query) ?

|||No, this does not work, I am getting no object found|||Can you post your query to access the linked server, as well as the error message you got? Maybe you got right object name, but incorrect schema name?|||

Hey u can't see the link server objects in 2005 as u used to see in sql2000.

But u can query an object as u wish.

if u know the name of any table.

Try to query the link server like

Select * from LinkServerName...tablename.

You can also use openquery statement.

Good Luck

|||Do you mean you can't find Linked Server objects in Management Studio? Actually?Linked Server objects are moved to the "Server Objects" catalog in Management Studio, no longer under the Security branch as in Enterprise Manager

You can also get information about linker servers usingsp_linkedservers system procedure.

Linked Server - sp_tables_ex

Hi,
1. I have a linked server to an Access 2003 mdb database. It works, as
indicated by select statements to its tables via QA or my stored procedures.
2. However, when I click on EM, Security, Linked Servers, Tables (or Views),
I get message:
Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
This also produces SqlDump Fatal Error log which indicates apparent running
of sp_tables_ex, which displays linked server table info.
* BEGIN STACK DUMP:
* 02/03/07 11:27:03 spid 51
*
* Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 144 bytes -
* exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null, '''TABLE'''
<<-- NOTE THIS!
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
...................................... .......
3. NOTE: If I create an empty mdb and import all objects from the current
live mdb into it, and then use this New mdb as the linked server, then I can
see the tables and views in EM.
4. Why do I care about this?
Because the connection to the linked server breaks sporadically ever few
days with the SAME SQL fatal error dump as above, apparently indicating the
the 'sp_tables_ex' procedure is being run from somewhere. It is not being
issued manually by anyone!!
The only way to restore the linked server connection is to stop and restart
sqlserver, which is very inconvenient to users of other databases.
5. I need to find a way to keep the linked server connection from breaking
due to the sporadic, but consistent fatal SqlDump error log above.
Thanks for any help.
Alan
PS.
Where might the sp_tables_ex be running from, and is there a way to kill it?
Does it get executed by maintenace plan integrity check?
Thanks.
Alan
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23q6Tgj8RHHA.412@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a linked server to an Access 2003 mdb database. It works, as
> indicated by select statements to its tables via QA or my stored
> procedures.
> 2. However, when I click on EM, Security, Linked Servers, Tables (or
> Views), I get message:
> Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
> This also produces SqlDump Fatal Error log which indicates apparent
> running of sp_tables_ex, which displays linked server table info.
> * BEGIN STACK DUMP:
> * 02/03/07 11:27:03 spid 51
> *
> * Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000000
> * Input Buffer 144 bytes -
> * exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null,
> '''TABLE''' <<-- NOTE THIS!
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> ...................................... ......
> 3. NOTE: If I create an empty mdb and import all objects from the current
> live mdb into it, and then use this New mdb as the linked server, then I
> can see the tables and views in EM.
> 4. Why do I care about this?
> Because the connection to the linked server breaks sporadically ever few
> days with the SAME SQL fatal error dump as above, apparently indicating
> the the 'sp_tables_ex' procedure is being run from somewhere. It is not
> being issued manually by anyone!!
> The only way to restore the linked server connection is to stop and
> restart sqlserver, which is very inconvenient to users of other databases.
> 5. I need to find a way to keep the linked server connection from breaking
> due to the sporadic, but consistent fatal SqlDump error log above.
> Thanks for any help.
> Alan
>
>
>
>
|||This is a bug in SQL Server. You should report it to Microsoft so they
can fix it. SQL Server should never under any conditions generate a
c0000005 EXCEPTION_ACCESS_VIOLATION error.
-Dave
Alan Z. Scharf wrote:
> Hi,
> 1. I have a linked server to an Access 2003 mdb database. It works, as
> indicated by select statements to its tables via QA or my stored procedures.
> 2. However, when I click on EM, Security, Linked Servers, Tables (or Views),
> I get message:
> Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
> This also produces SqlDump Fatal Error log which indicates apparent running
> of sp_tables_ex, which displays linked server table info.
> * BEGIN STACK DUMP:
> * 02/03/07 11:27:03 spid 51
> *
> * Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000000
> * Input Buffer 144 bytes -
> * exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null, '''TABLE'''
> <<-- NOTE THIS!
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> ...................................... ......
> 3. NOTE: If I create an empty mdb and import all objects from the current
> live mdb into it, and then use this New mdb as the linked server, then I can
> see the tables and views in EM.
> 4. Why do I care about this?
> Because the connection to the linked server breaks sporadically ever few
> days with the SAME SQL fatal error dump as above, apparently indicating the
> the 'sp_tables_ex' procedure is being run from somewhere. It is not being
> issued manually by anyone!!
> The only way to restore the linked server connection is to stop and restart
> sqlserver, which is very inconvenient to users of other databases.
> 5. I need to find a way to keep the linked server connection from breaking
> due to the sporadic, but consistent fatal SqlDump error log above.
> Thanks for any help.
> Alan
>
>
>
>
-Dave Markle
http://www.markleconsulting.com/blog
|||Dave,
Thanks.
Alan
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:%23K1ymbGSHHA.488@.TK2MSFTNGP06.phx.gbl...
> This is a bug in SQL Server. You should report it to Microsoft so they
> can fix it. SQL Server should never under any conditions generate a
> c0000005 EXCEPTION_ACCESS_VIOLATION error.
> -Dave
> Alan Z. Scharf wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

Linked Server - sp_tables_ex

Hi,
1. I have a linked server to an Access 2003 mdb database. It works, as
indicated by select statements to its tables via QA or my stored procedures.
2. However, when I click on EM, Security, Linked Servers, Tables (or Views),
I get message:
Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
This also produces SqlDump Fatal Error log which indicates apparent running
of sp_tables_ex, which displays linked server table info.
* BEGIN STACK DUMP:
* 02/03/07 11:27:03 spid 51
*
* Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 144 bytes -
* exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null, '''TABLE'''
<<-- NOTE THIS!
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
............................................
3. NOTE: If I create an empty mdb and import all objects from the current
live mdb into it, and then use this New mdb as the linked server, then I can
see the tables and views in EM.
4. Why do I care about this?
Because the connection to the linked server breaks sporadically ever few
days with the SAME SQL fatal error dump as above, apparently indicating the
the 'sp_tables_ex' procedure is being run from somewhere. It is not being
issued manually by anyone!!
The only way to restore the linked server connection is to stop and restart
sqlserver, which is very inconvenient to users of other databases.
5. I need to find a way to keep the linked server connection from breaking
due to the sporadic, but consistent fatal SqlDump error log above.
Thanks for any help.
AlanPS.
Where might the sp_tables_ex be running from, and is there a way to kill it?
Does it get executed by maintenace plan integrity check?
Thanks.
Alan
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23q6Tgj8RHHA.412@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a linked server to an Access 2003 mdb database. It works, as
> indicated by select statements to its tables via QA or my stored
> procedures.
> 2. However, when I click on EM, Security, Linked Servers, Tables (or
> Views), I get message:
> Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
> This also produces SqlDump Fatal Error log which indicates apparent
> running of sp_tables_ex, which displays linked server table info.
> * BEGIN STACK DUMP:
> * 02/03/07 11:27:03 spid 51
> *
> * Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000000
> * Input Buffer 144 bytes -
> * exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null,
> '''TABLE''' <<-- NOTE THIS!
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> ............................................
> 3. NOTE: If I create an empty mdb and import all objects from the current
> live mdb into it, and then use this New mdb as the linked server, then I
> can see the tables and views in EM.
> 4. Why do I care about this?
> Because the connection to the linked server breaks sporadically ever few
> days with the SAME SQL fatal error dump as above, apparently indicating
> the the 'sp_tables_ex' procedure is being run from somewhere. It is not
> being issued manually by anyone!!
> The only way to restore the linked server connection is to stop and
> restart sqlserver, which is very inconvenient to users of other databases.
> 5. I need to find a way to keep the linked server connection from breaking
> due to the sporadic, but consistent fatal SqlDump error log above.
> Thanks for any help.
> Alan
>
>
>
>|||This is a bug in SQL Server. You should report it to Microsoft so they
can fix it. SQL Server should never under any conditions generate a
c0000005 EXCEPTION_ACCESS_VIOLATION error.
-Dave
Alan Z. Scharf wrote:
> Hi,
> 1. I have a linked server to an Access 2003 mdb database. It works, as
> indicated by select statements to its tables via QA or my stored procedure
s.
> 2. However, when I click on EM, Security, Linked Servers, Tables (or Views
),
> I get message:
> Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
> This also produces SqlDump Fatal Error log which indicates apparent runnin
g
> of sp_tables_ex, which displays linked server table info.
> * BEGIN STACK DUMP:
> * 02/03/07 11:27:03 spid 51
> *
> * Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000000
> * Input Buffer 144 bytes -
> * exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null, '''TABLE''
'
> <<-- NOTE THIS!
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> ............................................
> 3. NOTE: If I create an empty mdb and import all objects from the current
> live mdb into it, and then use this New mdb as the linked server, then I c
an
> see the tables and views in EM.
> 4. Why do I care about this?
> Because the connection to the linked server breaks sporadically ever few
> days with the SAME SQL fatal error dump as above, apparently indicating th
e
> the 'sp_tables_ex' procedure is being run from somewhere. It is not being
> issued manually by anyone!!
> The only way to restore the linked server connection is to stop and restar
t
> sqlserver, which is very inconvenient to users of other databases.
> 5. I need to find a way to keep the linked server connection from breaking
> due to the sporadic, but consistent fatal SqlDump error log above.
> Thanks for any help.
> Alan
>
>
>
>
-Dave Markle
http://www.markleconsulting.com/blog|||Dave,
Thanks.
Alan
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:%23K1ymbGSHHA.488@.TK2MSFTNGP06.phx.gbl...
> This is a bug in SQL Server. You should report it to Microsoft so they
> can fix it. SQL Server should never under any conditions generate a
> c0000005 EXCEPTION_ACCESS_VIOLATION error.
> -Dave
> Alan Z. Scharf wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

Linked Server - sp_tables_ex

Hi,
1. I have a linked server to an Access 2003 mdb database. It works, as
indicated by select statements to its tables via QA or my stored procedures.
2. However, when I click on EM, Security, Linked Servers, Tables (or Views),
I get message:
Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
This also produces SqlDump Fatal Error log which indicates apparent running
of sp_tables_ex, which displays linked server table info.
* BEGIN STACK DUMP:
* 02/03/07 11:27:03 spid 51
*
* Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 144 bytes -
* exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null, '''TABLE'''
<<-- NOTE THIS!
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
............................................
3. NOTE: If I create an empty mdb and import all objects from the current
live mdb into it, and then use this New mdb as the linked server, then I can
see the tables and views in EM.
4. Why do I care about this?
Because the connection to the linked server breaks sporadically ever few
days with the SAME SQL fatal error dump as above, apparently indicating the
the 'sp_tables_ex' procedure is being run from somewhere. It is not being
issued manually by anyone!!
The only way to restore the linked server connection is to stop and restart
sqlserver, which is very inconvenient to users of other databases.
5. I need to find a way to keep the linked server connection from breaking
due to the sporadic, but consistent fatal SqlDump error log above.
Thanks for any help.
AlanPS.
Where might the sp_tables_ex be running from, and is there a way to kill it?
Does it get executed by maintenace plan integrity check?
Thanks.
Alan
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23q6Tgj8RHHA.412@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a linked server to an Access 2003 mdb database. It works, as
> indicated by select statements to its tables via QA or my stored
> procedures.
> 2. However, when I click on EM, Security, Linked Servers, Tables (or
> Views), I get message:
> Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
> This also produces SqlDump Fatal Error log which indicates apparent
> running of sp_tables_ex, which displays linked server table info.
> * BEGIN STACK DUMP:
> * 02/03/07 11:27:03 spid 51
> *
> * Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000000
> * Input Buffer 144 bytes -
> * exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null,
> '''TABLE''' <<-- NOTE THIS!
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> ............................................
> 3. NOTE: If I create an empty mdb and import all objects from the current
> live mdb into it, and then use this New mdb as the linked server, then I
> can see the tables and views in EM.
> 4. Why do I care about this?
> Because the connection to the linked server breaks sporadically ever few
> days with the SAME SQL fatal error dump as above, apparently indicating
> the the 'sp_tables_ex' procedure is being run from somewhere. It is not
> being issued manually by anyone!!
> The only way to restore the linked server connection is to stop and
> restart sqlserver, which is very inconvenient to users of other databases.
> 5. I need to find a way to keep the linked server connection from breaking
> due to the sporadic, but consistent fatal SqlDump error log above.
> Thanks for any help.
> Alan
>
>
>
>|||This is a bug in SQL Server. You should report it to Microsoft so they
can fix it. SQL Server should never under any conditions generate a
c0000005 EXCEPTION_ACCESS_VIOLATION error.
-Dave
Alan Z. Scharf wrote:
> Hi,
> 1. I have a linked server to an Access 2003 mdb database. It works, as
> indicated by select statements to its tables via QA or my stored procedures.
> 2. However, when I click on EM, Security, Linked Servers, Tables (or Views),
> I get message:
> Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the precess
> This also produces SqlDump Fatal Error log which indicates apparent running
> of sp_tables_ex, which displays linked server table info.
> * BEGIN STACK DUMP:
> * 02/03/07 11:27:03 spid 51
> *
> * Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000000
> * Input Buffer 144 bytes -
> * exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null, '''TABLE'''
> <<-- NOTE THIS!
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> ............................................
> 3. NOTE: If I create an empty mdb and import all objects from the current
> live mdb into it, and then use this New mdb as the linked server, then I can
> see the tables and views in EM.
> 4. Why do I care about this?
> Because the connection to the linked server breaks sporadically ever few
> days with the SAME SQL fatal error dump as above, apparently indicating the
> the 'sp_tables_ex' procedure is being run from somewhere. It is not being
> issued manually by anyone!!
> The only way to restore the linked server connection is to stop and restart
> sqlserver, which is very inconvenient to users of other databases.
> 5. I need to find a way to keep the linked server connection from breaking
> due to the sporadic, but consistent fatal SqlDump error log above.
> Thanks for any help.
> Alan
>
>
>
>
-Dave Markle
http://www.markleconsulting.com/blog|||Dave,
Thanks.
Alan
"Dave Markle" <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com> wrote in message
news:%23K1ymbGSHHA.488@.TK2MSFTNGP06.phx.gbl...
> This is a bug in SQL Server. You should report it to Microsoft so they
> can fix it. SQL Server should never under any conditions generate a
> c0000005 EXCEPTION_ACCESS_VIOLATION error.
> -Dave
> Alan Z. Scharf wrote:
>> Hi,
>> 1. I have a linked server to an Access 2003 mdb database. It works, as
>> indicated by select statements to its tables via QA or my stored
>> procedures.
>> 2. However, when I click on EM, Security, Linked Servers, Tables (or
>> Views), I get message:
>> Error 0:SqlDumpExceptionHandler:Process 51 generated fatak exception
>> c0000005 EXCEPTION_ACCESS_VIOLATION. Sql Server is terminating the
>> precess
>> This also produces SqlDump Fatal Error log which indicates apparent
>> running of sp_tables_ex, which displays linked server table info.
>> * BEGIN STACK DUMP:
>> * 02/03/07 11:27:03 spid 51
>> *
>> * Exception Address = 06371F31 (Ordinal109 + 0000479B Line 0+00000000)
>> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
>> * Access Violation occurred reading address 00000000
>> * Input Buffer 144 bytes -
>> * exec master..sp_tables_ex N'WINFUNDSMDB' , null, null, null,
>> '''TABLE''' <<-- NOTE THIS!
>> *
>> * MODULE BASE END SIZE
>> * sqlservr 00400000 00B2CFFF 0072d000
>> ............................................
>> 3. NOTE: If I create an empty mdb and import all objects from the
>> current live mdb into it, and then use this New mdb as the linked server,
>> then I can see the tables and views in EM.
>> 4. Why do I care about this?
>> Because the connection to the linked server breaks sporadically ever few
>> days with the SAME SQL fatal error dump as above, apparently indicating
>> the the 'sp_tables_ex' procedure is being run from somewhere. It is not
>> being issued manually by anyone!!
>> The only way to restore the linked server connection is to stop and
>> restart sqlserver, which is very inconvenient to users of other
>> databases.
>> 5. I need to find a way to keep the linked server connection from
>> breaking due to the sporadic, but consistent fatal SqlDump error log
>> above.
>> Thanks for any help.
>> Alan
>>
>>
>>
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog

Linked Server

I have an oracel linked server that I use openqueryset statements to read the oracle tables.

However, I want to update some data in a couple of these oracle tables. The linked server is setup using a readonly user. I'd like to be able to call an Oracle Set Role to grant me update capability and then perform the update.

Can anyone help me out with some possibilities?

See SQL Server 2005 Books Online

OPENQUERY (Transact-SQL)

This is a KB error article but it has useful information on using OPENQUERY

http://support.microsoft.com/kb/q270119/

|||

Well... does not really apply. What I am thinking about doing is calling an oracle stored procedure that grants the linked server account update privledges on the table. Then submit an openquery statement to do the update.

However, I am not sure if the privledge granted from the prcale stored procedure will still be in effect when the second openquery statement is issued.

The reason why I am doing this is for security reasons to not allow a linked server "default" update privledges on the Oracle database.

|||

In case anyone needs to do this... or something similar... I have figured it out:

On Oracle server, create the Oracle Package and Procedure:

create or replace
PACKAGE BODY Call_SEC_ROLE_Package
AS
PROCEDURE sec_roles
(ReturnVal OUT SqlReturnTbl)
is
begin
dbms_session.set_role('apply_security identified by secure_pass');
ReturnVal(1) := '1';
end sec_roles;
end Call_SEC_ROLE_Package;

On SQL Server create your linked server and then call the Oracle stored procedure by:

SELECT *
FROM OPENQUERY
(LinkServerName, '{CALL Call_SEC_ROLE_Package.sec_roles({resultset 1, ReturnVal})}')
go