reusing temp table across different procs

Is there any way I can put the following consolidated statement into a proc
or function for reuse'
I have to use dynamic exec statement because filename,extension, or linked
server could change
<consolidated>
create table #t1(
c1 int,
c2 int,
c3 int,
c4 int,
c5 int
)
insert into #t1
EXEC('SELECT
c1,
c2,
c3,
c4,
c5
FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension + ']
')
select * from #t1
</consolidated>
And then I could call this consolidate code in different procs and do some
thing like this
inside proc1...
insert into dbo.mytbl1(c2,c3)
select c2,c3 from <consolidated #t1>
inside proc2...
insert into dbo.mytbl2(c4,c5)
select c4,c5 from <consolidated #t1>
Please let me know if this needs more clarification and TIA..what about global temp tables? prefixed with a double pound sign
http://sqlservercode.blogspot.com/|||If you create the #temp table in the "parent" proc, then execute other procs
from the parent, you can.
USE model;
GO
CREATE PROCEDURE dbo.foo2
AS
BEGIN
SET NOCOUNT ON;
INSERT #foo SELECT 1 UNION ALL SELECT 2;
END;
GO
CREATE PROCEDURE dbo.foo1
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #foo(a INT);
EXEC dbo.foo2;
SELECT * FROM #foo;
DROP TABLE #foo;
END;
GO
EXEC dbo.foo1;
GO
DROP PROCEDURE dbo.foo1, dbo.foo2;
GO
I've read your requirements and <consolidated> but I still don't quite
understand the actual goal and whether the above meets your requirements.
If you need to have independent stored procedures called separately and
still have access to the temp table, I think you are up the wrong tree.
http://www.sommarskog.se/share_data.html
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||Sorry, I missed the cross-server bit.
I don't think that will be possible because the #temp table will probably
belong to a different session. Again, I don't think you can use #temp
tables for this.
While I'll admit I haven't tried this extensively, I don't think global temp
tables will help either, because the session won't necessarily be maintained
across servers, and the calling proc's session dictates the life of the
global temp table.
A
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||I would like to avoid global temp tables
"SQL" wrote:

> what about global temp tables? prefixed with a double pound sign
> http://sqlservercode.blogspot.com/
>|||Aaron,
I would like to import data from a csv file into separate tables. To pull
rows into a table format in query analyzer, I am using linked server.
Temp table gives me staging table functionality. I would like to pull all
the data into a central location and then reuse that central location in
different procs to populate or process that data.
Please let me know if this clarifies the overall approach and thanks again
in advance.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If you create the #temp table in the "parent" proc, then execute other pro
cs
> from the parent, you can.
> USE model;
> GO
> CREATE PROCEDURE dbo.foo2
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT #foo SELECT 1 UNION ALL SELECT 2;
> END;
> GO
> CREATE PROCEDURE dbo.foo1
> AS
> BEGIN
> SET NOCOUNT ON;
> CREATE TABLE #foo(a INT);
> EXEC dbo.foo2;
> SELECT * FROM #foo;
> DROP TABLE #foo;
> END;
> GO
> EXEC dbo.foo1;
> GO
> DROP PROCEDURE dbo.foo1, dbo.foo2;
> GO
>
> I've read your requirements and <consolidated> but I still don't quite
> understand the actual goal and whether the above meets your requirements.
> If you need to have independent stored procedures called separately and
> still have access to the temp table, I think you are up the wrong tree.
> http://www.sommarskog.se/share_data.html
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>
>|||I hate procs that reference temporary tables created by other procs. 8-[
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKthgGKEGHA.3920@.tk2msftngp13.phx.gbl...
> Sorry, I missed the cross-server bit.
> I don't think that will be possible because the #temp table will probably
> belong to a different session. Again, I don't think you can use #temp
> tables for this.
> While I'll admit I haven't tried this extensively, I don't think global
> temp tables will help either, because the session won't necessarily be
> maintained across servers, and the calling proc's session dictates the
> life of the global temp table.
> A
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>|||i a different proc references a "temp" table, it's not really a temp
table.
but for this problem you're having, you can use tempdb. just drop and
create table as you please. for cross server, you can link the servers
together or use opendatasource. try not to do too much cross server
queries.|||>I hate procs that reference temporary tables created by other procs. 8-[
Ok. They can be useful for others, in spite of your hatred for them.
http://www.aspfaq.com/2248
Yes, full of bad practices, but useful nonetheless
PS I hate slow drivers in the left lane, but they still exist, and I still
have to deal with them on my commute.
A|||> but for this problem you're having, you can use tempdb. just drop and
> create table as you please.
Except if the proc is called by two sessions at the same time, they will
both try CREATE TABLE dbo.MyTable, oops, one of them wins, one of them
loses.
A

Reusing part of an existing measure group

Hello,

I have a large cube called Sales and inside that a measure group called SalesDetail. I'd like to reuse only a small slice of this cube in another cube, just 1 product subcategory in fact to meet that specific departments needs. They only want a subset of dimensions and only their data. Would perspectives allow filtering of data, i don't think so...

Rather than creating another view over the fact, and building another cube is there a way of reusing a slice of a cube?

So in the AventureWorks example i'd just like to specifically reuse "[Product].[Product Categories].[Subcategory].&[1]" subcategory..

Regards,

Ben

How about setting up a security role, with just that 1 allowed member on the Product Subcategory atttribute - visual totals can be enabled, so that users in this role only see totals contributed by that member?

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

...

Understanding the AllowedSet Property

The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members.

...

Understanding the VisualTotals Property

The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.

|||

I thought about using security, but will dimension security mean that these users cannot see all categories in another cube?

The intention is that this is a targeted cube for these users, and they won't be distracted by the whole set of data.

|||

"will dimension security mean that these users cannot see all categories in another cube?" - not necessarily, since you can define dimension security just on cube dimensions. This might not be obvious from BOL; but the "Dimension" drop-down tree in the "Dimension Data" tab of the "Role" designer in BIDS shows something like:

-- Database

- Dimensions in Database

-- Cube(s)

- Dimensions in Cube

Answer Re: Security on role-playing dimensions

When you define dimension security, you can set it either on database dimension or on cube dimension. You need to choose one of the cube's role-playing dimensions and set dimension security only on it.

---

Mosha - http://www.mosha.com/msolap

|||

thanks.. this is exactly what i am after... will try it out today.

Reusing parameters in a subreport.

Is it possible to reuse parameters from a primary query to trigger the query in a subreport?

My primary report does totals sold grouped by salesman code, and now we need grand totals per salesmanin the report footer.

So what I did was create a subreport that gives me exactly what I need. Now for my problem. We use the visual basic ActiveX control for our users to query the database for the report.

It was fine before since all the user had to do was enter a from_date, to_date, and a state parameter and the report would do it's thing. Now with the subreport it asks for...

from_date
to_date
state
from_date (SalesManTotals)
to_date (SalesManTotals)
state (SalesManTotals)

So our users are forced to enter the same parameters twice. Once from the VB interface, and then a second time from a Crystal Reports box requesting the same parameters for the subreport.

How can I tell the report to use the first set of parameters for both reports without being requested to enter them a second time for the subreport?Try linking those parameter fields to the field in the subreport that contains that data

Reusing a Chached Lookup component

Is it possible to reuse a Lookup component which is configured with Full chaching?

My requirement is as follows....

A input file have 2 columns called CurrentLocation and PreviousLocation. In the dataflow, values of these two columns needs to be replaced with values from a look up table called "Location".

In my package i have added two LookUp components which replaces values of CurrentLocation and PreviousLocation with the values available in the table "Location". Is there any way to reuse the cache of first lookup component for second column also?

Hi Gopi,

I guess the answer to your question is negative. It would be a really great feature if a tool allows it.

The otherway is, first 'unpivot' ur data, do the lookup and then 'pivot' it again. This way, u need not cache the data 2 times, though the # of lookups is going to be same.

|||

Thiru,

I'm not able to understand the advantage of performing "unpovit" transformation. How am i going to avoid chaching 2 times? Can you please explain in detail?

Regards,

Gopi

|||

Reuse caching would really be a good way to preserve memmory in the etl process. For instance use it in multiple packages that run parallel in a workflow with each other to load (for instance) fact tables.

Please put the reusability of a lookup on the wishlist.

|||

Hi Gopi,

Assume that your data is like this:

Id CurLoc PrevLoc

1 A X

2 A Y

3 B A

And your Location table is:

Location Id

A 1

B 2

X 24

Y 25

When you unpivot your data based on 'Id' field, it will become,

1 A

1 X

2 A

2 Y

3 B

3 A

Do the lookup and u will get result as

1 1

1 24

2 1

2 25

3 2

3 1

Now, if you pivot it with the 'Id' field, it will become,

1 1 24

2 1 25

3 2 1

As I said, still you have to do the lookup 6 times. But your caching it only once.

|||

Gopinath M wrote:

Is it possible to reuse a Lookup component which is configured with Full chaching?

There is no explicit control over it, but Lookup components do try to share the lookup cache, if they use the same datasource and columns for lookup.

You can check if this actually happen in your case by checking the number of queries in the SQL log.

|||

Thiru,

Your solution works for my case. Thanks you very much for the excellent solution.

Regards,

Gopi

|||

Michael ,

Thank you for providing information.

Regards,

Gopi

reuse subquery results in where clause (newbie)

How can I reuse the result of a subquery in the same query where-clause
without having to duplicate the subquery itself? Say I have a query like:
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
order by someCount
and I want to filter the rows by someCount: if I add a clause like:
where someCount > 5
I get a syntax error (invalid column name). How can I refer to the
calculated field someCount without repeating the subquery as a whole?
Thanks!Dan
select <column lists> from
(
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
) as Der
where someCount >5
order by someCount
"Dan" <fusid@.iol.it> wrote in message
news:epI8jZRDFHA.2232@.TK2MSFTNGP14.phx.gbl...
> How can I reuse the result of a subquery in the same query where-clause
> without having to duplicate the subquery itself? Say I have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
> Thanks!
>|||Dan wrote:
> How can I reuse the result of a subquery in the same query
> where-clause without having to duplicate the subquery itself? Say I
> have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
>
Move the subquery to your FROM clause:
select a,b, someCount
from MyTable t inner join
(select d, count(c) someCount from MyOtherTable
where MyOtherTable GROUP BY d) q
ON t.a = q.d
order by someCount
You may find this enlightening:
http://groups-beta.google.com/group...09662
c8
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reuse reports for diference datatbase

Instead of use report manager(http://localhost/Reports) and go to data source
then change the connection string. Can I do it programmatically? And what
class or reference I have to declare'
My goal is do all this in custom code (reference) so base on the pass from
web app, reports will decide which database to connect to.Add the Reporting Services web service to your project as a web reference.
(http://YourServer/ReportServer/ReportService.asmx). There is a DataSource
class. All the members (methods) of the class are listed there.
Charles Kangai, MCT, MCDBA
"vbui" wrote:
> Instead of use report manager(http://localhost/Reports) and go to data source
> then change the connection string. Can I do it programmatically? And what
> class or reference I have to declare'
> My goal is do all this in custom code (reference) so base on the pass from
> web app, reports will decide which database to connect to.
>|||Thanks, do you see anyone do that way or do you have a example of how to use
the reportingservice web reference. It'll help me a lot if you have one.
"Charles Kangai" wrote:
> Add the Reporting Services web service to your project as a web reference.
> (http://YourServer/ReportServer/ReportService.asmx). There is a DataSource
> class. All the members (methods) of the class are listed there.
> Charles Kangai, MCT, MCDBA
> "vbui" wrote:
> > Instead of use report manager(http://localhost/Reports) and go to data source
> > then change the connection string. Can I do it programmatically? And what
> > class or reference I have to declare'
> >
> > My goal is do all this in custom code (reference) so base on the pass from
> > web app, reports will decide which database to connect to.
> >|||Follow this link on Microsoft's web site
http://www.microsoft.com/downloads/details.aspx?FamilyID=e03e1a26-ff8c-4f30-b68c-08c9b27c3d54&DisplayLang=en
Charles Kangai, MCT, MCDBA
"vbui" wrote:
> Thanks, do you see anyone do that way or do you have a example of how to use
> the reportingservice web reference. It'll help me a lot if you have one.
> "Charles Kangai" wrote:
> > Add the Reporting Services web service to your project as a web reference.
> > (http://YourServer/ReportServer/ReportService.asmx). There is a DataSource
> > class. All the members (methods) of the class are listed there.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "vbui" wrote:
> >
> > > Instead of use report manager(http://localhost/Reports) and go to data source
> > > then change the connection string. Can I do it programmatically? And what
> > > class or reference I have to declare'
> > >
> > > My goal is do all this in custom code (reference) so base on the pass from
> > > web app, reports will decide which database to connect to.
> > >

Reuse of space after truncating

Hi,
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,
Can you explain the difference between unallocated and unused?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.129.250.171. 66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>
|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:

> Can you explain the difference between unallocated and unused?
>
|||Your understanding about pages and extent allocation seems to be correct. When you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essentially, you end up with
the same situation as if you just had created the table (and indexes) and before you insert the
first row into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.129.250.171. 68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>

Reuse of space after truncating

Hi,
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,Can you explain the difference between unallocated and unused?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.12
9.250.171.66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:

> Can you explain the difference between unallocated and unused?
>|||Your understanding about pages and extent allocation seems to be correct. Wh
en you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you
do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essenti
ally, you end up with
the same situation as if you just had created the table (and indexes) and be
fore you insert the
first row into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.12
9.250.171.68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>
>

Reuse of space after truncating

Hi,
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,Can you explain the difference between unallocated and unused?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.129.250.171.66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
> Can you explain the difference between unallocated and unused?
>|||Your understanding about pages and extent allocation seems to be correct. When you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essentially, you end up with
the same situation as if you just had created the table (and indexes) and before you insert the
first row into the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.129.250.171.68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>> Can you explain the difference between unallocated and unused?
>

Reuse of report elements

I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.

We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.

What is the best way to approach this?

My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.

My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination

If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!

You can't link common bits into a report unfortunately. IMO your best option is to keep using a template report as your base. Because reports are deployed to a RS server, you can't just update a file and expect all the reports to pick it up, the change has to be added to the reports and then they should be redeployed. I do believe there are ways to change the *styles* of already deployed reports, there are some css files you can tweak under the Reports and ReportServer virtual dirs in IIS.

You just have to make sure that whoever orders the changes knows the true cost of implementing it.

|||

Well, the subreport method got us most of the way there, but we had to do things that way.

The only part that I couldn't figure out was the ability to specify the report source for the subreport as a parameter or field. That way we could share the base report across our projects (using sourcesafe) and changes to it would propagate to all the reports (yes, we'd still have to redeploy, but the issue we're trying to avoid is the need to manually edit things, where a change could be forgotten on one report and introduce a bug.)

Reuse of Recursive Queries

I have a recursive query which I use to retrieve a set of identifiers. Something like:

WITH Set (Id)
AS (/* recursive code */)
SELECT Item FROM MyTable T
WHERE T.ItemId = ANY (SELECT Id FROM Set)

What I would like to achieve is to be able to reuse the code in bold in another procedure without duplicating the code (ie. reuse the query building up the set).

What I've done is to create a user-defined function which returns a TABLE parameter. So now, I have something like:

SELECT Item FROM MyTable T
WHERE T.ItemId = ANY(SELECT Id FROM MyFunction())

My question: is this the righ way of doing it? Does the use of a function incurs any relevant performance cost?

Thanks for advice.TVF would be fine. Another option is to convert it into a view.|||If you are making it as a TVF, then make sure it is inline otherwise you will have performance issues.

Reuse of Field Aliases

I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:

At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.

Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.

Example:
In Sybase I can write this:

Select
Price * Units as Cost
Cost * SalesTax as TotalTax
From Invoice
Where TotalTax > 3.5

However if i want to do this in MS SQL 2000 i have to go trough

Select

Price * Units as Cost

Price * Units * SalesTax as TotalTax

From Invoice

Where (Price * Units * SalesTax) > 3.5

In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?

Sorry: There is no direct translation of this feature.

Dave

|||

The only thing you could do is to reference the expression from as subquery or a common table expression (if you are using SQL Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Reuse deleted primary key id

Hello,

Can I reuse the deleted primary key id? I'm using SQLServer2005.

cheers,
imperialx

Yeah, that is no problem.

|||

Hi jamesqua,

I should have asked like this, "How can I reuse the deletedprimary key id?"

cheers,
imperialx

|||

Can i no u r exact requirement. deleted primary key id means ?

if its a value no problem. U can reuse it.

I think There is no way to store the deleted value(ofcourse u can place into a temp table or another. Incase of procedures its different).Plz check out

Exact wt i need is u r requirement . How u want to deal it.Explanation needed (Thank u)

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi srijaya,

srijaya_ramaraju@.yahoo.com:

...Exact wt i need is u r requirement . How u want to deal it.Explanation needed (Thank u)...

The primary key is incremented by 1 in every addition of data by default, letsay the primary key value is 56, if this row is deleted then I couldn't use thevalue 56 because the next primary key value will be 57 when adding a new data.

My tablewill now look like so, as you can see the id field values aren't sequentially accumulatedby 1.

id |username
1 | Tedd
2 | Scott
3 | Bing
57 | Mich
58 | Jake
76 | Jenn
77 | Lea

Can SQLServer2005 reuse the deleted primary key for me to have a sequencial value for my id fields?

Hope I explain my problem plainly.

cheers,
imperialx

|||

U r primary key column is defined with Identity property. Thats the reason u r Primary column id will be incremented by 1(or as per the seed). Once we removed that entry. I think its not possible(99.99%). So choice is u rs. U want to go with identity column u can. or manually u have to write the code.

Plz correct me if i m wrong.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

If your primary key is an identity column you can't reuse it. You have to rely on the number generated by sql server and can't put your own values in there. If this behavior does not meet your requirements I would suggest going with a non-identity public key.

Reuse calculation later on in query

How can I reuse the field CurrentBalance in my last AND statement?

SELECT rm.rmsacctnum AS [Rms Acct Num],
rf.rmstranamt10 as total_10,
rf.rmstranamt,
(rf.rmstranamt - rf.rmstranamt10) AS [Current Balance]
FROM RMASTER rm
INNER JOIN
(
SELECT
RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt,
SUM(CASE WHEN RMSTRANCDE = '10' THEN rmstranamt ELSE 0 END) AS rmstranamt10
FROM RFINANL
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
--where (rm.rmsacctnum = '4264287999892165' OR
--rm.rmsacctnum = '4264290999892300')
AND rf.RMSTRANCDE IN ('16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND [Current Balance] <> (select rff.rmsbalance from RFINANL rff inner join RMASTER rrr ON rff.RMSFILENUM = rrr.RMSFILENUM
where rrr.rmstrandate = ( select max( rmstrandate) from RMASTER) )

You can use a derived table or view or inline TVF or CTE (Only SQL Server 2005) to encapsulate the SELECT statement with the calculated expressions and then use it in your SELECT statement. If you do not want to create persistent objects then derived table or CTE is the way to go. Ex:

-- derived table

select j

from (select i + 1 from t) as t1(j)

where j > 10;

-- cte

with t1 (j)

as

(

select i+1 from t

)

select j from t1

where j > 10;