Loading ...
Sorry, an error occurred while loading the content.
 

XML loading problem

Expand Messages
  • robert sackey
    Hi, I have some xml files I want to load into SSIS. I use the XML source adapter. I generate an xsd for the xml files but on one I get the message: Error at
    Message 1 of 13 , Jul 28, 2010
      Hi,

      I have some xml files I want to load into SSIS. I use the XML source

      adapter. I generate an xsd for the xml files but on one I get the message:

      Error at Data Flow Task 1 [XML Source [1]]: The XML Source Adapter does not

      support mixed content model on Complex Types.



      How should I deal with this?




      [Non-text portions of this message have been removed]
    • Noman Aftab
      Hi, I am trying to call a function on a linked server with the following sql: --declarations declare @prefix varchar(1) declare @Code varchar(10) declare
      Message 2 of 13 , Aug 4, 2010
        Hi,
        I am trying to call a function on a linked server with the following sql:


        --declarations
        declare @prefix varchar(1)
        declare @Code varchar(10)
        declare @number varchar(10)
        declare @cost decimal(22,8)
        declare @str1 varchar(max)

        --values
        set @prefix = 'V'
        set @Code = 'A100'
        set @number = 'P90'


        --first attempt (concatenating a string and passing it to openquery)
        set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
        '''+@number+''') as COST'
        SELECT @cost = COST from OpenQuery(SERVER1, @str1)
        --error on first attempt (Incorrect syntax near '@str1'.)

        --second attempt (directly running the function)
        select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
        --SERVER1 is the name of the linked server
        --error on second attempt (Invalid column name 'SERVER1'.)

        --third attempt (concatenation without variable in openquery)
        SELECT @cost = COST from OPENQUERY(SERVER1, 'select
        DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as COST')
        --error on third attempt (Incorrect syntax near '+'.)

        --fourth attempt (which works, if values are hard coded in the string)
        SELECT @cost = COST from OPENQUERY(SERVER1, 'select
        DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')


        Please Help!



        Best Wishes,
        Noman Aftab
      • p_livengood
        Noman; First Attempt - To my knowledge passing variable strings to the OpenQuery function is not supported. You might take a look at
        Message 3 of 13 , Aug 4, 2010
          Noman;
          First Attempt - To my knowledge passing variable strings to the OpenQuery function is not supported. You might take a look at http://support.microsoft.com/kb/314520.

          Second Attempt - UDFs aren't supported directly on linked servers. Please look at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.

          Third Attempt - Quotes get tricky, but the article from First Attempt has a link to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1, 'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''', '''''+@number+''''') as COST')

          Forth Attemp - This shows that it will work, its just a matter of getting the quotes and stuff correct.

          HTH
          Paul


          --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
          >
          >
          >
          >
          > Hi,
          > I am trying to call a function on a linked server with the following sql:
          >
          >
          > --declarations
          > declare @prefix varchar(1)
          > declare @Code varchar(10)
          > declare @number varchar(10)
          > declare @cost decimal(22,8)
          > declare @str1 varchar(max)
          >
          > --values
          > set @prefix = 'V'
          > set @Code = 'A100'
          > set @number = 'P90'
          >
          >
          > --first attempt (concatenating a string and passing it to openquery)
          > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
          > '''+@number+''') as COST'
          > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
          > --error on first attempt (Incorrect syntax near '@str1'.)
          >
          > --second attempt (directly running the function)
          > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
          > --SERVER1 is the name of the linked server
          > --error on second attempt (Invalid column name 'SERVER1'.)
          >
          > --third attempt (concatenation without variable in openquery)
          > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
          > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as COST')
          > --error on third attempt (Incorrect syntax near '+'.)
          >
          > --fourth attempt (which works, if values are hard coded in the string)
          > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
          > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
          >
          >
          > Please Help!
          >
          >
          >
          > Best Wishes,
          > Noman Aftab
          >
        • Noman Aftab
          exec SERVER1.DB1.dbo.sp_executesql N SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number) , N @code varchar(50), @number varchar(50) , @code= @temp, @number =
          Message 4 of 13 , Aug 4, 2010
            exec SERVER1.DB1.dbo.sp_executesql
            N'SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
            N'@code varchar(50), @number varchar(50)', @code= @temp, @number = @num

            Thanks Paul for the link. The above sql has worked, but how to get the output
            into a scalar variable?

            i.e. output into @cost for further use.
            Best Wishes,
            Noman Aftab


            http://www.jalandhari.qsh.eu





            ________________________________
            From: p_livengood <p_livengood@...>
            To: SQLQueriesNoCode@yahoogroups.com
            Sent: Wed, 4 August, 2010 10:30:34 PM
            Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
            parameters




            Noman;
            First Attempt - To my knowledge passing variable strings to the OpenQuery
            function is not supported. You might take a look at
            http://support.microsoft.com/kb/314520.

            Second Attempt - UDFs aren't supported directly on linked servers. Please look
            at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.

            Third Attempt - Quotes get tricky, but the article from First Attempt has a link

            to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1,
            'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''',
            '''''+@number+''''') as COST')

            Forth Attemp - This shows that it will work, its just a matter of getting the
            quotes and stuff correct.


            HTH
            Paul

            --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
            >
            >
            >
            >
            > Hi,
            > I am trying to call a function on a linked server with the following sql:
            >
            >
            > --declarations
            > declare @prefix varchar(1)
            > declare @Code varchar(10)
            > declare @number varchar(10)
            > declare @cost decimal(22,8)
            > declare @str1 varchar(max)
            >
            > --values
            > set @prefix = 'V'
            > set @Code = 'A100'
            > set @number = 'P90'
            >
            >
            > --first attempt (concatenating a string and passing it to openquery)
            > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
            > '''+@number+''') as COST'
            > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
            > --error on first attempt (Incorrect syntax near '@str1'.)
            >
            > --second attempt (directly running the function)
            > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
            > --SERVER1 is the name of the linked server
            > --error on second attempt (Invalid column name 'SERVER1'.)
            >
            > --third attempt (concatenation without variable in openquery)
            > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
            > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as
            >COST')
            > --error on third attempt (Incorrect syntax near '+'.)
            >
            > --fourth attempt (which works, if values are hard coded in the string)
            > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
            > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
            >
            >
            > Please Help!
            >
            >
            >
            > Best Wishes,
            > Noman Aftab
            >
          • p_livengood
            To get the results assigned to your local variable @cost you will need to use an output variable in the EXECUTE SQL statement. I believe the following should
            Message 5 of 13 , Aug 5, 2010
              To get the results assigned to your local variable @cost you will need to use an output variable in the EXECUTE SQL statement. I believe the following should work

              exec SERVER1.DB1.dbo.sp_executesql
              N'SELECT @CostOUT = DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
              N'@code varchar(50), @number varchar(50), @CostOUT NUMERIC(18,2) OUTPUT',
              @code = @temp,
              @number = @num,
              @costOUT = @cost OUTPUT


              HTH
              Paul
              --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
              >
              > exec SERVER1.DB1.dbo.sp_executesql
              > N'SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
              > N'@code varchar(50), @number varchar(50)', @code= @temp, @number = @num
              >
              > Thanks Paul for the link. The above sql has worked, but how to get the output
              > into a scalar variable?
              >
              > i.e. output into @cost for further use.
              > Best Wishes,
              > Noman Aftab
              >
              >
              > http://www.jalandhari.qsh.eu
              >
              >
              >
              >
              >
              > ________________________________
              > From: p_livengood <p_livengood@...>
              > To: SQLQueriesNoCode@yahoogroups.com
              > Sent: Wed, 4 August, 2010 10:30:34 PM
              > Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
              > parameters
              >
              >
              >
              >
              > Noman;
              > First Attempt - To my knowledge passing variable strings to the OpenQuery
              > function is not supported. You might take a look at
              > http://support.microsoft.com/kb/314520.
              >
              > Second Attempt - UDFs aren't supported directly on linked servers. Please look
              > at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.
              >
              > Third Attempt - Quotes get tricky, but the article from First Attempt has a link
              >
              > to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1,
              > 'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''',
              > '''''+@number+''''') as COST')
              >
              > Forth Attemp - This shows that it will work, its just a matter of getting the
              > quotes and stuff correct.
              >
              >
              > HTH
              > Paul
              >
              > --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@> wrote:
              > >
              > >
              > >
              > >
              > > Hi,
              > > I am trying to call a function on a linked server with the following sql:
              > >
              > >
              > > --declarations
              > > declare @prefix varchar(1)
              > > declare @Code varchar(10)
              > > declare @number varchar(10)
              > > declare @cost decimal(22,8)
              > > declare @str1 varchar(max)
              > >
              > > --values
              > > set @prefix = 'V'
              > > set @Code = 'A100'
              > > set @number = 'P90'
              > >
              > >
              > > --first attempt (concatenating a string and passing it to openquery)
              > > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
              > > '''+@number+''') as COST'
              > > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
              > > --error on first attempt (Incorrect syntax near '@str1'.)
              > >
              > > --second attempt (directly running the function)
              > > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
              > > --SERVER1 is the name of the linked server
              > > --error on second attempt (Invalid column name 'SERVER1'.)
              > >
              > > --third attempt (concatenation without variable in openquery)
              > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
              > > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as
              > >COST')
              > > --error on third attempt (Incorrect syntax near '+'.)
              > >
              > > --fourth attempt (which works, if values are hard coded in the string)
              > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
              > > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
              > >
              > >
              > > Please Help!
              > >
              > >
              > >
              > > Best Wishes,
              > > Noman Aftab
              > >
              >
            • Noman Aftab
              Thanks again Paul, the OUTPUT parameter worked. (Y) Best Wishes, Noman Aftab http://www.corpus.quran.com/wordbyword.jsp http://www.jalandhari.qsh.eu
              Message 6 of 13 , Aug 8, 2010
                Thanks again Paul, the OUTPUT parameter worked. (Y)
                Best Wishes,
                Noman Aftab


                http://www.corpus.quran.com/wordbyword.jsp
                http://www.jalandhari.qsh.eu





                ________________________________
                From: p_livengood <p_livengood@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Fri, 6 August, 2010 12:17:43 AM
                Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
                parameters


                To get the results assigned to your local variable @cost you will need to use an
                output variable in the EXECUTE SQL statement. I believe the following should
                work

                exec SERVER1.DB1.dbo.sp_executesql
                N'SELECT @CostOUT = DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
                N'@code varchar(50), @number varchar(50), @CostOUT NUMERIC(18,2) OUTPUT',
                @code = @temp,
                @number = @num,
                @costOUT = @cost OUTPUT

                HTH
                Paul
                --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
                >
                > exec SERVER1.DB1.dbo.sp_executesql
                > N'SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
                > N'@code varchar(50), @number varchar(50)', @code= @temp, @number = @num
                >
                > Thanks Paul for the link. The above sql has worked, but how to get the output
                > into a scalar variable?
                >
                > i.e. output into @cost for further use.
                > Best Wishes,
                > Noman Aftab
                >
                >
                > http://www.jalandhari.qsh.eu
                >
                >
                >
                >
                >
                > ________________________________
                > From: p_livengood <p_livengood@...>
                > To: SQLQueriesNoCode@yahoogroups.com
                > Sent: Wed, 4 August, 2010 10:30:34 PM
                > Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
                > parameters
                >
                >
                >
                >
                > Noman;
                > First Attempt - To my knowledge passing variable strings to the OpenQuery
                > function is not supported. You might take a look at
                > http://support.microsoft.com/kb/314520.
                >
                > Second Attempt - UDFs aren't supported directly on linked servers. Please look
                >
                > at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.
                >
                > Third Attempt - Quotes get tricky, but the article from First Attempt has a
                >link
                >
                >
                > to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1,
                >
                > 'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''',
                > '''''+@number+''''') as COST')
                >
                > Forth Attemp - This shows that it will work, its just a matter of getting the
                > quotes and stuff correct.
                >
                >
                > HTH
                > Paul
                >
                > --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@> wrote:
                > >
                > >
                > >
                > >
                > > Hi,
                > > I am trying to call a function on a linked server with the following sql:
                > >
                > >
                > > --declarations
                > > declare @prefix varchar(1)
                > > declare @Code varchar(10)
                > > declare @number varchar(10)
                > > declare @cost decimal(22,8)
                > > declare @str1 varchar(max)
                > >
                > > --values
                > > set @prefix = 'V'
                > > set @Code = 'A100'
                > > set @number = 'P90'
                > >
                > >
                > > --first attempt (concatenating a string and passing it to openquery)
                > > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
                > > '''+@number+''') as COST'
                > > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
                > > --error on first attempt (Incorrect syntax near '@str1'.)
                > >
                > > --second attempt (directly running the function)
                > > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as
                COST
                > > --SERVER1 is the name of the linked server
                > > --error on second attempt (Invalid column name 'SERVER1'.)
                > >
                > > --third attempt (concatenation without variable in openquery)
                > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
                > > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as
                > >COST')
                > > --error on third attempt (Incorrect syntax near '+'.)
                > >
                > > --fourth attempt (which works, if values are hard coded in the string)
                > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
                > > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
                > >
                > >
                > > Please Help!
                > >
                > >
                > >
                > > Best Wishes,
                > > Noman Aftab
                > >
                >






                [Non-text portions of this message have been removed]
              Your message has been successfully submitted and would be delivered to recipients shortly.