Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

Do some statistics on calling a specific stored function

Dear all,
We would like to keep a counter on how many time a specific stored fuction
is called.
At first, we want to add this counter inside the function but update record
to table is not allow in stored function.
Is there any other method to do so?
IvanYou could run profiler, or you can have audit statements before the function
is called if it is within a proc.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ivan" <ivan@.microsoft.com> wrote in message
news:uF76fgk%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Dear all,
> We would like to keep a counter on how many time a specific stored fuction
> is called.
> At first, we want to add this counter inside the function but update
> record to table is not allow in stored function.
> Is there any other method to do so?
> Ivan
>

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?
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

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
>

do queries running from clr stored procs tie up one available connection

I'm wondering if one less external sql server connection is available when my clr stored proc querys my database inside of the db engine.

AFAIK, no

Niels

do process

how can I do
perform a stored procedure with paramert from my client application but my q
uestion is sent call but transfer process to sqlserver a for example i can c
lose my application but process continue running in a sqlserver until finiss
hed, similar a job but with
out job.
Thank You
AlfSome environments and providers (e.g. .NET) will allow you to make a stored
procedure call asynchronous, meaning it does not have to wait for the
result.
So, it is hard to say if your client application can do that, because you
forgot to tell us anything about your client application.
A
<ag> wrote in message news:%23iXkfN6QGHA.4740@.TK2MSFTNGP14.phx.gbl...
> how can I do
>
> perform a stored procedure with paramert from my client application but my
> question is sent call but transfer process to sqlserver a for example i
> can close my application but process continue running in a sqlserver until
> finisshed, similar a job but without job.
> Thank You
> Alf
>

Do locks slow other processes?

Hi,
Lets say I have a process running on the server (e.g a stored proc) -
Process A
Then another scheduled process starts to run but is locked up by process A.
Will process A run any slower as it's holding up the second process, or,
does it not affect the performance at all?
Basically, I'm quite happy for the second process to have to wait - but - I
don't want the performance to be radically slowed.
ThanksLondon
http://www.sql-server-performance.com/reducing_locks.asp
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>|||Process A should not be slowed by processes which are waiting on A.
Offcourse if there are more processes which are running or claiming memory,
process A can get slowed. A waiting process should consume very little (or
no) cpu. It is offcourse in a list of processes so the OS uses a very very
VERY small amount of CPU to check or pass over this process when
rescheduling, SQL-server has to set a wait on a lock this consumes very VERY
VERY small amount of cpu.
Offcourse if the process which is blocked by A holds locks and process A
needs those resources you wil have a deadlock. This holds both processes
till a deadlock is detected, one process is 'aborted' the other can
continue.
ben brugman
"London Developer" <dev@.nowhere.com> wrote in message
news:OSO5ge8lDHA.744@.tk2msftngp13.phx.gbl...
> Hi,
> Lets say I have a process running on the server (e.g a stored proc) -
> Process A
> Then another scheduled process starts to run but is locked up by process
A.
> Will process A run any slower as it's holding up the second process, or,
> does it not affect the performance at all?
> Basically, I'm quite happy for the second process to have to wait - but -
I
> don't want the performance to be radically slowed.
> Thanks
>sql

do i need to deny everything i don't use ?

hi again
i have an account with its password
with specific permissions,
i have to deny, then, the access
to the rest of the objects ?
i.e. systems stored proc, tables , etc ?
thanks
atte,
Hernn Castelo
SGA - UTN - FRBA
Depends what you want.
The user will only have access to those objects it is granted so there is no
need to deny.
But if the user is then added to a role which has other permissions (or they
are given to public) it will gain them.
If this is not what you want then you should deny permissions as well.
I usually only deny dbwriter to read only accounts and leave the rest to
gain from granted permissions
"Hernán Castelo" wrote:

> hi again
> i have an account with its password
> with specific permissions,
> i have to deny, then, the access
> to the rest of the objects ?
> i.e. systems stored proc, tables , etc ?
> thanks
>
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>
>
|||i'm asking because
i entered with a restricted account
and was able to exec SP_HELPTEXT
and i don't wish that
denying dbwriter sounds good,
how can i disable these type of sp's ?
atte,
Hernn Castelo
SGA - UTN - FRBA
"Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> Depends what you want.
> The user will only have access to those objects it is granted so there is
no
> need to deny.
> But if the user is then added to a role which has other permissions (or
they[vbcol=seagreen]
> are given to public) it will gain them.
> If this is not what you want then you should deny permissions as well.
> I usually only deny dbwriter to read only accounts and leave the rest to
> gain from granted permissions
> "Hernn Castelo" wrote:
|||Everyone can see the source code, I'm afraid. Closest you can come is creating the procedures using
the WITH ENCRYPTION option (note however that there exists tools to decrypt...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OO0nNnM0EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i'm asking because
> i entered with a restricted account
> and was able to exec SP_HELPTEXT
> and i don't wish that
> denying dbwriter sounds good,
> how can i disable these type of sp's ?
>
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
> "Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
> news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> no
> they
>

do i need to deny everything i don't use ?

hi again
i have an account with its password
with specific permissions,
i have to deny, then, the access
to the rest of the objects ?
i.e. systems stored proc, tables , etc ?
thanks
atte,
Hernn Castelo
SGA - UTN - FRBADepends what you want.
The user will only have access to those objects it is granted so there is no
need to deny.
But if the user is then added to a role which has other permissions (or they
are given to public) it will gain them.
If this is not what you want then you should deny permissions as well.
I usually only deny dbwriter to read only accounts and leave the rest to
gain from granted permissions
"Hernán Castelo" wrote:

> hi again
> i have an account with its password
> with specific permissions,
> i have to deny, then, the access
> to the rest of the objects ?
> i.e. systems stored proc, tables , etc ?
> thanks
>
> --
> atte,
> Hernán Castelo
> SGA - UTN - FRBA
>
>|||i'm asking because
i entered with a restricted account
and was able to exec SP_HELPTEXT
and i don't wish that
denying dbwriter sounds good,
how can i disable these type of sp's ?
atte,
Hernn Castelo
SGA - UTN - FRBA
"Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> Depends what you want.
> The user will only have access to those objects it is granted so there is
no
> need to deny.
> But if the user is then added to a role which has other permissions (or
they[vbcol=seagreen]
> are given to public) it will gain them.
> If this is not what you want then you should deny permissions as well.
> I usually only deny dbwriter to read only accounts and leave the rest to
> gain from granted permissions
> "Hernn Castelo" wrote:
>|||Everyone can see the source code, I'm afraid. Closest you can come is creati
ng the procedures using
the WITH ENCRYPTION option (note however that there exists tools to decrypt.
.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hernn Castelo" <bajopalabra@.hotmail.com> wrote in message
news:OO0nNnM0EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i'm asking because
> i entered with a restricted account
> and was able to exec SP_HELPTEXT
> and i don't wish that
> denying dbwriter sounds good,
> how can i disable these type of sp's ?
>
> --
> atte,
> Hernn Castelo
> SGA - UTN - FRBA
> "Nigel Rivett" <sqlnr@.hotmail.com> escribi en el mensaje
> news:FA87EBFA-AE01-4BDD-8869-E62687DB27FB@.microsoft.com...
> no
> they
>

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.

Do I need cursors?

I am creating a stored procedure to send emails (with xp_sendmail, I think)
based on certain conditions. This is my logic:
I need to loop through all of the Table1 records.
if Table2 exists for Table1 and Table2.column='T'
send variation 1 of email
else if table2 exists for table 1 and Table2.column='F'
send variation 2 of email
else if table3 exists for table 1
send variation 3 of email
Should I use a cursor to loop through the Table1 records? Or should I join
Table1 and Table2/Table3 and not have a top level query? I'm thinking I
should not use a cursor.
My second problem is this. Depending on the email variation I need to loop
through some records and concatenate their values. Do I need a cursor for
that? Or do I have other options?
Thanks for any help, I really appreciate it.Nick
DECLARE @.EmailName VARCHAR(100),@.userid VARCHAR(20)
IF EXISTS (SELECT * FROM Table2 JOIN Table1 ON Table2.pk=Table1.pk AND
Table2.column='T')

> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
SET @.userid='john,arie,alex'
SELECT @.EmailName=@.EmailName+COALESCE(Emailadd,
'') +',' FROM users where
CHARINDEX(',' + userid + ',',','+ @.userid +',')>0 and EmailName IS NOT
NULL
SET @.EmailName=LEFT(@.EmailName,LEN(@.EmailNam
e)-1)
--Send emails
I'm currently unable to test it but I'm sure it gives you an idea.
"Nick" <nickfinity@.nospam.nospam> wrote in message
news:48C35529-4878-455F-9DCC-F97057A4A5B8@.microsoft.com...
>I am creating a stored procedure to send emails (with xp_sendmail, I think)
> based on certain conditions. This is my logic:
> I need to loop through all of the Table1 records.
> if Table2 exists for Table1 and Table2.column='T'
> send variation 1 of email
> else if table2 exists for table 1 and Table2.column='F'
> send variation 2 of email
> else if table3 exists for table 1
> send variation 3 of email
> Should I use a cursor to loop through the Table1 records? Or should I join
> Table1 and Table2/Table3 and not have a top level query? I'm thinking I
> should not use a cursor.
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
> Thanks for any help, I really appreciate it.|||>From the limited information you've posted, it sounds like a CASE
statement is what you're looking for. In general, the only time I ever
find that I must use a cursor is when I have to call a stored procedure
on each value in a resultset. They seem to be much more useful to me in
ad hoc situations than in deployed solutions.
CASE Example in an UPDATE (sorry for the poor formatting):
UPDATE <tablealias1>
SET EmailAddress = CASE WHEN <condition1> THEN <expression>
WHEN <condition2> THEN <expression> ELSE <expression> END
FROM Table1 <tablealias1> INNER JOIN Table2 ON <...>
WHERE <condition>

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?

Do CR/LF get stored in a text column

I am capturing user comments in a textarea field on a web form and then storing the comments in a text column in a DB table. I would like to know if the carriage returns and linefeeds get stored in the database (MS SQL 2000)?I would say yes...

USE Northwind
GO

CREATE TABLE myTable99 (col1 varchar(8000))
GO

DECLARE @.x varchar(8000)
SELECT @.x = 'Wasted away again in
Margaritaville'
INSERT INTO myTable99 (Col1) SELECT @.x
SELECT @.x
GO

DROP TABLE myTable99
GO|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
I would say yes...
[/quote

I second that.sql

DNN DAL SqlDataProvider Passing NULL to SQL Stored Procedure

Hello,

I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value. My SQL Table boolean column allows nulls.

I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table. One of those fields is a boolean. I create an instance of the InfoObject in my code and assigns values to the various properties. The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything. I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL. If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens. How do I pass a null to the SQL database for a boolean field? I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean." Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?

Any help would be greatly appreciated. I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.

Thanks in advance for any help.Please help?!|||The issue was with my InfoObject construction. DNN Core Team provided the solution. You can see it athttp://www.dotnetnuke.com/Community/ForumsDotNetNuke/tabid/795/forumid/118/threadid/41618/threadpage/3/scope/posts/Default.aspx

DMX in TSQL?

Hi, I'm new to Transact-SQL and I'm trying to throw a DMX query I have created into a stored procedure because I think it's the only way to pass variables into an openquery statement! It seems that no matter what I try gets me the error 'Incorrect usage of quotes'. I'm trying to use code like this:

Code Snippet

AS

BEGIN

DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)

SET @.LinkedServer = 'DMSERVER'

SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''

SET @.TSQL = 'SELECT FLATTENED * FROM ['+@.miningModel+'].CONTENT'')'

EXEC (@.OPENQUERY+@.TSQL)

END

Which I found from some one else's posting on the data mining forums. I'm completely clueless as to how to make this work - Can I even do what I want to do? It seems like the brackets are causing all the fuss, but they're necessary for DMX queries. Any ideas? Thanks!

Code Snippet

AS

BEGIN

DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)

SET @.LinkedServer = 'DMSERVER'

SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''

SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'

EXEC (@.OPENQUERY+@.TSQL)

END

|||

Still doesn't work...here's the error:

Code Snippet

TITLE: Microsoft Report Designer

An error occurred while retrieving the parameters in the query.
SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "AS
BEGIN
DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)
SET @.LinkedServer = 'abc'
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'
EXEC (@.OPENQUERY+@.TSQL)

END", incorrect usage of quotes.

ADDITIONAL INFORMATION:

SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "AS
BEGIN
DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)
SET @.LinkedServer = 'abc'
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'
EXEC (@.OPENQUERY+@.TSQL)

END", incorrect usage of quotes. (System.Data)

BUTTONS:

OK

|||are you initializing the variable @.miningModel?

|||

I have it defined as a report parameter in reporting svcs, with default value of 'PRRelational'... Even if I make it a local variable it still throws the same error...

EDIT: Turns out I was doing this from the wrong location...! Thanks. Will post if I get it to work.

EDIT2: Post shows how to do this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1676053&SiteID=1&mode=1

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
>

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.

DML delete issues

Hi all,

I'm trying to return a processed Excel XML document from a stored procedure.

The procedure pulls a template document in XML format from a table, inserts data using DML and returns the result. The problem I've hit is in removing worksheets from the document prior to returning it. Inserts work fine when I try to remove a worksheet everything hangs. I'm thinking this could be a namespace problem, but am at a loss.

A small example(assume that @.XML contains a standard ExcelXML document):

set @.XML.modify('declare namespace ss="urnchemas-microsoft-comfficepreadsheet";
declare namespace o="urnchemas-microsoft-comfficeffice";
declare namespace x="urnchemas-microsoft-comffice:excel";
delete (/ss:Workbook[1]/ss:Worksheet[1])')

Any help greatly appreciated...

Regards,

Andy

I tried your case (delete the first Worksheet from a excel file in 2003 xml format) and it works fine:

Code Snippet

declare @.x xml = '<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>jinghaol</Author>
<LastAuthor>jinghaol</LastAuthor>
<Created>2007-04-24T16:15:41Z</Created>
<LastSaved>2007-04-24T16:17:18Z</LastSaved>
<Company>Microsoft</Company>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12345</WindowHeight>
<WindowWidth>18960</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">adsf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">asdf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="2" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:Index="2">
<Cell><Data ss:Type="String">asdf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>'
select @.x
set @.x.modify('
declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
declare namespace o="urn:schemas-microsoft-com:office:office";
declare namespace x="urn:schemas-microsoft-com:office:excel";
delete (/ss:Workbook[1]/ss:Worksheet[1])')
select @.x
go

I couldn't exactly copy/paste/run your query because the editor puts many and in namespace declare part.

DLL initialization failure using CDOSYS mail

We have a SQL 2000 stored procedure to send notification emails using CDOSYS and OLE Automation. It has been happily sending out emails for quite a while now from both of our dev and prod machines.

The other day I added a line of code to format the message body variable. I tested the change in a T-SQL script in dev, then added the line into the procedure and recompiled it in dev using an ALTER PROC script. I then called the dev proc and everything is still good. The change has no impact to the sp_OA* commands.

So then I used the same ALTER PROC script and pointed it to production. There is no difference between the dev and prod procs so this was OK. The script ran OK and the proc was updated with the change. However, now only the prod proc doesn't work. Further, the same code in a T-SQL script also fails. But everything remains fine in the dev environment.

We restored the database that had the email SP to a point prior to the change, but the problem persists. It is as if recompiling the proc has disabled the CDOSYS capability from SQL server. CDOSYS still works from VBscript on the server.

The error message:

Msg 50000, Level 18, State 3, Procedure usp_SendEmail, Line 154

Error in Email Object: Source: CDO.Configuration.1 . Description: A dynamic link library (DLL) initialization routine failed.

(EOLIAN.Tools.dbo.usp_SendEmail)

Here's a bit of the code:

DECLARE @.iMsg int,

@.hr int

EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

IF @.HR <> 0 GOTO Error_Handling

EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

IF @.HR <> 0 GOTO Error_Handling

I encountered a similar problem a few months ago when we collocated our DB server (Win2K, SQL 2005) and changed the domain it was in. At the time, granting the login that runs the SQL Server service access to the System32/InetSrv directory fixed the problem (seemed to be a metabase access issue). The one difference is that we use the Pickup directory (SendUsing=1).

A few weeks later, things stopped working again. Like you, I've tested using a VBScript logged in as the same account that's running the SQL Server service and the emails gets generated without difficulty. But using the stored proc or a pared-down SQL script generates the same DLL initialization error in CDO.Configuration.1.

This proc has been in use since we converted to SQL 2000 and has not been altered for some time (>12mo). The only change is the domain change which clearly introduced a number of security implications. However, that doesn't explain why it worked and then just stopped working.

If I figure out the problem, I'll post again. If you figure out hte problem, please post as well as we may be chasing the same issue.

|||Moving to the T-SQl group.|||The problem is resolved, although not understood. We rebooted the server and restarted SQL and all is well again. Not sure what caused the problem, or what the problem was.|||The solution was short lived. The DLL initialization failure message is back. :-(

dll & Web Service in VB.Net 2005 Stored Procedure

Hi,
I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProject Template.
Now, I have two questions for this stored procedure.
1) How can I import and execute the .dll in this Stored Procedures?
2) How can I connect the Web Service and get the result in this Stored
Procedures?
---
Partial Public Class StoredProcedures1
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetTable()
Dim strSQL As String = "SELECT * FROM Table1"
Using conn As New SqlConnection("context connection=true")
Using cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = strSQL
.CommandType = CommandType.Text
conn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd)
.Connection.Close()
End With
End Using
End Using
End Sub
End Class
---
Thanks!"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am writing a vb.net2005 program that needs to create a stored procedure
> with SqlServerProject Template.
> Now, I have two questions for this stored procedure.
> 1) How can I import and execute the .dll in this Stored Procedures?
You need to deploy the assembly into your database (CREATE ASSEMBLY) and
then add a stored procedure referencing the method in the assembly (CREATE
PROCEDURE). Visual Studio will do this for you if you right-click on the
project and select Deploy.
Here's how you would do it by hand:
CREATE ASSEMBLY [SqlServerProject2]
FROM 'c:\SqlServerProject2.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE [GetTable]
AS
EXTERNAL NAME
[SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]

> 2) How can I connect the Web Service and get the result in this Stored
> Procedures?
>
From the web service just connect to the database and execute it with a
SqlCommand.
David|||Hi David,
sorry, i think that u were misunderstood my problem.
My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
.dll & web service to get some value.
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:O3xF9H5iGHA.45
04@.TK2MSFTNGP05.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> You need to deploy the assembly into your database (CREATE ASSEMBLY) and
> then add a stored procedure referencing the method in the assembly (CREATE
> PROCEDURE). Visual Studio will do this for you if you right-click on the
> project and select Deploy.
> Here's how you would do it by hand:
> CREATE ASSEMBLY [SqlServerProject2]
> FROM 'c:\SqlServerProject2.dll'
> WITH PERMISSION_SET = SAFE
> GO
> CREATE PROCEDURE [GetTable]
> AS
> EXTERNAL NAME
> [SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]
>
> From the web service just connect to the database and execute it with a
> SqlCommand.
> David
>|||"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> Hi David,
> sorry, i think that u were misunderstood my problem.
> My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
> .dll & web service to get some value.
>
To use another .dll add a reference to your database project. To use a web
service add a web reference.
David|||Hi David,
1) For DLL, VS.Net 2005 is not allow me to import a new References in
SqlServerProject Template.
2) For Web Service, it will occur error when running.
----
--
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub SPWithWebService()
Dim WebService As New SqlServerProject1.localhost.Service
Dim msg As String = CStr(WebService.HelloWorld())
SqlContext.Pipe.Send(msg)
End Sub
End Class
----
--
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:OqYxDh5iGHA.34
96@.TK2MSFTNGP04.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> To use another .dll add a reference to your database project. To use a
> web service add a web reference.
> David
>|||Hello James,
When you are using the VS SQL Server Project you are restricted to what
assemblies you can reference. This behavior is by design. However, you
could create a normal class library project, add references as per usual
and then manually create your assembly through CREATE ASSEMBLY. Make sure
your referenced
assemblies are in the same directory as your user assembly.
If you want to use the VS SQL Server Project you can create the assembly
you want to reference in the database before you try to reference it. You
need do a manual CREATE ASSEMBLY against the DLL you want.
As for Web service , you will also need to use the CLR SDK tool SGEN.EXE to
create and register a helper assembly for the assembly generated by WSDL,
as described in:
http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.