T-SQL (SS2K8) :: Extracting Large Tables From Offsite Linked Server
Oct 1, 2014
I work in Healthcare IS for Company A, but Company B is hosting one of our EMR programs for us. This was done on purpose, so that whether a patient is seen at one or the other, their medical history is more complete. However, this puts all of the data that I need to get to on a server across town that I can only access via Sql Server Management Studio as a linked server.
Now, in some ways, the performance has been better than I expected, but sometimes it behaves very erratically. I am using OPENQUERY to handle all of the pulls, and am not joining to any local tables, in order to maximize efficiency.
Here is some of the code I run, and what happens:
SELECT *
FROM
OPENQUERY([linkedservername],
'
SELECT *
FROM Encounter_ItemChild
[Code] ....
***The above query was programmatically generated by taking the IDs from the second query, and packing as many into the IN condition as possible. Each statement could hold only about 900 IDs, so around 70 queries get built...however, each one returns the records in question in 1-2 seconds.
My main question is...if the second query pulls all IDs from Encounter in a few seconds, and that query is used in the first query's WHERE clause, why does it spin and spin, while manually throwing the IDs in instead runs almost instantly?
I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need.
In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.
When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.
When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'
This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I've got a linked server to an attomix database. When i send an erroneous statements to the server an error message is returned to the Message pane is SSMS as below:-
OLE DB provider "MSDASQL" for linked server "noble" returned message "[Noble Systems Corp.][ATOMIX ODBC Driver]Atomix error - (-217)Column (crap) not found in any table in the query.
".Msg 7215, Level 17, State 1, Line 5, Could not execute statement on remote server 'noble'.
I'm trying to store this error using the ERROR_MESSAGE() function but i only get the second statement ('Could not execute statement on remote server 'noble'.') rather than the real error in the first line. The first line statement is displayed in black as a message rather than red for an error.
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
We have several hundred very simple ETL SSIS 2K8 package files (*.dtsx).
I'd like to be able to interrogate them to determine source and destination fields.
There's no great need to map source to dest or to extract data types.
So far, the most promising candidate is to load them using OPENROWSET into an XML field in a SS table.No problem there, but querying using OPENXML has me stumped.
The package files will change a couple of times per year, so the process needs to be repeatable with minimal manual intervention.
Interesting issue. I have the following CTE that JOINs some tables from a Linked Server which is our SAP data. This CTE is in a stored procedure and then executed via a SQL Server Agent Job on a timer (every 10 minutes). This ran fine for almost 20 hours and then dies with a multipart identifier could not be bound error (exact error below CTE).
Server running the job: SQL Server 2008 R2 (no SP)
Linked Server: SQL Server 2005 SP3 housing SAP
CTE:
WITH TaktValues ([Counter], NODE, PLNNR) AS ( SELECT MAX(plpo1.ZAEHL) AS [Counter], MAX(plpo1.PLNKN) AS NODE, plpo1.PLNNR FROM etl.PLPO plpo1 GROUP BY plpo1.PLNNR
[Code] ....
Error:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1008.AUFNR" could not be bound.
Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Tbl1008.AUFNR" could not be bound.
I have a script that inserts rows from a linked server. It basically looks like this:
use mydb; go insert into my.table (col1, col2, ...) select col1, col2, ... from LinkedServer.db.my.table;
The DDL for both tables is identical: 550 columns (de-normalized from a data warehouse), of which 548 are varchar(max). The remaining two are varchar(255). (not my design! but unchangeable at the moment).
Running my query raises an error:
Cannot create a row of size 9948 which is greater than the allowable maximum row size of 8060. The statement has been terminated.
Now, inserting rows on the table on the LinkedServer has never been an issue. (Since most columns are varchar(max), SQL has the option to store the data off-row.) The data is there, intact and whole. However, when pulling it into my target server using a LinkedServer (4-part naming), I get the error.
Three pairs of eyes have confirmed that the table definitions are identical on both source and target.
We are in the middle of re-designing few tables (namely transaction tables) that would store very large data and would be hosted on cloud (Azure). The old design of this product breaks transaction tables into monthly tables. i.e. say ORDERS Table would be physically broke into twelve monthly tables over a year like ORDERS0115 (mmyy), ORDERS0215 and so on.
We are in the opinion that keeping the entire transactions in one Table is better. Would like to know what's the best practices for transaction tables like the one mentioned above? Is it better to use one table with partitions. I read somewhere that partitions can slow down SELECT queries if not designed and thought properly.Since this would be hosted on cloud (Azure), do you think some additional things are to be taken care? How a site like Amazon keeps their transactions tables?
I would really apprecite if anyone could make a comment on this...
what i wanna know is.... there are two tables, called 'A' and 'B' with identical structures... is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2 * Records of table 'A' which not in table 'B' and * Records of table 'B' which not in table 'A'
In a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate) 2)Title(id, DocNo,Main_Title, Other_Title) 3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....) 4)Publisher(id,DocNo , Name,Publisedate,address) 5)Subject(id,DocNo,Subject) 6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?
I have a source table in the staging database stg.fact and it needs to be merged into the warehouse table whs.Fact.
stg.fact is not a delta feed it is basically an intra-day refresh.
Both tables have a last updated date so its easy to see which have changed.
It will be new (insert) or changed (update) data that I am interested in, there are no deletions.
As this could be in the millions of rows that are inserts or updates then this needs to be efficient.
I expect whs.Fact to go to >150 million rows.
When I have done this before I started with T-SQL Merge statement and that was not performant once I got to this size.
My original option was to do this is SSIS with a lookup task that marks the inserts and updates and deal with them seperately. However I set up the lookup tranformation the reference data set will have a package variable in the SQL commnd. This does not seem possible with the lookup in 2012! Currently looking at Merge Join transformation and any clever basic T-SQL that could work as this will need to be fast, and thats where I think that T-SQL may be the better route.
Both tables will have >100,000,000 rows Both tables have the last updated date The Tables are in different databases but on the same SQL Instance Each table holds 5 integer columns, one Varchar, one datatime
Last time I used Merge it was a wider table with lots of columns so don't know if this would be an option.
We have an existing BI/DW process that adds large chunks of data daily (~10M rows) to an existing table, as well as using Deletes to remove stale data. This scenario seems to beg for partitioning to support switching in/out data.
After lots of reading on this, I have figured out the mechanics of the switching, bit I still have some unknowns about the indexes needed to support this.
The table currently has several non-clustered indexes, including one on the partitioning column - let's call that column snapshotdate. Fortunately there are no FKs involved, and no constraints.
Most of the partitioning material I see focuses on creating a clustered PK to assist with switching. Not sure if this is actually necessary, but assume I create one using an Identity column (currently missing) plus snapshotdate.
For the other non-clustered, non-unique indexes, can I just add the snapshotdate to the end of the index? i.e. will that satisfy the switching requirement?
Hello,Currently we have a database, and it is our desire for it to be ableto store millions of records. The data in the table can be divided upby client, and it stores nothing but about 7 integers.| table || id | clientId | int1 | int2 | int 3 | ... |Right now, our benchmarks indicate a drastic increase in performanceif we divide the data into different tables. For example,table_clientA, table_clientB, table_clientC, despite the fact thetables contain the exact same columns. This however does not seem veryclean or elegant to me, and rather illogical since a database existsas a single file on the harddrive.| table_clientA || id | clientId | int1 | int2 | int 3 | ...| table_clientB || id | clientId | int1 | int2 | int 3 | ...| table_clientC || id | clientId | int1 | int2 | int 3 | ...Is there anyway to duplicate this increase in database performancegained by splitting the table, perhaps by using a certain type ofindex?Thanks,Jeff BrubakerSoftware Developer
Hi @ll, I'm trying to extract data from a database where columns have names with spaces inside, I show an example:
select "total amount" from sales;
This query shows me all data stored in the "total amount" column in table sales, ok no problem.
Well, the problem comes when I try to execute this query with osql because the output text file where I put the data only have the text "total amount".
I use the query as I wrote before stored in a text file.
I'm doing something like this:
osql -U user -P passwd -i query.sql -o textfile.txt
I have these 3 tables in a database for printed chamber music works:
Table INSTRUMENTS: List of musical instruments Column ID -- int, primary key Column sInstrument -- string
Table PTRS_INSTRUMENTATION: Column ID -- int, primary key Column lInstrument -- int, points to a row in table INSTRUMENTS via ID Column lMainItem -- int, points to a row in table MAINITEMS via ID Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item
Table MAINITEMS: List of chamber music works Column ID -- int, primary key Column sItem -- string
Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).
I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),
And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).
I currently am in charge of creating a Sharepoint 2003/Exchange 2003 ticketing system. We have a client database (MS Access 2003) that has a linked table to our Exchange store, retrieving information we enter in Exchange regarding specific clients. I would like to move this database to SQL Server for performance reasons. Soon there will be many people accessing this database and I don't want the bottle neck to be Access.
The only quirk I can think of is, can one link a table from SQL Server to Exchange. Or for that matter, can SQL Server have linked tables at all? One solution I can think of is to create a System Service that runs on the server, to periodically update the SQL table with the information in the Exchange store. But that could become costly over time with large amounts of network bandwidth being used for unnecessary updates.
Is there maybe another solution I'm not seeing? Should I just stick with the Access database?
HiI received the below error when trying to run an update from one SQLServer to another.I can insert and select. I cannot delete or update. The permissionshave been changed to allow the linked server user to carry outeverything, the linked servers are working but we cannot change thedata.We are stumpped and your help would be appreciated.Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"charmfin"."charm"."TMP_BATCHPOSTING"' from OLEDB provider 'SQLOLEDB'. The provider could not support a row lookupposition. The provider indicates that conflicts occurred with otherproperties or requirements.[OLE/DB provider returned message: Multiple-step OLE DB operationgenerated errors. Check each OLE DB status value, if available. Nowork was done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowsetreturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=TrueSTATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUTVALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyIDVALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocateVALUE=True STATUS=DBPROPSTATUS_CONFLICTING],[PROPID=DBPROP_IRowsetChange VA...Thanks in advance.Simon
I just finished up setting up linked server from one of my database machines to another. After futzing to get permissions just right, it works great for many things:
select top 5 * from altai.prep.dbo.simulation_status 0 Prepping Simulation is being prepped by the owner. 1 Prepped Simulation has been prepped by the owner. 2 Checking Simulation is being checked by the owner. 3 Checked Simulation has been checked. 4 Running Simulation is running.
The trouble is, I have another table in this database called that begins with "S": Staging_Transition_States. If I try to access this table via the linked server, I get:
select * from altai.prep.dbo.Staging_Transition_States
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "altai" does not contain the table ""prep"."dbo"."Staging_Transition_States"". The table either does not exist or the current user does not have permissions on that table.
This table exists, sp_help for this table is below. Why doesn't this table show up in the output of sp_tables_ex? As far as I can tell, permissions for the tables that work and this table are identical.
A server I'm working on has a very unique situation, where user tables and production tables reside on the same database. Users update / create tables or populates these tables, so it can't be a table-specific trigger. However, they give a new meaning to "kamikaze pilots" as it's not uncommon for them to "accidentally" update / insert / delete 500,000,000 + records in a single statement. I've tried educating them to use batching, but to no avail, so now I'm forced to stop these statements BEFORE they execute, based on rowcount, as they fill up the database log so quickly that it goes into recovery mode (It has a 200GB log file - insane, I know).
I recon the mosts transactions allowed should be 1,000,000 records in a single statement. Looking for database trigger to stop them from executing statements with large records?
Create table code ( id identity(1,1) code parentcode internalreference)
There are other columns but I have omitted them for clarity.
The clustered index is on the ID.
There are indexes on the code, parentcode and internalreference columns.
The problem is the table stores a parentcode with an internalreference and around 2000 codes which are children of the parentcode. I realise the table is very badly designed, but the company love orms!!
The table currently holds around 300 millions rows.
The application does the following two queries to find the first internalreference of a code and the last internal refernce of a code:
--Find first internalrefernce SELECT TOP 1 ID, InternalReference FROM code WHERE ParentCode = 'M222' Order By InternalReference
-- Find last ineternalreference SELECT TOP 1 ID, InternalReference FROM code WHERE ParentCode = 'M222' Order By InternalReference DESC
These queries are running for a very long time, only because of the sort. If I run the query without the sort, then they return the results instantly, but obviously this doesn't find the first and last internalreference for a parentCode.
I realize the best way to fix this is to redesign the table, but I cannot do that at this time.
Is there a better way to do this so that two queries which individually run very slowly, can be combined into one that is more efficient?
I am running the follwing query to select data from a local and linked server using a right outer join as follows and get the error message shown below the query. How can this be corrected please? sg_q5 is local and sg_qt1 is defined as a linked server on sg_q5. Non join direct select commnads from sg_Q5 on the linked server works fine.
>> Query :
Select name from sysobjects RIGHT OUTER JOIN sg_qt1.globaldb.dbo.sysobjects ON sysobjects.dbo.Name = sg_qt1.globaldb.dbo.sysobjects.name where type = 'u' and name <> 'dtproperties' and name <> 'Rowcounts'
Error Message:
Server: Msg 117, Level 15, State 2, Line 4 The number name 'sg_qt1.globaldb.dbo.sysobjects' contains more than the maximum number of prefixes. The maximum is 3.
I am trying to create a linked server to some dbase 5 tables. I have read several other posts about using the OPENROWSET option and that does work however, I would like to be able to create a linked server for easier access.
End Goal: I am developing an ASP based app that needs to join a DB5 table to a SQL table.
Any help on the linked server properties in Enterprise Manager for dbase5 or on the proper sp_addlinkedserver function for dbase5 would be highly appreciated.
In enterprise manager, I can browse the objects by clicking on "Tables" under the selected linked server, and that allows me to browse my objects in that server and eventually design my query graphically.
In SQL Server 2005 Express, I know that I cannot browse the objects after referring to microsoft article above. "Note In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains. "
However, is it possible to design my query graphically?
SQL Server 2005 Standard Edition act as publisher and distributor. All subscribers are SQL Server 2005 Express Edition. According to 2005 book online, "MSMerge_History table exists in distribution database". Howevey I found this table in Subscriber database which is in SQL Server 2005 Express Edition. The problem is this table (MSMerge_History) and other two tables (MSMerge_genhistory, MSRepl_errors) are quite large. We want to keep SQL Server 2005 Express database as small as possible so we can put more data into it. Is there anyway (Manually or automatically) to clean those tables in SQL Server 2005 Express? Please help.
Hello, We are currently live with a CRM solution (Siebel) that uses SQL Server 6.5 as the back end. All is fine and dandy, except I have some reservations about security. Quite simply, it is possible for anyone to open up MS Access and link to any of the SQL Server database tables via the ODBC DSN used by the Siebel front end. This DSN is necessary for Siebel to function. I am bit worried that someone (out of incompetence or spite) might do just that and cause some serious damage. Its probably technically beyond the large proportion of our users (especially those that could make mistakes!), but I can't get the nagging fear out of my head. Does anyone know of anyway to combat this problem? I have scoured the web, including this site, and cant seem to get any information on this. Thanks and Regards Dike
is it possible to define in sql server linked tables to odbc data sources as it is possible to do in access? except Access and vfp, is there another database that allows to create linked tables to odbc data sources?
I need to combine in a database native tables with odbc linked tables, but it would need to use a database more powerful that access or vfp
I need to create a relationship between a local table and tables on alinked server. I used the design table wizard and selected therelationship property wizard. In the reslationship property wizard,the tables that I need to get the keys from in the linked server do notshow up. Is there a way to do this, or I simply don't have enoughpermission to tables in the linked server. On the local server, theSecurity tab of linked server property has Local Loging "sa", RemoteUser "sa" and Remote Password "****". Thanks for your help.
