September 27, 2011 at 2:46 am
Ninja's_RGR'us (9/26/2011)
pwalter83 (9/26/2011)
Ninja's_RGR'us (9/23/2011)
What's the value you have in that variable?@Commoditylike
The value comes from a column called Commodity.
I meant when you run the query and it fails to return anything.
Oh sorry about that....I have tried with all three:
1. As blank from the drop down list
2. As NULL from the drop down list
3. left it as blank (empty)
September 28, 2011 at 2:25 pm
pwalter83 (9/27/2011)
Ninja's_RGR'us (9/26/2011)
pwalter83 (9/26/2011)
Ninja's_RGR'us (9/23/2011)
What's the value you have in that variable?@Commoditylike
The value comes from a column called Commodity.
I meant when you run the query and it fails to return anything.
Oh sorry about that....I have tried with all three:
1. As blank from the drop down list
2. As NULL from the drop down list
3. left it as blank (empty)
Is it possible that the drop down is converting NULL to an empty string? I would put some code at the top to make sure I knew that @Commoditylike actually contains either a proper value or a NULL. Something like:
SET @Commoditylike = NULLIF(@Commoditylike, '')
I've had trouble before assuming I was getting a NULL from the front end when it was converting the null to an empty string.
Todd Fifield
September 28, 2011 at 2:29 pm
This is simple detective work. See what SSRS is REALLY sending over to sql server and match the where accordingly.
I'm sure you can handle it from there!
September 29, 2011 at 3:39 am
tfifield (9/28/2011)
pwalter83 (9/27/2011)
Ninja's_RGR'us (9/26/2011)
pwalter83 (9/26/2011)
Ninja's_RGR'us (9/23/2011)
What's the value you have in that variable?@Commoditylike
The value comes from a column called Commodity.
I meant when you run the query and it fails to return anything.
Oh sorry about that....I have tried with all three:
1. As blank from the drop down list
2. As NULL from the drop down list
3. left it as blank (empty)
Is it possible that the drop down is converting NULL to an empty string? I would put some code at the top to make sure I knew that @Commoditylike actually contains either a proper value or a NULL. Something like:
SET @Commoditylike = NULLIF(@Commoditylike, '')
I've had trouble before assuming I was getting a NULL from the front end when it was converting the null to an empty string.
Todd Fifield
I have tried what you have mentioned but it does'nt help. On top of that I have tried every possible scenario but I am still lost. Am at wits end now....dont know whats going on ?
September 29, 2011 at 9:53 am
Something is going on that you don't know about. When something is going on that doesn't make sense, it's time to examine your basic assumptions.
You can fire up the profiler as Remi suggested. You can create a dump table that corresponds to the input variables and insert all of the passed variables into it at the beginning of the procedure and examine the table later. Run the procedure from SSMS using those variables stored in dump table.
Something is going on. You just don't know what it is.
Todd Fifield
September 30, 2011 at 2:10 am
tfifield (9/29/2011)
Something is going on that you don't know about. When something is going on that doesn't make sense, it's time to examine your basic assumptions.You can fire up the profiler as Remi suggested. You can create a dump table that corresponds to the input variables and insert all of the passed variables into it at the beginning of the procedure and examine the table later. Run the procedure from SSMS using those variables stored in dump table.
Something is going on. You just don't know what it is.
Todd Fifield
Could you give me some steps on how to do this ? I have'nt tried this before...
October 3, 2011 at 1:40 pm
Just create a table in which to dump the variables passed into the procedure with a little extra data to make it easier to eyeball. If the procedure looks like:
CREATE PROCEDURE SomeProcedure
@SomeInt INT
, @SomeText VARCHAR(50)
, @MoreText VARCHAR(50)
Then create a table that looks like:
CREATE TABLE XX_Dump
RecID INT IDENTITY(1, 1)
, TheTime DATETIME DEFAULT GETDATE()
, SomeInt int
, SomeText VARCHAR(50)
, MoreText VARCHAR(50)
)
-- At the start of the procedure put some code like this:
INSERT INTO XX_Dump
(SomeInt, SomeText, MoreText)
VALUES
( @SomeInt, @SomeText, @MoreText )
That way you can view that table to see what is actually passed to the stored procedure.
Run the procedure from SSMS and use those values to see what you get.
Todd Fifield
October 4, 2011 at 8:23 pm
just a suggestion....this is the type of syntax that i would use for a report query of this kind (i.e. when there are parameters that may or may not be passed in by the user at runtime):
AND ISNULL(Commodity,'') LIKE CASE WHEN @Commoditylike IS NOT NULL THEN '%' + @Commoditylike + '%' ELSE '' END
hope it helps 🙂
October 6, 2011 at 6:05 am
Ivanna Noh (10/4/2011)
just a suggestion....this is the type of syntax that i would use for a report query of this kind (i.e. when there are parameters that may or may not be passed in by the user at runtime):
AND ISNULL(Commodity,'') LIKE CASE WHEN @Commoditylike IS NOT NULL THEN '%' + @Commoditylike + '%' ELSE '' ENDhope it helps 🙂
Thanks Ivanna,
I tried your code but unfortunately this didnt work as well...
October 12, 2011 at 4:23 am
Ivanna Noh (10/4/2011)
just a suggestion....this is the type of syntax that i would use for a report query of this kind (i.e. when there are parameters that may or may not be passed in by the user at runtime):
AND ISNULL(Commodity,'') LIKE CASE WHEN @Commoditylike IS NOT NULL THEN '%' + @Commoditylike + '%' ELSE '' ENDhope it helps 🙂
Does someone have any ideas or suggestions that can prove useful. ?....I am now stuck for a long time on this one.
October 12, 2011 at 5:23 am
You need to learn to use profiler if you're going to succeed in this career. There's nothing here you can't learn to do on your own.
Open profiler, connect on your dev server (or whatever machine the data is on) and hit go on the default trace. Then run the report and see what's going on.
Worse comes to worst you'll have to play with it for 10 minutes before you get the hang of it.
October 12, 2011 at 9:43 am
Ninja's_RGR'us (10/12/2011)
You need to learn to use profiler if you're going to succeed in this career. There's nothing here you can't learn to do on your own.Open profiler, connect on your dev server (or whatever machine the data is on) and hit go on the default trace. Then run the report and see what's going on.
Worse comes to worst you'll have to play with it for 10 minutes before you get the hang of it.
I have some questions. How does the report need to be run from the report manager or Visual Studio itself ? How and where would the SQL profiler trace the stored procedure ?
October 12, 2011 at 10:06 am
Run the report from VS (should be easier to spot in the trace, but either will do).
You point the trace to whatever sql server the report gets the data from.
October 13, 2011 at 2:06 am
Ninja's_RGR'us (10/12/2011)
Run the report from VS (should be easier to spot in the trace, but either will do).You point the trace to whatever sql server the report gets the data from.
I am really not sure whats going on....this is getting nowhere. What do I do with the results in the trace ?
Could someone please help who might have encountered the same issue with the NULL value ?
October 13, 2011 at 4:47 am
Start the trace, run the report (make sure to hit refresh to get new dataset).
Then copy and paste the command sent here so we can see that for starters.
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply