September 12, 2013 at 11:59 pm
Hi all,
I am new to this dynamic sql concept and I have written a script uaing dynamic sql but I am getting while executing it...
Declare
@ServerName nvarchar(100)='[OTINSRCPELODD01\SFA]',
@databasename nvarchar(100)='[ESSOTISNL_73DEV]',
@sql nvarchar(4000)
SET @sql = 'SELECT
DISTINCT
Sol.EquipmentOfficeId,
Ofc.CompanyID,
DRD.ProjectId,
DRD.ProposalId,
DRD.SolutionId,
DRD.UnitId,
DRD.DeviationNo,
I.IsRevisedRequest,
DRD.DeviationStatus,
DRD.RequestDate,
DRD.RequestedBy,
DRD.RepliedBy,
DRD.DeleteFlag,
DRD.DateAdded,
DRD.AddedBy,
DRD.DateChanged,
DRD.ChangedBy,
DRD.ReplyDate
FROM'+ @ServerName +'.'+ @Databasename +'.dbo.'+ 'DeviationRequestDetails DRD WITH(NOLOCK)
INNER JOIN'+ @ServerName +'.'+ @Databasename +'.dbo.'+ 'vw_MaxDeviation C ON C.ProjectId = DRD.ProjectId
AND C.ProposalId = DRD.ProposalId
AND C.SolutionId = DRD.SolutionId
AND C.UnitId = DRD.UnitId
AND C.RID = DRD.RequestId
INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+ 'DeviationRequestDetailsInstallation I WITH(NOLOCK) ON DRD.OfficeId = I.OfficeID
AND DRD.ProjectID = I.ProjectId
AND DRD.ProposalId = I.ProposalID
AND DRD.SolutionID = I.SolutionId AND DRD.UnitID = I.UnitId
AND DRD.RequestId = I.RequestId
INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+'Solution Sol WITH(NOLOCK) ON Sol.OfficeId = DRD.OfficeId
AND Sol.ProjectId = DRD.ProjectId
AND Sol.ProposalId = DRD.ProposalId
AND Sol.SolutionId = DRD.SolutionId
INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+ 'vw_Office Ofc ON Ofc.OfficeID = DRD.OfficeId'
--SELECT @sql
EXEC sp_executesql @sql,@databasename,@servername
--select * from [OTINSRCPELODD01\SFA].ESSOTISCLC_73DEV.dbo.ssislastrun
When I select the query from SELECT @sql and run it it runs succesfully but when I run using sp_execute sql it gives me error:
Incorrect syntax near 'ESSOTISNL_73DEV'.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 12:02 am
When I use only this-
EXE sp_executesql @sql
it works 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 9:20 am
When ever I have to rely on dynamic SQL I print it out before I execute it. This way I can see if there are any syntax errors in the query. You can simply copy/paste the printed generated SQL and see if it will compile. If that happens 99% of the time all is good.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 13, 2013 at 11:04 am
Kurt W. Zimmerman (9/13/2013)
When ever I have to rely on dynamic SQL I print it out before I execute it. This way I can see if there are any syntax errors in the query. You can simply copy/paste the printed generated SQL and see if it will compile. If that happens 99% of the time all is good.Kurt
yes i did that and able to find the problem n solve it 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply