Ad-hoc reporting

We have a web application using SQL Server 2000, and we'd like to add ad-hoc
reporting. Here are the options we thought of so far:
1. Use SQL Server 2000 Reporting Services - but we're not sure how "ad-hoc"
we can get using SSRS. Has anyone tried?
2. Use a 3rd party reporting software package. Does anyone know of any?
3. Build a custom .NET ad-hoc app that is fine tuned for this application.
This is likely the most costly.
The goal is to have the users build and save report templates, and the be
able to select any combination of fields in the relational schema to include
in the reports, and select on any fields as well.
Does anyone have any other suggestions?
Thank you!!My suggestion is to investigate Report Builder which comes with RS 2005. It
is an ad hoc reporting solution that came out with RS 2005. This is
non-trivial to create on your own and from a cost perspective you will be
better off paying for the upgrade. Plus RS 2005 is much faster for Excel and
PDF output, has multi-select parameters, end user sorting and date picker.
Note that as long as you pay the license you can upgrade just RS and leave
your database at 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:uUTBa8qrHHA.5028@.TK2MSFTNGP05.phx.gbl...
> We have a web application using SQL Server 2000, and we'd like to add
> ad-hoc
> reporting. Here are the options we thought of so far:
> 1. Use SQL Server 2000 Reporting Services - but we're not sure how
> "ad-hoc"
> we can get using SSRS. Has anyone tried?
> 2. Use a 3rd party reporting software package. Does anyone know of any?
> 3. Build a custom .NET ad-hoc app that is fine tuned for this application.
> This is likely the most costly.
> The goal is to have the users build and save report templates, and the be
> able to select any combination of fields in the relational schema to
> include
> in the reports, and select on any fields as well.
> Does anyone have any other suggestions?
> Thank you!!
>

Ad-hoc reporting memory consumption

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working. We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Ad-hoc reporting memory consumption

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.

We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)?

Thanks.

Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Ad-Hoc Reporting for users in RS 2005 with Visual Studio 2005

My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
Services 2005, and during those demos, the end user development environonment
was demoed. We are currently using SQL 2000 RS for a full set of Financial
reports for our 800+ users and would like to make available report writing to
our power users.
Are there any links that anybody knows of that explains setting up the
end-user VS2005 environment? Is there a place to start at in the SQL help
file that explains how users should be configured to use Visual Studio and
the Security implications?
Thanks in advance.RS comes with two ways to create reports. Report Builder and Report
Designer. The designer is essentially the same. Report Builder is a whole
new thing and is for power users. It does not use VS. I suggest reading up
on Report Builder. It is a smart client application (one click install).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> Services 2005, and during those demos, the end user development
> environonment
> was demoed. We are currently using SQL 2000 RS for a full set of
> Financial
> reports for our 800+ users and would like to make available report writing
> to
> our power users.
> Are there any links that anybody knows of that explains setting up the
> end-user VS2005 environment? Is there a place to start at in the SQL help
> file that explains how users should be configured to use Visual Studio and
> the Security implications?
> Thanks in advance.|||Do you know of any good websites for Report Builder/ad hoc reporting?
--
K. Thomas
"Bruce L-C [MVP]" wrote:
> RS comes with two ways to create reports. Report Builder and Report
> Designer. The designer is essentially the same. Report Builder is a whole
> new thing and is for power users. It does not use VS. I suggest reading up
> on Report Builder. It is a smart client application (one click install).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> > My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> > Services 2005, and during those demos, the end user development
> > environonment
> > was demoed. We are currently using SQL 2000 RS for a full set of
> > Financial
> > reports for our 800+ users and would like to make available report writing
> > to
> > our power users.
> >
> > Are there any links that anybody knows of that explains setting up the
> > end-user VS2005 environment? Is there a place to start at in the SQL help
> > file that explains how users should be configured to use Visual Studio and
> > the Security implications?
> >
> > Thanks in advance.
>
>

Ad-Hoc Reporting

Hi,

Can i know which version of Reporting services supports Ad-hoc reporting.

Reporting Services 2005. More information on the specific functionality for the various editions can be found here: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

Ad-hoc reporting

Is the ad-hoc reporting available for SQL Server 2005 reporting
services a web based environment or is it an application that has to be
installed?
Thanks
Robertits a clickonce application.
this required that the user have the .Net Framework 2.0 installed locally.
then the reportbuilder application is downloaded automatically without any
install process.
but its not a webbased application. its a client/server application which
use webservices calls to the server to execute andrender queries.
"rhoenig" <rhoenig@.charter.net> wrote in message
news:1158956048.392781.78140@.m7g2000cwm.googlegroups.com...
> Is the ad-hoc reporting available for SQL Server 2005 reporting
> services a web based environment or is it an application that has to be
> installed?
> Thanks
> Robert
>

Ad-hoc Report Generation Based on the Input Query!

Hi all,

I want to generate the ad-hoc report using SQL Reporting Services 2000 or SQL Reporting Services 2005. But the Database Server will be SQL Server 2000. So, Can I send the query at run-time and then attach the data source to the actual report. In this case there will not be any columns etc. at design time with the value field mentioned in it. I know this is not the requirement for which this report tool is developed for. But still if this is possible through some other way round then pls. let me know. Actually we want one generic report which will satisfy all the tables, with diff. conditions mentioned in the select clause and where clause. So, is this possible with this reporting tool? Is there any way out where we want to come up with only one such report by manipulating the datasource of the report at run-time? Pls. let me know.

Thanks & Regards,

Dipali.

There may be a better way to do this in 2005 but the way I acheived dynamic columns in 2000 was to have a maximum number of columns (say 20) that take the value Column01, Column02....Column20 and make sure your data source returns the values under those names. Then you can also pass in the column names as a parameter. That's the only way I could come up with for producing a completely dynamic report.sql

Adhoc Query Tool for Sql Server

Hi !!
We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets sayhttp://salebyowner.com/advancedSearch.php
We have few more options than this on which customer can do search.
I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?Hi love_luv,
Your example link shows a reasonably sophisticated query form written in PHP. But that doesn't mean PHP is a better database query tool than C# (or any other language for that matter).
PHP is just an application layer over top of some database. That database requires a completely different query language known as SQL (that has no direct link to PHP, C# or any other language).
The programmer in your example is simply using PHP to dyanmically costruct a SQL query statement (in text form) and passing it to their database to retrieve a result. C# (i.e., ASP.NET) can do that too.
In fact, with C# you can utilizeparameterized SQL queries for cleaner and safer SQL construction and execution.
The real trick behind sophisticated queries, of the type you're hoping to achieve, is a good familiarity with the SQL language (of the type native to your database brand,e.g., MS SQL Server uses a form of SQL known as Transact-SQL (a.k.a. TSql), and the knowledge to transform the user input from a webpage into said SQL.
Hope that helps.|||know, I understand that, its sophisticated query. I thought do we need to get some tool like Cognos etc etc for doing such query? More than parameterized query, I think its Dynamic or Adhoc query.|||You don't "need" a query tool at all, if you are familiar with SQL - regardless of how complex a query is. Visual Studio also query tools built in.

A special query tool or not, sophisticated or simple requirements - it all still comes down to a SQL statement to get your database results.
I found query tools useful once-upon-a-time for learning SQL, but I generally don't use them in my day-to-day programming now. You might find a tool useful to get an example of what your SQL text should look like, but once you have that then you use a language like C# to dynamically build the SQL text.
As an aside, "nearly" all database queries are dynamic (or adhoc) in nature. And using a parameterized query is just an optional format to make your dynamic (i.e., adhoc) queries easier to build (and safer to execute).
Hope that helps.|||So mean to say such a complex query is Dynamic Query written in C# code?
We really don't need any Commercial Query tool for Client end? We can use Web Forms to generate such query?
In couple of projects we implemented complex dynamic query, and that made us think if we need any tool or what? In our dynamic query, most of the time (99%) Select clause returns fix number of columns however, From Clause has base table and based on Where Conditions it may or may not join with other tables in data base.
In our version of dynamic query we builded WHERE clause in C#.
But how can we implement Join and WHERE using some proper structure.
Thanks|||

How one can handle dynamic SQL which has to perform JOIN with other tables.

Example I showed in my first post say has 2 - 3 tables:

PropertyListings and PropertyFeatures, PropertyLotFeatures

PropertyListings table gives me basic information I need to display.
PropertyFeatures and PropertyLotFeatures has information I need for advance search. I don't know which table to use and Join till user selects some features. based on Features selected for advance search I may have to Join:
1) PropertyListings with PropertyFeatures
2) PropertyListings with PropertyLotFeatures
3) PropertyListings with propertyFeatures and PropertyLotFeatures
I know once can right such query in C#, but is that how they do it or there is a better way? Am I missing something?

|||

Let me give an Example :

How about when there is Join Present. How whould you build Dynamic SQL? Do you need Dynamic SQL or there is a Better Way?
For Eg.
Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests
Table4 = Activities
Table5 = Customers_Activities
Table3 holds Customers Interests.
Table5 holds Customers Activities
Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3
Activities Table has Data: A1, A2, A3, A4
Customers_Activities has Data:
C1, A1
C1, A4
C2, A3
C4, A1
Now lets say on my webpage I represent Interests and Activities as CheckBoxes.
Case 1:
I select Interests I1, I3, I4 but No Activities Selected. In this case my query has to join Customers, Interests and Customers_Interests Table
So query will be: Give me list of Customers with Interests I1, I3, I4
Case 2:
If I select Activities A1, A3 and No Interests, query whas to join Customers, Activities and Customers_Activities Table
Case 2:
If I select Interests I1 and Activities A1, in this case Query has to join Customers, Interests, Customers_Interests, Activities and Customers_Activities tables(all tables)
How would you write Query?
Looking for help!!!!!!!!!!!!!!!!!!!!!!!!!!!

ad-hoc query printing

Based on the tutorial in
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
We have a prototype that works with our custom data extension in Reporting
Services 2005 such that we can generate a report based on a given query text.
Right now in order to view a report, we always publish the generated report
to a report server instance. Our question is whether we can do it without
using report server.
For example, does ReportViewer control (the local processing mode) help?
Our goal is to create a temporary report on the fly given a query such that
the user can print or export the report, which means that this temporary
report will be discarded as soon as the user is done with it. We would like
to know if this is possible.
Thanks in advance!Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to whether it is
possible to create a temporary report for print only. If I have
misunderstood your concern, please feel free to point it out.
Unfortunately, Reporting Services do not have such funtionality to do so. I
wonder why do you need a Report on fly?
I have an interesting experience before that might give you some ideas. The
data source in my project was a remote linked server that need a lot of
time executing the complex query (cost about 5 minutes each time). When
perform the Email-Delivery Subscription to 10 or more different users, the
query will be executed 10 or more 5-minutes (around for 1 hours to complete
the email delivery subscription), which is obviously unacceptedable. Hence,
I will use DTS to transfer the data from linked server to local server
(cost 5 minutes) and then do the email-delivery subscription on local
tables, which is pretty cheap.
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Oops, sorry that I think I missed something
Here is an interesting article that shows VS 2005's new ReportViewer
control and its built-in Report Designer enable smart-client and Web page
designers to lay out, format, embed, export, and print interactive reports
without running a SQL Server Report Server.
Build Client-Side Reports Easily
http://msforums.ph/forums/91285/ShowPost.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help. I found this walkthrough sample (similar to the one
you quoted below) from MSDN2 as well
http://msdn2.microsoft.com/en-us/library/ms251724.aspx
This is useful tutorial for us. However, I need to generate such report on
the fly ... that is, the dataset and datatable etc will be generated at
run-time. Is this even possible?
In addition, is there any tutorial on generating RDLC programmatically?
Thanks
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Oops, sorry that I think I missed something
> Here is an interesting article that shows VS 2005's new ReportViewer
> control and its built-in Report Designer enable smart-client and Web page
> designers to lay out, format, embed, export, and print interactive reports
> without running a SQL Server Report Server.
> Build Client-Side Reports Easily
> http://msforums.ph/forums/91285/ShowPost.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Actually, I just found out about the LocalReport class from
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.localreport.aspx
Do you know of any example of how to use it? Say I already have a dataset
or a datatable or a collection of business objects (can't seem to find more
about what exactly a business object is).
Thanks in advance!
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks for your help. I found this walkthrough sample (similar to the one
> you quoted below) from MSDN2 as well
> http://msdn2.microsoft.com/en-us/library/ms251724.aspx
> This is useful tutorial for us. However, I need to generate such report on
> the fly ... that is, the dataset and datatable etc will be generated at
> run-time. Is this even possible?
> In addition, is there any tutorial on generating RDLC programmatically?
> Thanks
> Jenny
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Oops, sorry that I think I missed something
> >
> > Here is an interesting article that shows VS 2005's new ReportViewer
> > control and its built-in Report Designer enable smart-client and Web page
> > designers to lay out, format, embed, export, and print interactive reports
> > without running a SQL Server Report Server.
> >
> > Build Client-Side Reports Easily
> > http://msforums.ph/forums/91285/ShowPost.aspx
> >
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >|||Hi,
To create a RDLC file, you may refer the article below
Creating Client Report Definition (.rdlc) Files
http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
Converting RDL and RDLC Files
http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
If you encounter any difficulties in the walkthrough, please let me know
the detail step you meet the error.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Michael for the links. I did take a look at both and did try some
walkthrough tutorials. They are great and now I understand more about
ReportViewer controls in general.
However, my problem is still not resolved. I guess I can rephrase it a bit
to let you understand it more:
(1) I need to generate the RDLC file programmatically because I don't know
the report fields at design time
(2) The same applies to the report data source, and I have a way to run a
query to get a System.Data.DataTable object which I can use as the report
data source, but again this depends on the search query, which I don't know
until run-time
I have attempted to modify the walkthrough from
http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
But instead of creating the rdlc file using the UI, I created the RDLC file
based on the tutorial from
http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
Again, I just renamed this file using the *.rdlc extension instead of *.rdl
and save it somewhere in my local system.
As I mentioned above, given a query, I can create a System.Data.DataTable on
the fly and modified the sample code such that I commented out the followings
from Form1.Designer.cs:
//this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
//this.reportViewer1.LocalReport.ReportEmbeddedResource ="BusinessObject.Report1.rdlc";
and added the following lines to Form1_Load() just before the line
this.reportViewer1.RefreshReport();
this.reportViewer1.LocalReport.DataSources.Add(new
ReportDataSource("Sales", datatable));
this.reportViewer1.LocalReport.ReportPath =@."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
When I ran the app, I got the following error:
"A data source instance has not been supplied for the data source 'DataSet1'"
First of all, am I in the wrong track? Can I actually programmatically
create an RDL file and simply renamed it as RDLC. What else do I need to do
programmatically (not through adding it to the project as indicated by the
walkthrough)?
Second of all, what am I missing? Is it possible to supply the DataTable
(report data source) programmatically?
Thanks a lot for reading my long post.
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> To create a RDLC file, you may refer the article below
> Creating Client Report Definition (.rdlc) Files
> http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> Converting RDL and RDLC Files
> http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> If you encounter any difficulties in the walkthrough, please let me know
> the detail step you meet the error.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Actually I've figured it out myself! :-)
When I added a new ReportDataSource object, I should have named it
'DataSet1' to associate that with my RDLC file.
Jenny
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks Michael for the links. I did take a look at both and did try some
> walkthrough tutorials. They are great and now I understand more about
> ReportViewer controls in general.
> However, my problem is still not resolved. I guess I can rephrase it a bit
> to let you understand it more:
> (1) I need to generate the RDLC file programmatically because I don't know
> the report fields at design time
> (2) The same applies to the report data source, and I have a way to run a
> query to get a System.Data.DataTable object which I can use as the report
> data source, but again this depends on the search query, which I don't know
> until run-time
> I have attempted to modify the walkthrough from
> http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
> But instead of creating the rdlc file using the UI, I created the RDLC file
> based on the tutorial from
> http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
> Again, I just renamed this file using the *.rdlc extension instead of *.rdl
> and save it somewhere in my local system.
> As I mentioned above, given a query, I can create a System.Data.DataTable on
> the fly and modified the sample code such that I commented out the followings
> from Form1.Designer.cs:
> //this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
> //this.reportViewer1.LocalReport.ReportEmbeddedResource => "BusinessObject.Report1.rdlc";
> and added the following lines to Form1_Load() just before the line
> this.reportViewer1.RefreshReport();
> this.reportViewer1.LocalReport.DataSources.Add(new
> ReportDataSource("Sales", datatable));
> this.reportViewer1.LocalReport.ReportPath => @."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
>
> When I ran the app, I got the following error:
> "A data source instance has not been supplied for the data source 'DataSet1'"
>
> First of all, am I in the wrong track? Can I actually programmatically
> create an RDL file and simply renamed it as RDLC. What else do I need to do
> programmatically (not through adding it to the project as indicated by the
> walkthrough)?
> Second of all, what am I missing? Is it possible to supply the DataTable
> (report data source) programmatically?
>
> Thanks a lot for reading my long post.
> Jenny
>
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > To create a RDLC file, you may refer the article below
> >
> > Creating Client Report Definition (.rdlc) Files
> > http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> >
> > Converting RDL and RDLC Files
> > http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> >
> > If you encounter any difficulties in the walkthrough, please let me know
> > the detail step you meet the error.
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hi Jenny,
Thanks for your prompt update and it's great to hear you have resovled it
now!! And thanks so much for sharing this information with us.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Ad-Hoc Matrix report calculations

Is there a way to change the value in an ad-hoc matrix report to be the percent of the row an not just the raw number?

unfortunatly not

Ad-hoc INSERT of new Identity Value

Hello,
I have a table with a primary key having IDENTITY (ID).
In case of an INSERT I want to fill another Column C1
with the new Identity value.
How can I achive this? By a Trigger maybe?
Thank you very much
JoachimTry
create table #t (c1 int not null identity(1,1),c2 char(1),c3 as c1)
insert into #t (c2) values ('a')
insert into #t (c2) values ('b')
insert into #t (c2) values ('c')
select * from #t
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim|||What would be the point of having redundant data in the table? Anyway, much
easier with a computed column than with a trigger, but this won't work if
your intention is to later update C1.
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1),
blat VARCHAR(32),
C1 AS id
);
INSERT dbo.foo(blat) SELECT 'bar';
SELECT * FROM dbo.foo;
DROP TABLE dbo.foo;
Or just use a view:
CREATE VIEW dbo.View_foo
AS
SELECT id, C1 = id
FROM dbo.foo;
But it's tough to provide a good answer if we have no idea. It's usually
better to describe the intended goal than to ask us how to accomplish the
solution you've already determined is the only way to do it...
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim