Is it possible to receive from a queue by a conversation handle? In the documentation there is an example that show you how to do it. Yet, if you "read" the whole document it says that the conversation handle can not be an expression.
The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression.
Here is what I'm trying to do:
;RECEIVE TOP(1) @MsgBody = CAST(message_body as XML)
FROM ProcessingLetters
WHERE conversation_handle = @Conversation_Handle
It doesn't seem to matter if I use RECEIVE or SELECT. It will return nothing.
I've even tried this:
where cast(Conversation_Handle as varchar(100)) = cast(@Conversation_Handle as varchar(100))
Why am I doing this? I've put something into the queue to let me know that something is processing. When it is done I want to pull it out and end the conversation.
So is the WHERE conversation_handle = @Conversation_Handle supposed to work?
I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.
The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.
I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.
Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.
Hello, This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A. When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back? 1. Message is left on the Queue. 2. is the worker procedure triggered again for the same message by the queue? 3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B. For my scenario messages are independent and ordering does not matter. Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
I am writing an SQL stored procedure that returns a dataset as well as a return value. When I execute the stored proc, all seems to work, except the return value comes back as null.
Code:
// Create Instance of Connection and Command Object SqlConnection connection = new SqlConnection(ApplicationConfig.ConnectionString); SqlCommand command = new SqlCommand("SoundLeaf_GetPagedProductsByCategory", connection);
// Mark the Command as a SPROC command.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC SqlParameter parameterID = new SqlParameter("@CategoryID", SqlDbType.Int); parameterID.Value = categoryID; command.Parameters.Add(parameterID);
SqlParameter parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int); parameterPageIndex.Value = pageIndex; command.Parameters.Add(parameterPageIndex);
SqlParameter parameterRowCount = new SqlParameter("@RowCount", SqlDbType.Int); parameterRowCount.Value = rowCount; command.Parameters.Add(parameterRowCount);
SqlParameter parameterPageCount = new SqlParameter("@PageCount", SqlDbType.Int); parameterPageCount.Direction = ParameterDirection.Output; command.Parameters.Add(parameterPageCount);
CREATE PROCEDURE GetPagedProductsByCategory] ( @CategoryId int = 0, @PageIndex int = 1, @RowCount int = 10, @PageCount int OUTPUT ) AS Declare @TotalRows int, @StartPosition int
Declare @PK int DECLARE @tmpTable TABLE ( PK int NOT NULL PRIMARY KEY )
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR select a.ProductId from products a, categoryProducts b where b.categoryid = @CategoryID and b.productid = a.productid Order By a.productid
Open PagingCursor Fetch Relative @StartPosition From PagingCursor Into @PK
while (@RowCount <> 0) And (@@Fetch_Status = 0) begin Insert Into @tmpTable (PK) Values (@PK)
Fetch Next From PagingCursor Into @PK Set @RowCount = @RowCount - 1 end
Close PagingCursor Deallocate PagingCursor
Select Products.* From Products Join @tmpTable temp ON Products.ProductID = temp.PK Order By Products.ProductID
SELECT @PageCount = COUNT(*) / @RowCount FROM Products // value is never returned
I have a scenario whereby I will have 200+ clients putting messages onto a service broker queue. This message will go through a pipes and filters based messaging system, and ultimately the message will pop out the other end.
Here's the question: what is a good way of making sure the same client gets the response to the message he received. Is there anyway I can selectively receive messages from a queue, i.e., pass a correlation id in with the message, and then filter messages based on that id.
Or if someone knows a better way to do it altogether i'd really appreciate it.
I am right now looking at the new functionality in SSIS to cover some of the ways we can streamline proceses in the company. I know that SSIS has SendMail tasks, however I was wondering if there is any functionality for going out and receiving mail and parsing those messages in? Would this have to be done as custom code within SSIS and if so can anyone direct me to what would be needed to do so? Thanks.
hi friends, i want to store an image in DB. but most of my friends told that, to store an image in web server then store tat location in a DB and access it anywhere. this is for asp.net in C#-code behind. how to do this? i've a table with a text field. then .......? waiting for ur reply............ note: i need coding. not by using controls. pls...
I created a new column and modified my businesslogic file and the stored procedure. My program uses the SQL helpder class to execute the stored procedure which inserts the article into the database. All of the other previous columns are getting written to, however the new column I created is getting NULL when I open the database to inspect. Why does this happen? I made sure the db column name matches exactly with the params elsewhere.Did I miss something? It's not throwing an error anymore. it's not taking the data I enter into the form and putting it into the db column.
Is there a way for me to set up CDC so that all the processing (SQL Agent, etc) happens on the machine receiving the data? I'd like to move as much of the processing as possible to the destination.
I have just had SQL Server 2005 installed on my machine. I did have SQL Server 2000 on my machine previously and can still access parts of 2000.
In 2005 I do not have the option schema option under security for any of the databases; therefore, I tried creating the schema manually as you will see below.
In 2005 I execute the following
CREATE SCHEMA TEST GO
The message returned is "COMMAND COMPLETED SUCCESSFULLY"
I, then, execute the following
CREATE TABLE TEST.SalesPeople
(
SalesPersonId INT,
SalesPersonName VARCHAR(50)
)
GO
The message returned is
Msg 2760, Level 16, State 1, Line 1
Specified owner name 'TEST' either does not exist or you do not have permission to use it.
Can you give me the correct syntax on how to create the schema manually? Do you know why this is not showing up under security for the databases?
I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.
I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.
Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.
Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.
And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.
I was using VB6 to access a MS SQL Server database. The code worked and works fine. I then decided to migrate the code to C#.Net 2005 using ADO 2.8 (not ADO.Net). Doing that yields with the same exact code the error message, "Current Recordset does not support updating".
I did a whole bunch of Google searches and didn't see anything useful. Mainly the advice from Microsoft and others is to make sure the mode on the connection string is set to "ReadWrite", as the default is "Read Only" and to make sure to set the lock type to either optimistic or pessimistic. Still others said that the code should set the CursorLocation property of the recordset.
I can safely say that I have been setting the mode to "Read/Write" since the start and have played around with the lock type, cursor location, and open method. Nothing works on C#, BUT VB6 is so totally happy with everything.
The provider works fine, as VB6 works fine, and the lock type is also fine, so therefore the built in suggestions do not apply.
My code is:
// Connection string template. Filled in properly in real code. strConnect = "Server={0};Database={1};"
// Set the connection properties. this.SQLConnection.ConnectionString = strConnect; this.SQLConnection.Provider = "SQLOLEDB"; this.SQLConnection.Mode = adModeReadWrite;
// Open the connection. this.SQLConnection.Open(strConnect, strUserName, strPassword, -1);
=================
// Create the ADO objects needed. dbRSAdd = new Recordset();
// Open the recordset. dbRSAdd.Open(strTable, dbCatalog.ActiveConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, (int)CommandTypeEnum.adCmdTable);
// Cycle through each record to add. dbRS.MoveFirst(); for (lRecord = 0; lRecord < dbRS.RecordCount; lRecord++) { // Add a new record. dbRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
... }
// NOTE: The code crashes with the call to 'AddNew'.
I have a table that records sales leads and I need to calculate the total number of leads per user and the total amount owed, then then show only the users who have recieved 3 leads. The UserID is an integer.
Here is what i have:
SELECT * FROM ( SELECT DISTINCT UserID, (SELECT COUNT(*) FROM LeadMatches WHERE (UserID = L.UserID)) AS TotalLeads, (SELECT SUM(Price) FROM LeadMatches WHERE (UserID = L.UserID)) AS TotalAmount FROM LeadMatches AS L ) WHERE TotalLeads = 3
This should work but it produced the following result:
UserID | TotalLeads | TotalAmount --------------------------------------------------------------- <Unsupported Data Type> | 3 | 75.00 <Unsupported Data Type> | 2 | 50.00
I have no idea why the result produceing <Unsupported Data Type> for the UserID and have been unable to find a way around as of yet. Any help would be appreciated.
While attempting to configure DTC on a Windows xp Pro, SP 2 machine I receive the error message [DTC was installed by the SQL Server. Please reinstall!] Where do I find this installation file to complete the requested task -- any help would be awesome -- I have tried down loading hotfixes, and security updates that mention fixing problems with DTC -- but alas they did not fix my error ;-)
I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )
When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.
But when I run the same app against the local machine SQL Server, I receive the message "Options".
Does anyone know if there are SQL Server options that must be set to certain values? I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl] @MtlTblMaterialType READONLY AS BEGIN Set NOCOUNT ON;
I keep receiving an update notification for KB 932557 and when I try to apply the fix, it fails. Checking update history tells me error code 0x2c24 is the reason for failure.
I cannot find any help on this error code anywhere.
I have the following query for my site which was functioning great using the OLEDB driver. Unfortunately, my client's web host forced us to use ODBC and it is no longer working Any suggestions for what I should do?
Here is the query: SELECT t.* FROM (SELECT TOP 10 itemnum, itemnum FROM ( SELECT TOP 100 itemnum, itemnum FROM(CatView) WHERE [CATALOG PAGE] LIKE 'SM%' ORDER BY itemnum ASC) AS foo ORDER BY itemnum DESC) AS bar INNER JOIN CatView AS t ON bar.itemnum = t.itemnum ORDER BY bar.itemnum;
Here is the error i'm now receiving:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The specified field 'itemnum' could refer to more than one table listed in the FROM clause of your SQL statement. I know it was working great before, so i'm not sure what to do.
hi friends, i need a code for storing and receiving an image to/fro SQL SERVER 2000 (in C#). i had searched some sites, all are in VB for windows forms not for website. finally i got a code from some site. it is working for storing purpose. not working for receiving purpose. the code for receiving and displaying purpose is (in a fresh page) private void Page_Load(object sender, System.EventArgs e){ // Put user code to initialize the page here MemoryStream stream = new MemoryStream (); SqlConnection connection = new SqlConnection (@"server=INDIAINDIA;database=iSense;uid=sa;pwd=india"); try { connection.Open (); SqlCommand command = new SqlCommand ("select Picture from Image", connection); byte[] image = (byte[]) command.ExecuteScalar (); stream.Write (image, 0, image.Length); Bitmap bitmap = new Bitmap (stream); Response.ContentType = "image/gif"; bitmap.Save (Response.OutputStream, ImageFormat.Gif); } finally { connection.Close (); stream.Close (); }}what s the problem is.........i'm getting an exception at Bitmap instantiation.(i.e Bitmap bitmap = new Bitmap (stream);)exception is "Parameter is not valid" what is the problem with that coding? was it correct? do u have any code for this in C#? if so, pls provide that......help me........pls.............
I have some VB.NET code to retrieve data from an SQL Server database and display it. The code is as follows: ------------------------------------------------------------------------------------------------------- sw_calendar = calendarAdapter.GetEventByID(cid) If sw_calendar.Rows.Count > 0 Then
lblStartDateText.Text = "*** Not Found ***" lblEndDateText.Text = "*** Not Found ***" lblTitleText.Text = "*** Not Found ***" lblLocationText.Text = "*** Not Found ***" lblDescriptionText.Text = "*** Not Found ***" End If ------------------------------------------------------------------------------------------------------- If all of the fields in the database has values, everything works ok. However, if the title, location or description fields have a null value, I receive the following error message: Unable to cast object of type 'System.DBNull' to type 'System.String'. I've tried a bunch of different things such as:
Adding ".ToString" to the database field, Seeing if the value is null: If sw_calendar(0).description = system.DBnull.value... ...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message. Can anyone help me with the code required to trap the null within the code example I've provided? I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline )
I have tried several times to install Reporting Services 2000. I get 75% done and then receive the following:
Setup Error An error has occured during setup. An error has occured while performing the following setup action. Please select help for more information about this error. You may be able to skip this action by selecting ignore, or retry the action by selecting retry. Selecting cancel will end the installation of SQL Server.
Configuring Microsoft SQL Server
Error Code: 1603
I have followed the resolution and continue to receive the above error. The server is running Windows 2003 standard edition, IIS 6 is installed and working, MDAC 2.8x installed, MS SQL 2000 and service pack 4 installed and working.The domain controller is SBS 2003 SR2.
I have tried several times to install Reporting Services 2000. I get 75% done and then receive the following:
Setup Error An error has occured during setup. An error has occured while performing the following setup action. Please select help for more information about this error. You may be able to skip this action by selecting ignore, or retry the action by selecting retry. Selecting cancel will end the installation of SQL Server.
Configuring Microsoft SQL Server
Error Code: 1603
I have followed the resolution and continue to receive the above error. The server is running Windows 2003 standard edition, IIS 6 is installed and working, MDAC 2.8x installed, MS SQL 2000 and service pack 4 installed and working.The domain controller is SBS 2003 SR2.
Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.
I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.
After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.
Creating a Foreach Loop Container:
1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:ClientsDep CommBEABEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.
I. Created Excel Connection:
Created excel connection called, €œDynamic Excel Connection Manager,€? that initially pointed to one of the excel workbooks. Went to the connection properties by right clicking the connection manager. Expanded Expressions and clicked the ellipsis button to bring up property expressions Chose Connection String in the Property. Clicked the Expression Ellipsis button. Put the following inside the Expression multi line text box: A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties="" + @[User::ExtProperties] + """
Clicked the Evaluate Expression button to get the following:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="" Clicked Ok button Inserted a Data flow task inside the foreach loop container.
II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:
Set the Foreach loop container Delay Validation to true. Set the Data Flow Task Container Delay Validation to true. Set the Dynamic Excel Connection Manager Delay Validation to true. Set the SQL Server Connection Manager Delay Validation to true. Set the Package Delay Validation to true. Package Locale ID set to English
Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.
Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".
I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile]
I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
I ran the package and received the following error in part: OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'E:ClientsDep CommBEABEA_Test_SourcePersonnelExpense_OCCs_051007.xls'."
I did not find anything helpful when I searched for the above errors and would very much appreciate anyone€™s assistance on this issue as this issue needs to be taken care of ASAP.
Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?
Your assistance in this matter is truly appreicated! Thanks!! Lee
I am trying to run a stored procedure which retreives 3 lakhs of records and updates the data and moves few of those records to some tables. Since the number of records are more , the time taken for the storede procedure is around 30 minutes when i directly execute it in query analyzer.
But I need to execute it from Visula Studio.Net 2005 (c#). Whole of the application contains only one form with a single button.When I click on the button , this stored procure has to be executed. But I am getting an error as shown below. "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"
Database used is SqlServer 2000
How to solve this error?. Any ideas are really appreciated.
I am trying to set up an Execute SQL Task that will feed into a foreach loop for processing of data. I am using an ADO.NET Connection with Direct input with full result set. I have tried using "select * from <table>" and also "select <column1>, <column2> from <table>". I have user variables set in the package and have the table cfolumn names listed in result name and the appropriate package variable in variable name on the Result Set tab. There is a total of 53 columns per row and the number of rows that should be returned can be anywhere from none to several hundred. Upon trying to run the package, I am receiving Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
I downloaded this program, and when trying to intall I am receiving an error meesage that there is insufficient disk space to install.
I have 98GB available on C and 107 GB available on D. I am on an IBM Intellistation dual processor 3,08 Ghz with 4GB RAM and 240 GB Disk (those are "G"'s), so I have tons of capacity. This has to be a problem with the .msi - any suggestions?
Using the following code we are having a problem connecting a clients WIndows Mobile device to the SDF under IIS. We have success elsewhere. Does anyone have any clues ?