October 13, 2011 at 7:40 am
Ninja's_RGR'us (10/13/2011)
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.
Is it possible to run the trace for just one database or a single stored procedure ? this is taking ages to run now...
October 13, 2011 at 7:48 am
You can stop the trace, restart it (that clears it all), run the report, and pause the trace right away.
once you find the correct row you can apply filters for your login or application name.
You can also save the trace to a table so you can query it with filters.
October 13, 2011 at 7:49 am
Check up that series, might give you great pointers :
October 14, 2011 at 9:14 am
Ninja's_RGR'us (10/13/2011)
Check up that series, might give you great pointers :
This is getting out of hand now, I am unable to find the particular piece of code in the profiler where it enounters NULL...Do you think SQL profiler would be the best bet to find the resolution for this ?
I am stuck with this...This should'nt be a unique issue someone must have enountered it in the past...Can someone pls help me with this ?
October 14, 2011 at 9:21 am
pwalter83 (10/14/2011)
Ninja's_RGR'us (10/13/2011)
Check up that series, might give you great pointers :This is getting out of hand now, I am unable to find the particular piece of code in the profiler where it enounters NULL...Do you think SQL profiler would be the best bet to find the resolution for this ?
I am stuck with this...This should'nt be a unique issue someone must have enountered it in the past...Can someone pls help me with this ?
We are helping you. The only thing we could do on top of what we're going is coming into your office to do it for you.
YOU need to decide you're going to do it and move towards that.
This has nothing to do with SQL, but you're current limitations in life.
I can't take that burden for you. I can only show you the way.
Profiler will show you the query. You just need to have the right filters in place (or not filter anything and manually search for it).
This series has a lot of info on profiler.
http://www.sqlservercentral.com/stairway/72363/
Once you see EXACTLY what's going on you can use the technic shown above to save the output to a temp table and isolate that last filter on it's own.
Once you have that we'll be able to give a tested answer. In the meantime we can't do anything else for you.
Stop trying to sabotage this job and possibly your life and get a move on. This is more a mindset issue than a sql one.
This is what BI & SSRS looks like on a daily basis. We're not hiding anything from you.
October 14, 2011 at 10:01 am
Ninja's_RGR'us (10/14/2011)
pwalter83 (10/14/2011)
Ninja's_RGR'us (10/13/2011)
Check up that series, might give you great pointers :This is getting out of hand now, I am unable to find the particular piece of code in the profiler where it enounters NULL...Do you think SQL profiler would be the best bet to find the resolution for this ?
I am stuck with this...This should'nt be a unique issue someone must have enountered it in the past...Can someone pls help me with this ?
We are helping you. The only thing we could do on top of what we're going is coming into your office to do it for you.
YOU need to decide you're going to do it and move towards that.
This has nothing to do with SQL, but you're current limitations in life.
I can't take that burden for you. I can only show you the way.
Profiler will show you the query. You just need to have the right filters in place (or not filter anything and manually search for it).
This series has a lot of info on profiler.
http://www.sqlservercentral.com/stairway/72363/
Once you see EXACTLY what's going on you can use the technic shown above to save the output to a temp table and isolate that last filter on it's own.
Once you have that we'll be able to give a tested answer. In the meantime we can't do anything else for you.
Stop trying to sabotage this job and possibly your life and get a move on. This is more a mindset issue than a sql one.
This is what BI & SSRS looks like on a daily basis. We're not hiding anything from you.
didnt know you also worked as a mind reader and a psychologist...is that supposed to be your part time job ?
i already told you that the particular line of code (where I am facing the issue of NULL) doesnt appear on the trace..what else am i supposed to do ? dont you think its better to ask than get frustrated...
October 14, 2011 at 10:05 am
From your last message I assume that you have seen the command in profiler sent from either BIDS or SSRS.
Please paste it here.
P.S. You won't see the full query of the stored proc, you should see something like EXEC dbo.ProName .... list of parameter values....
October 17, 2011 at 2:28 am
Ninja's_RGR'us (10/14/2011)
From your last message I assume that you have seen the command in profiler sent from either BIDS or SSRS.Please paste it here.
P.S. You won't see the full query of the stored proc, you should see something like EXEC dbo.ProName .... list of parameter values....
Thanks for your reply...but it doesnt show any statement as mentioned by you...all it displays is the select statement for all the parameters thats been included in the SSRS report. Below is one example of the same:
SQL:BatchStartingselect distinct trade_cd from tbl_TMIS_DATASET
order by trade_cd.Net SqlClient Data Provider
This is then followed by the following statement after each select statement for a parameter:
RPC:Completedexec sp_reset_connection .Net SqlClient Data Provider
To confirm, I have passed values and it shows up result based on that in BIDS.
October 17, 2011 at 4:41 am
pwalter83 (10/17/2011)
Ninja's_RGR'us (10/14/2011)
From your last message I assume that you have seen the command in profiler sent from either BIDS or SSRS.Please paste it here.
P.S. You won't see the full query of the stored proc, you should see something like EXEC dbo.ProName .... list of parameter values....
Thanks for your reply...but it doesnt show any statement as mentioned by you...all it displays is the select statement for all the parameters thats been included in the SSRS report. Below is one example of the same:
SQL:BatchStartingselect distinct trade_cd from tbl_TMIS_DATASET
order by trade_cd.Net SqlClient Data Provider
This is then followed by the following statement after each select statement for a parameter:
RPC:Completedexec sp_reset_connection .Net SqlClient Data Provider
To confirm, I have passed values and it shows up result based on that in BIDS.
Try to hit refresh so that the sp reloads (the data can be chached locally on your drive). If you want to make 100% sure it's not there anymore browse to your local folder where the .rdl file is and delete the .data file that has the same name as the report.
If after you still don't see it on profiler then either you have a filter that is blocking it or you're pointing to the wrong sql server (you must point to the server where the SP is).
You can confirm that server name in the project properties (as you can setup more than 1 server for each project and deploy on any of them).
October 17, 2011 at 6:06 am
Ninja's_RGR'us (10/17/2011)
pwalter83 (10/17/2011)
Ninja's_RGR'us (10/14/2011)
From your last message I assume that you have seen the command in profiler sent from either BIDS or SSRS.Please paste it here.
P.S. You won't see the full query of the stored proc, you should see something like EXEC dbo.ProName .... list of parameter values....
Thanks for your reply...but it doesnt show any statement as mentioned by you...all it displays is the select statement for all the parameters thats been included in the SSRS report. Below is one example of the same:
SQL:BatchStartingselect distinct trade_cd from tbl_TMIS_DATASET
order by trade_cd.Net SqlClient Data Provider
This is then followed by the following statement after each select statement for a parameter:
RPC:Completedexec sp_reset_connection .Net SqlClient Data Provider
To confirm, I have passed values and it shows up result based on that in BIDS.
Try to hit refresh so that the sp reloads (the data can be chached locally on your drive). If you want to make 100% sure it's not there anymore browse to your local folder where the .rdl file is and delete the .data file that has the same name as the report.
If after you still don't see it on profiler then either you have a filter that is blocking it or you're pointing to the wrong sql server (you must point to the server where the SP is).
You can confirm that server name in the project properties (as you can setup more than 1 server for each project and deploy on any of them).
I have double checked and can confirm that the profiler points to the correct server and have also deleted the .data file that has the same name as the report but still cannot see that piece of code. I think as you said there must be some filter blocking it but dont know where exactly should that be doing it...
by the way I hardcoded the values within the SP and then ran a trace on it, this is what I got:
SQL:BatchStarting[usp_QM_Details_Sailing] '01/01/2009','01/01/2011','IN','F','JAPAN','JPUKB','JPUKB','DEHAM','GERMANY','DEHAM','ASAEUR','NE4',NULL,'HKGB0007W'Microsoft SQL Server Management Studio -
October 17, 2011 at 6:11 am
Easy way to test for filters.
Start a new trace and start the standard (default) trace.
Depending on how SSRS plays here you might see something like SP_EXECUTESQL ... 'EXEC yourProcNameHere'
October 17, 2011 at 7:54 am
Ninja's_RGR'us (10/17/2011)
Easy way to test for filters.Start a new trace and start the standard (default) trace.
Depending on how SSRS plays here you might see something like SP_EXECUTESQL ... 'EXEC yourProcNameHere'
Done that but still cant see it...it still shows the select statements for all the parameters thats been included in the report..
October 17, 2011 at 7:58 am
pwalter83 (10/17/2011)
Ninja's_RGR'us (10/17/2011)
Easy way to test for filters.Start a new trace and start the standard (default) trace.
Depending on how SSRS plays here you might see something like SP_EXECUTESQL ... 'EXEC yourProcNameHere'
Done that but still cant see it...it still shows the select statements for all the parameters thats been included in the report..
That may be your real problem. If for some reason the sp doesn't run, it doesn't matter what we do, it won't ever work.
Try building a blank report with that sp as the datasource see if you can get data & see it in profiler.
October 17, 2011 at 8:11 am
Ninja's_RGR'us (10/17/2011)
pwalter83 (10/17/2011)
Ninja's_RGR'us (10/17/2011)
Easy way to test for filters.Start a new trace and start the standard (default) trace.
Depending on how SSRS plays here you might see something like SP_EXECUTESQL ... 'EXEC yourProcNameHere'
Done that but still cant see it...it still shows the select statements for all the parameters thats been included in the report..
That may be your real problem. If for some reason the sp doesn't run, it doesn't matter what we do, it won't ever work.
Try building a blank report with that sp as the datasource see if you can get data & see it in profiler.
before I found out the issue with NULLs this report was running absolutely fine....that is only problem I am facing, the rest of the report is okay....this is further diverting the attention from the main issue...there is nothing wrong with the SP...I was able to get data with it before....
A request: if the profiler is your only answer to my problem, can you please not answer it anymore and let others answer it. Let others also have a look into it....I am going round and round with this without any results...profile is not helping me anymore....I need some other outputs at this pojnt of time...hope you dont mind...
October 17, 2011 at 8:15 am
Well I'm out of ideas them. You know all I know about this problem and all the ways to treat the issue.
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply