none
Linq To SQL does it support StoreProc/Open Query ? RRS feed

  • Question

  • I was able to use a Store Procedure that has a Open Query to a server Progress 9.1, all is fine in my vb code if my open query as no parameters. But if there's a parameter I'm only able to Execute it from the DataBase Explorer and from my vb nothing no data ?!

    Public Class Form1

        Private records As New LINQ_JOBDataContext

    _____________________________________________________________________________________

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

            Dim parm As String = TextBox1.Text

            Dim rec = records.TEST2(parm)

            DataGridView1.DataSource = rec

        End Sub

    End Class

    ______________________________________________________________________________________

    CREATE PROCEDURE [dbo].[TEST4]

    @pEmID varchar(10)

    AS

           BEGIN

           SET NOCOUNT ON

           DECLARE @2Q          VARCHAR(2)

           DECLARE @CMD         NVARCHAR(MAX)

           DECLARE @BASESELECT  VARCHAR(MAX)

           DECLARE @PwHERE      vARCHAR(mAX)

          

           SET @2Q = CHAR(39) + CHAR(39)

           SET @PWHERE = ' AND H.EmpId  = ' + @2Q + @pEmID + @2Q

           SET @BASESELECT = ' SELECT ucase(Company), EmpID ,EmpStatus ,FirstName ,MiddleInitial ,LastName  ,Name  ,JCDept ,Address ,Address2 ,City ,"State"  ,ZIP ,Country  ,Phone ,EMailAddress as EmailAddress ,EmgContact   as ContactPerson ,EmgPhone        as ContactPhone, Shift, LaborRate

    FROM pub."EmpBasic" H

    WHERE H.Company = ''''THECie'''' ' + @PWHERE

    SET @CMD =    N'SELECT * FROM OPENQUERY ( vntglive, ''' + @BASESELECT + N' ''  ) '

    --print @cmd

    EXEC master.dbo.sp_executesql @CMD

    END

    return

    Monday, October 12, 2015 10:59 PM

Answers

  • But if there's a parameter I'm only able to Execute it from the DataBase Explorer and from my vb nothing no data ?!

    I found a Remark about OPENQUERY, it says

    OPENQUERY does not accept variables for its arguments.

    OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name. For example:

    EXEC SeattleSales.master.dbo.xp_msver
    

    Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. In particular, filter or join conditions applied on the result of one of those calls have no effect on the results of the other. Maybe this is the cause of issue.

    You can refer to see it in detail. https://msdn.microsoft.com/en-us/library/ms188427.aspx

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 14, 2015 1:57 AM
    Moderator