Problem with isnull. Need to substitute null if a var is null and compare it to null and return

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @.inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off? Thank you

set ansi_nulls off

go

declare

@.inFileName VARCHAR (100),

@.inFileSize INT,

@.Id int,

@.inlanguageid INT,

@.inFileVersion VARCHAR (100),

@.ExeState int

set @.inFileName = 'A0006337.EXE'

set @.inFileSize = 28796

set @.Id= 1

set @.inlanguageid =null

set @.inFileVersion =NULL

set @.ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @.inFileName

AND DR.FileSize =@.inFileSize

AND DR.FileVersion = isnull(@.inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@.inlanguageid,null)

AND DR.[ID]= @.ID

)

go

set ansi_nulls on

well actually you dont need to change the setting

if you're up to something like this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

|||

There is a slight problem with this. If the right side is null, it will evaluate to -1. If the left side is not null, it will evaluate to value stored in the table. It's VERY likely that the value in the table won't be -1. So the condition will be false. But, in actuality, it should be true, correct? Shouldn't it be like this?

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,isnull(DR.FileVersion,-1))

Thank you

|||

with this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

the ending equation would be

and (-1 = -1) which evaluates to true.

meaning null=null

remember that this equation resides in the "where clause" and not on the

select clause. if you want to have it returned you must

place a "case clause" in the select statement to evaluate this

nevertheless this clause must still exist in the where clause

to include the nulls

sql

Problem with INSERT Trigger

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.

Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.

In case I didn't describe it clearly:

Call storedproc1

storedproc1 inserts a record into table1

table1 has an INSERT trigger

expected behavior is: storedproc1 returns value1 and value2

actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2(timothy.alvis@.gmail.com) writes:

Quote:

Originally Posted by

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.
>
Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.
>
In case I didn't describe it clearly:
>
Call storedproc1
>
storedproc1 inserts a record into table1
>
table1 has an INSERT trigger
>
expected behavior is: storedproc1 returns value1 and value2
>
actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2

Returns? This needs some clarification. A stored procedure can return
data in three different ways:
o Result set
o Output parameters
o Return value. as in EXEC @.ret = some_sp

Which do you mean?

Overall, it would help if you posted the code of the procedure, so we know
what you are talking about. Please also include the output when run the
procedure in Query Analyzer.

--
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

problem with IIF statement

I've got this iif statement
=iif(Fields!Month.Value =13,"YTD",MonthName(Fields!Month.Value))
in a matrix header field that has the month numbers in it. it will
display ytd if i drop the month name function but with the monthname in
it throws an error. Also theres a warning that says
[rsRuntimeErrorInExpression] The Value expression for the textbox
'textbox47' contains an error: Argument 'Month' is not a valid
value.
any ideas on how to get it to display the month name and the YTD text?
Thanks for the help
MathiasI saw some other post as well, IIF evaluates both the truw and false
expression and then goes for comparison. so MonthName(13) will give error
since there is no 13. So reframe your conditions.
Amarnath.
"Mathias" wrote:
> I've got this iif statement
> =iif(Fields!Month.Value =13,"YTD",MonthName(Fields!Month.Value))
> in a matrix header field that has the month numbers in it. it will
> display ytd if i drop the month name function but with the monthname in
> it throws an error. Also theres a warning that says
> [rsRuntimeErrorInExpression] The Value expression for the textbox
> 'textbox47' contains an error: Argument 'Month' is not a valid
> value.
> any ideas on how to get it to display the month name and the YTD text?
> Thanks for the help
> Mathias
>|||so umm care to point out those posts or tell me something i don't
already know?
any hint as to how to reframe my condition's would be of great help.|||Mathias,
As far as posts go, just search for "IIF error" or "IIF doesn't work"
and you'll come up with tons of 'em.
My experience with this issue comes from trying to do divide by zero
error checking. For example, =IIF(exp2 = 0,0,exp1/exp2); SSRS
evaluates both T and F and blows up when exp2 = 0.
The only way I've found to work around is to create a custom code
function then use that function in your expression. For your situation
the function would be something like:
Public Function MonthValue (Exp1)
If Exp1 = 13 Then
MonthValue = "YTD"
Else MonthValue = MonthName(Exp1)
End If
End Function
Your expression would then be:
=code.MonthValue(Fields!Month.Value)
Good luck
toolman|||Thanks for the help. don't know why i never thought to look for iif
error. I'll give that custom code a shot and see what I come up with.
Thanks
Mathias
toolman wrote:
> Mathias,
> As far as posts go, just search for "IIF error" or "IIF doesn't work"
> and you'll come up with tons of 'em.
> My experience with this issue comes from trying to do divide by zero
> error checking. For example, =IIF(exp2 = 0,0,exp1/exp2); SSRS
> evaluates both T and F and blows up when exp2 = 0.
> The only way I've found to work around is to create a custom code
> function then use that function in your expression. For your situation
> the function would be something like:
> Public Function MonthValue (Exp1)
> If Exp1 = 13 Then
> MonthValue = "YTD"
> Else MonthValue = MonthName(Exp1)
> End If
> End Function
> Your expression would then be:
> =code.MonthValue(Fields!Month.Value)
> Good luck
> toolmansql

Problem with IIF and Like comparison

I am having a problem with the following expression

=IIf(Fields!client_short_name.Value Like "Capital% ", 1,0)

I am wanting to get a value of 1 if the field has any of the valid values that begin with Capital but it always returns 0. Any ideas?

You may have better luck with the Instr command.|||

Do you know what the proper syntax would be in this example? I'm struggling.

|||

Did you try to use LIKE "Capital" (without using %)

I don't know try that.

|||

I believe it would be

Code Snippet

=IIf(Instr(Fields!client_short_name.Value,"Capital") = 1,1,0)

Of course, this isn't exactly like your expression because the instr looks for the word anyware in the string. You could also use the left function

Code Snippet

=IIF(Left(Fields!client_short_name.Value,7) = "Capital",1,0)

problem with identity column value

Example:
create table test
(
id bigint identity(1000,1) NOT NULL,
x int
)
set IDENTITY_INSERT test on;
insert into test (id,x) values (10,1);
set IDENTITY_INSERT test off;
-- identity value is OK = 1000, but:
set IDENTITY_INSERT test on;
insert into test (id,x) values (2000,1);
set IDENTITY_INSERT test off;
-- identity value isn't OK = 2000, but I need identity value = 1000
-- I know solution, but it is to slow:
DECLARE @.id_seq bigint;
SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
set IDENTITY_INSERT test on;
insert into test (id, x) values (2000, 1);
set IDENTITY_INSERT test off;
DBCC CHECKIDENT ('test', RESEED, @.id_seq);
-- do you know some faster and better solution? Thankssilber wrote:
> Example:
> create table test
> (
> id bigint identity(1000,1) NOT NULL,
> x int
> )
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (10,1);
> set IDENTITY_INSERT test off;
> -- identity value is OK = 1000, but:
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (2000,1);
> set IDENTITY_INSERT test off;
> -- identity value isn't OK = 2000, but I need identity value = 1000
> -- I know solution, but it is to slow:
> DECLARE @.id_seq bigint;
> SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
> set IDENTITY_INSERT test on;
> insert into test (id, x) values (2000, 1);
> set IDENTITY_INSERT test off;
> DBCC CHECKIDENT ('test', RESEED, @.id_seq);
> -- do you know some faster and better solution? Thanks
I don't quite understand the problem. Apparently you want to reset the
current IDENTITY value after inserting some data with IDENTITY_INSERT
on. The only reason I can imagine performance would be in issue here is
if you had to issue DBCC CHECKIDENT on a frequent basis during
user-transactions. But if you had to do that on a frequent basis then
I'd say you would be better off not having the column as an IDENTITY at
all.
Alternatively, you could use only negatives for the IDENTITY_INSERT
values and use positives for the incrementing IDENTITY value. That way
the increment won't be affected.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Problem with IDENT_CURRENT

I'm having a problem returning a value with IDENT_CURRENT:
When I run it through the Analyzer it's OK, when I run it through my ASP
code, I get a casting error on my web application. (Depending on the sql
transaction method I use, I sometimes get a -1 value (when using
ExecuteNon query) instead of the casting error(when using execute
Scalar))
here's the code:
CREATE PROCEDURE spSelectOrderID
AS SELECT IDENT_CURRENT('tbOrder') + 1
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!"Patrick Delifer" <deliferp@.videotron.ca> wrote in message
news:e5fCqMDJFHA.2396@.TK2MSFTNGP12.phx.gbl...
> I'm having a problem returning a value with IDENT_CURRENT:
> When I run it through the Analyzer it's OK, when I run it through my ASP
> code, I get a casting error on my web application. (Depending on the sql
> transaction method I use, I sometimes get a -1 value (when using
> ExecuteNon query) instead of the casting error(when using execute
> Scalar))
> here's the code:
> CREATE PROCEDURE spSelectOrderID
> AS SELECT IDENT_CURRENT('tbOrder') + 1
> GO
>
Back up. You appear to be under the impression that this is a viable method
to prefetch an IDENTITY value for an upcoming insert into tbOrder. It's
not.
David

Problem with Historical Prediction in Sales Forecast Model

Hi,

I have built a time series model to forecast sales value

I have data from jan 2004 to jan 2006 and the sales value is

at a day level in my database. But I am aggregating it to month level in the

DSV of the mining model.

I am required to make only historical predictions using the

above model starting form jan 2004 to jan 2006 for every month.

I have set Historical_Model_Count

and Historical_Model_Gap parameter

values to 24 and 10 respectively, and trying to predict for the past few months

(PredictTImeseries(SalesValue,-1,1))

But its throwing me the following error

Error(Data Mining): A time series

prediction was requested with a start time further in the past than the

internal models of the mining model, Sales Forecast, specified in the

HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process

In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values

are

I am not able to figure our why this problem is happening?

What should the parameter values for the above scenario?

It would also be helpful if I can get an explanation on how

these two parameters affect the historical predictions. I kind of understand

that these two parameters are important for historical predictions but don’t

know why or how.

You want the values of _Count to be 24 and _Gap to be 1. The _Count param says "make this many models", the "_Gap" param says "leave this many time slices between models". Your original parameter set was making models to 240 months (20 years!) in the past.

Note that the _Gap parameter is to be set such that you get a good idea of how the model will predict for the range that you need to predict for. For example, setting it to 1 gives you an indication of how well the model will predict the next step. Setting the parameters to _Count = 4 and _Gap=6 gives an idea of how the model will predict 6 steps into the future.

|||

Thanks Jamie,

I have few more clarification regarding time series.

Firstly

In my model the month level product sales value represented across 1^st day of every month.So that the key time column is of datetime datatype containing a sequence of dates representing the 1^st day of every month of the year.

Eg: 2006-01-01, 2006-02-01………. etc. all in (yy-mm-dd) format

But when I make prediction for next five months, though it makes monthly predictions the date part for the months are random whereas I expect the date part to be 1^st of every month.What is the reason for this and how can I overcome it.

Secondly

Predicted sales values for some time period are negative though I do not have any negative value in the training data. What is the reason for this and how can I rectify it?

Thirdly

In one of your earlier posts you had said that the time series algorithm does not have any built in time intelligence but uses the key time column as a time sequence stamp. So If have to make predictions for a particular time period where the time slice for each time period is 25 days or 50 days etc, then I understand that the input data used to train the model should be in the same time sequence.

Or

Can I specify the span of the time period according to which the prediction needs to be made?

Basically how can I use the same time series model to make monthly, yearly, quarterly, daily or predictions or for custom time period like I have mentioned above.

|||Additional questions answered in other thread

Problem with Group By and Sum

I am trying to get a sum of the dollar amount for each term for each
customer. Unfortunately, the same value for sum is returned, so I thinkt
hat I have my query wrong. My query looks like this:
SELECT sales.name, sales.terms, t_order_term.order_term_description,
SUM(sales.prodamt) AS TheSum
FROM sales CROSS JOIN dbo.t_order_term
WHERE (NOT (dbo.sales.terms = N'12 , 24, 35, 39, 50, 57, 62'))
GROUP BY dbosales.name, dbo.t_order_term.order_term_description,
dbo.sales.terms
ORDER BY dbo.sales.name
My results look like this:
Customer1 30 988787.23
Customer1 60 988787.23
Customer2 30 78234.78
Customer2 60 78234.78
What am I doing wrong?
Thanks.
JoshuaDarn inner join problems...
I figured it out. Thanks.
Joshua
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:uEhyPS8LFHA.3064@.TK2MSFTNGP12.phx.gbl...
> I am trying to get a sum of the dollar amount for each term for each
> customer. Unfortunately, the same value for sum is returned, so I thinkt
> hat I have my query wrong. My query looks like this:
> SELECT sales.name, sales.terms, t_order_term.order_term_description,
> SUM(sales.prodamt) AS TheSum
> FROM sales CROSS JOIN dbo.t_order_term
> WHERE (NOT (dbo.sales.terms = N'12 , 24, 35, 39, 50, 57, 62'))
> GROUP BY dbosales.name, dbo.t_order_term.order_term_description,
> dbo.sales.terms
> ORDER BY dbo.sales.name
> My results look like this:
> Customer1 30 988787.23
> Customer1 60 988787.23
> Customer2 30 78234.78
> Customer2 60 78234.78
> What am I doing wrong?
> Thanks.
> Joshua
>
>|||Perhaps something more like this?
SELECT S1.name, S1.terms, O1.order_term_descri=ADption,
SUM(S1.prod_amt) AS prod_amt_total
FROM Sales AS S1, OrderTerms AS O1
WHERE S1.terms NOT IN (12, 24, 35, 39, 50, 57, 62)
GROUP BY S1.name, S1.terms, O1.order_term_de=ADscription ;
Surely you do not have a CSV list in a column!

Problem with GETDATE in SQL Stored Procedure

hi all,

i am using a stored procedure where i am using GETDATE to give default value to a field ( @.effectivedate as Datetime = GETDATE)

i am making the SP call in my code .

Dim cmd As System.Data.Common.DbCommand = db.GetStoredProcCommand("sel_TemplateData")

db.AddInParameter(cmd, "@.TemplateID", DbType.Int32, Convert.ToInt32(_templateId))

db.AddInParameter(cmd, "@.State", DbType.String, mrmParams("State").ToString())

db.AddInParameter(cmd, "@.SectionCode", DbType.String, mrmParams("SectionCode").ToString())

Dim ds As DataSet = db.ExecuteDataSet(cmd)

should i need to pass this as a parameter along with other parameter as below ? will it be defaultly taken.

when i try to add this parameter an error is thrown " cannot convert string to datetime .

is the syntax for GETDATE correct.

thanks in Advance

Since Getdate() is the non-deterministic function you can’t assign this function as your default value of the SP parameter.

Try to use the following approach to pick up the current date when there is no explicit value passed for datetime valued parameters.

C

Code Snippet

raete Procedure TestDateParam

(

@.Date as datetime= '1900-01-01'

)

as

Begin

Set @.Date = Case When @.Date = '1900-01-01' Then Getdate() Else @.Date End;

Select @.Date Date

End

Go

Exec TestDateParam --it will use the current date

Exec TestDateParam '1/1/2007' --it will use the passed date value

|||

Could you instead use NULL as your default value? If that is possible then you would not need the set statement but could use the ISNULL or COALESCE function -- something like:

ISNULL(@.Date, getdate())

or

COALESCE(@.Date, getdate())

within the body of your stored procedure

|||

If you are wanting to make @.EffectiveDate an optional parameter for the procedure, I suggest using Kent's suggestion of setting the optional value equal to NULL. It is cleaner than presuming a redefined date means none supplied...

|||In addition to Kent and Arnie you could consider using the syntax:

Create procedure someproc
(
@.SomeDate DATETIME = NULL
)
AS

SELECT
(...Something)
WHERE YourColumn = @.SomeDate OR @.SomeDate IS NULL

But this highly depends on your needs in the logic of the stored procedure.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem with Format() Function in expression

Hi,
I have a textbox with an expression to format integer data:
="HH: " + format(Fields!ANZHH.Value,"#.###.") + ", EW: " +
format(Fields!ANZEW.Value,"#.###.")
I want that integer values appear as: HH: 1.000, EW: 3.890
("." = group digit )
However the custom format style that I use does not return the expected
result, instead RS shows HH: 1000, EW: 3890
Any ideas how to troubleshoot?
--
Thanks in advance
BodoI finally got it solved:
1) modified expression to:
="HH: " + format(Fields!ANZHH.Value,"#,###.") + ", EW: " +
format(Fields!ANZEW.Value,"#,###.")
2) set report property Language to Default.
As a result values are formated in the culture specified in IE language
settings.
Bodo|||I'm guessing I'm not the first to ask this, but haven't found anything
either here or via googling around.
Per the subject, some of our database fields have html code in them that we
would like to render inside the report (using the table control) as actual
html instead of just code, is this at all possible with some toggles or
perhaps expression injection? Its mostly stuff like fonts colors/sizes/etc,
nothing drastic, but of course its showing up as a bunch of
<this><that>...etc rathern than actual html.
Please let me know either way, or perhaps point me to an example somewhere?
TIA.
andy|||Apologies for the above :(
"Andrzej E. Raczynski" <andy@.aea13.org> wrote in message
news:9CA7AD98-7309-4C21-B000-46E7CDFD429C@.microsoft.com...
> I'm guessing I'm not the first to ask this, but haven't found anything
> either here or via googling around.
> Per the subject, some of our database fields have html code in them that
> we would like to render inside the report (using the table control) as
> actual html instead of just code, is this at all possible with some
> toggles or perhaps expression injection? Its mostly stuff like fonts
> colors/sizes/etc, nothing drastic, but of course its showing up as a bunch
> of <this><that>...etc rathern than actual html.
> Please let me know either way, or perhaps point me to an example
> somewhere?
> TIA.
> andy

problem with float data type

Hi all,
I have declared a field with datatype as float.
When I enter value with two precision it chooses to round off to lower value and insert into the database.

I am losing precision in this case.

I want to insert 4.56. It inserts the way shown and hence all my further calculations go haywire.

Is SQL server designed to store float like this or Am I doing soemting wrong ?

Please advise...

4.56 (Inserted)

4.5599999999999996 (Stored)Generally you have to convert the value that is being inserted into the database, or you can convert it when it is pulled from the DB. I prefer to do it beforehand.

If you do something like this it should work:

Dim Val1 as Int16
Val1 = Convert.ToInt16(TextBox1.text)

Problem with Failure Constraint

Hi,

As part of my SSIS package I have a script task, that set its result based on the value of a package variable. From this task I have two precedence constraints, a sucess constrain and failure constraint that lead to two different tasks.

When the script task ends with a success result, the task that is connected by the success constraint is initiated, but when the script task ends with a failure result, it is marked with red color and the execution of the whole package stops (the next task that is connected by the failure constraint is not initiated).

All tasks are assigned with False to the properties: FailPackageOnFailure & FailParentOnFailure

Please assist.

Hmmmm....strange!

Can you post the contents of the .dtsx file up here so we can repro?

If you could take out all external references (e.g. connection managers, configurations, etc...) it'd be a big help!

Thanks

Jamie

|||Is there a way to attach the file?|||Have you increased MaxErrorCount property? (I don't remember the details of interactions between it and other two properties, but try increasing it).|||

Thanks for you help.

I believe I solved the problem: the task, that was connected to the script task by the failure constraint, could be executed also as an error handling task of another task, therefore all faliure constraints should have LogicalAnd property set to False.

Problem with expression window in derived column

Hi,

i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.

let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.

Can any one asssit in this regard how to achive it? and is it possible to use IF and CASE statement in this dervived column tranformation?

Sreenivas

Select the Add New column option, call it ColB or whatever, then use an expression like this -

ColA == 0 ? "GOOD" : "BAD"

This uses the conditional operator, as documented in Books Online - http://msdn2.microsoft.com/en-us/library/d38e6890-7338-4ce0-a837-2dbb41823a37(SQL.90).aspx

Logicaly it reads like this -

If ColA Equals 0 Then

Return "GOOD"

Else

Return "BAD"

End If

Problem with Expression for a added field in datasource.

I added a new field to my datasource called "TotalCostAssum". The expression for the datasource is as follows:

Iif(Previous(Fields!PNumber.Value) = Nothing Or Previous(Fields!PNumber.Value) <> Fields!PNumber.Value, Fields!TotalCost, 0)

But this expression gave me trouble. I even couldn't go to the Preview page because every time when I clicked the Preview button, the Visual Studio.NET was shut down by asking me if I need to send error report to Microsoft. When I got rid of the above expression, everything is fine. I was wondering if that's because the word "Previous" is not allowed here. But I have to access the previous data row to determine the value here. I was bothered by this the whole morning and couldn't get any hint by searching on the internet. Any anybody help me out? Thanks in advance.

Mistake. The above expression is for the field that I manually added to the dataset.

Problem with dynamic SQL syntax

What exactly do you mean by an empty field? Generally if
your field allows null and you don't set a value into
that field for a row, the field is set to NULL and you
can test for this by using "type IS NULL" in your where
clause.
"<>" is the operator for not equals and it is not a
singleton operator, it is a comparison operator. You
need something on both sides of the "<>" to compare to
each other.
For instance, if you are looking for rows where the type
field is not equal to a space, you could try "type <> ' '"
I hope that this helps.
Matthew Bando
bandoM@.CSCTechnologies-dot-com

>--Original Message--
>I' m having a problem with the syntax when I'm trying to
run a dynamic SQL
>statement.
>The code -
>set @.sql = 'SELECT *
INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
le_name+' Where
>Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
>exec sp_executesql @.sql
>- gives me the error "Incorrect syntax near '>'." The
purpose of the last <>
>is to find the records where this field is empty (that's
my understanding of
>it...). The basic structure of the query is from a DTS
Transform task, but
>I'm trying to "convert" this whole task to TSql.
>I've tried all sorts of different combinations of <>
and ' but it still
>won't do it. If I just prins the @.sql var. it looks fine.
>The CompanyNo field is int(4) and the Type field is
varchar(30).
>Is there any other ways to check for an empty varchar
field of can some of
>you guide me to what it is I'm missing in my "set
@.sql...." statement?
>Best Regards
>Steen
>
>.
>Hi
Sorry if I wasn't very clear. I assume that the purpose is to check for an
empty field. The "original" code that's being used in the DTS Transform task
is "...AND CompanyNo is not NULL and Type <>'' ". It's not me that have
written the transform task, but I assume that this last piece checks if
there're any empty fields. It might be my understanding of it that's wrong,
but then I'd be happy to hear about it.
This SQL statement runs fine in the DST task and also when I run it in Query
analyser using fixed values, but when I do it with variables/dynamic SQL it
seems to fail and not accept this last bit.
Regards
Steen
"Matthew Bando" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:071c01c46e4c$74405700$a501280a@.phx.gbl...[vbcol=seagreen]
> What exactly do you mean by an empty field? Generally if
> your field allows null and you don't set a value into
> that field for a row, the field is set to NULL and you
> can test for this by using "type IS NULL" in your where
> clause.
> "<>" is the operator for not equals and it is not a
> singleton operator, it is a comparison operator. You
> need something on both sides of the "<>" to compare to
> each other.
> For instance, if you are looking for rows where the type
> field is not equal to a space, you could try "type <> ' '"
> I hope that this helps.
> Matthew Bando
> bandoM@.CSCTechnologies-dot-com
>
> run a dynamic SQL
> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
> le_name+' Where
> purpose of the last <>
> my understanding of
> Transform task, but
> and ' but it still
> varchar(30).
> field of can some of
> @.sql...." statement?|||Sorry. It was the missing second single quote I was
referring to as missing.
Aaron is correct. You need to repeat the single quotes
since they are inside of a quoted expression.

>--Original Message--
>Hi
>Sorry if I wasn't very clear. I assume that the purpose
is to check for an
>empty field. The "original" code that's being used in
the DTS Transform task
>is "...AND CompanyNo is not NULL and Type <>'' ". It's
not me that have
>written the transform task, but I assume that this last
piece checks if
>there're any empty fields. It might be my understanding
of it that's wrong,
>but then I'd be happy to hear about it.
>This SQL statement runs fine in the DST task and also
when I run it in Query
>analyser using fixed values, but when I do it with
variables/dynamic SQL it
>seems to fail and not accept this last bit.
>Regards
>Steen
>"Matthew Bando" <anonymous@.discussions.microsoft.com>
skrev i en meddelelse
>news:071c01c46e4c$74405700$a501280a@.phx.gbl...
if[vbcol=seagreen]
type[vbcol=seagreen]
<> ' '"[vbcol=seagreen]
to[vbcol=seagreen]
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab[vbcol=seagreen]
(that's[vbcol=seagreen]
fine.[vbcol=seagreen]
>
>.
>

Problem with Dynamic SQL !

Hi friends,

I have a procedure with an input parameter & output parameter. The input parameter value is a table name which has an identity column. The procedure will set the next value of the identity column to the output parameter. I stuck with the dynamic sql. Here it goes...

---------------------------------------
CREATE TABLE seqtest(nextVal NUMERIC(38) IDENTITY(1000000,1),dummyCol TINYINT);

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
EXECUTE( 'SELECT '+ @.nv +' = id from ' + @.seqName )
SET @.nextVal = @.nv
END

---------------------------------------
after i created the table & procedure, i executed the below code:
---------------------------------------
DECLARE @.nextVal1 int
EXECUTE NextVal 'seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1
---------------------------------------

but it says

Msg 170, Level 15, State 1, Server SWISSQL-WIN2K, Line 1
Line 1: Incorrect syntax near '='.
(return status = 0)

Can anyone point out where i went wrong?

JakeDECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||Hi Eniqma,

It didn't solve my prob. same error...
The problem is in the select statement
EXECUTE( 'SELECT '+ @.nv +' = nextVal from ' + @.seqName )
It says Incorrect syntax near '='.

any idea?

Jake|||Oops ... i forgot ... you cannot create a dynamic string inside an execute statement

You will have to do something like

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )
select @.query ='SELECT '+ @.nv +' = id from ' + @.seqName
EXECUTE(@.query )
SET @.nextVal = @.nv
END|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= @.@.identity

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= scope_identity()

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1



You should really be using scope_identity()

And from what I think what you are trying to achieve , it would not matter even if you used a identity column in your original table ...|||Hi eniqma, when the procedure is executed, it will say

Msg 245, Level 16, State 1, Server SWISSQL-WIN2K, Line 9
Syntax error converting the varchar value 'SELECT ' to a column of data type int.

as int is concatenated with string.

@.@.IDENTITY approach guides me to the solution. but i didn't use @.@.IDENTITY as it returns the last identity value generated for any table in the current session, across all scopes.
I used IDENT_CURRENT('table_name') as it returns the last identity value generated for a specific table in any session and any scope.

so here is the final procedure

---------------------------------------
alter procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
SET @.nextVal = IDENT_CURRENT(@.seqName)
END
---------------------------------------

thanks eniqma & harshal for your time & help.

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake

I think u have already answered the question.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

since the select and insert are not in the scope i think it wont work.|||Hmm ... you are right harshal ... never thought about that

Jake .. mind clarifying why you are going through all this when you could have done with a indentity column in the table for which you are generating a sequence ...|||i thought the dynamic sql execution would happen in the current scope. it puzzles me... so what actually happens is dynamic sql execution happens in a different scope than where it is called. may be i have to understand the execute statement further...

thanks for the clarification, harshal.

Jake|||oh! sorry, i missed that... i am trying to automate the SQL stored procedures conversion from Oracle to SQL Server. as you know Oracle has sequence & SQL Server doesn't.. That's why i trying to simulate sequence in SQL Server...
Thanks for your interest.

Jake|||Oh ... so thats what you are trying to do ...

Talking about scope ... its the same in sql as in other languages ...
If you called a stored procedure B inside a stored procedure A then the variables declared in sproc B get out of scope as soon as it returns control to sproc A. Similar with EXEC ... think of it as a stored procedure which executes what ever is passed to it and returning the result.|||now i understand, enigma... glad to see your reply.

Thanks,
Jake|||Without reading this little lot too deeply, the answer to the original question is:
1: Look at the spaces before and after you + signs '+ @.n +' needs to be:
' + @.n + '
2: You have to explicilty convert the int variable to a string (sorry, thats the vb in me coming out) varchar before you can add it to one.

E.G. 'I am a varchar ' + CAST(@.IntVariable AS Varchar(250)) + ' The rest of the varchar string'

Otherwise you get the converting int to varchar error.

Have fun
Best regards
Steve

P.S. Don't forget the spaces when breaking strings and inserting variables (before and after). Use Print CAST(@.SqlString as Varchar(250)) to check your Sequel statement for errors.

Problem with dynamic query and statement IN

Hi, try to execute this:

"where a12.year_id = " & Parameters!years.Value &
" and a12.month_of_year in ( " & Parameters!months.Value &" ) " &

but when I run the report raise an error
someone kwon if it's possible to use the IN statement within a dynamic query?

I've setting The parameter month as multivalue

thanksI don′t know where you enter that query, but if you use the query in the command text of the report designer you can simply use the WHERE SomeColumn IN(@.ParameterName)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I enter thsi query in the Edit Expression because I need to use the IIF function!
Within the Edit Expression the @.someVariable is an identifier Unknow, you must use Parametres!someVariable.value

If I use the variable without multivalue option , it works right
"where table.fied =" & Parametres!someVariable.value

If I use the variable with multivalue option , it don't work right

"where table.fied IN ( " & Parametres!someVariable.value & ")"

tanks|||You can use dynamic queries as expressions and use a "IN" statement. The problem, or "bug", is that with dynamic queries the dataset may not get refreshed when something is changed. You can use the field value (Parameters!Some_param.Value) or the query syntax (@.Some_Param).

The way I've worked around this is to actually drop in a straight query with my parameters, refresh the dataset, then run the report for good measure. Once I know it runs I then put in my dynamic query expression and all seems to work.

Try this... and I hope it helps.|||thanks
Now it works
abc_abc

Problem with Dependencies and Default Values

Dear Anyone,

Im having trouble with RS2005 with regards to dependencies of report parameters and the default value. Below is my scenario:

1st parameter:

List of Letters from A to Z

Multiple Select

2nd Parameter:

List of Names starting with the last name

Uses 1 data set for list of values and default values

Multiple Select

Steps:

Select 2 letters from the 1st parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that all values are selected.

Select another letter from the first parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that not all is selected despite the fact that the data set being used to populate the report parameter is the same as with the default values

Is the behavior of the report parameters really like this? If so, how can I make it in a way that whenever I select a new letter from the first parameter and when the 2nd parameter gets refreshed because of the depencies, all values will still be selected despite the number of times I add more values to the first parameter?

Thanks,

Joseph

Is there a resolution to this issue? How do we overcome this behavior? I am running into the same issue in my reports.

apex|||Bumping up this thread. Any resolution to this problem will be very helpful.

Problem with Dependencies and Default Values

Dear Anyone,

Im having trouble with RS2005 with regards to dependencies of report parameters and the default value. Below is my scenario:

1st parameter:

List of Letters from A to Z

Multiple Select

2nd Parameter:

List of Names starting with the last name

Uses 1 data set for list of values and default values

Multiple Select

Steps:

Select 2 letters from the 1st parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that all values are selected.

Select another letter from the first parameter. This action would refresh the list of values of the 2nd parameter.

Inspect the 2nd parameter. You will notice that not all is selected despite the fact that the data set being used to populate the report parameter is the same as with the default values

Is the behavior of the report parameters really like this? If so, how can I make it in a way that whenever I select a new letter from the first parameter and when the 2nd parameter gets refreshed because of the depencies, all values will still be selected despite the number of times I add more values to the first parameter?

Thanks,

Joseph

Is there a resolution to this issue? How do we overcome this behavior? I am running into the same issue in my reports.

apex|||Bumping up this thread. Any resolution to this problem will be very helpful.

problem with DEFAULT (getdate())

Hi all

I create table and set default value detdate(). But after insert record date display ‘1900-01-01 00:00:00’.

Example this,

CREATE TABLE [tblTemp1] (

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 values(0)

select ItemUserDate from dbo.tblTemp1 =‘1900-01-01 00:00:00’

select getdate() =’2006-07-20 15:53:27.820’ I need this answer

Hi,

try this:

CREATE TABLE [tblTemp1] (

part1 char,

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 part1, values('a')

And then do your select. You'll recieve the correct answer.

You have you result because you're putting 0 in your default column, so default has no effect anymore...

Greeting.

|||

Hi

To make Insert work for the table structure with a single datetime column you can use Default VALUES option: INSERT INTO dbo.tblTemp1 DEFAULT VALUES

|||

thankx Stefan Haeck

|||thanks NB2006