Database

7-Jun-10 3:48

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 Suspicious | :suss:

Re: RTF to TEXT

Sebastian T Xavier7-Jun-10 4:25

Sebastian T Xavier

7-Jun-10 4:25

Good information....
Thank you

Sebastian

Re: RTF to TEXT

Eddy Vluggen7-Jun-10 4:59

Has anyone run SQL Server 2000 Developer's Edition on Windows 2008?

7-Jun-10 4:59

You're welcome Smile | :)

Xiangyang Liu 刘向阳3-Jun-10 12:52

Xiangyang Liu 刘向阳

3-Jun-10 12:52

Has anyone run SQL Server 2000 Developer's Edition on Windows 2008? If yes, any problem?

My .NET Business Application Framework
My Younger Son & His "PET"

INLINE FUNCTION

It_tech3-Jun-10 4:18

3-Jun-10 4:18

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

J4amieC3-Jun-10 4:39

3-Jun-10 4:39

It_tech wrote:
But keeps coming with error on EXEC Lline...

Do you think the error message might help us debug your problem?

It_tech3-Jun-10 5:51

3-Jun-10 5:51

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

J4amieC3-Jun-10 6:06

3-Jun-10 6:06

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.

It_tech3-Jun-10 6:26

3-Jun-10 6:26

Thanks Once again.Its Much appreciated.What is it about extended Stored Procedures?I hope we can execute them in table valued functions.

J4amieC3-Jun-10 6:34

3-Jun-10 6:34

As far as I know, you cant execute any SP's in a Function.

It_tech3-Jun-10 7:00

3-Jun-10 7:00

Thanks.What is the way around then?

Mycroft Holmes3-Jun-10 14:20

Mycroft Holmes

3-Jun-10 14:20

Don't use a function!

Never underestimate the power of human stupidity
RAH

J4amieC3-Jun-10 21:30

3-Jun-10 21:30

Without knowing what you're trying to achieve, I cant answer that.

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

J4amieC4-Jun-10 0:02

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.

It_tech4-Jun-10 0:35

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

J4amieC4-Jun-10 0:57

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.

Eddy Vluggen4-Jun-10 1:48

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:

It_tech4-Jun-10 2:51

Re: INLINE FUNCTION [modified]

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'
)

Eddy Vluggen4-Jun-10 3:27

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

Eddy Vluggen2-Jun-10 23:59

2-Jun-10 23:59

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 Suspicious | :suss:

mjawadkhatri3-Jun-10 0:10

mjawadkhatri

3-Jun-10 0:10

yes all record of productid=777 and productid=778

Eddy Vluggen3-Jun-10 0:39

3-Jun-10 0:39

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 Smile | :)

I are Troll Suspicious | :suss: