0

I'm trying the following Sql query to move my stored procedure result into table

SELECT *
  INTO #tmpTable
FROM OPENQUERY(WIN2K8\SQL2K12, 'EXEC vcs_gauge  @gauge_name=vs1_bag,@first_rec_time=2014-09-01 09:00:00,@last_rec_time=2014-09-01 10:00:00')

following error is thrown, when I execute the query.

Incorrect syntax near '\'.

I don't want to add linked server .How to resolve this issue?

EDIT1

When I do [win2k8\sql2k12], and first execute the following command

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE

A new message comes

OLE DB provider "SQLNCLI11" for linked server "WIN2K8\SQL2K12" returned message "Deferred prepare could not be completed.". Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
SRY_JAVA
  • 323
  • 3
  • 10
  • 21
  • 1
    OPENQUERY is used to execute sql against a linked server. The first parameter is the name of the linked server. Is `WIN2K8\ SQL2K12` the name of your linked server? If it really is then you need to use brackets `[WIN2K8\ SQL2K12]`. – Mikael Eriksson Jan 06 '15 at 13:08
  • @MikaelEriksson after doing [WIN2K8\ SQL2K12],an error **Server 'WIN2K8\SQL2K12' is not configured for DATA ACCESS** is shown – SRY_JAVA Jan 07 '15 at 04:08

1 Answers1

0

You need to enclose DATETIME values in single quotes. And since your query is in a string itself, those single-quotes need to be doubled / escaped as follows (and you should probably also put the first parameter's value in escaped-single-quotes as it is clearly a string).

You should also fully qualify the stored procedure name with [DatabaseName].[SchemaName]..

And since the vcs_gauge proc uses Dynamic SQL, you need to specify the WITH RESULT SETS clause. For more info on this clause, please see the MSDN page for EXECUTE.

SELECT *
INTO #tmpTable
FROM OPENQUERY([WIN2K8\SQL2K12],
             N'EXEC [DatabaseName].[SchemaName].vcs_gauge
                      @gauge_name = ''vs1_bag'',
                      @first_rec_time = ''2014-09-01 09:00:00'',
                      @last_rec_time = ''2014-09-01 10:00:00''
               WITH RESULT SETS ( { column_specification} );
             ');
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • **Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because every code path results in an error; see previous errors for some of these. Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 1 Could not find stored procedure 'vcs_gauge'.** ERROR is shown ,when I enclose datetime values with quotes as you mentioned. – SRY_JAVA Jan 07 '15 at 04:27
  • @srutzly now following error comes **Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because statement 'EXEC ('WITH C AS ('+@query+') SELECT '+@column_str2+',Avg_Pressure,st1_ring_avg_press' in procedure 'vcs_gauge' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set**. – SRY_JAVA Jan 07 '15 at 04:34
  • @SRY_JAVA Ok. That is a very specific error message. Have you done the suggestion? Due to that proc using dynamic SQL, your only option is to specify the `WITH RESULT SETS` clause. – Solomon Rutzky Jan 07 '15 at 04:40
  • I have not done the suggestion as I don't no how to use RESULT SET with SQl query and moreover I don't no all the datatypes of the columns which are retrieved from stored procedure.Is there some other way to fix it? – SRY_JAVA Jan 07 '15 at 04:50
  • @SRY_JAVA I updated with a basic example and provided a link to the MSDN documentation. It is just a standard column + datatype list, just like `CREATE TABLE`. If you don't know the datatypes, can you temporarily update the proc to add an "INSERT INTO ##temp" just before the SELECT, then run it once, then run `EXEC tempdb.dbo.sp_help N'##temp';`? – Solomon Rutzky Jan 07 '15 at 05:00