I have this bug when tring to export a report to pdf...
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Index and length must refer to a location within the string. Parameter name:
length
Any ideas? This only happens with 1 report, all other reports work fine...I finally found the cause of the bug if anyone is interested..
In the rdl code of the report I saw that there were 2 <language> parts, the
<Report> had a langauge setting of en-IRL but there was a textbox somewhere
else on the report that also had its own <language> setting which was
different to the report setting. I removed the <language> setting from the
textbox and redeplyed the report and now the report exports to PDF.
"NH" wrote:
> I have this bug when tring to export a report to pdf...
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index and length must refer to a location within the string. Parameter name:
> length
>
> Any ideas? This only happens with 1 report, all other reports work fine...
Showing posts with label type. Show all posts
Showing posts with label type. Show all posts
Monday, March 19, 2012
Bug using FOR XML AUTO with columns with type char(1)
Hello,
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, Nils
CHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegr oups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, Nils
CHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegr oups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>
Bug using FOR XML AUTO with columns with type char(1)
Hello,
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, NilsCHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegroups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, NilsCHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegroups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>
Wednesday, March 7, 2012
buflatch error
Under what circumstances do we get this message?
Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec. on
latch 8130f2c0. Not a BUF latch.My first port of call will be to check H/W(controller, disk drives for bad
sectors)
switch on perfmon(current disk read/write queue length) see under what
circumstances this is happening(heavy read/write)
HTH
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
on
> latch 8130f2c0. Not a BUF latch.|||The output of sysprocesses might give some more information.
Here is an article that might be useful for you:
http://support.microsoft.com/?kbid=822101
A latch is a short-term lightweight synchronization object. The following
list describes the different types of latches:
? Non-buffer (Non-BUF) latch: The non-buffer latches provide
synchronization services to in-memory data structures or provide re-entrancy
protection for concurrency-sensitive code lines. These latches can be used
for a variety of things, but they are not used to synchronize access to
buffer pages.
This message indicates longer than expected thread wait in the system. If
you see a drop of performance due to this, please consider contact microsoft
support. The problem might have been fixed by existing hotfix or QFE
http://support.microsoft.com/kb/309093/EN-US/
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> on
> latch 8130f2c0. Not a BUF latch.|||Thanks Wei. It helps
"wei xiao" wrote:
> The output of sysprocesses might give some more information.
> Here is an article that might be useful for you:
> http://support.microsoft.com/?kbid=822101
> A latch is a short-term lightweight synchronization object. The following
> list describes the different types of latches:
> ? Non-buffer (Non-BUF) latch: The non-buffer latches provide
> synchronization services to in-memory data structures or provide re-entrancy
> protection for concurrency-sensitive code lines. These latches can be used
> for a variety of things, but they are not used to synchronize access to
> buffer pages.
>
> This message indicates longer than expected thread wait in the system. If
> you see a drop of performance due to this, please consider contact microsoft
> support. The problem might have been fixed by existing hotfix or QFE
> http://support.microsoft.com/kb/309093/EN-US/
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bharath" <Bharath@.discussions.microsoft.com> wrote in message
> news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> > Under what circumstances do we get this message?
> >
> > Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> > 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> > on
> > latch 8130f2c0. Not a BUF latch.
>
>
Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec. on
latch 8130f2c0. Not a BUF latch.My first port of call will be to check H/W(controller, disk drives for bad
sectors)
switch on perfmon(current disk read/write queue length) see under what
circumstances this is happening(heavy read/write)
HTH
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
on
> latch 8130f2c0. Not a BUF latch.|||The output of sysprocesses might give some more information.
Here is an article that might be useful for you:
http://support.microsoft.com/?kbid=822101
A latch is a short-term lightweight synchronization object. The following
list describes the different types of latches:
? Non-buffer (Non-BUF) latch: The non-buffer latches provide
synchronization services to in-memory data structures or provide re-entrancy
protection for concurrency-sensitive code lines. These latches can be used
for a variety of things, but they are not used to synchronize access to
buffer pages.
This message indicates longer than expected thread wait in the system. If
you see a drop of performance due to this, please consider contact microsoft
support. The problem might have been fixed by existing hotfix or QFE
http://support.microsoft.com/kb/309093/EN-US/
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> on
> latch 8130f2c0. Not a BUF latch.|||Thanks Wei. It helps
"wei xiao" wrote:
> The output of sysprocesses might give some more information.
> Here is an article that might be useful for you:
> http://support.microsoft.com/?kbid=822101
> A latch is a short-term lightweight synchronization object. The following
> list describes the different types of latches:
> ? Non-buffer (Non-BUF) latch: The non-buffer latches provide
> synchronization services to in-memory data structures or provide re-entrancy
> protection for concurrency-sensitive code lines. These latches can be used
> for a variety of things, but they are not used to synchronize access to
> buffer pages.
>
> This message indicates longer than expected thread wait in the system. If
> you see a drop of performance due to this, please consider contact microsoft
> support. The problem might have been fixed by existing hotfix or QFE
> http://support.microsoft.com/kb/309093/EN-US/
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bharath" <Bharath@.discussions.microsoft.com> wrote in message
> news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> > Under what circumstances do we get this message?
> >
> > Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> > 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> > on
> > latch 8130f2c0. Not a BUF latch.
>
>
buflatch error
Under what circumstances do we get this message?
Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec. on
latch 8130f2c0. Not a BUF latch.
My first port of call will be to check H/W(controller, disk drives for bad
sectors)
switch on perfmon(current disk read/write queue length) see under what
circumstances this is happening(heavy read/write)
HTH
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
on
> latch 8130f2c0. Not a BUF latch.
|||The output of sysprocesses might give some more information.
Here is an article that might be useful for you:
http://support.microsoft.com/?kbid=822101
A latch is a short-term lightweight synchronization object. The following
list describes the different types of latches:
? Non-buffer (Non-BUF) latch: The non-buffer latches provide
synchronization services to in-memory data structures or provide re-entrancy
protection for concurrency-sensitive code lines. These latches can be used
for a variety of things, but they are not used to synchronize access to
buffer pages.
This message indicates longer than expected thread wait in the system. If
you see a drop of performance due to this, please consider contact microsoft
support. The problem might have been fixed by existing hotfix or QFE
http://support.microsoft.com/kb/309093/EN-US/
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> on
> latch 8130f2c0. Not a BUF latch.
|||Thanks Wei. It helps
"wei xiao" wrote:
> The output of sysprocesses might give some more information.
> Here is an article that might be useful for you:
> http://support.microsoft.com/?kbid=822101
> A latch is a short-term lightweight synchronization object. The following
> list describes the different types of latches:
> ? Non-buffer (Non-BUF) latch: The non-buffer latches provide
> synchronization services to in-memory data structures or provide re-entrancy
> protection for concurrency-sensitive code lines. These latches can be used
> for a variety of things, but they are not used to synchronize access to
> buffer pages.
>
> This message indicates longer than expected thread wait in the system. If
> you see a drop of performance due to this, please consider contact microsoft
> support. The problem might have been fixed by existing hotfix or QFE
> http://support.microsoft.com/kb/309093/EN-US/
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bharath" <Bharath@.discussions.microsoft.com> wrote in message
> news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
>
>
Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec. on
latch 8130f2c0. Not a BUF latch.
My first port of call will be to check H/W(controller, disk drives for bad
sectors)
switch on perfmon(current disk read/write queue length) see under what
circumstances this is happening(heavy read/write)
HTH
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
on
> latch 8130f2c0. Not a BUF latch.
|||The output of sysprocesses might give some more information.
Here is an article that might be useful for you:
http://support.microsoft.com/?kbid=822101
A latch is a short-term lightweight synchronization object. The following
list describes the different types of latches:
? Non-buffer (Non-BUF) latch: The non-buffer latches provide
synchronization services to in-memory data structures or provide re-entrancy
protection for concurrency-sensitive code lines. These latches can be used
for a variety of things, but they are not used to synchronize access to
buffer pages.
This message indicates longer than expected thread wait in the system. If
you see a drop of performance due to this, please consider contact microsoft
support. The problem might have been fixed by existing hotfix or QFE
http://support.microsoft.com/kb/309093/EN-US/
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> on
> latch 8130f2c0. Not a BUF latch.
|||Thanks Wei. It helps
"wei xiao" wrote:
> The output of sysprocesses might give some more information.
> Here is an article that might be useful for you:
> http://support.microsoft.com/?kbid=822101
> A latch is a short-term lightweight synchronization object. The following
> list describes the different types of latches:
> ? Non-buffer (Non-BUF) latch: The non-buffer latches provide
> synchronization services to in-memory data structures or provide re-entrancy
> protection for concurrency-sensitive code lines. These latches can be used
> for a variety of things, but they are not used to synchronize access to
> buffer pages.
>
> This message indicates longer than expected thread wait in the system. If
> you see a drop of performance due to this, please consider contact microsoft
> support. The problem might have been fixed by existing hotfix or QFE
> http://support.microsoft.com/kb/309093/EN-US/
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bharath" <Bharath@.discussions.microsoft.com> wrote in message
> news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
>
>
buflatch error
Under what circumstances do we get this message?
Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec. on
latch 8130f2c0. Not a BUF latch.My first port of call will be to check H/W(controller, disk drives for bad
sectors)
switch on perfmon(current disk read/write queue length) see under what
circumstances this is happening(heavy read/write)
HTH
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
on
> latch 8130f2c0. Not a BUF latch.|||The output of sysprocesses might give some more information.
Here is an article that might be useful for you:
http://support.microsoft.com/?kbid=822101
A latch is a short-term lightweight synchronization object. The following
list describes the different types of latches:
? Non-buffer (Non-BUF) latch: The non-buffer latches provide
synchronization services to in-memory data structures or provide re-entrancy
protection for concurrency-sensitive code lines. These latches can be used
for a variety of things, but they are not used to synchronize access to
buffer pages.
This message indicates longer than expected thread wait in the system. If
you see a drop of performance due to this, please consider contact microsoft
support. The problem might have been fixed by existing hotfix or QFE
http://support.microsoft.com/kb/309093/EN-US/
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> on
> latch 8130f2c0. Not a BUF latch.|||Thanks Wei. It helps
"wei xiao" wrote:
> The output of sysprocesses might give some more information.
> Here is an article that might be useful for you:
> http://support.microsoft.com/?kbid=822101
> A latch is a short-term lightweight synchronization object. The following
> list describes the different types of latches:
> ? Non-buffer (Non-BUF) latch: The non-buffer latches provide
> synchronization services to in-memory data structures or provide re-entran
cy
> protection for concurrency-sensitive code lines. These latches can be used
> for a variety of things, but they are not used to synchronize access to
> buffer pages.
>
> This message indicates longer than expected thread wait in the system. If
> you see a drop of performance due to this, please consider contact microso
ft
> support. The problem might have been fixed by existing hotfix or QFE
> http://support.microsoft.com/kb/309093/EN-US/
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Bharath" <Bharath@.discussions.microsoft.com> wrote in message
> news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
>
>
Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec. on
latch 8130f2c0. Not a BUF latch.My first port of call will be to check H/W(controller, disk drives for bad
sectors)
switch on perfmon(current disk read/write queue length) see under what
circumstances this is happening(heavy read/write)
HTH
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
on
> latch 8130f2c0. Not a BUF latch.|||The output of sysprocesses might give some more information.
Here is an article that might be useful for you:
http://support.microsoft.com/?kbid=822101
A latch is a short-term lightweight synchronization object. The following
list describes the different types of latches:
? Non-buffer (Non-BUF) latch: The non-buffer latches provide
synchronization services to in-memory data structures or provide re-entrancy
protection for concurrency-sensitive code lines. These latches can be used
for a variety of things, but they are not used to synchronize access to
buffer pages.
This message indicates longer than expected thread wait in the system. If
you see a drop of performance due to this, please consider contact microsoft
support. The problem might have been fixed by existing hotfix or QFE
http://support.microsoft.com/kb/309093/EN-US/
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
> Under what circumstances do we get this message?
> Waiting for type 0x2, current count 0x508, current owning EC 0x00000000.
> 2005-03-08 02:49:26.52 spid189 WARNING: EC 6e4d3560, 0 waited 4800 sec.
> on
> latch 8130f2c0. Not a BUF latch.|||Thanks Wei. It helps
"wei xiao" wrote:
> The output of sysprocesses might give some more information.
> Here is an article that might be useful for you:
> http://support.microsoft.com/?kbid=822101
> A latch is a short-term lightweight synchronization object. The following
> list describes the different types of latches:
> ? Non-buffer (Non-BUF) latch: The non-buffer latches provide
> synchronization services to in-memory data structures or provide re-entran
cy
> protection for concurrency-sensitive code lines. These latches can be used
> for a variety of things, but they are not used to synchronize access to
> buffer pages.
>
> This message indicates longer than expected thread wait in the system. If
> you see a drop of performance due to this, please consider contact microso
ft
> support. The problem might have been fixed by existing hotfix or QFE
> http://support.microsoft.com/kb/309093/EN-US/
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Bharath" <Bharath@.discussions.microsoft.com> wrote in message
> news:DFA6BE50-896C-408C-9C6B-16A1EA59645A@.microsoft.com...
>
>
Saturday, February 25, 2012
Buffer Latch error
I have been having the following time out error message on
my production server for a while now.
Waiting for type 0x4, current count 0xa, current owning EC
0x5E0B63C8.
Time out occurred while waiting for buffer latch type 4,bp
0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
The is an sms server that runs SQL2000 sp3 on Windows 2000
sp4. The microsoft suggestion is to apply sp3 - which I
already did when the server was built. Has anyone come
accross this problem and if so, how did you fix? Is
reapplying service pack a good thing to do? Thanks:I've seen this.
MS will probably disagree. But personally I feel that this is a horribly
handled error and perhaps a bug. You don't provide the specific error number
but assuming it's the same thing I've seen on numerous occaisions...
this error often points to a) a server with inadequte IO capacity and/or b)
queries that are inneffecient for one reason or another that are
exacerrbating the IO issue.
Now... I'll accept that a hardware platform and/or query might be slow...
but I do NOT like the fact that the query simply times out. I'd rather let
it run and have warning messages written to the log that indicate a problem
is happening on this spid. Just my two cents...
but anyway... you should probably be looking at IO issues at the server and
query level.
Of course it could be something completely different. There's not enough
info in your mail to know for sure...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"June Spearman" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June
this is a horribly
specific error number
occaisions...
capacity and/or b)
that are
might be slow...
out. I'd rather let
indicate a problem
at the server and
There's not enough
wrote in message
my production server for a while now.
Waiting for type 0x4, current count 0xa, current owning EC
0x5E0B63C8.
Time out occurred while waiting for buffer latch type 4,bp
0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
The is an sms server that runs SQL2000 sp3 on Windows 2000
sp4. The microsoft suggestion is to apply sp3 - which I
already did when the server was built. Has anyone come
accross this problem and if so, how did you fix? Is
reapplying service pack a good thing to do? Thanks:I've seen this.
MS will probably disagree. But personally I feel that this is a horribly
handled error and perhaps a bug. You don't provide the specific error number
but assuming it's the same thing I've seen on numerous occaisions...
this error often points to a) a server with inadequte IO capacity and/or b)
queries that are inneffecient for one reason or another that are
exacerrbating the IO issue.
Now... I'll accept that a hardware platform and/or query might be slow...
but I do NOT like the fact that the query simply times out. I'd rather let
it run and have warning messages written to the log that indicate a problem
is happening on this spid. Just my two cents...
but anyway... you should probably be looking at IO issues at the server and
query level.
Of course it could be something completely different. There's not enough
info in your mail to know for sure...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"June Spearman" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
quote:|||The server runs SMS and every so often during the day it
> I have been having the following time out error message on
> my production server for a while now.
> Waiting for type 0x4, current count 0xa, current owning EC
> 0x5E0B63C8.
> Time out occurred while waiting for buffer latch type 4,bp
> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>
> The is an sms server that runs SQL2000 sp3 on Windows 2000
> sp4. The microsoft suggestion is to apply sp3 - which I
> already did when the server was built. Has anyone come
> accross this problem and if so, how did you fix? Is
> reapplying service pack a good thing to do? Thanks:
>
>
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June
quote:
>--Original Message--
>I've seen this.
>MS will probably disagree. But personally I feel that
this is a horribly
quote:
>handled error and perhaps a bug. You don't provide the
specific error number
quote:
>but assuming it's the same thing I've seen on numerous
occaisions...
quote:
>this error often points to a) a server with inadequte IO
capacity and/or b)
quote:
>queries that are inneffecient for one reason or another
that are
quote:
>exacerrbating the IO issue.
>Now... I'll accept that a hardware platform and/or query
might be slow...
quote:
>but I do NOT like the fact that the query simply times
out. I'd rather let
quote:
>it run and have warning messages written to the log that
indicate a problem
quote:
>is happening on this spid. Just my two cents...
>but anyway... you should probably be looking at IO issues
at the server and
quote:
>query level.
>Of course it could be something completely different.
There's not enough
quote:
>info in your mail to know for sure...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"June Spearman" <anonymous@.discussions.microsoft.com>
wrote in message
quote:
>news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
on[QUOTE]
EC[QUOTE]
4,bp[QUOTE]
2000[QUOTE]
>
>.
>
Buffer Latch error
I have been having the following time out error message on
my production server for a while now.
Waiting for type 0x4, current count 0xa, current owning EC
0x5E0B63C8.
Time out occurred while waiting for buffer latch type 4,bp
0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
The is an sms server that runs SQL2000 sp3 on Windows 2000
sp4. The microsoft suggestion is to apply sp3 - which I
already did when the server was built. Has anyone come
accross this problem and if so, how did you fix? Is
reapplying service pack a good thing to do? Thanks:I've seen this.
MS will probably disagree. But personally I feel that this is a horribly
handled error and perhaps a bug. You don't provide the specific error number
but assuming it's the same thing I've seen on numerous occaisions...
this error often points to a) a server with inadequte IO capacity and/or b)
queries that are inneffecient for one reason or another that are
exacerrbating the IO issue.
Now... I'll accept that a hardware platform and/or query might be slow...
but I do NOT like the fact that the query simply times out. I'd rather let
it run and have warning messages written to the log that indicate a problem
is happening on this spid. Just my two cents...
but anyway... you should probably be looking at IO issues at the server and
query level.
Of course it could be something completely different. There's not enough
info in your mail to know for sure...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"June Spearman" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
> I have been having the following time out error message on
> my production server for a while now.
> Waiting for type 0x4, current count 0xa, current owning EC
> 0x5E0B63C8.
> Time out occurred while waiting for buffer latch type 4,bp
> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>
> The is an sms server that runs SQL2000 sp3 on Windows 2000
> sp4. The microsoft suggestion is to apply sp3 - which I
> already did when the server was built. Has anyone come
> accross this problem and if so, how did you fix? Is
> reapplying service pack a good thing to do? Thanks:
>
>|||The server runs SMS and every so often during the day it
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June
>--Original Message--
>I've seen this.
>MS will probably disagree. But personally I feel that
this is a horribly
>handled error and perhaps a bug. You don't provide the
specific error number
>but assuming it's the same thing I've seen on numerous
occaisions...
>this error often points to a) a server with inadequte IO
capacity and/or b)
>queries that are inneffecient for one reason or another
that are
>exacerrbating the IO issue.
>Now... I'll accept that a hardware platform and/or query
might be slow...
>but I do NOT like the fact that the query simply times
out. I'd rather let
>it run and have warning messages written to the log that
indicate a problem
>is happening on this spid. Just my two cents...
>but anyway... you should probably be looking at IO issues
at the server and
>query level.
>Of course it could be something completely different.
There's not enough
>info in your mail to know for sure...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"June Spearman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
>> I have been having the following time out error message
on
>> my production server for a while now.
>> Waiting for type 0x4, current count 0xa, current owning
EC
>> 0x5E0B63C8.
>> Time out occurred while waiting for buffer latch type
4,bp
>> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
>> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>>
>> The is an sms server that runs SQL2000 sp3 on Windows
2000
>> sp4. The microsoft suggestion is to apply sp3 - which I
>> already did when the server was built. Has anyone come
>> accross this problem and if so, how did you fix? Is
>> reapplying service pack a good thing to do? Thanks:
>>
>
>.
>
my production server for a while now.
Waiting for type 0x4, current count 0xa, current owning EC
0x5E0B63C8.
Time out occurred while waiting for buffer latch type 4,bp
0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
The is an sms server that runs SQL2000 sp3 on Windows 2000
sp4. The microsoft suggestion is to apply sp3 - which I
already did when the server was built. Has anyone come
accross this problem and if so, how did you fix? Is
reapplying service pack a good thing to do? Thanks:I've seen this.
MS will probably disagree. But personally I feel that this is a horribly
handled error and perhaps a bug. You don't provide the specific error number
but assuming it's the same thing I've seen on numerous occaisions...
this error often points to a) a server with inadequte IO capacity and/or b)
queries that are inneffecient for one reason or another that are
exacerrbating the IO issue.
Now... I'll accept that a hardware platform and/or query might be slow...
but I do NOT like the fact that the query simply times out. I'd rather let
it run and have warning messages written to the log that indicate a problem
is happening on this spid. Just my two cents...
but anyway... you should probably be looking at IO issues at the server and
query level.
Of course it could be something completely different. There's not enough
info in your mail to know for sure...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"June Spearman" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
> I have been having the following time out error message on
> my production server for a while now.
> Waiting for type 0x4, current count 0xa, current owning EC
> 0x5E0B63C8.
> Time out occurred while waiting for buffer latch type 4,bp
> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>
> The is an sms server that runs SQL2000 sp3 on Windows 2000
> sp4. The microsoft suggestion is to apply sp3 - which I
> already did when the server was built. Has anyone come
> accross this problem and if so, how did you fix? Is
> reapplying service pack a good thing to do? Thanks:
>
>|||The server runs SMS and every so often during the day it
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June
>--Original Message--
>I've seen this.
>MS will probably disagree. But personally I feel that
this is a horribly
>handled error and perhaps a bug. You don't provide the
specific error number
>but assuming it's the same thing I've seen on numerous
occaisions...
>this error often points to a) a server with inadequte IO
capacity and/or b)
>queries that are inneffecient for one reason or another
that are
>exacerrbating the IO issue.
>Now... I'll accept that a hardware platform and/or query
might be slow...
>but I do NOT like the fact that the query simply times
out. I'd rather let
>it run and have warning messages written to the log that
indicate a problem
>is happening on this spid. Just my two cents...
>but anyway... you should probably be looking at IO issues
at the server and
>query level.
>Of course it could be something completely different.
There's not enough
>info in your mail to know for sure...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"June Spearman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
>> I have been having the following time out error message
on
>> my production server for a while now.
>> Waiting for type 0x4, current count 0xa, current owning
EC
>> 0x5E0B63C8.
>> Time out occurred while waiting for buffer latch type
4,bp
>> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
>> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>>
>> The is an sms server that runs SQL2000 sp3 on Windows
2000
>> sp4. The microsoft suggestion is to apply sp3 - which I
>> already did when the server was built. Has anyone come
>> accross this problem and if so, how did you fix? Is
>> reapplying service pack a good thing to do? Thanks:
>>
>
>.
>
Thursday, February 16, 2012
Bringing text into a database
I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BCThe limit is 8000. Perhaps the wizard is the issue. Can you create the DTS
package manually? It may be that the input row contains a character that's
supposed to be used as a delimiter.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%237EjS3KPGHA.2668@.tk2msftngp13.phx.gbl...
I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BC|||Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the D
TS
> package manually? It may be that the input row contains a character that'
s
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC|||CRLF is the standard row terminator. You may need a hex editor to track
down exactly where it is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23RijHHLPGHA.1760@.TK2MSFTNGP10.phx.gbl...
Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the
> DTS
> package manually? It may be that the input row contains a character
> that's
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BCThe limit is 8000. Perhaps the wizard is the issue. Can you create the DTS
package manually? It may be that the input row contains a character that's
supposed to be used as a delimiter.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%237EjS3KPGHA.2668@.tk2msftngp13.phx.gbl...
I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BC|||Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the D
TS
> package manually? It may be that the input row contains a character that'
s
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC|||CRLF is the standard row terminator. You may need a hex editor to track
down exactly where it is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23RijHHLPGHA.1760@.TK2MSFTNGP10.phx.gbl...
Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the
> DTS
> package manually? It may be that the input row contains a character
> that's
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC
Tuesday, February 14, 2012
Breaking out data from a text field type
In my database there is a text field type that is used to enter street
address. This address could be a few lines long, each line with a
carriage return at the end.
Is there a way to search for these carriage returns and break out what
is in each line seperately?
address. This address could be a few lines long, each line with a
carriage return at the end.
Is there a way to search for these carriage returns and break out what
is in each line seperately?
Thanks.
Mike[posted and mailed, please reply in news]
Mike (mrea@.ohiotravelbag.com) writes:
> In my database there is a text field type that is used to enter street
> address. This address could be a few lines long, each line with a
> carriage return at the end.
> Is there a way to search for these carriage returns and break out what
> is in each line seperately?
Is that really the datatype text? That seems a bit over kill for a street
address. They would very rarely be over 8000 bytes. Or even 4000 if you
are using varchar.
The functions to use are substring and charindex. And char(13) for the
CRs. Or char(13) + char(10) if it's actually CR + LF. charindex does not
handle text beyond the varchar limit, but I don't think this would be
an issue.
You could also do:
SELECT @.adr = adr FROM tbl WHERE ..
SELECT str
FROM iter_charlist_to_table(@.adr, char(13))
ORDER BY listpos
You find this function on
http://www.sommarskog.se/arrays-in-...list-of-strings
Note that if you need to use char(13) + char(10) as delimiter, you
will have to change the function. (And not only the length of delimiter.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 12, 2012
Brain-Dead Newbie - SQL Question
Any help is sincerely appreciated:
I have data in a table that represents the following:
Admin Visit Type Registration Date Discharge Date
D 20050301 20050301
D 20050301 20050301
W 20050301 20050301
E 20050301 20050301
D 20050301 20050302
W 20050301 20050303
W 20050301 20050311
D 20050301 20050301
Patient Type is always and I for the records I want but there are also Patient Types = O which I don't care about..
What I would like to do is accoumlate a counter on the number of Registrations per date as well as Discharges per date.
There can be thousands of registrations per day as well as thousands of discharges per day.
So lets say I want to pass a date parameter to accumulate the total registrations and discharges per day.
I have beat my head against the desk for the last two days because I believe this is a simple query but I just cannot get the results I want - so any help is greatly appreciated.
I have written the following sql but I do not get a sum of the total registrations and discharges and EXPR3 and Expr4 always equal each other which is not the case . For example on 20040301 I have 88 registrations and 17 discharges but I can't ever get the correct totals...
I wrote the following in Query Analyzer - but it does not work and I have went around in circles and have tried so many things I am just frustrated.......
Declare @.Parm_Beg_Date as nvarchar(8)
Set @.Parm_Beg_Date = 20040313
SELECT
Patient_Visit_Result_Master.PVR_Admin_Visit_Type,
Patient_Visit_Result_Master.PVR_Patient_Type,
Patient_Visit_Result_Master.PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Discharge_Date,
Count(Distinct(Patient_Visit_Result_Master.PVR_Registration_Date)) as Expr3,
Count(Distinct(Patient_Visit_Result_Master.PVR_Discharge_Date)) as Expr4
FROM Patient_Visit_Result_Master INNER JOIN
Patient_Visit_Result_Master Patient_Visit_Result_Master_1 ON
Patient_Visit_Result_Master.PVR_Hospital_ID = Patient_Visit_Result_Master_1.PVR_Hospital_ID AND
@.Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Registration_Date as nvarchar(8))or
@.Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Discharge_Date as nvarchar(8))
WHERE (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'E')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @.Parm_Beg_Date)
OR (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @.Parm_Beg_Date)
Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @.Parm_Beg_Date)
Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D')
AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I')
and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @.Parm_Beg_Date)
GROUP BY
Patient_Visit_Result_Master.PVR_Admin_Visit_Type,
Patient_Visit_Result_Master.PVR_Patient_Type,
Patient_Visit_Result_Master.PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Discharge_Date
Order By Patient_Visit_Result_Master.PVR_Admin_Visit_Type
Thanks in advance
Of course, you can break it down even more into smaller temp tables checking for each WHERE clause and the do a UNION into a bigger table and then perform the aggregation.
One thing I noticed.. you are using a DISTINCT along with the COUNT... as a general rule, you dont need to use a DISTINCT when counting records...|||Thank you for our reply!
That is the entire issue - I do not want to create an "temp" table and I have been trying to do everything to avoid that solution - and believe I am no SQL guero!
From 1998 to 2005 there are 16.5 million records based on In patients and Out patients - I know I am close (LOL) somewhere - but where!
I think what I will do- after seroius thought is to write a a "stat file" that contains the values I am looking for and have some kind of trigger to populate accordingly.
|||Here are two possible solutions make your tables UNION compatible and use UNIONALL or use CASE statement, try the link below for CASE statement. In SQL Server to use UNION you must have the same datatypes for all table facing the same direction. Run a search fro UNION operator in SQL Server BOL (books online). Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm|||As far as the DISTINCT clause I became desperate! I have NEVER used the Distinct keyword...
I did not want to do a UNION in that I query the file the second time and this is a HUGE file.
Been thinkin about this problem for a couple of days - so I think I will just write some code to accumulate the stats I need and then query from there!
But, thre still has to be a way to accomplish this in some sort of way - without writing some sort of stat file.
Thank you and best regards,
|||Why do you want to avoid using temp tables? they are a great tool when dealing with complex queries! and it will make your future maintenance work easier too.
by temp tables i do not mean creating a REAL table (that is not necessary) but in-memory tables that you drop after your query is complete, example:
select * into #temp from Customers
will create an in-memory table that will hold all the customers table data.
after you query is complete you execute:
drop table #temp
and thats it.|||Ok - I will try this - thank you.
I did not realize there were temporary tables in that a lot of stuff I have read I have seen that people create "in line" tables where they define all of the fields etceteras - that's why this forum has been great for me ---
Is there any problems when multiple people access the query with using #temp as the temporary table name - or do I use date and time as the table name ?
Can you create multiple temp tables and then do a join on them as well?
Really - thanks - I will try and post back.
Best Regards,
|||
As in the query I explained above, you can create an in-memory temp table on the fly and do not need to specify any of its fields, it will just be a replica of the table you are copying. Nobody can access your temp table outside of the current connection that you established, and yes, you can do JOINS, UNIONS, etc...
That is why, going back to your first point, I would divide the query in multiple steps. Each step will generate a temp table and after checking each step has been fulfilled properly I would join them and perform the rest of operations. Remember you don't need the DISTINCT in the COUNT
"JAVIGUILLEN"
I am really glad I found this forum!!!
Sincere THANKS! You were correct - this is very simple - wished I knew about the #temp tables before - I just don't know after all of the "stinking" searching I have done why I missed this capability?
Anyway, this is what I wrote and works pretty well - after I got messing with the Into - I only inserted the fields I needed in the temp tables instead of the other 60 plus fields that are in the table.
I also threw this into a stored procedure..... This SP will do this for Day, Week, Month, Quarter and Year. Should I consider the ALTER procedure or With RECOMPILE option - I have looked at these two alternatives but don't really understand YET...
This SQL stuff is pretty cool and I am excited...I am sure once you get pretty good you can do all kinds of magical stuff.
Declare @.Parm_Beg_Date as nvarchar(8)
Declare @.Parm_Hospital as nvarchar(10)
/***** Accumulate Total Registrations for the Period Passeds as @.Parm_Beg_Date *****/
SELECT
Patient_Visit_Result_Master.PVR_Hospital_ID,
Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Ward_ID,
Count(1) AS Registrations
INTO #Temp
FROM Patient_Visit_Result_Master
INNER JOIN
Hospital_Ward_Master
ON Patient_Visit_Result_Master.PVR_Hospital_ID = Hospital_Ward_Master.HWM_Hospital_ID
AND Patient_Visit_Result_Master.PVR_Ward_ID = Hospital_Ward_Master.HWM_Ward_ID
WHERE Patient_Visit_Result_Master.PVR_Hospital_ID = @.Parm_Hospital
AND Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Registration_Date AS nvarchar(8))
OR Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Registration_Date AS nvarchar(8))
GROUP BY
Patient_Visit_Result_Master.PVR_Hospital_ID,
Patient_Visit_Result_Master.PVR_Registration_Date,
Patient_Visit_Result_Master.PVR_Ward_ID
/***** Accumulte Total Discharges for the Period Passed as @.Parm_Beg_Date
SELECT
Patient_Visit_Result_Master.PVR_Hospital_ID,
Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) as PVR_Discharge_Date,
Patient_Visit_Result_Master.PVR_Ward_ID,
Count(1) AS Discharges
INTO #Temp1
FROM Patient_Visit_Result_Master
WHERE Patient_Visit_Result_Master.PVR_Hospital_ID = @.Parm_Hospital
AND Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Discharge_Date AS nvarchar(8))
OR Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D'
AND Patient_Visit_Result_Master.PVR_Patient_Type = 'I'
AND @.Parm_Beg_Date = Cast(Patient_Visit_Result_Master.PVR_Discharge_Date AS nvarchar(8))
GROUP BY
Patient_Visit_Result_Master.PVR_Hospital_ID,
Patient_Visit_Result_Master.PVR_Ward_ID,
Patient_Visit_Result_Master.PVR_Discharge_Date
/***** Join Total Registrations and Total Discharges for the Period Passed and Cast the fields the Report Data Set is Expecting ****/
SELECT
#Temp.PVR_Hospital_ID as PVR_Hospital_ID,
Cast(#Temp.PVR_Registration_Date as nvarchar (8))PVR_Registration_Date,
Cast(#Temp1.PVR_Discharge_Date as nvarchar(8)) PVR_Discharge_Date,
#Temp.PVR_Ward_ID as PVR_Ward_ID,
#Temp.Registrations + #Temp1.Discharges AS Expr1,
#Temp.Registrations + #Temp1.Discharges AS Expr2,
#Temp.Registrations as Expr3,
#Temp1.Discharges as Expr4,
Hospital_Ward_Master.HWM_Ward_Name,
CAST(@.Parm_Beg_Date AS nvarchar(8)) AS wrkdate,
wrkdate as LabelDescr1,
' ' as LabelDescr2,
'By Day' as LabelDescr
FROM #Temp
INNER JOIN
#Temp1
ON #Temp.PVR_Hospital_ID = #Temp1.PVR_Hospital_ID
INNER JOIN
Hospital_Ward_Master ON
#Temp.PVR_Ward_ID = Hospital_Ward_Master.HWM_Ward_ID
Group By
#Temp.PVR_Hospital_ID,
#Temp.PVR_Ward_ID,
Hospital_Ward_Master.HWM_Ward_Name,
PVR_Registration_Date,
PVR_Discharge_Date,
#Temp.Registrations,
#Temp1.Discharges
Drop Table #Temp
Drop Table #Temp1
|||I am glad I was able to help :)|||the ALTER functionality is used when you want to modify a stored procedure that already exists in the database.
RECOMPILE forces SQL Server to NOT use any cached version of the stored procedure that might have been created. Its used if you make a change in the code but SQL Server doesnt recognize it because it is using a cached version instead...
Friday, February 10, 2012
Booleon and Numeric Data Type in SQL Server 2000
Hi all,
I wanna know about, r Sql Server 2000 support the Booleon data type, if yes plz tell how can it use...
and also tell about some common Data type.
Thanx in advance
Sajjad
I wanna know about, r Sql Server 2000 support the Booleon data type, if yes plz tell how can it use...
and also tell about some common Data type.
Thanx in advance
Sajjad
Boolean in SQL Server is BIT and NUMERIC is numeric it can be used when you need stable rounding in your calculations when money is giving you precision problems. Run a search for Boolean datatype in SQL Server BOL(books online). Hope this helps.
Kind regards,
Gift Peddie
|||Many thanx for reply,
I got it, but i have another question is that, I wanna store price in database so what datatype is suitable..., and also how can i restrict the user that he place only two character after decimal...
I m using SQL Server 2000...
thanx again
Sajjad
|||Try this link and look at the code when you open the Shopping Cart and click on the stored procs you will see all the tables with the datatypes. If you install it you can use it with your modifications. Hope this helps.
http://asp.net/CommerceStarterKit/docs/docs.htm
Subscribe to:
Posts (Atom)