SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Tweak my script to work.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blackghost
Starting Member

South Africa
10 Posts

Posted - 08/19/2014 :  02:45:28  Show Profile  Reply with Quote
I Have the below query (Procedure) now our front end can only recognize/use a view. I need to somehow get this saved as a view, I have never changed a Procedure or a Pivot into a view . I hope someone can help on the below, because

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getDocumentType]

AS
BEGIN
    Declare @q varchar(MAX)

    ;WITH N AS (
      SELECT DISTINCT FD_2A7417DC Pvt_Col 
      FROM   FD_Documents
    ), C (Cols) As (
      SELECT STUFF((SELECT ',' + QUOTENAME(Pvt_Col) 
                    FROM   N
                    ORDER BY Pvt_Col
                    FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)') 
                  , 1, 1, '')
    )
    SELECT @q 
           = 'SELECT ID_Number, Company, Date_Of_Birth, Department, Name
                   , Surname, Passport_No, Job_Title, Start_Date
                   , End_Date, Type
                   , ' + Cols + ' 
              FROM  (SELECT FD_0D39C6B0 AS Company, FD_DDD24E25 AS Date_Of_Birth, FD_74290EA9 AS Department, FD_2A7417DC AS Document_Type, FD_E3421C61 AS Name
                          , FD_2B9CFA8C AS Surname, FD_96990BE2 AS ID_Number, FD_E6621D8A as Passport_No, FD_3744C28A AS Job_Title, FD_903EBB60 as Start_Date
                          , FD_9BBAD0CA AS End_Date, FD_AE108F93 as Type
                     FROM FD_Documents
                    ) x
                    PIVOT 
                    (COUNT(Document_Type) FOR Document_Type in (' + Cols + ')) pvt'
    FROM C

    execute(@q)
END
GO

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/19/2014 :  04:39:58  Show Profile  Reply with Quote
Why can you only see a view? if you are talking about trying to read the stored procedure through an application such as Crystal Reports and you cant see it, this is because you need to set the permission level on the stored procedure in the db for those users you wish to see it.

We are the creators of our own reality!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/19/2014 :  04:44:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can make a workaround by creating a self-looped linked server.
And then use "EXEC sp_name_here" with openquery as datasource in the view.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Blackghost
Starting Member

South Africa
10 Posts

Posted - 08/19/2014 :  04:49:07  Show Profile  Reply with Quote
Hi sz1

This is not the case, our front end will only view a report using ether a view or a select statement, What needs to happen is we save the script or the view name and the DB pulls the report from the back-end.

now the way i constructed the report, is exactly what we need to see, however the front end users cannot access this report because of the restriction of how we are able to pull these reports.

I hope I'm making sense?
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/19/2014 :  04:59:06  Show Profile  Reply with Quote
Hi Blackghost, neat name!, I create reports all the time and I have many a time when users say I can’t run the report! This is because they can’t access the view or SP.
In these cases I set the permissions on the db object for them to be able to select and execute, this solves their problems. Of course always making sure there are no security issues around access.


We are the creators of our own reality!
Go to Top of Page

Blackghost
Starting Member

South Africa
10 Posts

Posted - 08/19/2014 :  06:00:37  Show Profile  Reply with Quote
Hi sz1

Thx, I like it too.

I can assure you that the permissions are correct, i sent this to the developers in Germany, they said its because the system can only recognize a view or a select statement in the link...
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/19/2014 :  06:18:17  Show Profile  Reply with Quote
Ok, must be the app but I get it. Have you looked at SwePeso answer in that case.

We are the creators of our own reality!
Go to Top of Page

Blackghost
Starting Member

South Africa
10 Posts

Posted - 08/19/2014 :  06:43:30  Show Profile  Reply with Quote
HI SwePeso

Sorry I missed your reply, to be totally honest, I have no idea, I have never used the function EXEC?? How would I input this into my script I already have?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/19/2014 :  08:23:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Create loopback linked server
EXEC sp_addlinkedserver @server = N'LOOPBACK', @srvproduct = N' ', @provider = N'SQLNCLI', @datasrc = N'(local)', @catalog = N'master';
GO

-- Create procedure
CREATE PROCEDURE dbo.MyStoredProcedureNameHere
AS
...
...
GO

-- Create view
CREATE VIEW dbo.MyViewNameHere
AS

SELECT * FROM OPENQUERY(LOOPBACK, 'EXEC DatabaseNameHere.dbo.MyStoredProcedureNameHere');
GO

-- Test
SELECT * from dbo.MyViewNameHere;
GO



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 08/19/2014 08:27:21
Go to Top of Page

Blackghost
Starting Member

South Africa
10 Posts

Posted - 08/19/2014 :  10:05:16  Show Profile  Reply with Quote
HI SwePeso

I have created Loop back link. (Success report below)

Step one

EXEC sp_addlinkedserver @server = N'LOOPBACK', @srvproduct = N' ', @provider = N'SQLNCLI', @datasrc = N'(local)', @catalog = N'master';
GO

Command(s) completed successfully.

Step two
-- Create procedure

Procedure already created [dbo].[getDocumentType]

Step Three

CREATE VIEW dbo.EOH_Report
AS

SELECT * FROM OPENQUERY(LOOPBACK, 'EXEC FD_71467AE6.dbo.getDocumentType')
GO

Error message - Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'execute(@q)' in procedure 'getDocumentType' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

What did I do wrong?


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/19/2014 :  15:23:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Strange... Have a look at OPENROWSET http://msdn.microsoft.com/en-us/library/ms190312.aspx
to see if that has the same problem.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/19/2014 :  15:24:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Another approach is to have the stored procedure written in SQLCLR.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Blackghost
Starting Member

South Africa
10 Posts

Posted - 08/20/2014 :  01:23:40  Show Profile  Reply with Quote
Hi Swepeso

Apologies for my ignorance, but I have not heard of SQLCLR, still a pup in learning about SQL Server. How would I go about this?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000