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.