Click here to Skip to main content
15,891,607 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hello.i have following t-sql. but i get this error:
"
CREATE/ALTER PROCEDURE' must be the first statement in a query batch
"


please some one correct my following script:
GO
declare @sqlCmd varchar(max) 
select @sqlCmd = 
'
GO
USE [CRM]
ALTER PROC [dbo].[uspReportTrasnlationOpticalCabinetToNormal]
(
	@requestsId varchar(max) = null
	--@IsSuccess bit output
)
AS
BEGIN
	Set XACT_ABORT ON 
	SET NOCOUNT ON
		BEGIN TRY
			
		--SELECT @IsSuccess = 0
			SELECT 
				R.ID RequestID,
				TC.ToTelephoneNo,
				TC.FromTelephoneNo,
				ISNULL(C.FirstNameOrTitle,'') FirstNameOrTitle,
				ISNULL(C.LastName,'') LastName,
				ISNULL(A.AddressContent,'') InstallAddress,
				ISNULL(A.PostalCode,'') InstallPostalCode,
				ISNULL(AA.AddressContent,'') CorrespondenceAddress,
				ISNULL(AA.PostalCode,'') CorrespondencePostalCode
			FROM 
				Request R
			INNER JOIN 
				TranslationOpticalCabinetToNormal TN ON TN.ID = R.ID
			INNER JOIN 
				TranslationOpticalCabinetToNormalConncetions TC ON TN.ID = TC.RequestID
			LEFT JOIN
				[Address] A ON A.ID = TC.InstallAddressID
			LEFT JOIN
				[Address] AA ON AA.ID = TC.CorrespondenceAddressID
			LEFT JOIN 
				Customer C ON C.ID = TC.CustomerID
			WHERE 
				TN.[Type] = 2 
				AND
				(@requestsId IS NULL OR LEN(@requestsId) = 0 OR R.ID IN (SELECT * FROM DBO.ufnSplitList(@requestsId)))

			
			--SELECT @IsSuccess = 1
		END TRY
		BEGIN CATCH
			EXEC  [dbo].[uspLogError] 
			--SELECT @IsSuccess = 0;
			THROW;
		END CATCH
END


'
EXECUTE (@sqlCmd) AT [mylinkedserver]
GO
Posted

try this


GO
declare @sqlCmd varchar(max)
select @sqlCmd =
'
GO
USE [CRM]
ALTER PROC [dbo].[uspReportTrasnlationOpticalCabinetToNormal]
(
@requestsId varchar(max) = null
--@IsSuccess bit output
)
AS
BEGIN
Set XACT_ABORT ON
SET NOCOUNT ON
BEGIN TRY

--SELECT @IsSuccess = 0
SELECT
R.ID RequestID,
TC.ToTelephoneNo,
TC.FromTelephoneNo,
ISNULL(C.FirstNameOrTitle,'''') FirstNameOrTitle,
ISNULL(C.LastName,'''') LastName,
ISNULL(A.AddressContent,'''') InstallAddress,
ISNULL(A.PostalCode,'''') InstallPostalCode,
ISNULL(AA.AddressContent,'') CorrespondenceAddress,
ISNULL(AA.PostalCode,'''') CorrespondencePostalCode
FROM
Request R
INNER JOIN
TranslationOpticalCabinetToNormal TN ON TN.ID = R.ID
INNER JOIN
TranslationOpticalCabinetToNormalConncetions TC ON TN.ID = TC.RequestID
LEFT JOIN
[Address] A ON A.ID = TC.InstallAddressID
LEFT JOIN
[Address] AA ON AA.ID = TC.CorrespondenceAddressID
LEFT JOIN
Customer C ON C.ID = TC.CustomerID
WHERE
TN.[Type] = 2
AND
(@requestsId IS NULL OR LEN(@requestsId) = 0 OR R.ID IN (SELECT * FROM DBO.ufnSplitList(@requestsId)))


--SELECT @IsSuccess = 1
END TRY
BEGIN CATCH
EXEC [dbo].[uspLogError]
--SELECT @IsSuccess = 0;
THROW;
END CATCH
END


'
EXECUTE (@sqlCmd) AT [mylinkedserver]
GO
 
Share this answer
 
You cannot use GO in dynamic SQL. What you can do however is:
SQL
EXECUTE (N'USE [CRM]; EXEC sp_executesql N'''+@sqlCmd+'''') AT [mylinkedserver]

https://ask.sqlservercentral.com/questions/5108/database-use-issues-with-dynamic-sql.html[^]
 
Share this answer
 
Comments
aliwpf 8-Mar-15 3:51am    
thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900