I'm trying to link a drill through report based on a text box that is derived from a "Switch" function. I only want the drill through capability if the source for the textbox is a certain field from my dataset.
ex: =Switch( Parameters!SummaryBy.Value = "Division", Fields!BranchDivision.Value , Parameters!SummaryBy.Value = "Region", Fields!BranchRegion.Value, Parameters!SummaryBy.Value = "Branch", Fields!BranchNbr.Value, Parameters!SummaryBy.Value = "Satellite", Fields!BranchSatId.Value, Parameters!SummaryBy.Value = "Loan Officer", Fields!LONbr.Value)
so if I only wanted the above textbox to navigate to a subreport if it returned LONbr as the source, what expression would I put in the "Jump to Report" advanced property?Try =IIF(Parameters!SummaryBy.Value = "Loan Officer", <your subreport>,
Nothing).
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JKS" <JKS@.discussions.microsoft.com> wrote in message
news:809BE35A-3B2C-4F50-8248-2DAF1709DCF4@.microsoft.com...
> I'm trying to link a drill through report based on a text box that is
derived from a "Switch" function. I only want the drill through capability
if the source for the textbox is a certain field from my dataset.
> ex: =Switch( Parameters!SummaryBy.Value = "Division",
Fields!BranchDivision.Value , Parameters!SummaryBy.Value = "Region",
Fields!BranchRegion.Value, Parameters!SummaryBy.Value = "Branch",
Fields!BranchNbr.Value, Parameters!SummaryBy.Value = "Satellite",
Fields!BranchSatId.Value, Parameters!SummaryBy.Value = "Loan Officer",
Fields!LONbr.Value)
> so if I only wanted the above textbox to navigate to a subreport if it
returned LONbr as the source, what expression would I put in the "Jump to
Report" advanced property?
Showing posts with label source. Show all posts
Showing posts with label source. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
servers
We are creating a newServer grabbing data from an extrnal data source.
We need to perform regular audits to verify the record counts (for now) are the same.
We have 2 physically separate SQL servers 1 with old 1 with new.
I have created a linked server but when i do a query on the linked server the performace is pathetic (2mins 12 secs) to do a simple count.
Is there a better way, or can improv the perf?
Thanks
Greg CSomething is grieviously broken, but I don't have enough information to tell you what is broken or how it should be fixed.
The first step would be to attempt the same query the new server using Query Analyzer. The purpose of this step is to determine if the problem lies in the connection between the two machines or within the SQL Server itself. If the query runs poorly in Query Analyzer, then the problem is in the connection between the machines (something is impeding SQL communications between them). If the query runs acceptably in Query Analyzer but does not using the linked server, then the problem is in the linked server.
-PatP|||Yep thanks, field i was counting wasnt indexed.. thanks|||That would be what we call a "bad thing" for lack of a better term! I'm glad to know that the answer was so simple.
-PatP
We need to perform regular audits to verify the record counts (for now) are the same.
We have 2 physically separate SQL servers 1 with old 1 with new.
I have created a linked server but when i do a query on the linked server the performace is pathetic (2mins 12 secs) to do a simple count.
Is there a better way, or can improv the perf?
Thanks
Greg CSomething is grieviously broken, but I don't have enough information to tell you what is broken or how it should be fixed.
The first step would be to attempt the same query the new server using Query Analyzer. The purpose of this step is to determine if the problem lies in the connection between the two machines or within the SQL Server itself. If the query runs poorly in Query Analyzer, then the problem is in the connection between the machines (something is impeding SQL communications between them). If the query runs acceptably in Query Analyzer but does not using the linked server, then the problem is in the linked server.
-PatP|||Yep thanks, field i was counting wasnt indexed.. thanks|||That would be what we call a "bad thing" for lack of a better term! I'm glad to know that the answer was so simple.
-PatP
server: Performance Issue with OpenQuery & SP best practices
We are using the linked server feature of SQL Server 2000 to execute queries
against SQL Server data source which is hosted on the remote computer. In
our analysis, it was found that OpenQuery() takes the a lot of time causing
the system to have a performance hit.
We are looking for the following answers:
a.. Is there any alternative to linked server in SQL Server to fetch the
recordset from the SQL database hosted on the remote machine?
b.. If we use linked server approach, then is there any way to optimize
OpenQuery() call?
c.. What are the best practices to write the stored procedures? Is it
recommended to use temporary tables and cursors?
Thanks
AshishAshish Sharma
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
Replications
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
Tunning the query.Make sure that you define proper indexes
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
It depends on your business requirements. You can use a temporary tables
within a stored procedure , however make sure that #table has indexes and a
stored
procedures does not get recompile each time as you calling it .
Cursors should be your last resort ,because you will be benefit from set
based solution in terms of perfomance
11
"Ashish Sharma" <ashish.sharma@.honeywell.com> wrote in message
news:%23M%23%23r5$nGHA.4604@.TK2MSFTNGP02.phx.gbl...
> We are using the linked server feature of SQL Server 2000 to execute
> queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time
> causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>|||Ashish Sharma wrote:
> We are using the linked server feature of SQL Server 2000 to execute queri
es
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time causin
g
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>
You first need to identify why it takes so long for the OPENQUERY query
to complete. Are the necessary indexes in place on the remote table(s)
to support the query? How much data is the query returning? You're
essentially copying data over the network, the more data to move, the
longer it's going to take.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
against SQL Server data source which is hosted on the remote computer. In
our analysis, it was found that OpenQuery() takes the a lot of time causing
the system to have a performance hit.
We are looking for the following answers:
a.. Is there any alternative to linked server in SQL Server to fetch the
recordset from the SQL database hosted on the remote machine?
b.. If we use linked server approach, then is there any way to optimize
OpenQuery() call?
c.. What are the best practices to write the stored procedures? Is it
recommended to use temporary tables and cursors?
Thanks
AshishAshish Sharma
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
Replications
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
Tunning the query.Make sure that you define proper indexes
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
It depends on your business requirements. You can use a temporary tables
within a stored procedure , however make sure that #table has indexes and a
stored
procedures does not get recompile each time as you calling it .
Cursors should be your last resort ,because you will be benefit from set
based solution in terms of perfomance
11
"Ashish Sharma" <ashish.sharma@.honeywell.com> wrote in message
news:%23M%23%23r5$nGHA.4604@.TK2MSFTNGP02.phx.gbl...
> We are using the linked server feature of SQL Server 2000 to execute
> queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time
> causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>|||Ashish Sharma wrote:
> We are using the linked server feature of SQL Server 2000 to execute queri
es
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time causin
g
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>
You first need to identify why it takes so long for the OPENQUERY query
to complete. Are the necessary indexes in place on the remote table(s)
to support the query? How much data is the query returning? You're
essentially copying data over the network, the more data to move, the
longer it's going to take.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
server: Performance Issue with OpenQuery & SP best practices
We are using the linked server feature of SQL Server 2000 to execute queries
against SQL Server data source which is hosted on the remote computer. In
our analysis, it was found that OpenQuery() takes the a lot of time causing
the system to have a performance hit.
We are looking for the following answers:
a.. Is there any alternative to linked server in SQL Server to fetch the
recordset from the SQL database hosted on the remote machine?
b.. If we use linked server approach, then is there any way to optimize
OpenQuery() call?
c.. What are the best practices to write the stored procedures? Is it
recommended to use temporary tables and cursors?
Thanks
AshishAshish Sharma
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
Replications
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
Tunning the query.Make sure that you define proper indexes
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
It depends on your business requirements. You can use a temporary tables
within a stored procedure , however make sure that #table has indexes and a
stored
procedures does not get recompile each time as you calling it .
Cursors should be your last resort ,because you will be benefit from set
based solution in terms of perfomance
11
"Ashish Sharma" <ashish.sharma@.honeywell.com> wrote in message
news:%23M%23%23r5$nGHA.4604@.TK2MSFTNGP02.phx.gbl...
> We are using the linked server feature of SQL Server 2000 to execute
> queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time
> causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>|||Ashish Sharma wrote:
> We are using the linked server feature of SQL Server 2000 to execute queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>
You first need to identify why it takes so long for the OPENQUERY query
to complete. Are the necessary indexes in place on the remote table(s)
to support the query? How much data is the query returning? You're
essentially copying data over the network, the more data to move, the
longer it's going to take.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
against SQL Server data source which is hosted on the remote computer. In
our analysis, it was found that OpenQuery() takes the a lot of time causing
the system to have a performance hit.
We are looking for the following answers:
a.. Is there any alternative to linked server in SQL Server to fetch the
recordset from the SQL database hosted on the remote machine?
b.. If we use linked server approach, then is there any way to optimize
OpenQuery() call?
c.. What are the best practices to write the stored procedures? Is it
recommended to use temporary tables and cursors?
Thanks
AshishAshish Sharma
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
Replications
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
Tunning the query.Make sure that you define proper indexes
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
It depends on your business requirements. You can use a temporary tables
within a stored procedure , however make sure that #table has indexes and a
stored
procedures does not get recompile each time as you calling it .
Cursors should be your last resort ,because you will be benefit from set
based solution in terms of perfomance
11
"Ashish Sharma" <ashish.sharma@.honeywell.com> wrote in message
news:%23M%23%23r5$nGHA.4604@.TK2MSFTNGP02.phx.gbl...
> We are using the linked server feature of SQL Server 2000 to execute
> queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time
> causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>|||Ashish Sharma wrote:
> We are using the linked server feature of SQL Server 2000 to execute queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>
You first need to identify why it takes so long for the OPENQUERY query
to complete. Are the necessary indexes in place on the remote table(s)
to support the query? How much data is the query returning? You're
essentially copying data over the network, the more data to move, the
longer it's going to take.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Friday, March 9, 2012
server, oledb for odbc, slow performance on join to cache
I have a sql linked server pointed to a cache database. I created it
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
..
Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>
|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
..
Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>
|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
server, oledb for odbc, slow performance on join to cache
I have a sql linked server pointed to a cache database. I created it
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)|||Hi
I did not see an example of the first query in your previous post!
You may also want to specify only the column names required instead of (*)
which will reduce the size of the data being retrieved
SELECT X.ID, X.Col1, X.Col2
FROM OPENQUERY(Cache_test01, 'SELECT ID, col1, col2 FROM Table01 WHERE
ID=22 ') AS X
JOIN dbo.tblTable01AddOn A ON X.ID = A.ID
Have you tried using a temporary table to store the data from the openquery?
If the openquery can be restricted that should be better, although it may
mean resorting to dynamic SQL.
John
"steven@.ironcube.com" wrote:
> Hi John,
> I got posts all over this group this week as well as the cache. Sorry,
> I didn't see that anyone got back to me on this one. I actually have
> tried both syntax you mentioned above. I saw the same from both of
> them. Very slow.
> I contacted cache over the weekend and I'm hoping to see an email from
> them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
> I'm going to post back just so other in the same boat know what
> happened.
> Thanks for taking the time to post. I appreciate it. Have a good one.
> :-)
>
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)|||Hi
I did not see an example of the first query in your previous post!
You may also want to specify only the column names required instead of (*)
which will reduce the size of the data being retrieved
SELECT X.ID, X.Col1, X.Col2
FROM OPENQUERY(Cache_test01, 'SELECT ID, col1, col2 FROM Table01 WHERE
ID=22 ') AS X
JOIN dbo.tblTable01AddOn A ON X.ID = A.ID
Have you tried using a temporary table to store the data from the openquery?
If the openquery can be restricted that should be better, although it may
mean resorting to dynamic SQL.
John
"steven@.ironcube.com" wrote:
> Hi John,
> I got posts all over this group this week as well as the cache. Sorry,
> I didn't see that anyone got back to me on this one. I actually have
> tried both syntax you mentioned above. I saw the same from both of
> them. Very slow.
> I contacted cache over the weekend and I'm hoping to see an email from
> them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
> I'm going to post back just so other in the same boat know what
> happened.
> Thanks for taking the time to post. I appreciate it. Have a good one.
> :-)
>
server, oledb for odbc, slow performance on join to cache
I have a sql linked server pointed to a cache database. I created it
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors')
a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors')
a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
server with MySQL
Hi,
I have a problem , I created a linked server to mysql with following parameters:
New linked server: ServerName
Server Type: Other data Source: Microsoft OLE DB Provider for ODBC Drivers.
In the Provider String:
Driver={mySQL ODBC 3.51 driver};Server="ip";Port=3306;Option=17923;Stmt=;Database="database";Uid=root;Pwd="pwd"And I created a ODBC with mysql, and connection worked sucessfully!
The connection worked corretly until I' ve running a transact query in sql query analyzer, thats connects to MySQL and...
when I try to connect in SQL Server 2000 standard Ed. in linked Server, the following message appears to me now, and before this I was able to connect!
Server: Msg 7399, Level 16, State 1, Line 11
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 169 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider reported an unexpected catastrophic failure.
The query just make a select in MySQL, but I think that broke something...
What I have to do?
reply to my email: daniel.sarnes@.antilhas.com.br
Thanks a lot!
I am not familiar with MySql. But if you create a linked server object inside MySql that points to SQL Server, you need to contact MySql to see if that is supported scenario.Wednesday, March 7, 2012
server to Visual Foxpro
I am on a LAN and having trouble creating a linked server to a FoxPro
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','select * from master')
Any Suggestions
Message posted via http://www.droptable.comHi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via droptable.com" <forum@.droptable.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQ
droptable.com...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','select * from master')
Any Suggestions
Message posted via http://www.droptable.comHi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via droptable.com" <forum@.droptable.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQ
droptable.com...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.
server to Visual Foxpro
I am on a LAN and having trouble creating a linked server to a FoxPro
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','se lect * from master')
Any Suggestions
Message posted via http://www.sqlmonster.com
Hi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQLMonster.c om...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','se lect * from master')
Any Suggestions
Message posted via http://www.sqlmonster.com
Hi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQLMonster.c om...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.
server to Visual Foxpro
I am on a LAN and having trouble creating a linked server to a FoxPro
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','select * from master')
Any Suggestions
--
Message posted via http://www.sqlmonster.comHi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQLMonster.com...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.
data source located on a server. When I use create a new linked server
and set the Provider String to use local directory things work fine -
that is I see a list of tables in the Tables view. If I specifiy a
directory located on a server in the provider string I see no tables.
This works:
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=c:\foxdbf.dbc;
These do not work:
(Network Drive)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;
(Network Path)
Provider string: Driver={Microsoft Visual FoxPro
Driver};SourceDB=\\server\volume\foxdbf.dbc;
Similarly, when I run the following T-SQL statement it fails against a
server directory, is successful against a local directory!
Select * from openrowset('MSDASQL','Driver={Microsoft Visual FoxPro
Driver};SourceDB=G:\foxdbf.dbc;SourceType=DBF','select * from master')
Any Suggestions
--
Message posted via http://www.sqlmonster.comHi Rameshwar,
I don't have a lot of experience with linked servers, but many times with
questions like this it's a permissions issue. The account that the server is
running under needs permissions to the external directory.
Also, I read your other post where you said "I get the ODBC driver error as
it cannot open the .dbc file." (BTW, you're better off keeping all your
questions in one thread.) All VFP tables for version 6 and below are
readable via the FoxPro and Visual FoxPro ODBC driver; the latest is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates/ .
However, new data features were added in versions 7 and above (the latest is
VFP9) and tables with those features can only be accessed via the VFP OLE DB
data provider, downloadable from the same page as above. From your question
in this post I'm assuming that you were able to read the tables, at least
locally. Is that the case?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"rameshwar elka via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9a658f3fe1834473ac99f66118dd2cbe@.SQLMonster.com...
>I am on a LAN and having trouble creating a linked server to a FoxPro
> data source located on a server. When I use create a new linked server
> and set the Provider String to use local directory things work fine -
> that is I see a list of tables in the Tables view. If I specifiy a
> directory located on a server in the provider string I see no tables.
server to Sybase 11 problem
Attempting to create a Linked Server to a Sybase 11 db. Did the following
- Used DSEDIT to create a data source. Ping(Test) works
- Created a System DSN using the Sybase System 11 driver
- Created a Linked Server with following options
General Tab
- Provider as - MS OLE DB Provider for ODBC Drivers
- Data source as the DSN name created above
- No provider string
- Put in the Catalog name
Security Tab
- No local login
- Selected "be made using this security context"
- Specified remote login and password as the Sybase "sa" and pwd
Server Options Tab
- Checked only Collation compatible and Data Access checkboxes
On trying to see the list of tables, got the following error
Error 7399: OLE DB Provider 'MSDASQL' reported an error.
The provider reported an unexpected catastrophic failure.
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION.SQL Server is termintating this process.
OLE DB error trace[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider
reported an unexpected catastrophic failure.].
Any solution to this?
HI Mat,Any response to you query? Even I am facing the same problem. Can you help me out if you have a solution?..
Pls respond to ansathya@.yahoo.com id..
thanks in advance.
Sathya|||We looked into this at is seems like an issue in the sybase provider itself. I'm trying to get a contact there...you folks should try contacting them as well.|||We have got a solution for this from some sources.
Syabase has a known issue on this.
Here are the steps to resolve the problem.
1. In OBDC, while creating the DSN (with the Sybase server name as DSN name) the database name should not be given.
2. In the Linked server properties only the Data source should be given. Remote login (sa and pwd) and in server options only Collation compatible and Data Access are to be selected.
3. The default database for sybase 'sa' login was taken as the user database for which the tables are to be linked.
The above solution works perfectly without issues.
Hope it is helpful to all.
Regards
Geeta
Subscribe to:
Posts (Atom)