How to simplify this slow Stored Procedure

Dear Reader(s),

Is there anyway to write the following stored procedure without the loop so that it goes much faster? :confused:

-----------------------
use MJ_ReportBase
go
if exists(select 1 from sysobjects where type='P' and name='sp_Periode')
begin
drop procedure sp_Periode
end
go
create procedure sp_Periode
@.start int
, @.stop int
as
declare @.x int

set @.x = 0
set @.x=@.start

delete from tbl_periode

while (@.x>=@.stop)
begin

-- --
-- --
-- Create table tbl_inout
if exists(select 1 from sysobjects where type='U' and name='tbl_inout')
begin
drop table tbl_inout
end

select datetimestamp,accname,badgeid,personname,inoutreg into tbl_inout from WinXS..x18 where convert(varchar,datetimestamp,120)+' '+ltrim(str(id))+' '+ltrim(str(badgeid)) in
(select convert(varchar,max(datetimestamp),120)+' '+ltrim(str(max(id)))+' '+ltrim(str(badgeid)) as datetimestamp from WinXS..x18 where (accname='Kelder -1' or accname='Tnk Entree') and convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105) group by badgeid)
and badgeid>0
order by personname

-- --
-- --
-- Create table tbl_result

if exists(select 1 from sysobjects where type='U' and name='tbl_result')
begin
drop table tbl_result
end

-- --
-- --

select
convert(varchar,datetimestamp,105) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
into
tbl_result
from
WinXS..x18
where
convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105)
and
accname in ('Kelder -1','Tnk Entree')
and badgeid>0
group by
convert(varchar,WinXS..x18.datetimestamp,105)
, badgeid
, initials
, personname
order by
initials
, personname asc
, convert(varchar,datetimestamp,105) asc

-- --
-- --
-- Rapportage tabel

insert into
tbl_periode
select
tbl_result.datum as DATUM
, ltrim(ltrim(rtrim(tbl_result.naam))+' '+ltrim(rtrim(isnull(tbl_result.voornaam,' ')))) as NAAM
, tbl_result.min as MIN
, tbl_result.max as MAX
, case tbl_inout.inoutreg when 1 then 'in' when 2 then 'out' else 'err' end as [IN/OUT]
, substring('00000',1,5-len(tbl_result.pas))+ltrim(str(tbl_result.pas)) as PAS
from
tbl_inout,tbl_result
where
tbl_result.datum+' '+tbl_result.max+' '+ltrim(str(tbl_result.pas))
= convert(varchar,tbl_inout.datetimestamp,105)+' '+convert(varchar,tbl_inout.datetimestamp,108)+' '+ltrim(str(badgeid))
order by
tbl_result.naam asc

-- --
-- --
--

set @.x=@.x-1
end
go

print 'Klaar!'
-----------------------

What it does is determining the minimum entry time and the maximum exiting time per day of people going true the main entrance of a building.

Many thanks in advance.
:)First...collapse the queries in to 1.

Second, what's the first select for?

Third

Loose the loop and do

WHERE datetimestamp > GetDate()-@.Start
AND datetimestamp < = GetDate()-@.Stop|||I don't even want to try to figure out your code. Don't use permanent tables for temporary storage (your tbl_inout table). That will get you into trouble in a multi-user environment.

Post the relevent DDL for your tables any somebody here can probably show you a more efficient set-based algorithm.|||Hi to all,

I have found the solution after a long time puzzeling. :)

Please check the sql below:
-----------------------
select
tbl_INOUT.*
, CASE B.inoutreg WHEN 1 THEN 'IN' WHEN 2 THEN 'UIT' ELSE 'Fout' END 'STATUS'
from
(
select
convert(char(10),datetimestamp,120) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
from
WinXS..x18
where
(convert(varchar(10),datetimestamp,120)>=convert(varchar(10),'2005-01-01',120)
and
convert(varchar(10),datetimestamp,120)<=convert(varchar(10),'2005-02-28',120))
and
accname in ('Kelder -1','Tnk Entree')
and
badgeid>0
group by
convert(char(10),datetimestamp,120)
, badgeid
, initials
, personname
) tbl_INOUT, WinXS..x18 B
where
tbl_INOUT.pas=B.badgeid
and
convert(datetime,tbl_INOUT.datum+' '+tbl_INOUT.max,120)=B.datetimestamp
and
badgeid=81
order by
tbl_INOUT.naam asc
, tbl_INOUT.datum DESC

Greetz,
DePrins
:D

How to simpify the Deployment process of a SQL Server Report

Dear all,
I have 5 SQL Servers and totally around 100 databases (1 database for 1
client).
Whenever I finish a Report development on my development server, I will need
to repeat the same task on these 100 databases 100 times ?
Although I can copy the Report Definition File from the development server
to these 100 databases' folders, I still need to do the following tasks :
(1)Create 100 new Visual Studio projects
(2)Enter "Start Item" and "Target Server URL" 100 times
(3)Create 100 new Shared Data Sources
(4)Build and Deploy Visual Studio projects 100 times.
Are there any better or more efficient ways to deploy a new Report in a
multiple servers or multiple databases environment ?
Please help me.
Thanks a lot."cpchan" wrote:
> Dear all,
>
> I have 5 SQL Servers and totally around 100 databases (1 database for 1
> client).
>
> Whenever I finish a Report development on my development server, I will need
> to repeat the same task on these 100 databases 100 times ?
> Although I can copy the Report Definition File from the development server
> to these 100 databases' folders, I still need to do the following tasks :
>
> (1)Create 100 new Visual Studio projects
> (2)Enter "Start Item" and "Target Server URL" 100 times
> (3)Create 100 new Shared Data Sources
> (4)Build and Deploy Visual Studio projects 100 times.
>
> Are there any better or more efficient ways to deploy a new Report in a
> multiple servers or multiple databases environment ?
>
> Please help me.
> Thanks a lot.
>
Hi
I am not a reporting services expert, you may find more help in the
reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
You can probably do what you require using multiple configurations for a
single project that deploys onto the different servers:
http://msdn2.microsoft.com/en-us/library/aa179464.aspx
http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
You could probably use the command prompt utilities and write a
parameterised script that will deploy to a server
John|||Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:679E4454-427D-4734-B5BB-7817C3C11BCB@.microsoft.com...
> "cpchan" wrote:
> > Dear all,
> >
> >
> > I have 5 SQL Servers and totally around 100 databases (1 database for 1
> > client).
> >
> >
> >
> > Whenever I finish a Report development on my development server, I will
need
> > to repeat the same task on these 100 databases 100 times ?
> > Although I can copy the Report Definition File from the development
server
> > to these 100 databases' folders, I still need to do the following tasks
:
> >
> >
> > (1)Create 100 new Visual Studio projects
> > (2)Enter "Start Item" and "Target Server URL" 100 times
> > (3)Create 100 new Shared Data Sources
> > (4)Build and Deploy Visual Studio projects 100 times.
> >
> >
> >
> > Are there any better or more efficient ways to deploy a new Report in a
> > multiple servers or multiple databases environment ?
> >
> >
> >
> > Please help me.
> > Thanks a lot.
> >
> Hi
> I am not a reporting services expert, you may find more help in the
> reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
> You can probably do what you require using multiple configurations for a
> single project that deploys onto the different servers:
> http://msdn2.microsoft.com/en-us/library/aa179464.aspx
> http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
> You could probably use the command prompt utilities and write a
> parameterised script that will deploy to a server
> John

How to show two dataset with equal & non equal of multiple selection.

Dear Friends,

In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.

Please advice. Thanks.

warm regards
Rakin
Singapore.

It sounds like you are using RS 2005, since you have multi value report parameters.

On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:

Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value

The IN-filter can automatically deal with multi-value parameters (which represent an array of values).

-- Robert

|||

Thanks Robert.

You are right. I am using RS 2005.

Now, I am able to get the value for the first option. ( with IN )

But I could n't find any NOT IN option in the filter for the second set of results ?

Could you please advice for this ?

Regards
Rakin

|||

Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:

E.g. select * from products where ReorderLevel not IN (@.Level)

I also attached a small sample report at the bottom based on the Northwind database.

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.

The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.

Have any of you encountered anything like this?

Niall

How to show two dataset with equal & non equal of multiple selection.

Dear Friends,

In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.

Please advice. Thanks.

warm regards
Rakin
Singapore.

It sounds like you are using RS 2005, since you have multi value report parameters.

On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:

Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value

The IN-filter can automatically deal with multi-value parameters (which represent an array of values).

-- Robert

|||

Thanks Robert.

You are right. I am using RS 2005.

Now, I am able to get the value for the first option. ( with IN )

But I could n't find any NOT IN option in the filter for the second set of results ?

Could you please advice for this ?

Regards
Rakin

|||

Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:

E.g. select * from products where ReorderLevel not IN (@.Level)

I also attached a small sample report at the bottom based on the Northwind database.

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.

The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.

Have any of you encountered anything like this?

Niall

sql

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
Ellis
Ellis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison
sql

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
EllisEllis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
Ellis
Ellis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
EllisEllis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

How to set up a regular job which will put the quesry result into file and email

Dear forum people,

I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.

I am appreciate every answer.

Thank you.

Annyou can do this whole thing with SQLMail.|||Thrasymachus,
please explain me how can I use SQLMail. Please do not get angry with me as I am not sql person, I just create some queries and know how to use some other things from SQL Enterprise Management.
Thank you.|||I do not get angry but I do not repeat readily available information. So, if you go to Start-->Programs-->Microsoft SQL Server-->SQL Server books Online-->Contents-->Admnistering SQL Server-->Managing Servers-->SQL Mail you can read all about this.

If you want to see examples type xp_sendmail in http://www.google.com/codesearch/advanced_code_search.

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select'bogus - '+LEFT(DATENAME(month,getdate()),3)+' '+(CAST(DatePart(dd,GetDate())asnvarchar)+' '+CAST(DatePart(yyyy,GetDate())asnvarchar))asFILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005Enterprise edition only|||

hi,

Can you help me on this issue,

My requirement is need to change the Report data file name appending with timestamp.

1. I created RDL
2. Opening the RDL data into my GWT application with new IE window
3. Exporting the data into excel/pdf.
4. Able to popup Download file Dialog.
5. Asking the File to save. ( Here by defualt it's giving the RDL File name only. I want to display with RDL file name with timestamp).

But what every you said , I created data-driven subscription, In Delivery Query Screen: Even i tried with A shared data source, able to validate the Command text. Finally not able to change the file name. Can you please tell what is the problem is.

best regards

bhushanam

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select 'bogus - ' + LEFT(DATENAME(month, getdate()),3) + ' ' + (CAST(DatePart(dd, GetDate()) as nvarchar) + ' ' + CAST(DatePart(yyyy, GetDate()) as nvarchar)) as FILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005 Enterprise edition only|||

hi,

Can you help me on this issue,

My requirement is need to change the Report data file name appending with timestamp.

1. I created RDL
2. Opening the RDL data into my GWT application with new IE window
3. Exporting the data into excel/pdf.
4. Able to popup Download file Dialog.
5. Asking the File to save. ( Here by defualt it's giving the RDL File name only. I want to display with RDL file name with timestamp).

But what every you said , I created data-driven subscription, In Delivery Query Screen: Even i tried with A shared data source, able to validate the Command text. Finally not able to change the file name. Can you please tell what is the problem is.

best regards

bhushanam

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select 'bogus - ' + LEFT(DATENAME(month, getdate()),3) + ' ' + (CAST(DatePart(dd, GetDate()) as nvarchar) + ' ' + CAST(DatePart(yyyy, GetDate()) as nvarchar)) as FILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005 Enterprise edition only

How to set replication

Dear Experts,
Would you please help me to set SQL 2000 replication ?
I need a link or article that show me how to set replication between SQL
2000 servers.
thank you
best regards.
Akkado,
have a look at
(a) this site for a graphical walkthrough:
http://www.mssqlcity.com/Articles/Replic/Replic.htm
(b) books on line (BOL) for an indepth description of replication.
(c) this recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
HTH,
Paul Ibison

How to set full-text indexing on table which has multi-columns as primary key?

Dear sirs,
How to set full-text indexing on table which has multi-columns as primary
key?
Thanks
ABC,
Unfortunately, it is not directly possible in any SQL Server versions,
including SQL Server 2005 as you must have a unique, single non-nullable
column to FT-enabled a table... That said, the workaround is easy. Alter the
table and add a int column with the identity property, for example:
use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
go
-- Alter Table: add unique, single non-nullable column
ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go
exec sp_help EmployeeTerritories
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"ABC" <abc.abc.com> wrote in message
news:%23$mqjDlwFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Dear sirs,
> How to set full-text indexing on table which has multi-columns as primary
> key?
> Thanks
>
|||Thanks, It will be a large great workload to alter all tables (over 200
tables).
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O7mMWulwFHA.1148@.TK2MSFTNGP11.phx.gbl...
> ABC,
> Unfortunately, it is not directly possible in any SQL Server versions,
> including SQL Server 2005 as you must have a unique, single non-nullable
> column to FT-enabled a table... That said, the workaround is easy. Alter
> the table and add a int column with the identity property, for example:
> use Northwind
> go
> exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
> (EmployeeID, TerritoryID)
> go
> -- Alter Table: add unique, single non-nullable column
> ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
> go
> CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
> go
> exec sp_help EmployeeTerritories
>
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "ABC" <abc.abc.com> wrote in message
> news:%23$mqjDlwFHA.3256@.TK2MSFTNGP09.phx.gbl...
>
|||You're welcome, ABC,
Unfortunately, this limit is by design and *might* be lifted in the next
version of SQL Server, post-SQL Server 2005.
However, if you're FT-enabling over 200 tables, you should be aware of
another SQL Server 2000 (and not SQL 2005) limit of 256 FT Catalogs per
server (machine). I'd recommend that you create a limited number of related
FT Catalogs and then use these to place more than one Ft-enabled table in
each FT Catalog.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"ABC" <abc.abc.com> wrote in message
news:efXQuMnwFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks, It will be a large great workload to alter all tables (over 200
> tables).
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7mMWulwFHA.1148@.TK2MSFTNGP11.phx.gbl...
>

how to set Default instant of sql server 2005

Dear All,
I install sql 2005 that instant name give Harshad but after installation
come as harshad\harshad
my pc name harshad. Now I want to set as harshad or (local) instant without
reinstalation.
thanks,
harshadharshad,
The bad news is that you cannot rename an instance. You will have to
reinstall to get a (local) instance. In the installation process do not
give an instance name but choose the default instance.
RLF
"harshad" <harshad7_jp@.hotmail.com> wrote in message
news:274295E6-1EE8-445E-B3A8-73655BD467AD@.microsoft.com...
> Dear All,
> I install sql 2005 that instant name give Harshad but after installation
> come as harshad\harshad
> my pc name harshad. Now I want to set as harshad or (local) instant
> without reinstalation.
> thanks,
> harshad
>

How to set connection, Send Parameter and Filter data

Dear all .Netter,

I'm newbie in Reporting Services. I'm able to creating raw Reporting Services reports.

I have these questions to you all :

1. How to change connection to server and database when running in ASP.Net form. Currently in development PC, the server name is ServerA and the Database name is DatabaseA. After i copy to production server, the server name is ServerB and the database name is DatabaseB.

2. How to send parameter to Reporting Services report. I want to show name of user that print the report.

3. How to filter data that will be showed in the Reporting Services. I want to print Invoice with no : INV-2007-0001 and next INV-2007-0002

Sorry, if I'm lazy, but i'm running with time to replace Crystal Reports with Reporting Services.

Thanks and Regards,

Kusno.

These are some great links that will give you any answer you want

http://www.codeproject.com/sqlrs/AHCreatRepsAspNet.asp
http://msdn2.microsoft.com/en-us/library/ms170246.aspx
http://www.codeproject.com/sqlrs/ReportViewer2005.asp

Have Fun