Handling Poison Messages Sample Code In BOL Wrong?
Jul 28, 2005
In the "Example: Detecting a Poison Message" section, it reads: This Transact-SQL example shows a simple, stateless service that includes logic for handling poison messages. Before the stored procedure receives a message, the procedure saves the transaction. When the procedure cannot process a message, the procedure rolls the transaction back to the save point. The partial rollback returns the message to the queue while continuing to hold a lock on the conversation group for the message.
Is there any sample code to demo the SSB send messages with same sql instance?
my case is very simple:
I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions.
Hello again, I have some poison message detection in place, based on the BOL sample. My problem is that after the 5th message retry my queue goes down - that is the fifth retry on any message. In actuallity, the first message is retried 3 times and it is taken off the queue [for real], the second message comes in and on the second retry - pooof - the queue is down.
I though the poison mechanism should work on a per message basis. It there a setting for the queue I missed? Is my only chance for to fix this: re-enable the queue upon BROKER_QUEUE_DISABLED event notification?
Books online mention the existence of sample code for several custom tasks, including the one mentioned in the title. But, when I try to find this code in the location mentioned it is nowhere to be found.
I have run a search on the rest of my drive and come up empty.
Hello guys,I need some ideas on how to handle an exception or a user defined error message.I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?This is what I was thinking....Solution #1--------------My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.Solution #2--------------The proc should have an output param ( @CreationStatus CHAR(1) ).If the @CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.What are some of the ways you deal with this situation?Your suggestions are greatly appreciated.Thank you!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
Does anyone have any sample code for using ntext data type, when inserting or reading big block of texts? How can you read a big huge block of text back into c#?
This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:
USE AdventureWorks; GO DECLARE @tablename sysname SET @tablename = N'Person.AddressType' table_loop: IF (@@FETCH_STATUS <> -2) BEGIN SELECT @tablename = RTRIM(UPPER(@tablename)) EXEC ('SELECT ''' + @tablename + ''' = COUNT(*) FROM ' + @tablename ) PRINT ' ' END FETCH NEXT FROM tnames_cursor INTO @tablename IF (@@FETCH_STATUS <> -1) GOTO table_loop GO
The errors are:
Msg 208, Level 16, State 1, Line 1 Invalid object name 'PERSON.ADDRESSTYPE'.
Msg 16916, Level 16, State 1, Line 9 A cursor with the name 'tnames_cursor' does not exist
The database is connected. Table Person.AddressType is a part of it.
hi, i learn by practice and i would like an asp code using c# that connects to sql database can retrives information from a table. can you please copy paste me some code or give me exact link a?
I was thinking of using a CTE to return history, have you ever done this?
I know this can be done in other ways but I wanted to wrap this CTE in a view so people could join to it.
Here is an example table: KEY ASOFDATE A 1/1/2007 A 3/1/2007 B 1/1/2007 B 2/5/2007 B 3/6/2007
I want the results to look like: KEY START END A 1/1/2007 3/1/2007 A 3/1/2007 NULL B 1/1/2007 2/5/2007 B 2/5/2007 3/6/2007 B 3/6/2007 NULL
This would be even better:
ID KEY ASOFDATE 1 A 1/1/2007 2 A 3/1/2007 3 B 1/1/2007 4 B 2/5/2007 5 B 3/6/2007
I want the results to look like: ID KEY START END 1 A 1/1/2007 3/1/2007 2 A 3/1/2007 NULL 3 B 1/1/2007 2/5/2007 4 B 2/5/2007 3/6/2007 5 B 3/6/2007 NULL
If somebody have worked on providing the code for the selection of delivery method and delivery schedule page in aspx . Then please share it with me. I am working on creating a Subscription page using aspx rather then reporting services page.
Please let me know if there are any controls provided by reporting services or some 3rd party for this.
Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris
I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.
I am looking for some examples of code for an appliction which will let run RS reports from a custom application. The user will choose the reports from a menu structure
We have an exiting application which we use to run crystal reports which we are going to be migrating to RS. We use custom web controls to capture the required parameter values for the reports, and hope to reuse the same controls (with minimal rework) to capture the parameters values for the RS reports.
What I am after is some code which will
a) let me run the report and display it. I don't want to display the report parameters in the URL etc as we generate some report parameter values based upon the user's ID and do NOT want them displayed back to the users
b) export the report to PDF, CSV etc depending upon the option chosen by the user.
The application will be developed in VS 2005 - VB. Does any one know of some links to some sample code
Hi, there; I try to import data from ODBC using C# programmatically. Is there any sample code we can have a look. Like how to read schema from ODBC source table and then create source column...
I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.
First, are there any books you recommend for learning ???? (I'm not even sure what I need to learn: .NET? ADO.NET?)
And as a follow-up, any good websites that provide good reference documentation?
And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.
Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID. I need to read each row and compare it to the next row. If STATUS is the same, discard the second row. When STATUS is different, send the first row to the output (to be used by the next task in the data flow). Using the "different" row from step 3, go to step 2.
I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.
Any help you can provide would be much appreciated.
How to upload excel data to sql server 2000 thorugh .net application. I want like one upload button should be there,we have to browese corresponding excel file and then we need to upload to database. before uploading it has to ask appending or replace everything in table. both options appending and replacing shoulb be there. How to upload ? any sample code is there plz give me.
After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.
On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"
The readonly User: variables that start with gs can be read in the PreExecute method
The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow
The xml code is adapted from an idea in Donald Farmers book
enjoy
Dave
Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat! ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components
Public Class ScriptMain Inherits UserComponent Dim sw As StreamWriter 'In addition to using the Imports System.Xml statement a reference must be added to the 'System.Xml assembly (Select Project-Add Reference from IDE) Dim xWriter As XmlTextWriter Dim OutputFileType As String '.csv or .xml
Public Overrides Sub PreExecute()
'Read Only variables Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid' Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_' Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv' Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment' Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment' Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE' Dim fileName As String = gsPickUp & "" & gsPickUpFilename fileName = fileName & (Format(Now(), "yyMMdd").ToString) 'MsgBox(fileName)
OutputFileType = gsPickUpFileExtn If OutputFileType = ".csv" Then fileName = fileName & gsPickUpFileExtn sw = New StreamWriter(fileName) 'connection to dest file
'Header records sw.Write(gsRunMode) sw.Write(Environment.NewLine) ' end of line sw.Write(gsMemoText) sw.Write(Environment.NewLine) sw.Write(gsStatementText) sw.Write(Environment.NewLine) sw.Write(Environment.NewLine) 'Spacer End If
If OutputFileType = ".xml" Then fileName = fileName & gsPickUpFileExtn 'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing) 'xWriter.WriteStartDocument() 'xWriter.WriteComment("Customer file parsed using script") 'xWriter.WriteStartElement("x", "customer", "http://some.org/name") 'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString) xWriter = New XmlTextWriter(fileName, Nothing) xWriter.WriteStartDocument() xWriter.WriteComment("Customer file parsed using script") xWriter.WriteStartElement("x", "customer", "http://some.org/name") xWriter.WriteAttributeString("FileName", fileName) End If
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
If OutputFileType = ".csv" Then Dim delim As String = ","
If OutputFileType = ".csv" Then 'Create the trailer sw.Write(Environment.NewLine) ' blank line sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible sw.Write(Environment.NewLine) sw.Flush() 'send the stream to file 'Close file sw.Close() End If
If OutputFileType = ".xml" Then xWriter.WriteStartElement("RecordCount") xWriter.WriteString(Me.Variables.giSuccessCount.ToString) xWriter.WriteEndElement() xWriter.WriteEndElement()
Our current service throws on-demand .DTSX and now we'd like that will throw old ETL 2000 too.
I'd like to know if success or not success when calling dtspkg.dll from my managed code (vb). Is it possible or not? I only capture errors for the sake of TRY..END TRY but I don't know how the hell to know if the dts execution was successful. Execute method not returns anything.
Does anybody have a working Java code sample that connects to an SQLServer 2005 database on a remote host, via the default named pipe, from a client using the SQLServer 2005 JDBC driver? Could you post it, or a pointer to it?
I've gotten java.sql DriverManager.getConnection() to work fine with TCP/IP connections before. But I'm a newbie with named pipes, and unclear on how the connection string/properties are different. I've tried to piece it together from multiple docs and threads, but haven't found sample code that quite fits my situation. I think a simple working example would best clarify the syntax.
The server is not using SQL Express. Most SQLServer configuration options are defaults; the named pipes protocol is enabled.
I am using MVWD 2005 Express and Sql Server 2005 Express. I am using SqlDataSource control and GridView control to disply a ata table, and let user to input data into the database through dropdownlist and TextBox. When I run the application I always get exception:"Conversion failed when converting character string to smalldatetime data type." I could not find anything wrong. Could anybody out there help me find out what's wrong with my code. Much appreciate it.Here is the code:<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"DataSourceID="SqlDataSource2" DataKeyNames="DocumentID"><Columns><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /><asp:BoundField DataField="DocumentName" HeaderText="DocumentName" ReadOnly="True"SortExpression="DocumentName" /><asp:BoundField DataField="DateInput" HeaderText="Date" SortExpression="DateInput" /><asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" /><asp:BoundField DataField="DocumentCategory" HeaderText="Category" SortExpression="DocumentCategory" /><asp:CheckBoxField DataField="TopDoc" HeaderText="TopDoc" SortExpression="TopDoc" /></Columns><EmptyDataTemplate>There is no data to be displayed!</EmptyDataTemplate></asp:GridView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" DeleteCommand="DELETE FROM [Documents] WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"InsertCommand="INSERT INTO [Documents] ([DocumentName], [DateInput], [Comments], [DocumentCategory], [TopDoc]) VALUES (@DocumentName, @DateInput, @Comments, @DocumentCategory, @TopDoc)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Documents]"UpdateCommand="UPDATE [Documents] SET [DocumentName] = @DocumentName, [DateInput] = @DateInput, [Comments] = @Comments, [DocumentCategory] = @DocumentCategory, [TopDoc] = @TopDoc WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"><DeleteParameters><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></DeleteParameters><UpdateParameters><asp:Parameter Name="DocumentName" Type="String" /><asp:Parameter Name="DateInput" Type="DateTime" /><asp:Parameter Name="Comments" Type="String" /><asp:Parameter Name="DocumentCategory" Type="Int32" /><asp:Parameter Name="TopDoc" Type="Boolean" /><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></UpdateParameters><InsertParameters><asp:ControlParameter Name="Comments" Type="String" ControlID="TextBox2" /><asp:ControlParameter Name="DocumentCategory" Type="Int32" ControlID="DropDownList1" PropertyName="SelectedValue" /><asp:ControlParameter Name="TopDoc" Type="Boolean" ControlID="RadioButton1" PropertyName="Checked" /></InsertParameters></asp:SqlDataSource>Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.ClickDim savePath As String = "C: empuploads"Dim fileName As String = FileUpload1.FileName Dim dt As DateTime = DateTime.Now SqlDataSource2.InsertParameters.Add("DocumentName", fileName) SqlDataSource2.InsertParameters.Add("DateInput", dt)SqlDataSource2.Insert()If (FileUpload1.HasFile) ThensavePath += FileUpload1.FileName FileUpload1.SaveAs(savePath) Label1.Text = "Your file was uploaded successfully."DisplayFileContents(FileUpload1.PostedFile)ElseLabel1.Text = "You did not specify a file to upload."End IfEnd Sub
Hi im new and new to coding when i run the following code it gives me the following error can someone please help me and tell me where i am going wrong; "Invalid attempt to read when no data is present. " using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls;
public partial class dclogin : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {
I want to print out the table name and record count whenever a table has record(s), but the statement: Print 'TableName: '+@TableName +' Row Count: '+CAST(@RCTR AS varchar) never gets executed.
Any help is appreciated.
****************************************** SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int
SET @TableName = '' SET @RCTR = 0
WHILE @TableName IS NOT NULL BEGIN SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ANDQUOTENAME(TABLE_NAME) > @TableName ANDOBJECTPROPERTY( OBJECT_ID(QUOTENAME(TABLE_NAME) ), 'IsMSShipped') = 0 )
SET @SQL = ( 'select count(*) from ' + @TableName ) EXEC (@SQL) SET @RCTR = @@ROWCOUNT IF @RCTR > 0 Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar) END *********************************
SELECT * FROM BOOK_CUSTOMER WHERE STATE='FL' OR STATE='NJ' AND REFERRED=NULL;
Im sorry for posting this, but the results just arent working out. Im trying to display user information if the customer lives in florida or new jersey and they cant have a referring user. I tried the above combination and managed to only list the people from fl and got one user who was referred. I also tried:
SELECT * FROM BOOK_CUSTOMER WHERE REFERRED=NULL AND STATE='FL' OR STATE='NJ';
And I only get people from nj and also get one referred user. Can anyone point out what im doing wrong?
I want to print out the table name and record count whenever a table has record(s), but the statement: Print 'TableName: '+@TableName +' Row Count: '+CAST(@RCTR AS varchar) never gets executed.
Any help is appreciated.
****************************************** SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int
SET @TableName = '' SET @RCTR = 0
WHILE @TableName IS NOT NULL BEGIN SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ANDQUOTENAME(TABLE_NAME) > @TableName ANDOBJECTPROPERTY( OBJECT_ID(QUOTENAME(TABLE_NAME) ), 'IsMSShipped') = 0 )
SET @SQL = ( 'select count(*) from ' + @TableName ) EXEC (@SQL) SET @RCTR = @@ROWCOUNT IF @RCTR > 0 Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar) END *********************************
Here is part of the code: CREATE table Fornecedor ( codintCONSTRAINT RI_79 PRIMARY KEY, NCint, nomeVARCHAR(100), telefoneint, moradaVARCHAR(100), CONSTRAINT RI_78 CHECK(cod>0), CONSTRAINT RI_80 CHECK(telefone>0), CONSTRAINT RI_81 CHECK(telefone is not null), CONSTRAINT RI_83 CHECK(morada is not null), CONSTRAINT RI_85 CHECK(nome is not null), CONSTRAINT RI_86 UNIQUE(nome), CONSTRAINT RI_87 CHECK(NC>0), CONSTRAINT RI_88 CHECK(NC is not null), CONSTRAINT RI_89 UNIQUE(NC) );
CREATE table Encomenda ( codintCONSTRAINT Fornecedor_não_existe_I FOREIGN KEY REFERENCES Fornecedor(cod), número_ordemint, lojaintCONSTRAINT Loja_não_existe_IV FOREIGN KEY REFERENCES Loja(loja), data_pedidoCHAR(8), data_previstaCHAR(8), data_entregaCHAR(8), CONSTRAINT RI_117_72 PRIMARY KEY(cod,número_ordem), CONSTRAINT RI_71 CHECK(número_ordem>0), CONSTRAINT RI_74 CHECK(data_pedido is not null), CONSTRAINT RI_76 CHECK(data_prevista is not null), CONSTRAINT RI_118 CHECK(loja is not null) );
Returns me this error when I try to create Encomenda_Disco: There are no primary or candidate keys in the referenced table 'Encomenda' that match the referencing column list in the foreign key 'Encomenda_não_existe_I'.
I have checked this code for more than an hour, and I'm starting to get prety anoyed with this. Can someone be kind enough to point me the error? :)
