October 16, 2014 at 12:58 pm
My objective is to create a stored procedure, so I can schedule this task.
First, I want to get the t-sql code to work.
This code works, but I'm trying to learn why I need the first GO statement.
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'rmtServer')
EXEC sp_dropserver 'rmtServer', 'droplogins'
EXEC sp_addlinkedserver @server ='rmtServer', @srvproduct= 'SQL Server'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'rmtServer',
@useself = 'false',
@rmtuser = 'rmtUserName',
@rmtpassword = 'rmtUserPW'
GO
DECLARE @CurrentLoadTime table (Event nvarchar(750), UpdateTime datetime)
DECLARE @DestinationLoadTime table (Event nvarchar(750), UpdateTime datetime)
INSERT INTO @CurrentLoadTime(Event, UpdateTime)
SELECT ObjectName, HistoryDate
FROM rmtServer.SQSources_Data.dbo.HistoryDate
WHERE HistoryDate > DATEADD(month, -2, GETDATE())
INSERT INTO @DestinationLoadTime(Event, UpdateTime)
SELECT Event, MAX(UpdateTime) as maxUpdateTime
FROM myDb.dbo.myTable
GROUP BY Event
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'rmtServer')
BEGIN
EXEC sp_dropserver 'rmtServer', 'droplogins'
END
INSERT INTO myDb.dbo.myTable(Event, UpdateTime)
SELECT C.Event, C.UpdateTime
FROM @CurrentLoadTime C
JOIN @DestinationLoadTime D ON C.Event = D.Event
WHERE C.UpdateTime > D.UpdateTime
SELECT * FROM myTable
ORDER BY Event
If I remove the GO, I get this error:
Msg 7202, Level 11, State 2, Line 16
Could not find server 'rmtServer' 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.
I don't understand why...
Thanks!
October 16, 2014 at 1:28 pm
The GO separates the code into "batches". SQL parses and prepares an execution plan separately for each batch.
Thus, without the GO, when SQL parses the query below the linked server does not exist, since no part of the batch has been processed yet.
With the GO, SQL parses and executes the first batch before it parses the second batch. Thus, the linked server exists for any query(s) after the GO.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 16, 2014 at 2:18 pm
Thank you, Scott.
So I take the same code and try to create a stored procedure by adding this:
IF OBJECT_ID('usp_DataLoadTime', 'P') IS NOT NULL
DROP PROC usp_DataLoadTime
GO
CREATE PROC usp_DataLoadTime
AS
BEGIN
... <code from above....>
END
...and I get these errors:
Msg 102, Level 15, State 1, Procedure usp_DataLoadTime, Line 15
Incorrect syntax near 'rmtUserPW'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'END'.
Why would an error free script error when making a stored procedure?
October 16, 2014 at 2:25 pm
Because GO separates code into batches and SQL parses and executes batches separately.
A batch is a set of commands sent to a server. Hence, if you run this:
CREATE PROCEDURE WontWork AS
BEGIN
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'rmtServer',
@useself = 'false',
@rmtuser = 'rmtUserName',
@rmtpassword = 'rmtUserPW'
GO
DECLARE @CurrentLoadTime table (Event nvarchar(750), UpdateTime datetime)
DECLARE @DestinationLoadTime table (Event nvarchar(750), UpdateTime datetime)
INSERT INTO @CurrentLoadTime(Event, UpdateTime)
SELECT ObjectName, HistoryDate
FROM rmtServer.SQSources_Data.dbo.HistoryDate
WHERE HistoryDate > DATEADD(month, -2, GETDATE())
INSERT INTO @DestinationLoadTime(Event, UpdateTime)
SELECT Event, MAX(UpdateTime) as maxUpdateTime
FROM myDb.dbo.myTable
GROUP BY Event
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'rmtServer')
BEGIN
EXEC sp_dropserver 'rmtServer', 'droplogins'
END
INSERT INTO myDb.dbo.myTable(Event, UpdateTime)
SELECT C.Event, C.UpdateTime
FROM @CurrentLoadTime C
JOIN @DestinationLoadTime D ON C.Event = D.Event
WHERE C.UpdateTime > D.UpdateTime
SELECT * FROM myTable
ORDER BY Event
END
then you are executing two batches.
The first batch does this:
CREATE PROCEDURE WontWork AS
BEGIN
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'rmtServer',
@useself = 'false',
@rmtuser = 'rmtUserName',
@rmtpassword = 'rmtUserPW'
GO
The second batch, sent to SQL Server after it's signalled that the first one finished
DECLARE @CurrentLoadTime table (Event nvarchar(750), UpdateTime datetime)
DECLARE @DestinationLoadTime table (Event nvarchar(750), UpdateTime datetime)
INSERT INTO @CurrentLoadTime(Event, UpdateTime)
SELECT ObjectName, HistoryDate
FROM rmtServer.SQSources_Data.dbo.HistoryDate
WHERE HistoryDate > DATEADD(month, -2, GETDATE())
INSERT INTO @DestinationLoadTime(Event, UpdateTime)
SELECT Event, MAX(UpdateTime) as maxUpdateTime
FROM myDb.dbo.myTable
GROUP BY Event
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'rmtServer')
BEGIN
EXEC sp_dropserver 'rmtServer', 'droplogins'
END
INSERT INTO myDb.dbo.myTable(Event, UpdateTime)
SELECT C.Event, C.UpdateTime
FROM @CurrentLoadTime C
JOIN @DestinationLoadTime D ON C.Event = D.Event
WHERE C.UpdateTime > D.UpdateTime
SELECT * FROM myTable
ORDER BY Event
END
If you look at those as two separate batches sent to SQL, it should be obvious why you're getting errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2014 at 2:32 pm
inevercheckthis2002 (10/16/2014)
Thank you, Scott.So I take the same code and try to create a stored procedure by adding this:
IF OBJECT_ID('usp_DataLoadTime', 'P') IS NOT NULL
DROP PROC usp_DataLoadTime
GO
CREATE PROC usp_DataLoadTime
AS
BEGIN
... <code from above....>
END
...and I get these errors:
Msg 102, Level 15, State 1, Procedure usp_DataLoadTime, Line 15Incorrect syntax near 'rmtUserPW'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'END'.
Why would an error free script error when making a stored procedure?
You can't use GO in a stored proc. But that still likely wouldn't explain the syntax errors. I'd have to see the exact code to debug those.
Dynamically adding a linked server for immediate use, in the same proc, is tricky. The best chance is either (1) use dynamic SQL to add the linked server, then regular code to reference it, or (2) call a separate proc to create the linked server.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply