SQL QUERY PROBLEM.. - Database Discussion Boards

It_tech3-Jun-10 23:45

3-Jun-10 23:45

Hi There,

I tried changing it to a Stored procedure but still have a problem

ALTER PROCEDURE [dbo].[sp_LiveRevenueGraph]
-- Add the parameters for the stored procedure here
@ShowType NVARCHAR(20),
@Statistic NVARCHAR(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @data TABLE ([Week] NVARCHAR(120), Sunday DECIMAL(12,5), Monday DECIMAL(12,5), Tuesday DECIMAL(12,5), Wednesday DECIMAL(12,5),
Thursday DECIMAL(12,5), Friday DECIMAL(12,5), Saturday DECIMAL(12,5), SubTotal DECIMAL(12,5))

-- Insert statements for procedure here
-- INSERT INTO @data EXECUTE C_WEB_sp_RevenueAchievedByShow 0, @ShowType;

END

Error : --INSERT EXEC statement cannot be nested

Please advice.

Thanks

Re: INLINE FUNCTION

J4amieC4-Jun-10 0:02

J4amieC

4-Jun-10 0:02

This is the answer you're looking for:

http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure[^]

Google is your friend for this sort of question. Learn to use it.

Re: INLINE FUNCTION

It_tech4-Jun-10 0:35

It_tech

4-Jun-10 0:35

Thanks very much for your help.

I have changed it to the following But seems to have problem with My server Name when using Open Query

Declare @ServName nvarchar(50)
set @ServName= (select @@SERVERNAME)
select @ServName
SELECT * INTO #data FROM OPENQUERY(@ServName, 'EXEc C_WEB_sp_RevenueAchievedByShow 0, @ShowType')

Error : Msg 102, Level 15, State 1, Procedure sp_LiveRevenueGraph, Line 22
Incorrect syntax near '@ServName

Re: INLINE FUNCTION

J4amieC4-Jun-10 0:57

J4amieC

4-Jun-10 0:57

There is no possible way for me to debug that. I have no access to your database, no knowledge of what you're doing. I think you need to spend some time debugging your problem yourself.

Re: INLINE FUNCTION

Eddy Vluggen4-Jun-10 1:48

Eddy Vluggen

4-Jun-10 1:48

Hi,

Just tried it, the code below works for me on the AdventureWorks database;

SQL

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * 
  INTO #MyTempTable 
  FROM OPENROWSET(
	'SQLNCLI', 
	'Server=(local);Trusted_Connection=yes;', 
	'EXEC AdventureWorks.dbo.uspGetEmployeeManagers 1'
  )

Hope this helps Smile | :)

I are Troll Suspicious | :suss:

Re: INLINE FUNCTION

It_tech4-Jun-10 2:51

It_tech

4-Jun-10 2:51

Many thanks for your help.

I have the following Error :

Msg 7403, Level 16, State 1, Line 2
The OLE DB provider "PC-NAME\SqlDev" has not been registered.

Here is how i changed it on my Development :

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT *
INTO #MyTempTable
FROM OPENROWSET(
'Kibrom\SqlDev',
'Server=(local);Trusted_Connection=yes;',
'Exec [dbo].[C_WEB_sp_RevenueAchievedByShow] 1,2'
)

Re: INLINE FUNCTION [modified]

Eddy Vluggen4-Jun-10 3:27

Eddy Vluggen

4-Jun-10 3:27

Nice

--edit
The first argument should be a provider; the name of the server has already been provided as "(local)" - this gets replaced with "Kibrom\SqlDev". Something like below should work;

SQL

SELECT * 
  INTO #MyTempTable 
  FROM OPENROWSET(
    'SQLNCLI', 
    'Server=(local);Trusted_Connection=yes;', 
    'Exec [dbo].[C_WEB_sp_RevenueAchievedByShow] 1,2'
)

I are Troll Suspicious | :suss:

modified on Friday, June 4, 2010 2:35 PM

SQL QUERY PROBLEM..

mjawadkhatri2-Jun-10 23:41

mjawadkhatri

2-Jun-10 23:41

Hi
Please check my this query and tell me error

select productname,sum(issueqty) QTY from issuance
where issuedate >= '5/1/2010' and issuedate <= '5/31/2010'
and productid= '777' and productid= '778'
group by productname
order by productname

i want to show these two product total issue qty of month may