Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Methods For Converting a Stored Procedure Expand / Collapse
Author
Message
Posted Thursday, October 29, 2009 5:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:04 AM
Points: 89, Visits: 294
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
Post #810637
Posted Thursday, October 29, 2009 5:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:04 AM
Points: 89, Visits: 294
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
Post #810639
Posted Thursday, October 29, 2009 7:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
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.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #810727
Posted Thursday, October 29, 2009 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 8, 2011 7:13 AM
Points: 1, 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.


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
Post #810766
Posted Thursday, October 29, 2009 8:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #810821
Posted Thursday, October 29, 2009 8:55 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
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.
Post #810836
Posted Thursday, October 29, 2009 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 4:57 AM
Points: 2, Visits: 26
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.
Post #810870
Posted Thursday, October 29, 2009 9:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #810871
Posted Thursday, October 29, 2009 9:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, 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.
Post #810872
Posted Thursday, October 29, 2009 9:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:41 AM
Points: 10, Visits: 77
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...
Post #810902
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse