Database

FOR XML EXPLICIT driving me nuts!

18-Feb-09 23:03

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.

The need to optimize rises from a bad design.My articles[^]

Re: Track DB changes

David Mujica18-Feb-09 6:26

David Mujica

18-Feb-09 6:26

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.

J4amieC18-Feb-09 1:47

J4amieC

18-Feb-09 1:47

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?

Re: FOR XML EXPLICIT driving me nuts!

Wendelius18-Feb-09 5:56

Simultaneous access to SQL server...

18-Feb-09 5:56

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

The need to optimize rises from a bad design.My articles[^]

ashok_rgm18-Feb-09 0:07

ashok_rgm

18-Feb-09 0:07

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

Re: Simultaneous access to SQL server...

Ashfield18-Feb-09 1:29

Ashfield

18-Feb-09 1:29

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

SQL Server Access

Diving Flo17-Feb-09 23:37

Diving Flo

17-Feb-09 23:37

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

Wendelius18-Feb-09 6:24

Re: SQL Server Access [modified]

18-Feb-09 6:24

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).

The need to optimize rises from a bad design.My articles[^]

Diving Flo18-Feb-09 20:32

Diving Flo

18-Feb-09 20:32

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

Wendelius19-Feb-09 8:13

19-Feb-09 8:13

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.

The need to optimize rises from a bad design.My articles[^]

Diving Flo24-Feb-09 20:21

Diving Flo

24-Feb-09 20:21

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.

Wendelius25-Feb-09 7:15

Re: Break point in sqlserver

25-Feb-09 7:15

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[^].

The need to optimize rises from a bad design.My articles[^]

Break point in sqlserver

venu65617-Feb-09 21:53

venu656

17-Feb-09 21:53

hi,
how to trace(break point) the stored procedures
can we get values that was declared in the stored procedure..
thanks..

Wendelius17-Feb-09 23:23

Improve the speed of Function

17-Feb-09 23:23

Have a look at this: Walkthrough: Debug a T-SQL Stored Procedure[^]

The need to optimize rises from a bad design.My articles[^]

member2717-Feb-09 15:05

member27

17-Feb-09 15:05

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

PIEBALDconsult17-Feb-09 16:05

PIEBALDconsult

17-Feb-09 16:05

cbenan wrote:
sub query

Have you tried a join?

Mycroft Holmes17-Feb-09 16:05

Mycroft Holmes

17-Feb-09 16:05

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

member2718-Feb-09 3:41

member27

18-Feb-09 3:41

Wendelius17-Feb-09 17:57

query to get results from 4 tables

17-Feb-09 17:57

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.

The need to optimize rises from a bad design.My articles[^]

icanmakeiteasy17-Feb-09 0:29

icanmakeiteasy

17-Feb-09 0:29

i am bit confused about my question only please help me to get the result

i hav 3 tables
table1,table2,table3

table1
uid   shiftcode   startdate     enddate
100      s1        xxxxxx         xxxxx
101      s1        xxxxxx         xxxxx
102      s1        xxxxxx         xxxxx

table2
uid   patterncode
103        p1


table3
uid      shiftcode     patterncode
103         s2              p1

i want shiftcode's of 100 to 103 users
how to get

plzz help me....

icanmakeiteasy

Wendelius17-Feb-09 1:37

Re: query to get results from 4 tables [modified]

17-Feb-09 1:37

I think your question is lacking some information. Could you explain the logic of combining those rows together. Is it for example that when table1 has shiftcode s1 you want shiftcode s2 from table3 or is the idea to go through table2 and join patterncode, but then again how does pattern code relate to shiftcode in table1 etc.

The need to optimize rises from a bad design.My articles[^]

icanmakeiteasy17-Feb-09 1:54

icanmakeiteasy

17-Feb-09 1:54

thx for reply...

user 103 is on bench so his shift code is s2 or any other
if user got work for some days simply he moves to table1 with his working days and his work code(s1,s2,s3..etc)

just i want work code(shiftcode) of 100 to 103 users between given dates

icanmakeiteasy

modified on Tuesday, February 17, 2009 8:18 AM

Wendelius17-Feb-09 2:36

17-Feb-09 2:36

So did I understand correctly, table1 contains info about users andtheir work periods and you want to find users having work in certain period. If that's the case, could it be something like

select ...
from table1
where startdate between periodstart and periodend
or enddate between periodstart and periodend

where the periodstart and periodend are the dates you're interested in

The need to optimize rises from a bad design.My articles[^]

icanmakeiteasy17-Feb-09 19:02

icanmakeiteasy

17-Feb-09 19:02

thx for reply...

srry... i am mistaken
table3 does not have uid column
table3
shiftcode     patterncode
  s2              p1

i want the shift code of users (100 to 103) between given periodic times

by using given query i can not get the shift code of user 103 where does not exist in the table1, so i have to get the shift code from table3, here table3 having patterncode only, now i have to get the paaterncode from table2... using that i want to show the shiftcode of user 103....

am i clear now??

icanmakeiteasy