Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Answered: select * from openrowset

    I am using the following code and it gives me an error when the datetime parameter is a PM time, if they are AM times then no problem, just the PM won't work:

    DECLARE @SQLString1 nvarchar(1000)
    SET @SQLString1 = 'select * from openrowset(''SQLOLEDB'',''Server=Servername;uid=us er;pwd=password',''SET FMTONLY OFF; exec StoredProcedure @Start=''''' + convert(varchar, @StartDate, 120) + ''''',@End=''''' + convert(varchar, @EndDate, 120) + ''''''')'
    INSERT INTO @DT EXEC (@SQLString1)

    Error message is:
    Cannot process the object "SET FMTONLY OFF; exec StoredProcedure @Start='2015-08-17 18:00:00',@End='2015-08-18 06:00:00'". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    Does anyone know what causes this and how to get around it?
    Please help

  2. Best Answer
    Posted by plsh

    "I found the solution. The execution of the SP was returning a warning message that if executed "normally" would be ignored but this way was returned. Anyway after the SET FMTONLY OFF; I needed to add SET ANSI_WARNINGS OFF;

    That solved my problem, no more issues, works 100% again."


  3. #2
    Join Date
    Aug 2015
    Location
    127.0.0.1
    Posts
    1
    DECLARE @SQLString1 nvarchar(1000)
    SET @SQLString1 = 'select * from openrowset(''SQLOLEDB'',''Server=Servername;uid=us er;pwd=password'',''SET FMTONLY OFF; exec StoredProcedure @Start=''''' + convert(varchar, @StartDate, 120) + ''''',@End=''''' + convert(varchar, @EndDate, 120) + ''''''')'

    unsure if it will matter but you have a space in your uid and also you're missing a single quotation mark after the password...

    what is the data types for variables @Start and @End?

  4. #3
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Thank you. That was a copy error, the space in my username and the missing quote after password. They are there.

    I have tried declaring the @Start and @End as both nvarchar and or as DateTime. Neither works.

    Everything works 100% if the times are am to am but as soon as they are pm to am or am to pm, so in other words a pm is in one of the parameters the execute fails.

  5. #4
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    I found the solution. The execution of the SP was returning a warning message that if executed "normally" would be ignored but this way was returned. Anyway after the SET FMTONLY OFF; I needed to add SET ANSI_WARNINGS OFF;

    That solved my problem, no more issues, works 100% again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •