none
Passing parameter to SQL connection RRS feed

  • Question

  • Hi Folks

    I was wondering can you help me with something. From last 3 weeks im scratching my heat trying find solution for something what should be really simple. 

    I was trying to ask for help on T-sql forum unfortunately answer i got didn't work in my scenario.

    I'm using Power Query to pull info from SQL server. I'm reusing SQL query capture directly from XEvent Profiler. I was checking what is triggered by other software. And im succeeded!  Followed Query return nicely results into power query.

    = Sql.Database((CBE_Source), "cbewrdb", [Query="Exec WRSP_ProcessRequest @DataXML='<RequestData RequestID=""JRNLRPSEC"">#(lf)<CustomData >#(lf)<JournalOptionsTemplate Description="""" Period=""Custom"" DateTimeFrom=""2018-06-29 23:59:59"" DateTimeTo=""2018-06-30 23:59:59"" Is24Hour=""0"" Is24HourEOD=""0"" IsRealTime=""0"" RealTimeInterval=""5"" IsLimitListSize=""0"" LimitListSize=""100"" ShowOnlyTotals=""0"" ReceiptNoFrom="""" ReceiptNoTo="""" CardNoFrom="""" CardNoTo="""" IsTotalValue=""1"" IsTotalQuantity=""1"" IsValueTotal=""1"" IsQuantityTotal=""1"" Report=""4"" SiteID=""01081"" SiteDesc=""01081"" OnlyActiveCashiers=""1"" >#(lf)<DiscountReasons>#(lf)</DiscountReasons>#(lf)</JournalOptionsTemplate>#(lf)</CustomData>#(lf)</RequestData>'", MultiSubnetFailover=true])

    Now, to make it work dynamically, followed strings in connection have to be replaced with parameter(pulling info from excel cell): Source,DateTimeFrom,DateTimeTo,SiteID.

    I manage to force to work Source, above you can see (CBE_Source) this is working parameter. Unfortunately im straggled with Date and SiteID. I have Date parameter taken from excel cell looking exactly the same as "2018-06-29 23:59:59". Ones i have it formatted as Text(DATE_MIN_T) second time as (DATE_MIN_T2),

    No matter what parameter i use im getting this error:

    DataSource.Error: Microsoft SQL: Conversion failed when converting date and/or time from character string.
    Details:
        DataSourceKind=SQL
        DataSourcePath=1.10.81.2\sqlexpress;cbewrdb
        Message=Conversion failed when converting date and/or time from character string.
        Number=241
        Class=16

    The same parameter taken as cut from parameter and paste to query working fine but not if is passed as parameter...

    Also if i use SiteID (values look like: 00644 or 01081) dont give me any error but just empty table.....

    Its like magic...:-P

    Below how query should like in my mind in perfect world.

    = Sql.Database((CBE_Source), "cbewrdb", [Query="Exec WRSP_ProcessRequest @DataXML='<RequestData RequestID=""JRNLRPSEC"">#(lf)<CustomData >#(lf)<JournalOptionsTemplate Description="""" Period=""Custom"" DateTimeFrom=""(DATE_MIN_T)"" DateTimeTo=""(DATE_MAX_T)"" Is24Hour=""0"" Is24HourEOD=""0"" IsRealTime=""0"" RealTimeInterval=""5"" IsLimitListSize=""0"" LimitListSize=""100"" ShowOnlyTotals=""0"" ReceiptNoFrom="""" ReceiptNoTo="""" CardNoFrom="""" CardNoTo="""" IsTotalValue=""1"" IsTotalQuantity=""1"" IsValueTotal=""1"" IsQuantityTotal=""1"" Report=""4"" SiteID=""(Site)"" SiteDesc=""(Site)"" OnlyActiveCashiers=""1"" >#(lf)<DiscountReasons>#(lf)</DiscountReasons>#(lf)</JournalOptionsTemplate>#(lf)</CustomData>#(lf)</RequestData>'", MultiSubnetFailover=true])

    Any chance you see what im doing wrong?

    If im using parameter after results are returned into table all working fine but this is not a solution as database is huge and need to be limited from start..  So change have to be done in SQL connection.

    Please help!

    Thanks!

    Let me know if you need more info.

    Regards,

    Lukas

    Monday, July 2, 2018 8:11 AM

Answers

  • the issue is right now the whole text is string rather than combination of string & references

    "...DateTimeFrom=""(DATE_MIN_T)"" DateTimeTo=""(DATE_MAX_T)""

    this part should look more like this (not sure about the quotes setup):

    "...DateTimeFrom=""" & DATE_MIN_T & """ DateTimeTo=""" & DATE_MAX_T & """



    Monday, July 2, 2018 9:12 AM

All replies

  • the issue is right now the whole text is string rather than combination of string & references

    "...DateTimeFrom=""(DATE_MIN_T)"" DateTimeTo=""(DATE_MAX_T)""

    this part should look more like this (not sure about the quotes setup):

    "...DateTimeFrom=""" & DATE_MIN_T & """ DateTimeTo=""" & DATE_MAX_T & """



    Monday, July 2, 2018 9:12 AM
  • Thanks a million Marcin!!

    I think i used all possible variances of (") and (&) and (') but i didn't think to put 3x (""") quotation marks. 

    Now its working!

    Regards!

    Lukas

    Monday, July 2, 2018 9:44 AM