Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Methods For Converting a Stored Procedure


Methods For Converting a Stored Procedure

Author
Message
clementhuge
clementhuge
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 301
When I deployed the code, the linked server was not recognized (the name of my server is ServerName\Instance1.
It seems that the code does not work with named instance.
I set up a linked server names .\Instance1 and it worked (I added the parameter @sLinkedServer Varchar(80) to the stored procedure.

Clement
clementhuge
clementhuge
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 301
I have performed recently a PCC-DSS Sql server audit and one of the best practice on the list was to avoid distributed transactions.
therefore if your industry requires stringent security rules, you have to make sure that allowing server properties are not against your industry rules.

Clement
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1803 Visits: 4626
we just migrated a server to new hardware and SQL 2005 from 2000 and got rid of a lot of openrowset code in the process. One reason is there is a bug in SQL 2005 SP2 and earlier. I forgot the CU where they fixed it, but it was due to a PSS case we opened. almost 2 years ago we bought a new reporting server and set it up according to security best practices. no domain administrator account to run the service.

a few months later people complain that they can't see some indexes in a database. we open a case and that was about the time we find that using a linked server to that server was flaky and didn't work a lot of times with integrated authentication. turns out there was a bug if you install SQL according to BOL instructions then some things won't work.
cwalker-700550
cwalker-700550
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
I'm stupid. you are using MASTER and I made a best practice comment on the assumption you weren't using MASTER. I should stop answering the phone when I read these columns. Hehe


Just an aside, I never name my stored procedures with sp_XXX as this is normally reserved to MASTER.

It also causes additional searches and added i/o
It will first search MASTER for the procedure, if not found it will then acquire a exclusive COMPILE lock to perform a second search of the other databases
Also if the procedure has the same name as an sp_XXX procedure in MASTER your stored procedure will never execute

Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1055 Visits: 1499
David McKinney (10/29/2009)
ruedifuchs (10/29/2009)
I agree, the code for using OPENROWSET is very interesting.

But since the stored procedure has to return a result set, what is the advantage over using a table-valued function?


I'm not sure if I understand your question, ruedi, but here goes.

The examples given, sp_who, sp_lock, are SYSTEM stored procedures (i.e. not yours!) - they are not table-valued functions. The data returned by these is very useful - but difficult to use effectively (filter, join, sort etc) unless you can put it into a recordset of some sort (table / view / CTE etc). The OPENROWSET code enables you to do that. Short of rewriting the entire sp as a table-valued function, I'm not sure how a table-valued function can help you?

Does this clarify?


I think that "ruedifuchs" meant that a Table Valued Function does provide a recordset including the table definition and could probably replace the stored procedure and be called directly with a select statement.

Some advantages are...
It can use stored procedures to populate it.
It can utilize temp tables where views cannot.
It can have parameters.
It will present the data definition of it's output to a calling program where a stored procedure will not.
It can be used in the FROM clause of a view if it doesn't have parameters.

Some disadvantages are...
Excel will not allow you to use it for a datasource.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

David McKinney
David McKinney
SSC Eights!
SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)

Group: General Forum Members
Points: 875 Visits: 2090
Tom Garth (10/29/2009)
David McKinney (10/29/2009)
ruedifuchs (10/29/2009)
I agree, the code for using OPENROWSET is very interesting.

But since the stored procedure has to return a result set, what is the advantage over using a table-valued function?


I'm not sure if I understand your question, ruedi, but here goes.

The examples given, sp_who, sp_lock, are SYSTEM stored procedures (i.e. not yours!) - they are not table-valued functions. The data returned by these is very useful - but difficult to use effectively (filter, join, sort etc) unless you can put it into a recordset of some sort (table / view / CTE etc). The OPENROWSET code enables you to do that. Short of rewriting the entire sp as a table-valued function, I'm not sure how a table-valued function can help you?

Does this clarify?


I think that "ruedifuchs" meant that a Table Valued Function does provide a recordset including the table definition and could probably replace the stored procedure and be called directly with a select statement.



Tom,

....could probably replace WHICH stored procedure? The sp_who / sp_lock? If this is what you mean, then I agree, sp_who, sp_lock would be handier if they were table type functions, BUT THEY ARE NOT! Microsoft didn't write them that way. Until they do (and as was pointed out DMV's may be an alternative), this (with some caveats) provides a fairly generic way of converting their output to a recordset. The alternative would be to rewrite ourselves sp_who / sp_lock as a table type function, but that's hardly a generic or quick solution.

Do you get my point .... or have I missed yours?

Regards,

David.
ruedifuchs
ruedifuchs
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 27
Yes, Tom,

Thanks for pointing out the problems with Microsofts built-in stored procedures.
If the OPENROWSET can handle this, that is great.

BTW:
In Excel I'm using table-valued functions and do not specify "Table" but "SQL" on a datasource (which provides the connection to the database):

select *, Datediff(day,open_date,getdate()) [Days Open]
from dbo.tvFn_TicketsLastActivity('IT Application Management', 1)
order by 15,6 desc

As you can see my table-valuied-function is called with parameters, but things like getdate() and sorting are not accepted in the table-valued-function and must be.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1055 Visits: 1499
Do you get my point .... or have I missed yours?

Regards,

David.


Maybe both Dave. I thought the article was real good and gave it a good rating. The UDF would not handle the multiple sp_ procedures easily because of the need for a defined output.

Thanks,

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Rob Fisk
Rob Fisk
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 428
First off. Nice article with a lot of things to think about.

As mentioned in the previous couple of posts. A ConvertProcToFunction would, in a lot of cases be even handier.

It's given me something to think about and some good starting blocks. I shall ponder on this further.

_______________________________________________________
Change is inevitable... Except from a vending machine.

Ramon-218872
Ramon-218872
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 114
Hi to all,

I'm trying to use the code, because it's that I need, but I get the error:

Msg 7357, Level 16, State 2, Procedure sp_ConvProc2View, Line 27
Cannot process the object "SET FMTONLY OFF EXEC sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.


Some body can help me?

Thanks in advance...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search