|
|
Can SqlDependency detect when a row is inserted?
|
|
|
|
|
Yes, since if the result set based on the select statement is changed, the OnChange event is triggered. This actually means that all modifications (updates, inserts and deletes) are notified as long as they change the result.
Or at least this is my understanding 
There's a bit old but quite good documentation about this: Query Notifications in ADO.NET 2.0
[^]
Mika
|
|
|
|
|
Hi!
I have a (almost) finished program using a database on an Sql Server 2005 Express. I don't have access to the program code but to the database. I need to track changes made (insert, upgrade, delete) in that database in order to compare them to changes made in my mobile version of that program. I am considering an easy solution for the changes on the device side, storing these changes in an XML file. How can I track these changes to the Express database? Is it possible via C# code or stored procedures or anything?
Thanks / Jacob
|
|
|
|
|
Is the mobile version using the same database as the other version of your program? Or is the idea that you have several databases and you want to compare their changes (I guess I didn't understand where the changes came from in the mobile version, from the program itself or the database).
If all the programs use one database, I think you could use triggers on the database side to gather (interesting) changes to the data.
Also one question is that do you want to compare the changes (and what would be the 'baseline') or do you want to compare current situations in different places (this sounds like you're trying to create a replication like logic).
|
|
|
|
|
Replication yes. I have an Sql Server Express DB and a compact .sdf file. Since the device is working offline I need to try and reinvent merge replication I think. I have never used triggers, is that the way to do it? Where are they implemented?
And yes, what I want to do is to track these changes, e.g. store them in a table seems an easy way pf tracking these events.
After googling a little I must point out that this DB has 165 tables. Do triggers have to be created for every single one?
Thanks for your help /Jacob
modified on Thursday, February 19, 2009 3:33 AM
|
|
|
|
|
Jacob Flarup wrote: I have never used triggers, is that the way to do it? Where are they implemented?
Triggers are created upon a table. Have a look at: CREATE TRIGGER[^]
You could for example take information from inserted and deleted virtual tables inside the trigger and sotre it in some other table. If you like, you could store it in xml.
Jacob Flarup wrote: Do triggers have to be created for every single one?
Yes they do, but if the logic is always the same, you could for example generate the trigger code for each table.
|
|
|
|
|
I believe what you want to do is create a set of triggers on the tables that you want to track changes and write values to a "changes" table. You would then need to write reconciliation logic to compare the values from your mobile device to your database. One way of handling this would be to compare timestamps of the two systems and have the latest timestamp win.
|
|
|
|
|
Ive done this so many times, I just cant seem to get my head around what's wrong!
I have a table with data which looks like this
Columns (in order): siteNo, cardSchemeCode, currencyCode, saleAmount, saleTotal, refundAmount, refundTotal
0000001 DEL GBP 12 240.05 0 0.00
0000001 JCB GBP 1 20.00 0 0.00
CU1000000001 DEL GBP 10 166.51 0 0.00
I am trying to get Xml that looks like:
<Root>
<Site siteNo="0000001">
<Currency currencyCode="GBP">
</Site>
<Site siteNo="CU1000000001">
<Currency currencyCode="GBP">
</Site>
</Root>
Instead I get:
<Root>
<Site siteNo="0000001" />
<Site siteNo="CU1000000001">
<Currency currencyCode="GBP" />
<Currency currencyCode="GBP" />
</Site>
</Root>
Here is my SQL:
SELECT 1 TAG,
NULL Parent,
Site.siteNo 'Site!1!siteNo',
NULL 'Currency!2!currencyCode'
FROM records [Site]
GROUP BY siteNo
UNION
SELECT 2 TAG,
1 Parent,
Site.siteNo,
Currency.currencyCode
FROM records [Currency]
INNER JOIN records [Site]
ON Currency.siteNo = Site.siteNo
GROUP BY Site.siteNo, Currency.currencyCode
FOR XML EXPLICIT, ROOT('Root')
Any help?
|
|
|
|
|
Hi,
Your query is otherwise correct, but you must add the ORDER BY clause since the FOR XML processes the row (almost) without no logic in the order they are in the result set. So the query would be:
...
ORDER BY 'Site!1!siteNo',
'Currency!2!currencyCode'
FOR XML EXPLICIT, ROOT('Root')
Mika
|
|
|
|
|
Hi all,
I am using SQL server 2000, I have a table with four columns (ID, Password, Name, contact).
This table is being used by a web application which is having a registration page, the registration credentials are inserted to above table in server.
Here I want to know how many users can simultaneously register. I mean to what particular count does database insert the records into the same table simultaneously.
Please address the scenario in detail if possible with example.
Regards,
ashok
|
|
|
|
|
Its really only limited by your hardware performance, sql server 2000 can handle many concurrent connections.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi there!
I am not sure whether I am in the right forum. Anyway, I try to find a solution here:
In our network/domain we have a user group. For this user group I have created a login on a SQL Server 2005 (Standard Edition) instance. This login is mapped to a user in the database. We are using Windows Authentication and there is no chance to use SQL Server Authentication.
OK, I expect any user that is assigned to the user group (in the domain) to be able to login to my database or at least to the server. But, this does not work! Is there a property I need to set on the SQL Server? What's wrong with my configuration?
Thanks for any ideas and hints,
Florian
|
|
|
|
|
The description you gave seems fine.
Is the login enabled? You could also use sp_validatelogins[^] to check that it's valid.
Also it would be good to know what is the error you get when trying to connect to the db. You could try it for example using Management Studio (or some other program that gives the original error message SQL Server throws).
|
|
|
|
|
Yeap, it is enabled (Login properties -> Status).
The procedure sp_validatelogins did return no rows. Should it return rows?
The error message that appears if I try to login:
"Cannot connect to mypc\SQLSERVER2005.
Additional Information:
Login failed for user 'domain\user'. (Microsoft SQL Server, Error: 18456)"
The error message from the server log:
"Error: 18456, Severity: 14, State: 11."
Of course I searched the www for that message. The result was that state 11 means "Valid login but server access failure". But no solution that fits...
Again the scenario:
domain\user is the user that is a member of the group (AD) domain\SqlServerUserGroup. On the database there is a login domain\SqlServerUserGroup. So I think it should work, shouldn't it? If I add a login domain\user it is working. But my intention is to maintain the access using the assignment of user to the AD group.
modified on Thursday, February 19, 2009 3:28 AM
|
|
|
|
|
Diving Flo wrote: The procedure sp_validatelogins did return no rows. Should it return rows?
No it shouldn't. If it doesn't return rows, all windows authenticated logins in SQL Server are valid in Windows domain.
Your description seems fine to me and it should work. Could you try to add the login for that group again (or perhaps a new test group), just in case that it's interpreted as local group or something else.
You could also check what you see with:
select * from syslogins
Especially check columns: name, loginname, hasaccess, isntname, isntgroup and isntuser. Those should show if there's some misinterpretation somewhere.
|
|
|
|
|
Sorry for the late reply.
Adding a new user to the original group does not make any difference. The newly added user cannot log on to the database as well.
Adding a new group to the domain is not possible. But I have about 6 different groups that I need to configure on the database. With none of them it is possible to log on.
But I did try the following: I added a local group with users of our domain. And now the users of that local group can log on to the server. Is it possible that the database is not able to request or resolve the nt group of a user that wants to log on? May be the account (local system) that is used to run the sql server has not the right priviliges?
I also checked the syslogins. Name and loginname are equal for the groups of interest. All of them have hasaccess, isntname and isntgroup set as true. Isntuser is false for those entries. Seems to be as it should be.
|
|
|
|
|
Diving Flo wrote: Adding a new user to the original group does not make any difference. The newly added user cannot log on to the database as well
That would be expected since the login is executed at group level so the problem aplies to all users in that group.
Diving Flo wrote: With none of them it is possible to log on
Ok, so it's not group specific.
Diving Flo wrote: Is it possible that the database is not able to request or resolve the nt group of a user that wants to log on? May be the account (local system) that is used to run the sql server has not the right priviliges
Oh, if you're using local system. That account may not have any access to domain services, depending on the privileges you have given to it. See: Service Account[^] and Setting Up Windows Service Accounts[^].
Also if you use Kerberos (which is recommended) instead of NTLM, you should take a look at this: http://support.microsoft.com/kb/909801[^].
|
|
|
|
|
hi,
how to trace(break point) the stored procedures
can we get values that was declared in the stored procedure..
thanks..
|
|
|
|
|
|
Hi all ,
Iam Using a query which returns a bulk of data.
first my query contains sub query to find the count so i panned to change as a function ..
So i created a function which returns a table..
create function fnname
returns table(name as varchar,....,....,...,...,...,...)
I inserted the values into the temp table from the select query and
(i want to find the count of receipt numbers so ...
i updated the query with another query update temp table set rno =(select count(rno) from table where
....)
then i executed the function but it is quite slow
please suggest me any way to increase the speed of the function or query..
how to increase the speed of the function
|
|
|
|
|
cbenan wrote: sub query
Have you tried a join?
|
|
|
|
|
It is unikely that the use of the function will affect the speed, it is more likely to be the number of times you call it. Quite often you can change the performance by totally restructuring your query. Break it down to part and do the part that reduces your data volume the most first.
Try using the execution plan to identify table scans that do not use indexes.
I have found the using table vars to hold large volumes is a disaster - I have gone back to #temp tables for anything over 2k rows. I also have no compunction about throwing an index onto a temp table.
If you have 2005 then you may want to look at rownumber and partitioning in a query to get the count by invoices.
Your question does not have 1 answer, this is where science turns into art, good luck!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<ok thank="" u="" very="" much="" for="" ur="" reply="">
|
|
|
|
|
Typically if you break down a single query to multiple different operations using a function or a procedure, the result can be slower. Because if you have separate SQL statements there's no way for the optimizer to search for the best plan to the whole query, only for the subcomponents.
Also if you use temp tables and update and select them, you'll most likely cause physical I/O which may/may not be prevented when using a single query.
Sometimes it's just impossible to write a single query for a certain task, but if you do have the opportunity, you should test the results using this approach (create a single query, combine few operations thus eliminating intermediate steps etc).
Another thing is indexing. Make sure that you have adequate indexes and on the righ columns (or column combinations).
For further details, I think you should post the code you currently have.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
