Service Broker Problem In Executing A Stored Procedure

Jan 3, 2008



Hi,

i am having a problem with service broker. here is what i have

i have two databases (DB1 and DB2) and both are on seprate servers (seprate instances)
in DB1 i am putting raw data from plc using rssql.
once data gets into the table, i am executing a trigger which bind the data and send it to the target service.
in the target service i have a activated stored procedure which unbind data and then call a stored procedure which is on in DB2( i am using Linked Server ). on DB2 i am doing really really complex calculations thats why i have to use service broker to seprate the calculation to data input for performance.

i ran this query on both databases so i won't have to create the certificate.
(ALTER DATABASE MyDatabaseName SET TRUSTWORTHY ON)

now my problem is, its not executing the stored procedure from DB2 and disabling the service everytime i enter the data into rawdata table(DB1).
But if i run the stored procedure manuly from TSQL it works fine.

can you guyz help me out on this issue.

Thanks.




View 13 Replies


ADVERTISEMENT

SQL2005 + Service Broker + CLR Stored Procedure + XML + Temp Tables

Feb 8, 2008

This is killing me...and not slowly.

I have a stored procedure (a) which calls another stored procedure (b).

Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements

If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.

But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.

Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable

- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.

The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.



----------------- UPDATE --------------
I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.

If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.

View 3 Replies View Related

Raiserror In Service Broker Internal Activation Stored Procedure

May 17, 2006

I use Try ... catch blok in my activation stored procedure. When SQL Server raise error (e.g. Primary key violation) in Try blok, XACT_STATE in Catch blok has value 1 = commitable transaction and I can use rollback transaction to savepoint. But when I use Raiserror() in Try blok, XACT_STATE in Catch blok has value -1 = uncommitable transaction and I can't use rollback transaction to savepoint. When I drop automatic activation for given queue and I run this stored procedure with Raiserror(), XACT_STATE has value 1 = commitable transaction.

What a problem may cause this different behavior ?

Best Regards,
Pavel

View 4 Replies View Related

SQL Server 2005 Service Broker - Activation Stored Procedure Memory Usuage

Nov 9, 2007

We are looking for some guidance with an issue we have picked up with our implementation of Service Broker here on the ABSA Capital project and I am hoping you can help or point us in the direction of someone.

The architecture we have implemented for service broker is to make use of an Activation stored procedure on two queues (1 SP per queue) to process the messages received. What we have found is that the activation stored procedure runs on a background session and its CPU time and memory just grows to the point where it brought one of our UAT servers to a grinding halt.

Is there anyway we can reduce the memory consumption of the activation stored procedure or is this one of those things that still need to be ironed out in Service Broker?

View 6 Replies View Related

How To Prevent The Hang On The Initator Service Broker If The Target Service Broker Is Not Started?

Sep 10, 2007

How to prevent the hang on the initator service broker if the target service broker is not started?

Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?

View 3 Replies View Related

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

The SQL Server Service Broker For The Current Database Is Not Enabled, And As A Result Query Notifications Are Not Supported. Please Enable The Service Broker For This Database If You Wish To Use Notifications.

Feb 16, 2008

Hello,          I receive this error  "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ...    Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)        System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString)    End Sub...Web.config ...    <connectionStrings>        <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True"         providerName="System.Data.SqlClient" />        <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"         providerName="System.Data.SqlClient" />    </connectionStrings>... Hope you could help.  cheers,imperialx 

View 1 Replies View Related

Service Broker Activated Procedure Getting Deadlock While Running Mulitple Queue_reader Threads

Mar 7, 2008

Hi,

I am getting deadlock on activated procedure which I am using to receive message from the Service Broker Queue.

Deadlock details:

Two threads are tring to do delete on internal table queue_messages_122847900 ends up in a dead lock.

Activated procedure code

RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;

IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @handle;
RETURN 0
END
.........................
.........................
After this I do process the message and some other processing

And then

END CONVERSATION @handle;

Note I do have single conversation group

Is their a problem in the way I am receiving and processing messages. Is it possible because of the delay between RECEIVE and END CONVERSATION same message is read by two different threads.

Thanks

View 1 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

Architectural (broker) Place Of SQL Service Broker

Apr 5, 2007

Hi,



I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.



I look forward to some responses.



Kind regards,

Neeva

View 2 Replies View Related

Conversation Handle Processed By An Activated Stored Procedure Service Can Not Be Invoked By A CLR Service Instance?

Dec 1, 2006

I have a initiator and a target service broker peer.

Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.

Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.

The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.

For this the C# unit test creates a Conversation off of the saved conversation handle:


Service client = new Service("cleintservicename", conn, tran);

Conversation dialog = null;

dialog = new Conversation(client, convHandle);
Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>".
Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR?
If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether?
Any help - greatly appreciated.

View 7 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

Service Broker TO Service Could Not Be Found Message Origin: Transport

Mar 30, 2007

I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....



CREATE PROCEDURE [usp_ProcessMessage]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @conversation_handle uniqueidentifier

DECLARE @message_body AS VARBINARY(MAX)

WHILE (1=1)

BEGIN

BEGIN TRANSACTION;

WAITFOR(RECEIVE TOP (1)

@conversation_handle = conversation_handle,

@message_body = message_body

FROM [tcp://mydomain.com/INITIATE/MyQueue]

), TIMEOUT 1000;

IF (@@ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

END CONVERSATION @conversation_handle

IF @message_body IS NOT NULL

BEGIN



BEGIN DIALOG CONVERSATION @conversation_handle

FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]

WITH ENCRYPTION = ON, LIFETIME = 600;

SEND ON CONVERSATION @conversation_handle

MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]

(@message_body);

END

COMMIT;

END

END

GO



My endpoints are created like so:



CREATE ENDPOINT MyEndpoint

STATE = STARTED

AS TCP

(

LISTENER_PORT = 4022

)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)

GO



GRANT CONNECT TO CertOwner

GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner

GO



And my routes like so:



GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner

GO

CREATE REMOTE SERVICE BINDING [MyCertificateBinding]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

WITH USER = CertOwner,

ANONYMOUS=OFF

CREATE ROUTE [tcp://mydomain.com/INITIATE/MyRoute]

WITH SERVICE_NAME = '[tcp://mydomain.com/TARGET/MyService]',

BROKER_INSTANCE = N'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',

ADDRESS = N'TCP://xxx.xx.xx.xx:4022'

GO

View 10 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







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