Showing posts with label order. Show all posts
Showing posts with label order. Show all posts

Friday, March 9, 2012

Msg "cannot add rows to sysdepends... procedure will still be created"

Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?

Well?? whatcha guys say?|||Sure you can.

Say that MainProc calls SubProc, but when you create the procs in the database you create MainProc 1st. When you create MainProc, you will get the sysdepends message "Cannot add rows ...". When you create SubProc, sysdepends is **not** updated. If you then drop and add MainProc, sysdepends will be populated with the dependancy information.|||Yeah, that's how I've gotten around a few of the problems created by the scripting/transfers to other databases (mirrors, in my case).

However, being the bull-headed son-of-a-db2 programmer that I am, I refuse to do that to every freakin' proc/table/function/etc. in my script. Seems to me that if I use SQL Server to script out a humongous script and then use that script to build a new DB, that I shouldn't have to manually go through the process of dropping and rebuilding each object in the new db.

Perhaps Uncle Bill wants it that way, but I have always been a problem nephew, so I am trying to figure a more global, one-button solution. I did find this thread on the subject after posting the original query, however. http://www.dbforums.com/t406785.html

I will take a look at that solution and see how it works.

Don't get me wrong, I do truly appreciate your response. Sincerely. I just don't have the time and inclination to use the manual drop/readd thang...when that is actually only a tad more desireable than manually writing down the object dependencies on the chalkboard in the hallway. ;)

Thanks again...and sorry for the rant...I just don't like the appearance of bells and whistles that only impotently blow the ball around inside (though they might do so at a high rate of speed), or dog and pony shows consisting of a toy poodle and a plow nag.|||Argh...alas...the sp_recompile/sp_refreshview solution still only goes from the individual object perspective...so I still will need to order the objects according to referential sequences before doing the recompiles.

Still not my silver bullet.

I guess I'll just requisition a few more chalkboards ;) Thanks again!
Paul|||Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.|||Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.

Are you saying that just running a stored proc will "rebuild" the dependency information in the sysdepends table? If so, then theoretically that means that all I need to do is wait for all the stored procs in the database to run and then my sysdepends table will be all better?

That would be cool, but it doesn't seem to hold true in my (admittedly limited) experience. I'll have to check it out, but my stuff runs M-F here at work, and yet I still have "missing" or "incomplete" dependencies when I list dependencies in Enterprise Manager *scratching head*

Am I missing your point? Are you saying the profiler must be run to rebuild the dependencies? *LOL* I guess instead of guessing I'll wait until you clarify your comments...

As always, thanks!|||I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference...but hoped that that would not be the case after all...Oh well, you're write, dependency can only be reestablished if you either re-CREATE or ALTER the parent procedure after the child has been created. SP_RECOMPILE does not do the trick, BTW.|||I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference.

I probably know just enough to be dangerous to myself and those around me ;) but I really just wanted to build the sysdepends table so that when I DISPLAY dependencies of the various objects (i.e., right click->all tasks->display dependencies) it would be accurate.

I'm not sure which of your situations that desire falls under. sorry if I don't understand...I think it would be the physical dependency that I am after. As far as I know, I've always been able to execute a procedure even if the sysdepends info is bad (i.e., even if the dependency is not displayed in "display dependencies" - which I have understood to get its info from the sysdepends table).

In any case, I think the bottom line is that there is no easy way to rebuild things in sysdepends so I can be assured that all my dependencies are displayed when I try to see them via right-click...short of the drop/add thing.

Although, as I recall, if I go into the stored proc and modifiy something (as opposed to drop/add the proc) - I think the dependencies are re-evaluated when I save my changes...at least that's what I recall from earlier playing around with 'em|||That's because EM fires ALTER which reevaluates the dependencies, as I mentioned earlier. If you want to achieve the result desired you need to invest into something like DBArtisan, but if you just want to continue bashing EM, - be my guest ;)

MSDTC w/ Oracle 8i EventID=4156

On June 11th, we changed the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSDTC\MTxOCI registry settings in order to correct the problem of failed ODBC connections to an Oracle 8.1.7 linked server.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
It appeared to fix our problems -- but...
After doing so, we began getting MSDTC EventID 4156 information events in the application log - String message: Session idle timeout over, tearing down the session.
Since making the registry change, 328 of these errors have occurred to date. The first one occurred ~2 hours after making the registry change.
OPENQUERY statements to the Oracle server are now intermittently requiring long access times (minutes) that used to take a split second.
Since making the registry change, 328 of these errors have occurred to date.
Are there any suggestions on how to rectify this problem?
Hello !!
I have the exact same error message. Did you find any resolution for this ?
Mirna.-
"elpepe" wrote:

> On June 11th, we changed the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSDTC\MTxOCI registry settings in order to correct the problem of failed ODBC connections to an Oracle 8.1.7 linked server.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I]
> "OracleXaLib"="oraclient8.dll"
> "OracleSqlLib"="orasql8.dll"
> "OracleOciLib"="oci.dll"
> It appeared to fix our problems -- but...
> After doing so, we began getting MSDTC EventID 4156 information events in the application log - String message: Session idle timeout over, tearing down the session.
> Since making the registry change, 328 of these errors have occurred to date. The first one occurred ~2 hours after making the registry change.
> OPENQUERY statements to the Oracle server are now intermittently requiring long access times (minutes) that used to take a split second.
> Since making the registry change, 328 of these errors have occurred to date.
> Are there any suggestions on how to rectify this problem?
|||below is the likely solution
PRB: MS DTC Event 4156: "Session Idle Timeout Over"
Article ID : 290334
Last Review : February 20, 2002
Revision : 1.0
This article was previously published under Q290334
On this Page
SYMPTOMS
CAUSE
RESOLUTION
REFERENCES
SYMPTOMS
You may see the following entry in the application event log:
String message: Session idle timeout over, tearing down the session.
CAUSE
This message indicates that the connection between a Distributed Transaction Coordinator (DTC) client and Microsoft Distributed Transaction Coordinator (MS DTC) is being torn down due to inactivity.
RESOLUTION
This message is usually just informational and can be ignored. Under normal circumstances, MS DTC tears down inactive connections after 10 minutes of idle time.
REFERENCES
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
197810 FIX: Upgrading DTC to Close Idle Connections
250987 HOWTO: Reinstall MS DTC for a Nonclustered Windows NT 4.0 Server
279786 HOWTO: Reinstall MS DTC for a Nonclustered Windows 2000 Serve

Quote:

Originally posted by Mirna Da Costa
Hello !!
I have the exact same error message. Did you find any resolution for this ?
Mirna.-
"elpepe" wrote:

> On June 11th, we changed the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSDTC\MTxOCI registry settings in order to correct the problem of failed ODBC connections to an Oracle 8.1.7 linked server.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I]
> "OracleXaLib"="oraclient8.dll"
> "OracleSqlLib"="orasql8.dll"
> "OracleOciLib"="oci.dll"
> It appeared to fix our problems -- but...
> After doing so, we began getting MSDTC EventID 4156 information events in the application log - String message: Session idle timeout over, tearing down the session.
> Since making the registry change, 328 of these errors have occurred to date. The first one occurred ~2 hours after making the registry change.
> OPENQUERY statements to the Oracle server are now intermittently requiring long access times (minutes) that used to take a split second.
> Since making the registry change, 328 of these errors have occurred to date.
> Are there any suggestions on how to rectify this problem?

MSDTC w/ Oracle 8i EventID=4156

On June 11th, we changed the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSDTC\MT
xOCI registry settings in order to correct the problem of failed ODBC connec
tions to an Oracle 8.1.7 linked server.
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
It appeared to fix our problems -- but...
After doing so, we began getting MSDTC EventID 4156 information events in th
e application log - String message: Session idle timeout over, tearing down
the session.
Since making the registry change, 328 of these errors have occurred to date.
The first one occurred ~2 hours after making the registry change.
OPENQUERY statements to the Oracle server are now intermittently requiring l
ong access times (minutes) that used to take a split second.
Since making the registry change, 328 of these errors have occurred to date.
Are there any suggestions on how to rectify this problem?Hello !!
I have the exact same error message. Did you find any resolution for this '
Mirna.-
"elpepe" wrote:

> On June 11th, we changed the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSDTC\
MTxOCI registry settings in order to correct the problem of failed ODBC conn
ections to an Oracle 8.1.7 linked server.
> & #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]
> "OracleXaLib"="oraclient8.dll"
> "OracleSqlLib"="orasql8.dll"
> "OracleOciLib"="oci.dll"
> It appeared to fix our problems -- but...
> After doing so, we began getting MSDTC EventID 4156 information events in
the application log - String message: Session idle timeout over, tearing do
wn the session.
> Since making the registry change, 328 of these errors have occurred to dat
e. The first one occurred ~2 hours after making the registry change.
> OPENQUERY statements to the Oracle server are now intermittently requiring
long access times (minutes) that used to take a split second.
> Since making the registry change, 328 of these errors have occurred to dat
e.
> Are there any suggestions on how to rectify this problem?|||below is the likely solution
PRB: MS DTC Event 4156: "Session Idle Timeout Over"
Article ID : 290334
Last Review : February 20, 2002
Revision : 1.0
This article was previously published under Q290334
On this Page
SYMPTOMS
CAUSE
RESOLUTION
REFERENCES
SYMPTOMS
You may see the following entry in the application event log:
String message: Session idle timeout over, tearing down the session.
CAUSE
This message indicates that the connection between a Distributed
Transaction Coordinator (DTC) client and Microsoft Distributed
Transaction Coordinator (MS DTC) is being torn down due to inactivity.
RESOLUTION
This message is usually just informational and can be ignored. Under
normal circumstances, MS DTC tears down inactive connections after 10
minutes of idle time.
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
197810 FIX: Upgrading DTC to Close Idle Connections
250987 HOWTO: Reinstall MS DTC for a Nonclustered Windows NT 4.0 Server
279786 HOWTO: Reinstall MS DTC for a Nonclustered Windows 2000 Serve
Mirna Da Costa wrote:[vbcol=seagreen]
> *Hello !!
> I have the exact same error message. Did you find any resolution for
> this '
> Mirna.-
>
> "elpepe" wrote:
>
> MSDTC\MTxOCI registry settings in order to correct the problem of
> failed ODBC connections to an Oracle 8.1.7 linked server.
> events in the application log - String message: Session idle timeout
> over, tearing down the session.
> to date. The first one occurred ~2 hours after making the registry
> change.
> requiring long access times (minutes) that used to take a split
> second.
> to date.
acsekar
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message808237.html

Monday, February 20, 2012

MSDTC

hello,
is MSDTC required in order for a 2003 sql cluster to work correctly?
No, MSDTC is not required in order for SQL Server 2000 failover Clustering to work correctly on Windows 2003 MSCS.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||What about when doing linked servers? Do you have to add the MSDTC, MSDTC
Network Name, MSDTC IP Address cluster resources? If not, when would be
necessary to use the MSDTC, MSDTC Network Name, MSDTC IP Address resources
cluste resources.
Does Enable DTC for network access just have to be installed? Our linked
server queries weren't working until we installed Enable DTC for network
access however the cluster also has also. The cluster is an active/active
sql 2000 enterprise with Windows 2003 Enteprise.
Steve
"Calvin Do" <calvin@.spinsonic.com> wrote in message
news:OP7LWvEnEHA.1672@.TK2MSFTNGP09.phx.gbl...
> hello,
> is MSDTC required in order for a 2003 sql cluster to work correctly?
>
|||Steve,
Good question. For distributed transactions if you are using MSDTC as the transaction manager then you will need to configure MSDTC as a clustered resource on a SQL Server 2000 Failover clustering. (Distributed
transactions span two or more servers known as resource managers. The management of the transaction must be coordinated between the resource managers by a server component called a transaction
manager. Microsoft SQL Server can operate as a resource manager in distributed transactions coordinated by transaction managers such as the Microsoft Distributed Transaction Coordinator (MS DTC), or other
transaction managers that support the X/Open XA specification for Distributed Transaction Processing. For more information, see the Microsoft Distributed Transaction Coordinator documentation in SQL Server 2000
Books Online)
Many think that MSDTC has to be configured first otherwise they cannot install SQL Server 2000 cluster properly or it will nto operated correctly and that is not true. I have seen and installed many SQL Server 2000
instances on Win2000 and Win2003 and did not configure MSDTC as it was not required (no distributed transactions were occuring).
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx