Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
Spin
Spin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
sql
Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts
Thursday, March 29, 2012
Do regular stored procedures still run when Agent XPs are disabled?
Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
--
SpinSpin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
--
SpinSpin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>
Sunday, March 25, 2012
Do I need the "dbo" at the end of my stored procedures?
I have some stored procedures with (dbo) after them and some that don't. Do I need this? how do I get rid of it? Do I need to refernence them in my code?
I intend to send my Access 2000 ADP front end to a customer and the back end SQL to them is this naming convention going to cause me issues?
Mitchnormally when one see "dbo" it is at the beginning of a stored proceure name i.e. master.dbo.sp_who.
If you have a database called "db1" and a stored procedure created by dbo called "sp1", you could have your app reference the sp as db1.dbo.sp1 from any db and it should work. From within db1 you could call dbo.sp1 annd that would work too.
If you referrence your sp as "sp1" and user "usr1" has created a stored procedure called "sp1" then when the user, "usr1", executes your sp "sp1" he/she/it will execute db1.usr1.sp1 not db1.dbo.sp1.
IMHO, if you want your users to execute the dbo version of a stored procedures then add "dbo." to the beggining of your sp name.
I intend to send my Access 2000 ADP front end to a customer and the back end SQL to them is this naming convention going to cause me issues?
Mitchnormally when one see "dbo" it is at the beginning of a stored proceure name i.e. master.dbo.sp_who.
If you have a database called "db1" and a stored procedure created by dbo called "sp1", you could have your app reference the sp as db1.dbo.sp1 from any db and it should work. From within db1 you could call dbo.sp1 annd that would work too.
If you referrence your sp as "sp1" and user "usr1" has created a stored procedure called "sp1" then when the user, "usr1", executes your sp "sp1" he/she/it will execute db1.usr1.sp1 not db1.dbo.sp1.
IMHO, if you want your users to execute the dbo version of a stored procedures then add "dbo." to the beggining of your sp name.
Thursday, March 22, 2012
Do I have the right to modify third-party applications stored procedures?
Our current concern deals with stored procedures from a third-party application that were modified in order to correct future data inconsistency that was being generated. Since the stored procedures were not encrypted, I was able to modify them and correct the problem. At the same time, we developed a small in-house application to correct the current data inconsistency and we created new stored procedures in the same database. Now I'm concern about if I had the right to modify those stored procedures and additionally, created new ones inside this database? Am I restricted somehow to use our full version of MS SQL Server with a scenario like this?It'd really depend on what sort of deal you have with the vendor of the third party app.
A lot of the vendors that I have dealt with in the past have allowed the changing of stored procedures and the addition of new ones on a "at your own risk" type of deal (eg. future versions will be be guaranteed to work, the database is no longer covered by their support etc).
I'd suggest having a talk to the vendor, don't tell them you have changed anything but ask them what their views on changing stored procedures etc are.
HTH|||If it clearly creates data inconsistency then I at least would have no second thaughts abut changing the sp's. I would sort of stick to the "what they don't know can't hurt'em"-plan if this was me...|||I'll try to describe better our situation.
Before I did the changes, the provider notified us that they were closing their doors. Thus, we didn't have any support for sometime. After a while, they came to us offering the source code which we rejected considering the fact that it was a very poor developed application. Afterwards, we decide to develop, with our own effort, an application to replace the existing. In short, there is a legal process running for last 4 years where they have alleged we have commited several violations to their rights which so far, we have disproved. Now, they are conducting their efforts toward the fact I modified 2 procs and also, created new ones. I know I didn't do wrong. But, how can I show the attorneys? Perhaps, I need leads to articles, statements or disclaims from, lets say, Microsoft that contain information about the pros and cons of leaving procs as text. How can a solution provider protect procs source code or even better, their know-how?
Originally posted by rokslide
It'd really depend on what sort of deal you have with the vendor of the third party app.
A lot of the vendors that I have dealt with in the past have allowed the changing of stored procedures and the addition of new ones on a "at your own risk" type of deal (eg. future versions will be be guaranteed to work, the database is no longer covered by their support etc).
I'd suggest having a talk to the vendor, don't tell them you have changed anything but ask them what their views on changing stored procedures etc are.
HTH|||This ha NOTHING to do with M$
Did you sign a contract with them?
Did you pay them anything?
Did they build this code for you exclusively?
If I contract out, any code I build belongs to the Client (usually)
It's all a matter of what's on paper...
Check this out:
The part about self employeement...
http://weblogs.sqlteam.com/markc/|||Wow, I didn't realize it was this serious. It is possible to encrypt sp's in sql-server and if this company has let you have direct access to the database then this would have been a good idea from their side. It is not common for software-vendors to expose their sourcecode and this company you are been in a dispute with should know this.
An sp can be encrypted like this:
CREATE PROCEDURE myProcName WITH ENCRYPTION
AS
...
This way you or any others will not be able to access the sorcecode of the procedure. You can try it yourself for verification:
EXEC sp_helptext myProcName
Now when it comes to your legal rights to change their sourcecode my belief is that you unfortunately have a weak case. Brett Kaiser is partially right I belive that it comes down to what you have on paper and not, but since you have used their software you automatically agree that their software is usable and if you hae paid for it aswell you aknowledge that they are the rightful owners. I would try to go down the lane of putting the blame on them for not fulfilling their duties as a software vendor, and because they didn't perform their duties you had to remedy the bugs yourself.
Good luck man, I'm sorry to say that I'll think you'll need it :(|||I apologize...I wasn't meaning to be offensive...
But I guess I was...
Sorry|||What are you suggesting? That they don't have laws in Venezuela? I really hope I misunderstood this message of yours because I found it to be quite rude, but I will give you the benifit of the doubt...|||Frettmaestro, I'm sure Brett didn't mean to be insulting. If you know the different parts of the US well, then, you know how those guys from Jersey can be. (Right Brett? :D )
j_shaw, not being a lawyer anywhere, much less knowledgeable about Venezuelan law, everything I say here is totally my opinion and not to be taken as legal advice. But here goes: Regardless of whether the procedures were encrypted or not, I think you overstepped the bounds by changing them without first establishing ownership or at least permission. If the work was done for you custom, then there's a good chance your company owns them, but if it was a commercial product, there's a good chance that you only had license to use, not change. And as Brett pointed out, this all comes down to what the paperwork says.
Just because someone doesn't encrypt their procedures doesn't mean you have the right to change them. It may be a stupid move on their part to leave them hanging out there so easy to read and altered, but it's not an excuse to say, "if you didn't want me to do it, you should have locked it". That's like saying if somebody leaves the door to their house unlocked, or a window open, then it's okay to enter and take anything you want. Wrong!
And nobody should be under the misperception that using SQL Server's WITH ENCRYPTION is a guaranteed secure lock. I've heard that it has been cracked. It's still a good idea, but not foolproof.
(P.S. Brett, thanks for the referral!)|||The story is a little longer. My company hired this guys to develop a solution for us. But, we are unable to prove it because in one update they changed our personalized version with one that they had previously registered.
Originally posted by Frettmaestro
Wow, I didn't realize it was this serious. It is possible to encrypt sp's in sql-server and if this company has let you have direct access to the database then this would have been a good idea from their side. It is not common for software-vendors to expose their sourcecode and this company you are been in a dispute with should know this.
An sp can be encrypted like this:
CREATE PROCEDURE myProcName WITH ENCRYPTION
AS
...
This way you or any others will not be able to access the sorcecode of the procedure. You can try it yourself for verification:
EXEC sp_helptext myProcName
Now when it comes to your legal rights to change their sourcecode my belief is that you unfortunately have a weak case. Brett Kaiser is partially right I belive that it comes down to what you have on paper and not, but since you have used their software you automatically agree that their software is usable and if you hae paid for it aswell you aknowledge that they are the rightful owners. I would try to go down the lane of putting the blame on them for not fulfilling their duties as a software vendor, and because they didn't perform their duties you had to remedy the bugs yourself.
Good luck man, I'm sorry to say that I'll think you'll need it :(|||Originally posted by AjarnMark
Frettmaestro, I'm sure Brett didn't mean to be insulting. If you know the different parts of the US well, then, you know how those guys from Jersey can be. (Right Brett? :D )
(P.S. Brett, thanks for the referral!)
Just being the ignorant American...
But to re-address in (hopefully) a different tone....
There were hardly ANY US laws for software and stuff till just recently...
How and what the laws are for Venezuala...I have no idea...
And wouldn't you want to counter-sue anyway for your time it took to do the data sanitation?
Don't you have the expectation/right to expect their product to work?|||My own cursory knowledge of law probably does not apply, but it does not sound like you made a profit by re-selling the application with your changes, so you should be safe from lawsuits by SCO. When you found that the application could invalidate your data, did you go to the software company and ask for a fix? If they refused, then you may be able to sue them (if you have in writing that the application promises to do so-and-such). Since they are bringing the suit, they have to prove that your changes have somehow damaged them.
In general, I do not allow any schema (or data) updates to a third party application database, as this can invalidate the service contract. Extra lawsuits have never come into the picture (as far as I know).|||A company I have been working for is currently in a legal dispute with a hosting provider. The case is not at all similar but this company did not have a signed agreement of any sort, and here in England that didn't matter because we supposedly accepted their terms and conditions automatically when they paid the first bill and made use of their services. The company I worked for will most likely get the case dismissed because the hosting company breached their own contract on several counts but my point is simply that even though no written contract has been signed you can still be legally obliged. This can offcourse be different in Venezuela, but I don't know that...
When it comes to altering software I do belive that you will have to make a case on the fact that their software was incapable of doing what it was supposed to and because they offered no real help, you had to change it yourself to save your business. You can't go bankrupt because some moron developer don't know what he's doing...|||Ok. Lets change the subject.
We also had to create an application to fix the already corrupted data. We created new sprocs and placed them in the same database. Do I have the right to add new objects to the existing database?
Originally posted by Frettmaestro
A company I have been working for is currently in a legal dispute with a hosting provider. The case is not at all similar but this company did not have a signed agreement of any sort, and here in England that didn't matter because we supposedly accepted their terms and conditions automatically when they paid the first bill and made use of their services. The company I worked for will most likely get the case dismissed because the hosting company breached their own contract on several counts but my point is simply that even though no written contract has been signed you can still be legally obliged. This can offcourse be different in Venezuela, but I don't know that...
When it comes to altering software I do belive that you will have to make a case on the fact that their software was incapable of doing what it was supposed to and because they offered no real help, you had to change it yourself to save your business. You can't go bankrupt because some moron developer don't know what he's doing...|||All in favor say 'eye'! EYE!
I don't think this will get you anywhere, we can say this and that but it all comes down to the laws of your country and your best bet is some venezuelan lawyers ;)|||A couple of things spring to light here...
The story is a little longer. My company hired this guys to develop a solution for us. But, we are unable to prove it because in one update they changed our personalized version with one that they had previously registered.
Okie,.. well if you hired them to develop a solution for you there should be documents showing it yes?
If they gave you a solution that was something they developed for others or that they in turn sold to others then they are in breach of contract (assuming standard contract laws apply).
If the application they gave you/developed for you is faulty as suggested...
We also had to create an application to fix the already corrupted data. We created new sprocs and placed them in the same database. Do I have the right to add new objects to the existing database?
...and you gave them the opportunity to fix it (which they didn't) then I don't see what the issue should be legally. Yes, you are on somewhat shakey ground for changing their code but they are on equally unstable ground for their practises during the development and delivery of the application.
You could also use the facts that their application was faulty and that they code was unprotected to show that they had made false claims about their abilities...
Of course I'm not a lawyer and I haven't studied law (except for what I have come across in the industry) and I don't have any leads/examples to point to, but I'm sure if you raised these points with an attorney he would be able to help you more.|||It's like the line from Animal House...
My advice to you is to drink heavily...
I didn't know you where in pre Med?
Pre Med, SQL Server DBA, what's the difference?
A lot of the vendors that I have dealt with in the past have allowed the changing of stored procedures and the addition of new ones on a "at your own risk" type of deal (eg. future versions will be be guaranteed to work, the database is no longer covered by their support etc).
I'd suggest having a talk to the vendor, don't tell them you have changed anything but ask them what their views on changing stored procedures etc are.
HTH|||If it clearly creates data inconsistency then I at least would have no second thaughts abut changing the sp's. I would sort of stick to the "what they don't know can't hurt'em"-plan if this was me...|||I'll try to describe better our situation.
Before I did the changes, the provider notified us that they were closing their doors. Thus, we didn't have any support for sometime. After a while, they came to us offering the source code which we rejected considering the fact that it was a very poor developed application. Afterwards, we decide to develop, with our own effort, an application to replace the existing. In short, there is a legal process running for last 4 years where they have alleged we have commited several violations to their rights which so far, we have disproved. Now, they are conducting their efforts toward the fact I modified 2 procs and also, created new ones. I know I didn't do wrong. But, how can I show the attorneys? Perhaps, I need leads to articles, statements or disclaims from, lets say, Microsoft that contain information about the pros and cons of leaving procs as text. How can a solution provider protect procs source code or even better, their know-how?
Originally posted by rokslide
It'd really depend on what sort of deal you have with the vendor of the third party app.
A lot of the vendors that I have dealt with in the past have allowed the changing of stored procedures and the addition of new ones on a "at your own risk" type of deal (eg. future versions will be be guaranteed to work, the database is no longer covered by their support etc).
I'd suggest having a talk to the vendor, don't tell them you have changed anything but ask them what their views on changing stored procedures etc are.
HTH|||This ha NOTHING to do with M$
Did you sign a contract with them?
Did you pay them anything?
Did they build this code for you exclusively?
If I contract out, any code I build belongs to the Client (usually)
It's all a matter of what's on paper...
Check this out:
The part about self employeement...
http://weblogs.sqlteam.com/markc/|||Wow, I didn't realize it was this serious. It is possible to encrypt sp's in sql-server and if this company has let you have direct access to the database then this would have been a good idea from their side. It is not common for software-vendors to expose their sourcecode and this company you are been in a dispute with should know this.
An sp can be encrypted like this:
CREATE PROCEDURE myProcName WITH ENCRYPTION
AS
...
This way you or any others will not be able to access the sorcecode of the procedure. You can try it yourself for verification:
EXEC sp_helptext myProcName
Now when it comes to your legal rights to change their sourcecode my belief is that you unfortunately have a weak case. Brett Kaiser is partially right I belive that it comes down to what you have on paper and not, but since you have used their software you automatically agree that their software is usable and if you hae paid for it aswell you aknowledge that they are the rightful owners. I would try to go down the lane of putting the blame on them for not fulfilling their duties as a software vendor, and because they didn't perform their duties you had to remedy the bugs yourself.
Good luck man, I'm sorry to say that I'll think you'll need it :(|||I apologize...I wasn't meaning to be offensive...
But I guess I was...
Sorry|||What are you suggesting? That they don't have laws in Venezuela? I really hope I misunderstood this message of yours because I found it to be quite rude, but I will give you the benifit of the doubt...|||Frettmaestro, I'm sure Brett didn't mean to be insulting. If you know the different parts of the US well, then, you know how those guys from Jersey can be. (Right Brett? :D )
j_shaw, not being a lawyer anywhere, much less knowledgeable about Venezuelan law, everything I say here is totally my opinion and not to be taken as legal advice. But here goes: Regardless of whether the procedures were encrypted or not, I think you overstepped the bounds by changing them without first establishing ownership or at least permission. If the work was done for you custom, then there's a good chance your company owns them, but if it was a commercial product, there's a good chance that you only had license to use, not change. And as Brett pointed out, this all comes down to what the paperwork says.
Just because someone doesn't encrypt their procedures doesn't mean you have the right to change them. It may be a stupid move on their part to leave them hanging out there so easy to read and altered, but it's not an excuse to say, "if you didn't want me to do it, you should have locked it". That's like saying if somebody leaves the door to their house unlocked, or a window open, then it's okay to enter and take anything you want. Wrong!
And nobody should be under the misperception that using SQL Server's WITH ENCRYPTION is a guaranteed secure lock. I've heard that it has been cracked. It's still a good idea, but not foolproof.
(P.S. Brett, thanks for the referral!)|||The story is a little longer. My company hired this guys to develop a solution for us. But, we are unable to prove it because in one update they changed our personalized version with one that they had previously registered.
Originally posted by Frettmaestro
Wow, I didn't realize it was this serious. It is possible to encrypt sp's in sql-server and if this company has let you have direct access to the database then this would have been a good idea from their side. It is not common for software-vendors to expose their sourcecode and this company you are been in a dispute with should know this.
An sp can be encrypted like this:
CREATE PROCEDURE myProcName WITH ENCRYPTION
AS
...
This way you or any others will not be able to access the sorcecode of the procedure. You can try it yourself for verification:
EXEC sp_helptext myProcName
Now when it comes to your legal rights to change their sourcecode my belief is that you unfortunately have a weak case. Brett Kaiser is partially right I belive that it comes down to what you have on paper and not, but since you have used their software you automatically agree that their software is usable and if you hae paid for it aswell you aknowledge that they are the rightful owners. I would try to go down the lane of putting the blame on them for not fulfilling their duties as a software vendor, and because they didn't perform their duties you had to remedy the bugs yourself.
Good luck man, I'm sorry to say that I'll think you'll need it :(|||Originally posted by AjarnMark
Frettmaestro, I'm sure Brett didn't mean to be insulting. If you know the different parts of the US well, then, you know how those guys from Jersey can be. (Right Brett? :D )
(P.S. Brett, thanks for the referral!)
Just being the ignorant American...
But to re-address in (hopefully) a different tone....
There were hardly ANY US laws for software and stuff till just recently...
How and what the laws are for Venezuala...I have no idea...
And wouldn't you want to counter-sue anyway for your time it took to do the data sanitation?
Don't you have the expectation/right to expect their product to work?|||My own cursory knowledge of law probably does not apply, but it does not sound like you made a profit by re-selling the application with your changes, so you should be safe from lawsuits by SCO. When you found that the application could invalidate your data, did you go to the software company and ask for a fix? If they refused, then you may be able to sue them (if you have in writing that the application promises to do so-and-such). Since they are bringing the suit, they have to prove that your changes have somehow damaged them.
In general, I do not allow any schema (or data) updates to a third party application database, as this can invalidate the service contract. Extra lawsuits have never come into the picture (as far as I know).|||A company I have been working for is currently in a legal dispute with a hosting provider. The case is not at all similar but this company did not have a signed agreement of any sort, and here in England that didn't matter because we supposedly accepted their terms and conditions automatically when they paid the first bill and made use of their services. The company I worked for will most likely get the case dismissed because the hosting company breached their own contract on several counts but my point is simply that even though no written contract has been signed you can still be legally obliged. This can offcourse be different in Venezuela, but I don't know that...
When it comes to altering software I do belive that you will have to make a case on the fact that their software was incapable of doing what it was supposed to and because they offered no real help, you had to change it yourself to save your business. You can't go bankrupt because some moron developer don't know what he's doing...|||Ok. Lets change the subject.
We also had to create an application to fix the already corrupted data. We created new sprocs and placed them in the same database. Do I have the right to add new objects to the existing database?
Originally posted by Frettmaestro
A company I have been working for is currently in a legal dispute with a hosting provider. The case is not at all similar but this company did not have a signed agreement of any sort, and here in England that didn't matter because we supposedly accepted their terms and conditions automatically when they paid the first bill and made use of their services. The company I worked for will most likely get the case dismissed because the hosting company breached their own contract on several counts but my point is simply that even though no written contract has been signed you can still be legally obliged. This can offcourse be different in Venezuela, but I don't know that...
When it comes to altering software I do belive that you will have to make a case on the fact that their software was incapable of doing what it was supposed to and because they offered no real help, you had to change it yourself to save your business. You can't go bankrupt because some moron developer don't know what he's doing...|||All in favor say 'eye'! EYE!
I don't think this will get you anywhere, we can say this and that but it all comes down to the laws of your country and your best bet is some venezuelan lawyers ;)|||A couple of things spring to light here...
The story is a little longer. My company hired this guys to develop a solution for us. But, we are unable to prove it because in one update they changed our personalized version with one that they had previously registered.
Okie,.. well if you hired them to develop a solution for you there should be documents showing it yes?
If they gave you a solution that was something they developed for others or that they in turn sold to others then they are in breach of contract (assuming standard contract laws apply).
If the application they gave you/developed for you is faulty as suggested...
We also had to create an application to fix the already corrupted data. We created new sprocs and placed them in the same database. Do I have the right to add new objects to the existing database?
...and you gave them the opportunity to fix it (which they didn't) then I don't see what the issue should be legally. Yes, you are on somewhat shakey ground for changing their code but they are on equally unstable ground for their practises during the development and delivery of the application.
You could also use the facts that their application was faulty and that they code was unprotected to show that they had made false claims about their abilities...
Of course I'm not a lawyer and I haven't studied law (except for what I have come across in the industry) and I don't have any leads/examples to point to, but I'm sure if you raised these points with an attorney he would be able to help you more.|||It's like the line from Animal House...
My advice to you is to drink heavily...
I didn't know you where in pre Med?
Pre Med, SQL Server DBA, what's the difference?
Monday, March 19, 2012
DMO
Although this is a client side issue (VB.NET) I was wondering if anyone
could help.
I'm iterating through the stored procedures for the names but its missing
some out and duplicating or triplicating others. The count of sprocs is
correct, does this mean that a sql system table is incorrect.
Any ideas?
Thanks
FrankIs it possible that there are mustiple copies of the SP created under
different users? This can happen is someone logs into the DB as a user
without DBO rights, and creates a SP without specifying the DBO prefix as
part of the SP name.
For example, if Frank logs into the DB and creates a SP like this:
CREATE PROCEDURE usp_order_insert
Then there could be 2 different versions of the SP:
DB.frank.usp_order_insert
DB.dbo.usp_order_insert
"Frank Ashley" <a@.a.com> wrote in message
news:%23OSCFYUYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Although this is a client side issue (VB.NET) I was wondering if anyone
> could help.
> I'm iterating through the stored procedures for the names but its missing
> some out and duplicating or triplicating others. The count of sprocs is
> correct, does this mean that a sql system table is incorrect.
> Any ideas?
> Thanks
> Frank
>|||Thats what I thought at first and in fact the offending sproc was indeed
missing dbo. Even though I fixed this error though it's still not showing
up.
Frank
"JT" <someone@.microsoft.com> wrote in message
news:%23XuiiuVYFHA.3488@.tk2msftngp13.phx.gbl...
> Is it possible that there are mustiple copies of the SP created under
> different users? This can happen is someone logs into the DB as a user
> without DBO rights, and creates a SP without specifying the DBO prefix as
> part of the SP name.
> For example, if Frank logs into the DB and creates a SP like this:
> CREATE PROCEDURE usp_order_insert
> Then there could be 2 different versions of the SP:
> DB.frank.usp_order_insert
> DB.dbo.usp_order_insert
> "Frank Ashley" <a@.a.com> wrote in message
> news:%23OSCFYUYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>|||Did you login as SA or DBO and delete the SPs?
"Frank Ashley" <a@.a.com> wrote in message
news:udq2QhWYFHA.584@.TK2MSFTNGP15.phx.gbl...
> Thats what I thought at first and in fact the offending sproc was indeed
> missing dbo. Even though I fixed this error though it's still not showing
> up.
>
> Frank
> "JT" <someone@.microsoft.com> wrote in message
> news:%23XuiiuVYFHA.3488@.tk2msftngp13.phx.gbl...
as
missing
>|||Yep. Tried that.
"JT" <someone@.microsoft.com> wrote in message
news:%23BudU3WYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Did you login as SA or DBO and delete the SPs?
> "Frank Ashley" <a@.a.com> wrote in message
> news:udq2QhWYFHA.584@.TK2MSFTNGP15.phx.gbl...
> as
> missing
>
could help.
I'm iterating through the stored procedures for the names but its missing
some out and duplicating or triplicating others. The count of sprocs is
correct, does this mean that a sql system table is incorrect.
Any ideas?
Thanks
FrankIs it possible that there are mustiple copies of the SP created under
different users? This can happen is someone logs into the DB as a user
without DBO rights, and creates a SP without specifying the DBO prefix as
part of the SP name.
For example, if Frank logs into the DB and creates a SP like this:
CREATE PROCEDURE usp_order_insert
Then there could be 2 different versions of the SP:
DB.frank.usp_order_insert
DB.dbo.usp_order_insert
"Frank Ashley" <a@.a.com> wrote in message
news:%23OSCFYUYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Although this is a client side issue (VB.NET) I was wondering if anyone
> could help.
> I'm iterating through the stored procedures for the names but its missing
> some out and duplicating or triplicating others. The count of sprocs is
> correct, does this mean that a sql system table is incorrect.
> Any ideas?
> Thanks
> Frank
>|||Thats what I thought at first and in fact the offending sproc was indeed
missing dbo. Even though I fixed this error though it's still not showing
up.
Frank
"JT" <someone@.microsoft.com> wrote in message
news:%23XuiiuVYFHA.3488@.tk2msftngp13.phx.gbl...
> Is it possible that there are mustiple copies of the SP created under
> different users? This can happen is someone logs into the DB as a user
> without DBO rights, and creates a SP without specifying the DBO prefix as
> part of the SP name.
> For example, if Frank logs into the DB and creates a SP like this:
> CREATE PROCEDURE usp_order_insert
> Then there could be 2 different versions of the SP:
> DB.frank.usp_order_insert
> DB.dbo.usp_order_insert
> "Frank Ashley" <a@.a.com> wrote in message
> news:%23OSCFYUYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>|||Did you login as SA or DBO and delete the SPs?
"Frank Ashley" <a@.a.com> wrote in message
news:udq2QhWYFHA.584@.TK2MSFTNGP15.phx.gbl...
> Thats what I thought at first and in fact the offending sproc was indeed
> missing dbo. Even though I fixed this error though it's still not showing
> up.
>
> Frank
> "JT" <someone@.microsoft.com> wrote in message
> news:%23XuiiuVYFHA.3488@.tk2msftngp13.phx.gbl...
as
missing
>|||Yep. Tried that.
"JT" <someone@.microsoft.com> wrote in message
news:%23BudU3WYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Did you login as SA or DBO and delete the SPs?
> "Frank Ashley" <a@.a.com> wrote in message
> news:udq2QhWYFHA.584@.TK2MSFTNGP15.phx.gbl...
> as
> missing
>
DML Statements in code vs. Stored Procedures
Hi,
We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)
We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way".
What i mean by using seperate stored procedures is:
- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)
- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)
- The body contains a DML statement that uses the parameters
- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)
For select statements they think our approach is best...
I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.
Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...
ThanksHere was the previous big discussion on stored procs vs. dynamic sql:
Rob Howard:
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
Then Frans Bouma:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
The Rob Howard rebuttal:
http://weblogs.asp.net/rhoward/archive/2003/11/18/38446.aspx
That should be a good start.
We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)
We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way".
What i mean by using seperate stored procedures is:
- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)
- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)
- The body contains a DML statement that uses the parameters
- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)
For select statements they think our approach is best...
I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.
Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...
ThanksHere was the previous big discussion on stored procs vs. dynamic sql:
Rob Howard:
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
Then Frans Bouma:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
The Rob Howard rebuttal:
http://weblogs.asp.net/rhoward/archive/2003/11/18/38446.aspx
That should be a good start.
Tuesday, February 14, 2012
Distributed transaction error
We have a server, for various reasons has link to a loop back linked server.
We have dozens of stored procedures that refer to the linked server in their
code. They have worked fine for several years until a developer added an on
update/insert trigger to a table. Now, when these stored procedures execute
,
there is a distritbuted transaction error. Distributed Transaction
Coordinator is verified as being on.
Does anyone know of a way to fix this or what is going on?
Thank you.For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
option XACT_ABORT should also be on to support modifications in a
distributed transaction.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Distributed transactions are not supported on loopback linked servers! If
itrigger is requred on a table and references another database on the same
server, hardcode the full 3 part name, ommiting server name.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Thanks for the advice, however, we have tested it with XACT_ABORT set to ON.
One of our database guru's mentioned:
--start quote--
“SQL Server still sees the all the involved statements as a single
transaction and because it have a call to the linked server, it tries to
initiates it as distributed transaction.”
I read in the sql server docs that whenever two databases are involved, sql
server treats the transaction as a distributed transaction even if the
databases are on the same box
--end quote--
Example: our stored procedure has a query similar to the following:
sql1 is the loop back linked server, the database resides ON the same box,
however, for various reasons we have left the sp to reference it as a linked
server.
update mytable
set mytable.column1 = 1
where NOT EXISTS
(select id from sql1.dbo.mytable2 p where p.column = mytable.column)
The following error message is generated whenever the trigger on
update/insert is active on the table mytable. We remove the trigger and the
SP works as always:
error msg: The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
Thanks for any help you can provide.
"Itzik Ben-Gan" wrote:
> For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
> option XACT_ABORT should also be on to support modifications in a
> distributed transaction.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||I appreciate your response Farmer,
however, the trigger itself has no reference at all to the loopback linked
server. It is trying to update a simple field in a table on the same server
as the trigger. The error happens in the SP, which has worked for several
years. If you remove the trigger, the SP works again.
"Farmer" wrote:
> Distributed transactions are not supported on loopback linked servers! If
> itrigger is requred on a table and references another database on the same
> server, hardcode the full 3 part name, ommiting server name.
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||Any data modification statements BEGIN an implisit transactions, that is why
selects worked and update fails now. Because it is involving linked server,
it is considered distributed.
BOL
Starting Transactions
You can start transactions in Microsoft SQL ServerT as explicit,
autocommit, or implicit transactions.
Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server. Each individual Transact-SQL
statement is committed when it completes. You do not have to specify any
statements to control transactions.
Implicit transactions
Set implicit transaction mode on through either an API function or the
Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement
automatically starts a new transaction. When that transaction is completed,
the next Transact-SQL statement starts a new transaction.
Connection modes are managed at the connection level. If one connection
changes from one transaction mode to another it has no effect on the
transaction modes of any other connection.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:A8695506-E70A-4412-8417-97E91699C2A0@.microsoft.com...
> Thanks for the advice, however, we have tested it with XACT_ABORT set to
> ON.
> One of our database guru's mentioned:
> --start quote--
> "SQL Server still sees the all the involved statements as a single
> transaction and because it have a call to the linked server, it tries to
> initiates it as distributed transaction."
> I read in the sql server docs that whenever two databases are involved,
> sql
> server treats the transaction as a distributed transaction even if the
> databases are on the same box
> --end quote--
> Example: our stored procedure has a query similar to the following:
> sql1 is the loop back linked server, the database resides ON the same box,
> however, for various reasons we have left the sp to reference it as a
> linked
> server.
> update mytable
> set mytable.column1 = 1
> where NOT EXISTS
> (select id from sql1.dbo.mytable2 p where p.column = mytable.column)
> The following error message is generated whenever the trigger on
> update/insert is active on the table mytable. We remove the trigger and
> the
> SP works as always:
> error msg: The operation could not be performed because the OLE DB
> provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
>
> Thanks for any help you can provide.
>
> "Itzik Ben-Gan" wrote:
>
We have dozens of stored procedures that refer to the linked server in their
code. They have worked fine for several years until a developer added an on
update/insert trigger to a table. Now, when these stored procedures execute
,
there is a distritbuted transaction error. Distributed Transaction
Coordinator is verified as being on.
Does anyone know of a way to fix this or what is going on?
Thank you.For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
option XACT_ABORT should also be on to support modifications in a
distributed transaction.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Distributed transactions are not supported on loopback linked servers! If
itrigger is requred on a table and references another database on the same
server, hardcode the full 3 part name, ommiting server name.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Thanks for the advice, however, we have tested it with XACT_ABORT set to ON.
One of our database guru's mentioned:
--start quote--
“SQL Server still sees the all the involved statements as a single
transaction and because it have a call to the linked server, it tries to
initiates it as distributed transaction.”
I read in the sql server docs that whenever two databases are involved, sql
server treats the transaction as a distributed transaction even if the
databases are on the same box
--end quote--
Example: our stored procedure has a query similar to the following:
sql1 is the loop back linked server, the database resides ON the same box,
however, for various reasons we have left the sp to reference it as a linked
server.
update mytable
set mytable.column1 = 1
where NOT EXISTS
(select id from sql1.dbo.mytable2 p where p.column = mytable.column)
The following error message is generated whenever the trigger on
update/insert is active on the table mytable. We remove the trigger and the
SP works as always:
error msg: The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
Thanks for any help you can provide.
"Itzik Ben-Gan" wrote:
> For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
> option XACT_ABORT should also be on to support modifications in a
> distributed transaction.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||I appreciate your response Farmer,
however, the trigger itself has no reference at all to the loopback linked
server. It is trying to update a simple field in a table on the same server
as the trigger. The error happens in the SP, which has worked for several
years. If you remove the trigger, the SP works again.
"Farmer" wrote:
> Distributed transactions are not supported on loopback linked servers! If
> itrigger is requred on a table and references another database on the same
> server, hardcode the full 3 part name, ommiting server name.
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||Any data modification statements BEGIN an implisit transactions, that is why
selects worked and update fails now. Because it is involving linked server,
it is considered distributed.
BOL
Starting Transactions
You can start transactions in Microsoft SQL ServerT as explicit,
autocommit, or implicit transactions.
Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server. Each individual Transact-SQL
statement is committed when it completes. You do not have to specify any
statements to control transactions.
Implicit transactions
Set implicit transaction mode on through either an API function or the
Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement
automatically starts a new transaction. When that transaction is completed,
the next Transact-SQL statement starts a new transaction.
Connection modes are managed at the connection level. If one connection
changes from one transaction mode to another it has no effect on the
transaction modes of any other connection.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:A8695506-E70A-4412-8417-97E91699C2A0@.microsoft.com...
> Thanks for the advice, however, we have tested it with XACT_ABORT set to
> ON.
> One of our database guru's mentioned:
> --start quote--
> "SQL Server still sees the all the involved statements as a single
> transaction and because it have a call to the linked server, it tries to
> initiates it as distributed transaction."
> I read in the sql server docs that whenever two databases are involved,
> sql
> server treats the transaction as a distributed transaction even if the
> databases are on the same box
> --end quote--
> Example: our stored procedure has a query similar to the following:
> sql1 is the loop back linked server, the database resides ON the same box,
> however, for various reasons we have left the sp to reference it as a
> linked
> server.
> update mytable
> set mytable.column1 = 1
> where NOT EXISTS
> (select id from sql1.dbo.mytable2 p where p.column = mytable.column)
> The following error message is generated whenever the trigger on
> update/insert is active on the table mytable. We remove the trigger and
> the
> SP works as always:
> error msg: The operation could not be performed because the OLE DB
> provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
>
> Thanks for any help you can provide.
>
> "Itzik Ben-Gan" wrote:
>
Distributed Stored Procedures
I have an SQL Server that has a structure as follows.
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedIf your stored procedure starts with the prefix sp_ SQL server will check
the master database first before the local database.
If you put your stored procedures in the master database and run them within
the context of the destination database without any database prefix doesn't
that work ?
Just an idea, and against the rules of clean coding.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
> Any help will be really appreciated|||"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
A stored procedure in say database 1 is able to be written such that
it can update one, any or all of the other 50 databases you reference
in 2 above. What I am not clear on is what the operational conditions
are like at the moment. How is this currently working (eg: the 500K
lines of app code looks at the procedures in database 1?).
If this is the case, dependent upon your needs for change, it should
be possible only to alter the base stored procs. However parameter
passing may become mandatory.
To have a sproc in database_1 write to database_n you preface the
table names with the database names
eg: select * from database_52..sysobjects
Pete Brown
Falls Creek
Oz
www.mountainman.com.au
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedIf your stored procedure starts with the prefix sp_ SQL server will check
the master database first before the local database.
If you put your stored procedures in the master database and run them within
the context of the destination database without any database prefix doesn't
that work ?
Just an idea, and against the rules of clean coding.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
> Any help will be really appreciated|||"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
A stored procedure in say database 1 is able to be written such that
it can update one, any or all of the other 50 databases you reference
in 2 above. What I am not clear on is what the operational conditions
are like at the moment. How is this currently working (eg: the 500K
lines of app code looks at the procedures in database 1?).
If this is the case, dependent upon your needs for change, it should
be possible only to alter the base stored procs. However parameter
passing may become mandatory.
To have a sproc in database_1 write to database_n you preface the
table names with the database names
eg: select * from database_52..sysobjects
Pete Brown
Falls Creek
Oz
www.mountainman.com.au
Distributed Stored Procedures
I have an SQL Server that has a structure as follows.
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedYou could consider to rename all SPs by adding sp_ prfix and move them to master database. Then when you call them from any database they will execute like they are in the db they are called from. This works only if everything is happening on the same server/instance.
You could test the concept with a few SPs first.
Cheers,
Dragan
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedYou could consider to rename all SPs by adding sp_ prfix and move them to master database. Then when you call them from any database they will execute like they are in the db they are called from. This works only if everything is happening on the same server/instance.
You could test the concept with a few SPs first.
Cheers,
Dragan
Distributed Stored Procedures
I have an SQL Server that has a structure as follows.
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedIf your stored procedure starts with the prefix sp_ SQL server will check
the master database first before the local database.
If you put your stored procedures in the master database and run them within
the context of the destination database without any database prefix doesn't
that work ?
Just an idea, and against the rules of clean coding.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
> Any help will be really appreciated|||"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
A stored procedure in say database 1 is able to be written such that
it can update one, any or all of the other 50 databases you reference
in 2 above. What I am not clear on is what the operational conditions
are like at the moment. How is this currently working (eg: the 500K
lines of app code looks at the procedures in database 1?).
If this is the case, dependent upon your needs for change, it should
be possible only to alter the base stored procs. However parameter
passing may become mandatory.
To have a sproc in database_1 write to database_n you preface the
table names with the database names
eg: select * from database_52..sysobjects
Pete Brown
Falls Creek
Oz
www.mountainman.com.au
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedIf your stored procedure starts with the prefix sp_ SQL server will check
the master database first before the local database.
If you put your stored procedures in the master database and run them within
the context of the destination database without any database prefix doesn't
that work ?
Just an idea, and against the rules of clean coding.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
> Any help will be really appreciated|||"Kofi" <kofi@.ibeaminc.com> wrote in message
news:fc429752.0402260800.61d32398@.posting.google.com...
> I have an SQL Server that has a structure as follows.
> 1. There is a common database that contains hundreds of stored
> procedures.
> 2. There are also 50+ identical databases, with the same number of
> tables and table structures.
> 3. The common stored procedures must be able to read/write data to any
> of these 50+ databases. How can this be done? I can pass the database
> names as a parameter in the stored procedures but that means changing
> hundreds of stored procedures and over 500K lines of application code.
> Is there a way I can let the stored procedures read/write to these
> databases without doing all of this?
A stored procedure in say database 1 is able to be written such that
it can update one, any or all of the other 50 databases you reference
in 2 above. What I am not clear on is what the operational conditions
are like at the moment. How is this currently working (eg: the 500K
lines of app code looks at the procedures in database 1?).
If this is the case, dependent upon your needs for change, it should
be possible only to alter the base stored procs. However parameter
passing may become mandatory.
To have a sproc in database_1 write to database_n you preface the
table names with the database names
eg: select * from database_52..sysobjects
Pete Brown
Falls Creek
Oz
www.mountainman.com.au
Distributed Stored Procedures
I have an SQL Server that has a structure as follows.
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedYou could consider to rename all SPs by adding sp_ prfix and move them to ma
ster database. Then when you call them from any database they will execute l
ike they are in the db they are called from. This works only if everything i
s happening on the same ser
ver/instance.
You could test the concept with a few SPs first.
Cheers,
Dragan
1. There is a common database that contains hundreds of stored
procedures.
2. There are also 50+ identical databases, with the same number of
tables and table structures.
3. The common stored procedures must be able to read/write data to any
of these 50+ databases. How can this be done? I can pass the database
names as a parameter in the stored procedures but that means changing
hundreds of stored procedures and over 500K lines of application code.
Is there a way I can let the stored procedures read/write to these
databases without doing all of this?
Any help will be really appreciatedYou could consider to rename all SPs by adding sp_ prfix and move them to ma
ster database. Then when you call them from any database they will execute l
ike they are in the db they are called from. This works only if everything i
s happening on the same ser
ver/instance.
You could test the concept with a few SPs first.
Cheers,
Dragan
Subscribe to:
Posts (Atom)