Hi,
I need to find the exact time a message was received in the service broker, the time it finished sending and the time it was received at the destination (which is another service broker queue on a different server). I am trying to figure out what the delay is, with increasing message size.
Please advice
Thanks
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
I have a select statement which should do this here below:
WHERE TimeStamp = Last 24 hours'
how can I program it ? I have tried -1, getdate() and more with no success.
Hallo,
I have some problems to handle the poison messages.
Messages are exchanged between 2 databases on the same SQL Server instance.
I created a Trigger on the insert , that call the sp that begin the conversation(10 minutes Timeout) and send the message to the target queue.
On the target queue I activated a SP and the main code is:
BEGIN TRANSACTION
WAIT FOR (Receive (1) ..) timeout 5000
IF (@ErrorSave <> 0)
BEGIN
INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES ();
END
ELSE
if (@messagetypeName = N'Message')
BEGIN
exec [dbo].[sp_ProcessMessage] @message_body
END
ELSE IF (@messagetypeName = N'EndOfStream')
BEGIN
END CONVERSATION @dialog
END
COMMIT TRANSACTION
My communication has a strange behaviour:
if I type
Begin
insert into TriggerTable values(XMLMessage)
insert into TriggerTable values(XMLMessage)
....
insert into TriggerTable values(XMLMessage)
end
Everything work fine but If I write an insert of 1000 message coming from an another table and I use the
cursor login after I while the transmission stop because the Target queue become inactive.
I can see my messages stuck in the Initiator transmission_queue so I think that there is some Poison message that cause 5 rollback and disable the receiver queue.
First I would like to isolate the wrong message and carry on with the insert, my application doesn't have to stop the conversation or return any error, but If I use the Sql server Debugger I'm not able to debug the Target queue's stored procedure.
I suppose that some error happens on the Target queue's stored procedure but how can I first at all find it out?
Maybe it has something to do with the transition wrapped around the RECEIVE command?
thank you for any helps!
Marina B.
Hi All,I am having an error message when running the program below, the message says " Could not find stored procedure 'dbo.U_Login ". I have tried a inline Sql statement to match if user exists and then redirects to another page but when using a Stored Procedure what I get is the above mentioned error. I am logged onto my PC as Administrator but I don't know why this error appaears. Can anyone help me with this ? I am using SQL Server Express with Visual Studio. here the application I am trying to run:1 using System;
2 using System.Data.SqlClient;
3 using System.Data;
4 using System.Configuration;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12
13 public partial class _Default : System.Web.UI.Page
14 {
15 protected void Page_Load(object sender, EventArgs e)
16 {
17
18 }
19 protected void btnSubmit_Click(object sender, EventArgs e)
20 {
21
22 //SqlDataSource LoginDataSource = new SqlDataSource();
23 //LoginDataSource.ConnectionString =
24 // ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
25 ////ConfigurationManager.ConnectionStrings["LoginConnectionString"].ToString();
26 ////// if (LoginDataSource != null)
27 //// // Response.Redirect("DebugPage.aspx");
28
29 //Sql connection = LoginDataSource.ConnectionString;
30
31 string UserName = txtUserName.Text;
32 string Password = txtPassword.Text;
33
34 // string query = "SELECT * FROM Users WHERE userName = @UserName " + "AND Password = @Password";
35 //SqlCommand Command = new SqlCommand(query, new SqlConnection(GetConnectionString()));
36
37 SqlCommand Command = new SqlCommand("dbo.U_Login", new SqlConnection(GetConnectionString()));
38
39
40 //SqlConnection SqlConnection1 = new SqlConnection(GetConnectionString()); //added
41 //SqlCommand Command = new SqlCommand(); //added
42 //Command.Connection = SqlConnection1; //added
43 // Command.Connection = new SqlConnection(GetConnectionString());
44 //SqlConnection_1.Open();//added
45
46 Command.CommandType = CommandType.StoredProcedure; //added
47 //Command.CommandText = "dbo.U_Login"; //added
48
49
50
51
52 Command.Parameters.AddWithValue("@UserName", UserName);
53
54 Command.Parameters.AddWithValue("@Password", Password);
55
56 Command.Connection.Open();
57
58 SqlDataReader reader;
59
60
61 //reader = Command.ExecuteReader(CommandBehavior.CloseConnection);11
62
63 reader = Command.ExecuteReader();
64
65
66 if (reader.Read())
67
68 Response.Redirect("DebugPage.aspx");
69 else
70 Response.Write("user doesn't exist");
71
72
73 //SqlConnection_1.Close();//added
74
75
76 }
77 private static string GetConnectionString()
78 {
79
80 return ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
81
82 }
83
84
85 }
86
87
88
89
Thanks in advance
I'm getting the following message from a VB6 program running ADO...
It will process a job then I get the message.... [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'FLDB1' in syssservers. Execute sp_addlinkedserver to add the server to sysservers...
This started to happen after I moved the db from SS2000 to SS2005...
Our db server and sql server instance on that server are both named 'FLDB1'... Do I need to run the above execute sp_addlinkedserver command on the new 2005 server?
Thanks-Dave
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
date time s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date time s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400 3
6/9/2015 11:56:26 TestSite 900 2
6/12/2015 12:56:26 TestSite 700 1
For starters, please feel free to move this if it is in the wrong forum.
The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?
Hello,
I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.
// Create an empty request message
string Msg = "Hello";
MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));
Message request = new Message("Request", body);
Thanks,
Scott Allison...
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedI have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
how to insert a value into table where the fieldname uses a datatype timestamp.(asp.net2.0)
View 1 Replies View RelatedI have a timestamp in a date field of a table I have but when run it does not enter the date automatically and all I get is system.byte[] display where the dtae and time should be, how can i get it so this field places the date in automatically.???
View 1 Replies View RelatedI am retrieving data from a database, and have been noticing some really strange timestamps. Has anyone ever seen a timestamp that looks like this: 16777215Karls
View 1 Replies View RelatedHello,
I just started using SQL and i don't really know too much syntax.
I have an ASP page that is inserting data into a table called YellowAlerts.
The first field in this table is AlertDate. I thought I read somewhere that there is a timestamp feature. If anyone could help me out that would be great.
Thanks.
How can I add a timestamp column to a table that will let me know when
a record was inserted? (perhaps by setting a default to that column?)
For some reason, @@currenttimestamp gives me
some binary garbage.
Please help with any suggestions.
How do I "disable" the timestamp so that when I copy my data into a new
database it will use the timestamp from the last time the table was effected?
Thanks,
Tracy
Hi All,
I believe that all created tables have got by default a timestamp field.
I tried to find out how to read this field with a Transact-SQL statement.
And so I don't know the syntax, is anyone can help me ?
Thanks,
Herve
If i have a table with a 3 fields and one of them has a timestamp as a field type and I want to do an insert or update what is the value I am inserting for the column timestamp.
Help appreciated
hello I put a time stamp in one of my column and it says binary data in the field. How do i make that binary data turn into a real date and time. If this is possible how do I separate the date from the time in the results or would I have to do this myself. Also, I need to sort the results by date can this be done?
Sorry for the stupid questions my sql book (sql demystified) does not elaborate. Thanks
hello,
m new to sql server.can any one tell me is it possible to insert only time value in timestamp datatype.
i have table which have datatype datetime.and i have to insert only time.i can't change the datatype datetime to any other one.p
plz any one can suggest me the solution..
Regards,
Sonal.
Hello I'm not a SQL Expert but i'm using it for a few months. Now i need to use a variable that everytime that is called to a select into it gives the timestamp (or Stamp) to put on a specific field. The problem is that i need this variable not in bd conotation (like 2007-02-12 12:00:44:33) but like this 2007021212004433. Can someone help me? Thanks in advance.
View 5 Replies View RelatedIs there any reason to have a TimeStamp column onto a table that has aPrimaryKey when using SQL Server and an Access front end?I read that doing so will always eliminate that write conflict error.But I notice that when pulling a recordset of 10K records, it takes 3Xless time if I delete the timestamp column on the table before runningthe SP.lq
View 2 Replies View RelatedIs there any chance to get the content of a timestamp field?I am trying to get the content via Visual Objects and I am receiving onlynothing.Not NIL but "".Is it possible to get any useful information out of this field in anylanguage?(btw I know that the MS-Timestamp is not a timeformat)THXJens
View 1 Replies View RelatedIs there any reason at all to use a timestamp column in a table havinga primarykey column???lq
View 2 Replies View RelatedHi there,
When inserting into a table with a timestamp column without explicitly setting the column_list you get the following.
ie.
insert into Table_A
select *
from Table_B
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
I was wondering if there's a way to overcome this without specifying the column list?
Thanks.
HI every one
I have a timestamp column in my database. I want to use this column to get current changes but i am not able to get desired results when i use to compare that field in C#.
Thanks in advance
Take Care
ALLAH HAFIZ
Hi folks, I am binding a gridview to a SQLDataSource that contains a timestamp column. My SQLDatasource contains an update method (via a stored procedure) that takes all of the data fields from the grid as input parameters. I am getting an error stating 'sql_variant is incompatible with timestamp' . The parameter definition for the update method specifies the timestamp column as type 'Object'. <UpdateParameters> <asp:Parameter Name="timestamp" Type="Object" /> ... </UpdateParameters> When the data is initially retrieved, the timestamp column is converted to a System.Byte[] however I cannot specify System.Byte[] in the parameter definition (different error) I have read other post with this issue but none seemed to have been solved (while still using SQLDataSource and binding).I have tried other datatypes and they do not work as well. Any help would be greatly appreciatedThanksTom
View 3 Replies View RelatedI have a database that I don't want to lock. I decided that before any updates can occur I would check a timestamp value and ensure that nobody else updated before I did (avoiding the 'last update wins' scenario).
I have a problem, I can read the Timestamp from the db when I read the record. I currently use the data to pre-fill a form (gee go figure ;) ) and the user changes some values and updates.
I don't know what to DO with the timestamp value while I am holding it. I have tried putting it into a hidden field on the form but the value does not seem to translate back and forth.
Do I have to store it as in memory as part of the session or can I somehow convert to and from text??
I have about 0 experience working with byte arrays so the best answer is one with an example.
Thanks.
Hi world,I would like to know how to maintain a table with a "timestamp" column. I mean a column that automatically set the current datetime when the row is updated.I don't want to go trought my application and set that in the update or insert command. I would like to know if there is a possibility of putting a trigger or something.thx
View 6 Replies View Related