How to create a table (structure) based on a result of a stored procedure

Dear all,
There is a stored procedure that returns a result set consisting of some 30
columns of different types.
How can I automatically (i.e. not by means of CREATE TABLE statement) create
a table with a structure that correspons to the returned result set?
I mean something like:
SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
(The above unfortunately doesn't work).
To be specific: the stored procedure in question is:
sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
Any help would be greatly appreciated!
Thank you in advance.
Best regards,
AndrewAndrew
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
No , you have to know how many "columns" stored procedures will be returned
insert into tablename exec sp
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
sp_storedprocedure)
instead of select you can use INSERT INTO will work
eg:
create table #t(i int)
insert into #t exec myproc
create proc myproc
as
select 1
vinu
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||Dear Uri,
As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
> No , you have to know how many "columns" stored procedures will be
> returned
> insert into tablename exec sp
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>|||As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
> sp_storedprocedure)
> instead of select you can use INSERT INTO will work
> eg:
> create table #t(i int)
> insert into #t exec myproc
>
> create proc myproc
> as
> select 1
>
> vinu
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>|||Andrew
Well, in that case take a look into dynamic sql
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>|||Dear Uri,
Thank you for the hint. If I understand you correctly, you suggest that I
should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
That's OK, but how can I get (programmatically) the list of "columns" that
are returned by the stored procedure?
My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
So, the situation is as follows: I have got a stored procedure and want to
put its result set into a new table.
Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
but instead of ExistingTableOrView there is a stored procedure.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Andrew
> Well, in that case take a look into dynamic sql
>
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>|||Andrew
--This procedure returns three or two columns from Orders Table depends on
parameter
create proc usp_test
@.orderid int =10248
as
if @.orderid=10248
select orderid,orderdate from orders where orderid=@.orderid
else
select orderid,orderdate,CustomerID from orders where orderid=@.orderid
--I use OPENROWSET command to run this SP and get the data out to temporary
table
select * into #t
from
openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
'set fmtonly off; exec usp_test 10249') as p
select * from #t
drop table #t
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for the hint. If I understand you correctly, you suggest that I
> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
> That's OK, but how can I get (programmatically) the list of "columns" that
> are returned by the stored procedure?
> My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
> So, the situation is as follows: I have got a stored procedure and want to
> put its result set into a new table.
> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
> but instead of ExistingTableOrView there is a stored procedure.
> Best regards,
> Andrew
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>>
>
>|||Well .. it is not possible to use select into...exec
vt
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OXdJJayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
>> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
>> sp_storedprocedure)
>> instead of select you can use INSERT INTO will work
>> eg:
>> create table #t(i int)
>> insert into #t exec myproc
>>
>> create proc myproc
>> as
>> select 1
>>
>> vinu
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>|||Dear Uri,
Great! It works!
Thank you very much for your help!
Best regards,
Andrew
P.S. Isn't it a bit strange that we have to refer to our own SQL server via
'openrowset'?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
> Andrew
> --This procedure returns three or two columns from Orders Table depends
> on parameter
> create proc usp_test
> @.orderid int =10248
> as
> if @.orderid=10248
> select orderid,orderdate from orders where orderid=@.orderid
> else
> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
> --I use OPENROWSET command to run this SP and get the data out to
> temporary table
> select * into #t
> from
> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
> 'set fmtonly off; exec usp_test 10249') as p
> select * from #t
> drop table #t
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>>
>>
>>
>|||> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
:-)
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> Great! It works!
> Thank you very much for your help!
> Best regards,
> Andrew
> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>
>>
>>
>>
>>
>>
>
>|||> P.S. Isn't it a bit strange that we have to refer to our own SQL server via
> 'openrowset'?
OPENQUERY() is another option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> Great! It works!
> Thank you very much for your help!
> Best regards,
> Andrew
> P.S. Isn't it a bit strange that we have to refer to our own SQL server via
> 'openrowset'?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>
>>
>>
>>
>>
>>
>
>|||Tibor
I did not want to create a linked server that OPENQUERY required unlike
OPENROWSET when you can reference to the server without having a linked
server
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfQkCm0AHHA.1224@.TK2MSFTNGP04.phx.gbl...
>> P.S. Isn't it a bit strange that we have to refer to our own SQL server
>> via
>> 'openrowset'?
> OPENQUERY() is another option...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> Great! It works!
>> Thank you very much for your help!
>> Best regards,
>> Andrew
>> P.S. Isn't it a bit strange that we have to refer to our own SQL server
>> via
>> 'openrowset'?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table
>> depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest
>> that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result
>>> set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT
>> to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>>> Andrew
>>>
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> No , you have to know how many "columns" stored procedures will be
>>> returned
>>>
>>> insert into tablename exec sp
>>>
>>>
>>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result
>>> set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>>
>

How to create a table (structure) based on a result of a stored procedure

Dear all,
There is a stored procedure that returns a result set consisting of some 30
columns of different types.
How can I automatically (i.e. not by means of CREATE TABLE statement) create
a table with a structure that correspons to the returned result set?
I mean something like:
SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
(The above unfortunately doesn't work).
To be specific: the stored procedure in question is:
sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
Any help would be greatly appreciated!
Thank you in advance.
Best regards,
AndrewAndrew

> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
No , you have to know how many "columns" stored procedures will be returned
insert into tablename exec sp
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
sp_storedprocedure)
instead of select you can use INSERT INTO will work
eg:
create table #t(i int)
insert into #t exec myproc
create proc myproc
as
select 1
vinu
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
> sp_storedprocedure)
> instead of select you can use INSERT INTO will work
> eg:
> create table #t(i int)
> insert into #t exec myproc
>
> create proc myproc
> as
> select 1
>
> vinu
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>|||Dear Uri,
As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Andrew
>
> No , you have to know how many "columns" stored procedures will be
> returned
> insert into tablename exec sp
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>|||Andrew
Well, in that case take a look into dynamic sql
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>|||Dear Uri,
Thank you for the hint. If I understand you correctly, you suggest that I
should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
That's OK, but how can I get (programmatically) the list of "columns" that
are returned by the stored procedure?
My basic question is
So, the situation is as follows: I have got a stored procedure and want to
put its result set into a new table.
Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
but instead of ExistingTableOrView there is a stored procedure.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Andrew
> Well, in that case take a look into dynamic sql
>
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>|||Andrew
--This procedure returns three or two columns from Orders Table depends on
parameter
create proc usp_test
@.orderid int =10248
as
if @.orderid=10248
select orderid,orderdate from orders where orderid=@.orderid
else
select orderid,orderdate,CustomerID from orders where orderid=@.orderid
--I use OPENROWSET command to run this SP and get the data out to temporary
table
select * into #t
from
openrowset('SQLOLEDB','SERVER=NT_SQL_4;D
ATABASE=Northwind;UID=appdev;PWD=np,
jho;',
'set fmtonly off; exec usp_test 10249') as p
select * from #t
drop table #t
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for the hint. If I understand you correctly, you suggest that I
> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
> That's OK, but how can I get (programmatically) the list of "columns" that
> are returned by the stored procedure?
> My basic question is
> So, the situation is as follows: I have got a stored procedure and want to
> put its result set into a new table.
> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
> but instead of ExistingTableOrView there is a stored procedure.
> Best regards,
> Andrew
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>
>|||Well .. it is not possible to use select into...exec
vt
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OXdJJayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
>|||Dear Uri,
Great! It works!
Thank you very much for your help!
Best regards,
Andrew
P.S. Isn't it a bit strange that we have to refer to our own SQL server via
'openrowset'?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
> Andrew
> --This procedure returns three or two columns from Orders Table depends
> on parameter
> create proc usp_test
> @.orderid int =10248
> as
> if @.orderid=10248
> select orderid,orderdate from orders where orderid=@.orderid
> else
> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
> --I use OPENROWSET command to run this SP and get the data out to
> temporary table
> select * into #t
> from
> openrowset('SQLOLEDB','SERVER=NT_SQL_4;D
ATABASE=Northwind;UID=appdev;PWD=n
p,jho;',
> 'set fmtonly off; exec usp_test 10249') as p
> select * from #t
> drop table #t
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>

how to create a landscape report?

Hello, I am new to Crystal reports and i am trying to CREATE a report in landscape view. When i start a new report it automatically comes up in portriat form how do i change it to landscape?
Thank you,
SantanaClick on File pull down menu, click Printer Setup, in lower right orientation portion choose landscape|||Thank you,

Santana

How to create a field that may accoss multiple pages?

I have a memo field that may not fit into one page. Is there any solution to let crystal report create multiple pages automatically at runtime? Splitting the string manually may not be the solution because how many characters are displayed for each page cannot be determined at the design time.

Thanks in advance!Doesn't the 'Can Grow' option work? (Right click field, Format Field, Common tab)?|||'Can Grow' does not work because the displaying area has to be fixed while the message may be too long to fit into one page.