internal consistency error

I am getting the following error when I am trying to
restore a database/differential backup. Any ideas about
what is going on ? I can not do DBCC CHECKDB on the
production server. I will do it on the backup server if I
can restore it of course.
An internal consistency error occurred. Contact technical
support for assistance. RESTORE DATABASE is terminating
abnormally.
Thanks.
Hi,
Could you please confirm that the SQL Server is up and running and the database in question is online.
And you are unable to run DBCC CHECKDB(<db name>)
On doing so what error you get? On running DBCC CHECKDB does it shows some inconsistency and allocation error on the second last line of output...
Also look for errors in the SQL Server error log for this database.
Regards
Abid
|||Why can't you run checkdb on your production server?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:f56601c43dca$1cce0b50$a501280a@.phx.gbl...
> I am getting the following error when I am trying to
> restore a database/differential backup. Any ideas about
> what is going on ? I can not do DBCC CHECKDB on the
> production server. I will do it on the backup server if I
> can restore it of course.
> An internal consistency error occurred. Contact technical
> support for assistance. RESTORE DATABASE is terminating
> abnormally.
> Thanks.
|||Are you sure that you have a complete, valid .bak file?
|||Production database is heavyly transaction oriented and I
don't want to run into performance issues while all the
users are in. The size of the DB is ~45 GB.
Thanks.

>--Original Message--
>Why can't you run checkdb on your production server?
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Tony" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:f56601c43dca$1cce0b50$a501280a@.phx.gbl...
I[vbcol=seagreen]
technical
>
>.
>
|||Yes I do. Actually, I am getting the error when I am
restoring the Differential backup. And NO, there is no
other backup after the differential backup.
I have the FULL backup from sunday and the differential
backup from last night. Both backups were completed
successfully.

>--Original Message--
>Are you sure that you have a complete, valid .bak file?
>.
>
sql

internal consistency error

I am getting the following error when I am trying to
restore a database/differential backup. Any ideas about
what is going on ' I can not do DBCC CHECKDB on the
production server. I will do it on the backup server if I
can restore it of course.
An internal consistency error occurred. Contact technical
support for assistance. RESTORE DATABASE is terminating
abnormally.
Thanks.Hi,
Could you please confirm that the SQL Server is up and running and the datab
ase in question is online.
And you are unable to run DBCC CHECKDB(<db name> )
On doing so what error you get? On running DBCC CHECKDB does it shows some i
nconsistency and allocation error on the second last line of output...
Also look for errors in the SQL Server error log for this database.
Regards
Abid|||Why can't you run checkdb on your production server?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:f56601c43dca$1cce0b50$a501280a@.phx.gbl...
> I am getting the following error when I am trying to
> restore a database/differential backup. Any ideas about
> what is going on ' I can not do DBCC CHECKDB on the
> production server. I will do it on the backup server if I
> can restore it of course.
> An internal consistency error occurred. Contact technical
> support for assistance. RESTORE DATABASE is terminating
> abnormally.
> Thanks.|||Are you sure that you have a complete, valid .bak file?|||Production database is heavyly transaction oriented and I
don't want to run into performance issues while all the
users are in. The size of the DB is ~45 GB.
Thanks.

>--Original Message--
>Why can't you run checkdb on your production server?
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Tony" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f56601c43dca$1cce0b50$a501280a@.phx.gbl...
I[vbcol=seagreen]
technical[vbcol=seagreen]
>
>.
>|||Yes I do. Actually, I am getting the error when I am
restoring the Differential backup. And NO, there is no
other backup after the differential backup.
I have the FULL backup from sunday and the differential
backup from last night. Both backups were completed
successfully.

>--Original Message--
>Are you sure that you have a complete, valid .bak file?
>.
>

internal consistency error

I am getting the following error when I am trying to
restore a database/differential backup. Any ideas about
what is going on ' I can not do DBCC CHECKDB on the
production server. I will do it on the backup server if I
can restore it of course.
An internal consistency error occurred. Contact technical
support for assistance. RESTORE DATABASE is terminating
abnormally.
Thanks.Why can't you run checkdb on your production server?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:f56601c43dca$1cce0b50$a501280a@.phx.gbl...
> I am getting the following error when I am trying to
> restore a database/differential backup. Any ideas about
> what is going on ' I can not do DBCC CHECKDB on the
> production server. I will do it on the backup server if I
> can restore it of course.
> An internal consistency error occurred. Contact technical
> support for assistance. RESTORE DATABASE is terminating
> abnormally.
> Thanks.|||Are you sure that you have a complete, valid .bak file?|||Production database is heavyly transaction oriented and I
don't want to run into performance issues while all the
users are in. The size of the DB is ~45 GB.
Thanks.
>--Original Message--
>Why can't you run checkdb on your production server?
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Tony" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f56601c43dca$1cce0b50$a501280a@.phx.gbl...
>> I am getting the following error when I am trying to
>> restore a database/differential backup. Any ideas about
>> what is going on ' I can not do DBCC CHECKDB on the
>> production server. I will do it on the backup server if
I
>> can restore it of course.
>> An internal consistency error occurred. Contact
technical
>> support for assistance. RESTORE DATABASE is terminating
>> abnormally.
>> Thanks.
>
>.
>|||Yes I do. Actually, I am getting the error when I am
restoring the Differential backup. And NO, there is no
other backup after the differential backup.
I have the FULL backup from sunday and the differential
backup from last night. Both backups were completed
successfully.
>--Original Message--
>Are you sure that you have a complete, valid .bak file?
>.
>

intermittent failures using web synchronisation

HI

We are using HTTPS anonymous merge subscriptions....

Sometimes when trying to synchonise, we will get the following error messages returned to the subscriber....

The upload message to be sent to Publisher '**thewebserver**' is being generated
The merge process is using Exchange ID '0F65CFCB-AF17-47DC-8D98-493A44C243E0' for this web synchronization session.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent received the following error status and message from the Internet Information Services (IIS) server during Web synchronization: [401 :'Unauthorized']. When troubleshooting, ensure that the Web synchronization settings for the subscription are correct, and increase the internet timeout setting at the Subscriber and the connection timeout at the Web server.

If I then go to a web brower, put in the HTTPS address, it brings up the logon dialog - I put in the admin username and password to confirm the connection and that's fine.

We try and synchronise again, and this time it works - it's as though I have 'woken' it up again and it's happy to play.

Is increasing the timeouts as suggested by the error message the way to go ? If so, where does one set the 'internet timeout setting at the subscriber', and the 'connection timeout at the webserver' ?

thanks

I dont think it is the timeout that is causing this. But anyways,

To increase the timeout on the Client (Merge Agent) use the Merge agent parameter: -InternetTimeout

To increase the timeout on the webserver, use IIS-->WebSites-->DefaultWebSite-->Properties-->ConnectionTimeout

From what you describe, it appears to me that the -InternetLogin may not have permissions to load the replisapi.dll once the process goes away? Would that be the case? This is just a hunch.

Under what context is the IIS running? (specifically the w3wp.exe process)

Is your InternetLogin and the login running the IIS (w3wp) the same?

You could also try other ways so that you can specify the w3wp.exe to never exit out after 20 minutes (I think that is the default one)

|||I had this happen just yesterday in my test environment. I added a new subscriber laptop to the mix and I kept on getting this error. It turns out that I had to visit the replisapi.dll via Internet Explorer and install the SSL certificate. (I am using Self SSL on the test environment) Once I installed the cert the error went away.sql

intermittent failures using web synchronisation

HI

We are using HTTPS anonymous merge subscriptions....

Sometimes when trying to synchonise, we will get the following error messages returned to the subscriber....

The upload message to be sent to Publisher '**thewebserver**' is being generated
The merge process is using Exchange ID '0F65CFCB-AF17-47DC-8D98-493A44C243E0' for this web synchronization session.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent received the following error status and message from the Internet Information Services (IIS) server during Web synchronization: [401 :'Unauthorized']. When troubleshooting, ensure that the Web synchronization settings for the subscription are correct, and increase the internet timeout setting at the Subscriber and the connection timeout at the Web server.

If I then go to a web brower, put in the HTTPS address, it brings up the logon dialog - I put in the admin username and password to confirm the connection and that's fine.

We try and synchronise again, and this time it works - it's as though I have 'woken' it up again and it's happy to play.

Is increasing the timeouts as suggested by the error message the way to go ? If so, where does one set the 'internet timeout setting at the subscriber', and the 'connection timeout at the webserver' ?

thanks

I dont think it is the timeout that is causing this. But anyways,

To increase the timeout on the Client (Merge Agent) use the Merge agent parameter: -InternetTimeout

To increase the timeout on the webserver, use IIS-->WebSites-->DefaultWebSite-->Properties-->ConnectionTimeout

From what you describe, it appears to me that the -InternetLogin may not have permissions to load the replisapi.dll once the process goes away? Would that be the case? This is just a hunch.

Under what context is the IIS running? (specifically the w3wp.exe process)

Is your InternetLogin and the login running the IIS (w3wp) the same?

You could also try other ways so that you can specify the w3wp.exe to never exit out after 20 minutes (I think that is the default one)

|||I had this happen just yesterday in my test environment. I added a new subscriber laptop to the mix and I kept on getting this error. It turns out that I had to visit the replisapi.dll via Internet Explorer and install the SSL certificate. (I am using Self SSL on the test environment) Once I installed the cert the error went away.

intermittent connection problems

We are having an intermittent connection problems with some DTS
packages. We get the following error about 1 out of every 15 days
Login failed for user '(null)'. Reason: Not associated
One DTS package connects to remote SQL servers with a linked server,
another package uses a OLE DB provide connection to connect to a remote
server.
Configuration:
Client network configuration TCP/IP NamePipes
Server network configuration TCP/IP NamePipes
SQLServer 2000 service pack 3
Windows 2000 Server.
Authentication is set for both SQL Server and Windows
Has anyone seen this type of problem or have any troubleshooting advice
thanksHi
If SQL Server can not authenticate the user against the Domain Controller,
it has no option but to deny the connection. So you will see this message.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<scott.yoder@.ngc.com> wrote in message
news:1112822349.200542.189700@.g14g2000cwa.googlegroups.com...
> We are having an intermittent connection problems with some DTS
> packages. We get the following error about 1 out of every 15 days
> Login failed for user '(null)'. Reason: Not associated
> One DTS package connects to remote SQL servers with a linked server,
> another package uses a OLE DB provide connection to connect to a remote
> server.
> Configuration:
> Client network configuration TCP/IP NamePipes
> Server network configuration TCP/IP NamePipes
> SQLServer 2000 service pack 3
> Windows 2000 Server.
> Authentication is set for both SQL Server and Windows
>
> Has anyone seen this type of problem or have any troubleshooting advice
> thanks
>

intermittent connection problems

We are having an intermittent connection problems with some DTS
packages. We get the following error about 1 out of every 15 days
Login failed for user '(null)'. Reason: Not associated
One DTS package connects to remote SQL servers with a linked server,
another package uses a OLE DB provide connection to connect to a remote
server.
Configuration:
Client network configuration TCP/IP NamePipes
Server network configuration TCP/IP NamePipes
SQLServer 2000 service pack 3
Windows 2000 Server.
Authentication is set for both SQL Server and Windows
Has anyone seen this type of problem or have any troubleshooting advice
thanks
Hi
If SQL Server can not authenticate the user against the Domain Controller,
it has no option but to deny the connection. So you will see this message.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<scott.yoder@.ngc.com> wrote in message
news:1112822349.200542.189700@.g14g2000cwa.googlegr oups.com...
> We are having an intermittent connection problems with some DTS
> packages. We get the following error about 1 out of every 15 days
> Login failed for user '(null)'. Reason: Not associated
> One DTS package connects to remote SQL servers with a linked server,
> another package uses a OLE DB provide connection to connect to a remote
> server.
> Configuration:
> Client network configuration TCP/IP NamePipes
> Server network configuration TCP/IP NamePipes
> SQLServer 2000 service pack 3
> Windows 2000 Server.
> Authentication is set for both SQL Server and Windows
>
> Has anyone seen this type of problem or have any troubleshooting advice
> thanks
>
sql

Intermittent Connection Issues With Linked Oracle Server

We have a linked Oracle server connected to our production SQL Server and it
works for the most part. However, we get the following intermittent message
when running a query from query analyzer:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12545: Connect failed because target
host or object does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Retrying the same query usually runs without error.
Any ideas on why these intermittent failures occur?
--
Larry Menzin
American Techsystems Corp.DId you try to tnsping the oracle Server ?
Its a commandline utility brought by oracle and can be used with the syntac
tnsping [TnsnameoftheOracleServer].
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
> We have a linked Oracle server connected to our production SQL Server and
> it
> works for the most part. However, we get the following intermittent
> message
> when running a query from query analyzer:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: ORA-12545: Connect failed because
> target
> host or object does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> Retrying the same query usually runs without error.
> Any ideas on why these intermittent failures occur?
> --
> Larry Menzin
> American Techsystems Corp.|||Tnsping is fine. I can connect via SQLPlus about 2 out of three times. Could
this be a time out issue? It is not a configuration issue since if I retry
the query a few times it works. Perhaps the connection latency over the WAN
causes the problem?
--
Larry Menzin
American Techsystems Corp.
"Jens Sü�meyer" wrote:
> DId you try to tnsping the oracle Server ?
> Its a commandline utility brought by oracle and can be used with the syntac
> tnsping [TnsnameoftheOracleServer].
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
> > We have a linked Oracle server connected to our production SQL Server and
> > it
> > works for the most part. However, we get the following intermittent
> > message
> > when running a query from query analyzer:
> >
> > Server: Msg 7399, Level 16, State 1, Line 1
> > OLE DB provider 'MSDAORA' reported an error.
> > [OLE/DB provider returned message: ORA-12545: Connect failed because
> > target
> > host or object does not exist
> > ]
> > OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> > returned 0x80004005: ].
> >
> > Retrying the same query usually runs without error.
> >
> > Any ideas on why these intermittent failures occur?
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.
>
>|||OK, you didn´t say that this is a WAN connected Oracle System. YOur
description really sounds like a timeout failure. Perhaps if there is a
running connection you should consider increasing the expire_time in
sqlnet.ora
http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...
> Tnsping is fine. I can connect via SQLPlus about 2 out of three times.
> Could
> this be a time out issue? It is not a configuration issue since if I retry
> the query a few times it works. Perhaps the connection latency over the
> WAN
> causes the problem?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Jens Süßmeyer" wrote:
>> DId you try to tnsping the oracle Server ?
>> Its a commandline utility brought by oracle and can be used with the
>> syntac
>> tnsping [TnsnameoftheOracleServer].
>>
>> --
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
>> Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
>> > We have a linked Oracle server connected to our production SQL Server
>> > and
>> > it
>> > works for the most part. However, we get the following intermittent
>> > message
>> > when running a query from query analyzer:
>> >
>> > Server: Msg 7399, Level 16, State 1, Line 1
>> > OLE DB provider 'MSDAORA' reported an error.
>> > [OLE/DB provider returned message: ORA-12545: Connect failed because
>> > target
>> > host or object does not exist
>> > ]
>> > OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>> > returned 0x80004005: ].
>> >
>> > Retrying the same query usually runs without error.
>> >
>> > Any ideas on why these intermittent failures occur?
>> >
>> > --
>> > Larry Menzin
>> > American Techsystems Corp.
>>|||I believe it is an initial connection problem. When SQL Server uses linked
servers does the connection persist? Or does SQL Server make a new connection
every time an OPENQUERY is executed against Oracle? If I connect via SQLPlus
and leave it, the SQL> prompt remains and the connection is persistent. It
seems like SQL Server reconnects with Oracle after a set time period.
--
Larry Menzin
American Techsystems Corp.
"Jens Sü�meyer" wrote:
> OK, you didn´t say that this is a WAN connected Oracle System. YOur
> description really sounds like a timeout failure. Perhaps if there is a
> running connection you should consider increasing the expire_time in
> sqlnet.ora
> http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...
> > Tnsping is fine. I can connect via SQLPlus about 2 out of three times.
> > Could
> > this be a time out issue? It is not a configuration issue since if I retry
> > the query a few times it works. Perhaps the connection latency over the
> > WAN
> > causes the problem?
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> >> DId you try to tnsping the oracle Server ?
> >>
> >> Its a commandline utility brought by oracle and can be used with the
> >> syntac
> >> tnsping [TnsnameoftheOracleServer].
> >>
> >>
> >> --
> >> HTH, Jens Suessmeyer.
> >>
> >> --
> >> http://www.sqlserver2005.de
> >> --
> >> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> >> Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
> >> > We have a linked Oracle server connected to our production SQL Server
> >> > and
> >> > it
> >> > works for the most part. However, we get the following intermittent
> >> > message
> >> > when running a query from query analyzer:
> >> >
> >> > Server: Msg 7399, Level 16, State 1, Line 1
> >> > OLE DB provider 'MSDAORA' reported an error.
> >> > [OLE/DB provider returned message: ORA-12545: Connect failed because
> >> > target
> >> > host or object does not exist
> >> > ]
> >> > OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> >> > returned 0x80004005: ].
> >> >
> >> > Retrying the same query usually runs without error.
> >> >
> >> > Any ideas on why these intermittent failures occur?
> >> >
> >> > --
> >> > Larry Menzin
> >> > American Techsystems Corp.
> >>
> >>
> >>
>
>

Intermittent Connection Issues With Linked Oracle Server

We have a linked Oracle server connected to our production SQL Server and it
works for the most part. However, we get the following intermittent message
when running a query from query analyzer:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12545: Connect failed because tar
get
host or object does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Retrying the same query usually runs without error.
Any ideas on why these intermittent failures occur?
Larry Menzin
American Techsystems Corp.DId you try to tnsping the oracle Server ?
Its a commandline utility brought by oracle and can be used with the syntac
tnsping [TnsnameoftheOracleServer].
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
> We have a linked Oracle server connected to our production SQL Server and
> it
> works for the most part. However, we get the following intermittent
> message
> when running a query from query analyzer:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: ORA-12545: Connect failed because
> target
> host or object does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> Retrying the same query usually runs without error.
> Any ideas on why these intermittent failures occur?
> --
> Larry Menzin
> American Techsystems Corp.|||Tnsping is fine. I can connect via SQLPlus about 2 out of three times. Could
this be a time out issue? It is not a configuration issue since if I retry
the query a few times it works. Perhaps the connection latency over the WAN
causes the problem?
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> DId you try to tnsping the oracle Server ?
> Its a commandline utility brought by oracle and can be used with the synta
c
> tnsping [TnsnameoftheOracleServer].
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
>
>|||OK, you didnt say that this is a WAN connected Oracle System. YOur
description really sounds like a timeout failure. Perhaps if there is a
running connection you should consider increasing the expire_time in
sqlnet.ora
http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...[vbcol=seagreen]
> Tnsping is fine. I can connect via SQLPlus about 2 out of three times.
> Could
> this be a time out issue? It is not a configuration issue since if I retry
> the query a few times it works. Perhaps the connection latency over the
> WAN
> causes the problem?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Jens Smeyer" wrote:
>|||I believe it is an initial connection problem. When SQL Server uses linked
servers does the connection persist? Or does SQL Server make a new connectio
n
every time an OPENQUERY is executed against Oracle? If I connect via SQLPlus
and leave it, the SQL> prompt remains and the connection is persistent. It
seems like SQL Server reconnects with Oracle after a set time period.
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> OK, you didn′t say that this is a WAN connected Oracle System. YOur
> description really sounds like a timeout failure. Perhaps if there is a
> running connection you should consider increasing the expire_time in
> sqlnet.ora
> http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...
>
>

Intermittent Connection Issues With Linked Oracle Server

We have a linked Oracle server connected to our production SQL Server and it
works for the most part. However, we get the following intermittent message
when running a query from query analyzer:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12545: Connect failed because target
host or object does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Retrying the same query usually runs without error.
Any ideas on why these intermittent failures occur?
Larry Menzin
American Techsystems Corp.
DId you try to tnsping the oracle Server ?
Its a commandline utility brought by oracle and can be used with the syntac
tnsping [TnsnameoftheOracleServer].
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
> We have a linked Oracle server connected to our production SQL Server and
> it
> works for the most part. However, we get the following intermittent
> message
> when running a query from query analyzer:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: ORA-12545: Connect failed because
> target
> host or object does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> Retrying the same query usually runs without error.
> Any ideas on why these intermittent failures occur?
> --
> Larry Menzin
> American Techsystems Corp.
|||Tnsping is fine. I can connect via SQLPlus about 2 out of three times. Could
this be a time out issue? It is not a configuration issue since if I retry
the query a few times it works. Perhaps the connection latency over the WAN
causes the problem?
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> DId you try to tnsping the oracle Server ?
> Its a commandline utility brought by oracle and can be used with the syntac
> tnsping [TnsnameoftheOracleServer].
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
>
>
|||OK, you didnt say that this is a WAN connected Oracle System. YOur
description really sounds like a timeout failure. Perhaps if there is a
running connection you should consider increasing the expire_time in
sqlnet.ora
http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...[vbcol=seagreen]
> Tnsping is fine. I can connect via SQLPlus about 2 out of three times.
> Could
> this be a time out issue? It is not a configuration issue since if I retry
> the query a few times it works. Perhaps the connection latency over the
> WAN
> causes the problem?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Jens Smeyer" wrote:
|||I believe it is an initial connection problem. When SQL Server uses linked
servers does the connection persist? Or does SQL Server make a new connection
every time an OPENQUERY is executed against Oracle? If I connect via SQLPlus
and leave it, the SQL> prompt remains and the connection is persistent. It
seems like SQL Server reconnects with Oracle after a set time period.
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> OK, you didn′t say that this is a WAN connected Oracle System. YOur
> description really sounds like a timeout failure. Perhaps if there is a
> running connection you should consider increasing the expire_time in
> sqlnet.ora
> http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...
>
>

Intermittant SQL Connection Issue

We currently have a system in production that raises the following error
intermittantly;
"A transport-level error has occurred when sending the request to the
server. (provider: TCP Provider, error: 0 - An existing connection was
forcibly closed by the remote host.)"
The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
SP1.
What could be causing this error, and more importantly how do we resolve it?
Regards
Andrew Teece
Technical Architect, eNateHi Andrew,
Per my understanding, the error was intermittantly thrown by your system in
production environment when it was trying to perform a database operation.
If I have misunderstood, please let me know.
From my research, the issue might be caused by various reasons, such as
broken connections in connection pool, hardware failure, network problems
etc. Also, it might be the known issue which is described in this article:
4.1.2 Connections May Be Forcibly Closed When Running on Windows Server
2003 SP1
http://support.microsoft.com/defaul...kb;EN-US;910228
As it mentioned, to resolve this issue, you may use the regedit.exe utility
to add a new DWORD value named SynAttackProtect to the registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\Tcpip\Parameters\with
value data of 00000000.
If that is not the problem, I recommend that you check if there is a
connection pool in your system and if the connections in the pool have a
timeout value. If so, please set the active connections in the pool with
infinite timeout value.
Since your environment is complex and the issue occurs intermittantly in
production environment, if this issue persists, immediately and effectively
I recommend that you contact Microsoft Customer Support Services ( CSS )
via telephone so that a dedicated Support Professional can assist you
recover the server in a more efficient manner. Please be advised that
contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> We currently have a system in production that raises the following error
> intermittantly;
> "A transport-level error has occurred when sending the request to the
> server. (provider: TCP Provider, error: 0 - An existing connection was
> forcibly closed by the remote host.)"
> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
> SP1.
> What could be causing this error, and more importantly how do we resolve i
t?
> Regards
> Andrew Teece
> Technical Architect, eNate
Check the following
1. Router log for any errors
Routers may support IP Virtual Re-Assembly on all interfaces. It is
turned on by default may cause numerous errors .Disable the option
on all interfaces of routers and try
2. Synattack Registry setting
3. Anti Virus software may be closing the connection
Report back after you solve the problem . It may help others
Thanks
Srinivas|||Thanks for the ideas M A Srinivas
At the moment, I think the most likly is the "2. Synattack Registry setting"
that was also mentioned by Charles Wang.
I think this mostly because we are running on a cluster, and the application
raising the error is running on the same pair of cluster servers. As the
application is running concurrently on both, and is raising the error from
both I doubt a network issue because on at least one of the servers my
understanding is that the connection will go through the tcp/ip layer, but
not onto the physical network.
Obviously I will post back any findings.
Regards
Andrew Teece
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1179827956.238191.166180@.u36g2000prd.googlegroups.com...
> On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> Check the following
> 1. Router log for any errors
> Routers may support IP Virtual Re-Assembly on all interfaces. It is
> turned on by default may cause numerous errors .Disable the option
> on all interfaces of routers and try
> 2. Synattack Registry setting
> 3. Anti Virus software may be closing the connection
> Report back after you solve the problem . It may help others
> Thanks
> Srinivas
>
>
>

Intermittant SQL Connection Issue

We currently have a system in production that raises the following error
intermittantly;
"A transport-level error has occurred when sending the request to the
server. (provider: TCP Provider, error: 0 - An existing connection was
forcibly closed by the remote host.)"
The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
SP1.
What could be causing this error, and more importantly how do we resolve it?
Regards
Andrew Teece
Technical Architect, eNateHi Andrew,
Per my understanding, the error was intermittantly thrown by your system in
production environment when it was trying to perform a database operation.
If I have misunderstood, please let me know.
From my research, the issue might be caused by various reasons, such as
broken connections in connection pool, hardware failure, network problems
etc. Also, it might be the known issue which is described in this article:
4.1.2 Connections May Be Forcibly Closed When Running on Windows Server
2003 SP1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;910228
As it mentioned, to resolve this issue, you may use the regedit.exe utility
to add a new DWORD value named SynAttackProtect to the registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\with
value data of 00000000.
If that is not the problem, I recommend that you check if there is a
connection pool in your system and if the connections in the pool have a
timeout value. If so, please set the active connections in the pool with
infinite timeout value.
Since your environment is complex and the issue occurs intermittantly in
production environment, if this issue persists, immediately and effectively
I recommend that you contact Microsoft Customer Support Services ( CSS )
via telephone so that a dedicated Support Professional can assist you
recover the server in a more efficient manner. Please be advised that
contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> We currently have a system in production that raises the following error
> intermittantly;
> "A transport-level error has occurred when sending the request to the
> server. (provider: TCP Provider, error: 0 - An existing connection was
> forcibly closed by the remote host.)"
> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
> SP1.
> What could be causing this error, and more importantly how do we resolve it?
> Regards
> Andrew Teece
> Technical Architect, eNate
Check the following
1. Router log for any errors
Routers may support IP Virtual Re-Assembly on all interfaces. It is
turned on by default may cause numerous errors .Disable the option
on all interfaces of routers and try
2. Synattack Registry setting
3. Anti Virus software may be closing the connection
Report back after you solve the problem . It may help others
Thanks
Srinivas|||Thanks for the ideas M A Srinivas
At the moment, I think the most likly is the "2. Synattack Registry setting"
that was also mentioned by Charles Wang.
I think this mostly because we are running on a cluster, and the application
raising the error is running on the same pair of cluster servers. As the
application is running concurrently on both, and is raising the error from
both I doubt a network issue because on at least one of the servers my
understanding is that the connection will go through the tcp/ip layer, but
not onto the physical network.
Obviously I will post back any findings.
Regards
Andrew Teece
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1179827956.238191.166180@.u36g2000prd.googlegroups.com...
> On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
>> We currently have a system in production that raises the following error
>> intermittantly;
>> "A transport-level error has occurred when sending the request to the
>> server. (provider: TCP Provider, error: 0 - An existing connection was
>> forcibly closed by the remote host.)"
>> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows
>> 2003
>> SP1.
>> What could be causing this error, and more importantly how do we resolve
>> it?
>> Regards
>> Andrew Teece
>> Technical Architect, eNate
> Check the following
> 1. Router log for any errors
> Routers may support IP Virtual Re-Assembly on all interfaces. It is
> turned on by default may cause numerous errors .Disable the option
> on all interfaces of routers and try
> 2. Synattack Registry setting
> 3. Anti Virus software may be closing the connection
> Report back after you solve the problem . It may help others
> Thanks
> Srinivas
>
>
>

Interesting UPDATE STATEMENT for SQL

This Following statement executes perfectly in SQL2000
but not in SQL7.0 it gives the message
Server: Msg 147, Level 16, State 2, Procedure spTEST1, Line 57
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Create Procedure "spTEST1"
As

--Update Processed Flags
UPDATE tblTransaction
SET TransProcessed = 1
FROM dbo.tblTrustGroupTrust
INNER JOIN dbo.tblTrust ON dbo.tblTrustGroupTrust.TgtTrustID = dbo.tblTrust.TrustID
INNER JOIN dbo.tblTransaction ON dbo.tblTrust.TrustID = dbo.tblTransaction.TransTrustID
INNER JOIN dbo.tblShareholder ON dbo.tblTransaction.TransShID = dbo.tblShareholder.ShID
INNER JOIN dbo.tblTransType ON dbo.tblTransType.TransTypeID = dbo.tblTransaction.TransTypeID
WHERE (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblTransaction.TransProcessed = 0)
AND (dbo.tblShareholder.ShPaymentMethod = 1) AND (dbo.tblShareholder.ShDeceased = 0) AND tblShareholder.ShBankAccount IS NOT NULL
AND EXISTS
(
SELECT dbo.tblTransaction.TransShID
FROM dbo.tblTrustGroupTrust
INNER JOIN dbo.tblTransaction ON dbo.tblTrustGroupTrust.tgtTrustID = dbo.tblTransaction.TransTrustID
WHERE (dbo.tblTransaction.TransProcessed = 0) AND (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblShareholder.SHID = dbo.tblTransaction.TransShID)
GROUP BY dbo.tblTransaction.TransShID
HAVING SUM(tblTransaction.TransAmt) >= 10
)

When using a straight select on this statement in SQL7.0 it works fine:
SELECT tblTransaction.TransProcessed
FROM .....

Can anyone shed some light on this?The aggregate is on the table being updated - although it is in a subquery this is correllated and so still fails the test.

If you can change the having clause to
HAVING SUM(dbo.tblTransaction.TransAmt) >= 10

so that it is using the copy of the table in the subquery then it should work.|||It might be clearer if you use an alias for the subquery table like
tblTransaction t2.|||Thanks, I very nearly tried to add the missing dbo prefix's in but I presumed that would be too trivial and the error was more complicated than something like that.

Obviously SQL2000 is a little more relaxed on this sort of thing, as it works without error.

Interesting SQL Server behaviour

Hi,
The following code describes a problem I'm having when I'm selecting
data via a Case statement.
The data I'm selecting is from a table with 2 varchar columns and 1
int column. When I select this data I get the following error:
Server: Msg245, Level 16,State 1, Line 1
Syntax error converting the varchar values 'A' to a column of datatype
int.
If I cast the int to a varchar it works, but I would prefer not to
cast if possible. SQL server seems to look at all the datatypes in the
case statement and if a int data type appears, it seems to be trying
to insert into a int column. Is this some sort of temp table? Anyone
know why it assigned an int column here?
Any ideas what I can do
Thanks,
Tom
- --
- -- Set up the table
- --
create table Tom
(charcol1 varchar(2)
,charcol2 varchar(2)
,intcol1 int
)
insert into tom
(charcol1
,intcol1
,charcol2
)
values
('A',1,'C')
- ---
- -- Code
- ---
SELECT
CASE numlist.list
WHEN 1 THEN
Tom.charcol1
WHEN 2 THEN
--cast (Tom.intcol1 as varchar(5))
Tom.intcol1
WHEN 3 THEN
Tom.charcol2
END AS result
FROM Tom
CROSS JOIN (select 1 as list
union all
select 2 as list
union all
select 3 as list
) as numlistThis is how CASE work, result from CASE expression is of the same datatype.
Output is determined as
per "datatype precedence" documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Staggly" <tomstagg@.gmail.com> wrote in message
news:e646e15f.0502090622.8a26408@.posting.google.com...
> Hi,
> The following code describes a problem I'm having when I'm selecting
> data via a Case statement.
> The data I'm selecting is from a table with 2 varchar columns and 1
> int column. When I select this data I get the following error:
> Server: Msg245, Level 16,State 1, Line 1
> Syntax error converting the varchar values 'A' to a column of datatype
> int.
> If I cast the int to a varchar it works, but I would prefer not to
> cast if possible. SQL server seems to look at all the datatypes in the
> case statement and if a int data type appears, it seems to be trying
> to insert into a int column. Is this some sort of temp table? Anyone
> know why it assigned an int column here?
> Any ideas what I can do
> Thanks,
> Tom
> - --
> - -- Set up the table
> - --
> create table Tom
> (charcol1 varchar(2)
> ,charcol2 varchar(2)
> ,intcol1 int
> )
> insert into tom
> (charcol1
> ,intcol1
> ,charcol2
> )
> values
> ('A',1,'C')
> - ---
> - -- Code
> - ---
> SELECT
> CASE numlist.list
> WHEN 1 THEN
> Tom.charcol1
> WHEN 2 THEN
> --cast (Tom.intcol1 as varchar(5))
> Tom.intcol1
> WHEN 3 THEN
> Tom.charcol2
> END AS result
> FROM Tom
> CROSS JOIN (select 1 as list
> union all
> select 2 as list
> union all
> select 3 as list
> ) as numlist

Interesting SQL problem : How to track movement history

Hello everyone,

There's an interesting SQL problem I've come across that I'm currently
banging my head against. Given the following table that contains item
location information populated every minute :

location_id date_created
=========== ============
5 2000-01-01 01:00 <-- Don't need
5 2000-01-01 01:01 <-- Don't need
5 2000-01-01 01:02 <-- Need
7 2000-01-01 01:03 <-- Don't need
7 2000-01-01 01:04 <-- Need
5 2000-01-01 01:05 <-- Need
2 2000-01-01 01:06 <-- Don't Need
2 2000-01-01 01:07 <-- Need
7 2000-01-01 01:08 <-- Need

how would you generate a result-set that returns the item's location
history *without* duplicating the same location if the item has been
sitting in the same room for a while. For example, the result set
should look like the following :

location_id date_created
=========== ============
5 2000-01-01 01:02
7 2000-01-01 01:04
5 2000-01-01 01:05
2 2000-01-01 01:07
7 2000-01-01 01:08

This is turning out to be a finger twister and I'm not sure if it
could be done in SQL; I may have to resort to writing a stored-proc.

Regards,

AnthonyIf you are using SQL Server 2005, you can do this

with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)|||On 20.04.2007 15:49, markc600@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

I'd do

select location_id, max(date_created) date_created
from your_table
group by location_id
order by max(date_created)

Am I missing something?

robert|||Hello Robert,

That wouldn't work since it groups by location_id and would return the
following :

location_id date_created
=========== ============
5 2000-01-01 01:05
2 2000-01-01 01:07
7 2000-01-01 01:08

I am looking into Mark's solution as we speak...

Regards,

Anthony

On Apr 20, 9:59 am, Robert Klemme <shortcut...@.googlemail.comwrote:

Quote:

Originally Posted by

On 20.04.2007 15:49, markc...@.hotmail.com wrote:
>

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this

>

Quote:

Originally Posted by

with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

>
I'd do
>
select location_id, max(date_created) date_created
from your_table
group by location_id
order by max(date_created)
>
Am I missing something?
>
robert

|||Yep. What if the item returns to a location that it once occupied? Your
solution would not pick up the previous history.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Robert Klemme" <shortcutter@.googlemail.comwrote in message
news:58rv9iF2ikhoqU1@.mid.individual.net...
On 20.04.2007 15:49, markc600@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

I'd do

select location_id, max(date_created) date_created
from your_table
group by location_id
order by max(date_created)

Am I missing something?

robert|||On 20.04.2007 16:08, Tom Moreau wrote:

Quote:

Originally Posted by

Yep. What if the item returns to a location that it once occupied? Your
solution would not pick up the previous history.

Right, thanks Tom and Anthony for correcting me. Stupid me. I should
have spent few more CPU cycles on this. :-)

robert|||Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations?

Regards,

Anthony

On Apr 20, 9:49 am, markc...@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

|||www.insidetsql.com
--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Anthony Paul" <anthonypaulo@.gmail.comwrote in message
news:1177078960.725463.69600@.y80g2000hsf.googlegro ups.com...
Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations?

Regards,

Anthony

On Apr 20, 9:49 am, markc...@.hotmail.com wrote:

Quote:

Originally Posted by

If you are using SQL Server 2005, you can do this
>
with cte(location_id,date_created,grp)
as (
select location_id,
date_created,
rank() over(partition by location_id order by date_created)
- rank() over(order by date_created)
from mytable)
select location_id,
max(date_created) as date_created
from cte
group by location_id,grp
order by max(date_created)

|||
"Anthony Paul" <anthonypaulo@.gmail.comwrote in message
news:1177078960.725463.69600@.y80g2000hsf.googlegro ups.com...

Quote:

Originally Posted by

Hello Mark,
>
So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P
>

I can't speak for Mark, but some of us have way too much time on our hands.
;-)

Quote:

Originally Posted by

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations?

Look for books by Itzak Ben-Gan. (Inside Microsoft SQL Server 2005; T-SQL
Querying covers this. it's a island/gap problem.)

I remember the first time I saw him demo those functions (they're new in SQL
2005).

Some pretty incredible stuff.

Quote:

Originally Posted by

>
Regards,
>
Anthony
>
On Apr 20, 9:49 am, markc...@.hotmail.com wrote:

Quote:

Originally Posted by

>If you are using SQL Server 2005, you can do this
>>
>with cte(location_id,date_created,grp)
>as (
>select location_id,
> date_created,
> rank() over(partition by location_id order by date_created)
> - rank() over(order by date_created)
>from mytable)
>select location_id,
> max(date_created) as date_created
>from cte
>group by location_id,grp
>order by max(date_created)

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||The usual design error is to have only one time in a row to capture
when an event started, then do horrible self-joins to get the duration
of the status change. Let me use a history table for price changes.
The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

Look up the Rick Snodgrass book on Temporal Queries in SQL at the
University of Arizona website; it is a free download.

Interesting Large Table Design Recommendation

Hi,

What's the most efficient way to store the following information:

* Table contains 1 million listings
* Each listing can be geo-targeted to any of the 200+ countries
* Searches return listings based on location

Storage options:

Option #1 (normalized)
* Listings (PK listingID int) [1 million rows]
* ListingLocations (listingID, locationID) [could be up to 200 million rows]

Option #2 (denormalized)
* Listings (PK listingID int, binary(32) with bit-mask consisting of 200 bits one for each location)

Usage: Usually the query will simply lookup listings based on some keywords. It will get back 50-200 listings. Then the application (C#) will filter the listings based on location.

Did anyone have experience with similar structures? Which option is more efficient?

I know that using the intersection-table in Option #1 is the "proper" relational-DB way of doing things. However, I do not like the idea of storing the listingID so many times (ones for each locationID).

Thanks,
Av

I think too the option #1 is the good solution and yourself explain this ("I do not like the idea of storing the listingID so many times (ones for each locationID)).

But i have an ideea vis-a-vis the way the users use your db: if your users should query the data using geographical criteria, let'say : country of West or Sud or East Europe, Africa etc you can use partitioned table with boundary values that geographic zones. So you'll grow the performance.

Interesting join

I'm looking for some guidance on how to create a join between two tables
given the following...
Table1
ID SeqNum
A 1
A 2
B 1
B 2
B 3
Table2
ID SeqNum
A 33
A 36
B 66
B 67
B 99
The problem here is that Table2's Seqnum field does not contain the same
value as Table1's Seqnum field. However, it is guaranteed to contain the
same sequential ordering within ID's. Therefore, the lowest Seqnum value in
Table1 logically links with the lowest Seqnum value in Table2 for each ID.
For example, given this sequential relationship, the following is the join
we're trying to achieve for ID B:
ID Table1.SeqNum Table2.SeqNum
B 1 66
B 2 67
B 3 99
Is there any way to logically join these two tables given the differing
Seqnum's?
Thanks in advanceSo we are talking about a simple join of the 2 tables on seqnum except that
there is an unknown offset between the actual numbering of seqnum?
Determine (query) what this offset is, store the result in a variable, and
incorporate the offset as needed in the join condition. Of course you know
that this is not a normalized relationship.
declare @.offset as int
select @.offset =
(select max(seqnum) from table2) - (select max(seqnum) from table1)
select
table1.id,
table1.seqnum,
table2.id,
table2.seqnum
from
table1
left join
table2
on table2.seqnum = (table1.seqnum + @.offset )
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
in
> Table1 logically links with the lowest Seqnum value in Table2 for each ID.
> For example, given this sequential relationship, the following is the join
> we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Cipher, try,
SELECT ...
FROM T1 AS T1A JOIN T2 AS T2A
ON T1A.id = T2A.id
AND (SELECT COUNT(*) FROM T1 AS T1B
WHERE T1B.id = T1A.id
AND T1B.seqnum <= T1A.seqnum) =
(SELECT COUNT(*) FROM T2 AS T2B
WHERE T2B.id = T2A.id
AND T2B.seqnum <= T2A.seqnum)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM Table1 t1
INNER JOIN (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table2 t3
INNER JOIN table2 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
if the SeqNums in Table1 are not sequential, you have to repeat the dose for
Table1 as well:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table1 t3
INNER JOIN table1 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t1
INNER JOIN (
SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
FROM Table2 t5
INNER JOIN table2 t6
ON t5.SeqNum <= t6.SeqNum
GROUP BY t5.ID, t5.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
Jacco Schalkwijk
SQL Server MVP
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Let's hope he doesn't have 1M rows or this join would occupy his server's
CPU all night long.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM Table1 t1
> INNER JOIN (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table2 t3
> INNER JOIN table2 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
> if the SeqNums in Table1 are not sequential, you have to repeat the dose
for
> Table1 as well:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table1 t3
> INNER JOIN table1 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t1
> INNER JOIN (
> SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
> FROM Table2 t5
> INNER JOIN table2 t6
> ON t5.SeqNum <= t6.SeqNum
> GROUP BY t5.ID, t5.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
>
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
the
value
each
the
>|||Thanks Itzik, this works perfectly.
I was going down the road of using cursors and programmatically replicating
the relationship logic but this has saved me from that pain.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OkUfSSiZFHA.3984@.TK2MSFTNGP10.phx.gbl...
> Cipher, try,
> SELECT ...
> FROM T1 AS T1A JOIN T2 AS T2A
> ON T1A.id = T2A.id
> AND (SELECT COUNT(*) FROM T1 AS T1B
> WHERE T1B.id = T1A.id
> AND T1B.seqnum <= T1A.seqnum) =
> (SELECT COUNT(*) FROM T2 AS T2B
> WHERE T2B.id = T2A.id
> AND T2B.seqnum <= T2A.seqnum)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
>|||With proper indexing on a million row table, it will take a number of
minutes, but nothing close to all night. But at least it provides the
required result.
Jacco Schalkwijk
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:uqwKEYiZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Let's hope he doesn't have 1M rows or this join would occupy his server's
> CPU all night long.
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> for
> the
> value
> each
> the
>

Interesting isNumeric result

Does anyone else get the following result when running this query?

Select isnumeric('4D7')

--

1

(1 row(s) affected)

Does anyone know why this would return true for numeric?

Thanks,

Ray

That is another representation of 4.0E+7 -or 40000000.0, and is, therefore, a valid numeric.

You may find this article useful.

isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390

|||Yeah, IsNumeric is pretty horrible. Vote here for a change https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=177308

intercept pipeline events programmatically

Hello,

I'm wish to receive pipeline events fired by a SSIS package.

I execute the package successufully with the following code (c#):

MyEventListener eventListener = new XplorerEventListener();
DtsApplication app = new DtsApplication();
Package pkg = app.LoadPackage("c:\test.dstx", null);
pkg.Execute(null, null, eventListener, null, null);

MyEventListener is inherited from DefaultEvents, overriding all OnXXX methods.

It works perfectly, however I cannot intercept the following events:

- PipelineExecutionTrees
- PipelineExecutionPlan
- PipelineExecutionInitialization
- BufferSizeTuning
- PipelineInitialization

Anyone knows how to catch those pipeline events?
TIA,
Paolo.

I believe that at least some of those are log events and you need to intercept them by implementing an IDTSLogging interface.

Harry

Interactive sort not working in Tabular View

I am just starting with Sql Server reporting, but I can't get interactive sort to work for columns with aggregate fields.

I am using the following query for DataSet:

SELECT
Store.ID as StoreID,
Store.Name as StoreName,
COUNT(*) as NumReservations,
SUM(Appointment.TotalBeforeTaxes) as Revenue
FROM Store LEFT JOIN Appointment ON Store.ID=Appointment.StoreID
GROUP BY ALL Store.ID, Store.Name
ORDER BY Store.Name ASC

For report, I am using tabular data view. Interactive sorting works great for StoreID, StoreName, but doesn't work for NumReservations and Revenue fields. I turned it on for all 4 columns.

What could be causing this problem?
Figured out the problem... It just doesn't work in FireFox. Seems to work fine in IE.