From The Sql Profiler Constantly Displays Exec Sp_executesql N'BEGIN CONVERSATION TIMER ... It That Ok?
Mar 8, 2007
Hello,
I'm using SQL service Broker 2005 with ASP.NET 2.0 in order to use the sql cache dependency.
everything works fine...
I have only a doubt regarding a query that is constantly executed on mu db ( i can see it be means of the SQL Profiler)
The query is:
exec sp_executesql N'BEGIN CONVERSATION TIMER ("'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle,
cast(message_body AS XML) as message_body from [SqlQueryNotificationService-GUID]), TIMEOUT @p2;',N'@p2 int',@p2=60000
The web application is not running from a browser ...
It this ok or I forget to clean/reset something from my web application and/or sql server?
Thank you
Marina B.
View 3 Replies
ADVERTISEMENT
Apr 27, 2006
I'm using conversation timers successfully to fire events at a predetermined time in the future, but I'm running into issues when using an interval of considerable size. I set the conversation timeout like so:
set @Timeout = DATEDIFF(SECOND, GETDATE(), DATEADD(MINUTE, -(@TimeOffset), @FollowUpDateTime));
if (@Timeout < 0)
set @Timeout = 1;
// begin dialog
begin conversation timer (@FollowUpConversation)
timeout = @Timeout;
In this case @Timeout was 94693494.
In the SQL error log I see the following error: "Invalid subcommand value 94693494. Legal range from 1 to 2147483."
I thought I may check the @Timeout value and simply set it to 2147483 if it is larger than 2147483, but I was wondering if there was a reason the upper limit was so small.
Thanks,
Chris
View 10 Replies
View Related
Apr 24, 2006
I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:
"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."
I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?
Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?
Thanks,
Chris
View 5 Replies
View Related
Nov 17, 2006
I need to follow up on a message and check on its status. I am planning on using Conversation Timers (self addressed). I've tried it and they do work well. I am wondering if the LIFETIME parameter can be used for the same purpose. If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue? It does not seem that this is the case, but it is worth checking, as it could be a much desired feature.
Thanks,
View 5 Replies
View Related
Apr 30, 2007
Hi,
I am using conversation Timer for delaying a message for a few seconds but I can see the message immediately in the queue.
Here is the code i am using. This is a part of a stored procedure I have used.
BEGIN CONVERSATION TIMER ( @h ) TIMEOUT = @DelayBySeconds;
SEND ON CONVERSATION @h
MESSAGE TYPE [sendmsg]
(@msg);
I am executing this stored procedure with following statements.
exec set_ssb_msg 'test3', 25;
exec set_ssb_msg 'test1', 1;
select * from q1
I was hoping to see just the 'Test1' and see test3 after 25 seconds. But I could see both the messages in a queue as soon as i run the stored proc.
If I execute a receive command on the queue, I am receiving 'test3' first and then 'test1'. This is exactly opposit of what i expected.
Can you please let me know if I am doing anything wrong or missing a step.
Any help is greatly appreciated.
Thanks,
Don.
View 1 Replies
View Related
Jul 23, 2005
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies
View Related
Oct 12, 2007
Hi,
I already had a thread for same problem but didn't recieved any response in last 4-5 months so I created a new thread for this
I am using service broker in between two database servers. following is the way i am sending and receiving messages
Send
BEGIN TRAN
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @SendService
TO SERVICE @ReceiveService
ON CONTRACT @Contract
WITH LIFETIME = @lifetime;
SEND ON CONVERSATION @handle
MESSAGE TYPE @xmlMessageType(@xmlMessage);
COMMIT
Receive
BEGIN TRAN;
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
----------------------------------------------------------------------------------------------------
-- Check to make sure a message was returned to process. In theory this should never happen.
----------------------------------------------------------------------------------------------------
IF @@rowcount = 1
BEGIN
IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @handle;
COMMIT
RETURN 0
END
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @handle;
COMMIT
RETURN 0
END
SET @sql = 'EXEC '+@message_processor_name+' @xml'
BEGIN TRAN
EXEC sp_executesql @sql, N'@xml XML', @xml=@xmlMessage
COMMIT TRAN
END CONVERSATION @handle;
END
COMMIT
I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.
1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.
2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.
Let me know if you need any other information
View 2 Replies
View Related
May 23, 2015
I have the SQL code for one of the select list columns as shown below in the SSRS Data set query level.
Example : Select IsNUll(max(ET.[# Utilizations by Utilization Method]),0) as [Employee Period Total]
SQL Result Set Output :
0
0
0
The above code when run in SQL displays correctly with zeroes if NULL values are available from the Source tables.Now at the SSRS Report level design I have grouped with more than one values.Even at the expression I have given =Fields!Employee_YTD_Total.Value but report output displays blank In order to evaluate 0 from the SQL result set I even tried equating the values to 0 like = Iif(Fields!Employee_YTD_Total.Value = 0 ,0,Fields!Employee_YTD_Total.Value) but still the report outputs is blank.
Issue/Query : Why it displays blank and I should have this as 0 in the report output to eliminate blank as this would be not appropriate to the end users to validate. What has to be changed either at the SSRS level or at the SQL level.
View 3 Replies
View Related
Dec 7, 2007
Hi, I just had a quick question about this code and if I know what it is doing: Is it putting what is in variable @x18 into @Lost_Alumni?
Declare @Lost_Alumin datetime
Set @ParmDef_Ls = '@x18 DateTime OUTPUT, '
EXEC sp_executeSQL @ParmDef_Ls,
@x18 = @Lost_Alumni OUTPUT,
thanks,
View 5 Replies
View Related
Sep 11, 2001
I need to do some dynamic execs based on SQL strings as variables taken from a table.
I need the best performance. In testing, I'm finding that the string I am executing is taking 20 times as long to run from within an Exec or sp_executesql vs running the same string from directly within query analyzer. Of course, there's some overehead with Exec, and we're talking milliseconds (30ms in query analyzer vs 600 in an Exec), but this still seems like a lot of overhead. I assume both statements still need to be compiled and an execution plan created. Is this difference in time normal?
I ask because I'm debating about whether to execute this process (which may need to run 9000 times a second) in a stored procedure or using an executable running separately on the server. I assume an executable's performance will approximate running the same query from query analyzer.
Here's the query:
Select GroupNodeNumber, SB.* From (Select Distinct GroupNodeNumber from #QASData) RD Cross Join (SELECT * From SQL_QAS_ServiceFailure WHERE QASEventID=99077223) SB Where (GroupNodeNumber=10 and (((VerbalPOD=-1) or (Canceled=-1) or ((DeliveryRequirement) = -1) or
(DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) or (InvoiceType<>0)))) or
(GroupNodeNumber=20 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=30 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>120) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=240) and (VerbalPOD=0) and (InvoiceType=0))) or
(GroupNodeNumber=40 and ((Canceled=0) and (InvoiceType=0) and (VerbalPOD=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=120))) or
(GroupNodeNumber=50 and ((ShipmentStatus='POD') and (VerbalPOD=0) and (InvoiceType=0)))
View 1 Replies
View Related
May 1, 2008
Hi everybody,
I have this problem I have been struggling with for more than an hour now, hoping for some help.
Using SQL Server 2005.
DECLARE @IPAddress nvarchar(15)
SET @IPAddress = '127.0.0.1'
DECLARE @UA nvarchar(300)
SET @UA = 'MSIE'
DECLARE @UserID int
SET @UserID = 123
EXEC sp_executesql N'User_LogonLog',N'@UserID int, @IPAddress nvarchar(15), @UA nvarchar(300)',@UserID,@IPAddress,@UA
It keeps given me the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'User_LogonLog'.
In my head it should be perfectly valid syntax. I have also read Mr. Sommarskog's nice guide http://www.sommarskog.se/dynamic_sql.html, but no luck either..
I have tried to remove datatypes in parameters declaration, the Unicdode N' and so... nu luck.
If I run the following (without sp_executesql) there is no problem, which indicates the User_LogonLog procedure is work perfectly fine.
EXEC User_LogonLog @UserID,@IPAddress,@UA
What am I missing?
View 6 Replies
View Related
Feb 17, 2004
I have 3 development SQL Servers A, B & C, all running SQL 2000 sp3 and Windows 2003. Servers B & C have a linked server pointing to A, and A has one pointing to B & C. The linkedservers all have RPC , RPC out enabled. I have a stored procedure called test on server A.
Create Proc test
as
Select Top 5 first_name, last_name from people
GO
--code ran on Servers B & C:
create table #tmptbl (nm varchar(100), nm2 varchar(100))
insert into #tmptbl
Exec ServerA.db1.dbo.test
When the Insert....Exec code above is ran from server B it works fine, however when I run it from Server C, I get error 7391
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]
But regular linked server calls (directly to tables) and openquery calls work fine from either server...
Eg
insert into #tmp
Select top 5 first_name, last_name from ServerA.db1.dbo.people
--and this works also
insert into #tmp
Select * from openquery(ServerA, 'Exec db1.dbo.test')
Both servers (B & C) appear to be configured the same, and
I have reconfigured MSDTC on all three boxes through control panel and component manager, have tried using SET xact_abort, SET implicit_transactions, registry hacks (TurnoffRPCsecurity), basically everything listed on Microsoft, and everything I've been able to find in these groups.
If anyone has any ideas, I'd like to hear them.
Tim.
View 3 Replies
View Related
Mar 26, 2008
Hi There
Ok i have a piece of test ddl sql that is written to a varchar(max) column. Entered by a user.
GO's and missing semi colons seem to break it, if you try to execute it with EXEC or sp_executesql, however the sql can be executed on Management Studio as is, so how can i execute it as is successfully?
In a nutshell i retreive the DDL sql from the column into a nvarchar(max) variable called @SQL , and i need to execute it.
I have tried:
EXEC(@SQL) and sp_executesql @SQL, both return the error , incorrect syntax near 'GO'.
The problem is obviously i have to have the go statements, in order to create some fo the ddl objects correctly. But EXEC and sp_executesql do not like that. I also found that semi colons are required after every statement.
The sql is as follows:
--===============================================================================
--DDL script
CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'Whatever!@(!';
CREATE USER TEST_USER FROM LOGIN TEST_LOGIN;
CREATE TABLE TEST_TABLE (Column1 int NULL);
GRANT INSERT ON TEST_TABLE TO TEST_USER;
CREATE CLUSTERED INDEX TEST_INDEX ON TEST_TABLE (Column1);
GO
CREATE PROCEDURE TEST_PROCEDURES
AS
SELECT GETDATE();
GO
CREATE VIEW TEST_VIEW
AS
SELECT * FROM TEST_TABLE;
GO
--ALTER DDL
ALTER VIEW TEST_VIEW
AS
SELECT GETDATE() AS 'DATE';
GO
ALTER PROCEDURE TEST_PROCEDURES
AS
SELECT * FROM TEST_TABLE;
GO
--DROP DDL
DROP INDEX TEST_TABLE.TEST_INDEX;
DROP TABLE TEST_TABLE;
DROP VIEW TEST_VIEW;
DROP USER TEST_USER;
DROP LOGIN TEST_LOGIN;
DROP PROCEDURE TEST_PROCEDURES;
--===============================================================================
View 5 Replies
View Related
Feb 13, 2008
Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
View 4 Replies
View Related
Dec 3, 2007
Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
View 10 Replies
View Related
Apr 19, 2006
Hi:
My service broker was working perfectly fine earlier. As I was testing...I recreated the whole service broker once again.
Now I am able to get the message at the server end from intiator. When trying to send message from my server to the intiator it gives this error in sql profiler.
broker:message undeliverable: This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'.
broker:message undeliverable This message could not be delivered because the 'receive sequenced message' action cannot be performed in the 'ERROR' state.
How do I proceed now ?
Thanks,
Pramod
View 14 Replies
View Related
Jan 18, 2008
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
View 2 Replies
View Related
Jun 7, 2008
using openrowset for querrying, the result displays ?????????? instead of column name .My column name is in arabic which is a alias name.
Where as i am able to insert data in arabic and i can retreive the data in arabic from my table.
The problem exists with alias names given for my column.My columns names are displayed as ???????? in stead of arabic names
View 1 Replies
View Related
Dec 8, 2003
I am trying to write my first app using SQL Server. I am not able to even open a connection. I get the following error message:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I am not sure first of all why the user is listed as null. I hard coded a user name in for test purposes. Second, how DO I establish a trusted connection with a SQL Server. The SQL Server is located on an internal domain controller.
Also, is it necessary to have anything special installed on my remote machine? As I said, I am VERY new to Microsoft SQL Server
View 3 Replies
View Related
Apr 29, 2008
SQL Server 2000 documentation says that the syproperties value (via sp_addextendedproperty) has a limit of 7,500 bytes. So I have a property (MS_Description) on a column which is 654 characters long. When I run fn_listextendedproperty or if I select value from sysproperties I get only the first 256 characters returned. As if the field was truncated when I added it. BUT, if I select something like right(cast(value as varchar(1000)), 200) from sysproperties, I see the remaining characters. So they are there but I can never return the entire string at one time.
Any ideas why this is and how I can get around it?
View 3 Replies
View Related
Jul 19, 2007
The comapny i work for has a server running the following.
Opteron 246x2
2 Gig memory
320 Gig Sata 2 drives
Windows 2003 Standard Edition
SQL Server 2005 Express Edition. The Free one.
There are approx 10 users that connect to the server.
There are two programs which seem to use sql server. Act 7.0 and service ceo.
When the computer is rebooted its at 0% for sqlserver.exe
Than when all connect it maxes it 50% and its steady there. Seems Service ceo affects it the most.
Told by comapny need to buy full blown sql server to resolve problem. But i dont think this is the problem.
Questions: Is it common for server to be at 50% all the time with sql server running?
And if its not is there a way to reduce the sql cpu usage.
I am new to sql server and have done alot of research and fixes. Ive unsitalled and reinstalled all sql instances and done the tweaks suggested. Any fresh ideas would be great thanks.
John
View 3 Replies
View Related
Feb 19, 2007
Issue:
SQL Server 2005 Management Studio displays the old filename in the tab at top if you open a .sql file and then do "file save as". I have upgraded to SP2 in hopes that this behavior would be corrected but to no avail.
View 4 Replies
View Related
Aug 3, 2007
We have 2 users accessing exactly the same reports/data but who are getting different numbers of pages printed.
The report viewer displays 12 pages of data; when one user selects Print preview they get 12 pages displayed and 12 pages print. When the other user displays exactly the same 12 pages of report data and preview it, they get 24 pages, with the last column apparently spilling on to a second page. I realise that physical pagination is controlled by the print control and is unrelated to the HTML rendering, but the users have the same printer selected and the print-preview shows the margins set to the same size. What else can be causing the difference?
We are using RS 2000, SP2. TIA for any assistance with this.
View 1 Replies
View Related
Oct 26, 2006
I've got a little console app that basically pulls back a recordset from our SQL Server 2005, goes through each row in the dataset and may/may not insert a record into a different table in the database. We use sproc's for every transaction and I close every connection in the application. However, when the application ends, I still show connection pools open in the performance monitor. Same with websites that I know have no traffic or that have been stopped by me in IIS.Last night I showed a total of 6000+ "Current # pooled and nonpooled connections". Should I be worried about what seems to be unending growth in the connection pools? If so, how can I look to manage this better?
View 2 Replies
View Related
Jun 15, 2007
Hi, I've been trying to figure out this error message I'm getting. The exact error message is: " Procedure addservicerequest has no parameters and arguments were supplied." and this is applied to a SQL Command, but what confuses me is that I've got parameters already created and added. Is there any advice to help me out with this problem? Here's the code for reference: 1 Dim sqlparadate As New sqlparameter2 Dim sqlparacompany As New SqlParameter3 Dim sqlparalocation As New SqlParameter4 Dim sqlparacontact As New SqlParameter5 Dim sqlparaphone As New SqlParameter6 Dim sqlparadetails As New SqlParameter7 Dim sqlparaso As New SqlParameter8 Dim sqlparadrive As New SqlParameter9 Dim sqlparawork As New SqlParameter10 Dim sqlparaback As New SqlParameter11 Dim sqlparaproblem As New SqlParameter12
13 Dim sqlcon As New SqlConnection14 Dim sqlinsert As New SqlCommand("addservicerequest", sqlcon)15
16 'txttest.Text = caldateofservice.SelectedDate
17 If sqlcon.State = Data.ConnectionState.Closed Then
18 sqlcon.ConnectionString = "***"
19 sqlcon.Open()20 End If
21
22 sqlinsert.CommandType = Data.CommandType.StoredProcedure23 'sqlinsert.CommandText = "addservicerequest"24 'sqlinsert.Connection = sqlcon
25
26 With sqlparadate27 .SqlDbType = Data.SqlDbType.SmallDateTime28 .Direction = Data.ParameterDirection.Input29 .ParameterName = "@dateofservice"
30 .Value = caldateofservice.SelectedDate.ToString31 End With
32 sqlinsert.Parameters.Add(sqlparadate)33
34 With sqlparacompany35 .SqlDbType = Data.SqlDbType.NVarChar36 .Direction = Data.ParameterDirection.Input37 .ParameterName = "@company"
38 .Value = txtcompany.Text39 End With
40 sqlinsert.Parameters.Add(sqlparacompany)41
42 With sqlparalocation43 .SqlDbType = Data.SqlDbType.NVarChar44 .Direction = Data.ParameterDirection.Input45 .ParameterName = "@location"
46 .Value = txtlocation.Text47 End With
48 sqlinsert.Parameters.Add(sqlparalocation)49
50 With sqlparacontact51 .SqlDbType = Data.SqlDbType.NVarChar52 .Direction = Data.ParameterDirection.Input53 .ParameterName = "@contactname"
54 .Value = txtcontactname.Text55 End With
56 sqlinsert.Parameters.Add(sqlparacontact)57
58 With sqlparaphone59 .SqlDbType = Data.SqlDbType.NVarChar60 .Direction = Data.ParameterDirection.Input61 .ParameterName = "@contactphone"
62 .Value = txtcontactphone.Text63 End With
64 sqlinsert.Parameters.Add(sqlparaphone)65
66 With sqlparadetails67 .SqlDbType = Data.SqlDbType.NVarChar68 .Direction = Data.ParameterDirection.Input69 .ParameterName = "@details"
70 If ddldetails.SelectedValue = "Other" Then
71 .Value = txtreason.Text72 Else
73 .Value = ddldetails.SelectedValue74 End If75 End With
76 sqlinsert.Parameters.Add(sqlparadetails)77
78 With sqlparaso79 .SqlDbType = Data.SqlDbType.NVarChar80 .Direction = Data.ParameterDirection.Input81 .ParameterName = "@serviceorder"
82 .Value = txtserviceorder.Text83 End With
84 sqlinsert.Parameters.Add(sqlparaso)85
86 With sqlparadrive87 .SqlDbType = Data.SqlDbType.NVarChar88 .Direction = Data.ParameterDirection.Input89 .ParameterName = "@drivetime"
90 .Value = ddldrivetime.SelectedValue91 End With
92 sqlinsert.Parameters.Add(sqlparadrive)93
94 With sqlparawork95 .SqlDbType = Data.SqlDbType.NVarChar96 .Direction = Data.ParameterDirection.Input97 .ParameterName = "@worktime"
98 .Value = ddlworktime.SelectedValue99 End With
100 sqlinsert.Parameters.Add(sqlparawork)101
102 With sqlparaback103 .SqlDbType = Data.SqlDbType.NVarChar104 .Direction = Data.ParameterDirection.Input105 .ParameterName = "@backhome"
106 .Value = ddlbackhome.SelectedValue107 End With
108 sqlinsert.Parameters.Add(sqlparaback)109
110 With sqlparaproblem111 .SqlDbType = Data.SqlDbType.NVarChar112 .Direction = Data.ParameterDirection.Input113 .ParameterName = "@typeproblem"
114 .Value = ddlproblem.SelectedValue115 End With
116 sqlinsert.Parameters.Add(sqlparaproblem)117
118 sqlinsert.ExecuteNonQuery()119
120 sqlcon.Close() Specifically it occurs on line 118. Thanks
View 5 Replies
View Related
Jun 21, 2000
Hello:
In my production environment, ALL OF THE SUDDEN, my backups via EM schedule tasks, fail, yet when i examine the error, it says that the DUMP/LOAD was successful. I receive the following error:
'Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestore history. This may indicate a problem with the msdb.. . . . . .
I expanded the MSDB database, and ran sp_purgehistory (no params) and it still FAILS. Is this related to the log? Please HELP asap. Thank you in advance.
View 2 Replies
View Related
Aug 22, 2014
I have a database with a table called RAW, this table receives msgs via XML, sms and various other formats. The data is written into this table at a rate of approximately 50-100 rows per minute.
I have a SP which takes the data written into the raw table and performs various actions looks for account information, writes to a log table, writes to incident table’s gets GPS information and so on.
The records written into the raw table need to be processed at almost instantly with a maximum of under a minute from when they arrive into the table.
At present there is a sql agent job which executes the SP, this consist of a step to execute the SP which on completion moves to the next step which is a loop waitfor delay and then back to step 1.
The trouble is it never actually finishes and runs 24/7 there is no break point for error handling and occasionally the records that arrive in the raw table do not get processed and the job has to be restarted for it to pick them up again.
I am looking for best way to handle this process, I thought about a trigger but the performance impact on using a trigger was too heavy to consider.
View 2 Replies
View Related
May 21, 2007
Hello,
when is seemd that everything works some weird behaviours comes out.
I try to summarize the problem without to post the complete code.
Service Broker is set to have a dialog between two databases on the same SQL Server instance.
The Initiator queue has retention=on and there is an activation SP to handle errors and Target's end dialog message.
The Target queue has retention=off, MAX_READER =1 and there is an activation SP to receive the message (WAIT FOR (RECEIVE (1) ...), TIMEOUT 30000 and do something with this message (sample insert into a DB).
The conversation has a Timeout Dialog to end the dialog after a while.
The problem that the message is constantly processed. The Process doens't stop is I end the dialof after the processing either.
n.b.the Receive is within a Transation that I commit at the end.
some other informations that in the meanwhile I found out :
This was my complete WAIT FOR(RECEIVE :
WAITFOR ( RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@messagetypename=message_type_name,
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [TargetQueue]
), timeout 1000;
if (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
IF I delet TIMEOUT 1000, everything works as expected ...
Inside if (@@ROWCOUNT = 0)BEGIN..END I wrote also an Insert into a table to see wheter the end of the queue was reached but this insert never occurs (neither with not without timeout)
I'm happy that it works what if this is the solution, it make no sense to me!
Any ideas?
Thank you!
M.B.
Thank you very much
M.B.
View 9 Replies
View Related
Jun 30, 2007
Hello.
Let me describe first my replication setup:
- SQL Server 2005 SP1 (SP2 coming soon)
- Approximately 35 remote users (Salesrep laptop) using Pull Subscriptions
- Merge (Bi-Directional) (8 articles - tables only)
- Merge (Uni-Directional) (5 articles - tables only)
- Transactional (5 articles - tables only)
Users receive data based on their territory #, therefore they receive their customers sets of data. It happens that customer change from one territory to another but not frequently. When it happens, so far so good, the data is redirected to the new salesrep using the model we configured (Territory table with SUSER_NAME() to filter the data).
Ok, here's my problem. Since a while, I can see in the replication monitor that some users seems to log the same conflict again and again (Merge process). I mean, checking the history for many subscribers, there is always the same number in the "Conflict" colums.
As an example:
- Merge completed after processing 18 data change(s) (4 insert(s), 14 update(s), 0 delete(s), 31 conflict(s))
- Merge completed after processing 27 data change(s) (10 insert(s), 17 update(s), 0 delete(s), 31 conflict(s))
- Merge completed after processing 20 data change(s) (5 insert(s), 15 update(s), 0 delete(s), 31 conflict(s))
and so on...(Those are only 3 historical entries for a single subscriptions but there are many like that, always with the same count of conflict - vary per user). It appears to me that the same conflicts come over and over.
The thing is that if I decide to reinitialize a subscription, conflicts will disappear, therefore I know that it is not a process on the server that keeps changing the data; anyway, even if it was, changes would be applied on the subscription because the server always win in my setup.
Any idea what should I do with this? Any help would be greatly appreciated.
Thanks.
View 3 Replies
View Related
Jul 23, 2005
I've got a new server with Pentium4 xeon processors. I've used sp_who2for quite a while on my old server -- to look at CPU and IO usage. Onthe new server, the CPU times are huge -- although actual performanceis quite good. I know sp_who2 is an undocumented proc. Has anyoneexperienced this or is it just me?
View 1 Replies
View Related
Jul 7, 2015
We have an issue with the Version Store growing constantly. According to sys.dm_os_performance_counters, "Version Generation rate (KB/s)" is growing, but "Version Cleanup rate (KB/s)" isn't. We use read-committed snapshot isolation
While dbcc opentran and sys.dm_exec_requests don't show any long running transactions, I wrote a query looking at sys.dm_tran_ active_snapshot_ database_transactions. This shows a number of long running transactions but, according to sys.dm_exec_sessions, they are all sleeping. The transactions that are running come and go very quickly, as I would expect.Could these sleeping transactions be responsible for preventing the version store from cleaning up?
View 2 Replies
View Related
Aug 13, 2007
Is it possible to alter the format/layout of the parameter input boxes that the user enters values into.
reporting services automatically arranges them. Is it possible for the report writer to set the layout?
I know that when using report viewer in asp.net that the properties of the viewer are confiigurable.
View 1 Replies
View Related
Sep 17, 2007
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
View 6 Replies
View Related
