locked
openrowset stored procedure passing parameters RRS feed

  • Question

  • Hi,

    How do I pass parameters to openrowset, openquery or opendatasource and my parameters are

    parameter1 = @startdate,

    parameter2 = @enddate,

    parameter3 = @country'

     

    SELECT *
    FROM OPENROWSET('SQLNCLI','server=SERVERNAME\INSTANCENAME;trusted_connection=yes','SET FMTONLY OFF exec Procname parameter1, parameter2, parameter3')

    Friday, February 11, 2011 12:02 AM

Answers

  • Use ISO format for dates '20100111' and '20110101'

    Also, your string is created incorrectly, it will never execute.

    Try this code

    DECLARE @sqlCommand varchar(1000)
    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    DECLARE @local int
    SET @StartDate = '20100101'
    SET @EndDate = '20110101'
    SET @local = 1
    SET @sqlCommand = 'SELECT *
    FROM OPENROWSET(''SQLNCLI'',''server=servername;trusted_connection=yes'',''SET FMTONLY OFF exec procname ''''' + convert(varchar(10),@StartDate,112) + ''''',''' + convert(varchar(10),@EndDate,112) + ''''',' + cast(@local as varchar(10)) + ''')'
    
    PRINT @sqlCommand
    --EXEC (@sqlCommand)
    
    

    Also, I found an old article by Louis Davidson and this link confirming my suggestion. 

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Kalman Toth Wednesday, February 16, 2011 1:38 PM
    Friday, February 11, 2011 2:34 PM

All replies

  • Did you try embedding them into the query string?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, February 11, 2011 12:05 AM
  • Hi,

    Put the code into a dynamic sql and execute?

     

    Thanks

    Friday, February 11, 2011 12:12 AM
  • Ok thanks...will try..............
    Friday, February 11, 2011 12:18 AM
  • Hi Naomi,

     

    DECLARE @sqlCommand varchar(1000)
    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    DECLARE @local int
    SET @StartDate = '''11/01/2010'''
    SET @EndDate = '''01/01/2011'''
    SET @local = 1
    SET @sqlCommand = 'SELECT *
    FROM OPENROWSET(''SQLNCLI'',''server=servername;trusted_connection=yes'',''SET FMTONLY OFF exec procname'' ' + @StartDate + ',' + @EndDate + ',' + @local
    PRINT @sqlCommand
    EXEC (@sqlCommand)

     

    gives me coversion error: Conversion failed when converting datetime from character string.

     

    Thanks

    Friday, February 11, 2011 3:46 AM
  • Use ISO format for dates '20100111' and '20110101'

    Also, your string is created incorrectly, it will never execute.

    Try this code

    DECLARE @sqlCommand varchar(1000)
    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    DECLARE @local int
    SET @StartDate = '20100101'
    SET @EndDate = '20110101'
    SET @local = 1
    SET @sqlCommand = 'SELECT *
    FROM OPENROWSET(''SQLNCLI'',''server=servername;trusted_connection=yes'',''SET FMTONLY OFF exec procname ''''' + convert(varchar(10),@StartDate,112) + ''''',''' + convert(varchar(10),@EndDate,112) + ''''',' + cast(@local as varchar(10)) + ''')'
    
    PRINT @sqlCommand
    --EXEC (@sqlCommand)
    
    

    Also, I found an old article by Louis Davidson and this link confirming my suggestion. 

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Kalman Toth Wednesday, February 16, 2011 1:38 PM
    Friday, February 11, 2011 2:34 PM
  • Hi Naomi,

     

    Worked thanks very much.

     

    Regards.

    Friday, February 11, 2011 7:28 PM
  • Hi Naomi,

    Do we need to use date variable value as '20100101', if so why?

    Can't we set the date variable value as '1-1-2010'

    Thanks,


    SequelBug
    Friday, November 4, 2011 11:11 AM
  • ISO Format for date is 'YYYYMMDD' thats what Naomi is using here.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    Friday, November 4, 2011 11:19 AM
    Answerer
  • Thanks Naomi. This worked great.

    In my case I had been reluctant to use 5 single quotes on the concatenation thinking "that's silly and likely won't work". Hah being hardheaded cost me about 2 hours.

    Tuesday, July 9, 2013 3:34 AM
  • Hi Naomi,

    Don't see what I am doing wrong:

    DECLARE @svrName nvarchar(50)
    DECLARE @sqlCommand nvarchar(2000)
    DECLARE @dbcursor CURSOR
    SET @dbcursor = CURSOR FOR
    SELECT distinct svrName FROM servers
    OPEN @dbcursor
    FETCH NEXT
    FROM @dbcursor INTO @svrName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT @svrName
     SET @sqlCommand = 'SELECT * INTO Disk FROM OPENROWSET(''SQLNCLI'',''server=(local);trusted_connection=yes''
          ,''SET FMTONLY OFF exec stp_schijfruimte ''[' 
          + @svrName + ']'''')'
     exec sp_executesql @sqlCommand
     print @sqlCommand

    FETCH NEXT
    FROM @dbcursor INTO @svrName
    END
    CLOSE @dbcursor
    DEALLOCATE @dbcursor

    But I keep getting this error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '10.twonumbers.0.twonumbers'.
    SELECT * INTO DBA.dbo.Disk FROM OPENROWSET('SQLNCLI','server=(local);trusted_connection=yes'
          ,'SET FMTONLY OFF exec stp_schijfruimte '[10.twonumbers.0.twonumbers]'')

    Thursday, May 23, 2019 8:11 AM
  • Generally speaking, it is not very effective to post a "me, too" comment to an old, answered thread. It is FAR better to start your own - and reference any other threads that might be related. 

    In this case, you need to learn to debug your code. The first step is to write the openrowset query as static sql. Get that working. Once you have that working, you can then convert it to dynamic sql. And how do you debug dynamic sql? You can't debug what you cannot see - so you need a method to examine the contents of the string you build - just as Naomi did in the marked answer. When you do that, you will see your string is not a valid sql statement. No one but you knows exactly what value you are trying to pass as a parameter (nor its datatype).  I'll guess that you have a problem trying to embed a string within a string. So refer back to my first suggestion - get the static query working first to provide a guide for your dynamic logic.

    • Proposed as answer by Naomi N Monday, May 27, 2019 12:51 PM
    Thursday, May 23, 2019 11:36 AM