problem with openquery

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  February 3, 2014 at 1:49 am

  #283113

  hello all.

  I have this sp:

  ALTER PROCEDURE [dbo].[IcanSp_HumanResourceDailyMissionRegister] @EC INT

  AS

  BEGIN

  DECLARE

  @RoleID INT,

  @NationalCode NVARCHAR(15),

  @MissionEndDate NVARCHAR(20),

  @MissionStartDate NVARCHAR(20),

  @MissionConfirmDate NVARCHAR(10),

  @MissionNO INT,

  @MissionPlace NVARCHAR(50),

  @MissionCityID INT,

  @MissionDaysCount INT

  SELECT

  @RoleID=employee,

  @MissionStartDate=dbo.[GregorindDate2Jalali] (fromDate),

  @MissionEndDate=dbo.[GregorindDate2Jalali] (ToDate),

  @MissionConfirmDate=dbo.[GregorindDate2Jalali] (ConfirmDate),

  @MissionNO=cast(replace(EntityNumber,'/','') as int),

  @MissionDaysCount=MissionDuration,

  @MissionCityID=location

  FROM

  Entity_DailyMission where EntityCode=@EC

  SELECT @NationalCode = NativeID FROM Users,Roles WHERE Users.User_ID=roles.UserID AND roles.Role_ID=@RoleID

  SELECT @MissionPlace = place FROM Entity_baseForm WHERE Activeplace=1 and EntityCode=@MissionCityID

  DECLARE @QueryPart1 NVARCHAR(MAX);

  DECLARE @QueryPart2 NVARCHAR(MAX);

  DECLARE @Query NVARCHAR(MAX);

  IF @MissionConfirmDate IS NULL SET @MissionConfirmDate=''

  SET @QueryPart1 ='SELECT * from openquery ([192.168.101.197\ps_2008],'

  SET @QueryPart2 ='[Ardakan_new].dbo.mamoriyat '

  + @NationalCode + ',2,''' + @MissionStartDate + ''',''' + @MissionEndDate + ''','''

  + @MissionConfirmDate + ''',' + Convert(nvarchar,@MissionNO)+','''

  + @MissionConfirmDate + ''',''' + @MissionConfirmDate + ''',1,'

  + Convert(nvarchar,@MissionDaysCount)+ ',''' + @MissionPlace + ''''

  SELECT @Query = @QueryPart1 + '''' + dbo.DuplicateQuotes(@QueryPart2) + ''')'

  EXECUTE sp_executesql @Query

  End

  I add 192.168.101.197\ps_2008 linkserver on mu sql and mamoriyat sp is:

  ALTER PROCEDURE [dbo].[mamoriyat] (@codmeli VARCHAR(15)--کد ملي

  ,@noeform INT--برون شهري=2 و درون شهري=1

  ,@datestart VARCHAR(10)--تاريخ شروع ماموريت

  ,@dateend VARCHAR(10)--تاريخ پايان ماموريت

  ,@datemali VARCHAR(10)--تاريخ اجرا

  ,@numhokm INT--شماره حکم

  ,@datesabt VARCHAR(10)--تاريخ ثبت ماموريت

  ,@dateedari VARCHAR(10)--تاريخ تاييد اداري

  ,@okdateacc VARCHAR(10)--تاريخ تاييد مالي

  --,@sal INT,@mah INT

  ,@countroz INT--تعداد ايام ماموريت

  ,@shahr NVARCHAR(50)--شهر محل ماموريت

  )

  AS

  SELECT 'Start'

  IF EXISTS(SELECT * FROM pay39 WHERE [Pay39_NOHokm]=@numhokm )

  DELETE FROM pay39 WHERE [Pay39_NOHokm]=@numhokm

  INSERT INTO [Ardakan_new].[dbo].[PAY39]

  ([fk_key]

  ,[list_pk]

  ,[Pay39_DataFrom]

  ,[Pay39_Datato]

  ,[Pay39_DataMali]

  ,[Pay39_OKHokm]

  ,[Pay39_OKAcc]

  ,[Pay39_NOHokm]

  ,[Pay39_DataHokm]

  ,[Pay39_OKHokmDate]

  ,[Pay39_OKAccDate]

  ,[Pay39_Cancel]

  ,[FLDM1]

  ,[FLDM2])

  VALUES

  ((SELECT personeli_pk FROM pay2

  WHERE pay2.Fld_CodeMeli=@codmeli),

  @noeform,

  @datestart,

  @dateend,

  @datemali,

  'true',

  'true',

  @numhokm,

  @datesabt,

  @dateedari,

  @okdateacc,

  'FALSE',

  @countroz,

  @shahr)

  SELECT 'Start'

  but when I exec my sp,insert dosen't work.what to i do?

  please guide me immediately:crying:

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 3, 2014 at 6:04 am

  #1685449

  Put a PRINT statement prior to the EXEC.

  PRINT @Query

  EXECUTE sp_executesql @Query

  Run the batch. Copy the output from the Messages window and paste it here.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden