How to create Site Map based on Catalog table

Hi
I want to create site map with quick access to all reports in my intranet.
I have already static map without links, based on Catalog table and i want
to expand its funcionality by adding dynamic links.
Has someone already solved this problem?
thx5 minutes after sending my question i found solution. Maybe someone will find
it usefull :
all you need is to make the link to URL (using navigation tab) to expresion :
="http://reports.mycompany.com/Reports/Pages/Report.aspx?ItemPath="
+ Fields!Path.Value
that's all - it's working!

How to create locialized report?

I know the language displayed on reporting service is based on language preference on IE, so I have no porblem on that.

But, is there any method to control the report itself? Can I use a resource control thing? or anything else to control it? so that when IE is displaying Chinese big5, the reoprt header and column header can display Chinese big5, and it goes the same for English and so on

Many thx

Yes - You can change the STYLE properties for any element and set the culture field to whatever you want.

How to create dynamic reports based on custom business objects?

How can we create dynamic reports(or reports created by the end user) by
selected fields from the list of custom business objects.
We would like to offer full data consolidation. Right Crystal offers this
type of functionality but I would like to steer away from that.
Any ideas how to proceed or whether is it even feasible. We do not want to
use reflection on the objects, I mean we can use it but that would not be the
best approach.
ThanksHi Amarnath,
you said SSRS has a new feature using which report builder could be given to
end user to create reports.
Is this Report builder browser based(so that the end user does not need to
install anything) and could design reports and modify the existing reports,
if yes, how to make use of this?
Thanks
Ponnu
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:9A2A1500-E374-45F1-8288-56CB8EC97883@.microsoft.com...
> What do you mean by custom BO, is it in the form of dll or assembly sort
> of
> thing then you can always refer the assembly in SS 2005. If it is in the
> form
> of Stored proc then you can always refer that in the report query..
> Infact in RS you need to create a report model and give it to the end
> users,
> SSRS new feature has report builder which is given to the end user to
> create
> their own reports.
>
> Amarnath
>
> "Atul Bahl" wrote:
>> How can we create dynamic reports(or reports created by the end user) by
>> selected fields from the list of custom business objects.
>> We would like to offer full data consolidation. Right Crystal offers this
>> type of functionality but I would like to steer away from that.
>> Any ideas how to proceed or whether is it even feasible. We do not want
>> to
>> use reflection on the objects, I mean we can use it but that would not be
>> the
>> best approach.
>> Thanks

How to create computed columns(dynamic) in matrix

Hello,
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
select div_sec, model_ID, model_time from table1
Any help is greatly appreciated.
Thanks,
VenkatSeems like this is not possible with reporting services!!...
venkat.oar@.gmail.com wrote:
> Hello,
> I am using matrix control for which the rows, columns are all dynamic(
> based on the group i specify). I am able to get the Subtotal for both
> the rows and colums, which is Great!!..
> my problem is i need to add more columns(some formula columns) which
> might have "% difference", "varience" etc..
> the report should look something like this (M1, M2 are model_id's)
> Div/Sec M1 M2 Variance % Difference
> ----
> O3580 0.71 1.47 -0.76 -107.04
> 8040 1.33 1.33 0 0
> 8110 9.98 14.47 -4.49 -44.99
> 11210 5.44 6.36 -0.92 -16.91
> and my query is :
> select div_sec, model_ID, model_time from table1
> Any help is greatly appreciated.
> Thanks,
> Venkat|||Yes when you select datasets from view menu and right click on the datasets
window you can see a add option. click and you can add calculated fields.
Amarnath
"venkat.oar@.gmail.com" wrote:
> Seems like this is not possible with reporting services!!...
> venkat.oar@.gmail.com wrote:
> > Hello,
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> > and my query is :
> >
> > select div_sec, model_ID, model_time from table1
> >
> > Any help is greatly appreciated.
> >
> > Thanks,
> > Venkat
>|||Sorry.. i think u got me wrong here. I am able to add calculated fields
but the data what i have is part of 2 rows.
here's a brief description for you.
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----==AD--
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
** *** READ BELOW QUERY..
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36
Any help is greatly appreciated.
Thanks,
Venkat
Amarnath wrote:
> Yes when you select datasets from view menu and right click on the datase=ts
> window you can see a add option. click and you can add calculated fields.
> Amarnath
> "venkat.oar@.gmail.com" wrote:
> > Seems like this is not possible with reporting services!!...
> >
> > venkat.oar@.gmail.com wrote:
> > > Hello,
> > >
> > > I am using matrix control for which the rows, columns are all dynamic(
> > > based on the group i specify). I am able to get the Subtotal for both
> > > the rows and colums, which is Great!!..
> > >
> > > my problem is i need to add more columns(some formula columns) which
> > > might have "% difference", "varience" etc..
> > >
> > > the report should look something like this (M1, M2 are model_id's)
> > >
> > > Div/Sec M1 M2 Variance % Difference
> > > ---=--
> > > O3580 0.71 1.47 -0.76 -107.04
> > > 8040 1.33 1.33 0 0
> > > 8110 9.98 14.47 -4.49 -44.99
> > > 11210 5.44 6.36 -0.92 -16.91
> > >
> > > and my query is :
> > >
> > > select div_sec, model_ID, model_time from table1
> > >
> > > Any help is greatly appreciated.
> > > > > > Thanks,
> > > Venkat
> > > >|||Is there a way in this scenario to use the ReportItems!<textbox>.Value
syntax? Or, does it fall apart because it's a matrix?
venkat.oar@.gmail.com wrote:
> Sorry.. i think u got me wrong here. I am able to add calculated fields
> but the data what i have is part of 2 rows.
> here's a brief description for you.
>
> I am using matrix control for which the rows, columns are all dynamic(
> based on the group i specify). I am able to get the Subtotal for both
> the rows and colums, which is Great!!..
>
> my problem is i need to add more columns(some formula columns) which
> might have "% difference", "varience" etc..
>
> the report should look something like this (M1, M2 are model_id's)
>
> Div/Sec M1 M2 Variance % Difference
> ----­--
> O3580 0.71 1.47 -0.76 -107.04
> 8040 1.33 1.33 0 0
> 8110 9.98 14.47 -4.49 -44.99
> 11210 5.44 6.36 -0.92 -16.91
>
> and my query is :
> ** *** READ BELOW QUERY..
> select div_sec, model_ID, model_time from table1
> the output of my query is below( seperated by commas)
> div_sec, model_ID, model_time
> O3580,M1,0.7
> O3580,M2,1.47
> 8040 ,M1,1.33
> 8040 ,M2,1.33
> 8110 ,M1,9.98
> 8110 ,M2,14.47
> 11210 ,M1,5.44
> 11210 ,M2,6.36
> Any help is greatly appreciated.
>
> Thanks,
> Venkat
> Amarnath wrote:
>>Yes when you select datasets from view menu and right click on the datasets
>>window you can see a add option. click and you can add calculated fields.
>>Amarnath
>>"venkat.oar@.gmail.com" wrote:
>>
>>Seems like this is not possible with reporting services!!...
>>venkat.oar@.gmail.com wrote:
>>Hello,
>>I am using matrix control for which the rows, columns are all dynamic(
>>based on the group i specify). I am able to get the Subtotal for both
>>the rows and colums, which is Great!!..
>>my problem is i need to add more columns(some formula columns) which
>>might have "% difference", "varience" etc..
>>the report should look something like this (M1, M2 are model_id's)
>>Div/Sec M1 M2 Variance % Difference
>>----
>>O3580 0.71 1.47 -0.76 -107.04
>>8040 1.33 1.33 0 0
>>8110 9.98 14.47 -4.49 -44.99
>>11210 5.44 6.36 -0.92 -16.91
>>and my query is :
>>select div_sec, model_ID, model_time from table1
>>Any help is greatly appreciated.
>>Thanks,
>>Venkat
>>
>|||Mike,
I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).
Still no clue how to do this or is it anyway possible?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > but the data what i have is part of 2 rows.
> > here's a brief description for you.
> >
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----=--=AD--
> >
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> >
> > and my query is :
> >
> > ** *** READ BELOW QUERY..
> > select div_sec, model_ID, model_time from table1
> > the output of my query is below( seperated by commas)
> > div_sec, model_ID, model_time
> > O3580,M1,0.7
> > O3580,M2,1.47
> > 8040 ,M1,1.33
> > 8040 ,M2,1.33
> > 8110 ,M1,9.98
> > 8110 ,M2,14.47
> > 11210 ,M1,5.44
> > 11210 ,M2,6.36
> >
> > Any help is greatly appreciated.
> >
> >
> > Thanks,
> > Venkat
> >
> > Amarnath wrote:
> >
> >>Yes when you select datasets from view menu and right click on the data=sets
> >>window you can see a add option. click and you can add calculated field=s=2E
> >>
> >>Amarnath
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>I am using matrix control for which the rows, columns are all dynamic(
> >>based on the group i specify). I am able to get the Subtotal for both
> >>the rows and colums, which is Great!!..
> >>
> >>my problem is i need to add more columns(some formula columns) which
> >>might have "% difference", "varience" etc..
> >>
> >>the report should look something like this (M1, M2 are model_id's)
> >>
> >>Div/Sec M1 M2 Variance % Difference
> >>---=--
> >>O3580 0.71 1.47 -0.76 -107.04
> >>8040 1.33 1.33 0 0
> >>8110 9.98 14.47 -4.49 -44.99
> >>11210 5.44 6.36 -0.92 -16.91
> >>
> >>and my query is :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >|||Hi try getting all the required data from query, here is the query for that.
see if it works for you.. I think you can put this in table object in SSRS as
well.
select div_sec,
sum((case (model_ID) when 'M1' then (model_time) end)) M1,
sum((case (model_ID) when 'M2' then (model_time) end)) M2,
sum((case (model_ID) when 'M1' then (model_time) end)) - sum((case
(model_ID) when 'M2' then (model_time) end)) Variance
from #temp
group by div_sec
order by 1
You can replace #temp with your table and you can add more columns as well.
This is basically converting all vertical data to horizontal ie pivoting.
Amarnath
"venkat.oar@.gmail.com" wrote:
> Mike,
> I can't even go that far to use ReportItems!<txtbox>.value, the reason
> is when i add column to the matrix, the columns appear for each column
> it is created dynamically( in my case the columns appear for each
> model_ID).
> Still no clue how to do this or is it anyway possible?.
>
> Michael Cervantes wrote:
> > Is there a way in this scenario to use the ReportItems!<textbox>.Value
> > syntax? Or, does it fall apart because it's a matrix?
> >
> >
> >
> > venkat.oar@.gmail.com wrote:
> >
> > > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > > but the data what i have is part of 2 rows.
> > > here's a brief description for you.
> > >
> > >
> > > I am using matrix control for which the rows, columns are all dynamic(
> > > based on the group i specify). I am able to get the Subtotal for both
> > > the rows and colums, which is Great!!..
> > >
> > >
> > > my problem is i need to add more columns(some formula columns) which
> > > might have "% difference", "varience" etc..
> > >
> > >
> > > the report should look something like this (M1, M2 are model_id's)
> > >
> > >
> > > Div/Sec M1 M2 Variance % Difference
> > > ----­--
> > >
> > > O3580 0.71 1.47 -0.76 -107.04
> > > 8040 1.33 1.33 0 0
> > > 8110 9.98 14.47 -4.49 -44.99
> > > 11210 5.44 6.36 -0.92 -16.91
> > >
> > >
> > > and my query is :
> > >
> > > ** *** READ BELOW QUERY..
> > > select div_sec, model_ID, model_time from table1
> > > the output of my query is below( seperated by commas)
> > > div_sec, model_ID, model_time
> > > O3580,M1,0.7
> > > O3580,M2,1.47
> > > 8040 ,M1,1.33
> > > 8040 ,M2,1.33
> > > 8110 ,M1,9.98
> > > 8110 ,M2,14.47
> > > 11210 ,M1,5.44
> > > 11210 ,M2,6.36
> > >
> > > Any help is greatly appreciated.
> > >
> > >
> > > Thanks,
> > > Venkat
> > >
> > > Amarnath wrote:
> > >
> > >>Yes when you select datasets from view menu and right click on the datasets
> > >>window you can see a add option. click and you can add calculated fields.
> > >>
> > >>Amarnath
> > >>
> > >>"venkat.oar@.gmail.com" wrote:
> > >>
> > >>
> > >>Seems like this is not possible with reporting services!!...
> > >>
> > >>venkat.oar@.gmail.com wrote:
> > >>
> > >>Hello,
> > >>
> > >>I am using matrix control for which the rows, columns are all dynamic(
> > >>based on the group i specify). I am able to get the Subtotal for both
> > >>the rows and colums, which is Great!!..
> > >>
> > >>my problem is i need to add more columns(some formula columns) which
> > >>might have "% difference", "varience" etc..
> > >>
> > >>the report should look something like this (M1, M2 are model_id's)
> > >>
> > >>Div/Sec M1 M2 Variance % Difference
> > >>----
> > >>O3580 0.71 1.47 -0.76 -107.04
> > >>8040 1.33 1.33 0 0
> > >>8110 9.98 14.47 -4.49 -44.99
> > >>11210 5.44 6.36 -0.92 -16.91
> > >>
> > >>and my query is :
> > >>
> > >>select div_sec, model_ID, model_time from table1
> > >>
> > >>Any help is greatly appreciated.
> > >>
> > >>Thanks,
> > >>Venkat
> > >>
> > >>
> > >
>|||Hi Venkat, my problem (as posted on 5th July, "Calculations in Matrix
report") is almost identical, so I'll let you know if a solution/workaround
can be found.
Pete
<venkat.oar@.gmail.com> wrote in message
news:1152882808.590984.275540@.i42g2000cwa.googlegroups.com...
Mike,
I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).
Still no clue how to do this or is it anyway possible?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > but the data what i have is part of 2 rows.
> > here's a brief description for you.
> >
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----­--
> >
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> >
> > and my query is :
> >
> > ** *** READ BELOW QUERY..
> > select div_sec, model_ID, model_time from table1
> > the output of my query is below( seperated by commas)
> > div_sec, model_ID, model_time
> > O3580,M1,0.7
> > O3580,M2,1.47
> > 8040 ,M1,1.33
> > 8040 ,M2,1.33
> > 8110 ,M1,9.98
> > 8110 ,M2,14.47
> > 11210 ,M1,5.44
> > 11210 ,M2,6.36
> >
> > Any help is greatly appreciated.
> >
> >
> > Thanks,
> > Venkat
> >
> > Amarnath wrote:
> >
> >>Yes when you select datasets from view menu and right click on the
> >>datasets
> >>window you can see a add option. click and you can add calculated
> >>fields.
> >>
> >>Amarnath
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>I am using matrix control for which the rows, columns are all dynamic(
> >>based on the group i specify). I am able to get the Subtotal for both
> >>the rows and colums, which is Great!!..
> >>
> >>my problem is i need to add more columns(some formula columns) which
> >>might have "% difference", "varience" etc..
> >>
> >>the report should look something like this (M1, M2 are model_id's)
> >>
> >>Div/Sec M1 M2 Variance % Difference
> >>----
> >>O3580 0.71 1.47 -0.76 -107.04
> >>8040 1.33 1.33 0 0
> >>8110 9.98 14.47 -4.49 -44.99
> >>11210 5.44 6.36 -0.92 -16.91
> >>
> >>and my query is :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >

How to Create CLR Based Stored Procedures

I need to create CLR Based Stored Procedures using VS2005 Standard Edition in Sql Server 2005 Standard Edition. Unfortunately, when I create a new project in VS2005. I can not find the template to create a Sql server project using C#. I re-install sql server and Vs2005, the problem still there. Does Vs2005 standard edition support create sql server project? Thanks a lot.

O sure it does, its located under File --> New --> Project --> Visual C# --> Database --> SQL Server Project.

If its not located in there you have to reinstall the Visual Studio template from the setup file.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

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

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
>
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...
>
sql

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

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
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
--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=No rthwind;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...
>
>
|||> 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...
>
>
|||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...
> 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...
>

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 new serverinstance but with old datafiles?

Hi group
I want to make a new serverinstance, but based on some old files from a
previous instance I once had from a SQL I scrapped.
I only know how to use the SQL Server enterprise manager console, and I
haven't found any tools in there to do that.
Any suggestion will be more than welcome.

AdrianYou can try to reattach the old database file using sp_attach_db or with
Enterprise Manager. This may not work of the old databases weren't cleanly
detached, though.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Adrian Hjelmslund" <anonymous@.nothing.com> wrote in message
news:KniUd.357$mj1.135@.news.get2net.dk...
> Hi group
> I want to make a new serverinstance, but based on some old files from a
> previous instance I once had from a SQL I scrapped.
> I only know how to use the SQL Server enterprise manager console, and I
> haven't found any tools in there to do that.
> Any suggestion will be more than welcome.
> Adrian

How to create a dimesnion for a range of values

I am new to OLAP and I was wondering if it is possible to create a dimension
in Analysis Services 2005 based on a range of values for a given field in a
Fact table.
Example:
Field Name: Quantity
Dimension Name: Quantity Range
Dimension Values: 0-100
101-200
201-1000
> 1000
The only solution I see now is to denormalize Quantity into a new field
QuantityRange that is computed based on the value of the quantity
1: 0-100
2: 101-200
3: 201-1000
4: > 1000
The values 1 to 4 would be my dimension.
Thanks al lot
I gave you some ideas on the microsoft.public.sqlserver.olap newsgroup.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"carlos" <carlos@.discussions.microsoft.com> wrote in message
news:6043ABBE-7ADA-4E9D-8AA7-D252F075DAEB@.microsoft.com...
>I am new to OLAP and I was wondering if it is possible to create a
>dimension
> in Analysis Services 2005 based on a range of values for a given field in
> a
> Fact table.
> Example:
> Field Name: Quantity
> Dimension Name: Quantity Range
> Dimension Values: 0-100
> 101-200
> 201-1000
> The only solution I see now is to denormalize Quantity into a new field
> QuantityRange that is computed based on the value of the quantity
> 1: 0-100
> 2: 101-200
> 3: 201-1000
> 4: > 1000
> The values 1 to 4 would be my dimension.
> Thanks al lot
|||You could drive this denormalisation using the data in the dimension
table.
1) Create a dimension table with max and min columns
ID MinValue MaxValue
1 0 100
2 101 200
3 201 1000
4 1000 2147483648 <-- max integer value
2) Create a view on the fact table like the following
Select
...
<list fact columns here>
...
,dim.ID
from fact f
inner join dimQuantityRange dim
on f.Quantity between dim.MinValue and dim.MaxValue
3) Alter you cube to use the newly created view as the fact "table".
then you can join the dimension back onto the id in the view.
The advantage of doing things this way is that everything is data
driven. If you need to add more ranges or change the ranges you just
update the max/min columns in the dim table and re-process.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

How to create a dimesnion for a range of values

I am new to OLAP and I was wondering if it is possible to create a dimension
in Analysis Services 2005 based on a range of values for a given field in a
Fact table.
Example:
Field Name: Quantity
Dimension Name: Quantity Range
Dimension Values: 0-100
101-200
201-1000
> 1000
The only solution I see now is to denormalize Quantity into a new field
QuantityRange that is computed based on the value of the quantity
1: 0-100
2: 101-200
3: 201-1000
4: > 1000
The values 1 to 4 would be my dimension.
Thanks al lotI gave you some ideas on the microsoft.public.sqlserver.olap newsgroup.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"carlos" <carlos@.discussions.microsoft.com> wrote in message
news:6043ABBE-7ADA-4E9D-8AA7-D252F075DAEB@.microsoft.com...
>I am new to OLAP and I was wondering if it is possible to create a
>dimension
> in Analysis Services 2005 based on a range of values for a given field in
> a
> Fact table.
> Example:
> Field Name: Quantity
> Dimension Name: Quantity Range
> Dimension Values: 0-100
> 101-200
> 201-1000
> The only solution I see now is to denormalize Quantity into a new field
> QuantityRange that is computed based on the value of the quantity
> 1: 0-100
> 2: 101-200
> 3: 201-1000
> 4: > 1000
> The values 1 to 4 would be my dimension.
> Thanks al lot|||You could drive this denormalisation using the data in the dimension
table.
1) Create a dimension table with max and min columns
ID MinValue MaxValue
1 0 100
2 101 200
3 201 1000
4 1000 2147483648 <-- max integer value
2) Create a view on the fact table like the following
Select
..
<list fact columns here>
..
,dim.ID
from fact f
inner join dimQuantityRange dim
on f.Quantity between dim.MinValue and dim.MaxValue
3) Alter you cube to use the newly created view as the fact "table".
then you can join the dimension back onto the id in the view.
The advantage of doing things this way is that everything is data
driven. If you need to add more ranges or change the ranges you just
update the max/min columns in the dim table and re-process.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

How to create a cystal report based on a text-based data file?

I am a beginner to use cystal report. I wonder if i could establish a report based on a text data file?

for example:

test report 9/20/2004

X Y
--
1 1
2 2
3 3
. .
. .
. .
. .

i'd like to create a graph in my test report. (X axis and Y axis array is based on the above data)

How can i do that? Thanks!

KevinYou can create your report using CDO (Crysal Data Objects). CDO is Crystal's version of a recordset. Basically, you populate a variant array with your data (that you pulled from the text file), then stuff the array into the CDO rowset and pass the CDO Rowset to Crystal.

To get a better explanation and some code examples, do a search for CDO on this forum. I've posted many times with code examples.

As for the Graph, you can click Insert, Chart and follow the instructions from there.

(All of my suggestions and code examples are for vb 6 and CR 8.5 using RDC.)|||As my study, one cystal report has to be set in one path location, right? Because i want to generate a test report for each user specified test record data, the test data is in different location.

So is that possible i can tell cystal report where to find the test data and then generate a report?

Thanks any way!

Kevin|||Using CDO (in the way that I described) gives VB all the control of gathering the data. The only thing Crystal Reports does is display the data that VB passes it. In my opinion, it gives you (as the developer) more control. It's better for error trapping (since you can step through the code and see exactly what is being passed to Crystal).

Try this link:
http://www.dev-archive.com/forum/showthread.php?s=&threadid=281658&highlight=cdo|||thanks a lot!

One more help, i wonder if i could also gerenate the pdf file based on my test data. Do you have any idea on which some control i can use for pdf?

Thanks!

Kevin|||Crystal Reports has it's own built-in Export function. You can export to pdf and several other formats. I can give you code that will work with CR 8.5 using RDC and VB 6 if tha will help you. Otherwise, do a search either on this forum or on the following links:

Crystal Reports Support:
http://support.businessobjects.com/search/advsearch.asp

Crystal Reports Forum:
http://support.businessobjects.com/forums/default.asp|||if you could provide me the sample, that will be great!

Thanks a lot! :)

Kevin|||This example uses CR 8.5 and RDC to export a report to pdf.

Report.ExportOptions.DestinationType = crEDTDiskFile
Report.ExportOptions.FormatType = crEFTPortableDocFormat
Report.ExportOptions.PDFExportAllPages = True

Report.ExportOptions.DiskFileName = strFilePath

'Export to PDF Format, setting False prevents the Export dialog window from showing
Report.Export False|||but how can i let chart draw a curve base on the arrays data, one array is for X-axis, the other is for Y-axis?

Thanks!

Kevin|||I don't how to do charts in Crystal (haven't had to use them yet), but you should be able to click Insert, Chart and follow the directions. Using Groups and Crosstabs has been fairly simple so far, maybe charts will be too?

How to cotrol format of datetime attributes?

There is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.

I assigned different format in the format field of key property window, but it had no effect. What I did wrong?

Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.

HTH

Thomas Ivarsson

|||

Thank you,

I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.

I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.

Do you know what is the format field in key properties for?