Post Execute Method Not Being Called - LockReadWriteVariables
Jul 22, 2007
I have a strange bug when trying to run SSIS. I get the message in the output window "
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. " in a script component but the Post Execute method is not being called.
I have placed a firewarning as the first statement to try and debug that the procedure is being called but to no avail as it is not being output
Has anyone come across this kind of issue before or have any suggestion on how to proceed ?
After a long period of time I then got the following messge displayed
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariableDispenser90.GetVariables(IDTSVariables90& ppVariables)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.LockReadWriteVariables(String readWriteVariables)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()
Within the package I have two script components one that uses a readwrite variable to set the variable to be a date in the first script tasks PostExecute and then the second component uses the date variable in its PostExecute method. Running the package seemd to imply that the first postexecute write t the variable is somehow locking out the next component from reading the variable.
View 3 Replies
ADVERTISEMENT
May 7, 2008
Hello, I have been wrestling with a script component and the post execute method. I have obtained some bits of information from the discussions from previous posting - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97494&SiteID=1 and have also tried the suggested approaches. Being new to SSIS and .NET as well I am certain that I do not see the entire picture yet.
I have a Read/Write variable that I am assigning a value to. The variable is then processed through a derived column and subsequently used by a flat file.
I have eliminated the following exception thus far.
Error: 0xC0047062 at Data Flow Task, Script Component [518]: Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute.
But, my flat file does not contain in it the assigned values to the variable. A brief summary of the process follows:
OLE DB SOURCE ===> followed by the Script Component ===> followed by the Derived Columns ===> followed by the flat file.
Sample Script is:
Public Class ScriptMain
Inherits UserComponent
Public NewName As String
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
NewName = Row.BPNM
Me.Variables.ShortName = NewName
End Sub
Public Overrides Sub PostExecute()
MsgBox("In first script...")
Me.Variables.ShortName = NewName
MsgBox("variable is " & Me.Variables.ShortName)
End Sub
Question. What am I not doing that prevents me from extracting and passing the respective values to the derived column for each of the rows processed. When I try to assign the value of NewName to the Short Name variable, In red above, I encounter the post execute exception, again.
What code should I have in place to insure that I am not trying to reference the variable on a PostExecute within the normal process of the script component? Any guidance would be immensly appreciated.
Help Please!!
View 11 Replies
View Related
Jan 27, 2008
I have two ASP pages. Page 1 has a button that when pressed, it will display page 2. Page 2 has a button that will call the reporting service (by a URL) . The button on page 1 has the "method=post" to pass a value to page 2. Page 2 wlll use this value as variable to the reporting service.
It works fine for the first time calling the report. But after that, I go back to page 1. When press the button on page 1 and go to page 2, it cannot get the value. Is that something wrong with the reporting services.
View 2 Replies
View Related
May 21, 2004
Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."
The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155
The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------------------------------------------------
EXEC master..xp_cmdshell 'dir d:BT_importDatasales_option_price_report.csv'
Thanks for your help
ehx5
View 1 Replies
View Related
Dec 27, 2007
I have a package that used to work fine, but after I imported it into a different existing solution and tried to run it, it always stalls during the data flow task at the very end.
It will run through and process all the rows and insert them into the destination except for the last chunk. The task blocks eventually all turn green as well, but then it never proceeds to the next task after the data flow. Looking at the Progress tab for the data flow task, I get the following:
Progress: Pre-Execute - 100 percent complete
[DTS.Pipeline] Information: Execute phase is beginning.
[DTS.Pipeline] Information: Post Execute phase is beginning.
Progress: Post Execute - 0 percent complete
Then it just stays at 0 percent for Post Execute. The program doesn't hang or anything, it just doesn't progress at all. I also just checked and I run the package from its original solution and it is now exhibiting the same behavior as well.
Any ideas what might be causing this or something to do to try and figure it out or at least get more information about what might be causing it?
Thanks.
View 13 Replies
View Related
Feb 20, 2007
Yesterday, SQL Server 2005 SP2 was installed on our DEV Server (Windows 2003 SP1 x86) and now I cannot right-click a package in the Solutions Explorer window in BIDS and Execute Package. When I do, I get the following error
===================================
Object reference not set to an instance of an object. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)
I know I could do this yesterday morning pre SP2. I've attempted this with a couple of different packages and solutions, so it is probably not a corrupt package (unless now they are all corrupt). I can execute in debug mode if I open the package first and then hit Start Debugging. However this is not always an optimal solution because I sometimes have many connected packages and sometimes what gets executed is not what is expected.
Your thoughts would be appreciated.
View 1 Replies
View Related
Jan 9, 2008
Hi,
I have a strange problem with calling packages from c# code. Packages start to execute, but never finish. Instead an error occures in eventlog:
(message is translated into english so it can sound a bit different)
Erring application AppName.exe, version 1.0.0.0, timestamp 0x4784c811, erring module ntdll.dll, version 6.0.6000.16386, timestamp 0x4549d372, exception code 0xc0000374, error shift 0x00000000000c87b7, process ID 0x1284, application start time 0x01c852ceb26bbad3.
In debuger, execution stoppes on line "DTSExecResult res = dtsPkg.Execute();", but no error is showed.
This behaviour occured unexpectally. Application worked correctly and in a minute I wasn´t able to execute any package from an application. I also tryed it from another computer and got the same error. Calling packages directly from management studio works fine.
OS:
Windows Vista Bussiness 64b
Code:
Package dtsPkg = new Package();
Variables variables = dtsPkg.Variables;
Microsoft.SqlServer.Dts.Runtime.Application dtsApp = new Microsoft.SqlServer.Dts.Runtime.Application();
dtsPkg = dtsApp.LoadFromSqlServer(@"\PackageName", "server", null, null, null);
dtsPkg.InteractiveMode = true;
dtsPkg.DebugMode = true;
dtsPkg.LoggingMode = DTSLoggingMode.Enabled;
dtsPkg.VariableDispenser.LockOneForWrite("FilePath", ref variables);
Variable pathVar = variables[0];
pathVar.Value = filePath;
variables.Unlock();
DTSExecResult res = dtsPkg.Execute();
I will be grateful for any ideas
Janca
View 8 Replies
View Related
Mar 25, 2008
Hi,
I am using following code to fill my dataset. when multiple user connect to the web-site it will give me following error:
Error Message: Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am using stored procedure to select records from database.
DataSet ds = new DataSet();SqlConnection mc = OpenSqlConnection();
try
{SqlCommand cmd = new SqlCommand(strCmd, mc);cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;if (parameters != null)
{foreach (KeyValuePair<string, object> p in parameters)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
}SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}catch (Exception ex)
{
}
finally
{
mc.Close();
}
return ds;
Appreciate your help,
prashant
View 1 Replies
View Related
Dec 8, 2005
Hi, I need to send a table data into flat and then ftp into different location.
I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error.
Could you advice me regrding network engineer thought and any solution for avoiding this error.
---------------------------
Execute Process Task: C:WINDOWSsystem32ftp.exe
---------------------------
CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0.
---------------------------
Thanks,
View 1 Replies
View Related
Jan 11, 2007
My report works with XMLDP data provider and obtains source data as a
result of a web method call. This memthod returns all data necessary
for the report. Thus, all of about 20 report datasets query the same
web method with the same parameters and then select appropriate data
with different ElementPath expressions. It's so inefficient and
time-consuming! I cant have respective web method for every dataset.
How can I configure SSRS to call web method only once while the report
is executing? Or how can I leverage the performance at all?
View 1 Replies
View Related
Aug 3, 2002
Hi friends
I copy script that use SQL proc to call DTS package from this site.
When I run this procedure in SQL query alalyzer. I got an error messages:
EXECUTE permission denied on object sp_OACreate/method/GetProperty/Destroy.
I did not change anything in spExecutePKG.
Even I used sa as ServerPWD. How to fix this problem? my code is
exec spExecutePKG 'myserver','execl_DTS','sa','0',''
Thanks
jimmy
------proc original code
CREATE PROC spExecutePKG
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO
View 1 Replies
View Related
Jun 1, 2006
I've created an SSIS package that contains a Sequence Container with TransactionOption = Required. Within the container, there are a number of Execute Package Task components running in a serial fashion which are responsible for performing "Upserts" to dimension and fact tables on our production server. The destination db configuration is loaded into each of these packages using an XML configuration file. The structure of these "Upsert" packages are nearly identical, while some execute correctly and others fail. Those that fail all provide the same error messages.
These messages appear during Pre-Execute
[Insert new dimension record [1627]] Error: The AcquireConnection method call to the connection manager "DW" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Insert new dimension record" (1627) failed the pre-execute phase and returned error code 0xC020801C.
... which are followed by
[Connection manager "DW"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".
[Connection manager "DW"] Error: An OLE DB error has occurred. Error code: 0x8004D00A.
While still in debug mode, I can check the properties of the "DW" connection and successfully test the connection within the packages that fail.
The same packages run successfully when tested outside the container (i.e. no transaction) or when the configuration file is modified to point the "DW" connection to a development version of the db which is running on the same server as the source database.
I have successfully used DTCtester to verify that transactions from source to destination server are working correctly. Also tried setting DelayValidation = True with no change. I have opened a case with Microsoft and am awaiting a reply so I thought I'd throw a post out here to see if anyone else has encountered this and might have a resolution. Here's some more on the environment:
Source Server:
Windows Server 2003 Enterprise Edition SP1
SQL Server 2005 Enterprise Edition SP0
Destination Server:
Windows Server 2003 Enterprise Edition SP1
SQL Server 2000 Enterprise Edition SP3 (clustered)
Thank you in advance for any feedback you might be able to provide.
KS
View 4 Replies
View Related
Oct 22, 2007
---------------------------------------------------------------
My Original Post
I have to query n table(NLRImports) using the Distinct keyword, to retrieve a set of ID numbers. ( "Select DISTINCT id_nbr from NLRImport" ).
Now i want to use those values i retrieved, to process the records in the table(NLRImports) 1 by 1. How do i use those ID no's i retrieved as Variables or parameters for my next query?? If this makes sense?
----------------------------------------------------------------
First, thanks for the response.... now here is what im trying to do.
I created a simple application in delphi to import information to a table in MSSql2005. This is some of the resulting columns...
date | id_nbr | account_nbr | sub_account_nbr | ... etc
-------------------------------------------------------------
Now there will be several entries with the same id no but on different dates, so i take it dates would rather be my pkey.
Then i need to take one person's entries(i work on id_nbr) and go thru all the entries taking the earliest date and comparing all the other entries for that person to the first date and select all the dates more than 19 days after the first date and less than 91 days from first date and place it in a new table.
I used cursor s and while loops to kind of get it going but i know that cursors are not really recommended use but the performance implications dont bother with this particular job.
What other ways should i be using to accomplish this?
thanks, i hope this is clear...
View 1 Replies
View Related
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Jan 29, 2008
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
View 7 Replies
View Related
Mar 11, 2008
Hi,
I just want to know how do you called when you are accessing another pc thru \. I forgot how it is called.
Second, I like to know what [MACH] and [INST]. They don't look like directories.
\MultforestHEALTH[MACH][INST]HEALTH_WAREHOUSEDeploy_20080228p_Vitals_FACT.sql'
View 9 Replies
View Related
Jan 16, 2004
How can I execute or access a web page from a DTS package?
Both SQL server AND website are hosted on the same server (a Dual 2.4Gz Xeon with 2Gb RAM, RAID 5 etc)
I have 2 tables in SQL server 2000 that hold orders. These need to be posted into another table at a predefined time (ie: 4:30pm) and at the same time, access a remote address (a web service) and post certain elements of the order back.
Basically, can anyone help me out on how to execute a web page from a DTS.
I do NOT want to access a DTS from a webpage, which is all I'm finding at the moment.
View 5 Replies
View Related
Oct 17, 2001
Hello,
Question is, when I try to create relationships in SQL 7 without using the wizard..upon adding the tables I get an errror message that says "Coinitialization has not been called". What does this mean?
View 1 Replies
View Related
Jan 24, 2007
CREATE PROCEDURE sp_getT
@m1 int ,
@txn int ,
@Pan varchar(50) ,
@Act varchar(50) OUTPUT,
@Bal Decimal(19,4) OUTPUT,
@CBal Decimal(19,4) OUTPUT
AS
declare @pBal money, @pCbal money, @pAct money
SET NOCOUNT ON
IF @m1 = 200
BEGIN
IF @txn = 31
BEGIN
exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
END
END
SET @Act = @pAct
SET @Bal = cast(@pBal as Decimal(19,4))
SET @CBal = cast(@pCBal as Decimal(19,4))
return @Act
return @Bal
return @CBal
the above code returns this error message
"Server: Msg 8144, Level 16, State 2, Procedure CheckBalance, Line 0
Procedure or function ChkBal has too many arguments specified."
How do i specify all the arguments i want in the called procedure?
View 14 Replies
View Related
Jul 31, 2007
Hi everyone -
I'm stumped on what to cal this, there might even be
a method or pattern named for what i am trying to accomplish...
In the database, a number field is included on each table
When the DAL reads the record from the database, it is passed to
the client - work is possibly done to the record and is sent
back to the DAL for update.
A query is done against the table to retrieve the record again,
the numbers are compared - if they don't match, it is assumed the record
been modified by another user/thread/activity. An error is returned to the client stating the data has been changed.
if the numbers match, the record is updated with the number field being incremented by one.
what is this methodology called (beside crap :-) )
thanks
tony
View 7 Replies
View Related
Mar 30, 2006
Hello, perhaps you guys have heard this before in the past, but here iswhat I'm looking for.I have a SQL 2000 table with Social security numbers. We need tocreate a Member ID using the Member's real SSN but since we are notallowed to use the exact SSN, we need to add 1 to each number in theSSN. That way, the new SSN would be the new Member ID.For example:if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.Sounds simply enough, but I can't seem to get it straight.I need this number to be created using a query, as this query is areport's record source.Again, any help would be appreciated it.
View 9 Replies
View Related
Jan 19, 2007
Hi
I have a drop down list which gets populated from database but I want to add the default value as
--Select----
View 4 Replies
View Related
Apr 22, 2008
This seems like a basic question but I have not been able to find the answer in the help files or by searching this forum.
Is a trigger called for each row updated or is it called once for all rows updated?
for example if I have:
Code Snippet
CREATE TRIGGER mytrigger
ON mytable
AFTER UPDATE
AS
BEGIN
EXEC e-mail-me inserted, N'mytrigger', getdate()
END
and I do this
Code Snippet
UPDATE mytable
SET mycolumn = N'whatever'
WHERE ID > 5 AND ID <= 10
Assuming there is a record for each nteger value of ID, than will mytrigger run 5 times (once for each row updated) or one time (with inserted containing all 5 rows)?
View 3 Replies
View Related
May 6, 2008
Hi,
When a column is evaluated against an UDF in a SELECT ... or WHERE ... It makes sense that the UDF is called for every row of the SELECT. But is it still true if the UDF is called in a subquery as below?
Code Snippet
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
I've made a test and the SQL Profiler Trace shows that the UDF is called only once.
Can anyone confirm if my test is valid and most importantly that the UDF is called only once? FYI, I never use sub queries. This is to clarify a technical detail for our performance investigation.
Thank in advance for any help.
Here is the code to setup the test:
USE NorthWind
GO
CREATE TABLE dbo.UdfTest (
LineID int Identity(1,1) NOT NULL,
AnyText varchar(200) COLLATE database_default NOT NULL
)
GO
INSERT dbo.UdfTest (AnyText) VALUES ('Test1')
INSERT dbo.UdfTest (AnyText) VALUES ('Test2')
INSERT dbo.UdfTest (AnyText) VALUES ('Test3')
GO
CREATE FUNCTION dbo.F_Bogus (
)
RETURNS @tab TABLE (
LineID int NOT NULL,
AnyText varchar(100) COLLATE database_default NOT NULL)
AS
BEGIN
INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1')
INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2')
INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3')
INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4')
INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4')
RETURN
END
GO
Here is the capture of SQL Profiler when executing the statement:
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
SQL:BatchStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQLtmtStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SPtarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus RETURN 51 2008-05-06 17:58:31.577
SQLtmtCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQL:BatchCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
View 5 Replies
View Related
Aug 20, 2007
My package inserts rows into tables with triggers.
The triggers are not being called.
What might be causing this?
View 1 Replies
View Related
Oct 12, 2005
Hello everyone:
I have some nightly jobs that execute stored procedure to call the tables? I want to know which table are called by these stored procedures. Is it possible? Any idea will be appreciated.
Thanks
ZYT
View 5 Replies
View Related
Oct 13, 2007
I am encountering an issue which is effecting our production environment, none of our sql jobs are now saving any kind of job history, nor are the status of the jobs being saved.
I have run a profiler trace and it seems as if procedure : sp_sqlagent_log_jobhistory is not being called during the execution of any of our jobs
Has anyone else encountered anything similar?
View 3 Replies
View Related
May 3, 2006
Hi
In a stored procedure the following code snippet 1 checks against duplicate data being inserted. I've tested it with snippet 2 and it works as expected. However, when the procedure is called from ASP.NET the check seems ineffective. I still get the error msg. The application uses a SqlDataSource with the following parameters.
Any suggestions?
Thanks,
Bakis.
PS I want to ask a question on the ASP.NET forum .The login/pwd for this forum "get me in" to the .net forum in the sense that when I log in I see a logout link. I don't get an "ask a question" button though. Is there a separate screening for each forum?
<UpdateParameters>
<asp:Parameter Name="CatItemUID" Type="Int32" />
<asp:Parameter Name="CatName" Type="String" />
<asp:Parameter Name="Item" Type="String" />
<asp:Parameter Name="Quad" Type="Int16" />
<asp:Parameter Name="UID" Type="Int64" />
</UpdateParameters>
snippet 1 :
if (@CatItemComboExists > 0 )
BEGIN
--print @CatItemComboExists
return 0
END
snippet 2:
begin tran
declare @return_status int
EXECUTE @return_status = spUpdateCatItemRec 343, 'blah','blih', 2,3
print @return_status
rollback
error msg only if proc is called from app
Violation of UNIQUE KEY constraint 'IX_tblCatItemUID'. Cannot insert duplicate key in object 'tblCatItemUID'.
View 3 Replies
View Related
Oct 12, 2006
I am maintaining some C# and ASP.NET code with SQL server 2000. My code calls a stored procedure. The code is a little confusing as to the name of the SQL Server stored procedure that is calling. At this point I don't know how to trace into or debug the stored procedure. Kind of hard to do in the first place when you are not absolutely certain as to the stored proc to be called.I can take an educated guess as to which stored procedure is being called. I figure if I can deliberately make a certain stored procedure fail then this might be able to somehow give me the name of the stored proc that I am calling.So is there a way to do this. Namely make a stored procedure fail, or to return the name of the stored Proc?I would sincerely appreciate some help with this problem.
View 1 Replies
View Related
Feb 23, 2007
Im using a SqlDataSource control. Ive got my "selectcommand" set to the procedure name, the "selectcommandtype" set to "storedprocedure"What am i doing wrong ? Ive got a Sql 2005 trace window open and NO sql statements are coming through "ds" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="my_proc_name" SelectCommandType="StoredProcedure">
"txtF1" Name="param1" Type="String" />
"txtF2" Name="param2" Type="String" />
"" FormField="txtF3" Name="param3" Type="String" />
"" FormField="txtF4" Name="param4" Type="String" />
View 2 Replies
View Related
Sep 12, 2005
Hi All,I have a report ASP.NET page that allow users to run a report by clicking a buttion to call a store procedure to generate the report, however, the store procedure is taking a few minutes to return the data, thus I got the 'timeout' error message on my page. How do I extend the time on my page?Thanks
View 3 Replies
View Related
Dec 11, 2001
Can someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:
sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2
sp_calc_values receives @var1, @var2 then calculates @var3, @var4
HOW does sp_caller receive the calculated values @var3, @var4 ???
thx in advance
View 2 Replies
View Related
Feb 2, 1999
Hi,
Please help me to elucidate a mistery !
I have a little SP in a database:
CREATE PROCEDURE XXXTest AS
DECLARE @a varchar(50)
SELECT @a = NULL
IF @a NOT IN ('x', 'y')
BEGIN
RAISERROR ('Error',16,1)
RETURN 0
END
RETURN 1
If I execute it from SQL Server, it returns 0. (after the error, of course).
If I execute it from MS ACCess through a Pass Through Query, it returns 0.
On the same server !!!
How could it to make me that ? I have been thought the SP is executed entirely on the server !!!
Thanks in advance clearing me the mind.
Mircea.
View 2 Replies
View Related
