What is the common mechanism (if any) to make the server notify a
client when some condition is met (say, upon completion of a
sql-transaction)? The only way to implement this feature I am aware of
now is to set up a timer and query the server every once in a while
but surely this technique isn't much good.
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
View 1 Replies View RelatedDoes anyone have any idea how I would go about creating an object that can be informed of any modifications to a SQL data base. i.e. a .NET object that can detect whenever a table is updated.
It is NOT possible to modify the database in anway, so I can't add triggers etc.
Hi,
I have a Windows application in C# and a SQL Server 2005 having a table "Notifications" in a DB.
This table is being updated at regular intervals and i want that my application should get a notification for this everytime a Record is inserted, updated or deleted.
How this can be achieved ?
Thanks
Kulvinder Singh
Hey all - been knocking my head on this on for awhile - perhaps someone could shed a little light.
I have 2 tables that I want to link:
Table 1:
UserID - Primary key
First Name
Last name
AuditionID - FK
Table 2:
AuditionID - PK
Description
Sing
instrument
So - I have a web form that collects the information for both tables and then insets the collected data. But, the AuditionID value in Table1 does not update. Now, I assume I am incorrect in thinking the AuditionID in Table1 will be automagically updated when Table2 creates it's key...?
If that is indeed the case, how do you update 2 tables and maintain a foreign key relationship?
I hope I am being clear enough - any help would be greatly appreciated.
Thanks all
Hello,
I'm running a (phpBB) forum which uses a MYSQL data base - I can access the database via a webpage through my ISP and it lets me run short scripts (through a web interface). I need to do the following (shown in psuedo code below). I would be eternally grateful if someone could translate it into pure SQL commands for me to paste into my SQL quiery input field on the web page I'm using to access my database. (I'm a complete SQL novice)
=====================
/* I need to be sure these commands will do nothing if 'jeff' does not exist in the user table */
/* extract the id value for the user called 'jeff' from the users table */
user = GET id FROM users WHERE username = "jeff"
/* remove all entries from the forums_watch and topics_watch tables that contain Jeff's userID */
REMOVE ALL ENTRIES WHERE userID = user
notify = 0;
/* insert many entries into the topics_watch table, one for each topic that exists in the topic table */
arrayOfTopicIDs = GET ALL id FROM topics
FOR EACH current_Topic IN arrayOfTopicIDs {
INSERT INTO topics_watch
(topic_id, user_id, notify_status)
values (current_Topic, user, notify)
}
/* insert many entries for jeff into the forums_watch table, one entry for each forum that exists in the forum table */
arrayOfForumIDs = GET ALL id FROM forums
FOR each current_Forum in arrayOfTopicIDs {
INSERT INTO forums_watch
(topic_id, user_id, notify_status)
values (current_Forum, user, notify)
}
=====================
I'm going to implement this with php and MYSQL as a mod for my forum application (phpBB) - but I could really do with a way of doing this immediately with pure SQL commands as a quick and dirty one shot paste into the input field of the MYSQL database web interface until I get the php mod going.
Can this be done using only SQL commands without tedious repetition?
Best Regards,
Alan
How do you work with an xml file that resides in a database column using IS XML related features? From what i understand so far, the path cannot point to a column within a database but only to a source outside of the database. Am I correct?
View 1 Replies View RelatedHi
How to create SMTP notifications using SQL Server 2000?
could you please gimme some usefull URLs.
Thanks & Regards
On Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
Hello,
I am asking a question i have seen many threads on, but I am looking for an idiot's guide on how to convert my SQL 2005 database to SQL 2000 so i can get it to run on my web hosting server. I'm very new to asp.net , but have ahd years of experience in normal HTML and a year or two in the old ASP.
I was advised to learn ASP.Net 2.0 and have found it nothing but brilliant. The intergration with SQL 2005 made it a lot quicker to link up a database than using Access. Unfortunatly my hosting company is a little behind and still using SQL 2000. There isn't mch databse intergration (a few aplication forms) so I dont mind re-writing the whole database but I dont know how to set Visual web developer up with a SQL 2000 Database. I have also read from various other forums that you can convert a databse to 2000 by doing something with the scripts, but the explaination is too complicated for me to follow.
Is there anyone out there who wouldn't mind going over some old ground and explain this all in simple terms? Im using 'SQL Server Managment Studio Express' (although i dont know how to use it) and 'Microsoft visual web developer 2005 express edition' .
Thanks for reading this
Simon
I'm sure this problem borders on retarded for most readers of this form, but here it is:
I have table with contest and another table with user ratings on those contests.
Contests: ContestID, CreatorUserID, Title
Ratings: ContestID, UserID, Rating
If I
SELECT Contests.*, Ratings.*
FROM Contests INNER JOIN Ratings ON Contests.ContestID = Ratings.ContestID
WHERE (Ratings.UserID = '1')
This only returns the contests that User 1 has rated. But I need all contests. If the user hasn't rated one, I want the result set to show Rating = NULL. The problem is that if a user hasn't rated a contest, that ContestID does not exist in the Ratings table.
Can someone please show me how to solve this?
Thanks!
Gavin
seems like it should be a simple ALTER TABLE query but im getting a 'Incorrect syntax near the keyword 'column'.' error. Trying to add a boolean column, heres the script, any help would apprectiated.
ALTER TABLE SHIPPING_ADDRESS
ADD COLUMN isUpdated bit,
thanks.
matt
We have SQL Server 2000 Standard and recently moved from Exchange 5.5to Exchange 2003. This problem occurred both before and after thee-mail change.We would like to send notifications of failed jobs to multiple peoplein case one person happens not to be in on the day there is a problem.Setting up an operator with an e-mail name that combines twoaddresses seems to work ok as long as we use a format like thefollowing:smith_john; jones_maryorJoin Bytes!; Join Bytes!However, when we add a third name, we start getting problems. Thefirst and third accounts listed get the message but the second onedoes not -- apparently because the address gets corrupted. Forexample (based on the message received at one of the good addresses),a message sent to:Join Bytes!; Join Bytes!;Join Bytes!goes to Smith and Jackson successfully but Jones' address is corruptedto:Join Bytes!omand, of course, fails.From some other testing, it appears that the last three characters ofthe first address always get appended to the end of the secondaddress.Has anyone else run across this? If so, is there a known fix? I hadhoped Exchange 2003 would fix the problem. Most likely, we will trysetting up a personal distribution list under the account from whichthe messages are sent. I just wanted to give one more shot to findingthe problem -- both because I would like to know and because beingable to string together addresses is a little easier than having to gothrough the person who can give us access to the account to set thePDL.Thanks.Jonathan LevyChicago Department of Public Health
View 1 Replies View RelatedHello,
I'm creating a website for a student organization at my university. Basically, I have an Excel file with a list of contacts that the members of our organization need to be able to search on our website.
The only field that I need our members to be able to search is "contact location." So for example, a member might want to know if our organization has any contacts in Spain, so the member would type "spain" into the "contact location search" field" and would get results.
I really don't known where to turn... I need the easiest thing that would be manageable for a novice. It doesn't need to be an intense search function; just something to make the website functional (so it obviously needs to be uploadable to a server; the server has PHP capability...no idea if that has any relevance). I've been searching around all day and have come across potentials like PHP, SQL, XML, etc. but have no idea which way to turn... I have Office and Adobe Studio programs at my disposal.
Thank you all-powerful and all-knowing web gods! I owe you my deepest gratitude!
Mikey
:beer:
I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.
Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime
exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body
I've heard that the Standard version supports the use of CommandNotification and that the Express version does not. What about the Workgroup edition?
The reason I'm asking is because there is a substantial monthly difference in cost between the two versions if I were to have my site hosted. I've been developing with MS SQL 2005 Express so obviously I have not tested this caching feature. Are command notifications reliable? On paper, it sounds impressive and extremely efficient especially if a site is to have heavy traffic and you can't afford the overhead of heavy database processing that is useless if the data is unchanged.
Does the Workgroup edition support automatic change notifications? Have automatic notifications been proven reliable and vastly more efficient than crude polling methods?
Thanks in advance.
What is the best way to get notification for a sql server 2005 job that runs longer than certain time.
View 5 Replies View RelatedWe have 15 clients running our applicaton
14 of then conected to SQL server using TCP/IP and it runs fine
1 of 15 when connected using TCP/IP produce "..Time out error "
but runs fine when swiched from TCP/IP to Named pipes
1.What area should we look to correct problem with Time out using TCP/IP ?
2. Where to get information about using TCP/IP via Named pipes ?
Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
View 78 Replies View RelatedHi,
I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.
I receive this error :
SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools
The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?
In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !
Thank you
Is there a way to send a net send notification to more than one user? I can send to one successfully. Thanks.
View 1 Replies View RelatedI have set up notifications to be sent if a job fails. however, nothing is sent
If I go to Job History, I can see the yellow envelope under "notification" but I receive no email I have set up an Operator and can send email using "Send Email" but it doesn't work!
1. SQL Server - Management
2. SQL Server AGent
3. Go to Operators
4. I can set up a new operator by right-clicking "New Operator"
5. In Name, I have "John"
6. After "E-mail Name", i click in the 3 elipses, which brings up my address book in Outlook. I will select, let's say "John Smith"
7. When I click Test, it says "message was sent successfully" but I get no email
8. However, if I go to Outlook and send to "John SMith", it will send
Hi
How to setup mobile notifications on jobs using SQL Server 2000. Aim is when a job fails I should receive SMS notification on my mobile phone.
Please show me web sites on this topic.
Please help.
Thanks & Regards
Raj.
Hi
How to Create SMTP Notifications using SQL Server 2000.
Thanks & Regards
-Raj
Raj.
Some packages I develop have a lot of steps, and some people like myself can go crazy with email notifications, depending on the graph, I sometimes add notifications (success/fail) at the component level, so I can know what happened to that SSIS package from my blackberry and immediately log into the VPN and fix the issue.
The only problem that I have with that is that sometimes, like right now, I see myself adding 6 "send mail tasks" to SSIS packages, one pass/fail for each component.
Basically, I was wondering if anyone had any ideas or suggestions, ideally I wish I could create one single send mail task that could be reused by other components on the package and just update the message/subject through expression and variables.
Does anyone know how to send SQL agent job failure notifications to more than one operator??
Thanks!
Hello,
We are building a simple distributed application around the service broker where each queue is serviced from a windows service. We have a windows service running constantly as a "listener" on an event queue for each of what we call "request" queues. When a message is received by the request, the listener service instantiates a reader to process the request.
As we are in development, things get out of sync at times and the event queues sometimes stop getting the event notification message. I can correct this usually emptying the queues by receiving all messages from the "request" and "event" queues but sometimes it becomes necessary to drop and recreate the broker objects. Can someone tell me what are the fail points event_notification and what corrective actions are needed at these points?
For instance, if I see that a queue has a state of "Notified" in dm_broker_queue_monitors, I just do a receive to drain the appropriate queue. I have been unable to determine what state the service broker is in when I have to delete and recreate the event notification so I don't know what is causing it and how to programmatically determine when it is necessary to take this action. We would of course prefer not to get to this state but since it can happen during development, I would guess this can happen in production so I need to develop the recovery logic to detect and correct for this condition.
Jim
Hi
I would like to know how I can get hold the text for a service and event notifications.
What I need is the ability to get hold of a list of the events I'm currently monitoring.
i.e.
Server or database level
I though I would be able to get hold of this in sys.server_event_notifications but it doesn't seem to have any data although my server level event notifcation service is running.
Any help?
TIA
I'm beginer with service brooker and i wandering if someone could help me to clarify what would be the proper usage of service broker for my current situation...
Suppose i have 3 tables: Customers, Orders and Providers. I would like my c# application to receive a notification for any data change on any of these tables. I know i could use my 3 tables in a SELECT statement for a global event notification but i need to know from wich table change occured.
I also though to create 3 separate event notification but i'm really not sure...
What is the best approch to acheive this??
Any help would be really appreciated
All,
I have Mirroring up and running successfully on two databases. The platform consists of Windows Server 2003 Enterprise Edition X64 (Build 3790: Service Pack 1) and SQL Server Enterprise Edition X64 (Build 9.00.3200.00). I set up alerting for unsent logs per the Alerting Mirror Events article:
http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx. The alerts show up in the sql server logs as intended and work fine up to that point; however I have the alert set to notify an operator that emails and pages and that functionality does not work. The operator is used for other alerts and works fine i.e. the email address and pager address is correct. The pager on duty schedule is correctly set up as well. I was wondering if anyone has come across this problem after setting up Mirroring. It appears to be an isolated issue related only to Mirroring.
Thanks in advance.
Hello,Is it necessary to upgrade the Client Connectivity Tools on all clientmachines after the SQL Server database server is upgraded from Version7.0 to 2000?Thank you in advance!Eddy
View 1 Replies View RelatedI am finding conflicting information about enabling query notifications for SQL Express. One book says it can be done and shows an example but most places on MSDN claim it requires the Service Broker. I am using Management Studio for SQL Express which does not show the Service Broker while an installation with SQL Server 2005 Dev Edition does show the Service Broker.This is all very confusing. I want to be able to develop locally with SQL Express with Query Notifications and later deploy to a server which is more capable. Previously I have read that only real difference between SQL Server 2005 and SQL Express is a 2gb memory limit.How would I go about setting up Query Notifications for SQL Express? Where would I find such documentation on MSDN?
View 1 Replies View RelatedHello all,
Here's the problem I'm trying to solve:
I have several alerts set up and a job setup to notify via email if alerts occur. There are times when I might be doing maintenace on a box that may generate alerts but I don't want to get emailed or paged for these "dummy" alerts. I thought first of setting the enabled column of sysalerts to 0 - that doesn't work. I still get notified. Then I thought I'd set the enabled column in sysjobs for my notification job to 0 while I'm doing the work. But as soon as I re-enable the job I get an alert. It seems that as soon as the job is re-enabled it checks the NT application log and sees that an alert was fired and somehow knows that there was no notification sent so sends one. If I purge the NT application log before re-enabling the job is solves the problem. But that is a very in-ellegant solution and I don't want to loose all the info stored in the event log any way. Does anyone know how SQL SErver aggent keeps track of this stuff? (ie. that an alert was fired but it's response job is disabled and to check once a job is re-enabled to see if any alerts occured while it was disabled?) I did a Profiler trace while re-enabling the job but didn't see anything pertinant in there.
Thanks in advance.
Patrick