Sql Linked Server: February 2012

Friday, February 24, 2012

Linked server (Access file)

I have created a linked server with an Access file. I can run queires agains
t
the linked server if I log in to Management Studio using the domain admin
account. I can't run queries against the linked server if I am logged in
using a sql account. I get
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"LinkedServerl" reported an error. Authentication failed. in my .aspx page.
SQL is on one server and the Access database is on another. I checked the
permissions on the folder where the Access database and "Everyone" has full
control. What else should I be checking to make this work?Curtis
Log in as SQL account on the server and try to copy/create/wrtite in the
folder access file located
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:CCEC2A3C-7F3D-4FC4-BCFA-4F365944426D@.microsoft.com...
>I have created a linked server with an Access file. I can run queires
>against
> the linked server if I log in to Management Studio using the domain admin
> account. I can't run queries against the linked server if I am logged in
> using a sql account. I get
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "LinkedServerl" reported an error. Authentication failed. in my .aspx
> page.
> SQL is on one server and the Access database is on another. I checked the
> permissions on the folder where the Access database and "Everyone" has
> full
> control. What else should I be checking to make this work?
>|||How can I log in using a sql account on a server? Since they have it set up
so everyone has full control, then it shouldn't matter who is logged into
sql, right?
"Uri Dimant" wrote:

> Curtis
> Log in as SQL account on the server and try to copy/create/wrtite in the
> folder access file located
>
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:CCEC2A3C-7F3D-4FC4-BCFA-4F365944426D@.microsoft.com...
>
>|||Curtis
That what I meant , a sql server account is a member of sysadmin server
role?
What is an account MSSQLSERVER service run under? Is it Local system ?
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:E41B3A59-2C83-445C-814A-28AD1CF2568E@.microsoft.com...[vbcol=seagreen]
> How can I log in using a sql account on a server? Since they have it set
> up
> so everyone has full control, then it shouldn't matter who is logged into
> sql, right?
> "Uri Dimant" wrote:
>|||SQL is running under Local System. The sql server account is not a member of
sysadmin.
"Uri Dimant" wrote:

> Curtis
> That what I meant , a sql server account is a member of sysadmin server
> role?
> What is an account MSSQLSERVER service run under? Is it Local system ?
>
>
>
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:E41B3A59-2C83-445C-814A-28AD1CF2568E@.microsoft.com...
>
>|||I put the sql server account in the sysadmin role and it now works. Thank yo
u.
"Uri Dimant" wrote:

> Curtis
> That what I meant , a sql server account is a member of sysadmin server
> role?
> What is an account MSSQLSERVER service run under? Is it Local system ?
>
>
>
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:E41B3A59-2C83-445C-814A-28AD1CF2568E@.microsoft.com...
>
>

Linked server (Access file)

I have created a linked server with an Access file. I can run queires against
the linked server if I log in to Management Studio using the domain admin
account. I can't run queries against the linked server if I am logged in
using a sql account. I get
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"LinkedServerl" reported an error. Authentication failed. in my .aspx page.
SQL is on one server and the Access database is on another. I checked the
permissions on the folder where the Access database and "Everyone" has full
control. What else should I be checking to make this work?Curtis
Log in as SQL account on the server and try to copy/create/wrtite in the
folder access file located
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:CCEC2A3C-7F3D-4FC4-BCFA-4F365944426D@.microsoft.com...
>I have created a linked server with an Access file. I can run queires
>against
> the linked server if I log in to Management Studio using the domain admin
> account. I can't run queries against the linked server if I am logged in
> using a sql account. I get
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "LinkedServerl" reported an error. Authentication failed. in my .aspx
> page.
> SQL is on one server and the Access database is on another. I checked the
> permissions on the folder where the Access database and "Everyone" has
> full
> control. What else should I be checking to make this work?
>|||Curtis
That what I meant , a sql server account is a member of sysadmin server
role?
What is an account MSSQLSERVER service run under? Is it Local system ?
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:E41B3A59-2C83-445C-814A-28AD1CF2568E@.microsoft.com...
> How can I log in using a sql account on a server? Since they have it set
> up
> so everyone has full control, then it shouldn't matter who is logged into
> sql, right?
> "Uri Dimant" wrote:
>> Curtis
>> Log in as SQL account on the server and try to copy/create/wrtite in the
>> folder access file located
>>
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:CCEC2A3C-7F3D-4FC4-BCFA-4F365944426D@.microsoft.com...
>> >I have created a linked server with an Access file. I can run queires
>> >against
>> > the linked server if I log in to Management Studio using the domain
>> > admin
>> > account. I can't run queries against the linked server if I am logged
>> > in
>> > using a sql account. I get
>> > The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
>> > "LinkedServerl" reported an error. Authentication failed. in my .aspx
>> > page.
>> >
>> > SQL is on one server and the Access database is on another. I checked
>> > the
>> > permissions on the folder where the Access database and "Everyone" has
>> > full
>> > control. What else should I be checking to make this work?
>> >
>>

Linked server (Access file)

I have created a linked server with an Access file. I can run queires against
the linked server if I log in to Management Studio using the domain admin
account. I can't run queries against the linked server if I am logged in
using a sql account. I get
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"LinkedServerl" reported an error. Authentication failed. in my .aspx page.
SQL is on one server and the Access database is on another. I checked the
permissions on the folder where the Access database and "Everyone" has full
control. What else should I be checking to make this work?
Curtis
Log in as SQL account on the server and try to copy/create/wrtite in the
folder access file located
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:CCEC2A3C-7F3D-4FC4-BCFA-4F365944426D@.microsoft.com...
>I have created a linked server with an Access file. I can run queires
>against
> the linked server if I log in to Management Studio using the domain admin
> account. I can't run queries against the linked server if I am logged in
> using a sql account. I get
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "LinkedServerl" reported an error. Authentication failed. in my .aspx
> page.
> SQL is on one server and the Access database is on another. I checked the
> permissions on the folder where the Access database and "Everyone" has
> full
> control. What else should I be checking to make this work?
>
|||How can I log in using a sql account on a server? Since they have it set up
so everyone has full control, then it shouldn't matter who is logged into
sql, right?
"Uri Dimant" wrote:

> Curtis
> Log in as SQL account on the server and try to copy/create/wrtite in the
> folder access file located
>
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:CCEC2A3C-7F3D-4FC4-BCFA-4F365944426D@.microsoft.com...
>
>
|||Curtis
That what I meant , a sql server account is a member of sysadmin server
role?
What is an account MSSQLSERVER service run under? Is it Local system ?
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:E41B3A59-2C83-445C-814A-28AD1CF2568E@.microsoft.com...[vbcol=seagreen]
> How can I log in using a sql account on a server? Since they have it set
> up
> so everyone has full control, then it shouldn't matter who is logged into
> sql, right?
> "Uri Dimant" wrote:
|||SQL is running under Local System. The sql server account is not a member of
sysadmin.
"Uri Dimant" wrote:

> Curtis
> That what I meant , a sql server account is a member of sysadmin server
> role?
> What is an account MSSQLSERVER service run under? Is it Local system ?
>
>
>
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:E41B3A59-2C83-445C-814A-28AD1CF2568E@.microsoft.com...
>
>
|||I put the sql server account in the sysadmin role and it now works. Thank you.
"Uri Dimant" wrote:

> Curtis
> That what I meant , a sql server account is a member of sysadmin server
> role?
> What is an account MSSQLSERVER service run under? Is it Local system ?
>
>
>
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:E41B3A59-2C83-445C-814A-28AD1CF2568E@.microsoft.com...
>
>

Linked Server -> Interbase

Hi!!!
I have a Interbase database and I would like to make a linked server.
I don't have Interbase driver in the Windows 98's ODBC Data Source Administrator
What I need to do?
Thank you.
ByeDownload from web.|||You can get the

Easysoft ODBC for Interbase at

www.easysoft.com

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:
>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> >
> > Is there anyway to set XACT_ABORT ON for a linked server? We have a
> linked
> > server setup between SQL 2000 and DB2. To use transactions we need to
> turn
> > XACT_ABORT on in every single stored procedure. Is there a way to turn it
> on
> > at the linked server level?
> >
> > Thanks.
> >
> > Paul
> >
> >
>
>

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it o
n
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF
."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:

>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> linked
> turn
> on
>
>

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paul
a.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>
|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:

>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> linked
> turn
> on
>
>

Linked server & transaction connection

Hi all,
I've a local server and a linked server, which I need to update both
servers' tables.
But I encountered the error "Can't start more transaction in a session'.
Following is the structure of the code in the program:
----
Begin Transaction
Insert local_server_A.table
Update linked_server_B.table
Commit Transaction
If Error
Rollback Transaction
I know the problem is the transaction control can only exist in one
connection.
The linked server is treated as another connection.
Is there any way to workaround for multi connection in a transaction control
?
Thanks in advance,
KristeHi
Read about distribution transactions in the BOL.
"whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> Hi all,
> I've a local server and a linked server, which I need to update both
> servers' tables.
> But I encountered the error "Can't start more transaction in a session'.
> Following is the structure of the code in the program:
> ----
> Begin Transaction
> Insert local_server_A.table
> Update linked_server_B.table
> Commit Transaction
> If Error
> Rollback Transaction
> I know the problem is the transaction control can only exist in one
> connection.
> The linked server is treated as another connection.
> Is there any way to workaround for multi connection in a transaction
control?
> Thanks in advance,
> Kriste|||Sorry, I read thr' BOL on MS DTC and replication etc. but I still can't find
solution how can this be applied just between linked servers. Can you provid
e
more info. on this?
"Uri Dimant" wrote:

> Hi
> Read about distribution transactions in the BOL.
>
> "whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
> news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> control?
>
>

Linked Server & SQL Auth

I am stuck... I am trying to use a linked server in sql 2005 (xls sheet), it works great using windows authentication but since the program will not be running locally i would prefer to use sql auth...

when i log in using the sql name i have created. it only has connect & execute permissions on the main database (as it should), but i also need to query this linked server, when i try to query it i get authentication failed. how do i fix that?

the xls is on a fat32 partition and can be moved to an ntfs partition with out any problems.

thanks in advance...not sure if this is the problem, but have you granted read permissions to the database (e.g. db_datareader role)?|||no i have not, i thought that would give to much power to that user and allow it access to other databases..if that is false please let me know..|||The db_datareader database role is for only within the database it is granted. So you would only be granting read access to tables within the specified database that the public role does not already allow read access to.|||i do not have these options on a linked server so... what is next.

Linked Server "NOLOCK" problem on SQL 2000

Dear all,
My current configuration : Windows 2000 Server + MS SQL Server 2000 Standard
Edition + Service Pack 3
I got an error when I used "NOLOCK" in selecting some records through Linked
Server :
SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran (NOLOCK)
Error :
Server : Msg 7377, Level 16, State 1, Line 1
Cannot specify an index or locking hint for a remote data source
But this error would not occurred if I re-wrote the statement :
SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran
Why "NOLOCK" doesn't work in SQL 2000 Linked Server ?
Hi
OPENQUERY can pass the NOLCOK hint It is one of those limitations that
probably has a valid technical and implementation reason, but the SQL Server
development team have not said why.
Look also at:
http://groups.google.com.au/group/mi...d97cfedeb0cb06
Regards--Mike Epprecht, Microsoft SQL Server
MVPZurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <cpjohn@.netvigator.com> wrote in message
news:d7ce0j$b4f25@.imsp212.netvigator.com...
> Dear all,
> My current configuration : Windows 2000 Server + MS SQL Server 2000
> Standard
> Edition + Service Pack 3
> I got an error when I used "NOLOCK" in selecting some records through
> Linked
> Server :
> SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran (NOLOCK)
> Error :
> Server : Msg 7377, Level 16, State 1, Line 1
> Cannot specify an index or locking hint for a remote data source
> But this error would not occurred if I re-wrote the statement :
> SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran
> Why "NOLOCK" doesn't work in SQL 2000 Linked Server ?
>
>
>
|||Thanks.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u9OUzS7ZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hi
> OPENQUERY can pass the NOLCOK hint It is one of those limitations that
> probably has a valid technical and implementation reason, but the SQL
Server
> development team have not said why.
> Look also at:
>
http://groups.google.com.au/group/mi...erver/browse_t
hread/thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5
&hl=en#29d97cfedeb0cb06
> Regards--Mike Epprecht, Microsoft SQL Server
> MVPZurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "John" <cpjohn@.netvigator.com> wrote in message
> news:d7ce0j$b4f25@.imsp212.netvigator.com...
>
|||We get around this by exec'ing a remote stored proc.
exec @.RetVal = thelinkedserver.dbname.myproc @.param1 = @.param1 ....
etc
The limitations are that you can't insert data from exec into a table
variable. normal tables and #tables are OK.
Also you can't exec another proc in the remote proc.
You should get better performance using this as well.
Paul

Linked Server "NOLOCK" problem on SQL 2000

Dear all,
My current configuration : Windows 2000 Server + MS SQL Server 2000 Standard
Edition + Service Pack 3
I got an error when I used "NOLOCK" in selecting some records through Linked
Server :
SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran (NOLOCK)
Error :
Server : Msg 7377, Level 16, State 1, Line 1
Cannot specify an index or locking hint for a remote data source
But this error would not occurred if I re-wrote the statement :
SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran
Why "NOLOCK" doesn't work in SQL 2000 Linked Server ?Hi
OPENQUERY can pass the NOLCOK hint It is one of those limitations that
probably has a valid technical and implementation reason, but the SQL Server
development team have not said why.
Look also at:
thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5&hl=en#29d97
cfedeb0cb06" target="_blank">http://groups.google.com.au/group/m.../>
cfedeb0cb06
Regards--Mike Epprecht, Microsoft SQL Server
MVPZurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <cpjohn@.netvigator.com> wrote in message
news:d7ce0j$b4f25@.imsp212.netvigator.com...
> Dear all,
> My current configuration : Windows 2000 Server + MS SQL Server 2000
> Standard
> Edition + Service Pack 3
> I got an error when I used "NOLOCK" in selecting some records through
> Linked
> Server :
> SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran (NOLOCK)
> Error :
> Server : Msg 7377, Level 16, State 1, Line 1
> Cannot specify an index or locking hint for a remote data source
> But this error would not occurred if I re-wrote the statement :
> SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran
> Why "NOLOCK" doesn't work in SQL 2000 Linked Server ?
>
>
>|||Thanks.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u9OUzS7ZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hi
> OPENQUERY can pass the NOLCOK hint It is one of those limitations that
> probably has a valid technical and implementation reason, but the SQL
Server
> development team have not said why.
> Look also at:
>
http://groups.google.com.au/group/m...server/browse_t
hread/thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5
&hl=en#29d97cfedeb0cb06
> Regards--Mike Epprecht, Microsoft SQL Server
> MVPZurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "John" <cpjohn@.netvigator.com> wrote in message
> news:d7ce0j$b4f25@.imsp212.netvigator.com...
>|||We get around this by exec'ing a remote stored proc.
exec @.RetVal = thelinkedserver.dbname.myproc @.param1 = @.param1 ....
etc
The limitations are that you can't insert data from exec into a table
variable. normal tables and #tables are OK.
Also you can't exec another proc in the remote proc.
You should get better performance using this as well.
Paul

Linked Server "NOLOCK" problem on MS SQL 2000

Linked Server -- unexpected return data length

Hello everybody,
Thank you for your attention.
I can get numeric data from a linked HP database but unable to pull character type data. Here is the error message:
Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[].CUSTPART'. The expected data length is 20, while the returned data length is 11.
I went thru Microsoft Knowledge Garden but didn't find any satisfactory answers. Your help is greatly appreciated.
Wei
Hi Wei,
Some 3rd party ODBC drivers behave differently than SQL Server ODBC driver
in the way
they handle fixed length char columns. The difference involves how the
length
indicator value for fixed length columns is handled. If SQL Server ODBC
driver is
being used, the length indicator buffer (last parameter to SQLBindCol) is
always
populated with the max length of the fixed length character column no
matter how
much data is actually in the given data field. The only exception to this
is NULL
values, where the length indicator buffer is populated with the value '-1'.
Some 3rd party ODBC drivers, however, do not take this approach and instead
populate the length indicator buffer with the exact number of bytes of
character
data in the current database field.
To Overcome this, set the trace flag 8765 and you should not face the
problem
To set the flag, in the SQL Server Properties on the General Tab, click on
Startup Parameters and add the new parameter as "-T8765" as one word
without the quotes.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you very much Ashish. It works like magic!! I wish I
had checked your answer earlier.
Wei

>--Original Message--
>Hi Wei,
>Some 3rd party ODBC drivers behave differently than SQL
Server ODBC driver
>in the way
>they handle fixed length char columns. The difference
involves how the
>length
>indicator value for fixed length columns is handled. If
SQL Server ODBC
>driver is
>being used, the length indicator buffer (last parameter
to SQLBindCol) is
>always
>populated with the max length of the fixed length
character column no
>matter how
>much data is actually in the given data field. The only
exception to this
>is NULL
>values, where the length indicator buffer is populated
with the value '-1'.
>Some 3rd party ODBC drivers, however, do not take this
approach and instead
>populate the length indicator buffer with the exact
number of bytes of
>character
>data in the current database field.
>To Overcome this, set the trace flag 8765 and you should
not face the
>problem
>To set the flag, in the SQL Server Properties on the
General Tab, click on
>Startup Parameters and add the new parameter as "-
T8765" as one word
>without the quotes.
>
>HTH
>Ashish
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>

Linked Server -- unexpected return data length

Hello everybody,
Thank you for your attention.
I can get numeric data from a linked HP database but unable to pull characte
r type data. Here is the error message:
Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-l
ength column '[].CUSTPART'. The expected data length is 20, while the re
turned data length is 11.
I went thru Microsoft Knowledge Garden but didn't find any satisfactory answ
ers. Your help is greatly appreciated.
WeiHi Wei,
Some 3rd party ODBC drivers behave differently than SQL Server ODBC driver
in the way
they handle fixed length char columns. The difference involves how the
length
indicator value for fixed length columns is handled. If SQL Server ODBC
driver is
being used, the length indicator buffer (last parameter to SQLBindCol) is
always
populated with the max length of the fixed length character column no
matter how
much data is actually in the given data field. The only exception to this
is NULL
values, where the length indicator buffer is populated with the value '-1'.
Some 3rd party ODBC drivers, however, do not take this approach and instead
populate the length indicator buffer with the exact number of bytes of
character
data in the current database field.
To Overcome this, set the trace flag 8765 and you should not face the
problem
To set the flag, in the SQL Server Properties on the General Tab, click on
Startup Parameters and add the new parameter as "-T8765" as one word
without the quotes.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you very much Ashish. It works like magic!! I wish I
had checked your answer earlier.
Wei

>--Original Message--
>Hi Wei,
>Some 3rd party ODBC drivers behave differently than SQL
Server ODBC driver
>in the way
>they handle fixed length char columns. The difference
involves how the
>length
>indicator value for fixed length columns is handled. If
SQL Server ODBC
>driver is
>being used, the length indicator buffer (last parameter
to SQLBindCol) is
>always
>populated with the max length of the fixed length
character column no
>matter how
>much data is actually in the given data field. The only
exception to this
>is NULL
>values, where the length indicator buffer is populated
with the value '-1'.
>Some 3rd party ODBC drivers, however, do not take this
approach and instead
>populate the length indicator buffer with the exact
number of bytes of
>character
>data in the current database field.
>To Overcome this, set the trace flag 8765 and you should
not face the
>problem
>To set the flag, in the SQL Server Properties on the
General Tab, click on
>Startup Parameters and add the new parameter as "-
T8765" as one word
>without the quotes.
>
>HTH
>Ashish
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>

Linked Server - Turn Off Constraints

I want to turn off constraints on all tables on a linked server using:
"SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'"
Should I make a connection to the linked server, and/or use OpenQuery to
pull this off?Derek,
That could very easily cause data integrity issues. Are you really sure you
want to do that?
HTH
Jerry
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:OZ%235R8seGHA.4948@.TK2MSFTNGP04.phx.gbl...
>I want to turn off constraints on all tables on a linked server using:
> "SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'"
> Should I make a connection to the linked server, and/or use OpenQuery to
> pull this off?
>

Linked Server - Trigger problem

Hi !

I have a problem, which i have dealt with a long time now. The scenario is that I have 2 SQL Server's (one 2K, the other 7). There are on both Servers a table (with users in it). In both tables on both servers, there are triggers (which fires on respectively : INSERT, DELETE and UPDATE).
If for example I add a new user on the SQL Server 2K, a new user with same properties are to be added on the other SQL Server. Very straight forward ! The 2 Servers are connected via "Linked Servers". In that way they each hold a reference to the other SQL Server. I have turned off both "recursive triggers" and "indirect recursive triggers". BUT, when I insert a new user, I get an error from MS DTC, that "Transaction context in use by another session. [OLE/DB provider returned message: Unspecified error]". I cannot get by this error. It is because the first trigger insert on the other table that again fires a trigger. This I can handle, but MS DTC cannot, because it fires a new transaction with the first one.... No can do, Microsoft says !!!!

Is there a bright head that have a solution for this one. And yes I know it not a very clever solution the one stated above, but under the circumstances it is the only thing we can do, without getting out in the middleware forest !!! (not a nice thing)what a night mare!!

Don't know if this will help but could both triggers call just one stored procedure that sits on one machine that does the actual insert ??

Alternativley can you use replication ??

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 - server name same as instance name

I am trying to configure a linked server in SQL 2005 to point to a server that has the same name as the SQL 2005 instance name. In other words, my computer name and SQL Server 2005 instance name are both EMUNDORF. In the "New Linked Server" dialog, I set the following:

Linked Server: EMUNDORF

Provider: SQL Native Client

Product Name: EMUNDORF

Data Source: EMUNDORF\EMUNDORF

This does not work. If I configure a Linked Server to a server with a named instance that is different than the server name, it works. The following is the error I get:

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 50000, Level 16, State 2, Line 10

Unable to connect to server. This operation will be tried later!

Can someone tell me what I am doing wrong?

The error is the same as if I try to connect to a server that is no longer running. I double checked all my settings with a server that has a named instance, and can link to other servers, but I can see no differences. The other strange thing is that my server can link to other servers, but no one is able to connect to mine. I know it has to be a setting somewhere...but where?|||

Did you enable remote connections for the server instance (It is disabled by default). To do that -

Go to Configuration Tools -> SQL Server Surface Area Configuration

Select Surface Area Configuration for Services and Connections

You should be able to see your server instance in the available components, Select Database Engine -> Remote Connections and then click on Local and Remote Connections with the Appropriate protocol. You need to restart the service once you save the settings.

Hope that helps,

Kuntal

|||

I would expect your product name to be NULL since you are using SQL Server.

I have success with these:

sp_addlinkedserver @.server = N'yourLnkdSrvName', @.srvproduct=N'',@.provider='SQLNCLI', @.datasrc=N'yourserver/instance' ;

Linked Server - server name same as instance name

Linked Server: EMUNDORF

Provider: SQL Native Client

Product Name: EMUNDORF

Data Source: EMUNDORF\EMUNDORF

This does not work. If I configure a Linked Server to a server with a named instance that is different than the server name, it works. The following is the error I get:

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" returned message "Login timeout expired".

Msg 50000, Level 16, State 2, Line 10

Unable to connect to server. This operation will be tried later!

Can someone tell me what I am doing wrong?

Did you enable remote connections for the server instance (It is disabled by default). To do that -

Go to Configuration Tools -> SQL Server Surface Area Configuration

Select Surface Area Configuration for Services and Connections

Hope that helps,

Kuntal

|||

I would expect your product name to be NULL since you are using SQL Server.

I have success with these:

sp_addlinkedserver @.server = N'yourLnkdSrvName', @.srvproduct=N'',@.provider='SQLNCLI', @.datasrc=N'yourserver/instance' ;

Linked Server - server name same as instance name

Linked Server: EMUNDORF

Provider: SQL Native Client

Product Name: EMUNDORF

Data Source: EMUNDORF\EMUNDORF

This does not work. If I configure a Linked Server to a server with a named instance that is different than the server name, it works. The following is the error I get:

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" returned message "Login timeout expired".

Msg 50000, Level 16, State 2, Line 10

Unable to connect to server. This operation will be tried later!

Can someone tell me what I am doing wrong?

Did you enable remote connections for the server instance (It is disabled by default). To do that -

Go to Configuration Tools -> SQL Server Surface Area Configuration

Select Surface Area Configuration for Services and Connections

Hope that helps,

Kuntal

|||

I would expect your product name to be NULL since you are using SQL Server.

I have success with these:

sp_addlinkedserver @.server = N'yourLnkdSrvName', @.srvproduct=N'',@.provider='SQLNCLI', @.datasrc=N'yourserver/instance' ;

Linked Server - Security Problem

Hi
We have set up Transactional Replication between two servers.
However the linked server that is created at the subscriber does not work be
default.
I therefore selected the last option "Be made using this security context"
and entered the sa username and password.
This solves the problem - linked server can then connect.
But what I want to know is how to get the first option working by specifying
a local login and remote user and remote password.
I have tried all users on the servers and in different combinations with
impersonate options ticked on some! but could not get it working.
So for now it is running using the sa username and password.
How does login-mapping work? why can I not get it to work?
Please advise
The security context used is whatever security context you configured your
distribution agent to connect to the subscriber with. By default this will
be the Publisher SQL Server Agent account if you are using a push
subscription.
When you created your subscription you define this in the Distribution Agent
dialog box, in the Connect to subscriber section.
"I.W Coetzer" <IWCoetzer@.discussions.microsoft.com> wrote in message
news:ACE5DB2B-1A03-48EC-BA15-3005F0C41A16@.microsoft.com...
> Hi
> We have set up Transactional Replication between two servers.
> However the linked server that is created at the subscriber does not work
> be
> default.
> I therefore selected the last option "Be made using this security context"
> and entered the sa username and password.
> This solves the problem - linked server can then connect.
> But what I want to know is how to get the first option working by
> specifying
> a local login and remote user and remote password.
> I have tried all users on the servers and in different combinations with
> impersonate options ticked on some! but could not get it working.
> So for now it is running using the sa username and password.
> How does login-mapping work? why can I not get it to work?
> Please advise
|||Hi
Our Sql Server Agent is set up to use a domain account.
ourdomain\our_special_account
All of our sql servers on the same domain (even in different locations) use
this special domain account that our administrators created for us.
I can not seem to create a linked server using this domain account.
Therefore I set it up to use the sa account of the server.
all our servers also have the same sa sql account and password because these
servers were created from the same virtual machine templates ...
"Hilary Cotter" wrote:

> The security context used is whatever security context you configured your
> distribution agent to connect to the subscriber with. By default this will
> be the Publisher SQL Server Agent account if you are using a push
> subscription.
> When you created your subscription you define this in the Distribution Agent
> dialog box, in the Connect to subscriber section.
> "I.W Coetzer" <IWCoetzer@.discussions.microsoft.com> wrote in message
> news:ACE5DB2B-1A03-48EC-BA15-3005F0C41A16@.microsoft.com...
>
>

Linked Server - Query

Hey all,
I have an Access app that need to be able to Query a Linked Server. The
Linked Server points to an Access Database. When I put the Access App on th
e
SQL Box where SQL 2000 and the Linked Database reside, I am able to Query th
e
Linked Server from the Access App with a SELECT or SP containing a statement
such as:
"SELECT * FROM PCCW...TRANS"
But when I try to run this same Query from the same Access App on any other
machine, I get the error:
"Cannot start your application. The Workgroup information file is missing or
opened exclusively by another user"
Any help is appreciated as always!
Thanks,
GerardHi Gerard
Have you checked out
http://msdn.microsoft.com/library/d...serr_2_6jzt.asp
John
"Gerard" wrote:

> Hey all,
> I have an Access app that need to be able to Query a Linked Server. T
he
> Linked Server points to an Access Database. When I put the Access App on
the
> SQL Box where SQL 2000 and the Linked Database reside, I am able to Query
the
> Linked Server from the Access App with a SELECT or SP containing a stateme
nt
> such as:
> "SELECT * FROM PCCW...TRANS"
> But when I try to run this same Query from the same Access App on any othe
r
> machine, I get the error:
> "Cannot start your application. The Workgroup information file is missing
or
> opened exclusively by another user"
> Any help is appreciated as always!
> Thanks,
> Gerard

Linked Server - On Connection Error Resume

I have a stored procedure which connects to multiple linked servers. If one connection fails, the stored procedure ends with error.

Is it possible to have the stored proc continue instead of quit?

I have tried the following and none work:
- connect in a different sp
- use sp_executesql

Thanks

Oh yeah...I am using SQL SERVER 2000 SP4 and I know SQL SERVER 2005 has a sp to test a linked server, but I cannot upgrade.

I once had the problem to check for the availbility of linked server. The solution (in our case) was to ping the servers on a regular basis, by using a SQL Server Agent job, which did something like

--INSERT INTO PingTable
--SomeSelectonTheServer, mainly something like SELECT 1, or SELECT * FROM dual (in this case for Oracle)
--UPDATE PingTable, update the status of the ping record to successfull.

Therefore we could query the table for the successfull connectivity, if the column Successfull was not filed or updated properly we threw a manual error.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Linked Server - OLE/DB Provider MSDAORA

We have created linked server between SQL Server 2000 and Oracle 10g in offshore environment as well as onsite environment,both the environments are almost IDENTICAL.Basically, the whole execution happens on Oracle and the output is passed to SQL Server.When the below query is executed in offshore environment it is returning values perfectly.

SELECT * FROM OPENQUERY(ORACLE_LINK,'{Call TEST.IVR.lo_Dlr_GetInfo(55,{resultset 2,p_ReturnVal})}')

Here: ORACLE_LINK - is the Linked Server Name,
TEST - Oracle Schema Name
IVR - Oracle Package Name

But when the same query is executed on onsite environment it is raising the following error.

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
The environment details are:

Offshore Environment(Successfully returning values):

1)Operating System - Windows 2000 Advanced Server
2)Service pack on Operating System - SP4
3)Version on MSDORA.dll(C:\ProgramFiles\CommonFiles\System\OLED B) - 2.81.1117.0
4)Version on tstbestsql1--SQL Database - SQL Server 2000 Enterprise Edition
5)ServicePack on tstbestsql1--SQL Database - 8.00.2040 (SP3a)

Onsite Environment:

1)Operating System - Windows 2000 Advanced Server
2)Service pack on Operating System - SP4
3)Version on MSDORA.dll(C:\ProgramFiles\CommonFiles\System\OLED B) - 2.81.1117.0
4)Version on tstbestsql1--SQL Database - SQL Server 2000 Enterprise Edition
5)ServicePack on tstbestsql1--SQL Database - 8.00.2040 (SP4)

We are able to execute if we are executing the query as

SELECT * FROM OPENQUERY(ORACLE_LINK,'select column1 from table1') on the Onshore server and it is perfectly returning the value.I believe the problem might be with "Call" if anyone can help with it that will be great.

Thanks,
RaahulFYI - also on SQLTeam:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75784

linked server - MS Access - database pwd

I have to link to an MS Access file that has a database password but no
user-level security (as far as I'm aware). I could not get the OLE DB
provider for Jet to authenticate and instead had to create an ODBC System DSN
for the Access database and then use OLE DB for ODBC databases as my linked
server provider.
Is is it possible to create a linked server using the OLE DB provider for
Jet in this scenario so I don't have to manage the ODBC System DSN?
Thanks, TadHi Tad
On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
> I have to link to an MS Access file that has a database password but no
> user-level security (as far as I'm aware). I could not get the OLE DB
> provider for Jet to authenticate and instead had to create an ODBC System DSN
> for the Access database and then use OLE DB for ODBC databases as my linked
> server provider.
> Is is it possible to create a linked server using the OLE DB provider for
> Jet in this scenario so I don't have to manage the ODBC System DSN?
> Thanks, Tad
BOL gives and example for using sp_addlinkedserver to do this:
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'SEATTLE Mktg',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
if you are using SQL Server logins they will need to be mapped with
sp_addlinkedsrvlogin
which is also described in the link
e.g.
EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
John|||Thanks, John but my read of BOL is that you need user-level security to
implement their examples. My mdb has a database password but no user-level
security.
Tad
"John Bell" wrote:
> Hi Tad
> On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
> > I have to link to an MS Access file that has a database password but no
> > user-level security (as far as I'm aware). I could not get the OLE DB
> > provider for Jet to authenticate and instead had to create an ODBC System DSN
> > for the Access database and then use OLE DB for ODBC databases as my linked
> > server provider.
> >
> > Is is it possible to create a linked server using the OLE DB provider for
> > Jet in this scenario so I don't have to manage the ODBC System DSN?
> >
> > Thanks, Tad
> BOL gives and example for using sp_addlinkedserver to do this:
> USE master
> GO
> -- To use named parameters:
> EXEC sp_addlinkedserver
> @.server = 'SEATTLE Mktg',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
> GO
> Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
> if you are using SQL Server logins they will need to be mapped with
> sp_addlinkedsrvlogin
> which is also described in the link
> e.g.
> EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
> John
>
>|||Hi
Database passwords will not work with linked servers and they appear to be
nothing but obfuscation see
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/813a1960b96a26a8?hl=en&
John
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:5274D022-03CD-4D8F-9F73-B3EC4D4F18EA@.microsoft.com...
> Thanks, John but my read of BOL is that you need user-level security to
> implement their examples. My mdb has a database password but no
> user-level
> security.
> Tad
> "John Bell" wrote:
>> Hi Tad
>> On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
>> > I have to link to an MS Access file that has a database password but no
>> > user-level security (as far as I'm aware). I could not get the OLE DB
>> > provider for Jet to authenticate and instead had to create an ODBC
>> > System DSN
>> > for the Access database and then use OLE DB for ODBC databases as my
>> > linked
>> > server provider.
>> >
>> > Is is it possible to create a linked server using the OLE DB provider
>> > for
>> > Jet in this scenario so I don't have to manage the ODBC System DSN?
>> >
>> > Thanks, Tad
>> BOL gives and example for using sp_addlinkedserver to do this:
>> USE master
>> GO
>> -- To use named parameters:
>> EXEC sp_addlinkedserver
>> @.server = 'SEATTLE Mktg',
>> @.provider = 'Microsoft.Jet.OLEDB.4.0',
>> @.srvproduct = 'OLE DB Provider for Jet',
>> @.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
>> GO
>> Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
>> if you are using SQL Server logins they will need to be mapped with
>> sp_addlinkedsrvlogin
>> which is also described in the link
>> e.g.
>> EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
>> John
>>|||Thanks, John - I suspected as much but that's good ammunition to take to client
"John Bell" wrote:
> Hi
> Database passwords will not work with linked servers and they appear to be
> nothing but obfuscation see
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/813a1960b96a26a8?hl=en&
> John
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:5274D022-03CD-4D8F-9F73-B3EC4D4F18EA@.microsoft.com...
> > Thanks, John but my read of BOL is that you need user-level security to
> > implement their examples. My mdb has a database password but no
> > user-level
> > security.
> >
> > Tad
> >
> > "John Bell" wrote:
> >
> >> Hi Tad
> >>
> >> On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
> >> > I have to link to an MS Access file that has a database password but no
> >> > user-level security (as far as I'm aware). I could not get the OLE DB
> >> > provider for Jet to authenticate and instead had to create an ODBC
> >> > System DSN
> >> > for the Access database and then use OLE DB for ODBC databases as my
> >> > linked
> >> > server provider.
> >> >
> >> > Is is it possible to create a linked server using the OLE DB provider
> >> > for
> >> > Jet in this scenario so I don't have to manage the ODBC System DSN?
> >> >
> >> > Thanks, Tad
> >> BOL gives and example for using sp_addlinkedserver to do this:
> >>
> >> USE master
> >> GO
> >> -- To use named parameters:
> >> EXEC sp_addlinkedserver
> >> @.server = 'SEATTLE Mktg',
> >> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> >> @.srvproduct = 'OLE DB Provider for Jet',
> >> @.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
> >> GO
> >>
> >> Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
> >> if you are using SQL Server logins they will need to be mapped with
> >> sp_addlinkedsrvlogin
> >> which is also described in the link
> >> e.g.
> >> EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
> >>
> >> John
> >>
> >>
> >>
>
>

linked server - MS Access - database pwd

Linked Server - Limiting Index Search Result

Hi all,
I'm making some experiments with the Full Text features offered by
MS-SQLServer (MSSEARCH) and the Indexing Service.
Querying MSSEARCH, I can limit the result using top_n_by_rank
in CONTAINSTABLE / FREETEXTTABLE.
I wasn't able to find out anything I can use to get the same result querying
the Indexing Service as a linked server.
If I try the following:
SELECT Q.*
FROM OpenQuery(FileSystem,
'SELECT TOP 100 rank, FileName, Size, DocAuthor, DocTitle
FROM TESTCATALOG..SCOPE('' "" '')
WHERE FREETEXT(''enhacing security'')
ORDER by rank') AS Q
I got an error:
Server: Msg 7321, Level 16, State 2, Line 1
[OLE/DB provider returned message: Incorrect syntax near '100'. Expected ',', '.', FROM. SQLSTATE=42000 ]
[OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare returned 0x80040e14].
(I guess TOP is not supported by syntax).
Of course I can do something like this:
SELECT TOP 100 Q.*
FROM OpenQuery(FileSystem,
'SELECT rank, FileName, Size, DocAuthor, DocTitle
FROM TESTCATALOG..SCOPE('' "" '')
WHERE FREETEXT(''enhacing security'')' ) AS Q
order by Q.rank desc
but in this case (I think..) all the matching rows are returned by
Indexing Service and then SQLServer get the first N rows discarding
the rest.
Do you know if there is some way to instruct the Index Service
(i.e. MSIDXS provider) to return only the first N best matches ?
Thanks a lot for your help
MadMax
The MSIDXS provider does not provide a method to limit the number of rows it will return.
Another option is to use the sp_oacreate objects and instantiate the ixsso object.
Here is an example. Here I am limitiing it to 200 records.
--usage: sp_IndexServer 'microsoft'
create procedure sp_indexServer @.input char(255)
as
declare @.HResult int
declare @.objIxsso int
declare @.RecordSet int
declare @.recordCount int
declare @.Count int
declare @.fields int
declare @.path varchar(100)
declare @.vpath varchar(100)
declare @.doctitle varchar(100)
declare @.size varchar(100)
declare @.filename varchar(100)
declare @.write varchar(100)
declare @.rank varchar(100)
declare @.hitcount varchar(100)
EXEC @.HResult = sp_OACreate 'Ixsso.query', @.objIxsso Out
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Query', @.input
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'catalog', 'web'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Columns', _
'path, vpath, doctitle,size,filename,write,rank,hitcount'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'MaxRecords', '200'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'SortBy', 'rank[d]'
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
exec @.HResult =sp_OAMethod @.objIxsso,_
'CreateRecordSet("nonsequential")',@.recordSet OUTPUT
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
EXEC @.HResult = sp_OAGetProperty @.recordSet, 'RecordCount',_
@.RecordCount output
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.recordset
RETURN
END
if @.recordcount =0
print 'No matches found'
else
begin
print convert (varchar(5),@.recordcount) +' matches found'
SET @.Count = 1
WHILE ( @.Count <= @.RecordCount )
BEGIN
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,0
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.path output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,1
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.vpath output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,2
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.doctitle output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,3
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.size output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,4
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.filename output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', @.fields out,5
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.write output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', @.fields out,6
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.rank output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', @.fields out,7
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.hitcount output
print @.path +', ' +@.vpath+', '+@.doctitle+', '+@.size +', '+_
@.filename +', '+@.write+', '+@.rank+', '+@.hitcount
EXEC @.HResult =sp_OAMethod @.RecordSet, 'MoveNext'
select @.count=@.Count+1
END
print @.count
EXEC @.HResult = sp_OADestroy @.fields
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.fields
RETURN
END
END
EXEC @.HResult = sp_OADestroy @.recordset
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.recordset
RETURN
END
EXEC @.HResult = sp_OADestroy @.objIxsso
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MadMax" wrote:

> Hi all,
> I'm making some experiments with the Full Text features offered by
> MS-SQLServer (MSSEARCH) and the Indexing Service.
> Querying MSSEARCH, I can limit the result using top_n_by_rank
> in CONTAINSTABLE / FREETEXTTABLE.
> I wasn't able to find out anything I can use to get the same result querying
> the Indexing Service as a linked server.
> If I try the following:
> SELECT Q.*
> FROM OpenQuery(FileSystem,
> 'SELECT TOP 100 rank, FileName, Size, DocAuthor, DocTitle
> FROM TESTCATALOG..SCOPE('' "" '')
> WHERE FREETEXT(''enhacing security'')
> ORDER by rank') AS Q
>
> I got an error:
> Server: Msg 7321, Level 16, State 2, Line 1
> [OLE/DB provider returned message: Incorrect syntax near '100'. Expected ',', '.', FROM. SQLSTATE=42000 ]
> [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare returned 0x80040e14].
> (I guess TOP is not supported by syntax).
> Of course I can do something like this:
> SELECT TOP 100 Q.*
> FROM OpenQuery(FileSystem,
> 'SELECT rank, FileName, Size, DocAuthor, DocTitle
> FROM TESTCATALOG..SCOPE('' "" '')
> WHERE FREETEXT(''enhacing security'')' ) AS Q
> order by Q.rank desc
> but in this case (I think..) all the matching rows are returned by
> Indexing Service and then SQLServer get the first N rows discarding
> the rest.
> Do you know if there is some way to instruct the Index Service
> (i.e. MSIDXS provider) to return only the first N best matches ?
> Thanks a lot for your help
> MadMax

Friday, February 24, 2012

Linked server (Access file)

Linked server (Access file)

Linked server (Access file)

Linked Server -> Interbase

Linked Server & XACT_ABORT

Linked Server & XACT_ABORT

Linked Server & XACT_ABORT

Linked server & transaction connection

Linked Server & SQL Auth

Linked Server "NOLOCK" problem on SQL 2000

Linked Server "NOLOCK" problem on SQL 2000

Linked Server "NOLOCK" problem on MS SQL 2000

Linked Server "NOLOCK" problem on MS SQL 2000

Linked Server -- unexpected return data length

Linked Server -- unexpected return data length

Linked Server - Turn Off Constraints

Linked Server - Trigger problem

Linked Server - sp_tables_ex

Linked Server - sp_tables_ex

Linked Server - sp_tables_ex

Linked Server - server name same as instance name

Linked Server - server name same as instance name

Linked Server - server name same as instance name

Linked Server - Security Problem

Linked Server - Query

Linked Server - On Connection Error Resume

Linked Server - OLE/DB Provider MSDAORA

linked server - MS Access - database pwd

linked server - MS Access - database pwd

linked server - MS Access - database pwd

Linked Server - Limiting Index Search Result

Sql Linked Server

Blog Archive

About Me