SQL Error with Stored Procedure Could not find server

Question

This is the first time that I have created a stored procedure and I have no clue what I am doing wrong.

I am getting the following error:

Msg 7202, Level 11, State 2, Procedure update_call_details, Line 8
Could not find server 'SELECT TOP 1 b' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Stored procedure:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[update_call_details] 
AS
BEGIN
    DECLARE @EventTime varchar(50)

    SET @EventTime = 'SELECT TOP 1  b.event_time FROM db1.dbo.TableName as b order by b.event_time desc'

    EXEC @EventTime

    INSERT INTO Sdb1.dbo.TableName
        SELECT 
            CAST(START_TIME as DATE) as START_TIME,
            AGENT_ID,
            GRP_DBID,
            DIRECTION,
            ANI, DNIS, TRACKNUM,
            N_HOLD, T_HOLD,
            N_CONFRENCE, T_CONFRENCE,
            N_TRANSFER, T_TRANSFER,
            T_WRAP_UP,
            TALK_TIME,
            CAST(END_TIME as DATE) as END_TIME,
            CAST(EVENT_TIME as DATE) as EVENT_TIME
        FROM 
            OPENQUERY(db2, 'SELECT * FROM db2.TableName 
                            WHERE EVENT_TIME > "@EventTime"')
END

Mat Richardson · Answer 1 · 2015-12-16T23:29:04.187

1

Change the line:-

DECLARE @EventTime varchar(50)

to

DECLARE @EventTime varchar(200)

The size of the variable is truncating its contents so the EXEC command is failing. I've increased the size of the variable to hopefully cover any additional changes you might make.

edited Dec 16 '15 at 23:29

answered Dec 16 '15 at 23:21

Mat Richardson

3,576
4
31
56

Now I have a new error: Msg 203, Level 16, State 2, Procedure update_call_details, Line 8 The name 'SELECT TOP 1 b.event_time FROM DB.dbo.TableName as b order by b.event_tim' is not a valid identifier. – Jennifer Millwood Dec 16 '15 at 23:25
How come your SELECT statement is now different?? – Mat Richardson Dec 16 '15 at 23:28
To be safe, change your varchar size to varchar(200) - see edited answer. – Mat Richardson Dec 16 '15 at 23:28
Operator error... and I am stil getting that: 'SELECT TOP 1 b.event_time FROM db1.dbo.TableName as b order by b.event_tim' is not a valid identifier – Jennifer Millwood Dec 16 '15 at 23:32
I found the answer: I needed to have my exec (@EventTime) in order for it to work. – Jennifer Millwood Dec 16 '15 at 23:39

score 1 · Answer 2 · edited May 23 '17 at 11:52

You can not use parameters like that with openquery. Your query must be a single string literal.

In this instance you can simply put your EventTime sub-query directly inline:

ALTER PROCEDURE [dbo].[update_call_details] 
AS
BEGIN
INSERT INTO Sdb1.dbo.TableName
SELECT CAST(START_TIME as DATE) as START_TIME,
AGENT_ID,
GRP_DBID,
DIRECTION,
ANI,
DNIS,
TRACKNUM,
N_HOLD,
T_HOLD,
N_CONFRENCE,
T_CONFRENCE,
N_TRANSFER,
T_TRANSFER,
T_WRAP_UP,
TALK_TIME,
CAST(END_TIME as DATE) as END_TIME,
CAST(EVENT_TIME as DATE) as EVENT_TIME
 FROM openquery(db2, 
    'SELECT * 
    FROM db2.TableName 
    WHERE EVENT_TIME > 
        (SELECT TOP 1 b.event_time 
        FROM db1.dbo.TableName as b 
        order by b.event_time desc)')
END

Otherwise... to use parameters in a query you can use one of the options from this question: including parameters in OPENQUERY

SQL Error with Stored Procedure Could not find server

2 Answers2