insert vales into temp tble from an executable SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

insert vales into temp tble from an executable SP

Author

Topic

harikrishnan.s@eisplc.com
Starting Member

4 Posts

Posted - 2008-11-05 : 02:27:43

Hello
here my problem
we have to isert a SP record set values int temp tabel....
The SP willl change dynamically

SET @sproc = @strTables // proc name it will change runtime
SET @sproc_params = @sproc_paramsfrom // proc parameter
SET @query = @sproc + ' ' + @sproc_params

i got some code

CREATE TABLE #TEMP1(TaskName varchar(2000))
insert into #TEMP1(TaskName)exec SP_executesql @query
SELECT * FROM #TEMP1

Here creating temp table #temp1
But at the creating time here we specifiyng the field name TaskName varchar(2000)

But in this our requirement. we have to create the temp table the basis of exec SP_executesql @query
That means the recordset(exec SP_executesql @query) fields and datas should be same in the temp table

Harikrishnan.S

Technoprak TVM IND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:49:39

will columns in resultset change altogether or will the columns be same but number of columns will only vary?

harikrishnan.s@eisplc.com
Starting Member

4 Posts

Posted - 2008-11-05 : 02:58:45

Thaks for ur valable commants............................

The coloumn will change.................any time............ we changing the sp name and params aspx page.......................got??

Harikrishnan.S

Technoprak TVM IND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 03:21:41

Not sure why your resultset will vary like that.
Anyways one way is not to precreate the table. just use

SELECT fields... INTO #YourTempTable FROM OPENROWSET(SQLCNI1,...,'EXEC spname...')

more details here
(2nd method)

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

harikrishnan.s@eisplc.com
Starting Member

4 Posts

Posted - 2008-11-05 : 03:35:25

ok
I added this code
but getting this msg

Access to the remote server is denied because no login-mapping exists.

am using server 70.87.15.210 username a and pass b

so hw can we implement this code basis of this

Technoprak TVM IND

harikrishnan.s@eisplc.com
Starting Member

4 Posts

Posted - 2008-11-05 : 03:36:53

select @strFields

into
MyTemp
from
OPENROWSET('SQLOLEDB','Data Source=seoservice;Trusted_Connection=yes;
Integrated Security=SSPI','exec SP_executesql @query')

select * from MyTemp

drop table MyTemp

Any pblm in this

HArikrishnan.S
Urg pls

Technoprak TVM IND

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources