Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Prakash9404
Starting Member
1 Post |
 Posted - 2010-12-28 : 09:54:59
|
| Hi,I have one table PLOG in my database which has SQL Dependency notification ON. Now I have a windows service running which takes data from another table TRAN in the same datababase and populates the PLOG table. There is one more Windows Service running on the same machine which processess the records in PLOG table once inserted, this windows service uses the SQL Dependency to check if a new record is inserted in the Table.The Issue that I am facing is If the sqldependency works the first windows service cannot insert records into the database I receive below error-1 (SQL_STATE) Action(Native: 0) - SQLExecDirect 37000(Native: 1934) - [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PSet Options which are set to True for Database are ANSI NULL AND NUMERIC ROUND ABORT.Now if I Set option in Numeric Round abort for the database dependency starts working but record insertion into the table fails.Below are the queue and service scripts---service brokerCREATE SERVICE [NotificationService] AUTHORIZATION [dbo] ON QUEUE [dbo].[NotificationQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])--notification queueCREATEQUEUE [dbo].[NotificationQueue] WITH STATUS = ON , RETENTION = OFF ON [Group00]I have tried this for multiple combinations of the SET options on the DB as well as on the query but to no avail. I even tried with the Stored Procedure with proper SET options but no luck.The windows services running use inline querries to query the database so I am left with the tables and Database set options.Thanks,Prakash |
|
|
|
|
|
SQL Dependency not firing