Showing posts with label error. Show all posts
Showing posts with label error. Show all posts

Thursday, March 29, 2012

Calling Stored Procedure fromanother Stored Procedure

Hi,

I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.

My first Stored Procedure has the following input output parameters:

ALTER PROCEDURE dbo.FixedCharges

@.InvoiceNo int,

@.InvoiceDate smalldatetime,

@.TotalOut decimal(8,2) output

AS ...

I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.

CALL FixedCharges (

@.InvoiceNo,

@.InvoiceDate,

@.TotalOut )

Many thanks in advance

James

I believe you want to use 'EXEC'|||

abadincrotch:

I believe you want to use 'EXEC'

You should use System stored procedure sp_executesql, it will take care of the dependcy chain for. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

|||

Caddre:

abadincrotch:

I believe you want to use 'EXEC'

You should use System stored procedure sp_executesql, it will take care of the dependcy chain for. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

you'll still need to EXECUTE (EXEC) sp_executesql to begin with.

|||Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends. So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.|||

Caddre:

Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends. So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.

I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.

|||

(I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.)

That error is because of the broken dependency chain and one of sp_executesql job is to provide the dependency chain.

|||never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.|||

abadincrotch:

never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

|||

Caddre:

abadincrotch:

never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

|||

abadincrotch:

Caddre:

abadincrotch:

never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

and like I said, you still need to CALL or EXEC sp_executesql to begin with, so your point is ... ?

|||

(I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.)

You said I don't understand what the user asked for when I gave the correct solution, when you gave something that works some times and not always so you have a point I don't.

The Exec works some times and not always sp_executesql works always.

|||

Thanks for your help People,

It worked!!Smile

James

|||

JamesNZ:

Thanks for your help People,

It worked!!Smile

James

out of curiosity, which?

|||

Caddre:

The Exec works some times and not always sp_executesql works always.

somehow I fail to see where it says EXEC doesn't always "work."

yes, the secureables need to have permissions granted to the role/login executing the statement ... that's just plain common sense ... where does it say exec doesn't always work?

Tuesday, March 27, 2012

Calling SqlDataSource from DLL instead of CodeFile C#.

Hello,

Is it possible to call a SqlDataSource from a DLL within a method like you can from the CodeFile? I'm getting this error: The Name SqlDataSource1 does not exist in the current context when trying to call it from within a method defined in a DLL. Thanks!

It would be much much better if you can post your code here.

The Name SqlDataSource1 does not exist in the current context

Most probably you didn't declare this parameter before you refer to it.

Is it possible to call a SqlDataSource from a DLL within a method like you can from the CodeFile?

Well, i think it totally depends on whethere you have passed that sqldatasource value to your dll.If yes, you can call that sqldatasource within your dll.

BTW, remember to add necessary dlls and using proper namespaces in your dll, thus sqldatasource class may not be identified.

Hope my suggestion helps

Sunday, March 25, 2012

Calling sp_StartJob again when the job is running, what would

as far as I know, if you don't handle the error that is returned (I'm not
talking about @.@.error that will be 0 anyway), the insert and the trigger
will finish alright.
"Frank Lee" wrote:

> I do not understand what 'Error message will be raised and the trigger wil
l
> still be executed' means.
> In this case, If a asp.net server page is connecting by using ado.net, whi
ch
> try to insert a new record to MyTable by calling a SP named MyInsert, will
> this asp.net server page get the error message, be hang up and just show u
p
> the default error page?
> If the asp.net server page will hang up, is there a way to mask this error
> message?
> I mean, no matter the MyJob is running or not, I would like the client (th
e
> server page) insert a new record smoothly. Never got this Error Message.
> So I hope that you say 'Error message will be raised and the trigger will
> still be executed' means that '@.@.error would be set and if there is no
> consideration to care this @.@.error, the trigger will still be executed
> quietly'.
> If not, how can I prevent the error message?
>
> "ariel" <ariel@.discussions.microsoft.com> ???g?ó?l¥ó·s?D:5C8009A
2-126D-4BA3-8D01-54935DC5BD71@.microsoft.com...
>
>Thanks. I see.
"ariel" <ariel@.discussions.microsoft.com> glsD:ABF0F938-1630-4794-BE3E-2651812831
9B@.microsoft.com...
> as far as I know, if you don't handle the error that is returned (I'm not
> talking about @.@.error that will be 0 anyway), the insert and the trigger
> will finish alright.
> "Frank Lee" wrote:
>

calling reports from forms

I have been trying to call my report from form through this code but neither it gives me any error nor its showing any report.

declare
report_id Report_Object;
report_job_id VARCHAR2(100);
BEGIN
report_id:= find_report_object('REPORT5');
report_JOB_ID:=run_report_object(report_id);
END;

Comm Mode : async
desctype : screen
Ex mode: runtime
file name : F:\Docs\Forms\vivek.rdf

report5 is the name of report object in the navigator.

What could be the problem??Hi,

Does Your report works fine? Is it fetching the required records ?|||Yep perfectly !

Originally posted by satish_ct
Hi,

Does Your report works fine? Is it fetching the required records ?|||Try using RUN_REPORT(<<filename>>)|||Originally posted by shelva
Try using RUN_REPORT(<<filename>>)

RUN_REPORT runs only from report as SRW.RUN_REPORT and not from form.|||Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms

Or in ur method, just give full path of the reports file name|||Originally posted by shelva
Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms

Or in ur method, just give full path of the reports file name

Never heard of it and neither it works.|||Oye again typing mistake

its CALL_REPORT.. sorry again

calling report from asp.net page times out

Hello,
I built an aspx page to call a report from reportserver. The report takes
about 2 minutes to run and I am getting a timeout error from reporting web
services. Here is the error:
System.Net.WebException: The operation has timed-out.
at
System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest
request)
at
System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebReques
t request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] parameters)
at Time.ReportServiceWS.ReportingService.Render(String Report, String
Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters,
DataSourceCredentials[] Credentials, String ShowHideToggle, String&
Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]&
Warnings, String[]& StreamIds) in c:\inetpub\wwwroot\Time\Web
References\ReportServiceWS\Reference.vb:line 554
at Time.ReportLib.GenerateReport(String rptPath, String rptFormat,
Hashtable param) in c:\inetpub\wwwroot\Time\ReportLib.vb:line 50
The reprotw orks just fine from the report manager and from Report designer.
I am pretty sure it is a config issue somewhere. Does anyone know what
setting I need to change so that report does not timeout?
Thanks,
BivaBiva,
Wnen you instantiate a new RS object in your code, set the time out
property. Use rs.timeout=XXXXX. The should help,
Or, you should look at the IIS timeout param.
-rwiethorn
"Biva" wrote:
> Hello,
> I built an aspx page to call a report from reportserver. The report takes
> about 2 minutes to run and I am getting a timeout error from reporting web
> services. Here is the error:
>
> System.Net.WebException: The operation has timed-out.
> at
> System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest
> request)
> at
> System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebReques
> t request)
> at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
> methodName, Object[] parameters)
> at Time.ReportServiceWS.ReportingService.Render(String Report, String
> Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters,
> DataSourceCredentials[] Credentials, String ShowHideToggle, String&
> Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]&
> Warnings, String[]& StreamIds) in c:\inetpub\wwwroot\Time\Web
> References\ReportServiceWS\Reference.vb:line 554
> at Time.ReportLib.GenerateReport(String rptPath, String rptFormat,
> Hashtable param) in c:\inetpub\wwwroot\Time\ReportLib.vb:line 50
> The reprotw orks just fine from the report manager and from Report designer.
> I am pretty sure it is a config issue somewhere. Does anyone know what
> setting I need to change so that report does not timeout?
> Thanks,
> Biva
>
>

Thursday, March 22, 2012

Calling Oracle Stored Procedure

Hi all

I am trying to call oracle stored procedure from SRSS 2005. I am using the syntax { Call s_test_rcur()} .

I am getting following error.

Any suggestions?

Thanks in advance

Mvr

An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at line 1

ADDITIONAL INFORMATION:

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at line 1
(System.Data.OracleClient)

Here is the Oracel stored proc.

TYPE rc_test IS REF CURSOR;

PROCEDURE s_test_rcur (
po_test_rc OUT rc_test, -- returns a record set
po_error OUT INTEGER
)
IS

BEGIN
g_err_level := 1;

OPEN po_test_rc FOR
SELECT a.ssn_id,
TO_CHAR (a.acad_yr) || TO_CHAR (a.acad_yr + 1) acad_yr,
RPAD (NVL (last_name, ' '), 30, ' ') last_name,
RPAD (NVL (first_name, ' '), 30, ' ') first_name,
NVL (middle_initial, ' ') middle_initial
from test_table
order by last_name;

po_error := 0;
EXCEPTION
WHEN OTHERS
THEN
po_error := -1;
g_error_code := SQLCODE;
g_error_msg :=
'Err level :' ||
TO_CHAR (g_err_level) ||
' ' ||
SUBSTR (SQLERRM, 1, 250);

END;

You may want to read the following related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1

Basically, you need to write a wrapper stored procedure that has only one OUT REF cursor and no other OUT parameters.

-- Robert

Tuesday, March 20, 2012

Calling CLR functions from MDX query

Hi,

I am finding it extremely tricky in getting the CLR function in C# to return anything useful that doenst contain an error message. My function in the assembly SqlServerProject1 and the class UserDefinedFunctions is fnBitwise and performs a bitwise operation on the two input params.

select SqlServerProject1.UserDefinedFunctions.fnBitwise(24,8) on 0,

[measures].[Discount Amount] on 1

from [analysis services tutorial]

OR

Call SqlServerProject1.UserDefinedFunctions.fnBitwise(24,8)

Are not producing results, instead I get error msgs.

I have been getting msgs such as

Execution of the managed stored procedure fnBitwise failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.

OR

Parser: The end of the input was reached.

Please could someone help as it seems like there is not clear help on this. What Im trying to do is to perform a bitwise operation and summing the results to create a new calculated member. Unfuortunately, Analysis services doesn't allow for bitwise operations, so I had created a CLR function in VS.Net to perform the operation. Now, while this works fine in SQL Server after being deployed there by VS.Net SQLServerProject, I am more inportantly trying to get this to work in AS using an MDX query to test the function. The SQL Server project doesn't let me add a reference to the class library that I may need as described by http://solidqualitylearning.com/blogs/dejan/archive/2005/12/01/1492.aspx .

The CLR Function is very simpe and is shown below:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Data.OleDb;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlInt32 fnBitwise(byte varA, byte varB)

{

long result = varA & varB;

SqlInt32 b;

if (result != 0)

{

b = 1;

}

else

{

b = 0;

}

return b;

}

};

I need a way of getting the result or the bitwise operation out in the MDX query so I can then create my calculated member in SQL Server Business Intelligence Development Studio Cube Designer.

Thanking you in advance.

Rob

There are two problems here. First of all AS doesn't seem to like SQL Server data types; I replaced SqlInt32 with int and got the sproc working ok:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Data.OleDb;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static int fnBitwise(byte varA, byte varB)

{

long result = varA & varB;

int b;

if (result != 0)

{

b = 1;

}

else

{

b = 0;

}

return b;

}

};

Secondly, neither of the examples of calling the sproc you give in your post are going to work because it returns an integer value and you're calling it places where AS is expecting either a member or a set (in the first example) or a cellset. You need to call the sproc within a calculated member definition, for example:

with member measures.test as ASStoredProc.fnBitWise(8,24)

select measures.test on 0 from mycube

HTH,

Chris

|||

Thanks Chris.

That's great.

Regards

Rob

Monday, March 19, 2012

Calling a web service from a CLR SQL stored procedure

Hi,
I am trying to call a remote webservice from within SQl server using CLR.
However, SQL Server gives me an error saying that load(), loadModule() etc
are disabled. Of what I can tell, C# needs to dynamically generate and load
the XML serialization for the web service call. Does anyone know how I can
get around this problem?
Platform: SQL Server 2005 + C# (.net 2.0)Please use the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Krishna" <Krishna@.discussions.microsoft.com> wrote in message
news:B5643087-4F83-4EB6-AA7E-12D7CF6C6DEF@.microsoft.com...
> Hi,
> I am trying to call a remote webservice from within SQl server using CLR.
> However, SQL Server gives me an error saying that load(), loadModule() etc
> are disabled. Of what I can tell, C# needs to dynamically generate and
> load
> the XML serialization for the web service call. Does anyone know how I can
> get around this problem?
> Platform: SQL Server 2005 + C# (.net 2.0)

Calling a web service from a CLR SQL stored procedure

Hi,
I am trying to call a remote webservice from within SQl server using CLR.
However, SQL Server gives me an error saying that load(), loadModule() etc
are disabled. Of what I can tell, C# needs to dynamically generate and load
the XML serialization for the web service call. Does anyone know how I can
get around this problem?
Platform: SQL Server 2005 + C# (.net 2.0)Please use the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Krishna" <Krishna@.discussions.microsoft.com> wrote in message
news:B5643087-4F83-4EB6-AA7E-12D7CF6C6DEF@.microsoft.com...
> Hi,
> I am trying to call a remote webservice from within SQl server using CLR.
> However, SQL Server gives me an error saying that load(), loadModule() etc
> are disabled. Of what I can tell, C# needs to dynamically generate and
> load
> the XML serialization for the web service call. Does anyone know how I can
> get around this problem?
> Platform: SQL Server 2005 + C# (.net 2.0)

Calling a web service from a CLR SQL stored procedure

Hi,
I am trying to call a remote webservice from within SQl server using CLR.
However, SQL Server gives me an error saying that load(), loadModule() etc
are disabled. Of what I can tell, C# needs to dynamically generate and load
the XML serialization for the web service call. Does anyone know how I can
get around this problem?
Platform: SQL Server 2005 + C# (.net 2.0)
Please use the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Krishna" <Krishna@.discussions.microsoft.com> wrote in message
news:B5643087-4F83-4EB6-AA7E-12D7CF6C6DEF@.microsoft.com...
> Hi,
> I am trying to call a remote webservice from within SQl server using CLR.
> However, SQL Server gives me an error saying that load(), loadModule() etc
> are disabled. Of what I can tell, C# needs to dynamically generate and
> load
> the XML serialization for the web service call. Does anyone know how I can
> get around this problem?
> Platform: SQL Server 2005 + C# (.net 2.0)

Sunday, March 11, 2012

Calling a sp from a UDF

Dear folks,
Is it possible? We are getting a error message from QA.
Let me know any thought/idea/link about this.
Regards,
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From the BOL
The types of statements that are valid in a function include:
a.. DECLARE statements can be used to define data variables and cursors
that are local to the function.
b.. Assignments of values to objects local to the function, such as using
SET to assign values to scalar and table local variables.
c.. Cursor operations that reference local cursors that are declared,
opened, closed, and deallocated in the function. FETCH statements that
return data to the client are not allowed. Only FETCH statements that assign
values to local variables using the INTO clause are allowed.
d.. Control-of-flow statements.
e.. SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
f.. UPDATE, INSERT, and DELETE statements modifying table variables that
are local to the function.
g.. EXECUTE statements calling an extended stored procedure.
So it looks like you can call extended sprocs, but not local sprocs.
Rick Sawtell
MCT, MCSD, MCDBA|||Only extended procedures can be executed from user-defined functions. Can't
you include the logic from your procedure in the UDF?
ML
http://milambda.blogspot.com/|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From BOL:
The following statements are allowed in the body of a multi-statement
function. Statements not in this list are not allowed in the body of a
function: ...
Assignment statements.
Control-of-Flow statements.
DECLARE statements defining data variables and cursors that are local to the
function.
SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
Cursor operations referencing local cursors that are declared, opened,
closed, and deallocated in the function. Only FETCH statements that assign
values to local variables using the INTO clause are allowed; FETCH
statements that return data to the client are not allowed.
UPDATE, INSERT, and DELETE statements modifying table variables that are
local to the function.
EXECUTE statements calling an extended stored procedure.
Also from BOL:
User-defined functions cannot be used to perform a set of actions that
modify the global database state.|||No this is not possible.
HTH, jens Suessmeyer.|||Have not tried it, but couldn't you call a clr sproc from a clr UDF?
William Stacey [MVP]
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
| Dear folks,
|
| Is it possible? We are getting a error message from QA.
| Let me know any thought/idea/link about this.
|
| Regards,
|
| Enric

calling a stored procedure from another on sql server 2000, bo

Here are two outstanding articles.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Alejandro Mesa" wrote:
> Is this case you do not need a transaction in the second sp, if the insert
> fails, nothing with be inserted. You have to check the return value of the
> second sp in the first one.
> CREATE PROCEDURE dbo.TestTran1
> as
> set nocount on
> declare @.rv int
> declare @.erri int
> begin tran
> insert into prosjekt values('this is a test', NULL, 1)
> select @.erri = @.@.error
> if @.erri <> 0
> goto errHandler
> exec @.rv = TestTran2
> select @.erri = coalesce(nullif(@.rv, 0), @.@.error)
> if @.erri <> 0
> goto errHandler2
> commit tran
> return 0
> errHandler:
> if @.@.trancount > 0
> rollback tran
> Raiserror('Det oppstod en feil i TestTran1',16,1)
> GO
> CREATE PROCEDURE dbo.TestTran2 AS
> set nocount on
> insert into prosjekt values('Dette er et testprosjekt Dette er et
> testprosjekt Dette er et testprosjekt ', null, 2)
> return @.@.error
> GO
>
> AMB
>
> "kongsballa" wrote:
>Thank you both for your replies!!
bd, I see your point there!
Alejandro, the reason both procedures use the "begin transaction" is
because they should also be able to run standalone. I have the same
problem with stored procedures in my production environment. Different
stored procedures might call each other to do a piece of work, but can
also be called standalone. Would you concider the approach where you
test the @.@.trancount first as a good one?
I will read the articles you recommended.
Thanks!!
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Thursday, March 8, 2012

Callind Stored Proc from Stored Proc

I am trying to call a stored proc from within a stored proc as below, but it
throws a syntax error.
SELECT COUNT(*) FROM (EXEC spName)
I have no access to "spName" to make any amends, currnetly it returns a
large recordset of customers.
Anyone any ideas how to do this?Billy
INSERT INTO #tmp EXEC spName --Make sure that number of columns in the
#tmp should be match to the output from the SP
SELECT COUNT(*) FROM #tmp
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:26DC8B64-FF85-4BE0-8913-B84A021C2D40@.microsoft.com...
>I am trying to call a stored proc from within a stored proc as below, but
>it
> throws a syntax error.
> SELECT COUNT(*) FROM (EXEC spName)
> I have no access to "spName" to make any amends, currnetly it returns a
> large recordset of customers.
> Anyone any ideas how to do this?
>|||Try using "select @.@.rowcount" immediately after executing the proc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Callind Stored Proc from Stored Proc

I am trying to call a stored proc from within a stored proc as below, but it
throws a syntax error.
SELECT COUNT(*) FROM (EXEC spName)
I have no access to "spName" to make any amends, currnetly it returns a
large recordset of customers.
Anyone any ideas how to do this?
Billy
INSERT INTO #tmp EXEC spName --Make sure that number of columns in the
#tmp should be match to the output from the SP
SELECT COUNT(*) FROM #tmp
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:26DC8B64-FF85-4BE0-8913-B84A021C2D40@.microsoft.com...
>I am trying to call a stored proc from within a stored proc as below, but
>it
> throws a syntax error.
> SELECT COUNT(*) FROM (EXEC spName)
> I have no access to "spName" to make any amends, currnetly it returns a
> large recordset of customers.
> Anyone any ideas how to do this?
>
|||Try using "select @.@.rowcount" immediately after executing the proc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Call vb.Net developed dll in SQL Server 2005 with configuration level 80 then gets error "I

Hi,

I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.

Error Source: ODSOLE Extended Procedure

Description: Invalid class string

Code of stored procedure and vb.net class is given below:

VB.Net

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class PositivePay

Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)

' impersonate the calling user

Dim newContext As System.Security.Principal.WindowsImpersonationContext

newContext = SqlContext.WindowsIdentity.Impersonate()

Try

Dim w As StreamWriter = File.AppendText(LogName)

LogIt(newMessage, w)

w.Close()

Catch Ex As Exception

Finally

newContext.Undo()

End Try

End Sub

End Class

===============================================================

STORED PROCEDURE

Create PROCEDURE [dbo].[PPGenerateFile]

AS

BEGIN

Declare @.retVal INT

Declare @.comHandler INT

declare @.errorSource nvarchar(500)

declare @.errorDescription nvarchar(500)

declare @.retString nvarchar(100)

-- Intialize the COM component

EXEC @.retVal= sp_OACreate 'PositivePay.class', @.comHandler OUTPUT

IF(@.retVal <> 0)

BEGIN

--Trap errors if any

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

-- Call a method into the component

EXEC @.retVal = sp_OAMethod @.comHandler,'LogToTextFile',@.retString OUTPUT, @.LogName = 'D:\text.txt',@.newMessage='Hello'

IF (@.retVal <>0 )

BEGIN

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

select @.retString

END

sp_OACreate is used to invoke a OLE object. Assuming you are wanting to leverage SQLCLR since this is the forum you are posting the question in to do this you would create a class in .Net, reference it in a CLR procedure, and deploy both the class and the procedure.

If you are wanting to leverage the older (.80/2000) methods you can try creating a Service Component (assuming this is still supported in .Net 2.0, this exploses .Net assemblies via COM+) and then use sp_OACreate to invoke the type.

Derek

|||

Hi Derek,

Basicaly our application is using SQL Server 2005 with configuration level 80. So i have to use older method to call com component from SQL Server using stored procedure as shown in already posted code.
Please review my code and help me what should i do to perfom my task successfuly. I give you again some description about the task.
Basicaly a job will call the stored procedure at some settled time. That stored procedure will innvoke the component (whose developed in VB.Net using Framework 2.0). That component will get some record from database and will write them in simple .txt file.

So please tell me by revewing my above code that what should i do?
Best Regards,
Jawad Naeem

|||

Try this:

use the type library export, TlbExp.exe via the .Net Framework SDK command prompt, you can use the "/?" to show it's full syntax.

Then Register your new type via RegSvr32.exe

Then test invoking it from say a VBScript using Dim oTest oTest = CreateObject()

If the test succeeds, then use the sp_OACreate in a TSQL script to invoke the type successfuly.

The end goal here is to create a basic COM component from a managed assembly. TSQL's consumption of the object is a mute point. Any automation-aware environment could consume it.

HTH,

Derek

|||

Hi,

Please tell me the steps with more detail, like using code example

Thanks

|||I am assuming your new thread was the same issue as this one, thus please see your other thread for my response...and give me two answers if it solves your problem lol :)

Call vb.Net developed dll in SQL Server 2005 with configuration level 80 then gets error &qu

Hi,

I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.

Error Source: ODSOLE Extended Procedure

Description: Invalid class string

Code of stored procedure and vb.net class is given below:

VB.Net

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class PositivePay

Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)

' impersonate the calling user

Dim newContext As System.Security.Principal.WindowsImpersonationContext

newContext = SqlContext.WindowsIdentity.Impersonate()

Try

Dim w As StreamWriter = File.AppendText(LogName)

LogIt(newMessage, w)

w.Close()

Catch Ex As Exception

Finally

newContext.Undo()

End Try

End Sub

End Class

===============================================================

STORED PROCEDURE

Create PROCEDURE [dbo].[PPGenerateFile]

AS

BEGIN

Declare @.retVal INT

Declare @.comHandler INT

declare @.errorSource nvarchar(500)

declare @.errorDescription nvarchar(500)

declare @.retString nvarchar(100)

-- Intialize the COM component

EXEC @.retVal= sp_OACreate 'PositivePay.class', @.comHandler OUTPUT

IF(@.retVal <> 0)

BEGIN

--Trap errors if any

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

-- Call a method into the component

EXEC @.retVal = sp_OAMethod @.comHandler,'LogToTextFile',@.retString OUTPUT, @.LogName = 'D:\text.txt',@.newMessage='Hello'

IF (@.retVal <>0 )

BEGIN

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

select @.retString

END

sp_OACreate is used to invoke a OLE object. Assuming you are wanting to leverage SQLCLR since this is the forum you are posting the question in to do this you would create a class in .Net, reference it in a CLR procedure, and deploy both the class and the procedure.

If you are wanting to leverage the older (.80/2000) methods you can try creating a Service Component (assuming this is still supported in .Net 2.0, this exploses .Net assemblies via COM+) and then use sp_OACreate to invoke the type.

Derek

|||

Hi Derek,

Basicaly our application is using SQL Server 2005 with configuration level 80. So i have to use older method to call com component from SQL Server using stored procedure as shown in already posted code.
Please review my code and help me what should i do to perfom my task successfuly. I give you again some description about the task.
Basicaly a job will call the stored procedure at some settled time. That stored procedure will innvoke the component (whose developed in VB.Net using Framework 2.0). That component will get some record from database and will write them in simple .txt file.

So please tell me by revewing my above code that what should i do?
Best Regards,
Jawad Naeem

|||

Try this:

use the type library export, TlbExp.exe via the .Net Framework SDK command prompt, you can use the "/?" to show it's full syntax.

Then Register your new type via RegSvr32.exe

Then test invoking it from say a VBScript using Dim oTest oTest = CreateObject()

If the test succeeds, then use the sp_OACreate in a TSQL script to invoke the type successfuly.

The end goal here is to create a basic COM component from a managed assembly. TSQL's consumption of the object is a mute point. Any automation-aware environment could consume it.

HTH,

Derek

|||

Hi,

Please tell me the steps with more detail, like using code example

Thanks

|||I am assuming your new thread was the same issue as this one, thus please see your other thread for my response...and give me two answers if it solves your problem lol :)

Wednesday, March 7, 2012

Call Stored Procedure AS400 From SQL Server

Hi,
I have SQL Server 7 with a link server.
When i try to execute a SP from SQL Server, i have this error :
[ OLE/DB provider returned message: [ IBM][Pilote ODBC iSeries
Access][DB2 UDB]SQL0107 - "pack14rexe" is too long. 10 characters
maximum are accepted. ]
I try this : exec recette.recette.pack14rexe.idcb050
I think that it's the '.' but i've try to change with '/' but i have an
another error...
Can you help me?
Thank's
Portekoi
Hi
I don't know specifically about your error, but there seems to be similar
issue with MS Query and the ODBC driver
http://groups.google.com/groups?q=SQ...g=d&hl=en&lr=&
you may want to try the oledb driver:
http://msdn.microsoft.com/library/de..._db2_oledb.asp
or possibly use OPENQUERY (if you do return a rowset from the stored
procedure or add a dummy one to the query).
See also http://support.microsoft.com/kb/218590/
John
"Portekoi" <portekoi@.passion-cinquieme.com> wrote in message
news:1138975377.316513.28700@.g49g2000cwa.googlegro ups.com...
> Hi,
> I have SQL Server 7 with a link server.
> When i try to execute a SP from SQL Server, i have this error :
> [ OLE/DB provider returned message: [ IBM][Pilote ODBC iSeries
> Access][DB2 UDB]SQL0107 - "pack14rexe" is too long. 10 characters
> maximum are accepted. ]
> I try this : exec recette.recette.pack14rexe.idcb050
> I think that it's the '.' but i've try to change with '/' but i have an
> another error...
> Can you help me?
> Thank's
>
> Portekoi
>
|||I can't help you with your problem, But I sure love to know how you configured your linked server.
I am using Client Access Express and can't find any documentation for what the Product Name, Provider String
and Data Source Should be.
Thanks,
Johnny
Posted with NewsLeecher v3.0 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet

Call Stored Procedure AS400 From SQL Server

Hi,
I have SQL Server 7 with a link server.
When i try to execute a SP from SQL Server, i have this error :
[ OLE/DB provider returned message: [ IBM][Pilote ODBC iSeries
Access][DB2 UDB]SQL0107 - "pack14rexe" is too long. 10 characters
maximum are accepted. ]
I try this : exec recette.recette.pack14rexe.idcb050
I think that it's the '.' but i've try to change with '/' but i have an
another error...
Can you help me?
Thank's :)
PortekoiHi
I don't know specifically about your error, but there seems to be similar
issue with MS Query and the ODBC driver
http://groups.google.com/groups?q=SQL0107&start=0&scoring=d&hl=en&lr=&
you may want to try the oledb driver:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/his_2004Main/htm/_sna_distributed_query_support_using_the_ole_db_provider_for_db2_oledb.asp
or possibly use OPENQUERY (if you do return a rowset from the stored
procedure or add a dummy one to the query).
See also http://support.microsoft.com/kb/218590/
John
"Portekoi" <portekoi@.passion-cinquieme.com> wrote in message
news:1138975377.316513.28700@.g49g2000cwa.googlegroups.com...
> Hi,
> I have SQL Server 7 with a link server.
> When i try to execute a SP from SQL Server, i have this error :
> [ OLE/DB provider returned message: [ IBM][Pilote ODBC iSeries
> Access][DB2 UDB]SQL0107 - "pack14rexe" is too long. 10 characters
> maximum are accepted. ]
> I try this : exec recette.recette.pack14rexe.idcb050
> I think that it's the '.' but i've try to change with '/' but i have an
> another error...
> Can you help me?
> Thank's :)
>
> Portekoi
>|||I can't help you with your problem, But I sure love to know how you configured your linked server.
I am using Client Access Express and can't find any documentation for what the Product Name, Provider String
and Data Source Should be.
Thanks,
Johnny
--
---
Posted with NewsLeecher v3.0 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
---

Call Stored Procedure AS400 From SQL Server

Hi,
I have SQL Server 7 with a link server.
When i try to execute a SP from SQL Server, i have this error :
[ OLE/DB provider returned message: [ IBM][Pilote ODBC iSeries
Access][DB2 UDB]SQL0107 - "pack14rexe" is too long. 10 characters
maximum are accepted. ]
I try this : exec recette.recette.pack14rexe.idcb050
I think that it's the '.' but i've try to change with '/' but i have an
another error...
Can you help me?
Thank's
PortekoiHi
I don't know specifically about your error, but there seems to be similar
issue with MS Query and the ODBC driver
http://groups.google.com/groups?q=S...ng=d&hl=en&lr=&
you may want to try the oledb driver:
_sna_distributed_query_support_using_the
_ole_db_provider_for_db2_oledb.asp" target="_blank">http://msdn.microsoft.com/library/d...r_db2_oledb.asp
or possibly use OPENQUERY (if you do return a rowset from the stored
procedure or add a dummy one to the query).
See also http://support.microsoft.com/kb/218590/
John
"Portekoi" <portekoi@.passion-cinquieme.com> wrote in message
news:1138975377.316513.28700@.g49g2000cwa.googlegroups.com...
> Hi,
> I have SQL Server 7 with a link server.
> When i try to execute a SP from SQL Server, i have this error :
> [ OLE/DB provider returned message: [ IBM][Pilote ODBC iSerie
s
> Access][DB2 UDB]SQL0107 - "pack14rexe" is too long. 10 characters
> maximum are accepted. ]
> I try this : exec recette.recette.pack14rexe.idcb050
> I think that it's the '.' but i've try to change with '/' but i have an
> another error...
> Can you help me?
> Thank's
>
> Portekoi
>|||I can't help you with your problem, But I sure love to know how you configur
ed your linked server.
I am using Client Access Express and can't find any documentation for what t
he Product Name, Provider String
and Data Source Should be.
Thanks,
Johnny
--
---
Posted with NewsLeecher v3.0 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
---

Call Stack Level

I figure there is someway to pull this out but I can't find it anywhere. I
have a recursive stored procedure and I want to error out if it recurses
more than 10 times. How can I pull out how deep in the call stack I am? Is
this possible? I know I could pass a parameter to the stored procedure and
keep incrementing it but I was hoping there was a better way.
--
TIA
AltmanAltman (NotGiven@.SickOfSpam.com) writes:
> I figure there is someway to pull this out but I can't find it anywhere.
> I have a recursive stored procedure and I want to error out if it
> recurses more than 10 times. How can I pull out how deep in the call
> stack I am? Is this possible? I know I could pass a parameter to the
> stored procedure and keep incrementing it but I was hoping there was a
> better way.
There is a global variable (or function as Microsoft calls it these
days), @.@.nestlevel that holds this information. @.@.nestlevel is increased
by 1 for every scope you to descend to. For this reason, I would be hesitant
to use @.@.nestlevel, as the rules would change if your procedure was
wrapped into another procedure. Passing a parameter is probably better.
The max nesting level in SQL Server is 32, by the way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx