Is transaction name has to be unique?

Hi

I remember that once I had a problem when using a cursor in a sp and when several instances of the sp were running I had a problem when the first sp in the sequence deallocated the cursor and all the other who run in parallel had errors...
Well, this is not the problem now, but my question is, if I have a sp that has begin tran t1, and several instances of the sp are running in parallel, and each of course has begin tran t1, should I expect the same collision effect like with the cursor? Is every tran has to be with unique name? Or maybe the server knows how to manage this and when one tran has started and another sp tried to start another with the same name it makes it wait until the first one committed or rolled back?

Thanks,
Inon.I would think that this would be a particularly bad idea, though I have no specific experience in this area (transaction numbers). As I always understood it, the idea behind an explicitly identified transaction was to be able to roll back that specific transaction, particularly in an asynchronous environment. You have to ask yourself, what is the value in re-using the same transaction identifier? If you are just going to re-use the same identifier, then why bother with an identifier at all?

Regards,

hmscott

is this possible?

hi

I would like to display agregated data, as well as my image, but it seems that this requires me to put my image file field in my group by statement too, but sql does not seem to allow sorting of images. Is what I am trying to possible? Or do I have to do both things separate?

here is the code:

select tbltimesheets.weekno, sum(tblentries.hrsnorm) as hrsnorm, sum(tblentries.hrspot) as hrspot, sum(tblentries.hrsnpot) as hrsnpot, tblusers.username, images.imagefile from
tbltimesheets
inner join tblentries on tbltimesheets.tskey = tblEntries.TSKey
inner join tblusers on tbltimesheets.userkey = tblusers.userkey
inner join images on images.userkey = tblusers.userkey
group by tbltimesheets.weekno, tblusers.username, images.imagefileYou have to ddo the thing separate. YOu can use some smart SQL for this, though, working, and still do it in one query.

Or, instead summing, you could use another method (like one returning the first image in the group).

Is this possible without using cursors?

Hi!

I have 2 tables: Person and Address

Person
(
PersonID int PK
)

Adress
(
AddresID int PK,
PersonID int FK,
Default -- 1 if address is default for person
)

so when I join those table it yelds (for example):

p1 a1 1
p1 a2 0
p1 a3 0
p2 a4 1
p3 a5 0
p3 a6 0

Person may:
- have one default addres and some non-default;
- haven't default address;
- have only default adres

So proper result is:

p1 a1 p1 a1
p2 a4 OR p2 a4
p3 a5 p3 a6

I want to get list of persons and their adresses in following manner:

Get person and
- default adress ID if exists for this person
- any address ID else

Is this possible without using cursors?

Regards,
Walter

Walter:

This can be done with an outer join. The main thing to verify is what you want to do when you have NULL returned for OUTER TABLE results:

select p.personID,
a.addressID,
a.[default]
from Person p
left join address a
on p.personID = a.personID
and a.[default] = 1

-- Sample Output:

-- personID addressID default
-- -- -- -
-- 1 1 1
-- 2 4 1
-- 3 NULL NULL

Dave

|||

Hmmm...

I that statement, you have persons with defaults addresses, or info that there's no default address. But there is no addressID when it is non-default. We don't want NULLs, but any non-default addressID...

Walte

|||

Sorry about that. Is this closer to what you have in mind?

select personID,
addressID
from ( select p.personID,
a.addressID,
a.[default],
row_number () over
( partition by p.personID
order by a.[default] desc, a.addressID
) as seq
from Person p
inner join address a
on p.personID = a.personID
) a
where seq = 1

-- Sample Output:

-- personID addressID
-- -- --
-- 1 1
-- 2 4
-- 3 5

|||

Please mention the version of SQL Server so it is easy to suggest the correct solution that will work in that version or all versions. You can do below in SQL Server 2005:

select ...

from Person as p

cross apply (

select top 1 *

from Address as a

where a.PersonID = p.PersonID

order by a.Default desc

) as pa

And add an index on (Address.PersonID, Address.Default DESC) to get the best performance. If you need solution for older version of SQL Server then please reply back.

|||

I simulated 32767 rows of person data with about 75000 rows of address data with:

drop table dbo.Person
go
create table dbo.Person
(
PersonID int primary key
)
go
drop table dbo.address
go

create table dbo.Address
(
AddressID int primary key,
PersonID int,
[default] tinyint
)
go

insert into person
select iter
from small_iterator

insert into address
select personID,
personID,
0
from person
where personID % 11 > 1

insert into address
select 32768 + personID,
personID,
0
from person
where personID % 11 < 2

insert into address
select 2*32768 + personID,
personID,
0
from person
where personID %17 not in (3, 5, 13)

insert into address
select 3*32768 + personID,
personID,
0
from person
where personID %23 not in (7, 10, 13, 19)

First, I ran this query with these results:

declare @.begDt datetime
set @.begDt = getdate()

select personID,
addressID
from ( select p.personID,
a.addressID,
a.[default],
row_number () over
( partition by p.personID
order by a.[default] desc, a.addressID
) as seq
from Person p
inner join address a
on p.personID = a.personID
) a
where seq = 1

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Sample Output:

-- personID addressID
-- -- --
-- 1 32769
-- 2 2
-- ...
-- 32766 32766
-- 32767 32767

-- |--Filter(WHERE:([Expr1004]=(1)))
-- |--Sequence Project(DEFINE:([Expr1004]=row_number))
-- |--Compute Scalar(DEFINE:([Expr1006]=(1)))
-- |--Segment
-- |--Sort(ORDER BY:([p].[PersonID] ASC, .[default] DESC, .[AddressID] ASC))
-- |--Hash Match(Inner Join, HASH:([p].[PersonID])=(.[PersonID]), RESIDUAL:([Mugambo].[dbo].[Address].[PersonID] as .[PersonID]=[Mugambo].[dbo].[Person].[PersonID] as [p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Person].[PK__Person__7D63964E] AS [p]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Address].[PK__Address__7F4BDEC0] AS ))

-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Address'. Scan count 1, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Elapsed Time
--
-- 656

And then ran this query with these results:

declare @.begDt datetime
select @.begDt = getdate()

select p.personID,
pa.addressID
from person p
cross apply
( select top 1 *
from address as a
where a.personID = p.personID
order by a.[default] desc
) pa

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Person].[PK__Person__7D63964E] AS [p]))
-- |--Sort(TOP 1, ORDER BY:(.[default] DESC))
-- |--Index Spool(SEEK:(.[PersonID]=[Mugambo].[dbo].[Person].[PersonID] as [p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Address].[PK__Address__7F4BDEC0] AS ))

-- Table 'Worktable'. Scan count 32767, logical reads 242508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Address'. Scan count 1, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2563

Dave

|||Please add an index on (Address.PersonID, Address.Default DESC) and you will get the best performance with the APPLY approach. Also, the results from both queries will not be identical because of the additional ordering on AddressID in your query.|||

Hi,

thanks for yours posts :) Unfortunately I use SQL SERVER 2000....

Regards,
Walter

|||

The following query might help you

Select Person.PersonID,Isnull(Adress.AddresID,Adress2.AddresID) from Person
left Outer Join Adress on Person.PersonID = Adress.PersonID And Adress.isDefault = 1
left Outer Join Adress Adress2 On Person.PersonID = Adress2.PersonID And Adress2.isDefault = 0
and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

|||

Select Person.PersonID,Isnull(Adress.AddresID,Adress2.AddresID) from Person
left Outer Join Adress on Person.PersonID = Adress.PersonID And Adress.isDefault = 1
left Outer Join Adress Adress2 On Person.PersonID = Adress2.PersonID And Adress2.isDefault = 0
and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

This query has me confused; I am not sure that I am getting the correct return data for my large test case.

|||

Here if there is a default value then it will pull that value..

If there is no default value instead of fetching Min/Max address id it will pull the random address from the DB.

May be your test case fail if you try to get Max/Min address id as expected.

Change your test case as "IN Non Default Address List instead of Particular Address"

|||Well, I am getting nulls for some address IDs and some rows that have duplicate personIDs. And I agree with you in that this might be a data problem. (I gotta get something to eat.)|||

Mani:

Ran the following three queries and got the results that follow:

select count(*) as [Person Records] from person
select count(distinct personID) as [Address Records] from address
select count(distinct a.personID) [Matched Person Records] from person a inner join address b on a.personID = b.personID

-- Person Records
-- --
-- 32767

-- Address Records
--
-- 32767

-- Matched Person Records
-- -
-- 32767

Therefore, I do not think that the problem is a data problem. I re-examined your select and I question this line:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

specifically, the "... Where Address2.PersonID ..." portion. I think this is the source of the NULL and duplicate PersonID records. When I change this line to:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

I get what I perceive to be "correct" results. Please verify whether or not you aggree.

Also, I completely agree with Umachandar's assessment that we need an additional index. I am therefore going to add a cover index based on (1) personID, (2) default and (3) addressID.

Running one more test.

Dave

|||

Mani:

Ran the following three queries and got the results that follow:

select count(*) as [Person Records] from person
select count(distinct personID) as [Address Records] from address
select count(distinct a.personID) [Matched Person Records] from person a inner join address b on a.personID = b.personID

-- Person Records
-- --
-- 32767

-- Address Records
--
-- 32767

-- Matched Person Records
-- -
-- 32767

Therefore, I do not think that the problem is a data problem. I re-examined your select and I question this line:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

specifically, the "... Where Address2.PersonID ..." portion. I think this is the source of the NULL and duplicate PersonID records. When I change this line to:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

I get what I perceive to be "correct" results. Please verify whether or not you aggree.

Also, I completely agree with Umachandar's assessment that we need an additional index. I am therefore going to add a cover index based on (1) personID, (2) default and (3) addressID.

Running one more test.

Dave

|||

I ran the modified version of Mani's query against my mock tables and got the following results:

-- |--Compute Scalar(DEFINE:([Expr1010]=isnull([Address].[AddressID], [Address].[AddressID])))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Person].[PersonID]))
-- |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Address].[PersonID])=([Person].[PersonID]), RESIDUAL:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Sort(ORDER BY:([Address].[PersonID] ASC))
-- | | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]), WHERE:([Address].[default]=1))
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B]), ORDERED FORWARD)
-- |--Hash Match(Cache, HASH:([Person].[PersonID]), RESIDUAL:([Person].[PersonID]=[Person].[PersonID]))
-- |--Nested Loops(Inner Join, OUTER REFERENCES:([Address].[AddressID]))
-- |--Sort(TOP 1, ORDER BY:([Expr1009] ASC))
-- | |--Compute Scalar(DEFINE:([Address].[AddressID]=[Address].[AddressID], [Expr1009]=newid()))
-- | |--Index Spool(SEEK:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]))
-- |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]), SEEK:([Address].[AddressID]=[Address].[AddressID]), WHERE:([Address].[PersonID]=[Person].[PersonID] AND [Address].[default]=0) ORDERED FORWARD)

-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Table 'Address'. Scan count 3, logical reads 585, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 85514, logical reads 347474, physical reads 0, read-ahead reads 0.

-- Elapsed Time
--
-- 2186

I next added this index:

create index address_personID_ndx
on address (personID, [default] desc, addressID)

And then I re-tested the modified version of Mani's query and received these improved results:

-- |--Compute Scalar(DEFINE:([Expr1010]=isnull([Address].[AddressID], [Address].[AddressID])))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Person].[PersonID]))
-- |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Address].[PersonID])=([Person].[PersonID]), RESIDUAL:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Index Scan(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), WHERE:([Address].[default]=1) ORDERED FORWARD)
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B]), ORDERED FORWARD)
-- |--Hash Match(Cache, HASH:([Person].[PersonID]), RESIDUAL:([Person].[PersonID]=[Person].[PersonID]))
-- |--Nested Loops(Inner Join, OUTER REFERENCES:([Address].[AddressID]))
-- |--Sort(TOP 1, ORDER BY:([Expr1009] ASC))
-- | |--Compute Scalar(DEFINE:([Address].[AddressID]=[Address].[AddressID], [Expr1009]=newid()))
-- | |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), SEEK:([Address].[PersonID]=[Person].[PersonID]) ORDERED FORWARD)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), SEEK:([Address].[PersonID]=[Person].[PersonID] AND [Address].[default]=0 AND [Address].[AddressID]=[Address].[AddressID]) ORDERED FORWARD)

-- Table 'Address'. Scan count 65535, logical reads 131671, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 1953

I then tested a different query and obtained the results that follow:

select p.personID,
( select top 1 addressID from address b
where p.personID = b.personID
order by [default] desc
) as addressID
from Person p

-- |--Compute Scalar(DEFINE:(.[AddressID]=.[AddressID]))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B] AS [p]))
-- |--Compute Scalar(DEFINE:(.[AddressID]=.[AddressID]))
-- |--Top(1)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx] AS ), SEEK:(.[PersonID]=[p].[PersonID]) ORDERED FORWARD)

-- Table 'Address'. Scan count 32767, logical reads 65593, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 1890

There is not a lot of difference with respect to execution time between this query and the modified version of Mani's query. This version has one less join and therefore half the scans and half the logical reads. Also, if you neglect to add the index this query is liable to run slow because of the NESTED LOOP join and associated TABLE SCANS instead of INDEX SEEKS.

Either way, BE SURE TO ADD THE INDEX RECOMMENDED BY Umachandar!

Dave

sql

Is There Any Body To Help Me....?

hi
I am having 120 crystal reports of 9 and when ever I want to change the DBuser I am facing problem and i have to change for all the 120 reports
IS THERE ANY EASY SOLUTION FOR ME..?
:thumb:I think there is nothing in crystal reports that you can't automate with Visual Basic. There is a lot of help around on the internet on this. You might want to have a look at this to start with...
http://www.codesforall.com/cr/articles.htm|||poor u. u can customize 1 CR to handle all 120, providing that they r all columner reports. u can change the DB user, location, etc at run time using Vb code.
u can find answers if u check other answers on this forum, so i'm not repeating the same stuff

Is there any API to browse for folder on SQL Server's machine?

Hi!
I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which
allows me to implement a folder select dialog.
But SQL Server's Enterprise Manager already has such dialog. For example
if you open "Attach Database"
dialog and then press browse [...] button Enterprise Manager will show you
"Browse For Existing File" dialog. And
as you can see all the path information in that dialog is local to SQL
Server's machine. This is what
SHBrowseForFolder cannot do for you because SHBrowseForFolder always shows
paths relative to local machine.
My question is: Is there any way to reuse that Enterprise Manager's
"Browse For Existing File" dialog? Or I am
obliged to reinvent it?Hi,
You need to re-invent it using the xp's you mentioned
Greg O
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.suy1vyq7n8ihmu@.iw2k.helpmicro.local...
> Hi!
> I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which
> allows me to implement a folder select dialog.
> But SQL Server's Enterprise Manager already has such dialog. For example
> if you open "Attach Database"
> dialog and then press browse [...] button Enterprise Manager will show you
> "Browse For Existing File" dialog. And
> as you can see all the path information in that dialog is local to SQL
> Server's machine. This is what
> SHBrowseForFolder cannot do for you because SHBrowseForFolder always shows
> paths relative to local machine.
> My question is: Is there any way to reuse that Enterprise Manager's
> "Browse For Existing File" dialog? Or I am
> obliged to reinvent it?|||Hi
If you use SQL profiler to check out what EM is doing you will see the same
procedures being called!
John
"Igor Solodovnikov" wrote:

> Hi!
> I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which
> allows me to implement a folder select dialog.
> But SQL Server's Enterprise Manager already has such dialog. For example
> if you open "Attach Database"
> dialog and then press browse [...] button Enterprise Manager will show you
> "Browse For Existing File" dialog. And
> as you can see all the path information in that dialog is local to SQL
> Server's machine. This is what
> SHBrowseForFolder cannot do for you because SHBrowseForFolder always shows
> paths relative to local machine.
> My question is: Is there any way to reuse that Enterprise Manager's
> "Browse For Existing File" dialog? Or I am
> obliged to reinvent it?
>|||You could look into the SQL-NS API (where NS stands for Name Space). The API
exposes most of EM
dialogs as COM objects, I'm not sure about the file browsing dialogs, though
. It is not
re-distributable, though. This means that the client machine need the SQL Se
rver tools (EM)
installed to run your app. And it will not be carried forward to SQL Server
2005.
Two other alternatives:
Use the undocumented xp's you mention. Downside is that they are undocumente
d, unsupported etc.
Use xp_cmdshell and DIR etc. Harder to interpret the result (a table with on
e column, so some string
parsing is needed).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in me
ssage
news:op.suy1vyq7n8ihmu@.iw2k.helpmicro.local...
> Hi!
> I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which a
llows me to implement a
> folder select dialog.
> But SQL Server's Enterprise Manager already has such dialog. For example
if you open "Attach
> Database"
> dialog and then press browse [...] button Enterprise Manager will show you "Br
owse For Existing
> File" dialog. And
> as you can see all the path information in that dialog is local to SQL Se
rver's machine. This is
> what
> SHBrowseForFolder cannot do for you because SHBrowseForFolder always shows
paths relative to
> local machine.
> My question is: Is there any way to reuse that Enterprise Manager's "Brow
se For Existing File"
> dialog? Or I am
> obliged to reinvent it?|||> You could look into the SQL-NS API (where NS stands for Name Space). The
> API exposes most of EM dialogs as COM objects, I'm not sure about the
> file browsing dialogs, though. It is not re-distributable, though. This
> means that the client machine need the SQL Server tools (EM) installed
> to run your app. And it will not be carried forward to SQL Server 2005.
Thank you for this information. I will loo into SQL-NS

> Two other alternatives:
> Use the undocumented xp's you mention. Downside is that they are
> undocumented, unsupported etc.
And another downside: i need to spend significant time to implement and
debug that dialog. :(

Is there an Online Remote SQL Server I can test against?

Hi

I am trying to connect an application to a remote sql server database, however it does not appear to eb configured for remote connections.

Does anyone know a connection string of a database I can connect to , soem sort of learnsers database sort of idea ?

Thank you.Fo anyone looking at the post ,just download SQL express