My boss whats me to collect data in a way that will allow him to view it in a non-normalized view...
name phone_type_1, sn_phone_type_1, phone_type_2, sn_phone_type_2, phone_type_3, sn_phone_type_3...
He does NOT want...
name, phone_type, sn_phone_type
Bill ATT XX44YY
Bill ATT QQ66TT
Bill DELL 77JJ88
So how can I collect the data in a normalized fashion, then display it in the format he desires? If possible I really didn't want to write code to step through the records splitting them up, Id like to use SQL.
Thanks in advance for your suggestions.Collect it normalized like you want to. Then go like this:
declare @.foo varchar(8000)
select @.foo = coalesce (@.foo + ',', '')
+ phone_type + ',' + sn_phone_type
from persontable where name = 'Bill'
select @.foo
or something like that.
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Normal view compare to partitioned view
Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florence
Hi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florence
Hi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>
Normal view compare to partitioned view
Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florenceHi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florenceHi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>
Friday, March 23, 2012
Non-SA can't view Activity Monitor with VIEW SERVER STATE perm.
I have a developer who has dbo privileges in master and msdb, and also has
VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
documentation she should be able to view the Activity Monitor, but instead
gets the following error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions.
(ActivityMonitor)
SELECT permission denied on object 'tables', database 'mssqlsystemresource',
schema 'sys'.
SELECT permission denied on object 'partitions', database
'mssqlsystemresource', schema 'sys'.
Changed database context to '<db1>'.
Changed database context to '<db2>'.
Changed database context to '<db3>'.
Changed database context to '<db4>'.
Changed database context to '<db5>'.
Changed database context to '<db6>'.
Changed database context to 'tempdb'. (Microsoft SQL Server, Error: 229)
It popped up last week, and I read that Reporting Services running may have
an effect on it, so I stopped the service and she was able to get in with no
problem. No changes have been made to the server since I stopped the
service, but this error just started again today. No updates or patches hav
e
been installed on the server recently.
I have tried granting permission to 'tables' and 'partition' for master and
mssqlsystemresource db's, and to individual system tables but I run into
errors every time. I'm pretty new to SQL Server 2005 so please explain
everything in very simple terms!
THANKS!
Details: OS is Windows Server 2003 (clustered) and I have SQL Server 2005 SP
1 installed.Augspies (Augspies@.discussions.microsoft.com) writes:
> I have a developer who has dbo privileges in master and msdb, and also has
> VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
> documentation she should be able to view the Activity Monitor, but instead
> gets the following error:
>...
> SELECT permission denied on object 'tables', database
> 'mssqlsystemresource',
> schema 'sys'.
> SELECT permission denied on object 'partitions', database
> 'mssqlsystemresource', schema 'sys'.
That's a tricky one. It would indicate that there are locks on tables
in msqlsystemresource. And mssqlsystemresource is unavailable to
everyone, including sa, but if you're in the sysadmin role, the
check may be bypassed so you don't see the error.
I don't really know what to suggest, but you could try my aba_lockinfo
which gives about the same information as the Actvity Monitor, but
without any GUI. You can get it from
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Maybe I would be more worried over the locks in mssqlsystemresource.
That does not really sound normal to me...
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
VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
documentation she should be able to view the Activity Monitor, but instead
gets the following error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions.
(ActivityMonitor)
SELECT permission denied on object 'tables', database 'mssqlsystemresource',
schema 'sys'.
SELECT permission denied on object 'partitions', database
'mssqlsystemresource', schema 'sys'.
Changed database context to '<db1>'.
Changed database context to '<db2>'.
Changed database context to '<db3>'.
Changed database context to '<db4>'.
Changed database context to '<db5>'.
Changed database context to '<db6>'.
Changed database context to 'tempdb'. (Microsoft SQL Server, Error: 229)
It popped up last week, and I read that Reporting Services running may have
an effect on it, so I stopped the service and she was able to get in with no
problem. No changes have been made to the server since I stopped the
service, but this error just started again today. No updates or patches hav
e
been installed on the server recently.
I have tried granting permission to 'tables' and 'partition' for master and
mssqlsystemresource db's, and to individual system tables but I run into
errors every time. I'm pretty new to SQL Server 2005 so please explain
everything in very simple terms!
THANKS!
Details: OS is Windows Server 2003 (clustered) and I have SQL Server 2005 SP
1 installed.Augspies (Augspies@.discussions.microsoft.com) writes:
> I have a developer who has dbo privileges in master and msdb, and also has
> VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
> documentation she should be able to view the Activity Monitor, but instead
> gets the following error:
>...
> SELECT permission denied on object 'tables', database
> 'mssqlsystemresource',
> schema 'sys'.
> SELECT permission denied on object 'partitions', database
> 'mssqlsystemresource', schema 'sys'.
That's a tricky one. It would indicate that there are locks on tables
in msqlsystemresource. And mssqlsystemresource is unavailable to
everyone, including sa, but if you're in the sysadmin role, the
check may be bypassed so you don't see the error.
I don't really know what to suggest, but you could try my aba_lockinfo
which gives about the same information as the Actvity Monitor, but
without any GUI. You can get it from
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Maybe I would be more worried over the locks in mssqlsystemresource.
That does not really sound normal to me...
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
Wednesday, March 21, 2012
nonoandy
Hello,
Stack Trace: |||
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.
I think that this is some session time or something like that.
Am I right? How can fix it?
Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||
Back call without drillthrough report
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.InvalidOperationException: Back call without drillthrough report
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.
It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."
This only happens when I drill-through.
|||I'm having this issue also. Did anyone ever find a solution? thanks
|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
-nonoandy|||
i am also facing this problem... 
any solutions for this?
|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this linkhttp://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.
Read this
http://support.microsoft.com/default.aspx/kb/933137
and download
Thanks!
Andy
nonoandy
Hello,
Stack Trace: |||
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.
I think that this is some session time or something like that.
Am I right? How can fix it?
Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||
Back call without drillthrough report
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.InvalidOperationException: Back call without drillthrough report
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.
It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."
This only happens when I drill-through.
|||I'm having this issue also. Did anyone ever find a solution? thanks
|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
-nonoandy|||
i am also facing this problem...
any solutions for this?
|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this linkhttp://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.
Read this
http://support.microsoft.com/default.aspx/kb/933137
and download
Thanks!
Andy
nonoandy
Hello,
Stack Trace: |||
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.
I think that this is some session time or something like that.
Am I right? How can fix it?
Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||
Back call without drillthrough report
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.InvalidOperationException: Back call without drillthrough report
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.
It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."
This only happens when I drill-through.
|||I'm having this issue also. Did anyone ever find a solution? thanks
|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
-nonoandy|||
i am also facing this problem...
any solutions for this?
|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this linkhttp://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.
Read this
http://support.microsoft.com/default.aspx/kb/933137
and download
Thanks!
Andy
nonoandy
Hello,
Stack Trace: |||
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
My users often view some report for some minutes and after that want to return to parent report.
If they did it after small time (~less than minute), everything is OK. But if this pause is above 1 minute they receive "Runtime Error" message and can only to go to homepage.
I think that this is some session time or something like that.
Am I right? How can fix it?
Thank youAfter setting customError=off we receive "Back call without drillthrough report" error|||Did you find a fix for the problem? Can anyone help? I get the same problem.|||
Back call without drillthrough report
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.InvalidOperationException: Back call without drillthrough report
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[InvalidOperationException: Back call without drillthrough report] Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) +229 Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) +25 Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) +40 Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, ImageClickEventArgs e) +25 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +105 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 I am also receiving this error but there is no delay. As soon as the report is loaded, I click back to goto the previous report and I get this error.
It is interesting because charts are also not loading. I dont receive an error message but in the event log this is entered: "The stream cannot be found."
This only happens when I drill-through.
|||I'm having this issue also. Did anyone ever find a solution? thanks
|||Hey guys I think that this issue has something to do with some other problems I have been having due to SP1. I notice that the back button wasn't the only thing messing up, and it was only doing it in the ReportViewer (not when I went to the report directly in VS)check out this article
http://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
-nonoandy|||
i am also facing this problem...
any solutions for this?
|||Just checking if anyone has any luck with this? I spend more time on these forums than i do developing SSRS. Come across issues on a seemingly endless basis.|||Did you check out this linkhttp://www.eggheadcafe.com/software/aspnet/29672997/back-call-without-drillth.aspx
?
I had two things that was giving me the same problem one was a Ajax update panel around it and the other was because of the bad release of VS2005 SP1. The "Microsoft.ReportViewer.* assemblies" where messing it up so I had to un-install them from my GAC and find the older "File version"
(To find out what file version of the assemblies you have right click and hit properties. Don't look at the Version tab in the windows explore.)
But you don't have to do that now Microsoft came out with new assemblies in and you can download them in a redistributable package.
Read this
http://support.microsoft.com/default.aspx/kb/933137
and download
Thanks!
Andy
non-matching recrods 2 tables (Newbie)
Hi,
I am new to SQL and these forums, and have a quick simple question.
I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.
Here is an example of what the two tables contain.
Table 1
customer_no eaddress_no name address (36000 records)
Table2
customer_no eaddress_no email (17000 records)
I need to find out which customers are not in table 2 by linking the eaddress_no numbers.
This is one of the scripts i ran that gives me the large results with a ton of dups:
SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no
Thanks in advance for any help with this.Try this:
SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.eaddress_no <> Table2.eaddress_no|||I got the same results:
Alot of dups.
I did just find this at SQLTeam after i posted this message. I am just getting ready to try it now.
SELECT A.No,A.Date
FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL|||If your query does not work, try this:
SELECT *
FROM Table1
WHERE eaddress_no NOT IN
(SELECT eaddress_no FROM Table2)|||gyuan that worked.
yours looked a little easier for me to follow so i just used that one.
Thanks a million.|||Using a left join is generally preferable to NOT EXISTS.
SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null
This can also be easily be modified to show records that are missing from either table:
SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||Thanks Blind Man,
Those make sense to me now . I helps to see the table names in the query.|||Blindman,
you have an extra ) where would the opening one go?|||Just remove it...you don't need it...|||Sorry. No Parenthesis is necessary. It was a copy/paste error.|||I tried that and it returns no records.|||If you run this
SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null
and no records are returned, then there are no eaddress_no values in Table1 that are not also in Table2.
Run the second one with the FULL OUTER JOIN clause and see what you get:
SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||OOPS!
WHERE clause was looking at wrong table! We want to see if Table2 is null...
SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table2.eaddress_no is null|||Yep that did it. And it took about 2 seconds few to run.
Thanks blindman, now i see.
I am new to SQL and these forums, and have a quick simple question.
I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.
Here is an example of what the two tables contain.
Table 1
customer_no eaddress_no name address (36000 records)
Table2
customer_no eaddress_no email (17000 records)
I need to find out which customers are not in table 2 by linking the eaddress_no numbers.
This is one of the scripts i ran that gives me the large results with a ton of dups:
SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no
Thanks in advance for any help with this.Try this:
SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.eaddress_no <> Table2.eaddress_no|||I got the same results:
Alot of dups.
I did just find this at SQLTeam after i posted this message. I am just getting ready to try it now.
SELECT A.No,A.Date
FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL|||If your query does not work, try this:
SELECT *
FROM Table1
WHERE eaddress_no NOT IN
(SELECT eaddress_no FROM Table2)|||gyuan that worked.
yours looked a little easier for me to follow so i just used that one.
Thanks a million.|||Using a left join is generally preferable to NOT EXISTS.
SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null
This can also be easily be modified to show records that are missing from either table:
SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||Thanks Blind Man,
Those make sense to me now . I helps to see the table names in the query.|||Blindman,
you have an extra ) where would the opening one go?|||Just remove it...you don't need it...|||Sorry. No Parenthesis is necessary. It was a copy/paste error.|||I tried that and it returns no records.|||If you run this
SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null
and no records are returned, then there are no eaddress_no values in Table1 that are not also in Table2.
Run the second one with the FULL OUTER JOIN clause and see what you get:
SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||OOPS!
WHERE clause was looking at wrong table! We want to see if Table2 is null...
SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table2.eaddress_no is null|||Yep that did it. And it took about 2 seconds few to run.
Thanks blindman, now i see.
Tuesday, March 20, 2012
Non-deterministic Clustered Index for Indexed View
Hi all,
I am trying to create an indexed view to do aggregation on a table of
Payments. The table has columns as follows:
CREATE TABLE [Payments] (
[PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
[PmtAmt] [smallmoney] NOT NULL ,
[PmtDate] [smalldatetime] NOT NULL ,
[Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[PmtKey]
) ON [PRIMARY]
) ON [PRIMARY]
I am trying to create an indexed view as follows:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
I am doing the cast to try to get all payments grouped by the same date,
ignoring the time portion.
When I go to add a unique clustered index to this view,
CREATE UNIQUE CLUSTERED
INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
WITH
FILLFACTOR = 85
I am told that the column PmtDate is non-detereministic or too imprecise.
I understand the requirement that the indices in an indexed view cannot be
nullable and must be deterministic. Is there another way to cast PmtDate
that will allow me to do the grouping and aggregation I am trying to achieve
?
Thanks.
--
John> Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
CAST is non-deterministic when used with smalldatetime. Try CONVERT with a
style parameter instead:
CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
> Hi all,
> I am trying to create an indexed view to do aggregation on a table of
> Payments. The table has columns as follows:
> CREATE TABLE [Payments] (
> [PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
> [PmtAmt] [smallmoney] NOT NULL ,
> [PmtDate] [smalldatetime] NOT NULL ,
> [Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
> CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
> (
> [PmtKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
> I am doing the cast to try to get all payments grouped by the same date,
> ignoring the time portion.
> When I go to add a unique clustered index to this view,
> CREATE UNIQUE CLUSTERED
> INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
> WITH
> FILLFACTOR = 85
> I am told that the column PmtDate is non-detereministic or too imprecise.
> I understand the requirement that the indices in an indexed view cannot be
> nullable and must be deterministic. Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
> Thanks.
> --
> John|||JT (Jthayer@.online.nospam) writes:
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
Dan posted a solution, but it works only on SQL 2005. (I've tested).
On SQL 2000 you may have to let it suffice with:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(char(8), PmtDate, 112)
Thus, you get PmtDate as a char(8) column instead.
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|||Thanks Dan,
From BOL:
CONVERT: Deterministic unless used with datetime, smalldatetime, or
sql_variant. The datetime and smalldatetime data types are deterministic if
the style parameter is also specified.
In theory, it would sure seem that your solution should work. In practice,
I get the same error. Any other thoughts?
--
John
"Dan Guzman" wrote:
> CAST is non-deterministic when used with smalldatetime. Try CONVERT with
a
> style parameter instead:
> CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
>
>|||It looks like you are using SQL 2000.
Erland posted one method that will work in SQL 2000. Here's an extention of
that technique that will return a smalldatetime:
CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
> Thanks Dan,
> From BOL:
> CONVERT: Deterministic unless used with datetime, smalldatetime, or
> sql_variant. The datetime and smalldatetime data types are deterministic
> if
> the style parameter is also specified.
> In theory, it would sure seem that your solution should work. In
> practice,
> I get the same error. Any other thoughts?
> --
> John
>
> "Dan Guzman" wrote:
>|||Dan and Erland,
Thank you both for your posts and wisdom. I independently got it working by
getting rid of the 0 and explicitly setting 1/1/1900 as the index date for
SQL Server's Datediff function. For example:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME, '1900-01-01
00:00:00', 102), PmtDate), 101) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME,
'1900-01-01 00:00:00', 102), PmtDate), 101)
Pretty darn ugly, if I may say so. I like your method better.
Thanks again.
John
"Dan Guzman" wrote:
> It looks like you are using SQL 2000.
> Erland posted one method that will work in SQL 2000. Here's an extention
of
> that technique that will return a smalldatetime:
> CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
>
>|||On second thought, I think I will stick with my convoluted method as it
preserves the column as smalldatetime datatype, which will be helpful in
sorting records for reporting. Sorting dates cast as Char(8) gives you all
of the January's, then the February's when converted to style 101, which is
what I use in my reports.
Thanks again.
--
John
"Erland Sommarskog" wrote:
> JT (Jthayer@.online.nospam) writes:
> Dan posted a solution, but it works only on SQL 2005. (I've tested).
> On SQL 2000 you may have to let it suffice with:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CONVERT(char(8), PmtDate, 112)
> Thus, you get PmtDate as a char(8) column instead.
>
> --
> 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
>|||Let's start with everything is wrong. You have an IDENTITY column, no
key, and use assembly language style BIT flags and proprietary MONEY
data types in spite of the math errors in it. The payments are not
posted to an account or an invoice? Wild guess at a valid design
CREATE TABLE Payments
(invoice_nbr INTEGER NOT NULL,
payment_nbr INTEGER NOT NULL,
PRIMARY KEY (invoice_nbr, payment_nbr),
pmt_amt DECIMAL (12,4) NOT NULL,
pmt_date DATETIME NOT NULL
CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
DATETIME)), -- other ways to do this, too
pmt_status INTEGER NOT NULL);
What if you had a relational approach and not allow bad data that has
to be clean out later? Good DDL will save you from complex kludges.|||Thanks for the enlightenment! You forgot the Volkswagen lecture, as the vie
w
is named vw... The actual table is considerably different than the
simplified example I posted. Seriously, though, thanks for your input,
especially about the datetime column. Something to consider...
--
John
"--CELKO--" wrote:
> Let's start with everything is wrong. You have an IDENTITY column, no
> key, and use assembly language style BIT flags and proprietary MONEY
> data types in spite of the math errors in it. The payments are not
> posted to an account or an invoice? Wild guess at a valid design
> CREATE TABLE Payments
> (invoice_nbr INTEGER NOT NULL,
> payment_nbr INTEGER NOT NULL,
> PRIMARY KEY (invoice_nbr, payment_nbr),
> pmt_amt DECIMAL (12,4) NOT NULL,
> pmt_date DATETIME NOT NULL
> CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
> DATETIME)), -- other ways to do this, too
> pmt_status INTEGER NOT NULL);
>
> What if you had a relational approach and not allow bad data that has
> to be clean out later? Good DDL will save you from complex kludges.
>|||I pikced up a slogan from Graeme Simsion, who is a data quality and
design guru -- "Mop the floor, but then fix the leak!" . I am getting
a presentation on advanced DDL ready for PASS this year. DML gets all
the glory, but good DDL does the real work.
And one day, I will figure out DCL trick.
s
I am trying to create an indexed view to do aggregation on a table of
Payments. The table has columns as follows:
CREATE TABLE [Payments] (
[PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
[PmtAmt] [smallmoney] NOT NULL ,
[PmtDate] [smalldatetime] NOT NULL ,
[Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[PmtKey]
) ON [PRIMARY]
) ON [PRIMARY]
I am trying to create an indexed view as follows:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
I am doing the cast to try to get all payments grouped by the same date,
ignoring the time portion.
When I go to add a unique clustered index to this view,
CREATE UNIQUE CLUSTERED
INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
WITH
FILLFACTOR = 85
I am told that the column PmtDate is non-detereministic or too imprecise.
I understand the requirement that the indices in an indexed view cannot be
nullable and must be deterministic. Is there another way to cast PmtDate
that will allow me to do the grouping and aggregation I am trying to achieve
?
Thanks.
--
John> Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
CAST is non-deterministic when used with smalldatetime. Try CONVERT with a
style parameter instead:
CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
> Hi all,
> I am trying to create an indexed view to do aggregation on a table of
> Payments. The table has columns as follows:
> CREATE TABLE [Payments] (
> [PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
> [PmtAmt] [smallmoney] NOT NULL ,
> [PmtDate] [smalldatetime] NOT NULL ,
> [Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
> CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
> (
> [PmtKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
> I am doing the cast to try to get all payments grouped by the same date,
> ignoring the time portion.
> When I go to add a unique clustered index to this view,
> CREATE UNIQUE CLUSTERED
> INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
> WITH
> FILLFACTOR = 85
> I am told that the column PmtDate is non-detereministic or too imprecise.
> I understand the requirement that the indices in an indexed view cannot be
> nullable and must be deterministic. Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
> Thanks.
> --
> John|||JT (Jthayer@.online.nospam) writes:
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
Dan posted a solution, but it works only on SQL 2005. (I've tested).
On SQL 2000 you may have to let it suffice with:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(char(8), PmtDate, 112)
Thus, you get PmtDate as a char(8) column instead.
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|||Thanks Dan,
From BOL:
CONVERT: Deterministic unless used with datetime, smalldatetime, or
sql_variant. The datetime and smalldatetime data types are deterministic if
the style parameter is also specified.
In theory, it would sure seem that your solution should work. In practice,
I get the same error. Any other thoughts?
--
John
"Dan Guzman" wrote:
> CAST is non-deterministic when used with smalldatetime. Try CONVERT with
a
> style parameter instead:
> CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
>
>|||It looks like you are using SQL 2000.
Erland posted one method that will work in SQL 2000. Here's an extention of
that technique that will return a smalldatetime:
CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
> Thanks Dan,
> From BOL:
> CONVERT: Deterministic unless used with datetime, smalldatetime, or
> sql_variant. The datetime and smalldatetime data types are deterministic
> if
> the style parameter is also specified.
> In theory, it would sure seem that your solution should work. In
> practice,
> I get the same error. Any other thoughts?
> --
> John
>
> "Dan Guzman" wrote:
>|||Dan and Erland,
Thank you both for your posts and wisdom. I independently got it working by
getting rid of the 0 and explicitly setting 1/1/1900 as the index date for
SQL Server's Datediff function. For example:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME, '1900-01-01
00:00:00', 102), PmtDate), 101) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME,
'1900-01-01 00:00:00', 102), PmtDate), 101)
Pretty darn ugly, if I may say so. I like your method better.
Thanks again.
John
"Dan Guzman" wrote:
> It looks like you are using SQL 2000.
> Erland posted one method that will work in SQL 2000. Here's an extention
of
> that technique that will return a smalldatetime:
> CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
>
>|||On second thought, I think I will stick with my convoluted method as it
preserves the column as smalldatetime datatype, which will be helpful in
sorting records for reporting. Sorting dates cast as Char(8) gives you all
of the January's, then the February's when converted to style 101, which is
what I use in my reports.
Thanks again.
--
John
"Erland Sommarskog" wrote:
> JT (Jthayer@.online.nospam) writes:
> Dan posted a solution, but it works only on SQL 2005. (I've tested).
> On SQL 2000 you may have to let it suffice with:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CONVERT(char(8), PmtDate, 112)
> Thus, you get PmtDate as a char(8) column instead.
>
> --
> 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
>|||Let's start with everything is wrong. You have an IDENTITY column, no
key, and use assembly language style BIT flags and proprietary MONEY
data types in spite of the math errors in it. The payments are not
posted to an account or an invoice? Wild guess at a valid design
CREATE TABLE Payments
(invoice_nbr INTEGER NOT NULL,
payment_nbr INTEGER NOT NULL,
PRIMARY KEY (invoice_nbr, payment_nbr),
pmt_amt DECIMAL (12,4) NOT NULL,
pmt_date DATETIME NOT NULL
CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
DATETIME)), -- other ways to do this, too
pmt_status INTEGER NOT NULL);
What if you had a relational approach and not allow bad data that has
to be clean out later? Good DDL will save you from complex kludges.|||Thanks for the enlightenment! You forgot the Volkswagen lecture, as the vie
w
is named vw... The actual table is considerably different than the
simplified example I posted. Seriously, though, thanks for your input,
especially about the datetime column. Something to consider...
--
John
"--CELKO--" wrote:
> Let's start with everything is wrong. You have an IDENTITY column, no
> key, and use assembly language style BIT flags and proprietary MONEY
> data types in spite of the math errors in it. The payments are not
> posted to an account or an invoice? Wild guess at a valid design
> CREATE TABLE Payments
> (invoice_nbr INTEGER NOT NULL,
> payment_nbr INTEGER NOT NULL,
> PRIMARY KEY (invoice_nbr, payment_nbr),
> pmt_amt DECIMAL (12,4) NOT NULL,
> pmt_date DATETIME NOT NULL
> CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
> DATETIME)), -- other ways to do this, too
> pmt_status INTEGER NOT NULL);
>
> What if you had a relational approach and not allow bad data that has
> to be clean out later? Good DDL will save you from complex kludges.
>|||I pikced up a slogan from Graeme Simsion, who is a data quality and
design guru -- "Mop the floor, but then fix the leak!" . I am getting
a presentation on advanced DDL ready for PASS this year. DML gets all
the glory, but good DDL does the real work.
And one day, I will figure out DCL trick.
s
Wednesday, March 7, 2012
NOLOCK hint on views?
Hi all
If i have a view:
CREATE VIEW vw_Users
AS
SELECT * FROM Users WITH(NOLOCK)
Is it suggested to use nolock in views?
And if i needed to use this view in stored procs is it then suggested to apply the nolock hint?
CREATE PROC [dbo] .[usp_GetCompanyUsers]
AS
SELECT * FROM Companies WITH(NOLOCK) JOIN
vw_Users WITH(NOLOCK) --<< --is this suggested?
If you using NOLOCK hint while creating view, you don't need to use the NOLOCK again when accessing the view. The locking is always done on the table and not on the view.
Monday, February 20, 2012
No tables in Linked Excel Server view
This allowed me to create a linked server, but when I try and view the
tables, it does not show any tables.
I assumed the tables would be the sheet names, no?
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'H:\IT\Shared\tblInItem.xls',
NULL,
'Excel 5.0'
GO
--
-Tobias Mazzei
Wraith SystemsNot really - see example F:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
You need to name the cell range. I think I saw a VBA procedure some where
(online) that automates range naming - provided there's only a single table
per worksheet.
ML|||Okay, I found the skinny on this. I hope my pain helps others on this.
Apparently I goofed in setting up the linked servers to begin with.
The standard procedure for setting these up is to
1) Create the Excel file(s)
2) Name the columns using the range name function, these names will show up
in your "tables" list once you establish the link
3) Make sure you have sy
min AND setupadmin rights first
4) Use Enterprise Mgr to right click on linked servers and create new link
-or-
Use Sql Query Analyzer and build a statement like:
DECLARE @.RC int
DECLARE @.server nvarchar(128)
DECLARE @.srvproduct nvarchar(128)
DECLARE @.provider nvarchar(128)
DECLARE @.datasrc nvarchar(4000)
DECLARE @.location nvarchar(4000)
DECLARE @.provstr nvarchar(4000)
DECLARE @.catalog nvarchar(128)
-- Set parameter values
-- Remote Server Name
SET @.server = 'xcltblInItemUom'
-- Program you are importing from
SET @.srvproduct = 'Excel'
-- Type of Data Link Engine
SET @.provider = 'Microsoft.Jet.OLEDB.4.0'
-- Important, Use full domain namespace
SET @.datasrc = '\\svrsbs3hyc\Company\IT\Shared\tblInIte
mUom.xls'
SET @.provstr = 'Excel 8.0'
-- Execute command to link server to Master.DBO
EXEC @.RC = [master].[dbo].[sp_addlinkedserver]
@.server, @.srvproduct, @.provider,
@.datasrc, @.location, @.provstr, @.catalog
Note that if you are not programming directly ON the SQL server you MUST use
the UNC of the location of the excel files. (most of us dont use the server
to program on, so this is important)
5) Depending on your security policies, choose link server properties after
link, and under security, select Be made without using a security context
6) when referencing the query, the sheet is designated by a '$' after the na
me
7) Rinse, repeat for each file you need to link.
This operation works, although I am sure that there are more glamourous ways
of doing this.
For those beginners out there that didn't get this stuff in MCDBA courses,
good luck!!
-Tobias Mazzei
Wraith Systems
"ML" wrote:
> Not really - see example F:
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> You need to name the cell range. I think I saw a VBA procedure some where
> (online) that automates range naming - provided there's only a single tabl
e
> per worksheet.
>
> ML
tables, it does not show any tables.
I assumed the tables would be the sheet names, no?
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'H:\IT\Shared\tblInItem.xls',
NULL,
'Excel 5.0'
GO
--
-Tobias Mazzei
Wraith SystemsNot really - see example F:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
You need to name the cell range. I think I saw a VBA procedure some where
(online) that automates range naming - provided there's only a single table
per worksheet.
ML|||Okay, I found the skinny on this. I hope my pain helps others on this.
Apparently I goofed in setting up the linked servers to begin with.
The standard procedure for setting these up is to
1) Create the Excel file(s)
2) Name the columns using the range name function, these names will show up
in your "tables" list once you establish the link
3) Make sure you have sy
min AND setupadmin rights first4) Use Enterprise Mgr to right click on linked servers and create new link
-or-
Use Sql Query Analyzer and build a statement like:
DECLARE @.RC int
DECLARE @.server nvarchar(128)
DECLARE @.srvproduct nvarchar(128)
DECLARE @.provider nvarchar(128)
DECLARE @.datasrc nvarchar(4000)
DECLARE @.location nvarchar(4000)
DECLARE @.provstr nvarchar(4000)
DECLARE @.catalog nvarchar(128)
-- Set parameter values
-- Remote Server Name
SET @.server = 'xcltblInItemUom'
-- Program you are importing from
SET @.srvproduct = 'Excel'
-- Type of Data Link Engine
SET @.provider = 'Microsoft.Jet.OLEDB.4.0'
-- Important, Use full domain namespace
SET @.datasrc = '\\svrsbs3hyc\Company\IT\Shared\tblInIte
mUom.xls'
SET @.provstr = 'Excel 8.0'
-- Execute command to link server to Master.DBO
EXEC @.RC = [master].[dbo].[sp_addlinkedserver]
@.server, @.srvproduct, @.provider,
@.datasrc, @.location, @.provstr, @.catalog
Note that if you are not programming directly ON the SQL server you MUST use
the UNC of the location of the excel files. (most of us dont use the server
to program on, so this is important)
5) Depending on your security policies, choose link server properties after
link, and under security, select Be made without using a security context
6) when referencing the query, the sheet is designated by a '$' after the na
me
7) Rinse, repeat for each file you need to link.
This operation works, although I am sure that there are more glamourous ways
of doing this.
For those beginners out there that didn't get this stuff in MCDBA courses,
good luck!!
-Tobias Mazzei
Wraith Systems
"ML" wrote:
> Not really - see example F:
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> You need to name the cell range. I think I saw a VBA procedure some where
> (online) that automates range naming - provided there's only a single tabl
e
> per worksheet.
>
> ML
Subscribe to:
Posts (Atom)