Parent-child variable issues that may impact deployment

We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent).

In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong?

What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing.

Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another.

Oh, and I think you could use a spellchecker on this message box. At least I could use one.

In the child package, create a variable to hold the parent variable. Same name is fine. Then in the control flow, right click on the background and select "package configurations".

Enable package configurations. Then add a new one. Change the configuration type to "parent package variable." Then, in the specify configuration settings entry, enter the name of the variable in the parent package. Next, on the following screen, select the "value" property of the variable created in your child package. (Expand the child variable until you can select the value property.) Click next. Give the configuration a name and hit finish. Done.

Phil|||

Phil,

Thanks, but we already have that functionality. The problem is that we don't like it. We want something more robust, sort of like passing a parameter from one function to another. And we can't even pass a value parameter with this thingy. This parent-child functionality is just plain ugly. We are using a control table instead, but we don't like doing that either.

Partitioning Question

I'm faced with a project that requires the caching of vacations.
Each vacation has a departure date & a price.
The amount of different vacations that will need to be cached is probably near 1 million per day.

I will then need to select the price(s) of vacations for either a single day or a date range (based on the vacation criteria).

I was considering creating a new partition (table with a date on it) every day.
This would allow me to jump into the needed table(s) based on the vacation search criteria. This would also allow me to drop tables with past dates.

I was considering running this all on 1 sql server. I was hoping I could create multiple threads for a datespan search and hit all the tables in the daterange at the same time.

Can you guys enlighten the noob on where I really need some help on this?There's quite a lot of info on this in "Books online" (i.e. the Help that comes with SQL Server). If you follow the set of rules, having a table for each day with a 'check constraint' (e.g. on the date) and a top level view doing a UNION of all the tables it should work quite nicely.

Partitioned View Performance Question

Hi,
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
NickNick Dawson (no.thanks@.nowhere.com) writes:
> I've got a view that union's 3 tables together on the same server. The
> base tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
First of all, use the date format YYYYMMDD. The above constraints will
fail if the connection uses a language where Jan and Dec are not month
names.
Second, write the constraints as
Date >= '20060101' AND Date < '20070101'
since there is no date type in SQL Server, your constraints loses a
day as far as SQL Server is concerned.

> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06',
> 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
Do you really get poor performance, or do you just think that the query
plan looks bad?
The query plan is expected. Since SQL Server does not know the value of the
variable before-hand, it must have a plan that involves all tabeles.
But if everything is working OK, you will see that the plan operators
has a STARTUP EXPR. Furthermore, if you run a query with SET STATISTICS ON,
you will see that for two the tables there are 0 logical reads. What
happens is that the SQL Server prunes two of the tables at run-time.
There is one thing that is very important with partioned views, and that
is that the CHECK constraints are *trusted*. It is not sufficient that
they are enabled. If you use a tool like Enterprise Mangager to change
the constraint, it is likely that it use something like:
ALTER TABLE tbl ADD constraint ckc WITH NOCHECK CHECK (Date...)
WITH NOCHECK means that SQL Server should not check whether existing
values conforms to the constraint. This sames time, but leads to the
constraint being not trusted, because there could be non-conformant
data.
To find if you have any constraints that are not trusted do:
SELECT name, xtype, object_id(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What happens when @.d is fed to the proc as a datetime value and you
eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
Hi,
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
Nick|||Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||That's odd. have you tried declaring a datetime variable, assigning it the
date and then selecting from the view, using the parameter?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||I think it's your CHECK constraints. They overlap. See below:
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
'20070101')
GO
These don't overlap, since I changed the <= to <.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||Hhmm, I didn't spot that - nice call.
I changed them as you suggested.
But, these two queries:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
select * from alltmp where DateCol = CONVERT(DATETIME,'2006.02.02')
Still produce different queryplans - the second one is what I'd call
"correct" Even in stats i/o output, the second one clearly only scans one
table whereas the top one scans both of them.
Digging around on google groups a liitle, it appears that other people have
had issues with Datetime columns on partitioning views indicating that it
"doesn't work" Maybe I've encountered a similar problem.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u1NrGqKMGHA.2624@.TK2MSFTNGP12.phx.gbl...
>I think it's your CHECK constraints. They overlap. See below:
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
> '20070101')
> GO
> These don't overlap, since I changed the <= to <.
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
> I just re-created all the constraints with "Check" in place. Thanks for
> pointing that out.
> Unfortunately it doesn't seem to make a difference. Do I need to create
> the
> view in a particular way? - I simply did:
> create view alltmp as
> select * from tmp1
> union all
> select * from tmp2
> I also tried explicitly providing the column names.
> Thanks for your help Tom.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
>
>

partitioned view goofiness

ok, i have a partitoned view of 5 years worth or
partitioned monthly data. each undertable has a check
constraint on a date column
when i run this I get a proper execution plan (note i am
reviewing the actual execution plan, not the estimated)
select * from viewname
where datecol = cast('20041101' as datetime)
but when I run either of these, my execution plans show I
hit all 60 undertables (I tried with cast AND convert and
get the same)
declare @.dt datetime
set @.dt = '20041101'
select * from viewname
where datecol = @.dt
declare @.dt datetime
set @.dt = cast('20041101' as datetime)
select * from viewname
where datecol = @.dt
what am I doing wrong? I want to put the date as a
parameter in a stored procedure but the results are
terrible because the server hits all 60 undertables.
Help pleaseHi
Although it is not the same example as documented in "The Guru's Guide to
SQL Server Architecture and Internals" by Ken Henderson ISBN 0-201-70047-6
goes does talk about query plans for partitioned views and he shows that
although you may see the other partitions in the plan they should have 0
executions.
John
"Buzzer" <anonymous@.discussions.microsoft.com> wrote in message
news:062801c53953$76239780$a501280a@.phx.gbl...
> ok, i have a partitoned view of 5 years worth or
> partitioned monthly data. each undertable has a check
> constraint on a date column
> when i run this I get a proper execution plan (note i am
> reviewing the actual execution plan, not the estimated)
> select * from viewname
> where datecol = cast('20041101' as datetime)
> but when I run either of these, my execution plans show I
> hit all 60 undertables (I tried with cast AND convert and
> get the same)
> declare @.dt datetime
> set @.dt = '20041101'
> select * from viewname
> where datecol = @.dt
> declare @.dt datetime
> set @.dt = cast('20041101' as datetime)
> select * from viewname
> where datecol = @.dt
>
> what am I doing wrong? I want to put the date as a
> parameter in a stored procedure but the results are
> terrible because the server hits all 60 undertables.
> Help please
>|||Sorry John, that is not the case. Each undertable
contributes a healthy percentage to the overall
performance of the execution plan and the query. Yes as
you mentioned, the rows are zero, but each has a cost.
The problem I believe revolves around the datetime issue.
When I compare the actual execution plans of using "cast
('20041101' as datetime)" versus "@.dt" in the where
clause, the difference is about 90% to 10% where the 90%
is the for the variable @.dt.
Maybe someone from Microsoft can help. Anyone?

>--Original Message--
>Hi
>Although it is not the same example as documented in "The
Guru's Guide to
>SQL Server Architecture and Internals" by Ken Henderson
ISBN 0-201-70047-6
>goes does talk about query plans for partitioned views
and he shows that
>although you may see the other partitions in the plan
they should have 0
>executions.
>John
>"Buzzer" <anonymous@.discussions.microsoft.com> wrote in
message
>news:062801c53953$76239780$a501280a@.phx.gbl...
I
and
>
>.
>|||On Tue, 5 Apr 2005 05:20:05 -0700, "Buzzer"
<anonymous@.discussions.microsoft.com> wrote:
>Sorry John, that is not the case. Each undertable
>contributes a healthy percentage to the overall
>performance of the execution plan and the query. Yes as
>you mentioned, the rows are zero, but each has a cost.
>The problem I believe revolves around the datetime issue.
>When I compare the actual execution plans of using "cast
>('20041101' as datetime)" versus "@.dt" in the where
>clause, the difference is about 90% to 10% where the 90%
>is the for the variable @.dt.
>Maybe someone from Microsoft can help. Anyone?
Well, it's not "data sniffing" correctly.
I wonder if you put each of the cases into a separate stored procedure
and do your query plans and comparisons, if that would work better.
Josh|||[Sorry about responding to the reply, but the original is not
on msnews.microsoft.com]
The "show execution plan" percentages are estimates, so unless
you have confirmed the 90%/10% figure you quote with real
timings and table access, it's meaningless.
Also, you say "each has a cost", but the fact is that "show execution
plan" does not show actual costs. It only shows estimated cost.
The actual cost of access to tables that are not accessed is probably
zero.
If you have *actual* costs to show there is a difference here, as
opposed to estimated costs, let us know. Estimated costs are not
something you need to worry about.
The reason you see this is fairly simple. The optimizer
can figure out which table contains '20041101', so it shows
the specific table that will be accessed.
When you use @.dt, the optimizer still knows the first thing to do
is find out what table to access, but the optimizer doesn't evaluate
@.dt itself - it creates a plan where the first step is to choose the
correct table. Most likely, the only real difference in cost here
is that a tiny bit of the work (picking the table) is done by the
optimizer in one case and the query processor in the other. More
than likely, you will have the same actual cost in each case.
Just don't assume that the costs and percentages in "show
[actual] execution plan" are for real. The only actual values
this shows are for Rowcount and Executes. The rest are
the same estimates you get with estimated execution plan.
Steve Kass
Drew University
JRStern wrote:

>On Tue, 5 Apr 2005 05:20:05 -0700, "Buzzer"
><anonymous@.discussions.microsoft.com> wrote:
>
>Well, it's not "data sniffing" correctly.
>I wonder if you put each of the cases into a separate stored procedure
>and do your query plans and comparisons, if that would work better.
>Josh
>
>

Parameters: Users must choose one or both parameters

I have two date parameters in my report. The user must make a selction on at
least one of the two parameters (either one), but does not have to make a
selection on both.
How do I verify that at least one parameters has a value? I guess this must
be verified on a pre-Execute event on the View Report button.
Thanks in advance for your help.Allow Null on both dates. Then in your sproc (which is essentially what the
query turns into) check both are not null, else raise an error.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
|I have two date parameters in my report. The user must make a selction on
at
| least one of the two parameters (either one), but does not have to make a
| selection on both.
|
| How do I verify that at least one parameters has a value? I guess this
must
| be verified on a pre-Execute event on the View Report button.
|
| Thanks in advance for your help.|||Do I understand you correct when I sum up your answer like this:
In the receiving procedure on the SQL Server I will have to check if at
least one of the two parameters has a value. If not I raise an error in the
stored procedure. This error is automatically caught by Reporting Services
and displayed to the end user.
"William Stacey [MVP]" wrote:
> Allow Null on both dates. Then in your sproc (which is essentially what the
> query turns into) check both are not null, else raise an error.
> --
> William Stacey [MVP]
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
> |I have two date parameters in my report. The user must make a selction on
> at
> | least one of the two parameters (either one), but does not have to make a
> | selection on both.
> |
> | How do I verify that at least one parameters has a value? I guess this
> must
> | be verified on a pre-Execute event on the View Report button.
> |
> | Thanks in advance for your help.
>
>|||Right. And the report page will display the error text as you entered it.
A dialog box would be better (maybe), but that is what we have.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:9A2E1E77-BC99-466C-A856-F4DD48DD2B64@.microsoft.com...
| Do I understand you correct when I sum up your answer like this:
|
| In the receiving procedure on the SQL Server I will have to check if at
| least one of the two parameters has a value. If not I raise an error in
the
| stored procedure. This error is automatically caught by Reporting Services
| and displayed to the end user.
|
|
|
|
| "William Stacey [MVP]" wrote:
|
| > Allow Null on both dates. Then in your sproc (which is essentially what
the
| > query turns into) check both are not null, else raise an error.
| >
| > --
| > William Stacey [MVP]
| >
| > "Billy" <Billy@.discussions.microsoft.com> wrote in message
| > news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
| > |I have two date parameters in my report. The user must make a selction
on
| > at
| > | least one of the two parameters (either one), but does not have to
make a
| > | selection on both.
| > |
| > | How do I verify that at least one parameters has a value? I guess this
| > must
| > | be verified on a pre-Execute event on the View Report button.
| > |
| > | Thanks in advance for your help.
| >
| >
| >

Parameters of SqlDataSource

Hello I need help withsetting parameters for SqlDataSource

I have a simple program. I want display date from database on MS SQLSERVER from the table USERS only for current sing on user select by his login.

I save into this variable login current user: string @.LOGIN = Context.User.Identity.Name;

I have already done with this way without SqlDataSource:

string login = Context.User.Identity.Name;SqlConnection conn1 =newSqlConnection("server=CR\\SQLEXPRESS;database=myData;integrated security=SSPI");

conn1.Open();

SqlCommand cmd1 =newSqlCommand(" SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID whereUSER.LOGIN=@.LOGIN", conn1);

cmd1.Parameters.Add("@.LOGIN",SqlDbType.NVarChar, 50);

cmd1.Parameters[

"@.LOGIN"].Value = login;1.Parameters.Add("@.LOGIN",SqlDbType.NVarChar, 50);

cmd1.Parameters[

"@.LOGIN"].Value = login;

Now I don't know how to do with SqlDataSource, what I have to set in SqlDataSource1 yet

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"ProviderName="<%$ ConnectionStrings:myConnectionString.ProviderName %>"SelectCommand="SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@.LOGIN"></asp:SqlDataSource>

Right click on the SqlDataSource in the design view, follow the wizard and write custom query with parameters.

bullpit

Parameters in a matrix table

I have a matrix report with a start date & end date parameter. when i run the
query in the data payne eveything works fine. when i run the report in the
preview pane, the matrix table returns the entire data in the database. same
occurs when i add a table. what am i missing' thanks in advanceDo you have default values for your parameters? Open Report\Report
Parameters from the main menu, setup available values and default
values for your parameters.
Good luck.
Henry
Tango wrote:
> I have a matrix report with a start date & end date parameter. when i
run the
> query in the data payne eveything works fine. when i run the report
in the
> preview pane, the matrix table returns the entire data in the
database. same
> occurs when i add a table. what am i missing' thanks in advance|||thanks for your help but ive done that. default is today
"fanh@.tycoelectronics.com" wrote:
> Do you have default values for your parameters? Open Report\Report
> Parameters from the main menu, setup available values and default
> values for your parameters.
> Good luck.
> Henry
>
> Tango wrote:
> > I have a matrix report with a start date & end date parameter. when i
> run the
> > query in the data payne eveything works fine. when i run the report
> in the
> > preview pane, the matrix table returns the entire data in the
> database. same
> > occurs when i add a table. what am i missing' thanks in advance
>|||it was the filters in the matrix properties
thanks to me for working this out
"Tango" wrote:
> thanks for your help but ive done that. default is today
> "fanh@.tycoelectronics.com" wrote:
> > Do you have default values for your parameters? Open Report\Report
> > Parameters from the main menu, setup available values and default
> > values for your parameters.
> > Good luck.
> >
> > Henry
> >
> >
> > Tango wrote:
> > > I have a matrix report with a start date & end date parameter. when i
> > run the
> > > query in the data payne eveything works fine. when i run the report
> > in the
> > > preview pane, the matrix table returns the entire data in the
> > database. same
> > > occurs when i add a table. what am i missing' thanks in advance
> >
> >

Parameters datepicker

Dear All,
I was wondering if we can use datepicker to enter date parameter in our
report.
thanks in advanceIf you are running RS 2005, yes. No date picker available with RS 2000. If
RS 2005 just have the parameter be of datatime type (Report->Report
Parameters menu.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Karim Mohamed" <k_a_r_i_m_._m_o_h_a_m_e_d@.link.net> wrote in message
news:OmanuDaWGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Dear All,
> I was wondering if we can use datepicker to enter date parameter in our
> report.
> thanks in advance
>

Parameters & reporting model

Hi,

I have created a reporting model.

When i use this model to create a report, all goes well.

But now i want to add date parameters (from ... to) to this report.

When i add my parameters in my reporting parameters (i use visual studio to create my reports)

i get the calender to select a from and to date.

but when i generate my report, i get the whole date range available in my database (so the parameters

are not used).

I tried to create a filter in my dataset, but there i cannot specify the parameters i created (error : from is not a date. i also tried @.From but that doesn't work either)

anybody have an idea how i can add parameters ? but it must be based on reporting model.

Vincent

Follow the following steps to achive this:

1. open the report.

2. click on data tab, edit selected dataset

3. click on parameter tab.

4. give the propername of parameter. and select the declared report parameter in value column.

5. then click on filter tab, select the desire field name in Exp. column and then value column will be

=Parameters!Parametername.Value

Now run the report.

Parameters & Sybase DB

I have a report that takes a single date parameter. My DataSource however,
could be either a MSSQL db or a Sybase db. Both of these are included as
shared datasources in my project (This is becase some clients of the report
may point to the MSSQL db while others will point to the Sybase db). However,
so far it seems that the rules for writing parameterized queries differ for
these 2 platforms i.e.
For MSSQL, i have:
where date > @.date,
but for Sybase, I had to say:
where date > ?
How can I write just one report so that when deployed, changing the
datasource from MSSQL to Sybase does not cause a problem?Have you tried doing a stored procedure and passing a the parameter in?...
That might work for both...(Although I am surprised that there is even the
difference you have discovered.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>I have a report that takes a single date parameter. My DataSource however,
> could be either a MSSQL db or a Sybase db. Both of these are included as
> shared datasources in my project (This is becase some clients of the
> report
> may point to the MSSQL db while others will point to the Sybase db).
> However,
> so far it seems that the rules for writing parameterized queries differ
> for
> these 2 platforms i.e.
> For MSSQL, i have:
> where date > @.date,
> but for Sybase, I had to say:
> where date > ?
> How can I write just one report so that when deployed, changing the
> datasource from MSSQL to Sybase does not cause a problem?|||One important principle of Reporting Services 2000 is to not "rewrite" the
dataset query. The statement is essentially sent directly to the data
provider. Some data providers do not support named parameters (like OleDb
data providers). Other data providers support named parameters, but they use
different syntax to mark parameters and may have various flavors for SQL
keywords.
E.g.:
Managed SQL Provider: select * from emp where name = @.Name
Managed Oracle Provider: select * from emp where name = :Name
For the Sybase provider, it looks like Aparna is using the Sybase OleDb
provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> Have you tried doing a stored procedure and passing a the parameter in?...
> That might work for both...(Although I am surprised that there is even the
> difference you have discovered.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>>I have a report that takes a single date parameter. My DataSource however,
>> could be either a MSSQL db or a Sybase db. Both of these are included as
>> shared datasources in my project (This is becase some clients of the
>> report
>> may point to the MSSQL db while others will point to the Sybase db).
>> However,
>> so far it seems that the rules for writing parameterized queries differ
>> for
>> these 2 platforms i.e.
>> For MSSQL, i have:
>> where date > @.date,
>> but for Sybase, I had to say:
>> where date > ?
>> How can I write just one report so that when deployed, changing the
>> datasource from MSSQL to Sybase does not cause a problem?
>|||Hi Robert,
I actually tired using both the Sybase ASE Ole DB provider, as well as the
'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
Problems'. Here are the steps he mentioned:
---
1. Create the ODBC Machine DSN and test that connection works
2. Pick provider Microsoft OLE DB Provider for ODBC drivers
3. Next
4. Use Data Source name and pick the DSN you created in 1
5. Enter username and password to use.
6. Pick initial catalog to use and click on test connection
7. Click OK
You should now be done. If you have a problem with username and password
after you are done double click on the data source and go to the credential
tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
---
In both cases, I tried running the parameterized query (using @.Date) in both
the Generic Query Designer as well the Visual Query Designer. In both cases,
I get the message "The Data Extension ODBC does not support named parameters.
Use unnamed parameters instead".
I did read about Oracle's conventions for using named parameters, and tried
searching for info. on using named parameters in Sybase, but to no avail. I
know Wayne mentioned using stored procedures, but I wanted to know if I could
use a simple query for both Sybase & Sql Server platforms. Any idea how named
parameters are used for Sybase? All in all, it seems to me that because
there are slight variations in syntax, etc between the various providers, it
may not be possible to write a parameterized query and then dynamically
change the data source provider once deployed (i.e. from Sql Server to Oracle
or Sybase)...Is that correct?
--Aparna.
"Robert Bruckner [MSFT]" wrote:
> One important principle of Reporting Services 2000 is to not "rewrite" the
> dataset query. The statement is essentially sent directly to the data
> provider. Some data providers do not support named parameters (like OleDb
> data providers). Other data providers support named parameters, but they use
> different syntax to mark parameters and may have various flavors for SQL
> keywords.
> E.g.:
> Managed SQL Provider: select * from emp where name = @.Name
> Managed Oracle Provider: select * from emp where name = :Name
> For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > Have you tried doing a stored procedure and passing a the parameter in?...
> > That might work for both...(Although I am surprised that there is even the
> > difference you have discovered.)
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> >>I have a report that takes a single date parameter. My DataSource however,
> >> could be either a MSSQL db or a Sybase db. Both of these are included as
> >> shared datasources in my project (This is becase some clients of the
> >> report
> >> may point to the MSSQL db while others will point to the Sybase db).
> >> However,
> >> so far it seems that the rules for writing parameterized queries differ
> >> for
> >> these 2 platforms i.e.
> >> For MSSQL, i have:
> >> where date > @.date,
> >>
> >> but for Sybase, I had to say:
> >> where date > ?
> >>
> >> How can I write just one report so that when deployed, changing the
> >> datasource from MSSQL to Sybase does not cause a problem?
> >
> >
>
>|||For Sybase you cannot use named parameters (regardless of provider used).
For Sybase you put a ?
I have (unfortunately) been living in Sybase with RS so let me know if you
have any other difficulties. There are little quirks from time to time.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
> Hi Robert,
> I actually tired using both the Sybase ASE Ole DB provider, as well as the
> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
> Problems'. Here are the steps he mentioned:
> ---
> 1. Create the ODBC Machine DSN and test that connection works
> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
> 3. Next
> 4. Use Data Source name and pick the DSN you created in 1
> 5. Enter username and password to use.
> 6. Pick initial catalog to use and click on test connection
> 7. Click OK
> You should now be done. If you have a problem with username and password
> after you are done double click on the data source and go to the
credential
> tab.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ---
> In both cases, I tried running the parameterized query (using @.Date) in
both
> the Generic Query Designer as well the Visual Query Designer. In both
cases,
> I get the message "The Data Extension ODBC does not support named
parameters.
> Use unnamed parameters instead".
> I did read about Oracle's conventions for using named parameters, and
tried
> searching for info. on using named parameters in Sybase, but to no avail.
I
> know Wayne mentioned using stored procedures, but I wanted to know if I
could
> use a simple query for both Sybase & Sql Server platforms. Any idea how
named
> parameters are used for Sybase? All in all, it seems to me that because
> there are slight variations in syntax, etc between the various providers,
it
> may not be possible to write a parameterized query and then dynamically
> change the data source provider once deployed (i.e. from Sql Server to
Oracle
> or Sybase)...Is that correct?
> --Aparna.
> "Robert Bruckner [MSFT]" wrote:
> > One important principle of Reporting Services 2000 is to not "rewrite"
the
> > dataset query. The statement is essentially sent directly to the data
> > provider. Some data providers do not support named parameters (like
OleDb
> > data providers). Other data providers support named parameters, but they
use
> > different syntax to mark parameters and may have various flavors for SQL
> > keywords.
> > E.g.:
> > Managed SQL Provider: select * from emp where name = @.Name
> > Managed Oracle Provider: select * from emp where name = :Name
> >
> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> > provider.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > > Have you tried doing a stored procedure and passing a the parameter
in?...
> > > That might work for both...(Although I am surprised that there is even
the
> > > difference you have discovered.)
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> > >>I have a report that takes a single date parameter. My DataSource
however,
> > >> could be either a MSSQL db or a Sybase db. Both of these are included
as
> > >> shared datasources in my project (This is becase some clients of the
> > >> report
> > >> may point to the MSSQL db while others will point to the Sybase db).
> > >> However,
> > >> so far it seems that the rules for writing parameterized queries
differ
> > >> for
> > >> these 2 platforms i.e.
> > >> For MSSQL, i have:
> > >> where date > @.date,
> > >>
> > >> but for Sybase, I had to say:
> > >> where date > ?
> > >>
> > >> How can I write just one report so that when deployed, changing the
> > >> datasource from MSSQL to Sybase does not cause a problem?
> > >
> > >
> >
> >
> >|||I have a Sybase Anywhere db (version 8.x) that I have been using since RS
was released. For some reason my parameter (specified with a ?) is not
linking up with the selection entered in by the user prompt I have
associated with it. Any ideas? =)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> For Sybase you cannot use named parameters (regardless of provider used).
> For Sybase you put a ?
> I have (unfortunately) been living in Sybase with RS so let me know if you
> have any other difficulties. There are little quirks from time to time.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
> both
>> the Generic Query Designer as well the Visual Query Designer. In both
> cases,
>> I get the message "The Data Extension ODBC does not support named
> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
> tried
>> searching for info. on using named parameters in Sybase, but to no avail.
> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various providers,
> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
> as
>> > >> shared datasources in my project (This is becase some clients of the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>|||Sometimes you have to hook them backup up. Go to the dataset, click on the
..., parameters tab. On the left put ? on the right pick the appropriate
report parameter. This should be done in the order that the ? appear in your
query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:11hhcmvr55mpl81@.corp.supernews.com...
>I have a Sybase Anywhere db (version 8.x) that I have been using since RS
>was released. For some reason my parameter (specified with a ?) is not
>linking up with the selection entered in by the user prompt I have
>associated with it. Any ideas? =)
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
>> For Sybase you cannot use named parameters (regardless of provider used).
>> For Sybase you put a ?
>> I have (unfortunately) been living in Sybase with RS so let me know if
>> you
>> have any other difficulties. There are little quirks from time to time.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
>> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
>> both
>> the Generic Query Designer as well the Visual Query Designer. In both
>> cases,
>> I get the message "The Data Extension ODBC does not support named
>> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
>> tried
>> searching for info. on using named parameters in Sybase, but to no
>> avail.
>> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
>> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
>> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various
>> providers,
>> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
>> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
>> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
>> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
>> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase
>> > OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
>> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
>> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
>> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
>> as
>> > >> shared datasources in my project (This is becase some clients of
>> > >> the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase
>> > >> db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
>> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>>
>

Parameters

Hello everybody!
I have a question for you.
In access, you can make a query using parameters:
DELETE *
FROM tableName
WHERE myDate < [DATE]
[DATE] is a question asked to the user.
Can we do the same in SQL Store procedures?
I've tried the ? and %DATE% but it don't work.
Tanks all!
MarianneSQL Server runs on the server machine, so it cannot be made to pop up any
type of dialog on the client machine. I.e., you cannot have any type of user
interaction inside a stored procedure. Do this in the client application
instead and pass the value as a parameter to the stored procedure.
--
Tibor Karaszi
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Try:
CREATE PROCEDURE myproc
int @.myvariable
AS
SELECT @.myvariable
DELETE FROM mytable WHERE mytable.myfield = @.myvariable
GO
Take a look "CREATE PROCEDURE" in BOL.
Regards
---
All information provided above AS IS.
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Hello,
In SQL server you cant provide a parameter during run time. Instead you have
to execute the statement along with parameter value.
Sample:
declare @.date datetime
set @.date= getdate()
DELETE FROM tableName WHERE myDate < @.date
You can stored procedures also to do the similar stuff.
Thanks
Hari
US Technology
"SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
news:erTWbu6oDHA.2964@.tk2msftngp13.phx.gbl...
> Try:
> CREATE PROCEDURE myproc
> int @.myvariable
> AS
> SELECT @.myvariable
> DELETE FROM mytable WHERE mytable.myfield = @.myvariable
> GO
> Take a look "CREATE PROCEDURE" in BOL.
> Regards
> ---
> All information provided above AS IS.
> "Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
> news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> > Hello everybody!
> > I have a question for you.
> >
> > In access, you can make a query using parameters:
> >
> > DELETE *
> > FROM tableName
> > WHERE myDate < [DATE]
> >
> > [DATE] is a question asked to the user.
> > Can we do the same in SQL Store procedures?
> > I've tried the ? and %DATE% but it don't work.
> >
> > Tanks all!
> > Marianne
> >
>

Parameters

As a new person to SQL RS, With the report date parameters, can you have a
date control to choose from the calendar?Hi,
With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
Jan Pieter Posthuma
"Mike R" wrote:
> As a new person to SQL RS, With the report date parameters, can you have a
> date control to choose from the calendar?
>
>|||"Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> wrote in message
news:E705C246-22A2-4FC7-B60D-1E7465CB03F6@.microsoft.com...
> Hi,
> With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
> Jan Pieter Posthuma
> "Mike R" wrote:
>> As a new person to SQL RS, With the report date parameters, can you have
>> a
>> date control to choose from the calendar?
>>
Thanks, Whens SRS2005 available?|||November is the promised release date.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike R" <news@.mikeread.freeserve.co.uk> wrote in message
news:d9lvqc$6vj$1$8302bc10@.news.demon.co.uk...
> "Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> wrote in message
> news:E705C246-22A2-4FC7-B60D-1E7465CB03F6@.microsoft.com...
>> Hi,
>> With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
>> Jan Pieter Posthuma
>> "Mike R" wrote:
>> As a new person to SQL RS, With the report date parameters, can you have
>> a
>> date control to choose from the calendar?
>>
> Thanks, Whens SRS2005 available?
>

Parameters

Hi all,
Is it possible to group parameters fields into logical groups in report
view?
eg.
filters:
start date:
end date:
colors:
table border:
chart background:
...
TIA,
KamelThere would be nice to have separate control to manage parameters
presentation and logic.
Sometimes there is a need to have a lot of parameters (over 20) and
there is a presentation problem in reportviewer.
Kamel
kamel wrote:
> Hi all,
> Is it possible to group parameters fields into logical groups in report
> view?
> eg.
> filters:
> start date:
> end date:
> colors:
> table border:
> chart background:
> ...
> TIA,
> Kamel

Parameterized queries with RDA

Hello..

Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?

Thanks in advance.

Best option is to use a stored procedure on the server and use RDA to execute it.

Darren