Open Query

SQL Server Administration
1

I have a Query I am trying to pull from another server some data over to a table on another server I am trying to use open query and I am not sure what I am doing wrong. It is not finding the other server or seeing it. Here is my query.

USE ARLP_DATA
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp.Create_AC_MASTER]

AS
BEGIN
SET NOCOUNT ON;

TRUNCATE TABLE [dbo.].[MASTER_DATA]

INSERT INTO [dbo.].[MASTER_DATA]
(UWI,
LEASE,
RSV_CAT,
FIELD,
HOLE_TYPE,
RESERVOIR,
OPERATOR,
COUNTY,
STATE,
BASIN,
API,
FIRST_PROD,
SPUD_DATE,
COMPL_DATE,
START_DATE,
LAT_LEN,
LL_MULT,
SHLAT,
SHLONG,
BHLAT,
BHLONG)

SELECT * FROM OPENQUERY(PRTDMINSQL1, 'SELECT FROM
ARIES_MASTER.dbo.AC_PROPERTY')
UWI,
LEASE,
RSV_CAT,
FIELD,
HOLE_TYPE,
RESERVOIR,
OPERATOR,
COUNTY,
STATE,
BASIN,
API,
FIRST_PROD,
SPUD_DATE,
COMPL_DATE,
START_DATE,
LAT_LEN,
LL_MULT,
SHLAT,
SHLONG,
BHLAT,
BHLONG

END
GO
;

I keep getting this error: Incorrect syntax near '.'.

Not Sure why?

2

Will that resolve to two dots?

3

I see several problems...

[dbo.].[MASTER_DATA] is looking for a schema named dbo. (with the period). Do you have a custom schema with that name?

In your OPENQUERY you do not have a list of columns being returned (SELECT FROM instead of SELECT col1, col2, ... FROM).

4

I removed the extra dot sorry about that but still not resolved.

5

Ok I changed the code to this I am still getting one error it is like it is not seeing the other table on the other server. Here is the new code:

USE ARLP_DATA
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp.Create_AC_MASTER]

AS
BEGIN
SET NOCOUNT ON;

TRUNCATE TABLE [dbo.].[MASTER_DATA]

INSERT INTO [dbo.].[MASTER_DATA]
(UWI,
LEASE,
RSV_CAT,
FIELD,
HOLE_TYPE,
RESERVOIR,
OPERATOR,
COUNTY,
STATE,
BASIN,
API,
FIRST_PROD,
SPUD_DATE,
COMPL_DATE,
START_DATE,
LAT_LEN,
LL_MULT,
SHLAT,
SHLONG,
BHLAT,
BHLONG)

SELECT * FROM OPENQUERY(PRTDMINSQL1, AC_PROPERTY,
UWI,
LEASE,
RSV_CAT,
FIELD,
HOLE_TYPE,
RESERVOIR,
OPERATOR,
COUNTY,
STATE,
BASIN,
API,
FIRST_PROD,
SPUD_DATE,
COMPL_DATE,
START_DATE,
LAT_LEN,
LL_MULT,
SHLAT,
SHLONG,
BHLAT,
BHLONG

END
GO
;
Incorrect syntax near AC_PROPERTY

6

You need to remove . from dbo

[dbo.].[MASTER_DATA] should be [dbo].[MASTER_DATA]

for the open query, you need something like this

SELECT * FROM OPENQUERY(PRTDMINSQL1, 'SELECT AC_PROPERTY,
UWI,
LEASE,
RSV_CAT,
FIELD,
HOLE_TYPE,
RESERVOIR,
OPERATOR,
COUNTY,
STATE,
BASIN,
API,
FIRST_PROD,
SPUD_DATE,
COMPL_DATE,
START_DATE,
LAT_LEN,
LL_MULT,
SHLAT,
SHLONG,
BHLAT,
BHLONG
From Table');

7

Thanks I got it corrected by adding a sy link to SQL thanks again