Sproc To Determine If Query Will Produce Invalid SqlNotificationEventArgs Event
Feb 7, 2007
I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.
I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...
exec sp_QueryInvalidForNotification "exec myTestSproc 99"
Does such a sproc exist? Or does anyone know how to write one?
View 1 Replies
ADVERTISEMENT
Feb 18, 2007
Hi I have a gridview that is being populated from a method that gets it's data from a table view.
SELECT dbo.cis_AlumniContact.Street, dbo.cis_AlumniContact.City, dbo.cis_AlumniContact.State, dbo.cis_AlumniContact.Telephone, dbo.cis_AlumniContact.Occupation, dbo.cis_AlumniContact.Description, dbo.cis_AlumniContact.Zip, dbo.cis_AlumniContact.FirstName, dbo.cis_AlumniContact.LastName, dbo.cis_AlumniContact.YearGraduate, dbo.cis_AlumniContact.Email, dbo.cis_AlumniContact.Contact, dbo.aspnet_Users.UserName, dbo.cis_StudentId.UaaStudentIdFROM dbo.aspnet_Users INNER JOIN dbo.cis_AlumniContact ON dbo.aspnet_Users.UserId = dbo.cis_AlumniContact.UserId INNER JOIN dbo.cis_StudentId ON dbo.aspnet_Users.UserId = dbo.cis_StudentId.UserId
No big deal, works great. Now when I click update I call this method
<DataObjectMethod(DataObjectMethodType.Update)> Public Sub UpdateAlumni(ByVal Street As String, ByVal City As String, ByVal State As String, ByVal Telephone As String, ByVal Occupation As String, ByVal Description As String, ByVal Zip As String, ByVal FirstName As String, ByVal LastName As String, ByVal YearGraduate As String, ByVal Email As String, ByVal Contact As Boolean, ByVal original_UserName As String, ByVal UaaStudentId As String)
Try
Dim connx As New SqlConnection(getConnectionString)
connx.Open()
Dim sqlCmd As New SqlCommand("cis_UpdateAlumniContact", connx)
sqlCmd.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NVarChar))
sqlCmd.Parameters("@UserName").Value = original_UserName
sqlCmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar))
sqlCmd.Parameters("@FirstName").Value = FirstName
sqlCmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar))
sqlCmd.Parameters("@LastName").Value = LastName
sqlCmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar))
sqlCmd.Parameters("@Email").Value = Email
sqlCmd.Parameters.Add(New SqlParameter("@Street", SqlDbType.NVarChar))
sqlCmd.Parameters("@Street").Value = Street
sqlCmd.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar))
sqlCmd.Parameters("@City").Value = City
sqlCmd.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar))
sqlCmd.Parameters("@State").Value = State
sqlCmd.Parameters.Add(New SqlParameter("@Occupation", SqlDbType.NVarChar))
sqlCmd.Parameters("@Occupation").Value = Occupation
sqlCmd.Parameters.Add(New SqlParameter("@Description", SqlDbType.NVarChar))
sqlCmd.Parameters("@Description").Value = Description
sqlCmd.Parameters.Add(New SqlParameter("@Telephone", SqlDbType.NChar))
sqlCmd.Parameters("@Telephone").Value = Telephone
sqlCmd.Parameters.Add(New SqlParameter("@Zip", SqlDbType.NChar))
sqlCmd.Parameters("@Zip").Value = Zip
sqlCmd.Parameters.Add(New SqlParameter("@Contact", SqlDbType.Bit))
sqlCmd.Parameters("@Contact").Value = Contact
sqlCmd.Parameters.Add(New SqlParameter("@YearGraduate", SqlDbType.NVarChar))
sqlCmd.Parameters("@YearGraduate").Value = YearGraduate
Dim cmd As SqlDataReader = sqlCmd.ExecuteReader
Catch ex As Exception
Dim er As New cis_ODS_Error
er.InsertError("cis_ODS_Alumni - UpdateAlumni: " + ex.Message.ToString)
End Try
End Sub
The sproc it calls is:
ALTER PROCEDURE dbo.cis_UpdateAlumniContact
@UserName as nvarchar(50),
@Street as nvarchar(50),
@City as nvarchar(50),
@State as nvarchar(2),
@Telephone as nvarchar(50),
@Occupation as nvarchar(50),
@Description as nvarchar(50),
@Zip as nvarchar(50),
@FirstName as nvarchar(50),
@LastName as nvarchar(50),
@YearGraduate as nvarchar(4),
@Email as nvarchar(50),
@Contact as bit
AS
UPDATE cis_AlumniContact
SET Street = @Street, City = @City, State = @State, Telephone = @Telephone, Occupation = @Occupation, Description = @Description, Zip = @Zip, FirstName = @FirstName, LastName = @LastName, YearGraduate = @YearGraduate, Email = @Email, Contact = @Contact
FROM aspnet_Users INNER JOIN cis_AlumniContact
ON cis_AlumniContact.UserId = aspnet_Users.UserId
WHERE @UserName = aspnet_Users.UserName
RETURN
I get this vague error
cis_ODS_Alumni - UpdateAlumni: Incorrect syntax near 'cis_UpdateAlumniContact'
If I execute the SQL from the editor it works fine. The only thing different about this sproc and my other update sprocs is the inner join. Any idea? Thanks
View 2 Replies
View Related
Jul 19, 2015
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
View 3 Replies
View Related
Nov 2, 2007
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
View 4 Replies
View Related
May 15, 2006
Hi Everyone:
*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error
"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I would have searched the forums myself, but at this moment in time, search is broken :(
If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.
It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.
fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.
Thanks in advance for other suggested examination paths.
View 10 Replies
View Related
Jan 24, 2014
I am trying to add to a query to produce a report in Apex. Currently I have this
SELECT
RE_EZPAY_AT_AGENCY_DAILY.WAITING_TO_BILL AS "WTB",
VE_MEMBER.TITLE,
VE_MEMBER.FIRSTNAME,
VE_MEMBER.SURNAME,
VE_MEMBER.ADDRESS_1,
[Code] ....
I want to add 'OR FOLLOW_UP_DATE <= SYSDATE'. How do I add another OR statement?
View 3 Replies
View Related
Aug 12, 2014
join three tables and wont be duplicate records.
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
[Code]....
View 4 Replies
View Related
Mar 16, 2007
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
View 1 Replies
View Related
Jun 4, 2015
I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U') from the above I need following output (attached output),
View 3 Replies
View Related
Oct 21, 2013
Aim –To produce a unique line by month for the FDMSAccountNo
I have created the following query (see below) which produces the following result set
FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000
SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate
[Code] ....
Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08
[Code] ....
View 1 Replies
View Related
Nov 18, 2015
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
View 9 Replies
View Related
Jan 31, 2008
Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location
location shows the dif cities and the sql query will show cities will a count of people per city
how do you do this?
View 3 Replies
View Related
May 24, 2007
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
View 5 Replies
View Related
Sep 17, 2005
I am trying to determine which of three stored procedure designs arefastest in the Query Analyzer:One query is a straight SELECT query with all desired rows and a dozen(tblName.RowName = @param or @param = Null) filters in the WHEREstatement.One query populates a #Temp table with the UniqueIDs from the resultsof the SELECT query in the above example, then joins that #Temp tableto get the desired rows.One query users EXEC sp_executesql @sql, @paramlist, @paramin which the @param has the dozen filters.What I'm trying to determine is which is the fastest.Each time I run the query in Query Analyzer it returns the samerecordset (duh!) but with much different Time Statistics.Are the Time Statisticts THE HOLY QRAIL as far as determining which isfastest, and what so I want to look at, the Vale or the Average? Inotice there are different numbers of bytse sen and bytes received foreach of the three queries.Any illumination on this is appreciated.lq
View 4 Replies
View Related
Jun 3, 2008
When i try to run this sproc no rows are are returned but if i give PlanId = 898 and PortfolioId = 1913 i should get one row.. This is my sproc
ALTER PROCEDURE [dbo].[rpt_Custom_AllocationsSub]
(
@PlanId int,
@PortfolioId int
)
AS
SELECT
c.PlanId,
c.PlanName,
pp.PortfolioId,
pp.PortfolioName,
pp.PortfolioDescription,
f.FundId,
p.displayOrder,
case When pf.PlanFundDisplayName IS NULL THEN f.ShortName ELSE pf.PlanFundDisplayName END FundNames,
CAST(p.AllocationPercent AS integer) AS PPF_Percent
FROM
Fund f
INNER JOIN PlanPortfolioFund p
ON f.FundId = p.FundId
INNER JOIN PlanFund pf
on f.FundId = pf.FundId
RIGHT OUTER JOIN [ClientPlan] c
INNER JOIN PlanPortfolio pp ON c.PlanId = pp.PlanId
ON p.PortfolioId = pp.PortfolioId
WHERE
c.PlanId = @PlanId
ANDp.PortfolioId = @PortfolioId
AND pf.PlanId = pp.PlanId
order by p.DisplayOrder
This is my table structure
ClientPlanId
PlanId int indentity
PlanFund -- PlanId int, FundId int
PlanPortfolio -- PortfolioId int, PlanId int, PortfolioName varchar , PortfolioDescription varchar
PlanPortfolioFund PortfolioId int, FundId int, AllocationPercent int.
any help will be appreciated...
Thanks
Karen
View 2 Replies
View Related
Mar 4, 2007
I have a query that works fine but fails as a sproc.
QUERY:
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = 'Brown'
SPROC:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetProfileId]
@UserName nvarchar
AS
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = @UserName
The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0
View 2 Replies
View Related
Feb 12, 2008
Hi, I'm sorry if this is the wrong place to ask this question. These forums are overwhelming and I couldn't find the appropriate place to post.
I am wondering if there is some way to determine when a query has completed. What I want to do is use a PleaseWaitButton in asp.net when the user clicks to run a query and have the button reactivate after the query has completed. I know how to set a specific wait time but I do not want the user to have to wait a default time for the query to complete. Any help you may provide would be greatly appreciated.
Thanks!
View 2 Replies
View Related
Mar 6, 2008
I need a query that gets all of the databases on a server and determines if a backup has been taken. I would normally use the jobs query I currently have, but it wouldn't tell me when the last backup was taken for a new database. I think I should be able to use sp_helpdb or some table from the msdb database. Also, this has to work on SQL Server 2000/2005. If anyone has anything I'd be very grateful. Thanks in advance.
-Kyle
View 2 Replies
View Related
Mar 7, 2001
Hi all,
I am looking for a query to determine the exact size of a large table.
Thanks
JJ
View 1 Replies
View Related
Oct 17, 2006
Hi.
I have a VB.NET function that returns an array of Integers.
Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54]
One characteristic of the array is that no two numbers
repeat - it reflects the IDs of my Users table. And, that
it is not ascending or descending.
What I would like to know is how do I sort my query in
the order of the integers in this array? Ideally, I
would like to use ORDER BY for this query.
thanks in advance.
View 2 Replies
View Related
Aug 30, 2007
Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle
View 2 Replies
View Related
Sep 22, 2015
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
View 2 Replies
View Related
Oct 19, 2007
Hey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access. The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!
View 2 Replies
View Related
May 12, 2015
Given the data below, I have a couple needs:
1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)
2) Query to determine if any date ranges of the same category overlap
declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'
[code]....
View 7 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Jun 2, 2015
Recently we migrated our environment to 2012.
We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.
Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.
I have gone through so many websites but i never find.
View 13 Replies
View Related
Oct 25, 2011
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM
Log SQL Server (Current - 10/25/2011 10:55:00 AM)
Source spid
Message
Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
View 2 Replies
View Related
Apr 8, 2008
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file.
The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30
WHERE targetinstance isa 'CIM_DataFile'
AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message.
However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
View 2 Replies
View Related
Feb 13, 2004
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
View 3 Replies
View Related
Feb 16, 2004
I am looking for a way to log all events that occur on the server. I have used quary profier and that gets me the info i need but is there a way to truncate the file, say only log the last 30 minutes of activity and having it rolling so that it is always the last 30 minutes. Or is there some other software that will do this. The database times out at random times and I am trying to fix the problem but dont want to keep houndreds of log files that query profiler will create by leaving a trace running 24/7. Any suggestions.
Thanks
Mark
View 5 Replies
View Related
Jun 21, 2006
Hi,
We have 8 SSIS jobs which need to be made dependent on the arrival of a single file.
All the flows need to be triggered based on the presence of a file
Whats the way to do this
Thanks
Manish
View 1 Replies
View Related
May 31, 2007
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup)
We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers.
We were having
Event Type: Failure Audit
Event Source: Security
Event Category: Object AccessEvent ID: 560
Date: 5/23/2007
Time: 6:27:15 AM
User: domainuser
Computer: MACHINENAME
Description:
Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,1623975729}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: Domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: User
Client Domain: Domain
Client Logon ID: (0x0,0x6097C608)
Accesses: READ_CONTROL
Connect to service controller
Enumerate services
Query service database lock state
Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015
Applied the following fix
http://support.microsoft.com/kb/907460/
Now we are getting
Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: user
Client Domain: domain
Client Logon ID: (0x0,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D
View 4 Replies
View Related
Oct 30, 2007
I have the following table called BADSANTA:
varchar(30) datetime
NAME WHENBAD
OJ Simpson 2007-1-12 xx:xx:xx:xxx
OJ Simpson 2007-4-2 xx:xx:xx:xxx
Monica Lewinsky 2006-7-4 xx:xx:xx:xxx
Monica Lewinsky 2006-10-31 xx:xx:xx:xxx
Bill Clinton 2006-7-4 xx:xx:xx:xxx
Bill Clinton 2006-10-31 xx:xx:xx:xxx
Bart Simpson 2006-11-2 xx:xx:xx:xxx
Bart Simpson 2006-2-25 xx:xx:xx:xxx
Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
Thanks in advanced.
View 4 Replies
View Related
