By Database is heavily used , we use Transaction log backups ever 10 minuts, now someone fired a wrong query , that updated a particular table , the DB is still in use, there are some users working on it , I cant get the DB offline , what can I do to correct the mistake , please advice.
I am investigating using Query Notifications - a great addition BTW. I have built a test app - loosely based on MSDN example - and am running against a SQL Express 2005 server. I have the following problem:
I have start/stop buttons to enable change checking, and a hardcode query that I am using for testing. If I stop and restart I now recieve duplicate notifcations, a single change causing the OnChange event to fires twice with two different ids Repeat this and the event will fires 3 times for each change and so on.
This only happens with a running app, if I restart the app I only get a single notification so I assume I have missed a step in stopping query notifcations or reinitialisation. I have include code below:
private bool Start()
{
try
{
// Remove any existing dependency connection, then create a new one.
SqlDependency.Stop(_currentConnectionString);
if (SqlDependency.Start(_currentConnectionString))
{
if (connection == null)
{
connection = new SqlConnection(_currentConnectionString);
}
if (command == null)
{
command = new SqlCommand(_sqlQueryString, connection);
}
return (true);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
private void Stop()
{
SqlDependency.Stop(_currentConnectionString);
if (command != null)
{
command.Notification = null;
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
private void GetDataSnapshot()
{
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
Hi, I have an Access frontend and an SQL Server backend. I use a Query (in Access frontend) to insert in two tables simultaneously. I have a trigger in one of the involved table which update the foreign key in another involved table. I've built an SQL View with exacly!!! the same syntax as my Query in Access. And when I insert a new entry through the SQL View the trigger fires(the foreign key is updated), but when I insert through my Access Query the trigger doesn't fires (the foreign key is not updated). Any idea ?
I was wondering when events inside IDTSComponentEvents interface are called when you throw a SSIS package? I've got a private class which implements IDTSEvents and along with that got another one that implements the IDTSComponentEvents. I see how neither of them are fired when I debug the code by using F11, and I don't know what is it for.
Let me know your comments or come back to me if you need further details on that.
Is it possible to only handle the outter-most event? For example, I have an email that goes out in response to OnPreExecute and OnPostExecute, but I only want it to go out once, instead of for every tasks/object.
Can anyone please tell me what is wrong with this query: rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") In DreamWeaver the bit in bold is greyed out, why? rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") Thanks Joe
I am attempting to count distinct orders and display by client for the preceding month. Below is my current query. It is providing inaccurate results. Could someone with a fresh look point out my error(s)?
SELECT DISTINCT MtgeBroker, COUNT(CreatedDate) AS [Title Orders for Current Month]
FROM RECalendar
WHERE ( RECalendar.FileType IN ('COM','CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','0 Is Not Closing','Witness') AND RECalendar.ModuleID IN ('594','603','675','814','815','816','817','818','819','820','821','822','823','824','825','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893','894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910','911','912','913','914','915','974') ) AND ( MONTH(EventDateBegin)=MONTH(DATEADD(MM,-1,GETDATE())) AND YEAR(EventDateBegin)= YEAR(DATEADD(MM,-1,GETDATE())) ) GROUP BY MtgeBroker
string ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["blogg"].ConnectionString; SqlConnection con = new SqlConnection(ConnectionString);
Finally, the database has a table called poll, including the fields int lagID Pk string lagnamn int votes
What happens if I push the Rösta(Vote)-button is that it properly displays the text in it's label, but it won't update the database. I hope someone will see where I am going wrong here.
I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:
If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.
For instance, this code:
sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g " sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName" Set rs = conn.Execute(sql) Do While Not rs.EOF lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")" rs.MoveNext Loop Set rs = Nothing
could show the following: 2441-John Doe (10) 2441-John Doe (11) if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.
select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS , SUM(inv_numb, freight_val) FROM OPCSAHH WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND LESS THEN = 2003 AND PREPAID COLLECT FLAG = 'C'
What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.
THe following query generates, Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'if'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ',' errors.
Can anyone tell me what I am missing?
EXECUTE sp_MSforeachtable ' select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user) from ? as o where not exists(select * from users u where (u.users_id = o.rn_create_user) )';
Hi. I have a SP named, for instance, SP1.I need to execute something likeSELECT Sum([Field1]) FROM SP1 WHERE [SP1].[Field1]='0'and I get the message:Server: Msg 208, Level 16, State 3, Line 1Invalid object name 'SP1'.However, SP1 *IS THERE* and runs fine !!!ThanksAlex
For a given table, I want to know all the columns that are included inan index. I have looked on the web and come up with this, which seemsto work, but just wanted some verification. Are there any reasons whyI should be using the metadata functions like OBJECT_NAME?ThanksBruceSELECTDISTINCT c.namefrom sysusers u,sysobjects o,syscolumns c,sysindexes i,sysindexkeys kWHERE o.uid = u.uidAND u.name = userAND o.name = 'ing_customer'AND o.id = i.idAND i.indid = k.indidAND OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) )AND i.indid BETWEEN 1 And 250AND k.id = o.idand k.colid = c.colidand c.id = o.idORDER BY c.name
SELECT T1.lnumber AS LOT_Number,(T2.time_out - T1.time_in) AS Duration, ((DATEPART (hour, (T2.time_out-T1.time_in)) *60) + DATEPART (minute, (T2.time_out-T1.time_in))) AS Minutes, COUNT(DISTINCT T2.test_desc) AS Number_of_Process
FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber
WHERE T2.test_desc = 'Shipping' AND T1.test_desc = 'Receiving'
I have an error message that says:
Msg 8120, Level 16, State 1, Line 3 Column 'Results.lnumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
If I take this line out: COUNT(DISTINCT T2.test_desc) AS Number_of_Process The query runs ok.
Also if I run [COUNT(DISTINCT T2.test_desc) AS Number_of_Process] in:
SELECT COUNT(DISTINCT T2.test_desc) AS Number_of_Process, T2.lnumber AS Lot_number FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber GROUP BY T2.lnumber
It will also give me result. Please help.... Thanks for your time
select a.service_num, a.cust_num from table1 a, table2 b where a.service_num = b.service_num and b.cust_num = b.cust_num and datediff(day, a.compl_date, b.compl_date) between case when month(getdate()+7) - month(getdate()) != 0 then -7 and 7 else -30 and 30 end and a.qty+b.qty=0
it gives me an error message as follow:
Server: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'and'.
actually what i am trying to do is if the date of executing this query is the last week of each month, then the datediff between a.compl_date and b.compl_date is 30 days, if not then the datdiff is 7 days.
We have an asp web aplication using COM on a SQL Server 2000 database. The application uses COM to perform the database access. On altering data, the code checks the @@rowcount to see if the intented insert or update was succesfull - this is done on changes in various colums in various locations in the code. Now we need to add a trigger to update changed data into other tables. This is because we start migrating to a different data structure - while keeping most of the original code intact.
The problem is now, that the trigger beeing fired erases the @@rowcount. I can imagine others to have experienced the same problem. So, the big question is here:
Is there a solition/workaround to keep the @@rowcount on update/insert/deletes on a table evn if a trigger fires?
Thank you, Marco van Schagen, Crexx, the Netherlands
I'm trying to cath content of PRINT statements through InfoMessage event. I have subscribed on InfoMessage event through ConnectionEventsVt interface. The problem that event fired only once for statement. For example i have following T-SQL script:
Code Snippet
PRINT 'Message1' PRINT 'Message2' GO
PRINT 'Message3' PRINT 'Message4' GO
When i execute this script InfoMessage event fired twice. First time Error object Description property contains "Message1" text. Second time it contains "Message3" text.
What i'm doing wrong?
Server: MS SQL Sever 2005 OS: Windows XP SP2 IDE: Delphi 7
the error message shows it's now allow " =�!=�<�<=�>�>=" after sub query..
Select * From ZT_MediaImportLog Where isNumeric(ImportFileTime) = 1 And ImportFileTime < Convert(varchar(10),DateAdd(Month,-CAST (( select keepmonth from ZT_MediaImportLog, ZT_BillerChain a,ZT_BillerInfo b,ZT_Biller c,ZT_databackup d where a.BillerInfoCode = b.BillerInfoCode AND c.CompanyCode = d.Companycode AND ZT_MediaImportLog.Importsource = a.ChainCode ) AS int),GetDate()),112)
I keep getting the error "Invalid attempt to read when no data is present" when trying to query a table in my SQL DB. I have checked and rechecked and the table name and column names within it are spelled correctly. There are only three records in the database, they all have data in them, and the code in Country.Text precisely matches the data in the Country field in one of the records.
It's worth mentioning that when I use Visual Studio 2005's little direct SQL query tool to build and run the following SQL statement that it works properly:
I am perplexed. Any ideas anybody...here is the code...?
Dim SelectSQL_Countries As String SelectSQL_Countries = "SELECT * FROM data_Countries " SelectSQL_Countries &= "WHERE Country='" & Country.Text & "'"Dim con_Countries As New SqlConnection(ConfigurationManager.ConnectionStrings("MySiteMainDB").ConnectionString) Dim cmd_Countries As New SqlCommand(SelectSQL_Countries, con_Countries)Dim reader_Countries As SqlDataReader Try con_Countries.Open() reader_Countries = cmd_Countries.ExecuteReader()StateID.Text = reader_Countries("WordForState") reader_Countries.Close()Catch err As Exception lblResults.Text = err.Message Finally con_Countries.Close() End Try
1 SELECT 2 RowNumber, 3 'Source.Dbf, Plan.Dbf', 4 'Source Name is missing for Source Number "' + IsNull(RTrim(f.SOURCE_NUM),'Unknown') + '" in Plan.Dbf table.' 5 FROM 6 SourceDbf f 7 JOIN 8 ( 9 SELECT DISTINCT 10 SOURCE_NUM, 11 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 12 WHEN 2 Then SRC2NAME 13 WHEN 3 THEN SRC3NAME 14 WHEN 4 THEN SRC4NAME 15 WHEN 5 THEN SRC5NAME 16 WHEN 6 THEN SRC6NAME 17 WHEN 7 THEN SRC7NAME 18 WHEN 8 THEN SRC8NAME 19 WHEN 9 THEN SRC9NAME 20 WHEN 10 THEN SRC10NAME 21 WHEN 11 THEN SRC11NAME 22 WHEN 12 THEN SRC12NAME 23 WHEN 13 THEN SRC13NAME 24 WHEN 14 THEN SRC14NAME 25 WHEN 15 THEN SRC15NAME 26 END 27 FROM 28 PlanDBF p 29 Where 30 p.PLAN_NUM = s.PLAN_NUM 31 ) as SourceName 32 FROM 33 SourceDBF s ) c on f.PLAN_NUM = c.PLAN_NUM
i am getting an error on Line 33 and this what the error says... Msg 207, Level 16, State 1, Line 33Invalid column name 'PLAN_NUM'.
I have tried running this query multiple times with no success I get the following errorIncorrect syntax near '('.I tried with quotes and without quotes around the 10 and also without the brackets around variable. It runs when an integer in entered in the variables place but that is not what I want. What am I doing wrong DECLARE @p AS intSET @p='10'SELECT TOP (@p)* FROM my_tbl order by newid()
strCommand = "SELECT * FROM tblevents WHERE startingDate=#"&startDate &"# AND eventtitle like '%"&criteria &"%' ORDER BY " &sSortSt I want to find any records that match a certain keyword on a specific date... But nothing comes up even though there is an event matching that criteria on the given date. Do I need brackets around my query or something?
What is the reason everything here executes except the very last "INSERT INTO forums_UsersInRoles VALUES ((@@identity),11) "? IF I change the order of the last two queries it is the other one (now last) that is not executed. Is there a limit to how many you can run at a time?
strSQL = " BEGIN INSERT INTO forums_Users (UserName, Password, Email) VALUES ('" & request.form("UserName") & "','" & Request("UserPassword") & "','" & Request("UserEmail") & "'); INSERT INTO forums_UserProfile (UserID) VALUES (@@identity) ;INSERT INTO forums_UsersInRoles VALUES ((@@identity),11) END;"
Hi all, As my user runs a query for her data, the query shows up with someone else's data. Can somebody tell me what happened and how o fix the problem. Thanks!
Since its so long, I've shown it as multiple lines, but in my code it is one long statement:
CurrentDb.Execute "Insert into Speechfiles(...) values (...)"
The table is called Speechfiles and the fields are as follows (types are in brackets)
FileName (text) SpeakerID (number) SamplingRate (number) DirectoryPathID (number) Text (text) CreationDate (text) <- this isn't date format, so not the prob Duration (number) Range (number)
and the values I'm trying to insert are all of the same type as the fields are.... SOOOOOOOOOOOOOooooo, can anyone spot anything? I've spent way to long on this. All my other CurrentDb.Execute "Insert Into...." statements are working fine, it's just this one!