locked
exec () AT LikedServer Failing RRS feed

  • Question

  • DECLARE @SqlStr VARCHAR(Max)
    
    
    SET @SqlStr =
    
          'DECLARE @SQL varchar(max)
    
                            SET @SQL = '' CREATE SCHEMA  '' +SchemaName
                             PRINT (@SQL)
    						SET @metasql = ''
    							USE test
    							EXEC (''''' + REPLACE(@SqlStr, '''''', '''''''') + ''''')
    							''
    
    						EXEC (@metasql)
                      
    
          select @SQL = (SELECT ''select * into [db1].''+Schema_Name+''.''+ Table_Name + '' from #temp;'' + CHAR(10) + CHAR(13) from      [db2].[dbo].[table]
    
          FOR XML PATH(''''),TYPE).value(''.'',''varchar(max)'')
    
          EXEC (@SQL)'
    
    EXEC (@SqlStr) AT [GAALPSVR04CD]
    

    My code skeleton is something like this. When I am trying to execute this, I am getting below error.

    OLE DB provider "SQLNCLI11" for linked server "MyLikedServer" returned message "Command text was not set for the command object.".

    Msg 7215, Level 17, State 1, Line 68

    Could not execute statement on remote server 'MyLinkedServer'.

    Wednesday, 17 September 2014 1:41 PM

Answers

  • SET CONCAT_NULL_YIELDS_NULL OFF

    The variable is initially null. You either fill it with an empty string or set this connection parameter off.

    • Marked as answer by naveej Wednesday, 17 September 2014 3:24 PM
    Wednesday, 17 September 2014 2:15 PM

All replies

  • >       EXEC ('''''

    +REPLACE(@SqlStr,'''''','''''''')+''''')


    What are you trying to do? It does not look right.

    Dynamic SQL: http://www.sqlusa.com/bestpractices/dynamicsql/

    Always do a PRINT to check the dynamic SQL prior to attempting execution.



    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    • Edited by Kalman Toth Wednesday, 17 September 2014 1:54 PM
    Wednesday, 17 September 2014 1:50 PM
  • We cannot use CREATE script with any other statements. So, as I need to execute this on a specific database, I added that.

    It does not make any difference even after deleting that statement from the code. I am getting the same error.


    Actually, the create schema code is within while loop.
    • Edited by naveej Wednesday, 17 September 2014 2:01 PM
    Wednesday, 17 September 2014 1:59 PM
  • SET CONCAT_NULL_YIELDS_NULL OFF

    The variable is initially null. You either fill it with an empty string or set this connection parameter off.

    • Marked as answer by naveej Wednesday, 17 September 2014 3:24 PM
    Wednesday, 17 September 2014 2:15 PM
  • What output do you get from "PRINT @SqlStr"?

    Judging from the error message, @SqlStr is NULL. I'm not risking my sanity on trying to understand this maze of nested quotes, but it looks funky that you have replace(@SqlStr) in the middie of the assignment to the nested variable.

    It also looks funny that you seem to assign @SQL to a value which your print but never execute before you set @SQL again. Of course, since this is in the nested string you have not discovered this yet.

    Use this functions to built your nested SQL string in a structured way:
    http://www.sommarskog.se/dynamic_sql.html#quotestring


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, 17 September 2014 2:46 PM
  • seems there was a issue between sqlserver and oracle client drivers.

    workaround:

    1.create an ODBC 32/64 system DSN  with your oracle server

    2. Create a Linked server with MS OLEDB Provider for ODBC drivers and give the datasource name is SYSTEM DSN name

    3. use the same user name and password which you have used while creating system DSN

    4. set linked server setting like RPC ,RPC out as true.

    Thursday, 19 December 2019 9:46 AM