Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts

Monday, March 19, 2012

Mysterious Stored Procedures

SQL Server 2005 sp1

Maybe you can answer this.

Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?

Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?

Thanks!

Frank

These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:

dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_verstamp007
dt_whocheckedout
dt_whocheckedout_u
dtproperties

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Good morning, Jens.

Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?

Thanks a lot.

Fernando M.

|||Yes, if you need created database diagrams you should leave the dbpoperties.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Mysterious Stored Procedures

SQL Server 2005 sp1

Maybe you can answer this.

Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?

Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?

Thanks!

Frank

These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:

dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_verstamp007
dt_whocheckedout
dt_whocheckedout_u
dtproperties

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Good morning, Jens.

Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?

Thanks a lot.

Fernando M.

|||Yes, if you need created database diagrams you should leave the dbpoperties.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Monday, March 12, 2012

mysterious corruption of stored procedures

Hi everyone,

It looks like a mystery, but I hope there should be some explanation to
the issue I experience. Once in a blue moon a random stored procedure
stops working the way it was designed. The stored procedure code looks
unchanged. Recompiling, altering the code do not help. It looks like it
is simply does not execute some part of it or does not run at all.
However it returns no errors.
One time a procedure entered into infinite loop and almost hang the
whole server.

When I copy procedure code and save it under different name, it works
as designed. But nothing helps with existing procedure. The only way
how to fix it is to completely drop and recreate.

The problem is, that you usually have to do it in the middle of the
business day after you spent few hours trying to realize what went
wrong before you realize that you got another mysterious corruption. Of
cause I have no clue of how to detect such things in advance and to
prevent them from occurring in the future.

I can guarantee that the SQL code in those procedures was absolutely
bug free, fully tested and was working fine for a long time.

For the first time I thought that internal compiled code might corrupt.
In this case altering or recompiling should help.
I also thought about execution plan, but it should be also fixed by
doing things above.
DBCC checkdb does not find any errors
The issue never goes away until stored procedure is manually dropped
and recreated with the same SQL code.

So, I'm asking all if someone experienced something similar and can
explain how to prevent it, please share the knowledge. I would
appreciate any type of help.

Thank you.Sergey (afanas01@.gmail.com) writes:
> It looks like a mystery, but I hope there should be some explanation to
> the issue I experience. Once in a blue moon a random stored procedure
> stops working the way it was designed. The stored procedure code looks
> unchanged. Recompiling, altering the code do not help. It looks like it
> is simply does not execute some part of it or does not run at all.
> However it returns no errors.
> One time a procedure entered into infinite loop and almost hang the
> whole server.
> When I copy procedure code and save it under different name, it works
> as designed. But nothing helps with existing procedure. The only way
> how to fix it is to completely drop and recreate.

Have you ruled out the possibility there are two procedures with the same
name, but different schema? (= different owner in SQL 2000?) Or for some-
thing really exotic, what about numbered procedures? (That is, some_sp;2)

If you for instance change the code of the corrupted procedure to say
"PRINT 'Hello!" and nothing else, does print the message, or does it
continue what it used to do?

When this happens a SELECT * FROM sysobjects WHERE name = 'procname'
may reveal something.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||No numbered procedures, no different schema/owner. Everything is "plain
vanilla". Unfortunately, I did not have chance to play with corrupted
procedures.Usually when you have a production problem everybody whant
a) have it fixed ASAP and only then b) provide them with reasonable
explanation. I have a problm with b) ;-)|||Sergey (afanas01@.gmail.com) writes:
> No numbered procedures, no different schema/owner. Everything is "plain
> vanilla". Unfortunately, I did not have chance to play with corrupted
> procedures.Usually when you have a production problem everybody whant
> a) have it fixed ASAP and only then b) provide them with reasonable
> explanation. I have a problm with b) ;-)

I would still encourage you to examine sysobjects next time it happens.
Maybe there is something in your system, you did not know of. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again

Monday, February 20, 2012

My guess is stored procedures

Greetings All!
I just started playing with MS SQL and was wondering how i could
possibly do the following for example.
I do a 'SELECT source FROM history' and that gives me a list of
sources. Now i want to run a SELECT for each source i received from
the previous SELECT.

For further clarity, in programming terms, it would be just like
looping through an array.

Thanks Guys,
PatricePlease define what you mean by "run a SELECT for each source". It might help
to post the DDL (CREATE TABLE statements) and some sample data (INSERT
statements) for your table(s). Also, show the end-result you would want to
get from that sample data.

In general in SQL you should try to minimise procedural code such as loops.
SQLServer is optimised for set-based queries. If you want to SELECT a set of
rows for each "source" then you can probably write that query as a JOIN to
one or more tables.

For example (from the Pubs database). To retrieve all the Titles for each
Author:

SELECT A.au_fname+' '+A.au_lname AS author, T.title
FROM Authors AS A
JOIN TitleAuthor AS B
ON A.au_id = B.au_id
JOIN Titles AS T
ON B.title_id = T.title_id
ORDER BY A.au_lname, A.au_fname

--
David Portas
SQL Server MVP
--|||Hi David,
Sorry for the lack of clarity.

My plan is to detect if any users login from more than 3 sources.

I have a history table
CREATE TABLE history(client_id int, when datetime, source varchar(20))

After some inserts a SELECT * FROM history looks like

-------------
|client_id | when | source |
-------------
|1010 | <date> | 202.22.21.33 |
-------------
|1818 | <date> | 192.10.22.31 |
-------------
|1542 | <date> | 222.452.1.36 |
-------------
|1010 | <date> | 192.22.21.33 |
-------------
|1010 | <date> | 242.22.21.33 |
-------------
|1010 | <date> | 256.22.21.33 |
-------------

so from this table i would to a
SELECT DISTINCT client_id from history (if thats the syntax) this would
return
----
|client_id |
----
|1010 |
----
|1818 |
----
|1542 |
----

Now from these values i want do a
SELECT count(DISTINCT source) from history where client_id = 1010

this would return 4. Since client_id 1010 logged in from 4 different
sources.
Then i want to run the same query with client_id = 1818 and so on. I.e
loop through the client ids.

How can i do this in a stored proc. My guess is cursors. Any help?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||No need for cursors or loops. This should give the result you want:

SELECT client_id, COUNT(DISTINCT source)
FROM History
GROUP BY client_id

--
David Portas
SQL Server MVP
--|||
Dave, you're a champ!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!