Parsing Variable Length Delimited Records

I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Parsing Variable Length Delimited Records

I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Pareser take wrong index when using @name runtime variable

Hi,
Today one of our developers came to me with a question which totaly
flabbergasted me. He had 2 little examples (see below) which lead to totally
different execution plans and I couldn't tell him why. Maybe anyone in this
newsgroups know the problem and can tell me what's going on.
The first SELECT runs less than one second because it uses an Index for the
index seek. The second SELECT however stunned me completetly because it does
an index scan on the Primary Key resulting in endless waiting.
The question is: Why does the Optimizer take the wrong index for the second
SELECT?
Paul Sinnema
DECLARE@.SVERARJOURNALNUMERIC(21,0),
@.SFIRMA NUMERIC(21,0)
-- TIME : 0 SEC
SELECTSBUCHZEILE, XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = 15884
AND SFIRMA = 0
AND CEINZSTATUS = '40'
SET @.SFIRMA = 0
SET @.SVERARJOURNAL = 15884
-- RUNS INDEFENITLY
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'
Its called "parameter sniffing" ,google it and you will find the more info
Run those command between the queries
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>
|||There are a couple of possibilities. First, make sure the actual column
data types match the variable declarations. If these are different data
types, the expressions will not be sargable if the column value needs to be
converted to the variable declaration (numeric).
Also, when the optimizer generates the plan for the first query, the actual
values are known so the best plan can be generated. However, the values of
@.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
distribution statistics of existing data to guess what actual values might
be provided. This might yield a different plan.
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>
|||Thanks Uri and Dan,
You're probably right. I've also tried the following:
DECLARE @.COMMAND AS VARCHAR(200)
SET @.COMMAND = 'SELECT SBUCHZEILE, XBUCHTEXT' +
' FROM BUCHZEILE ' +
' WHERE SVERARJOURNAL = ' + CAST ( @.SVERARJOURNAL AS VARCHAR) +
' AND SFIRMA = ' + CAST(@.SFIRMA AS VARCHAR) +
' AND CEINZSTATUS = ''40'''
EXEC ( @.COMMAND )
It's a dirty trick but it results in the right index being taken. It
probably proves your suggestion right, that the parser is better at
determining which path to take with constants that with variables.
We could also use the INDEX HINT but that's not very nice. The indexes in
our Project are generated by a tool called Uniface (from Compuware). It
generates the indexes with a sequence number (i.e. BuchzeileI1, BuchzeileI2,
etc.). When one is removed from Uniface all indexes, behind the one removed,
become different names which could lead to dissaster for our INDEX HINTS.
Paul.
"Dan Guzman" wrote:

> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>
|||Nop, doesn't change a thing. See reply to Guzman!
"Uri Dimant" wrote:

> Its called "parameter sniffing" ,google it and you will find the more info
> Run those command between the queries
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
>
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>
>
|||Dan,
Is there a way to trick the Optimizer. We've already tried the following:
- Added an index for SVERARJOURNAL. No result.
- Tried changing the order of the WHERE Clause. No result.
- Use the INDEX HINT. Good result, for us bad solution.
Paul.
"Dan Guzman" wrote:

> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>
|||If the query is in a stored proc, try setting the stored procedure
parameters default values to the ones used for the 'good' plan. The
optimizer will use those values for plan generation instead guessing based
on distribution stats. If that doesn't work, post your table DDL,
including indexes.
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) = 0.0,
@.SFIRMA NUMERIC(21,0) = 15884.0
AS
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...[vbcol=seagreen]
> Dan,
> Is there a way to trick the Optimizer. We've already tried the following:
> - Added an index for SVERARJOURNAL. No result.
> - Tried changing the order of the WHERE Clause. No result.
> - Use the INDEX HINT. Good result, for us bad solution.
> Paul.
>
> "Dan Guzman" wrote:
|||I addition to Dan's great explanation , you can declare a local variable to
perevent parameter sniffing
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) ,
@.SFIRMA NUMERIC(21,0)
AS
DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
DECLARE @.Local_SFIRMA NUMERIC(21,0)
SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
SET @.Local_SFIRMA =@.SFIRMA
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
AND SFIRMA = @.Local_SFIRMA
AND CEINZSTATUS = '40'
> GO
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> If the query is in a stored proc, try setting the stored procedure
> parameters default values to the ones used for the 'good' plan. The
> optimizer will use those values for plan generation instead guessing based
> on distribution stats. If that doesn't work, post your table DDL,
> including indexes.
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> @.SFIRMA NUMERIC(21,0) = 15884.0
> AS
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>
|||Good point, Uri. Paul's initial script used local variables instead of proc
parameters so I didn't go there. To clarify the issue for Paul, SQL Server
'sniffs' parameter values as follows:
1) When stored procedure parameters are used directly in the WHERE clause,
SQL Server uses the default parameters to generate the execution plan. If
you don't specify default values, SQL Server uses NULL which is often the
unusual case and can result in a poor plan for non-trivial queries.
Specifying typical values as parameter defaults instead of NULL will
generate a plan that is good for data of similar cardinality.
2) When local variables are used, SQL Server estimates what values may be
based on table index and column statistics. This is often better than #1
but may perform poorly when unusual values are specified.
3) With constants, the actual values are known so the best execution plan
is generated.
SQL server 2005 introduces features such as statement-level recompilation
that help address this issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>I addition to Dan's great explanation , you can declare a local variable to
>perevent parameter sniffing
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) ,
> @.SFIRMA NUMERIC(21,0)
> AS
> DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> DECLARE @.Local_SFIRMA NUMERIC(21,0)
> SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> SET @.Local_SFIRMA =@.SFIRMA
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> AND SFIRMA = @.Local_SFIRMA
> AND CEINZSTATUS = '40'
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>
|||Guzman, Uri
Thanks guys for the help. The first example of Guzman takes the right index.
The second one of Uri doesn't (i.e. we have the old problem back again). What
I didn't know is that the presented queries came from a stored procedure the
programmer had written. He added a default value for the @.SVERARJOURNAL
parameter and voila, it works as desired.
Thanks again. We've learned something today.
Paul.
"Dan Guzman" wrote:

> Good point, Uri. Paul's initial script used local variables instead of proc
> parameters so I didn't go there. To clarify the issue for Paul, SQL Server
> 'sniffs' parameter values as follows:
> 1) When stored procedure parameters are used directly in the WHERE clause,
> SQL Server uses the default parameters to generate the execution plan. If
> you don't specify default values, SQL Server uses NULL which is often the
> unusual case and can result in a poor plan for non-trivial queries.
> Specifying typical values as parameter defaults instead of NULL will
> generate a plan that is good for data of similar cardinality.
> 2) When local variables are used, SQL Server estimates what values may be
> based on table index and column statistics. This is often better than #1
> but may perform poorly when unusual values are specified.
> 3) With constants, the actual values are known so the best execution plan
> is generated.
> SQL server 2005 introduces features such as statement-level recompilation
> that help address this issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>

Pareser take wrong index when using @name runtime variable

Hi,
Today one of our developers came to me with a question which totaly
flabbergasted me. He had 2 little examples (see below) which lead to totally
different execution plans and I couldn't tell him why. Maybe anyone in this
newsgroups know the problem and can tell me what's going on.
The first SELECT runs less than one second because it uses an Index for the
index seek. The second SELECT however stunned me completetly because it does
an index scan on the Primary Key resulting in endless waiting.
The question is: Why does the Optimizer take the wrong index for the second
SELECT?
Paul Sinnema
DECLARE @.SVERARJOURNAL NUMERIC(21,0),
@.SFIRMA NUMERIC(21,0)
-- TIME : 0 SEC
SELECT SBUCHZEILE, XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = 15884
AND SFIRMA = 0
AND CEINZSTATUS = '40'
SET @.SFIRMA = 0
SET @.SVERARJOURNAL = 15884
-- RUNS INDEFENITLY
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'Its called "parameter sniffing" ,google it and you will find the more info
Run those command between the queries
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||There are a couple of possibilities. First, make sure the actual column
data types match the variable declarations. If these are different data
types, the expressions will not be sargable if the column value needs to be
converted to the variable declaration (numeric).
Also, when the optimizer generates the plan for the first query, the actual
values are known so the best plan can be generated. However, the values of
@.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
distribution statistics of existing data to guess what actual values might
be provided. This might yield a different plan.
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||Nop, doesn't change a thing. See reply to Guzman!
"Uri Dimant" wrote:

> Its called "parameter sniffing" ,google it and you will find the more info
> Run those command between the queries
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
>
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>
>|||Dan,
Is there a way to trick the Optimizer. We've already tried the following:
- Added an index for SVERARJOURNAL. No result.
- Tried changing the order of the WHERE Clause. No result.
- Use the INDEX HINT. Good result, for us bad solution.
Paul.
"Dan Guzman" wrote:

> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to b
e
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actua
l
> values are known so the best plan can be generated. However, the values o
f
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer us
es
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>|||If the query is in a stored proc, try setting the stored procedure
parameters default values to the ones used for the 'good' plan. The
optimizer will use those values for plan generation instead guessing based
on distribution stats. If that doesn't work, post your table DDL,
including indexes.
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) = 0.0,
@.SFIRMA NUMERIC(21,0) = 15884.0
AS
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...[vbcol=seagreen]
> Dan,
> Is there a way to trick the Optimizer. We've already tried the following:
> - Added an index for SVERARJOURNAL. No result.
> - Tried changing the order of the WHERE Clause. No result.
> - Use the INDEX HINT. Good result, for us bad solution.
> Paul.
>
> "Dan Guzman" wrote:
>|||I addition to Dan's great explanation , you can declare a local variable to
perevent parameter sniffing
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) ,
@.SFIRMA NUMERIC(21,0)
AS
DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
DECLARE @.Local_SFIRMA NUMERIC(21,0)
SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
SET @.Local_SFIRMA =@.SFIRMA
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
AND SFIRMA = @.Local_SFIRMA
AND CEINZSTATUS = '40'
> GO
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> If the query is in a stored proc, try setting the stored procedure
> parameters default values to the ones used for the 'good' plan. The
> optimizer will use those values for plan generation instead guessing based
> on distribution stats. If that doesn't work, post your table DDL,
> including indexes.
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> @.SFIRMA NUMERIC(21,0) = 15884.0
> AS
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>|||Good point, Uri. Paul's initial script used local variables instead of proc
parameters so I didn't go there. To clarify the issue for Paul, SQL Server
'sniffs' parameter values as follows:
1) When stored procedure parameters are used directly in the WHERE clause,
SQL Server uses the default parameters to generate the execution plan. If
you don't specify default values, SQL Server uses NULL which is often the
unusual case and can result in a poor plan for non-trivial queries.
Specifying typical values as parameter defaults instead of NULL will
generate a plan that is good for data of similar cardinality.
2) When local variables are used, SQL Server estimates what values may be
based on table index and column statistics. This is often better than #1
but may perform poorly when unusual values are specified.
3) With constants, the actual values are known so the best execution plan
is generated.
SQL server 2005 introduces features such as statement-level recompilation
that help address this issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>I addition to Dan's great explanation , you can declare a local variable to
>perevent parameter sniffing
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) ,
> @.SFIRMA NUMERIC(21,0)
> AS
> DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> DECLARE @.Local_SFIRMA NUMERIC(21,0)
> SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> SET @.Local_SFIRMA =@.SFIRMA
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> AND SFIRMA = @.Local_SFIRMA
> AND CEINZSTATUS = '40'
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>|||Guzman, Uri
Thanks guys for the help. The first example of Guzman takes the right index.
The second one of Uri doesn't (i.e. we have the old problem back again). Wha
t
I didn't know is that the presented queries came from a stored procedure the
programmer had written. He added a default value for the @.SVERARJOURNAL
parameter and voila, it works as desired.
Thanks again. We've learned something today.
Paul.
"Dan Guzman" wrote:

> Good point, Uri. Paul's initial script used local variables instead of pr
oc
> parameters so I didn't go there. To clarify the issue for Paul, SQL Serve
r
> 'sniffs' parameter values as follows:
> 1) When stored procedure parameters are used directly in the WHERE clause
,
> SQL Server uses the default parameters to generate the execution plan. If
> you don't specify default values, SQL Server uses NULL which is often the
> unusual case and can result in a poor plan for non-trivial queries.
> Specifying typical values as parameter defaults instead of NULL will
> generate a plan that is good for data of similar cardinality.
> 2) When local variables are used, SQL Server estimates what values may be
> based on table index and column statistics. This is often better than #1
> but may perform poorly when unusual values are specified.
> 3) With constants, the actual values are known so the best execution plan
> is generated.
> SQL server 2005 introduces features such as statement-level recompilation
> that help address this issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>|||I'm glad we could help you out. Below is a link to the post by Bart Duncan
of Microsoft that describes the behavior in more detail.
http://groups.google.com/group/micr...ff9e6e72122e1fb
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:6D5B834D-3D4A-41E4-87B7-BD43F7DD32FF@.microsoft.com...[vbcol=seagreen]
> Guzman, Uri
> Thanks guys for the help. The first example of Guzman takes the right
> index.
> The second one of Uri doesn't (i.e. we have the old problem back again).
> What
> I didn't know is that the presented queries came from a stored procedure
> the
> programmer had written. He added a default value for the @.SVERARJOURNAL
> parameter and voila, it works as desired.
> Thanks again. We've learned something today.
> Paul.
> "Dan Guzman" wrote:
>

Pareser take wrong index when using @name runtime variable

Hi,
Today one of our developers came to me with a question which totaly
flabbergasted me. He had 2 little examples (see below) which lead to totally
different execution plans and I couldn't tell him why. Maybe anyone in this
newsgroups know the problem and can tell me what's going on.
The first SELECT runs less than one second because it uses an Index for the
index seek. The second SELECT however stunned me completetly because it does
an index scan on the Primary Key resulting in endless waiting.
The question is: Why does the Optimizer take the wrong index for the second
SELECT?
Paul Sinnema
DECLARE @.SVERARJOURNAL NUMERIC(21,0),
@.SFIRMA NUMERIC(21,0)
-- TIME : 0 SEC
SELECT SBUCHZEILE, XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = 15884
AND SFIRMA = 0
AND CEINZSTATUS = '40'
SET @.SFIRMA = 0
SET @.SVERARJOURNAL = 15884
-- RUNS INDEFENITLY
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'Its called "parameter sniffing" ,google it and you will find the more info
Run those command between the queries
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||There are a couple of possibilities. First, make sure the actual column
data types match the variable declarations. If these are different data
types, the expressions will not be sargable if the column value needs to be
converted to the variable declaration (numeric).
Also, when the optimizer generates the plan for the first query, the actual
values are known so the best plan can be generated. However, the values of
@.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
distribution statistics of existing data to guess what actual values might
be provided. This might yield a different plan.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||Thanks Uri and Dan,
You're probably right. I've also tried the following:
DECLARE @.COMMAND AS VARCHAR(200)
SET @.COMMAND = 'SELECT SBUCHZEILE, XBUCHTEXT' +
' FROM BUCHZEILE ' +
' WHERE SVERARJOURNAL = ' + CAST ( @.SVERARJOURNAL AS VARCHAR) +
' AND SFIRMA = ' + CAST(@.SFIRMA AS VARCHAR) +
' AND CEINZSTATUS = ''40'''
EXEC ( @.COMMAND )
It's a dirty trick but it results in the right index being taken. It
probably proves your suggestion right, that the parser is better at
determining which path to take with constants that with variables.
We could also use the INDEX HINT but that's not very nice. The indexes in
our Project are generated by a tool called Uniface (from Compuware). It
generates the indexes with a sequence number (i.e. BuchzeileI1, BuchzeileI2,
etc.). When one is removed from Uniface all indexes, behind the one removed,
become different names which could lead to dissaster for our INDEX HINTS.
Paul.
"Dan Guzman" wrote:
> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>|||Nop, doesn't change a thing. See reply to Guzman!
"Uri Dimant" wrote:
> Its called "parameter sniffing" ,google it and you will find the more info
> Run those command between the queries
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
>
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>
>|||Dan,
Is there a way to trick the Optimizer. We've already tried the following:
- Added an index for SVERARJOURNAL. No result.
- Tried changing the order of the WHERE Clause. No result.
- Use the INDEX HINT. Good result, for us bad solution.
Paul.
"Dan Guzman" wrote:
> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>|||If the query is in a stored proc, try setting the stored procedure
parameters default values to the ones used for the 'good' plan. The
optimizer will use those values for plan generation instead guessing based
on distribution stats. If that doesn't work, post your table DDL,
including indexes.
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) = 0.0,
@.SFIRMA NUMERIC(21,0) = 15884.0
AS
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
> Dan,
> Is there a way to trick the Optimizer. We've already tried the following:
> - Added an index for SVERARJOURNAL. No result.
> - Tried changing the order of the WHERE Clause. No result.
> - Use the INDEX HINT. Good result, for us bad solution.
> Paul.
>
> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual column
>> data types match the variable declarations. If these are different data
>> types, the expressions will not be sargable if the column value needs to
>> be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the values
>> of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index for
>> > the
>> > index seek. The second SELECT however stunned me completetly because it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >|||I addition to Dan's great explanation , you can declare a local variable to
perevent parameter sniffing
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) ,
@.SFIRMA NUMERIC(21,0)
AS
DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
DECLARE @.Local_SFIRMA NUMERIC(21,0)
SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
SET @.Local_SFIRMA =@.SFIRMA
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
AND SFIRMA = @.Local_SFIRMA
AND CEINZSTATUS = '40'
> GO
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> If the query is in a stored proc, try setting the stored procedure
> parameters default values to the ones used for the 'good' plan. The
> optimizer will use those values for plan generation instead guessing based
> on distribution stats. If that doesn't work, post your table DDL,
> including indexes.
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> @.SFIRMA NUMERIC(21,0) = 15884.0
> AS
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> Dan,
>> Is there a way to trick the Optimizer. We've already tried the following:
>> - Added an index for SVERARJOURNAL. No result.
>> - Tried changing the order of the WHERE Clause. No result.
>> - Use the INDEX HINT. Good result, for us bad solution.
>> Paul.
>>
>> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual column
>> data types match the variable declarations. If these are different data
>> types, the expressions will not be sargable if the column value needs to
>> be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the values
>> of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index
>> > for
>> > the
>> > index seek. The second SELECT however stunned me completetly because
>> > it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >
>|||Good point, Uri. Paul's initial script used local variables instead of proc
parameters so I didn't go there. To clarify the issue for Paul, SQL Server
'sniffs' parameter values as follows:
1) When stored procedure parameters are used directly in the WHERE clause,
SQL Server uses the default parameters to generate the execution plan. If
you don't specify default values, SQL Server uses NULL which is often the
unusual case and can result in a poor plan for non-trivial queries.
Specifying typical values as parameter defaults instead of NULL will
generate a plan that is good for data of similar cardinality.
2) When local variables are used, SQL Server estimates what values may be
based on table index and column statistics. This is often better than #1
but may perform poorly when unusual values are specified.
3) With constants, the actual values are known so the best execution plan
is generated.
SQL server 2005 introduces features such as statement-level recompilation
that help address this issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>I addition to Dan's great explanation , you can declare a local variable to
>perevent parameter sniffing
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) ,
> @.SFIRMA NUMERIC(21,0)
> AS
> DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> DECLARE @.Local_SFIRMA NUMERIC(21,0)
> SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> SET @.Local_SFIRMA =@.SFIRMA
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> AND SFIRMA = @.Local_SFIRMA
> AND CEINZSTATUS = '40'
>> GO
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>> If the query is in a stored proc, try setting the stored procedure
>> parameters default values to the ones used for the 'good' plan. The
>> optimizer will use those values for plan generation instead guessing
>> based on distribution stats. If that doesn't work, post your table DDL,
>> including indexes.
>> CREATE PROC dbo_select_BUCHZEILE
>> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
>> @.SFIRMA NUMERIC(21,0) = 15884.0
>> AS
>> SELECT SBUCHZEILE,XBUCHTEXT
>> FROM BUCHZEILE
>> WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> AND SFIRMA = @.SFIRMA
>> AND CEINZSTATUS = '40'
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> Dan,
>> Is there a way to trick the Optimizer. We've already tried the
>> following:
>> - Added an index for SVERARJOURNAL. No result.
>> - Tried changing the order of the WHERE Clause. No result.
>> - Use the INDEX HINT. Good result, for us bad solution.
>> Paul.
>>
>> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual
>> column
>> data types match the variable declarations. If these are different
>> data
>> types, the expressions will not be sargable if the column value needs
>> to be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the
>> values of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone
>> > in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index
>> > for
>> > the
>> > index seek. The second SELECT however stunned me completetly because
>> > it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >
>>
>|||Guzman, Uri
Thanks guys for the help. The first example of Guzman takes the right index.
The second one of Uri doesn't (i.e. we have the old problem back again). What
I didn't know is that the presented queries came from a stored procedure the
programmer had written. He added a default value for the @.SVERARJOURNAL
parameter and voila, it works as desired.
Thanks again. We've learned something today.
Paul.
"Dan Guzman" wrote:
> Good point, Uri. Paul's initial script used local variables instead of proc
> parameters so I didn't go there. To clarify the issue for Paul, SQL Server
> 'sniffs' parameter values as follows:
> 1) When stored procedure parameters are used directly in the WHERE clause,
> SQL Server uses the default parameters to generate the execution plan. If
> you don't specify default values, SQL Server uses NULL which is often the
> unusual case and can result in a poor plan for non-trivial queries.
> Specifying typical values as parameter defaults instead of NULL will
> generate a plan that is good for data of similar cardinality.
> 2) When local variables are used, SQL Server estimates what values may be
> based on table index and column statistics. This is often better than #1
> but may perform poorly when unusual values are specified.
> 3) With constants, the actual values are known so the best execution plan
> is generated.
> SQL server 2005 introduces features such as statement-level recompilation
> that help address this issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
> >I addition to Dan's great explanation , you can declare a local variable to
> >perevent parameter sniffing
> >
> > CREATE PROC dbo_select_BUCHZEILE
> > @.SVERARJOURNAL NUMERIC(21,0) ,
> > @.SFIRMA NUMERIC(21,0)
> > AS
> >
> > DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> > DECLARE @.Local_SFIRMA NUMERIC(21,0)
> >
> > SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> > SET @.Local_SFIRMA =@.SFIRMA
> >
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> > AND SFIRMA = @.Local_SFIRMA
> > AND CEINZSTATUS = '40'
> >> GO
> >
> >
> >
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> >> If the query is in a stored proc, try setting the stored procedure
> >> parameters default values to the ones used for the 'good' plan. The
> >> optimizer will use those values for plan generation instead guessing
> >> based on distribution stats. If that doesn't work, post your table DDL,
> >> including indexes.
> >>
> >> CREATE PROC dbo_select_BUCHZEILE
> >> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> >> @.SFIRMA NUMERIC(21,0) = 15884.0
> >> AS
> >> SELECT SBUCHZEILE,XBUCHTEXT
> >> FROM BUCHZEILE
> >> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> >> AND SFIRMA = @.SFIRMA
> >> AND CEINZSTATUS = '40'
> >> GO
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> >> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
> >> Dan,
> >>
> >> Is there a way to trick the Optimizer. We've already tried the
> >> following:
> >> - Added an index for SVERARJOURNAL. No result.
> >> - Tried changing the order of the WHERE Clause. No result.
> >> - Use the INDEX HINT. Good result, for us bad solution.
> >>
> >> Paul.
> >>
> >>
> >> "Dan Guzman" wrote:
> >>
> >> There are a couple of possibilities. First, make sure the actual
> >> column
> >> data types match the variable declarations. If these are different
> >> data
> >> types, the expressions will not be sargable if the column value needs
> >> to be
> >> converted to the variable declaration (numeric).
> >>
> >> Also, when the optimizer generates the plan for the first query, the
> >> actual
> >> values are known so the best plan can be generated. However, the
> >> values of
> >> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
> >> uses
> >> distribution statistics of existing data to guess what actual values
> >> might
> >> be provided. This might yield a different plan.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> >> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> >> > Hi,
> >> >
> >> > Today one of our developers came to me with a question which totaly
> >> > flabbergasted me. He had 2 little examples (see below) which lead to
> >> > totally
> >> > different execution plans and I couldn't tell him why. Maybe anyone
> >> > in
> >> > this
> >> > newsgroups know the problem and can tell me what's going on.
> >> >
> >> > The first SELECT runs less than one second because it uses an Index
> >> > for
> >> > the
> >> > index seek. The second SELECT however stunned me completetly because
> >> > it
> >> > does
> >> > an index scan on the Primary Key resulting in endless waiting.
> >> >
> >> > The question is: Why does the Optimizer take the wrong index for the
> >> > second
> >> > SELECT?
> >> >
> >> > Paul Sinnema
> >> >
> >> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> >> > @.SFIRMA NUMERIC(21,0)
> >> >
> >> > -- TIME : 0 SEC
> >> > SELECT SBUCHZEILE, XBUCHTEXT
> >> > FROM BUCHZEILE
> >> > WHERE SVERARJOURNAL = 15884
> >> > AND SFIRMA = 0
> >> > AND CEINZSTATUS = '40'
> >> >
> >> > SET @.SFIRMA = 0
> >> > SET @.SVERARJOURNAL = 15884
> >> >
> >> > -- RUNS INDEFENITLY
> >> > SELECT SBUCHZEILE,XBUCHTEXT
> >> > FROM BUCHZEILE
> >> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> >> > AND SFIRMA = @.SFIRMA
> >> > AND CEINZSTATUS = '40'
> >> >
> >>
> >>
> >
> >
>|||I'm glad we could help you out. Below is a link to the post by Bart Duncan
of Microsoft that describes the behavior in more detail.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a6ae4020b1e3621/0ff9e6e72122e1fb?lnk=st&rnum=1#0ff9e6e72122e1fb
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:6D5B834D-3D4A-41E4-87B7-BD43F7DD32FF@.microsoft.com...
> Guzman, Uri
> Thanks guys for the help. The first example of Guzman takes the right
> index.
> The second one of Uri doesn't (i.e. we have the old problem back again).
> What
> I didn't know is that the presented queries came from a stored procedure
> the
> programmer had written. He added a default value for the @.SVERARJOURNAL
> parameter and voila, it works as desired.
> Thanks again. We've learned something today.
> Paul.
> "Dan Guzman" wrote:
>> Good point, Uri. Paul's initial script used local variables instead of
>> proc
>> parameters so I didn't go there. To clarify the issue for Paul, SQL
>> Server
>> 'sniffs' parameter values as follows:
>> 1) When stored procedure parameters are used directly in the WHERE
>> clause,
>> SQL Server uses the default parameters to generate the execution plan.
>> If
>> you don't specify default values, SQL Server uses NULL which is often the
>> unusual case and can result in a poor plan for non-trivial queries.
>> Specifying typical values as parameter defaults instead of NULL will
>> generate a plan that is good for data of similar cardinality.
>> 2) When local variables are used, SQL Server estimates what values may
>> be
>> based on table index and column statistics. This is often better than #1
>> but may perform poorly when unusual values are specified.
>> 3) With constants, the actual values are known so the best execution
>> plan
>> is generated.
>> SQL server 2005 introduces features such as statement-level recompilation
>> that help address this issue.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>> >I addition to Dan's great explanation , you can declare a local variable
>> >to
>> >perevent parameter sniffing
>> >
>> > CREATE PROC dbo_select_BUCHZEILE
>> > @.SVERARJOURNAL NUMERIC(21,0) ,
>> > @.SFIRMA NUMERIC(21,0)
>> > AS
>> >
>> > DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
>> > DECLARE @.Local_SFIRMA NUMERIC(21,0)
>> >
>> > SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
>> > SET @.Local_SFIRMA =@.SFIRMA
>> >
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
>> > AND SFIRMA = @.Local_SFIRMA
>> > AND CEINZSTATUS = '40'
>> >> GO
>> >
>> >
>> >
>> >
>> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> > news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>> >> If the query is in a stored proc, try setting the stored procedure
>> >> parameters default values to the ones used for the 'good' plan. The
>> >> optimizer will use those values for plan generation instead guessing
>> >> based on distribution stats. If that doesn't work, post your table
>> >> DDL,
>> >> including indexes.
>> >>
>> >> CREATE PROC dbo_select_BUCHZEILE
>> >> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
>> >> @.SFIRMA NUMERIC(21,0) = 15884.0
>> >> AS
>> >> SELECT SBUCHZEILE,XBUCHTEXT
>> >> FROM BUCHZEILE
>> >> WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> >> AND SFIRMA = @.SFIRMA
>> >> AND CEINZSTATUS = '40'
>> >> GO
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> >> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> >> Dan,
>> >>
>> >> Is there a way to trick the Optimizer. We've already tried the
>> >> following:
>> >> - Added an index for SVERARJOURNAL. No result.
>> >> - Tried changing the order of the WHERE Clause. No result.
>> >> - Use the INDEX HINT. Good result, for us bad solution.
>> >>
>> >> Paul.
>> >>
>> >>
>> >> "Dan Guzman" wrote:
>> >>
>> >> There are a couple of possibilities. First, make sure the actual
>> >> column
>> >> data types match the variable declarations. If these are different
>> >> data
>> >> types, the expressions will not be sargable if the column value
>> >> needs
>> >> to be
>> >> converted to the variable declaration (numeric).
>> >>
>> >> Also, when the optimizer generates the plan for the first query, the
>> >> actual
>> >> values are known so the best plan can be generated. However, the
>> >> values of
>> >> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the
>> >> optimizer
>> >> uses
>> >> distribution statistics of existing data to guess what actual values
>> >> might
>> >> be provided. This might yield a different plan.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > Today one of our developers came to me with a question which
>> >> > totaly
>> >> > flabbergasted me. He had 2 little examples (see below) which lead
>> >> > to
>> >> > totally
>> >> > different execution plans and I couldn't tell him why. Maybe
>> >> > anyone
>> >> > in
>> >> > this
>> >> > newsgroups know the problem and can tell me what's going on.
>> >> >
>> >> > The first SELECT runs less than one second because it uses an
>> >> > Index
>> >> > for
>> >> > the
>> >> > index seek. The second SELECT however stunned me completetly
>> >> > because
>> >> > it
>> >> > does
>> >> > an index scan on the Primary Key resulting in endless waiting.
>> >> >
>> >> > The question is: Why does the Optimizer take the wrong index for
>> >> > the
>> >> > second
>> >> > SELECT?
>> >> >
>> >> > Paul Sinnema
>> >> >
>> >> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> >> > @.SFIRMA NUMERIC(21,0)
>> >> >
>> >> > -- TIME : 0 SEC
>> >> > SELECT SBUCHZEILE, XBUCHTEXT
>> >> > FROM BUCHZEILE
>> >> > WHERE SVERARJOURNAL = 15884
>> >> > AND SFIRMA = 0
>> >> > AND CEINZSTATUS = '40'
>> >> >
>> >> > SET @.SFIRMA = 0
>> >> > SET @.SVERARJOURNAL = 15884
>> >> >
>> >> > -- RUNS INDEFENITLY
>> >> > SELECT SBUCHZEILE,XBUCHTEXT
>> >> > FROM BUCHZEILE
>> >> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> >> > AND SFIRMA = @.SFIRMA
>> >> > AND CEINZSTATUS = '40'
>> >> >
>> >>
>> >>
>> >
>> >

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.

Parent Package Variable visibility when designing packages

I've created an SSIS package that uses parent package variables at several steps in the data flow. However, those parent package variables are only visible during runtime, making debugging the package practically impossible. Let me give you a simplified example:

In the parent package, I have a string variable named "sqlLookup" that looks like this:

"SELECT * from tblTest WHERE city = " + @.city

Also in the parent package is a variable named "city" which I can set to the name of the city that I want to query on. This dynamically updates the sqlLookup variable, which is being evaluated as an expression.

In the child package, I have an OLE DB Source control which is using the "sqlLookup" variable for its query. I have set up my parent package variable configuration, and it works when I run the package.

The problem is this...when I open the child package, I get an error on the OLE DB Source control using the parent package variable, "Command text was not set for the command object". Presumably, this is because the parent package variable is only available during run-time, and not at design time. And, if metadata changes (which it has), I can't get into the OLE DB Source control to edit it, because it throws the error.

So, my question is this: are there any workarounds for this problem? Is there a better way to do this? This seems like such an obvious problem that I'm wondering if I either missed a step somewhere, or if I'm just doing it the wrong way.

Thanks!

Try setting DelayValidation=TRUE on the data-flow task in the child package.

If that fails, set Work Offline on the SSIS menu.

-Jamie

|||

Jamie,

Thanks for the reply, however I'm not having any luck with either of your suggestions. I created a new Project from scratch to simplify the problem.

My Project contains two files, parent.dtsx and child.dtsx.

parent.dtsx contains only an Execute Package task pointing to child.dtsx, and one string variable called sqlGetNames, which is set to " SELECT * from tblCustomers WHERE city = 'Denver' "

child.dtsx contains a single Data Flow, with one OLE DB Source control. It contains one variable, also called "sqlGetNames". I have enabled Package Configurations, and created a Parent Package Variable that should get the value of sqlGetNames from the parent.

However, when I go into my OLE DB Source control in the child package, I get the following error when I tell it to use sqlGetNames as the variable for the SQL command:

Error at Data Flow Task [OLE DB Source[1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

I get this error whether or not I have DelayValidation set to False. If I set SSIS to work offline, then I can't set up the OLE DB Source control because there is no connection.

Any other ideas?

|||It may work if you set the child package's variable to have an initial value that works at design time, ie child.sqlGetNames = "SELECT * from tblCustomers"|||

Thanks. That's not exactly what I was hoping for, but it's a useful workaround. I appreciate the suggestion.

Parent Package Variable issue?

I have noticed an issue with parent package variables. I have a package with multiple parent package variables defined, call them X, Y, and Z. I also have a parent package that calls this other package. The parent package has variable definitions for X and Z. It seems that the value for X will be passed along, and Y will give a warning since there is no variable of that name in the parent. The issue is that Z will not be passed along. It seems like the parent package configuration process stops after it encounters one missing variable.

Is this a know issue? Is it by design?Yep, I see the same thing.|||Good to know I'm not crazy! Thanks.|||Look for a fix in SP3, perhaps.

There is already a bug filed for this issue.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=174510

Parent Package variable assignment issue in Child Package.

We

have one main package from which 7 other child packages are called. We are using

ParentPackage variables to assign values for variables and database connections.

While the values from ParentPackage variable get assigned to some of the

packages properly, to others it doesn’t assign the value.

For example:

Except for one of the packages the database connection string gets assigned

properly to all other packages.

Similarly, in another package one of the

variables doesn’t get assigned. In other packages it is assigned properly.

We

have checked all the other property values and they are exactly the same.

We cannot make any head or tail of this erratic behavior.

Please Help.

The only known issue I am aware of is that Package configurations based on parent package variables always occour last no matter its place in the configuration organizer.

This represent an issue when you use this method to configure a connection string that would be used by other configuration (when using SQL Server based package configurations). But this should not be the case when it is used to assign variable values, etc.

Make sure the spelling of the variable name in the child package is right; also enable package logging an see is there is any 'Warning' at the begining of the execution log. When a package configuration does not occur a warning is generated. If you are running the package via BIDS review the progress tab as it offers the same info than the logging.

|||Thanks,

The issue has got solved. There was the problem of Case of the variable names only and I also came to know by checking the warnings.

Thanks again.

Parent package configurations failing

Has anyone seen a problem where if you have several parent configurations setup and one fails to find the variable in the parent, gives a warning, and then does NOT load the rest of the parent configruations? I realize order matters in how your configurations are processed, but I wouldn't expect the rest of my configurations to not work simply because it could not find one parent variable.

The problem only seems to come up when I'm dealing with multiple parent configurations. If I'm loading a variable from a config file and then loading the same variable from a parent variable the process works fine. This way I can handle both cases, when I'm debugging it pulls from the config file, when it's running in production it pulls from the parent variable.

If what you're seeing is truly a cancellation of configuration loads, then it's a bug and should be reported as such.

Kirk Haselden
Author "SQL Server Integration Services"

Parse output from xp_cmdshell

I would like to write the output from (Exec master..xp_cmdshell 'dir
E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each line of
the output variable to see if (wildcard.BAK) exist. If (wildcard.BAK) exis
t
I append this code to my existing script.
Please help me create this script.
Thanks,
-- directory full backup listing
Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
Volume in drive E is NWORLBD01A Dumps
Volume Serial Number is DRV-73B6
NULL
Directory of E:\NewOrleans
NULL
05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
1 File(s) 168,968,064 bytes
0 Dir(s) 295,359,264 bytes free
NULLJoe K. wrote:
> I would like to write the output from (Exec master..xp_cmdshell 'dir
> E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each
> line of the output variable to see if (wildcard.BAK) exist. If
> (wildcard.BAK) exist I append this code to my existing script.
> Please help me create this script.
> Thanks,
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
Create Table #output (output varchar(1000))
insert into #output
Exec master..xp_cmdshell 'dir C:'
Select * from #output
drop table #output
David Gugick
Imceda Software
www.imceda.com|||Look at this example:
create table #ipconfig(line varchar(2000))
insert into #ipconfig
execute xp_cmdshell 'ipconfig.exe'
select line from #ipconfig
drop table #ipconfig
YOu can "parse" that table with
Select * form #ipconfig where line like '%Something%'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4D55228E-D4E2-4EA0-A533-1B4556C5232F@.microsoft.com...
> I would like to write the output from (Exec master..xp_cmdshell 'dir
> E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each line
> of
> the output variable to see if (wildcard.BAK) exist. If (wildcard.BAK)
> exist
> I append this code to my existing script.
> Please help me create this script.
> Thanks,
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
>|||Create temp table and change my path to you path and it will give you
directory returned data in a table then you extract the peaces you need.
Create TABLE #temp
(
data nvarchar (1000)
)
Insert #temp
Exec master..xp_cmdshell 'dir \\server\backup\sql\FULL_Backup\*(FULL)*
.BKP'
select * from #temp
"Jens Sü?meyer" wrote:

> Look at this example:
> create table #ipconfig(line varchar(2000))
> insert into #ipconfig
> execute xp_cmdshell 'ipconfig.exe'
> select line from #ipconfig
> drop table #ipconfig
>
> YOu can "parse" that table with
> Select * form #ipconfig where line like '%Something%'
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:4D55228E-D4E2-4EA0-A533-1B4556C5232F@.microsoft.com...
>
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
Yahya
Yahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>
|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User
defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...een">
> Can I create parameterized view, in other words like stored procedures whe
re
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parameters to SP ?

How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.You cannot pass a table variable as an input to the SP. Not sure about your logic but if it is unavoidable, one way is to populate a temp table and read from the SP and then delete it as soon as you exit from the called SP. This is one option. Sure there could be some better ones.

- CB

Originally posted by Decastod
How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.|||Thanks for your help.
Tired it in another way as you mentioned and it worked.