|
Hello Eddy,
please see an earlier post in this chain..
Click
Will this same issue happen to CLR-UDF?
please advise
Thanks for your thoughts
Sebastian
|
|
|
|
|
Sebastian T Xavier wrote: Will this same issue happen to CLR-UDF?
Aw, yes; the WinForms assembly isn't considered "safe" in Sql Server. Installing the CLR-UDF (when using WinForms components) is described here[^], along with some other viable solutions.
I are Troll
|
|
|
|
|
Good information....
Thank you
Sebastian
|
|
|
|
|
You're welcome
|
|
|
|
|
Has anyone run SQL Server 2000 Developer's Edition on Windows 2008? If yes, any problem?
|
|
|
|
|
Hi all,
I have a problem in getting the one below to work. I think i have used the right syntax. But keeps coming with error on EXEC Lline...
CREATE FUNCTION Summary (@Month varchar(30),@Year varchar(30),@ShowType varchar(30))
RETURNS TABLE
AS
RETURN
With
EXECUTE C_WEB_sp_RevenueSummaryByShow 'April', '2010', '3'
Go
Thansk for your time
|
|
|
|
|
It_tech wrote: But keeps coming with error on EXEC Lline...
Do you think the error message might help us debug your problem?
|
|
|
|
|
Many thanks for your help.
Here is the error message:
Msg 156, Level 15, State 1, Procedure Summary, Line 6
Incorrect syntax near the keyword 'EXECUTE'.
|
|
|
|
|
The error message is somewhat descriptive. The main problem you're having is that you wont be able to return the result of a stored procedure call from a function. Its just not possible in SQLServer.
|
|
|
|
|
Thanks Once again.Its Much appreciated.What is it about extended Stored Procedures?I hope we can execute them in table valued functions.
|
|
|
|
|
As far as I know, you cant execute any SP's in a Function.
|
|
|
|
|
Thanks.What is the way around then?
|
|
|
|
|
Don't use a function!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Without knowing what you're trying to achieve, I cant answer that.
|
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
Hi,
Just tried it, the code below works for me on the AdventureWorks database;
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
I are Troll
|
|
|
|
|
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'
)
|
|
|
|
|
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;
SELECT *
INTO #MyTempTable
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes;',
'Exec [dbo].[C_WEB_sp_RevenueAchievedByShow] 1,2'
)
I are Troll
modified on Friday, June 4, 2010 2:35 PM
|
|
|
|
|
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
|
|
|
|
|
mjawadkhatri wrote: i want to show these two product total issue qty of month may
For all records with the number "777" and "778"? Didn't you want an OR instead of an AND ?
I are Troll
|
|
|
|
|
yes all record of productid=777 and productid=778
|
|
|
|
|
You want the records that have number "777" or "778". You are asking for the records that have *both* numbers.
The resulting list will have records with both records containing 777 AND 778, but you'll need to ask for the records that contain 777 OR 778
I are Troll
|
|
|
|