Create Temp Table On Linked Server From Local Server
Jan 15, 2004
Hi,
I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large.
If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that.
I appreciate any advice / guidance anyone can offer me!
I have a local SQL2005 server with a linked SQL2000 server. I would like to know how to create a temporary table in the remote server in such a way that I can make an inner join as follows; my idea is to optimized a distributed query by doing so:
create table #myRemoteTempTable
insert into #myRemoteTempTable select * from myLocalTable
update myRemoteTable set Value=#myRemoteTempTable.Value from myRemoteTable inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID
Trying to do this all day and googling for answers but found none, hopesomeone can help. Thanks in advance.select * intoOPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test) from LocalTableReason: I am joining local tables with linked server tables using theformat "LinkedServer.database.owner.object" to execute a query, ittakes forever to execute since the tables joined on the remote servershave more than 50Mil records. I read somewhere that sql server needs tocopy the tables locally to the temp db and does the join there, hence Iwas hoping to dump the data of the local database into a temp table onthe remote server and then do a join with OPENQUERY, which will executethe query on the linked server and return the results.
Basically - this doesn't work as an error is returned:-
Code:
The object name 'GODZILLA.Racing.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Now - it has no problem running a SELECT (ie read-only) query from the remote DB but why does it have a problem with writing? And if thats the issue, why doesn't it say so. Am I going about this the right way???
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
I have the following select that is designed to compare values on parallel servers.SELECT TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate,TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate FROM TradeAnalysis.dbo.SalesIncome_Quarterly AS TA1 FULL OUTER JOIN L_TA_MIRROR.TradeAnalysis.dbo.SalesIncome_Quarterl y AS TA2 ON ((TA1.OSID = TA2.OSID) AND (TA1.QtrYear = TA2.QtrYear) AND (TA1.QuarterNo = TA2.QuarterNo)) WHERE(BINARY_CHECKSUM(TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate) <> BINARY_CHECKSUM(TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate))Disregard the full outer join, it is necessary in the full select, I have just pared it down to show here and for testing. A standard join will also have the same result.
The trouble is that the comparison (the BINARY_CHECKSUM) is failing on the EarningsReportDate column, which is defined on BOTH servers in DUPLICATE databases as a smalldatetime.
The data is actually the same in both rows of each table (as is all the other data being compared). What I am seeing is that the select returns milliseconds on the LINKED server (the L_TA_MIRROR reference), but not on the local database?
for example, Local server data returned in above select is 2000-01-28 00:00:00 Linked server data returned in above select is 2000-01-28 00:00:00.000
Wassup with THAT? If I remove the date from the select/comparison, it says everything matches, so I am pretty sure this is the culprit that is causing the BINARY_CHECKSUM comparison to fail.
Is there some setting that I need to put on the Linked Server reference that says "treat this as the smalldatetime data type it IS, dammit!"????
I can get by the issue if I replace the date reference in the linked server part of the select with CAST(TA2.EarningsReportDate AS smalldatetime) but why do I have to do this?
Yes, I am SURE both servers have the column in question defined as a smalldatetime, as running the same select on either server causes the same failure, but only when referencing the LINKED server column.
I found that when I install MSDE, then I can create/edit database objects for MSDE database in Visual Studio 2003 Server Explorer, but when I try to create/edit database objects under the default database created by the SQL Server 2000 installation, I cannot do these tasks as no option for these tasks appear when I right-click on the database object in Server Explorer. Anyone knows why this is happening?
I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
I have created a linked server that connects a SQL 2000 database to a SQL 2005 database. If I use individual SQL or Windows accounts as local logins on the SQL 2000 instance, I can successfully query the linked SQL 2005 database. (For security we use the setting "For a login not defined in the list above, connections will: not be made")
If I try to use a Windows group as the local login, remote queries fail with the error "Access to the remote server is denied because no login-mapping exists"
Is it not possible to use a Windows group for the local login of a linked server?
If I run 'exec sp_linkedservers' the setup appears valid
Linked Server Local Login Is Self Mapping Remote Login SQL2005Serv DomainBRubble 0 SQL_Read_Access SQL2005Serv DomainWindows_Group 0 SQL_Read_Access SQL2005Serv DomainFFlintstone 0 SQL_Read_Access
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000)) INSERT ##MyTempTbl values ('Put your long message here.') INSERT ##MyTempTbl values ('Put your second long message here.') INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!') DECLARE @cmd varchar(256) DECLARE @LargestEventSize int DECLARE @Width int, @Msg varchar(128) SELECT @LargestEventSize = Max(Len(MyWords)) FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' + CONVERT(varchar(5), @LargestEventSize) + ')) FROM ##MyTempTbl order by SeqNo' SET @Width = @LargestEventSize + 1 SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------' EXECUTE Master.dbo.xp_sendmail 'YoMama@WhoKnows.com', @query = @cmd, @no_header= 'TRUE', @width = @Width, @dbuse = 'MyDB', @subject='none of your darn business', @message= @Msg DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
Does SQL Server Compact Edition support Linked Server? I have 2 SDF files and I want to copy data from one to the other? I thought Linked Server is the easier to go.
I'm Trying to create a linked server from an SQL Server to another SQLServer on another machine.Can anyone suggest some good reference material or tutorials?? So farI'm only finding examples for a linked server to an ACCESS database.Thanks in Advance!
I have a clustered server environment where three of the SQL Server local groups were not created during installation. One of these missing groups is SQLServer2005SQLAgentUser$ComputerName$InstanceName, and this is causing problems with trying to automate replication.
How do I create this group after I have installed SQL Server?
Hi all!I have a problem with a temp table.I start creating my table:bdsqlado.execute ("CREATE TABLE #MyTable ...")There is no error. The sql string has been tested and when it'sexecuted in the sql query analyzer it really creates the table.After creating the table, I execute an insert statement:bdsqlado.execute ("INSERT INTO #MyTable VALUES(...) "It returns an error like this: "Invalid Object Name #MyTable"I don't understand what's wrong. If I execute both sql sentences inthe SQL Query Analyzer it works perfectly.I use the same connection to execute both statements and I don't closeit before the INSERT is executed.I think it may be something related to dynamic properties of theconnection, but I'm not sure. It's just an idea.Please I need help.Thanks a lot,
I am trying to create a linked server in Management Studio Exoress. In the Objext Explorer, I open Server Objexts and the right-click on Linked Servers and select New Linked Server. I then get an error that says "Cannot show the requested dialog. Additional information: Cannot find table 0. (System Data). The full text of the error is as follows:
===================================
Cannot show requested dialog.
===================================
Cannot find table 0. (System.Data)
------------------------------ Program Location:
at System.Data.DataTableCollection.get_Item(Int32 index) at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.PopulateProvidersCombo() at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnInitialization() at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node) at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SelectCurrentNode() at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.InitializeUI(ViewSwitcherTreeView treeView, ISqlControlCollection viewsHolder, Panel rightPane) at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control) at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc) at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()
I am running XP SP2 and SSE SP2. One other item is that the providers folder is empty. I checked another box and there are several providers listed in that installation. It looks like when the SSE is installed, the providers are not being created. I have tried uninstalling and reinstalling and am having the same problem. Is this a installation bug or is there a conflict with another program? I also re-downloaded the installation files in case there was a problem with that, but it didn't solve the issue either.
Can someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
I have a view which works fine but I cannot display the data in the Report tool because its
CCSID is HEX. If I could create it to temp table
I think then there would be an easy way to get around this problem.
This is the code:
CREATE VIEW astlib.acbalmpk AS ( (SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1, COALESCE(IRLOC2,'') as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#, '' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as IELOC3, '' as IERIDC, '' as IEWHS#
Hi, I'm using a couple of linked servers. I want to create a stored procedure on all of the linked servers in a database with a name which exists on all of the linked servers. For executing SQL on all of the linked servers I'm using:
declare @x int declare @dbname varchar(500) declare @SQL nvarchar(600) set @x = 1 create table #databases (ID int IDENTITY,name varchar(500)) insert #databases select instancelongname from instances while @x <= (select max(id) from #databases) begin select @dbname = name from #databases where id = @x select @SQL='blabla bla bla create PROCEDURE [dbo].[usp_xxxx]' execute @SQL set @x = @x + 1 end drop table #databases
Is it possible to use a ‘create procedure’ in this construction? Can anybody give me some help how to create a proper syntax for it?
I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.
But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server
select * from linktompp.testmpp.dbo.tasks
such that linktompp is the linked server name testmpp is the catalog name tasks is the table name i need to select data from
it shows an error that says
"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."
"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]" could anyone tell me the reason of this error
what are the steps to create a linked server, I have tried unsuccesfully: The scenario is the following: From server A I want to be able to execute a select statement to a table in server B like select * from [server B].myRemoteDb.dbo.myremTable
I am administrator of server A but not of server B. I tried the
sp_addlinkedserver syntax but have not been succesful, so I would be grateful if somebody could just list the steps necessary to link Server B from Server A,
I'm using sql server 2005 and exchange 2003. End result is I want to upload email attachments to a table in sql. No one seems to have any idea how do do this except with .net, which i know nothing about so not really an option for me. I found this bit of info with some extensive web searching. Can anyone help fill in the blanks? I can't get the linked server to work yet.
EXEC sp_addlinkedserver 'exchange', 'Exchange OLE DB provider', 'exoledb.DataSource.1', 'file:\.ackofficestoragemailservernamepublic folders'
Error received:The OLE DB provider "exoledb.DataSource.1" has not been registered.
I´m trying to create a linked server to a Sybase database (.db file) so i can create some reports in sql 2005 (with Reporting Services and Report Designer). After reading all articles I did the following.
1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server. 2) Created a USER DSN to the Db file (Control Panel - odbc connections) and it works fine: The Settings for the ODBC are:
Database File: c:Project FolderDataBase.db As I said, when i go for "test connection", works fine.
3) Sql Management Studio - Object Explorer - Server Objects - New Linked Server, with settings as following:
General
Linked Server: lkDbNom
Server Type: Other Data Source
Provider: Sybase Adaptive Server Anywhere Provider 9.0
Product Name: Sybase
Product Name: dbNom (the dsn name, right?)
Security
Be made using this security context.- Remote Login: DBA; Password: SQL (same as DSN) All other settings, as default, click in OK and shows no errors (aparently it creates the linked server successfully). But when i try to query the linked server with:
SELECT * FROM OPENQUERY ('SYBASE', 'SELECT * FROM nom_Robot')
And i get the following message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SYBASE'.
Even better, if i go to the Object Explorer - Server Objects, i test the connection and it´s ok, but when i try to retrieve the catalog an error displays:
TITLE: Microsoft SQL Server Management Studio ------------------------------ Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The OLE DB provider "ASAProv.90" for linked server "SYBASE" reported an error. Access denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ASAProv.90" for linked server "SYBASE". (Microsoft SQL Server, Error: 7399) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
So, i really don´t understand what is missing in my linked server, i tried with MDASQL (OLEDB for ODBC) but it doesn´t even completes to create the linked server.
The database file is ok because, because in another server (one that doesn´t have SQL, only visual studio) i did the following:
1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server. 2) Created a DSN to the Db file, and it works fine. (copied the db file, so is local, no remote access), exactly the same setting as i did on the sql 2005 server. 3) Created a connection in visual studio 2008 (server explorer - data connections), i can retrieve sdata...but of course the provider is .NET Framework Data Provider for ODBC and i cannot use the same connection string.
Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation. I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices. Basically, a shopping cart! I thought of using session variables and string manipulations but I am more comfortable with DB queries. a simple 4 column table would cover everything. SQL server and VBScript Thanks M
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A 1component B 1Bom D 1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A 1......component B 1......Bom D 2.........Component A 2.........Component C 2.........Bom C 3............Component F 3............Component Z 1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
