i am trying execute this query but i got a error:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 11
Invalid object name '#test1'.
my code:
select * from openrowset ('SQLOLEDB','DRIVER={SQL Server};SERVER=10.12.131.58;UID=sa;PWD=desarrollo','
create table #test1
(
id int,
name1 varchar(50)
)
insert into #test1
select cliente,nomcli from opepte.dbo.clientes
select * from #test1
/*this is a example but in real query i need manipulate this information and return
a resulset with few rows
*/
')
but this other query works fine.
select * from openrowset ('SQLOLEDB','DRIVER={SQL Server};SERVER=10.12.131.58;UID=sa;PWD=desarrollo','
create table #test1
(
id int,
name1 varchar(50)
)
--insert into #test1
select cliente,nomcli from opepte.dbo.clientes
--select * from #test1
/*this is a example but in real query i need manipulate this information and return
a resulset with few rows
*/
')
Note: insert into #test1 and select * from #test1 are coment
Also consider more robust and secure provider and connection string: 'SQLNCLI', 'Server=localhost;Integrated Security=SSPI;Trusted_Connection=yes;'
select * from openrowset ('SQLOLEDB','DRIVER={SQL Server};SERVER=10.12.131.58;UID=sa;PWD=desarrollo', N'
SET FMTONLY OFF
SET NOCOUNT ON
declare @q int = 1
declare @test1 table
(
id int,
name1 varchar(50)
)
insert into @test1
select 1,''q''
insert into @test1
select 1,''q''
select * from @test1
/*this is a example but in real query i need manipulate this information and return
a resulset with few rows
*/
')