Issue Executing Stored Procedure Using Constraints

Nov 7, 2007



I have been uisng the latest MSSQL JDBC jar.
I have a Stored procedure which inserts data into RTFTickerData table which had the following type of constraint 'Check_RTFTickerData' defined in it.


Constraint details: (len(ltrim([TickerID])) > 0 and len(ltrim([PrimaryFeedID])) > 0 and len(ltrim([PrimaryServiceProvider])) > 0 and len(ltrim([LastUpdateBy])) > 0 and len(ltrim([LastUpdateApp])) > 0 and len(ltrim([LastUpdateFrom])) > 0 and [ScalingFactor] <> 0.0)

Now when i try to execute this SP I get an error

To call this stored procedure I use Callable statement.If I use a simple Statement it works fine.I want it to work using CallableStatement only.


UPDATE statement conflicted with TABLE CHECK constraint 'Check_RTFTickerData'. The conflict occurred in database 'PJ_Adaptiv', table 'RTFTickerData'.

If I try using older version of the JDBC driver it works fine.

Please help me out solving this issue using the latest jar?Is there anything that I have lto set in the code ?

View 1 Replies


ADVERTISEMENT

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

Dropping FK Constraints In A Stored Procedure

Jul 23, 2005

I just looked at a coworker's stored procedure and this person isdropping 4 Foreign key constraints and then re-adding them afterprocessing the required logic (updating rows in the 4 tables inquestion).Is there *ANY* good reason to do this? What are the performanceimplications of doing this - negative or otherwise?I was furious when I found this because every once in a while I wouldnotice that the constraints would be in flux....some days they werethere, othere days there were not. I mean, this is a good enough reasonto NOT do this, but I need some additional feedback. Maybe there *is* agood reason, and that the logic just needs tweaking.Thanks.

View 4 Replies View Related

Executing DTS From Stored Procedure

Oct 11, 2001

Hi,

I have create a DTS package "test"

i see the name is stored in msdb.sysdtspackages

how do i run this "test" package from stored procedure

thx
vik

View 1 Replies View Related

Executing A Stored Procedure

Nov 1, 2006

Hello,

I'm using Sql Server 2005, and I am receiving an error when I attempt to run a stored procedure and I have no clue why. Can someone please help?

I receive an error when I attempt to execute the following stored procedure:
exec INSERT_OBJ
1234,
'Name',
123,
NULL,
GetDate(),
'system'

The error I receive is: "Incorrect syntax near ')'" All of the values are valid values and valid types.

Is it possible to be an error within the stored procedure itself? This error makes it sound like the syntax of my attempt is incorrect, thus it never gets ran.

Thank you ,
Crystal

View 2 Replies View Related

Need Help In Executing A Stored Procedure Using Op

Jun 12, 2008

Hi All,

I am trying to execute a "ServerB" Stored Procedure in "ServerA".This SP is in Multiple DB's in ServerB.
I am trying to use Openquery and Dynamic SQL to do that.But I am having issues.
Intially i am trying to pass just one DBname as parameter..if it returns values then i can use cursor or other options to retrieve for multiple DB's
Please Help!!!

Ex:



DECLARE @TSQL varchar(8000), @DBNAME char(20)
SELECT @DBNAME = 'DB1'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(serverB'+','+''exec '' + @DBNAME + ''.dbo.sp_StoredProcedure''+')'
EXEC (@TSQL)


Thanks in Advance!!!

View 3 Replies View Related

Executing Stored Procedure Using VB6.0

Jul 10, 2007

Hi,
Can anyone tell me how to execute a stored procedure using vb6.0.I am able to connect to sqlserver from my application.Simple select queries are working.

The stored procedure contains a select statement at the end.I want to get that as the resultset in vb6.0.How do I do this.

Keerthi

View 1 Replies View Related

Executing Stored Procedure... Hangs...

Oct 2, 2007

Hi
I have a stored procedure in SQL Server 2005. It make a backup of a database and restores it to a different name.I use ASP.NET and Framework 1.1.It works really fine when I use SQL Server 2000.
But!When trying to do the same thing on SQL Server 2005, the database seems to be created "half way" I can see that the database is created, but after the name is the text Restoring....It never finish restoring.... and nothing shows in the server logs.
Any ideas?Differences between SQL Server 2000 and SQL Server 2005 that I must be aware of?Priviliges?ConnectionString parameters?Drivers?
I'm using .NET Framework 1.1ODBC (SQL Native Client)
Here is the Store procedure code:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 ALTER PROCEDURE [dbo].[CreateProjectDataBase]
@AFModelDatabaseProject varchar(200),
@TemplateDbBackupFileAndName varchar(200),
@DatabaseName varchar(200),
@DataFilePathAndName varchar(200),
@LogFilePathAndName varchar(200)
AS
 
BACKUP DATABASE @AFModelDatabaseProject TO DISK = @TemplateDbBackupFileAndName
WITH INIT
RESTORE DATABASE @DatabaseName FROM DISK = @TemplateDbBackupFileAndName
WITH MOVE 'AdressTNG_Project_Data' TO @DataFilePathAndName,
MOVE 'AdressTNG_Project_Log' TO @LogFilePathAndName
 
and here is how it is called from within .NET:this.odbcCreateDataBaseCommand.CommandType = System.Data.CommandType.StoredProcedure;
this.odbcCreateDataBaseCommand.Parameters["@AFModelDatabaseProject"].Value = afModelDataBaseName;this.odbcCreateDataBaseCommand.Parameters["@TemplateDbBackupFileAndName"].Value = TemplateDbBackupFileAndName;
this.odbcCreateDataBaseCommand.Parameters["@DatabaseName"].Value = dbName;this.odbcCreateDataBaseCommand.Parameters["@DataFilePathAndName"].Value = DataFilePathAndName;
this.odbcCreateDataBaseCommand.Parameters["@LogFilePathAndName"].Value = LogFilePathAndName;this.odbcCreateDataBaseCommand.CommandText = "{ CALL CreateProjectDataBase(?,?,?,?,?) }";
this.odbcCreateDataBaseCommand.ExecuteNonQuery();
RegardsTomas

View 2 Replies View Related

Bad Token When Executing Stored Procedure

Mar 11, 1999

We have a test and production environment. After transfering some tables from
test to prod and all stored procedures using those tables.
We get an error when executing those stored procedures:
" DB-library: Possible network error:
Bad token from SQL Server:
Datastream processing out of sync.
Net-library error 0:
DB-libray Process Dead - Connection Broken. "
When we execute the stored procedure with 1 parameter less we get a parameter
missing error. Then we execute the stored procedure again and everything is allright?
Has anyone experienced this before? If so, please help.
SQlServer 6.50.201

Kees Visser

View 3 Replies View Related

Executing A Stored Procedure Result

Jul 22, 2004

Hi Guys..

How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..

I got this results from running (sp_dropallindex) like this:

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO
ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO
DROP INDEX table1.index1 GO
DROP INDEX table1.index2 GO

I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...

I tried something like this:

DECLARE @DROP AS VARCHAR(8000)
SET @DROP='exec sp_drop_allindex'
EXECUTE (@DROP)

and i see the same out , but my indexes and PK still there ... i'm confused about it ..

PLEASE HELP ME OUT :D

View 1 Replies View Related

Executing A Stored Procedure From Server

Oct 4, 2014

how to use opendatasource to execute a stored procedure remotely in MySQL with parameters..I am using sp_configure to enable and disable Ad Hoc Distributed queries as below before and after the open data source statement

sp_configure show,1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

[code]...

View 0 Replies View Related

Executing Stored Procedure In A Select

Jun 12, 2008

i wanna execute a stored procedure in a select and use its return type
i.e

select name from table1 where id = sp 1,1

i executed it and occurred an error

help me pls

View 2 Replies View Related

Stored Procedure Executing Durations Are Different

Jan 24, 2008

Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image attached


Any ideas for this issue?

Thanks a lot

View 2 Replies View Related

Stored Procedure, Error When Executing (

Feb 20, 2007

Hi everyone,first time here, so I'm sorry if this has been covered already agesago. :(I am trying to learn T-SQL and Stored Procedures and bought the bookon these topics by Djan Sunderic, Publisher McGraw Hill/Osborne. I'malready stuck on my first Stored Procedure and getting error messagesthat I cannot understand. I've already tried Google and Microsoftonline to no avail. I do have the .NET Framework on my system and useit for programming in C# sometimes and by the looks of the error, it'ssomething to do with that? Please note I am only using SQL ServerExpress.Here is the first Stored Procedure:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE ap_HelloASBEGINSET NOCOUNT ON;SELECT 'Hello world'SELECT * from dbo.EqTypeENDGOThe error in its entirety is this:Msg 6522, Level 16, State 1, Procedure ctrd_DDL_PROCEDURE_EVENTS_vb,Line 1A .NET Framework error occurred during execution of user definedroutine or aggregate 'ctrd_DDL_PROCEDURE_EVENTS_vb':System.UnauthorizedAccessException: Access to the path 'c:server_login.log' is denied.System.UnauthorizedAccessException:at System.IO.__Error.WinIOError(Int32 errorCode, StringmaybeFullPath)at System.IO.FileStream.Init(String path, FileMode mode, FileAccessaccess, Int32 rights, Boolean useRights, FileShare share, Int32bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, StringmsgPath, Boolean bFromProxy)at System.IO.FileStream..ctor(String path, FileMode mode,FileAccess access, FileShare share, Int32 bufferSize, FileOptionsoptions)at System.IO.StreamWriter.CreateFile(String path, Boolean append)at System.IO.StreamWriter..ctor(String path, Boolean append,Encoding encoding, Int32 bufferSize)at System.IO.StreamWriter..ctor(String path, Boolean append)at VbTriggers.Triggers.trigger_DDL_PROCEDURE_EVENTS() ..The statement has been terminated.Can anyone assist please? :(Many thanks in advance,Lara

View 3 Replies View Related

Timer For Executing Stored Procedure

Jul 20, 2005

HI,I need to run same kind of transactions (basically deleting records)in a loop but I have only 1 hour in a day to run my procedure. So Ineed to set a timer in a SP so that SP terminates after one hour andthen rest of the transactions will be done next day.Can anybody suggest as how to check execution time in a storedprocedure? The execution of the SP will be scheduled every night.If u need any further info pls ask.Thanks,Subodh

View 2 Replies View Related

Get Records After Executing A Stored Procedure

Dec 1, 2006

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@countRun =?,
@startTime =?,
@endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

View 9 Replies View Related

Executing A Script From A Stored Procedure

Mar 27, 2008



I have a maintenance stored procedure that executes a script using xp_cmdshell and sqlcmd. The stored procedure has one argument that is contains the name of the procedure to update.

The stored procedures retrieves the location of the stored procedures (stored in a table) and then builds a variable that contains the sqlcmd command. Note that the file name of the script is <stored procedure name>.sql

Basically stored procedure is ...

select @cmd = 'sqlcmd -E -d ' + @dbname + ' -i "' + @filelocation + @proc + '.sql" '
exec master..xp_cmdshell @cmd


Is there a way to execute sql scripts within a stored procedure without using xp_cmdshell?

Thanks!!

View 2 Replies View Related

Executing Stored Procedure With Variables

Nov 21, 2006

I have a foreach loop in my SSIS script. I am able to successfully enumerate through an input query. I have a script task inside of my container. I would like to use this task to formulate a Stored Procedure and save this procedrue in a variable so I can use in a future Execute SQL task.

Here is a copy of the code (Which Does Not Work) I am using in the script task to set the variables.

Public Sub Main()

Dim vars As Variables

Dim DropVariable As String

Dim CreateVariable As String

Dim InsertVariable As String

DropVariable = "Execute dbo.[sp_DropTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

CreateVariable = "Execute dbo.[sp_CustomTables] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

InsertVariable = "Execute dbo.[sp_InsertTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

Dts.VariableDispenser.LockOneForWrite("varDropTable", vars)

Dts.VariableDispenser.LockOneForWrite("varCreateTable", vars)

Dts.VariableDispenser.LockOneForWrite("varInsertTable", vars)

Dts.Variables("varDropTable").Value = DropVariable

Dts.Variables("varCreateTable").Value = CreateVariable

Dts.Variables("varInsertTable").Value = InsertVariable



'MsgBox(Dts.Variables("varDropTable").Value)

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

View 4 Replies View Related

Diff B/w Executing As Stored Procedure And Script

Nov 6, 2006

Hi All,     I have a peculiar problem with SQL2000. When i execute a Stored procedure in Demo & Production i get different outputs. But i copied the business logistics from the Sp and executed as a script in both the servers. Now Both the records are same.
I WANT TO KNOW  " WHETHER THERE IS ANY DIFFERENCE IN EXECUTION METHODOLOGY BETWEEN STORED PROCEDURE AND QUERY".
NOTE: My stored procedure has 14 executable scripts. Upto 10 scripts no date comparisons were made. But at the 11th script the records differ.
I DOUBT WHETHER THERE WILL BE ANY DATE RELATED ISSUE WHEN EXECUTING AS STORED PROCEDURE AND SCRIPT

View 2 Replies View Related

Executing SSIS From Stored Procedure - Need To Get Return Value Into ASP.NET

Aug 23, 2007

I'm executing an SSIS package using the following stored procedure
 ALTER PROC [dbo].[SSISRunBuildSCCDW] AS

BEGIN

DECLARE @ServerName VARCHAR(30), @ReturnValue int, @Cmd varchar(1000)

SET @ReturnValue = -1
SET @ServerName = 'myserver'

SET @Cmd = 'DTExec /SER ' + @ServerName + ' ' +
' /SQL ' + 'BuildSCCDW ' --Location of the package stored in the mdb
--' /CONF "\ConfigFilePath.dtsConfig" ' +
--' /SET Package.Variables[ImportUserID].Value; ' +
--' /U "LoginName" /P "password" '

EXECUTE @ReturnValue = master..xp_cmdshell @Cmd, NO_OUTPUT
RETURN @ReturnValue
--SELECT @ReturnValue [Result]

END
 I'm then using a tableadapter to execute this from my ASP.NET page using the following code, Protected Sub ExecutePackage()
Dim ExecuteAdapter As New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter()
ExecuteAdapter.SetCommandTimeOut(0)
Dim strResult As String
strResult = ExecuteAdapter.Execute()
lblResult.Text = strResult
End Sub
 If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.
When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @ReturnValue and SELECT @ReturnValue to no avail.
Can someone suggest how I can get the value 0-6 to my code?
 

View 1 Replies View Related

Executing A Variable Inside A Stored Procedure

May 7, 2005

Hello :) I need to do something like this:
 
CREATE PROCEDURE SelectCostumers @name varchar(100)
Declare @SQL = "SELECT Id, Name FROM Costumers"
AS
IF (@name IS NULL)
@SQL
ELSE
@SQL += "WHERE Name LIKE @name"
 
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
 
Thank you

View 2 Replies View Related

Problem With Executing Query Or Stored Procedure

May 8, 2002

Hi all
i am facing problems in executing stored procedures.
Sometimes the connection to server fails.
all the query, sp working fine few minutes back fails.
the sp executes and executes and does not get results but hangs later a get an error.

Any ideas please let me know it is quite urgent. i am at the point of testing
and only now facing such problems

thanks
with regards
roopa

View 3 Replies View Related

Access Not Executing Stored Procedure Correctly

Dec 16, 2004

Hi guys I cant seem to get my stored procedure to execute properly through Access Xp. Do you think there is something wrong with my stored procedure??

CREATE PROCEDURE [insert_ConditionalLicense_UpdateFromTerms]
(@TM_# [int],
@FirstName [nvarchar](50),
@LastName [nvarchar](50),
@SS# [nvarchar](50),
@Birthdate [nvarchar](50),
@reasonforconditional [ntext],
@Notes [ntext])

AS INSERT INTO [GamingCommissiondb].[dbo].[ConditionalLicense_View]
( [TM #],
[FirstName],
[LastName],
[SS#],
[reasonforconditional],
[ConditionalStart Date])


SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]

FROM EmployeeGamingLicense
WHERE STATUS = 'TERMINATION-COND'
IF @@Error <> '0'
RETURN



when I execute it through a command button this is the message I get "paramater" not quite sure why I am getting this message

View 5 Replies View Related

SQL 2012 :: Executing Stored Procedure As Different User

Nov 26, 2014

I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.

[code=sql]
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[Code] ....

If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.

Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?

If so, how to continue.

In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.

View 2 Replies View Related

Executing Stored Procedure With Parameter NULL

Jun 25, 2015

ALTER PROCEDURE [dbo].[p_sub_agent_Grp_report]
@parent_pay_agent_cd VARCHAR(25) ,
@tagno NUMERIC(18,0) = NULL,
@labFromCLS VARCHAR(10),
@labToCLS VARCHAR(10),
@status VARCHAR(1)

[Code] .....

1. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , 173, '2015-06-01' , '2015-06-25', 'Y'
2. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , NULL, '2015-06-01' , '2015-06-25', 'Y'

2nd Stored procedure executes successfully and 1st stored does not executes where only difference is 3rd parameter value 173 and NULL value.

Is it because " IF @tagno IS NOT NULL" used in stored procedure is not working while @tagno is sent as 173 in sp parameter. Error throwing while executing 1st stored procedure is as below.

Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.

Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.

Note: sub_agent_tag_no column of table Sub_agent_tag_dtl has datatype Numeric(18,0).

View 2 Replies View Related

Executing A Stored Procedure In ActiveX Script

Dec 13, 2005

Hi!

How do you call a store procedure in an ActiveX script? I have a stored procedure that I want to execute everytime my Data Driven Query tasks inserts a new record.

Thanks.


$3.99/yr .COM!
http://www.greatdomains4less.com

View 2 Replies View Related

Executing An MS SQL Stored Procedure From A Java Servlet

Jul 20, 2005

I'm trying to use a servlet to process a form, then send that data toan SQL server stored procedure. I'm using the WebLogic 8 App. server.I am able to retrieve database information, so I know my applicationserver can talk to the database.I've determined the failure occurs when the the following statement isexecuted: cstmt.execute(); (due to the failure of println statementsplaced afterwards). I get the following error after trying to executethe stored procedure call:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find storedprocedure 'insertTheForm'The username and password i'm using to connect is a Windows user withadmin rights. It is also associated with the Odbc connection--and ofcourse is a database user..with full rights. I have executablepermissions on the stored procedure set up as well. I did a microsoftrecommended registry fix as well (for a previouserror:http://support.microsoft.com/defaul...;en-us;Q238971).Am I missing something? I posted my servlet code below.Thanks for any help!DineshformHandlingServlet.class-------------------------package showme;/** formHandlingServlet.java** Created on July 6, 2003, 7:01 PM*/import javax.servlet.*;import javax.servlet.http.*;import java.io.*;import java.sql.*;import java.text.DateFormat;/**** @author Administrator*/public class formHandlingServlet extends HttpServlet {private static final String email1 = "email";private static final String password1 = "password1";private static final String password2 = "password2";private static final String displayname = "displayname";Connection dbConn = null;// create a persistent conneciton to the SQL serverpublic void init() throws ServletException{String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";String dbURL = "jdbc:odbc:Con2";String usernameDbConn = "dinesh";String passwordDbConn = "werty6969";try{Class.forName(jdbcDriver).newInstance();dbConn = DriverManager.getConnection(dbURL, usernameDbConn,passwordDbConn);}catch (ClassNotFoundException e){throw new UnavailableException("jdbc driver not found:" + dbURL);}catch (SQLException e){throw new UnavailableException("error: " + e);}catch (Exception e){throw new UnavailableException("error: " +e);}}public void doPost(HttpServletRequest request, HttpServletResponseresponse) throws ServletException, IOException{response.setContentType("text/plain");PrintWriter out = response.getWriter();//extract parameter information from register.jspString email1 = request.getParameter("email1");String password1 = request.getParameter("password1");String password2 = request.getParameter("password2");String displayname = request.getParameter("displayname");try{//make a callable statement for a stored procedure.//It has four parametersCallableStatement cstmt = dbConn.prepareCall("{call insertTheForm(?, ?, ?, ?)}");//set the values of the stored procedure's input parametersout.println("calling stored procedure . . .");cstmt.setString(1, email1);cstmt.setString(2, password1);cstmt.setString(3, password2);cstmt.setString(4, displayname);//now that the input parameters are set, we can proceed to execute theinsertTheForm stored procedurecstmt.execute();out.println("stored procedure executed");}catch (SQLException e){throw new UnavailableException("error: " + e);}}}

View 3 Replies View Related

Occasional Error When Executing CLR Stored Procedure

Aug 23, 2007

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:
<code>
Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select
End If
End While
End If
End If
Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)
Finally
' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If
End Try
End Sub
</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?


View 5 Replies View Related

Stored Procedure To Executing An SSIS Package

May 24, 2006

In SQL Server 2005 I need a stored procedure that will execute an SSIS Package for me. There is some earlier stuff on the board but I don't understand it. I don't want to create a Job to do it if I don't have to.

Thanks,

George Cooper



View 3 Replies View Related

Executing SSIS Package From Stored Procedure

Mar 8, 2006

Hey guys,
I've got a problem here. I need to send the query result to a csv file then transfer the file to a website. I thought this is a good candidate for a SSIS package. The package is ready now but I don't know how can I execute it from within a stored procedure.
I thought sp_OA family of extended procedure would be helpfull. After following steps:
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT

EXEC @hr1 = sp_OAMethod @oPKG, 'LoadFromSQLServer("foo", ,, 256, , , , "foo1")', NULL

EXEC @hr1 = sp_OAMethod @oPKG, 'exec'
EXEC @hr1 = sp_OADestroy @oPKG
it tells me command execute successfully. But no package actually gets executed and I can see no results
Thanks

View 1 Replies View Related

Executing A Job From A Stored Procedure Stopped Working

Apr 29, 2008



I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:

CREATE PROCEDURE xxxxx
WITH EXECUTE AS 'domainusername'

AS

EXEC msdb.dbo.sp_start_job B'jobname' ;

RETURN

The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.

This worked for the past several months.

Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'

I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.

Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.

Thanks - SM

View 3 Replies View Related







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