Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.Use Query Analyzer for viewing data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.|||I mean, use SELECT statements in Query Analyzer, to view data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.|||We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead o
f
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||Could you check the datatype of that column, using sp_help (from Query
Analyzer), and see if it is any different to other databases.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead
of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||I understood what you meant. And we do usually use the QA.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> I mean, use SELECT statements in Query Analyzer, to view data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead o
f
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
>
> of
>
>|||Then, I am not sure. Haven't seen that before. Have you searched KB for any
known issues? My last option would be to reinstall the client utilities.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
>
> of
>
>|||I've been searching kb and the web. We're going to reinstall and see if that
fixes it.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Then, I am not sure. Haven't seen that before. Have you searched KB for an
y
> known issues? My last option would be to reinstall the client utilities.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
> Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying t
he
> "binary" word. I had this happen on my local box once but I can't remember
> how I fixed it.
> "Narayana Vyas Kondreddi" wrote:
>
>
>
Showing posts with label actual. Show all posts
Showing posts with label actual. Show all posts
Wednesday, March 7, 2012
em displays binary data
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
Dan D.
Use Query Analyzer for viewing data.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
Dan D.
|||I mean, use SELECT statements in Query Analyzer, to view data.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
Dan D.
|||We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>
|||Could you check the datatype of that column, using sp_help (from Query
Analyzer), and see if it is any different to other databases.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead
of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>
|||I understood what you meant. And we do usually use the QA.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> I mean, use SELECT statements in Query Analyzer, to view data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>
|||Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> of
>
>
|||Then, I am not sure. Haven't seen that before. Have you searched KB for any
known issues? My last option would be to reinstall the client utilities.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> of
>
>
|||I've been searching kb and the web. We're going to reinstall and see if that
fixes it.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Then, I am not sure. Haven't seen that before. Have you searched KB for any
> known issues? My last option would be to reinstall the client utilities.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
> Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
> "binary" word. I had this happen on my local box once but I can't remember
> how I fixed it.
> "Narayana Vyas Kondreddi" wrote:
>
>
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
Dan D.
Use Query Analyzer for viewing data.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
Dan D.
|||I mean, use SELECT statements in Query Analyzer, to view data.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
Dan D.
|||We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>
|||Could you check the datatype of that column, using sp_help (from Query
Analyzer), and see if it is any different to other databases.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead
of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>
|||I understood what you meant. And we do usually use the QA.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> I mean, use SELECT statements in Query Analyzer, to view data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>
|||Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> of
>
>
|||Then, I am not sure. Haven't seen that before. Have you searched KB for any
known issues? My last option would be to reinstall the client utilities.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> of
>
>
|||I've been searching kb and the web. We're going to reinstall and see if that
fixes it.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Then, I am not sure. Haven't seen that before. Have you searched KB for any
> known issues? My last option would be to reinstall the client utilities.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
> Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
> "binary" word. I had this happen on my local box once but I can't remember
> how I fixed it.
> "Narayana Vyas Kondreddi" wrote:
>
>
em displays binary data
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.Use Query Analyzer for viewing data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.|||I mean, use SELECT statements in Query Analyzer, to view data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.|||We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||Could you check the datatype of that column, using sp_help (from Query
Analyzer), and see if it is any different to other databases.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead
of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||I understood what you meant. And we do usually use the QA.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> I mean, use SELECT statements in Query Analyzer, to view data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> > Use Query Analyzer for viewing data.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> > Using SS2000 SP3a. When displaying data from a user table in EM, instead
> of
> > the actual data displaying, the word "binary" will display.
> >
> > How can I see the actual data?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> >
>
>|||Then, I am not sure. Haven't seen that before. Have you searched KB for any
known issues? My last option would be to reinstall the client utilities.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> > Use Query Analyzer for viewing data.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> > Using SS2000 SP3a. When displaying data from a user table in EM, instead
> of
> > the actual data displaying, the word "binary" will display.
> >
> > How can I see the actual data?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> >
>
>|||I've been searching kb and the web. We're going to reinstall and see if that
fixes it.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Then, I am not sure. Haven't seen that before. Have you searched KB for any
> known issues? My last option would be to reinstall the client utilities.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
> Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
> "binary" word. I had this happen on my local box once but I can't remember
> how I fixed it.
> "Narayana Vyas Kondreddi" wrote:
> > Could you check the datatype of that column, using sp_help (from Query
> > Analyzer), and see if it is any different to other databases.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> > We have 7 SQL Server boxes and what I'm describing only happens on one
> > server. I want to fix the problem. Do you have any ideas on how to fix it?
> >
> > Thanks
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Use Query Analyzer for viewing data.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > > news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> > > Using SS2000 SP3a. When displaying data from a user table in EM, instead
> > of
> > > the actual data displaying, the word "binary" will display.
> > >
> > > How can I see the actual data?
> > >
> > > Thanks,
> > > --
> > > Dan D.
> > >
> > >
> > >
> >
> >
> >
>
>
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.Use Query Analyzer for viewing data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.|||I mean, use SELECT statements in Query Analyzer, to view data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
Using SS2000 SP3a. When displaying data from a user table in EM, instead of
the actual data displaying, the word "binary" will display.
How can I see the actual data?
Thanks,
--
Dan D.|||We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||Could you check the datatype of that column, using sp_help (from Query
Analyzer), and see if it is any different to other databases.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
We have 7 SQL Server boxes and what I'm describing only happens on one
server. I want to fix the problem. Do you have any ideas on how to fix it?
Thanks
"Narayana Vyas Kondreddi" wrote:
> Use Query Analyzer for viewing data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead
of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||I understood what you meant. And we do usually use the QA.
Thanks,
"Narayana Vyas Kondreddi" wrote:
> I mean, use SELECT statements in Query Analyzer, to view data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> Using SS2000 SP3a. When displaying data from a user table in EM, instead of
> the actual data displaying, the word "binary" will display.
> How can I see the actual data?
> Thanks,
> --
> Dan D.
>
>|||Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> > Use Query Analyzer for viewing data.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> > Using SS2000 SP3a. When displaying data from a user table in EM, instead
> of
> > the actual data displaying, the word "binary" will display.
> >
> > How can I see the actual data?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> >
>
>|||Then, I am not sure. Haven't seen that before. Have you searched KB for any
known issues? My last option would be to reinstall the client utilities.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
"binary" word. I had this happen on my local box once but I can't remember
how I fixed it.
"Narayana Vyas Kondreddi" wrote:
> Could you check the datatype of that column, using sp_help (from Query
> Analyzer), and see if it is any different to other databases.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> We have 7 SQL Server boxes and what I'm describing only happens on one
> server. I want to fix the problem. Do you have any ideas on how to fix it?
> Thanks
> "Narayana Vyas Kondreddi" wrote:
> > Use Query Analyzer for viewing data.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> > Using SS2000 SP3a. When displaying data from a user table in EM, instead
> of
> > the actual data displaying, the word "binary" will display.
> >
> > How can I see the actual data?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> >
>
>|||I've been searching kb and the web. We're going to reinstall and see if that
fixes it.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Then, I am not sure. Haven't seen that before. Have you searched KB for any
> known issues? My last option would be to reinstall the client utilities.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:01BCB015-3A5D-4545-A652-5C1D4400CFAC@.microsoft.com...
> Sorry I wasn't clearer. It's ALL columns in ANY table that is displaying the
> "binary" word. I had this happen on my local box once but I can't remember
> how I fixed it.
> "Narayana Vyas Kondreddi" wrote:
> > Could you check the datatype of that column, using sp_help (from Query
> > Analyzer), and see if it is any different to other databases.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:1AC2C424-CDE6-4303-A817-DCA79AB700B6@.microsoft.com...
> > We have 7 SQL Server boxes and what I'm describing only happens on one
> > server. I want to fix the problem. Do you have any ideas on how to fix it?
> >
> > Thanks
> >
> > "Narayana Vyas Kondreddi" wrote:
> >
> > > Use Query Analyzer for viewing data.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > > news:27FE850B-74C6-41A2-B51C-B929B5FA715D@.microsoft.com...
> > > Using SS2000 SP3a. When displaying data from a user table in EM, instead
> > of
> > > the actual data displaying, the word "binary" will display.
> > >
> > > How can I see the actual data?
> > >
> > > Thanks,
> > > --
> > > Dan D.
> > >
> > >
> > >
> >
> >
> >
>
>
Sunday, February 26, 2012
Eliminating spaces in query output
Is there a way that I can do a select statement that will just select the text within a field. ie. If the field is 200 wide, but the actual text is only 20 characters, can I select JUST the 20 characters with nothing else? I need to do this for a huge number of rows ....
Also, I'm running the select statement via osql if that helps.When selecting the column name you can use the trim function, e.g.
select rtrim(ltrim(emp_name))
from employees
This will trim blank spaces from the left and right hand ends of the character string. If you only need to clear trailing blanks just use the rtrim function.|||RTRIM was just what I needed, thank you.
Also, I'm running the select statement via osql if that helps.When selecting the column name you can use the trim function, e.g.
select rtrim(ltrim(emp_name))
from employees
This will trim blank spaces from the left and right hand ends of the character string. If you only need to clear trailing blanks just use the rtrim function.|||RTRIM was just what I needed, thank you.
Friday, February 24, 2012
Eliminate Division by Zero
This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.
Any suggestions on how to improve this?
SELECT FL.REPORT_PLANT,
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
FROM PRODUCTION_CMPLT PC INNER JOIN
FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND
PC.PROD_LINE = FL.PROD_LINE INNER JOIN
PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANTSELECT [JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END)
the problem lies in your second CASE expression yielding 1 when the sum is not =1. What you want is for the whole expression to yield 0 if that =1...
I think this might be the what you want
SELECT
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN (PC.TONS * 2000 / PM.EA_WT) / (PC.HOURS * 60) ELSE 0 END)|||Or...
...ELSE 1|||(select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm
Or
Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
And Pm. Ea_wt <> 0
??|||re: georgev
ahhh... But I need Sum(Units)/Sum(Minutes), which is not the same as
Sum(Units/Minutes)
Changing to Else 1 yields a "false positive" in the off months, and throws off my numbers.|||(select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm
Or
Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
And Pm. Ea_wt <> 0
??
I don't have a problem with ea_wt. There is a constraint on the table that doesn't allow that field to be equal to 0.|||I think you want to do this
SELECT FL.REPORT_PLANT,
, [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT
UNION ALL
SELECT FL.REPORT_PLANT,
, [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 2
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT
UNION ALL
..ect|||That looks promising. I'll give it a go.|||Actually, with that one you will need a labl;e for each union to know what month it is...but you could do this as columns like you have
SELECT *
FROM FACILITY_LINES xxx
INNER JOIN (
SELECT FL.REPORT_PLANT
, [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS JAN
ON xxx.REPORT_PLANT = JAN.REPORT_PLANT
INNER JOIN (
SELECT FL.REPORT_PLANT
, [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 2
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS FEB
ON xxx.REPORT_PLANT = FEB.REPORT_PLANT
..ect|||For some reason -that I can't figure out- I had to use SELECT DISTINCT.
I also had to use a LEFT JOIN for each subquery...
SELECT DISTINCT FL.REPORT_PLANT, JAN, FEB, MAR, APR, MAY
FROM FACILITY_LINES FL
LEFT OUTER JOIN (
SELECT FL.REPORT_PLANT, [JAN] =sum( PC.TONS * 2000 /PM.EA_WT)/sum(PC.HOURS*60)
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS JAN
ON FL.REPORT_PLANT = JAN.REPORT_PLANT
LEFT OUTER JOIN (
SELECT FL.REPORT_PLANT
, [FEB] = SUM(PC.TONS * 2000 /PM.EA_WT)/sUM(PC.HOURS*60)
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 2
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS FEB
ON FL.REPORT_PLANT = FEB.REPORT_PLANT
...
That's exactly what I needed.
Thanks
Mark|||Why...what did the output look like without distinct?|||The Facility Lines table can have multiple production lines for each report plant.
Facility Lines
==========
PLANT
PROD_LINE (pk)
REPORT_PLANT
I was getting one record for each production line|||I still don't get it.
I would definetly figure out why, so as to not use DISTINCT as a hack|||Since the table has data like this:
Plant--Prod_Line--Report_Plant
==========================
-1---A---PLANT1
-1---B---PLANT1
-1---C---PLANT1
-2---D---PLANT2
-3---E---PLANT3
My output looks like (without using distinct)
Report_Plant--JAN--FEB--MAR...
========================
PLANT1---10--9--10
PLANT1---10--9--10
PLANT1---10--9--10
PLANT2---17--24--25
PLANT3---8--6--7|||So shouldn't you be grouping?|||I could do a
Group By Report_Plant, Jan, Feb, Mar...
and get the same result. Is there a reason to do that over DISTINCT?|||I could do a
Group By Report_Plant, Jan, Feb, Mar...
and get the same result. Is there a reason to do that over DISTINCT?No.
BTW - you are scannig the table 12 times here. If it chugs along a bit slow you can optimise this:
....
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
....to
PC.MNTHYR BETWEEN StartOfMonth AND EndOfMonth and ditto for the start and end of year. {Assuming it is indexed} this may be quicker.|||No speed issues. Takes less than a second to run. It's part of an overnight job, anyway.
Next problem, though...
This whole thing runs as part of a series of loops. The UPDATE portion of the query looks the same, I plug in a few different queries in the FROM portion,
and change the parameters in the WHERE portion. I guess you'd call it dynamic SQL. By the time I glue it all together, I end up with a string that's 7500 characters long. I'm afraid that when I rewrite the other queries that need it, I'll end up with some that are more than 8000 characters long.
The max length for VARCHAR appears to be 8000. So now what?|||Since the table has data like this:
Plant--Prod_Line--Report_Plant
==========================
-1---A---PLANT1
-1---B---PLANT1
-1---C---PLANT1
-2---D---PLANT2
-3---E---PLANT3
My output looks like (without using distinct)
Report_Plant--JAN--FEB--MAR...
========================
PLANT1---10--9--10
PLANT1---10--9--10
PLANT1---10--9--10
PLANT2---17--24--25
PLANT3---8--6--7
I don't see how. You are GROUPING on Report_Plan
Post the Query you finished, DDL and sample data|||PC.MNTHYR BETWEEN StartOfMonth AND EndOfMonth and ditto for the start and end of year. {Assuming it is indexed} this may be quicker.
How do you figure???|||Split it into multiple statements and then concat when you execute them?
Declare @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SET @.a = "SELECT dave "
SET @.b = "FROM thetable "
SET @.c = "WHERE name='dave'"
EXEC(@.a + @.b + @.c)|||Split it into multiple statements and then concat when you execute them?
Declare @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SET @.a = "SELECT dave "
SET @.b = "FROM thetable "
SET @.c = "WHERE name='dave'"
EXEC(@.a + @.b + @.c)
Because it doesn't work that way
DECLARE @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SELECT @.a = REPLICATE('a',8000), @.b = REPLICATE('b',8000), @.c = REPLICATE('c',8000)
SELECT LEN(@.a+@.b+@.c)
SELECT @.a+@.b+@.c|||I'm afraid to say I have no idea what replicate does... I've used the above method before when I was writing dynamic SQL (ok, it was at the beginning of my SQL learning ;)) and if you use the exec line, the concats are fine.|||How do you figure???Wiv me noggin govnor.
MONTH(PC.MNTHYR) = 1
is destined to scan.
PC.MNTHYR BETWEEN '20070201' AND '20070228'
is not. It might do but if there is sufficient data it will not.
I assume MNTHYR has no time element otherwise that needs covering too.|||Because it doesn't work that way
actually, EXEC(@.a + @.b) does work that way.
see: http://www.sommarskog.se/dynamic_sql.html#EXEC4
afaik this is the only advantage that EXEC has over sp_executesql when it comes to dynamic sql|||afaik this is the only advantage that EXEC has over sp_executesql when it comes to dynamic sqlUnless you are attempting to inject sql rather than, er, eject it of course :)|||Wiv me noggin govnor.
ummmmmm...did you execute the sample code?|||ummmmmm...did you execute the sample code?
Try it yourself :)
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'date_t') BEGIN
DROP TABLE dbo.date_t
END
CREATE TABLE dbo.date_t
(
date_c SMALLDATETIME
, CONSTRAINT pk_date_t PRIMARY KEY NONCLUSTERED (date_c) WITH (FILLFACTOR = 100)
)
GO
INSERT dbo.date_t
SELECT DATEADD(d, number, 0)
FROM dbo.numbers
GO
SET showplan_all on
GO
SELECT *
FROM dbo.date_t
WHERE month(date_c) = 2
AND year(date_c) = 1901
SELECT *
FROM dbo.date_t
WHERE date_c BETWEEN '19010201' AND '19010228'
GO
SET showplan_all off
go
SELECT *
FROM dbo.date_t
WHERE month(date_c) = 2
AND year(date_c) = 1901 1 1 0 NULL NULL 1 NULL 2048 NULL NULL NULL 0.1531494 NULL NULL SELECT 0 NULL
|--Table Scan(OBJECT:([dh_test_db].[dbo].[date_t]), WHERE:(datepart(month,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(2) AND datepart(year,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(1901))) 1 2 1 Table Scan Table Scan OBJECT:([dh_test_db].[dbo].[date_t]), WHERE:(datepart(month,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(2) AND datepart(year,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(1901)) [dh_test_db].[dbo].[date_t].[date_c] 2048 0.08090278 0.0722466 11 0.1531494 [dh_test_db].[dbo].[date_t].[date_c] NULL PLAN_ROW 0 1
SELECT *
FROM dbo.date_t
WHERE date_c BETWEEN '19010201' AND '19010228' 2 3 0 NULL NULL 2 NULL 28.86587 NULL NULL NULL 0.003313752 NULL NULL SELECT 0 NULL
|--Index Seek(OBJECT:([dh_test_db].[dbo].[date_t].[pk_date_t]), SEEK:([dh_test_db].[dbo].[date_t].[date_c] >= CONVERT_IMPLICIT(smalldatetime,[@.1],0) AND [dh_test_db].[dbo].[date_t].[date_c] <= CONVERT_IMPLICIT(smalldatetime,[@.2],0)) ORDERED FORWARD) 2 4 3 Index Seek Index Seek OBJECT:([dh_test_db].[dbo].[date_t].[pk_date_t]), SEEK:([dh_test_db].[dbo].[date_t].[date_c] >= CONVERT_IMPLICIT(smalldatetime,[@.1],0) AND [dh_test_db].[dbo].[date_t].[date_c] <= CONVERT_IMPLICIT(smalldatetime,[@.2],0)) ORDERED FORWARD [dh_test_db].[dbo].[date_t].[date_c] 28.86587 0.003125 0.0001887525 11 0.003313752 [dh_test_db].[dbo].[date_t].[date_c] NULL PLAN_ROW 0 1
Any suggestions on how to improve this?
SELECT FL.REPORT_PLANT,
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
FROM PRODUCTION_CMPLT PC INNER JOIN
FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND
PC.PROD_LINE = FL.PROD_LINE INNER JOIN
PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANTSELECT [JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END)
the problem lies in your second CASE expression yielding 1 when the sum is not =1. What you want is for the whole expression to yield 0 if that =1...
I think this might be the what you want
SELECT
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN (PC.TONS * 2000 / PM.EA_WT) / (PC.HOURS * 60) ELSE 0 END)|||Or...
...ELSE 1|||(select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm
Or
Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
And Pm. Ea_wt <> 0
??|||re: georgev
ahhh... But I need Sum(Units)/Sum(Minutes), which is not the same as
Sum(Units/Minutes)
Changing to Else 1 yields a "false positive" in the off months, and throws off my numbers.|||(select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm
Or
Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
And Pm. Ea_wt <> 0
??
I don't have a problem with ea_wt. There is a constraint on the table that doesn't allow that field to be equal to 0.|||I think you want to do this
SELECT FL.REPORT_PLANT,
, [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT
UNION ALL
SELECT FL.REPORT_PLANT,
, [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 2
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT
UNION ALL
..ect|||That looks promising. I'll give it a go.|||Actually, with that one you will need a labl;e for each union to know what month it is...but you could do this as columns like you have
SELECT *
FROM FACILITY_LINES xxx
INNER JOIN (
SELECT FL.REPORT_PLANT
, [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS JAN
ON xxx.REPORT_PLANT = JAN.REPORT_PLANT
INNER JOIN (
SELECT FL.REPORT_PLANT
, [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 2
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS FEB
ON xxx.REPORT_PLANT = FEB.REPORT_PLANT
..ect|||For some reason -that I can't figure out- I had to use SELECT DISTINCT.
I also had to use a LEFT JOIN for each subquery...
SELECT DISTINCT FL.REPORT_PLANT, JAN, FEB, MAR, APR, MAY
FROM FACILITY_LINES FL
LEFT OUTER JOIN (
SELECT FL.REPORT_PLANT, [JAN] =sum( PC.TONS * 2000 /PM.EA_WT)/sum(PC.HOURS*60)
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS JAN
ON FL.REPORT_PLANT = JAN.REPORT_PLANT
LEFT OUTER JOIN (
SELECT FL.REPORT_PLANT
, [FEB] = SUM(PC.TONS * 2000 /PM.EA_WT)/sUM(PC.HOURS*60)
FROM PRODUCTION_CMPLT PC
INNER JOIN FACILITY_LINES FL
ON PC.MANUF_SITE = FL.MANUF_SITE
AND PC.PROD_LINE = FL.PROD_LINE
INNER JOIN PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 2
AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT) AS FEB
ON FL.REPORT_PLANT = FEB.REPORT_PLANT
...
That's exactly what I needed.
Thanks
Mark|||Why...what did the output look like without distinct?|||The Facility Lines table can have multiple production lines for each report plant.
Facility Lines
==========
PLANT
PROD_LINE (pk)
REPORT_PLANT
I was getting one record for each production line|||I still don't get it.
I would definetly figure out why, so as to not use DISTINCT as a hack|||Since the table has data like this:
Plant--Prod_Line--Report_Plant
==========================
-1---A---PLANT1
-1---B---PLANT1
-1---C---PLANT1
-2---D---PLANT2
-3---E---PLANT3
My output looks like (without using distinct)
Report_Plant--JAN--FEB--MAR...
========================
PLANT1---10--9--10
PLANT1---10--9--10
PLANT1---10--9--10
PLANT2---17--24--25
PLANT3---8--6--7|||So shouldn't you be grouping?|||I could do a
Group By Report_Plant, Jan, Feb, Mar...
and get the same result. Is there a reason to do that over DISTINCT?|||I could do a
Group By Report_Plant, Jan, Feb, Mar...
and get the same result. Is there a reason to do that over DISTINCT?No.
BTW - you are scannig the table 12 times here. If it chugs along a bit slow you can optimise this:
....
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND MONTH(PC.MNTHYR) = 1
....to
PC.MNTHYR BETWEEN StartOfMonth AND EndOfMonth and ditto for the start and end of year. {Assuming it is indexed} this may be quicker.|||No speed issues. Takes less than a second to run. It's part of an overnight job, anyway.
Next problem, though...
This whole thing runs as part of a series of loops. The UPDATE portion of the query looks the same, I plug in a few different queries in the FROM portion,
and change the parameters in the WHERE portion. I guess you'd call it dynamic SQL. By the time I glue it all together, I end up with a string that's 7500 characters long. I'm afraid that when I rewrite the other queries that need it, I'll end up with some that are more than 8000 characters long.
The max length for VARCHAR appears to be 8000. So now what?|||Since the table has data like this:
Plant--Prod_Line--Report_Plant
==========================
-1---A---PLANT1
-1---B---PLANT1
-1---C---PLANT1
-2---D---PLANT2
-3---E---PLANT3
My output looks like (without using distinct)
Report_Plant--JAN--FEB--MAR...
========================
PLANT1---10--9--10
PLANT1---10--9--10
PLANT1---10--9--10
PLANT2---17--24--25
PLANT3---8--6--7
I don't see how. You are GROUPING on Report_Plan
Post the Query you finished, DDL and sample data|||PC.MNTHYR BETWEEN StartOfMonth AND EndOfMonth and ditto for the start and end of year. {Assuming it is indexed} this may be quicker.
How do you figure???|||Split it into multiple statements and then concat when you execute them?
Declare @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SET @.a = "SELECT dave "
SET @.b = "FROM thetable "
SET @.c = "WHERE name='dave'"
EXEC(@.a + @.b + @.c)|||Split it into multiple statements and then concat when you execute them?
Declare @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SET @.a = "SELECT dave "
SET @.b = "FROM thetable "
SET @.c = "WHERE name='dave'"
EXEC(@.a + @.b + @.c)
Because it doesn't work that way
DECLARE @.a varchar(8000), @.b varchar(8000), @.c varchar(8000)
SELECT @.a = REPLICATE('a',8000), @.b = REPLICATE('b',8000), @.c = REPLICATE('c',8000)
SELECT LEN(@.a+@.b+@.c)
SELECT @.a+@.b+@.c|||I'm afraid to say I have no idea what replicate does... I've used the above method before when I was writing dynamic SQL (ok, it was at the beginning of my SQL learning ;)) and if you use the exec line, the concats are fine.|||How do you figure???Wiv me noggin govnor.
MONTH(PC.MNTHYR) = 1
is destined to scan.
PC.MNTHYR BETWEEN '20070201' AND '20070228'
is not. It might do but if there is sufficient data it will not.
I assume MNTHYR has no time element otherwise that needs covering too.|||Because it doesn't work that way
actually, EXEC(@.a + @.b) does work that way.
see: http://www.sommarskog.se/dynamic_sql.html#EXEC4
afaik this is the only advantage that EXEC has over sp_executesql when it comes to dynamic sql|||afaik this is the only advantage that EXEC has over sp_executesql when it comes to dynamic sqlUnless you are attempting to inject sql rather than, er, eject it of course :)|||Wiv me noggin govnor.
ummmmmm...did you execute the sample code?|||ummmmmm...did you execute the sample code?
Try it yourself :)
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'date_t') BEGIN
DROP TABLE dbo.date_t
END
CREATE TABLE dbo.date_t
(
date_c SMALLDATETIME
, CONSTRAINT pk_date_t PRIMARY KEY NONCLUSTERED (date_c) WITH (FILLFACTOR = 100)
)
GO
INSERT dbo.date_t
SELECT DATEADD(d, number, 0)
FROM dbo.numbers
GO
SET showplan_all on
GO
SELECT *
FROM dbo.date_t
WHERE month(date_c) = 2
AND year(date_c) = 1901
SELECT *
FROM dbo.date_t
WHERE date_c BETWEEN '19010201' AND '19010228'
GO
SET showplan_all off
go
SELECT *
FROM dbo.date_t
WHERE month(date_c) = 2
AND year(date_c) = 1901 1 1 0 NULL NULL 1 NULL 2048 NULL NULL NULL 0.1531494 NULL NULL SELECT 0 NULL
|--Table Scan(OBJECT:([dh_test_db].[dbo].[date_t]), WHERE:(datepart(month,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(2) AND datepart(year,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(1901))) 1 2 1 Table Scan Table Scan OBJECT:([dh_test_db].[dbo].[date_t]), WHERE:(datepart(month,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(2) AND datepart(year,CONVERT_IMPLICIT(datetime,[dh_test_db].[dbo].[date_t].[date_c],0))=(1901)) [dh_test_db].[dbo].[date_t].[date_c] 2048 0.08090278 0.0722466 11 0.1531494 [dh_test_db].[dbo].[date_t].[date_c] NULL PLAN_ROW 0 1
SELECT *
FROM dbo.date_t
WHERE date_c BETWEEN '19010201' AND '19010228' 2 3 0 NULL NULL 2 NULL 28.86587 NULL NULL NULL 0.003313752 NULL NULL SELECT 0 NULL
|--Index Seek(OBJECT:([dh_test_db].[dbo].[date_t].[pk_date_t]), SEEK:([dh_test_db].[dbo].[date_t].[date_c] >= CONVERT_IMPLICIT(smalldatetime,[@.1],0) AND [dh_test_db].[dbo].[date_t].[date_c] <= CONVERT_IMPLICIT(smalldatetime,[@.2],0)) ORDERED FORWARD) 2 4 3 Index Seek Index Seek OBJECT:([dh_test_db].[dbo].[date_t].[pk_date_t]), SEEK:([dh_test_db].[dbo].[date_t].[date_c] >= CONVERT_IMPLICIT(smalldatetime,[@.1],0) AND [dh_test_db].[dbo].[date_t].[date_c] <= CONVERT_IMPLICIT(smalldatetime,[@.2],0)) ORDERED FORWARD [dh_test_db].[dbo].[date_t].[date_c] 28.86587 0.003125 0.0001887525 11 0.003313752 [dh_test_db].[dbo].[date_t].[date_c] NULL PLAN_ROW 0 1
Friday, February 17, 2012
Effect of Shrinking a DB
Just wondering, other than the effect of making the database smaller, how does the shrink call affect actual performance? Should I expect an performance increase after it is done? Thanks.
~piroGenerally all shrink does it reduce the size of the database files, no real performance gain as far as I know. You have to ask your self why did they grow to that size in the first place, before shrinking them as it is a performance hit when they have to grow again.|||Thank you for replying.
So there really is no performance decrease when I shrink the database. The performance hit will come from db growth, which is normal. Do you know of any other commands or functions that are similar to Access's compact command?
~piro|||Not sure what Access's compact command does, but DBCC SHRINKDATABASE can move all data to contiguous pages and remove any remaining unused space. This has the potential of reducing I/O since you would need to read fewer pages to access all data.|||Good point, forgot about the moving of data pages.
~piroGenerally all shrink does it reduce the size of the database files, no real performance gain as far as I know. You have to ask your self why did they grow to that size in the first place, before shrinking them as it is a performance hit when they have to grow again.|||Thank you for replying.
So there really is no performance decrease when I shrink the database. The performance hit will come from db growth, which is normal. Do you know of any other commands or functions that are similar to Access's compact command?
~piro|||Not sure what Access's compact command does, but DBCC SHRINKDATABASE can move all data to contiguous pages and remove any remaining unused space. This has the potential of reducing I/O since you would need to read fewer pages to access all data.|||Good point, forgot about the moving of data pages.
Subscribe to:
Posts (Atom)