Hello can some one explain this to me and give me some advice. I have sqlserver1 and sqlserver2 I have a linked server set up [ipaddress] on both servers. When I pass a variable to the stored proc or the query it takes up to 20 seconds to return 1 row but if I replace my varibles like email='sqlboy@coxnet' and firstname='ted' and lastname='clien' it returns the one row im looking for in about 2 seconds but if I pass email =@email ,fname=@fname,lname=@lname I get the 20 second thing. The query is a stored proc being called from an asp app. I get the same results when I run this in query analyzer.
IF EXISTS (SELECT TOP 1 email
FROM [ipaddress].{database}.dbo.{tablename}--server1
WHERE email = @email )
BEGIN
set nocount on
SELECT TOP 1 email,
FIRSTNAME,
MIDDLENAME,
LASTNAME,
TITLE,
COMPANYNAME,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIP,
COUNTRY
FROM [ipaddress].{database}.dbo.{tablename} --server2
WHERE email in(SELECT EMAIL
FROM [ipaddress].{database}.dbo.{tablename} where email=@email--server1)
AND RecordStatusID ='1' and FirstName=@fname and LastName=@lname
END
ELSE
IF EXISTS (SELECT top 1 email,
FIRSTNAME,
MIDDLEINITIAL,
LASTNAME,
TITLE,
COMPANYNAME,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIP,
COUNTRY
FROM {databases}.dbo.attendees--server1
WHERE email =@email and FirstName=@fname and LastName=@lname)
BEGIN
SELECT TOP 1 email,
firstname,
MIDDLEINITIAL AS MIDDLENAME,
lastname,
title,
companyname,
address1,
address2,
city,
state,
zip,
country
FROM {database}.dbo.attendees --server1
WHERE email in (SELECT DISTINCT email
FROM server1.dbo.ebooksrequests--server1
where email=@email) and email=@email and FirstName=@fname and LastName=@lname
SET NOCOUNT OFF
I have 2 servers (say MAINSRV e SECSRV) running SQL2000 Standard SP3 on Windows 2000 Advanced within a NT (!) domain and each server is linked to the other.
My problem is that if I run a query returning few dozens of rows like:
SELECT * FROM MAINSRV.DbName.dbo.TblName TBLA WHERE Fieldx = 'anyval'
from a client connected to the SECSRV server, it takes something like 35 minutes to complete, while the same query completes in no time when run on clients connected to MAINSRV.
Even the simplest SELECT Count(*) FROM... takes more than one minute from SECSRV while completing in a fraction of second from MAINSRV.
I tried to change the linked server security options (SQL/Windows), but the remote query remains slow.
There are no locks active on the table, both the servers have almost no load (CPU less than 10%, when tested) and the query returns just a few KBytes, so communication overhead will not be the problem.
Any suggestions will be very appreciated, thank you!!!
OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired". [SQLSTATE 01000]
A linked Server was set up against a remote database to backup a database and it runs perfectly well when executed directly against the remote server but returns the above error when set up through Sql Server Agent. Permissions are good as the step returns success. I reset the query timeout property to zero but error persist. What else should I be looking at to make this work?
I need to push 40k rows daily to an Oracle database. I am on a Win2k Pro box with the 8.1 client and ADO 2.6 loaded. I've configured the linked server with the Oracle as well as the Microsoft provider and it takes over a minute to push just 24 rows, less than 100 bytes each, at it. Any suggestions?
I've connect some .DBF file to SQL server 7 with linked server option. My table have about 200.000 records. I've tried with ODBC and Jet 4.0 option (found in www.mssqlserver.com) but I've very slow response if I use a "where" in the select instance. In fact I've the response after 10 seconds and during this seconds the processor go to 100%. Does anyone found a most speed way to do this?
I have a linked server set up on my local SQL 2000 instance. I try and run an update against an SQL 2005 database and it take 29 seconds. I checked the execution plan and it says it takes the entire time on the Remote Scan. Is there something I need to do to speed this up? There is an index on the PK that I am searching against.
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected) Data access (selected) RPC (not selected) RPC Out (not selected) collation name connection timeout query timeout
I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.
I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.
What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.
I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.
It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
Creating a web application. Running a simple query "SELECT username FROM vwCustomer"
vwCustomer is a view built on top of an Access DB which is set up as a linked server. Within SQL Server that view responds immediately. But when I try to access it from an ASP page it takes over 20 seconds to respond.
The following T-SQL 2005 snippet executes a remote sproc on an Oracle database. DECLARE @OutParam VARCHAR(5) EXEC ('{Call getnextpin(1)}') AT ALTTEST GetNextPin takes a single input parameter. I need to modify the above code snippet to return the next PIN number into @OutParam. Any ideas? Any help appreciated.
I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.
I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.
The problem is: only 32 records returned.
May I ask if there is any problem to this linked server setup?
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
I previously posted re. this, but thought I'd try again with a summary offacts.I have an Access 2000 MDB with a SQL Server 7 back end. There is a view thatis linked to the database via ODBC and has been in place for several yearswithout any performance problems.Recently I added a couple of fields to the output of the view, and it becamevery slow when scrolling. When just opened in the database window, thelinked view takes about a second to scroll down one screen. When opened inthe form (in Continuous Form view), it takes about 2-3 seconds. It used toscroll just about instantaneously.I tried removing the few fields I added to restore the view to its previousform, but it had no effect. The view was still much slower than it had been.The total number of records returned from the view is about 1300, so it'snot a large number of records. The view has about 25 fields.I found that when I link the view in the MDB without specifying a uniqueindex, it scrolls very quickly -- almost instantaneously. But when I specifythe unique index, it is slow. Since the view needs to be edited, it needsthe unique index defined.As noted, it's been in place for years, with a unique index defined, yetwithout the slowness. Any ideas as to what might have caused this and whatmight be done would be appreciated. I've included the SQL for the viewbelow.Thanks,NeilSQL FOR MAIN VIEW:SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,INVTRY.attFirstEdition, INVTRY.attSigned,ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,INVTRY.PRICE, INVTRY.Web, INVTRY.Status,INVTRY.WebStatusPending, INVTRY.ActivateDate,INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,INVTRY.AllowDuplicate, INVTRY.WebAction,INVTRY.WebActionPending, INVTRY.DateModified,INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,INVTRY.HImage, INVTRY.AdCode,CASE WHEN INVTRY.WebAddedBatchID IS NOT NULLTHEN - 1 ELSE 0 END AS OnWebFROM vwInventory_Dupes INNER JOIN(WebStatus INNER JOIN(INVTRY INNER JOINtabStatus ON INVTRY.Status = tabStatus.Status) ONWebStatus.WebStatus = INVTRY.Web) ON(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND(vwInventory_Dupes.TITLE = INVTRY.TITLE)WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)= 1))SQL FOR vwInventory_Dupes:SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,Cast(attFirstEdition AS tinyint) FirstEd,Cast(attSigned AS tinyint) Signed,ISNULL(INVTRY.attSignedPD, ' ') SignedCond,INVTRY.YRPUB YearPubFROM WebStatus INNER JOIN(INVTRY INNER JOINtabStatus ON INVTRY.Status = tabStatus.Status) ONWebStatus.WebStatus = INVTRY.WebWHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)= 1))GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUBHAVING (((COUNT(INVTRY.[INDEX])) > 1))
I have a database and when I run a query on it the query takes 10 minutes to complete. I am running the following query
SELECT t103.cs_flag, t103.pr_flag, SUM (t103.amount), COUNT (t103.record_id) FROM br_data t103 WHERE t103.acct_id = 12 AND (t103.state = 3 OR t103.state = 7) GROUP BY t103.cs_flag, t103.pr_flag
The br_data table doesnt seem to be using its indexes ?? And it has around a million records. Now when I export the database and import on to another SQL server and then run the same query as above it only takes 1 or 2 seconds.
On the server that we are having problems with I have tried to re-build the indexes using DBCC DBREINDEX (br_data,' ',0) but this hasnt helped. I have also tried backing up the database, delete the database then restore, this also hasnt helped. I have no idea why the query runs slow on the original box, but then quick when I transfer it to another server??
Both servers are running windows 2003 with SQL 2000 SP4. There are no resorrce problems such as CPU / memory, Any ideas??
Dear friends,I have a problem with a simple select statement and I don't know why it is happening.I have 2 tables, Fees and FeesDataRoles. Fees presents all the fees and FeesDataRole is a middle table between Fees and Roles table. So each fee can have multiple Roles and a Role can have many Fees.Now I have a select statement:Select *From Fees Inner Join FeesDataRoles ON Fees.FeeID = FeesDataRoles.FeeIDWhere (FeesDataRoles.DataRoleID = @DataRoleID) AND (FeesDataRoles.RecordStatus = 1 ) AND (FeesDataRoles.ValidFrom >= getdate() ) AND ( FeesDataRoles.ValidTo <= getdate() OR FeesDataRoles.ValidTo is null)Now it shouldn't take that long to execute this procedure but surprisingly sometimes when I insert a value to the table and then execute this store procedure it does now show the data just added. Very strange.....!!!!I ran the procedure 5 times after inserting an item and nearly 1 out of 5 does not return the right result righ. ( It does not include the recently inserted rows)Anyone have any idea....?I used Tuning Advisor, no sugestion. I change the clustered index in FeesDataRoles from FeesDataRoleID(the primary key of the table) to DataRoleID to increase the performance, still it happens sometimes.Is my Where clause so costly that cause this problem.Please help. I really appreciate your help.Regards,Mehdi
i have created an RPC from my SQL server which queries a database of a linked server (remote server). the query result is very very slow. (it is a mis-size query, with many JOINs on tables with many entries). Let's say it takes about two minutes to get about 3000 results. The query uses five tables in the database of the linked server, runs a few (let's about 5-8) JOIN clauses and selects the entries. except for two tables (out of 8), each table has about 1000-2000 entries. the two have about 40,000 entries. Is this normal?! Is there anyway i can optimize my query? i also tested my query and asked for only 100 results as opposed to all of the 3000. there was only 2-3 second difference in getting the results back, which indicates that it is not the remote connection but the query itself which is slow.
I have a very unusual situation - we converted a client from DB2 7.2 to MS SQL Server 2000, SP3. There is one report that runs very quickly when ran on the Database Server, but it takes a long time to complete when it is ran from a client system. This query is ran from within the application and not from within Query Analyzer.
Has anyone else here ever encountered this issue? What did it turn out to be? I am leaning away from it being a network issue.
Hi All,I have a table that currently contains approx. 8 million records.I'm running a SELECT query against this table that in somecircumstances is either very quick (ie results returned in QueryAnalyzer almost instantaneously), or very slow (ie 30 to 40 seconds toreturn results), and I'm trying to work out how I improve performance.Essentially the query I'm running is nothing more complex than:SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id[tier] is a smallint column with a non-clustered, non-unique index onit. [member_id] is a numeric column with a clustered, unique index onit.When I supply a [tier] value of 1, it returns results instantaneously.I have no idea if this is meaningful, but the tier = 1 records wereloaded first into the table, and comprise approximately 5 millionrecords.When I supply a [tier] value of 2, the results take 30 to 40 seconds.tier =2 records were loaded second, and comprise approximately 3million records.I've tried running an execution plan, and while I'm no expert, itappears to me that the index on tier isn't being used, even if I use:tier = CAST(2 as SMALLINT)I'm wondering if anyone can give me ANY advice on how to get anybetter performance out of this SELECT statement?Also, out of curiosity, can a disk defragment have a positive impacton SELECT query performance?Any help very much appreciated!Much warmth,Murray
I have successfully been able to use a SELECT query to see tables on a remote (Linked) server. Now, I want to be able to join those records with a WHERE clause to the same tables I have on my local database. The idea here is to be able to see current data on my local database(which is not current data), which resides in the remote database(which is current data). Here is what I have so far:
SELECT top 10 * from LinkServer.MC_Card.webuser.POS_TX
But, I'm not sure how to SELECT from two tables. Would I do a SELECT from the local database WHERE all records = records on the remote database?
Not sure how to do this. Is this what would be considered a Distributed Query? And how would I make this work with joins like the existing joins I have to the tables in my local database?
This is new territory for me. Sorry if this is such a newbie question.
I'm using MS SQL Server Express and I've added a linked server. (I created the linked server by right mouse clicking on Server Objects, selecting SQL and entering the SQL2 as the name.) Now I'm trying to query a linked table. The following query works.
SELECT * FROM SQL2.PA.dbo.Counties
This one doesn't:
Select * from OPENQUERY(SQL2,"SELECT * FROM SQL2.PA.dbo.Counties")
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'SELECT * FROM SQL2.PA.dbo.Counties'.
I have two tables in a linked server that I am trying to accomplish the following:
Server is called Medic, schema is informix, catalog is v001 table names are findet and findhdr
1) join indices (patkey and invno) 2) distinct count (finhdr.patid) 3) sum currency(findet.amount) 4) within a date range(findet.ar_date) 5) having specific criteria (findet.proccode = "99201 thru 99205")
Hi guys I need some help connecting to a LINKED SERVER. I have setup the LINKED SERVER via Enterprise Manager. This is what I did. I know this is a difficult thing to explain so I will try my best and attempt to give you a good understanding of the scenario.
1. I have three servers named DEVUK, DEVUS and DEVASIA. 2. DEVUK I can connect to via my computer and contains a databases called GOD. 3. I went into DEVASIA server and went to Administrative Tools > New ODBC Connection and called it DEVASIAUK. 4. Setup everything in there and tested the connection which completed successfully. 5. I am now on my computer where I have connected to DEVASIA and gone into Server Objects > Linked Servers. 6. I created a new LINKED SERVER and under GENERAL TAB have stated DEVASIAUK and Provider as SQL Server. 7. Security I have used NT_AUTHORITYand option BE MADE USING THE LOGINS CURRENT SECURITY CONTEXT.
I have the SQL Command: SELECT * FROM DEVASIAUK ... tblLocalTable
Am I doing this right? Or am I missing something here? I am new to Linked Servers but it shows it is executing a query but then it says Login Failed.
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
Is there a way to set four part reference used in distributed query to a short name in local server, so for each query you do not have to type the LONG four part ref. repeatedly to linked server objects ?
I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.
For example, the following statement works just fine:
SELECT * FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")
But If I try to use:
DECLARE @DynamicSQL VARCHAR(1000), @StartDate VARCHAR(10), @EndDate VARCHAR(10) SET @StartDate = '2000.01.01' SET @EndDate = '2000.01.31'
SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'" --SELECT @DynamicSQL
SELECT * FROM OPENQUERY(OracleLinked, @DynamicSQL)
it does not work.
Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)
Then is there any way I can accomplish what I want to on the Remote server?
I have a delete query on a linked server that never ends. I can do a select where ID = x and it returns lickity split but when I do a delete where id = x it never comes back? Any Clues???
I am now facing a problem related to the linked server. I created the connection between server A and B as linked servers. When I execute the following SQL statement on server A,
select * from B.database1.dbo.tableA where id ='12345'
I can get the results within couple of seconds. But the similar query would take several minutes if I switch the server name in the query from B to A and run it on server B! The tables on server A and B actually have the same sizes and the same indexes.
Do you have any clues and suggestions on this issue?
where 'Testaccess' is the name of the linked server and 'test' is the name of the ODBC connection. [The ODBC connection stores the name of the Access datbase.]
I can run sp_tables_ex and view the table_schem, table_cat etc etc
BUT when I try to run a query on the linked server, I get the following message:
"7312 - Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema."
I am attempting/using the following command specifying the linked server name.database name.owner.table name in the FROM clause:
I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name) The Provider string, Location and Catalog I leave blank Under security, I assign all users to be a valid username/password
I can then see the tables in EM, but when I try to query them in QA I get this
Select * from db2db...customer
Server: Msg 7313, Level 16, State 1 Line 1 Invalid Schema or catalog specified for provider 'MSDASQL'
I am using SS7.0, and DB2 connect V7.1 I do not have/use SNA server
My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data
any ideas ? I think I need to enter something in the catalog box, but what ?
We want to read data from a DB2 view so we set up a linked server in SQL7 (ODBC via Neon's Shadow Direct) but when we issue the following type of query from Query analyzer: select * from openrowset(my_db2link,'select * from test.myview where mycol = ''value''') it just hangs, and we can't kill the process (the Enterprise manager 'KIll Process' button has no effect!) - the only way we can get rid of these queries seems to be to stop/restart SQL Server When we look at current activity in Enterprise manager the process seems to be waiting for a resource (either MISCELLANEOUS or PAGEIOLATCH) The query works OK if I run it on the server using Shadow direct, so the error must be between SQL Server and Shadow direct Has anyone seen this error before? Thanks in advance, John