We're looking to send messages to an SSB Queue (our 'Notification' queue) that contains multiple values.. like Message #1: 20,1 to indicate Record #20, send Notification #1.. the reason for this is we have lots of different types of records in our database, and based on certain conditions, we would like to send different notices regarding these various record types.
What is the best format to send mesages like this? We thought about sending TABLE vars.. what other options are there?
Any ideas on how I can send multiple values from a listbox to a stored procedure? right now I have a ListBox Control called lbCategory, and I want to pass multiple selected items to a stored procedure. <asp:SqlDataSource ID="dsFS" runat="server" ConnectionString="someConnectionString" SelectCommand="usp_FS" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="lbCategory" DefaultValue="%" Name="category" PropertyName="SelectedValue" type="String" /> </SelectParameters> </asp:SqlDataSource>
Hi, after some tooling round, googling, hair-pulling etc i have some prototypes of a simple C#.NET 1.1 console app which can now post a message to my yukon SB service i set up. It calls an internal activation stored proc to store the message in a simple table. The problem i had was in the connection string "... Asynchronous Processing=true". The run-time throws an exception moaning about the Asynchronous Processing=true bit. So i just omitted it completely... Seems to work fine and as expected. I can post a message, my app exits, and the dummy stored proc (which i deliberately made enter a loop updating the table thousands of times to slow it down and lock the table) completed several seconds later proving the asychronousity of it (i issued a "select count(*) from X" query to wait for the write lock to be released, so i knew when the stored proc had finished and released its lock).
So, i guess my question is, firstly what is the "Asynchronous Processing=true" bit for in the connection string if we can just leave it out? And can anyone see any issues with what ive done?
I was wondering if there are any potential issues/drawbacks of a service sending a message to itself. Basically I have a situation where I only require one queue to hold messages. So say for instance I had a service named S1 associated with a queue named Q1 then I can do this:
Code Snippet
Begin conversation dialog @dialog_handle
from S1
to 'S1'
on contract MyContract
with encryption = off; Send on conversation @dialog_handle message type MyMessage
Yesterday I made the HelloWorld_CLR sample work and figured today I would create a simple windows service to immulate its functionality. When this was not working I tried the sample again and it stopped working. I have tried several things and nothing has worked. Has anyone seen this before or have any ideas on how I can get it working again? I even tried removing SQL Server and its samples then reinstalling to give me a clean slate but that did not work. I did not make any changes to the software nor did I make any changes to the database however the sample did so I need to make sure I can fix this incase it happens in development not to mention I need to continue development.
The Client Queue is normally empty but sometimes has a request to two in it. The Server Queue has alot in it that are both Request and an unspecified type or has a URL for a type. I would paste it in but it takes too much space. Please let me know if you have any ideas because I am not sure what else to try.
Bellow is a sample of the output from running this sample.
Connecting to SQL Server instance Connected to SQL Server instance
Transaction 1 begun Dialog begun from service (HelloWorldClient) to service (HelloWorldService) Message sent of type 'Request' Transaction 1 committed
Transaction 2 begun Waiting for Response.... No message received - Ending dialog with Error Transaction 2 committed
Hello, I've been banging my head trying to figure this one out so I appreciate any help you guys can provide.
I've setup and configured SQL 2005's Database mail and have sent a few plain and simple text emails and I receive it fine at the destination.
I now want to send mime messages but I'm not sure what I'm doing wrong. I took the following source from an email that linked-in sent me which was a mime message: "
Mime-Version: 1.0
Content-Type: multipart/alternative;
boundary='----=_Part_434343432'
------=_Part_434343432
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Plain Text Goes Here
------=_Part_434343432
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<html>
<body>
<b>HTML Goes here</b>
</body>
</html>
------=_Part_434343432--
" and put the above text in the @body parameter of the stored proc msdb.dbo.sp_send_dbmail and submitted it.
When I received the email, it didn't appear as html. Instead, I just got the mime message above as plain text in my email as if someone just typed it and sent it to me. What do I need to do for SQL Database Mail to send the above as a MIME message?
After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues. Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.
We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?
Thank you very much,
Lubomir
P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;
I have a SS2K5 stored procedure that executes 2 others stored procedures sp_zero1 and sp_zero2 sp_zero1 and sp_zero2 do the same thing ... raises an Divide by zero error I need to (print / select into a database) both error messages using just one try catch block instead of 2 blocks like in the next example:
-- THIS IS THE WORKING CODE THAT I DONT WANT BEGIN TRY exec sp_zero1 END TRY BEGIN CATCH PRINT ERROR_MESSAGE() PRINT ERROR_PROCEDURE() END CATCH BEGIN TRY exec sp_zero2 END TRY BEGIN CATCH print ERROR_MESSAGE() print ERROR_PROCEDURE() END CATCH
if I try the next code
--THIS IS THE NON WORKING CODE BEGIN TRY exec sp_zero1 exec sp_zero2 END TRY BEGIN CATCH print ERROR_MESSAGE() print ERROR_PROCEDURE() END CATCH
only the first error message is printed and the execution is stopped
This is a generic example ... in reality I have a stored procedure that executes tens and hundreds of other stored procedures ... so thats the reason I need just one block of try catch instead of hundreds of blocks
I need to send multiple emails to recipients one at a time what is the best way to do this?for example i do a select statement to get 5 email addresses, now i need to be able to fire off an email with verbiage to each one of them separately. The reason they have to be separate is in the verbiage i need to have there name in the 'Dear Jondoe1,' Here is my second dilemma. How would you insert a name in the verbiage for each email?
Can anyone outline some best practices for sending the same message out to multiple servers? What is the easiest way to do this?
Currently, I have a message sent from Server A to Server B, which is fired by a table insert, update, delete trigger.
The goal is to keep this table synchronized on many remote servers. It sounds like a tedious exercise to deploy the SSB messages, queues, services, etc to 20+ more sites! I am hoping there is a shortcut of some kind.
I have a view that retrieves the details for sys.messages in SQL Server Express and noticed that the language ID is 1033 (U.S. English). I will need French and Portuguese, and understand that these are standard. Is there a set of DDL Scripts that will install the sys.messages for these languages?
If I send multiple sql's with ado.net in one statement (one executeSql separated by semilcolons), and the second one fails, will the first one be rolled back? or do I need to put it all in a transaction?
I have a procedure where after processing, i am required to send multiple message to calling application.
For ex:
create procedure test as (@a as int, @b as int @c as int ) /* some transformations */ print 'variable a is' + @a; print 'variable b is' + @b; print 'variable c is' + @c;
I am only providing a sample of return messages, but in reality there a lot more messages where a string and a parameter value need to concatenated.Unfortunately print is not allowing to concatenate parameter value.I can use RaiseError, but these messages are not really any error messages.
i tried to concatenate all message and output it using OUT type parameter, but the length of all messages combined exceeds 10000 characters.
Is there any other alternate to send these messages out to application?
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage
end close emailCursor deallocate emailCursor
Currently I have two users in my table that I want to send email to, but when the stored procedure runs I got two emails for the last user in the table instead one for each user.
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
If I have a Select statement like this in my C# code: Select * From foods Where foodgroup In (@foodgroup) And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter? I tried meat, dairy, fruit 'meat', 'dairy', 'fruit' but neither worked. Is this possible?
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
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
itemnum - over 16000 records closedate - can be 2001, 2002 location - is 1, 2, 3 etc
Like to find itemnum where location is 1 and itemnum has closedate of 2001, if it does have close date of 2001 than check if another record of same item with closedate of 2002. If yes, than diplay that item.
The result would be itemnum which have closedate of 2001 as well as 2002. Each item may have more than one record for each year.
Hi. I;m trying to sort out some values. Ok so for sort. We have a contract and a renewal column. A contract can have many renewals. I'm trying to get the latest renewal for each contract (latest renewal is the one with the higher number.
so im trying this..
select distinct c.policyno,c.renewalno from CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID where c.renewalno in (select max(crn.renewalno) from contract crn where c.id= crn.id
But i keep getting multiple results (p.e. contra 12 ,renewal 13,14,15 and i want contract 12, renewal 15) Any help?
hi everyone how do i insert multiple rows in a database ? i came up with something like this after googling but it does not work INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')
Hi there I have an exel spreadsheet with a very long list of towns. How can I import/insert that into my "Towns" table in sql express? I can't seem to find any way to import it and I'm not sure how to do multiple inserts. Thanks
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
i have about 2,000 record and i need to insert them in my table. how do i insert these informations using this syntax?? au_id au_name au_fname 1003 vivian latin 1005 cecy mani 1004 bili david
insert into autors (au_id, au_name,au_fname) Values ('1101', 'Rabit','jesicca')
