Tweak my script to work. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Tweak my script to work.

Author

Topic

Blackghost
Starting Member

10 Posts

Posted - 2014-08-19 : 02:45:28

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
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 04:39:58

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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-19 : 04:44:47

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

Blackghost
Starting Member

10 Posts

Posted - 2014-08-19 : 04:49:07

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?

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 04:59:06

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!

Blackghost
Starting Member

10 Posts

Posted - 2014-08-19 : 06:00:37

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 06:18:17

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!

Blackghost
Starting Member

10 Posts

Posted - 2014-08-19 : 06:43:30

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-19 : 08:23:46

[code]-- 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[/code]

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

Blackghost
Starting Member

10 Posts

Posted - 2014-08-19 : 10:05:16

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-19 : 15:23:12

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-19 : 15:24:29

Another approach is to have the stored procedure written in SQLCLR.

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

Blackghost
Starting Member

10 Posts