I am receiving the error SQL0104N An unexpected token "," was found following "select .loan_number". Expected tokens may include: "". SQLSTATE=42601 IF OBJECT_ID('tempdb..#Temp1') is not null begin drop table #Temp1 select * into #Temp1 from openquery(LnkServer, ' Select X.loan_number, X.ls_code, x.ls_actual_completion_date x.ls_...
What am I missing in this code below that will cause the error: Msg 170, level 15, line 113 line 113: Incorrect syntax near 'actual_completion_date'. update #Loss_mit_step set [STEP924_COMPL_DATE] = Case when step_code ='924' then ls_actual_completion_date else ' ' end, [STEP926_COMPL_DATE] = Case when step_code ='926' then ...
I have a ASP.NET MVC website in IIS 7 running against a SQL Server 2008 R2 database, in which I have a linked server set up. The linked server is set up so that a SQL account from my database is linked to a SQL account on the linked server. All works fine until I recompile my website, and then it can't connect to the linked server anym...
After creating a linked server on SSMS 2008, how should I see linked server table schema? Should I change any permission on remote SQL Server? Also, what commands can check those permissions for all accounts? Thanks. EDIT: running on SQL Server 2000. ...
I am linked to a Proficy Historian that allows periods in the column names. Because the data is stored in a non DBMS format I can not use openquery to get the data because there is no set schema to the tables. So I must use four part name syntax to get the data. This example works: SELECT * FROM iHist...[SELECT * FROM ihTrend] but ...
If I want to run this sort of query in SQL Server, how can I do the same query from one server I am connected to to another? I tried adding "[ServerName1]." before "[DatabaseName1].[dbo]..." and "[ServerName2]." before "[DatabaseName2].[dbo]..." but that didn't seem to work. INSERT INTO [DatabaseName1].[dbo].[TableName] ([Fi...
I am trying to set up a third party MySQL as a Linked Server on SQL Server 2008. When I try to access the data via a simple OpenQuery below I get an error. SELECT * FROM OpenQuery(SERVERNAME, 'SELECT * FROM table') The error message: OLE DB provider 'MSDASQL' for linked server 'SERVERNAME' returned data that does not match expected...
I need to do the following query (for example): SELECT c1.CustomerName FROM Customer as c1 INNER JOIN [ExternalServer].[Database].[dbo].[Customer] as c2 ON c2.RefId = c1.RefId For some security reason my client doesn't allow me to create a linked server. The user under whom I execute this query has access to both tables. Is it possibl...
Here is some sample code: DECLARE @sourceFile varchar(255), @testRows int SELECT @sourceFile = @xmlInfo.value('(/SelectFile/DataSource)[1]', 'VARCHAR(255)') EXEC sp_addlinkedserver 'SomeData', 'Excel', 'Microsoft.Jet.OLEDB.4.0', @sourceFile, '', 'Excel 8.0', '' IF @xmlInfo.exist('/ConfigArgs/ParamsXml/ImportDealerData') = 1 ...
Here is some sample code that worked fine in a 32-bit machine (SQL server 2005) and needs to be updated for a 64-bit... I know this is a common problem but have not been able to find how to fix it! DECLARE @sourceFile varchar(255), @testRows int SELECT @sourceFile = @xmlInfo.value('(/SelectFile/DataSource)[1]', 'VARCHAR(100)') ...
Hi Based on the 2 databases below: Database_A on Server_1 Database_B on Server_2 I have created a linked server to Database_B on Server_1 instance by name 'LS_B'. I have a huge script file which basically creates required tables, functions, views, and stored procs on Database_A. These functions, views and stored procs in turn ref...
I want to execute the following statement through from a linked server (openquery): UPDATE SAP_PLANT SET (OWNER, OWNER_COUNTRY) = (SELECT import.AFNAME, import.COUNTRY FROM SAP_IMPORT_CUSTOMERS import, SAP_PLANT plant WHERE plant.SAP_FL = import.SAP_NO ...
Hi, I have 2 similar tables on different databases. I already linked the remote DB and set it's data access to 'True'. However I can't get the following query to work GO USE LOCALDB GO DELETE from [dbo].[TableA] WHERE [dbo].[TableA].[UniqueField] = (SELECT [UniqueField] FROM [REMOTESERVER].[REMOTEDB].[dbo].[TableA]) GO This query d...
So I have 2 SQL servers, both internal on the firewall, and my webserver is on the DMZ. The webserver currently connects to one of the SQL servers for it's data. I need some information from the other SQL server which I cannot connect to from the DMZ because of security risks that the company is not willing to take. How can I connect ...
I'm trying to use this query to delete the rows that are already on a linked server's Database: GO USE TAMSTest GO DELETE from [dbo].[Hour] WHERE [dbo].[Hour].[InHour] = (SELECT [InHour] FROM [TDG-MBL-005].[TAMSTEST].[dbo].[Hour]) GO When there is only 1 row in the linked server's table, SELECT [InH...
Hi, I have successfully created linked server between 2005 and 2008 version. We changed a table schema on 2008 and re ordered the table columns. We also did the same on 2005 server. If we query both table in their own database then schema looks fine however when I do SELECT * FROM and Select * from then it is showing me old schema ...
Hi all, I'm trying to create a linked server in Sql server 2008 R2, just tried downloading the '64-bit version of the Office 2010 Access AD Engine' exe from Microsoft. Unfortunately, I had tried adding a linked server, BEFORE making this installation, and it failed: EXEC sp_addlinkedserver 'LinkedServer1', 'Excel', 'Microsoft.Jet.OLE...
I am doing a large archival of data from one DB to another DB on a linked server. I am copying from MS sql server 2000 to MS sql server 2005. This involves copying millions of records and then deleting them. This all happens via a dynamic script. I would like to incorporate the shrinking of the log file for this process, but I am unabl...
I have these linked servers, A and B. Server A is running SQL Server 2008, and B is running SQL Server 2000. I have written a script to migrate data from a bunch of tables from B to A. The data is quite sensitive so I'm afraid I can't tell you anymore about that. I can, however, tell you that we are talking about a lot of data, worst ca...
Can someone help me construct the SQL that I need to query the Projects_dim table using the Linked Server "idwd"? To test the connection, I ran a sample query using the linked server name. To access the tables on the linked server, I used a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name. replacing t...