T-SQL (SS2K8) :: Inline Table-valued Function - A Severe Error Occurred

Jan 21, 2015

I'm attempting to convert some INSERT-EXEC structures into table-valued functions because the procedures are deeply nested and INSERT-EXEC doesn't like nesting (Error 3915: Cannot use the ROLLBACK statement within an INSERT-EXEC statement)

The procedure has a single select statement, so I created an inline table-valued function. When I ran it with sample data, I received this error (yes, twice):

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

After ruling out obvious mistakes, I started to deconstruct the select statement with its CTE and TVP. The result is the following, built in my local sandbox database:

CREATE TYPE test_list AS TABLE(a int);
GO
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
RETURNS TABLE
AS
RETURN (
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
GO
DECLARE @t test_list;
INSERT @t VALUES(1);
SELECT * FROM test_function(1, @t);

When I run this, I get the same error as noted above. I'm running on version 10.50.4000.0, Developer Edition. (2008 R2 SP2)

The function above does just about nothing and has redundancies because I stripped the actual function down to the essential elements to cause the error. The essential elements are:

- One of the parameters is a table-valued parameter (the UDTT definition does not seem to matter)

- The SELECT statement has a CTE

- The TVP is accessed within the CTE

- The outer FROM clause references the CTE

- There is also a scalar parameter on the function (scalar type does not seem to matter).

- The scalar parameter precedes the TVP in the parameter list.

So I have an easy work-around: put the TVP first in the parameter list.

View 5 Replies


ADVERTISEMENT

Inline Table-valued Function With Multi-value Parameter

Jul 18, 2007

Hello everybody,

I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?

Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?

Thanks in advance for your answers.

View 7 Replies View Related

SQL Server 2008 :: Recursive CTE On Inline Table Valued Function

Jun 22, 2015

I have a recursive CTE on an inline table valued function. I need to set the MAXRECURSION option on the CTE, but SQL Server is complaining with "Incorrect syntax near the keyword 'OPTION'".

It works fine on non-inline function. I couldn't find any documentation indicating this wasn't possible.

I can use the MAXRECURSION option in call to the function

SELECT * FROM MyFunction ()
OPTION ( MAXRECURSION 0 )

but that means that the user needs to know the "MyFunction" uses recursive CTE, which defeats the purpose of the abstraction.

View 5 Replies View Related

Can A Stored Procedure Called From An Inline Table-Valued Function

Oct 5, 2006

Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View 4 Replies View Related

T-SQL (SS2K8) :: Create A Table Valued Function?

Oct 20, 2014

I would like to create a table valued function using the following data:

create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG

[code].....

I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,

CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Table Valued Function For Active Directory Group Membership

Jun 8, 2012

For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?

The following works fine:

SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn
FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a
WHERE sn IS NOT NULL

The following gives me the error:

Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
(
@group nvarchar(255)
)
RETURNS @rtnTable TABLE

[Code] .....

View 7 Replies View Related

A Severe Error Occurred On The Current Command.

Aug 23, 2006

I am having problems with an error that I keep receiving. The error message states "A severe error occurred on the current command. The results, if any, should
be discarded."This error is popping up when I try to call a specific stored procedure in SQL2005. The error is not specific to ASP.NET code because it also occurs if I try to run the procedure in SQL Server Management Studio. The procedure is exactly the same that is running on an SQL2000 server on a live site, so it apparently is specific to SQL2005.I have not worked much with SQL2005 or SQL cursors in the past and this code was not written by me so I am not sure where to start making changes, if any are needed.The procedure is as follows:ALTER PROCEDURE [dbo].[spGetAdmins]     @userid    int,    @forDate datetimeASDECLARE    @lngTabCount INTEGER,        @lngLoopCount INTEGER,        @type varchar(15),        @id varchar(20),        @statement varchar(1000),        @useridLocal int,        @forDateLocal datetimeSET    @useridLocal = @useridSET    @forDateLocal = @forDateDECLARE    @AdminInfo TABLE (AdminID varchar(20), AdminName varchar(50))    /* check parameters */    IF (@useridLocal <= 0)         BEGIN            RAISERROR( 'Invalid UserID (%d)', 16, 1, @useridLocal )        END    IF (@forDateLocal IS NULL)         BEGIN            SELECT @forDateLocal = GETDATE()        ENDDECLARE profInfo CURSORLOCAL FORWARD_ONLYFOR    SELECT    ProfileType, ProfileValue    FROM    tblCSUserProfile    WHERE    UserID = @useridLocal and         (StartDate is not null or StartDate <= @forDateLocal ) and         (EndDate is null or EndDate > @forDateLocal )    ORDER BY    ProfileTypeOPEN profInfoFETCH profInfo INTO @type, @idWHILE @@Fetch_Status = 0    BEGIN        IF @id = '*'            BEGIN                INSERT INTO @AdminInfo                VALUES ('*', '* Everything')                                 END        ELSE            BEGIN                INSERT INTO @AdminInfo                SELECT adm.[ADM_CODE], adm.[ADMIN_NAM]                FROM OPENROWSET('SQLOLEDB', 'server'; 'database'; 'password', [table]) AS adm                WHERE adm.[ADM_CODE] = @id            END        FETCH profInfo INTO @type, @id            ENDCLOSE profInfoDEALLOCATE profInfoSELECT    DISTINCT(UPPER(AdminID)) AS AdminCodeFROM @AdminInfo GROUP BY AdminIDORDER BY AdminCode

View 1 Replies View Related

A Severe Error Occurred On The Current Command

Oct 17, 2007

Hi EveryBody,

I have got some problem in our SQL Server 2k5, Randomly the SQL Servercies stops responding on the server side and if we try to login to SQL server it gives Error "A severe error occurred on the current command"

we is there any solution to this? whenever we face this problem we need to restart the server, on few places i have seen the recomendation of SP 2, but m not sure that it will solve the problem or not.... i will appreciate if some one can can suggest me the solution??

View 4 Replies View Related

A Severe Error Occurred On The Current Command. The Results, If Any, Should Be Ignored

Feb 1, 2008

I got the above error executing a stored procedure from .Net app and I isolated the issue on the following sql statement inside the sp. I executed the statement manually inside the MSSQL Mgt Studio and got the same error as above. Here is the sql statement:
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29 from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\TPSDATA\az\;','select * from prohist.csv where f1 < 0 ')

the data file exists in the directory 'E: psdataazprohist.csv' . I'm running on SQL2005 version 9.00.3054.00. Any ideas what the problem is? Appreciate any feedback

View 4 Replies View Related

SQL 2000 A Severe Error Occurred On The Current Command.

Jan 9, 2007

I am recently getting SQLException on production sql database that uses SQL Session State. Any help on how to fix or what to look for. I also noticed some errors on the eventlog (attached below).

Error Message:

System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded

Stack Trace:

[HttpException (0x80004005): Unable to connect to SQL Server session database.]

System.Web.SessionState.SqlStateClientManager.SetAsyncWorker(String id, SessionStateItem item, Byte[] buf, Int32 length, Boolean inStorage) + 524

System.Web.SessionState.SqlStateClientManager.System.Web.SessionState.IStateClientManager.Set(String id, SessionStateItem item, Boolean inStorage) ...

EventLog:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 1/9/2007
Time: 9:28:34 AM
User: N/A
Computer: CompName
Description:
Error: 17803, Severity: 20, State: 12
Insufficient memory available.
Data:
0000: 8b 45 00 00 14 00 00 00 ‹E......
0008: 07 00 00 00 4c 00 4c 00 ....L.L.
0010: 44 00 42 00 30 00 31 00 D.B.0.1.
0018: 00 00 09 00 00 00 41 00 ......A.
0020: 53 00 50 00 53 00 74 00 S.P.S.t.
0028: 61 00 74 00 65 00 00 00 a.t.e...

View 5 Replies View Related

Inline-table-valued Functions

May 1, 2007

Help! Been doing the box step with BOL for several hours , Using tables in Adventureworks to create inline-table-valued function to provide a parameterized view of three JOINS - Have sucessfully created the function but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID - If you can help me cut this dance short I would REALLY Appreciate it.

View 7 Replies View Related

A Severe Error Occurred On The Current Command. The Results, If Any, Should Be Discarded.

Aug 27, 2002

Hi,

I am hosting my ASP.NET application on a Host and after some time I get this error
(Don't get it on my development machine):

A severe error occurred on the current command. The results, if any, should be discarded.

And then it says this on the same page:

Exception Details: System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

[SqlException: A severe error occurred on the current command. The results, if any, should be discarded.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +643
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +9
ASPNetPortal.PortalSettings..ctor(Int32 tabIndex, Int32 tabId)
ASPNetPortal.Global.Application_BeginRequest(Object sender, EventArgs e)
System.Web.SyncEventExecutionStep.Execute() +60
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&amp; completedSynchronously)


I thought this is a problem with max pool size and I did it max pool size = 5000, now application runs ok for some time and then produces this error but some times this comes very soon.

As a solution, I have to copy my dll in bin directory again and application restarts and works properly but then after some time this happenes again.

Please let me know whats the problem.
I checked all of my SqlDataReaders and SqlConnections are closed properly.

Any help would be appreciated.

Thanks.

Rahul.

View 8 Replies View Related

A Severe Error Occurred On The Current Command. The Results, If Any, Should Be Discarded

Feb 26, 2008

Hi all. I am running on SQL 2005 Standard eddition SP2, Also did install " KB Article Number(s): 944929, 946608, 948490, 949118". Running on 2003 server.

Error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I have 2 servers. One of them is free text seach with files stored in DB. On main server there is a link back to this file DB server.

I am trying to run this script and then error from above... link_kance_doc is link to table on other server. Strange thing is when I run this same script on server 3 with same link to file DB server it works. It also works when runed on file DB server.
Also, if I dont use WHERE CONTRAINS SQL does return Dataset..


SELECT link_kance_doc.ID, link_kance_doc.DocName, link_kance_doc.DocExtension, link_kance_doc.KancelID, link_kance_doc.Comments,
link_kance_doc.CreateByUser, link_kance_doc.CreateTS, link_kance_doc.ActiveYN
FROM link_kance_doc
where Contains(*,'"murte"')

Any ideas..





Server LOG

02/26/2008 16:54:05,Server,Unknown,A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
02/26/2008 16:54:05,Server,Unknown,Error: 17310<c/> Severity: 20<c/> State: 1.
02/26/2008 16:54:05,spid57,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
02/26/2008 16:54:04,spid57,Unknown,Stack Signature for the dump is 0x28BDEE59
02/26/2008 16:54:04,spid57,Unknown,78132A47 Module(MSVCR80+00002A47)
02/26/2008 16:54:04,spid57,Unknown,781329BB Module(MSVCR80+000029BB)
02/26/2008 16:54:04,spid57,Unknown,010B9201 Module(sqlservr+000B9201)
02/26/2008 16:54:04,spid57,Unknown,010B9064 Module(sqlservr+000B9064)
02/26/2008 16:54:04,spid57,Unknown,010B939C Module(sqlservr+000B939C)
02/26/2008 16:54:04,spid57,Unknown,010B94A5 Module(sqlservr+000B94A5)
02/26/2008 16:54:04,spid57,Unknown,010075DC Module(sqlservr+000075DC)
02/26/2008 16:54:04,spid57,Unknown,010078CC Module(sqlservr+000078CC)
02/26/2008 16:54:04,spid57,Unknown,010077A6 Module(sqlservr+000077A6)
02/26/2008 16:54:04,spid57,Unknown,0102F1F4 Module(sqlservr+0002F1F4)
02/26/2008 16:54:04,spid57,Unknown,01032A36 Module(sqlservr+00032A36)
02/26/2008 16:54:04,spid57,Unknown,01028600 Module(sqlservr+00028600)
02/26/2008 16:54:04,spid57,Unknown,0134752A Module(sqlservr+0034752A)
02/26/2008 16:54:04,spid57,Unknown,01430633 Module(sqlservr+00430633)
02/26/2008 16:54:04,spid57,Unknown,01346B6B Module(sqlservr+00346B6B)
02/26/2008 16:54:04,spid57,Unknown,013A929A Module(sqlservr+003A929A)
02/26/2008 16:54:04,spid57,Unknown,01368BB2 Module(sqlservr+00368BB2)
02/26/2008 16:54:04,spid57,Unknown,013A9BDC Module(sqlservr+003A9BDC)
02/26/2008 16:54:04,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:04,spid57,Unknown,013AFC6E Module(sqlservr+003AFC6E)
02/26/2008 16:54:04,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:04,spid57,Unknown,013AF8D0 Module(sqlservr+003AF8D0)
02/26/2008 16:54:04,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:04,spid57,Unknown,013B0CC7 Module(sqlservr+003B0CC7)
02/26/2008 16:54:04,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:04,spid57,Unknown,013A9DC5 Module(sqlservr+003A9DC5)
02/26/2008 16:54:04,spid57,Unknown,013A9F20 Module(sqlservr+003A9F20)
02/26/2008 16:54:04,spid57,Unknown,01B2E1B9 Module(sqlservr+00B2E1B9)
02/26/2008 16:54:04,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:04,spid57,Unknown,01B24372 Module(sqlservr+00B24372)
02/26/2008 16:54:04,spid57,Unknown,0136A2D8 Module(sqlservr+0036A2D8)
02/26/2008 16:54:04,spid57,Unknown,0149C867 Module(sqlservr+0049C867)
02/26/2008 16:54:04,spid57,Unknown,0170D0CD Module(sqlservr+0070D0CD)
02/26/2008 16:54:04,spid57,Unknown,0170F7E3 Module(sqlservr+0070F7E3)
02/26/2008 16:54:04,spid57,Unknown,0170FB90 Module(sqlservr+0070FB90)
02/26/2008 16:54:04,spid57,Unknown,01702514 Module(sqlservr+00702514)
02/26/2008 16:54:04,spid57,Unknown,010A0F91 Module(sqlservr+000A0F91)
02/26/2008 16:54:04,spid57,Unknown,* Short Stack Dump
02/26/2008 16:54:04,spid57,Unknown,* -------------------------------------------------------------------------------
02/26/2008 16:54:04,spid57,Unknown,* *******************************************************************************
02/26/2008 16:54:04,spid57,Unknown,* SegSs: 00000023:
02/26/2008 16:54:04,spid57,Unknown,* Esp: 6373E24C: 00000004 00000000 6373E544 01702514 00000004 01019094
02/26/2008 16:54:04,spid57,Unknown,* EFlags: 00010206: 0055004E 0042004D 00520045 004F005F 005F0046 00520050
02/26/2008 16:54:04,spid57,Unknown,* SegCs: 0000001B:
02/26/2008 16:54:04,spid57,Unknown,* Ebp: 6373E254: 6373E544 01702514 00000004 01019094 637311EB 042DAD88
02/26/2008 16:54:04,spid57,Unknown,* Eip: 010A0F91: 103B118B B60FCD74 38B60F11 840FD72B 003D5E1C 01B8D285
02/26/2008 16:54:04,spid57,Unknown,* Edx: 042DAD48: 042DAAB8 042DA278 042DAA18 042DAD70 042DAF58 00000001
02/26/2008 16:54:04,spid57,Unknown,* Ecx: 00000004:
02/26/2008 16:54:04,spid57,Unknown,* Ebx: 05E929F8: 01622A98 00000001 00000000 00000000 00000000 00000000
02/26/2008 16:54:04,spid57,Unknown,* Eax: 01019094: 00000000 00000000 00000000 00000000 90909090 55FF8B90
02/26/2008 16:54:04,spid57,Unknown,* Esi: 00000010:
02/26/2008 16:54:04,spid57,Unknown,* Edi: 00000004:
02/26/2008 16:54:04,spid57,Unknown,*
02/26/2008 16:54:04,spid57,Unknown,* dbghelp 64600000 64714FFF 00115000
02/26/2008 16:54:04,spid57,Unknown,* MSDATL3 64450000 64464FFF 00015000
02/26/2008 16:54:04,spid57,Unknown,* sqloledb 643C0000 64440FFF 00081000
02/26/2008 16:54:04,spid57,Unknown,* xpstar90 64390000 643B5FFF 00026000
02/26/2008 16:54:04,spid57,Unknown,* msadcer 64380000 64384FFF 00005000
02/26/2008 16:54:04,spid57,Unknown,* msadce 64320000 64377FFF 00058000
02/26/2008 16:54:04,spid57,Unknown,* OLEDB32R 64300000 64310FFF 00011000
02/26/2008 16:54:04,spid57,Unknown,* MSDART 64060000 64079FFF 0001a000
02/26/2008 16:54:04,spid57,Unknown,* oledb32 63FE0000 64058FFF 00079000
02/26/2008 16:54:04,spid57,Unknown,* odbcint 63FC0000 63FD6FFF 00017000
02/26/2008 16:54:04,spid57,Unknown,* ATL80 7C630000 7C64AFFF 0001b000
02/26/2008 16:54:04,spid57,Unknown,* BatchParser90 63E50000 63E6EFFF 0001f000
02/26/2008 16:54:04,spid57,Unknown,* ODBC32 63E10000 63E4CFFF 0003d000
02/26/2008 16:54:04,spid57,Unknown,* SQLSCM90 63DF0000 63DF8FFF 00009000
02/26/2008 16:54:04,spid57,Unknown,* xpstar90 63D90000 63DD8FFF 00049000
02/26/2008 16:54:04,spid57,Unknown,* msftepxy 63600000 63614FFF 00015000
02/26/2008 16:54:04,spid57,Unknown,* SQLNCLIR 00830000 00862FFF 00033000
02/26/2008 16:54:04,spid57,Unknown,* comdlg32 762B0000 762F8FFF 00049000
02/26/2008 16:54:04,spid57,Unknown,* COMCTL32 77530000 775C6FFF 00097000
02/26/2008 16:54:04,spid57,Unknown,* sqlncli 631D0000 633F3FFF 00224000
02/26/2008 16:54:04,spid57,Unknown,* CLBCatQ 777B0000 77832FFF 00083000
02/26/2008 16:54:04,spid57,Unknown,* xpsp2res 62F00000 631C4FFF 002c5000
02/26/2008 16:54:04,spid57,Unknown,* ntdsapi 766F0000 76703FFF 00014000
02/26/2008 16:54:04,spid57,Unknown,* SAMLIB 7E020000 7E02EFFF 0000f000
02/26/2008 16:54:04,spid57,Unknown,* NTMARTA 77E00000 77E20FFF 00021000
02/26/2008 16:54:04,spid57,Unknown,* wshtcpip 71AE0000 71AE7FFF 00008000
02/26/2008 16:54:04,spid57,Unknown,* hnetcfg 62E60000 62EB9FFF 0005a000
02/26/2008 16:54:04,spid57,Unknown,* dssenh 68100000 68126FFF 00027000
02/26/2008 16:54:04,spid57,Unknown,* imagehlp 76C10000 76C37FFF 00028000
02/26/2008 16:54:04,spid57,Unknown,* WINTRUST 76BB0000 76BDAFFF 0002b000
02/26/2008 16:54:04,spid57,Unknown,* dbghelp 62B00000 62C14FFF 00115000
02/26/2008 16:54:04,spid57,Unknown,* msfte 628A0000 62AF8FFF 00259000
02/26/2008 16:54:04,spid57,Unknown,* security 62210000 62213FFF 00004000
02/26/2008 16:54:04,spid57,Unknown,* rasadhlp 76F80000 76F84FFF 00005000
02/26/2008 16:54:04,spid57,Unknown,* WLDAP32 76F10000 76F3DFFF 0002e000
02/26/2008 16:54:04,spid57,Unknown,* winrnr 76F70000 76F76FFF 00007000
02/26/2008 16:54:04,spid57,Unknown,* DNSAPI 76ED0000 76EF9FFF 0002a000
02/26/2008 16:54:04,spid57,Unknown,* RESUTILS 61DC0000 61DD2FFF 00013000
02/26/2008 16:54:04,spid57,Unknown,* CLUSAPI 61DA0000 61DB1FFF 00012000
02/26/2008 16:54:04,spid57,Unknown,* WSOCK32 71BB0000 71BB8FFF 00009000
02/26/2008 16:54:04,spid57,Unknown,* VERSION 77B90000 77B97FFF 00008000
02/26/2008 16:54:04,spid57,Unknown,* MTXCLU 61D80000 61D98FFF 00019000
02/26/2008 16:54:04,spid57,Unknown,* msvcp60 61D10000 61D74FFF 00065000
02/26/2008 16:54:04,spid57,Unknown,* OLEAUT32 77D00000 77D8AFFF 0008b000
02/26/2008 16:54:04,spid57,Unknown,* MSDTCPRX 61C90000 61D08FFF 00079000
02/26/2008 16:54:04,spid57,Unknown,* XOLEHLP 61C80000 61C85FFF 00006000
02/26/2008 16:54:04,spid57,Unknown,* COMRES 77010000 770D5FFF 000c6000
02/26/2008 16:54:04,spid57,Unknown,* schannel 76750000 76776FFF 00027000
02/26/2008 16:54:04,spid57,Unknown,* cryptdll 766E0000 766EBFFF 0000c000
02/26/2008 16:54:04,spid57,Unknown,* kerberos 61BD0000 61C27FFF 00058000
02/26/2008 16:54:04,spid57,Unknown,* iphlpapi 76CF0000 76D09FFF 0001a000
02/26/2008 16:54:04,spid57,Unknown,* msv1_0 76C90000 76CB6FFF 00027000
02/26/2008 16:54:04,spid57,Unknown,* ole32 77670000 777A8FFF 00139000
02/26/2008 16:54:04,spid57,Unknown,* MSCOREE 34190000 341D4FFF 00045000
02/26/2008 16:54:04,spid57,Unknown,* AUTHZ 76C40000 76C53FFF 00014000
02/26/2008 16:54:04,spid57,Unknown,* rsaenh 68000000 68034FFF 00035000
02/26/2008 16:54:04,spid57,Unknown,* SQLOS 344D0000 344D4FFF 00005000
02/26/2008 16:54:04,spid57,Unknown,* sqlevn70 4F610000 4F7B8FFF 001a9000
02/26/2008 16:54:04,spid57,Unknown,* instapi 48060000 48069FFF 0000a000
02/26/2008 16:54:04,spid57,Unknown,* psapi 76B70000 76B7AFFF 0000b000
02/26/2008 16:54:04,spid57,Unknown,* comctl32 77420000 77522FFF 00103000
02/26/2008 16:54:04,spid57,Unknown,* IMM32 76290000 762ACFFF 0001d000
02/26/2008 16:54:04,spid57,Unknown,* SHLWAPI 77DA0000 77DF1FFF 00052000
02/26/2008 16:54:04,spid57,Unknown,* SHELL32 7C8D0000 7D0CEFFF 007ff000
02/26/2008 16:54:04,spid57,Unknown,* NETAPI32 71C40000 71C96FFF 00057000
02/26/2008 16:54:04,spid57,Unknown,* opends60 333E0000 333E6FFF 00007000
02/26/2008 16:54:04,spid57,Unknown,* USERENV 76920000 769E1FFF 000c2000
02/26/2008 16:54:04,spid57,Unknown,* WS2HELP 71BF0000 71BF7FFF 00008000
02/26/2008 16:54:04,spid57,Unknown,* WS2_32 71C00000 71C16FFF 00017000
02/26/2008 16:54:04,spid57,Unknown,* MSWSOCK 71B20000 71B60FFF 00041000
02/26/2008 16:54:04,spid57,Unknown,* MSASN1 76190000 761A1FFF 00012000
02/26/2008 16:54:04,spid57,Unknown,* CRYPT32 761B0000 76242FFF 00093000
02/26/2008 16:54:04,spid57,Unknown,* GDI32 77C00000 77C47FFF 00048000
02/26/2008 16:54:04,spid57,Unknown,* USER32 77380000 77410FFF 00091000
02/26/2008 16:54:04,spid57,Unknown,* Secur32 76F50000 76F62FFF 00013000
02/26/2008 16:54:04,spid57,Unknown,* RPCRT4 77C50000 77CEEFFF 0009f000
02/26/2008 16:54:04,spid57,Unknown,* ADVAPI32 77F50000 77FEAFFF 0009b000
02/26/2008 16:54:04,spid57,Unknown,* MSVCP80 7C420000 7C4A6FFF 00087000
02/26/2008 16:54:04,spid57,Unknown,* msvcrt 77BA0000 77BF9FFF 0005a000
02/26/2008 16:54:04,spid57,Unknown,* MSVCR80 78130000 781CAFFF 0009b000
02/26/2008 16:54:04,spid57,Unknown,* kernel32 77E40000 77F41FFF 00102000
02/26/2008 16:54:04,spid57,Unknown,* ntdll 7C800000 7C8BFFFF 000c0000
02/26/2008 16:54:04,spid57,Unknown,* sqlservr 01000000 02C09FFF 01c0a000
02/26/2008 16:54:04,spid57,Unknown,* MODULE BASE END SIZE
02/26/2008 16:54:04,spid57,Unknown,*
02/26/2008 16:54:04,spid57,Unknown,*
02/26/2008 16:54:04,spid57,Unknown,* ins(*<c/>'"murte"')
02/26/2008 16:54:04,spid57,Unknown,* teTS<c/> link_kance_doc.ActiveYN FROM link_kance_doc where Conta
02/26/2008 16:54:04,spid57,Unknown,* link_kance_doc.CreateByUser<c/> link_kance_doc.Crea
02/26/2008 16:54:04,spid57,Unknown,* nce_doc.DocExtension<c/> link_kance_doc.KancelID<c/> link_kance_doc.Comments<c/>
02/26/2008 16:54:04,spid57,Unknown,* SELECT link_kance_doc.ID<c/> link_kance_doc.DocName<c/> link_ka
02/26/2008 16:54:04,spid57,Unknown,* Input Buffer 510 bytes -
02/26/2008 16:54:04,spid57,Unknown,* Access Violation occurred reading address 00000004
02/26/2008 16:54:04,spid57,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
02/26/2008 16:54:04,spid57,Unknown,* Exception Address = 010A0F91 Module(sqlservr+000A0F91)
02/26/2008 16:54:04,spid57,Unknown,*
02/26/2008 16:54:04,spid57,Unknown,*
02/26/2008 16:54:04,spid57,Unknown,* 02/26/08 16:54:04 spid 57
02/26/2008 16:54:04,spid57,Unknown,* BEGIN STACK DUMP:
02/26/2008 16:54:04,spid57,Unknown,*
02/26/2008 16:54:04,spid57,Unknown,* *******************************************************************************
02/26/2008 16:54:04,spid57,Unknown,SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
02/26/2008 16:54:04,spid57,Unknown,***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0063.txt
02/26/2008 16:54:04,spid57,Unknown,Using 'dbghelp.dll' version '4.0.5'
02/26/2008 16:54:03,Server,Unknown,A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
02/26/2008 16:54:03,Server,Unknown,Error: 17310<c/> Severity: 20<c/> State: 1.
02/26/2008 16:54:03,spid57,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
02/26/2008 16:54:02,spid57,Unknown,Stack Signature for the dump is 0x28BDEE59
02/26/2008 16:54:02,spid57,Unknown,78132A47 Module(MSVCR80+00002A47)
02/26/2008 16:54:02,spid57,Unknown,781329BB Module(MSVCR80+000029BB)
02/26/2008 16:54:02,spid57,Unknown,010B9201 Module(sqlservr+000B9201)
02/26/2008 16:54:02,spid57,Unknown,010B9064 Module(sqlservr+000B9064)
02/26/2008 16:54:02,spid57,Unknown,010B939C Module(sqlservr+000B939C)
02/26/2008 16:54:02,spid57,Unknown,010B94A5 Module(sqlservr+000B94A5)
02/26/2008 16:54:02,spid57,Unknown,010075DC Module(sqlservr+000075DC)
02/26/2008 16:54:02,spid57,Unknown,010078CC Module(sqlservr+000078CC)
02/26/2008 16:54:02,spid57,Unknown,010077A6 Module(sqlservr+000077A6)
02/26/2008 16:54:02,spid57,Unknown,0102F1F4 Module(sqlservr+0002F1F4)
02/26/2008 16:54:02,spid57,Unknown,01032A36 Module(sqlservr+00032A36)
02/26/2008 16:54:02,spid57,Unknown,01028600 Module(sqlservr+00028600)
02/26/2008 16:54:02,spid57,Unknown,0134752A Module(sqlservr+0034752A)
02/26/2008 16:54:02,spid57,Unknown,01430633 Module(sqlservr+00430633)
02/26/2008 16:54:02,spid57,Unknown,01346B6B Module(sqlservr+00346B6B)
02/26/2008 16:54:02,spid57,Unknown,013A929A Module(sqlservr+003A929A)
02/26/2008 16:54:02,spid57,Unknown,01368BB2 Module(sqlservr+00368BB2)
02/26/2008 16:54:02,spid57,Unknown,013A9BDC Module(sqlservr+003A9BDC)
02/26/2008 16:54:02,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:02,spid57,Unknown,013AFC6E Module(sqlservr+003AFC6E)
02/26/2008 16:54:02,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:02,spid57,Unknown,013AF8D0 Module(sqlservr+003AF8D0)
02/26/2008 16:54:02,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:02,spid57,Unknown,013B0CC7 Module(sqlservr+003B0CC7)
02/26/2008 16:54:02,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:02,spid57,Unknown,013A9DC5 Module(sqlservr+003A9DC5)
02/26/2008 16:54:02,spid57,Unknown,013A9F20 Module(sqlservr+003A9F20)
02/26/2008 16:54:02,spid57,Unknown,01B2E1B9 Module(sqlservr+00B2E1B9)
02/26/2008 16:54:02,spid57,Unknown,013A9A04 Module(sqlservr+003A9A04)
02/26/2008 16:54:02,spid57,Unknown,01B24372 Module(sqlservr+00B24372)
02/26/2008 16:54:02,spid57,Unknown,0136A2D8 Module(sqlservr+0036A2D8)
02/26/2008 16:54:02,spid57,Unknown,0149C867 Module(sqlservr+0049C867)
02/26/2008 16:54:02,spid57,Unknown,0170D0CD Module(sqlservr+0070D0CD)
02/26/2008 16:54:02,spid57,Unknown,0170F7E3 Module(sqlservr+0070F7E3)
02/26/2008 16:54:02,spid57,Unknown,0170FB90 Module(sqlservr+0070FB90)
02/26/2008 16:54:02,spid57,Unknown,01702514 Module(sqlservr+00702514)
02/26/2008 16:54:02,spid57,Unknown,010A0F91 Module(sqlservr+000A0F91)

View 1 Replies View Related

DBCC SHRINKFILE Gives A Severe Error Occurred On The Current Command.

Oct 14, 2007

I am using SQL Server SP 2 on Windows 2003 Server Standard edition:


Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I have a datbase that's rather large. The log file is 94656 pages, and the data file itself is 94197200 pages. There's only one data file and one log file. The database passes DBCC CHEKCDATABASE with no errors.

When I run DBCC SHRINKDATABASE against the database, the command runs for about twenty seconds then produces this error:


Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I can't find anything interesting in the ERRORLOG around the time that I run this command. The error appears if I use the TRUNCATEONLY option or not.

How do I fix this problem?

And in general, why are the engine errors in SQL Server so confusing and not directly actionable?

View 1 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

T-SQL (SS2K8) :: Proper Use Of CTE In Scalar Valued Function?

Dec 1, 2014

I have troubles with this scalar-valued UDF:

I get the error:

Select statements included within a function cannot return data to a client.

Is this a proper way to include a CTE in a function?

USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)

[code].....

View 4 Replies View Related

SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With Sys?

Nov 2, 2006

Hi all,

I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:

Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.

I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?

Thanks, Jos

Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/

PRINT @@VERSION
go

PRINT 'Scalar function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
()
RETURNS INT
AS
BEGIN
RETURN 1
END
go

DROP FUNCTION sys_test
go

PRINT ''
go


PRINT 'In-line table-valued function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
()
RETURNS TABLE
AS
RETURN SELECT 1 c
go

DROP FUNCTION sys_test
go

PRINT ''
go


PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go

CREATE FUNCTION sys_tmp
()
RETURNS @t TABLE
(c INT)
AS
BEGIN

INSERT INTO @t VALUES (1)

RETURN

END
go

DROP FUNCTION sys_test
go

PRINT ''
go

/*
----------------------------------------------------------------------------------------------------
*/

And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Scalar function with name "sys_" creates ok...

In-line table-valued function with name "sys_" creates ok...

Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

Using OPTION Clause Within CREATE FUNCTION Statement For Inline Table Functions

May 13, 2008

Hi!

I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:

Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34

Incorrect syntax near the keyword 'option'.

Here is the function:


create FUNCTION [dbo].[ExpandedCTE]

(

@p_id int

)

RETURNS TABLE

AS

RETURN

(

with tbl_cte (id, tbl_id, lvl)

as

(

select


id, tbl_id, 0 lvl

from


tbl

where


id = @p_id

union all

select


t.id, t.tbl_id, lvl + 1
from

tbl_cte
inner join tbl t


on rnr.tbl_id = tbl_cte.id

)

select


id, tbl_id, lvl

from


tbl_cte

option (maxrecursion 0)

)


Please help!

Alexander.


P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.

View 12 Replies View Related

Table-Valued Function

Aug 8, 2007

I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this?

Thanks

Mangala

View 3 Replies View Related

Join With Table Valued Function

Mar 10, 2008

Hi,

I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method ..
eg..
SELECT A.Col1,A.Col2,B.Col1,B.Col2
FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B
ON A.Col1 = B.Col1

any body help me ... thanx in advance..

View 3 Replies View Related

Trigger On Table-valued Function?

Jul 20, 2005

Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.

View 2 Replies View Related

How To Join Using A Table-valued Function?

Oct 22, 2007

Hi there. I've hit some gap in my SQL fundementals. I'm playing with table-valued functions but I can't figure out how to join those results with another table. I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this. What am I misunderstanding here?

The Given Objects:
function Split(stringToSplit, delimiter) returns table (column: token)
table Words (column: Words.word) -- table of predefined words
table Sentences (column: Sentences.sentence) -- table of sentences; tokens may not be in Words table, etc

The Problems:
1) how do I query a set of Sentences and their Tokens? (using Split)
2) how do I join tables Sentences and Words using the Split function?

The Attempts:
A)
select word, sentence, token
from Words,
Sentences,
dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences?
where word = token

resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

B)
select word, sentence
from Words, Sentences
where word in (select token from dbo.Split(sentence, ' ')) -- correlated subquery?

resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

View 6 Replies View Related

Table Valued Function And Constraints

May 29, 2008



Is it possible to define a constraint for Primary Key on more than 1 column or an alternate index on a column in a return table from an inline table valed function?

Example Header:


alter FUNCTION [dbo].[fntMetaFrame] (@ii_CompanyID int)

RETURNS @tbl_MetaFrame TABLE ( pk_Key int Identity(1,1) primary key,






ObjectID int ,







Seq int null )




I want the primary key to be pk_Key, ObjectID

OR

I want to add another index on ObjectID.

View 6 Replies View Related

Can I Use If Statement In A Table Valued Function?

Aug 22, 2007

hi,
I am using a function in sql server 2005 like this:
...... myfunction(... @FlagOn int)
.......
begin
return
(

if(@FlagOn = 1)
select * from.......
else
select * form....
)
end

But it keeps complaining there is some syntax error around if. What is it?

Thanks.

View 5 Replies View Related

Indexing Table-valued Function?

Aug 30, 2007

I am using a multi-statement table-valued function to assemble data from several tables and views for a report. To do this, I INSERT data into the first few columns and then use UPDATEs to put data additional data into each row. Each UPDATE uses a WHERE criteria that identifies a unique row, based on the value of the first few columns.

The problem I'm having is that the UPDATEs are taking forever to execute. I believe the reason is that the temporary table that's created for the function is not indexed, so each row update requires a complete search of several columns.

In other situations I've been able to define one column as a primary key for the temporary table, but in this situation the primary key would have to consist of four columns, which doesn't seem to be allowed in the table definition for the function.

Is there any way to create indexes for the temporary tables that are created for multistatement table-valued functions? I think that would improve the UPDATE performance dramatically.

Thanks,
Lee Silverman
JackRabbit Sports

View 1 Replies View Related

SQL Server 2008 :: Create A Table Valued Function That Fetch Through The Table?

Apr 24, 2015

I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique

EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value

View 9 Replies View Related

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function

Apr 18, 2007

Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View 2 Replies View Related

Multi-statement Table-Valued Function

Oct 18, 2007

I'm creating a Multi-statement Table-Valued Function...

Is it possible to insert variables into the table? In other words, is it possible
to have something like

declare
@value1 varchar(10)
@value2 varchar(10)

BEGIN
<do some work on value1 and value2>
INSERT @returningTable
@value1, @value2

instead of

BEGIN
<do some work on value1 and value2>
INSERT @returningTable
SELECT col1, col2 from T_SOURCE

Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key.
I'll go with an example to explain what i have to do

Source_table
col1 col2
Mike 10
Mike 20
Ben 50
John 15
John 25
John 35

The table that my function needs to create should look like this
col1 col2 col3
Mike 10 1
Mike 20 2
Ben 50 1
John 15 1
John 25 2
John 35 3

I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table.

Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated.

Thank you.

View 7 Replies View Related

Indexes On Table Variable Of Table Valued Function

Jan 6, 2004

Hi there,

Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff

RETURN
END

View 2 Replies View Related

Table-Valued Function Result Vs. Calculation Table

Jun 6, 2006

 

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

 DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call. 

View 3 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved