I have a stored procedure being called from Visual Cafe 4.0 that takes over 30 minutes to run. Is there any way to backround this so that control returns to the browser that the JFC Applet is running in? The result set is saved to local disk and an email message sent to the user on completion.
Thanks, Dave.
I have a stored procedure in SQL 2005 that purges data, and may take a few minutes to run. I'd like to report back to the client with status messages as the sp executes, using PRINT statements or something similar. I imagine something similar to BACKUP DATABASE, where it reports on percentage complete as the backup is executing.
I can't seem to find any information on how to do this. All posts on this subject state that it's not possible; that PRINT data is returned after the procedure executes. However it would seem possible since BACKUP DATABASE, for example, does this.
Is there any way to send status type messages to the client as the sp is executing??
Thanks.
I have a vendor database that has a stored procedure that runs a long time.Eventually, the database runs out of log space.
Setting the database to FULL and doing frequent log backups does not work.
The log does not get truncated during this log backups.
The stored procedure in question has SET XACT_ABORT ON statement at the beginning.
How does one prevent a long running procedure form crapping out in CLR?
I am trying to do a pull from a distant data source and it works, except I have to break down my stored procedure call into several smaller calls. I would like to do everything in one shot, but I get the thread abort exception when I try to get a lot of data.
Any ideas?
Thanks.
When I execute a long running procedure, I get timeout errors when other users try to execute other procedures with UPDATE or INSERT statements.
I suspect that the other procedures are trying to execute DML statements on tables that are locked by the long running procedure.
I have a sharred trigger on all my tables that creates and updates records in tables AuditLogDetails and AuditLogParent for keeping a log of modifications. I suspect that tables AuditoLogDetails and AuditLogParent are locked by the long running procedure.
How can I change the LOCKING behavior of the long running procedure to fix the time out errors that I get?
The long running procedure is displayed below.
ALTER PROCEDURE [dbo].[spPostPresenceToHistory2]
@PostDate DateTime,
@Department Int,
@Division Int,
@Testing Bit = 0,
@XDoc xml OUTPUT,
@XDoc2 xml OUTPUT,
@ModifierID varchar(20),
@Comment varchar(200)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @PostCount Int,@PreCount Int,@DiffCount Int
IF @Testing=1
BEGIN
PRINT 'DELETE FROM History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
IF EXISTS(SELECT E.ID FROM History2_Personel E WHERE E.PostDate=@PostDate)
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN History2_Personel Ps ON H.Personel_ID=Ps.ID AND Ps.PostDate=@PostDate
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
ELSE
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN Personel As Ps ON H.Personel_ID=Ps.ID
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="History2_Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
PRINT 'INSERT INTO History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
INSERT INTO [dbo].[History2_Presence]
([ID]
,[User_ID]
,[Personel_ID]
,[Date_de_Presence]
,[Category_Motif_ID]
,[DateEntre]
,[Category_TypeDePresence_ID]
,[Travaille_de_Jour]
,[Heur_Supplementaire_Travaille]
,[prime_transport]
,[Tarif]
,[Jour_Travaille]
,[Montant_Supplementaire_Par_Heur]
,[Salair_par_Jour]
,[Salair_Minimum]
,[IsAutomaticRec])
SELECT [P].[ID]
,[P].[User_ID]
,[P].[Personel_ID]
,[P].[Date_de_Presence]
,[P].[Category_Motif_ID]
,[P].[DateEntre]
,[P].[Category_TypeDePresence_ID]
,[P].[Travaille_de_Jour]
,[P].[Heur_Supplementaire_Travaille]
,[P].[prime_transport]
,[P].[Tarif]
,[P].[Jour_Travaille]
,[P].[Montant_Supplementaire_Par_Heur]
,[P].[Salair_par_Jour]
,[P].[Salair_Minimum]
,[P].[IsAutomaticRec]
FROM [dbo].[Presence] AS P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND NOT EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PostCount-@PreCount
SET @xdoc.modify('
insert <Table Name="History2_Presence" RecordAdded="{ sql:variable("@DiffCount") }"/> as last into /Inserted_Records[1]
')
END
IF @Testing=0
BEGIN
PRINT 'DELETE FROM Presence'
EXEC sp_SetPostingProperties 'Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM Presence
DELETE FROM Presence FROM Presence P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
COMMIT TRANSACTION
END
We have just implemented our new app and I need to improve performance. We are the victims of not having a very adequate stress testing tool prior to launch. Is there an easy way to track all queries or stored procs longer than a specified time?
View 1 Replies View RelatedWe have just implemented our new app and are expericing some slowness but no blocking issues. Is there an easy way to track all queries taking over x seconds to run?
View 1 Replies View RelatedAll -
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
Thanks.
Hi There,
We have developed a application in VB and connected to SQL Server 6.5, we have some stored procedures where it brings the data from SQL Server 6.5, this application is running since some months, when we run this application it usually take only one minute to generate the report but since couple of days it is taking 25 Minutes to generate the report, even when I run that stored procedure at backend in Query analyzer at Server it is taking 15-20 Minutes to give the result.
please can any one help in identifying the problem, What all the things I need to check to identify it.
Give me the solution.
Thanks in Advance,
Bye,
Madhuker.
I think I may need help with query optimization.
We have a customer table and an address table. Each customer can have 1 or more addresses in the address table.
The task is to synchronize customer information from 2 entirely separate systems. I have a stored procedure that reads a text file (exported from the 'master' system) and updates all the customer records in the second system (which is in the sql server db). The queries themselves work. The data is accurate. My issue is with how long it takes the stored procedure to run. With over 11,000 records in the text file, the stored procedure sometimes takes over 3 hours to run.
That seems excessive to me. (and probably to those of you snickering in the back)
As an overview: my stored procedure does the following.
1) creates a temp table
2) dumps the data from the text file into the temp table
3) updates the address table with data from the temp table (records already there)
4) inserts records into the address table from the temp table (records not already there)
5) updates the customer table with an address id
6) drops the temp table
Any help/suggestions is appreciated. If you need more info, let me know.
I have a stored procedure that is taking too long to complete. I'ved narrowed the problem down to the following code. Is there any reason why this should take long to complete for about 40,000 records?
UPDATE Entries
SET EntryStatus = 1, reason = 'code entered more than once'
WHERE TimeSubmitted > @StartOfPeriod AND TimeSubmitted < @EndOfPeriod
AND TimeSubmitted <> @t and ShortCode = @ShortCode
AND verbatim = @v
help-take a long time run this stored procedure 15 second
Code Snippet
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,@NumDays TINYINT,@myNum TINYINT
set @myNum=4
SELECT @WantedDate = CAST(CAST(YEAR(GETDATE()) AS nvarchar)+ '-' +CAST(1 AS nvarchar)+ '-' +CAST(@myNum AS nvarchar) AS SMALLDATETIME), -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
select @WantedDate
IF @NumDays = 28
BEGIN
SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,
CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis
FROM dbo.v_hasot_all INNER JOIN
dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int
WHERE (NOT (dbo.v_hasot_all.nameday IN (29, 30, 31)))
END
ELSE IF @Numdays = 29
BEGIN
SELECT ...............
WHERE (NOT (dbo.v_hasot_all.nameday IN ( 30, 31)))
END
ELSE IF @Numdays = 30
BEGIN
SELECT ......................
WHERE (NOT (dbo.v_hasot_all.nameday IN (31)))
END
ELSE IF @Numdays = 31
BEGIN
SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,
CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis
FROM dbo.v_hasot_all INNER JOIN
dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int
END
and whan i run my code separately it run fast
i think it is the ELSE IF @Numdays
TNX
I'm an experienced SQL Server and .NET developer, but I wanted to expand the way I look at things and see how other developers approach the situation I'm going to outline in this post. I'm going to be engineering a large, new project soon and I want to examine how I approach this and see if there is a better way.
I work in a small development group with two developers (myself and another). We pretty much wear all the design, testing ,and development hats during the course of a system's development. I had a discussion today with the other developer about creation of stored procedures.
I like to create small specific stored procedures for whatever I'm doing. I will usually have at least 4 stored procedures for each table; Insert, Delete, Update, and Select. Frequently I'll have more Select procedures for special cases. I do this for several reason. One I can get Visual Studio to generate the basic procedures for me and utilize them in a typed dataset. Secondly I can keep all my SQL code server side, and in small maintainable chunks. It is also fairly obvious what my stored procedures do from the name. The main drawback is that the list of stored procedures gets huge.
The developer I work with likes to create a single stored procedure for Insert, Update, and Deletes. Based on the passed primary key, the procedure determines what it should do. For example:
Code Snippet
CREATE PROCEDURE udp_users_processing
@key int output,
@name varchar(200),
@status int
AS
IF IsNull(@key,0)=0
BEGIN
INSERT INTO ut_users(key, name, status) VALUES (@key, @name, @status)
SET @key = SCOPE_IDENTITY()
END
ELSE
IF KEY > 0
UPDATE ut_users SET key = @key, name = @name, status = @status
ELSE
BEGIN
DELETE FROM ut_users WHERE key = @key
END
This has the advantage of being compact, but it has issues with VS.NET and designer support. Loss of designer support isn't a huge problem, but it can be handy to have. I'm also not certain how this approach would work when using typed dataset and the table adapter to do updates.
What is YOUR opinion? How would YOU approach this in your situations? Are there other alternatives that might work just as well?
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
Hi
I am trying to upload an excel file into a sql server database. I uploading the spreadsheet from an asp.net page and then running the dts froma stored procedure. But it doesn't work, I am totally lost on what I am doing wrong.
Any help would be greatly appreciated.
Asp.net code;
Dim oCmd As SqlCommand
oCmd = New SqlCommand("exportData", rtConn)
oCmd.CommandType = CommandType.StoredProcedure
rtConn.Open()
With oCmd
.CommandType = CommandType.StoredProcedure
Response.write("CommandType.StoredProcedure")
End With
Try
oCmd.ExecuteNonQuery()
Response.write("ExecuteNonQuery")
Finally
rtConn.Close()
End Try
StoredProcedure;
CREATE PROCEDURE exportData AS
Exec master..xp_cmdshell
'DTSRUN /local/DTS_ExamResults'
GO
Thanks
Rachel
I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.
How do i call a DB2 stored procedure?
Hi Guys & Gals
I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:
ALTER PROC sp_get_allowed_growers
@GrowerList varchar(500)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT nu_code, nu_description, nu_master
FROM nursery WHERE nu_master IN (' + @GrowerList + ') ORDER BY nu_code ASC'
EXEC(@SQL)
END
GO
and the code I'm using to execute the procedure is this:
public DataSet GetGrowers(string Username)
{
System.Text.StringBuilder UserRoles = new System.Text.StringBuilder();
UsersDB ps = new UsersDB();
SqlDataReader dr = ps.GetRolesByUser(Username);
while(dr.Read())
{
UserRoles.Append(dr["RoleName"]+",");
}
UserRoles.Remove(UserRoles.Length-1,1);
//Create instance of Connection and Command objects
SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]);
SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection);
//Create and fill the DataSet
SqlParameter paramList = new SqlParameter("@GrowerList",SqlDbType.VarChar);
paramList.Value = UserRoles.ToString();
transloadCommand.SelectCommand.Parameters.Add(paramList);
DataSet dsGrowers = new DataSet();
transloadCommand.Fill(dsGrowers);
return dsGrowers;
}
The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:
Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Anyone with any ideas would be very helpful...
I am calling a SQL Server 6.5 Stored Procedure from Access 2000 with the following code :-
Public Function CheckDigitCalc()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
On Error GoTo TryAgain
conn.Open "DSN=WEB;uid=sa;pwd=;DATABASE=WEB;"
Set cmd.ActiveConnection = conn
cmd.CommandText = "SPtest2"
cmd.CommandType = adCmdStoredProc
cmd.Execute
MsgBox "Numbers created OK.", vbOKOnly
Exit Function
TryAgain:
MsgBox "Error occurred, see details below :-" & vbCrLf & vbCrLf & _
Err & vbCrLf & vbCrLf & _
Error & vbCrLf & vbCrLf, 48, "Error"
End Function
The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.
hi,
i'm using SQL server 2000. i'm getting the below error when i run a store procedure.
"Specified column precision 500 is greater than the maximum precision of 38."
I have created a temporary table inside the stored procedure inserting the values by selecting the fields from other table. mostly i have given the column type as varchar(50) and some fields are numeric(50).
How can I find out if a stored procedure is currently being executed?
sp_who2 and sys.sysprocesses, Command, Cmd fields just gives me parts of the sql inside the stored procedure.
We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks
View 22 Replies View RelatedHi,
I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.
I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)
I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.
Here is my SP
ALTER PROCEDURE uspSelectBarItemID2
(
@BarTabID INT,
@DrinkID INT,
@ReturnBarItemID INT OUTPUT
)
AS
BEGIN
SELECT @ReturnBarItemID = barItemID
FROM [Bar Items]
WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)
END
In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.
What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.
Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.
Cheers.
HI all, I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks.
View 6 Replies View RelatedI’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…
Hi,
I'm running a CLR stored procedure through my web using table adapters as follows:
res = BLL.contractRateAdviceAdapter.AutoGenCRA() 'with BLL being the business logic layer that hooks into the DAL containing the table adapters.
The AutoGen stored procedure runs fine when executed directly from within Management Studio, but times out after 30 seconds when run from my application. It's quite a complex stored procedure and will often take longer than 30 seconds to complete.
The stored procedure contains a number of queries and updates which all run as a single transaction. The transaction is defined as follows:
----------------------------------------------------------------------------------------------------------------------
options.IsolationLevel = Transactions.IsolationLevel.ReadUncommittedoptions.Timeout = New TimeSpan(1, 0, 0)
Using scope As New TransactionScope(TransactionScopeOption.Required, options)
'Once we've opened this connection, we need to pass it through to just about every
'function so it can be used throughout. Opening and closing the same connection doesn't seem to work
'within a single transactionUsing conn As New SqlConnection("Context Connection=true")
conn.Open()
ProcessEffectedCRAs(dtTableInfo, arDateList, conn)
scope.Complete()
End Using
End Using
----------------------------------------------------------------------------------------------------------------------
As I said, the code encompassed within this transaction performs a number of database table operations, using the one connection. Each of these operations uses it's own instance of SQLCommand. For example:
----------------------------------------------------------------------------------------------------------------------Dim dt As DataTable
Dim strSQL As StringDim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 0Dim rdr As SqlDataReaderstrSQL = "SELECT * FROM " & Table
cmd.CommandText = strSQL
rdr = cmd.ExecuteReader
SqlContext.Pipe.Send(rdr)
rdr.Close()
----------------------------------------------------------------------------------------------------------------------
Each instance of SQLCommand throughout the stored procedure specifies cmd.CommandTimeout = 0, which is supposed to be endless. And the fact that the stored procedure is successful when run directly from Management studio indicates to me that the stored procedure itself is fine. I also know from output messages that there is no issues with the database connection.
I've set the ASP.Net configuration properties in IIS accordingly.
Are there any other settings that I need to change?
Can I set a timeout property when I'm calling the stored procedure in the first place?
Any advice would be appreciated.
Thanks
I am new to ASP.NET so please excuse what may seem like a dumb question.
I have a stored procedure that I need to run when the user clicks on our submit button. I am using Visual Studio 2005 and thought I could use the SqlDataSOurce Control. IS it possible to us the control or do I need to create a connection and call the stored procedure in the the button_click sub?
Thanks in advance
MF
Hi all, I wonder if you can help me with this. Basically, Visual Web Developer doesn't like this part of my code despite the fact that the stored procedure has been created in MS SQL. It just won't accept that bold line in the code below and even when I comment it just to cheat, it still gives me an error about the Stored Procedure. Here's the line of code: // Define data objects SqlConnection conn; SqlCommand comm; // Initialize connection string connectionString = ConfigurationManager.ConnectionStrings[ "pay"].ConnectionString; // Initialize connection conn = new SqlConnection(connectionString); // Create command comm = new SqlCommand("UpdatePaymentDetails", conn); //comm.CommandType = CommandType.StoredProcedure; // Add command parameters comm.Parameters.Add("PaymentID", System.Data.SqlDbType.Int); comm.Parameters["PaymentID"].Value = paymentID; comm.Parameters.Add("NewPayment", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewPayment"].Value = newPayment; comm.Parameters.Add("NewInvoice", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewInvoice"].Value = newInvoice; comm.Parameters.Add("NewAmount", System.Data.SqlDbType.Money); comm.Parameters["NewAmount"].Value = newAmount; comm.Parameters.Add("NewMargin", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewMargin"].Value = newMargin; comm.Parameters.Add("NewProfit", System.Data.SqlDbType.Money); comm.Parameters["NewProfit"].Value = newProfit; comm.Parameters.Add("NewEditDate", System.Data.SqlDbType.DateTime); comm.Parameters["NewEditDate"].Value = newEditDate; comm.Parameters.Add("NewQStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewQStatus"].Value = newQStatus; comm.Parameters.Add("NewStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewStatus"].Value = newStatus; // Enclose database code in Try-Catch-Finally try { conn.Open(); comm.ExecuteNonQuery(); }
View 7 Replies View RelatedI have a table with information about a mobile account in one table, a table for mobile plans, and a table for planfeatures. Each mobile account is associated with a planid, and may be associated with any combination of the features associated with that plan. The plan features are stored in a bridgetable which contains 'invoicedate' (date stamped on the invoice in question), 'subaccountnumber' (the cell phone number), 'planid', and 'featureid'. I've tested the UpdateMobileFeature sp directly from the SQL Profiler--it works fine on it's own. I use a stored procedure to fill the mobile table (called from aspx page), and since I use three of the four columns written above for both the mobilesub and the mobilefeature tables, I tried just adding the parameter which holds the featureid's to the sp to update the mobilesub table. Then I call the UpdateMobileFeature sp from the updatemobile sp. (The code in mobilesub that is not calling UpdateMobileDetail works well). All seems to work fine--nothing crashes or anything, but nothing is being added to the mobilefeature table. Here is the code:CREATE procedure usp_updatemobile( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanId int, @FeatureList varchar(500) --added for UpdateMobileFeatures. In the form of a comma-seperated list, to imitate an array. --***irrelevant parameters removed***--)
as
exec dbo.UpdateMobileFeature '@InvoiceDate','@SubaccountNumber','@PlanId','@FeatureList' --the apparently nonfunctional call
if exists ( //select for the mobile row in question ) Begin //update row
End
Else Begin //insert new row End
GOCREATE PROC dbo.UpdateMobileFeatures( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanID int, @FeatureList varchar(500))ASBEGIN SET NOCOUNT ON
CREATE TABLE #TempList ( InvoiceDate smalldatetime, SubaccountNumber varchar(50), PlanID int, FeatureID int )
DECLARE @FeatureID varchar(10), @Pos int SET @FeatureList = LTRIM(RTRIM(@FeatureList))+ ',' SET @Pos = CHARINDEX(',', @FeatureList, 1)
IF REPLACE(@FeatureList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @FeatureID = LTRIM(RTRIM(LEFT(@FeatureList, @Pos - 1))) IF @FeatureID <> '' BEGIN INSERT INTO #TempList (InvoiceDate, SubaccountNumber, PlanID, FeatureID) VALUES (@InvoiceDate, @SubaccountNumber, @PlanID, CAST(@FeatureID AS int)) --Use Appropriate conversion END SET @FeatureList = RIGHT(@FeatureList, LEN(@FeatureList) - @Pos) SET @Pos = CHARINDEX(',', @FeatureList, 1)
END END
--SELECT o.FeatureID, CustomerID, EmployeeID, FeatureDate --FROM dbo.Features AS o -- JOIN -- #TempList t -- ON o.FeatureID = t.FeatureID
Insert Into MobileFeatures Select InvoiceDate, SubaccountNumber, PlanID, FeatureID From #TempList ENDGOHere is the method I used to call the first sp: (also with irrelevant stuff removed)public void saveCurrentMobile() {
calculateTotals();
InvoiceDataSet dataset = InvoiceDataSet.GetInstance();
SqlConnection conn = (SqlConnection)Session["connection"];
SqlCommand cmdUpdateMobile;
cmdUpdateMobile = new SqlCommand("usp_updatemobile", conn); cmdUpdateMobile.CommandType = CommandType.StoredProcedure;
SqlParameter invoicedate = cmdUpdateMobile.Parameters.Add("@InvoiceDate", SqlDbType.SmallDateTime); invoicedate.Value = DateTime.Parse(Request.Params["InvoiceDate"].ToString());
SqlParameter cellnumber = cmdUpdateMobile.Parameters.Add("@SubaccountNumber", SqlDbType.VarChar, 50); cellnumber.Value = txtCellNumber.Text.Trim();
SqlParameter plan = cmdUpdateMobile.Parameters.Add("@PlanId", SqlDbType.Int); plan.Value = planid;
SqlParameter featurelist = cmdUpdateMobile.Parameters.Add("@FeatureList", SqlDbType.VarChar, 500); featurelist.Value = planform.FeatureList;
//Response.Write(isthirdparty.ToString()); Response.Write(thirdpartycompany);
SqlParameter cycle = cmdUpdateMobile.Parameters.Add("@Cycle", SqlDbType.Int); cycle.Value = Convert.ToInt32(Request.Params["Cycle"]);
int returnvalue = runStoredProcedure(cmdUpdateMobile); //the sp is called within this method.
if (returnvalue != 0) { dataset.fillMobileTable(); Response.Write("Mobile Subaccount Saved!"); } }
I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.
Can someone give me some insite why this stored procedure won't run as a scheduled job?
ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)
Thanks in advance-
Hi
Is it possible in my DTS Package to check if a stored procedure which I'm executing from the Execute sql task icon can be tested for failure?
Is there a way to run/call a batch file from a stored procedure?
Or, is there a way to run/call a batch file from a trigger?
Hey guys!
I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck.
I've created a hierarchal listbox form that drills down From
Product - Colour - Year.
based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes.
So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER.
I want that product number to be used to populate the grid view. Is there away for me to do this?
Thanks in advanced everybody!
HI ALL
I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.
How can I add my stored procedure to the SQL server agent jobs??
Any Idea..